Example #1
0
        public override QueryOver <NodeVersion> VisitFieldPredicate(FieldPredicateExpression node)
        {
            var fieldName      = node.SelectorExpression.FieldName;
            var valueKey       = node.SelectorExpression.ValueKey;
            var fieldValue     = node.ValueExpression.Value;
            var fieldValueType = fieldValue != null?fieldValue.GetType() : typeof(string);

            switch (fieldName.ToLowerInvariant())
            {
            case "id":
                Guid idValue = GetIdValue(node);

                switch (node.ValueExpression.ClauseType)
                {
                case ValuePredicateType.Equal:
                    return(QueryOver.Of <NodeVersion>().Where(x => x.Node.Id == idValue).Select(x => x.Id));

                case ValuePredicateType.NotEqual:
                    return(QueryOver.Of <NodeVersion>().Where(x => x.Node.Id != idValue).Select(x => x.Id));;

                default:
                    throw new InvalidOperationException(
                              "Cannot query an item by id by any other operator than == or !=");
                }
            }

            // First look up the types of the main field
            AttributeDefinition defAlias  = null;
            AttributeType       typeAlias = null;
            var attributeType             = _activeSession.QueryOver <AttributeDefinition>(() => defAlias)
                                            .JoinAlias(() => defAlias.AttributeType, () => typeAlias)
                                            .Where(() => defAlias.Alias == fieldName)
                                            .Select(x => typeAlias.PersistenceTypeProvider)
                                            .List <string>();

            var typesAlreadyEstablished = new List <string>();
            var typesToQuery            = new List <DataSerializationTypes>();

            foreach (var type in attributeType)
            {
                var typeName = type;
                if (typesAlreadyEstablished.Contains(typeName))
                {
                    continue;
                }
                try
                {
                    typesAlreadyEstablished.Add(typeName);
                    var persisterType = Type.GetType(typeName, false);
                    if (persisterType != null)
                    {
                        var persisterInstance = Activator.CreateInstance(persisterType) as IAttributeSerializationDefinition;
                        if (persisterInstance != null)
                        {
                            typesToQuery.Add(persisterInstance.DataSerializationType);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }

            // U5-789
            // Workaround pending a better check of what data is actually saved
            // An issue arose because previous data had been saved in long-string,
            // but the datatype changed to be just string, therefore only string was
            // being queried despite all the data residing still in long-string
            if (typesToQuery.Contains(DataSerializationTypes.String) && !typesToQuery.Contains(DataSerializationTypes.LongString))
            {
                typesToQuery.Add(DataSerializationTypes.LongString);
            }

            NodeVersion              aliasNodeVersion         = null;
            Attribute                aliasAttribute           = null;
            AttributeDefinition      aliasAttributeDefinition = null;
            AttributeStringValue     aliasStringValue         = null;
            AttributeLongStringValue aliasLongStringValue     = null;
            AttributeIntegerValue    aliasIntegerValue        = null;
            AttributeDecimalValue    aliasDecimalValue        = null;
            NodeRelation             aliasNodeRelation        = null;
            AttributeDateValue       aliasDateValue           = null;

            QueryOver <NodeVersion, AttributeDefinition> queryExtender = QueryOver.Of <NodeVersion>(() => aliasNodeVersion)
                                                                         .JoinQueryOver <Attribute>(() => aliasNodeVersion.Attributes, () => aliasAttribute)
                                                                         .JoinQueryOver <AttributeDefinition>(() => aliasAttribute.AttributeDefinition, () => aliasAttributeDefinition);

            int numberOfMatchesEvaluated         = 0;
            AbstractCriterion restrictionBuilder = null;

            foreach (var dataSerializationTypese in typesToQuery.Distinct())
            {
                AbstractCriterion           restriction        = null;
                Expression <Func <object> > propertyExpression = null;
                Expression <Func <object> > subkeyExpression   = null;
                List <ValuePredicateType>   validClauseTypes   = null;
                var useLikeMatchForStrings = false;
                switch (dataSerializationTypese)
                {
                case DataSerializationTypes.SmallInt:
                case DataSerializationTypes.LargeInt:
                case DataSerializationTypes.Boolean:
                    queryExtender      = queryExtender.Left.JoinAlias(() => aliasAttribute.AttributeIntegerValues, () => aliasIntegerValue);
                    propertyExpression = () => aliasIntegerValue.Value;
                    subkeyExpression   = () => aliasIntegerValue.ValueKey;
                    validClauseTypes   = new List <ValuePredicateType>()
                    {
                        ValuePredicateType.Equal,
                        ValuePredicateType.GreaterThan,
                        ValuePredicateType.GreaterThanOrEqual,
                        ValuePredicateType.LessThan,
                        ValuePredicateType.LessThanOrEqual,
                        ValuePredicateType.NotEqual
                    };
                    break;

                case DataSerializationTypes.Decimal:
                    queryExtender      = queryExtender.Left.JoinAlias(() => aliasAttribute.AttributeDecimalValues, () => aliasDecimalValue);
                    propertyExpression = () => aliasDecimalValue.Value;
                    subkeyExpression   = () => aliasDecimalValue.ValueKey;
                    validClauseTypes   = new List <ValuePredicateType>()
                    {
                        ValuePredicateType.Equal,
                        ValuePredicateType.GreaterThan,
                        ValuePredicateType.GreaterThanOrEqual,
                        ValuePredicateType.LessThan,
                        ValuePredicateType.LessThanOrEqual,
                        ValuePredicateType.NotEqual
                    };
                    break;

                case DataSerializationTypes.String:
                    queryExtender      = queryExtender.Left.JoinAlias(() => aliasAttribute.AttributeStringValues, () => aliasStringValue);
                    propertyExpression = () => aliasStringValue.Value;
                    subkeyExpression   = () => aliasStringValue.ValueKey;
                    validClauseTypes   = new List <ValuePredicateType>()
                    {
                        ValuePredicateType.Equal,
                        ValuePredicateType.NotEqual,
                        ValuePredicateType.Contains,
                        ValuePredicateType.StartsWith,
                        ValuePredicateType.EndsWith,
                        ValuePredicateType.MatchesWildcard
                    };
                    break;

                case DataSerializationTypes.LongString:
                    queryExtender          = queryExtender.Left.JoinAlias(() => aliasAttribute.AttributeLongStringValues, () => aliasLongStringValue);
                    propertyExpression     = () => aliasLongStringValue.Value;
                    subkeyExpression       = () => aliasLongStringValue.ValueKey;
                    useLikeMatchForStrings = true;
                    validClauseTypes       = new List <ValuePredicateType>()
                    {
                        ValuePredicateType.Equal,
                        ValuePredicateType.NotEqual,
                        ValuePredicateType.Contains,
                        ValuePredicateType.StartsWith,
                        ValuePredicateType.EndsWith,
                        ValuePredicateType.MatchesWildcard
                    };
                    break;

                case DataSerializationTypes.Date:
                    queryExtender      = queryExtender.Left.JoinAlias(() => aliasAttribute.AttributeDateValues, () => aliasDateValue);
                    propertyExpression = () => aliasDateValue.Value;
                    subkeyExpression   = () => aliasDateValue.ValueKey;
                    validClauseTypes   = new List <ValuePredicateType>()
                    {
                        ValuePredicateType.Equal,
                        ValuePredicateType.GreaterThan,
                        ValuePredicateType.GreaterThanOrEqual,
                        ValuePredicateType.LessThan,
                        ValuePredicateType.LessThanOrEqual,
                        ValuePredicateType.NotEqual,
                        ValuePredicateType.Empty
                    };
                    break;
                }

                if (!validClauseTypes.Contains(node.ValueExpression.ClauseType))
                {
                    throw new InvalidOperationException("A field of type {0} cannot be queried with operator {1}".InvariantFormat(dataSerializationTypese.ToString(), node.ValueExpression.ClauseType.ToString()));
                }

                switch (node.ValueExpression.ClauseType)
                {
                case ValuePredicateType.Equal:
                    restriction = GetRestrictionEq(fieldValue, useLikeMatchForStrings, propertyExpression, subkeyExpression, valueKey);
                    break;

                case ValuePredicateType.NotEqual:
                    restriction = !GetRestrictionEq(fieldValue, useLikeMatchForStrings, propertyExpression);
                    break;

                case ValuePredicateType.LessThan:
                    restriction = GetRestrictionLt(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.LessThanOrEqual:
                    restriction = GetRestrictionLtEq(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.GreaterThan:
                    restriction = GetRestrictionGt(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.GreaterThanOrEqual:
                    restriction = GetRestrictionGtEq(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.Contains:
                    restriction = GetRestrictionContains(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.StartsWith:
                    restriction = GetRestrictionStarts(fieldValue, propertyExpression);
                    break;

                case ValuePredicateType.EndsWith:
                    restriction = GetRestrictionEnds(fieldValue, propertyExpression);
                    break;
                }

                if (restriction != null)
                {
                    if (numberOfMatchesEvaluated == 0)
                    {
                        restrictionBuilder = restriction;
                        numberOfMatchesEvaluated++;
                    }
                    else
                    {
                        restrictionBuilder = Restrictions.Or(restriction, restrictionBuilder);
                    }
                }
            }

            if (restrictionBuilder != null)
            {
                queryExtender = queryExtender.Where(restrictionBuilder);
            }

            queryExtender = queryExtender
                            .And(x => aliasAttributeDefinition.Alias == fieldName);

            return(queryExtender.Select(x => x.Id));
        }
Example #2
0
        private IList <ComplaintGuiltyNode> GetGuilties(ComplaintFilterViewModel filter)
        {
            Complaint                     complaintAlias              = null;
            Subdivision                   subdivisionAlias            = null;
            Subdivision                   subdivisionForEmployeeAlias = null;
            Employee                      employeeAlias             = null;
            ComplaintGuiltyItem           guiltyItemAlias           = null;
            ComplaintResultOfCounterparty resultOfCounterpartyAlias = null;
            ComplaintResultOfEmployees    resultOfEmployeesAlias    = null;
            QueryNode                     queryNodeAlias            = null;
            ComplaintDiscussion           discussionAlias           = null;

            var query = InfoProvider.UoW.Session.QueryOver(() => guiltyItemAlias)
                        .Left.JoinAlias(() => guiltyItemAlias.Complaint, () => complaintAlias)
                        .Left.JoinAlias(() => complaintAlias.ComplaintResultOfCounterparty, () => resultOfCounterpartyAlias)
                        .Left.JoinAlias(() => complaintAlias.ComplaintResultOfEmployees, () => resultOfEmployeesAlias)
                        .Left.JoinAlias(() => guiltyItemAlias.Subdivision, () => subdivisionAlias)
                        .Left.JoinAlias(() => guiltyItemAlias.Employee, () => employeeAlias)
                        .Left.JoinAlias(() => employeeAlias.Subdivision, () => subdivisionForEmployeeAlias);

            filter.EndDate = filter.EndDate.Date.AddHours(23).AddMinutes(59);
            if (filter.StartDate.HasValue)
            {
                filter.StartDate = filter.StartDate.Value.Date;
            }

            QueryOver <ComplaintDiscussion, ComplaintDiscussion> dicussionQuery = null;

            if (filter.Subdivision != null)
            {
                dicussionQuery = QueryOver.Of(() => discussionAlias)
                                 .Select(Projections.Property <ComplaintDiscussion>(p => p.Id))
                                 .Where(() => discussionAlias.Subdivision.Id == filter.Subdivision.Id)
                                 .And(() => discussionAlias.Complaint.Id == complaintAlias.Id);
            }

            if (filter.StartDate.HasValue)
            {
                switch (filter.FilterDateType)
                {
                case DateFilterType.PlannedCompletionDate:
                    if (dicussionQuery == null)
                    {
                        query = query.Where(() => complaintAlias.PlannedCompletionDate <= filter.EndDate)
                                .And(() => filter.StartDate == null || complaintAlias.PlannedCompletionDate >= filter.StartDate.Value);
                    }
                    else
                    {
                        dicussionQuery = dicussionQuery
                                         .And(() => filter.StartDate == null || discussionAlias.PlannedCompletionDate >= filter.StartDate.Value)
                                         .And(() => discussionAlias.PlannedCompletionDate <= filter.EndDate);
                    }
                    break;

                case DateFilterType.ActualCompletionDate:
                    query = query.Where(() => complaintAlias.ActualCompletionDate <= filter.EndDate)
                            .And(() => filter.StartDate == null || complaintAlias.ActualCompletionDate >= filter.StartDate.Value);
                    break;

                case DateFilterType.CreationDate:
                    query = query.Where(() => complaintAlias.CreationDate <= filter.EndDate)
                            .And(() => filter.StartDate == null || complaintAlias.CreationDate >= filter.StartDate.Value);
                    break;

                default:
                    throw new ArgumentOutOfRangeException();
                }
            }

            if (dicussionQuery != null)
            {
                query.WithSubquery.WhereExists(dicussionQuery);
            }
            if (filter.ComplaintType != null)
            {
                query = query.Where(() => complaintAlias.ComplaintType == filter.ComplaintType);
            }
            if (filter.ComplaintStatus != null)
            {
                query = query.Where(() => complaintAlias.Status == filter.ComplaintStatus);
            }
            if (filter.Employee != null)
            {
                query = query.Where(() => complaintAlias.CreatedBy.Id == filter.Employee.Id);
            }

            if (filter.GuiltyItemVM?.Entity?.GuiltyType != null)
            {
                var subquery = QueryOver.Of <ComplaintGuiltyItem>()
                               .Where(g => g.GuiltyType == filter.GuiltyItemVM.Entity.GuiltyType.Value);
                switch (filter.GuiltyItemVM.Entity.GuiltyType)
                {
                case ComplaintGuiltyTypes.None:
                case ComplaintGuiltyTypes.Client:
                case ComplaintGuiltyTypes.Depreciation:
                case ComplaintGuiltyTypes.Supplier:
                    break;

                case ComplaintGuiltyTypes.Employee:
                    if (filter.GuiltyItemVM.Entity.Employee != null)
                    {
                        subquery.Where(g => g.Employee.Id == filter.GuiltyItemVM.Entity.Employee.Id);
                    }
                    break;

                case ComplaintGuiltyTypes.Subdivision:
                    if (filter.GuiltyItemVM.Entity.Subdivision != null)
                    {
                        subquery.Where(g => g.Subdivision.Id == filter.GuiltyItemVM.Entity.Subdivision.Id);
                    }
                    break;

                default:
                    break;
                }
                query.WithSubquery.WhereProperty(() => complaintAlias.Id).In(subquery.Select(x => x.Complaint));
            }

            if (filter.ComplaintKind != null)
            {
                query.Where(() => complaintAlias.ComplaintKind.Id == filter.ComplaintKind.Id);
            }

            var result = query.SelectList(list => list
                                          .SelectGroup(c => c.Complaint.Id)
                                          .Select(() => complaintAlias.Status).WithAlias(() => queryNodeAlias.Status)
                                          .Select(() => resultOfCounterpartyAlias.Name).WithAlias(() => queryNodeAlias.ResultOfCounterpartyText)
                                          .Select(() => resultOfEmployeesAlias.Name).WithAlias(() => queryNodeAlias.ResultOfEmployeesText)
                                          .Select(Projections.SqlFunction(
                                                      new SQLFunctionTemplate(
                                                          NHibernateUtil.String,
                                                          "GROUP_CONCAT(" +
                                                          "CASE ?1 " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.Employee)}' THEN IFNULL(CONCAT('Отд: ', ?2), 'Отдел ВВ') " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.Subdivision)}' THEN IFNULL(CONCAT('Отд: ', ?3), 'Отдел ВВ') " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.Client)}' THEN 'Клиент' " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.Depreciation)}' THEN 'Износ' " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.Supplier)}' THEN 'Поставщик' " +
                                                          $"WHEN '{nameof(ComplaintGuiltyTypes.None)}' THEN 'Нет (не рекламация)' " +
                                                          "ELSE ?1 " +
                                                          "END " +
                                                          "ORDER BY ?1 ASC SEPARATOR '\n')"),
                                                      NHibernateUtil.String,
                                                      Projections.Property(() => guiltyItemAlias.GuiltyType),
                                                      Projections.Property(() => subdivisionForEmployeeAlias.Name),
                                                      Projections.Property(() => subdivisionAlias.Name)))
                                          .WithAlias(() => queryNodeAlias.GuiltyName))
                         .TransformUsing(Transformers.AliasToBean <QueryNode>())
                         .List <QueryNode>();

            var groupedResult = result.GroupBy(p => p.GuiltyName, (guiltyName, guiltiesGroup) => new ComplaintGuiltyNode {
                GuiltyName = guiltyName,
                Count      = guiltiesGroup.Count(),
                Guilties   = guiltiesGroup.ToList()
            }).ToList();

            //Удаление дублирующихся названий отделов
            for (int i = 0; i < groupedResult.Count; i++)
            {
                if (groupedResult[i].GuiltyName.Contains("\n"))
                {
                    groupedResult[i].GuiltyName = string.Join("\n", groupedResult[i].GuiltyName.Split('\n').Distinct());
                }
            }

            foreach (var item in groupedResult)
            {
                item.CreateComplaintResultNodes();
            }
            return(groupedResult);
        }
        private IQueryOver <Complaint> GetComplaintQuery(IUnitOfWork uow)
        {
            ComplaintJournalNode resultAlias = null;

            Complaint           complaintAlias           = null;
            Employee            authorAlias              = null;
            Counterparty        counterpartyAlias        = null;
            DeliveryPoint       deliveryPointAlias       = null;
            ComplaintGuiltyItem complaintGuiltyItemAlias = null;
            Employee            guiltyEmployeeAlias      = null;
            Subdivision         guiltySubdivisionAlias   = null;
            Fine  fineAlias  = null;
            Order orderAlias = null;
            ComplaintDiscussion discussionAlias    = null;
            Subdivision         subdivisionAlias   = null;
            ComplaintKind       complaintKindAlias = null;
            Subdivision         superspecialAlias  = null;

            var authorProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GET_PERSON_NAME_WITH_INITIALS(?1, ?2, ?3)"),
                NHibernateUtil.String,
                Projections.Property(() => authorAlias.LastName),
                Projections.Property(() => authorAlias.Name),
                Projections.Property(() => authorAlias.Patronymic)
                );

            var workInSubdivisionsSubQuery = QueryOver.Of <Subdivision>(() => subdivisionAlias)
                                             .Where(() => subdivisionAlias.Id == discussionAlias.Subdivision.Id)
                                             .Where(() => discussionAlias.Status == ComplaintStatuses.InProcess)
                                             .Select(Projections.Conditional(
                                                         Restrictions.IsNotNull(Projections.Property(() => subdivisionAlias.ShortName)),
                                                         Projections.Property(() => subdivisionAlias.ShortName),
                                                         Projections.Constant("?")
                                                         )
                                                     );

            var subdivisionsSubqueryProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
                NHibernateUtil.String,
                Projections.SubQuery(workInSubdivisionsSubQuery),
                Projections.Constant(", "));

            string okkSubdivision = uow.GetById <Subdivision>(subdivisionService.GetOkkId()).ShortName ?? "?";

            var workInSubdivisionsProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS(',', ?1, IF(?2 = 'Checking',?3, ''))"),
                NHibernateUtil.String,
                subdivisionsSubqueryProjection,
                Projections.Property(() => complaintAlias.Status),
                Projections.Constant(okkSubdivision)
                );

            var plannedCompletionDateProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT DATE_FORMAT(?1, \"%d.%m.%Y\") SEPARATOR ?2)"),
                NHibernateUtil.String,
                Projections.Property(() => discussionAlias.PlannedCompletionDate),
                Projections.Constant("\n"));

            var lastPlannedCompletionDateProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.DateTime, "MAX(DISTINCT ?1)"),
                NHibernateUtil.DateTime,
                Projections.Property(() => discussionAlias.PlannedCompletionDate));

            var counterpartyWithAddressProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS('\n', ?1, COMPILE_ADDRESS(?2))"),
                NHibernateUtil.String,
                Projections.Property(() => counterpartyAlias.Name),
                Projections.Property(() => deliveryPointAlias.Id));

            var guiltyEmployeeProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GET_PERSON_NAME_WITH_INITIALS(?1, ?2, ?3)"),
                NHibernateUtil.String,
                Projections.Property(() => guiltyEmployeeAlias.LastName),
                Projections.Property(() => guiltyEmployeeAlias.Name),
                Projections.Property(() => guiltyEmployeeAlias.Patronymic)
                );

            var guiltiesProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT " +
                                        "CASE ?1 " +
                                        $"WHEN '{nameof(ComplaintGuiltyTypes.Client)}' THEN 'Клиент' " +
                                        $"WHEN '{nameof(ComplaintGuiltyTypes.None)}' THEN 'Нет' " +
                                        $"WHEN '{nameof(ComplaintGuiltyTypes.Employee)}' THEN CONCAT('(',?5,')', ?2)" +
                                        $"WHEN '{nameof(ComplaintGuiltyTypes.Subdivision)}' THEN ?3 " +
                                        "ELSE '' " +
                                        "END" +
                                        " SEPARATOR ?4)"),
                NHibernateUtil.String,
                Projections.Property(() => complaintGuiltyItemAlias.GuiltyType),
                guiltyEmployeeProjection,
                Projections.Property(() => guiltySubdivisionAlias.ShortName),
                Projections.Constant("\n"),
                Projections.Property(() => superspecialAlias.ShortName));

            var finesProjection = Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT CONCAT(ROUND(?1, 2), ' р.')  SEPARATOR ?2)"),
                NHibernateUtil.String,
                Projections.Property(() => fineAlias.TotalMoney),
                Projections.Constant("\n"));

            var query = uow.Session.QueryOver(() => complaintAlias)
                        .Left.JoinAlias(() => complaintAlias.CreatedBy, () => authorAlias)
                        .Left.JoinAlias(() => complaintAlias.Counterparty, () => counterpartyAlias)
                        .Left.JoinAlias(() => complaintAlias.Order, () => orderAlias)
                        .Left.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
                        .Left.JoinAlias(() => complaintAlias.Guilties, () => complaintGuiltyItemAlias)
                        .Left.JoinAlias(() => complaintAlias.ComplaintKind, () => complaintKindAlias)
                        .Left.JoinAlias(() => complaintAlias.Fines, () => fineAlias)
                        .Left.JoinAlias(() => complaintAlias.ComplaintDiscussions, () => discussionAlias)
                        .Left.JoinAlias(() => discussionAlias.Subdivision, () => subdivisionAlias)
                        .Left.JoinAlias(() => complaintGuiltyItemAlias.Employee, () => guiltyEmployeeAlias)
                        .Left.JoinAlias(() => guiltyEmployeeAlias.Subdivision, () => superspecialAlias)
                        .Left.JoinAlias(() => complaintGuiltyItemAlias.Subdivision, () => guiltySubdivisionAlias);

            #region Filter

            if (FilterViewModel != null)
            {
                if (FilterViewModel.IsForRetail != null)
                {
                    query.Where(() => counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail);
                }

                FilterViewModel.EndDate = FilterViewModel.EndDate.Date.AddHours(23).AddMinutes(59);
                if (FilterViewModel.StartDate.HasValue)
                {
                    FilterViewModel.StartDate = FilterViewModel.StartDate.Value.Date;
                }

                QueryOver <ComplaintDiscussion, ComplaintDiscussion> dicussionQuery = null;

                if (FilterViewModel.Subdivision != null)
                {
                    dicussionQuery = QueryOver.Of(() => discussionAlias)
                                     .Select(Projections.Property <ComplaintDiscussion>(p => p.Id))
                                     .Where(() => discussionAlias.Subdivision.Id == FilterViewModel.Subdivision.Id)
                                     .And(() => discussionAlias.Complaint.Id == complaintAlias.Id);
                }

                if (FilterViewModel.StartDate.HasValue)
                {
                    switch (FilterViewModel.FilterDateType)
                    {
                    case DateFilterType.PlannedCompletionDate:
                        if (dicussionQuery == null)
                        {
                            query = query.Where(() => complaintAlias.PlannedCompletionDate <= FilterViewModel.EndDate)
                                    .And(() => FilterViewModel.StartDate == null || complaintAlias.PlannedCompletionDate >= FilterViewModel.StartDate.Value);
                        }
                        else
                        {
                            dicussionQuery = dicussionQuery
                                             .And(() => FilterViewModel.StartDate == null || discussionAlias.PlannedCompletionDate >= FilterViewModel.StartDate.Value)
                                             .And(() => discussionAlias.PlannedCompletionDate <= FilterViewModel.EndDate);
                        }
                        break;

                    case DateFilterType.ActualCompletionDate:
                        query = query.Where(() => complaintAlias.ActualCompletionDate <= FilterViewModel.EndDate)
                                .And(() => FilterViewModel.StartDate == null || complaintAlias.ActualCompletionDate >= FilterViewModel.StartDate.Value);
                        break;

                    case DateFilterType.CreationDate:
                        query = query.Where(() => complaintAlias.CreationDate <= FilterViewModel.EndDate)
                                .And(() => FilterViewModel.StartDate == null || complaintAlias.CreationDate >= FilterViewModel.StartDate.Value);
                        break;

                    default:
                        throw new ArgumentOutOfRangeException();
                    }
                }

                if (dicussionQuery != null)
                {
                    query.WithSubquery.WhereExists(dicussionQuery);
                }
                if (FilterViewModel.ComplaintType != null)
                {
                    query = query.Where(() => complaintAlias.ComplaintType == FilterViewModel.ComplaintType);
                }
                if (FilterViewModel.ComplaintStatus != null)
                {
                    query = query.Where(() => complaintAlias.Status == FilterViewModel.ComplaintStatus);
                }
                if (FilterViewModel.Employee != null)
                {
                    query = query.Where(() => complaintAlias.CreatedBy.Id == FilterViewModel.Employee.Id);
                }

                if (FilterViewModel.CurrentUserSubdivision != null &&
                    FilterViewModel.ComplaintDiscussionStatus != null)
                {
                    query = query.Where(() => discussionAlias.Subdivision.Id == FilterViewModel.CurrentUserSubdivision.Id)
                            .And(() => discussionAlias.Status == FilterViewModel.ComplaintDiscussionStatus);
                }

                if (FilterViewModel.GuiltyItemVM?.Entity?.GuiltyType != null)
                {
                    var subquery = QueryOver.Of <ComplaintGuiltyItem>()
                                   .Where(g => g.GuiltyType == FilterViewModel.GuiltyItemVM.Entity.GuiltyType.Value);
                    switch (FilterViewModel.GuiltyItemVM.Entity.GuiltyType)
                    {
                    case ComplaintGuiltyTypes.None:
                    case ComplaintGuiltyTypes.Client:
                        break;

                    case ComplaintGuiltyTypes.Employee:
                        if (FilterViewModel.GuiltyItemVM.Entity.Employee != null)
                        {
                            subquery.Where(g => g.Employee.Id == FilterViewModel.GuiltyItemVM.Entity.Employee.Id);
                        }
                        break;

                    case ComplaintGuiltyTypes.Subdivision:
                        if (FilterViewModel.GuiltyItemVM.Entity.Subdivision != null)
                        {
                            subquery.Where(g => g.Subdivision.Id == FilterViewModel.GuiltyItemVM.Entity.Subdivision.Id);
                        }
                        break;

                    default:
                        break;
                    }
                    query.WithSubquery.WhereProperty(x => x.Id).In(subquery.Select(x => x.Complaint));
                }

                if (FilterViewModel.ComplaintKind != null)
                {
                    query.Where(() => complaintAlias.ComplaintKind.Id == FilterViewModel.ComplaintKind.Id);
                }
            }

            #endregion Filter

            query.Where(
                GetSearchCriterion(
                    () => complaintAlias.Id,
                    () => complaintAlias.ComplaintText,
                    () => complaintAlias.ResultText,
                    () => counterpartyAlias.Name,
                    () => deliveryPointAlias.CompiledAddress
                    )
                );

            query.SelectList(list => list
                             .SelectGroup(() => complaintAlias.Id).WithAlias(() => resultAlias.Id)
                             .Select(() => complaintAlias.CreationDate).WithAlias(() => resultAlias.Date)
                             .Select(() => complaintAlias.ComplaintType).WithAlias(() => resultAlias.Type)
                             .Select(() => complaintAlias.Status).WithAlias(() => resultAlias.Status)
                             .Select(workInSubdivisionsProjection).WithAlias(() => resultAlias.WorkInSubdivision)
                             .Select(plannedCompletionDateProjection).WithAlias(() => resultAlias.PlannedCompletionDate)
                             .Select(lastPlannedCompletionDateProjection).WithAlias(() => resultAlias.LastPlannedCompletionDate)
                             .Select(counterpartyWithAddressProjection).WithAlias(() => resultAlias.ClientNameWithAddress)
                             .Select(guiltiesProjection).WithAlias(() => resultAlias.Guilties)
                             .Select(authorProjection).WithAlias(() => resultAlias.Author)
                             .Select(finesProjection).WithAlias(() => resultAlias.Fines)
                             .Select(() => complaintAlias.ComplaintText).WithAlias(() => resultAlias.ComplaintText)
                             .Select(() => complaintKindAlias.Name).WithAlias(() => resultAlias.ComplaintKindString)
                             .Select(() => complaintKindAlias.IsArchive).WithAlias(() => resultAlias.ComplaintKindIsArchive)
                             .Select(() => complaintAlias.ResultText).WithAlias(() => resultAlias.ResultText)
                             .Select(() => complaintAlias.ActualCompletionDate).WithAlias(() => resultAlias.ActualCompletionDate)
                             );

            var result = query.TransformUsing(Transformers.AliasToBean <ComplaintJournalNode>())
                         .OrderBy(n => n.Id)
                         .Desc().List <ComplaintJournalNode>()
            ;

            return(query);
        }