initial commit
[namibia] / module / Report / src / Report / Report / LoadVsSold.php
1 <?php
2 namespace Report\Report;
3
4
5
6 /**
7  * Stock Control Report.
8  * @author andre.fourie
9  */
10 class LoadVsSold extends \Utility\Service\Report
11 {
12
13
14
15         /**
16          * @var string
17          */
18         protected $_title  = 'Loaded and Sold';
19         /**
20          * @var string
21          */
22         protected $_subject = 'Vehicles loaded vs vehicles sold';
23         /**
24          * @var string
25          */
26         protected $_description = 'Vehicles loaded vs vehicles sold.';
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                         'Month',
38                         'Loaded',
39                         'Sold'
40         );
41         /**
42          * @var array
43          */
44         protected $_fields = array(
45                         'month',
46                         'loaded',
47                         'sold'
48         );
49         /**
50          * @var array
51          */
52         protected $_totals = array(
53                         'loaded',
54                         'sold'
55         );
56         /**
57          * @var array
58          */
59         protected $_currencyFields = array();
60
61         /**
62          * @var array
63          */
64
65         /**
66          * Build the dataset.
67          */
68         public function build()
69         {
70                 $config = $this->em->getConfiguration();
71         $config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');
72         $config->addCustomStringFunction('SOUNDEX', 'UVd\DoctrineFunction\Soundex');
73
74         $year = $this->_input['year'];
75                 $this->_data = array();
76
77                 //Collect Data
78                 $selection = array(
79                                 'division.name',
80                                 'company.name as companyName',
81                                 'tradeCenter.name as tradeCenterName',
82                                 'manager.firstName',
83                                 'manager.familyName',
84                                 'stock.id as stockId',
85                                 'SOUNDEX(stock.stockNumber) as stockNumber',
86                                 'SOUNDEX(stock.registrationNumber) as registrationNumber',
87                                 'SOUNDEX(stock.vinNumber) as vinNumber',
88                                 'SOUNDEX(stock.engineNumber) as engineNumber',
89                                 'DATE_FORMAT(auction.endDate ,\'%Y-%m\') as month',
90                                 'auction.jobState'
91                         );
92                 $where     = array();
93                 $params    = array();
94                 $query = 'SELECT [SELECTION] '
95                                 . 'FROM \Stock\Entity\Stock stock '
96                                 . 'JOIN stock.auction auction '
97                                 . 'JOIN stock.company company '
98                                 . 'LEFT JOIN company.tradeCenter tradeCenter '
99                         . 'LEFT JOIN company.region region '
100                                 . 'LEFT JOIN company.group grp '
101                                 . 'LEFT JOIN company.groupDivision division '
102                         . 'LEFT JOIN company.regionalManager manager '
103                                 . '[WHERE] '
104                                 . 'ORDER BY auction.endDate';
105                 $where[] = 'auction.jobState != :status';
106                 $params['status'] = 'Active';
107
108                 $this->_queries['Date Range From'] = $year . '-01-01';
109                 $where[] = 'auction.endDate >= :dateFrom';
110                 $params['dateFrom'] = new \DateTime($year . '-01-01');
111
112                 $this->_queries['Date Range Until'] = $year . '-12-31';
113                 $where[] = 'auction.endDate <= :dateTo';
114                 $params['dateTo'] = new \DateTime($year . '-12-31');
115
116                 if (isset($this->_input['group'])
117                         && !empty($this->_input['group'])
118                         && 'null' != $this->_input['group'])
119                 {
120                         $this->_queries['Group'] = $this->em
121                                 ->find('\Company\Entity\Group', $this->_input['group'])
122                                 ->name;
123                         $where[] = 'grp.id = :group';
124                         $params['group'] = $this->_input['group'];
125                 }
126                 if (isset($this->_input['region'])
127                         && !empty($this->_input['region'])
128                         && 'null' != $this->_input['region'])
129                 {
130                         $this->_queries['Region'] = $this->em
131                                 ->find('\Location\Entity\Region', $this->_input['region'])
132                                 ->name;
133                         $where[] = 'region.id = :region';
134                         $params['region'] = $this->_input['region'];
135                 }
136
137                 if (isset($this->_input['groupDivision'])
138                         && !empty($this->_input['groupDivision'])
139                         && 'null' != $this->_input['groupDivision'])
140                 {
141                         $this->_queries['Division'] = $this->em
142                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
143                                 ->name;
144                         $where[] = 'division.id = :division';
145                         $params['division'] = $this->_input['groupDivision'];
146                 }
147
148                 if (isset($this->_input['company'])
149                         && !empty($this->_input['company'])
150                         && 'null' != $this->_input['company'])
151                 {
152                         $this->_queries['Dealership'] = $this->em
153                                 ->find('\Company\Entity\Company', $this->_input['company'])
154                                 ->name;
155                         $where[] = 'company.id = :company';
156                         $params['company'] = $this->_input['company'];
157                 }
158
159                 /* if (isset($this->_input['regionalManager'])
160                  && !empty($this->_input['regionalManager'])
161                                 && 'null' != $this->_input['regionalManager'])
162                 {
163                 $where[] = 'manager.id IN (:regionalManager)';
164                 $params['regionalManager'] = $this->_input['regionalManager'];
165                 } */
166
167                 //Finalize query.
168                 $query = str_replace(array(
169                                 '[SELECTION]', '[WHERE]'
170                 ), array(
171                                 implode(', ', $selection) . ' ',
172                                 !empty($where)
173                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
174                                         : ' '
175                 ), $query);
176
177                 $query = $this->em->createQuery($query);
178                 !empty($params)
179                         && $query->setParameters($params);
180                 $result = $query->getScalarResult();
181                 $this->em->clear();
182
183                 $prepDvsn = array();
184                 $prepComp = array();
185                 $centers  = array();
186                 foreach ($result as $id => $row)
187                 {
188                         $combo = $row['name']
189                                 . '.' . $row['firstName']
190                                 . '.' . $row['familyName']
191                                 . '.' . $row['stockNumber']
192                                 . '.' . $row['registrationNumber']
193                                 . '.' . $row['vinNumber']
194                                 . '.' . $row['engineNumber']
195                                 . '.' . $row['month'];
196                         $fullName = $row['firstName'] . ' ' . $row['familyName'];
197                         $divisionName = $row['name'];
198                         $companyName  = $row['companyName'];
199                         if (!is_null($row['tradeCenterName']))
200                         {
201                                 $centers["$divisionName.$companyName"] = $row['tradeCenterName'];
202                         }
203
204                         #-> Division aggregation.
205                         if (!isset($prepDvsn[$divisionName]))
206                         {
207                                 $prepDvsn[$divisionName] = array();
208                         }
209                         if (!isset($prepDvsn[$divisionName][$fullName]))
210                         {
211                                 $prepDvsn[$divisionName][$fullName] = array();
212                         }
213                         if (!isset($prepDvsn[$divisionName][$fullName][$row['month']]))
214                         {
215                                 $prepDvsn[$divisionName][$fullName][$row['month']] = array();
216                         }
217                         if (!isset($prepDvsn[$divisionName][$fullName][$row['month']][$combo]))
218                         {
219                                 $prepDvsn[$divisionName][$fullName][$row['month']][$combo] = $row;
220                         }
221
222                         #-> Company aggregation.
223                         if (!isset($prepComp[$divisionName]))
224                         {
225                                 $prepComp[$divisionName] = array();
226                         }
227                         if (!isset($prepComp[$divisionName][$fullName]))
228                         {
229                                 $prepComp[$divisionName][$fullName] = array();
230                         }
231                         if (!isset($prepComp[$divisionName][$fullName][$companyName]))
232                         {
233                                 $prepComp[$divisionName][$fullName][$companyName] = array();
234                         }
235                         if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']]))
236                         {
237                                 $prepComp[$divisionName][$fullName][$companyName][$row['month']] = array();
238                         }
239                         if (!isset($prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo]))
240                         {
241                                 $prepComp[$divisionName][$fullName][$companyName][$row['month']][$combo] = $row;
242                         }
243                 }
244                 $this->_data['TradeCenterData'] = $centers;
245
246                 #-> Division data.
247                 $output = array();
248                 foreach ($prepDvsn as $division => $row1)
249                 {
250                         $output[$division] = array();
251                         foreach ($row1 as $manager => $row2)
252                         {
253                                 $output[$division][$manager] = array();
254                                 foreach ($row2 as $month => $row3)
255                                 {
256                                         $output[$division][$manager][$month] = array(
257                                                 'Relist' => 0,
258                                                 'Sold'   => 0,
259                                                 'Undone' => 0
260                                         );
261                                         foreach ($row3 as $combo => $data)
262                                         {
263                                                 $output[$division][$manager][$month][$data['jobState']]++;
264                                         }
265                                 }
266                         }
267                 }
268                 $prepDvsn = null;
269                 $this->_data['DivisionData'] = $output;
270
271                 #-> Company data.
272                 $output = array();
273                 foreach ($prepComp as $division => $row1)
274                 {
275                         $output[$division] = array();
276                         foreach ($row1 as $manager => $row2)
277                         {
278                                 $output[$division][$manager] = array();
279                                 foreach ($row2 as $company => $row3)
280                                 {
281                                         $output[$division][$manager][$company] = array();
282                                         foreach ($row3 as $month => $row4)
283                                         {
284                                                 $output[$division][$manager][$company][$month] = array(
285                                                                 'Relist' => 0,
286                                                                 'Sold'   => 0,
287                                                                 'Undone' => 0
288                                                 );
289                                                 foreach ($row4 as $combo => $data)
290                                                 {
291                                                         $output[$division][$manager][$company][$month][$data['jobState']]++;
292                                                 }
293                                         }
294                                 }
295                         }
296                 }
297                 $prepDvsn = null;
298                 $this->_data['CompanyData'] = $output;
299
300                 #-> Finalise.
301                 $this->_queries['year'] = $year;
302                 return;
303         }
304
305 }