2 namespace Report\Report;
7 * Valuation Statistics Report.
10 class ValuationStatistical extends \Utility\Service\Report
18 protected $_title = 'Valuation Statistical Report';
22 protected $_subject = 'Valuation';
26 protected $_description = 'Statistical report for vehicles valuations.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.co.za'
36 protected $_headers = array(
39 'No of Valuations sent to sales',
41 //'Total Trade Value',
43 //'Total Managers Offer',
48 'Send to Trade Centre',
49 '%Send to Trade Centre'
55 protected $_fields = array(
62 //'total_amountOffered',
64 'percentage_dealsDone',
66 'percentage_dealsNotDone',
67 'total_sendToTradeCenter',
68 'percentage_sendToTradeCenter',
73 protected $_totals = array(
74 /* 'total_valuations',
84 protected $_currencyFields = array(
95 public function build()
97 ini_set('memory_limit','512M');
100 'total_valuations' => '',
101 'num_valuations' => '',
102 'percentage_toTrade' => '% Deals Done:',
104 //'total_recon' => '',
105 //'total_amountOffered' => '',
106 'total_dealsDone' => '',
107 'percentage_dealsDone' => '',
108 'total_dealsNotDone' => '',
109 'percentage_dealsNotDone' => '',
110 'total_sendToTradeCenter' => '',
111 'percentage_sendToTradeCentere' => '',
112 'total_tradeP' => 0.0,
113 'total_amountOfferedP' => 0.0
116 'company_name' => '',
117 'total_valuations' => '',
118 'num_valuations' => 0,
119 'percentage_toTrade' => 0,
121 //'total_recon' => 0,
122 //'total_amountOffered' => 0,
123 'total_dealsDone' => 0,
124 'percentage_dealsDone' => 0.0,
125 'total_dealsNotDone' => 0,
126 'percentage_dealsNotDone' => 0.0,
127 'total_sendToTradeCenter' => 0,
128 'percentage_sendToTradeCenter' => 0.0,
129 'total_tradeP' => 0.0,
130 'total_amountOfferedP' => 0.0
133 'company_name' => 'Dealer',
134 'total_valuations' => 'Total Valuations',
135 'num_valuations' => 'No of Valuations Send to Sales',
136 'percentage_toTrade' => '% To Trade',
137 'total_trade' => 'Total Trade Value',
138 //'total_recon' => 'Total Recon',
139 //'total_amountOffered' => 'Total Managers Offer',
140 'total_dealsDone' => 'Deals Done',
141 'percentage_dealsDone' => '% Deals Done',
142 'total_dealsNotDone' => 'Deals Not Done',
143 'percentage_dealsNotDone' => '% Deals Not Done',
144 'total_sendToTradeCenter' => 'Send to Trade Centre',
145 'percentage_sendToTradeCenter' => '% Send to Trade Centre',
147 'excel.rowStyle' => 'Titles'
150 #-> Establish list of dealerships to work with.
152 'company', 'companyGroup', 'groupDivision'
154 $query = 'SELECT [SELECTION] '
155 . 'FROM \Company\Entity\Company company'
156 . ' LEFT JOIN company.group companyGroup '
157 . ' LEFT JOIN company.groupDivision groupDivision '
159 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
160 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
162 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
164 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
166 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
168 if (isset($this->_input['group'])
169 && !empty($this->_input['group'])
170 && 'null' != $this->_input['group'])
172 $this->_queries['Group'] = $this->em
173 ->find('\Company\Entity\Group', $this->_input['group'])
175 $where[] = 'companyGroup.id = :group';
176 $params['group'] = $this->_input['group'];
178 if (isset($this->_input['groupDivision'])
179 && !empty($this->_input['groupDivision'])
180 && 'null' != $this->_input['groupDivision'])
182 error_log($this->_input['groupDivision']);
183 $this->_queries['Division'] = $this->em
184 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
186 $where[] = 'groupDivision.id = :division';
187 $params['division'] = $this->_input['groupDivision'];
189 if (isset($this->_input['company'])
190 && !empty($this->_input['company'])
191 && 'null' != $this->_input['company'])
193 $this->_queries['Dealership'] = $this->em
194 ->find('\Company\Entity\Company', $this->_input['company'])
196 $where[] = 'company.id = :company';
197 $params['company'] = $this->_input['company'];
200 $query = str_replace(array(
201 '[SELECTION]', '[WHERE]'
203 implode(', ', $selection) . ' ',
205 ? 'WHERE ' . implode(' AND ', $where) . ' '
209 $query = $this->em->createQuery($query);
211 && $query->setParameters($params);
213 $companies = $query->getScalarResult();
217 foreach ($companies as $company)
219 if ('' == $company['companyGroup_name'])
221 $company['companyGroup_name'] = 'None';
223 if ('' == $company['groupDivision_name'])
225 $company['groupDivision_name'] = 'None';
227 if ($div != $company['groupDivision_name'])
232 $totals['company_name'] = 'DIVISION TOTALS';
233 $totals['id'] = 'dt';
234 $totals['excel.rowStyle'] = 'Totals';
235 $this->_data[] = $totals;
237 $totals['company_name'] = $grp . ': ' . $div;
238 $totals['id'] = 'dn';
239 $totals['excel.rowStyle'] = 'Totals';
240 $this->_data[] = $totals;
241 $this->_data[] = array();
242 if ($grp == $company['companyGroup_name'])
246 $this->_data[] = $titles;
250 $div = $company['groupDivision_name'];
252 if ($grp != $company['companyGroup_name'])
256 #-> Not the first group, so we have grand totals to add
258 $totals['company_name'] = 'GROUP TOTALS';
259 $totals['id'] = 'gt';
260 $totals['excel.rowStyle'] = 'Totals';
261 $this->_data[] = $totals;
263 $totals['company_name'] = $grp;
264 $totals['id'] = 'gn';
265 $totals['excel.rowStyle'] = 'Totals';
266 $this->_data[] = $totals;
267 $this->_data[] = array();
268 $this->_data[] = array();
269 $this->_data[] = $titles;
271 $grp = $company['companyGroup_name'];
274 $entry['id'] = $company['company_id'];
275 $entry['company_name'] = $company['company_name'];
276 $this->_data[] = $entry;
279 $totals['company_name'] = 'DIVISION TOTALS';
280 $totals['id'] = 'dt';
281 $totals['excel.rowStyle'] = 'Totals';
282 $this->_data[] = $totals;
284 $totals['company_name'] = $grp . ': ' . $div;
285 $totals['id'] = 'dn';
286 $totals['excel.rowStyle'] = 'Totals';
287 $this->_data[] = $totals;
288 $this->_data[] = array();
290 $totals['company_name'] = 'GROUP TOTALS';
291 $totals['id'] = 'gt';
292 $totals['excel.rowStyle'] = 'Totals';
293 $this->_data[] = $totals;
295 $totals['company_name'] = $grp;
296 $totals['id'] = 'gn';
297 $totals['excel.rowStyle'] = 'Totals';
298 $this->_data[] = $totals;
303 'companyGroup.name', 'groupDivision.name', 'company.name', 'company.id',
304 'COUNT(valuation.id) AS total_valuations',
305 'SUM(CASE valuation.amountOffered WHEN 0.0 THEN 0 ELSE 1 END) AS num_valuations',
306 'SUM(stock.tradePrice) AS total_trade',
307 'SUM(stock.damageTotal) AS total_recon',
308 'SUM(valuation.amountOffered) AS total_amountOffered',
309 'SUM(CASE WHEN stock.tradePrice > 0.0 AND valuation.amountOffered > 0.0 THEN stock.tradePrice ELSE 0.0 END) AS total_tradeP',
310 'SUM(CASE WHEN stock.tradePrice > 0.0 AND valuation.amountOffered > 0.0 THEN valuation.amountOffered ELSE 0.0 END) AS total_amountOfferedP'
316 $query = 'SELECT [SELECTION] '
317 . 'FROM \Valuation\Entity\Valuation valuation '
318 . ' JOIN valuation.stock stock '
319 . ' JOIN stock.company company '
320 . ' LEFT JOIN company.group companyGroup '
321 . ' LEFT JOIN company.groupDivision groupDivision '
323 . 'GROUP BY companyGroup.name, groupDivision.name, company.name, company.id '
324 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
326 #-> Construct details.
327 // $where[] = 'stock.tradePrice > 0.0';
328 $authData = \Utility\Registry::getAuthData();
329 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
331 $where[] = 'valuation.created >= :dateFrom';
332 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
334 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
336 $where[] = 'valuation.created <= :dateTo';
337 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
339 if (isset($this->_input['group'])
340 && !empty($this->_input['group'])
341 && 'null' != $this->_input['group'])
343 $where[] = 'companyGroup.id = :group';
344 $params['group'] = $this->_input['group'];
346 if (isset($this->_input['groupDivision'])
347 && !empty($this->_input['groupDivision'])
348 && 'null' != $this->_input['groupDivision'])
350 $where[] = 'groupDivision.id = :division';
351 $params['division'] = $this->_input['groupDivision'];
353 if (isset($this->_input['company'])
354 && !empty($this->_input['company'])
355 && 'null' != $this->_input['company'])
357 $where[] = 'company.id = :company';
358 $params['company'] = $this->_input['company'];
362 $query = str_replace(array(
363 '[SELECTION]', '[WHERE]'
365 implode(', ', $selection) . ' ',
367 ? 'WHERE ' . implode(' AND ', $where) . ' '
372 $query = $this->em->createQuery($query);
374 && $query->setParameters($params);
375 $stats = $query->getScalarResult();
379 $divTotals = $baseRow;
380 $grpTotal = $baseRow;
382 $stat = array_shift($stats);
383 foreach ($this->_data as $i => $entry)
389 switch ($entry['id'])
393 case 'dt': // Division Totals
394 $this->_data[$i]['total_valuations'] = $divTotals['total_valuations'];
395 $this->_data[$i]['num_valuations'] = $divTotals['num_valuations'];
396 $this->_data[$i]['total_dealsDone'] = 0;
397 $this->_data[$i]['total_dealsNotDone'] = 0;
398 $this->_data[$i]['total_sendToTradeCenter'] = 0;
399 $this->_data[$i]['percentage_toTrade'] = '';
400 $this->_data[$i]['percentage_dealsDone'] = '';
401 $this->_data[$i]['percentage_dealsNotDone'] = '';
402 $this->_data[$i]['percentage_sendToTradeCenter'] = '';
406 case 'dn':// Division Ave
407 $this->_data[$i]['percentage_toTrade'] = $divTotals['total_tradeP'] > 0
408 ? round((100 / $divTotals['total_tradeP']) * $divTotals['total_amountOfferedP'], 1)
410 $this->_data[$i]['percentage_dealsDone'] = 0;
411 $this->_data[$i]['percentage_dealsNotDone'] = 0;
412 $this->_data[$i]['percentage_sendToTradeCenter'] = 0;
414 $divTotals = $baseRow;
416 case 'gt'://Group Totals
417 $this->_data[$i]['total_valuations'] = $grpTotal['total_valuations'];
418 $this->_data[$i]['num_valuations'] = $grpTotal['num_valuations'];
419 $this->_data[$i]['total_dealsDone'] = 0;
420 $this->_data[$i]['total_dealsNotDone'] = 0;
421 $this->_data[$i]['total_sendToTradeCenter'] = 0;
422 $this->_data[$i]['percentage_toTrade'] = '';
423 $this->_data[$i]['percentage_dealsDone'] = '';
424 $this->_data[$i]['percentage_dealsNotDone'] = '';
425 $this->_data[$i]['percentage_sendToTradeCenter'] = '';
428 case 'gn':// Group Ave
429 $this->_data[$i]['percentage_toTrade'] = $grpTotal['total_tradeP'] > 0
430 ? round((100 / $grpTotal['total_tradeP']) * $grpTotal['total_amountOfferedP'], 1)
432 $this->_data[$i]['percentage_dealsDone'] = 0;
433 $this->_data[$i]['percentage_dealsNotDone'] = 0;
434 $this->_data[$i]['percentage_sendToTradeCenter'] = 0;
436 $grpTotal = $baseRow;
439 if ($stat['id'] == $entry['id'])
441 #-> Update the entry.
443 $this->_data[$i]['total_valuations'] = $stat['total_valuations'];
444 $this->_data[$i]['num_valuations'] = $stat['num_valuations'];
445 $this->_data[$i]['total_trade'] = $stat['total_trade'];
446 $this->_data[$i]['total_trade'] = $stat['total_trade'];
447 $this->_data[$i]['total_recon'] = $stat['total_recon'];
448 $this->_data[$i]['total_amountOffered'] = $stat['total_amountOffered'];
449 $this->_data[$i]['percentage_toTrade'] = $stat['total_tradeP'] > 0
450 && $stat['total_amountOfferedP'] > 0
451 ? round((100 / $stat['total_tradeP']) * $stat['total_amountOfferedP'], 1)
454 $this->_data[$i]['total_dealsDone'] = 0;
455 $this->_data[$i]['percentage_dealsDone'] = 0;
456 $this->_data[$i]['total_dealsNotDone'] = 0;
457 $this->_data[$i]['percentage_dealsNotDone'] = 0;
458 $this->_data[$i]['total_sendToTradeCenter'] = 0;
459 $this->_data[$i]['percentage_sendToTradeCenter'] = 0;
461 #-> Update the totals.
462 $divTotals['total_tradeP'] += $stat['total_tradeP'];
463 $divTotals['total_amountOfferedP'] += $stat['total_amountOfferedP'];
464 $divTotals['total_valuations'] += $stat['total_valuations'];
465 $divTotals['num_valuations'] += $stat['num_valuations'];
466 $divTotals['total_trade'] += $stat['total_trade'];
467 $divTotals['total_dealsDone'] += 0;
468 $divTotals['total_dealsNotDone'] += 0;
469 $divTotals['total_sendToTradeCenter'] += 0;
471 $grpTotal['total_amountOfferedP'] += $stat['total_amountOfferedP'];
472 $grpTotal['total_tradeP'] += $stat['total_tradeP'];
473 $grpTotal['num_valuations'] += $stat['num_valuations'];
474 $grpTotal['total_trade'] += $stat['total_trade'];
475 $grpTotal['total_dealsDone'] += 0;
476 $grpTotal['total_dealsNotDone'] += 0;
477 $grpTotal['total_sendToTradeCenter'] += 0;
479 #-> Company handled, move on to next one.
480 $stat = array_shift($stats);
486 #-> Collect more stats.
488 'companyGroup.name', 'groupDivision.name', 'company.name', 'company.id', 'valuation.jobState as job_state','stock.tradePrice as trade_price',
489 'SUM(stat.dealdone) AS total_dealsDone',
490 'SUM(stat.dealnotdone) AS total_dealsNotDone',
491 'SUM(stat.tradecentre) AS total_sendToTradeCenter'
497 $query = 'SELECT [SELECTION] '
498 . 'FROM \Statistical\Entity\Statistical stat '
499 . ' JOIN stat.stock stock '
500 . ' LEFT JOIN stock.valuation valuation '
501 . ' LEFT JOIN stock.company company '
502 . ' LEFT JOIN company.group companyGroup '
503 . ' LEFT JOIN company.groupDivision groupDivision '
505 . 'GROUP BY companyGroup.name, groupDivision.name, company.name, company.id '
506 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
508 #-> Construct details.
509 // $where[] = 'stock.tradePrice > 0.0';
510 $authData = \Utility\Registry::getAuthData();
511 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
513 $where[] = 'stat.created >= :dateFrom';
514 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
516 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
518 $where[] = 'stat.created <= :dateTo';
519 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
521 if (isset($this->_input['group'])
522 && !empty($this->_input['group'])
523 && 'null' != $this->_input['group'])
525 $where[] = 'companyGroup.id = :group';
526 $params['group'] = $this->_input['group'];
528 if (isset($this->_input['groupDivision'])
529 && !empty($this->_input['groupDivision'])
530 && 'null' != $this->_input['groupDivision'])
532 $where[] = 'groupDivision.id = :division';
533 $params['division'] = $this->_input['groupDivision'];
535 if (isset($this->_input['company'])
536 && !empty($this->_input['company'])
537 && 'null' != $this->_input['company'])
539 $where[] = 'company.id = :company';
540 $params['company'] = $this->_input['company'];
544 $query = str_replace(array(
545 '[SELECTION]', '[WHERE]'
547 implode(', ', $selection) . ' ',
549 ? 'WHERE ' . implode(' AND ', $where) . ' '
554 $query = $this->em->createQuery($query);
556 && $query->setParameters($params);
558 // \Utility\Debug::errorLog('$query->getSQL()', $query->getSQL());
559 // \Utility\Debug::errorLog('$query->getParameters()', $query->getParameters());
561 $stats = $query->getScalarResult();
565 $divTotals = $baseRow;
566 $grpTotal = $baseRow;
569 foreach ($this->_data as $i => $entry)
575 switch ($entry['id'])
579 case 'dt': // Division Totals
580 $this->_data[$i]['total_dealsDone'] = $divTotals['total_dealsDone'];
581 $this->_data[$i]['total_dealsNotDone'] = $divTotals['total_dealsNotDone'];
582 $this->_data[$i]['total_sendToTradeCenter'] = $divTotals['total_sendToTradeCenter'];
583 $this->_data[$i]['percentage_dealsDone'] = '';
584 $this->_data[$i]['percentage_dealsNotDone'] = '';
585 $this->_data[$i]['percentage_sendToTradeCenter'] = '';
589 case 'dn': // Division Ave
590 $this->_data[$i]['percentage_dealsDone'] = $divTotals['total_dealsDone'] > 0
591 && $grpTotal['num_valuations'] > 0
592 ? round(($divTotals['total_dealsDone'] / $divTotals['num_valuations']) * 100, 2)
594 $this->_data[$i]['percentage_dealsNotDone'] = $divTotals['total_dealsNotDone'] > 0
595 && $grpTotal['num_valuations'] > 0
596 ? round(($divTotals['total_dealsNotDone'] / $divTotals['num_valuations']) * 100, 2)
598 $this->_data[$i]['percentage_sendToTradeCenter'] = $divTotals['total_sendToTradeCenter'] > 0
599 && $grpTotal['num_valuations'] > 0
600 ? round(($divTotals['total_sendToTradeCenter'] / $divTotals['num_valuations']) * 100, 2)
602 $divTotals = $baseRow;
604 case 'gt': //Group Totals
605 $this->_data[$i]['total_dealsDone'] = $grpTotal['total_dealsDone'];
606 $this->_data[$i]['total_dealsNotDone'] = $grpTotal['total_dealsNotDone'];
607 $this->_data[$i]['total_sendToTradeCenter'] = $grpTotal['total_sendToTradeCenter'];
608 $this->_data[$i]['percentage_dealsDone'] = '';
609 $this->_data[$i]['percentage_dealsNotDone'] = '';
610 $this->_data[$i]['percentage_sendToTradeCenter'] = '';
613 case 'gn': // Group Ave
614 $this->_data[$i]['percentage_dealsDone'] = $grpTotal['total_dealsDone'] > 0
615 && $grpTotal['num_valuations'] > 0
616 ? round(($grpTotal['total_dealsDone'] / $grpTotal['num_valuations']) * 100, 2)
618 $this->_data[$i]['percentage_dealsNotDone'] = $grpTotal['total_dealsNotDone'] > 0
619 && $grpTotal['num_valuations'] > 0
620 ? round(($grpTotal['total_dealsNotDone'] / $grpTotal['num_valuations']) * 100, 2)
622 $this->_data[$i]['percentage_sendToTradeCenter'] = $grpTotal['total_sendToTradeCenter'] > 0
623 && $grpTotal['num_valuations'] > 0
624 ? round(($grpTotal['total_sendToTradeCenter'] / $grpTotal['num_valuations']) * 100, 2)
626 $grpTotal = $baseRow;
630 $statArrayId = $this->searcharray($entry['id'], 'id', $stat);
632 if ($stat[$statArrayId]['id'] == $entry['id'])
634 if('Complete Valuation' != $stat[$statArrayId]['job_state'] && 0 < $stat[$statArrayId]['trade_price']) {
636 #-> Update the entry.
637 $this->_data[$i]['total_dealsDone'] = $stat[$statArrayId]['total_dealsDone'];
638 $this->_data[$i]['percentage_dealsDone'] = $stat[$statArrayId]['total_dealsDone'] > 0
639 && $this->_data[$i]['num_valuations'] > 0
640 ? round(($stat[$statArrayId]['total_dealsDone'] / $this->_data[$i]['num_valuations']) * 100, 1)
642 $this->_data[$i]['total_dealsNotDone'] = $stat[$statArrayId]['total_dealsNotDone'];
643 $this->_data[$i]['percentage_dealsNotDone'] = $stat[$statArrayId]['total_dealsNotDone'] > 0
644 && $this->_data[$i]['num_valuations'] > 0
645 ? round(($stat[$statArrayId]['total_dealsNotDone'] / $this->_data[$i]['num_valuations']) * 100, 1)
647 $this->_data[$i]['total_sendToTradeCenter'] = $stat[$statArrayId]['total_sendToTradeCenter'];
648 $this->_data[$i]['percentage_sendToTradeCenter'] = $stat[$statArrayId]['total_sendToTradeCenter'] > 0
649 && $this->_data[$i]['num_valuations'] > 0
650 ? round(($stat[$statArrayId]['total_sendToTradeCenter'] / $this->_data[$i]['num_valuations']) * 100, 1)
653 #-> Update the totals.
654 $divTotals['total_dealsDone'] += $stat[$statArrayId]['total_dealsDone'];
655 $divTotals['total_dealsNotDone'] += $stat[$statArrayId]['total_dealsNotDone'];
656 $divTotals['total_sendToTradeCenter'] += $stat[$statArrayId]['total_sendToTradeCenter'];
658 $grpTotal['total_dealsDone'] += $stat[$statArrayId]['total_dealsDone'];
659 $grpTotal['total_dealsNotDone'] += $stat[$statArrayId]['total_dealsNotDone'];
660 $grpTotal['total_sendToTradeCenter'] += $stat[$statArrayId]['total_sendToTradeCenter'];
662 #-> Company handled, move on to next one.
663 // $stat = array_shift($stats);
667 #-> Update the totals.
668 $divTotals['num_valuations'] += $this->_data[$i]['num_valuations'];
669 $grpTotal['num_valuations'] += $this->_data[$i]['num_valuations'];
679 public function searcharray($value, $key, $array) {
680 foreach ($array as $k => $val) {
681 if ($val[$key] == $value) {