private static DbQuery BuildSql(QueryCondition conditions)
{
var userQuery = new DbQuery();
var suQuery = new DbQuery();
var typesL1 = new List <Type> {
typeof(User), typeof(District), typeof(UserLoginInfo)
};
var typesL2 = new List <Type> {
typesL1[0], typeof(SchoolUser), typeof(School)
};
userQuery.Sql.AppendFormat(@"select {0} from [{1}]
left join [{2}] on [{1}].{3} = [{2}].{4}
left join [{5}] on [{5}].[{6}] = [{1}].[{7}]"
, Orm.ComplexResultSetQuery(typesL1), typesL1[0].Name, typesL1[1].Name,
User.DISTRICT_REF_FIELD, District.ID_FIELD, typesL1[2].Name, UserLoginInfo.ID_FIELD, User.ID_FIELD);
conditions.BuildSqlWhere(userQuery, typesL1[0].Name);
suQuery.Sql.AppendFormat(@"select {0} from [User]
join SchoolUser on [User].[{1}] = SchoolUser.[{2}] and [User].[{7}] = SchoolUser.[{8}]
join School on SchoolUser.[{3}] = School.[{4}] and SchoolUser.[{8}] = School.[{9}]
left join UserLoginInfo on UserLoginInfo.[{5}] = [User].[{6}]", Orm.ComplexResultSetQuery(typesL2)
, User.SIS_USER_ID_FIELD, SchoolUser.USER_REF_FIELD, SchoolUser.SCHOOL_REF_FIELD, School.LOCAL_ID_FIELD
, UserLoginInfo.ID_FIELD, User.ID_FIELD, User.DISTRICT_REF_FIELD, SchoolUser.DISTRICT_REF_FIELD, School.DISTRICT_REF_FIELD);
conditions.BuildSqlWhere(suQuery, typesL1[0].Name);
return(new DbQuery(new List <DbQuery> {
userQuery, suQuery
}));
}
private DbQuery BuildAutoGradesSelect()
{
var query = new DbQuery();
var types = new List <Type> {
typeof(AutoGrade), typeof(AnnouncementApplication)
};
query.Sql.AppendFormat(Orm.SELECT_FORMAT, Orm.ComplexResultSetQuery(types), types[0].Name)
.Append(" ").AppendFormat(Orm.SIMPLE_JOIN_FORMAT, types[1].Name, nameof(AnnouncementApplication.Id)
, types[0].Name, AutoGrade.ANNOUNCEMENT_APPLICATION_REF_FIELD);
query.Sql.Append(" ");
return(query);
}
public static DbQuery BuildGetDeveloperQuery(QueryCondition conds)
{
var developertype = typeof(Developer);
var resulSet = Orm.ComplexResultSetQuery(new List <Type> {
developertype, typeof(User)
});
DbQuery query = new DbQuery();
query.Sql.AppendFormat(@"select {0}
from [Developer]
join [User] on [User].Id = [Developer].[Id] ", resulSet);
conds.BuildSqlWhere(query, developertype.Name);
return(query);
}
//TODO: maybe move this to procedure later
public IList <StudentContactDetails> GetStudentContactsDetails(int studentId)
{
var stcQuery = new DbQuery();
var types = new List <Type> {
typeof(StudentContact), typeof(Person), typeof(ContactRelationship)
};
stcQuery.Sql.AppendFormat(@" select {0} from [{1}]
join [{4}] on [{4}].[{5}] = [{1}].[{2}]
join [{6}] on [{6}].[{7}] = [{1}].[{3}] "
, Orm.ComplexResultSetQuery(types), types[0].Name, StudentContact.CONTACT_REF_FIELD,
StudentContact.CONTACT_RELATIONSHIP_REF_FIELD,
types[1].Name, Person.ID_FIELD, types[2].Name, ContactRelationship.ID_FIELD);
var conds = new AndQueryCondition {
{ StudentContact.STUDENT_REF_FIELD, studentId }
};
conds.BuildSqlWhere(stcQuery, types[0].Name);
var addressQuery = new DbQuery();
addressQuery.Sql.AppendFormat(@" select Address.* from Address
join Person on Person.AddressRef = Address.Id
join StudentContact on StudentContact.ContactRef = Person.Id ");
conds.BuildSqlWhere(addressQuery, types[0].Name);
var phonesQuery = new DbQuery();
phonesQuery.Sql.AppendFormat(@" select Phone.* from Phone
join StudentContact on StudentContact.ContactRef = Phone.PersonRef ");
conds.BuildSqlWhere(phonesQuery, types[0].Name);
var emailsQuery = new DbQuery();
emailsQuery.Sql.AppendFormat(@" select PersonEmail.* from PersonEmail
join StudentContact on StudentContact.ContactRef = PersonEmail.PersonRef ");
conds.BuildSqlWhere(emailsQuery, types[0].Name);
var dbQeury = new DbQuery(new List <DbQuery> {
stcQuery, addressQuery, phonesQuery, emailsQuery
});
return(Read(dbQeury, ReadStudentContactDetailsRes));
}
public IList <AnnouncementStandardDetails> GetAnnouncementStandardsByAnnId(int announcementId)
{
var dbQuery = new DbQuery();
var types = new List <Type> {
typeof(AnnouncementStandard), typeof(Standard)
};
dbQuery.Sql
.AppendFormat(Orm.SELECT_FORMAT, Orm.ComplexResultSetQuery(types), types[0].Name).Append(" ")
.AppendFormat(Orm.SIMPLE_JOIN_FORMAT, types[1].Name, nameof(Standard.Id),
types[0].Name, AnnouncementStandard.STANDARD_REF_FIELD)
.Append(" ");
var conds = new AndQueryCondition {
{ AnnouncementStandard.ANNOUNCEMENT_REF_FIELD, announcementId }
};
conds.BuildSqlWhere(dbQuery, types[0].Name);
return(ReadMany <AnnouncementStandardDetails>(dbQuery, true));
}
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);
}
