<?php
namespace Report\Report;


/**
 * Stock Contyrol Report.
 * @author andre.fourie
 */
class Invoice extends \Utility\Service\Report
{


	/**
	 * @var string
	 */
	protected $_title  = 'Invoice Report';
	/**
	 * @var string
	 */
	protected $_subject = 'Invoice';
	/**
	 * @var string
	 */
	protected $_description = 'Historical report for invoice data.';
	/**
	 * @var array
	 */
	protected $_notes = array(
			'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
	);
	/**
	 * @var array
	 */
	protected $_headers = array(
			'Group',
			'Division',
			'Dealership',
			'SMS Quantity Total',
			'SMS Rand Value Total',
			'Sold on Auction',
			'Auction Invoice Total',
			'Price Guide Offer Matched',
			'Commission',
			'Price Guide Matched Duplicates',
			'Matched to multiple stock entries'
	);
	/**
	 * @var array
	 */
	protected $_fields = array(
			'fromGroup_name',
			'fromDivision_name',
			'fromCompany_name',
			'clickCount',
			'total',
			'auctionItems',
			'auctionTotal',
			'pgTotal',
			'pgCommission',
			'pgDuplicates',
			'pgMultiMatch'
	);
	/**
	 * @var array
	 */
	protected $_totals = array(
			'clickCount',
			'total',
			'auctionItems',
			'auctionTotal',
			'pgTotal',
			'pgCommission'
	);
	/**
	 * @var array
	 */
	protected $_currencyFields = array(
			'total',
			'auctionTotal',
			'pgCommission'
	);


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

