public PaginatedList <SchoolYear> GetSchoolYears(int start = 0, int count = int.MaxValue, int?schoolId = null, int?acadYear = null, bool activeOnly = false)
{
var acadSessions = ConnectorLocator.UsersConnector.GetUserAcadSessionsIds();
var conds = new AndQueryCondition();
if (schoolId.HasValue)
{
conds.Add(nameof(SchoolYear.SchoolRef), schoolId.Value);
}
if (activeOnly)
{
conds.Add(nameof(SchoolYear.ArchiveDate), null);
}
if (acadYear.HasValue)
{
conds.Add(nameof(SchoolYear.AcadYear), acadYear);
}
var res = DoRead(u => new SchoolYearDataAccess(u).GetAll(conds));
res = res.Where(x => acadSessions.Contains(x.Id)).ToList();
if (res.Count == 0)
{
throw new ChalkableException("Current user does not have access to any of school acadSessions");
}
res = res.OrderBy(x => x.StartDate).ToList();
return(new PaginatedList <SchoolYear>(res, start / count, count, res.Count));
}
protected IList <LessonPlan> GetLessonPlanTemplates(int?lessonPlanId, int?lpGalleryCategoryId, string titleFilter, int?classId, AnnouncementState?state, int callerId)
{
var conds = new AndQueryCondition {
{ LessonPlan.IN_GALLERY, 1, ConditionRelation.Equal }
};
if (lessonPlanId.HasValue)
{
conds.Add(Announcement.ID_FIELD, lessonPlanId);
}
if (state.HasValue)
{
conds.Add(Announcement.STATE_FIELD, state);
}
if (lpGalleryCategoryId.HasValue)
{
conds.Add(LessonPlan.LP_GALERRY_CATEGORY_REF_FIELD, lpGalleryCategoryId);
}
if (classId.HasValue)
{
conds.Add(LessonPlan.CLASS_REF_FIELD, classId);
}
var dbQuery = SelectLessonPlan(conds, callerId);
dbQuery.Sql.AppendFormat(" and {0} like @{0}", Announcement.TITLE_FIELD);
dbQuery.Parameters.Add(Announcement.TITLE_FIELD, string.Format(FILTER_FORMAT, titleFilter));
return(ReadMany <LessonPlan>(dbQuery));
}
public IList <Topic> Get(Guid?subjectDocId, Guid?courseId, Guid?parentId, bool firstLevelOnly = false)
{
AndQueryCondition conditions = new AndQueryCondition();
if (subjectDocId.HasValue)
{
conditions.Add(nameof(Topic.SubjectDocRef), subjectDocId, ConditionRelation.Equal);
}
if (parentId.HasValue)
{
conditions.Add(nameof(Topic.ParentRef), parentId, ConditionRelation.Equal);
}
if (courseId.HasValue)
{
conditions.Add(nameof(Topic.CourseRef), courseId, ConditionRelation.Equal);
}
if (firstLevelOnly)
{
conditions.Add(nameof(Topic.Level), 1, ConditionRelation.Equal);
}
var query = Orm.SimpleSelect(nameof(Topic), conditions);
return(ReadMany <Topic>(query));
}
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 <PersonSetting> GetPersonSettings(IList <string> keys, int?personId, int?schoolYearId, int?classId)
{
var conds = new AndQueryCondition();
if (personId.HasValue)
{
conds.Add(nameof(PersonSetting.PersonRef), personId.Value);
}
if (schoolYearId.HasValue)
{
conds.Add(nameof(PersonSetting.SchoolYearRef), schoolYearId.Value);
}
if (classId.HasValue)
{
conds.Add(nameof(PersonSetting.ClassRef), classId.Value);
}
var q = Orm.SimpleSelect <PersonSetting>(conds);
if (keys != null && keys.Count > 0)
{
var pKyes = new List <string>();
for (int i = 0; i < keys.Count; i++)
{
var pKey = $"@key_{i + 1}";
q.Parameters.Add(pKey, keys[i]);
pKyes.Add(pKey);
}
q.Sql.Append($" and [{nameof(PersonSetting.Key)}] in ({pKyes.JoinString(",")})");
}
return(ReadMany <PersonSetting>(q));
}
private DbQuery BuildConditionQuery(DbQuery dbQuery, DateQuery query)
{
var condition = new AndQueryCondition();
if (query.SchoolYearId.HasValue)
{
condition.Add(Date.SCHOOL_YEAR_REF, query.SchoolYearId, ConditionRelation.Equal);
}
if (query.DayType.HasValue)
{
condition.Add(Date.DATE_TYPE_REF_FIELD, query.DayType, ConditionRelation.Equal);
}
if (query.FromDate.HasValue)
{
condition.Add(Date.DATE_TIME_FIELD, "fromDate", query.FromDate, ConditionRelation.GreaterEqual);
}
if (query.ToDate.HasValue)
{
condition.Add(Date.DATE_TIME_FIELD, "toDate", query.ToDate, ConditionRelation.LessEqual);
}
if (query.SchoolDaysOnly)
{
condition.Add(Date.IS_SCHOOL_DAY_FIELD, true, ConditionRelation.Equal);
}
condition.BuildSqlWhere(dbQuery, "Date");
if (query.MarkingPeriodId.HasValue)
{
dbQuery.Parameters.Add("@markingPeriodId", query.MarkingPeriodId);
dbQuery.Sql.AppendFormat(@" and exists(select * from MarkingPeriod where [MarkingPeriod].[{0}] = @markingPeriodId
and [MarkingPeriod].[{1}] <= [Date].[{3}] and [MarkingPeriod].[{2}] >= [Date].[{3}])"
, MarkingPeriod.ID_FIELD, MarkingPeriod.START_DATE_FIELD, MarkingPeriod.END_DATE_FIELD, Date.DATE_TIME_FIELD);
}
return(dbQuery);
}
public void DeleteOlder(Guid?districtId, DateTime dateTime)
{
var q = new AndQueryCondition();
q.Add(BackgroundTask.DISTRICT_REF_FIELD_NAME, districtId);
q.Add(BackgroundTask.COMPLETED_FIELD_NAME, dateTime, ConditionRelation.Less);
SimpleDelete(q);
}
private QueryCondition BuildConditions(MarkingPeriodClassQuery query)
{
var res = new AndQueryCondition();
if (query.ClassId.HasValue)
{
res.Add(MarkingPeriodClass.CLASS_REF_FIELD, query.ClassId);
}
if (query.MarkingPeriodId.HasValue)
{
res.Add(MarkingPeriodClass.MARKING_PERIOD_REF_FIELD, query.MarkingPeriodId);
}
return(res);
}
public IList <Infraction> GetInfractions(bool onlyActive = false, bool onlyVisibleInClassRoom = false)
{
var conds = new AndQueryCondition();
if (onlyActive)
{
conds.Add(Infraction.IS_ACTIVE_FIELD, true);
}
if (onlyVisibleInClassRoom)
{
conds.Add(Infraction.VISIBLE_IN_CLASSROOM_FIELD, true);
}
return(DoRead(u => new DataAccessBase <Infraction>(u).GetAll(conds)));
}
private QueryCondition BuildConditioins(int?classId, int?teacherId)
{
var conds = new AndQueryCondition();
if (classId.HasValue)
{
conds.Add(ClassTeacher.CLASS_REF_FIELD, classId);
}
if (teacherId.HasValue)
{
conds.Add(ClassTeacher.PERSON_REF_FIELD, teacherId);
}
return(conds);
}
public void Delete(int?announcementId, int?standardId)
{
var conds = new AndQueryCondition();
if (announcementId.HasValue)
{
conds.Add(nameof(AnnouncementStandard.AnnouncementRef), announcementId.Value);
}
if (standardId.HasValue)
{
conds.Add(nameof(AnnouncementStandard.StandardRef), standardId.Value);
}
SimpleDelete(conds);
}
public IList <Class> GetClassesBySchoolYear(int schoolYearId, int?gradeLevel)
{
var conds = new AndQueryCondition {
{ nameof(Class.SchoolYearRef), schoolYearId }
};
if (gradeLevel.HasValue)
{
conds.Add(nameof(Class.MinGradeLevelRef), gradeLevel, ConditionRelation.GreaterEqual);
conds.Add(nameof(Class.MaxGradeLevelRef), gradeLevel, ConditionRelation.LessEqual);
}
return(DoRead(u => new ClassDataAccess(u).GetAll(conds))
.OrderBy(c => c.Name)
.ThenBy(c => c.ClassNumber)
.ToList());
}
public IList <AttendanceMonth> GetAttendanceMonths(int schoolYearId, DateTime?fromDate = null, DateTime?endDate = null)
{
var conds = new AndQueryCondition {
{ AttendanceMonth.SCHOOL_YEAR_REF_FIELD, schoolYearId }
};
if (fromDate.HasValue)
{
conds.Add(AttendanceMonth.END_DATE_FIELD, fromDate.Value, ConditionRelation.GreaterEqual);
}
if (endDate.HasValue)
{
conds.Add(AttendanceMonth.START_DATE_FIELD, endDate.Value, ConditionRelation.LessEqual);
}
return(DoRead(u => new DataAccessBase <AttendanceMonth>(u).GetAll(conds)));
}
public User GetUser(string login, string password, Guid?id)
{
var conds = new AndQueryCondition();
if (login != null)
{
conds.Add(User.LOGIN_FIELD, login);
}
if (password != null)
{
conds.Add(User.PASSWORD_FIELD, password);
}
if (id != null)
{
conds.Add(User.ID_FIELD, id);
}
return(GetUser(conds));
}
public PaginatedList <BackgroundTask> Find(Guid?districtId, BackgroundTaskStateEnum?state, BackgroundTaskTypeEnum?type, bool allDistricts, int start, int count)
{
var q = new AndQueryCondition();
if (state.HasValue)
{
q.Add(BackgroundTask.STATE_FIELD_NAME, state);
}
if (type.HasValue)
{
q.Add(BackgroundTask.TYPE_FIELD_NAME, type);
}
if (!allDistricts)
{
q.Add(BackgroundTask.DISTRICT_REF_FIELD_NAME, districtId);
}
return(PaginatedSelect <BackgroundTask>(q, BackgroundTask.SCHEDULED_FIELD_NAME, start, count, Orm.OrderType.Desc));
}
public Attachment GetById(int attachmentId, int callerId, int roleId)
{
var dbQuery = new DbQuery();
var conds = new AndQueryCondition();
conds.Add(Attachment.ID_FIELD, attachmentId);
throw new NotImplementedException();
}
private QueryCondition GetSchoolPersonsCondition(int?personId, int?roleId, int?schoolId)
{
var res = new AndQueryCondition();
if (personId.HasValue)
{
res.Add(SchoolPerson.PERSON_REF_FIELD, personId);
}
if (roleId.HasValue)
{
res.Add(SchoolPerson.ROLE_REF, roleId);
}
if (schoolId.HasValue)
{
res.Add(SchoolPerson.SCHOOL_REF_FIELD, schoolId);
}
return(res);
}
public IList <LimitedEnglish> GetList(bool?activeOnly)
{
var conds = new AndQueryCondition();
if (activeOnly.HasValue)
{
conds.Add(nameof(LimitedEnglish.IsActive), activeOnly.Value);
}
return(DoRead(u => new DataAccessBase <LimitedEnglish>(u).GetAll()));
}
public User GetUser(string confirmationKey)
{
var conds = new AndQueryCondition();
if (!string.IsNullOrEmpty(confirmationKey))
{
conds.Add(User.CONFIRMATION_KEY_FIELD, confirmationKey);
}
return(GetUser(conds));
}
private QueryCondition GetCondsBySchoolYear(int?schoolYearId)
{
var res = new AndQueryCondition();
if (schoolYearId.HasValue)
{
res.Add(Period.SCHOOL_YEAR_REF, schoolYearId);
}
return(res);
}
public IList <CustomReportTemplate> GetList(TemplateType?type)
{
var conds = new AndQueryCondition();
if (type.HasValue)
{
conds.Add(nameof(CustomReportTemplate.Type), (int)type);
}
return(DoRead(u => new DataAccessBase <CustomReportTemplate>(u).GetAll(conds)));
}
public PaginatedList <School> GetSchools(Guid?districtId, int start, int count)
{
var conds = new AndQueryCondition();
if (districtId.HasValue)
{
conds.Add(School.DISTRICT_REF_FIELD, districtId);
}
return(PaginatedSelect <School>(conds, School.ID_FIELD, start, count));
}
//TODO: move this to stored procedure
public IList <Standard> GetStandards(StandardQuery query)
{
var condition = new AndQueryCondition();
if (query.StandardSubjectId.HasValue)
{
condition.Add(nameof(Standard.StandardSubjectRef), query.StandardSubjectId);
}
if (query.GradeLavelId.HasValue)
{
condition.Add(nameof(Standard.LowerGradeLevelRef), query.GradeLavelId, ConditionRelation.LessEqual);
condition.Add(nameof(Standard.UpperGradeLevelRef), query.GradeLavelId, ConditionRelation.GreaterEqual);
}
if (query.ParentStandardId.HasValue)
{
condition.Add(nameof(Standard.ParentStandardRef), query.ParentStandardId);
}
if (query.ActiveOnly)
{
condition.Add(nameof(Standard.IsActive), true);
}
var dbQuery = BuildSelectQuery(condition);
if (query.ClassId.HasValue)
{
dbQuery.Parameters.Add("classId", query.ClassId);
dbQuery.Sql.AppendFormat("and [{0}].[{1}] in (", "Standard", nameof(Standard.Id));
var subQuery = BuildClassStandardSubQuery("classId");
dbQuery.Sql.Append(subQuery).Append(")");
if (!query.ParentStandardId.HasValue && !query.AllStandards)
{
dbQuery.Sql.AppendFormat(" and ([{0}].[{1}] is null or [{0}].[{1}] not in (", "Standard",
nameof(Standard.ParentStandardRef))
.Append(subQuery).Append("))");
}
}
return(ReadMany <Standard>(dbQuery));
}
public MarkingPeriod GetLast(DateTime tillDate, int schoolYearId)
{
var conds = new AndQueryCondition {
{ MarkingPeriod.START_DATE_FIELD, tillDate, ConditionRelation.LessEqual }
};
conds.Add(MarkingPeriod.SCHOOL_YEAR_REF, schoolYearId);
var q = Orm.SimpleSelect <MarkingPeriod>(conds);
q.Sql.AppendFormat("order by {0} desc", MarkingPeriod.END_DATE_FIELD);
return(ReadOneOrNull <MarkingPeriod>(q));
}
public bool Exists(string name, int?excludedCategoryId)
{
var conds = new AndQueryCondition {
{ LPGalleryCategory.NAME_FIELD, name }
};
if (excludedCategoryId.HasValue)
{
conds.Add(LPGalleryCategory.ID_FIELD, excludedCategoryId.Value, ConditionRelation.NotEqual);
}
return(Exists(conds));
}
public QueryCondition BuildConditioins(ClassPersonQuery query)
{
var conds = new AndQueryCondition();
if (query.ClassId.HasValue)
{
conds.Add(ClassPerson.CLASS_REF_FIELD, query.ClassId);
}
if (query.PersonId.HasValue)
{
conds.Add(ClassPerson.PERSON_REF_FIELD, query.PersonId);
}
if (query.MarkingPeriodId.HasValue)
{
conds.Add(ClassPerson.MARKING_PERIOD_REF, query.MarkingPeriodId);
}
if (query.IsEnrolled.HasValue)
{
conds.Add(ClassPerson.IS_ENROLLED_FIELD, query.IsEnrolled);
}
return(conds);
}
public IList <int> GetEnrollmentStudentsIds(int schoolYearId, int?gradeLevelId)
{
var conds = new AndQueryCondition
{
{ StudentSchoolYear.SCHOOL_YEAR_REF_FIELD, schoolYearId },
{ StudentSchoolYear.ENROLLMENT_STATUS_FIELD, StudentEnrollmentStatusEnum.CurrentlyEnrolled }
};
if (gradeLevelId.HasValue)
{
conds.Add(StudentSchoolYear.GRADE_LEVEL_REF_FIELD, gradeLevelId);
}
return(SelectMany <StudentSchoolYear>(conds).Select(x => x.StudentRef).ToList());
}
public User GetBySisUserId(int userId, Guid?districtId)
{
using (var uow = Read())
{
var conds = new AndQueryCondition {
{ User.SIS_USER_ID_FIELD, userId }
};
if (districtId.HasValue)
{
conds.Add(User.DISTRICT_REF_FIELD, districtId.Value);
}
return(new UserDataAccess(uow).GetUser(conds));
}
}
public MarkingPeriod GetMarkingPeriod(DateTime date, int?schoolYearId)
{
var conds = new AndQueryCondition
{
{ MarkingPeriod.START_DATE_FIELD, "date1", date, ConditionRelation.LessEqual },
{ MarkingPeriod.END_DATE_FIELD, "date2", date, ConditionRelation.GreaterEqual }
};
if (schoolYearId.HasValue)
{
conds.Add(MarkingPeriod.SCHOOL_YEAR_REF, schoolYearId.Value);
}
return(SelectOneOrNull <MarkingPeriod>(conds));
}
public IList <LessonPlan> GetLessonPlans(DateTime?fromDate, DateTime?toDate, int?classId, int?lpGalleryCategoryId, int callerId, int?studentId, int?teacherId, bool filterByStartDate = true, int?standardId = null)
{
//TODO: move this to the stored procedure later
var conds = new AndQueryCondition
{
{ Announcement.STATE_FIELD, AnnouncementState.Created },
};
if (fromDate.HasValue)
{
conds.Add(LessonPlan.END_DATE_FIELD, "fromDate", fromDate, ConditionRelation.GreaterEqual);
}
if (toDate.HasValue)
{
conds.Add(LessonPlan.START_DATE_FIELD, "toDate", toDate, ConditionRelation.LessEqual);
}
if (filterByStartDate)
{
conds.Add(LessonPlan.START_DATE_FIELD, "fromDate2", fromDate, ConditionRelation.GreaterEqual);
}
//if classId was set there is no need to filter by schoolYear ... possible case when class is no in the current schoolYear.
if (classId.HasValue)
{
conds.Add(LessonPlan.CLASS_REF_FIELD, classId);
}
else
{
conds.Add(LessonPlan.SCHOOL_SCHOOLYEAR_REF_FIELD, schoolYearId);
}
if (lpGalleryCategoryId.HasValue)
{
conds.Add(LessonPlan.LP_GALERRY_CATEGORY_REF_FIELD, lpGalleryCategoryId);
}
var dbQuery = SelectLessonPlan(conds, callerId);
dbQuery = FilterLessonPlanByCallerId(dbQuery, callerId);
if (studentId.HasValue)
{
dbQuery.Sql.Append($" and exists(select * from ClassPerson where PersonRef = {studentId} and ClassPerson.ClassRef = {LessonPlan.VW_LESSON_PLAN_NAME}.ClassRef)");
}
if (teacherId.HasValue)
{
dbQuery.Sql.Append($" and exists(select * from ClassTeacher where PersonRef = {teacherId} and ClassTeacher.ClassRef = {LessonPlan.VW_LESSON_PLAN_NAME}.ClassRef)");
}
if (standardId.HasValue)
{
dbQuery.Sql.Append($" and exists(select * from AnnouncementStandard where StandardRef = {standardId.Value} and AnnouncementRef = {LessonPlan.VW_LESSON_PLAN_NAME}.Id)");
}
return(ReadMany <LessonPlan>(dbQuery));
}