<?php

namespace App\Dashboard\Widget;

class Charts_FutureServicesStatsCount extends Charts_GeneralAbstract
{
	use Traits\FinancialWidget;

	/**
	 * {@inheritDoc}
	 * @see \App\Dashboard\Widget\Charts_GeneralAbstract::getTemplatePath()
	 */
	public function getTemplatePath()
	{
		return  APP_REAL_PATH."/_templates/application/dashboard/widgets/D3Charts/column.tpl";
	}

	/**
	 * {@inheritDoc}
	 * @see \App\Dashboard\Widget\Charts_GeneralAbstract::defineChart()
	 */
	protected function defineChart()
	{

		$chart = new \App\QuickReports\Charts\Types\Column();
//		$chart->setPivotSource('OrderServices');

		$_LANG = \App\Loader_Lang::getInstance()->getGlobal();

		$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');

		// get and manually set the chart dataSets
		$records = $this->getRecords();
		$dataSets = $this->getDataSets($records);
		$chart->setDataSetsManually( $dataSets );

		return $chart;

	}


	/**
	 * Returns the raw data sets for this chart.
	 * @return array
	 */
	private function getRecords()
	{

		global $DBTPivotTablesData_orderServices;

		$startDate = TODAY_ISO;
		$endDate = \Engine\DateTime::create('+30 days')->formatDateIso();

		// totally cashed services
		$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 ]);

		// partially cashed services
		$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 ]);

		// not cashed services
		$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 ]);

		// not invoiced services
		$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;
			// increase day
			$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;

	}


}