2 namespace Report\Report;
7 * Stock Control Report.
10 class PriceGuide extends \Utility\Service\Report
18 protected $_title = 'PriceGuide Report';
22 protected $_subject = 'PriceGuide';
26 protected $_description = 'Historical report for vehicles sold on price guide.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.co.za'
36 protected $_headers = array(
41 'Seller User Surname',
62 'Automate Duplicates',
63 'Matches to multiple stock entries',
64 'Date invoiced on Automate',
74 protected $_fields = array(
76 'sellerDivision_name',
78 'sellerProfile_firstName',
79 'sellerProfile_familyName',
83 'buyerProfile_firstName',
84 'buyerProfile_familyName',
92 'stock_registrationNumber',
103 'adherence_dateSold',
106 'adherence_dealerCode',
113 protected $_totals = array(
114 'stock_highestOffer',
121 protected $_currencyFields = array(
122 'stock_highestOffer',
132 public function build()
134 ini_set('memory_limit','512M');
136 #-> Prepare parameters.
138 'stock', 'priceGuide', 'offer', 'type', 'model', 'make', 'year',
139 'sellerCompany', 'sellerGroup', 'sellerDivision', 'sellerProfile',
140 'buyerCompany', 'buyerGroup', 'buyerDivision', 'buyerProfile',
147 $query = 'SELECT [SELECTION] '
148 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide'
149 . ' JOIN priceGuide.stock stock '
150 . ' JOIN priceGuide.offers offer WITH offer.amount=stock.highestOffer '
151 . ' JOIN stock.adherence link '
152 . ' JOIN link.adherence adherence '
153 . ' JOIN stock.type type '
154 . ' JOIN stock.vehicleYear year '
155 . ' JOIN type.model model '
156 . ' JOIN model.make make '
157 . ' JOIN priceGuide.createdBy sellerProfile '
158 . ' JOIN sellerProfile.company sellerCompany '
159 . ' JOIN offer.profile buyerProfile '
160 . ' JOIN offer.company buyerCompany '
161 . ' LEFT JOIN sellerCompany.group sellerGroup '
162 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
163 . ' LEFT JOIN buyerProfile.group buyerGroup '
164 . ' LEFT JOIN buyerProfile.groupDivision buyerDivision '
166 . 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';
168 #-> Construct details.
169 $authData = \Utility\Registry::getAuthData();
170 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
172 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
174 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
175 $where[] = 'adherence.dateSold >= :dateFrom';
176 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
178 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
180 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
182 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
183 $where[] = 'adherence.dateSold < :dateTo';
184 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
186 $where[] = 'priceGuide.created >= :datePgFrom';
187 $where[] = 'priceGuide.created <= :dateEnd';
188 $params['dateEnd'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -3 days');
189 $params['datePgFrom'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -4 months');
191 if (isset($this->_input['group'])
192 && !empty($this->_input['group'])
193 && 'null' != $this->_input['group'])
195 $this->_queries['Group'] = $this->em
196 ->find('\Company\Entity\Group', $this->_input['group'])
198 $where[] = 'sellerGroup.id = :group';
199 $params['group'] = $this->_input['group'];
201 if (isset($this->_input['groupDivision'])
202 && !empty($this->_input['groupDivision'])
203 && 'null' != $this->_input['groupDivision'])
205 error_log($this->_input['groupDivision']);
206 $this->_queries['Division'] = $this->em
207 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
209 $where[] = 'sellerDivision.id = :division';
210 $params['division'] = $this->_input['groupDivision'];
212 if (isset($this->_input['company'])
213 && !empty($this->_input['company'])
214 && 'null' != $this->_input['company'])
216 $this->_queries['Dealership'] = $this->em
217 ->find('\Company\Entity\Company', $this->_input['company'])
219 $where[] = 'sellerCompany.id = :company';
220 $params['company'] = $this->_input['company'];
224 $query = str_replace(array(
225 '[SELECTION]', '[WHERE]'
227 implode(', ', $selection) . ' ',
229 ? 'WHERE ' . implode(' AND ', $where) . ' '
234 $query = $this->em->createQuery($query);
236 && $query->setParameters($params);
237 $this->_data = $query->getScalarResult();
241 $increments = $this->em
242 ->getRepository('\Auction\Entity\Increment')
243 ->findBy(array(), array('to' => 'ASC'));
246 foreach ($this->_data as $id => $row)
248 if (!isset($matched[$row['adherence_id']]))
250 $matched[$row['adherence_id']] = array();
252 $matched[$row['adherence_id']][$row['stock_id']] = true;
254 foreach ($this->_data as $id => $row)
257 if ($prevId != $row['adherence_id'])
259 $prevId = $row['adherence_id'];
260 $isDuplicate = false;
262 $multimatch = 1 < count($matched[$row['adherence_id']])
265 $endDate = new \DateTime($row['priceGuide_created']->format(\Utility\Definitions\Locale::getDateTimeFormat()));
266 $endDate->add(new \DateInterval('P3D'));
267 $this->_data[$id]['priceGuide_endDate'] = $endDate;
268 $this->_data[$id]['commission'] = !$isDuplicate
271 $this->_data[$id]['isMatched'] = !is_null($row['adherence_id'])
274 $this->_data[$id]['multiMatch'] = $multimatch
277 $this->_data[$id]['isDuplicate'] = $isDuplicate
285 #-> No match section.
286 #-> Collect some data.
288 'adherence', 'sellerCompany', 'sellerGroup', 'sellerDivision'
295 $query = 'SELECT [SELECTION] '
296 . 'FROM \Adherence\Entity\Adherence adherence'
297 . ' LEFT JOIN adherence.stock stock '
298 . ' LEFT JOIN adherence.company sellerCompany '
299 . ' LEFT JOIN sellerCompany.group sellerGroup '
300 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
302 . 'ORDER BY adherence.id ASC';
303 $where[] = 'stock.id IS NULL';
305 #-> Construct details.
306 $authData = \Utility\Registry::getAuthData();
307 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
309 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
311 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
312 $where[] = 'adherence.dateSold >= :dateFrom';
313 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
315 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
317 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
319 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
320 $where[] = 'adherence.dateSold < :dateTo';
321 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
323 if (isset($this->_input['group'])
324 && !empty($this->_input['group'])
325 && 'null' != $this->_input['group'])
327 $this->_queries['Group'] = $this->em
328 ->find('\Company\Entity\Group', $this->_input['group'])
330 $where[] = 'sellerGroup.id = :group';
331 $params['group'] = $this->_input['group'];
333 if (isset($this->_input['groupDivision'])
334 && !empty($this->_input['groupDivision'])
335 && 'null' != $this->_input['groupDivision'])
337 error_log($this->_input['groupDivision']);
338 $this->_queries['Division'] = $this->em
339 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
341 $where[] = 'sellerDivision.id = :division';
342 $params['division'] = $this->_input['groupDivision'];
344 if (isset($this->_input['company'])
345 && !empty($this->_input['company'])
346 && 'null' != $this->_input['company'])
348 $this->_queries['Dealership'] = $this->em
349 ->find('\Company\Entity\Company', $this->_input['company'])
351 $where[] = 'sellerCompany.id = :company';
352 $params['company'] = $this->_input['company'];
356 $query = str_replace(array(
357 '[SELECTION]', '[WHERE]'
359 implode(', ', $selection) . ' ',
361 ? 'WHERE ' . implode(' AND ', $where) . ' '
366 $query = $this->em->createQuery($query);
368 && $query->setParameters($params);
369 $data = $query->getScalarResult();
372 foreach ($data as $id => $row)
375 if ($prevId != $row['adherence_id'])
377 $prevId = $row['adherence_id'];
378 $isDuplicate = false;
380 $this->_data[] = array(
381 'sellerGroup_name' => $row['sellerGroup_name'],
382 'sellerDivision_name' => $row['sellerDivision_name'],
383 'sellerCompany_name' => $row['sellerCompany_name'],
384 'sellerProfile_firstName' => '',
385 'sellerProfile_familyName' => '',
386 'buyerGroup_name' => '',
387 'buyerDivision_name' => '',
388 'buyerCompany_name' => '',
389 'buyerProfile_firstName' => '',
390 'buyerProfile_familyName' => '',
391 'make_name' => $row['adherence_make'],
395 'stock_stockNumber' => $row['adherence_stockNumber'],
396 'stock_vinNumber' => $row['adherence_vinNumber'],
397 'stock_engineNumber' => $row['adherence_engineNumber'],
398 'stock_registrationNumber' => $row['adherence_registrationNumber'],
399 'stock_tradePrice' => 0,
400 'stock_retailPrice' => 0,
401 'stock_listPrice' => 0,
402 'priceGuide_created' => '',
403 'priceGuide_endDate' => '',
404 'stock_highestOffer' => '',
405 'commission' => 1000.00,
407 'isDuplicate' => $isDuplicate
410 'multiMatch' => 'No',
411 'adherence_dateSold' => $row['adherence_dateSold'],
412 'adherence_type' => $row['adherence_type'],
413 'adherence_file' => $row['adherence_file'],
414 'adherence_dealerCode' => $row['adherence_dealerCode'],
415 'adherence_amount' => $row['adherence_amount'],
416 'adherence_soldTo' => $row['adherence_soldTo']