//@"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));
}