<?php
namespace Report\Report;


/**
 * Stock Control Report.
 * @author andre.fourie
 */
class LoadVsSoldDetail 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(
		'Group',
		'Division',
		'Trade Centre',
		'Dealership',
		'Region',
		'Date',
		'Reference',
		'Auction No',
		'Stock No',
		'Registration No',
		'VIN Number',
		'Make',
		'Type',
		'Trade Price',
		'Initial Reserve Price',
		'Reserve',
		'Sold',
		'Bids',
		'B4C Profit',
		'Kilometres'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
		'group_name',
		'division_name',
		'trade_centre',
		'company_name',
		'region_name',
		'auction_endDate',
		'reference',
		'auction_id',
		'stock_stockNumber',
		'stock_registrationNumber',
		'vin_number',
		'make_name',
		'type_name',
		'trade_price',
		'auction_initial_reservePrice',
		'auction_reservePrice',
		'auction_currentBidPrice',
		'auction_numberOfBids',
		'auction_profit',
		'stock_km'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
		'trade_price',
		'auction_reservePrice',
		'auction_currentBidPrice',
		'auction_numberOfBids',
		'auction_profit'
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array(
		'auction_reservePrice',
		'auction_currentBidPrice',
		'auction_profit'
	);


	/**
	 * Build the dataset.
	 */
	public function build()
	{
		#-> Prepare parameters.
		#-> Collect loaded and sold totals.
		$selection = array(
			'grp.name AS group_name',
			'division.name AS division_name',
			'company.name AS company_name',
			'(CASE WHEN stock.previousState = :tradeState THEN tradeCentre.name THEN :blank END) as trade_centre',
			'region.name AS region_name',
			'auction.endDate AS auction_endDate',
			'stock.referenceNumber as reference',
			'auction.id AS auction_id',
			'stock.stockNumber AS stock_stockNumber',
			'stock.registrationNumber AS stock_registrationNumber',
			'stock.vinNumber AS vin_number',
			'make.name AS make_name',
			'model.name AS model_name',
			'type.name AS type_name',
			'stock.tradePrice as trade_price',
			'auction.reservePrice AS auction_initial_reservePrice',
			'auction.reservePrice AS auction_reservePrice',
			'auction.currentBidPrice AS auction_currentBidPrice',
			'COUNT(DISTINCT bid.id) AS auction_numberOfBids',
			'(auction.currentBidPrice - auction.reservePrice) AS auction_profit', //sold - reserve price
			'stock.km AS stock_km',
			'auction.jobState AS status'
		);
		$join = array();
		$leftJion = array();
		$where = array();
		$params = array();
		$query = 'SELECT [SELECTION] '
			. 'FROM \Stock\Entity\Stock stock '
			. 'JOIN stock.auction auction '
			. 'JOIN stock.company company '
			. 'LEFT JOIN company.tradeCenter tradeCentre '
			. 'LEFT JOIN auction.bids bid '
			. 'LEFT JOIN company.city city '
			. 'LEFT JOIN city.region region '
			. 'LEFT JOIN stock.type type '
			. 'LEFT JOIN type.model model '
			. 'LEFT JOIN model.make make '
			. 'LEFT JOIN company.group grp '
			. 'LEFT JOIN company.groupDivision division '
			. '[WHERE] '
			. 'GROUP BY auction.id '
			. 'ORDER BY auction.endDate ASC';

		$where[] = 'auction.jobState != :status';
		$params['status'] = 'Active';
		$params['tradeState'] = 'Trade Center';
		$params['blank'] = '';

		if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
		{
			$this->_queries['Date Range From'] = $this->_input['dateFrom'];
			$where[] = 'auction.endDate >= :dateFrom';
			$params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
		}
		if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
		{
			$this->_queries['Date Range Until'] = $this->_input['dateTo'];
			$where[] = 'auction.endDate <= :dateTo';
			$params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
		}
		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['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[] = 'division.id = :division';
			$params['division'] = $this->_input['groupDivision'];
		}
		if (isset($this->_input['company'])
			&& !empty($this->_input['company'])
			&& 'null' != $this->_input['company']
		)
		{
			error_log($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);
		$this->_data = $query->getScalarResult();
		$this->em->clear();
		foreach ($this->_data as $id => $row)
		{
			if ('Sold' != $row['status'])
			{
				$this->_data[$id]['auction_currentBidPrice'] = 0.00;
				$this->_data[$id]['auction_bidIncrement'] = 0.00;
			}
			$query = 'SELECT auction.reservePrice '
				. 'FROM \Auction\Entity\Auction auction '
				. 'JOIN auction.stock stock '
				. 'WHERE stock.referenceNumber = :reference '
				. 'ORDER BY auction.endDate ASC';

			$params = array();
			$params['reference'] = $row['reference'];

			$query = $this->em->createQuery($query);
			$query->setParameters($params);
			$temp = $query->getScalarResult();

			$this->_data[$id]['auction_initial_reservePrice'] = $temp[0]['reservePrice'];
		}
	}

}