public static string LimitOffsetCheck(ScheduleFilters cf) { if (cf.limit == null && cf.offset == null) { return(""); } else { if (cf.limit == null && cf.offset != null) { return(""); } else if (cf.limit != null && cf.offset == null) { string queryLimit = " LIMIT " + cf.limit.ToString(); return(queryLimit); } else { string queryLimit = " LIMIT " + cf.limit.ToString() + " OFFSET " + cf.offset.ToString(); return(queryLimit); } } }
public ScheduleService(int clubid, ScheduleFilters classFilters) { string query = BuildQuery(clubid, classFilters); var bqq = new BigQueryQuery(); var client = bqq.CreateClient(); var job = bqq.CreateQueryJob(client, query); Results.Add(bqq.GetBigQueryResults(client, job)); }
public static string ClassTypeCheck(ScheduleFilters cf) { if (cf.classtypeid == null) { return(""); } else { string classTypeQuery = " and ClassTypes.ClassTypeId = " + cf.classtypeid.ToString(); return(classTypeQuery); } }
public static string KeywordCheck(ScheduleFilters cf) { if (cf.keyword == null) { return(""); } else { string queryKeyword = " and LOWER(ClassCategories.CategoryName) LIKE LOWER('%" + cf.keyword.ToString() + "%')"; return(queryKeyword); } }
public static string StatusCheck(ScheduleFilters cf) { if (cf.status == null) { return(""); } else { string queryStatus = " and Classes.Status = " + cf.status; return(queryStatus); } }
public static string ActivityTypeCheck(ScheduleFilters cf) { if (cf.activitytype == null) { return(""); } else { string queryActivityType = " and MovementTypes.MovementTypeId = " + cf.activitytype; return(queryActivityType); } }
public static string PersonnelCheck(ScheduleFilters cf) { if (cf.instructorid == null) { return(""); } else { string queryPersonnel = " and Classes.EmployeeId in (" + string.Join(",", cf.instructorid) + ")"; return(queryPersonnel); } }
public static string ClassCheck(ScheduleFilters cf) { if (cf.classid == null) { return(""); } else { string queryClasses = " and Classes.ClassId in (" + string.Join(",", cf.classid) + ")"; return(queryClasses); } }
public static string ConceptCheck(ScheduleFilters cf) { if (cf.conceptid == null) { return(""); } else { string queryConcept = " and Concepts.ConceptId in (" + string.Join(",", cf.conceptid) + ")"; return(queryConcept); } }
public ActionResult GetSchedules(int clubid, string activitytype, string status, string keyword, string datefrom = null, string dateto = null, int?[] conceptid = null, int?[] personnelid = null, int?limit = null, int?offset = null, int?classtypeid = null, int?language = 0) { _logger.LogInformation("Logging Info"); ScheduleFilters classFilters = RequestHelper.GetClassesFilters(Request); List <Schedule> classes = new ScheduleService(clubid, classFilters).GetClasses(); if (classes.Count() == 0) { return(NotFound("No result")); } else { return(Ok(classes)); } }
public void ShouldConsolidateResultsOfEachFilter() { var scheduleRequest = MockRepository.GenerateMock <ScheduleRequest>(); var filter1 = MockRepository.GenerateMock <IScheduleFilter>(); var filter2 = MockRepository.GenerateMock <IScheduleFilter>(); var scheduleFilters = new ScheduleFilters(new List <IScheduleFilter> { filter1, filter2, }); var originalModels = MockRepository.GenerateMock <IList <BillModel> >(); var firstFilterResults = MockRepository.GenerateMock <IList <BillModel> >(); var secondFilterResults = MockRepository.GenerateMock <IList <BillModel> >(); filter1.Stub(f => f.Filter(originalModels, scheduleRequest)).Return(firstFilterResults); filter2.Stub(f => f.Filter(firstFilterResults, scheduleRequest)).Return(secondFilterResults); var billModels = scheduleFilters.Filter(originalModels, scheduleRequest); Assert.That(billModels, Is.EqualTo(secondFilterResults)); }
public static string DateCheck(ScheduleFilters cf) { DateTime df = Convert.ToDateTime(cf.datefrom); DateTime dt = Convert.ToDateTime(cf.dateto); if (cf.datefrom == null && cf.dateto == null) { return(""); } else if (cf.datefrom != null && cf.dateto == null) { dt = Convert.ToDateTime(cf.datefrom); string queryDates = " and Classes.Date >= '" + df.ToString("yyyy-MM-dd") + "' and Classes.Date < DATE_ADD('" + dt.ToString("yyyy-MM-dd") + "',INTERVAL 1 DAY)"; return(queryDates); } else { string queryDates = " and Classes.Date >= '" + df.ToString("yyyy-MM-dd") + "' and Classes.Date < DATE_ADD('" + dt.ToString("yyyy-MM-dd") + "',INTERVAL 1 DAY)"; return(queryDates); } }
public static ScheduleFilters GetClassesFilters(HttpRequest request) { ScheduleFilters _classFilters = new ScheduleFilters { datefrom = null, dateto = null, conceptid = null, classid = null, instructorid = null, activitytype = null, keyword = null, limit = null, offset = null, classtypeid = null, language = 0 }; Dictionary <string, string> valuePairs = ParseUri(request); if (valuePairs.Count > 0) { if (valuePairs.TryGetValue("datefrom", out string f)) { _classFilters.datefrom = valuePairs["datefrom"].ToString(); } if (valuePairs.TryGetValue("dateto", out string t)) { _classFilters.dateto = valuePairs["dateto"].ToString(); } if (valuePairs.TryGetValue("conceptid", out string s)) { _classFilters.conceptid = valuePairs["conceptid"].ToString(); } if (valuePairs.TryGetValue("classid", out string c)) { _classFilters.classid = valuePairs["classid"].ToString(); } if (valuePairs.TryGetValue("instructorid", out string p)) { _classFilters.instructorid = valuePairs["instructorid"].ToString(); } if (valuePairs.TryGetValue("activitytype", out string at)) { _classFilters.activitytype = valuePairs["activitytype"].ToString(); } if (valuePairs.TryGetValue("keyword", out string k)) { _classFilters.keyword = valuePairs["keyword"].ToString(); } if (valuePairs.TryGetValue("limit", out string l)) { _classFilters.limit = Convert.ToInt32(valuePairs["limit"]); } if (valuePairs.TryGetValue("offset", out string o)) { _classFilters.offset = Convert.ToInt32(valuePairs["offset"]); } if (valuePairs.TryGetValue("classtypeid", out string ct)) { _classFilters.classtypeid = Convert.ToInt32(valuePairs["classtypeid"]); } if (valuePairs.TryGetValue("language", out string lang)) { _classFilters.language = Convert.ToInt32(valuePairs["language"]); } return(_classFilters); } else { return(_classFilters); } }
public static string BuildQuery(int clubid, ScheduleFilters cf) { string query = ""; string groupQuery = ""; if (cf.language == 0 || cf.language == 2) { query = @"SELECT Classes.ClassId classid, Classes.ClubId clubid, IFNULL(ClassCategories.CategoryName,'') name, IFNULL(ClassCategories.Description, '') shortDescription, IFNULL(MovementTypes.MovementTypeId, 0) activityTypeId, ARRAY_AGG (Distinct IFNULL(Classes.EmployeeId, 0)) personnelid, ARRAY_AGG (Distinct IFNULL(CONCAT(Employees.FirstName, ' ', Employees.LastName),'')) personnelName, IFNULL(DATETIME(Classes.Date, Classes.TimeFrom), '1900-01-01') startDateTime, IFNULL(DATETIME(Classes.Date, Classes.TimeTo), '1900-01-01') endDateTime, IFNULL(ClassSchedules.ClassScheduleType,'') activityCode, IFNULL(Concepts.ConceptId, 0) conceptId, IFNULL(Concepts.Concept,'') conceptName, IFNULL(Classes.Attendance,0) booked, CASE WHEN ClassSchedules.ClassScheduleType = 'PRG' THEN ClassSchedules.DateFrom ELSE DATE('1900-01-01') END sessionBeginDate, CASE WHEN ClassSchedules.ClassScheduleType = 'PRG' THEN ClassSchedules.DateTo ELSE DATE('1900-01-01') END sessionEndDate, CASE WHEN ClassCategories.NonMemberFlag = false THEN 'Member Only' ELSE 'Non-Member Allowed' END memberStatus, CASE WHEN ClassSchedules.MemberAmount > 0 then true WHEN ClassSchedules.NonMemberAmount > 0 then true ELSE false END isPaid, ARRAY_AGG (Distinct IFNULL(Classes.resourceId,0)) resourceId, IFNULL(Classes.Capacity,0) attendingCapacity, IFNULL(ClassSchedules.CSIScheduleGUID,'') scheduleGUID, IFNULL(ClassCategories.ClassTypeId,0) ClassTypeId, IFNULL(ClassCategories.FamilyFlag, false) FamilyFlag, IFNULL(ClassCategories.DropInFlag,false) isDropIn, IFNULL(Classes.Intensity, '') Intensity FROM Data_Layer_Test.Classes INNER JOIN Data_Layer_Test.ClassSchedules on Classes.ClassScheduleId = ClassSchedules.ClassScheduleId LEFT JOIN Data_Layer_Test.ClassCategories on ClassCategories.ClassCategoryId = ClassSchedules.ClassCategoryId and ClassCategories.ClubId = Classes.ClubId LEFT JOIN Data_Layer_Test.MovementTypes on MovementTypes.MovementTypeId = ClassCategories.MovementTypeId LEFT JOIN Data_Layer_Test.ClassTypes on ClassTypes.ClassTypeId = ClassCategories.ClassTypeId and ClassTypes.CSIServiceId = ClassCategories.ClassCategoryId LEFT JOIN Data_Layer_Test.Concepts on Concepts.ConceptId = ClassTypes.ConceptId and Concepts.ClubId = ClassTypes.ClubId LEFT JOIN Data_Layer_Test.Employees on Employees.CSIEmployeeId = Classes.EmployeeId WHERE Classes.Date >= CURRENT_DATE() and Classes.ClubId = " + clubid.ToString(); groupQuery = @" GROUP BY ClassId, ClubId, CategoryName, Description, MovementTypes.MovementTypeId, Date, TimeFrom, TimeTo, ClassScheduleType, ConceptId, Concept, Attendance, ClassScheduleType, ClassSchedules.DateFrom, ClassSchedules.DateTo, NonMemberFlag, MemberAmount, NonMemberAmount, Capacity, CSIScheduleGUID, ClassTypeId, FamilyFlag, DropInFlag, Intensity order by classid "; } else { query = @"SELECT Classes.ClassId classid, Classes.ClubId clubid, IFNULL(ClassCategories.FrenchCategoryName,'') name, IFNULL(ClassCategories.FrenchDescription, '') shortDescription, IFNULL(MovementTypes.MovementTypeId, 0) activityTypeId, ARRAY_AGG (Distinct IFNULL(Classes.EmployeeId, 0)) personnelid, ARRAY_AGG (Distinct IFNULL(CONCAT(Employees.FirstName, ' ', Employees.LastName),'')) personnelName, IFNULL(DATETIME(Classes.Date, Classes.TimeFrom), '1900-01-01') startDateTime, IFNULL(DATETIME(Classes.Date, Classes.TimeTo), '1900-01-01') endDateTime, IFNULL(ClassSchedules.ClassScheduleType,'') activityCode, IFNULL(Concepts.ConceptId, 0) conceptId, IFNULL(Concepts.FrenchConcept,'') conceptName, IFNULL(Classes.Attendance,0) booked, CASE WHEN ClassSchedules.ClassScheduleType = 'PRG' THEN ClassSchedules.DateFrom ELSE DATE('1900-01-01') END sessionBeginDate, CASE WHEN ClassSchedules.ClassScheduleType = 'PRG' THEN ClassSchedules.DateTo ELSE DATE('1900-01-01') END sessionEndDate, CASE WHEN ClassCategories.NonMemberFlag = false THEN 'Member Only' ELSE 'Non-Member Allowed' END memberStatus, CASE WHEN ClassSchedules.MemberAmount > 0 then true WHEN ClassSchedules.NonMemberAmount > 0 then true ELSE false END isPaid, ARRAY_AGG (Distinct IFNULL(Classes.resourceId,0)) resourceId, IFNULL(Classes.Capacity,0) attendingCapacity, IFNULL(ClassSchedules.CSIScheduleGUID,'') scheduleGUID, IFNULL(ClassCategories.ClassTypeId,0) ClassTypeId, IFNULL(ClassCategories.FamilyFlag, false) FamilyFlag, IFNULL(ClassCategories.DropInFlag,false) isDropIn, IFNULL(Classes.Intensity, '') Intensity FROM Data_Layer_Test.Classes INNER JOIN Data_Layer_Test.ClassSchedules on Classes.ClassScheduleId = ClassSchedules.ClassScheduleId LEFT JOIN Data_Layer_Test.ClassCategories on ClassCategories.ClassCategoryId = ClassSchedules.ClassCategoryId and ClassCategories.ClubId = Classes.ClubId LEFT JOIN Data_Layer_Test.MovementTypes on MovementTypes.MovementTypeId = ClassCategories.MovementTypeId LEFT JOIN Data_Layer_Test.ClassTypes on ClassTypes.ClassTypeId = ClassCategories.ClassTypeId and ClassTypes.CSIServiceId = ClassCategories.ClassCategoryId LEFT JOIN Data_Layer_Test.Concepts on Concepts.ConceptId = ClassTypes.ConceptId and Concepts.ClubId = ClassTypes.ClubId LEFT JOIN Data_Layer_Test.Employees on Employees.CSIEmployeeId = Classes.EmployeeId WHERE Classes.Date >= CURRENT_DATE() and Classes.ClubId = " + clubid.ToString(); groupQuery = @" GROUP BY ClassId, ClubId, FrenchCategoryName, FrenchDescription, MovementTypes.MovementTypeId, Date, TimeFrom, TimeTo, ClassScheduleType, ConceptId, FrenchConcept, Attendance, ClassScheduleType, ClassSchedules.DateFrom, ClassSchedules.DateTo, NonMemberFlag, MemberAmount, NonMemberAmount, Capacity, CSIScheduleGUID, ClassTypeId, FamilyFlag, DropInFlag, Intensity order by classid "; } if (cf == null) { return(query + groupQuery); } else { string d = DateCheck(cf); string s = ConceptCheck(cf); string c = ClassCheck(cf); string p = PersonnelCheck(cf); string at = ActivityTypeCheck(cf); string st = StatusCheck(cf); string k = KeywordCheck(cf); string lo = LimitOffsetCheck(cf); string ct = ClassTypeCheck(cf); return(query = query + d + s + c + p + at + st + k + ct + groupQuery + lo); } }