public AmazonSESComplaintNotification(string notification) { AmazonSESNotification amazonSESNotification = null; if (TryParse(notification, out amazonSESNotification)) { var amazonSESComplaintNotification = amazonSESNotification as AmazonSESComplaintNotification; Mail = amazonSESComplaintNotification.Mail; NotificationType = amazonSESComplaintNotification.NotificationType; Complaint = amazonSESComplaintNotification.Complaint; } }
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; ComplaintObject complaintObjectAlias = 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 " + $"WHEN '{nameof(ComplaintGuiltyTypes.Supplier)}' THEN 'Поставщик' " + "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(() => complaintAlias.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) .Left.JoinAlias(() => complaintKindAlias.ComplaintObject, () => complaintObjectAlias); #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.Counterparty != null) { query = query.Where(() => complaintAlias.Counterparty.Id == FilterViewModel.Counterparty.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: case ComplaintGuiltyTypes.Supplier: 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); } if (FilterViewModel.ComplaintObject != null) { query.Where(() => complaintObjectAlias.Id == FilterViewModel.ComplaintObject.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) .Select(() => complaintObjectAlias.Name).WithAlias(() => resultAlias.ComplaintObjectString) ); var result = query.TransformUsing(Transformers.AliasToBean <ComplaintJournalNode>()) .OrderBy(n => n.Id) .Desc().List <ComplaintJournalNode>() ; return(query); }