2 namespace Report\Report;
6 * Stock Control Report.
9 class LoadVsSoldDetail extends \Utility\Service\Report
17 protected $_title = 'Loaded and Sold';
21 protected $_subject = 'Vehicles loaded vs vehicles sold';
25 protected $_description = 'Vehicles loaded vs vehicles sold.';
29 protected $_notes = array(
30 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.co.za'
35 protected $_headers = array(
50 'Initial Reserve Price',
60 protected $_fields = array(
70 'stock_registrationNumber',
75 'auction_initial_reservePrice',
76 'auction_reservePrice',
77 'auction_currentBidPrice',
78 'auction_numberOfBids',
85 protected $_totals = array(
87 'auction_reservePrice',
88 'auction_currentBidPrice',
89 'auction_numberOfBids',
95 protected $_currencyFields = array(
96 'auction_reservePrice',
97 'auction_currentBidPrice',
105 public function build()
107 #-> Prepare parameters.
108 #-> Collect loaded and sold totals.
110 'grp.name AS group_name',
111 'division.name AS division_name',
112 'company.name AS company_name',
113 '(CASE WHEN stock.previousState = :tradeState THEN tradeCentre.name THEN :blank END) as trade_centre',
114 'region.name AS region_name',
115 'auction.endDate AS auction_endDate',
116 'stock.referenceNumber as reference',
117 'auction.id AS auction_id',
118 'stock.stockNumber AS stock_stockNumber',
119 'stock.registrationNumber AS stock_registrationNumber',
120 'stock.vinNumber AS vin_number',
121 'make.name AS make_name',
122 'model.name AS model_name',
123 'type.name AS type_name',
124 'stock.tradePrice as trade_price',
125 'auction.reservePrice AS auction_initial_reservePrice',
126 'auction.reservePrice AS auction_reservePrice',
127 'auction.currentBidPrice AS auction_currentBidPrice',
128 'COUNT(DISTINCT bid.id) AS auction_numberOfBids',
129 '(auction.currentBidPrice - auction.reservePrice) AS auction_profit', //sold - reserve price
130 'stock.km AS stock_km',
131 'auction.jobState AS status'
137 $query = 'SELECT [SELECTION] '
138 . 'FROM \Stock\Entity\Stock stock '
139 . 'JOIN stock.auction auction '
140 . 'JOIN stock.company company '
141 . 'LEFT JOIN company.tradeCenter tradeCentre '
142 . 'LEFT JOIN auction.bids bid '
143 . 'LEFT JOIN company.city city '
144 . 'LEFT JOIN city.region region '
145 . 'LEFT JOIN stock.type type '
146 . 'LEFT JOIN type.model model '
147 . 'LEFT JOIN model.make make '
148 . 'LEFT JOIN company.group grp '
149 . 'LEFT JOIN company.groupDivision division '
151 . 'GROUP BY auction.id '
152 . 'ORDER BY auction.endDate ASC';
154 $where[] = 'auction.jobState != :status';
155 $params['status'] = 'Active';
156 $params['tradeState'] = 'Trade Center';
157 $params['blank'] = '';
159 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
161 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
162 $where[] = 'auction.endDate >= :dateFrom';
163 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
165 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
167 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
168 $where[] = 'auction.endDate <= :dateTo';
169 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
171 if (isset($this->_input['group'])
172 && !empty($this->_input['group'])
173 && 'null' != $this->_input['group']
176 $this->_queries['Group'] = $this->em
177 ->find('\Company\Entity\Group', $this->_input['group'])
179 $where[] = 'grp.id = :group';
180 $params['group'] = $this->_input['group'];
182 if (isset($this->_input['groupDivision'])
183 && !empty($this->_input['groupDivision'])
184 && 'null' != $this->_input['groupDivision']
187 error_log($this->_input['groupDivision']);
188 $this->_queries['Division'] = $this->em
189 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
191 $where[] = 'division.id = :division';
192 $params['division'] = $this->_input['groupDivision'];
194 if (isset($this->_input['company'])
195 && !empty($this->_input['company'])
196 && 'null' != $this->_input['company']
199 error_log($this->_input['company']);
200 $this->_queries['Dealership'] = $this->em
201 ->find('\Company\Entity\Company', $this->_input['company'])
203 $where[] = 'company.id = :company';
204 $params['company'] = $this->_input['company'];
208 $query = str_replace(array(
209 '[SELECTION]', '[WHERE]'
211 implode(', ', $selection) . ' ',
213 ? 'WHERE ' . implode(' AND ', $where) . ' '
218 $query = $this->em->createQuery($query);
220 && $query->setParameters($params);
221 $this->_data = $query->getScalarResult();
223 foreach ($this->_data as $id => $row)
225 if ('Sold' != $row['status'])
227 $this->_data[$id]['auction_currentBidPrice'] = 0.00;
228 $this->_data[$id]['auction_bidIncrement'] = 0.00;
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'];