<?php
namespace Report\Report;


/**
 * Trade offers Report.
 * @author andre.fourie
 */
class Trader extends \Utility\Service\Report
{


	/**
	 * @var string
	 */
	protected $_title  = 'Trader Report';
	/**
	 * @var string
	 */
	protected $_subject = 'Trade Offers';
	/**
	 * @var string
	 */
	protected $_description = 'Historical report for trade offers 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(
			'Dealership',
			'Total Offers',
			'Total Highest Offers',
			'Total Trade Value',
			'Total Offer Value',
			'Percentage',
			'Total Trade Value (Highest Offers)',
			'Total Offer Value (Highest Offers)',
			'Percentage (Highest Offers)',
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'company_name',
			'num_offers',
			'num_highest',
			'sum_trade',
			'sum_offers',
			'perc',
			'sum_trade_highest',
			'sum_offers_highest',
			'perc_highest'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
			'num_offers',
			'num_highest',
			'sum_trade',
			'sum_offers',
			'sum_trade_highest',
			'sum_offers_highest'
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array(
			'sum_trade',
			'sum_offers',
			'sum_trade_highest',
			'sum_offers_highest'
	);


	/**
	 * Build the dataset.
	 */
	public function build()
	{
		#-> Prepare parameters.
		$selection = array(
				'company.name',
				'COUNT(offer.id) AS num_offers',
				'SUM(IF(offer.amount = stock.highest_offer, 1, 0)) AS num_highest',
				'SUM(stock.trade_price) as sum_trade',
				'SUM(offer.amount) AS sum_offers',
				'ROUND((100/SUM(stock.trade_price)) * SUM(offer.amount), 2) as perc',
				'SUM(IF(offer.amount = stock.highest_offer, stock.trade_price, 0)) as sum_trade_highest',
				'SUM(IF(offer.amount = stock.highest_offer, offer.amount, 0)) AS sum_offers_highest',
				'ROUND((100/SUM(IF(offer.amount = stock.highest_offer, stock.trade_price, 0))) * SUM(IF(offer.amount = stock.highest_offer, offer.amount, 0)), 2) as perc_highest'
		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM stock '
				. ' JOIN price_guide priceGuide ON priceGuide.id = stock.price_guide_id '
				. ' JOIN price_guide_offer offer ON offer.price_guide_id=priceGuide.id '
				. ' JOIN company company ON company.id=offer.company_id '
				. '[WHERE] '
				. 'GROUP BY company.name '
				. 'ORDER BY company.name ASC';
		$where[] = array(
				'offer.created BETWEEN :start AND :end',
				'offer.updated BETWEEN :start AND :end'
		);
		$where[] = array(
				'stock.vehicle_natis_id IS NULL',
				'stock.vehicle_natis_id IN (1, 2)'
		);
		$where[] = 'stock.trade_price IS NOT NULL';
		$where[] = 'stock.trade_price > 0';

		#-> Construct details.
		$authData = \Utility\Registry::getAuthData();
		if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
		{
			if ($this->_input['dateTo'] && !empty($this->_input['dateTo']))
			{
				$parts = explode('-', $this->_input['dateTo']);
				$this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, $parts[1], 1, $parts[0]));
			}
			else
			{
				$this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
				$this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, date('m') - 1, 1, date('Y')));
			}
		}
		$this->_queries['Date Range From'] = $this->_input['dateFrom'];
		$params['start'] = new \DateTime($this->_input['dateFrom']);
		if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
		{
			$parts = explode('-', $this->_input['dateFrom']);
			$this->_input['dateTo'] = date('Y-m-d', mktime(1, 0, 0, $parts[1] + 1, 0, $parts[0]));
		}
		$this->_queries['Date Range Until'] = $this->_input['dateTo'];
		$params['end'] = 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[] = 'company.company_group_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[] = 'company.company_group_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'];
		}

		#-> Finalize query.
		foreach ($where as $key => $value)
		{
			if (is_array($value))
			{
				$where[$key] = '(' . implode(' OR ', $value) . ')';
			}
		}
		$query = str_replace(array(
				'[SELECTION]', '[WHERE]'
			), array(
				implode(', ', $selection) . ' ',
				!empty($where)
					? 'WHERE ' . implode(' AND ', $where) . ' '
					: ' '
			), $query);

		#-> Collect data.
		$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->em);
		$rsm->addRootEntityFromClassMetadata('\Stock\Entity\Stock', 'stock');
		$rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\PriceGuide', 'priceGuide', 'stock', 'priceGuide',
				array(
						'id' 					=> 'pg_id',
						'company_id' 			=> 'pg_company_id',
						'created_by_profile_id' => 'pg_created_by_profile_id',
						'stock_valuation_id' 	=> 'pg_stock_valuation_id',
						'previous_status' 		=> 'pg_previous_status',
						'status' 				=> 'pg_status',
						'created' 				=> 'pg_created',
						'updated' 				=> 'pg_updated',
						'archived' 				=> 'pg_archived'
				)
		);
		$rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\Offer', 'offer', 'priceGuide', 'offers',
				array(
						'id' 					=> 'pgo_id',
						'company_id' 			=> 'pgo_company_id',
						'profile_id' 			=> 'pgo_profile_id',
						'price_guide_id' 		=> 'pgo_price_guide_id',
						'previous_status' 		=> 'pgo_previous_status',
						'status' 				=> 'pgo_status',
						'created' 				=> 'pgo_created',
						'updated' 				=> 'pgo_updated',
						'archived' 				=> 'pgo_archived'
				)
		);
		$rsm->addJoinedEntityFromClassMetadata('\Company\Entity\Company', 'company', 'offer', 'company',
				array(
						'id' 					=> 'cmp_id',
						'lib_region_id' 		=> 'cmp_lib_region_id',
						'status' 				=> 'cmp_status',
						'created' 				=> 'cmp_created',
						'updated' 				=> 'cmp_updated',
						'archived' 				=> 'cmp_archived'
				)
		);
		$rsm->addScalarResult('num_offers', 'num_offers');
		$rsm->addScalarResult('num_highest', 'num_highest');
		$rsm->addScalarResult('sum_trade', 'sum_trade');
		$rsm->addScalarResult('sum_offers', 'sum_offers');
		$rsm->addScalarResult('perc', 'perc');
		$rsm->addScalarResult('sum_trade_highest', 'sum_trade_highest');
		$rsm->addScalarResult('sum_offers_highest', 'sum_offers_highest');
		$rsm->addScalarResult('perc_highest', 'perc_highest');
		$query = $this->em->createNativeQuery($query, $rsm);
		!empty($params)
			&& $query->setParameters($params);

		$this->_data = $query->getScalarResult();
		$this->em->clear();
	}

}