2 namespace Report\Report;
7 * Price Guide Statistics Report.
10 class PriceGuideStatistical extends \Utility\Service\Report
18 protected $_title = 'PriceGuide Statistical Report';
22 protected $_subject = 'PriceGuide';
26 protected $_description = 'Statistical report for vehicles sold on price guide.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
36 protected $_headers = array(
48 protected $_fields = array(
60 protected $_totals = array(
70 protected $_currencyFields = array(
81 public function build()
83 ini_set('memory_limit','512M');
86 'num_valuations' => '',
89 'total_highestOffer' => '',
90 'percentage_toTrade' => 'Offers per unit:',
91 'total_numOffers' => ''
95 'num_valuations' => 0,
98 'total_highestOffer' => 0,
99 'percentage_toTrade' => 0,
100 'total_numOffers' => 0
103 'company_name' => 'Dealer',
104 'num_valuations' => 'No of Valuations',
105 'total_trade' => 'Trade Value',
106 'total_recon' => 'Recon',
107 'total_highestOffer' => 'Highest Offer',
108 'percentage_toTrade' => '% To Trade',
109 'total_numOffers' => 'No of Offers',
111 'excel.rowStyle' => 'Titles'
114 #-> Establish list of dealerships to work with.
116 'company', 'companyGroup', 'groupDivision'
118 $query = 'SELECT [SELECTION] '
119 . 'FROM \Company\Entity\Company company'
120 . ' LEFT JOIN company.group companyGroup '
121 . ' LEFT JOIN company.groupDivision groupDivision '
123 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC, company.id ASC';
124 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
126 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
128 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
130 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
132 if (isset($this->_input['group'])
133 && !empty($this->_input['group'])
134 && 'null' != $this->_input['group'])
136 $this->_queries['Group'] = $this->em
137 ->find('\Company\Entity\Group', $this->_input['group'])
139 $where[] = 'companyGroup.id = :group';
140 $params['group'] = $this->_input['group'];
142 if (isset($this->_input['groupDivision'])
143 && !empty($this->_input['groupDivision'])
144 && 'null' != $this->_input['groupDivision'])
146 error_log($this->_input['groupDivision']);
147 $this->_queries['Division'] = $this->em
148 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
150 $where[] = 'groupDivision.id = :division';
151 $params['division'] = $this->_input['groupDivision'];
153 if (isset($this->_input['company'])
154 && !empty($this->_input['company'])
155 && 'null' != $this->_input['company'])
157 $this->_queries['Dealership'] = $this->em
158 ->find('\Company\Entity\Company', $this->_input['company'])
160 $where[] = 'company.id = :company';
161 $params['company'] = $this->_input['company'];
164 $query = str_replace(array(
165 '[SELECTION]', '[WHERE]'
167 implode(', ', $selection) . ' ',
169 ? 'WHERE ' . implode(' AND ', $where) . ' '
173 $query = $this->em->createQuery($query);
175 && $query->setParameters($params);
176 $companies = $query->getScalarResult();
180 foreach ($companies as $company)
182 if ('' == $company['companyGroup_name'])
184 $company['companyGroup_name'] = 'None';
186 if ('' == $company['groupDivision_name'])
188 $company['groupDivision_name'] = 'None';
190 if ($div != $company['groupDivision_name'])
195 $totals['company_name'] = 'DIVISION TOTALS';
196 $totals['id'] = 'dt';
197 $totals['excel.rowStyle'] = 'Totals';
198 $this->_data[] = $totals;
200 $totals['company_name'] = $grp . ': ' . $div;
201 $totals['id'] = 'dn';
202 $totals['excel.rowStyle'] = 'Totals';
203 $this->_data[] = $totals;
204 $this->_data[] = array();
205 if ($grp == $company['companyGroup_name'])
209 $this->_data[] = $titles;
213 $div = $company['groupDivision_name'];
215 if ($grp != $company['companyGroup_name'])
219 #-> Not the first group, so we have grand totals to add
221 $totals['company_name'] = 'GROUP TOTALS';
222 $totals['id'] = 'gt';
223 $totals['excel.rowStyle'] = 'Totals';
224 $this->_data[] = $totals;
226 $totals['company_name'] = $grp;
227 $totals['id'] = 'gn';
228 $totals['excel.rowStyle'] = 'Totals';
229 $this->_data[] = $totals;
230 $this->_data[] = array();
231 $this->_data[] = array();
232 $this->_data[] = $titles;
234 $grp = $company['companyGroup_name'];
237 $entry['id'] = $company['company_id'];
238 $entry['company_name'] = $company['company_name'];
239 $this->_data[] = $entry;
242 $totals['company_name'] = 'DIVISION TOTALS';
243 $totals['id'] = 'dt';
244 $totals['excel.rowStyle'] = 'Totals';
245 $this->_data[] = $totals;
247 $totals['company_name'] = $grp . ': ' . $div;
248 $totals['id'] = 'dn';
249 $totals['excel.rowStyle'] = 'Totals';
250 $this->_data[] = $totals;
251 $this->_data[] = array();
253 $totals['company_name'] = 'GROUP TOTALS';
254 $totals['id'] = 'gt';
255 $totals['excel.rowStyle'] = 'Totals';
256 $this->_data[] = $totals;
258 $totals['company_name'] = $grp;
259 $totals['id'] = 'gn';
260 $totals['excel.rowStyle'] = 'Totals';
261 $this->_data[] = $totals;
266 'companyGroup.name', 'groupDivision.name', 'company.name', 'company.id',
267 'COUNT(priceGuide.id) AS num_valuations',
268 'SUM(stock.tradePrice) AS total_trade',
269 'SUM(stock.damageTotal) AS total_recon',
270 'SUM(stock.highestOffer) AS total_highestOffer',
271 'SUM(stock.numberOfOffers) AS total_numOffers'
277 $query = 'SELECT [SELECTION] '
278 . 'FROM \Stock\Entity\Stock stock'
279 . ' JOIN stock.priceGuide priceGuide '
280 . ' JOIN stock.company company '
281 . ' LEFT JOIN company.group companyGroup '
282 . ' LEFT JOIN company.groupDivision groupDivision '
284 . 'GROUP BY companyGroup.name, groupDivision.name, company.name '
285 . 'ORDER BY companyGroup.name ASC, groupDivision.name ASC, company.name ASC';
287 #-> Construct details.
288 $where[] = 'stock.tradePrice > 0.0';
289 $where[] = 'stock.numberOfOffers > 0';
290 $authData = \Utility\Registry::getAuthData();if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
292 $where[] = 'priceGuide.created >= :dateFrom';
293 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
295 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
297 $where[] = 'priceGuide.created <= :dateTo';
298 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
300 if (isset($this->_input['group'])
301 && !empty($this->_input['group'])
302 && 'null' != $this->_input['group'])
304 $where[] = 'companyGroup.id = :group';
305 $params['group'] = $this->_input['group'];
307 if (isset($this->_input['groupDivision'])
308 && !empty($this->_input['groupDivision'])
309 && 'null' != $this->_input['groupDivision'])
311 $where[] = 'groupDivision.id = :division';
312 $params['division'] = $this->_input['groupDivision'];
314 if (isset($this->_input['company'])
315 && !empty($this->_input['company'])
316 && 'null' != $this->_input['company'])
318 $where[] = 'company.id = :company';
319 $params['company'] = $this->_input['company'];
323 $query = str_replace(array(
324 '[SELECTION]', '[WHERE]'
326 implode(', ', $selection) . ' ',
328 ? 'WHERE ' . implode(' AND ', $where) . ' '
333 $query = $this->em->createQuery($query);
335 && $query->setParameters($params);
336 $stats = $query->getScalarResult();
340 $divTotals = $baseRow;
341 $grpTotal = $baseRow;
342 $stat = array_shift($stats);
343 foreach ($this->_data as $i => $entry)
349 switch ($entry['id'])
354 $this->_data[$i]['num_valuations'] = $divTotals['num_valuations'];
355 $this->_data[$i]['total_trade'] = $divTotals['total_trade'];
356 $this->_data[$i]['total_recon'] = $divTotals['total_recon'];
357 $this->_data[$i]['total_highestOffer'] = $divTotals['total_highestOffer'];
358 $this->_data[$i]['percentage_toTrade'] = $divTotals['total_trade'] > 0
359 ? round((100 / $divTotals['total_trade']) * $divTotals['total_highestOffer'], 1)
361 $this->_data[$i]['total_numOffers'] = $divTotals['total_numOffers'];
364 $this->_data[$i]['total_numOffers'] = $divTotals['num_valuations'] > 0
365 ? round($divTotals['total_numOffers'] / $divTotals['num_valuations'], 2)
367 $divTotals = $baseRow;
370 $this->_data[$i]['num_valuations'] = $grpTotal['num_valuations'];
371 $this->_data[$i]['total_trade'] = $grpTotal['total_trade'];
372 $this->_data[$i]['total_recon'] = $grpTotal['total_recon'];
373 $this->_data[$i]['total_highestOffer'] = $grpTotal['total_highestOffer'];
374 $this->_data[$i]['percentage_toTrade'] = $grpTotal['total_trade'] > 0
375 ? round((100 / $grpTotal['total_trade']) * $grpTotal['total_highestOffer'], 1)
377 $this->_data[$i]['total_numOffers'] = $grpTotal['total_numOffers'];
380 $this->_data[$i]['total_numOffers'] = $grpTotal['num_valuations'] > 0
381 ? round($grpTotal['total_numOffers'] / $grpTotal['num_valuations'], 2)
383 $grpTotal = $baseRow;
386 if ($stat['id'] == $entry['id'])
388 #-> Update the entry.
389 $this->_data[$i]['num_valuations'] = $stat['num_valuations'];
390 $this->_data[$i]['total_trade'] = $stat['total_trade'];
391 $this->_data[$i]['total_recon'] = $stat['total_recon'];
392 $this->_data[$i]['total_highestOffer'] = $stat['total_highestOffer'];
393 $this->_data[$i]['percentage_toTrade'] = $stat['total_trade'] > 0
394 ? round((100 / $stat['total_trade']) * $stat['total_highestOffer'], 1)
396 $this->_data[$i]['total_numOffers'] = $stat['total_numOffers'];
398 #-> Update the totals.
399 $divTotals['num_valuations'] += $stat['num_valuations'];
400 $divTotals['total_trade'] += $stat['total_trade'];
401 $divTotals['total_recon'] += $stat['total_recon'];
402 $divTotals['total_highestOffer'] += $stat['total_highestOffer'];
403 $divTotals['total_numOffers'] += $stat['total_numOffers'];
404 $grpTotal['num_valuations'] += $stat['num_valuations'];
405 $grpTotal['total_trade'] += $stat['total_trade'];
406 $grpTotal['total_recon'] += $stat['total_recon'];
407 $grpTotal['total_highestOffer'] += $stat['total_highestOffer'];
408 $grpTotal['total_numOffers'] += $stat['total_numOffers'];
410 #-> Company handled, move on to next one.
411 $stat = array_shift($stats);