initial commit
[namibia] / module / Report / src / Report / Report / Auction.php
1 <?php
2 namespace Report\Report;
3
4
5
6 /**
7  * Stock Control Report.
8  * @author andre.fourie
9  */
10 class Auction extends \Utility\Service\Report
11 {
12
13
14
15         /**
16          * @var string
17          */
18         protected $_title  = 'Auction Report';
19         /**
20          * @var string
21          */
22         protected $_subject = 'Auction';
23         /**
24          * @var string
25          */
26         protected $_description = 'Historical report for vehicles sold on auction.';
27         /**
28          * @var array
29          */
30         protected $_notes = array(
31                         'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.co.za'
32         );
33         /**
34          * @var array
35          */
36         protected $_headers = array(
37                         'Auction ID',
38                         'Seller Group',
39                         'Seller Division',
40                         'Seller Dealership',
41             'Seller Region',
42 //                      'Seller User Name',
43 //                      'Seller User Surname',
44 //                      'Buyer Group',
45 //                      'Buyer Division',
46                         'Buyer Dealership',
47             'Buyer Region',
48 //                      'Buyer User Name',
49 //                      'Buyer User Surname',
50                         'Vehicle Make',
51 //                      'Model',
52                         'Type',
53                         'Year',
54                         'Stock No.',
55                         'VIN No.',
56                         'Engine No.',
57                         'Registration No.',
58             'Papers',
59                         'Date Loaded',
60                         'Date Sold',
61                         'Trade Price',
62                         'Reserve Price',
63                         'Total Recon',
64                         'Kilometers',
65                         'Total no of Bids',
66                         'Highest Bid',
67                         'Commission',
68                         'Highest Bid / Trade Price'
69         );
70         /**
71          * @var array
72          */
73         protected $_fields = array(
74                         'auction_id',
75                         'sellerGroup_name',
76                         'sellerDivision_name',
77                         'sellerCompany_name',
78             'sellerRegion_name',
79 //                      'sellerProfile_firstName',
80 //                      'sellerProfile_familyName',
81 //                      'buyerGroup_name',
82 //                      'buyerDivision_name',
83                         'buyerCompany_name',
84             'buyerRegion_name',
85 //                      'buyerProfile_firstName',
86 //                      'buyerProfile_familyName',
87                         'make_name',
88 //                      'model_name',
89                         'type_name',
90                         'year_name',
91                         'stock_stockNumber',
92                         'stock_vinNumber',
93                         'stock_engineNumber',
94                         'stock_registrationNumber',
95             'papers_name',
96                         'auction_created',
97                         'auction_endDate',
98                         'stock_tradePrice',
99                         'auction_reservePrice',
100                         'stock_damageTotal',
101                         'stock_km',
102                         'numBids',
103                         'auction_currentBidPrice',
104                         'auction_bidIncrement',
105                         'bidPerc'
106         );
107         /**
108          * @var array
109          */
110         protected $_totals = array(
111                         'auction_currentBidPrice',
112                         'auction_bidIncrement',
113                         'stock_tradePrice',
114                         'auction_reservePrice',
115                         'stock_damageTotal',
116                         'numBids'
117         );
118         /**
119          * @var array
120          */
121         protected $_currencyFields = array(
122                         'auction_currentBidPrice',
123                         'auction_bidIncrement',
124                         'stock_tradePrice',
125                         'auction_reservePrice',
126                         'stock_damageTotal'
127         );
128
129
130
131         /**
132          * Build the dataset.
133          */
134         public function build()
135         {
136         ini_set('memory_limit','1024M');
137                 #-> Prepare parameters.
138                 $selection = array(
139                                 'auction', 'stock', 'type', 'year', 'model', 'make', 'papers',
140                                 'sellerCompany', 'sellerRegion', 'sellerGroup', 'sellerDivision', 'sellerProfile',
141                                 'buyerCompany', 'buyerRegion', 'buyerGroup', 'buyerDivision', 'buyerProfile',
142                                 'COUNT(DISTINCT bid.id) as numBids'
143                 );
144                 $join      = array();
145                 $leftJion  = array();
146                 $where     = array();
147                 $params    = array();
148                 $query = 'SELECT [SELECTION] '
149                                 . 'FROM \Auction\Entity\Auction auction '
150                                 . ' JOIN auction.stock stock '
151                                 . ' JOIN stock.company sellerCompany '
152                 . ' JOIN sellerCompany.region sellerRegion '
153                                 . ' JOIN auction.bids bid '
154                                 . ' LEFT JOIN stock.type type '
155                                 . ' LEFT JOIN stock.vehicleYear year '
156                                 . ' LEFT JOIN type.model model '
157                                 . ' LEFT JOIN model.make make '
158                 . ' LEFT JOIN stock.papers papers '
159                                 . ' LEFT JOIN auction.createdBy sellerProfile '
160                                 . ' LEFT JOIN auction.currentBid currentBid '
161                                 . ' LEFT JOIN currentBid.profile buyerProfile '
162                                 . ' LEFT JOIN currentBid.company buyerCompany '
163                 . ' LEFT JOIN buyerCompany.region buyerRegion '
164                                 . ' LEFT JOIN sellerCompany.group sellerGroup '
165                                 . ' LEFT JOIN sellerCompany.groupDivision sellerDivision '
166                                 . ' LEFT JOIN buyerProfile.group buyerGroup '
167                                 . ' LEFT JOIN buyerProfile.groupDivision buyerDivision '
168                                 . '[WHERE] '
169                                 . 'GROUP BY auction.id '
170                                 . 'ORDER BY auction.created ASC';
171                 $where[] = 'auction.jobState = \'Sold\'';
172
173                 #-> Construct details.
174                 $authData = \Utility\Registry::getAuthData();
175                 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
176                 {
177                         $this->_queries['Date Range From'] = $this->_input['dateFrom'];
178                         $where[] = 'auction.endDate >= :dateFrom';
179                         $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
180                 }
181                 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
182                 {
183                         $this->_queries['Date Range Until'] = $this->_input['dateTo'];
184                         $where[] = 'auction.endDate <= :dateTo';
185                         $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
186                 }
187                 if (isset($this->_input['group'])
188                                 && !empty($this->_input['group'])
189                                 && 'null' != $this->_input['group'])
190                 {
191                         $this->_queries['Group'] = $this->em
192                                 ->find('\Company\Entity\Group', $this->_input['group'])
193                                 ->name;
194                         $where[] = 'sellerGroup.id = :group';
195                         $params['group'] = $this->_input['group'];
196                 }
197                 if (isset($this->_input['groupDivision'])
198                                 && !empty($this->_input['groupDivision'])
199                                 && 'null' != $this->_input['groupDivision'])
200                 {
201                         error_log($this->_input['groupDivision']);
202                         $this->_queries['Division'] = $this->em
203                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
204                                 ->name;
205                         $where[] = 'sellerDivision.id = :division';
206                         $params['division'] = $this->_input['groupDivision'];
207                 }
208                 if (isset($this->_input['company'])
209                                 && !empty($this->_input['company'])
210                                 && 'null' != $this->_input['company'])
211                 {
212                         $this->_queries['Dealership'] = $this->em
213                                 ->find('\Company\Entity\Company', $this->_input['company'])
214                                 ->name;
215                         $where[] = 'sellerCompany.id = :company';
216                         $params['company'] = $this->_input['company'];
217                 }
218
219                 #-> Finalize query.
220                 $query = str_replace(array(
221                                 '[SELECTION]', '[WHERE]'
222                         ), array(
223                                 implode(', ', $selection) . ' ',
224                                 !empty($where)
225                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
226                                         : ' '
227                         ), $query);
228
229                 #-> Collect data.
230                 $query = $this->em->createQuery($query);
231                 !empty($params)
232                         && $query->setParameters($params);
233
234 //        \Utility\Debug::errorLog('$query->getSQL()', $query->getSQL());
235 //        \Utility\Debug::errorLog('$query->getParameters()', $query->getParameters());
236
237         $this->_data = $query->getScalarResult();
238                 $this->em->clear();
239                 foreach ($this->_data as $id => $row)
240                 {
241                         $this->_data[$id]['bidPerc'] = 0 < $row['stock_tradePrice']
242                                 ? round((100 / $row['stock_tradePrice']) * $row['auction_currentBidPrice'], 1) . ' %'
243                                 : '0.0 %';
244
245             $this->_data[$id]['papers_name'] = '';
246
247             if('Relist' != $row['auction_jobState'])
248             {
249                 $this->_data[$id]['papers_name'] =  $row['papers_name'];
250             }
251
252                 }
253         }
254
255 }