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 string $toDate
67 * Number of rows in result set
69 public static function getTotalCount($mailing_id, $job_id = NULL, $toDate = NULL) {
70 $dao = new CRM_Core_DAO();
72 $delivered = self
::getTableName();
73 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
74 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
75 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
76 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
79 SELECT COUNT($delivered.id) as delivered
82 ON $delivered.event_queue_id = $queue.id
84 ON $delivered.event_queue_id = $bounce.event_queue_id
86 ON $queue.job_id = $job.id
89 ON $job.mailing_id = $mailing.id
90 WHERE $bounce.id IS null
91 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
93 if (!empty($toDate)) {
94 $query .= " AND $delivered.time_stamp <= $toDate";
97 if (!empty($job_id)) {
98 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
105 return $dao->delivered
;
112 * Get rows for the event browser.
114 * @param int $mailing_id
117 * Optional ID of the job.
118 * @param bool $is_distinct
119 * Group by queue id?.
122 * @param int $rowCount
127 * @param int $is_test
132 public static function &getRows(
133 $mailing_id, $job_id = NULL,
134 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
137 $dao = new CRM_Core_DAO();
139 $delivered = self
::getTableName();
140 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
141 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
142 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
143 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
144 $contact = CRM_Contact_BAO_Contact
::getTableName();
145 $email = CRM_Core_BAO_Email
::getTableName();
148 SELECT $delivered.id as id,
149 $contact.display_name as display_name,
150 $contact.id as contact_id,
151 $email.email as email,
152 $delivered.time_stamp as date
155 ON $queue.contact_id = $contact.id
157 ON $queue.email_id = $email.id
158 INNER JOIN $delivered
159 ON $delivered.event_queue_id = $queue.id
161 ON $bounce.event_queue_id = $queue.id
163 ON $queue.job_id = $job.id
164 AND $job.is_test = $is_test
166 ON $job.mailing_id = $mailing.id
167 WHERE $bounce.id IS null
168 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
170 if (!empty($job_id)) {
171 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
174 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
176 if (is_string($sort)) {
177 $sort = CRM_Utils_Type
::escape($sort, 'String');
181 $orderBy = trim($sort->orderBy());
185 $query .= " ORDER BY {$orderBy} ";
187 if ($offset ||
$rowCount) {
188 //Added "||$rowCount" to avoid displaying all records on first page
189 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
196 while ($dao->fetch()) {
197 $url = CRM_Utils_System
::url('civicrm/contact/view',
198 "reset=1&cid={$dao->contact_id}"
200 $results[$dao->id
] = [
201 'contact_id' => $dao->contact_id
,
202 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
203 'email' => $dao->email
,
204 'date' => CRM_Utils_Date
::customFormat($dao->date
),
211 * @param $eventQueueIDs
214 public static function bulkCreate($eventQueueIDs, $time = NULL) {
216 $time = date('YmdHis');
219 // construct a bulk insert statement
221 foreach ($eventQueueIDs as $eqID) {
222 $values[] = "( $eqID, '{$time}' )";
225 while (!empty($values)) {
226 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
227 $str = implode(',', $input);
228 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
229 CRM_Core_DAO
::executeQuery($sql);
234 * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
235 * we cannot decide when an email address last got an email.
237 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
238 * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence
239 * the above scenario results in the email being put on hold
241 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
242 * and setting the resetDate to the date that an email was delivered
244 * @param int $minDays
245 * Consider mailings that were completed at least $minDays ago.
246 * @param int $maxDays
247 * Consider mailings that were completed not more than $maxDays ago.
249 public static function updateEmailResetDate(int $minDays = 3, int $maxDays = 7) {
251 if ($minDays < 0 ||
$maxDays < 0 ||
$maxDays <= $minDays) {
252 throw new \
InvalidArgumentException("minDays and maxDays must be >=0 and maxDays > minDays");
255 $temporaryTable = CRM_Utils_SQL_TempTable
::build()
256 ->setCategory('mailingemail')
258 ->createWithColumns('id int primary key, reset_date datetime');
259 $temporaryTableName = $temporaryTable->getName();
261 // also exclude on_hold = opt-out (2)
263 INSERT INTO {$temporaryTableName} (id, reset_date)
264 SELECT civicrm_email.id as email_id,
265 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
266 FROM civicrm_mailing_event_queue
267 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
268 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
269 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
270 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
271 WHERE civicrm_mailing_event_bounce.id IS NULL
272 AND civicrm_mailing_job.status = 'Complete'
273 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
274 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
275 AND civicrm_email.on_hold != 2
276 GROUP BY civicrm_email.id
278 CRM_Core_DAO
::executeQuery($query);
281 UPDATE civicrm_email e
282 INNER JOIN {$temporaryTableName} et ON e.id = et.id
285 e.reset_date = et.reset_date
287 CRM_Core_DAO
::executeQuery($query);
288 $temporaryTable->drop();