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
}));
}
public IList <Group> GetAll(QueryCondition conditions = null, string filter = null)
{
var query = new DbQuery();
var groupTName = typeof(Group).Name;
var querySet = string.Format(" [{0}].*, (select count(*) from [{3}] where [{3}].[{4}] = [{1}]) as {2}"
, groupTName, Group.ID_FIELD, Group.STUDENT_COUNT_FIELD
, typeof(StudentGroup).Name, StudentGroup.GROUP_REF_FIELD);
query.Sql.AppendFormat(Orm.SELECT_FORMAT, querySet, groupTName);
if (conditions != null)
{
conditions.BuildSqlWhere(query, groupTName);
}
if (!string.IsNullOrEmpty(filter))
{
query.Sql.AppendFormat(" AND (");
string[] sl = filter.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < sl.Length; i++)
{
query.Parameters.Add("@filter" + i, string.Format(FILTER_FORMAT, sl[i]));
query.Sql.AppendFormat($" [{typeof(Group).Name}].[{nameof(Group.Name)}] like @filter" + i + " OR");
}
query.Sql.Remove(query.Sql.Length - 2, 2);
query.Sql.AppendFormat(")");
}
return(ReadMany <Group>(query));
}
protected DbQuery SelectSupplementalAnnouncement(QueryCondition condition, int?callerId = null)
{
var dbQuery = new DbQuery();
var ownerQ = new DbQuery();
if (!callerId.HasValue)
{
ownerQ.Sql.Append("cast(0 as bit)");
}
else
{
ownerQ.Sql
.AppendFormat(Orm.SELECT_FORMAT, "cast(case when count(*) > 0 then 1 else 0 end as bit)",
nameof(ClassTeacher))
.Append(" Where ")
.Append($"[{nameof(ClassTeacher)}].[{nameof(ClassTeacher.PersonRef)}] = {callerId}")
.Append(" and ")
.Append($"[{nameof(ClassTeacher)}].[{nameof(ClassTeacher.ClassRef)}]").Append("=")
.Append($"[{SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT}].[{nameof(SupplementalAnnouncement.ClassRef)}]");
}
var selectSet = $"{SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT}.*, ({ownerQ.Sql}) as IsOwner";
dbQuery.Sql.AppendFormat(Orm.SELECT_FORMAT, selectSet, SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT);
condition.BuildSqlWhere(dbQuery, SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT);
return(dbQuery);
}
public override IList <ClassAnnouncement> GetAnnouncements(QueryCondition conds, int callerId)
{
var dbQuery = SelectClassAnnouncements(ClassAnnouncement.VW_CLASS_ANNOUNCEMENT_NAME, callerId);
conds.BuildSqlWhere(dbQuery, ClassAnnouncement.VW_CLASS_ANNOUNCEMENT_NAME);
FilterClassAnnouncementByCaller(dbQuery, callerId);
return(ReadMany <ClassAnnouncement>(dbQuery));
}
public override IList <AdminAnnouncement> GetAnnouncements(QueryCondition conds, int callerId)
{
var dbQuery = SelectAdminAnnouncement(callerId);
conds.BuildSqlWhere(dbQuery, AdminAnnouncement.VW_ADMIN_ANNOUNCEMENT_NAME);
dbQuery = FilterAdminAnnouncementByCaller(dbQuery, callerId);
return(ReadMany <AdminAnnouncement>(dbQuery));
}
private DbQuery BuildClassStandardQuery(IEnumerable <string> columnsResList, QueryCondition classCondition)
{
var res = new DbQuery();
res.Sql.AppendFormat(@"select {0}
from ClassStandard
join [Class] on [Class].[{1}] = ClassStandard.[{2}] or ClassStandard.[{2}] = Class.[{3}]"
, columnsResList.Select(x => "ClassStandard.[" + x + "]").JoinString(",")
, Class.ID_FIELD, ClassStandard.CLASS_REF_FIELD, Class.COURSE_REF_FIELD);
classCondition.BuildSqlWhere(res, "Class");
return(res);
}
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);
}
private DbQuery BuildSelectQuery(QueryCondition condition)
{
var dbQuery = new DbQuery();
condition = condition ?? new AndQueryCondition();
//TODO make a view later
dbQuery.Sql.Append($" Select [{nameof(Standard)}].*, ")
// calculating is deepest(or leaf) standard
.Append($" cast((case when exists(Select * From [{nameof(Standard)}] innerSt ")
.Append($" Where innerSt.{nameof(Standard.ParentStandardRef)} = [{nameof(Standard)}].{nameof(Standard.Id)}) ")
.Append($" then 0 else 1 end) as bit) as [{nameof(Standard.IsDeepest)}] ")
.Append($" From [{nameof(Standard)}] ");
condition.BuildSqlWhere(dbQuery, nameof(Standard));
return(dbQuery);
}
public void Delete(QueryCondition annCondition, QueryCondition classCondition, bool notInClassStandard)
{
var cStandardDbQuery = BuildClassStandardQuery(new List <string> {
ClassStandard.STANDARD_REF_FIELD
}, classCondition);
var dbQuery = new DbQuery();
dbQuery.Sql.AppendFormat(@"delete from AnnouncementStandard ");
annCondition.BuildSqlWhere(dbQuery, "AnnouncementStandard");
dbQuery.Sql.AppendFormat(" and AnnouncementStandard.[{0}] {2} in ({1})"
, AnnouncementStandard.STANDARD_REF_FIELD, cStandardDbQuery.Sql, notInClassStandard ? "not" : "");
foreach (var parameter in cStandardDbQuery.Parameters)
{
dbQuery.Parameters.Add(parameter);
}
ExecuteNonQueryParametrized(dbQuery.Sql.ToString(), dbQuery.Parameters);
}
protected DbQuery SelectLessonPlan(QueryCondition condition, int?callerId = null)
{
var dbQuery = new DbQuery();
var classTeacherSql = "cast(0 as bit)";
if (callerId.HasValue)
{
classTeacherSql = string.Format(@"(select count(*) from [{0}] where [{0}].[{1}] = {2} and [{0}].[{3}] = [{4}].[{5}])",
"ClassTeacher", ClassTeacher.PERSON_REF_FIELD, callerId, ClassTeacher.CLASS_REF_FIELD,
LessonPlan.VW_LESSON_PLAN_NAME, LessonPlan.CLASS_REF_FIELD);
classTeacherSql = $"cast(case when {nameof(LessonPlan.GalleryOwnerRef)} = {callerId} or {classTeacherSql} > 0 then 1 else 0 end as bit)";
}
var selectSet = $"{LessonPlan.VW_LESSON_PLAN_NAME}.*, {classTeacherSql} as IsOwner";
dbQuery.Sql.AppendFormat(Orm.SELECT_FORMAT, selectSet, LessonPlan.VW_LESSON_PLAN_NAME);
condition.BuildSqlWhere(dbQuery, LessonPlan.VW_LESSON_PLAN_NAME);
return(dbQuery);
}
