2 namespace Report\Report;
7 * Stock Contyrol Report.
10 class Invoice extends \Utility\Service\Report
18 protected $_title = 'Invoice Report';
22 protected $_subject = 'Invoice';
26 protected $_description = 'Historical report for invoice data.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
36 protected $_headers = array(
41 'SMS Rand Value Total',
43 'Auction Invoice Total',
44 'Price Guide Offer Matched',
46 'Price Guide Matched Duplicates',
47 'Matched to multiple stock entries'
52 protected $_fields = array(
68 protected $_totals = array(
79 protected $_currencyFields = array(
90 public function build()
92 ini_set('memory_limit','512M');
94 #-> Prepare parameters.
95 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
97 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 1, 1, date('m' - 1), 1, date('Y')));
99 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
101 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
104 'log', 'fromCompany', 'fromGroup', 'fromDivision'
110 $query = 'SELECT [SELECTION] '
111 . 'FROM \Utility\Entity\NotificationLog log '
112 . ' LEFT JOIN log.fromCompany fromCompany '
113 . ' LEFT JOIN fromCompany.group fromGroup '
114 . ' LEFT JOIN fromCompany.groupDivision fromDivision '
116 . 'ORDER BY fromGroup.name, fromDivision.name, fromCompany.name';
118 #-> Construct details.
119 $clickVal = isset($this->_input['clickVal']) && is_numeric($this->_input['clickVal'])
120 ? $this->_input['clickVal']
122 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
124 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
125 $where[] = 'log.created >= :dateFrom';
126 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
128 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
130 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
131 $where[] = 'log.created <= :dateTo';
132 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
134 if (isset($this->_input['group'])
135 && !empty($this->_input['group'])
136 && 'null' != $this->_input['group'])
138 $this->_queries['Group'] = $this->em
139 ->find('\Company\Entity\Group', $this->_input['group'])
141 $where[] = 'fromGroup.id = :group';
142 $params['group'] = $this->_input['group'];
144 if (isset($this->_input['groupDivision'])
145 && !empty($this->_input['groupDivision'])
146 && 'null' != $this->_input['groupDivision'])
148 $this->_queries['Division'] = $this->em
149 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
151 $where[] = 'fromDivision.id = :division';
152 $params['division'] = $this->_input['groupDivision'];
154 if (isset($this->_input['company'])
155 && !empty($this->_input['company'])
156 && 'null' != $this->_input['company'])
158 $this->_queries['Dealership'] = $this->em
159 ->find('\Company\Entity\Company', $this->_input['company'])
161 $where[] = 'fromCompany.id = :company';
162 $params['company'] = $this->_input['company'];
164 $where[] = 'log.smsTo IS NOT NULL AND log.smsBody != \'\'';
167 $query = str_replace(array(
168 '[SELECTION]', '[WHERE]'
170 implode(', ', $selection) . ' ',
172 ? 'WHERE ' . implode(' AND ', $where) . ' '
177 $query = $this->em->createQuery($query);
179 && $query->setParameters($params);
180 $data = $query->getScalarResult();
182 $this->_data = array();
185 foreach ($data as $id => $row)
187 if ($company !== $row['fromCompany_name'])
189 if (false != $company)
191 $this->_data[$company] = $entry;
193 $company = $row['fromCompany_name'];
195 $entry['clickCount'] = 0;
197 $entry['auctionItems'] = 0;
198 $entry['auctionTotal'] = 0;
199 $entry['pgTotal'] = 0;
200 $entry['pgCommission'] = 0.0;
201 $entry['pgDuplicates'] = '';
202 $entry['pgMultiMatch'] = '';
204 $entry['clickCount'] += strlen($row['log_smsBody']) > 160
205 ? ceil(strlen($row['log_smsBody']) / (160 - 7))
207 $entry['total'] = round($entry['clickCount'] * $clickVal, 2);
211 $this->_data[$company] = $entry;
217 'auction', 'company', 'companyGroup', 'companyDivision',
218 'COUNT(auction) as auctionItems, SUM(auction.bidIncrement) as auctionTotal'
224 $query = 'SELECT [SELECTION] '
225 . 'FROM \Auction\Entity\Auction auction '
226 . ' JOIN auction.stock stock '
227 . ' JOIN stock.company company '
228 . ' LEFT JOIN company.group companyGroup '
229 . ' LEFT JOIN company.groupDivision companyDivision '
231 . 'GROUP BY companyGroup.name, companyDivision.name, company.name';
232 $where[] = 'auction.jobState=\'Sold\'';
234 #-> Construct details.
235 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
237 $where[] = 'auction.endDate >= :dateFrom';
238 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
240 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
242 $where[] = 'auction.endDate <= :dateTo';
243 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
245 if (isset($this->_input['group'])
246 && !empty($this->_input['group'])
247 && 'null' != $this->_input['group'])
249 $where[] = 'companyGroup.id = :group';
250 $params['group'] = $this->_input['group'];
252 if (isset($this->_input['groupDivision'])
253 && !empty($this->_input['groupDivision'])
254 && 'null' != $this->_input['groupDivision'])
256 $where[] = 'companyDivision.id = :division';
257 $params['division'] = $this->_input['groupDivision'];
259 if (isset($this->_input['company'])
260 && !empty($this->_input['company'])
261 && 'null' != $this->_input['company'])
263 $where[] = 'company.id = :company';
264 $params['company'] = $this->_input['company'];
268 $query = str_replace(array(
269 '[SELECTION]', '[WHERE]'
271 implode(', ', $selection) . ' ',
273 ? 'WHERE ' . implode(' AND ', $where) . ' '
278 $query = $this->em->createQuery($query);
280 && $query->setParameters($params);
281 $data = $query->getScalarResult();
283 foreach ($data as $id => $row)
285 if (isset($this->_data[$row['company_name']]))
287 $this->_data[$row['company_name']]['auctionItems'] += $row['auctionItems'];
288 $this->_data[$row['company_name']]['auctionTotal'] += $row['auctionTotal'];
292 $this->_data[$row['company_name']] = array(
293 'fromGroup_name' => $row['companyGroup_name'],
294 'fromDivision_name' => $row['companyDivision_name'],
295 'fromCompany_name' => $row['company_name'],
298 'auctionItems' => $row['auctionItems'],
299 'auctionTotal' => $row['auctionTotal'],
301 'pgCommission' => 0.0,
302 'pgDuplicates' => '',
311 'stock', 'priceGuide', 'offer',
312 'sellerCompany', 'sellerGroup', 'sellerDivision',
319 $query = 'SELECT [SELECTION] '
320 . 'FROM \PriceGuide\Entity\PriceGuide priceGuide'
321 . ' JOIN priceGuide.stock stock '
322 . ' JOIN priceGuide.offers offer WITH offer.amount=stock.highestOffer '
323 . ' JOIN stock.adherence link '
324 . ' JOIN link.adherence adherence '
325 . ' JOIN priceGuide.createdBy sellerProfile '
326 . ' JOIN sellerProfile.company sellerCompany '
327 . ' LEFT JOIN sellerCompany.group sellerGroup '
328 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
330 . 'ORDER BY adherence.id ASC, priceGuide.id ASC, offer.id DESC';
332 #-> Construct details.
333 $where[] = 'adherence.dateSold >= :dateFrom';
334 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
335 $where[] = 'adherence.dateSold < :dateTo';
336 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 00:00:01');
337 $where[] = 'priceGuide.created >= :datePgFrom';
338 $where[] = 'priceGuide.created <= :dateTo';
339 $params['datePgFrom'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59 -4 months');
340 if (isset($this->_input['group'])
341 && !empty($this->_input['group'])
342 && 'null' != $this->_input['group'])
344 $where[] = 'sellerGroup.id = :group';
345 $params['group'] = $this->_input['group'];
347 if (isset($this->_input['groupDivision'])
348 && !empty($this->_input['groupDivision'])
349 && 'null' != $this->_input['groupDivision'])
351 $where[] = 'sellerDivision.id = :division';
352 $params['division'] = $this->_input['groupDivision'];
354 if (isset($this->_input['company'])
355 && !empty($this->_input['company'])
356 && 'null' != $this->_input['company'])
358 $where[] = 'sellerCompany.id = :company';
359 $params['company'] = $this->_input['company'];
363 $query = str_replace(array(
364 '[SELECTION]', '[WHERE]'
366 implode(', ', $selection) . ' ',
368 ? 'WHERE ' . implode(' AND ', $where) . ' '
373 $query = $this->em->createQuery($query);
375 && $query->setParameters($params);
376 $data = $query->getScalarResult();
382 foreach ($data as $id => $row)
384 if (!isset($matched[$row['adherence_id']]))
386 $matched[$row['adherence_id']] = array();
388 $matched[$row['adherence_id']][$row['stock_id']] = true;
390 foreach ($data as $id => $row)
393 if ($prevId != $row['adherence_id'])
395 $prevId = $row['adherence_id'];
396 $isDuplicate = false;
398 $multimatch = 1 < count($matched[$row['adherence_id']])
402 if (isset($this->_data[$row['sellerCompany_name']]))
404 $this->_data[$row['sellerCompany_name']]['pgTotal'] = 1;
405 $this->_data[$row['sellerCompany_name']]['pgCommission'] += ($isDuplicate ? 0.0 : 1000.0);
406 $this->_data[$row['sellerCompany_name']]['pgDuplicates'] =
407 $isDuplicate || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgDuplicates']
410 $this->_data[$row['sellerCompany_name']]['pgMultiMatch'] =
411 $multimatch || 'Yes' == $this->_data[$row['sellerCompany_name']]['pgMultiMatch']
417 $this->_data[$row['sellerCompany_name']] = array(
418 'fromGroup_name' => $row['sellerGroup_name'],
419 'fromDivision_name' => $row['sellerDivision_name'],
420 'fromCompany_name' => $row['sellerCompany_name'],
424 'auctionTotal' => 0.0,
426 'pgCommission' => $isDuplicate ? 0.0 : 1000.0,
427 'pgDuplicates' => $isDuplicate ? 'Yes' : '',
428 'pgMultiMatch' => $multimatch ? 'Yes' : ''