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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 /**
   7  * Stock Control Report.
   8  * @author andre.fourie
   9  */
  10 class PriceGuide extends \Utility\Service\Report
  11 {
  12
  13
  14
  15         /**
  16          * @var string
  17          */
  18         protected $_title  = 'PriceGuide Report';
  19         /**
  20          * @var string
  21          */
  22         protected $_subject = 'PriceGuide';
  23         /**
  24          * @var string
  25          */
  26         protected $_description = 'Historical 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.com.na'
  32         );
  33         /**
  34          * @var array
  35          */
  36         protected $_headers = array(
  37                         'Seller Group',
  38                         'Seller Division',
  39                         'Seller Dealership',
  40                         'Seller User Name',
  41                         'Seller User Surname',
  42                         'Buyer Group',
  43                         'Buyer Division',
  44                         'Buyer Dealership',
  45                         'Buyer User Name',
  46                         'Buyer User Surname',
  47                         'Vehicle Make',
  48                         'Model',
  49                         'Type',
  50                         'Year',
  51                         'Stock No.',
  52                         'VIN No.',
  53                         'Engine No.',
  54                         'Registration No.',
  55                         'Trade Price',
  56                         'Retail Price',
  57                         'Date Loaded',
  58                         'Date Offered',
  59                         'Highest Offer',
  60                         'Commission',
  61                         'Automate Matched',
  62                         'Automate Duplicates',
  63                         'Matches to multiple stock entries',
  64                         'Date invoiced on Automate',
  65                         'Source',
  66                         'Source file',
  67                         'Branch code',
  68                         'Invoice price',
  69                         'Sold to'
  70         );
  71         /**
  72          * @var array
  73          */
  74         protected $_fields = array(
  75                         'sellerGroup_name',
  76                         'sellerDivision_name',
  77                         'sellerCompany_name',
  78                         'sellerProfile_firstName',
  79                         'sellerProfile_familyName',
  80                         'buyerGroup_name',
  81                         'buyerDivision_name',
  82                         'buyerCompany_name',
  83                         'buyerProfile_firstName',
  84                         'buyerProfile_familyName',
  85                         'make_name',
  86                         'model_name',
  87                         'type_name',
  88                         'year_name',
  89                         'stock_stockNumber',
  90                         'stock_vinNumber',
  91                         'stock_engineNumber',
  92                         'stock_registrationNumber',
  93                         'stock_tradePrice',
  94                         'stock_retailPrice',
  95             'stock_listPrice',
  96                         'priceGuide_created',
  97                         'priceGuide_endDate',
  98                         'stock_highestOffer',
  99                         'commission',
 100                         'isMatched',
 101                         'isDuplicate',
 102                         'multiMatch',
 103                         'adherence_dateSold',
 104                         'adherence_type',
 105                         'adherence_file',
 106                         'adherence_dealerCode',
 107                         'adherence_amount',
 108                         'adherence_soldTo'
 109         );
 110         /**
 111          * @var array
 112          */
 113         protected $_totals = array(
 114                         'stock_highestOffer',
 115                         'commission',
 116                         'adherence_amount'
 117         );
 118         /**
 119          * @var array
 120          */
 121         protected $_currencyFields = array(
 122                         'stock_highestOffer',
 123                         'commission',
 124                         'adherence_amount'
 125         );
 126
 127
 128
 129         /**
 130          * Build the dataset.
 131          */
 132         public function build()
 133         {
 134                 ini_set('memory_limit','512M');
 135
 136                 #-> Prepare parameters.
 137                 $selection = array(
 138                                 'stock', 'priceGuide', 'offer', 'type', 'model', 'make', 'year',
 139                                 'sellerCompany', 'sellerGroup', 'sellerDivision', 'sellerProfile',
 140                                 'buyerCompany', 'buyerGroup', 'buyerDivision', 'buyerProfile',
 141                                 'adherence'
 142                 );
 143                 $join      = array();
 144                 $leftJion  = array();
 145                 $where     = array();
 146                 $params    = array();
 147                 $query = 'SELECT [SELECTION] '
 148                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide'
 149                                 . ' JOIN priceGuide.stock stock '
 150                                 . ' JOIN priceGuide.offers offer WITH offer.amount=stock.highestOffer '
 151                                 . ' JOIN stock.adherence link '
 152                                 . ' JOIN link.adherence adherence '
 153                                 . ' JOIN stock.type type '
 154                                 . ' JOIN stock.vehicleYear year '
 155                                 . ' JOIN type.model model '
 156                                 . ' JOIN model.make make '
 157                                 . ' JOIN priceGuide.createdBy sellerProfile '
 158                                 . ' JOIN sellerProfile.company sellerCompany '
 159                                 . ' JOIN offer.profile buyerProfile '
 160                                 . ' JOIN offer.company buyerCompany '
 161                                 . ' LEFT JOIN sellerCompany.group sellerGroup '
 162                                 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
 163                                 . ' LEFT JOIN buyerProfile.group buyerGroup '
 164                                 . ' LEFT JOIN buyerProfile.groupDivision buyerDivision '
 165                                 . '[WHERE] '
 166                                 . 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';
 167
 168                 #-> Construct details.
 169                 $authData = \Utility\Registry::getAuthData();
 170                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
 171                 {
 172                         $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
 173                 }
 174                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 175                 $where[] = 'adherence.dateSold >= :dateFrom';
 176                 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 177
 178                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
 179                 {
 180                         $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
 181                 }
 182                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 183                 $where[] = 'adherence.dateSold < :dateTo';
 184                 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
 185
 186                 $where[] = 'priceGuide.created >= :datePgFrom';
 187                 $where[] = 'priceGuide.created <= :dateEnd';
 188                 $params['dateEnd'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -3 days');
 189                 $params['datePgFrom'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -4 months');
 190
 191                 if (isset($this->_input['group'])
 192                                 && !empty($this->_input['group'])
 193                                 && 'null' != $this->_input['group'])
 194                 {
 195                         $this->_queries['Group'] = $this->em
 196                                 ->find('\Company\Entity\Group', $this->_input['group'])
 197                                 ->name;
 198                         $where[] = 'sellerGroup.id = :group';
 199                         $params['group'] = $this->_input['group'];
 200                 }
 201                 if (isset($this->_input['groupDivision'])
 202                                 && !empty($this->_input['groupDivision'])
 203                                 && 'null' != $this->_input['groupDivision'])
 204                 {
 205                         error_log($this->_input['groupDivision']);
 206                         $this->_queries['Division'] = $this->em
 207                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 208                                 ->name;
 209                         $where[] = 'sellerDivision.id = :division';
 210                         $params['division'] = $this->_input['groupDivision'];
 211                 }
 212                 if (isset($this->_input['company'])
 213                                 && !empty($this->_input['company'])
 214                                 && 'null' != $this->_input['company'])
 215                 {
 216                         $this->_queries['Dealership'] = $this->em
 217                                 ->find('\Company\Entity\Company', $this->_input['company'])
 218                                 ->name;
 219                         $where[] = 'sellerCompany.id = :company';
 220                         $params['company'] = $this->_input['company'];
 221                 }
 222
 223                 #-> Finalize query.
 224                 $query = str_replace(array(
 225                                 '[SELECTION]', '[WHERE]'
 226                         ), array(
 227                                 implode(', ', $selection) . ' ',
 228                                 !empty($where)
 229                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 230                                         : ' '
 231                         ), $query);
 232
 233                 #-> Collect data.
 234                 $query = $this->em->createQuery($query);
 235                 !empty($params)
 236                         && $query->setParameters($params);
 237                 $this->_data = $query->getScalarResult();
 238                 $this->em->clear();
 239
 240                 #-> Additional info.
 241                 $increments = $this->em
 242                         ->getRepository('\Auction\Entity\Increment')
 243                         ->findBy(array(), array('to' => 'ASC'));
 244                 $prevId = 0;
 245                 $matched = array();
 246                 foreach ($this->_data as $id => $row)
 247                 {
 248                         if (!isset($matched[$row['adherence_id']]))
 249                         {
 250                                 $matched[$row['adherence_id']] = array();
 251                         }
 252                         $matched[$row['adherence_id']][$row['stock_id']] = true;
 253                 }
 254                 foreach ($this->_data as $id => $row)
 255                 {
 256                         $isDuplicate = true;
 257                         if ($prevId != $row['adherence_id'])
 258                         {
 259                                 $prevId = $row['adherence_id'];
 260                                 $isDuplicate = false;
 261                         }
 262                         $multimatch = 1 < count($matched[$row['adherence_id']])
 263                                 ? true
 264                                 : false;
 265                         $endDate = new \DateTime($row['priceGuide_created']->format(\Utility\Definitions\Locale::getDateTimeFormat()));
 266                         $endDate->add(new \DateInterval('P3D'));
 267                         $this->_data[$id]['priceGuide_endDate'] = $endDate;
 268                         $this->_data[$id]['commission'] = !$isDuplicate
 269                                 ? 1000.00
 270                                 : 0.00;
 271                         $this->_data[$id]['isMatched'] = !is_null($row['adherence_id'])
 272                                 ? 'Yes'
 273                                 : 'No';
 274                         $this->_data[$id]['multiMatch'] = $multimatch
 275                                 ? 'Yes'
 276                                 : 'No';
 277                         $this->_data[$id]['isDuplicate'] = $isDuplicate
 278                                 ? 'Yes'
 279                                 : 'No';
 280                 }
 281
 282
 283
 284
 285                 #-> No match section.
 286                 #-> Collect some data.
 287                 $selection = array(
 288                                 'adherence', 'sellerCompany', 'sellerGroup', 'sellerDivision'
 289
 290                 );
 291                 $join      = array();
 292                 $leftJion  = array();
 293                 $where     = array();
 294                 $params    = array();
 295                 $query = 'SELECT [SELECTION] '
 296                                 . 'FROM \Adherence\Entity\Adherence adherence'
 297                                 . ' LEFT JOIN adherence.stock stock '
 298                                 . ' LEFT JOIN adherence.company sellerCompany '
 299                                 . ' LEFT JOIN sellerCompany.group sellerGroup '
 300                                 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
 301                                 . '[WHERE] '
 302                                 . 'ORDER BY adherence.id ASC';
 303                 $where[] = 'stock.id IS NULL';
 304
 305                 #-> Construct details.
 306                 $authData = \Utility\Registry::getAuthData();
 307                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
 308                 {
 309                         $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
 310                 }
 311                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 312                 $where[] = 'adherence.dateSold >= :dateFrom';
 313                 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 314
 315                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
 316                 {
 317                         $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
 318                 }
 319                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
 320                 $where[] = 'adherence.dateSold < :dateTo';
 321                 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
 322
 323                 if (isset($this->_input['group'])
 324                                 && !empty($this->_input['group'])
 325                                 && 'null' != $this->_input['group'])
 326                 {
 327                         $this->_queries['Group'] = $this->em
 328                                 ->find('\Company\Entity\Group', $this->_input['group'])
 329                                 ->name;
 330                         $where[] = 'sellerGroup.id = :group';
 331                         $params['group'] = $this->_input['group'];
 332                 }
 333                 if (isset($this->_input['groupDivision'])
 334                                 && !empty($this->_input['groupDivision'])
 335                                 && 'null' != $this->_input['groupDivision'])
 336                 {
 337                         error_log($this->_input['groupDivision']);
 338                         $this->_queries['Division'] = $this->em
 339                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 340                                 ->name;
 341                         $where[] = 'sellerDivision.id = :division';
 342                         $params['division'] = $this->_input['groupDivision'];
 343                 }
 344                 if (isset($this->_input['company'])
 345                                 && !empty($this->_input['company'])
 346                                 && 'null' != $this->_input['company'])
 347                 {
 348                         $this->_queries['Dealership'] = $this->em
 349                                 ->find('\Company\Entity\Company', $this->_input['company'])
 350                                 ->name;
 351                         $where[] = 'sellerCompany.id = :company';
 352                         $params['company'] = $this->_input['company'];
 353                 }
 354
 355                 #-> Finalize query.
 356                 $query = str_replace(array(
 357                                 '[SELECTION]', '[WHERE]'
 358                         ), array(
 359                                 implode(', ', $selection) . ' ',
 360                                 !empty($where)
 361                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 362                                         : ' '
 363                         ), $query);
 364
 365                 #-> Collect data.
 366                 $query = $this->em->createQuery($query);
 367                 !empty($params)
 368                         && $query->setParameters($params);
 369                 $data = $query->getScalarResult();
 370                 $this->em->clear();
 371                 $prevId = 0;
 372                 foreach ($data as $id => $row)
 373                 {
 374                         $isDuplicate = true;
 375                         if ($prevId != $row['adherence_id'])
 376                         {
 377                                 $prevId = $row['adherence_id'];
 378                                 $isDuplicate = false;
 379                         }
 380                         $this->_data[] = array(
 381                                         'sellerGroup_name'                      => $row['sellerGroup_name'],
 382                                         'sellerDivision_name'           => $row['sellerDivision_name'],
 383                                         'sellerCompany_name'            => $row['sellerCompany_name'],
 384                                         'sellerProfile_firstName'       => '',
 385                                         'sellerProfile_familyName'      => '',
 386                                         'buyerGroup_name'                       => '',
 387                                         'buyerDivision_name'            => '',
 388                                         'buyerCompany_name'             => '',
 389                                         'buyerProfile_firstName'        => '',
 390                                         'buyerProfile_familyName'       => '',
 391                                         'make_name'                             => $row['adherence_make'],
 392                                         'model_name'                            => '',
 393                                         'type_name'                             => '',
 394                                         'year_name'                             => '',
 395                                         'stock_stockNumber'             => $row['adherence_stockNumber'],
 396                                         'stock_vinNumber'                       => $row['adherence_vinNumber'],
 397                                         'stock_engineNumber'            => $row['adherence_engineNumber'],
 398                                         'stock_registrationNumber'      => $row['adherence_registrationNumber'],
 399                                         'stock_tradePrice'                      => 0,
 400                                         'stock_retailPrice'             => 0,
 401                     'stock_listPrice'                   => 0,
 402                                         'priceGuide_created'            => '',
 403                                         'priceGuide_endDate'            => '',
 404                                         'stock_highestOffer'            => '',
 405                                         'commission'                            => 1000.00,
 406                                         'isMatched'                             => 'No',
 407                                         'isDuplicate'                           => $isDuplicate
 408                                                                                                         ? 'Yes'
 409                                                                                                         : 'No',
 410                                         'multiMatch'                            => 'No',
 411                                         'adherence_dateSold'            => $row['adherence_dateSold'],
 412                                         'adherence_type'                        => $row['adherence_type'],
 413                                         'adherence_file'                        => $row['adherence_file'],
 414                                         'adherence_dealerCode'          => $row['adherence_dealerCode'],
 415                                         'adherence_amount'                      => $row['adherence_amount'],
 416                                         'adherence_soldTo'                      => $row['adherence_soldTo']
 417                         );
 418                 }
 419         }
 420
 421 }