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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 /**
   7  * Trade offers Report.
   8  * @author andre.fourie
   9  */
  10 class Trader extends \Utility\Service\Report
  11 {
  12
  13
  14
  15         /**
  16          * @var string
  17          */
  18         protected $_title  = 'Trader Report';
  19         /**
  20          * @var string
  21          */
  22         protected $_subject = 'Trade Offers';
  23         /**
  24          * @var string
  25          */
  26         protected $_description = 'Historical report for trade offers on Price Guide.';
  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          * @var array
  35          */
  36         protected $_headers = array(
  37                         'Dealership',
  38                         'Total Offers',
  39                         'Total Highest Offers',
  40                         'Total Trade Value',
  41                         'Total Offer Value',
  42                         'Percentage',
  43                         'Total Trade Value (Highest Offers)',
  44                         'Total Offer Value (Highest Offers)',
  45                         'Percentage (Highest Offers)',
  46         );
  47         /**
  48          * @var array
  49          */
  50         protected $_fields = array(
  51                         'company_name',
  52                         'num_offers',
  53                         'num_highest',
  54                         'sum_trade',
  55                         'sum_offers',
  56                         'perc',
  57                         'sum_trade_highest',
  58                         'sum_offers_highest',
  59                         'perc_highest'
  60         );
  61         /**
  62          * @var array
  63          */
  64         protected $_totals = array(
  65                         'num_offers',
  66                         'num_highest',
  67                         'sum_trade',
  68                         'sum_offers',
  69                         'sum_trade_highest',
  70                         'sum_offers_highest'
  71         );
  72         /**
  73          * @var array
  74          */
  75         protected $_currencyFields = array(
  76                         'sum_trade',
  77                         'sum_offers',
  78                         'sum_trade_highest',
  79                         'sum_offers_highest'
  80         );
  81
  82
  83
  84         /**
  85          * Build the dataset.
  86          */
  87         public function build()
  88         {
  89                 #-> Prepare parameters.
  90                 $selection = array(
  91                                 'company.name',
  92                                 'COUNT(offer.id) AS num_offers',
  93                                 'SUM(IF(offer.amount = stock.highest_offer, 1, 0)) AS num_highest',
  94                                 'SUM(stock.trade_price) as sum_trade',
  95                                 'SUM(offer.amount) AS sum_offers',
  96                                 'ROUND((100/SUM(stock.trade_price)) * SUM(offer.amount), 2) as perc',
  97                                 'SUM(IF(offer.amount = stock.highest_offer, stock.trade_price, 0)) as sum_trade_highest',
  98                                 'SUM(IF(offer.amount = stock.highest_offer, offer.amount, 0)) AS sum_offers_highest',
  99                                 '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'
 100                 );
 101                 $join      = array();
 102                 $leftJion  = array();
 103                 $where     = array();
 104                 $params    = array();
 105                 $query = 'SELECT [SELECTION] '
 106                                 . 'FROM stock '
 107                                 . ' JOIN price_guide priceGuide ON priceGuide.id = stock.price_guide_id '
 108                                 . ' JOIN price_guide_offer offer ON offer.price_guide_id=priceGuide.id '
 109                                 . ' JOIN company company ON company.id=offer.company_id '
 110                                 . '[WHERE] '
 111                                 . 'GROUP BY company.name '
 112                                 . 'ORDER BY company.name ASC';
 113                 $where[] = array(
 114                                 'offer.created BETWEEN :start AND :end',
 115                                 'offer.updated BETWEEN :start AND :end'
 116                 );
 117                 $where[] = array(
 118                                 'stock.vehicle_natis_id IS NULL',
 119                                 'stock.vehicle_natis_id IN (1, 2)'
 120                 );
 121                 $where[] = 'stock.trade_price IS NOT NULL';
 122                 $where[] = 'stock.trade_price > 0';
 123
 124                 #-> Construct details.
 125                 $authData = \Utility\Registry::getAuthData();
 126                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
 127                 {
 128                         if ($this->_input['dateTo'] && !empty($this->_input['dateTo']))
 129                         {
 130                                 $parts = explode('-', $this->_input['dateTo']);
 131                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, $parts[1], 1, $parts[0]));
 132                         }
 133                         else
 134                         {
 135                                 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
 136                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, date('m') - 1, 1, date('Y')));
 137                         }
 138                 }
 139                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 140                 $params['start'] = new \DateTime($this->_input['dateFrom']);
 141                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
 142                 {
 143                         $parts = explode('-', $this->_input['dateFrom']);
 144                         $this->_input['dateTo'] = date('Y-m-d', mktime(1, 0, 0, $parts[1] + 1, 0, $parts[0]));
 145                 }
 146                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 147                 $params['end'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 148                 if (isset($this->_input['group'])
 149                                 && !empty($this->_input['group'])
 150                                 && 'null' != $this->_input['group'])
 151                 {
 152                         $this->_queries['Group'] = $this->em
 153                                 ->find('\Company\Entity\Group', $this->_input['group'])
 154                                 ->name;
 155                         $where[] = 'company.company_group_id = :group';
 156                         $params['group'] = $this->_input['group'];
 157                 }
 158                 if (isset($this->_input['groupDivision'])
 159                                 && !empty($this->_input['groupDivision'])
 160                                 && 'null' != $this->_input['groupDivision'])
 161                 {
 162                         error_log($this->_input['groupDivision']);
 163                         $this->_queries['Division'] = $this->em
 164                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 165                                 ->name;
 166                         $where[] = 'company.company_group_division_id = :division';
 167                         $params['division'] = $this->_input['groupDivision'];
 168                 }
 169                 if (isset($this->_input['company'])
 170                                 && !empty($this->_input['company'])
 171                                 && 'null' != $this->_input['company'])
 172                 {
 173                         $this->_queries['Dealership'] = $this->em
 174                                 ->find('\Company\Entity\Company', $this->_input['company'])
 175                                 ->name;
 176                         $where[] = 'company.id = :company';
 177                         $params['company'] = $this->_input['company'];
 178                 }
 179
 180                 #-> Finalize query.
 181                 foreach ($where as $key => $value)
 182                 {
 183                         if (is_array($value))
 184                         {
 185                                 $where[$key] = '(' . implode(' OR ', $value) . ')';
 186                         }
 187                 }
 188                 $query = str_replace(array(
 189                                 '[SELECTION]', '[WHERE]'
 190                         ), array(
 191                                 implode(', ', $selection) . ' ',
 192                                 !empty($where)
 193                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 194                                         : ' '
 195                         ), $query);
 196
 197                 #-> Collect data.
 198                 $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->em);
 199                 $rsm->addRootEntityFromClassMetadata('\Stock\Entity\Stock', 'stock');
 200                 $rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\PriceGuide', 'priceGuide', 'stock', 'priceGuide',
 201                                 array(
 202                                                 'id'                                    => 'pg_id',
 203                                                 'company_id'                    => 'pg_company_id',
 204                                                 'created_by_profile_id' => 'pg_created_by_profile_id',
 205                                                 'stock_valuation_id'    => 'pg_stock_valuation_id',
 206                                                 'previous_status'               => 'pg_previous_status',
 207                                                 'status'                                => 'pg_status',
 208                                                 'created'                               => 'pg_created',
 209                                                 'updated'                               => 'pg_updated',
 210                                                 'archived'                              => 'pg_archived'
 211                                 )
 212                 );
 213                 $rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\Offer', 'offer', 'priceGuide', 'offers',
 214                                 array(
 215                                                 'id'                                    => 'pgo_id',
 216                                                 'company_id'                    => 'pgo_company_id',
 217                                                 'profile_id'                    => 'pgo_profile_id',
 218                                                 'price_guide_id'                => 'pgo_price_guide_id',
 219                                                 'previous_status'               => 'pgo_previous_status',
 220                                                 'status'                                => 'pgo_status',
 221                                                 'created'                               => 'pgo_created',
 222                                                 'updated'                               => 'pgo_updated',
 223                                                 'archived'                              => 'pgo_archived'
 224                                 )
 225                 );
 226                 $rsm->addJoinedEntityFromClassMetadata('\Company\Entity\Company', 'company', 'offer', 'company',
 227                                 array(
 228                                                 'id'                                    => 'cmp_id',
 229                                                 'lib_region_id'                 => 'cmp_lib_region_id',
 230                                                 'status'                                => 'cmp_status',
 231                                                 'created'                               => 'cmp_created',
 232                                                 'updated'                               => 'cmp_updated',
 233                                                 'archived'                              => 'cmp_archived'
 234                                 )
 235                 );
 236                 $rsm->addScalarResult('num_offers', 'num_offers');
 237                 $rsm->addScalarResult('num_highest', 'num_highest');
 238                 $rsm->addScalarResult('sum_trade', 'sum_trade');
 239                 $rsm->addScalarResult('sum_offers', 'sum_offers');
 240                 $rsm->addScalarResult('perc', 'perc');
 241                 $rsm->addScalarResult('sum_trade_highest', 'sum_trade_highest');
 242                 $rsm->addScalarResult('sum_offers_highest', 'sum_offers_highest');
 243                 $rsm->addScalarResult('perc_highest', 'perc_highest');
 244                 $query = $this->em->createNativeQuery($query, $rsm);
 245                 !empty($params)
 246                         && $query->setParameters($params);
 247
 248                 $this->_data = $query->getScalarResult();
 249                 $this->em->clear();
 250         }
 251
 252 }