//@"SELECT TO_JSON_STRING(t, true) FROM(SELECT ClubId clubid, ClubName clubname, Location location, isActive FROM Data_Layer.Clubs WHERE DivisionId = 2) t"; public ClubsService() { var bqq = new BigQueryQuery(); var client = bqq.CreateClient(); var job = bqq.CreateQueryJob(client, query); Results.Add(bqq.GetBigQueryResults(client, job)); }
public EventService(int clubid, int?studioid = null, int?month = null, string keyword = null) { string query = @"SELECT EventId eventid, e.ClubId clubid, e.Description description, Date date, TimeFrom time, s.StudioId studioid, EmployeeId personnelid, NonMemberFlag nonmember, CanBook canbook, CAST(NonMemberAmount as FLOAT64) nonmemberamount, CAST(MemberAmount as FLOAT64) memberamount, Attendance attendance FROM Data_Layer.Events e INNER JOIN Data_Layer.Resources r ON e.ResourceId = r.ResourceId INNER JOIN Data_Layer.Studios s ON r.StudioId = s.StudioId WHERE DATETIME (e.Date, TimeFrom) >= CURRENT_DATETIME() and e.ClubId = " + clubid.ToString(); if (studioid != null && month != null && keyword != null) { query = query + " and s.StudioId = " + studioid.ToString() + " and EXTRACT( MONTH, DATETIME(e.Date) ) = " + month.ToString() + " and e.Description LIKE '%" + keyword + "%"; } else if (studioid != null) { query = query + " and s.StudioId = " + studioid.ToString(); } else if (month != null) { query = query + " and EXTRACT( MONTH, DATETIME(e.Date) ) = " + month.ToString(); } else if (keyword != null) { query = query + " and e.Description LIKE '%" + keyword + "%"; } var bqq = new BigQueryQuery(); var client = bqq.CreateClient(); var job = bqq.CreateQueryJob(client, query); Results.Add(bqq.GetBigQueryResults(client, job)); }
public PersonnelService(int clubid, int?studioid = null, int?personnelid = null, string personneltype = null) { string query = @"SELECT Employees.EmployeeId employeeid, CONCAT(FirstName,' ',LastName) employeename, Employees.ClubId clubid, ARRAY_CONCAT( ARRAY( SELECT DISTINCT Resources.StudioId as Studios FROM Data_Layer.Events INNER JOIN Data_Layer.Resources ON Resources.ResourceId = Events.ResourceId WHERE Events.Date = CURRENT_DATE() and Events.EmployeeId = Employees.EmployeeId), ARRAY( SELECT DISTINCT Resources.StudioId as Studios FROM Data_Layer.Classes INNER JOIN Data_Layer.Resources ON Resources.ResourceId = Classes.ResourceId WHERE Classes.Date = CURRENT_DATE() and Classes.EmployeeId = Employees.EmployeeId) ) Studios, Employees.JobTitleId jobtitleid, CASE WHEN Employees.ClubID = 30 THEN RTRIM(JobTitles.FrenchName) ELSE RTRIM(JobTitles.EnglishName) END jobtitle FROM Data_Layer.Employees INNER JOIN Data_Layer.JobTitles ON JobTitles.JobTitleId = Employees.JobTitleId WHERE Employees.ClubId = " + clubid.ToString(); string queryStudio = " and " + studioid.ToString() + " IN UNNEST(a.Studios)"; string queryEmployee = " and Employees.EmployeeId = " + personnelid.ToString(); string queryJob = " and RTRIM(JobTitles.EnglishName) = " + personneltype; if (studioid != null && personnelid != null && personneltype != null) { query = query + queryStudio + queryEmployee + queryJob; } else if (studioid != null && personnelid != null) { query = query + queryStudio + queryEmployee; } else if (studioid != null && personneltype != null) { query = query + queryStudio + queryJob; } else if (personneltype != null && personnelid != null) { query = query + queryEmployee + queryJob; } else if (studioid != null) { query = query + queryStudio; } else if (personnelid != null) { query = query + queryEmployee; } else if (personneltype != null) { query = query + queryJob; } var bqq = new BigQueryQuery(); var client = bqq.CreateClient(); var job = bqq.CreateQueryJob(client, query); Results.Add(bqq.GetBigQueryResults(client, job)); }