Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
06b69b18 | 4 | | CiviCRM version 4.5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
06b69b18 | 6 | | Copyright CiviCRM LLC (c) 2004-2014 | |
6a488035 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
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. | | |
13 | | | | |
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. | | |
18 | | | | |
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 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
06b69b18 | 31 | * @copyright CiviCRM LLC (c) 2004-2014 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Mailing_Event_BAO_Delivered extends CRM_Mailing_Event_DAO_Delivered { | |
36 | ||
37 | /** | |
38 | * class constructor | |
39 | */ | |
40 | function __construct() { | |
41 | parent::__construct(); | |
42 | } | |
43 | ||
44 | /** | |
45 | * Create a new delivery event | |
46 | * | |
47 | * @param array $params Associative array of delivery event values | |
48 | * | |
49 | * @return void | |
50 | * @access public | |
51 | * @static | |
52 | */ | |
53 | public static function &create(&$params) { | |
54 | $q = &CRM_Mailing_Event_BAO_Queue::verify($params['job_id'], | |
55 | $params['event_queue_id'], | |
56 | $params['hash'] | |
57 | ); | |
58 | ||
59 | if (!$q) { | |
60 | return NULL; | |
61 | } | |
62 | $q->free(); | |
63 | ||
64 | $delivered = new CRM_Mailing_Event_BAO_Delivered(); | |
65 | $delivered->time_stamp = date('YmdHis'); | |
66 | $delivered->copyValues($params); | |
67 | $delivered->save(); | |
68 | ||
69 | $queue = new CRM_Mailing_Event_BAO_Queue(); | |
70 | $queue->id = $params['event_queue_id']; | |
71 | $queue->find(TRUE); | |
72 | ||
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'); | |
78 | $email->save(); | |
79 | } | |
80 | ||
81 | return $delivered; | |
82 | } | |
83 | ||
84 | /** | |
85 | * Get row count for the event selector | |
86 | * | |
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? | |
90 | * | |
91 | * @return int Number of rows in result set | |
92 | * @access public | |
93 | * @static | |
94 | */ | |
95 | public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE) { | |
96 | $dao = new CRM_Core_DAO(); | |
97 | ||
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(); | |
9da8dc8c | 102 | $job = CRM_Mailing_BAO_MailingJob::getTableName(); |
6a488035 TO |
103 | |
104 | $query = " | |
105 | SELECT COUNT($delivered.id) as delivered | |
106 | FROM $delivered | |
107 | INNER JOIN $queue | |
108 | ON $delivered.event_queue_id = $queue.id | |
109 | LEFT JOIN $bounce | |
110 | ON $delivered.event_queue_id = $bounce.event_queue_id | |
111 | INNER JOIN $job | |
112 | ON $queue.job_id = $job.id | |
113 | AND $job.is_test = 0 | |
114 | INNER JOIN $mailing | |
115 | ON $job.mailing_id = $mailing.id | |
116 | WHERE $bounce.id IS null | |
117 | AND $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
118 | ||
119 | if (!empty($job_id)) { | |
120 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
121 | } | |
122 | ||
123 | if ($is_distinct) { | |
124 | $query .= " GROUP BY $queue.id "; | |
125 | } | |
126 | ||
127 | // query was missing | |
128 | $dao->query($query); | |
129 | ||
130 | if ($dao->fetch()) { | |
131 | return $dao->delivered; | |
132 | } | |
133 | ||
134 | return NULL; | |
135 | } | |
136 | ||
137 | /** | |
138 | * Get rows for the event browser | |
139 | * | |
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 | |
146 | * | |
147 | * @return array Result set | |
148 | * @access public | |
149 | * @static | |
150 | */ | |
151 | public static function &getRows($mailing_id, $job_id = NULL, | |
152 | $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL | |
153 | ) { | |
154 | ||
155 | $dao = new CRM_Core_Dao(); | |
156 | ||
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(); | |
9da8dc8c | 161 | $job = CRM_Mailing_BAO_MailingJob::getTableName(); |
6a488035 TO |
162 | $contact = CRM_Contact_BAO_Contact::getTableName(); |
163 | $email = CRM_Core_BAO_Email::getTableName(); | |
164 | ||
165 | $query = " | |
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 | |
170 | FROM $contact | |
171 | INNER JOIN $queue | |
172 | ON $queue.contact_id = $contact.id | |
173 | INNER JOIN $email | |
174 | ON $queue.email_id = $email.id | |
175 | INNER JOIN $delivered | |
176 | ON $delivered.event_queue_id = $queue.id | |
177 | LEFT JOIN $bounce | |
178 | ON $bounce.event_queue_id = $queue.id | |
179 | INNER JOIN $job | |
180 | ON $queue.job_id = $job.id | |
181 | AND $job.is_test = 0 | |
182 | INNER JOIN $mailing | |
183 | ON $job.mailing_id = $mailing.id | |
184 | WHERE $bounce.id IS null | |
185 | AND $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
186 | ||
187 | if (!empty($job_id)) { | |
188 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
189 | } | |
190 | ||
191 | if ($is_distinct) { | |
192 | $query .= " GROUP BY $queue.id "; | |
193 | } | |
194 | ||
195 | $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC"; | |
196 | if ($sort) { | |
197 | if (is_string($sort)) { | |
21d32567 | 198 | $sort = CRM_Utils_Type::escape($sort, 'String'); |
6a488035 TO |
199 | $orderBy = $sort; |
200 | } | |
201 | else { | |
202 | $orderBy = trim($sort->orderBy()); | |
203 | } | |
204 | } | |
205 | ||
206 | $query .= " ORDER BY {$orderBy} "; | |
207 | ||
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'); | |
211 | } | |
212 | ||
213 | $dao->query($query); | |
214 | ||
215 | $results = array(); | |
216 | ||
217 | while ($dao->fetch()) { | |
218 | $url = CRM_Utils_System::url('civicrm/contact/view', | |
219 | "reset=1&cid={$dao->contact_id}" | |
220 | ); | |
221 | $results[] = array( | |
222 | 'name' => "<a href=\"$url\">{$dao->display_name}</a>", | |
223 | 'email' => $dao->email, | |
224 | 'date' => CRM_Utils_Date::customFormat($dao->date), | |
225 | ); | |
226 | } | |
227 | return $results; | |
228 | } | |
229 | ||
230 | static function bulkCreate($eventQueueIDs, $time = NULL) { | |
231 | if (!$time) { | |
232 | $time = date('YmdHis'); | |
233 | } | |
234 | ||
235 | // construct a bulk insert statement | |
236 | $values = array(); | |
237 | foreach ($eventQueueIDs as $eqID) { | |
238 | $values[] = "( $eqID, '{$time}' )"; | |
239 | } | |
240 | ||
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); | |
246 | } | |
247 | } | |
248 | ||
249 | /** | |
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. | |
252 | * | |
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 | |
256 | * | |
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 | |
259 | * | |
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 | |
262 | * | |
263 | * @return void | |
264 | * @static | |
265 | **/ | |
266 | public static function updateEmailResetDate($minDays = 3, $maxDays = 7) { | |
267 | $dao = new CRM_Core_Dao(); | |
268 | ||
269 | $query = " | |
270 | CREATE TEMPORARY TABLE civicrm_email_temp_values ( | |
271 | id int primary key, | |
272 | reset_date datetime | |
273 | ) ENGINE = HEAP; | |
274 | "; | |
275 | CRM_Core_DAO::executeQuery($query); | |
276 | ||
277 | $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 | |
291 | "; | |
292 | ||
293 | $query = " | |
294 | UPDATE civicrm_email e | |
295 | INNER JOIN civicrm_email_temp_values et ON e.id = et.id | |
296 | SET e.on_hold = 0, | |
297 | e.hold_date = NULL, | |
298 | e.reset_date = et.reset_date | |
299 | "; | |
300 | CRM_Core_DAO::executeQuery($query); | |
301 | } | |
302 | ||
303 | } | |
304 |