Example #1
0
        //@"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));
        }
Example #2
0
        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));
        }
Example #3
0
        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));
        }