<?php

namespace App\Dashboard\Widget;

class Charts_CancelledServicesCountMonthly 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/bar.tpl";
	}

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

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

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

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

		// 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;

		$startDt = \Engine\DateTime::create('-12 months');
		$startTransDate = $startDt->format('Y-m-01');

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

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

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

		// add empty months (with no records)
		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;
 			}
		}

		// associate values to the final data set
		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;

	}


}