Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Mailing_Event_BAO_Delivered extends CRM_Mailing_Event_DAO_Delivered { | |
18 | ||
6a488035 | 19 | /** |
fe482240 | 20 | * Create a new delivery event. |
6a488035 | 21 | * |
90c8230e TO |
22 | * @param array $params |
23 | * Associative array of delivery event values. | |
f3f00653 | 24 | * |
25 | * @return \CRM_Mailing_Event_BAO_Delivered | |
6a488035 TO |
26 | */ |
27 | public static function &create(&$params) { | |
28 | $q = &CRM_Mailing_Event_BAO_Queue::verify($params['job_id'], | |
29 | $params['event_queue_id'], | |
30 | $params['hash'] | |
31 | ); | |
32 | ||
33 | if (!$q) { | |
34 | return NULL; | |
35 | } | |
6a488035 TO |
36 | |
37 | $delivered = new CRM_Mailing_Event_BAO_Delivered(); | |
38 | $delivered->time_stamp = date('YmdHis'); | |
39 | $delivered->copyValues($params); | |
40 | $delivered->save(); | |
41 | ||
42 | $queue = new CRM_Mailing_Event_BAO_Queue(); | |
43 | $queue->id = $params['event_queue_id']; | |
44 | $queue->find(TRUE); | |
45 | ||
46 | while ($queue->fetch()) { | |
353ffa53 TO |
47 | $email = new CRM_Core_BAO_Email(); |
48 | $email->id = $queue->email_id; | |
49 | $email->hold_date = ''; | |
6a488035 TO |
50 | $email->reset_date = date('YmdHis'); |
51 | $email->save(); | |
52 | } | |
53 | ||
54 | return $delivered; | |
55 | } | |
56 | ||
57 | /** | |
fe482240 | 58 | * Get row count for the event selector. |
6a488035 | 59 | * |
90c8230e TO |
60 | * @param int $mailing_id |
61 | * ID of the mailing. | |
62 | * @param int $job_id | |
63 | * Optional ID of a job to filter on. | |
64 | * @param bool $is_distinct | |
65 | * Group by queue ID?. | |
ad37ac8e | 66 | * @param string $toDate |
6a488035 | 67 | * |
a6c01b45 CW |
68 | * @return int |
69 | * Number of rows in result set | |
6a488035 | 70 | */ |
7811a84b | 71 | public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) { |
6a488035 TO |
72 | $dao = new CRM_Core_DAO(); |
73 | ||
74 | $delivered = self::getTableName(); | |
353ffa53 TO |
75 | $bounce = CRM_Mailing_Event_BAO_Bounce::getTableName(); |
76 | $queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
77 | $mailing = CRM_Mailing_BAO_Mailing::getTableName(); | |
78 | $job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
6a488035 TO |
79 | |
80 | $query = " | |
81 | SELECT COUNT($delivered.id) as delivered | |
82 | FROM $delivered | |
83 | INNER JOIN $queue | |
84 | ON $delivered.event_queue_id = $queue.id | |
85 | LEFT JOIN $bounce | |
86 | ON $delivered.event_queue_id = $bounce.event_queue_id | |
87 | INNER JOIN $job | |
88 | ON $queue.job_id = $job.id | |
89 | AND $job.is_test = 0 | |
90 | INNER JOIN $mailing | |
91 | ON $job.mailing_id = $mailing.id | |
92 | WHERE $bounce.id IS null | |
93 | AND $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
94 | ||
7811a84b | 95 | if (!empty($toDate)) { |
96 | $query .= " AND $delivered.time_stamp <= $toDate"; | |
97 | } | |
98 | ||
6a488035 TO |
99 | if (!empty($job_id)) { |
100 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
101 | } | |
102 | ||
103 | if ($is_distinct) { | |
104 | $query .= " GROUP BY $queue.id "; | |
105 | } | |
106 | ||
107 | // query was missing | |
108 | $dao->query($query); | |
109 | ||
110 | if ($dao->fetch()) { | |
111 | return $dao->delivered; | |
112 | } | |
113 | ||
114 | return NULL; | |
115 | } | |
116 | ||
117 | /** | |
fe482240 | 118 | * Get rows for the event browser. |
6a488035 | 119 | * |
90c8230e TO |
120 | * @param int $mailing_id |
121 | * ID of the mailing. | |
122 | * @param int $job_id | |
123 | * Optional ID of the job. | |
124 | * @param bool $is_distinct | |
125 | * Group by queue id?. | |
126 | * @param int $offset | |
127 | * Offset. | |
128 | * @param int $rowCount | |
129 | * Number of rows. | |
130 | * @param array $sort | |
131 | * Sort array. | |
6a488035 | 132 | * |
ad37ac8e | 133 | * @param int $is_test |
134 | * | |
a6c01b45 CW |
135 | * @return array |
136 | * Result set | |
6a488035 | 137 | */ |
a3d7e8ee TO |
138 | public static function &getRows( |
139 | $mailing_id, $job_id = NULL, | |
2e84f38d | 140 | $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0 |
6a488035 TO |
141 | ) { |
142 | ||
0fc59d7a | 143 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
144 | |
145 | $delivered = self::getTableName(); | |
353ffa53 TO |
146 | $bounce = CRM_Mailing_Event_BAO_Bounce::getTableName(); |
147 | $queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
148 | $mailing = CRM_Mailing_BAO_Mailing::getTableName(); | |
149 | $job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
150 | $contact = CRM_Contact_BAO_Contact::getTableName(); | |
151 | $email = CRM_Core_BAO_Email::getTableName(); | |
6a488035 TO |
152 | |
153 | $query = " | |
ef643544 | 154 | SELECT $delivered.id as id, |
155 | $contact.display_name as display_name, | |
6a488035 TO |
156 | $contact.id as contact_id, |
157 | $email.email as email, | |
158 | $delivered.time_stamp as date | |
159 | FROM $contact | |
160 | INNER JOIN $queue | |
161 | ON $queue.contact_id = $contact.id | |
162 | INNER JOIN $email | |
163 | ON $queue.email_id = $email.id | |
164 | INNER JOIN $delivered | |
165 | ON $delivered.event_queue_id = $queue.id | |
166 | LEFT JOIN $bounce | |
167 | ON $bounce.event_queue_id = $queue.id | |
168 | INNER JOIN $job | |
169 | ON $queue.job_id = $job.id | |
ef643544 | 170 | AND $job.is_test = $is_test |
6a488035 TO |
171 | INNER JOIN $mailing |
172 | ON $job.mailing_id = $mailing.id | |
173 | WHERE $bounce.id IS null | |
174 | AND $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
175 | ||
176 | if (!empty($job_id)) { | |
177 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
178 | } | |
179 | ||
180 | if ($is_distinct) { | |
0ee5581e | 181 | $query .= " GROUP BY $queue.id, $delivered.id"; |
6a488035 TO |
182 | } |
183 | ||
184 | $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC"; | |
185 | if ($sort) { | |
186 | if (is_string($sort)) { | |
21d32567 | 187 | $sort = CRM_Utils_Type::escape($sort, 'String'); |
6a488035 TO |
188 | $orderBy = $sort; |
189 | } | |
190 | else { | |
191 | $orderBy = trim($sort->orderBy()); | |
192 | } | |
193 | } | |
194 | ||
195 | $query .= " ORDER BY {$orderBy} "; | |
196 | ||
197 | if ($offset || $rowCount) { | |
198 | //Added "||$rowCount" to avoid displaying all records on first page | |
199 | $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer'); | |
200 | } | |
201 | ||
202 | $dao->query($query); | |
203 | ||
be2fb01f | 204 | $results = []; |
6a488035 TO |
205 | |
206 | while ($dao->fetch()) { | |
207 | $url = CRM_Utils_System::url('civicrm/contact/view', | |
208 | "reset=1&cid={$dao->contact_id}" | |
209 | ); | |
be2fb01f | 210 | $results[$dao->id] = [ |
ef643544 | 211 | 'contact_id' => $dao->contact_id, |
6a488035 TO |
212 | 'name' => "<a href=\"$url\">{$dao->display_name}</a>", |
213 | 'email' => $dao->email, | |
214 | 'date' => CRM_Utils_Date::customFormat($dao->date), | |
be2fb01f | 215 | ]; |
6a488035 TO |
216 | } |
217 | return $results; | |
218 | } | |
219 | ||
e0ef6999 EM |
220 | /** |
221 | * @param $eventQueueIDs | |
222 | * @param null $time | |
223 | */ | |
00be9182 | 224 | public static function bulkCreate($eventQueueIDs, $time = NULL) { |
6a488035 TO |
225 | if (!$time) { |
226 | $time = date('YmdHis'); | |
227 | } | |
228 | ||
229 | // construct a bulk insert statement | |
be2fb01f | 230 | $values = []; |
6a488035 TO |
231 | foreach ($eventQueueIDs as $eqID) { |
232 | $values[] = "( $eqID, '{$time}' )"; | |
233 | } | |
234 | ||
235 | while (!empty($values)) { | |
236 | $input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT); | |
353ffa53 TO |
237 | $str = implode(',', $input); |
238 | $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;"; | |
6a488035 TO |
239 | CRM_Core_DAO::executeQuery($sql); |
240 | } | |
241 | } | |
242 | ||
243 | /** | |
25606795 | 244 | * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?) |
6a488035 TO |
245 | * we cannot decide when an email address last got an email. |
246 | * | |
247 | * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few | |
b44e3f84 | 248 | * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence |
6a488035 TO |
249 | * the above scenario results in the email being put on hold |
250 | * | |
251 | * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays | |
252 | * and setting the resetDate to the date that an email was delivered | |
253 | * | |
90c8230e TO |
254 | * @param int $minDays |
255 | * Consider mailings that were completed at least $minDays ago. | |
256 | * @param int $maxDays | |
257 | * Consider mailings that were completed not more than $maxDays ago. | |
d5cc0fc2 | 258 | */ |
0b960db1 RLAR |
259 | public static function updateEmailResetDate(int $minDays = 3, int $maxDays = 7) { |
260 | ||
261 | if ($minDays < 0 || $maxDays < 0 || $maxDays <= $minDays) { | |
262 | throw new \InvalidArgumentException("minDays and maxDays must be >=0 and maxDays > minDays"); | |
263 | } | |
6a488035 | 264 | |
cc06bec0 SL |
265 | $temporaryTable = CRM_Utils_SQL_TempTable::build() |
266 | ->setCategory('mailingemail') | |
267 | ->setMemory() | |
268 | ->createWithColumns('id int primary key, reset_date datetime'); | |
269 | $temporaryTableName = $temporaryTable->getName(); | |
6a488035 | 270 | |
b0aecc75 | 271 | // also exclude on_hold = opt-out (2) |
6a488035 | 272 | $query = " |
cc06bec0 | 273 | INSERT INTO {$temporaryTableName} (id, reset_date) |
6a488035 TO |
274 | SELECT civicrm_email.id as email_id, |
275 | max(civicrm_mailing_event_delivered.time_stamp) as reset_date | |
276 | FROM civicrm_mailing_event_queue | |
277 | INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id | |
278 | INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id | |
279 | LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id | |
280 | INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0 | |
281 | WHERE civicrm_mailing_event_bounce.id IS NULL | |
282 | AND civicrm_mailing_job.status = 'Complete' | |
283 | AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day) | |
284 | AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date) | |
5ce7a855 | 285 | AND civicrm_email.on_hold != 2 |
6a488035 TO |
286 | GROUP BY civicrm_email.id |
287 | "; | |
8488b430 | 288 | CRM_Core_DAO::executeQuery($query); |
6a488035 TO |
289 | |
290 | $query = " | |
291 | UPDATE civicrm_email e | |
cc06bec0 | 292 | INNER JOIN {$temporaryTableName} et ON e.id = et.id |
6a488035 TO |
293 | SET e.on_hold = 0, |
294 | e.hold_date = NULL, | |
295 | e.reset_date = et.reset_date | |
296 | "; | |
297 | CRM_Core_DAO::executeQuery($query); | |
cc06bec0 | 298 | $temporaryTable->drop(); |
6a488035 TO |
299 | } |
300 | ||
301 | } |