/**
* @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']);
}
