<?php
 namespace App\Dashboard\Widget; class Charts_FutureServicesStatsCount extends Charts_GeneralAbstract { use Traits\FinancialWidget; public function getTemplatePath() { return APP_REAL_PATH."/_templates/application/dashboard/widgets/D3Charts/column.tpl"; } protected function defineChart() { $chart = new \App\QuickReports\Charts\Types\Column(); $_LANG = \App\Loader_Lang::getInstance()->getAll(); $chart->setTitle($_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_FutureServicesStatsCount']['chartTitle']); $chart->setDescription($_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_FutureServicesStatsCount']['chartDescription']); $chart->setDataSetsDisplayGrouping('clusteredAndStacked'); $records = $this->getRecords(); $dataSets = $this->getDataSets($records); $chart->setDataSetsManually( $dataSets ); return $chart; } private function getRecords() { global $DBTPivotTablesData_orderServices; $startDate = TODAY_ISO; $endDate = \Engine\DateTime::create('+30 days')->formatDateIso(); $recordsList1SQL = "SELECT offerStartDate AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE (offerStartDate BETWEEN ? AND ?) AND invoicesCashedStatus='t' GROUP BY offerStartDate"; $recordsList1 = $this->executeAndCacheSql($recordsList1SQL, [ $startDate, $endDate ]); $recordsList2SQL = "SELECT offerStartDate AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE (offerStartDate BETWEEN ? AND ?) AND invoicesCashedStatus='p' GROUP BY offerStartDate"; $recordsList2 = $this->executeAndCacheSql($recordsList2SQL, [ $startDate, $endDate ]); $recordsList3SQL = "SELECT offerStartDate AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE (offerStartDate BETWEEN ? AND ?) AND invoicesCashedStatus='n' AND statusFin='TI' AND statusOp!='XX' GROUP BY offerStartDate"; $recordsList3 = $this->executeAndCacheSql($recordsList3SQL, [ $startDate, $endDate ]); $recordsList4SQL = "SELECT offerStartDate AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE (offerStartDate BETWEEN ? AND ?) AND statusFin!='TI' AND statusOp!='XX' GROUP BY offerStartDate"; $recordsList4 = $this->executeAndCacheSql($recordsList4SQL, [ $startDate, $endDate ]); $records = []; $date = $startDate; while ($date<=$endDate) { $records['cashed_t'][$date] = 0; $records['cashed_p'][$date] = 0; $records['cashed_n'][$date] = 0; $records['notInvoiced'][$date] = 0; $dateObj = \Engine\DateTime::createFromIsoDate($date); $dateObj->add(\DateInterval::createFromDateString('1 day')); $date = $dateObj->formatDateIso(); } foreach($recordsList1 as $row) { $records['cashed_t'][$row['transDate']] = (Int)$row['cnt']; } foreach($recordsList2 as $row) { $records['cashed_p'][$row['transDate']] = (Int)$row['cnt']; } foreach($recordsList3 as $row) { $records['cashed_n'][$row['transDate']] = (Int)$row['cnt']; } foreach($recordsList4 as $row) { $records['notInvoiced'][$row['transDate']] = (Int)$row['cnt']; } return $records; } private function getDataSets(array $records) { $_LANG = \App\Loader_Lang::getInstance()->getGlobal(); $data = []; foreach ($records as $status=>$statusRecords) { $thisData = [ 'attributes' => [ 'label' => $_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_FutureServicesStatsCount']['status'][$status] ], 'data'=>[] ]; foreach ($statusRecords as $date => $value) { $thisData['data'][] = ['n'=>\Engine\DateTime::createFromIsoDate($date)->format('d M'), 'v'=>$value]; } $data[] = $thisData; } return $data; } }