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));
}