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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 /**
   7  * Stock Contyrol Report.
   8  * @author andre.fourie
   9  */
  10 class Invoice extends \Utility\Service\Report
  11 {
  12
  13
  14
  15         /**
  16          * @var string
  17          */
  18         protected $_title  = 'Invoice Report';
  19         /**
  20          * @var string
  21          */
  22         protected $_subject = 'Invoice';
  23         /**
  24          * @var string
  25          */
  26         protected $_description = 'Historical report for invoice data.';
  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                         'Group',
  38                         'Division',
  39                         'Dealership',
  40                         'SMS Quantity Total',
  41                         'SMS Rand Value Total',
  42                         'Sold on Auction',
  43                         'Auction Invoice Total',
  44                         'Price Guide Offer Matched',
  45                         'Commission',
  46                         'Price Guide Matched Duplicates',
  47                         'Matched to multiple stock entries'
  48         );
  49         /**
  50          * @var array
  51          */
  52         protected $_fields = array(
  53                         'fromGroup_name',
  54                         'fromDivision_name',
  55                         'fromCompany_name',
  56                         'clickCount',
  57                         'total',
  58                         'auctionItems',
  59                         'auctionTotal',
  60                         'pgTotal',
  61                         'pgCommission',
  62                         'pgDuplicates',
  63                         'pgMultiMatch'
  64         );
  65         /**
  66          * @var array
  67          */
  68         protected $_totals = array(
  69                         'clickCount',
  70                         'total',
  71                         'auctionItems',
  72                         'auctionTotal',
  73                         'pgTotal',
  74                         'pgCommission'
  75         );
  76         /**
  77          * @var array
  78          */
  79         protected $_currencyFields = array(
  80                         'total',
  81                         'auctionTotal',
  82                         'pgCommission'
  83         );
  84
  85
  86
  87         /**
  88          * Build the dataset.
  89          */
  90         public function build()
  91         {
  92                 ini_set('memory_limit','512M');
  93
  94                 #-> Prepare parameters.
  95                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
  96                 {
  97                         $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
  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                 $selection = array(
 104                                 'log', 'fromCompany', 'fromGroup', 'fromDivision'
 105                 );
 106                 $join      = array();
 107                 $leftJion  = array();
 108                 $where     = array();
 109                 $params    = array();
 110                 $query = 'SELECT [SELECTION] '
 111                                 . 'FROM \Utility\Entity\NotificationLog log '
 112                                 . ' LEFT JOIN log.fromCompany fromCompany '
 113                                 . ' LEFT JOIN fromCompany.group fromGroup '
 114                                 . ' LEFT JOIN fromCompany.groupDivision fromDivision '
 115                                 . '[WHERE] '
 116                                 . 'ORDER BY fromGroup.name, fromDivision.name, fromCompany.name';
 117
 118                 #-> Construct details.
 119                 $clickVal = isset($this->_input['clickVal']) && is_numeric($this->_input['clickVal'])
 120                         ? $this->_input['clickVal']
 121                         : 0.25;
 122                 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
 123                 {
 124                         $this->_queries['Date Range From'] = $this->_input['dateFrom'];
 125                         $where[] = 'log.created >= :dateFrom';
 126                         $params['dateFrom'] = new \DateTime($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                         $where[] = 'log.created <= :dateTo';
 132                         $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 133                 }
 134                 if (isset($this->_input['group'])
 135                                 && !empty($this->_input['group'])
 136                                 && 'null' != $this->_input['group'])
 137                 {
 138                         $this->_queries['Group'] = $this->em
 139                                 ->find('\Company\Entity\Group', $this->_input['group'])
 140                                 ->name;
 141                         $where[] = 'fromGroup.id = :group';
 142                         $params['group'] = $this->_input['group'];
 143                 }
 144                 if (isset($this->_input['groupDivision'])
 145                                 && !empty($this->_input['groupDivision'])
 146                                 && 'null' != $this->_input['groupDivision'])
 147                 {
 148                         $this->_queries['Division'] = $this->em
 149                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 150                                 ->name;
 151                         $where[] = 'fromDivision.id = :division';
 152                         $params['division'] = $this->_input['groupDivision'];
 153                 }
 154                 if (isset($this->_input['company'])
 155                                 && !empty($this->_input['company'])
 156                                 && 'null' != $this->_input['company'])
 157                 {
 158                         $this->_queries['Dealership'] = $this->em
 159                                 ->find('\Company\Entity\Company', $this->_input['company'])
 160                                 ->name;
 161                         $where[] = 'fromCompany.id = :company';
 162                         $params['company'] = $this->_input['company'];
 163                 }
 164                 $where[] = 'log.smsTo IS NOT NULL AND log.smsBody != \'\'';
 165
 166                 #-> Finalize query.
 167                 $query = str_replace(array(
 168                                 '[SELECTION]', '[WHERE]'
 169                         ), array(
 170                                 implode(', ', $selection) . ' ',
 171                                 !empty($where)
 172                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 173                                         : ' '
 174                         ), $query);
 175
 176                 #-> Collect data.
 177                 $query = $this->em->createQuery($query);
 178                 !empty($params)
 179                         && $query->setParameters($params);
 180                 $data = $query->getScalarResult();
 181                 $this->em->clear();
 182                 $this->_data = array();
 183                 $company = false;
 184                 $entry = array();
 185                 foreach ($data as $id => $row)
 186                 {
 187                         if ($company !== $row['fromCompany_name'])
 188                         {
 189                                 if (false != $company)
 190                                 {
 191                                         $this->_data[$company] = $entry;
 192                                 }
 193                                 $company = $row['fromCompany_name'];
 194                                 $entry = $row;
 195                                 $entry['clickCount']    = 0;
 196                                 $entry['total']                 = 0;
 197                                 $entry['auctionItems']  = 0;
 198                                 $entry['auctionTotal']  = 0;
 199                                 $entry['pgTotal']               = 0;
 200                                 $entry['pgCommission']  = 0.0;
 201                                 $entry['pgDuplicates']  = '';
 202                                 $entry['pgMultiMatch']  = '';
 203                         }
 204                         $entry['clickCount'] += strlen($row['log_smsBody']) > 160
 205                                 ? ceil(strlen($row['log_smsBody']) / (160 - 7))
 206                                 : 1;
 207                         $entry['total'] = round($entry['clickCount'] * $clickVal, 2);
 208                 }
 209                 if (!empty($entry))
 210                 {
 211                         $this->_data[$company] = $entry;
 212                 }
 213
 214
 215                 #-> Auction data.
 216                 $selection = array(
 217                                 'auction', 'company', 'companyGroup', 'companyDivision',
 218                                 'COUNT(auction) as auctionItems, SUM(auction.bidIncrement) as auctionTotal'
 219                 );
 220                 $join      = array();
 221                 $leftJion  = array();
 222                 $where     = array();
 223                 $params    = array();
 224                 $query = 'SELECT [SELECTION] '
 225                                 . 'FROM \Auction\Entity\Auction auction '
 226                                 . ' JOIN auction.stock stock '
 227                                 . ' JOIN stock.company company '
 228                                 . ' LEFT JOIN company.group companyGroup '
 229                                 . ' LEFT JOIN company.groupDivision companyDivision '
 230                                 . '[WHERE] '
 231                                 . 'GROUP BY companyGroup.name, companyDivision.name, company.name';
 232                 $where[] = 'auction.jobState=\'Sold\'';
 233
 234                 #-> Construct details.
 235                 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
 236                 {
 237                         $where[] = 'auction.endDate >= :dateFrom';
 238                         $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 239                 }
 240                 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
 241                 {
 242                         $where[] = 'auction.endDate <= :dateTo';
 243                         $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
 244                 }
 245                 if (isset($this->_input['group'])
 246                                 && !empty($this->_input['group'])
 247                                 && 'null' != $this->_input['group'])
 248                 {
 249                         $where[] = 'companyGroup.id = :group';
 250                         $params['group'] = $this->_input['group'];
 251                 }
 252                 if (isset($this->_input['groupDivision'])
 253                                 && !empty($this->_input['groupDivision'])
 254                                 && 'null' != $this->_input['groupDivision'])
 255                 {
 256                         $where[] = 'companyDivision.id = :division';
 257                         $params['division'] = $this->_input['groupDivision'];
 258                 }
 259                 if (isset($this->_input['company'])
 260                                 && !empty($this->_input['company'])
 261                                 && 'null' != $this->_input['company'])
 262                 {
 263                         $where[] = 'company.id = :company';
 264                         $params['company'] = $this->_input['company'];
 265                 }
 266
 267                 #-> Finalize query.
 268                 $query = str_replace(array(
 269                                 '[SELECTION]', '[WHERE]'
 270                         ), array(
 271                                 implode(', ', $selection) . ' ',
 272                                 !empty($where)
 273                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 274                                         : ' '
 275                         ), $query);
 276
 277                 #-> Collect data.
 278                 $query = $this->em->createQuery($query);
 279                 !empty($params)
 280                         && $query->setParameters($params);
 281                 $data = $query->getScalarResult();
 282                 $this->em->clear();
 283                 foreach ($data as $id => $row)
 284                 {
 285                         if (isset($this->_data[$row['company_name']]))
 286                         {
 287                                 $this->_data[$row['company_name']]['auctionItems'] += $row['auctionItems'];
 288                                 $this->_data[$row['company_name']]['auctionTotal'] += $row['auctionTotal'];
 289                         }
 290                         else
 291                         {
 292                                 $this->_data[$row['company_name']] = array(
 293                                         'fromGroup_name'        => $row['companyGroup_name'],
 294                                         'fromDivision_name' => $row['companyDivision_name'],
 295                                         'fromCompany_name'      => $row['company_name'],
 296                                         'clickCount'            => 0,
 297                                         'total'                         => 0.0,
 298                                         'auctionItems'          => $row['auctionItems'],
 299                                         'auctionTotal'          => $row['auctionTotal'],
 300                                         'pgTotal'                       => 0,
 301                                         'pgCommission'          => 0.0,
 302                                         'pgDuplicates'          => '',
 303                                         'pgMultiMatch'          => ''
 304                                 );
 305                         }
 306                 }
 307
 308
 309                 #-> Price Guide.
 310                 $selection = array(
 311                                 'stock', 'priceGuide', 'offer',
 312                                 'sellerCompany', 'sellerGroup', 'sellerDivision',
 313                                 'adherence'
 314                 );
 315                 $join      = array();
 316                 $leftJion  = array();
 317                 $where     = array();
 318                 $params    = array();
 319                 $query = 'SELECT [SELECTION] '
 320                                 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide'
 321                                 . ' JOIN priceGuide.stock stock '
 322                                 . ' JOIN priceGuide.offers offer WITH offer.amount=stock.highestOffer '
 323                                 . ' JOIN stock.adherence link '
 324                                 . ' JOIN link.adherence adherence '
 325                                 . ' JOIN priceGuide.createdBy sellerProfile '
 326                                 . ' JOIN sellerProfile.company sellerCompany '
 327                                 . ' LEFT JOIN sellerCompany.group sellerGroup '
 328                                 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
 329                                 . '[WHERE] '
 330                                 . 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';
 331
 332                 #-> Construct details.
 333                 $where[] = 'adherence.dateSold >= :dateFrom';
 334                 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
 335                 $where[] = 'adherence.dateSold < :dateTo';
 336                 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
 337                 $where[] = 'priceGuide.created >= :datePgFrom';
 338                 $where[] = 'priceGuide.created <= :dateTo';
 339                 $params['datePgFrom'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -4 months');
 340                 if (isset($this->_input['group'])
 341                                 && !empty($this->_input['group'])
 342                                 && 'null' != $this->_input['group'])
 343                 {
 344                         $where[] = 'sellerGroup.id = :group';
 345                         $params['group'] = $this->_input['group'];
 346                 }
 347                 if (isset($this->_input['groupDivision'])
 348                                 && !empty($this->_input['groupDivision'])
 349                                 && 'null' != $this->_input['groupDivision'])
 350                 {
 351                         $where[] = 'sellerDivision.id = :division';
 352                         $params['division'] = $this->_input['groupDivision'];
 353                 }
 354                 if (isset($this->_input['company'])
 355                                 && !empty($this->_input['company'])
 356                                 && 'null' != $this->_input['company'])
 357                 {
 358                         $where[] = 'sellerCompany.id = :company';
 359                         $params['company'] = $this->_input['company'];
 360                 }
 361
 362                 #-> Finalize query.
 363                 $query = str_replace(array(
 364                                 '[SELECTION]', '[WHERE]'
 365                         ), array(
 366                                 implode(', ', $selection) . ' ',
 367                                 !empty($where)
 368                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 369                                         : ' '
 370                         ), $query);
 371
 372                 #-> Collect data.
 373                 $query = $this->em->createQuery($query);
 374                 !empty($params)
 375                         && $query->setParameters($params);
 376                 $data = $query->getScalarResult();
 377                 $this->em->clear();
 378
 379                 #-> Additional info.
 380                 $prevId = 0;
 381                 $matched = array();
 382                 foreach ($data as $id => $row)
 383                 {
 384                         if (!isset($matched[$row['adherence_id']]))
 385                         {
 386                                 $matched[$row['adherence_id']] = array();
 387                         }
 388                         $matched[$row['adherence_id']][$row['stock_id']] = true;
 389                 }
 390                 foreach ($data as $id => $row)
 391                 {
 392                         $isDuplicate = true;
 393                         if ($prevId != $row['adherence_id'])
 394                         {
 395                                 $prevId = $row['adherence_id'];
 396                                 $isDuplicate = false;
 397                         }
 398                         $multimatch = 1 < count($matched[$row['adherence_id']])
 399                                 ? true
 400                                 : false;
 401
 402                         if (isset($this->_data[$row['sellerCompany_name']]))
 403                         {
 404                                 $this->_data[$row['sellerCompany_name']]['pgTotal'] = 1;
 405                                 $this->_data[$row['sellerCompany_name']]['pgCommission'] += ($isDuplicate ? 0.0 : 1000.0);
 406                                 $this->_data[$row['sellerCompany_name']]['pgDuplicates'] =
 407                                         $isDuplicate || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgDuplicates']
 408                                                 ? 'Yes'
 409                                                 : '';
 410                                 $this->_data[$row['sellerCompany_name']]['pgMultiMatch'] =
 411                                         $multimatch || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgMultiMatch']
 412                                                 ? 'Yes'
 413                                                 : '';
 414                         }
 415                         else
 416                         {
 417                                 $this->_data[$row['sellerCompany_name']] = array(
 418                                                 'fromGroup_name'        => $row['sellerGroup_name'],
 419                                                 'fromDivision_name' => $row['sellerDivision_name'],
 420                                                 'fromCompany_name'      => $row['sellerCompany_name'],
 421                                                 'clickCount'            => 0,
 422                                                 'total'                         => 0.0,
 423                                                 'auctionItems'          => 0,
 424                                                 'auctionTotal'          => 0.0,
 425                                                 'pgTotal'                       => 1,
 426                                                 'pgCommission'          => $isDuplicate ? 0.0 : 1000.0,
 427                                                 'pgDuplicates'          => $isDuplicate ? 'Yes' : '',
 428                                                 'pgMultiMatch'          => $multimatch ? 'Yes' : ''
 429                                 );
 430                         }
 431                 }
 432         }
 433
 434 }