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

   1 <?php
   2
   3 namespace Report\Report;
   4
   5 class LoadVsSoldExcel extends \Utility\Export\ExcelReport
   6 {
   7
   8         /**
   9          * Constructor.
  10          *
  11          * @param \Utility\Service\Report $report
  12          */
  13         public function __construct(\Utility\Service\Report $report) {
  14                 $this->_fileName = str_replace( ' ', '-', $report->getTitle() );
  15                 $objPHPExcel = new \PHPExcel();
  16                 $objWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel2007' );
  17                 $this->_objReport = $report;
  18                 $this->_format = $report->getFormat();
  19                 $this->_queries = $report->getQueries();
  20                 $this->_headers = $report->getHeaders();
  21                 $this->_fields = $report->getFields();
  22                 $this->_totals = $report->getTotalFields();
  23                 $this->_currencyFields = $report->getCurrencyFields();
  24                 $this->_notes = $report->getNotes();
  25                 $this->_chart = $report->getChart();
  26                 $this->_data = $report->getData();
  27                 $this->_objWriter = $objWriter;
  28                 $this->_objPHPExcel = $objPHPExcel;
  29                 $this->_objPHPExcel->getProperties()->setCreator( APPLICATION );
  30                 $this->_objPHPExcel->getProperties()->setLastModifiedBy( APPLICATION );
  31                 $this->_objPHPExcel->getProperties()->setTitle( $report->getTitle() );
  32                 $this->_objPHPExcel->getProperties()->setSubject( $report->getSubject() );
  33                 $this->_objPHPExcel->getProperties()->setDescription( $report->getDescription() );
  34
  35                 if (count( $this->_data['DivisionData'] ) > 1) {
  36                         $objPHPExcel->getActiveSheet()->setTitle( 'Summary' );
  37                         $this->writeHeader( $this->_queries ['year'] . ' Loaded Vs Sold' );
  38
  39                         $lineNumber = 3;
  40
  41                         $this->writeMonths( 1, $lineNumber );
  42
  43                         $lineNumber += 2;
  44
  45                         $this->writeSummary( $lineNumber );
  46
  47                         $this->writeDivisions();
  48                 } else {
  49                         $this->writeDivisions( false );
  50                 }
  51
  52                 return $this;
  53         }
  54
  55         private function writeHeader($text) {
  56                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [0] . "1", $text );
  57                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [0] . "1" )->applyFromArray( array(
  58                                 'font' => array(
  59                                                 'bold' => true,
  60                                                 'size' => 14
  61                                 ),
  62                                 'alignment' => array(
  63                                                 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT
  64                                 )
  65                 ) );
  66         }
  67
  68         private function writeMonths($i, $lineNumber, $region = false) {
  69                 $headers = array(
  70                                 'January',
  71                                 'February',
  72                                 'March',
  73                                 'April',
  74                                 'May',
  75                                 'June',
  76                                 'July',
  77                                 'August',
  78                                 'September',
  79                                 'October',
  80                                 'November',
  81                                 'December'
  82                 );
  83
  84                 $subHeading = array(
  85                                 'Loaded',
  86                                 'Sold'
  87                 );
  88
  89                 if ($region) {
  90                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1), 'Regional Manager' );
  91                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] .($lineNumber + 1) )->applyFromArray( array(
  92                                         'font' => array(
  93                                                         'bold' => true,
  94                                                         'size' => 8
  95                                         ),
  96                                         'alignment' => array(
  97                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
  98                                         ),
  99                                         'fill' => array(
 100                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 101                                                         'color' => array(
 102                                                                         'rgb' => 'dddddd'
 103                                                         )
 104                                         )
 105                         ) );
 106
 107                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1), 'Trade Center' );
 108                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] .($lineNumber + 1) )->applyFromArray( array(
 109                                         'font' => array(
 110                                                         'bold' => true,
 111                                                         'size' => 8
 112                                         ),
 113                                         'alignment' => array(
 114                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
 115                                         ),
 116                                         'fill' => array(
 117                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 118                                                         'color' => array(
 119                                                                         'rgb' => 'dddddd'
 120                                                         )
 121                                         )
 122                         ) );
 123
 124                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1), 'Branch' );
 125                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] .($lineNumber + 1) )->applyFromArray( array(
 126                                         'font' => array(
 127                                                         'bold' => true,
 128                                                         'size' => 8
 129                                         ),
 130                                         'alignment' => array(
 131                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
 132                                         ),
 133                                         'fill' => array(
 134                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 135                                                         'color' => array(
 136                                                                         'rgb' => 'dddddd'
 137                                                         )
 138                                         )
 139                         ) );
 140                 }
 141
 142                 foreach ( $headers as $key => $val ) {
 143                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $val );
 144                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i] . $lineNumber )->applyFromArray( array(
 145                                         'font' => array(
 146                                                         'bold' => true,
 147                                                         'size' => 8
 148                                         ),
 149                                         'alignment' => array(
 150                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
 151                                         ),
 152                                         'fill' => array(
 153                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 154                                                         'color' => array(
 155                                                                         'rgb' => 'aaaaaa'
 156                                                         )
 157                                         )
 158                         ) );
 159
 160                         $this->_objPHPExcel->getActiveSheet()->mergeCells( $this->numberToColumn [$i] . $lineNumber . ':' . $this->numberToColumn [$i + 1] . $lineNumber );
 161
 162                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1), $subHeading [0] );
 163                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i] .($lineNumber + 1) )->applyFromArray( array(
 164                                         'font' => array(
 165                                                         'bold' => true,
 166                                                         'size' => 8
 167                                         ),
 168                                         'alignment' => array(
 169                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
 170                                         ),
 171                                         'fill' => array(
 172                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 173                                                         'color' => array(
 174                                                                         'rgb' => 'dddddd'
 175                                                         )
 176                                         )
 177                         ) );
 178
 179                         $i ++;
 180
 181                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1), $subHeading [1] );
 182                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i] .($lineNumber + 1) )->applyFromArray( array(
 183                                         'font' => array(
 184                                                         'bold' => true,
 185                                                         'size' => 8
 186                                         ),
 187                                         'alignment' => array(
 188                                                         'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
 189                                         ),
 190                                         'fill' => array(
 191                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 192                                                         'color' => array(
 193                                                                         'rgb' => 'dddddd'
 194                                                         )
 195                                         )
 196                         ) );
 197
 198                         $i ++;
 199                 }
 200         }
 201
 202         private function writeSummary($lineNumber)
 203         {
 204                 $summary = array();
 205                 foreach ( $this->_data['DivisionData'] as $division_name => $division ) {
 206                         foreach ( $division as $manager ) {
 207                                 foreach ( $manager as $month_name => $month ) {
 208                                         if (!isset($summary[$division_name][$month_name]))
 209                                         {
 210                                                 $summary[$division_name][$month_name] = array(
 211                                                                 'Sold'          => 0,
 212                                                                 'Loaded'        => 0
 213                                                 );
 214                                         }
 215                                         $summary[$division_name][$month_name]['Sold']   += $month['Sold'];
 216                                         $summary[$division_name][$month_name]['Loaded'] += $month['Sold'];
 217                                         $summary[$division_name][$month_name]['Loaded'] += $month['Relist'];
 218                                         $summary[$division_name][$month_name]['Loaded'] += $month['Undone'];
 219
 220                                         /* if ($month ['jobState'] == 'Sold') {
 221                                                 $summary [$division_name] [$month ['month']] ['Sold'] =(isset( $summary [$division_name] [$month ['month']] ['Sold'] ) ? $summary [$division_name] [$month ['month']] ['Sold'] + $month ['counter'] : $month ['counter']);
 222                                                 $summary [$division_name] [$month ['month']] ['Loaded'] =(isset( $summary [$division_name] [$month ['month']] ['Loaded'] ) ? $summary [$division_name] [$month ['month']] ['Loaded'] + $month ['counter'] : $month ['counter']);
 223                                         } else {
 224                                                 $summary [$division_name] [$month ['month']] ['Loaded'] =(isset( $summary [$division_name] [$month ['month']] ['Loaded'] ) ? $summary [$division_name] [$month ['month']] ['Loaded'] + $month ['counter'] : $month ['counter']);
 225                                         } */
 226                                 }
 227                         }
 228                 }
 229
 230                 $lineNumber = $this->writeData( $summary, $lineNumber );
 231         }
 232
 233         private function writeDivisions($summary = true)
 234         {
 235                 foreach ( $this->_data['CompanyData'] as $division_name => $division ) {
 236                         if ($summary) {
 237                                 $this->_objPHPExcel->createSheet();
 238                                 $this->_objPHPExcel->setActiveSheetIndex( $this->_objPHPExcel->getSheetCount() - 1 );
 239                         }
 240                         $title = preg_replace( "/[^a-zA-Z0-9\s]+/", "", $division_name );
 241                         $title = substr( $title, 0, 30 );
 242
 243                         if ($title) {
 244                                 $this->_objPHPExcel->getActiveSheet()->setTitle( $title );
 245                         } else {
 246                                 $this->_objPHPExcel->getActiveSheet()->setTitle( 'Unknown' );
 247                         }
 248
 249                         $this->writeHeader( $division_name . ' ' . $this->_queries ['year'] . ' Loaded Vs Sold' );
 250
 251                         $lineNumber = 3;
 252
 253                         foreach ( $division as $manager_name => $manager ) {
 254                                 $this->writeMonths( 0, $lineNumber, true );
 255
 256                                 $lineNumber += 2;
 257
 258                                 $summary = array();
 259
 260                                 foreach ( $manager as $company_name => $company ) {
 261                                         foreach ( $company as $month_name => $month ) {
 262                                                 if (!isset($summary[$company_name][$month_name]))
 263                                                 {
 264                                                         $summary[$company_name][$month_name] = array(
 265                                                                         'Sold'          => 0,
 266                                                                         'Loaded'        => 0
 267                                                         );
 268                                                 }
 269                                                 $summary[$company_name][$month_name]['Sold']    += $month['Sold'];
 270                                                 $summary[$company_name][$month_name]['Loaded']  += $month['Sold'];
 271                                                 $summary[$company_name][$month_name]['Loaded']  += $month['Relist'];
 272                                                 $summary[$company_name][$month_name]['Loaded']  += $month['Undone'];
 273
 274                                                 /* if ($month ['jobState'] == 'Sold') {
 275                                                         $summary [$company_name] [$month ['month']] ['Sold'] =(isset( $summary [$month ['companyName']] [$month ['month']] ['Sold'] ) ? $summary [$month ['companyName']] [$month ['month']] ['Sold'] + $month ['counter'] : $month ['counter']);
 276                                                         $summary [$company_name] [$month ['month']] ['Loaded'] =(isset( $summary [$month ['companyName']] [$month ['month']] ['Loaded'] ) ? $summary [$month ['companyName']] [$month ['month']] ['Loaded'] + $month ['counter'] : $month ['counter']);
 277                                                 } else {
 278                                                         $summary [$company_name] [$month ['month']] ['Loaded'] =(isset( $summary [$month ['companyName']] [$month ['month']] ['Loaded'] ) ? $summary [$month ['companyName']] [$month ['month']] ['Loaded'] + $month ['counter'] : $month ['counter']);
 279                                                 } */
 280                                         }
 281                                 }
 282
 283                                 $lineNumber = $this->writeData( $summary, $lineNumber, $manager_name, $division_name );
 284                         }
 285                 }
 286         }
 287
 288         private function writeData($data, $lineNumber, $region = false, $division = false)
 289         {
 290                 $months = array();
 291
 292                 for($i = 1; $i <= 12; $i ++) {
 293                         $months [] = $this->_queries ['year'] . '-' . str_pad(( string ) $i, 2, '0', STR_PAD_LEFT );
 294                 }
 295
 296                 $first = true;
 297
 298                 $totals = array();
 299                 foreach ( $months as $month ) {
 300                         $totals [$month] ['Loaded'] = 0;
 301                         $totals [$month] ['Sold'] = 0;
 302                 }
 303
 304                 foreach ( $data as $key => $entry ) {
 305                         $i = 0;
 306                         if ($region) {
 307                                 if ($first) {
 308                                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber,($region == ' ' ? 'Other' : $region) );
 309                                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i] . $lineNumber )->applyFromArray( array(
 310                                                         'font' => array(
 311                                                                         'bold' => true,
 312                                                                         'size' => 8
 313                                                         )
 314                                         ) );
 315                                         $first = false;
 316                                 }
 317                                 $i ++;
 318
 319                                 $tradeCenter = isset($this->_data['TradeCenterData']["$division.$key"])
 320                                         ? $this->_data['TradeCenterData']["$division.$key"]
 321                                         : '';
 322                                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $tradeCenter );
 323                                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 324                                                 'font' => array(
 325                                                                 'bold' => true,
 326                                                                 'size' => 8
 327                                                 )
 328                                 ) );
 329
 330                                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $key );
 331                                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 332                                                 'font' => array(
 333                                                                 'bold' => true,
 334                                                                 'size' => 8
 335                                                 )
 336                                 ) );
 337
 338                         } else {
 339                                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $key );
 340                                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 341                                                 'font' => array(
 342                                                                 'bold' => true,
 343                                                                 'size' => 8
 344                                                 )
 345                                 ) );
 346                         }
 347
 348                         foreach ( $months as $month ) {
 349                                 $sold =(isset( $entry [$month] ['Sold'] ) ? $entry [$month] ['Sold'] : '0');
 350
 351                                 $loaded =(isset( $entry [$month] ['Loaded'] ) ? $entry [$month] ['Loaded'] : '0');
 352
 353                                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $loaded );
 354                                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 355                                                 'font' => array(
 356                                                                 'bold' => true,
 357                                                                 'size' => 8
 358                                                 )
 359                                 ) );
 360                                 $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $sold );
 361                                 $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 362                                                 'font' => array(
 363                                                                 'bold' => true,
 364                                                                 'size' => 8
 365                                                 )
 366                                 ) );
 367
 368                                 $totals [$month] ['Loaded'] += $loaded;
 369                                 $totals [$month] ['Sold'] += $sold;
 370                         }
 371                         $lineNumber ++;
 372                 }
 373
 374                 // Totals
 375                 $i = 1;
 376                 if ($region) {
 377                         $i = 3;
 378                 }
 379
 380                 foreach ( $totals as $total ) {
 381
 382                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $total ['Loaded'] );
 383                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] . $lineNumber )->applyFromArray( array(
 384                                         'font' => array(
 385                                                         'bold' => true,
 386                                                         'size' => 8
 387                                         ),
 388                                         'fill' => array(
 389                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 390                                                         'color' => array(
 391                                                                         'rgb' => 'dddddd'
 392                                                         )
 393                                         )
 394                         ) );
 395                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] . $lineNumber, $total ['Sold'] );
 396                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i] . $lineNumber )->applyFromArray( array(
 397                                         'font' => array(
 398                                                         'bold' => true,
 399                                                         'size' => 8
 400                                         ),
 401                                         'fill' => array(
 402                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 403                                                         'color' => array(
 404                                                                         'rgb' => 'dddddd'
 405                                                         )
 406                                         )
 407                         ) );
 408
 409                         $this->_objPHPExcel->getActiveSheet()->setCellValue( $this->numberToColumn [$i] .($lineNumber + 1),($total ['Loaded'] ? number_format( $total ['Sold'] / $total ['Loaded'] * 100, 2 ) : 0) . '%' );
 410                         $this->_objPHPExcel->getActiveSheet()->getStyle( $this->numberToColumn [$i ++] .($lineNumber + 1) )->applyFromArray( array(
 411                                         'font' => array(
 412                                                         'bold' => true,
 413                                                         'size' => 8
 414                                         )
 415                         ) );
 416                 }
 417
 418                 return $lineNumber + 1;
 419         }
 420 }