2 namespace Report\Report;
10 class Sms extends \Utility\Service\Report
18 protected $_title = 'Sms Report';
22 protected $_subject = 'Sms';
26 protected $_description = 'Historical report for sms notifications.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
36 protected $_headers = array(
38 'Originator Division',
39 'Originator Dealership',
40 'Originator User Name',
41 'Originator User Surname',
45 'Receiver Dealership',
48 'Receiver Mobile Number',
58 protected $_fields = array(
62 'fromProfile_firstName',
63 'fromProfile_familyName',
68 'toProfile_firstName',
69 'toProfile_familyName',
80 protected $_totals = array(
89 protected $_currencyFields = array(
99 public function build()
101 #-> Prepare parameters.
104 'fromCompany', 'fromGroup', 'fromDivision', 'fromProfile',
105 'toCompany', 'toGroup', 'toDivision', 'toProfile',
112 ini_set('memory_limit','512M');
115 . 'fromCompany.name AS fromCompany_name, fromGroup.name AS fromGroup_name, fromDivision.name AS fromDivision_name, '
116 . 'fromProfile.firstName AS fromProfile_firstName, fromProfile.familyName AS fromProfile_familyName, '
117 . 'log.created AS log_created, log.smsTo AS log_smsTo, log.smsBody AS log_smsBody, '
118 . 'toCompany.name AS toCompany_name, toGroup.name AS toGroup_name, toDivision.name AS toDivision_name, '
119 . 'toProfile.firstName AS toProfile_firstName, toProfile.familyName AS toProfile_familyName '
120 . 'FROM \Utility\Entity\NotificationLog log '
121 . ' LEFT JOIN log.fromCompany fromCompany '
122 . ' LEFT JOIN fromCompany.group fromGroup '
123 . ' LEFT JOIN fromCompany.groupDivision fromDivision '
124 . ' LEFT JOIN log.fromProfile fromProfile '
125 . ' LEFT JOIN log.toCompany toCompany '
126 . ' LEFT JOIN toCompany.group toGroup '
127 . ' LEFT JOIN toCompany.groupDivision toDivision '
128 . ' LEFT JOIN log.toProfile toProfile '
130 . 'ORDER BY log_created ASC';
131 $where[] = 'log.smsTo IS NOT NULL AND log.smsBody != \'\'';
133 #-> Construct details.
134 $authData = \Utility\Registry::getAuthData();
135 $clickVal = isset($this->_input['clickVal']) && is_numeric($this->_input['clickVal'])
136 ? $this->_input['clickVal']
138 if (isset($this->_input['dateFrom']) && !empty($this->_input['dateFrom']))
140 $this->_queries['Date Range From'] = $this->_input['dateFrom'];
141 $where[] = 'log.created >= :dateFrom';
142 $params['dateFrom'] = new \DateTime($this->_input['dateFrom']);
144 if (isset($this->_input['dateTo']) && !empty($this->_input['dateTo']))
146 $this->_queries['Date Range Until'] = $this->_input['dateTo'];
147 $where[] = 'log.created <= :dateTo';
148 $params['dateTo'] = new \DateTime($this->_input['dateTo'] . ' 23:59:59');
150 if (isset($this->_input['group'])
151 && !empty($this->_input['group'])
152 && 'null' != $this->_input['group'])
154 $this->_queries['Group'] = $this->em
155 ->find('\Company\Entity\Group', $this->_input['group'])
157 $where[] = 'fromGroup.id = :group';
158 $params['group'] = $this->_input['group'];
160 if (isset($this->_input['groupDivision'])
161 && !empty($this->_input['groupDivision'])
162 && 'null' != $this->_input['groupDivision'])
164 error_log($this->_input['groupDivision']);
165 $this->_queries['Division'] = $this->em
166 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
168 $where[] = 'fromDivision.id = :division';
169 $params['division'] = $this->_input['groupDivision'];
171 if (isset($this->_input['company'])
172 && !empty($this->_input['company'])
173 && 'null' != $this->_input['company'])
175 $this->_queries['Dealership'] = $this->em
176 ->find('\Company\Entity\Company', $this->_input['company'])
178 $where[] = 'fromCompany.id = :company';
179 $params['company'] = $this->_input['company'];
183 $query = str_replace(array(
187 ? 'WHERE ' . implode(' AND ', $where) . ' '
192 $query = $this->em->createQuery($query);
194 && $query->setParameters($params);
195 $result = $query->getResult();
198 while(($record = array_shift($result)) != null)
200 $tmpRecord = array();
201 $tmpRecord['fromGroup_name'] = $record['fromGroup_name'];
202 $tmpRecord['fromDivision_name'] = $record['fromDivision_name'];
203 $tmpRecord['fromCompany_name'] = $record['fromCompany_name'];
205 $tmpRecord['fromProfile_firstName'] = $record['fromProfile_firstName'];
206 $tmpRecord['fromProfile_familyName'] = $record['fromProfile_firstName'];
208 $tmpRecord['log_created'] = $record['log_created'];
210 $tmpRecord['toGroup_name'] = $record['toGroup_name'];
211 $tmpRecord['toDivision_name'] = $record['toDivision_name'];
212 $tmpRecord['toCompany_name'] = $record['toCompany_name'];
214 $tmpRecord['toProfile_firstName'] = $record['toProfile_firstName'];
215 $tmpRecord['toProfile_familyName'] = $record['toProfile_familyName'];
217 $tmpRecord['log_smsTo'] = $record['log_smsTo'];
218 $tmpRecord['log_smsBody'] = $record['log_smsBody'];
219 $tmpRecord['charCount'] = strlen($record['log_smsBody']);
220 $tmpRecord['clickCount'] = strlen($record['log_smsBody']) > 160
221 ? ceil(strlen($record['log_smsBody']) / (160 - 7))
224 $tmpRecord['clickValue'] = $clickVal;
225 $tmpRecord['total'] = round($tmpRecord['charCount'] * $clickVal, 2);
227 $data[] = $tmpRecord;
230 $this->_data = $data;