1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/* |
4
|
|
|
* @copyright 2014 Mautic Contributors. All rights reserved |
5
|
|
|
* @author Mautic |
6
|
|
|
* |
7
|
|
|
* @link http://mautic.org |
8
|
|
|
* |
9
|
|
|
* @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html |
10
|
|
|
*/ |
11
|
|
|
|
12
|
|
|
namespace Mautic\CoreBundle\Entity; |
13
|
|
|
|
14
|
|
|
use Mautic\CoreBundle\Helper\DateTimeHelper; |
15
|
|
|
use Mautic\LeadBundle\Entity\Lead; |
16
|
|
|
use Mautic\LeadBundle\Entity\TimelineTrait; |
17
|
|
|
|
18
|
|
|
/** |
19
|
|
|
* AuditLogRepository. |
20
|
|
|
*/ |
21
|
|
|
class AuditLogRepository extends CommonRepository |
22
|
|
|
{ |
23
|
|
|
use TimelineTrait; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* @param array $filters |
27
|
|
|
* |
28
|
|
|
* @return int |
29
|
|
|
*/ |
30
|
|
|
public function getAuditLogsCount(Lead $lead, array $filters = null) |
31
|
|
|
{ |
32
|
|
|
$query = $this->_em->getConnection()->createQueryBuilder() |
33
|
|
|
->from(MAUTIC_TABLE_PREFIX.'audit_log', 'al') |
34
|
|
|
->select('count(*)') |
35
|
|
|
->where('al.object = \'lead\'') |
36
|
|
|
->andWhere('al.object_id = :id') |
37
|
|
|
->setParameter('id', $lead->getId()); |
38
|
|
|
|
39
|
|
|
if (is_array($filters) && !empty($filters['search'])) { |
40
|
|
|
$query->andWhere('al.details like \'%'.$filters['search'].'%\''); |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
if (is_array($filters) && !empty($filters['includeEvents'])) { |
44
|
|
|
$includeList = "'".implode("','", $filters['includeEvents'])."'"; |
45
|
|
|
$query->andWhere('al.action in ('.$includeList.')'); |
46
|
|
|
} |
47
|
|
|
|
48
|
|
|
if (is_array($filters) && !empty($filters['excludeEvents'])) { |
49
|
|
|
$excludeList = "'".implode("','", $filters['excludeEvents'])."'"; |
50
|
|
|
$query->andWhere('al.action not in ('.$excludeList.')'); |
51
|
|
|
} |
52
|
|
|
|
53
|
|
|
return $query->execute()->fetchColumn(); |
|
|
|
|
54
|
|
|
} |
55
|
|
|
|
56
|
|
|
/** |
57
|
|
|
* @param array $filters |
58
|
|
|
* @param int $page |
59
|
|
|
* @param int $limit |
60
|
|
|
* |
61
|
|
|
* @return array |
62
|
|
|
*/ |
63
|
|
|
public function getAuditLogs(Lead $lead, array $filters = null, array $orderBy = null, $page = 1, $limit = 25) |
64
|
|
|
{ |
65
|
|
|
$query = $this->createQueryBuilder('al') |
66
|
|
|
->select('al.userName, al.userId, al.bundle, al.object, al.objectId, al.action, al.details, al.dateAdded, al.ipAddress') |
67
|
|
|
->where('al.bundle = \'lead\'') |
68
|
|
|
->andWhere('al.object = \'lead\'') |
69
|
|
|
->andWhere('al.objectId = :id') |
70
|
|
|
->setParameter('id', $lead->getId()); |
71
|
|
|
|
72
|
|
|
if (is_array($filters) && !empty($filters['search'])) { |
73
|
|
|
$query->andWhere('al.details like \'%'.$filters['search'].'%\''); |
74
|
|
|
} |
75
|
|
|
|
76
|
|
|
if (is_array($filters) && !empty($filters['includeEvents'])) { |
77
|
|
|
$includeList = "'".implode("','", $filters['includeEvents'])."'"; |
78
|
|
|
$query->andWhere('al.action in ('.$includeList.')'); |
79
|
|
|
} |
80
|
|
|
|
81
|
|
|
if (is_array($filters) && !empty($filters['excludeEvents'])) { |
82
|
|
|
$excludeList = "'".implode("','", $filters['excludeEvents'])."'"; |
83
|
|
|
$query->andWhere('al.action not in ('.$excludeList.')'); |
84
|
|
|
} |
85
|
|
|
|
86
|
|
|
if (0 === $page) { |
87
|
|
|
$page = 1; |
88
|
|
|
} |
89
|
|
|
$query->setFirstResult(($page - 1) * $limit); |
90
|
|
|
$query->setMaxResults($limit); |
91
|
|
|
|
92
|
|
|
if (is_array($orderBy)) { |
93
|
|
|
$orderdir = 'ASC'; |
94
|
|
|
$order = 'id'; |
95
|
|
|
if (isset($orderBy[0])) { |
96
|
|
|
$order = $orderBy[0]; |
97
|
|
|
} |
98
|
|
|
if (isset($orderBy[1])) { |
99
|
|
|
$orderdir = $orderBy[1]; |
100
|
|
|
} |
101
|
|
|
if (0 !== strpos($order, 'al.')) { |
102
|
|
|
$order = 'al.'.$order; |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
$query->orderBy($order, $orderdir); |
106
|
|
|
} |
107
|
|
|
|
108
|
|
|
return $query->getQuery()->getArrayResult(); |
109
|
|
|
} |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* @param array $filters |
113
|
|
|
* @param $listOfContacts |
114
|
|
|
* |
115
|
|
|
* @return array |
116
|
|
|
*/ |
117
|
|
|
public function getAuditLogsForLeads(array $listOfContacts, array $filters = null, array $orderBy = null, $dateAdded = null) |
118
|
|
|
{ |
119
|
|
|
$query = $this->createQueryBuilder('al') |
120
|
|
|
->select('al.userName, al.userId, al.bundle, al.object, al.objectId, al.action, al.details, al.dateAdded, al.ipAddress') |
121
|
|
|
->where('al.bundle = \'lead\'') |
122
|
|
|
->andWhere('al.object = \'lead\''); |
123
|
|
|
$query |
124
|
|
|
->andWhere($query->expr()->in('al.objectId', $listOfContacts)); |
125
|
|
|
|
126
|
|
|
if (is_array($filters) && !empty($filters['search'])) { |
127
|
|
|
$query->andWhere('al.details like \'%'.$filters['search'].'%\''); |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
if (is_array($filters) && !empty($filters['includeEvents'])) { |
131
|
|
|
$includeList = "'".implode("','", $filters['includeEvents'])."'"; |
132
|
|
|
$query->andWhere('al.action in ('.$includeList.')'); |
133
|
|
|
} |
134
|
|
|
|
135
|
|
|
if ($dateAdded) { |
136
|
|
|
$query->andWhere($query->expr()->gte('al.dateAdded', ':dateAdded'))->setParameter('dateAdded', $dateAdded); |
137
|
|
|
} |
138
|
|
|
|
139
|
|
|
if (is_array($filters) && !empty($filters['excludeEvents'])) { |
140
|
|
|
$excludeList = "'".implode("','", $filters['excludeEvents'])."'"; |
141
|
|
|
$query->andWhere('al.action not in ('.$excludeList.')'); |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
if (is_array($orderBy)) { |
145
|
|
|
$orderdir = 'DESC'; |
146
|
|
|
$order = 'id'; |
147
|
|
|
if (isset($orderBy[0])) { |
148
|
|
|
$order = $orderBy[0]; |
149
|
|
|
} |
150
|
|
|
if (isset($orderBy[1])) { |
151
|
|
|
$orderdir = $orderBy[1]; |
152
|
|
|
} |
153
|
|
|
if (0 !== strpos($order, 'al.')) { |
154
|
|
|
$order = 'al.'.$order; |
155
|
|
|
} |
156
|
|
|
|
157
|
|
|
$query->orderBy($order, $orderdir); |
158
|
|
|
} |
159
|
|
|
|
160
|
|
|
return $query->getQuery()->getArrayResult(); |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* Get array of objects which belongs to the object. |
165
|
|
|
* |
166
|
|
|
* @param null $object |
167
|
|
|
* @param null $id |
168
|
|
|
* @param int $limit |
169
|
|
|
* @param null $afterDate |
170
|
|
|
* @param null $bundle |
171
|
|
|
* |
172
|
|
|
* @return array |
173
|
|
|
*/ |
174
|
|
|
public function getLogForObject($object = null, $id = null, $limit = 10, $afterDate = null, $bundle = null) |
175
|
|
|
{ |
176
|
|
|
$query = $this->createQueryBuilder('al') |
177
|
|
|
->select('al.userName, al.userId, al.bundle, al.object, al.objectId, al.action, al.details, al.dateAdded, al.ipAddress') |
178
|
|
|
->where('al.object != :category') |
179
|
|
|
->setParameter('category', 'category'); |
180
|
|
|
|
181
|
|
|
if (null != $object && null !== $id) { |
|
|
|
|
182
|
|
|
$query |
183
|
|
|
->andWhere('al.object = :object') |
184
|
|
|
->andWhere('al.objectId = :id') |
185
|
|
|
->setParameter('object', $object) |
186
|
|
|
->setParameter('id', $id); |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
if ($bundle) { |
|
|
|
|
190
|
|
|
$query->andWhere('al.bundle = :bundle') |
191
|
|
|
->setParameter('bundle', $bundle); |
192
|
|
|
} |
193
|
|
|
|
194
|
|
|
// Prevent InnoDB shared IDs |
195
|
|
|
if ($afterDate) { |
|
|
|
|
196
|
|
|
$query->andWhere( |
197
|
|
|
$query->expr()->gte('al.dateAdded', ':date') |
198
|
|
|
) |
199
|
|
|
->setParameter('date', $afterDate); |
200
|
|
|
} |
201
|
|
|
|
202
|
|
|
$query->orderBy('al.dateAdded', 'DESC') |
203
|
|
|
->setMaxResults($limit); |
204
|
|
|
|
205
|
|
|
return $query->getQuery()->getArrayResult(); |
206
|
|
|
} |
207
|
|
|
|
208
|
|
|
/** |
209
|
|
|
* @return array |
210
|
|
|
*/ |
211
|
|
|
public function getLeadIpLogs(Lead $lead = null, array $options = []) |
212
|
|
|
{ |
213
|
|
|
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
214
|
|
|
$sqb = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
215
|
|
|
|
216
|
|
|
$sqb |
217
|
|
|
->select('MAX(l.date_added) as date_added, MIN(l.id) as id, l.ip_address, l.object_id as lead_id') |
218
|
|
|
->from(MAUTIC_TABLE_PREFIX.'audit_log', 'l') |
219
|
|
|
->where( |
220
|
|
|
$sqb->expr()->andX( |
221
|
|
|
$sqb->expr()->eq('l.bundle', $sqb->expr()->literal('lead')), |
222
|
|
|
$sqb->expr()->eq('l.object', $sqb->expr()->literal('lead')), |
223
|
|
|
$sqb->expr()->eq('l.action', $sqb->expr()->literal('ipadded')) |
224
|
|
|
) |
225
|
|
|
) |
226
|
|
|
->groupBy('l.ip_address'); |
227
|
|
|
|
228
|
|
|
if ($lead instanceof Lead) { |
229
|
|
|
$dateTimeFormat = 'Y-m-d H:i:s'; |
230
|
|
|
|
231
|
|
|
// Just a check to ensure reused IDs (happens with innodb) doesn't infect data |
232
|
|
|
$dateTimeHelper = new DateTimeHelper($lead->getDateAdded(), $dateTimeFormat, 'local'); |
233
|
|
|
|
234
|
|
|
$sqb->andWhere( |
235
|
|
|
$sqb->expr()->andX( |
236
|
|
|
$sqb->expr()->eq('l.object_id', $lead->getId()), |
237
|
|
|
$sqb->expr()->gte('l.date_added', $sqb->expr()->literal($dateTimeHelper->toUtcString($dateTimeFormat))) |
238
|
|
|
) |
239
|
|
|
); |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
$qb |
243
|
|
|
->select('ip.date_added, ip.ip_address, ip.lead_id, ip.id') |
244
|
|
|
->from(sprintf('(%s)', $sqb->getSQL()), 'ip'); |
245
|
|
|
|
246
|
|
|
return $this->getTimelineResults($qb, $options, 'ip.ip_address', 'ip.date_added', [], ['date_added']); |
247
|
|
|
} |
248
|
|
|
} |
249
|
|
|
|
If the returned type also contains false, it is an indicator that maybe an error condition leading to the specific return statement remains unhandled.