/** * @param null $year * * @return array * * @throws \Google_Exception */ public function getAnalyticsReport($year = null) { $season = new Season($year); $client = new Google_Client(); $credentials = $client->loadServiceAccountJson($this->clientSecret, 'https://www.googleapis.com/auth/analytics.readonly'); $client->setAssertionCredentials($credentials); if ($client->getAuth()->isAccessTokenExpired()) { $client->getAuth()->refreshTokenWithAssertion(); } $analytics = new Google_Service_Analytics($client); $analyticsViewId = $this->viewId; $startDate = $season->getStart()->format('Y-m-d'); $endDate = $season->getEnd()->format('Y-m-d'); $metrics = 'ga:sessions'; $gaParameters = new GaParameters($analytics, $analyticsViewId, $startDate, $endDate, $metrics); return ['countries' => $this->getTopCountries($gaParameters)->rows, 'language' => $this->getTopLanguages($gaParameters), 'deviceCategory' => $this->getDeviceCategory($gaParameters)->rows, 'browser' => $this->getBrowsers($gaParameters)->rows]; }
/** * @param Season $season * * @return mixed */ private function countWishlists(Season $season) { $query = $this->dbal->createQueryBuilder()->select('count(p.id) AS wishlistCount')->from('Pool', 'p')->innerJoin('p', 'Entry', 'e', 'p.id = e.poolId')->where('p.sentdate >= :firstDay')->andWhere('p.sentdate < :lastDay')->andWhere('e.wishlist_updated = TRUE')->setParameter('firstDay', $season->getStart()->format('Y-m-d H:i:s'))->setParameter('lastDay', $season->getEnd()->format('Y-m-d H:i:s')); return $query->execute()->fetchAll(); }
/** * @param Season $season * * @return mixed */ public function queryDataForMonthlyPoolChart(Season $season) { $query = $this->dbal->createQueryBuilder()->select('count(p.id) AS accumulatedPoolCountByMonth, p.sentdate AS month')->from('Pool', 'p')->where('p.sentdate >= :firstDay')->andWhere('p.sentdate < :lastDay')->groupBy('month(p.sentdate)')->orderBy('month(p.sentdate) < 4, month(p.sentdate)')->setParameter('firstDay', $season->getStart()->format('Y-m-d H:i:s'))->setParameter('lastDay', $season->getEnd()->format('Y-m-d H:i:s')); return $query->execute()->fetchAll(); }
/** * @param Season $season * * @return mixed */ private function queryIpv6Records(Season $season) { $query = $this->dbal->createQueryBuilder()->select('count(e.ipv6) AS ipv6Count')->from('Pool', 'p')->innerJoin('p', 'Entry', 'e', 'p.id = e.poolId')->where('e.ipv6 IS NOT NULL')->andWhere('p.sentdate >= :firstDay')->andWhere('p.sentdate < :lastDay')->setParameter('firstDay', $season->getStart()->format('Y-m-d H:i:s'))->setParameter('lastDay', $season->getEnd()->format('Y-m-d H:i:s')); return $query->execute()->fetch(); }
/** * @param Season $season * * @return mixed */ public function fetchParticipantEmailsForExport(Season $season) { return $this->dbal->executeQuery("\n SELECT e.name, e.email, e.poolId\n FROM Pool p\n JOIN Entry e ON p.id = e.poolId\n WHERE p.sentdate >= :firstDay\n AND p.sentdate < :lastDay\n AND e.poolAdmin = 0\n GROUP BY e.email\n INTO OUTFILE :location\n FIELDS TERMINATED BY ','\n ENCLOSED BY '\"'\n LINES TERMINATED BY '\\n'", ['firstDay' => $season->getStart()->format('Y-m-d H:i:s'), 'lastDay' => $season->getEnd()->format('Y-m-d H:i:s'), 'location' => $this->rootDirectory . '/../export/participant/' . date('Y-m-d H.i.s') . '_participants.csv']); }