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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 use Valuation;
   7 /**
   8  * Stock Control Report.
   9  * @author andre.fourie
  10  */
  11 class PriceGuideFlash extends \Utility\Service\Report
  12 {
  13
  14
  15
  16         /**
  17          * @var string
  18          */
  19         protected $_title  = 'PriceGuide Flash Report';
  20         /**
  21          * @var string
  22          */
  23         protected $_subject = 'PriceGuide Flash';
  24         /**
  25          * @var string
  26          */
  27         protected $_description = 'Amount of valuations send to price guide.';
  28         /**
  29          * @var array
  30          */
  31         protected $_notes = array(
  32                         'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
  33         );
  34         /**
  35          * @var array
  36          */
  37         protected $_headers = array(
  38                         'Total Cars Loaded',
  39                         'Total Cars Loaded on Price Guide',
  40                         'With Offers',
  41                         '%',
  42                         'Total number of offers made',
  43                         'Avg Number Offers',
  44                         'Number with 3 offers +',
  45                         '%',
  46                         'Number with 2 offers',
  47                         '%',
  48                         'Number with 1 offer',
  49                         '%',
  50                         'Number with 0 offers',
  51                         '%'
  52         );
  53         /**
  54          * @var array
  55          */
  56         protected $_fields = array(
  57                         'totalCarsLoaded',
  58                         'totalLoadedOnPg',
  59                         'TotalCarsOffers',
  60                         'TotalCarsOffersPer',
  61                         'tNumOffers',
  62                         'AvgNumOffers',
  63                         'NumOffers3',
  64                         'NumOffers3Per',
  65                         'NumOffers2',
  66                         'NumOffers2Per',
  67                         'NumOffers1',
  68                         'NumOffers1Per',
  69                         'NumOffers0',
  70                         'NumOffers0Per'
  71         );
  72
  73
  74
  75
  76         /**
  77          * Build the dataset.
  78          */
  79         public function build()
  80         {
  81                 ini_set('memory_limit','512M');
  82
  83                 #-> Filtering.
  84                 $join      = array();
  85                 $leftJion  = array();
  86                 $where     = array();
  87                 $stockWhere = array();
  88                 $params    = array();
  89
  90                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
  91                 {
  92                         $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
  93                 }
  94                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
  95                 $where[] = 'priceGuide.created >= :dateFrom';
  96                 $stockWhere[] = 'stock.created >= :dateFrom';
  97                 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
  98
  99                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
 100                 {
 101                         $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
 102                 }
 103                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 104                 $where[] = 'priceGuide.created < :dateTo';
 105                 $stockWhere[] = 'stock.created < :dateTo';
 106                 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
 107
 108                 if (isset($this->_input['group'])
 109                         && !empty($this->_input['group'])
 110                         && 'null' != $this->_input['group'])
 111                 {
 112                         $this->_queries['Group'] = $this->em
 113                                 ->find('\Company\Entity\Group', $this->_input['group'])
 114                                 ->name;
 115                         $where[] = 'IDENTITY(company.group) = :group';
 116                         $stockWhere[] = 'IDENTITY(company.group) = :group';
 117                         $params['group'] = $this->_input['group'];
 118                 }
 119                 if (isset($this->_input['groupDivision'])
 120                         && !empty($this->_input['groupDivision'])
 121                         && 'null' != $this->_input['groupDivision'])
 122                 {
 123                         error_log($this->_input['groupDivision']);
 124                         $this->_queries['Division'] = $this->em
 125                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 126                                 ->name;
 127                         $where[] = 'IDENTITY(company.groupDivision) = :division';
 128                         $stockWhere[] = 'IDENTITY(company.groupDivision) = :division';
 129                         $params['division'] = $this->_input['groupDivision'];
 130                 }
 131                 if (isset($this->_input['company'])
 132                         && !empty($this->_input['company'])
 133                         && 'null' != $this->_input['company'])
 134                 {
 135                         $this->_queries['Dealership'] = $this->em
 136                                 ->find('\Company\Entity\Company', $this->_input['company'])
 137                                 ->name;
 138                         $where[] = 'company.id = :company';
 139                         $stockWhere[] = 'company.id = :company';
 140                         $params['company'] = $this->_input['company'];
 141                 }
 142
 143                 #-> Total Cars Loaded
 144                 $query = 'SELECT COUNT(stock.id) as CarsLoaded '
 145                                         . 'FROM \Stock\Entity\Stock stock '
 146                                         . 'JOIN stock.company company '
 147                                         . '[WHERE]';
 148                 $query = str_replace(array(
 149                                 '[WHERE]'
 150                 ), array(
 151                                 !empty($stockWhere)
 152                                 ? 'WHERE ' . implode(' AND ', $stockWhere) . ' '
 153                                 : ' '
 154                 ), $query);
 155                 $query = $this->em->createQuery($query);
 156                 !empty($params)
 157                         && $query->setParameters($params);
 158                 $data0 = $query->getScalarResult();
 159                 $this->em->clear();
 160
 161                 #-> Total Cars Loaded on price guide
 162                 $query = 'SELECT COUNT(DISTINCT priceGuide.id) AS CarsLoaded '
 163                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 164                                 . 'JOIN priceGuide.company company '
 165                                 . '[WHERE]';
 166                 $query = str_replace(array(
 167                                 '[WHERE]'
 168                 ), array(
 169                                 !empty($where)
 170                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 171                                 : ' '
 172                 ), $query);
 173                 $query = $this->em->createQuery($query);
 174                 !empty($params)
 175                         && $query->setParameters($params);
 176                 $data1 = $query->getScalarResult();
 177                 $this->em->clear();
 178
 179                 //\Utility\Debug::errorLog('data', $params);
 180                 //var_dump($params);
 181                 //exit();
 182
 183                 #-> Total cars that has offers
 184                 $query = 'SELECT COUNT(DISTINCT priceGuide.id) AS NumCarsWithOffers '
 185                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 186                                 . 'JOIN priceGuide.company company '
 187                                 . 'JOIN priceGuide.offers offer '
 188                                 . '[WHERE]';
 189                 $query = str_replace(array(
 190                                 '[WHERE]'
 191                 ), array(
 192                                 !empty($where)
 193                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 194                                 : ' '
 195                 ), $query);
 196                 $query = $this->em->createQuery($query);
 197                 !empty($params)
 198                         && $query->setParameters($params);
 199                 $data2 = $query->getScalarResult();
 200                 $this->em->clear();
 201
 202                 #-> Total cars that has offers vs cars loaded in %
 203                 $data3 = 0 == $data1[0]['CarsLoaded']
 204                         ? 0
 205                         : round(($data2[0]['NumCarsWithOffers']*100)/$data1[0]['CarsLoaded']);
 206
 207                 #-> Total number of offers made
 208
 209                 $query = 'SELECT COUNT(DISTINCT offers.id) AS NumOffersMade '
 210                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 211                                 . 'JOIN priceGuide.company company '
 212                                 . 'JOIN priceGuide.offers offers '
 213                                 . '[WHERE]';
 214                 $query = str_replace(array(
 215                                 '[WHERE]'
 216                 ), array(
 217                                 !empty($where)
 218                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 219                                 : ' '
 220                 ), $query);
 221                 $query = $this->em->createQuery($query);
 222                 !empty($params)
 223                         && $query->setParameters($params);
 224                 $data4 = $query->getScalarResult();
 225                 $this->em->clear();
 226
 227                 #-> Ave number of offers per Cars Loaded
 228                 $data5 = 0 == $data1[0]['CarsLoaded']
 229                         ? 0
 230                         : round($data4[0]['NumOffersMade']/$data1[0]['CarsLoaded']);
 231
 232                 #-> Number with 3+ offers
 233                 $query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
 234                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 235                                 . 'JOIN priceGuide.company company '
 236                                 . 'LEFT JOIN priceGuide.offers offer '
 237                                 . '[WHERE]'
 238                                 . 'GROUP BY priceGuide.id '
 239                                 . 'HAVING numOffers > 2';
 240                 $query = str_replace(array(
 241                                 '[WHERE]'
 242                 ), array(
 243                                 !empty($where)
 244                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 245                                 : ' '
 246                 ), $query);
 247                 $query = $this->em->createQuery($query);
 248                 !empty($params)
 249                         && $query->setParameters($params);
 250                 $data6 = count($query->getScalarResult());
 251                 $this->em->clear();
 252
 253                 #-> Number with 3 offers %
 254                 $data7 = 0 == $data1[0]['CarsLoaded']
 255                         ? 0
 256                         : round((100 / $data1[0]['CarsLoaded']) * $data6, 2);
 257
 258                 #-> Number with 2 offers
 259                 $query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
 260                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 261                                 . 'JOIN priceGuide.company company '
 262                                 . 'LEFT JOIN priceGuide.offers offer '
 263                                 . '[WHERE]'
 264                                 . 'GROUP BY priceGuide.id '
 265                                 . 'HAVING numOffers = 2';
 266                 $query = str_replace(array(
 267                                 '[WHERE]'
 268                 ), array(
 269                                 !empty($where)
 270                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 271                                 : ' '
 272                 ), $query);
 273                 $query = $this->em->createQuery($query);
 274                 !empty($params)
 275                         && $query->setParameters($params);
 276                 $data8 = count($query->getScalarResult());
 277                 $this->em->clear();
 278
 279                 #-> Number with 2 offers %
 280                 $data9 = 0 == $data1[0]['CarsLoaded']
 281                         ? 0
 282                         : round((100 / $data1[0]['CarsLoaded']) * $data8, 2);
 283
 284                 #-> Number with 1 offers
 285                 $query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
 286                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 287                                 . 'JOIN priceGuide.company company '
 288                                 . 'LEFT JOIN priceGuide.offers offer '
 289                                 . '[WHERE]'
 290                                 . 'GROUP BY priceGuide.id '
 291                                 . 'HAVING numOffers = 1';
 292                 $query = str_replace(array(
 293                                 '[WHERE]'
 294                 ), array(
 295                                 !empty($where)
 296                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 297                                 : ' '
 298                 ), $query);
 299                 $query = $this->em->createQuery($query);
 300                 !empty($params)
 301                         && $query->setParameters($params);
 302                 $data10 = count($query->getScalarResult());
 303                 $this->em->clear();
 304
 305                 #-> Number with 1 offers %
 306                 $data11 = 0 == $data1[0]['CarsLoaded']
 307                         ? 0
 308                         : round((100 / $data1[0]['CarsLoaded']) * $data10, 2);
 309
 310                 #-> Number with 0 offers
 311                 $query = 'SELECT priceGuide.id, COUNT(offer.id) AS numOffers '
 312                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 313                                 . 'JOIN priceGuide.company company '
 314                                 . 'LEFT JOIN priceGuide.offers offer '
 315                                 . '[WHERE]'
 316                                 . 'GROUP BY priceGuide.id '
 317                                 . 'HAVING numOffers = 0';
 318                 $query = str_replace(array(
 319                                 '[WHERE]'
 320                 ), array(
 321                                 !empty($where)
 322                                 ? 'WHERE ' . implode(' AND ', $where) . ' '
 323                                 : ' '
 324                 ), $query);
 325                 $query = $this->em->createQuery($query);
 326                 !empty($params)
 327                         && $query->setParameters($params);
 328                 $data12 = count($query->getScalarResult());
 329                 $this->em->clear();
 330
 331                 #-> Number with 1 offers %
 332                 $data13 = 0 == $data1[0]['CarsLoaded']
 333                         ? 0
 334                         : round((100 / $data1[0]['CarsLoaded']) * $data12, 2);
 335
 336                 #-> Change Data
 337                 $data0val = $data0[0]['CarsLoaded'];
 338                 $data1val = $data1[0]['CarsLoaded'];
 339                 $data2val = $data2[0]['NumCarsWithOffers'];
 340                 $data4val = $data4[0]['NumOffersMade'];
 341
 342                 #-> Collate information.
 343                 $this->_data = array(array(
 344                                                         'totalCarsLoaded' => $data0val,
 345                                                         'totalLoadedOnPg' => $data1val,
 346                                                         'TotalCarsOffers' => $data2val,
 347                                                         'TotalCarsOffersPer' => $data3,
 348                                                         'tNumOffers' => $data4val,
 349                                                         'AvgNumOffers' => $data5,
 350                                                         'NumOffers3' => $data6,
 351                                                         'NumOffers3Per' => $data7,
 352                                                         'NumOffers2' => $data8,
 353                                                         'NumOffers2Per' => $data9,
 354                                                         'NumOffers1' => $data10,
 355                                                         'NumOffers1Per' => $data11,
 356                                                         'NumOffers0' => $data12,
 357                                                         'NumOffers0Per' => $data13
 358                         ));
 359                 foreach ($this->_data as $id => $row)
 360                 {
 361                         //
 362                 }
 363         }
 364
 365 }