<?php
 namespace App\Dashboard\Widget; class Charts_EstimatedDailyBalance 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()->getGlobal(); if ($this->getOption('reportCurrency')=='work') { $reportCurrency = 'work'; $currency = \App\Config\Currencies::getInstance()->getWorkCurrency(); } else { $reportCurrency = 'default'; $currency = \App\Config\Currencies::getInstance()->getDefaultCurrency(); } $chart->setTitle($_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_EstimatedDailyBalance']['chartTitle']); $chartDescription = sprintf( $_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_EstimatedDailyBalance']['chartDescription1'], $currency ); $chartDescription .= '<br />' . $_LANG['dashboard']['widgetsList']['App\Dashboard\Widget\Charts_EstimatedDailyBalance']['chartDescription2']; $chart->setDescription($chartDescription); $chart->setDataSetsDisplayGrouping('clustered'); $records = $this->getRecords($reportCurrency); $dataSets = $this->getDataSets($records); $chart->setDataSetsManually( $dataSets ); return $chart; } private function getRecords($reportCurrency) { $duePayVal = "duePayDef"; if ($reportCurrency == 'work') { $duePayVal = "duePayWrk"; } global $DBTPivotTablesData_clientInvoiceRows, $DBTPivotTablesData_supplierInvoiceRows; $startDate = TODAY_ISO; $endDate = \Engine\DateTime::create('+45 days')->formatDateIso(); $dailyIncomeSQL = "
			SELECT IF(dueDate IS NOT NULL, dueDate, invoiceDate) AS invDate, SUM($duePayVal) AS totalValue
			FROM `$DBTPivotTablesData_clientInvoiceRows`
			WHERE
				(
					( dueDate IS NOT NULL AND (dueDate BETWEEN ? AND ?) )
					OR
					( dueDate IS NULL AND (invoiceDate BETWEEN ? AND ?) )
				)
				AND invoiceVoidDate IS NULL AND cashedStatus IN ('n', 'p') AND invoiceIssueDate IS NOT NULL
			GROUP BY invDate
		"; $dailyIncome = $this->executeAndCacheSql($dailyIncomeSQL, [ $startDate, $endDate, $startDate, $endDate ]); $dailyExpenseSQL = "
			SELECT IF(dueDate IS NOT NULL, dueDate, invoiceDate) AS invDate, SUM($duePayVal) AS totalValue
			FROM `$DBTPivotTablesData_supplierInvoiceRows`
			WHERE
				(
					( dueDate IS NOT NULL AND (dueDate BETWEEN ? AND ?) )
					OR
					( dueDate IS NULL AND (invoiceDate BETWEEN ? AND ?) )
				)
				AND payedStatus IN ('n', 'p') AND statusOp = 'confirmed'
			GROUP BY invDate
		"; $dailyExpense = $this->executeAndCacheSql($dailyExpenseSQL, [ $startDate, $endDate, $startDate, $endDate ]); $records = []; $dateObj = \Engine\DateTime::createFromIsoDate($startDate); $dateStr = $startDate; while ($dateStr<=$endDate) { $records['income'][$dateStr] = 0; $records['expense'][$dateStr] = 0; $records['dailyBalance'][$dateStr] = 0; $records['cashFlow'][$dateStr] = 0; $dateObj->add(\DateInterval::createFromDateString('1 day')); $dateStr = $dateObj->formatDateIso(); } foreach ($dailyIncome as $row) { $records['income'][$row['invDate']] = (float)$row['totalValue']; $records['dailyBalance'][$row['invDate']] += (float)$row['totalValue']; } foreach ($dailyExpense as $row) { $records['expense'][$row['invDate']] = -(float)$row['totalValue']; $records['dailyBalance'][$row['invDate']] -= (float)$row['totalValue']; } $cashFlow = 0; foreach ($records['dailyBalance'] as $day=>$value) { $cashFlow += $value; $records['cashFlow'][$day] = $cashFlow; } 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_EstimatedDailyBalance']['field'][$status] ], 'data'=>[] ]; foreach ($statusRecords as $date => $value) { $thisData['data'][] = ['n'=>\Engine\DateTime::createFromIsoDate($date)->format('d M'), 'v'=>round($value, 2)]; } $data[] = $thisData; } return $data; } protected function getDefaultOptions() { return [ 'reportCurrency' => 'default', ]; } protected function populateLocalOptionsForm(\Engine\Form\Container\GeneralAbstract $form) { $loaderLang = \App\Loader_Lang::getInstance(); $loaderLang->loadAppFile("_customLoad/dashboard/widgets/_general.lang.php"); $_LANG = $loaderLang->getAll(); $fieldset = \Engine\Form\Container\Fieldset::create(''); $defCurr = \App\Config\Currencies::getInstance()->getDefaultCurrency(); $workCurr = \App\Config\Currencies::getInstance()->getWorkCurrency(); $field = new \Engine\Form\Input\Radio($_LANG['dashboard']['widgets']['_GENERAL_']['qrExtra']['currency'], 'reportCurrency', $this->getOption('reportCurrency')); $field->setPossibleValues(['default'=>sprintf($_LANG['dashboard']['widgets']['_GENERAL_']['qrExtra']['defCurr'], $defCurr), 'work'=>sprintf($_LANG['dashboard']['widgets']['_GENERAL_']['qrExtra']['wrkCurr'], $workCurr)]); $fieldset->addElement($field); $form->addContainer($fieldset); return $form; } protected function extractFormOptions($formData) { $options = ['reportCurrency' => $formData['reportCurrency'] ]; $this->setOptions($options); } }