3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Mailing_Event_BAO_Delivered
extends CRM_Mailing_Event_DAO_Delivered
{
38 public function __construct() {
39 parent
::__construct();
43 * Create a new delivery event.
45 * @param array $params
46 * Associative array of delivery event values.
48 * @return \CRM_Mailing_Event_BAO_Delivered
50 public static function &create(&$params) {
51 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
52 $params['event_queue_id'],
61 $delivered = new CRM_Mailing_Event_BAO_Delivered();
62 $delivered->time_stamp
= date('YmdHis');
63 $delivered->copyValues($params);
66 $queue = new CRM_Mailing_Event_BAO_Queue();
67 $queue->id
= $params['event_queue_id'];
70 while ($queue->fetch()) {
71 $email = new CRM_Core_BAO_Email();
72 $email->id
= $queue->email_id
;
73 $email->hold_date
= '';
74 $email->reset_date
= date('YmdHis');
82 * Get row count for the event selector.
84 * @param int $mailing_id
87 * Optional ID of a job to filter on.
88 * @param bool $is_distinct
90 * @param string $toDate
93 * Number of rows in result set
95 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
96 $dao = new CRM_Core_DAO();
98 $delivered = self
::getTableName();
99 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
100 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
101 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
102 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
105 SELECT COUNT($delivered.id) as delivered
108 ON $delivered.event_queue_id = $queue.id
110 ON $delivered.event_queue_id = $bounce.event_queue_id
112 ON $queue.job_id = $job.id
115 ON $job.mailing_id = $mailing.id
116 WHERE $bounce.id IS null
117 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
119 if (!empty($toDate)) {
120 $query .= " AND $delivered.time_stamp <= $toDate";
123 if (!empty($job_id)) {
124 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
128 $query .= " GROUP BY $queue.id ";
135 return $dao->delivered
;
142 * Get rows for the event browser.
144 * @param int $mailing_id
147 * Optional ID of the job.
148 * @param bool $is_distinct
149 * Group by queue id?.
152 * @param int $rowCount
157 * @param int $is_test
162 public static function &getRows(
163 $mailing_id, $job_id = NULL,
164 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
167 $dao = new CRM_Core_Dao();
169 $delivered = self
::getTableName();
170 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
171 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
172 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
173 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
174 $contact = CRM_Contact_BAO_Contact
::getTableName();
175 $email = CRM_Core_BAO_Email
::getTableName();
178 SELECT $delivered.id as id,
179 $contact.display_name as display_name,
180 $contact.id as contact_id,
181 $email.email as email,
182 $delivered.time_stamp as date
185 ON $queue.contact_id = $contact.id
187 ON $queue.email_id = $email.id
188 INNER JOIN $delivered
189 ON $delivered.event_queue_id = $queue.id
191 ON $bounce.event_queue_id = $queue.id
193 ON $queue.job_id = $job.id
194 AND $job.is_test = $is_test
196 ON $job.mailing_id = $mailing.id
197 WHERE $bounce.id IS null
198 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
200 if (!empty($job_id)) {
201 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
205 $query .= " GROUP BY $queue.id, $delivered.id";
208 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
210 if (is_string($sort)) {
211 $sort = CRM_Utils_Type
::escape($sort, 'String');
215 $orderBy = trim($sort->orderBy());
219 $query .= " ORDER BY {$orderBy} ";
221 if ($offset ||
$rowCount) {
222 //Added "||$rowCount" to avoid displaying all records on first page
223 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
230 while ($dao->fetch()) {
231 $url = CRM_Utils_System
::url('civicrm/contact/view',
232 "reset=1&cid={$dao->contact_id}"
234 $results[$dao->id
] = array(
235 'contact_id' => $dao->contact_id
,
236 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
237 'email' => $dao->email
,
238 'date' => CRM_Utils_Date
::customFormat($dao->date
),
245 * @param $eventQueueIDs
248 public static function bulkCreate($eventQueueIDs, $time = NULL) {
250 $time = date('YmdHis');
253 // construct a bulk insert statement
255 foreach ($eventQueueIDs as $eqID) {
256 $values[] = "( $eqID, '{$time}' )";
259 while (!empty($values)) {
260 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
261 $str = implode(',', $input);
262 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
263 CRM_Core_DAO
::executeQuery($sql);
268 * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
269 * we cannot decide when an email address last got an email.
271 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
272 * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence
273 * the above scenario results in the email being put on hold
275 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
276 * and setting the resetDate to the date that an email was delivered
278 * @param int $minDays
279 * Consider mailings that were completed at least $minDays ago.
280 * @param int $maxDays
281 * Consider mailings that were completed not more than $maxDays ago.
283 public static function updateEmailResetDate($minDays = 3, $maxDays = 7) {
284 $dao = new CRM_Core_Dao();
287 CREATE TEMPORARY TABLE civicrm_email_temp_values (
292 CRM_Core_DAO
::executeQuery($query);
295 INSERT INTO civicrm_email_temp_values (id, reset_date)
296 SELECT civicrm_email.id as email_id,
297 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
298 FROM civicrm_mailing_event_queue
299 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
300 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
301 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
302 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
303 WHERE civicrm_mailing_event_bounce.id IS NULL
304 AND civicrm_mailing_job.status = 'Complete'
305 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
306 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
307 GROUP BY civicrm_email.id
309 CRM_Core_DAO
::executeQuery($query);
312 UPDATE civicrm_email e
313 INNER JOIN civicrm_email_temp_values et ON e.id = et.id
316 e.reset_date = et.reset_date
318 CRM_Core_DAO
::executeQuery($query);