xp-dev.com Git - namibia/blob - module/Report/src/Report/Report/AuctionTrader.php

   1 <?php
   2
   3 namespace Report\Report;
   4
   5 /**
   6  * Trade offers Report.
   7  * @author andre.fourie
   8  */
   9 class AuctionTrader extends \Utility\Service\Report
  10 {
  11
  12         /**
  13          * @var string
  14          */
  15         protected $_title = 'Auction Trader Report';
  16
  17         /**
  18          * @var string
  19          */
  20         protected $_subject = 'Auction Trader Report';
  21
  22         /**
  23          * @var string
  24          */
  25         protected $_description = 'Historical report for trade offers on Auction.';
  26
  27         /**
  28          * @var array
  29          */
  30         protected $_notes = array(
  31                 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
  32         );
  33
  34         /**
  35          * @var array
  36          */
  37         protected $_headers = array(
  38                 'Region',
  39                 'Dealership',
  40                 'Buyer Contact Person',
  41                 'Mobile Number',
  42                 'Total Bids',
  43                 'Total Winning Bids',
  44                 'Total Trade Value (Highest Bids)',
  45                 'Total Bid Value (Highest Bid)',
  46                 'Percentage (Highest Bid)'
  47         );
  48
  49         /**
  50          * @var array
  51          */
  52         protected $_fields = array(
  53                 'region_name',
  54                 'company_name',
  55                 'fullname',
  56                 'mobile',
  57                 'bids',
  58                 'winning',
  59                 'tradePrice',
  60                 'bidPrice',
  61                 'percentage'
  62         );
  63
  64         /**
  65          * @var array
  66          */
  67         protected $_totals = array(
  68                 'bids',
  69                 'winning',
  70                 'tradePrice',
  71                 'bidPrice',
  72         );
  73
  74         /**
  75          * @var array
  76          */
  77         protected $_currencyFields = array(
  78                 'tradePrice',
  79                 'bidPrice',
  80         );
  81
  82         /**
  83          * Build the dataset.
  84          */
  85         public function build()
  86         {
  87                 #-> Prepare parameters.
  88                 $selection = array(
  89                         'region.name as region_name',
  90                         'company.name as company_name',
  91                         'profile.firstName as firstName',
  92                         'profile.familyName as familyName',
  93                         'profile.mobile as mobile',
  94                         'COUNT(DISTINCT auction.id) as bids',
  95                         'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN 1 ELSE 0 END) AS winning',
  96                         'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN stock.tradePrice ELSE 0 END) AS tradePrice',
  97                         'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN auction.currentBidPrice ELSE 0 END) AS bidPrice'
  98                 );
  99                 $where = array();
 100                 $params = array();
 101                 $query = 'SELECT [SELECTION] FROM \Auction\Entity\Bid bid '
 102                         . ' LEFT JOIN bid.auction auction '
 103                         . ' LEFT JOIN auction.stock stock '
 104                         . ' LEFT JOIN bid.company company '
 105                         . ' LEFT JOIN bid.profile profile'
 106                         . ' LEFT JOIN company.region region '
 107                         . ' LEFT JOIN company.group grp '
 108                         . ' LEFT JOIN company.groupDivision division '
 109                         . '[WHERE] '
 110                         . 'GROUP BY bid.profile';
 111
 112                 $where[] = 'bid.created >= :start';
 113                 $where[] = 'bid.created <= :end';
 114
 115                         #-> Construct details.
 116                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
 117                 {
 118                         if ($this->_input['dateTo'] && !empty($this->_input['dateTo']))
 119                         {
 120                                 $parts = explode('-', $this->_input['dateTo']);
 121                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, $parts[1], 1, $parts[0]));
 122                         }
 123                         else
 124                         {
 125                                 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
 126                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, date('m') - 1, 1, date('Y')));
 127                         }
 128                 }
 129                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
 130                 {
 131                         $parts = explode('-', $this->_input['dateFrom']);
 132                         $this->_input['dateTo'] = date('Y-m-d', mktime(1, 0, 0, $parts[1] + 1, 0, $parts[0]));
 133                 }
 134
 135                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 136                 $params['start'] = new \DateTime($this->_input['dateFrom']);
 137
 138                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 139                 $params['end'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 140
 141                 if (isset($this->_input['group']) && !empty($this->_input['group']) && 'null' != $this->_input['group'])
 142                 {
 143                         $this->_queries['Group'] = $this->em
 144                                         ->find('\Company\Entity\Group', $this->_input['group'])
 145                                         ->name;
 146                         $where[] = 'grp.id = :group';
 147                         $params['group'] = $this->_input['group'];
 148                 }
 149
 150                 if (isset($this->_input['groupDivision']) && !empty($this->_input['groupDivision']) && 'null' != $this->_input['groupDivision'])
 151                 {
 152                         $this->_queries['Division'] = $this->em
 153                                         ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 154                                 ->name;
 155                         $where[] = 'division.id = :division';
 156                         $params['division'] = $this->_input['groupDivision'];
 157                 }
 158                 if (isset($this->_input['company']) && !empty($this->_input['company']) && 'null' != $this->_input['company'])
 159                 {
 160                         $this->_queries['Dealership'] = $this->em
 161                                         ->find('\Company\Entity\Company', $this->_input['company'])
 162                                 ->name;
 163                         $where[] = 'company.id = :company';
 164                         $params['company'] = $this->_input['company'];
 165                 }
 166
 167                 #-> Finalize query.
 168                 $query = str_replace(array(
 169                         '[SELECTION]', '[WHERE]'
 170                         ), array(
 171                         implode(', ', $selection) . ' ',
 172                         !empty($where) ? 'WHERE ' . implode(' AND ', $where) . ' ' : ' '
 173                         ), $query);
 174
 175                 $query = $this->em->createQuery($query);
 176                 !empty($params) && $query->setParameters($params);
 177                 $this->_data = $query->getResult();
 178
 179                 foreach ($this->_data as $key => $data)
 180                 {
 181                         $this->_data[$key]['percentage'] = 0 < $data['tradePrice']
 182                                 ? round(($data['bidPrice'] / $data['tradePrice']) * 100)
 183                                 : 0;
 184                         $this->_data[$key]['fullname'] = $data['firstName'] . ' ' . $data['familyName'];
 185                 }
 186                 $this->em->clear();
 187         }
 188
 189 }