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\LeadBundle\Entity; |
||
13 | |||
14 | use Mautic\CoreBundle\Entity\CommonRepository; |
||
15 | |||
16 | /** |
||
17 | * FrequecyRuleRepository. |
||
18 | */ |
||
19 | class FrequencyRuleRepository extends CommonRepository |
||
20 | { |
||
21 | /** |
||
22 | * @param $channel |
||
23 | * @param $leadIds |
||
24 | * @param $defaultFrequencyNumber |
||
25 | * @param $defaultFrequencyTime |
||
26 | * @param string $statTable |
||
27 | * @param string $statSentColumn |
||
28 | * @param string $statContactColumn |
||
29 | * |
||
30 | * @return array |
||
31 | */ |
||
32 | public function getAppliedFrequencyRules($channel, $leadIds, $defaultFrequencyNumber, $defaultFrequencyTime, $statTable = 'email_stats', $statContactColumn = 'lead_id', $statSentColumn = 'date_sent') |
||
33 | { |
||
34 | $q = $this->_em->getConnection()->createQueryBuilder(); |
||
35 | |||
36 | $selectFrequency = ($defaultFrequencyNumber) ? 'IFNULL(fr.frequency_number,:defaultNumber) as frequency_number' : 'fr.frequency_number'; |
||
37 | $selectNumber = ($defaultFrequencyTime) ? 'IFNULL(fr.frequency_time,:frequencyTime) as frequency_time' : 'fr.frequency_time'; |
||
38 | |||
39 | $q->select("ch.$statContactColumn, $selectFrequency, $selectNumber") |
||
40 | ->from(MAUTIC_TABLE_PREFIX.$statTable, 'ch') |
||
41 | ->leftJoin('ch', MAUTIC_TABLE_PREFIX.'lead_frequencyrules', 'fr', "ch.{$statContactColumn} = fr.lead_id"); |
||
42 | |||
43 | if ($channel) { |
||
44 | $q->andWhere('fr.channel = :channel or fr.channel is null') |
||
45 | ->setParameter('channel', $channel); |
||
46 | } |
||
47 | |||
48 | if (!empty($defaultFrequencyTime)) { |
||
49 | $q->andWhere('ch.'.$statSentColumn.' >= case fr.frequency_time |
||
50 | when \'MONTH\' then DATE_SUB(NOW(),INTERVAL 1 MONTH) |
||
51 | when \'DAY\' then DATE_SUB(NOW(),INTERVAL 1 DAY) |
||
52 | when \'WEEK\' then DATE_SUB(NOW(),INTERVAL 1 WEEK) |
||
53 | else DATE_SUB(NOW(),INTERVAL 1 '.$defaultFrequencyTime.') |
||
54 | end') |
||
55 | ->setParameter('frequencyTime', $defaultFrequencyTime); |
||
56 | } else { |
||
57 | $q->andWhere('(ch.'.$statSentColumn.' >= case fr.frequency_time |
||
58 | when \'MONTH\' then DATE_SUB(NOW(),INTERVAL 1 MONTH) |
||
59 | when \'DAY\' then DATE_SUB(NOW(),INTERVAL 1 DAY) |
||
60 | when \'WEEK\' then DATE_SUB(NOW(),INTERVAL 1 WEEK) |
||
61 | end)'); |
||
62 | } |
||
63 | |||
64 | if (empty($leadIds)) { |
||
65 | // Preventative for fetching every single email stat |
||
66 | $leadIds = [0]; |
||
67 | } |
||
68 | $q->andWhere( |
||
69 | $q->expr()->in("ch.$statContactColumn", $leadIds) |
||
70 | ); |
||
71 | |||
72 | $q->groupBy("ch.$statContactColumn, fr.frequency_time, fr.frequency_number"); |
||
73 | |||
74 | $havingAnd = 'AND '.$q->expr()->in("ch.$statContactColumn", $leadIds); |
||
75 | if (null != $defaultFrequencyNumber) { |
||
76 | $q->having("(count(ch.$statContactColumn) >= IFNULL(fr.frequency_number,:defaultNumber) $havingAnd)") |
||
77 | ->setParameter('defaultNumber', $defaultFrequencyNumber); |
||
78 | } else { |
||
79 | $q->having("(count(ch.$statContactColumn) >= fr.frequency_number $havingAnd)"); |
||
80 | } |
||
81 | |||
82 | return $q->execute()->fetchAll(); |
||
83 | } |
||
84 | |||
85 | /** |
||
86 | * @param null $channel |
||
87 | * @param null $leadIds |
||
88 | * |
||
89 | * @return array |
||
90 | */ |
||
91 | public function getFrequencyRules($channel = null, $leadIds = null) |
||
92 | { |
||
93 | $q = $this->_em->getConnection()->createQueryBuilder(); |
||
94 | |||
95 | $q->select( |
||
96 | 'fr.id, fr.frequency_time, fr.frequency_number, fr.channel, fr.preferred_channel, fr.pause_from_date, fr.pause_to_date, fr.lead_id' |
||
97 | ) |
||
98 | ->from(MAUTIC_TABLE_PREFIX.'lead_frequencyrules', 'fr'); |
||
99 | |||
100 | if ($channel) { |
||
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
101 | $q->andWhere('fr.channel = :channel') |
||
102 | ->setParameter('channel', $channel); |
||
103 | } |
||
104 | |||
105 | $groupByLeads = is_array($leadIds); |
||
106 | if ($leadIds) { |
||
107 | if ($groupByLeads) { |
||
108 | $q->andWhere( |
||
109 | $q->expr()->in('fr.lead_id', $leadIds) |
||
110 | ); |
||
111 | } else { |
||
112 | $q->andWhere('fr.lead_id = :leadId') |
||
113 | ->setParameter('leadId', (int) $leadIds); |
||
114 | } |
||
115 | } |
||
116 | |||
117 | $results = $q->execute()->fetchAll(); |
||
118 | |||
119 | $frequencyRules = []; |
||
120 | |||
121 | foreach ($results as $result) { |
||
122 | if ($groupByLeads) { |
||
123 | if (!isset($frequencyRules[$result['lead_id']])) { |
||
124 | $frequencyRules[$result['lead_id']] = []; |
||
125 | } |
||
126 | |||
127 | $frequencyRules[$result['lead_id']][$result['channel']] = $result; |
||
128 | } else { |
||
129 | $frequencyRules[$result['channel']] = $result; |
||
130 | } |
||
131 | } |
||
132 | |||
133 | return $frequencyRules; |
||
134 | } |
||
135 | |||
136 | /** |
||
137 | * @param $leadId |
||
138 | * |
||
139 | * @return array |
||
140 | */ |
||
141 | public function getPreferredChannel($leadId) |
||
142 | { |
||
143 | $q = $this->_em->getConnection()->createQueryBuilder(); |
||
144 | |||
145 | $q->select('fr.id, fr.frequency_time, fr.frequency_number, fr.channel, fr.pause_from_date, fr.pause_to_date') |
||
146 | ->from(MAUTIC_TABLE_PREFIX.'lead_frequencyrules', 'fr'); |
||
147 | $q->where('fr.preferred_channel = :preferredChannel') |
||
148 | ->setParameter('preferredChannel', true, 'boolean'); |
||
149 | if ($leadId) { |
||
150 | $q->andWhere('fr.lead_id = :leadId') |
||
151 | ->setParameter('leadId', $leadId); |
||
152 | } |
||
153 | |||
154 | return $q->execute()->fetchAll(); |
||
155 | } |
||
156 | } |
||
157 |