3 namespace Report\Report;
9 class AuctionTrader extends \Utility\Service\Report
15 protected $_title = 'Auction Trader Report';
20 protected $_subject = 'Auction Trader Report';
25 protected $_description = 'Historical report for trade offers on Auction.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
37 protected $_headers = array(
40 'Buyer Contact Person',
44 'Total Trade Value (Highest Bids)',
45 'Total Bid Value (Highest Bid)',
46 'Percentage (Highest Bid)'
52 protected $_fields = array(
67 protected $_totals = array(
77 protected $_currencyFields = array(
85 public function build()
87 #-> Prepare parameters.
89 'region.name as region_name',
90 'company.name as company_name',
91 'profile.firstName as firstName',
92 'profile.familyName as familyName',
93 'profile.mobile as mobile',
94 'COUNT(DISTINCT auction.id) as bids',
95 'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN 1 ELSE 0 END) AS winning',
96 'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN stock.tradePrice ELSE 0 END) AS tradePrice',
97 'SUM(CASE WHEN auction.currentBid = bid.id AND auction.jobState=\'Sold\' THEN auction.currentBidPrice ELSE 0 END) AS bidPrice'
101 $query = 'SELECT [SELECTION] FROM \Auction\Entity\Bid bid '
102 . ' LEFT JOIN bid.auction auction '
103 . ' LEFT JOIN auction.stock stock '
104 . ' LEFT JOIN bid.company company '
105 . ' LEFT JOIN bid.profile profile'
106 . ' LEFT JOIN company.region region '
107 . ' LEFT JOIN company.group grp '
108 . ' LEFT JOIN company.groupDivision division '
110 . 'GROUP BY bid.profile';
112 $where[] = 'bid.created >= :start';
113 $where[] = 'bid.created <= :end';
115 #-> Construct details.
116 if (!isset($this->_input['dateFrom']) || empty($this->_input['dateFrom']))
118 if ($this->_input['dateTo'] && !empty($this->_input['dateTo']))
120 $parts = explode('-', $this->_input['dateTo']);
121 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, $parts[1], 1, $parts[0]));
125 $this->_input['dateTo'] = date('Y-m-d', mktime(23, 59, 59, date('m'), 0, date('Y')));
126 $this->_input['dateFrom'] = date('Y-m-d', mktime(1, 0, 0, date('m') - 1, 1, date('Y')));
129 if (!isset($this->_input['dateTo']) || empty($this->_input['dateTo']))
131 $parts = explode('-', $this->_input['dateFrom']);
132 $this->_input['dateTo'] = date('Y-m-d', mktime(1, 0, 0, $parts[1] + 1, 0, $parts[0]));
135 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
136 $params['start'] = new \DateTime($this->_input['dateFrom']);
138 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
139 $params['end'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
141 if (isset($this->_input['group']) && !empty($this->_input['group']) && 'null' != $this->_input['group'])
143 $this->_queries['Group'] = $this->em
144 ->find('\Company\Entity\Group', $this->_input['group'])
146 $where[] = 'grp.id = :group';
147 $params['group'] = $this->_input['group'];
150 if (isset($this->_input['groupDivision']) && !empty($this->_input['groupDivision']) && 'null' != $this->_input['groupDivision'])
152 $this->_queries['Division'] = $this->em
153 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
155 $where[] = 'division.id = :division';
156 $params['division'] = $this->_input['groupDivision'];
158 if (isset($this->_input['company']) && !empty($this->_input['company']) && 'null' != $this->_input['company'])
160 $this->_queries['Dealership'] = $this->em
161 ->find('\Company\Entity\Company', $this->_input['company'])
163 $where[] = 'company.id = :company';
164 $params['company'] = $this->_input['company'];
168 $query = str_replace(array(
169 '[SELECTION]', '[WHERE]'
171 implode(', ', $selection) . ' ',
172 !empty($where) ? 'WHERE ' . implode(' AND ', $where) . ' ' : ' '
175 $query = $this->em->createQuery($query);
176 !empty($params) && $query->setParameters($params);
177 $this->_data = $query->getResult();
179 foreach ($this->_data as $key => $data)
181 $this->_data[$key]['percentage'] = 0 < $data['tradePrice']
182 ? round(($data['bidPrice'] / $data['tradePrice']) * 100)
184 $this->_data[$key]['fullname'] = $data['firstName'] . ' ' . $data['familyName'];