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
{
22 public function __construct() {
23 parent
::__construct();
27 * Create a new delivery event.
29 * @param array $params
30 * Associative array of delivery event values.
32 * @return \CRM_Mailing_Event_BAO_Delivered
34 public static function &create(&$params) {
35 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
36 $params['event_queue_id'],
44 $delivered = new CRM_Mailing_Event_BAO_Delivered();
45 $delivered->time_stamp
= date('YmdHis');
46 $delivered->copyValues($params);
49 $queue = new CRM_Mailing_Event_BAO_Queue();
50 $queue->id
= $params['event_queue_id'];
53 while ($queue->fetch()) {
54 $email = new CRM_Core_BAO_Email();
55 $email->id
= $queue->email_id
;
56 $email->hold_date
= '';
57 $email->reset_date
= date('YmdHis');
65 * Get row count for the event selector.
67 * @param int $mailing_id
70 * Optional ID of a job to filter on.
71 * @param bool $is_distinct
73 * @param string $toDate
76 * Number of rows in result set
78 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
79 $dao = new CRM_Core_DAO();
81 $delivered = self
::getTableName();
82 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
83 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
84 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
85 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
88 SELECT COUNT($delivered.id) as delivered
91 ON $delivered.event_queue_id = $queue.id
93 ON $delivered.event_queue_id = $bounce.event_queue_id
95 ON $queue.job_id = $job.id
98 ON $job.mailing_id = $mailing.id
99 WHERE $bounce.id IS null
100 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
102 if (!empty($toDate)) {
103 $query .= " AND $delivered.time_stamp <= $toDate";
106 if (!empty($job_id)) {
107 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
111 $query .= " GROUP BY $queue.id ";
118 return $dao->delivered
;
125 * Get rows for the event browser.
127 * @param int $mailing_id
130 * Optional ID of the job.
131 * @param bool $is_distinct
132 * Group by queue id?.
135 * @param int $rowCount
140 * @param int $is_test
145 public static function &getRows(
146 $mailing_id, $job_id = NULL,
147 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
150 $dao = new CRM_Core_DAO();
152 $delivered = self
::getTableName();
153 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
154 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
155 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
156 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
157 $contact = CRM_Contact_BAO_Contact
::getTableName();
158 $email = CRM_Core_BAO_Email
::getTableName();
161 SELECT $delivered.id as id,
162 $contact.display_name as display_name,
163 $contact.id as contact_id,
164 $email.email as email,
165 $delivered.time_stamp as date
168 ON $queue.contact_id = $contact.id
170 ON $queue.email_id = $email.id
171 INNER JOIN $delivered
172 ON $delivered.event_queue_id = $queue.id
174 ON $bounce.event_queue_id = $queue.id
176 ON $queue.job_id = $job.id
177 AND $job.is_test = $is_test
179 ON $job.mailing_id = $mailing.id
180 WHERE $bounce.id IS null
181 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
183 if (!empty($job_id)) {
184 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
188 $query .= " GROUP BY $queue.id, $delivered.id";
191 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
193 if (is_string($sort)) {
194 $sort = CRM_Utils_Type
::escape($sort, 'String');
198 $orderBy = trim($sort->orderBy());
202 $query .= " ORDER BY {$orderBy} ";
204 if ($offset ||
$rowCount) {
205 //Added "||$rowCount" to avoid displaying all records on first page
206 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
213 while ($dao->fetch()) {
214 $url = CRM_Utils_System
::url('civicrm/contact/view',
215 "reset=1&cid={$dao->contact_id}"
217 $results[$dao->id
] = [
218 'contact_id' => $dao->contact_id
,
219 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
220 'email' => $dao->email
,
221 'date' => CRM_Utils_Date
::customFormat($dao->date
),
228 * @param $eventQueueIDs
231 public static function bulkCreate($eventQueueIDs, $time = NULL) {
233 $time = date('YmdHis');
236 // construct a bulk insert statement
238 foreach ($eventQueueIDs as $eqID) {
239 $values[] = "( $eqID, '{$time}' )";
242 while (!empty($values)) {
243 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
244 $str = implode(',', $input);
245 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
246 CRM_Core_DAO
::executeQuery($sql);
251 * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
252 * we cannot decide when an email address last got an email.
254 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
255 * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence
256 * the above scenario results in the email being put on hold
258 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
259 * and setting the resetDate to the date that an email was delivered
261 * @param int $minDays
262 * Consider mailings that were completed at least $minDays ago.
263 * @param int $maxDays
264 * Consider mailings that were completed not more than $maxDays ago.
266 public static function updateEmailResetDate(int $minDays = 3, int $maxDays = 7) {
268 if ($minDays < 0 ||
$maxDays < 0 ||
$maxDays <= $minDays) {
269 throw new \
InvalidArgumentException("minDays and maxDays must be >=0 and maxDays > minDays");
272 $temporaryTable = CRM_Utils_SQL_TempTable
::build()
273 ->setCategory('mailingemail')
275 ->createWithColumns('id int primary key, reset_date datetime');
276 $temporaryTableName = $temporaryTable->getName();
279 INSERT INTO {$temporaryTableName} (id, reset_date)
280 SELECT civicrm_email.id as email_id,
281 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
282 FROM civicrm_mailing_event_queue
283 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
284 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
285 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
286 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
287 WHERE civicrm_mailing_event_bounce.id IS NULL
288 AND civicrm_mailing_job.status = 'Complete'
289 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
290 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
291 AND civicrm_email.on_hold != 2
292 GROUP BY civicrm_email.id
294 CRM_Core_DAO
::executeQuery($query);
297 UPDATE civicrm_email e
298 INNER JOIN {$temporaryTableName} et ON e.id = et.id
301 e.reset_date = et.reset_date
303 CRM_Core_DAO
::executeQuery($query);
304 $temporaryTable->drop();