<?php
namespace App\Controller;
use Ob\HighchartsBundle\Highcharts\Highchart;
use JMS\DiExtraBundle\Annotation as DI;
//use JMS\Payment\CoreBundle\Entity\Payment;
//use JMS\Payment\CoreBundle\PluginController\Result;
//use JMS\Payment\CoreBundle\Plugin\Exception\ActionRequiredException;
//use JMS\Payment\CoreBundle\Plugin\Exception\Action\VisitUrl;
use Symfony\Component\Routing\Annotation\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Symfony\Component\HttpFoundation\RedirectResponse;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Knp\Bundle\SnappyBundle\Snappy\Response\PdfResponse;
use Symfony\Component\HttpFoundation\Session\SessionInterface;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Component\Security\Http\Authentication\AuthenticationUtils;
use Doctrine\Common\Collections\Criteria;
use Symfony\Component\Security\Core\Authorization\AuthorizationChecker;
use Symfony\Component\Security\Core\Exception\AccessDeniedException;
use App\Repository\EventsRepository;
use App\Entity\Orders;
use App\Entity\OrderItems;
use App\Entity\OrderTickets;
use App\Entity\Events;
use App\Entity\EventDates;
use App\Entity\EventTickets;
use App\Entity\EventExtra;
use App\Entity\Sites;
use App\Entity\Language;
use App\Entity\Coupons;
use App\Service\EventStats;
/**
* @Route("/",
* name="stats_",
* condition="request.headers.get('Host') matches '/stats\./i'"
* )
*/
class StatsController extends Controller {
private $eventStats;
private $searchFilter = '';
public function __construct(EventStats $eventStats) {
$this->eventStats = $eventStats;
}
/**
* @Route("/", name = "home")
*/
public function homeAction(Request $request) {
return $this->redirectToRoute('stats_dashboard');
}
private function getEvents() {
//$request = $this->get('request');
$user = $this->getUser();
$dates = new ArrayCollection();
//$key = $request->query->get('q');
if($user->isGranted('ROLE_ADMIN')) {
// Very well... All events are granted!
$datesObj = $this->getDoctrine()->getManager()->getRepository(EventDates::class)->findAll(array(), array('date_start' => 'DESC'));
foreach($datesObj as $date) {
$dates->add($date);
}
// All event dates, sorted by start date descending
} else {
$manager = $this->getDoctrine()->getManager();
// Fetch the events from the user object...
$userEvents = $this->getUser()->getEvents();
if($userEvents->count() > 0) {
foreach($userEvents as $event) {
foreach($event->getDates() as $date) {
if(!$dates->contains($date)) {
$dates->add($date);
}
}
}
}
$userOrganisers = $this->getUser()->getOrganisers();
if($userOrganisers->count() > 0) {
foreach($userOrganisers as $organiser) {
foreach($organiser->getEvents() as $event) {
foreach($event->getDates() as $date) {
if(!$dates->contains($date)) {
$dates->add($date);
}
}
}
}
}
$userArtists = $this->getUser()->getArtists();
if($userArtists->count() > 0) {
foreach($userArtists as $artist) {
foreach($artist->getEvents() as $event) {
foreach($event->getDates() as $date) {
if(!$dates->contains($date)) {
$dates->add($date);
}
}
}
}
}
/*$userLocations = $this->getUser()->getLocations();
if($userLocations->count() > 0) {
foreach($userLocations as $location) {
foreach($location->getEvents() as $event) {
foreach($event->getEvents()->getDates() as $date) {
if(!$dates->contains($date)) {
$dates->add($date);
}
}
}
}
}*/
$userSites = $this->getUser()->getSites();
if($userSites->count() > 0) {
foreach($userSites as $site) {
// If the site has a location filter active... Do so here too!
$locationCriteria = false; // Preset so no errors will be thrown...
if($site->getFilterByVenue()) {
$location = $site->getLocation();
$locationCriteria = Criteria::create()->andWhere(Criteria::expr()->eq("location", $location));
}
foreach($site->getEvents() as $event) {
if($locationCriteria instanceof Criteria) {
$event_dates = $event->getEvents()->getDates()->matching($locationCriteria);
} else {
$event_dates = $event->getEvents()->getDates();
}
if($event_dates->count() > 0) {
foreach($event_dates as $date) {
if(!$dates->contains($date)) {
$dates->add($date);
}
}
}
}
}
}
}
return $dates;
}
private function countTotalTicketsSold(EventDates $date) {
return $this->getDoctrine()->getManager()->getRepository(EventDates::class)->getAmountTicketsSold($date);
}
private function countTotalTicketWeight(EventDates $date) {
return $this->getDoctrine()->getManager()->getRepository(EventDates::class)->getAmountWeightSold($date);
}
/**
* @Route("/stats/list", name = "list")
*/
public function listAction(Request $request) {
// User has to be logged in to get here by security firewall, no additional checks are done now.
// No user object means no data...
$data = array();
$x = $y = $z = 0;
$sp = $request->query->has('start') ? $request->query->get('start') : 0; // Start of records
$pp = $request->query->has('length') ? $request->query->get('length') : 10; // Amount of records
$pn = $request->query->has('draw') ? $request->query->get('draw') : 1; // Which page
$recordsTotal = 0;
$recordsFiltered = 0;
if($request->query->has('search')) {
$sq = $request->query->get('search');
if(isSet($sq['value'])) {
if(strlen($sq['value']) > 0) {
// Use the filter...
$this->searchFilter = addslashes($sq['value']);
}
}
}
$dates = $this->getEvents(); // Now contains the events the user has rights for...
// Collect an array iterator.
$iterator = $dates->getIterator();
// Do sort the new iterator.
$iterator->uasort(function ($a, $b) {
return ($a->getDateStart() > $b->getDateStart()) ? -1 : 1;
});
// pass sorted array to a new ArrayCollection.
$datesSorted = new ArrayCollection(iterator_to_array($iterator));
// Leave the sorting to datatables now....
foreach($datesSorted as $date) {
if(isSet($date->getEvents()->getEventDescription()[0])) {
$title = $date->getEvents()->getEventDescription()[0]->getTitle();
$city = $date->getLocation()->getCity();
// We have at least one object for the description. If not, we won't use it.
if((strlen($this->searchFilter) < 1) || stristr($title,$this->searchFilter) || stristr($city,$this->searchFilter)) {
if(($x >= $sp) && ($x < ($sp + $pp))) {
$cnt = $cnta = 0;
$cnt = $this->countTotalTicketsSold($date);
$cnta = $this->countTotalTicketWeight($date);
$data[$y] = array(
'id' => $date->getId(),
'date_id' => $date->getId(),
'event_id' => $date->getEvents()->getId(),
'start_date' => (string)$date, // __toString magic
'title' => $title,
'city' => $city,
'tickets' => $cnt,
'attendees' => $cnta,
'actions' => array(),
);
$data[$y]['actions']['stats'] = false;
$data[$y]['actions']['export'] = false;
$data[$y]['actions']['list'] = false;
if($this->getUser()->isGranted('ROLE_STATS') || $this->getUser()->isGranted('ROLE_ADMIN') ) {
$data[$y]['actions']['stats'] = $this->generateUrl('stats_details_event_date', array('event'=>$date->getEvents()->getId(),'date'=>$date->getId()));
}
if($this->getUser()->isGranted('ROLE_EXPORT') || $this->getUser()->isGranted('ROLE_ADMIN') ) {
$data[$y]['actions']['export'] = $this->generateUrl('stats_export_event_date', array('event'=>$date->getEvents()->getId(),'date'=>$date->getId()));
}
if($this->getUser()->isGranted('ROLE_LIST') || $this->getUser()->isGranted('ROLE_ADMIN') ) {
$data[$y]['actions']['list'] = $this->generateUrl('stats_list_event_date', array('event'=>$date->getEvents()->getId(),'date'=>$date->getId()));
}
$y++;
}
$x++;
}
$z++;
}
}
$recordsTotal = $z;
$recordsFiltered = $x;
return new JsonResponse(array('data'=>$data,'draw'=>$pn,'recordsTotal'=>$recordsTotal,'recordsFiltered'=>$recordsFiltered));
}
/**
* @Route("/stats/dashboard", name = "dashboard")
*/
public function dashboardAction(Request $request) {
$user = $this->getUser();
$tpl = 'stats/';
$columns = array(
'event_id' => 'Evenement ID',
'date_id' => 'Datum ID',
'start_date' => 'Startdatum Evenement',
'title' => 'Titel Evenement',
'city' => 'Plaats',
'tickets' => 'Tickets Verkocht',
'attendees' => 'Deelnemers<br>(Op ticket-gewicht)',
);
$jsonCol = $tblCol = array();
foreach($columns as $id=>$column) {
$jsonCol[] = ['data'=>$id,'title'=>$column];
//$tblCol[] = $column;
}
return $this->render($tpl.'/dashboard.html.twig',array('jsoncol'=>$jsonCol));
}
/**
* @Route("/stats/details/{event}/{date}", name = "details_event_date")
*/
public function detailsEventDateAction(Request $request, Events $event, EventDates $date) {
// User determines access rights. We ignore the domain here.
if(!$this->getUser()->isGranted('ROLE_STATS') && !$this->getUser()->isGranted('ROLE_ADMIN')) {
// How the F did he even get here?
return new Response("Geen rechten om dit te zien.",Response::HTTP_OK);
}
// We do have to check if the user has the rights to the chosen event, or we're getting problems later on...
$dates = $this->getEvents();
if(!$dates->contains($date)) {
// Too bad, no access...
return new Response("Geen rechten voor het gekozen event.",Response::HTTP_OK);
}
$eStats = $this->eventStats;
$data = $eStats->getEventData($date);
$prData = $eStats->getProvincesByEvent($date);
$gmData = $eStats->getTownshipsByEvent($date);
$columns = array();
$columns['title'] = 'Naam';
$columns['price'] = 'Prijs';
$columns['total'] = 'Verkocht';
$columns['paid'] = 'Betaald';
$columns['pending'] = 'Wacht op betaling';
$chartData1 = $eStats->getTicketCountByDate($date);
// Chart
$series = array(
array("name" => "Tickets", "data" => $chartData1)
);
$ob = new Highchart();
$ob->chart->renderTo('chart1'); // The #id of the div where to render the chart
$ob->title->text('Verkoopstatistieken');
$ob->xAxis->title(array('text' => "Datum"));
$ob->yAxis->title(array('text' => "Aantal"));
$ob->xAxis->type('datetime');
$ob->xAxis->tickInterval((28 * 24 * 3600 * 1000));
$ob->chart->zoomType("x");
$ob->series($series);
$ob2 = new Highchart();
$ob2->chart->renderTo('chart2');
$ob2->title->text('Verdeling tussen de type\'s tickets');
$ob2->plotOptions->pie(array(
'allowPointSelect' => true,
'cursor' => 'pointer',
'dataLabels' => array(
'enabled' => false,
'format' => '{point.name}: {point.y:.1f}%'
),
'showInLegend' => true
));
$ob2->tooltip->pointFormat('<span style="color:{point.color}">{point.name}</span>: <b>{point.y:.2f}%</b><br/>');
$tdata = array();
$total = 0;
foreach($data['tickets'] as $ticket) {
$total += (int)$ticket['total'];
}
foreach($data['tickets'] as $ticket) {
$perc = ($ticket['total'] / $total) * 100;
$tdata[] = array($ticket['title'], $perc);
}
$ob2->series(array(array('type' => 'pie','name' => 'Ticketverdeling', 'data' => $tdata)));
$ob3 = new Highchart();
$ob3->chart->renderTo('chart3');
$ob3->title->text('Statistieken per provincie');
$ob3->plotOptions->pie(array(
'allowPointSelect' => true,
'cursor' => 'pointer',
'dataLabels' => array(
'enabled' => false,
'format' => '{point.name}: {point.y:.1f}%'
),
'showInLegend' => true
));
$ob3->tooltip->pointFormat('<span style="color:{point.color}">{point.name}</span>: <b>{point.y:.2f}%</b><br/>');
$tdata = array();
$total = 0;
foreach($prData as $aantal) {
$total += (int)$aantal;
}
foreach($prData as $provincie=>$aantal) {
$perc = ($aantal / $total) * 100;
$tdata[] = array($provincie, $perc);
}
$ob3->series(array(array('type' => 'pie','name' => 'Provincie', 'data' => $tdata)));
$ob4 = new Highchart();
$ob4->chart->renderTo('chart4');
$ob4->title->text('Top 10 Gemeentes');
$ob4->plotOptions->pie(array(
'allowPointSelect' => true,
'cursor' => 'pointer',
'dataLabels' => array(
'enabled' => false,
'format' => '{point.name}: {point.y:.1f}%'
),
'showInLegend' => true
));
$ob4->tooltip->pointFormat('<span style="color:{point.color}">{point.name}</span>: <b>{point.y:.2f}%</b><br/>');
arsort($gmData);
$tdata = array();
$total = 0;
foreach($gmData as $aantal) {
$total += (int)$aantal;
}
$i = 1;
foreach($gmData as $gemeente=>$aantal) {
if($i > 10) continue;
$perc = ($aantal / $total) * 100;
$tdata[] = array($gemeente, $perc);
$i++;
}
$ob4->series(array(array('type' => 'pie','name' => 'Gemeente', 'data' => $tdata)));
unset($data['ticketmeta']);
unset($data['globalmeta']);
return $this->render('stats/detail.html.twig',
array('event'=>$event,
'data'=>$data,
'columns'=>$columns,
'chart1' => $ob,
'chart2' => $ob2,
'chart3' => $ob3,
'chart4' => $ob4,
'modal'=>true
)
);
}
/**
* @Route("/stats/export/{event}/{date}", name = "export_event_date")
*/
public function exportAction(Request $request, Events $event, EventDates $date) {
// User determines access rights. We ignore the domain here.
if(!$this->getUser()->isGranted('ROLE_EXPORT') && !$this->getUser()->isGranted('ROLE_ADMIN')) {
// How the F did he even get here?
return new Response("Geen rechten om te exporteren.",Response::HTTP_OK);
}
// We do have to check if the user has the rights to the chosen event, or we're getting problems later on...
$dates = $this->getEvents();
if(!$dates->contains($date)) {
// Too bad, no access...
return new Response("Geen rechten voor het gekozen event.",Response::HTTP_OK);
}
// OK, so it is allowed?
return new Response("OK",Response::HTTP_OK);
}
/**
* @Route("/stats/list/{event}/{date}", name = "list_event_date")
*/
public function listEventDateAction(Request $request, Events $event, EventDates $date) {
// User determines access rights. We ignore the domain here.
if(!$this->getUser()->isGranted('ROLE_LIST') && !$this->getUser()->isGranted('ROLE_ADMIN')) {
// How the F did he even get here?
return new Response("Geen rechten om te weergeven.",Response::HTTP_OK);
}
// We do have to check if the user has the rights to the chosen event, or we're getting problems later on...
$dates = $this->getEvents();
if(!$dates->contains($date)) {
// Too bad, no access...
return new Response("Geen rechten voor het gekozen event.",Response::HTTP_OK);
}
$retval = $this->getExportData($request,$event,$date);
$retval = str_replace("\r\n","</td></tr>\r\n<tr><td>",$retval);
$retval = str_replace(";","</td><td>",$retval);
$retval = "<table><tr><td>".$retval."</td></tr></table>";
return new Response($retval,Response::HTTP_OK);
}
/**
* @Route("/stats/info/{event}/{date}", name = "info_event_date")
*/
public function infoEventDateAction(Request $request, Events $event, EventDates $date) {
// User determines access rights. We ignore the domain here.
if(!$this->getUser()->isGranted('ROLE_LIST') && !$this->getUser()->isGranted('ROLE_ADMIN')) {
// How the F did he even get here?
return new Response("Geen rechten om te weergeven.",Response::HTTP_OK);
}
// We do have to check if the user has the rights to the chosen event, or we're getting problems later on...
$dates = $this->getEvents();
if(!$dates->contains($date)) {
// Too bad, no access...
return new Response("Geen rechten voor het gekozen event.",Response::HTTP_OK);
}
return new Response($retval,Response::HTTP_OK);
}
/**
* @Route("/stats/export/{event}/{date}/download", name = "export_event_date_download")
* @Route("/stats/export/{event}/{date}/download.{ext}", name = "export_event_date_download")
*/
public function exportDownloadAction(Request $request, Events $event, EventDates $date, $ext = false) {
// User determines access rights. We ignore the domain here.
if(!$this->getUser()->isGranted('ROLE_EXPORT') && !$this->getUser()->isGranted('ROLE_ADMIN')) {
// How the F did he even get here?
return new Response("Geen rechten om te exporteren.",Response::HTTP_OK);
}
// We do have to check if the user has the rights to the chosen event, or we're getting problems later on...
$dates = $this->getEvents();
if(!$dates->contains($date)) {
// Too bad, no access...
return new Response("Geen rechten voor het gekozen event.",Response::HTTP_OK);
}
$response = new Response();
$response->headers->set('Content-Type', 'text/csv');
$response->headers->set('Content-Disposition', 'attachment; filename="EventExport '.date("Y-m-d H:i:s").' - '.(string)$event.'.csv"');
$retval = $this->getExportData($request,$event,$date);
$response->setContent($retval);
return $response;
}
private function getExportData(Request $request, Events $event, EventDates $date) {
$orders = $this->getDoctrine()->getManager()->getRepository(Orders::class)->getByEventDate($date);
$eStats = $this->eventStats;
$data = $eStats->getEventData($date);
$allowedTickets = $date->getTickets();
// Determine columns....
$columns['Id'] = 'Ordernummer';
$columns['date_created'] = 'Besteldatum';
$columns['email'] = 'Email';
$columns['first_name'] = 'Voornaam';
$columns['lastname'] = 'Achternaam';
$columns['phone'] = 'Telefoon';
$columns['address'] = 'Adres';
$columns['streetnr'] = 'Huisnr';
$columns['postal_code'] = 'Postcode';
$columns['city'] = 'Plaats';
$columns['country'] = 'Land';
$columns['total'] = 'Bedrag';
$columns['birthdate'] = 'Geboortedatum';
$columns['status_text'] = 'Status';
$columns['sites']['name'] = "Besteld via";
if($this->getUser()->isGranted('ROLE_ADMIN')) {
$columns['amount_tickets'] = 'Aantal Tickets';
$columns['ticket_link'] = 'Ticket URL';
$columns['comments'] = 'Opmerkingen';
}
$columns['order_extra'] = array();
$columns['order_meta'] = array();
$columns['empty1'] = '';
$columns['order_tickets']['Id'] = "Ticketnummer";
$columns['order_tickets']['product_title'] = "Ticket Type";
$columns['order_tickets']['weight'] = "Deelnemers";
$columns['order_tickets']['vak'] = "Vak";
$columns['order_tickets']['rij'] = "Rij";
$columns['order_tickets']['stoel'] = "Stoel";
$columns['order_tickets']['checked_in'] = "Status";
$columns['ticket_extra'] = array();
$columns['ticket_meta'] = array();
$columns['empty2'] = '';
$colIndex = array();
$i = 0;
$y = 0;
$ordermeta = Orders::getOrderMeta($event->getId());
$ticketMetaColumns = array();
foreach($data['ticketmeta'] as $prod_id=>$block) {
foreach($block as $key=>$value) {
if(!isSet($columns['ticket_meta'][$key])) {
$columns['ticket_meta'][$key] = $value;
$ticketMetaColumns[$key] = $value;
}
}
}
foreach($data['globalextra'] as $prod_id=>$info) {
$columns['order_extra'][$prod_id] = $info['title'];
}
foreach($data['ticketextra'] as $prod_id=>$info) {
$columns['order_extra'][$prod_id] = $info['title'];
}
foreach($columns as $k=>$v) {
if(!is_array($v)) {
$retval[$i][] = $v;
$colIndex[$y] = $k;
$y++;
} else {
foreach($v as $key=>$value) {
$retval[$i][] = $value;
$colIndex[$y] = $key;
$y++;
}
}
}
foreach($orders as $order) {
$status = $order->getStatus();
if(!in_array($status, array(
Orders::ORDER_STATUS_COMPLETE,
Orders::ORDER_STATUS_PAID,
Orders::ORDER_STATUS_PENDING,
Orders::ORDER_STATUS_NEW,
))) {
continue; // Skip this order, not the correct status.
}
foreach($order->getOrderTickets() as $ticket) {
if(!$allowedTickets->contains($ticket->getProduct())) continue; // Skip this ticket!
//$extra = $ticket->getProduct()->getEvents()->getEventExtra();
$i++;
foreach($columns as $k=>$v) {
if(!is_array($v)) {
if(!strstr($k,'empty')) {
$value = false;
if($k == 'amount_tickets') {
$cntTick = 0;
foreach($order->getOrderTickets() as $tick) {
if($allowedTickets->contains($tick->getProduct())) {
$cntTick++;
}
}
$value = $cntTick;
}
if($k == 'ticket_link') {
$token = md5($order->getEmail().'-Order-Code-'.$order->getId());
$site = $order->getSites();
$value = 'https://'.$site->getUrl()."/order/".$order->getId()."/pdf/".$token;
}
if(!$value) {
$value = $order->get($k);
if($value instanceof \DateTime) {
$value = $value->format("d-m-Y H:i:s");
}
}
$retval[$i][] = $value;
} else {
$retval[$i][] = '';
}
} else {
if($k == 'order_extra' || $k =='ticket_extra') {
foreach($v as $key=>$value) {
$value = 0;
foreach($order->getItems() as $item) {
if($item->getType() == 'extra' && $item->getProduct() == $key) {
$value = $item->getAmount();
}
}
$retval[$i][] = $value;
}
}
if($k =='ticket_meta') {
$meta = $ticket->getMeta();
foreach($v as $key=>$value) {
$value = '';
if(isSet($meta[$key])) {
$value = $meta[$key];
}
$retval[$i][] = $value;
}
}
if($k == 'sites') {
$object = $order->getSites();
foreach($v as $key=>$value) {
$value = false;
if(!$value) {
$value = $object->get($key);
}
$retval[$i][] = $value;
}
}
if($k == 'order_tickets') {
foreach($v as $key=>$value) {
$value = false;
if($key == 'product_title') {
$value = $ticket->getProduct()->getTitle();
}
if($key == 'checked_in') {
$value = $ticket->getCheckedIn();
$value = ($value == 1) ? 'Ingecheckt' : ' ';
}
if($key == 'weight') {
$value = $ticket->getProduct()->getWeight();
}
if($key == 'rij' || $key == 'stoel' || $key == 'vak') {
$value = '-NVT-';
}
if(!$value) {
$value = $ticket->get($key);
}
$retval[$i][] = $value;
}
}
}
}
}
}
foreach($retval as $x => $row) {
$retval[$x] = implode(";",$row);
}
$retval = implode("\r\n",$retval);
return $retval;
}
/**
* @Route("/stats/login", name = "login")
*/
public function loginAction(Request $request, AuthenticationUtils $authenticationUtils) {
// get the login error if there is one
$error = $authenticationUtils->getLastAuthenticationError();
// last username entered by the user
$lastUsername = $authenticationUtils->getLastUsername();
return $this->render('stats/login.html.twig', [
'last_username' => $lastUsername,
'error' => $error,
]);
}
}