3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
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-2017
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 public static function &create(&$params) {
49 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
50 $params['event_queue_id'],
59 $delivered = new CRM_Mailing_Event_BAO_Delivered();
60 $delivered->time_stamp
= date('YmdHis');
61 $delivered->copyValues($params);
64 $queue = new CRM_Mailing_Event_BAO_Queue();
65 $queue->id
= $params['event_queue_id'];
68 while ($queue->fetch()) {
69 $email = new CRM_Core_BAO_Email();
70 $email->id
= $queue->email_id
;
71 $email->hold_date
= '';
72 $email->reset_date
= date('YmdHis');
80 * Get row count for the event selector.
82 * @param int $mailing_id
85 * Optional ID of a job to filter on.
86 * @param bool $is_distinct
88 * @param string $toDate
91 * Number of rows in result set
93 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
94 $dao = new CRM_Core_DAO();
96 $delivered = self
::getTableName();
97 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
98 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
99 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
100 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
103 SELECT COUNT($delivered.id) as delivered
106 ON $delivered.event_queue_id = $queue.id
108 ON $delivered.event_queue_id = $bounce.event_queue_id
110 ON $queue.job_id = $job.id
113 ON $job.mailing_id = $mailing.id
114 WHERE $bounce.id IS null
115 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
117 if (!empty($toDate)) {
118 $query .= " AND $delivered.time_stamp <= $toDate";
121 if (!empty($job_id)) {
122 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
126 $query .= " GROUP BY $queue.id ";
133 return $dao->delivered
;
140 * Get rows for the event browser.
142 * @param int $mailing_id
145 * Optional ID of the job.
146 * @param bool $is_distinct
147 * Group by queue id?.
150 * @param int $rowCount
155 * @param int $is_test
160 public static function &getRows(
161 $mailing_id, $job_id = NULL,
162 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
165 $dao = new CRM_Core_Dao();
167 $delivered = self
::getTableName();
168 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
169 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
170 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
171 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
172 $contact = CRM_Contact_BAO_Contact
::getTableName();
173 $email = CRM_Core_BAO_Email
::getTableName();
176 SELECT $delivered.id as id,
177 $contact.display_name as display_name,
178 $contact.id as contact_id,
179 $email.email as email,
180 $delivered.time_stamp as date
183 ON $queue.contact_id = $contact.id
185 ON $queue.email_id = $email.id
186 INNER JOIN $delivered
187 ON $delivered.event_queue_id = $queue.id
189 ON $bounce.event_queue_id = $queue.id
191 ON $queue.job_id = $job.id
192 AND $job.is_test = $is_test
194 ON $job.mailing_id = $mailing.id
195 WHERE $bounce.id IS null
196 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
198 if (!empty($job_id)) {
199 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
203 $query .= " GROUP BY $queue.id, $delivered.id";
206 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
208 if (is_string($sort)) {
209 $sort = CRM_Utils_Type
::escape($sort, 'String');
213 $orderBy = trim($sort->orderBy());
217 $query .= " ORDER BY {$orderBy} ";
219 if ($offset ||
$rowCount) {
220 //Added "||$rowCount" to avoid displaying all records on first page
221 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
228 while ($dao->fetch()) {
229 $url = CRM_Utils_System
::url('civicrm/contact/view',
230 "reset=1&cid={$dao->contact_id}"
232 $results[$dao->id
] = array(
233 'contact_id' => $dao->contact_id
,
234 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
235 'email' => $dao->email
,
236 'date' => CRM_Utils_Date
::customFormat($dao->date
),
243 * @param $eventQueueIDs
246 public static function bulkCreate($eventQueueIDs, $time = NULL) {
248 $time = date('YmdHis');
251 // construct a bulk insert statement
253 foreach ($eventQueueIDs as $eqID) {
254 $values[] = "( $eqID, '{$time}' )";
257 while (!empty($values)) {
258 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
259 $str = implode(',', $input);
260 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
261 CRM_Core_DAO
::executeQuery($sql);
266 * Since we never know when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
267 * we cannot decide when an email address last got an email.
269 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
270 * successful deliveries before starting the bounce again. The current code does not set the resetDate and hence
271 * the above scenario results in the email being put on hold
273 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
274 * and setting the resetDate to the date that an email was delivered
276 * @param int $minDays
277 * Consider mailings that were completed at least $minDays ago.
278 * @param int $maxDays
279 * Consider mailings that were completed not more than $maxDays ago.
281 public static function updateEmailResetDate($minDays = 3, $maxDays = 7) {
282 $dao = new CRM_Core_Dao();
285 CREATE TEMPORARY TABLE civicrm_email_temp_values (
290 CRM_Core_DAO
::executeQuery($query);
293 INSERT INTO civicrm_email_temp_values (id, reset_date)
294 SELECT civicrm_email.id as email_id,
295 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
296 FROM civicrm_mailing_event_queue
297 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
298 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
299 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
300 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
301 WHERE civicrm_mailing_event_bounce.id IS NULL
302 AND civicrm_mailing_job.status = 'Complete'
303 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
304 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
305 GROUP BY civicrm_email.id
307 CRM_Core_DAO
::executeQuery($query);
310 UPDATE civicrm_email e
311 INNER JOIN civicrm_email_temp_values et ON e.id = et.id
314 e.reset_date = et.reset_date
316 CRM_Core_DAO
::executeQuery($query);