3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Mailing_Event_BAO_Delivered
extends CRM_Mailing_Event_DAO_Delivered
{
40 public function __construct() {
41 parent
::__construct();
45 * Create a new delivery event
47 * @param array $params
48 * Associative array of delivery event values.
52 public static function &create(&$params) {
53 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
54 $params['event_queue_id'],
63 $delivered = new CRM_Mailing_Event_BAO_Delivered();
64 $delivered->time_stamp
= date('YmdHis');
65 $delivered->copyValues($params);
68 $queue = new CRM_Mailing_Event_BAO_Queue();
69 $queue->id
= $params['event_queue_id'];
72 while ($queue->fetch()) {
73 $email = new CRM_Core_BAO_Email();
74 $email->id
= $queue->email_id
;
75 $email->hold_date
= '';
76 $email->reset_date
= date('YmdHis');
84 * Get row count for the event selector
86 * @param int $mailing_id
89 * Optional ID of a job to filter on.
90 * @param bool $is_distinct
94 * Number of rows in result set
96 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
97 $dao = new CRM_Core_DAO();
99 $delivered = self
::getTableName();
100 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
101 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
102 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
103 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
106 SELECT COUNT($delivered.id) as delivered
109 ON $delivered.event_queue_id = $queue.id
111 ON $delivered.event_queue_id = $bounce.event_queue_id
113 ON $queue.job_id = $job.id
116 ON $job.mailing_id = $mailing.id
117 WHERE $bounce.id IS null
118 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
120 if (!empty($toDate)) {
121 $query .= " AND $delivered.time_stamp <= $toDate";
124 if (!empty($job_id)) {
125 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
129 $query .= " GROUP BY $queue.id ";
136 return $dao->delivered
;
143 * Get rows for the event browser
145 * @param int $mailing_id
148 * Optional ID of the job.
149 * @param bool $is_distinct
150 * Group by queue id?.
153 * @param int $rowCount
161 public static function &getRows(
162 $mailing_id, $job_id = NULL,
163 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $is_test = 0
166 $dao = new CRM_Core_Dao();
168 $delivered = self
::getTableName();
169 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
170 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
171 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
172 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
173 $contact = CRM_Contact_BAO_Contact
::getTableName();
174 $email = CRM_Core_BAO_Email
::getTableName();
177 SELECT $delivered.id as id,
178 $contact.display_name as display_name,
179 $contact.id as contact_id,
180 $email.email as email,
181 $delivered.time_stamp as date
184 ON $queue.contact_id = $contact.id
186 ON $queue.email_id = $email.id
187 INNER JOIN $delivered
188 ON $delivered.event_queue_id = $queue.id
190 ON $bounce.event_queue_id = $queue.id
192 ON $queue.job_id = $job.id
193 AND $job.is_test = $is_test
195 ON $job.mailing_id = $mailing.id
196 WHERE $bounce.id IS null
197 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
199 if (!empty($job_id)) {
200 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
204 $query .= " GROUP BY $queue.id ";
207 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
209 if (is_string($sort)) {
210 $sort = CRM_Utils_Type
::escape($sort, 'String');
214 $orderBy = trim($sort->orderBy());
218 $query .= " ORDER BY {$orderBy} ";
220 if ($offset ||
$rowCount) {
221 //Added "||$rowCount" to avoid displaying all records on first page
222 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
229 while ($dao->fetch()) {
230 $url = CRM_Utils_System
::url('civicrm/contact/view',
231 "reset=1&cid={$dao->contact_id}"
233 $results[$dao->id
] = array(
234 'contact_id' => $dao->contact_id
,
235 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
236 'email' => $dao->email
,
237 'date' => CRM_Utils_Date
::customFormat($dao->date
),
244 * @param $eventQueueIDs
247 public static function bulkCreate($eventQueueIDs, $time = NULL) {
249 $time = date('YmdHis');
252 // construct a bulk insert statement
254 foreach ($eventQueueIDs as $eqID) {
255 $values[] = "( $eqID, '{$time}' )";
258 while (!empty($values)) {
259 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
260 $str = implode(',', $input);
261 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
262 CRM_Core_DAO
::executeQuery($sql);
267 * Since we never no when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
268 * we cannot decide when an email address last got an email.
270 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
271 * successfull deliveries before starting the bounce again. The current code does not set the resetDate and hence
272 * the above scenario results in the email being put on hold
274 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
275 * and setting the resetDate to the date that an email was delivered
277 * @param int $minDays
278 * Consider mailings that were completed at least $minDays ago.
279 * @param int $maxDays
280 * Consider mailings that were completed not more than $maxDays ago.
284 public static function updateEmailResetDate($minDays = 3, $maxDays = 7) {
285 $dao = new CRM_Core_Dao();
288 CREATE TEMPORARY TABLE civicrm_email_temp_values (
293 CRM_Core_DAO
::executeQuery($query);
296 INSERT INTO civicrm_email_temp_values (id, reset_date)
297 SELECT civicrm_email.id as email_id,
298 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
299 FROM civicrm_mailing_event_queue
300 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
301 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
302 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
303 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
304 WHERE civicrm_mailing_event_bounce.id IS NULL
305 AND civicrm_mailing_job.status = 'Complete'
306 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
307 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
308 GROUP BY civicrm_email.id
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);