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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 /**
   7  * Price Guide Statistics Report.
   8  * @author andre.fourie
   9  */
  10 class PriceGuideStatistical extends \Utility\Service\Report
  11 {
  12
  13
  14
  15         /**
  16          * @var string
  17          */
  18         protected $_title  = 'PriceGuide Statistical Report';
  19         /**
  20          * @var string
  21          */
  22         protected $_subject = 'PriceGuide';
  23         /**
  24          * @var string
  25          */
  26         protected $_description = 'Statistical report for vehicles sold 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.co.za'
  32         );
  33         /**
  34          * @var array
  35          */
  36         protected $_headers = array(
  37                         'Dealer',
  38                         'No of Valuations',
  39                         'Trade Value',
  40                         'Recon',
  41                         'Highest Offer',
  42                         '% To Trade',
  43                         'No of Offers'
  44         );
  45         /**
  46          * @var array
  47          */
  48         protected $_fields = array(
  49                         'company_name',
  50                         'num_valuations',
  51                         'total_trade',
  52                         'total_recon',
  53                         'total_highestOffer',
  54                         'percentage_toTrade',
  55                         'total_numOffers'
  56         );
  57         /**
  58          * @var array
  59          */
  60         protected $_totals = array(
  61                         /* 'num_valuations',
  62                         'total_trade',
  63                         'total_recon',
  64                         'total_highestOffer',
  65                         'total_numOffers' */
  66         );
  67         /**
  68          * @var array
  69          */
  70         protected $_currencyFields = array(
  71                         'total_trade',
  72                         'total_recon',
  73                         'total_highestOffer'
  74         );
  75
  76
  77
  78         /**
  79          * Build the dataset.
  80          */
  81         public function build()
  82         {
  83                 ini_set('memory_limit','512M');
  84                 $totalRow = array(
  85                                 'company_name'                  => '',
  86                                 'num_valuations'                => '',
  87                                 'total_trade'                   => '',
  88                                 'total_recon'                   => '',
  89                                 'total_highestOffer'    => '',
  90                                 'percentage_toTrade'    => 'Offers per unit:',
  91                                 'total_numOffers'               => ''
  92                 );
  93                 $baseRow = array(
  94                                 'company_name'                  => '',
  95                                 'num_valuations'                => 0,
  96                                 'total_trade'                   => 0,
  97                                 'total_recon'                   => 0,
  98                                 'total_highestOffer'    => 0,
  99                                 'percentage_toTrade'    => 0,
 100                                 'total_numOffers'               => 0
 101                 );
 102                 $titles =  array(
 103                                 'company_name'                  => 'Dealer',
 104                                 'num_valuations'                => 'No of Valuations',
 105                                 'total_trade'                   => 'Trade Value',
 106                                 'total_recon'                   => 'Recon',
 107                                 'total_highestOffer'    => 'Highest Offer',
 108                                 'percentage_toTrade'    => '% To Trade',
 109                                 'total_numOffers'               => 'No of Offers',
 110                                 'id'                                    => 'h',
 111                                 'excel.rowStyle'                => 'Titles'
 112                 );
 113
 114                 #-> Establish list of dealerships to work with.
 115                 $selection = array(
 116                                 'company', 'companyGroup', 'groupDivision'
 117                 );
 118                 $query = 'SELECT [SELECTION] '
 119                                 . 'FROM \Company\Entity\Company company'
 120                                 . ' LEFT JOIN company.group companyGroup '
 121                                 . ' LEFT JOIN company.groupDivision groupDivision '
 122                                 . '[WHERE] '
 123                                 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
 124                 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
 125                 {
 126                         $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 127                 }
 128                 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
 129                 {
 130                         $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 131                 }
 132                 if (isset($this->_input['group'])
 133                                 && !empty($this->_input['group'])
 134                                 && 'null' != $this->_input['group'])
 135                 {
 136                         $this->_queries['Group'] = $this->em
 137                                 ->find('\Company\Entity\Group', $this->_input['group'])
 138                                 ->name;
 139                         $where[] = 'companyGroup.id = :group';
 140                         $params['group'] = $this->_input['group'];
 141                 }
 142                 if (isset($this->_input['groupDivision'])
 143                                 && !empty($this->_input['groupDivision'])
 144                                 && 'null' != $this->_input['groupDivision'])
 145                 {
 146                         error_log($this->_input['groupDivision']);
 147                         $this->_queries['Division'] = $this->em
 148                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 149                                 ->name;
 150                         $where[] = 'groupDivision.id = :division';
 151                         $params['division'] = $this->_input['groupDivision'];
 152                 }
 153                 if (isset($this->_input['company'])
 154                                 && !empty($this->_input['company'])
 155                                 && 'null' != $this->_input['company'])
 156                 {
 157                         $this->_queries['Dealership'] = $this->em
 158                                 ->find('\Company\Entity\Company', $this->_input['company'])
 159                                 ->name;
 160                         $where[] = 'company.id = :company';
 161                         $params['company'] = $this->_input['company'];
 162                 }
 163                 #-> Finalize query.
 164                 $query = str_replace(array(
 165                                 '[SELECTION]', '[WHERE]'
 166                         ), array(
 167                                 implode(', ', $selection) . ' ',
 168                                 !empty($where)
 169                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 170                                         : ' '
 171                         ), $query);
 172                 #-> Collect data.
 173                 $query = $this->em->createQuery($query);
 174                 !empty($params)
 175                         && $query->setParameters($params);
 176                 $companies = $query->getScalarResult();
 177                 $this->em->clear();
 178                 $grp = false;
 179                 $div = false;
 180                 foreach ($companies as $company)
 181                 {
 182                         if ('' == $company['companyGroup_name'])
 183                         {
 184                                 $company['companyGroup_name'] = 'None';
 185                         }
 186                         if ('' == $company['groupDivision_name'])
 187                         {
 188                                 $company['groupDivision_name'] = 'None';
 189                         }
 190                         if ($div != $company['groupDivision_name'])
 191                         {
 192                                 if (false != $div)
 193                                 {
 194                                         $totals = $baseRow;
 195                                         $totals['company_name'] = 'DIVISION TOTALS';
 196                                         $totals['id'] = 'dt';
 197                                         $totals['excel.rowStyle'] = 'Totals';
 198                                         $this->_data[] = $totals;
 199                                         $totals = $totalRow;
 200                                         $totals['company_name'] = $grp . ': ' . $div;
 201                                         $totals['id'] = 'dn';
 202                                         $totals['excel.rowStyle'] = 'Totals';
 203                                         $this->_data[] = $totals;
 204                                         $this->_data[] = array();
 205                                         if ($grp == $company['companyGroup_name'])
 206                                         {
 207                                                 if (false != $grp)
 208                                                 {
 209                                                         $this->_data[] = $titles;
 210                                                 }
 211                                         }
 212                                 }
 213                                 $div = $company['groupDivision_name'];
 214                         }
 215                         if ($grp != $company['companyGroup_name'])
 216                         {
 217                                 if (false != $grp)
 218                                 {
 219                                         #-> Not the first group, so we have grand totals to add
 220                                         $totals = $baseRow;
 221                                         $totals['company_name'] = 'GROUP TOTALS';
 222                                         $totals['id'] = 'gt';
 223                                         $totals['excel.rowStyle'] = 'Totals';
 224                                         $this->_data[] = $totals;
 225                                         $totals = $totalRow;
 226                                         $totals['company_name'] = $grp;
 227                                         $totals['id'] = 'gn';
 228                                         $totals['excel.rowStyle'] = 'Totals';
 229                                         $this->_data[] = $totals;
 230                                         $this->_data[] = array();
 231                                         $this->_data[] = array();
 232                                         $this->_data[] = $titles;
 233                                 }
 234                                 $grp = $company['companyGroup_name'];
 235                         }
 236                         $entry = $baseRow;
 237                         $entry['id'] = $company['company_id'];
 238                         $entry['company_name'] = $company['company_name'];
 239                         $this->_data[] = $entry;
 240                 }
 241                 $totals = $baseRow;
 242                 $totals['company_name'] = 'DIVISION TOTALS';
 243                 $totals['id'] = 'dt';
 244                 $totals['excel.rowStyle'] = 'Totals';
 245                 $this->_data[] = $totals;
 246                 $totals = $totalRow;
 247                 $totals['company_name'] = $grp . ': ' . $div;
 248                 $totals['id'] = 'dn';
 249                 $totals['excel.rowStyle'] = 'Totals';
 250                 $this->_data[] = $totals;
 251                 $this->_data[] = array();
 252                 $totals = $baseRow;
 253                 $totals['company_name'] = 'GROUP TOTALS';
 254                 $totals['id'] = 'gt';
 255                 $totals['excel.rowStyle'] = 'Totals';
 256                 $this->_data[] = $totals;
 257                 $totals = $totalRow;
 258                 $totals['company_name'] = $grp;
 259                 $totals['id'] = 'gn';
 260                 $totals['excel.rowStyle'] = 'Totals';
 261                 $this->_data[] = $totals;
 262
 263
 264                 #-> Collect stats.
 265                 $selection = array(
 266                                 'companyGroup.name', 'groupDivision.name', 'company.name', 'company.id',
 267                                 'COUNT(priceGuide.id) AS num_valuations',
 268                                 'SUM(stock.tradePrice) AS total_trade',
 269                                 'SUM(stock.damageTotal) AS total_recon',
 270                                 'SUM(stock.highestOffer) AS total_highestOffer',
 271                                 'SUM(stock.numberOfOffers) AS total_numOffers'
 272                 );
 273                 $join      = array();
 274                 $leftJion  = array();
 275                 $where     = array();
 276                 $params    = array();
 277                 $query = 'SELECT [SELECTION] '
 278                                 . 'FROM \Stock\Entity\Stock stock'
 279                                 . ' JOIN stock.priceGuide priceGuide '
 280                                 . ' JOIN stock.company company '
 281                                 . ' LEFT JOIN company.group companyGroup '
 282                                 . ' LEFT JOIN company.groupDivision groupDivision '
 283                                 . '[WHERE] '
 284                                 . 'GROUP BY companyGroup.name, groupDivision.name, company.name '
 285                                 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC';
 286
 287                 #-> Construct details.
 288                 $where[] = 'stock.tradePrice > 0.0';
 289                 $where[] = 'stock.numberOfOffers > 0';
 290                 $authData = \Utility\Registry::getAuthData();if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
 291                 {
 292                         $where[] = 'priceGuide.created >= :dateFrom';
 293                         $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 294                 }
 295                 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
 296                 {
 297                         $where[] = 'priceGuide.created <= :dateTo';
 298                         $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 299                 }
 300                 if (isset($this->_input['group'])
 301                                 && !empty($this->_input['group'])
 302                                 && 'null' != $this->_input['group'])
 303                 {
 304                         $where[] = 'companyGroup.id = :group';
 305                         $params['group'] = $this->_input['group'];
 306                 }
 307                 if (isset($this->_input['groupDivision'])
 308                                 && !empty($this->_input['groupDivision'])
 309                                 && 'null' != $this->_input['groupDivision'])
 310                 {
 311                         $where[] = 'groupDivision.id = :division';
 312                         $params['division'] = $this->_input['groupDivision'];
 313                 }
 314                 if (isset($this->_input['company'])
 315                                 && !empty($this->_input['company'])
 316                                 && 'null' != $this->_input['company'])
 317                 {
 318                         $where[] = 'company.id = :company';
 319                         $params['company'] = $this->_input['company'];
 320                 }
 321
 322                 #-> Finalize query.
 323                 $query = str_replace(array(
 324                                 '[SELECTION]', '[WHERE]'
 325                         ), array(
 326                                 implode(', ', $selection) . ' ',
 327                                 !empty($where)
 328                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 329                                         : ' '
 330                         ), $query);
 331
 332                 #-> Collect data.
 333                 $query = $this->em->createQuery($query);
 334                 !empty($params)
 335                         && $query->setParameters($params);
 336                 $stats = $query->getScalarResult();
 337                 $this->em->clear();
 338                 $grp = '';
 339                 $div = '';
 340                 $divTotals = $baseRow;
 341                 $grpTotal = $baseRow;
 342                 $stat = array_shift($stats);
 343                 foreach ($this->_data as $i => $entry)
 344                 {
 345                         if (empty($entry))
 346                         {
 347                                 continue;
 348                         }
 349                         switch ($entry['id'])
 350                         {
 351                                 case 'h':
 352                                         break;
 353                                 case 'dt':
 354                                         $this->_data[$i]['num_valuations'] = $divTotals['num_valuations'];
 355                                         $this->_data[$i]['total_trade'] = $divTotals['total_trade'];
 356                                         $this->_data[$i]['total_recon'] = $divTotals['total_recon'];
 357                                         $this->_data[$i]['total_highestOffer'] = $divTotals['total_highestOffer'];
 358                                         $this->_data[$i]['percentage_toTrade'] = $divTotals['total_trade'] > 0
 359                                                 ? round((100 / $divTotals['total_trade']) * $divTotals['total_highestOffer'], 1)
 360                                                 : 0;
 361                                         $this->_data[$i]['total_numOffers'] = $divTotals['total_numOffers'];
 362                                         break;
 363                                 case 'dn':
 364                                         $this->_data[$i]['total_numOffers'] = $divTotals['num_valuations'] > 0
 365                                                 ? round($divTotals['total_numOffers'] / $divTotals['num_valuations'], 2)
 366                                                 : 0;
 367                                         $divTotals = $baseRow;
 368                                         break;
 369                                 case 'gt':
 370                                         $this->_data[$i]['num_valuations'] = $grpTotal['num_valuations'];
 371                                         $this->_data[$i]['total_trade'] = $grpTotal['total_trade'];
 372                                         $this->_data[$i]['total_recon'] = $grpTotal['total_recon'];
 373                                         $this->_data[$i]['total_highestOffer'] = $grpTotal['total_highestOffer'];
 374                                         $this->_data[$i]['percentage_toTrade'] = $grpTotal['total_trade'] > 0
 375                                                 ? round((100 / $grpTotal['total_trade']) * $grpTotal['total_highestOffer'], 1)
 376                                                 : 0;
 377                                         $this->_data[$i]['total_numOffers'] = $grpTotal['total_numOffers'];
 378                                         break;
 379                                 case 'gn':
 380                                         $this->_data[$i]['total_numOffers'] = $grpTotal['num_valuations'] > 0
 381                                                 ? round($grpTotal['total_numOffers'] / $grpTotal['num_valuations'], 2)
 382                                                 : 0;
 383                                         $grpTotal = $baseRow;
 384                                         break;
 385                                 default:
 386                                         if ($stat['id'] == $entry['id'])
 387                                         {
 388                                                 #-> Update the entry.
 389                                                 $this->_data[$i]['num_valuations'] = $stat['num_valuations'];
 390                                                 $this->_data[$i]['total_trade'] = $stat['total_trade'];
 391                                                 $this->_data[$i]['total_recon'] = $stat['total_recon'];
 392                                                 $this->_data[$i]['total_highestOffer'] = $stat['total_highestOffer'];
 393                                                 $this->_data[$i]['percentage_toTrade'] = $stat['total_trade'] > 0
 394                                                         ? round((100 / $stat['total_trade']) * $stat['total_highestOffer'], 1)
 395                                                         : 0;
 396                                                 $this->_data[$i]['total_numOffers'] = $stat['total_numOffers'];
 397
 398                                                 #-> Update the totals.
 399                                                 $divTotals['num_valuations'] += $stat['num_valuations'];
 400                                                 $divTotals['total_trade'] += $stat['total_trade'];
 401                                                 $divTotals['total_recon'] += $stat['total_recon'];
 402                                                 $divTotals['total_highestOffer'] += $stat['total_highestOffer'];
 403                                                 $divTotals['total_numOffers'] += $stat['total_numOffers'];
 404                                                 $grpTotal['num_valuations'] += $stat['num_valuations'];
 405                                                 $grpTotal['total_trade'] += $stat['total_trade'];
 406                                                 $grpTotal['total_recon'] += $stat['total_recon'];
 407                                                 $grpTotal['total_highestOffer'] += $stat['total_highestOffer'];
 408                                                 $grpTotal['total_numOffers'] += $stat['total_numOffers'];
 409
 410                                                 #-> Company handled, move on to next one.
 411                                                 $stat = array_shift($stats);
 412                                         }
 413                                         break;
 414                         }
 415
 416                 }
 417         }
 418
 419 }