<?php
namespace Report\Report;



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



	/**
	 * @var string
	 */
	protected $_title  = 'AuctionAdherence Report';
	/**
	 * @var string
	 */
	protected $_subject = 'AuctionAdherence';
	/**
	 * @var string
	 */
	protected $_description = 'Details of vehicles found on Bid4Cars auction that was sold/invoiced through automate.';
	/**
	 * @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',
    'Stock No',
    'Make',
    'VIN Number',
    'Registration',
    'Engine Number',
    'Total Cost (incl)',
    'Invoiced (incl)',
    'SPL True',
    'Invoiced To',
    'Sold To',
    'Date Loaded',
    'Date Sold',
    'Initial Reserve',
    'VIN Matched',
    'Reg Matched',
    'Loaded',
    'Sold',
    'Variance against Potential'
  );

  /**
   * @var array
   */
  protected $_fields = array(
    'group_name',
    'division_name',
    'trade_centre_name',
    'company_name',
    'region_name',
    'stock_stockNumber',
    'make_name',
    'vin_number',
    'reg_number',
    'eng_number',
    'total_cost',
    'sales_total',
    'spl',
    'sold_to',
    'sold_to_name',
    'date_loaded',
    'auction_endDate',
    'auction_initial_reservePrice',
    'vin_match',
    'reg_match',
    'auction_reservePrice',
    'auction_sold_price',
    'potential'
  );

  /**
   * @var array
   */
  protected $_totals = array(
    'total_cost',
    'sales_total',
    'auction_reservePrice',
    'auction_sold_price',
    'potential'
  );

  /**
   * @var array
   */
  protected $_currencyFields = array(
    'auction_reservePrice',
    'auction_currentBidPrice',
    'auction_bidIncrement'
  );

  /**
   * 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',
      'trade_center.name AS trade_centre_name',
      'region.name as region_name',
      'stock.stockNumber AS stock_stockNumber',
      'make.name AS make_name',
      'stock.vinNumber as vin_number',
      'stock.registrationNumber as reg_number',
      'stock.engineNumber as eng_number',
      'ts.totalCost as total_cost',
      'ts.salesTotal as sales_total',
      '(ts.salesTotal - ts.totalCost) as spl',
      'ts.soldTo as sold_to',
      'sold_to_company.name as sold_to_name',
      'auction.startDate as date_loaded',
      '(CASE WHEN auction.jobState = :auctionState THEN auction.endDate THEN :notApplicable END) as auction_endDate',
      'auction.reservePrice AS auction_initial_reservePrice',
      'ts.vinMatch as vin_match',
      'ts.regMatch as reg_match',
      'auction.reservePrice AS auction_reservePrice',
      '(CASE WHEN auction.jobState = :auctionState THEN auction.currentBidPrice THEN :notApplicable END) as auction_sold_price',
      '(CASE WHEN auction.jobState = :auctionState THEN auction.currentBidPrice - ts.salesTotal THEN :notApplicable END) as potential',
      'stock.referenceNumber as reference',
    );
    $join = array();
    $leftJion = array();
    $where = array();
    $params = array();
    $query = 'SELECT [SELECTION] '
      . 'FROM \Adherence\Entity\TradeSales ts '
      . 'LEFT JOIN ts.stock stock '
      . 'LEFT JOIN stock.auction auction '
      . 'LEFT JOIN auction.soldToCompany sold_to_company '
      . 'LEFT JOIN stock.company company '
      . 'LEFT JOIN company.region region '
      . 'LEFT JOIN company.tradeCenter trade_center '
      . '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] '
      . 'ORDER BY ts.saleDate ASC';

    $params['auctionState'] = 'Sold';
    $params['notApplicable'] = 'N/A';

    $where[] = '(auction.jobState != :status OR auction.jobState IS NULL)';
    $params['status'] = 'Undone';

    $where[] = '(auction.jobState != :status2 OR auction.jobState IS NULL)';
    $params['status2'] = 'Relist';

    if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
    {
      $this->_queries['Date Range From'] = $this->_input['dateFrom'];
      $where[] = 'ts.saleDate >= :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[] = 'ts.saleDate <= :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);
    error_log($query->getSQL());
    $this->_data = $query->getScalarResult();
    $this->em->clear();
    foreach ($this->_data as $id => $row)
    {
      if ($row['reference'])
      {
        $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'];
      }
    }
  }

}