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));
}
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));
}
public IList <LessonPlan> GetLessonPlansByFilter(string filter, int callerId)
{
var conds = new AndQueryCondition {
{ LessonPlan.SCHOOL_SCHOOLYEAR_REF_FIELD, schoolYearId }
};
var dbQuery = SelectLessonPlan(conds, callerId);
FilterLessonPlanByCallerId(dbQuery, callerId);
var words = filter.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (words.Length > 0)
{
dbQuery.Sql.Append(" and (");
for (var i = 0; i < words.Length; i++)
{
var filterName = $"filter{i}";
dbQuery.Parameters.Add(filterName, string.Format(FILTER_FORMAT, words[i]));
dbQuery.Sql.Append("( ")
.AppendFormat("{0} like @{1} or ", LessonPlan.FULL_CLASS_NAME, filterName)
.AppendFormat("{0} like @{1} ", Announcement.TITLE_FIELD, filterName)
.Append(" ) ");
if (i < words.Length - 1)
{
dbQuery.Sql.Append(" or ");
}
}
dbQuery.Sql.Append(")");
}
return(ReadMany <LessonPlan>(dbQuery));
}
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);
}
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 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));
}
public override IList <SupplementalAnnouncement> GetAnnouncements(QueryCondition condition, int callerId)
{
var conds = new AndQueryCondition {
condition, { LessonPlan.SCHOOL_SCHOOLYEAR_REF_FIELD, _schoolYearId }
};
var dbQuery = SelectSupplementalAnnouncement(conds, callerId);
dbQuery = FilterSupplementalByCallerId(dbQuery, callerId);
return(ReadMany <SupplementalAnnouncement>(dbQuery));
//var condition = new AndQueryCondition {conds, {nameof(SupplementalAnnouncement.SchoolYearRef), SchoolYearId} };
//var isOwnerScript =
// $@"Select
// Cast(Case When Count(*) > 0 Then 1 Else 0 End As bit)
// From
// {nameof(ClassTeacher)}
// Join {nameof(SupplementalAnnouncement)}
// On {nameof(SupplementalAnnouncement)}.{SupplementalAnnouncement.CLASS_REF_FIELD} = {nameof(ClassTeacher)}.{ClassTeacher.CLASS_REF_FIELD}
// Where
// {nameof(ClassTeacher)}.{ClassTeacher.PERSON_REF_FIELD} = @callerId";
//var query =
// $@"Select {SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT}.*, ({isOwnerScript}) as IsOwner
// From {SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT}";
//var @params = new Dictionary<string, object>
//{
// ["callerId"] = callerId
//};
//var dbQuery = new DbQuery(query, @params);
//condition.BuildSqlWhere(dbQuery, SupplementalAnnouncement.VW_SUPPLEMENTAL_ANNOUNCEMENT);
//return ReadMany<SupplementalAnnouncement>(dbQuery);
}
//TODO make sp for this method
public AnnouncementComment GetDetailsById(int announcementCommentId, int callerId, int roleId, bool withSubComments = true)
{
var idColumn = $"{nameof(AnnouncementComment)}_{nameof(AnnouncementComment.Id)}";
var conds = new AndQueryCondition {
{ idColumn, announcementCommentId }
};
var queries = new List <DbQuery> {
Orm.SimpleSelect(AnnouncementComment.VW_ANNOUNCEMENT_COMMENT, conds)
};
var attsQuery = new DbQuery();
attsQuery.Sql.Append($"Select * From {nameof(AnnouncementCommentAttachment)} ")
.Append($"Join {nameof(Attachment)} ")
.Append($" On {nameof(Attachment)}.{nameof(Attachment.Id)} = ")
.Append($" {nameof(AnnouncementCommentAttachment)}.{nameof(AnnouncementCommentAttachment.AttachmentRef)}");
var attsConds = new AndQueryCondition {
{ nameof(AnnouncementCommentAttachment.AnnouncementCommentRef), announcementCommentId }
};
attsConds.BuildSqlWhere(attsQuery, nameof(AnnouncementCommentAttachment));
queries.Add(attsQuery);
var res = Read(new DbQuery(queries), ReadCommentsResult).FirstOrDefault();
if (withSubComments && res != null)
{
var all = GetList(res.AnnouncementRef, callerId, roleId);
return(BuildSubComments(new List <AnnouncementComment> {
res
}, all).First());
}
return(res);
}
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 <ClassAnnouncement> GetClassAnnouncementByFilter(string filter, int callerId)
{
var conds = new AndQueryCondition();
var dbQuery = SelectClassAnnouncements(ClassAnnouncement.VW_CLASS_ANNOUNCEMENT_NAME, callerId);
conds.BuildSqlWhere(dbQuery, ClassAnnouncement.VW_CLASS_ANNOUNCEMENT_NAME);
FilterClassAnnouncementByCaller(dbQuery, callerId);
var words = filter.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (words.Length > 0)
{
dbQuery.Sql.Append(" and (");
for (var i = 0; i < words.Length; i++)
{
var filterName = $"filter{i}";
dbQuery.Parameters.Add(filterName, string.Format(FILTER_FORMAT, words[i]));
dbQuery.Sql.Append("( ")
.AppendFormat($"{ClassAnnouncement.FULL_CLASS_NAME} like @{filterName} or ")
.AppendFormat($"{Announcement.TITLE_FIELD} like @{filterName} ")
.Append(" )");
if (i < words.Length - 1)
{
dbQuery.Sql.Append(" or ");
}
}
dbQuery.Sql.Append(")");
}
return(ReadMany <ClassAnnouncement>(dbQuery));
}
public override Developer GetByIdOrNull(Guid id)
{
var conds = new AndQueryCondition {
{ Developer.ID_FIELD, id }
};
return(ReadOne <Developer>(BuildGetDeveloperQuery(conds), true));
}
public ReportCardsLogo GetDistrictLogo()
{
var conds = new AndQueryCondition {
{ nameof(ReportCardsLogo.SchoolRef), null }
};
return(DoRead(u => new DataAccessBase <ReportCardsLogo>(u).GetAll(conds)).FirstOrDefault());
}
public ReportCardsLogo GetLogoBySchoolId(int schoolId)
{
var conds = new AndQueryCondition {
{ nameof(ReportCardsLogo.SchoolRef), schoolId }
};
return(DoRead(u => new DataAccessBase <ReportCardsLogo>(u).GetAll(conds)).FirstOrDefault());
}
public override AdminAnnouncement GetLastDraft(int personId)
{
var conds = new AndQueryCondition {
{ Announcement.STATE_FIELD, AnnouncementState.Draft }
};
return(GetAnnouncements(conds, personId).OrderByDescending(x => x.Created).FirstOrDefault());
}
public User GetSysAdmin()
{
var conds = new AndQueryCondition {
{ User.IS_SYS_ADMIN_FIELD, true }
};
return(GetUser(conds));
}
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);
}
public Preference GetPreferenceOrNull(string key)
{
var conds = new AndQueryCondition {
{ "Key", key }
};
return(SelectOneOrNull <Preference>(conds));
}
public IList <SchoolProgram> GetAll()
{
var conds = new AndQueryCondition {
{ nameof(SchoolProgram.IsActive), true }
};
return(DoRead(u => new SchoolProgramDataAccess(u).GetAll(conds)).OrderBy(x => x.Name).ToList());
}
public override LPGalleryCategory GetByIdOrNull(int key)
{
var conds = new AndQueryCondition {
{ LPGalleryCategory.ID_FIELD, key }
};
return(ReadOneOrNull <LPGalleryCategory>(BuildSelectQuery(conds)));
}
public override ClassAnnouncement GetAnnouncement(int id, int callerId)
{
var conds = new AndQueryCondition {
{ Announcement.ID_FIELD, id }
};
return(GetAnnouncements(conds, callerId).FirstOrDefault());
}
public IList <MealType> GetAll()
{
var conds = new AndQueryCondition {
{ nameof(MealType.IsActive), true }
};
return(DoRead(u => new DataAccessBase <MealType>(u).GetAll(conds)).OrderBy(x => x.Name).ToList());
}
public IList <Attachment> GetBySisAttachmentId(int sisAttachmentId)
{
var conds = new AndQueryCondition {
{ Attachment.SIS_ATTACHMENT_ID_FIELD, sisAttachmentId }
};
return(GetAll(conds));
}
public Developer GetDeveloper(Guid districtId)
{
var conds = new AndQueryCondition {
{ Developer.DISTRICT_REF_FIELD, districtId }
};
return(ReadOneOrNull <Developer>(BuildGetDeveloperQuery(conds), true));
}
public IList <Attachment> GetLastList(int personId, int count = int.MaxValue)
{
var conds = new AndQueryCondition {
{ Attachment.PERSON_REF_FIELD, personId }
};
return(GetAll(conds).OrderByDescending(x => x.Id).Take(count).ToList());
}
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();
}
public IList <string> GetLastFieldValues(int personId, int count)
{
var conds = new AndQueryCondition {
{ AdminAnnouncement.ADMIN_REF_FIELD, personId }
};
var dbQuery = Orm.SimpleSelect(AdminAnnouncement.VW_ADMIN_ANNOUNCEMENT_NAME, conds);
return(ReadMany <AdminAnnouncement>(dbQuery).Select(x => x.Content).Distinct().ToList());
}
public IList <GradedItem> GetGradedItems(int gradingPeriodId)
{
var conds = new AndQueryCondition()
{
{ GradedItem.GRADING_PERIOD_REF_FIELD, gradingPeriodId }
};
return(DoRead(u => new DataAccessBase <GradedItem>(u).GetAll(conds)));
}
public IList <DayType> GetDayTypes()
{
Trace.Assert(Context.SchoolYearId.HasValue);
var conds = new AndQueryCondition {
{ DayType.SCHOOL_YEAR_REF, Context.SchoolYearId }
};
return(DoRead(u => new DataAccessBase <DayType>(u).GetAll(conds)));
}
public AnnouncementAttachment GetById(int id, int callerId, int roleId, bool hasAdminClassPermission = false)
{
var idField = $"{nameof (AnnouncementAttachment)}_{nameof(AnnouncementAttachment.Id)}";
var conds = new AndQueryCondition {
{ idField, id }
};
return(GetAnnouncementAttachments(conds, callerId, roleId, hasAdminClassPermission: hasAdminClassPermission).FirstOrDefault());
}
public override ClassAnnouncement GetById(int key)
{
var conds = new AndQueryCondition {
{ Announcement.ID_FIELD, key }
};
var dbQuery = Orm.SimpleSelect(ClassAnnouncement.VW_CLASS_ANNOUNCEMENT_NAME, conds);
return(ReadOne <ClassAnnouncement>(dbQuery));
}
