3 namespace Report\Report;
9 class Login extends \Utility\Service\Report
15 protected $_title = 'Login Report';
20 protected $_subject = 'Login';
25 protected $_description = 'Historical report for login notifications.';
30 protected $_notes = array(
31 'Confidential information, generated using Bid 4 Cars, for more information visit bid4cars.com.na'
37 protected $_headers = array(
58 protected $_fields = array(
77 protected $_totals = array();
82 protected $_currencyFields = array();
87 public function build()
89 #-> Prepare parameters.
95 ini_set('memory_limit', '512M');
98 . 'company.name AS companyName, grp.name AS groupName, division.name AS divisionName, permission.name AS permissionName, '
99 . 'profile.firstName AS firstName, profile.familyName AS familyName, profile.email AS email,'
100 . 'profile.mobile AS mobile, '
101 . ' (CASE WHEN (SUM(CASE WHEN auth.created >= :dateFrom THEN 1 ELSE 0 END) > 0) THEN profile.lastLogin ELSE \'none\' END) AS lastLogin, '
102 . ' SUM(CASE WHEN auth.created >= :dateFrom THEN 1 ELSE 0 END) AS numberOfLogins, '
103 . ' SUM(CASE WHEN auth.created >= :dateFrom60 THEN 1 ELSE 0 END) AS days_60, '
104 . ' SUM(CASE WHEN auth.created >= :dateFrom120 THEN 1 ELSE 0 END) AS days_120, '
105 . ' SUM(CASE WHEN auth.created >= :dateFrom180 THEN 1 ELSE 0 END) AS days_180 '
106 . ' FROM \User\Entity\Profile profile '
107 . ' LEFT JOIN profile.logins auth '
108 . ' LEFT JOIN profile.company company '
109 . ' LEFT JOIN profile.permissions permission '
110 . ' LEFT JOIN company.group grp '
111 . ' LEFT JOIN company.groupDivision division '
113 . 'GROUP BY profile.id '
114 . 'ORDER BY numberOfLogins DESC';
116 $params['dateFrom'] = new \DateTime('-30 days');
117 $params['dateFrom60'] = new \DateTime('-60 days');
118 $params['dateFrom120'] = new \DateTime('-120 days');
119 $params['dateFrom180'] = new \DateTime('-180 days');
120 $this->_queries['Date Created'] = new \DateTime();
121 $this->_queries['Date Created'] = $this->_queries['Date Created']->format('Y-m-d');
123 if (isset($this->_input['group']) && !empty($this->_input['group']) && 'null' != $this->_input['group'])
125 $this->_queries['Group'] = $this->em
126 ->find('\Company\Entity\Group', $this->_input['group'])
128 $where[] = 'grp.id = :group';
129 $params['group'] = $this->_input['group'];
131 if (isset($this->_input['groupDivision']) && !empty($this->_input['groupDivision']) && 'null' != $this->_input['groupDivision'])
133 $this->_queries['Division'] = $this->em
134 ->find('\Company\Entity\GroupDivision', $this->_input['groupDivision'])
136 $where[] = 'division.id = :division';
137 $params['division'] = $this->_input['groupDivision'];
139 if (isset($this->_input['company']) && !empty($this->_input['company']) && 'null' != $this->_input['company'])
141 $this->_queries['Dealership'] = $this->em
142 ->find('\Company\Entity\Company', $this->_input['company'])
144 $where[] = 'company.id = :company';
145 $params['company'] = $this->_input['company'];
149 $query = str_replace(array(
153 !empty($where) ? 'WHERE ' . implode(' AND ', $where) . ' ' : ' '
157 $query = $this->em->createQuery($query);
159 error_log(var_export($query->getSQL(),true));
160 !empty($params) && $query->setParameters($params);
161 $results = $query->getResult();
163 // \Utility\Debug::errorLog('$results',$results );
165 for($i = 0; $i <= count($results); $i ++)
167 // \Utility\Debug::errorLog('$results[$i]',$results[$i] );
174 if(!empty($results[$i]))
177 if('0' != $results[$i]['numberOfLogins'] && 0 != $results[$i]['numberOfLogins'] && '' != $results[$i]['numberOfLogins'])
179 $days_30 = $results[$i]['numberOfLogins'];
181 if('0' != $results[$i]['days_60'] && 0 != $results[$i]['days_60'] && '' != $results[$i]['days_60'])
183 $days_60 = $results[$i]['days_60'];
185 if('0' != $results[$i]['days_120'] && 0 != $results[$i]['days_120'] && '' != $results[$i]['days_120'])
187 $days_120 = $results[$i]['days_120'];
189 if('0' != $results[$i]['days_180'] && 0 != $results[$i]['days_180'] && '' != $results[$i]['days_180'])
191 $days_180 = $results[$i]['days_180'];
194 $days_60 = $days_60 - $days_30;
195 $days_120 = $days_120 - $days_60 - $days_30;
196 $days_180 = $days_180 - $days_120 - $days_60 - $days_30;
199 $results[$i]['days_60'] = $days_60;
200 $results[$i]['days_120'] = $days_120;
201 $results[$i]['days_180'] = $days_180;
207 $this->_data = $results;