<?php
namespace Report\Report;


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


	/**
	 * @var string
	 */
	protected $_title  = 'PriceGuide Report';
	/**
	 * @var string
	 */
	protected $_subject = 'PriceGuide';
	/**
	 * @var string
	 */
	protected $_description = 'Historical 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(
			'Seller Group',
			'Seller Division',
			'Seller Dealership',
			'Seller User Name',
			'Seller User Surname',
			'Buyer Group',
			'Buyer Division',
			'Buyer Dealership',
			'Buyer User Name',
			'Buyer User Surname',
			'Vehicle Make',
			'Model',
			'Type',
			'Year',
			'Stock No.',
			'VIN No.',
			'Engine No.',
			'Registration No.',
			'Trade Price',
			'Retail Price',
			'Date Loaded',
			'Date Offered',
			'Highest Offer',
			'Commission',
			'Automate Matched',
			'Automate Duplicates',
			'Matches to multiple stock entries',
			'Date invoiced on Automate',
			'Source',
			'Source file',
			'Branch code',
			'Invoice price',
			'Sold to'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'sellerGroup_name',
			'sellerDivision_name',
			'sellerCompany_name',
			'sellerProfile_firstName',
			'sellerProfile_familyName',
			'buyerGroup_name',
			'buyerDivision_name',
			'buyerCompany_name',
			'buyerProfile_firstName',
			'buyerProfile_familyName',
			'make_name',
			'model_name',
			'type_name',
			'year_name',
			'stock_stockNumber',
			'stock_vinNumber',
			'stock_engineNumber',
			'stock_registrationNumber',
			'stock_tradePrice',
			'stock_retailPrice',
            'stock_listPrice',
			'priceGuide_created',
			'priceGuide_endDate',
			'stock_highestOffer',
			'commission',
			'isMatched',
			'isDuplicate',
			'multiMatch',
			'adherence_dateSold',
			'adherence_type',
			'adherence_file',
			'adherence_dealerCode',
			'adherence_amount',
			'adherence_soldTo'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
			'stock_highestOffer',
			'commission',
			'adherence_amount'
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array(
			'stock_highestOffer',
			'commission',
			'adherence_amount'
	);


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

		#-> Prepare parameters.
		$selection = array(
				'stock', 'priceGuide', 'offer', 'type', 'model', 'make', 'year',
				'sellerCompany', 'sellerGroup', 'sellerDivision', 'sellerProfile',
				'buyerCompany', 'buyerGroup', 'buyerDivision', 'buyerProfile',
				'adherence'
		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \PriceGuide\Entity\PriceGuide priceGuide'
				. ' JOIN priceGuide.stock stock '
				. ' JOIN priceGuide.offers offer WITH offer.amount=stock.highestOffer '
				. ' JOIN stock.adherence link '
				. ' JOIN link.adherence adherence '
				. ' JOIN stock.type type '
				. ' JOIN stock.vehicleYear year '
				. ' JOIN type.model model '
				. ' JOIN model.make make '
				. ' JOIN priceGuide.createdBy sellerProfile '
				. ' JOIN sellerProfile.company sellerCompany '
				. ' JOIN offer.profile buyerProfile '
				. ' JOIN offer.company buyerCompany '
				. ' LEFT JOIN sellerCompany.group sellerGroup '
				. ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
				. ' LEFT JOIN buyerProfile.group buyerGroup '
				. ' LEFT JOIN buyerProfile.groupDivision buyerDivision '
				. '[WHERE] '
				. 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';

		#-> Construct details.
		$authData = \Utility\Registry::getAuthData();
		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[] = 'adherence.dateSold >= :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[] = 'adherence.dateSold < :dateTo';
		$params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');

		$where[] = 'priceGuide.created >= :datePgFrom';
		$where[] = 'priceGuide.created <= :dateEnd';
		$params['dateEnd'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -3 days');
		$params['datePgFrom'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -4 months');

		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[] = 'sellerGroup.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[] = 'sellerDivision.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[] = 'sellerCompany.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);
		$this->_data = $query->getScalarResult();
		$this->em->clear();

		#-> Additional info.
		$increments = $this->em
			->getRepository('\Auction\Entity\Increment')
			->findBy(array(), array('to' => 'ASC'));
		$prevId = 0;
		$matched = array();
		foreach ($this->_data as $id => $row)
		{
			if (!isset($matched[$row['adherence_id']]))
			{
				$matched[$row['adherence_id']] = array();
			}
			$matched[$row['adherence_id']][$row['stock_id']] = true;
		}
		foreach ($this->_data as $id => $row)
		{
			$isDuplicate = true;
			if ($prevId != $row['adherence_id'])
			{
				$prevId = $row['adherence_id'];
				$isDuplicate = false;
			}
			$multimatch = 1 < count($matched[$row['adherence_id']])
				? true
				: false;
			$endDate = new \DateTime($row['priceGuide_created']->format(\Utility\Definitions\Locale::getDateTimeFormat()));
			$endDate->add(new \DateInterval('P3D'));
			$this->_data[$id]['priceGuide_endDate'] = $endDate;
			$this->_data[$id]['commission'] = !$isDuplicate
				? 1000.00
				: 0.00;
			$this->_data[$id]['isMatched'] = !is_null($row['adherence_id'])
				? 'Yes'
				: 'No';
			$this->_data[$id]['multiMatch'] = $multimatch
				? 'Yes'
				: 'No';
			$this->_data[$id]['isDuplicate'] = $isDuplicate
				? 'Yes'
				: 'No';
		}


		#-> No match section.
		#-> Collect some data.
		$selection = array(
				'adherence', 'sellerCompany', 'sellerGroup', 'sellerDivision'

		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \Adherence\Entity\Adherence adherence'
				. ' LEFT JOIN adherence.stock stock '
				. ' LEFT JOIN adherence.company sellerCompany '
				. ' LEFT JOIN sellerCompany.group sellerGroup '
				. ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
				. '[WHERE] '
				. 'ORDER BY adherence.id ASC';
		$where[] = 'stock.id IS NULL';

		#-> Construct details.
		$authData = \Utility\Registry::getAuthData();
		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[] = 'adherence.dateSold >= :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[] = 'adherence.dateSold < :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[] = 'sellerGroup.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[] = 'sellerDivision.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[] = 'sellerCompany.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);
		$data = $query->getScalarResult();
		$this->em->clear();
		$prevId = 0;
		foreach ($data as $id => $row)
		{
			$isDuplicate = true;
			if ($prevId != $row['adherence_id'])
			{
				$prevId = $row['adherence_id'];
				$isDuplicate = false;
			}
			$this->_data[] = array(
					'sellerGroup_name'			=> $row['sellerGroup_name'],
					'sellerDivision_name'		=> $row['sellerDivision_name'],
					'sellerCompany_name'		=> $row['sellerCompany_name'],
					'sellerProfile_firstName' 	=> '',
					'sellerProfile_familyName' 	=> '',
					'buyerGroup_name' 			=> '',
					'buyerDivision_name' 		=> '',
					'buyerCompany_name' 		=> '',
					'buyerProfile_firstName' 	=> '',
					'buyerProfile_familyName' 	=> '',
					'make_name' 				=> $row['adherence_make'],
					'model_name' 				=> '',
					'type_name' 				=> '',
					'year_name' 				=> '',
					'stock_stockNumber' 		=> $row['adherence_stockNumber'],
					'stock_vinNumber' 			=> $row['adherence_vinNumber'],
					'stock_engineNumber' 		=> $row['adherence_engineNumber'],
					'stock_registrationNumber' 	=> $row['adherence_registrationNumber'],
					'stock_tradePrice' 			=> 0,
					'stock_retailPrice' 		=> 0,
                    'stock_listPrice' 			=> 0,
					'priceGuide_created' 		=> '',
					'priceGuide_endDate' 		=> '',
					'stock_highestOffer' 		=> '',
					'commission' 				=> 1000.00,
					'isMatched' 				=> 'No',
					'isDuplicate' 				=> $isDuplicate
													? 'Yes'
													: 'No',
					'multiMatch' 				=> 'No',
					'adherence_dateSold'		=> $row['adherence_dateSold'],
					'adherence_type'			=> $row['adherence_type'],
					'adherence_file'			=> $row['adherence_file'],
					'adherence_dealerCode'		=> $row['adherence_dealerCode'],
					'adherence_amount'			=> $row['adherence_amount'],
					'adherence_soldTo'			=> $row['adherence_soldTo']
			);
		}
	}

}