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\ReportBundle\Event; |
||
13 | |||
14 | use Doctrine\DBAL\Query\Expression\ExpressionBuilder; |
||
15 | use Doctrine\DBAL\Query\QueryBuilder; |
||
16 | use Mautic\ChannelBundle\Helper\ChannelListHelper; |
||
17 | use Mautic\ReportBundle\Entity\Report; |
||
18 | use Mautic\ReportBundle\Model\ReportModel; |
||
19 | |||
20 | /** |
||
21 | * Class ReportGeneratorEvent. |
||
22 | */ |
||
23 | class ReportGeneratorEvent extends AbstractReportEvent |
||
24 | { |
||
25 | /** |
||
26 | * @var array |
||
27 | */ |
||
28 | private $selectColumns = []; |
||
29 | |||
30 | /** |
||
31 | * QueryBuilder object. |
||
32 | * |
||
33 | * @var QueryBuilder |
||
34 | */ |
||
35 | private $queryBuilder; |
||
36 | |||
37 | /** |
||
38 | * contentTemplate. |
||
39 | * |
||
40 | * @var string |
||
41 | */ |
||
42 | private $contentTemplate; |
||
43 | |||
44 | /** |
||
45 | * @var array |
||
46 | */ |
||
47 | private $options = []; |
||
48 | |||
49 | /** |
||
50 | * @var ExpressionBuilder|null |
||
51 | */ |
||
52 | private $filterExpression; |
||
53 | |||
54 | /** |
||
55 | * @var ChannelListHelper |
||
56 | */ |
||
57 | private $channelListHelper; |
||
58 | |||
59 | /** |
||
60 | * ReportGeneratorEvent constructor. |
||
61 | */ |
||
62 | public function __construct(Report $report, array $options, QueryBuilder $qb, ChannelListHelper $channelListHelper) |
||
63 | { |
||
64 | $this->report = $report; |
||
65 | $this->context = $report->getSource(); |
||
66 | $this->options = $options; |
||
67 | $this->queryBuilder = $qb; |
||
68 | $this->channelListHelper = $channelListHelper; |
||
69 | } |
||
70 | |||
71 | /** |
||
72 | * Fetch the QueryBuilder object. |
||
73 | * |
||
74 | * @return QueryBuilder |
||
75 | * |
||
76 | * @throws \RuntimeException |
||
77 | */ |
||
78 | public function getQueryBuilder() |
||
79 | { |
||
80 | if ($this->queryBuilder instanceof QueryBuilder) { |
||
81 | return $this->queryBuilder; |
||
82 | } |
||
83 | |||
84 | throw new \RuntimeException('QueryBuilder not set.'); |
||
85 | } |
||
86 | |||
87 | /** |
||
88 | * Set the QueryBuilder object. |
||
89 | * |
||
90 | * @return $this |
||
91 | */ |
||
92 | public function setQueryBuilder(QueryBuilder $queryBuilder) |
||
93 | { |
||
94 | $this->queryBuilder = $queryBuilder; |
||
95 | |||
96 | return $this; |
||
97 | } |
||
98 | |||
99 | /** |
||
100 | * Fetch the ContentTemplate path. |
||
101 | * |
||
102 | * @return QueryBuilder |
||
103 | * |
||
104 | * @throws \RuntimeException |
||
105 | */ |
||
106 | public function getContentTemplate() |
||
107 | { |
||
108 | if ($this->contentTemplate) { |
||
109 | return $this->contentTemplate; |
||
0 ignored issues
–
show
Bug
Best Practice
introduced
by
Loading history...
|
|||
110 | } |
||
111 | |||
112 | // Default content template |
||
113 | return 'MauticReportBundle:Report:details_data.html.php'; |
||
114 | } |
||
115 | |||
116 | /** |
||
117 | * Set the ContentTemplate path. |
||
118 | * |
||
119 | * @param string $contentTemplate |
||
120 | * |
||
121 | * @return $this |
||
122 | */ |
||
123 | public function setContentTemplate($contentTemplate) |
||
124 | { |
||
125 | $this->contentTemplate = $contentTemplate; |
||
126 | |||
127 | return $this; |
||
128 | } |
||
129 | |||
130 | /** |
||
131 | * @return array |
||
132 | */ |
||
133 | public function getSelectColumns() |
||
134 | { |
||
135 | return $this->selectColumns; |
||
136 | } |
||
137 | |||
138 | /** |
||
139 | * Set custom select columns with aliases based on report settings. |
||
140 | * |
||
141 | * @return $this |
||
142 | */ |
||
143 | public function setSelectColumns(array $selectColumns) |
||
144 | { |
||
145 | $this->selectColumns = $selectColumns; |
||
146 | |||
147 | return $this; |
||
148 | } |
||
149 | |||
150 | /** |
||
151 | * @return array |
||
152 | */ |
||
153 | public function getOptions() |
||
154 | { |
||
155 | return $this->options; |
||
156 | } |
||
157 | |||
158 | /** |
||
159 | * @return $this |
||
160 | */ |
||
161 | public function setOptions(array $options) |
||
162 | { |
||
163 | $this->options = array_merge($this->options, $options); |
||
164 | |||
165 | return $this; |
||
166 | } |
||
167 | |||
168 | /** |
||
169 | * @return ExpressionBuilder|null |
||
170 | */ |
||
171 | public function getFilterExpression() |
||
172 | { |
||
173 | return $this->filterExpression; |
||
174 | } |
||
175 | |||
176 | /** |
||
177 | * @return $this |
||
178 | */ |
||
179 | public function setFilterExpression(ExpressionBuilder $filterExpression) |
||
180 | { |
||
181 | $this->filterExpression = $filterExpression; |
||
182 | |||
183 | return $this; |
||
184 | } |
||
185 | |||
186 | /** |
||
187 | * Add category left join. |
||
188 | * |
||
189 | * @param $prefix |
||
190 | * |
||
191 | * @return $this |
||
192 | */ |
||
193 | public function addCategoryLeftJoin(QueryBuilder $queryBuilder, $prefix, $categoryPrefix = 'c') |
||
194 | { |
||
195 | $queryBuilder->leftJoin($prefix, MAUTIC_TABLE_PREFIX.'categories', $categoryPrefix, $categoryPrefix.'.id = '.$prefix.'.category_id'); |
||
196 | |||
197 | return $this; |
||
198 | } |
||
199 | |||
200 | /** |
||
201 | * Add lead left join. |
||
202 | * |
||
203 | * @param $prefix |
||
204 | * @param string $leadPrefix |
||
205 | * |
||
206 | * @return $this |
||
207 | */ |
||
208 | public function addLeadLeftJoin(QueryBuilder $queryBuilder, $prefix, $leadPrefix = 'l') |
||
209 | { |
||
210 | $queryBuilder->leftJoin($prefix, MAUTIC_TABLE_PREFIX.'leads', $leadPrefix, $leadPrefix.'.id = '.$prefix.'.lead_id'); |
||
211 | |||
212 | return $this; |
||
213 | } |
||
214 | |||
215 | /** |
||
216 | * Add IP left join. |
||
217 | * |
||
218 | * @param $prefix |
||
219 | * @param string $ipPrefix |
||
220 | * |
||
221 | * @return $this |
||
222 | */ |
||
223 | public function addIpAddressLeftJoin(QueryBuilder $queryBuilder, $prefix, $ipPrefix = 'i') |
||
224 | { |
||
225 | $queryBuilder->leftJoin($prefix, MAUTIC_TABLE_PREFIX.'ip_addresses', $ipPrefix, $ipPrefix.'.id = '.$prefix.'.ip_id'); |
||
226 | |||
227 | return $this; |
||
228 | } |
||
229 | |||
230 | /** |
||
231 | * Add IP left join with lead join. |
||
232 | * |
||
233 | * @param string $ipXrefPrefix |
||
234 | * @param string $ipPrefix |
||
235 | * @param string $leadPrefix |
||
236 | * |
||
237 | * @return $this |
||
238 | */ |
||
239 | public function addLeadIpAddressLeftJoin(QueryBuilder $queryBuilder, $ipXrefPrefix = 'lip', $ipPrefix = 'i', $leadPrefix = 'l') |
||
240 | { |
||
241 | $this->addIpAddressLeftJoin($queryBuilder, $ipXrefPrefix, $ipPrefix); |
||
242 | $queryBuilder->leftJoin($leadPrefix, MAUTIC_TABLE_PREFIX.'lead_ips_xref', $ipXrefPrefix, $ipXrefPrefix.'.lead_id = '.$leadPrefix.'.id'); |
||
243 | |||
244 | return $this; |
||
245 | } |
||
246 | |||
247 | /** |
||
248 | * Add IP left join. |
||
249 | * |
||
250 | * @param $prefix |
||
251 | * @param string $leadPrefix |
||
252 | * |
||
253 | * @return $this |
||
254 | */ |
||
255 | public function addCampaignByChannelJoin(QueryBuilder $queryBuilder, $prefix, $channel, $leadPrefix = 'l') |
||
256 | { |
||
257 | $options = $this->getOptions(); |
||
258 | $cmpName = 'cmp.name'; |
||
259 | $cmpId = 'clel.campaign_id'; |
||
260 | |||
261 | if ($this->hasColumn($cmpName) |
||
262 | || $this->hasFilter($cmpName) |
||
263 | || $this->hasGroupByColumn($cmpName) |
||
264 | || $this->hasColumn($cmpId) |
||
265 | || $this->hasFilter($cmpId) |
||
266 | || $this->hasGroupByColumn($cmpId) |
||
267 | || (!empty($options['order'][0] |
||
268 | && ($options['order'][0] === $cmpName |
||
269 | || $options['order'][0] === $cmpId)))) { |
||
270 | $queryBuilder->leftJoin($prefix, MAUTIC_TABLE_PREFIX.'campaign_lead_event_log', 'clel', sprintf('clel.channel="%s" AND %s.id = clel.channel_id AND clel.lead_id = %s.id', $channel, $prefix, $leadPrefix)) |
||
271 | ->leftJoin('clel', MAUTIC_TABLE_PREFIX.'campaigns', 'cmp', 'cmp.id = clel.campaign_id'); |
||
272 | } |
||
273 | |||
274 | return $this; |
||
275 | } |
||
276 | |||
277 | /** |
||
278 | * Join channel columns. |
||
279 | * |
||
280 | * @param $prefix |
||
281 | * |
||
282 | * @return $this |
||
283 | */ |
||
284 | public function addChannelLeftJoins(QueryBuilder $queryBuilder, $prefix) |
||
285 | { |
||
286 | foreach ($this->channelListHelper->getChannels() as $channel => $details) { |
||
287 | if (!array_key_exists(ReportModel::CHANNEL_FEATURE, $details)) { |
||
288 | continue; |
||
289 | } |
||
290 | |||
291 | $reportDetails = $details[ReportModel::CHANNEL_FEATURE]; |
||
292 | |||
293 | if (!array_key_exists('table', $reportDetails)) { |
||
294 | continue; |
||
295 | } |
||
296 | |||
297 | $channelParameter = 'channelParameter'.$channel; |
||
298 | |||
299 | $queryBuilder->leftJoin( |
||
300 | $prefix, |
||
301 | MAUTIC_TABLE_PREFIX.$reportDetails['table'], |
||
302 | $channel, |
||
303 | $prefix.'.channel_id = '.$channel.'.id AND '.$prefix.'.channel = :'.$channelParameter |
||
304 | ); |
||
305 | |||
306 | $queryBuilder->setParameter($channelParameter, $channel); |
||
307 | } |
||
308 | |||
309 | return $this; |
||
310 | } |
||
311 | |||
312 | /** |
||
313 | * Add company left join. |
||
314 | */ |
||
315 | public function addCompanyLeftJoin(QueryBuilder $queryBuilder) |
||
316 | { |
||
317 | $queryBuilder->leftJoin('l', MAUTIC_TABLE_PREFIX.'companies_leads', 'companies_lead', 'l.id = companies_lead.lead_id'); |
||
318 | $queryBuilder->leftJoin('companies_lead', MAUTIC_TABLE_PREFIX.'companies', 'comp', 'companies_lead.company_id = comp.id'); |
||
319 | } |
||
320 | |||
321 | /** |
||
322 | * Apply date filters to the query. |
||
323 | * |
||
324 | * @param string $dateColumn |
||
325 | * @param string $tablePrefix |
||
326 | * |
||
327 | * @return $this |
||
328 | */ |
||
329 | public function applyDateFilters(QueryBuilder $queryBuilder, $dateColumn, $tablePrefix = 't', $dateOnly = false) |
||
330 | { |
||
331 | if ($tablePrefix) { |
||
332 | $tablePrefix .= '.'; |
||
333 | } |
||
334 | |||
335 | if (empty($this->options['dateFrom'])) { |
||
336 | $this->options['dateFrom'] = new \DateTime(); |
||
337 | $this->options['dateFrom']->modify('-30 days'); |
||
338 | } |
||
339 | |||
340 | if (empty($this->options['dateTo'])) { |
||
341 | $this->options['dateTo'] = new \DateTime(); |
||
342 | } |
||
343 | |||
344 | if ($dateOnly) { |
||
345 | $queryBuilder->andWhere(sprintf('%1$s IS NULL OR (DATE(%1$s) BETWEEN :dateFrom AND :dateTo)', $tablePrefix.$dateColumn)); |
||
346 | $queryBuilder->setParameter('dateFrom', $this->options['dateFrom']->format('Y-m-d')); |
||
347 | $queryBuilder->setParameter('dateTo', $this->options['dateTo']->format('Y-m-d')); |
||
348 | } else { |
||
349 | $queryBuilder->andWhere(sprintf('%1$s IS NULL OR (%1$s BETWEEN :dateFrom AND :dateTo)', $tablePrefix.$dateColumn)); |
||
350 | $queryBuilder->setParameter('dateFrom', $this->options['dateFrom']->format('Y-m-d H:i:s')); |
||
351 | $queryBuilder->setParameter('dateTo', $this->options['dateTo']->format('Y-m-d H:i:s')); |
||
352 | } |
||
353 | |||
354 | return $this; |
||
355 | } |
||
356 | |||
357 | /** |
||
358 | * Check if the report has a specific column. |
||
359 | * |
||
360 | * @param array|string $column |
||
361 | * |
||
362 | * @return bool |
||
363 | */ |
||
364 | public function hasColumn($column) |
||
365 | { |
||
366 | $columns = $this->getReport()->getSelectAndAggregatorAndOrderAndGroupByColumns(); |
||
367 | |||
368 | if (is_array($column)) { |
||
369 | foreach ($column as $checkMe) { |
||
370 | if (in_array($checkMe, $columns, true)) { |
||
371 | return true; |
||
372 | } |
||
373 | } |
||
374 | |||
375 | return false; |
||
376 | } |
||
377 | |||
378 | return in_array($column, $columns, true); |
||
379 | } |
||
380 | |||
381 | /** |
||
382 | * Check if the report has a specific filter. |
||
383 | * |
||
384 | * @param array|string $column |
||
385 | * |
||
386 | * @return bool |
||
387 | */ |
||
388 | public function hasFilter($column) |
||
389 | { |
||
390 | static $sorted; |
||
391 | |||
392 | if (null == $sorted) { |
||
393 | $filters = $this->getReport()->getFilters(); |
||
394 | |||
395 | foreach ($filters as $field) { |
||
396 | $sorted[$field['column']] = true; |
||
397 | } |
||
398 | } |
||
399 | |||
400 | if (is_array($column)) { |
||
401 | foreach ($column as $checkMe) { |
||
402 | if (isset($sorted[$checkMe])) { |
||
403 | return true; |
||
404 | } |
||
405 | } |
||
406 | |||
407 | return false; |
||
408 | } |
||
409 | |||
410 | return isset($sorted[$column]); |
||
411 | } |
||
412 | |||
413 | /** |
||
414 | * Get filter value from a specific filter. |
||
415 | * |
||
416 | * @param string $column |
||
417 | * |
||
418 | * @return mixed |
||
419 | * |
||
420 | * @throws \UnexpectedValueException |
||
421 | */ |
||
422 | public function getFilterValue($column) |
||
423 | { |
||
424 | return $this->getReport()->getFilterValue($column); |
||
425 | } |
||
426 | |||
427 | /** |
||
428 | * Get filter values from a specific filter. |
||
429 | * |
||
430 | * @param string $column |
||
431 | * |
||
432 | * @return array |
||
433 | * |
||
434 | * @throws \UnexpectedValueException |
||
435 | */ |
||
436 | public function getFilterValues($column) |
||
437 | { |
||
438 | return $this->getReport()->getFilterValues($column); |
||
439 | } |
||
440 | |||
441 | /** |
||
442 | * Check if the report has a groupBy columns selected. |
||
443 | * |
||
444 | * @return bool |
||
445 | */ |
||
446 | public function hasGroupBy() |
||
447 | { |
||
448 | if (!empty($this->getReport()->getGroupBy())) { |
||
449 | return true; |
||
450 | } |
||
451 | |||
452 | return false; |
||
453 | } |
||
454 | |||
455 | /** |
||
456 | * Check if the report has a specific column. |
||
457 | * |
||
458 | * @param string $column |
||
459 | * |
||
460 | * @return bool |
||
461 | */ |
||
462 | private function hasGroupByColumn($column) |
||
463 | { |
||
464 | return in_array($column, $this->getReport()->getGroupBy(), true); |
||
465 | } |
||
466 | |||
467 | /** |
||
468 | * @return string |
||
469 | */ |
||
470 | public function createParameterName() |
||
471 | { |
||
472 | $alpha_numeric = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'; |
||
473 | |||
474 | return substr(str_shuffle($alpha_numeric), 0, 8); |
||
475 | } |
||
476 | } |
||
477 |