<?php
namespace Report\Report;


/**
 * Stock Control Report.
 * @author andre.fourie
 */
class LoadVsSold extends \Utility\Service\Report
{


	/**
	 * @var string
	 */
	protected $_title  = 'Loaded and Sold';
	/**
	 * @var string
	 */
	protected $_subject = 'Vehicles loaded vs vehicles sold';
	/**
	 * @var string
	 */
	protected $_description = 'Vehicles loaded vs vehicles sold.';
	/**
	 * @var array
	 */
	protected $_notes = array(
			'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.co.za'
	);
	/**
	 * @var array
	 */
	protected $_headers = array(
			'Month',
			'Loaded',
			'Sold'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'month',
			'loaded',
			'sold'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
			'loaded',
			'sold'
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array();

	/**
	 * @var array
	 */

	/**
	 * Build the dataset.
	 */
	public function build()
	{
		$config = $this->em->getConfiguration();
    	$config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');
    	$config->addCustomStringFunction('SOUNDEX', 'UVd\DoctrineFunction\Soundex');

    	$year = $this->_input['year'];
		$this->_data = array();

		//Collect Data
		$selection = array(
				'division.name',
				'company.name as companyName',
				'tradeCenter.name as tradeCenterName',
				'manager.firstName',
				'manager.familyName',
				'stock.id as stockId',
				'SOUNDEX(stock.stockNumber) as stockNumber',
				'SOUNDEX(stock.registrationNumber) as registrationNumber',
				'SOUNDEX(stock.vinNumber) as vinNumber',
				'SOUNDEX(stock.engineNumber) as engineNumber',
				'DATE_FORMAT(auction.endDate ,\'%Y-%m\') as month',
				'auction.jobState'
			);
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \Stock\Entity\Stock stock '
				. 'JOIN stock.auction auction '
				. 'JOIN stock.company company '
				. 'LEFT JOIN company.tradeCenter tradeCenter '
        		. 'LEFT JOIN company.region region '
				. 'LEFT JOIN company.group grp '
				. 'LEFT JOIN company.groupDivision division '
        		. 'LEFT JOIN company.regionalManager manager '
				. '[WHERE] '
				. 'ORDER BY auction.endDate';
		$where[] = 'auction.jobState != :status';
		$params['status'] = 'Active';

		$this->_queries['Date Range From'] = $year . '-01-01';
		$where[] = 'auction.endDate >= :dateFrom';
		$params['dateFrom'] = new \DateTime($year . '-01-01');

		$this->_queries['Date Range Until'] = $year . '-12-31';
		$where[] = 'auction.endDate <= :dateTo';
		$params['dateTo'] = new \DateTime($year . '-12-31');

		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[] = 'grp.id = :group';
			$params['group'] = $this->_input['group'];
		}
		if (isset($this->_input['region'])
			&& !empty($this->_input['region'])
			&& 'null' != $this->_input['region'])
		{
			$this->_queries['Region'] = $this->em
				->find('\Location\Entity\Region', $this->_input['region'])
				->name;
			$where[] = 'region.id = :region';
			$params['region'] = $this->_input['region'];
		}

		if (isset($this->_input['groupDivision'])
			&& !empty($this->_input['groupDivision'])
			&& 'null' != $this->_input['groupDivision'])
		{
			$this->_queries['Division'] = $this->em
				->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
				->name;
			$where[] = 'division.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'];
		}

		/* if (isset($this->_input['regionalManager'])
		 && !empty($this->_input['regionalManager'])
				&& 'null' != $this->_input['regionalManager'])
		{
		$where[] = 'manager.id IN (:regionalManager)';
		$params['regionalManager'] = $this->_input['regionalManager'];
		} */

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

		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$result = $query->getScalarResult();
		$this->em->clear();

		$prepDvsn = array();
		$prepComp = array();
		$centers  = array();
		foreach ($result as $id => $row)
		{
			$combo = $row['name']
				. '.' . $row['firstName']
				. '.' . $row['familyName']
				. '.' . $row['stockNumber']
				. '.' . $row['registrationNumber']
				. '.' . $row['vinNumber']
				. '.' . $row['engineNumber']
				. '.' . $row['month'];
			$fullName = $row['firstName'] . ' ' . $row['familyName'];
			$divisionName = $row['name'];
			$companyName  = $row['companyName'];
			if (!is_null($row['tradeCenterName']))
			{
				$centers["$divisionName.$companyName"] = $row['tradeCenterName'];
			}

			#-> Division aggregation.
			if (!isset($prepDvsn[$divisionName]))
			{
				$prepDvsn[$divisionName] = array();
			}
			if (!isset($prepDvsn[$divisionName][$fullName]))
			{
				$prepDvsn[$divisionName][$fullName] = array();
			}
			if (!isset($prepDvsn[$divisionName][$fullName][$row['month']]))
			{
				$prepDvsn[$divisionName][$fullName][$row['month']] = array();
			}
			if (!isset($prepDvsn[$divisionName][$fullName][$row['month']][$combo]))
			{
				$prepDvsn[$divisionName][$fullName][$row['month']][$combo] = $row;
			}

			#-> Company aggregation.
			if (!isset($prepComp[$divisionName]))
			{
				$prepComp[$divisionName] = array();
			}
			if (!isset($prepComp[$divisionName][$fullName]))
			{
				$prepComp[$divisionName][$fullName] = array();
			}
			if (!isset($prepComp[$divisionName][$fullName][$companyName]))
			{
				$prepComp[$divisionName][$fullName][$companyName] = array();
			}
			if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']]))
			{
				$prepComp[$divisionName][$fullName][$companyName][$row['month']] = array();
			}
			if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo]))
			{
				$prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo] = $row;
			}
		}
		$this->_data['TradeCenterData'] = $centers;

		#-> Division data.
		$output = array();
		foreach ($prepDvsn as $division => $row1)
		{
			$output[$division] = array();
			foreach ($row1 as $manager => $row2)
			{
				$output[$division][$manager] = array();
				foreach ($row2 as $month => $row3)
				{
					$output[$division][$manager][$month] = array(
						'Relist' => 0,
						'Sold'   => 0,
						'Undone' => 0
					);
					foreach ($row3 as $combo => $data)
					{
						$output[$division][$manager][$month][$data['jobState']]++;
					}
				}
			}
		}
		$prepDvsn = null;
		$this->_data['DivisionData'] = $output;

		#-> Company data.
		$output = array();
		foreach ($prepComp as $division => $row1)
		{
			$output[$division] = array();
			foreach ($row1 as $manager => $row2)
			{
				$output[$division][$manager] = array();
				foreach ($row2 as $company => $row3)
				{
					$output[$division][$manager][$company] = array();
					foreach ($row3 as $month => $row4)
					{
						$output[$division][$manager][$company][$month] = array(
								'Relist' => 0,
								'Sold'   => 0,
								'Undone' => 0
						);
						foreach ($row4 as $combo => $data)
						{
							$output[$division][$manager][$company][$month][$data['jobState']]++;
						}
					}
				}
			}
		}
		$prepDvsn = null;
		$this->_data['CompanyData'] = $output;

		#-> Finalise.
		$this->_queries['year'] = $year;
		return;
	}

}