//private const string CALLER_ID = "@callerId" private IList <AnnouncementAttachment> GetAnnouncementAttachments(QueryConditionSet conds, int callerId, int roleId, string filter = null, bool hasAdminClassPermission = false) { var query = BuildGetAttachmentQuery(conds, callerId, roleId, true, filter, hasAdminClassPermission); return(query == null ? new List <AnnouncementAttachment>() : ReadMany <AnnouncementAttachment>(query, true)); }
//TODO: refactor this ... probably move this to stored procedure private DbQuery BuildGetAttachmentQuery(QueryConditionSet queryCondition, int callerId, int roleId, bool needsAllAttachments = true, string filter = null, bool hasAdminClassPermission = false) { var res = new DbQuery(); var annRefField = $"{nameof(AnnouncementAttachment)}_{nameof(AnnouncementAttachment.AnnouncementRef)}"; res.Sql.AppendFormat(@"select [{0}].* from [{0}] join [{2}] on [{2}].[{3}] = [{0}].[{1}]" , AnnouncementAttachment.VW_ANNOUNCEMENT_ATTACHMENT, annRefField , nameof(Announcement), nameof(Announcement.Id)); res.Sql.AppendFormat(@" left join LessonPlan on LessonPlan.Id = Announcement.Id left join ClassAnnouncement on ClassAnnouncement.Id = Announcement.Id left join SupplementalAnnouncement on SupplementalAnnouncement.Id = Announcement.Id left join AdminAnnouncement on AdminAnnouncement.Id = Announcement.Id "); queryCondition.BuildSqlWhere(res, AnnouncementAttachment.VW_ANNOUNCEMENT_ATTACHMENT); if (!needsAllAttachments) { res.Sql.AppendFormat(" and Attachment_PersonRef = @callerId"); } if (!string.IsNullOrEmpty(filter)) { string[] sl = filter.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); var filters = new List <string>(); if (sl.Length > 0) { filters.Add("@filter1"); res.Parameters.Add("@filter1", string.Format(FILTER_FORMAT, sl[0])); } if (sl.Length > 1) { filters.Add("@filter2"); res.Parameters.Add("@filter2", string.Format(FILTER_FORMAT, sl[1])); } if (sl.Length > 2) { filters.Add("@filter3"); res.Parameters.Add("@filter3", string.Format(FILTER_FORMAT, sl[2])); } if (filters.Count > 0) { res.Sql.AppendFormat(" and (LOWER(Attachment_Name) like {0})", filters.JoinString(" or LOWER(Attachment_Name) like ")); } } res.Parameters.Add("@callerId", callerId); res.Parameters.Add("@roleId", roleId); if (CoreRoles.SUPER_ADMIN_ROLE.Id == roleId) { return(res); } if (CoreRoles.DISTRICT_ADMIN_ROLE.Id == roleId || CoreRoles.TEACHER_ROLE.Id == roleId) { if (CoreRoles.DISTRICT_ADMIN_ROLE.Id == roleId) { res.Sql.Append("and (AdminAnnouncement.Id is null or Attachment_PersonRef = @callerId)"); } if (CoreRoles.TEACHER_ROLE.Id == roleId) { res.Sql.Append(" and AdminAnnouncement.Id is null "); } if (!hasAdminClassPermission) { res.Sql.Append("and (AdminAnnouncement.Id is not null or "); res.Sql.Append(@" exists(select * from ClassTeacher where (ClassTeacher.PersonRef = @callerId or Attachment_PersonRef = ClassTeacher.PersonRef) and (ClassTeacher.ClassRef = LessonPlan.ClassRef or ClassTeacher.ClassRef = ClassAnnouncement.ClassRef or ClassTeacher.ClassRef = SupplementalAnnouncement.ClassRef) )"); res.Sql.Append(")"); } return(res); } if (CoreRoles.STUDENT_ROLE.Id == roleId) { res.Sql.Append(@" and (Attachment_PersonRef = @callerId or ( ( exists(select * from ClassPerson cp where cp.PersonRef = @callerId and (cp.ClassRef = LessonPlan.ClassRef or cp.ClassRef = ClassAnnouncement.ClassRef) ) or exists(select * from SupplementalAnnouncementRecipient Where StudentRef = @callerId and SupplementalAnnouncementRef = SupplementalAnnouncement.Id) ) and exists(select ct.ClassRef from ClassTeacher ct where ct.PersonRef = Attachment_PersonRef and (ct.ClassRef = LessonPlan.ClassRef or ct.ClassRef = ClassAnnouncement.ClassRef or ct.ClassRef = SupplementalAnnouncement.ClassRef)) ) or (AdminAnnouncement.Id is not null and exists(select * from AnnouncementGroup aar join StudentGroup st on st.GroupRef = aar.GroupRef where st.StudentRef = @callerId and aar.AnnouncementRef = Announcement.Id) ) )"); return(res); } return(null); }