2 namespace Report\Report;
7 * Stock Control Report.
10 class AuctionAdherence extends \Utility\Service\Report
18 protected $_title = 'AuctionAdherence Report';
22 protected $_subject = 'AuctionAdherence';
26 protected $_description = 'Details of vehicles found on Bid4Cars auction that was sold/invoiced through automate.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
36 protected $_headers = array(
59 'Variance against Potential'
65 protected $_fields = array(
83 'auction_initial_reservePrice',
86 'auction_reservePrice',
94 protected $_totals = array(
97 'auction_reservePrice',
105 protected $_currencyFields = array(
106 'auction_reservePrice',
107 'auction_currentBidPrice',
108 'auction_bidIncrement'
114 public function build()
116 #-> Prepare parameters.
117 #-> Collect loaded and sold totals.
119 'grp.name AS group_name',
120 'division.name AS division_name',
121 'company.name AS company_name',
122 'trade_center.name AS trade_centre_name',
123 'region.name as region_name',
124 'stock.stockNumber AS stock_stockNumber',
125 'make.name AS make_name',
126 'stock.vinNumber as vin_number',
127 'stock.registrationNumber as reg_number',
128 'stock.engineNumber as eng_number',
129 'ts.totalCost as total_cost',
130 'ts.salesTotal as sales_total',
131 '(ts.salesTotal - ts.totalCost) as spl',
132 'ts.soldTo as sold_to',
133 'sold_to_company.name as sold_to_name',
134 'auction.startDate as date_loaded',
135 '(CASE WHEN auction.jobState = :auctionState THEN auction.endDate THEN :notApplicable END) as auction_endDate',
136 'auction.reservePrice AS auction_initial_reservePrice',
137 'ts.vinMatch as vin_match',
138 'ts.regMatch as reg_match',
139 'auction.reservePrice AS auction_reservePrice',
140 '(CASE WHEN auction.jobState = :auctionState THEN auction.currentBidPrice THEN :notApplicable END) as auction_sold_price',
141 '(CASE WHEN auction.jobState = :auctionState THEN auction.currentBidPrice - ts.salesTotal THEN :notApplicable END) as potential',
142 'stock.referenceNumber as reference',
148 $query = 'SELECT [SELECTION] '
149 . 'FROM \Adherence\Entity\TradeSales ts '
150 . 'LEFT JOIN ts.stock stock '
151 . 'LEFT JOIN stock.auction auction '
152 . 'LEFT JOIN auction.soldToCompany sold_to_company '
153 . 'LEFT JOIN stock.company company '
154 . 'LEFT JOIN company.region region '
155 . 'LEFT JOIN company.tradeCenter trade_center '
156 . 'LEFT JOIN stock.type type '
157 . 'LEFT JOIN type.model model '
158 . 'LEFT JOIN model.make make '
159 . 'LEFT JOIN company.group grp '
160 . 'LEFT JOIN company.groupDivision division '
162 . 'ORDER BY ts.saleDate ASC';
164 $params['auctionState'] = 'Sold';
165 $params['notApplicable'] = 'N/A';
167 $where[] = '(auction.jobState != :status OR auction.jobState IS NULL)';
168 $params['status'] = 'Undone';
170 $where[] = '(auction.jobState != :status2 OR auction.jobState IS NULL)';
171 $params['status2'] = 'Relist';
173 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
175 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
176 $where[] = 'ts.saleDate >= :dateFrom';
177 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
179 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
181 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
182 $where[] = 'ts.saleDate <= :dateTo';
183 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
185 if (isset($this->_input['group']) && !empty($this->_input['group']) && 'null' != $this->_input['group'])
187 $this->_queries['Group'] = $this->em
188 ->find('\Company\Entity\Group', $this->_input['group'])
190 $where[] = 'grp.id = :group';
191 $params['group'] = $this->_input['group'];
193 if (isset($this->_input['groupDivision']) && !empty($this->_input['groupDivision']) && 'null' != $this->_input['groupDivision'])
195 error_log($this->_input['groupDivision']);
196 $this->_queries['Division'] = $this->em
197 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
199 $where[] = 'division.id = :division';
200 $params['division'] = $this->_input['groupDivision'];
202 if (isset($this->_input['company']) && !empty($this->_input['company']) && 'null' != $this->_input['company'])
204 error_log($this->_input['company']);
205 $this->_queries['Dealership'] = $this->em
206 ->find('\Company\Entity\Company', $this->_input['company'])
208 $where[] = 'company.id = :company';
209 $params['company'] = $this->_input['company'];
213 $query = str_replace(array(
214 '[SELECTION]', '[WHERE]'
216 implode(', ', $selection) . ' ',
217 !empty($where) ? 'WHERE ' . implode(' AND ', $where) . ' ' : ' '
221 $query = $this->em->createQuery($query);
222 !empty($params) && $query->setParameters($params);
223 error_log($query->getSQL());
224 $this->_data = $query->getScalarResult();
226 foreach ($this->_data as $id => $row)
228 if ($row['reference'])
230 $query = 'SELECT auction.reservePrice '
231 . 'FROM \Auction\Entity\Auction auction '
232 . 'JOIN auction.stock stock '
233 . 'WHERE stock.referenceNumber = :reference '
234 . 'ORDER BY auction.endDate ASC';
237 $params['reference'] = $row['reference'];
239 $query = $this->em->createQuery($query);
240 $query->setParameters($params);
241 $temp = $query->getScalarResult();
243 $this->_data[$id]['auction_initial_reservePrice'] = $temp[0]['reservePrice'];