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

   1 <?php
   2 namespace Report\Report;
   3
   4
   5
   6 /**
   7  * Stock Control Report.
   8  * @author andre.fourie
   9  */
  10 class LoadVsSold extends \Utility\Service\Report
  11 {
  12
  13
  14
  15         /**
  16          * @var string
  17          */
  18         protected $_title  = 'Loaded and Sold';
  19         /**
  20          * @var string
  21          */
  22         protected $_subject = 'Vehicles loaded vs vehicles sold';
  23         /**
  24          * @var string
  25          */
  26         protected $_description = 'Vehicles loaded vs vehicles sold.';
  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                         'Month',
  38                         'Loaded',
  39                         'Sold'
  40         );
  41         /**
  42          * @var array
  43          */
  44         protected $_fields = array(
  45                         'month',
  46                         'loaded',
  47                         'sold'
  48         );
  49         /**
  50          * @var array
  51          */
  52         protected $_totals = array(
  53                         'loaded',
  54                         'sold'
  55         );
  56         /**
  57          * @var array
  58          */
  59         protected $_currencyFields = array();
  60
  61         /**
  62          * @var array
  63          */
  64
  65         /**
  66          * Build the dataset.
  67          */
  68         public function build()
  69         {
  70                 $config = $this->em->getConfiguration();
  71         $config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');
  72         $config->addCustomStringFunction('SOUNDEX', 'UVd\DoctrineFunction\Soundex');
  73
  74         $year = $this->_input['year'];
  75                 $this->_data = array();
  76
  77                 //Collect Data
  78                 $selection = array(
  79                                 'division.name',
  80                                 'company.name as companyName',
  81                                 'tradeCenter.name as tradeCenterName',
  82                                 'manager.firstName',
  83                                 'manager.familyName',
  84                                 'stock.id as stockId',
  85                                 'SOUNDEX(stock.stockNumber) as stockNumber',
  86                                 'SOUNDEX(stock.registrationNumber) as registrationNumber',
  87                                 'SOUNDEX(stock.vinNumber) as vinNumber',
  88                                 'SOUNDEX(stock.engineNumber) as engineNumber',
  89                                 'DATE_FORMAT(auction.endDate ,\'%Y-%m\') as month',
  90                                 'auction.jobState'
  91                         );
  92                 $where     = array();
  93                 $params    = array();
  94                 $query = 'SELECT [SELECTION] '
  95                                 . 'FROM \Stock\Entity\Stock stock '
  96                                 . 'JOIN stock.auction auction '
  97                                 . 'JOIN stock.company company '
  98                                 . 'LEFT JOIN company.tradeCenter tradeCenter '
  99                         . 'LEFT JOIN company.region region '
 100                                 . 'LEFT JOIN company.group grp '
 101                                 . 'LEFT JOIN company.groupDivision division '
 102                         . 'LEFT JOIN company.regionalManager manager '
 103                                 . '[WHERE] '
 104                                 . 'ORDER BY auction.endDate';
 105                 $where[] = 'auction.jobState != :status';
 106                 $params['status'] = 'Active';
 107
 108                 $this->_queries['Date Range From'] = $year . '-01-01';
 109                 $where[] = 'auction.endDate >= :dateFrom';
 110                 $params['dateFrom'] = new \DateTime($year . '-01-01');
 111
 112                 $this->_queries['Date Range Until'] = $year . '-12-31';
 113                 $where[] = 'auction.endDate <= :dateTo';
 114                 $params['dateTo'] = new \DateTime($year . '-12-31');
 115
 116                 if (isset($this->_input['group'])
 117                         && !empty($this->_input['group'])
 118                         && 'null' != $this->_input['group'])
 119                 {
 120                         $this->_queries['Group'] = $this->em
 121                                 ->find('\Company\Entity\Group', $this->_input['group'])
 122                                 ->name;
 123                         $where[] = 'grp.id = :group';
 124                         $params['group'] = $this->_input['group'];
 125                 }
 126                 if (isset($this->_input['region'])
 127                         && !empty($this->_input['region'])
 128                         && 'null' != $this->_input['region'])
 129                 {
 130                         $this->_queries['Region'] = $this->em
 131                                 ->find('\Location\Entity\Region', $this->_input['region'])
 132                                 ->name;
 133                         $where[] = 'region.id = :region';
 134                         $params['region'] = $this->_input['region'];
 135                 }
 136
 137                 if (isset($this->_input['groupDivision'])
 138                         && !empty($this->_input['groupDivision'])
 139                         && 'null' != $this->_input['groupDivision'])
 140                 {
 141                         $this->_queries['Division'] = $this->em
 142                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
 143                                 ->name;
 144                         $where[] = 'division.id = :division';
 145                         $params['division'] = $this->_input['groupDivision'];
 146                 }
 147
 148                 if (isset($this->_input['company'])
 149                         && !empty($this->_input['company'])
 150                         && 'null' != $this->_input['company'])
 151                 {
 152                         $this->_queries['Dealership'] = $this->em
 153                                 ->find('\Company\Entity\Company', $this->_input['company'])
 154                                 ->name;
 155                         $where[] = 'company.id = :company';
 156                         $params['company'] = $this->_input['company'];
 157                 }
 158
 159                 /* if (isset($this->_input['regionalManager'])
 160                  && !empty($this->_input['regionalManager'])
 161                                 && 'null' != $this->_input['regionalManager'])
 162                 {
 163                 $where[] = 'manager.id IN (:regionalManager)';
 164                 $params['regionalManager'] = $this->_input['regionalManager'];
 165                 } */
 166
 167                 //Finalize query.
 168                 $query = str_replace(array(
 169                                 '[SELECTION]', '[WHERE]'
 170                 ), array(
 171                                 implode(', ', $selection) . ' ',
 172                                 !empty($where)
 173                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
 174                                         : ' '
 175                 ), $query);
 176
 177                 $query = $this->em->createQuery($query);
 178                 !empty($params)
 179                         && $query->setParameters($params);
 180                 $result = $query->getScalarResult();
 181                 $this->em->clear();
 182
 183                 $prepDvsn = array();
 184                 $prepComp = array();
 185                 $centers  = array();
 186                 foreach ($result as $id => $row)
 187                 {
 188                         $combo = $row['name']
 189                                 . '.' . $row['firstName']
 190                                 . '.' . $row['familyName']
 191                                 . '.' . $row['stockNumber']
 192                                 . '.' . $row['registrationNumber']
 193                                 . '.' . $row['vinNumber']
 194                                 . '.' . $row['engineNumber']
 195                                 . '.' . $row['month'];
 196                         $fullName = $row['firstName'] . ' ' . $row['familyName'];
 197                         $divisionName = $row['name'];
 198                         $companyName  = $row['companyName'];
 199                         if (!is_null($row['tradeCenterName']))
 200                         {
 201                                 $centers["$divisionName.$companyName"] = $row['tradeCenterName'];
 202                         }
 203
 204                         #-> Division aggregation.
 205                         if (!isset($prepDvsn[$divisionName]))
 206                         {
 207                                 $prepDvsn[$divisionName] = array();
 208                         }
 209                         if (!isset($prepDvsn[$divisionName][$fullName]))
 210                         {
 211                                 $prepDvsn[$divisionName][$fullName] = array();
 212                         }
 213                         if (!isset($prepDvsn[$divisionName][$fullName][$row['month']]))
 214                         {
 215                                 $prepDvsn[$divisionName][$fullName][$row['month']] = array();
 216                         }
 217                         if (!isset($prepDvsn[$divisionName][$fullName][$row['month']][$combo]))
 218                         {
 219                                 $prepDvsn[$divisionName][$fullName][$row['month']][$combo] = $row;
 220                         }
 221
 222                         #-> Company aggregation.
 223                         if (!isset($prepComp[$divisionName]))
 224                         {
 225                                 $prepComp[$divisionName] = array();
 226                         }
 227                         if (!isset($prepComp[$divisionName][$fullName]))
 228                         {
 229                                 $prepComp[$divisionName][$fullName] = array();
 230                         }
 231                         if (!isset($prepComp[$divisionName][$fullName][$companyName]))
 232                         {
 233                                 $prepComp[$divisionName][$fullName][$companyName] = array();
 234                         }
 235                         if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']]))
 236                         {
 237                                 $prepComp[$divisionName][$fullName][$companyName][$row['month']] = array();
 238                         }
 239                         if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo]))
 240                         {
 241                                 $prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo] = $row;
 242                         }
 243                 }
 244                 $this->_data['TradeCenterData'] = $centers;
 245
 246                 #-> Division data.
 247                 $output = array();
 248                 foreach ($prepDvsn as $division => $row1)
 249                 {
 250                         $output[$division] = array();
 251                         foreach ($row1 as $manager => $row2)
 252                         {
 253                                 $output[$division][$manager] = array();
 254                                 foreach ($row2 as $month => $row3)
 255                                 {
 256                                         $output[$division][$manager][$month] = array(
 257                                                 'Relist' => 0,
 258                                                 'Sold'   => 0,
 259                                                 'Undone' => 0
 260                                         );
 261                                         foreach ($row3 as $combo => $data)
 262                                         {
 263                                                 $output[$division][$manager][$month][$data['jobState']]++;
 264                                         }
 265                                 }
 266                         }
 267                 }
 268                 $prepDvsn = null;
 269                 $this->_data['DivisionData'] = $output;
 270
 271                 #-> Company data.
 272                 $output = array();
 273                 foreach ($prepComp as $division => $row1)
 274                 {
 275                         $output[$division] = array();
 276                         foreach ($row1 as $manager => $row2)
 277                         {
 278                                 $output[$division][$manager] = array();
 279                                 foreach ($row2 as $company => $row3)
 280                                 {
 281                                         $output[$division][$manager][$company] = array();
 282                                         foreach ($row3 as $month => $row4)
 283                                         {
 284                                                 $output[$division][$manager][$company][$month] = array(
 285                                                                 'Relist' => 0,
 286                                                                 'Sold'   => 0,
 287                                                                 'Undone' => 0
 288                                                 );
 289                                                 foreach ($row4 as $combo => $data)
 290                                                 {
 291                                                         $output[$division][$manager][$company][$month][$data['jobState']]++;
 292                                                 }
 293                                         }
 294                                 }
 295                         }
 296                 }
 297                 $prepDvsn = null;
 298                 $this->_data['CompanyData'] = $output;
 299
 300                 #-> Finalise.
 301                 $this->_queries['year'] = $year;
 302                 return;
 303         }
 304
 305 }