3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Mailing_Event_BAO_Delivered
extends CRM_Mailing_Event_DAO_Delivered
{
20 * Create a new delivery event.
22 * @param array $params
23 * Associative array of delivery event values.
25 * @return \CRM_Mailing_Event_BAO_Delivered
27 public static function &create(&$params) {
28 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
29 $params['event_queue_id'],
37 $delivered = new CRM_Mailing_Event_BAO_Delivered();
38 $delivered->time_stamp
= date('YmdHis');
39 $delivered->copyValues($params);
42 $queue = new CRM_Mailing_Event_BAO_Queue();
43 $queue->id
= $params['event_queue_id'];
46 while ($queue->fetch()) {
47 $email = new CRM_Core_BAO_Email();
48 $email->id
= $queue->email_id
;
49 $email->hold_date
= '';
50 $email->reset_date
= date('YmdHis');
58 * Get row count for the event selector.
60 * @param int $mailing_id
63 * Optional ID of a job to filter on.
64 * @param bool $is_distinct
66 * @param string $toDate
69 * Number of rows in result set
71 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
72 $dao = new CRM_Core_DAO();
74 $delivered = self
::getTableName();
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();
81 SELECT COUNT($delivered.id) as delivered
84 ON $delivered.event_queue_id = $queue.id
86 ON $delivered.event_queue_id = $bounce.event_queue_id
88 ON $queue.job_id = $job.id
91 ON $job.mailing_id = $mailing.id
92 WHERE $bounce.id IS null
93 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
95 if (!empty($toDate)) {
96 $query .= " AND $delivered.time_stamp <= $toDate";
99 if (!empty($job_id)) {
100 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
104 $query .= " GROUP BY $queue.id ";
111 return $dao->delivered
;
118 * Get rows for the event browser.
120 * @param int $mailing_id
123 * Optional ID of the job.
124 * @param bool $is_distinct
125 * Group by queue id?.
128 * @param int $rowCount
133 * @param int $is_test
138 public static function &getRows(
139 $mailing_id, $job_id = NULL,
140 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
143 $dao = new CRM_Core_DAO();
145 $delivered = self
::getTableName();
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();
154 SELECT $delivered.id as id,
155 $contact.display_name as display_name,
156 $contact.id as contact_id,
157 $email.email as email,
158 $delivered.time_stamp as date
161 ON $queue.contact_id = $contact.id
163 ON $queue.email_id = $email.id
164 INNER JOIN $delivered
165 ON $delivered.event_queue_id = $queue.id
167 ON $bounce.event_queue_id = $queue.id
169 ON $queue.job_id = $job.id
170 AND $job.is_test = $is_test
172 ON $job.mailing_id = $mailing.id
173 WHERE $bounce.id IS null
174 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
176 if (!empty($job_id)) {
177 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
181 $query .= " GROUP BY $queue.id, $delivered.id";
184 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
186 if (is_string($sort)) {
187 $sort = CRM_Utils_Type
::escape($sort, 'String');
191 $orderBy = trim($sort->orderBy());
195 $query .= " ORDER BY {$orderBy} ";
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');
206 while ($dao->fetch()) {
207 $url = CRM_Utils_System
::url('civicrm/contact/view',
208 "reset=1&cid={$dao->contact_id}"
210 $results[$dao->id
] = [
211 'contact_id' => $dao->contact_id
,
212 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
213 'email' => $dao->email
,
214 'date' => CRM_Utils_Date
::customFormat($dao->date
),
221 * @param $eventQueueIDs
224 public static function bulkCreate($eventQueueIDs, $time = NULL) {
226 $time = date('YmdHis');
229 // construct a bulk insert statement
231 foreach ($eventQueueIDs as $eqID) {
232 $values[] = "( $eqID, '{$time}' )";
235 while (!empty($values)) {
236 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
237 $str = implode(',', $input);
238 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
239 CRM_Core_DAO
::executeQuery($sql);
244 * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
245 * we cannot decide when an email address last got an email.
247 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
248 * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence
249 * the above scenario results in the email being put on hold
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
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.
259 public static function updateEmailResetDate(int $minDays = 3, int $maxDays = 7) {
261 if ($minDays < 0 ||
$maxDays < 0 ||
$maxDays <= $minDays) {
262 throw new \
InvalidArgumentException("minDays and maxDays must be >=0 and maxDays > minDays");
265 $temporaryTable = CRM_Utils_SQL_TempTable
::build()
266 ->setCategory('mailingemail')
268 ->createWithColumns('id int primary key, reset_date datetime');
269 $temporaryTableName = $temporaryTable->getName();
271 // also exclude on_hold = opt-out (2)
273 INSERT INTO {$temporaryTableName} (id, reset_date)
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)
285 AND civicrm_email.on_hold != 2
286 GROUP BY civicrm_email.id
288 CRM_Core_DAO
::executeQuery($query);
291 UPDATE civicrm_email e
292 INNER JOIN {$temporaryTableName} et ON e.id = et.id
295 e.reset_date = et.reset_date
297 CRM_Core_DAO
::executeQuery($query);
298 $temporaryTable->drop();