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)); }