protected override Expression VisitPropertyAccess(PropertyAccessExpression expr)
{
string memberName = MemberNameVisitor.GetMemberName(_rootCriteria, expr);
_projections.Add(NHProjections.Property(memberName));
return(expr);
}
protected override Expression VisitMemberAccess(MemberExpression expr)
{
//this must be a grouping parameter member access
IProjection projection = _rootCriteria.GetProjection();
if (projection != null && projection.IsGrouped)
{
_projections.Add(NHProjections.Alias(projection, expr.Member.Name));
}
return(expr);
}
protected override Expression VisitUnary(UnaryExpression expr)
{
if (expr.NodeType == ExpressionType.Convert)
{
var visitor = new SelectArgumentsVisitor(_rootCriteria, _session);
visitor.Visit(expr.Operand);
ProjectionList list = NHProjections.ProjectionList();
foreach (IProjection proj in visitor._projections)
{
list.Add(proj);
}
var projection = new CastProjection(NHibernateUtil.GuessType(expr.Type), list);
_projections.Add(projection);
}
return(expr);
}
public IList <UserSimpleDTO> GetUsersSimpleData(bool all = true)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
User u = null;
var users = session.QueryOver(() => u)
.Where(Res.EqProperty(Pro.Property("u.IsSystemUser"),
Pro.Conditional(Res.Eq(Pro.Constant(all), true), Pro.Property("u.IsSystemUser"), Pro.Constant(false, SqlType.Boolean))))
.SelectList(l => l
.Select(() => u.Id)
.Select(() => u.Username)
.Select(() => u.Name)
.Select(() => u.Surname))
.OrderBy(() => u.Username).Asc
.TransformUsing(Transformers.AliasToBeanConstructor(typeof(UserSimpleDTO).GetConstructors()[1]))
.List <UserSimpleDTO>();
return(users);
}
}
private IList<PermissionDTO> GetPermissions()
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Permission p = null;
UserRole r = null;
User u = null;
var permissions = session.QueryOver(() => p)
.JoinAlias(() => p.Roles, () => r)
.JoinAlias(() => r.Users, () => u)
.Where(Res.Eq(Pro.Property("u.Id"), AuthenticatedUser.Id.Value))
.SelectList(l => l
.Select(Pro.Group(() => p.Id))
.Select(Pro.Group(() => p.Name)))
.TransformUsing(Transformers.AliasToBeanConstructor(typeof(PermissionDTO).GetConstructors()[1]))
.List<PermissionDTO>();
return permissions;
}
}
protected override NewExpression VisitNew(NewExpression expr)
{
NewExpression newExpr = base.VisitNew(expr);
_transformer = new TypeSafeConstructorMemberInitResultTransformer(expr);
var aggregators = expr.Arguments.Where(arg => arg is MethodCallExpression && SupportsMethod(((MethodCallExpression)arg).Method.Name));
if (aggregators.Any())
{
foreach (var exp in expr.Arguments.Except(aggregators))
{
string propertyName = MemberNameVisitor.GetMemberName(_rootCriteria, exp);
if (!String.IsNullOrEmpty(propertyName))
{
_projections.Add(NHProjections.GroupProperty(propertyName));
}
}
}
return(newExpr);
}
public string GetSelectedTaskParticipants(int taskId)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Task t = null;
User u = null;
var participants = session.QueryOver(() => t)
.JoinAlias(() => t.Users, () => u)
.Where(() => t.Id == taskId)
.SelectList(l => l
.Select(Pro.SqlFunction("CONCAT", SqlType.String,
Pro.SqlFunction("SUBSTRING", SqlType.String,
Pro.SqlFunction("UPPER", SqlType.String, Pro.Property("u.Name")), Pro.Constant(1), Pro.Constant(1))
,
Pro.SqlFunction("SUBSTRING", SqlType.String,
Pro.SqlFunction("UPPER", SqlType.String, Pro.Property("u.Surname")), Pro.Constant(1), Pro.Constant(1))
)))
.List <string>();
return(string.Join(", ", participants.OrderBy(s => s)));
}
}
public AggregateProjection Avg()
{
return(Projections.Avg(PropertyName));
}
public IList <TaskPrimaryDataDTO> GetTasks(int pageNo, int pageSize, TaskSearchCriteria criteria)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Task t = null;
TaskStatus s = null;
TaskPriority p = null;
TaskGenre g = null;
TaskComment c = null;
TaskComment c_sub = null;
User uc = null;
var query = session.QueryOver(() => t)
.JoinAlias(() => t.Status, () => s)
.JoinAlias(() => t.Priority, () => p)
.JoinAlias(() => t.Genre, () => g)
.Left.JoinAlias(() => t.Comments, () => c)
.Left.JoinAlias(() => c.User, () => uc)
.Where(
Res.EqProperty(
Pro.Conditional(Res.IsNull("c.Id"), Pro.Constant(0), Pro.Property("c.Id")),
Pro.SubQuery(QueryOver.Of(() => c_sub)
.Where(() => c_sub.Task.Id == c.Task.Id)
.Select(Pro.Conditional(Res.IsNull("c_sub.Id"), Pro.Constant(0), Pro.Max("c_sub.Id"))))
));
if (criteria.TaskAuthorUsername.IsNotNull())
{
query.Where(() => t.Author == criteria.TaskAuthorUsername);
}
if (criteria.ShowTasksOnly)
{
query.Where(() => g.Id == 4);
}
if (criteria.ShowInstallationsOnly)
{
query.Where(() => g.Id == 2);
}
if (criteria.ShowTonersOnly)
{
query.Where(() => g.Id == 3);
}
if (criteria.ShowUpdatesOnly)
{
query.Where(() => g.Id == 1);
}
if (criteria.ShowWithoutCanceledTasks)
{
query.Where(Res.Not(Res.Eq(Pro.Property("s.Id"), 1)));
}
if (criteria.TaskId.IsNotNull())
{
query.Where(() => t.Id == criteria.TaskId.Value);
}
if (criteria.Topic.IsNotNull())
{
query.Where(Res.InsensitiveLike(Pro.Property("t.Topic"), criteria.Topic, MatchMode.Anywhere));
}
if (criteria.Content.IsNotNull())
{
query.Where(Res.InsensitiveLike(Pro.Property("t.Content"), criteria.Content, MatchMode.Anywhere));
}
if (criteria.StartDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Date, Pro.Property("t.StartPeriod")), criteria.StartDate.Value));
}
if (criteria.StartHour.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Time, Pro.Property("t.StartPeriod")), criteria.StartHour.Value.ToString()));
}
if (criteria.EndDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Date, Pro.Property("t.EndPeriod")), criteria.EndDate.Value));
}
if (criteria.EndDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Time, Pro.Property("t.EndPeriod")), criteria.EndHour.Value.ToString()));
}
if (criteria.TaskPriorityId.IsNotNull())
{
query.Where(() => p.Id == criteria.TaskPriorityId);
}
if (criteria.TaskStatusId.IsNotNull())
{
query.Where(() => s.Id == criteria.TaskStatusId);
}
if (criteria.TaskParticipantId.IsNotNull())
{
Task subT = null;
User subU = null;
var subquery = QueryOver.Of(() => subT)
.JoinAlias(() => subT.Users, () => subU)
.Where(Res.And(
Res.EqProperty(Pro.Property("t.Id"), Pro.Property("subT.Id")),
Res.Eq(Pro.Property("subU.Id"), criteria.TaskParticipantId)))
.SelectList(l => l
.Select(() => subU.Id));
query.WithSubquery.WhereExists(subquery);
}
if (criteria.Comment.IsNotNull())
{
TaskComment subC = null;
Task subT = null;
var subquery = QueryOver.Of(() => subC)
.JoinAlias(() => subC.Task, () => subT)
.Where(Res.And(
Res.EqProperty(Pro.Property("subT.Id"), Pro.Property("t.Id")),
Res.InsensitiveLike(Pro.Property("subC.Content"), criteria.Comment, MatchMode.Anywhere)))
.SelectList(l => l
.Select(() => subT.Id));
query.WithSubquery.WhereExists(subquery);
}
if (criteria.CommentAuthorId.IsNotNull())
{
TaskComment subC = null;
Task subT = null;
User subU = null;
var subquery = QueryOver.Of(() => subC)
.JoinAlias(() => subC.Task, () => subT)
.JoinAlias(() => subC.User, () => subU)
.Where(Res.And(
Res.EqProperty(Pro.Property("subT.Id"), Pro.Property("t.Id")),
Res.Eq(Pro.Property("subU.Id"), criteria.CommentAuthorId)))
.SelectList(l => l
.Select(() => subT.Id));
query.WithSubquery.WhereExists(subquery);
}
query.SelectList(l => l
.Select(() => t.Id)
.Select(() => t.Topic)
.Select(() => t.Content)
.Select(() => t.StartPeriod)
.Select(() => t.EndPeriod)
.Select(() => c.Id)
.Select(() => c.Content)
.Select(() => c.Date)
.Select(() => uc.Username)
.Select(() => s.Name)
.Select(() => p.Name)
.Select(() => g.Name)
.Select(() => t.Author))
.OrderBy(() => t.Id).Desc
.TransformUsing(Transformers.AliasToBeanConstructor(typeof(TaskPrimaryDataDTO).GetConstructors()[1]))
.Skip(pageSize * (pageNo - 1)).Take(pageSize);
return(query.List <TaskPrimaryDataDTO>());
#region Above generates sql like...
/*
* SELECT this_.Id AS y0_,
* this_.Topic AS y1_,
* this_.StartPeriod AS y2_,
* this_.EndPeriod AS y3_,
* c4_.Id AS y4_,
* c4_.Content AS y5_,
* c4_.Date AS y6_,
* u5_.Username AS y7_,
* s1_.Name AS y8_,
* p2_.Name AS y9_,
* g3_.Name AS y10_
* FROM `Task` this_
* LEFT OUTER JOIN `TaskComment` c4_ ON this_.Id=c4_.Task_id
* LEFT OUTER JOIN `User` u5_ ON c4_.User_id=u5_.Id
* INNER JOIN `TaskGenre` g3_ ON this_.Genre_id=g3_.Id
* INNER JOIN `TaskStatus` s1_ ON this_.Status_id=s1_.Id
* INNER JOIN `TaskPriority` p2_ ON this_.Priority_id=p2_.Id
* WHERE (CASE WHEN c4_.Id IS NULL THEN ?p0 ELSE c4_.Id END) =
* (SELECT (CASE WHEN this_0_.Id IS NULL THEN ?p1 ELSE max(this_0_.Id) END) AS y0_ FROM `TaskComment` this_0_)
* ORDER BY this_.Id DESC
* LIMIT ?p2;
*
* ?p0 = 0 [Type: Int32 (0)],
* ?p1 = 0 [Type: Int32 (0)],
* ?p2 = 50 [Type: Int32 (0)]
*/
#endregion
}
}
public int GetTasksPagesCount(int pageSize, TaskSearchCriteria criteria)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Task t = null;
TaskStatus s = null;
TaskPriority p = null;
TaskGenre g = null;
TaskComment c = null;
User uc = null;
var query = session.QueryOver(() => t)
.JoinAlias(() => t.Status, () => s)
.JoinAlias(() => t.Priority, () => p)
.JoinAlias(() => t.Genre, () => g)
.Left.JoinAlias(() => t.Comments, () => c)
.Left.JoinAlias(() => c.User, () => uc);
if (criteria.TaskAuthorUsername.IsNotNull())
{
query.Where(() => t.Author == criteria.TaskAuthorUsername);
}
if (criteria.ShowTasksOnly)
{
query.Where(() => g.Id == 4);
}
if (criteria.ShowInstallationsOnly)
{
query.Where(() => g.Id == 2);
}
if (criteria.ShowTonersOnly)
{
query.Where(() => g.Id == 3);
}
if (criteria.ShowUpdatesOnly)
{
query.Where(() => g.Id == 1);
}
if (criteria.ShowWithoutCanceledTasks)
{
query.Where(Res.Not(Res.Eq(Pro.Property("s.Id"), 1)));
}
if (criteria.TaskId.IsNotNull())
{
query.Where(() => t.Id == criteria.TaskId.Value);
}
if (criteria.Topic.IsNotNull())
{
query.Where(Res.InsensitiveLike(Pro.Property("t.Topic"), criteria.Topic, MatchMode.Anywhere));
}
if (criteria.Content.IsNotNull())
{
query.Where(Res.InsensitiveLike(Pro.Property("t.Content"), criteria.Content, MatchMode.Anywhere));
}
if (criteria.StartDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Date, Pro.Property("t.StartPeriod")), criteria.StartDate.Value));
}
if (criteria.StartHour.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Time, Pro.Property("t.StartPeriod")), criteria.StartHour.Value.ToString()));
}
if (criteria.EndDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Date, Pro.Property("t.EndPeriod")), criteria.EndDate.Value));
}
if (criteria.EndDate.IsNotNull())
{
query.Where(Res.Eq(Pro.Cast(SqlType.Time, Pro.Property("t.EndPeriod")), criteria.EndHour.Value.ToString()));
}
if (criteria.TaskPriorityId.IsNotNull())
{
query.Where(() => p.Id == criteria.TaskPriorityId);
}
if (criteria.TaskStatusId.IsNotNull())
{
query.Where(() => s.Id == criteria.TaskStatusId);
}
if (criteria.TaskParticipantId.IsNotNull())
{
Task subT = null;
User subU = null;
var subquery = QueryOver.Of(() => subT)
.JoinAlias(() => subT.Users, () => subU)
.Where(Res.And(
Res.EqProperty(Pro.Property("t.Id"), Pro.Property("subT.Id")),
Res.Eq(Pro.Property("subU.Id"), criteria.TaskParticipantId)))
.SelectList(l => l
.Select(() => subU.Id));
query.WithSubquery.WhereExists(subquery);
}
if (criteria.Comment.IsNotNull())
{
TaskComment subC = null;
Task subT = null;
var subquery = QueryOver.Of(() => subC)
.JoinAlias(() => subC.Task, () => subT)
.Where(Res.And(
Res.EqProperty(Pro.Property("subT.Id"), Pro.Property("t.Id")),
Res.InsensitiveLike(Pro.Property("subC.Content"), criteria.Comment, MatchMode.Anywhere)))
.SelectList(l => l
.Select(() => subT.Id));
query.WithSubquery.WhereExists(subquery);
}
if (criteria.CommentAuthorId.IsNotNull())
{
TaskComment subC = null;
Task subT = null;
User subU = null;
var subquery = QueryOver.Of(() => subC)
.JoinAlias(() => subC.Task, () => subT)
.JoinAlias(() => subC.User, () => subU)
.Where(Res.And(
Res.EqProperty(Pro.Property("subT.Id"), Pro.Property("t.Id")),
Res.Eq(Pro.Property("subU.Id"), criteria.CommentAuthorId)))
.SelectList(l => l
.Select(() => subT.Id));
query.WithSubquery.WhereExists(subquery);
}
var result = Math.Ceiling((1.0 * query.RowCount()) / pageSize);
return(result == 0 ? 1 : Convert.ToInt32(result));
}
}
internal static IProjection ProcessBitLength(MethodCallExpression methodCallExpression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
return(Projections.SqlFunction("bit_length", NHibernateUtil.String, property));
}
protected override Expression VisitMethodCall(MethodCallExpression expr)
{
if (WhereArgumentsVisitor.SupportsMethod(expr.Method.Name))
{
return(VisitCriterionExpression(expr));
}
//TODO: this needs to be refactored...
//create any collection subcriteria and get the collection access expression
MemberNameVisitor memberVisitor = new MemberNameVisitor(_rootCriteria, true);
memberVisitor.Visit(expr.Arguments[0]);
CollectionAccessExpression collectionExpr = (CollectionAccessExpression)memberVisitor.CurrentExpression;
string propertyName = null;
IProjection projection = null;
PropertyProjection currentProjection;
if (expr.Arguments.Count > 1)
{
propertyName = MemberNameVisitor.GetMemberName(_rootCriteria, expr.Arguments[1]);
}
else if ((currentProjection = _rootCriteria.GetProjection() as PropertyProjection) != null)
{
propertyName = currentProjection.PropertyName;
}
switch (expr.Method.Name)
{
case "Average":
projection = NHProjections.Avg(propertyName);
break;
case "Count":
case "LongCount":
if (expr.Arguments.Count > 1)
{
_rootCriteria.Add(WhereArgumentsVisitor.GetCriterion(_rootCriteria, _session, expr.Arguments[1]));
}
if (collectionExpr != null)
{
//get count on collection element's identifier property
propertyName = memberVisitor.MemberName + "." + collectionExpr.ElementExpression.MetaData.IdentifierPropertyName;
projection = NHProjections.Count(propertyName);
}
else
{
projection = NHProjections.RowCount();
}
break;
case "Max":
projection = NHProjections.Max(propertyName);
break;
case "Min":
projection = NHProjections.Min(propertyName);
break;
case "Sum":
projection = NHProjections.Sum(propertyName);
break;
default:
throw new NotImplementedException("The method '" + expr.Method.Name + "' is not implemented.");
}
_projections.Add(projection);
return(expr);
}
internal static IProjection ProcessCharIndex(MethodCallExpression methodCallExpression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
object theChar = ExpressionProcessor.FindValue(methodCallExpression.Arguments[1]);
object startLocation = ExpressionProcessor.FindValue(methodCallExpression.Arguments[2]);
return(Projections.SqlFunction("locate", NHibernateUtil.String, Projections.Constant(theChar), property, Projections.Constant(startLocation)));
}
internal static IProjection ProcessSubstr(MethodCallExpression methodCallExpression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
object startIndex = ExpressionProcessor.FindValue(methodCallExpression.Arguments[1]);
object length = ExpressionProcessor.FindValue(methodCallExpression.Arguments[2]);
return(Projections.SqlFunction("substring", NHibernateUtil.String, property, Projections.Constant(startIndex), Projections.Constant(length)));
}
internal static IProjection ProcessDay(System.Linq.Expressions.Expression expression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(expression).AsProjection();
return(Projections.SqlFunction("day", NHibernateUtil.Int32, property));
}
internal static IProjection ProcessCoalesce(MethodCallExpression methodCallExpression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
object replaceValueIfIsNull = ExpressionProcessor.FindValue(methodCallExpression.Arguments[1]);
return(Projections.SqlFunction("coalesce", NHibernateUtil.Object, property, Projections.Constant(replaceValueIfIsNull)));
}
public PropertyProjection Group()
{
return(Projections.GroupProperty(PropertyName));
}
internal static IProjection ProcessDoubleAbs(MethodCallExpression methodCallExpression)
{
IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
return(Projections.SqlFunction("abs", NHibernateUtil.Double, property));
}
public AggregateProjection Min()
{
return(Projections.Min(PropertyName));
}
public CountProjection Count()
{
return(Projections.Count(PropertyName));
}
private static IProjection SqlFunction(string name, IType type, System.Linq.Expressions.Expression projection)
{
IProjection property = ExpressionProcessor.FindMemberProjection(projection).AsProjection();
return(Projections.SqlFunction(name, type, property));
}
