private AndQueryCondition BuildShortConditions(NotificationQuery query) { var res = new AndQueryCondition { { Notification.PERSON_REF_FIELD, query.PersonId }, { Notification.ROLE_REF_FIELD, query.RoleId } }; if (query.Id.HasValue) { res.Add(Notification.ID_FIELD, query.Id); } if (query.Shown.HasValue) { res.Add(Notification.SHOWN_FIELD, query.Shown); } if (query.Type.HasValue) { res.Add(Notification.TYPE_FIELD, query.Type); } if (query.FromDate.HasValue) { res.Add(Notification.CREATED_FIELD, "fromDate", query.FromDate, ConditionRelation.GreaterEqual); } if (query.ToDate.HasValue) { res.Add(Notification.CREATED_FIELD, "toDate", query.ToDate, ConditionRelation.LessEqual); } return(res); }
public IList <NotificationDetails> GetNotificationsDetails(NotificationQuery query, bool includeMessages = true) { var q = BuildGetNotificationDetailsDbQuery(query, includeMessages); using (var reader = ExecuteReaderParametrized(q.Sql.ToString(), q.Parameters)) { return(ReadListNotifcationDetails(reader)); } }
public PaginatedList <NotificationDetails> GetPaginatedNotificationsDetails(NotificationQuery query, bool includeMessages = true) { var innerQuery = BuildGetNotificationDetailsDbQuery(query, includeMessages); var orderBy = new Dictionary <string, Orm.OrderType> { { "Notification_" + Notification.CREATED_FIELD, Orm.OrderType.Desc }, { "Notification_" + Notification.ID_FIELD, Orm.OrderType.Desc } }; var q = Orm.PaginationSelect(innerQuery, orderBy, query.Start, query.Count); return(ReadPaginatedResult(q, query.Start, query.Count, ReadListNotifcationDetails)); }
private DbQuery BuildGetNotificationDetailsDbQuery(NotificationQuery query, bool includeMessages) { var conds = BuildShortConditions(query); if (!includeMessages) { conds.Add(Notification.TYPE_FIELD, NotificationType.Message, ConditionRelation.NotEqual); } var tables = new List <Type> { typeof(Notification), typeof(Announcement), typeof(MarkingPeriod) }; //TODO: think how to rewrtite this ... move this to stored procedure var sql = $@"select distinct {Orm.ComplexResultSetQuery(tables)}, PrivateMessage.*, PrivateMessageRecipient.[{PrivateMessageRecipient.READ_FIELD}] as [{PrivateMessageRecipient.READ_FIELD}], PrivateMessageRecipient.[{PrivateMessageRecipient.DELETED_BY_RECIPIENT_FIELD}] as [{PrivateMessageRecipient.DELETED_BY_RECIPIENT_FIELD}], {"MessageSender"}.{Person.ID_FIELD} as {"Sender"}{Person.ID_FIELD}, {"MessageSender"}.{Person.FIRST_NAME_FIELD} as {"Sender"}{Person.FIRST_NAME_FIELD}, {"MessageSender"}.{Person.LAST_NAME_FIELD} as {"Sender"}{Person.LAST_NAME_FIELD}, {"MessageSender"}.{Person.SALUTATION_FIELD} as {"Sender"}{Person.SALUTATION_FIELD}, {"MessageSender"}.{Person.ROLE_REF_FIELD} as {"Sender"}{Person.ROLE_REF_FIELD}, {"MessageSender"}.{Person.GENDER_FIELD} as {"Sender"}{Person.GENDER_FIELD}, toPerson.Id as ToPerson_Id, toPerson.FirstName as ToPerson_FirstName, toPerson.LastName as ToPerson_LastName, toPerson.Gender as ToPerson_Gender, toPerson.Salutation as ToPerson_Salutation, toPerson.RoleRef as ToPerson_RoleRef, QuestionPerson.FirstName as QuestionPerson_FirstName, QuestionPerson.LastName as QuestionPerson_LastName, QuestionPerson.Gender as QuestionPerson_Gender, QuestionPerson.Salutation as QuestionPerson_Salutation, QuestionPerson.RoleRef as QuestionPerson_RoleRef, QuestionPerson.Id as QuestionPerson_Id from [Notification] left join Announcement on Announcement.Id = [Notification].AnnouncementRef left join MarkingPeriod on MarkingPeriod.Id = [Notification].MarkingPeriodRef left join PrivateMessage on PrivateMessage.Id = [Notification].PrivateMessageRef left join PrivateMessageRecipient on PrivateMessageRecipient.PrivateMessageRef = PrivateMessage.Id left join {Person.VW_PERSON} MessageSender on MessageSender.Id = PrivateMessage.{PrivateMessage.FROM_PERSON_REF_FIELD} join vwPerson toPerson on toPerson.Id = [Notification].PersonRef left join vwPerson QuestionPerson on QuestionPerson.Id = [Notification].QuestionPersonRef"; var res = new DbQuery(sql, new Dictionary <string, object>()); conds.BuildSqlWhere(res, tables[0].Name); if (query.SchoolId.HasValue) { res.Sql.AppendFormat(" and (toPerson.[{0}] =@{0} and (QuestionPerson.[{0}] is null or QuestionPerson.[{0}] =@{0}))" , SchoolPerson.SCHOOL_REF_FIELD); res.Parameters.Add(SchoolPerson.SCHOOL_REF_FIELD, query.SchoolId); } //res.Sql.AppendFormat(" and (PrivateMessage_Id is null or (PrivateMessage_SenderSchoolRef = @{0} and PrivateMessage_RecipientSchoolRef = @{0}))", SchoolPerson.SCHOOL_REF_FIELD); return(res); }
public IList <Notification> GetNotifications(NotificationQuery query) { var conds = BuildShortConditions(query); return(SelectMany <Notification>(conds)); }