<?php
 namespace App\Dashboard\Widget; class Charts_CancelledServicesCountMonthly extends Charts_GeneralAbstract { use Traits\FinancialWidget; public function getTemplatePath() { return APP_REAL_PATH."/_templates/application/dashboard/widgets/D3Charts/bar.tpl"; } protected function defineChart() { $chart = new \App\QuickReports\Charts\Types\Bar(); $_LANG = \App\Loader_Lang::getInstance()->getGlobal(); $chart->setTitle($_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_CancelledServicesCountMonthly']['chartTitle']); $chart->setDescription($_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_CancelledServicesCountMonthly']['chartDescription']); $chart->setDataSetsDisplayGrouping('clusteredAndStacked'); $records = $this->getRecords(); $dataSets = $this->getDataSets($records); $chart->setDataSetsManually( $dataSets ); return $chart; } private function getRecords() { global $DBTPivotTablesData_orderServices; $startDt = \Engine\DateTime::create('-12 months'); $startTransDate = $startDt->format('Y-m-01'); $recordsList1SQL = "SELECT DATE_FORMAT(addDate, '%Y%m') AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE addDate>=? GROUP BY DATE_FORMAT(addDate, '%Y%m')"; $recordsList1 = $this->executeAndCacheSql($recordsList1SQL, [ $startTransDate ]); $recordsList2SQL = "SELECT DATE_FORMAT(cancelDate, '%Y%m') AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE cancelDate IS NOT NULL AND cancelDate>=? GROUP BY DATE_FORMAT(cancelDate, '%Y%m')"; $recordsList2 = $this->executeAndCacheSql($recordsList2SQL, [ $startTransDate ]); $recordsList3SQL = "SELECT DATE_FORMAT(refundDate, '%Y%m') AS transDate, COUNT(*) AS cnt FROM `$DBTPivotTablesData_orderServices` WHERE refundDate IS NOT NULL AND refundDate>=? GROUP BY DATE_FORMAT(refundDate, '%Y%m')"; $recordsList3 = $this->executeAndCacheSql($recordsList3SQL, [ $startTransDate ]); for ( $records = [] , $currYm = $startDt->format('Ym'), $endYm = TODAY_YEAR.TODAY_MONTH ; $currYm<=$endYm ; $currYm++ ) { $records['add'][$currYm] = 0;$records['cancel'][$currYm] = 0;$records['refund'][$currYm] = 0; if ($currYm%100==12) { $currYm += 88; } } foreach($recordsList1 as $row) { $records['add'][$row['transDate']] = (Int)$row['cnt']; } foreach($recordsList2 as $row) { $records['cancel'][$row['transDate']] = (Int)$row['cnt']; } foreach($recordsList3 as $row) { $records['refund'][$row['transDate']] = (Int)$row['cnt']; } return $records; } private function getDataSets($records) { $_LANG = \App\Loader_Lang::getInstance()->getGlobal(); $data = []; foreach ($records as $status=>$statusRecords) { $thisData = [ 'attributes' => [ 'label' => $_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_CancelledServicesCountMonthly']['status'][$status] ], 'data'=>[] ]; foreach ($statusRecords as $date => $value) { $month = $_LANG['date']['month']['short'][(Int)substr($date, 4)] . ' ' . substr($date, 0, 4); $thisData['data'][] = ['n'=>$month, 'v'=>$value]; } $data[] = $thisData; } return $data; } }