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\CampaignBundle\Entity; |
||
13 | |||
14 | use Doctrine\DBAL\Types\Type; |
||
15 | use Doctrine\ORM\Query\Expr; |
||
16 | use Mautic\CampaignBundle\Entity\Result\CountResult; |
||
17 | use Mautic\CampaignBundle\Executioner\ContactFinder\Limiter\ContactLimiter; |
||
18 | use Mautic\CoreBundle\Entity\CommonRepository; |
||
19 | |||
20 | class CampaignRepository extends CommonRepository |
||
21 | { |
||
22 | use ContactLimiterTrait; |
||
23 | use SlaveConnectionTrait; |
||
24 | |||
25 | /** |
||
26 | * {@inheritdoc} |
||
27 | */ |
||
28 | public function getEntities(array $args = []) |
||
29 | { |
||
30 | $q = $this->getEntityManager() |
||
31 | ->createQueryBuilder() |
||
32 | ->select($this->getTableAlias().', cat') |
||
33 | ->from('MauticCampaignBundle:Campaign', $this->getTableAlias(), $this->getTableAlias().'.id') |
||
34 | ->leftJoin($this->getTableAlias().'.category', 'cat'); |
||
35 | |||
36 | if (!empty($args['joinLists'])) { |
||
37 | $q->leftJoin($this->getTableAlias().'.lists', 'l'); |
||
38 | } |
||
39 | |||
40 | if (!empty($args['joinForms'])) { |
||
41 | $q->leftJoin($this->getTableAlias().'.forms', 'f'); |
||
42 | } |
||
43 | |||
44 | $args['qb'] = $q; |
||
45 | |||
46 | return parent::getEntities($args); |
||
47 | } |
||
48 | |||
49 | /** |
||
50 | * {@inheritdoc} |
||
51 | * |
||
52 | * @param object $entity |
||
53 | * @param bool $flush |
||
54 | */ |
||
55 | public function deleteEntity($entity, $flush = true) |
||
56 | { |
||
57 | // Null parents of associated events first |
||
58 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
59 | $q->update(MAUTIC_TABLE_PREFIX.'campaign_events') |
||
60 | ->set('parent_id', ':null') |
||
61 | ->setParameter('null', null) |
||
62 | ->where('campaign_id = '.$entity->getId()) |
||
63 | ->execute(); |
||
64 | |||
65 | // Delete events |
||
66 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
67 | $q->delete(MAUTIC_TABLE_PREFIX.'campaign_events') |
||
68 | ->where('campaign_id = '.$entity->getId()) |
||
69 | ->execute(); |
||
70 | |||
71 | parent::deleteEntity($entity, $flush); |
||
72 | } |
||
73 | |||
74 | /** |
||
75 | * Returns a list of all published (and active) campaigns (optionally for a specific lead). |
||
76 | * |
||
77 | * @param null $specificId |
||
78 | * @param null $leadId |
||
79 | * @param bool $forList If true, returns ID and name only |
||
80 | * @param bool $viewOther If true, returns all the campaigns |
||
81 | * |
||
82 | * @return array |
||
83 | */ |
||
84 | public function getPublishedCampaigns($specificId = null, $leadId = null, $forList = false, $viewOther = false) |
||
85 | { |
||
86 | $q = $this->getEntityManager()->createQueryBuilder() |
||
87 | ->from('MauticCampaignBundle:Campaign', 'c', 'c.id'); |
||
88 | |||
89 | if ($forList && $leadId) { |
||
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
90 | $q->select('partial c.{id, name}, partial l.{campaign, lead, dateAdded, manuallyAdded, manuallyRemoved}, partial ll.{id}'); |
||
91 | } elseif ($forList) { |
||
92 | $q->select('partial c.{id, name}, partial ll.{id}'); |
||
93 | } else { |
||
94 | $q->select('c, l, partial ll.{id}') |
||
95 | ->leftJoin('c.events', 'e') |
||
96 | ->leftJoin('e.log', 'o'); |
||
97 | } |
||
98 | |||
99 | if ($leadId || !$forList) { |
||
100 | $q->leftJoin('c.leads', 'l'); |
||
101 | } |
||
102 | |||
103 | $q->leftJoin('c.lists', 'll') |
||
104 | ->where($this->getPublishedByDateExpression($q)); |
||
105 | |||
106 | if (!$viewOther) { |
||
107 | $q->andWhere($q->expr()->eq('c.createdBy', ':id')) |
||
108 | ->setParameter('id', $this->currentUser->getId()); |
||
109 | } |
||
110 | |||
111 | if (!empty($specificId)) { |
||
112 | $q->andWhere( |
||
113 | $q->expr()->eq('c.id', (int) $specificId) |
||
114 | ); |
||
115 | } |
||
116 | |||
117 | if (!empty($leadId)) { |
||
118 | $q->andWhere( |
||
119 | $q->expr()->eq('IDENTITY(l.lead)', (int) $leadId) |
||
120 | ); |
||
121 | $q->andWhere( |
||
122 | $q->expr()->eq('l.manuallyRemoved', ':manuallyRemoved') |
||
123 | )->setParameter('manuallyRemoved', false); |
||
124 | } |
||
125 | |||
126 | return $q->getQuery()->getArrayResult(); |
||
127 | } |
||
128 | |||
129 | /** |
||
130 | * Returns a list of all published (and active) campaigns that specific lead lists are part of. |
||
131 | * |
||
132 | * @param int|array $leadLists |
||
133 | * |
||
134 | * @return array |
||
135 | */ |
||
136 | public function getPublishedCampaignsByLeadLists($leadLists, $viewOther = false) |
||
137 | { |
||
138 | if (!is_array($leadLists)) { |
||
139 | $leadLists = [(int) $leadLists]; |
||
140 | } else { |
||
141 | foreach ($leadLists as &$id) { |
||
142 | $id = (int) $id; |
||
143 | } |
||
144 | } |
||
145 | |||
146 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder() |
||
147 | ->select('c.id, c.name, ll.leadlist_id as list_id') |
||
148 | ->from(MAUTIC_TABLE_PREFIX.'campaigns', 'c'); |
||
149 | |||
150 | $q->join('c', MAUTIC_TABLE_PREFIX.'campaign_leadlist_xref', 'll', 'c.id = ll.campaign_id') |
||
151 | ->where($this->getPublishedByDateExpression($q)); |
||
152 | |||
153 | $q->andWhere( |
||
154 | $q->expr()->in('ll.leadlist_id', $leadLists) |
||
155 | ); |
||
156 | |||
157 | if (!$viewOther) { |
||
158 | $q->andWhere($q->expr()->eq('c.created_by', ':id')) |
||
159 | ->setParameter('id', $this->currentUser->getId()); |
||
160 | } |
||
161 | |||
162 | $results = $q->execute()->fetchAll(); |
||
163 | |||
164 | $campaigns = []; |
||
165 | foreach ($results as $result) { |
||
166 | if (!isset($campaigns[$result['id']])) { |
||
167 | $campaigns[$result['id']] = [ |
||
168 | 'id' => $result['id'], |
||
169 | 'name' => $result['name'], |
||
170 | 'lists' => [], |
||
171 | ]; |
||
172 | } |
||
173 | |||
174 | $campaigns[$result['id']]['lists'][$result['list_id']] = [ |
||
175 | 'id' => $result['list_id'], |
||
176 | ]; |
||
177 | } |
||
178 | |||
179 | return $campaigns; |
||
180 | } |
||
181 | |||
182 | /** |
||
183 | * Get array of list IDs assigned to this campaign. |
||
184 | * |
||
185 | * @param null $id |
||
186 | * |
||
187 | * @return array |
||
188 | */ |
||
189 | public function getCampaignListIds($id = null) |
||
190 | { |
||
191 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder() |
||
192 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leadlist_xref', 'cl'); |
||
193 | |||
194 | if ($id) { |
||
0 ignored issues
–
show
|
|||
195 | $q->select('cl.leadlist_id') |
||
196 | ->where( |
||
197 | $q->expr()->eq('cl.campaign_id', $id) |
||
198 | ); |
||
199 | } else { |
||
200 | // Retrieve a list of unique IDs that are assigned to a campaign |
||
201 | $q->select('DISTINCT cl.leadlist_id'); |
||
202 | } |
||
203 | |||
204 | $lists = []; |
||
205 | $results = $q->execute()->fetchAll(); |
||
206 | |||
207 | foreach ($results as $r) { |
||
208 | $lists[] = $r['leadlist_id']; |
||
209 | } |
||
210 | |||
211 | return $lists; |
||
212 | } |
||
213 | |||
214 | /** |
||
215 | * Get array of list IDs => name assigned to this campaign. |
||
216 | * |
||
217 | * @param null $id |
||
218 | * |
||
219 | * @return array |
||
220 | */ |
||
221 | public function getCampaignListSources($id) |
||
222 | { |
||
223 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder() |
||
224 | ->select('cl.leadlist_id, l.name') |
||
225 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leadlist_xref', 'cl') |
||
226 | ->join('cl', MAUTIC_TABLE_PREFIX.'lead_lists', 'l', 'l.id = cl.leadlist_id'); |
||
227 | $q->where( |
||
228 | $q->expr()->eq('cl.campaign_id', $id) |
||
229 | ); |
||
230 | |||
231 | $lists = []; |
||
232 | $results = $q->execute()->fetchAll(); |
||
233 | |||
234 | foreach ($results as $r) { |
||
235 | $lists[$r['leadlist_id']] = $r['name']; |
||
236 | } |
||
237 | |||
238 | return $lists; |
||
239 | } |
||
240 | |||
241 | /** |
||
242 | * Get array of form IDs => name assigned to this campaign. |
||
243 | * |
||
244 | * @param $id |
||
245 | * |
||
246 | * @return array |
||
247 | */ |
||
248 | public function getCampaignFormSources($id) |
||
249 | { |
||
250 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder() |
||
251 | ->select('cf.form_id, f.name') |
||
252 | ->from(MAUTIC_TABLE_PREFIX.'campaign_form_xref', 'cf') |
||
253 | ->join('cf', MAUTIC_TABLE_PREFIX.'forms', 'f', 'f.id = cf.form_id'); |
||
254 | $q->where( |
||
255 | $q->expr()->eq('cf.campaign_id', $id) |
||
256 | ); |
||
257 | |||
258 | $forms = []; |
||
259 | $results = $q->execute()->fetchAll(); |
||
260 | |||
261 | foreach ($results as $r) { |
||
262 | $forms[$r['form_id']] = $r['name']; |
||
263 | } |
||
264 | |||
265 | return $forms; |
||
266 | } |
||
267 | |||
268 | /** |
||
269 | * @param $formId |
||
270 | * |
||
271 | * @return array |
||
272 | */ |
||
273 | public function findByFormId($formId) |
||
274 | { |
||
275 | $q = $this->createQueryBuilder('c') |
||
276 | ->join('c.forms', 'f'); |
||
277 | $q->where( |
||
278 | $q->expr()->eq('f.id', $formId) |
||
279 | ); |
||
280 | |||
281 | return $q->getQuery()->getResult(); |
||
282 | } |
||
283 | |||
284 | /** |
||
285 | * @return string |
||
286 | */ |
||
287 | public function getTableAlias() |
||
288 | { |
||
289 | return 'c'; |
||
290 | } |
||
291 | |||
292 | /** |
||
293 | * @param \Doctrine\ORM\QueryBuilder|\Doctrine\DBAL\Query\QueryBuilder $q |
||
294 | * @param $filter |
||
295 | * |
||
296 | * @return array |
||
297 | */ |
||
298 | protected function addCatchAllWhereClause($q, $filter) |
||
299 | { |
||
300 | return $this->addStandardCatchAllWhereClause($q, $filter, [ |
||
301 | 'c.name', |
||
302 | 'c.description', |
||
303 | ]); |
||
304 | } |
||
305 | |||
306 | /** |
||
307 | * @param \Doctrine\ORM\QueryBuilder|\Doctrine\DBAL\Query\QueryBuilder $q |
||
308 | * @param $filter |
||
309 | * |
||
310 | * @return array |
||
311 | */ |
||
312 | protected function addSearchCommandWhereClause($q, $filter) |
||
313 | { |
||
314 | return $this->addStandardSearchCommandWhereClause($q, $filter); |
||
315 | } |
||
316 | |||
317 | /** |
||
318 | * @return array |
||
319 | */ |
||
320 | public function getSearchCommands() |
||
321 | { |
||
322 | return $this->getStandardSearchCommands(); |
||
323 | } |
||
324 | |||
325 | /** |
||
326 | * Get a list of popular (by logs) campaigns. |
||
327 | * |
||
328 | * @param int $limit |
||
329 | * |
||
330 | * @return array |
||
331 | */ |
||
332 | public function getPopularCampaigns($limit = 10) |
||
333 | { |
||
334 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
335 | |||
336 | $q->select('count(cl.ip_id) as hits, c.id AS campaign_id, c.name') |
||
337 | ->from(MAUTIC_TABLE_PREFIX.'campaign_lead_event_log', 'cl') |
||
338 | ->leftJoin('cl', MAUTIC_TABLE_PREFIX.'campaigns', 'c', 'cl.campaign_id = c.id') |
||
339 | ->orderBy('hits', 'DESC') |
||
340 | ->groupBy('c.id, c.name') |
||
341 | ->setMaxResults($limit); |
||
342 | |||
343 | $expr = $this->getPublishedByDateExpression($q, 'c'); |
||
344 | $q->where($expr); |
||
345 | |||
346 | return $q->execute()->fetchAll(); |
||
347 | } |
||
348 | |||
349 | /** |
||
350 | * @param $campaignId |
||
351 | * |
||
352 | * @return CountResult |
||
353 | */ |
||
354 | public function getCountsForPendingContacts($campaignId, array $pendingEvents, ContactLimiter $limiter) |
||
355 | { |
||
356 | $q = $this->getSlaveConnection($limiter)->createQueryBuilder(); |
||
357 | |||
358 | $q->select('min(cl.lead_id) as min_id, max(cl.lead_id) as max_id, count(cl.lead_id) as the_count') |
||
359 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leads', 'cl') |
||
360 | ->where( |
||
361 | $q->expr()->andX( |
||
362 | $q->expr()->eq('cl.campaign_id', (int) $campaignId), |
||
363 | $q->expr()->eq('cl.manually_removed', ':false') |
||
364 | ) |
||
365 | ) |
||
366 | ->setParameter('false', false, 'boolean'); |
||
367 | |||
368 | $this->updateQueryFromContactLimiter('cl', $q, $limiter, true); |
||
369 | |||
370 | if (count($pendingEvents) > 0) { |
||
371 | $sq = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
372 | $sq->select('null') |
||
373 | ->from(MAUTIC_TABLE_PREFIX.'campaign_lead_event_log', 'e') |
||
374 | ->where( |
||
375 | $sq->expr()->andX( |
||
376 | $sq->expr()->eq('cl.lead_id', 'e.lead_id'), |
||
377 | $sq->expr()->eq('e.rotation', 'cl.rotation'), |
||
378 | $sq->expr()->in('e.event_id', $pendingEvents) |
||
379 | ) |
||
380 | ); |
||
381 | |||
382 | $q->andWhere( |
||
383 | sprintf('NOT EXISTS (%s)', $sq->getSQL()) |
||
384 | ); |
||
385 | } |
||
386 | |||
387 | $result = $q->execute()->fetch(); |
||
388 | |||
389 | return new CountResult($result['the_count'], $result['min_id'], $result['max_id']); |
||
390 | } |
||
391 | |||
392 | /** |
||
393 | * Get pending contact IDs for a campaign. |
||
394 | * |
||
395 | * @param $campaignId |
||
396 | * |
||
397 | * @return array |
||
398 | */ |
||
399 | public function getPendingContactIds($campaignId, ContactLimiter $limiter) |
||
400 | { |
||
401 | if ($limiter->hasCampaignLimit() && 0 === $limiter->getCampaignLimitRemaining()) { |
||
402 | return []; |
||
403 | } |
||
404 | |||
405 | $q = $this->getSlaveConnection($limiter)->createQueryBuilder(); |
||
406 | |||
407 | $q->select('cl.lead_id') |
||
408 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leads', 'cl') |
||
409 | ->where( |
||
410 | $q->expr()->andX( |
||
411 | $q->expr()->eq('cl.campaign_id', (int) $campaignId), |
||
412 | $q->expr()->eq('cl.manually_removed', ':false') |
||
413 | ) |
||
414 | ) |
||
415 | ->setParameter('false', false, 'boolean') |
||
416 | ->orderBy('cl.lead_id', 'ASC'); |
||
417 | |||
418 | $this->updateQueryFromContactLimiter('cl', $q, $limiter); |
||
419 | |||
420 | // Only leads that have not started the campaign |
||
421 | $sq = $this->getSlaveConnection($limiter)->createQueryBuilder(); |
||
422 | $sq->select('null') |
||
423 | ->from(MAUTIC_TABLE_PREFIX.'campaign_lead_event_log', 'e') |
||
424 | ->where( |
||
425 | $sq->expr()->andX( |
||
426 | $sq->expr()->eq('e.lead_id', 'cl.lead_id'), |
||
427 | $sq->expr()->eq('e.campaign_id', (int) $campaignId), |
||
428 | $sq->expr()->eq('e.rotation', 'cl.rotation') |
||
429 | ) |
||
430 | ); |
||
431 | |||
432 | $q->andWhere( |
||
433 | sprintf('NOT EXISTS (%s)', $sq->getSQL()) |
||
434 | ); |
||
435 | |||
436 | if ($limiter->hasCampaignLimit() && $limiter->getCampaignLimitRemaining() < $limiter->getBatchLimit()) { |
||
437 | $q->setMaxResults($limiter->getCampaignLimitRemaining()); |
||
438 | } |
||
439 | |||
440 | $results = $q->execute()->fetchAll(); |
||
441 | $leads = []; |
||
442 | foreach ($results as $r) { |
||
443 | $leads[] = $r['lead_id']; |
||
444 | } |
||
445 | unset($results); |
||
446 | |||
447 | if ($limiter->hasCampaignLimit()) { |
||
448 | $limiter->reduceCampaignLimitRemaining(count($leads)); |
||
449 | } |
||
450 | |||
451 | return $leads; |
||
452 | } |
||
453 | |||
454 | /** |
||
455 | * Get a count of leads that belong to the campaign. |
||
456 | * |
||
457 | * @param int $campaignId |
||
458 | * @param int $leadId Optional lead ID to check if lead is part of campaign |
||
459 | * @param array $pendingEvents List of specific events to rule out |
||
460 | * |
||
461 | * @return int |
||
462 | */ |
||
463 | public function getCampaignLeadCount($campaignId, $leadId = null, $pendingEvents = []) |
||
464 | { |
||
465 | $q = $this->getSlaveConnection()->createQueryBuilder(); |
||
466 | |||
467 | $q->select('count(cl.lead_id) as lead_count') |
||
468 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leads', 'cl') |
||
469 | ->where( |
||
470 | $q->expr()->andX( |
||
471 | $q->expr()->eq('cl.campaign_id', (int) $campaignId), |
||
472 | $q->expr()->eq('cl.manually_removed', ':false') |
||
473 | ) |
||
474 | ) |
||
475 | ->setParameter('false', false, Type::BOOLEAN); |
||
476 | |||
477 | if ($leadId) { |
||
478 | $q->andWhere( |
||
479 | $q->expr()->eq('cl.lead_id', (int) $leadId) |
||
480 | ); |
||
481 | } |
||
482 | |||
483 | if (count($pendingEvents) > 0) { |
||
484 | $sq = $this->getSlaveConnection()->createQueryBuilder(); |
||
485 | $sq->select('null') |
||
486 | ->from(MAUTIC_TABLE_PREFIX.'campaign_lead_event_log', 'e') |
||
487 | ->where( |
||
488 | $sq->expr()->andX( |
||
489 | $sq->expr()->eq('cl.lead_id', 'e.lead_id'), |
||
490 | $sq->expr()->in('e.event_id', $pendingEvents) |
||
491 | ) |
||
492 | ); |
||
493 | |||
494 | $q->andWhere( |
||
495 | sprintf('NOT EXISTS (%s)', $sq->getSQL()) |
||
496 | ); |
||
497 | } |
||
498 | |||
499 | $results = $q->execute()->fetchAll(); |
||
500 | |||
501 | return (int) $results[0]['lead_count']; |
||
502 | } |
||
503 | |||
504 | /** |
||
505 | * Get lead data of a campaign. |
||
506 | * |
||
507 | * @param $campaignId |
||
508 | * @param int $start |
||
509 | * @param bool|false $limit |
||
510 | * @param array $select |
||
511 | * |
||
512 | * @return mixed |
||
513 | */ |
||
514 | public function getCampaignLeads($campaignId, $start = 0, $limit = false, $select = ['cl.lead_id']) |
||
515 | { |
||
516 | $q = $this->getSlaveConnection()->createQueryBuilder(); |
||
517 | |||
518 | $q->select($select) |
||
519 | ->from(MAUTIC_TABLE_PREFIX.'campaign_leads', 'cl') |
||
520 | ->where( |
||
521 | $q->expr()->andX( |
||
522 | $q->expr()->eq('cl.campaign_id', (int) $campaignId), |
||
523 | $q->expr()->eq('cl.manually_removed', ':false') |
||
524 | ) |
||
525 | ) |
||
526 | ->setParameter('false', false, 'boolean') |
||
527 | ->orderBy('cl.lead_id', 'ASC'); |
||
528 | |||
529 | if (!empty($limit)) { |
||
530 | $q->setFirstResult($start) |
||
531 | ->setMaxResults($limit); |
||
532 | } |
||
533 | |||
534 | return $q->execute()->fetchAll(); |
||
535 | } |
||
536 | |||
537 | /** |
||
538 | * @param $contactId |
||
539 | * @param $campaignId |
||
540 | * |
||
541 | * @return mixed |
||
542 | */ |
||
543 | public function getContactSingleSegmentByCampaign($contactId, $campaignId) |
||
544 | { |
||
545 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
546 | |||
547 | return $q->select('ll.id, ll.name') |
||
548 | ->from(MAUTIC_TABLE_PREFIX.'lead_lists', 'll') |
||
549 | ->join('ll', MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'lll', 'lll.leadlist_id = ll.id and lll.lead_id = :contactId and lll.manually_removed = 0') |
||
550 | ->join('ll', MAUTIC_TABLE_PREFIX.'campaign_leadlist_xref', 'clx', 'clx.leadlist_id = ll.id and clx.campaign_id = :campaignId') |
||
551 | ->setParameter('contactId', (int) $contactId) |
||
552 | ->setParameter('campaignId', (int) $campaignId) |
||
553 | ->setMaxResults(1) |
||
554 | ->execute() |
||
555 | ->fetch(); |
||
556 | } |
||
557 | |||
558 | /** |
||
559 | * @param int $segmentId |
||
560 | * @param array $campaignIds |
||
561 | * |
||
562 | * @return array |
||
563 | */ |
||
564 | public function getCampaignsSegmentShare($segmentId, $campaignIds = []) |
||
565 | { |
||
566 | $q = $this->getEntityManager()->getConnection()->createQueryBuilder(); |
||
567 | $q->select('c.id, c.name, ROUND(IFNULL(COUNT(DISTINCT t.lead_id)/COUNT(DISTINCT cl.lead_id)*100, 0),1) segmentCampaignShare'); |
||
568 | $q->from(MAUTIC_TABLE_PREFIX.'campaigns', 'c') |
||
569 | ->leftJoin('c', MAUTIC_TABLE_PREFIX.'campaign_leads', 'cl', 'cl.campaign_id = c.id AND cl.manually_removed = 0') |
||
570 | ->leftJoin('cl', |
||
571 | '(SELECT lll.lead_id AS ll, lll.lead_id FROM lead_lists_leads lll WHERE lll.leadlist_id = '.$segmentId |
||
572 | .' AND lll.manually_removed = 0)', |
||
573 | 't', |
||
574 | 't.lead_id = cl.lead_id' |
||
575 | ); |
||
576 | $q->groupBy('c.id'); |
||
577 | |||
578 | if (!empty($campaignIds)) { |
||
579 | $q->where($q->expr()->in('c.id', $campaignIds)); |
||
580 | } |
||
581 | |||
582 | return $q->execute()->fetchAll(); |
||
583 | } |
||
584 | |||
585 | /** |
||
586 | * Searches for emails assigned to campaign and returns associative array of email ids in format:. |
||
587 | * |
||
588 | * array (size=1) |
||
589 | * 0 => |
||
590 | * array (size=2) |
||
591 | * 'channelId' => int 18 |
||
592 | * |
||
593 | * or empty array if nothing found. |
||
594 | * |
||
595 | * @param int $id |
||
596 | * |
||
597 | * @return array |
||
598 | */ |
||
599 | public function fetchEmailIdsById($id) |
||
600 | { |
||
601 | $emails = $this->getEntityManager() |
||
602 | ->createQueryBuilder() |
||
603 | ->select('e.channelId') |
||
604 | ->from('MauticCampaignBundle:Campaign', $this->getTableAlias(), $this->getTableAlias().'.id') |
||
605 | ->leftJoin( |
||
606 | $this->getTableAlias().'.events', |
||
607 | 'e', |
||
608 | Expr\Join::WITH, |
||
609 | "e.channel = '".Event::CHANNEL_EMAIL."'" |
||
610 | ) |
||
611 | ->where($this->getTableAlias().'.id = :id') |
||
612 | ->setParameter('id', $id) |
||
613 | ->andWhere('e.channelId IS NOT NULL') |
||
614 | ->getQuery() |
||
615 | ->setHydrationMode(\Doctrine\ORM\Query::HYDRATE_ARRAY) |
||
616 | ->getResult(); |
||
617 | |||
618 | $return = []; |
||
619 | foreach ($emails as $email) { |
||
620 | // Every channelId represents e-mail ID |
||
621 | $return[] = $email['channelId']; // mautic_campaign_events.channel_id |
||
622 | } |
||
623 | |||
624 | return $return; |
||
625 | } |
||
626 | } |
||
627 |