private void AddGeneralConditions(Select select)
{
base.AddConditions(select);
var integerDataValueType = new IntegerDataValueType(new DataValueTypeManager())
{
Name = "Integer"
};
Select activeSysAdminUnitSelect = GetActiveSysAdminUnitSelect();
select
.And("SysAdminUnit", "Active").IsEqual(Column.Parameter(true))
.And("SysAdminUnit", "LoggedIn").IsEqual(Column.Parameter(true))
.And("Reminding", "NotificationTypeId").IsEqual(Column.Parameter(RemindingConsts.NotificationTypeAnniversaryId))
.And().Exists(activeSysAdminUnitSelect)
.Having(Func.Max(Func.Cast("IsRead", integerDataValueType))).IsEqual(Column.Parameter(0));
}
private void AddGeneralColumns(Select select)
{
var integerDataValueType = new IntegerDataValueType(new DataValueTypeManager())
{
Name = "Integer"
};
select
.Column("Reminding", "SubjectId").As("Id")
.Column(Func.Max("Reminding", "RemindTime")).As("RemindTime")
.Column("Reminding", "SubjectId").As("EntityId")
.Column("SysSchema", "Name").As("EntitySchemaName")
.Column("NotificationsSettings", "SysImageId").As("ImageId")
.Column("SysAdminUnit", "Id").As("SysAdminUnitId")
.Column(Func.Max(Func.Cast("IsRead", integerDataValueType))).As("IsRead");
}
public override Select GetEntitiesSelect()
{
var sysAdminUnitId = (Guid)this.parameters["sysAdminUnitId"];
var notificationTypeName = NotificationProviderType.Anniversary.ToString();
var date = DateTime.Now;
var integerDataValueType = new IntegerDataValueType(new DataValueTypeManager())
{
Name = "Integer"
};
var select =
new Select(UserConnection)
.Column("Reminding", "SubjectId").As("Id")
.Column(Func.Max("Reminding", "RemindTime")).As("RemindTime")
.Column("Reminding", "SubjectId").As("EntityId")
.Column("SysSchema", "Name").As("EntitySchemaName")
.Column("Contact", "Name").As("ContactName")
.Column("Contact", "BirthDate").As("ContactDate")
.Column("Account", "Name").As("AccountName")
.Column("AccountAnniversary", "Date").As("AccountDate")
.Column("NotificationsSettings", "SysImageId").As("ImageId")
.Column(Func.Max(Func.Cast("IsRead", integerDataValueType))).As("IsRead")
.Distinct()
.From("Reminding")
.LeftOuterJoin("SysAdminUnit").On("Reminding", "ContactId").IsEqual("SysAdminUnit", "ContactId")
.LeftOuterJoin("SysSchema").On("Reminding", "SysEntitySchemaId").IsEqual("SysSchema", "UId")
.LeftOuterJoin("Contact").On("Reminding", "SubjectId").IsEqual("Contact", "Id")
.LeftOuterJoin("Account").On("Reminding", "SubjectId").IsEqual("Account", "Id")
.LeftOuterJoin("NotificationType").On("Reminding", "NotificationTypeId").IsEqual("NotificationType", "Id")
.LeftOuterJoin("AccountAnniversary").On("Account", "Id").IsEqual("AccountAnniversary", "AccountId")
.LeftOuterJoin("NotificationsSettings").On("Reminding", "SysEntitySchemaId").IsEqual("NotificationsSettings", "SysEntitySchemaUId")
.Where("RemindTime").IsLessOrEqual(Column.Parameter(date.ToUniversalTime()))
.And("SysAdminUnit", "Id").IsEqual(Column.Parameter(sysAdminUnitId))
.And("NotificationType", "Name").IsEqual(Column.Parameter(notificationTypeName))
.Having(Func.Max(Func.Cast("IsRead", integerDataValueType))).IsEqual(Column.Parameter(0))
.GroupBy("Reminding", "SubjectId")
.GroupBy("SysSchema", "Name")
.GroupBy("Contact", "Name")
.GroupBy("Contact", "BirthDate")
.GroupBy("Account", "Name")
.GroupBy("AccountAnniversary", "Date")
.GroupBy("NotificationsSettings", "SysImageId") as Select;
return(select);
}
