initial commit
[namibia] / module / Report / src / Report / Report / Trader.php
1 <?php
2 namespace Report\Report;
3
4
5
6 /**
7  * Trade offers Report.
8  * @author andre.fourie
9  */
10 class Trader extends \Utility\Service\Report
11 {
12
13
14
15         /**
16          * @var string
17          */
18         protected $_title  = 'Trader Report';
19         /**
20          * @var string
21          */
22         protected $_subject = 'Trade Offers';
23         /**
24          * @var string
25          */
26         protected $_description = 'Historical report for trade offers on Price Guide.';
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                         'Dealership',
38                         'Total Offers',
39                         'Total Highest Offers',
40                         'Total Trade Value',
41                         'Total Offer Value',
42                         'Percentage',
43                         'Total Trade Value (Highest Offers)',
44                         'Total Offer Value (Highest Offers)',
45                         'Percentage (Highest Offers)',
46         );
47         /**
48          * @var array
49          */
50         protected $_fields = array(
51                         'company_name',
52                         'num_offers',
53                         'num_highest',
54                         'sum_trade',
55                         'sum_offers',
56                         'perc',
57                         'sum_trade_highest',
58                         'sum_offers_highest',
59                         'perc_highest'
60         );
61         /**
62          * @var array
63          */
64         protected $_totals = array(
65                         'num_offers',
66                         'num_highest',
67                         'sum_trade',
68                         'sum_offers',
69                         'sum_trade_highest',
70                         'sum_offers_highest'
71         );
72         /**
73          * @var array
74          */
75         protected $_currencyFields = array(
76                         'sum_trade',
77                         'sum_offers',
78                         'sum_trade_highest',
79                         'sum_offers_highest'
80         );
81
82
83
84         /**
85          * Build the dataset.
86          */
87         public function build()
88         {
89                 #-> Prepare parameters.
90                 $selection = array(
91                                 'company.name',
92                                 'COUNT(offer.id) AS num_offers',
93                                 'SUM(IF(offer.amount = stock.highest_offer, 1, 0)) AS num_highest',
94                                 'SUM(stock.trade_price) as sum_trade',
95                                 'SUM(offer.amount) AS sum_offers',
96                                 'ROUND((100/SUM(stock.trade_price)) * SUM(offer.amount), 2) as perc',
97                                 'SUM(IF(offer.amount = stock.highest_offer, stock.trade_price, 0)) as sum_trade_highest',
98                                 'SUM(IF(offer.amount = stock.highest_offer, offer.amount, 0)) AS sum_offers_highest',
99                                 'ROUND((100/SUM(IF(offer.amount = stock.highest_offer, stock.trade_price, 0))) * SUM(IF(offer.amount = stock.highest_offer, offer.amount, 0)), 2) as perc_highest'
100                 );
101                 $join      = array();
102                 $leftJion  = array();
103                 $where     = array();
104                 $params    = array();
105                 $query = 'SELECT [SELECTION] '
106                                 . 'FROM stock '
107                                 . ' JOIN price_guide priceGuide ON priceGuide.id = stock.price_guide_id '
108                                 . ' JOIN price_guide_offer offer ON offer.price_guide_id=priceGuide.id '
109                                 . ' JOIN company company ON company.id=offer.company_id '
110                                 . '[WHERE] '
111                                 . 'GROUP BY company.name '
112                                 . 'ORDER BY company.name ASC';
113                 $where[] = array(
114                                 'offer.created BETWEEN :start AND :end',
115                                 'offer.updated BETWEEN :start AND :end'
116                 );
117                 $where[] = array(
118                                 'stock.vehicle_natis_id IS NULL',
119                                 'stock.vehicle_natis_id IN (1, 2)'
120                 );
121                 $where[] = 'stock.trade_price IS NOT NULL';
122                 $where[] = 'stock.trade_price > 0';
123
124                 #-> Construct details.
125                 $authData = \Utility\Registry::getAuthData();
126                 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
127                 {
128                         if ($this->_input['dateTo'] && !empty($this->_input['dateTo']))
129                         {
130                                 $parts = explode('-', $this->_input['dateTo']);
131                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, $parts[1], 1, $parts[0]));
132                         }
133                         else
134                         {
135                                 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
136                                 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, date('m') - 1, 1, date('Y')));
137                         }
138                 }
139                 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
140                 $params['start'] = new \DateTime($this->_input['dateFrom']);
141                 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
142                 {
143                         $parts = explode('-', $this->_input['dateFrom']);
144                         $this->_input['dateTo'] = date('Y-m-d', mktime(1, 0, 0, $parts[1] + 1, 0, $parts[0]));
145                 }
146                 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
147                 $params['end'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
148                 if (isset($this->_input['group'])
149                                 && !empty($this->_input['group'])
150                                 && 'null' != $this->_input['group'])
151                 {
152                         $this->_queries['Group'] = $this->em
153                                 ->find('\Company\Entity\Group', $this->_input['group'])
154                                 ->name;
155                         $where[] = 'company.company_group_id = :group';
156                         $params['group'] = $this->_input['group'];
157                 }
158                 if (isset($this->_input['groupDivision'])
159                                 && !empty($this->_input['groupDivision'])
160                                 && 'null' != $this->_input['groupDivision'])
161                 {
162                         error_log($this->_input['groupDivision']);
163                         $this->_queries['Division'] = $this->em
164                                 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
165                                 ->name;
166                         $where[] = 'company.company_group_division_id = :division';
167                         $params['division'] = $this->_input['groupDivision'];
168                 }
169                 if (isset($this->_input['company'])
170                                 && !empty($this->_input['company'])
171                                 && 'null' != $this->_input['company'])
172                 {
173                         $this->_queries['Dealership'] = $this->em
174                                 ->find('\Company\Entity\Company', $this->_input['company'])
175                                 ->name;
176                         $where[] = 'company.id = :company';
177                         $params['company'] = $this->_input['company'];
178                 }
179
180                 #-> Finalize query.
181                 foreach ($where as $key => $value)
182                 {
183                         if (is_array($value))
184                         {
185                                 $where[$key] = '(' . implode(' OR ', $value) . ')';
186                         }
187                 }
188                 $query = str_replace(array(
189                                 '[SELECTION]', '[WHERE]'
190                         ), array(
191                                 implode(', ', $selection) . ' ',
192                                 !empty($where)
193                                         ? 'WHERE ' . implode(' AND ', $where) . ' '
194                                         : ' '
195                         ), $query);
196
197                 #-> Collect data.
198                 $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->em);
199                 $rsm->addRootEntityFromClassMetadata('\Stock\Entity\Stock', 'stock');
200                 $rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\PriceGuide', 'priceGuide', 'stock', 'priceGuide',
201                                 array(
202                                                 'id'                                    => 'pg_id',
203                                                 'company_id'                    => 'pg_company_id',
204                                                 'created_by_profile_id' => 'pg_created_by_profile_id',
205                                                 'stock_valuation_id'    => 'pg_stock_valuation_id',
206                                                 'previous_status'               => 'pg_previous_status',
207                                                 'status'                                => 'pg_status',
208                                                 'created'                               => 'pg_created',
209                                                 'updated'                               => 'pg_updated',
210                                                 'archived'                              => 'pg_archived'
211                                 )
212                 );
213                 $rsm->addJoinedEntityFromClassMetadata('\PriceGuide\Entity\Offer', 'offer', 'priceGuide', 'offers',
214                                 array(
215                                                 'id'                                    => 'pgo_id',
216                                                 'company_id'                    => 'pgo_company_id',
217                                                 'profile_id'                    => 'pgo_profile_id',
218                                                 'price_guide_id'                => 'pgo_price_guide_id',
219                                                 'previous_status'               => 'pgo_previous_status',
220                                                 'status'                                => 'pgo_status',
221                                                 'created'                               => 'pgo_created',
222                                                 'updated'                               => 'pgo_updated',
223                                                 'archived'                              => 'pgo_archived'
224                                 )
225                 );
226                 $rsm->addJoinedEntityFromClassMetadata('\Company\Entity\Company', 'company', 'offer', 'company',
227                                 array(
228                                                 'id'                                    => 'cmp_id',
229                                                 'lib_region_id'                 => 'cmp_lib_region_id',
230                                                 'status'                                => 'cmp_status',
231                                                 'created'                               => 'cmp_created',
232                                                 'updated'                               => 'cmp_updated',
233                                                 'archived'                              => 'cmp_archived'
234                                 )
235                 );
236                 $rsm->addScalarResult('num_offers', 'num_offers');
237                 $rsm->addScalarResult('num_highest', 'num_highest');
238                 $rsm->addScalarResult('sum_trade', 'sum_trade');
239                 $rsm->addScalarResult('sum_offers', 'sum_offers');
240                 $rsm->addScalarResult('perc', 'perc');
241                 $rsm->addScalarResult('sum_trade_highest', 'sum_trade_highest');
242                 $rsm->addScalarResult('sum_offers_highest', 'sum_offers_highest');
243                 $rsm->addScalarResult('perc_highest', 'perc_highest');
244                 $query = $this->em->createNativeQuery($query, $rsm);
245                 !empty($params)
246                         && $query->setParameters($params);
247
248                 $this->_data = $query->getScalarResult();
249                 $this->em->clear();
250         }
251
252 }