<?php
namespace Report\Report;


use Valuation;
/**
 * Stock Control Report.
 * @author andre.fourie
 */
class PriceGuideFlash extends \Utility\Service\Report
{


	/**
	 * @var string
	 */
	protected $_title  = 'PriceGuide Flash Report';
	/**
	 * @var string
	 */
	protected $_subject = 'PriceGuide Flash';
	/**
	 * @var string
	 */
	protected $_description = 'Amount of valuations send to 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(
			'Total Cars Loaded',
			'Total Cars Loaded on Price Guide',
			'With Offers',
			'%',
			'Total number of offers made',
			'Avg Number Offers',
			'Number with 3 offers +',
			'%',
			'Number with 2 offers',
			'%',
			'Number with 1 offer',
			'%',
			'Number with 0 offers',
			'%'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'totalCarsLoaded',
			'totalLoadedOnPg',
			'TotalCarsOffers',
			'TotalCarsOffersPer',
			'tNumOffers',
			'AvgNumOffers',
			'NumOffers3',
			'NumOffers3Per',
			'NumOffers2',
			'NumOffers2Per',
			'NumOffers1',
			'NumOffers1Per',
			'NumOffers0',
			'NumOffers0Per'
	);


	/**
	 * Build the dataset.
	 */
	public function build()
	{
		ini_set('memory_limit','512M');

		#-> Filtering.
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$stockWhere = array();
		$params    = array();

		if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
		{
			$this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
		}
		$this->_queries['Date Range From'] = $this->_input['dateFrom'];
		$where[] = 'priceGuide.created >= :dateFrom';
		$stockWhere[] = 'stock.created >= :dateFrom';
		$params['dateFrom'] = new \DateTime($this->_input['dateFrom']);

		if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
		{
			$this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
		}
		$this->_queries['Date Range Until'] = $this->_input['dateTo'];
		$where[] = 'priceGuide.created < :dateTo';
		$stockWhere[] = 'stock.created < :dateTo';
		$params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');

		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[] = 'IDENTITY(company.group) = :group';
			$stockWhere[] = 'IDENTITY(company.group) = :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[] = 'IDENTITY(company.groupDivision) = :division';
			$stockWhere[] = 'IDENTITY(company.groupDivision) = :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';
			$stockWhere[] = 'company.id = :company';
			$params['company'] = $this->_input['company'];
		}

		#-> Total Cars Loaded
		$query = 'SELECT COUNT(stock.id) as CarsLoaded '
					. 'FROM \Stock\Entity\Stock stock '
					. 'JOIN stock.company company '
					. '[WHERE]';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($stockWhere)
				? 'WHERE ' . implode(' AND ', $stockWhere) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data0 = $query->getScalarResult();
		$this->em->clear();

		#-> Total Cars Loaded on price guide
		$query = 'SELECT COUNT(DISTINCT priceGuide.id) AS CarsLoaded '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. '[WHERE]';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data1 = $query->getScalarResult();
		$this->em->clear();

		//\Utility\Debug::errorLog('data', $params);
		//var_dump($params);
		//exit();

		#-> Total cars that has offers
		$query = 'SELECT COUNT(DISTINCT priceGuide.id) AS NumCarsWithOffers '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'JOIN priceGuide.offers offer '
				. '[WHERE]';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data2 = $query->getScalarResult();
		$this->em->clear();

		#-> Total cars that has offers vs cars loaded in %
		$data3 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round(($data2[0]['NumCarsWithOffers']*100)/$data1[0]['CarsLoaded']);

		#-> Total number of offers made

		$query = 'SELECT COUNT(DISTINCT offers.id) AS NumOffersMade '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'JOIN priceGuide.offers offers '
				. '[WHERE]';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data4 = $query->getScalarResult();
		$this->em->clear();

		#-> Ave number of offers per Cars Loaded
		$data5 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round($data4[0]['NumOffersMade']/$data1[0]['CarsLoaded']);

		#-> Number with 3+ offers
		$query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'LEFT JOIN priceGuide.offers offer '
				. '[WHERE]'
				. 'GROUP BY priceGuide.id '
				. 'HAVING numOffers > 2';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data6 = count($query->getScalarResult());
		$this->em->clear();

		#-> Number with 3 offers %
		$data7 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round((100 / $data1[0]['CarsLoaded']) * $data6, 2);

		#-> Number with 2 offers
		$query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'LEFT JOIN priceGuide.offers offer '
				. '[WHERE]'
				. 'GROUP BY priceGuide.id '
				. 'HAVING numOffers = 2';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data8 = count($query->getScalarResult());
		$this->em->clear();

		#-> Number with 2 offers %
		$data9 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round((100 / $data1[0]['CarsLoaded']) * $data8, 2);

		#-> Number with 1 offers
		$query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'LEFT JOIN priceGuide.offers offer '
				. '[WHERE]'
				. 'GROUP BY priceGuide.id '
				. 'HAVING numOffers = 1';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data10 = count($query->getScalarResult());
		$this->em->clear();

		#-> Number with 1 offers %
		$data11 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round((100 / $data1[0]['CarsLoaded']) * $data10, 2);

		#-> Number with 0 offers
		$query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
				. 'JOIN priceGuide.company company '
				. 'LEFT JOIN priceGuide.offers offer '
				. '[WHERE]'
				. 'GROUP BY priceGuide.id '
				. 'HAVING numOffers = 0';
		$query = str_replace(array(
				'[WHERE]'
		), array(
				!empty($where)
				? 'WHERE ' . implode(' AND ', $where) . ' '
				: ' '
		), $query);
		$query = $this->em->createQuery($query);
		!empty($params)
			&& $query->setParameters($params);
		$data12 = count($query->getScalarResult());
		$this->em->clear();

		#-> Number with 1 offers %
		$data13 = 0 == $data1[0]['CarsLoaded']
			? 0
			: round((100 / $data1[0]['CarsLoaded']) * $data12, 2);

		#-> Change Data
		$data0val = $data0[0]['CarsLoaded'];
		$data1val = $data1[0]['CarsLoaded'];
		$data2val = $data2[0]['NumCarsWithOffers'];
		$data4val = $data4[0]['NumOffersMade'];

		#-> Collate information.
		$this->_data = array(array(
							'totalCarsLoaded' => $data0val,
							'totalLoadedOnPg' => $data1val,
							'TotalCarsOffers' => $data2val,
							'TotalCarsOffersPer' => $data3,
							'tNumOffers' => $data4val,
							'AvgNumOffers' => $data5,
							'NumOffers3' => $data6,
							'NumOffers3Per' => $data7,
							'NumOffers2' => $data8,
							'NumOffers2Per' => $data9,
							'NumOffers1' => $data10,
							'NumOffers1Per' => $data11,
							'NumOffers0' => $data12,
							'NumOffers0Per' => $data13
			));
		foreach ($this->_data as $id => $row)
		{
			//
		}
	}

}