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