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);
            }
        }
Пример #5
0
        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);
        }
Пример #7
0
        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)));
            }
        }
Пример #8
0
 public AggregateProjection Avg()
 {
     return(Projections.Avg(PropertyName));
 }
Пример #9
0
        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
            }
        }
Пример #10
0
        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));
            }
        }
Пример #11
0
        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);
        }
Пример #13
0
        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)));
        }
Пример #14
0
        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)));
        }
Пример #15
0
        internal static IProjection ProcessDay(System.Linq.Expressions.Expression expression)
        {
            IProjection property = ExpressionProcessor.FindMemberProjection(expression).AsProjection();

            return(Projections.SqlFunction("day", NHibernateUtil.Int32, property));
        }
Пример #16
0
        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)));
        }
Пример #17
0
 public PropertyProjection Group()
 {
     return(Projections.GroupProperty(PropertyName));
 }
Пример #18
0
        internal static IProjection ProcessDoubleAbs(MethodCallExpression methodCallExpression)
        {
            IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();

            return(Projections.SqlFunction("abs", NHibernateUtil.Double, property));
        }
Пример #19
0
 public AggregateProjection Min()
 {
     return(Projections.Min(PropertyName));
 }
Пример #20
0
 public CountProjection Count()
 {
     return(Projections.Count(PropertyName));
 }
Пример #21
0
        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));
        }