3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.4 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
35 class CRM_Mailing_Event_BAO_Delivered
extends CRM_Mailing_Event_DAO_Delivered
{
40 function __construct() {
41 parent
::__construct();
45 * Create a new delivery event
47 * @param array $params Associative array of delivery event values
53 public static function &create(&$params) {
54 $q = &CRM_Mailing_Event_BAO_Queue
::verify($params['job_id'],
55 $params['event_queue_id'],
64 $delivered = new CRM_Mailing_Event_BAO_Delivered();
65 $delivered->time_stamp
= date('YmdHis');
66 $delivered->copyValues($params);
69 $queue = new CRM_Mailing_Event_BAO_Queue();
70 $queue->id
= $params['event_queue_id'];
73 while ($queue->fetch()) {
74 $email = new CRM_Core_BAO_Email();
75 $email->id
= $queue->email_id
;
76 $email->hold_date
= '';
77 $email->reset_date
= date('YmdHis');
85 * Get row count for the event selector
87 * @param int $mailing_id ID of the mailing
88 * @param int $job_id Optional ID of a job to filter on
89 * @param boolean $is_distinct Group by queue ID?
91 * @return int Number of rows in result set
95 public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE) {
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($job_id)) {
120 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
124 $query .= " GROUP BY $queue.id ";
131 return $dao->delivered
;
138 * Get rows for the event browser
140 * @param int $mailing_id ID of the mailing
141 * @param int $job_id optional ID of the job
142 * @param boolean $is_distinct Group by queue id?
143 * @param int $offset Offset
144 * @param int $rowCount Number of rows
145 * @param array $sort sort array
147 * @return array Result set
151 public static function &getRows($mailing_id, $job_id = NULL,
152 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL
155 $dao = new CRM_Core_Dao();
157 $delivered = self
::getTableName();
158 $bounce = CRM_Mailing_Event_BAO_Bounce
::getTableName();
159 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
160 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
161 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
162 $contact = CRM_Contact_BAO_Contact
::getTableName();
163 $email = CRM_Core_BAO_Email
::getTableName();
166 SELECT $contact.display_name as display_name,
167 $contact.id as contact_id,
168 $email.email as email,
169 $delivered.time_stamp as date
172 ON $queue.contact_id = $contact.id
174 ON $queue.email_id = $email.id
175 INNER JOIN $delivered
176 ON $delivered.event_queue_id = $queue.id
178 ON $bounce.event_queue_id = $queue.id
180 ON $queue.job_id = $job.id
183 ON $job.mailing_id = $mailing.id
184 WHERE $bounce.id IS null
185 AND $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
187 if (!empty($job_id)) {
188 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
192 $query .= " GROUP BY $queue.id ";
195 $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC";
197 if (is_string($sort)) {
198 $sort = CRM_Utils_Type
::escape($sort, 'String');
202 $orderBy = trim($sort->orderBy());
206 $query .= " ORDER BY {$orderBy} ";
208 if ($offset ||
$rowCount) {
209 //Added "||$rowCount" to avoid displaying all records on first page
210 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
217 while ($dao->fetch()) {
218 $url = CRM_Utils_System
::url('civicrm/contact/view',
219 "reset=1&cid={$dao->contact_id}"
222 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
223 'email' => $dao->email
,
224 'date' => CRM_Utils_Date
::customFormat($dao->date
),
230 static function bulkCreate($eventQueueIDs, $time = NULL) {
232 $time = date('YmdHis');
235 // construct a bulk insert statement
237 foreach ($eventQueueIDs as $eqID) {
238 $values[] = "( $eqID, '{$time}' )";
241 while (!empty($values)) {
242 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
243 $str = implode(',', $input);
244 $sql = "INSERT INTO civicrm_mailing_event_delivered ( event_queue_id, time_stamp ) VALUES $str;";
245 CRM_Core_DAO
::executeQuery($sql);
250 * Since we never no when a mailing really bounces (hard bounce == NOW, soft bounce == NOW to NOW + 3 days?)
251 * we cannot decide when an email address last got an email.
253 * We want to avoid putting on hold an email address which had a few bounces (mbox full) and then got quite a few
254 * successfull deliveries before starting the bounce again. The current code does not set the resetDate and hence
255 * the above scenario results in the email being put on hold
257 * This function rectifies that by considering all non-test mailing jobs which have completed between $minDays and $maxDays
258 * and setting the resetDate to the date that an email was delivered
260 * @param integer $minDays consider mailings that were completed at least $minDays ago
261 * @param integer $maxDays consider mailings that were completed not more than $maxDays ago
266 public static function updateEmailResetDate($minDays = 3, $maxDays = 7) {
267 $dao = new CRM_Core_Dao();
270 CREATE TEMPORARY TABLE civicrm_email_temp_values (
275 CRM_Core_DAO
::executeQuery($query);
278 INSERT INTO civicrm_email_temp_values (id, reset_date)
279 SELECT civicrm_email.id as email_id,
280 max(civicrm_mailing_event_delivered.time_stamp) as reset_date
281 FROM civicrm_mailing_event_queue
282 INNER JOIN civicrm_email ON civicrm_mailing_event_queue.email_id = civicrm_email.id
283 INNER JOIN civicrm_mailing_event_delivered ON civicrm_mailing_event_delivered.event_queue_id = civicrm_mailing_event_queue.id
284 LEFT JOIN civicrm_mailing_event_bounce ON civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
285 INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.is_test = 0
286 WHERE civicrm_mailing_event_bounce.id IS NULL
287 AND civicrm_mailing_job.status = 'Complete'
288 AND civicrm_mailing_job.end_date BETWEEN DATE_SUB(NOW(), INTERVAL $maxDays day) AND DATE_SUB(NOW(), INTERVAL $minDays day)
289 AND (civicrm_email.reset_date IS NULL OR civicrm_email.reset_date < civicrm_mailing_job.start_date)
290 GROUP BY civicrm_email.id
294 UPDATE civicrm_email e
295 INNER JOIN civicrm_email_temp_values et ON e.id = et.id
298 e.reset_date = et.reset_date
300 CRM_Core_DAO
::executeQuery($query);