Symfony Profiler

<?php
namespace LaunchPad\Bundle\LaunchPadBundle\Base\Service;
use Doctrine\ORM\QueryBuilder;
use LaunchPad\Bundle\LaunchPadBundle\Base\Entity\Payment\TopUpRequest;
use LaunchPad\Bundle\LaunchPadBundle\Base\Entity\Transaction\Transaction;
use Symfony\Component\HttpFoundation\Response;
class StatsService extends BaseService
{
public function getUserStats()
{
return [
'total' => $this->getTotalUsers(),
'lastWeek' => $this->getLastWeekUsers(),
'lastMonth' => $this->getLastMonthUsers(),
'lastYear' => $this->getLastYearUsers()
];
}
/**
* Get user trend stats
*
* @return array
* @throws \Exception
*/
public function getUserTrendStats()
{
$resultYearly = $this->getUserRepository()
->createQueryBuilder('u')
->select('DATE_FORMAT(u.created, \'%b\') as MonthOfYear, count(u) as Total')
->where('u.created >= :end')
->setParameter('end', new \DateTime('-1 year'))
->groupBy( 'MonthOfYear')
->getQuery()
->getArrayResult();
$result = array(
"labels" => array(),
"data" => array(),
);
foreach($resultYearly as $item) {
$result['labels'][] = $item['MonthOfYear'];
$result['data'][] = $item['Total'];
}
return $result;
}
public function getTransactionTrendStats()
{
$resultYearly = $this->getTransactionRepository()
->createQueryBuilder('u')
->select('DATE_FORMAT(u.created, \'%b\') as MonthOfYear, count(u) as Total')
->where('u.created >= :end')
->setParameter('end', new \DateTime('-1 year'))
->groupBy( 'MonthOfYear')
->getQuery()
->getArrayResult();
$result = array(
"labels" => array(),
"data" => array(),
);
// TODO: Move to specific function
$months = [];
$start = (new \DateTime('-11 months'))->modify('first day of this month');
$end = (new \DateTime())->modify('first day of next month');
$interval = \DateInterval::createFromDateString('1 month');
$period = new \DatePeriod($start, $interval, $end);
foreach ($period as $dt) {
$months[] = $dt->format("M");
}
$result['labels'] = $months;
$result['data'] = array_fill(0, count($months), 0);
foreach($resultYearly as $yearSpending){
foreach($result['labels'] as $index => $label) {
if($label == $yearSpending["MonthOfYear"]) {
$result['data'][$index] = $yearSpending["Total"];
}
}
}
return $result;
}
/**
* Get total users
*
* @return int|mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getTotalUsers()
{
$totalUsers = $this->getUserRepository()
->createQueryBuilder('u')
->select('count(u.id) as Total')
->getQuery()
->getSingleScalarResult();
return $totalUsers ?? 0;
}
/**
* Get last week users
*
* @return int|mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLastWeekUsers()
{
$totalUsers = $this->getUserRepository()
->createQueryBuilder('u')
->select('count(u.id) as Total')
->where('u.created >= :end')
->setParameter('end', new \DateTime('-7 days'))
->getQuery()
->getSingleScalarResult();
return $totalUsers ?? 0;
}
/**
* Get last month users
*
* @return int|mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLastMonthUsers()
{
$totalUsers = $this->getUserRepository()
->createQueryBuilder('u')
->select('count(u.id) as Total')
->where('u.created >= :end')
->setParameter('end', new \DateTime('-1 month'))
->getQuery()
->getSingleScalarResult();
return $totalUsers ?? 0;
}
/**
* Get last year users
*
* @return int|mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLastYearUsers()
{
$totalUsers = $this->getUserRepository()
->createQueryBuilder('u')
->select('count(u.id) as Total')
->where('u.created >= :end')
->setParameter('end', new \DateTime('-1 year'))
->getQuery()
->getSingleScalarResult();
return $totalUsers ?? 0;
}
/**
* Get totals
*
* @param $filters
* @return array
* @throws \Doctrine\ORM\NonUniqueResultException
* @throws \Doctrine\ORM\NoResultException
*/
public function getTotals($filters)
{
$fromDate = null;
$toDate = null;
foreach($filters as $filter) {
if(!$filter['value']) continue;
if($filter['name'] == 'fromDate') {
$fromDate = \DateTime::createFromFormat('d/m/Y H:i:s', $filter['value'])->format('Y-m-d H:i:s');
}
if($filter['name'] == 'toDate') {
$toDate = \DateTime::createFromFormat('d/m/Y H:i:s', $filter['value'])->format('Y-m-d H:i:s');
}
}
return array_merge(
$this->getCardStatistics($fromDate, $toDate),
$this->getTransactionStatistics($fromDate, $toDate)
);
}
/**
* @param $filters
* @return Response
* @throws \Doctrine\ORM\NonUniqueResultException
* @throws \Doctrine\ORM\NoResultException
*/
public function downloadTotals($filters)
{
$totals = $this->getTotals($filters);
// Create csv file
$output = fopen('php://temp', 'r+');
foreach ($totals as $label => $value) {
fputcsv($output, [$this->getLabel($label), $value]);
}
rewind($output);
$csvData = '';
while ($line = fgets($output)) {
$csvData .= $line;
}
$csvData .= fgets($output);
$response = new Response();
$response->setContent($csvData);
$response->headers->set('Access-Control-Expose-Headers', 'Content-Disposition');
$response->headers->set('Content-Type', 'text/csv');
$response->headers->set('Content-Disposition', 'attachment; filename="report-totals-' . date('Y-m-d-H-i-s') . '.csv"');
return $response;
}
/**
* Get card statistics
*
* @param null $fromDate
* @param null $toDate
* @return array
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getCardStatistics($fromDate = null, $toDate = null)
{
return [
'orderedCards' => $this->getOrderedCards($fromDate, $toDate),
'activatedCards' => $this->getActivatedCards($fromDate, $toDate),
'loadedCards' => $this->getLoadedCards($fromDate, $toDate),
'uniqueTransactingCards' => $this->getUniqueTransactingCardCount($fromDate, $toDate)
];
}
/**
* Get transaction statistics
*
* @param null $fromDate
* @param null $toDate
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getTransactionStatistics($fromDate = null, $toDate = null)
{
return array_merge(
$this->getPosTransactionStats($fromDate, $toDate),
$this->getAtmTransactionStats($fromDate, $toDate),
$this->getLoadStats($fromDate, $toDate)
);
}
/**
* Get ordered cards
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getOrderedCards($fromDate, $toDate)
{
$query = $this->getPaymentDeviceRepository()
->createQueryBuilder('x')
->select('COUNT(x.id) as orderedCards');
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleScalarResult();
}
/**
* Get activated cards
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getActivatedCards($fromDate, $toDate)
{
$query = $this->getPaymentDeviceRepository()
->createQueryBuilder('x')
->select('COUNT(x.id) as activatedCards')
->where('x.activated IS NOT NULL');
$this->appendDateConditions($query, $fromDate, $toDate, 'activated');
return $query->getQuery()->getSingleScalarResult();
}
/**
* Get loaded cards
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getLoadedCards($fromDate, $toDate)
{
// TODO: Extract public token in GpsWebService and use that to check for unique card!
$query = $this->getGpsServiceCallRepository()
->createQueryBuilder('x')
->select('COUNT(DISTINCT IDENTITY(x.user)) as loadedCards')
->where('x.success = true');
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleScalarResult();
}
/**
* Get unique transacting card count
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getUniqueTransactingCardCount($fromDate, $toDate)
{
$query = $this->getTransactionRepository()
->createQueryBuilder('x')
->select('COUNT(DISTINCT x.paymentDevice) as uniqueTransactingCards');
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleScalarResult();
}
/**
* Get pos transaction stats
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getPosTransactionStats($fromDate, $toDate)
{
$query = $this->getTransactionRepository()
->createQueryBuilder('x')
->select('COUNT(x.id) as posTransactions, ROUND(SUM(x.amount), 2) as posTransactionsValue, ROUND(AVG(x.amount), 2) as posTransactionsAverageValue')
->where('x.group = :posType')
->andWhere('x.status = :completedStatus')
->setParameter('posType', Transaction::GROUP_POS)
->setParameter('completedStatus', Transaction::STATUS_COMPLETED);
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleResult();
}
/**
* Get atm transaction stats
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getAtmTransactionStats($fromDate, $toDate)
{
$query = $this->getTransactionRepository()
->createQueryBuilder('x')
->select('COUNT(x.id) as atmTransactions, ROUND(SUM(x.amount), 2) as atmTransactionsValue, ROUND(AVG(x.amount), 2) as atmTransactionsAverageValue')
->where('x.group = :posType')
->andWhere('x.status = :completedStatus')
->setParameter('posType', Transaction::GROUP_ATM)
->setParameter('completedStatus', Transaction::STATUS_COMPLETED);
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleResult();
}
/**
* Get load stats
*
* @param $fromDate
* @param $toDate
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
private function getLoadStats($fromDate, $toDate)
{
$query = $this->getTopUpRequestRepository()
->createQueryBuilder('x')
->select('COUNT(x.id) as loadsCount, ROUND(SUM(x.amount), 2) as loadsValue, ROUND(AVG(x.amount), 2) as loadsAverageValue')
->andWhere('x.status = :completedStatus')
->setParameter('completedStatus', TopUpRequest::STATUS_SUCCESS);
$this->appendDateConditions($query, $fromDate, $toDate, 'created');
return $query->getQuery()->getSingleResult();
}
/**
* Append date conditions
*
* @param QueryBuilder $query
* @param $fromDate
* @param $toDate
* @param $field
*/
private function appendDateConditions(QueryBuilder $query, $fromDate, $toDate, $field)
{
if($fromDate) {
$query->andWhere('x.' . $field . ' >= :fromDate');
$query->setParameter('fromDate', $fromDate);
}
if($toDate) {
$query->andWhere('x.' . $field . ' <= :toDate');
$query->setParameter('toDate', $toDate);
}
}
/**
* Get label for stat field
*
* @param $label
* @return mixed
*/
private function getLabel($label)
{
$mapping = [
"activatedCards" => "Activated Cards",
"atmTransactions" => "ATM Transaction Count",
"atmTransactionsAverageValue" => "ATM Transaction Average Value",
"atmTransactionsValue" => "Atm Transactions Value",
"donationsAverageValue" => "Donations Average Value",
"donationsCount" => "Donations Count",
"donationsTotalValue" => "Donations Total Value",
"undistributedDonationsAverageValue" => "Undistributed Donations Average Value",
"undistributedDonationsCount" => "Undistributed Donations Count",
"undistributedDonationsTotalValue" => "Undistributed Donations Total Value",
"distributedDonationsAverageValue" => "Distributed Donations Average Value",
"distributedDonationsCount" => "Distributed Donations Count",
"distributedDonationsTotalValue" => "Distributed Donations Total Value",
"giftAidNumberOfSignedUp" => "Number of Signed Up for GiftAid",
"giftAidNumberOfOptedOut" => "Number of opted out from GiftAid",
"giftAidPercentageOfSignedUp" => "Percentage of Signed Up for GiftAid",
"giftAidTotalValue" => "GiftAid Total Collected",
"giftAidAverageValue" => "Average value of GiftAid donation",
"loadedCards" => "Card Loads",
"loadsAverageValue" => "Average Card Load Value",
"loadsCount" => "Loads Count",
"loadsValue" => "Value Loaded",
"orderedCards" => "Ordered Cards",
"posTransactions" => "POS Transactions Count",
"posTransactionsAverageValue" => "Pos Transaction Average Value",
"posTransactionsValue" => "POS Transactions Value",
"schoolsRegisteredCount" => "Registered Svhools",
"uniqueTransactingCards" => "Unique Transacting Cards Count"
];
if(isset($mapping[$label])) return $mapping[$label];
return $label;
}
}

vendor/launchpad/backend/src/Base/Service/StatsService.php line 39