public EventService(int clubid, EventsFilters eventsFilters, int?eventid = null)
{
string query = BuildQuery(clubid, eventsFilters, eventid);
var bqq = new BigQueryQuery();
var client = bqq.CreateClient();
var job = bqq.CreateQueryJob(client, query);
Results.Add(bqq.GetBigQueryResults(client, job));
}
public async Task <ActionResult <List <Event> > > Get([FromQuery] EventsFilters filters)
{
var result = await _eventService
.GetFilteredAsync(filters?.Limit ?? 20,
filters?.Source ?? string.Empty,
filters?.Days ?? 365,
filters?.OrderField ?? OrderField.Id,
filters?.OrderType ?? OrderType.Asc);
return(result);
}
public static string ResourceCheck(EventsFilters ef)
{
if (ef.resourceid == null)
{
return("");
}
else
{
string queryResource = " and r.ResourceId = " + ef.resourceid.ToString();
return(queryResource);
}
}
public static string KeywordCheck(EventsFilters ef)
{
if (ef.keyword == null)
{
return("");
}
else
{
string queryKeyword = " and cc.CategoryName LIKE '%" + ef.keyword.ToString() + "%'";
return(queryKeyword);
}
}
public static string StudioCheck(EventsFilters ef)
{
if (ef.conceptid == null)
{
return("");
}
else
{
string queryStudio = " and c.ConceptId = " + ef.conceptid.ToString();
return(queryStudio);
}
}
public static EventsFilters GetEventFilters(HttpRequest request)
{
EventsFilters _eventsFilters = new EventsFilters
{
conceptid = null,
resourceid = null,
keyword = null,
datefrom = null,
dateto = null,
language = 0
};
Dictionary <string, string> valuePairs = ParseUri(request);
if (valuePairs.Count > 0)
{
if (valuePairs.TryGetValue("conceptid", out string s))
{
_eventsFilters.conceptid = Convert.ToInt32(valuePairs["conceptid"]);
}
if (valuePairs.TryGetValue("resourceid", out string r))
{
_eventsFilters.resourceid = Convert.ToInt32(valuePairs["resourceid"]);
}
if (valuePairs.TryGetValue("datefrom", out string f))
{
_eventsFilters.datefrom = valuePairs["datefrom"].ToString();
}
if (valuePairs.TryGetValue("dateto", out string t))
{
_eventsFilters.dateto = valuePairs["dateto"].ToString();
}
if (valuePairs.TryGetValue("keyword", out string k))
{
_eventsFilters.keyword = valuePairs["keyword"];
}
if (valuePairs.TryGetValue("language", out string l))
{
_eventsFilters.language = Convert.ToInt32(valuePairs["language"]);
}
return(_eventsFilters);
}
else
{
return(_eventsFilters);
}
}
public static string DateCheck(EventsFilters ef)
{
DateTime df = Convert.ToDateTime(ef.datefrom);
DateTime dt = Convert.ToDateTime(ef.dateto);
if (ef.datefrom == null)
{
return("");
}
else if (ef.datefrom != null && ef.dateto == null)
{
dt = Convert.ToDateTime(ef.datefrom);
string queryDate = " and DATETIME(e.Date, TimeFrom) >= '" + df.ToString("yyyy-MM-dd") + "' and DATETIME(e.Date, TimeFrom ) <= " + dt.ToString("yyyy-MM-dd");
return(queryDate);
}
else
{
string queryDateRange = " and DATETIME(e.Date, TimeFrom) >= '" + df.ToString("yyyy-MM-dd") + "' and DATETIME(e.Date, TimeFrom ) < DATETIME_ADD('" + dt.ToString("yyyy-MM-dd") + "', INTERVAL 1 DAY)";
return(queryDateRange);
}
}
public static string BuildQuery(int clubid, EventsFilters ef, int?eventid = null)
{
string mainquery = "";
string groupquery = "";
if (ef.language == 0 || ef.language == 2)
{
mainquery = @" select e.eventid,
IFNULL(cc.CategoryName, '') name,
IFNULL(cc.Description, '') description,
DATETIME(e.Date, e.TimeFrom) startdate,
DATETIME(e.Date, e.TimeTo) enddate,
CAST(MemberAmount as FLOAT64) memberamount,
CAST(NonMemberAmount as FLOAT64) nonmemberamount,
e.CanBook canbook,
e.ClubId clubid,
cl.ClubName location,
e.Attendance attendance,
IFNULL(e.Capacity, 20) capacity,
ARRAY_AGG(DISTINCT IFNULL(em.employeeid, 0)) personnelid,
cs.CSIScheduleGUID scheduleGUID,
ARRAY_AGG(DISTINCT IFNULL(cc.MovementTypeId,0)) activityTypeId,
cc.FamilyFlag familyFlag,
ARRAY_AGG(DISTINCT IFNULL(e.resourceId, 0)) resourceId,
c.conceptId,
IFNULL(c.Concept, '') conceptName
FROM Data_Layer_Test.Events e
Left JOIN Data_Layer_Test.ClassSchedules cs
ON e.ClassScheduleId = cs.ClassScheduleId
left JOIN Data_Layer_Test.ClassCategories cc
ON cc.ClassCategoryId = cs.ClassCategoryId
LEFT JOIN Data_Layer_Test.ClassTypes ct
ON ct.ClassTypeId = cc.ClassTypeId and ct.CSIServiceId = cc.ClassCategoryId
LEFT JOIN Data_Layer_Test.Concepts c
ON c.ConceptId = ct.ConceptId
LEFT JOIN Data_Layer_Test.Resources r
ON e.ResourceId = r.ResourceId
LEFT JOIN Data_Layer_Test.Employees em
ON em.CSIEmployeeId = e.EmployeeId
LEFT JOIN Data_Layer_Test.Clubs cl
ON e.ClubId = cl.ClubId
where DATETIME(e.Date, e.TimeFrom) >= CURRENT_DATETIME()
and e.Clubid = " + clubid.ToString();
groupquery = @" group by e.eventid,
cc.CategoryName,
cc.Description,
e.Date,
e.TimeFrom,
e.TimeTo,
cs.MemberAmount,
cs.NonMemberAmount,
e.CanBook,
e.ClubId,
cl.ClubName,
e.Attendance,
e.Capacity,
cs.CSIScheduleGUID,
cc.FamilyFlag,
c.conceptId,
c.Concept
order by e.eventid";
}
else
{
mainquery = @" select e.eventid,
IFNULL(cc.FrenchCategoryName, '') name,
IFNULL(cc.FrenchDescription, '') description,
DATETIME(e.Date, e.TimeFrom) startdate,
DATETIME(e.Date, e.TimeTo) enddate,
CAST(MemberAmount as FLOAT64) memberamount,
CAST(NonMemberAmount as FLOAT64) nonmemberamount,
e.CanBook canbook,
e.ClubId clubid,
cl.ClubName location,
e.Attendance attendance,
IFNULL(e.Capacity, 20) capacity,
ARRAY_AGG(DISTINCT IFNULL(em.employeeid, 0)) personnelid,
cs.CSIScheduleGUID scheduleGUID,
ARRAY_AGG(DISTINCT IFNULL(cc.MovementTypeId,0)) activityTypeId,
cc.FamilyFlag familyFlag,
ARRAY_AGG(DISTINCT IFNULL(e.resourceId, 0)) resourceId,
c.conceptId,
IFNULL(c.FrenchConcept, '') conceptName
FROM Data_Layer_Test.Events e
Left JOIN Data_Layer_Test.ClassSchedules cs
ON e.ClassScheduleId = cs.ClassScheduleId
left JOIN Data_Layer_Test.ClassCategories cc
ON cc.ClassCategoryId = cs.ClassCategoryId
LEFT JOIN Data_Layer_Test.ClassTypes ct
ON ct.ClassTypeId = cc.ClassTypeId and ct.CSIServiceId = cc.ClassCategoryId
LEFT JOIN Data_Layer_Test.Concepts c
ON c.ConceptId = ct.ConceptId
LEFT JOIN Data_Layer_Test.Resources r
ON e.ResourceId = r.ResourceId
LEFT JOIN Data_Layer_Test.Employees em
ON em.CSIEmployeeId = e.EmployeeId
LEFT JOIN Data_Layer_Test.Clubs cl
ON e.ClubId = cl.ClubId
where DATETIME(e.Date, e.DateFrom) >= CURRENT_DATETIME()
and e.Clubid = " + clubid.ToString();
groupquery = @" group by e.eventid,
cc.FrenchCategoryName,
cc.FrenchDescription,
e.Date,
e.TimeFrom,
e.TimeTo,
cs.MemberAmount,
cs.NonMemberAmount,
e.CanBook,
e.ClubId,
cl.ClubName,
e.Attendance,
e.Capacity,
cs.CSIScheduleGUID,
cc.FamilyFlag,
c.conceptId,
c.FrenchConcept
order by e.eventid";
}
if (ef == null)
{
if (eventid == null)
{
return(mainquery + groupquery);
}
else
{
string e = EventCheck(eventid);
return(mainquery + e + groupquery);
}
}
else
{
string s = StudioCheck(ef);
string d = DateCheck(ef);
string k = KeywordCheck(ef);
string r = ResourceCheck(ef);
string e = EventCheck(eventid);
return(mainquery + s + d + k + r + e + groupquery);
}
}