		#-> Prepare parameters.
		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')));
		}
		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')));
		}
		$selection = array(
				'log', 'fromCompany', 'fromGroup', 'fromDivision'
		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \Utility\Entity\NotificationLog log '
				. ' LEFT JOIN log.fromCompany fromCompany '
				. ' LEFT JOIN fromCompany.group fromGroup '
				. ' LEFT JOIN fromCompany.groupDivision fromDivision '
				. '[WHERE] '
				. 'ORDER BY fromGroup.name, fromDivision.name, fromCompany.name';

		#-> Construct details.
		$clickVal = isset($this->_input['clickVal']) && is_numeric($this->_input['clickVal'])
			? $this->_input['clickVal']
			: 0.25;
		if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
		{
			$this->_queries['Date Range From'] = $this->_input['dateFrom'];
			$where[] = 'log.created >= :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[] = 'log.created <= :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[] = 'fromGroup.id = :group';
			$params['group'] = $this->_input['group'];
		}
		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[] = 'fromDivision.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[] = 'fromCompany.id = :company';
			$params['company'] = $this->_input['company'];
		}
		$where[] = 'log.smsTo IS NOT NULL AND log.smsBody != \'\'';

		#-> 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();
		$this->_data = array();
		$company = false;
		$entry = array();
		foreach ($data as $id => $row)
		{
			if ($company !== $row['fromCompany_name'])
			{
				if (false != $company)
				{
					$this->_data[$company] = $entry;
				}
				$company = $row['fromCompany_name'];
				$entry = $row;
				$entry['clickCount'] 	= 0;
				$entry['total'] 		= 0;
				$entry['auctionItems'] 	= 0;
				$entry['auctionTotal'] 	= 0;
				$entry['pgTotal'] 		= 0;
				$entry['pgCommission'] 	= 0.0;
				$entry['pgDuplicates'] 	= '';
				$entry['pgMultiMatch'] 	= '';
			}
			$entry['clickCount'] += strlen($row['log_smsBody']) > 160
				? ceil(strlen($row['log_smsBody']) / (160 - 7))
				: 1;
			$entry['total'] = round($entry['clickCount'] * $clickVal, 2);
		}
		if (!empty($entry))
		{
			$this->_data[$company] = $entry;
		}


		#-> Auction data.
		$selection = array(
				'auction', 'company', 'companyGroup', 'companyDivision',
				'COUNT(auction) as auctionItems, SUM(auction.bidIncrement) as auctionTotal'
		);
		$join      = array();
		$leftJion  = array();
		$where     = array();
		$params    = array();
		$query = 'SELECT [SELECTION] '
				. 'FROM \Auction\Entity\Auction auction '
				. ' JOIN auction.stock stock '
				. ' JOIN stock.company company '
				. ' LEFT JOIN company.group companyGroup '
				. ' LEFT JOIN company.groupDivision companyDivision '
				. '[WHERE] '
				. 'GROUP BY companyGroup.name, companyDivision.name, company.name';
		$where[] = 'auction.jobState=\'Sold\'';

		#-> Construct details.
		if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
		{
			$where[] = 'auction.endDate >= :dateFrom';
			$params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
		}
		if (isset($this->_input['dateTo']) && !empty($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'])
		{
			$where[] = 'companyGroup.id = :group';
			$params['group'] = $this->_input['group'];
		}
		if (isset($this->_input['groupDivision'])
				&& !empty($this->_input['groupDivision'])
				&& 'null' != $this->_input['groupDivision'])
		{
			$where[] = 'companyDivision.id = :division';
			$params['division'] = $this->_input['groupDivision'];
		}
		if (isset($this->_input['company'])
				&& !empty($this->_input['company'])
				&& 'null' != $this->_input['company'])
		{
			$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);
		$data = $query->getScalarResult();
		$this->em->clear();
		foreach ($data as $id => $row)
		{
			if (isset($this->_data[$row['company_name']]))
			{
				$this->_data[$row['company_name']]['auctionItems'] += $row['auctionItems'];
				$this->_data[$row['company_name']]['auctionTotal'] += $row['auctionTotal'];
			}
			else
			{
				$this->_data[$row['company_name']] = array(
					'fromGroup_name' 	=> $row['companyGroup_name'],
					'fromDivision_name' => $row['companyDivision_name'],
					'fromCompany_name' 	=> $row['company_name'],
					'clickCount' 		=> 0,
					'total' 			=> 0.0,
					'auctionItems' 		=> $row['auctionItems'],
					'auctionTotal' 		=> $row['auctionTotal'],
					'pgTotal' 			=> 0,
					'pgCommission' 		=> 0.0,
					'pgDuplicates' 		=> '',
					'pgMultiMatch' 		=> ''
				);
			}
		}


		#-> Price Guide.
		$selection = array(
				'stock', 'priceGuide', 'offer',
				'sellerCompany', 'sellerGroup', 'sellerDivision',
				'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 priceGuide.createdBy sellerProfile '
				. ' JOIN sellerProfile.company sellerCompany '
				. ' LEFT JOIN sellerCompany.group sellerGroup '
				. ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
				. '[WHERE] '
				. 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';

		#-> Construct details.
		$where[] = 'adherence.dateSold >= :dateFrom';
		$params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
		$where[] = 'adherence.dateSold < :dateTo';
		$params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
		$where[] = 'priceGuide.created >= :datePgFrom';
		$where[] = 'priceGuide.created <= :dateTo';
		$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'])
		{
			$where[] = 'sellerGroup.id = :group';
			$params['group'] = $this->_input['group'];
		}
		if (isset($this->_input['groupDivision'])
				&& !empty($this->_input['groupDivision'])
				&& 'null' != $this->_input['groupDivision'])
		{
			$where[] = 'sellerDivision.id = :division';
			$params['division'] = $this->_input['groupDivision'];
		}
		if (isset($this->_input['company'])
				&& !empty($this->_input['company'])
				&& 'null' != $this->_input['company'])
		{
			$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();

		#-> Additional info.
		$prevId = 0;
		$matched = array();
		foreach ($data as $id => $row)
		{
			if (!isset($matched[$row['adherence_id']]))
			{
				$matched[$row['adherence_id']] = array();
			}
			$matched[$row['adherence_id']][$row['stock_id']] = true;
		}
		foreach ($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;

			if (isset($this->_data[$row['sellerCompany_name']]))
			{
				$this->_data[$row['sellerCompany_name']]['pgTotal'] = 1;
				$this->_data[$row['sellerCompany_name']]['pgCommission'] += ($isDuplicate ? 0.0 : 1000.0);
				$this->_data[$row['sellerCompany_name']]['pgDuplicates'] =
					$isDuplicate || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgDuplicates']
						? 'Yes'
						: '';
				$this->_data[$row['sellerCompany_name']]['pgMultiMatch'] =
					$multimatch || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgMultiMatch']
						? 'Yes'
						: '';
			}
			else
			{
				$this->_data[$row['sellerCompany_name']] = array(
						'fromGroup_name' 	=> $row['sellerGroup_name'],
						'fromDivision_name' => $row['sellerDivision_name'],
						'fromCompany_name' 	=> $row['sellerCompany_name'],
						'clickCount' 		=> 0,
						'total' 			=> 0.0,
						'auctionItems' 		=> 0,
						'auctionTotal' 		=> 0.0,
						'pgTotal' 			=> 1,
						'pgCommission' 		=> $isDuplicate ? 0.0 : 1000.0,
						'pgDuplicates' 		=> $isDuplicate ? 'Yes' : '',
						'pgMultiMatch' 		=> $multimatch ? 'Yes' : ''
				);
			}
		}
	}

}