<?php
namespace App\Controller;
use App\Entity\Action;
use App\Entity\Appointments;
use App\Entity\User;
use App\Entity\Order;
use App\Entity\Settings;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
class DashboardController extends AbstractController
{
/**
* @Route("/", name="dashboard") */ public function dashboard(): Response
{
# get the entity manager $em
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
# create variables with dates
$today = new \DateTime("today midnight");
$thisyear = $today->format('Y');
$startofthisyear = date_create_from_format('j-M-Y', '1-Jan-' . $thisyear);
$endofthisyear = date_create_from_format('j-M-Y', '31-Dec-' . $thisyear);
#----------------------------------------------------------------------------#
# PIE CHART # 1. Get all users # 2. Check for each user the actions that are not finished $userlist = [];
$useractionlist = [];
$colorlist = [];
# get repository for all users
$repoUsers = $em->getRepository(User::class);
# build and execute query to get all users with status = 1
$queryUsers = $repoUsers->createQueryBuilder('a')
->select('a.id, a.username, a.color')
->where('a.status = 1');
$users = $queryUsers->getQuery()->execute();
# loop through all the users
foreach ($users as $user) {
# get user id
$userid = $user['id'];
# build and execute custom query to select all open actions related to the user
$countuserquery = "SELECT id FROM action WHERE actionholder_id = $userid and status = 1 and isfinished = 0";
$stmt = $em->getConnection()->prepare($countuserquery);
$stmt->execute();
$result = $stmt->fetchAll();
# if there are open actions
if (count($result)) {
# create array with all users (usernames)
$userlist[] = $user['username'];
# create array with number of open actions
$useractionlist[] = count($result);
# create array with colors (user setting)
$colorlist[] = $user['color'];
} } #----------------------------------------------------------------------------#
# APPOINTMENTS # get repository via the entity manager $repoAppointments = $em->getRepository(Appointments::class);
# build query and execute, get list with appointment for logged in user
$queryAppointments= $repoAppointments->createQueryBuilder('a')
->select('a.id, a.title, a.startdate, a.enddate')
->where('a.status = 1')
->andWhere('a.enddate >= :now')
->andWhere('a.host = ' . $this->getUser()->getId())
->setParameter('now',$today->format('Y-m-d'))
->orderBy('a.startdate','ASC');
$appointments = $queryAppointments->getQuery()->execute();
# build query and execute to count all open appointments
$queryAppointmentsCount= $repoAppointments->createQueryBuilder('a')
->select('count(DISTINCT a.id)')
->leftJoin('a.actionholder','u')
->andWhere('a.host = ' . $this->getUser()->getId())
->orWhere('u.id = ' . $this->getUser()->getId())
->andWhere('a.enddate >= :today')
->andWhere('a.status = 1')
#->groupBy('a.id')
->setParameter('today',$today);
$appointmentscount = $queryAppointmentsCount->getQuery()->getSingleScalarResult();
#----------------------------------------------------------------------------#
# ORDERS # default array for number of orders per month this year $orderspermontharray = [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ];
# build query to get orders per month for this year
$orderspermonthquery = "SELECT MONTH(o.createdate) Maand, COUNT(*) `Aantal` FROM `order` o WHERE YEAR(o.createdate) = YEAR(CURRENT_DATE) GROUP BY YEAR(o.createdate), MONTH(o.createdate)";
#$orderspermonthquery = "SELECT MONTH(o.createdate) Maand, COUNT(*) `Aantal` FROM `order` o GROUP BY YEAR(o.createdate), MONTH(o.createdate)";
$statement = $connection->prepare($orderspermonthquery);
$statement->execute();
$orderspermonth = $statement->fetchAll();
foreach ($orderspermonth as $key=>$value) {
$currentmonth = $value["Maand"];
$orderspermontharray[$currentmonth-1] = $value["Aantal"];
} #----------------------------------------------------------------------------#
# FINANCIALS toezeggingen last year # financials per month array ly = last year $lyfinancialspermontharray = [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ];
$lyfinancialspermonthquery = "SELECT MONTH(f.financialdate) Maand, COUNT(*) `Aantal`, SUM(invatprice) 'EUR' FROM `financials` f JOIN `order` o ON f.orderid_id = o.id WHERE f.category_id = 1 AND o.STATUS = 1 AND f.STATUS = 1 AND YEAR(f.financialdate) = YEAR(CURRENT_DATE)-1 GROUP BY YEAR(f.financialdate), MONTH(f.financialdate)";
$statement = $connection->prepare($lyfinancialspermonthquery);
$statement->execute();
$lyfinancialspermonth = $statement->fetchAll();
foreach ($lyfinancialspermonth as $key=>$value) {
$currentmonth = $value["Maand"];
$lyfinancialspermontharray[$currentmonth-1] = $value["EUR"];
} #----------------------------------------------------------------------------#
# FINANCIALS toezeggingen this year # financials per month array ty = this year $tyfinancialspermontharray = [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ];
$tyfinancialspermonthquery = "SELECT MONTH(f.financialdate) Maand, COUNT(*) `Aantal`, SUM(invatprice) 'EUR' FROM `financials` f JOIN `order` o ON f.orderid_id = o.id WHERE f.category_id = 1 AND o.STATUS = 1 AND f.STATUS = 1 AND YEAR(f.financialdate) = YEAR(CURRENT_DATE) GROUP BY YEAR(f.financialdate), MONTH(f.financialdate)";
$statement = $connection->prepare($tyfinancialspermonthquery);
$statement->execute();
$tyfinancialspermonth = $statement->fetchAll();
foreach ($tyfinancialspermonth as $key=>$value) {
$currentmonth = $value["Maand"];
$tyfinancialspermontharray[$currentmonth-1] = $value["EUR"];
} #dump($tyfinancialspermontharray);
#exit(); #----------------------------------------------------------------------------# # ACTIONS # get repository via entity manager of actions $repoActions = $em->getRepository(Action::class);
# build and execute query to get all actions for the logged in user that are open
$queryActions = $repoActions->createQueryBuilder('a')
->select('a.title, a.id, a.duedate, a.status, c.name')
->leftjoin('a.orderid','o')
->leftjoin('o.customer','c')
->where('a.status = 1')
->andWhere('a.actionholder = ' . $this->getUser()->getId())
->andWhere('a.isfinished = false')
->orderBy('a.duedate', 'asc');
$actions = $queryActions->getQuery()->execute();
# build and execute query to get all intime finished actions for the logged in user of this year
$queryActionsIntime = $repoActions->createQueryBuilder('a')
->select('count(a.id)')
->where('a.status = 1')
->andWhere('a.actionholder = ' . $this->getUser()->getId())
->andWhere('a.duedate >= :startofthisyear')
->andWhere('a.duedate < :endofthisyear')
->andWhere('a.isfinished = true')
->andWhere('a.completedate <= a.duedate')
->orderBy('a.duedate','ASC')
->setParameter('startofthisyear',$startofthisyear->format('Y-m-d'))
->setParameter('endofthisyear',$endofthisyear->format('Y-m-d'));
$actionsIntimeCount = $queryActionsIntime->getQuery()->getSingleScalarResult();
# build and execute query to get all outtime actions for the logged in user of this year
$queryActionsOuttime = $repoActions->createQueryBuilder('a')
->select('count(a.id)')
->where('a.status = 1')
->andWhere('a.actionholder = ' . $this->getUser()->getId())
->andWhere('a.duedate >= :startofthisyear')
->andWhere('a.duedate < :endofthisyear')
->andWhere('a.isfinished = true')
->andWhere('a.completedate > a.duedate')
->setParameter('startofthisyear',$startofthisyear->format('Y-m-d'))
->setParameter('endofthisyear',$endofthisyear->format('Y-m-d'));
$actionsOuttimeCount = $queryActionsOuttime->getQuery()->getSingleScalarResult();
# calculate the ratio between the in time and not in time finished actions by the user of this year
if ($actionsOuttimeCount OR $actionsIntimeCount) {
$intimeratio = round(100*(($actionsIntimeCount / ($actionsIntimeCount + $actionsOuttimeCount))));
} else { $intimeratio = 100;
} # build and execute query to get all current open actions that are too late
$queryActionsOpenLateCount = $repoActions->createQueryBuilder('a')
->select('count(a.id)')
->where('a.status = 1')
->andWhere('a.actionholder = ' . $this->getUser()->getId())
->andWhere('a.duedate < :today')
->andWhere('a.isfinished = false')
->orderBy('a.duedate','ASC')
->setParameter('today',$today->format('Y-m-d'));
$actionsOpenLateCount = $queryActionsOpenLateCount->getQuery()->getSingleScalarResult();
#----------------------------------------------------------------------------#
# ACTIONS in category 42 Vergunningaanvraag (ingediend bij gemeente) # build and execute query to get all actions for the logged in user that are open $queryActionsVergunning = $repoActions->createQueryBuilder('a')
->select('a.title, a.id, a.duedate, a.status, c.name, c.city')
->leftjoin('a.orderid','o')
->leftjoin('o.customer','c')
->where('a.status = 1')
->andWhere('a.category = 42')
->andWhere('a.isfinished = false')
->orderBy('a.duedate', 'asc');
$actionsvergunningsaanvraag = $queryActionsVergunning->getQuery()->execute();
#----------------------------------------------------------------------------#
# ORDERS WITHOUT ACTIONS #$queryOrdersnoActions ="SELECT o.id as `id`, COUNT(a.id) - SUM(a.isfinished) as `som` FROM `order` o LEFT JOIN action a ON o.id=a.orderid_id WHERE a.id IS NULL and o.status = 1 OR a.status = 1 and o.status = 1 and o.orderstatus_id = 9 OR o.orderstatus_id = 11 GROUP BY o.id"; $queryOrdersNoOpenActions ="SELECT o.id as `id`, COUNT(a.id) - SUM(a.isfinished) as `som` FROM `order` o LEFT JOIN action a ON o.id=a.orderid_id WHERE o.status = 1 and a.status = 1 and (o.orderstatus_id = 9 OR o.orderstatus_id = 11) GROUP BY o.id ORDER BY `id` ASC";
$statement = $connection->prepare($queryOrdersNoOpenActions);
$statement->execute();
$ordersNoOpenActions = $statement->fetchAll();
$ordernoactionarray = [];
$count = 0;
foreach ($ordersNoOpenActions as $orderNoOpenAction) {
$sum = $orderNoOpenAction["som"];
if ($sum == 0) {
$count++;
$order = $this->getDoctrine()->getRepository(Order::class)->find($orderNoOpenAction["id"]);
$ordernoactionarray[$count]["id"] = $order->getId();
$ordernoactionarray[$count]["customer"] = $order->getCustomer()->getName();
$ordernoactionarray[$count]["city"] = $order->getCustomer()->getCity();
$ordernoactionarray[$count]["ordernumber"] = $order->getOrdernumber();
} } # find orders without actions
$queryOrdersNoActions ="SELECT o.id as `id`, COUNT(a.id) as `som` FROM `order` o LEFT JOIN action a ON o.id=a.orderid_id WHERE o.status = 1 and (o.orderstatus_id = 9 OR o.orderstatus_id = 11) GROUP BY o.id ORDER BY `id` ASC";
$statement = $connection->prepare($queryOrdersNoActions);
$statement->execute();
$ordersNoActions = $statement->fetchAll();
foreach ($ordersNoActions as $ordersNoAction) {
$sum = $ordersNoAction["som"];
if ($sum == 0) {
$count++;
$order = $this->getDoctrine()->getRepository(Order::class)->find($ordersNoAction["id"]);
$ordernoactionarray[$count]["id"] = $order->getId();
$ordernoactionarray[$count]["customer"] = $order->getCustomer()->getName();
$ordernoactionarray[$count]["city"] = $order->getCustomer()->getCity();
$ordernoactionarray[$count]["ordernumber"] = $order->getOrdernumber();
} } #----------------------------------------------------------------------------#
# ORDERS IN NAZORG [LEGACY] # get repository via the entity manager #$repoOrders = $em->getRepository(Order::class); # build and execute query to get all orders in nazorg #$queryordersinnazorg = $repoOrders->createQueryBuilder('o') # ->select('o.id','o.ordernumber','c.name','c.city') # ->leftjoin('o.customer','c') # ->where('o.status = 1') # ->andWhere('o.orderstatus = 11') # ->orderBy('o.ordernumber', 'asc'); #$ordersinnazorg = $queryordersinnazorg->getQuery()->execute(); #----------------------------------------------------------------------------# # ACTIONS in category XX Nazorg $actionCategoryNazorgIds = $em->getRepository(Settings::class)->findOneBy(['name' => 'defaultnazorgcategory'])->getValue();
// Zorg ervoor dat de waarde een array wordt, bijvoorbeeld als er meerdere IDs in een string staan
$actionCategoryNazorgIds = explode(',', $actionCategoryNazorgIds);
$actionCategoryNazorgIds = array_map('trim', $actionCategoryNazorgIds); // Spaties verwijderen
$queryActionsNazorg = $repoActions->createQueryBuilder('a')
->select('a.title, a.id, a.duedate, a.status, c.name, c.city')
->leftJoin('a.orderid', 'o')
->leftJoin('o.customer', 'c')
->where('a.status = 1')
->andWhere('a.category IN (:actionCategoryNazorgIds)')
->andWhere('a.isfinished = false')
->setParameter('actionCategoryNazorgIds', $actionCategoryNazorgIds)
->orderBy('a.duedate', 'asc');
$actionsnazorg = $queryActionsNazorg->getQuery()->execute();
#----------------------------------------------------------------------------#
# RETURN #echo "<pre>"; #print_r($colorlist); #echo "<br />"; #print_r($orderspermontharray); #exit(); return $this->render('dashboard/index.html.twig', [
'user' => $this->getUser(), # current logged in user
'actions' => $actions, # object actions
'vergunningsaanvragen' => $actionsvergunningsaanvraag, # object actions in specific category
'appointments' => $appointments, # object appointment
'appointmentscount' => $appointmentscount, # integer with number of appointments
'orderspermontharray' => $orderspermontharray, # array with the number of orders for each month
'lyfinancialspermontharray' => $lyfinancialspermontharray, # array with the sum of toezeggingen for each month for last year
'tyfinancialspermontharray' => $tyfinancialspermontharray, # array with the sum of toezeggingen for each month for this year
'actionsopenlatecount' => $actionsOpenLateCount, # integer with number of open actions that are late
'intimeratio' => $intimeratio, # double with intimeratio
'userlist' => $userlist, # array with users for pie chart
'useractionlist' => $useractionlist, # array with number of actions for pie chart
'colorlist' => $colorlist, # array with color HEX for pie chart
'ordernoaction' => $ordernoactionarray, # array with orders with no open actions
'nazorgacties' => $actionsnazorg, # object actions in specific category
#'ordersinnazorg' => $ordersinnazorg, # objects orders in nazorg [LEGACY] ]);
} /**
* @Route("/changelog", name="changelog") */ public function changelog(): Response
{
return $this->render('dashboard/changelog.html.twig', [
]); } /**
* @Route("/phpinfo", name="phpinfo") */ public function phpinfo(): Response
{
phpinfo();
exit(); }}