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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5 /**
   6  * Stock Control Report.
   7  * @author andre.fourie
   8  */
   9 class LoadVsSoldDetail extends \Utility\Service\Report
  10 {
  11
  12
  13
  14         /**
  15          * @var string
  16          */
  17         protected $_title = 'Loaded and Sold';
  18         /**
  19          * @var string
  20          */
  21         protected $_subject = 'Vehicles loaded vs vehicles sold';
  22         /**
  23          * @var string
  24          */
  25         protected $_description = 'Vehicles loaded vs vehicles sold.';
  26         /**
  27          * @var array
  28          */
  29         protected $_notes = array(
  30                 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
  31         );
  32         /**
  33          * @var array
  34          */
  35         protected $_headers = array(
  36                 'Group',
  37                 'Division',
  38                 'Trade Centre',
  39                 'Dealership',
  40                 'Region',
  41                 'Date',
  42                 'Reference',
  43                 'Auction No',
  44                 'Stock No',
  45                 'Registration No',
  46                 'VIN Number',
  47                 'Make',
  48                 'Type',
  49                 'Trade Price',
  50                 'Initial Reserve Price',
  51                 'Reserve',
  52                 'Sold',
  53                 'Bids',
  54                 'B4C Profit',
  55                 'Kilometres'
  56         );
  57         /**
  58          * @var array
  59          */
  60         protected $_fields = array(
  61                 'group_name',
  62                 'division_name',
  63                 'trade_centre',
  64                 'company_name',
  65                 'region_name',
  66                 'auction_endDate',
  67                 'reference',
  68                 'auction_id',
  69                 'stock_stockNumber',
  70                 'stock_registrationNumber',
  71                 'vin_number',
  72                 'make_name',
  73                 'type_name',
  74                 'trade_price',
  75                 'auction_initial_reservePrice',
  76                 'auction_reservePrice',
  77                 'auction_currentBidPrice',
  78                 'auction_numberOfBids',
  79                 'auction_profit',
  80                 'stock_km'
  81         );
  82         /**
  83          * @var array
  84          */
  85         protected $_totals = array(
  86                 'trade_price',
  87                 'auction_reservePrice',
  88                 'auction_currentBidPrice',
  89                 'auction_numberOfBids',
  90                 'auction_profit'
  91         );
  92         /**
  93          * @var array
  94          */
  95         protected $_currencyFields = array(
  96                 'auction_reservePrice',
  97                 'auction_currentBidPrice',
  98                 'auction_profit'
  99         );
 100
 101
 102         /**
 103          * Build the dataset.
 104          */
 105         public function build()
 106         {
 107                 #-> Prepare parameters.
 108                 #-> Collect loaded and sold totals.
 109                 $selection = array(
 110                         'grp.name AS group_name',
 111                         'division.name AS division_name',
 112                         'company.name AS company_name',
 113                         '(CASE WHEN stock.previousState = :tradeState THEN tradeCentre.name THEN :blank END) as trade_centre',
 114                         'region.name AS region_name',
 115                         'auction.endDate AS auction_endDate',
 116                         'stock.referenceNumber as reference',
 117                         'auction.id AS auction_id',
 118                         'stock.stockNumber AS stock_stockNumber',
 119                         'stock.registrationNumber AS stock_registrationNumber',
 120                         'stock.vinNumber AS vin_number',
 121                         'make.name AS make_name',
 122                         'model.name AS model_name',
 123                         'type.name AS type_name',
 124                         'stock.tradePrice as trade_price',
 125                         'auction.reservePrice AS auction_initial_reservePrice',
 126                         'auction.reservePrice AS auction_reservePrice',
 127                         'auction.currentBidPrice AS auction_currentBidPrice',
 128                         'COUNT(DISTINCT bid.id) AS auction_numberOfBids',
 129                         '(auction.currentBidPrice - auction.reservePrice) AS auction_profit', //sold - reserve price
 130                         'stock.km AS stock_km',
 131                         'auction.jobState AS status'
 132                 );
 133                 $join = array();
 134                 $leftJion = array();
 135                 $where = array();
 136                 $params = array();
 137                 $query = 'SELECT [SELECTION] '
 138                         . 'FROM \Stock\Entity\Stock stock '
 139                         . 'JOIN stock.auction auction '
 140                         . 'JOIN stock.company company '
 141                         . 'LEFT JOIN company.tradeCenter tradeCentre '
 142                         . 'LEFT JOIN auction.bids bid '
 143                         . 'LEFT JOIN company.city city '
 144                         . 'LEFT JOIN city.region region '
 145                         . 'LEFT JOIN stock.type type '
 146                         . 'LEFT JOIN type.model model '
 147                         . 'LEFT JOIN model.make make '
 148                         . 'LEFT JOIN company.group grp '
 149                         . 'LEFT JOIN company.groupDivision division '
 150                         . '[WHERE] '
 151                         . 'GROUP BY auction.id '
 152                         . 'ORDER BY auction.endDate ASC';
 153
 154                 $where[] = 'auction.jobState != :status';
 155                 $params['status'] = 'Active';
 156                 $params['tradeState'] = 'Trade Center';
 157                 $params['blank'] = '';
 158
 159                 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
 160                 {
 161                         $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 162                         $where[] = 'auction.endDate >= :dateFrom';
 163                         $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 164                 }
 165                 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
 166                 {
 167                         $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 168                         $where[] = 'auction.endDate <= :dateTo';
 169                         $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 170                 }
 171                 if (isset($this->_input['group'])
 172                         && !empty($this->_input['group'])
 173                         && 'null' != $this->_input['group']
 174                 )
 175                 {
 176                         $this->_queries['Group'] = $this->em
 177                                 ->find('\Company\Entity\Group', $this->_input['group'])
 178                                 ->name;
 179                         $where[] = 'grp.id = :group';
 180                         $params['group'] = $this->_input['group'];
 181                 }
 182                 if (isset($this->_input['groupDivision'])
 183                         && !empty($this->_input['groupDivision'])
 184                         && 'null' != $this->_input['groupDivision']
 185                 )
 186                 {
 187                         error_log($this->_input['groupDivision']);
 188                         $this->_queries['Division'] = $this->em
 189                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 190                                 ->name;
 191                         $where[] = 'division.id = :division';
 192                         $params['division'] = $this->_input['groupDivision'];
 193                 }
 194                 if (isset($this->_input['company'])
 195                         && !empty($this->_input['company'])
 196                         && 'null' != $this->_input['company']
 197                 )
 198                 {
 199                         error_log($this->_input['company']);
 200                         $this->_queries['Dealership'] = $this->em
 201                                 ->find('\Company\Entity\Company', $this->_input['company'])
 202                                 ->name;
 203                         $where[] = 'company.id = :company';
 204                         $params['company'] = $this->_input['company'];
 205                 }
 206
 207                 #-> Finalize query.
 208                 $query = str_replace(array(
 209                         '[SELECTION]', '[WHERE]'
 210                 ), array(
 211                         implode(', ', $selection) . ' ',
 212                         !empty($where)
 213                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 214                                 : ' '
 215                 ), $query);
 216
 217                 #-> Collect data.
 218                 $query = $this->em->createQuery($query);
 219                 !empty($params)
 220                 && $query->setParameters($params);
 221                 $this->_data = $query->getScalarResult();
 222                 $this->em->clear();
 223                 foreach ($this->_data as $id => $row)
 224                 {
 225                         if ('Sold' != $row['status'])
 226                         {
 227                                 $this->_data[$id]['auction_currentBidPrice'] = 0.00;
 228                                 $this->_data[$id]['auction_bidIncrement'] = 0.00;
 229                         }
 230                         $query = 'SELECT auction.reservePrice '
 231                                 . 'FROM \Auction\Entity\Auction auction '
 232                                 . 'JOIN auction.stock stock '
 233                                 . 'WHERE stock.referenceNumber = :reference '
 234                                 . 'ORDER BY auction.endDate ASC';
 235
 236                         $params = array();
 237                         $params['reference'] = $row['reference'];
 238
 239                         $query = $this->em->createQuery($query);
 240                         $query->setParameters($params);
 241                         $temp = $query->getScalarResult();
 242
 243                         $this->_data[$id]['auction_initial_reservePrice'] = $temp[0]['reservePrice'];
 244                 }
 245         }
 246
 247 }