<?php
namespace Report\Report;


/**
 * Price Guide Statistics Report.
 * @author andre.fourie
 */
class PriceGuideStatistical extends \Utility\Service\Report
{


	/**
	 * @var string
	 */
	protected $_title  = 'PriceGuide Statistical Report';
	/**
	 * @var string
	 */
	protected $_subject = 'PriceGuide';
	/**
	 * @var string
	 */
	protected $_description = 'Statistical report for vehicles sold on price guide.';
	/**
	 * @var array
	 */
	protected $_notes = array(
			'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
	);
	/**
	 * @var array
	 */
	protected $_headers = array(
			'Dealer',
			'No of Valuations',
			'Trade Value',
			'Recon',
			'Highest Offer',
			'% To Trade',
			'No of Offers'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'company_name',
			'num_valuations',
			'total_trade',
			'total_recon',
			'total_highestOffer',
			'percentage_toTrade',
			'total_numOffers'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
			/* 'num_valuations',
			'total_trade',
			'total_recon',
			'total_highestOffer',
			'total_numOffers' */
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array(
			'total_trade',
			'total_recon',
			'total_highestOffer'
	);


	/**
	 * Build the dataset.
	 */
	public function build()
	{
		ini_set('memory_limit','512M');
		$totalRow = array(
				'company_name' 			=> '',
				'num_valuations' 		=> '',
				'total_trade' 			=> '',
				'total_recon' 			=> '',
				'total_highestOffer' 	=> '',
				'percentage_toTrade' 	=> 'Offers per unit:',
				'total_numOffers' 		=> ''
		);
		$baseRow = array(
				'company_name' 			=> '',
				'num_valuations' 		=> 0,
				'total_trade' 			=> 0,
				'total_recon' 			=> 0,
				'total_highestOffer' 	=> 0,
				'percentage_toTrade' 	=> 0,
				'total_numOffers' 		=> 0
		);
		$titles =  array(
				'company_name' 			=> 'Dealer',
				'num_valuations' 		=> 'No of Valuations',
				'total_trade' 			=> 'Trade Value',
				'total_recon' 			=> 'Recon',
				'total_highestOffer' 	=> 'Highest Offer',
				'percentage_toTrade' 	=> '% To Trade',
				'total_numOffers' 		=> 'No of Offers',
				'id' 					=> 'h',
				'excel.rowStyle' 		=> 'Titles'
		);

		#-> Establish list of dealerships to work with.
		$selection = array(
				'company', 'companyGroup', 'groupDivision'
		);
		$query = 'SELECT [SELECTION] '
				. 'FROM \Company\Entity\Company company'
				. ' LEFT JOIN company.group companyGroup '
				. ' LEFT JOIN company.groupDivision groupDivision '
				. '[WHERE] '
				. 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
		if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
		{
			$this->_queries['Date Range From'] = $this->_input['dateFrom'];
		}
		if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
		{
			$this->_queries['Date Range Until'] = $this->_input['dateTo'];
		}
		if (isset($this->_input['group'])
				&& !empty($this->_input['group'])
				&& 'null' != $this->_input['group'])
		{
			$this->_queries['Group'] = $this->em
				->find('\Company\Entity\Group', $this->_input['group'])
				->name;
			$where[] = 'companyGroup.id = :group';
			$params['group'] = $this->_input['group'];
		}
		if (isset($this->_input['groupDivision'])
				&& !empty($this->_input['groupDivision'])
				&& 'null' != $this->_input['groupDivision'])
		{
			error_log($this->_input['groupDivision']);
			$this->_queries['Division'] = $this->em
				->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
				->name;
			$where[] = 'groupDivision.id = :division';
			$params['division'] = $this->_input['groupDivision'];
		}
		if (isset($this->_input['company'])
				&& !empty($this->_input['company'])
				&& 'null' != $this->_input['company'])
		{
			$this->_queries['Dealership'] = $this->em
				->find('\Company\Entity\Company', $this->_input['company'])
				->name;
			$where[] = 'company.id = :company';
			$params['company'] = $this->_input['company'];
		}
		#-> Finalize query.
		$query = str_replace(array(
				'[SELECTION]', '[WHERE]'
			), array(
				implode(', ', $selection) . ' ',
				!empty($where)
					? 'WHERE ' . implode(' AND ', $where) . ' '
					: ' '
			), $query);
		#-> Collect data.
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$companies = $query->getScalarResult();
		$this->em->clear();
		$grp = false;
		$div = false;
		foreach ($companies as $company)
		{
			if ('' == $company['companyGroup_name'])
			{
				$company['companyGroup_name'] = 'None';
			}
			if ('' == $company['groupDivision_name'])
			{
				$company['groupDivision_name'] = 'None';
			}
			if ($div != $company['groupDivision_name'])
			{
				if (false != $div)
				{
					$totals = $baseRow;
					$totals['company_name'] = 'DIVISION TOTALS';
					$totals['id'] = 'dt';
					$totals['excel.rowStyle'] = 'Totals';
					$this->_data[] = $totals;
					$totals = $totalRow;
					$totals['company_name'] = $grp . ': ' . $div;
					$totals['id'] = 'dn';
					$totals['excel.rowStyle'] = 'Totals';
					$this->_data[] = $totals;
					$this->_data[] = array();
					if ($grp == $company['companyGroup_name'])
					{
						if (false != $grp)
						{
							$this->_data[] = $titles;
						}
					}
				}
				$div = $company['groupDivision_name'];
			}
			if ($grp != $company['companyGroup_name'])
			{
				if (false != $grp)
				{
					#-> Not the first group, so we have grand totals to add
					$totals = $baseRow;
					$totals['company_name'] = 'GROUP TOTALS';
					$totals['id'] = 'gt';
					$totals['excel.rowStyle'] = 'Totals';
					$this->_data[] = $totals;
					$totals = $totalRow;
					$totals['company_name'] = $grp;
					$totals['id'] = 'gn';
					$totals['excel.rowStyle'] = 'Totals';
					$this->_data[] = $totals;
					$this->_data[] = array();
					$this->_data[] = array();
					$this->_data[] = $titles;
				}
				$grp = $company['companyGroup_name'];
			}
			$entry = $baseRow;
			$entry['id'] = $company['company_id'];
			$entry['company_name'] = $company['company_name'];
			$this->_data[] = $entry;
		}
		$totals = $baseRow;
		$totals['company_name'] = 'DIVISION TOTALS';
		$totals['id'] = 'dt';
		$totals['excel.rowStyle'] = 'Totals';
		$this->_data[] = $totals;
		$totals = $totalRow;
		$totals['company_name'] = $grp . ': ' . $div;
		$totals['id'] = 'dn';
		$totals['excel.rowStyle'] = 'Totals';
		$this->_data[] = $totals;
		$this->_data[] = array();
		$totals = $baseRow;
		$totals['company_name'] = 'GROUP TOTALS';
		$totals['id'] = 'gt';
		$totals['excel.rowStyle'] = 'Totals';
		$this->_data[] = $totals;
		$totals = $totalRow;
		$totals['company_name'] = $grp;
		$totals['id'] = 'gn';
		$totals['excel.rowStyle'] = 'Totals';
		$this->_data[] = $totals;


		#-> Collect stats.
		$selection = array(
				'companyGroup.name', 'groupDivision.name', 'company.name', 'company.id',
				'COUNT(priceGuide.id) AS num_valuations',
				'SUM(stock.tradePrice) AS total_trade',
				'SUM(stock.damageTotal) AS total_recon',
				'SUM(stock.highestOffer) AS total_highestOffer',
				'SUM(stock.numberOfOffers) AS total_numOffers'
		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \Stock\Entity\Stock stock'
				. ' JOIN stock.priceGuide priceGuide '
				. ' JOIN stock.company company '
				. ' LEFT JOIN company.group companyGroup '
				. ' LEFT JOIN company.groupDivision groupDivision '
				. '[WHERE] '
				. 'GROUP BY companyGroup.name, groupDivision.name, company.name '
				. 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC';

		#-> Construct details.
		$where[] = 'stock.tradePrice > 0.0';
		$where[] = 'stock.numberOfOffers > 0';
		$authData = \Utility\Registry::getAuthData();if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
		{
			$where[] = 'priceGuide.created >= :dateFrom';
			$params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
		}
		if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
		{
			$where[] = 'priceGuide.created <= :dateTo';
			$params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
		}
		if (isset($this->_input['group'])
				&& !empty($this->_input['group'])
				&& 'null' != $this->_input['group'])
		{
			$where[] = 'companyGroup.id = :group';
			$params['group'] = $this->_input['group'];
		}
		if (isset($this->_input['groupDivision'])
				&& !empty($this->_input['groupDivision'])
				&& 'null' != $this->_input['groupDivision'])
		{
			$where[] = 'groupDivision.id = :division';
			$params['division'] = $this->_input['groupDivision'];
		}
		if (isset($this->_input['company'])
				&& !empty($this->_input['company'])
				&& 'null' != $this->_input['company'])
		{
			$where[] = 'company.id = :company';
			$params['company'] = $this->_input['company'];
		}

		#-> Finalize query.
		$query = str_replace(array(
				'[SELECTION]', '[WHERE]'
			), array(
				implode(', ', $selection) . ' ',
				!empty($where)
					? 'WHERE ' . implode(' AND ', $where) . ' '
					: ' '
			), $query);

		#-> Collect data.
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$stats = $query->getScalarResult();
		$this->em->clear();
		$grp = '';
		$div = '';
		$divTotals = $baseRow;
		$grpTotal = $baseRow;
		$stat = array_shift($stats);
		foreach ($this->_data as $i => $entry)
		{
			if (empty($entry))
			{
				continue;
			}
			switch ($entry['id'])
			{
				case 'h':
					break;
				case 'dt':
					$this->_data[$i]['num_valuations'] = $divTotals['num_valuations'];
					$this->_data[$i]['total_trade'] = $divTotals['total_trade'];
					$this->_data[$i]['total_recon'] = $divTotals['total_recon'];
					$this->_data[$i]['total_highestOffer'] = $divTotals['total_highestOffer'];
					$this->_data[$i]['percentage_toTrade'] = $divTotals['total_trade'] > 0
						? round((100 / $divTotals['total_trade']) * $divTotals['total_highestOffer'], 1)
						: 0;
					$this->_data[$i]['total_numOffers'] = $divTotals['total_numOffers'];
					break;
				case 'dn':
					$this->_data[$i]['total_numOffers'] = $divTotals['num_valuations'] > 0
						? round($divTotals['total_numOffers'] / $divTotals['num_valuations'], 2)
						: 0;
					$divTotals = $baseRow;
					break;
				case 'gt':
					$this->_data[$i]['num_valuations'] = $grpTotal['num_valuations'];
					$this->_data[$i]['total_trade'] = $grpTotal['total_trade'];
					$this->_data[$i]['total_recon'] = $grpTotal['total_recon'];
					$this->_data[$i]['total_highestOffer'] = $grpTotal['total_highestOffer'];
					$this->_data[$i]['percentage_toTrade'] = $grpTotal['total_trade'] > 0
						? round((100 / $grpTotal['total_trade']) * $grpTotal['total_highestOffer'], 1)
						: 0;
					$this->_data[$i]['total_numOffers'] = $grpTotal['total_numOffers'];
					break;
				case 'gn':
					$this->_data[$i]['total_numOffers'] = $grpTotal['num_valuations'] > 0
						? round($grpTotal['total_numOffers'] / $grpTotal['num_valuations'], 2)
						: 0;
					$grpTotal = $baseRow;
					break;
				default:
					if ($stat['id'] == $entry['id'])
					{
						#-> Update the entry.
						$this->_data[$i]['num_valuations'] = $stat['num_valuations'];
						$this->_data[$i]['total_trade'] = $stat['total_trade'];
						$this->_data[$i]['total_recon'] = $stat['total_recon'];
						$this->_data[$i]['total_highestOffer'] = $stat['total_highestOffer'];
						$this->_data[$i]['percentage_toTrade'] = $stat['total_trade'] > 0
							? round((100 / $stat['total_trade']) * $stat['total_highestOffer'], 1)
							: 0;
						$this->_data[$i]['total_numOffers'] = $stat['total_numOffers'];

						#-> Update the totals.
						$divTotals['num_valuations'] += $stat['num_valuations'];
						$divTotals['total_trade'] += $stat['total_trade'];
						$divTotals['total_recon'] += $stat['total_recon'];
						$divTotals['total_highestOffer'] += $stat['total_highestOffer'];
						$divTotals['total_numOffers'] += $stat['total_numOffers'];
						$grpTotal['num_valuations'] += $stat['num_valuations'];
						$grpTotal['total_trade'] += $stat['total_trade'];
						$grpTotal['total_recon'] += $stat['total_recon'];
						$grpTotal['total_highestOffer'] += $stat['total_highestOffer'];
						$grpTotal['total_numOffers'] += $stat['total_numOffers'];

						#-> Company handled, move on to next one.
						$stat = array_shift($stats);
					}
					break;
			}

		}
	}

}