Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
8c9251b3 | 6 | | Copyright CiviCRM LLC (c) 2004-2018 | |
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 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
8c9251b3 | 31 | * @copyright CiviCRM LLC (c) 2004-2018 |
6a488035 TO |
32 | */ |
33 | ||
34 | require_once 'Mail/mime.php'; | |
4c6ce474 EM |
35 | |
36 | /** | |
37 | * Class CRM_Mailing_Event_BAO_Unsubscribe | |
38 | */ | |
6a488035 TO |
39 | class CRM_Mailing_Event_BAO_Unsubscribe extends CRM_Mailing_Event_DAO_Unsubscribe { |
40 | ||
41 | /** | |
fe482240 | 42 | * Class constructor. |
6a488035 | 43 | */ |
00be9182 | 44 | public function __construct() { |
6a488035 TO |
45 | parent::__construct(); |
46 | } | |
47 | ||
48 | /** | |
fe482240 | 49 | * Unsubscribe a contact from the domain. |
6a488035 | 50 | * |
90c8230e TO |
51 | * @param int $job_id |
52 | * The job ID. | |
53 | * @param int $queue_id | |
54 | * The Queue Event ID of the recipient. | |
55 | * @param string $hash | |
56 | * The hash. | |
6a488035 | 57 | * |
ae5ffbb7 | 58 | * @return bool |
a6c01b45 | 59 | * Was the contact successfully unsubscribed? |
6a488035 TO |
60 | */ |
61 | public static function unsub_from_domain($job_id, $queue_id, $hash) { | |
62 | $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash); | |
63 | if (!$q) { | |
64 | return FALSE; | |
65 | } | |
66 | ||
67 | $transaction = new CRM_Core_Transaction(); | |
68 | ||
69 | $now = date('YmdHis'); | |
70 | if (CRM_Core_BAO_Email::isMultipleBulkMail()) { | |
71 | $email = new CRM_Core_BAO_Email(); | |
72 | $email->id = $q->email_id; | |
73 | if ($email->find(TRUE)) { | |
74 | $sql = " | |
75 | UPDATE civicrm_email | |
76 | SET on_hold = 2, | |
77 | hold_date = %1 | |
78 | WHERE email = %2 | |
79 | "; | |
35f7561f | 80 | $sqlParams = array( |
353ffa53 | 81 | 1 => array($now, 'Timestamp'), |
6a488035 TO |
82 | 2 => array($email->email, 'String'), |
83 | ); | |
84 | CRM_Core_DAO::executeQuery($sql, $sqlParams); | |
85 | } | |
86 | } | |
87 | else { | |
88 | $contact = new CRM_Contact_BAO_Contact(); | |
89 | $contact->id = $q->contact_id; | |
90 | $contact->is_opt_out = TRUE; | |
91 | $contact->save(); | |
92 | } | |
93 | ||
94 | $ue = new CRM_Mailing_Event_BAO_Unsubscribe(); | |
95 | $ue->event_queue_id = $queue_id; | |
96 | $ue->org_unsubscribe = 1; | |
97 | $ue->time_stamp = $now; | |
98 | $ue->save(); | |
99 | ||
100 | $shParams = array( | |
101 | 'contact_id' => $q->contact_id, | |
102 | 'group_id' => NULL, | |
103 | 'status' => 'Removed', | |
104 | 'method' => 'Email', | |
105 | 'tracking' => $ue->id, | |
106 | ); | |
107 | CRM_Contact_BAO_SubscriptionHistory::create($shParams); | |
108 | ||
109 | $transaction->commit(); | |
110 | ||
111 | return TRUE; | |
112 | } | |
113 | ||
114 | /** | |
fe482240 | 115 | * Unsubscribe a contact from all groups that received this mailing. |
6a488035 | 116 | * |
90c8230e TO |
117 | * @param int $job_id |
118 | * The job ID. | |
119 | * @param int $queue_id | |
120 | * The Queue Event ID of the recipient. | |
121 | * @param string $hash | |
122 | * The hash. | |
123 | * @param bool $return | |
124 | * If true return the list of groups. | |
6a488035 | 125 | * |
72b3a70c CW |
126 | * @return array|null |
127 | * $groups Array of all groups from which the contact was removed, or null if the queue event could not be found. | |
6a488035 TO |
128 | */ |
129 | public static function &unsub_from_mailing($job_id, $queue_id, $hash, $return = FALSE) { | |
25606795 | 130 | // First make sure there's a matching queue event. |
6a488035 TO |
131 | |
132 | $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash); | |
133 | $success = NULL; | |
134 | if (!$q) { | |
135 | return $success; | |
136 | } | |
137 | ||
138 | $contact_id = $q->contact_id; | |
139 | $transaction = new CRM_Core_Transaction(); | |
140 | ||
141 | $do = new CRM_Core_DAO(); | |
04124b30 | 142 | $mgObject = new CRM_Mailing_DAO_MailingGroup(); |
6a488035 | 143 | $mg = $mgObject->getTableName(); |
9da8dc8c | 144 | $jobObject = new CRM_Mailing_BAO_MailingJob(); |
6a488035 TO |
145 | $job = $jobObject->getTableName(); |
146 | $mailingObject = new CRM_Mailing_BAO_Mailing(); | |
147 | $mailing = $mailingObject->getTableName(); | |
148 | $groupObject = new CRM_Contact_BAO_Group(); | |
149 | $group = $groupObject->getTableName(); | |
150 | $gcObject = new CRM_Contact_BAO_GroupContact(); | |
151 | $gc = $gcObject->getTableName(); | |
abf4cee0 SL |
152 | $abObject = new CRM_Mailing_DAO_MailingAB(); |
153 | $ab = $abObject->getTableName(); | |
6a488035 TO |
154 | |
155 | //We Need the mailing Id for the hook... | |
ceb10dc7 DL |
156 | $do->query("SELECT $job.mailing_id as mailing_id |
157 | FROM $job | |
6a488035 TO |
158 | WHERE $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer')); |
159 | $do->fetch(); | |
160 | $mailing_id = $do->mailing_id; | |
b6f5751b | 161 | $mailing_type = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_Mailing', $mailing_id, 'mailing_type', 'id'); |
96fce13b | 162 | $entity = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingGroup', $mailing_id, 'entity_table', 'mailing_id'); |
abf4cee0 | 163 | |
b6f5751b SL |
164 | // If $entity is null and $mailing_Type is either winner or experiment then we are deailing with an AB test |
165 | $abtest_types = array('experiment', 'winner'); | |
166 | if (empty($entity) && in_array($mailing_type, $abtest_types)) { | |
abf4cee0 SL |
167 | $mailing_id_a = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingAB', $mailing_id, 'mailing_id_a', 'mailing_id_b'); |
168 | $field = 'mailing_id_b'; | |
169 | if (empty($mailing_id_a)) { | |
170 | $mailing_id_a = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingAB', $mailing_id, 'mailing_id_a', 'mailing_id_c'); | |
171 | $field = 'mailing_id_c'; | |
172 | } | |
173 | $jobJoin = "INNER JOIN $ab ON $ab.mailing_id_a = $mg.mailing_id | |
174 | INNER JOIN $job ON $job.mailing_id = $ab.$field"; | |
b6f5751b | 175 | $entity = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingGroup', $mailing_id_a, 'entity_table', 'mailing_id'); |
abf4cee0 SL |
176 | } |
177 | else { | |
178 | $jobJoin = "INNER JOIN $job ON $job.mailing_id = $mg.mailing_id"; | |
179 | } | |
180 | ||
96fce13b JP |
181 | $groupClause = ''; |
182 | if ($entity == $group) { | |
183 | $groupClause = "AND $group.is_hidden = 0"; | |
184 | } | |
6a488035 TO |
185 | |
186 | $do->query(" | |
187 | SELECT $mg.entity_table as entity_table, | |
188 | $mg.entity_id as entity_id, | |
189 | $mg.group_type as group_type | |
190 | FROM $mg | |
abf4cee0 | 191 | $jobJoin |
96fce13b JP |
192 | INNER JOIN $entity |
193 | ON $mg.entity_id = $entity.id | |
6a488035 | 194 | WHERE $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer') . " |
96fce13b | 195 | AND $mg.group_type IN ('Include', 'Base') $groupClause" |
6a488035 TO |
196 | ); |
197 | ||
25606795 SB |
198 | // Make a list of groups and a list of prior mailings that received |
199 | // this mailing. | |
6a488035 | 200 | |
6a488035 TO |
201 | $groups = array(); |
202 | $base_groups = array(); | |
203 | $mailings = array(); | |
204 | ||
205 | while ($do->fetch()) { | |
206 | if ($do->entity_table == $group) { | |
207 | if ($do->group_type == 'Base') { | |
208 | $base_groups[$do->entity_id] = NULL; | |
209 | } | |
210 | else { | |
211 | $groups[$do->entity_id] = NULL; | |
212 | } | |
213 | } | |
214 | elseif ($do->entity_table == $mailing) { | |
215 | $mailings[] = $do->entity_id; | |
216 | } | |
217 | } | |
218 | ||
25606795 SB |
219 | // As long as we have prior mailings, find their groups and add to the |
220 | // list. | |
6a488035 TO |
221 | |
222 | while (!empty($mailings)) { | |
223 | $do->query(" | |
224 | SELECT $mg.entity_table as entity_table, | |
225 | $mg.entity_id as entity_id | |
226 | FROM $mg | |
227 | WHERE $mg.mailing_id IN (" . implode(', ', $mailings) . ") | |
228 | AND $mg.group_type = 'Include'"); | |
229 | ||
230 | $mailings = array(); | |
231 | ||
232 | while ($do->fetch()) { | |
233 | if ($do->entity_table == $group) { | |
234 | $groups[$do->entity_id] = TRUE; | |
235 | } | |
236 | elseif ($do->entity_table == $mailing) { | |
237 | $mailings[] = $do->entity_id; | |
238 | } | |
239 | } | |
240 | } | |
241 | ||
242 | //Pass the groups to be unsubscribed from through a hook. | |
472e3d84 | 243 | $groupIds = array_keys($groups); |
244 | //include child groups if any | |
245 | $groupIds = array_merge($groupIds, CRM_Contact_BAO_Group::getChildGroupIds($groupIds)); | |
246 | ||
247 | $baseGroupIds = array_keys($base_groups); | |
248 | CRM_Utils_Hook::unsubscribeGroups('unsubscribe', $mailing_id, $contact_id, $groupIds, $baseGroupIds); | |
6a488035 | 249 | |
25606795 SB |
250 | // Now we have a complete list of recipient groups. Filter out all |
251 | // those except smart groups, those that the contact belongs to and | |
252 | // base groups from search based mailings. | |
6a488035 | 253 | $baseGroupClause = ''; |
472e3d84 | 254 | if (!empty($baseGroupIds)) { |
255 | $baseGroupClause = "OR $group.id IN(" . implode(', ', $baseGroupIds) . ")"; | |
6a488035 | 256 | } |
3921b55d | 257 | $groupIdClause = ''; |
472e3d84 | 258 | if ($groupIds || $baseGroupIds) { |
259 | $groupIdClause = "AND $group.id IN (" . implode(', ', array_merge($groupIds, $baseGroupIds)) . ")"; | |
3921b55d | 260 | } |
6a488035 TO |
261 | $do->query(" |
262 | SELECT $group.id as group_id, | |
263 | $group.title as title, | |
264 | $group.description as description | |
265 | FROM $group | |
266 | LEFT JOIN $gc | |
267 | ON $gc.group_id = $group.id | |
3921b55d CW |
268 | WHERE $group.is_hidden = 0 |
269 | $groupIdClause | |
6a488035 TO |
270 | AND ($group.saved_search_id is not null |
271 | OR ($gc.contact_id = $contact_id | |
272 | AND $gc.status = 'Added') | |
273 | $baseGroupClause | |
274 | )"); | |
275 | ||
276 | if ($return) { | |
277 | $returnGroups = array(); | |
278 | while ($do->fetch()) { | |
279 | $returnGroups[$do->group_id] = array( | |
280 | 'title' => $do->title, | |
281 | 'description' => $do->description, | |
282 | ); | |
283 | } | |
284 | return $returnGroups; | |
285 | } | |
286 | else { | |
287 | while ($do->fetch()) { | |
288 | $groups[$do->group_id] = $do->title; | |
289 | } | |
290 | } | |
291 | ||
292 | $contacts = array($contact_id); | |
293 | foreach ($groups as $group_id => $group_name) { | |
294 | $notremoved = FALSE; | |
295 | if ($group_name) { | |
472e3d84 | 296 | if (in_array($group_id, $baseGroupIds)) { |
6a488035 TO |
297 | list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::addContactsToGroup($contacts, $group_id, 'Email', 'Removed'); |
298 | } | |
299 | else { | |
300 | list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::removeContactsFromGroup($contacts, $group_id, 'Email'); | |
301 | } | |
302 | } | |
303 | if ($notremoved) { | |
304 | unset($groups[$group_id]); | |
305 | } | |
306 | } | |
307 | ||
308 | $ue = new CRM_Mailing_Event_BAO_Unsubscribe(); | |
309 | $ue->event_queue_id = $queue_id; | |
310 | $ue->org_unsubscribe = 0; | |
311 | $ue->time_stamp = date('YmdHis'); | |
312 | $ue->save(); | |
313 | ||
314 | $transaction->commit(); | |
315 | return $groups; | |
316 | } | |
317 | ||
318 | /** | |
b44e3f84 | 319 | * Send a response email informing the contact of the groups from which he. |
6a488035 TO |
320 | * has been unsubscribed. |
321 | * | |
90c8230e TO |
322 | * @param string $queue_id |
323 | * The queue event ID. | |
324 | * @param array $groups | |
325 | * List of group IDs. | |
326 | * @param bool $is_domain | |
327 | * Is this domain-level?. | |
328 | * @param int $job | |
329 | * The job ID. | |
6a488035 TO |
330 | */ |
331 | public static function send_unsub_response($queue_id, $groups, $is_domain = FALSE, $job) { | |
332 | $config = CRM_Core_Config::singleton(); | |
333 | $domain = CRM_Core_BAO_Domain::getDomain(); | |
334 | ||
9da8dc8c | 335 | $jobObject = new CRM_Mailing_BAO_MailingJob(); |
6a488035 TO |
336 | $jobTable = $jobObject->getTableName(); |
337 | $mailingObject = new CRM_Mailing_DAO_Mailing(); | |
338 | $mailingTable = $mailingObject->getTableName(); | |
339 | $contactsObject = new CRM_Contact_DAO_Contact(); | |
340 | $contacts = $contactsObject->getTableName(); | |
341 | $emailObject = new CRM_Core_DAO_Email(); | |
342 | $email = $emailObject->getTableName(); | |
343 | $queueObject = new CRM_Mailing_Event_BAO_Queue(); | |
344 | $queue = $queueObject->getTableName(); | |
345 | ||
346 | //get the default domain email address. | |
347 | list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail(); | |
348 | ||
349 | $dao = new CRM_Mailing_BAO_Mailing(); | |
ceb10dc7 | 350 | $dao->query(" SELECT * FROM $mailingTable |
6a488035 | 351 | INNER JOIN $jobTable ON |
ceb10dc7 | 352 | $jobTable.mailing_id = $mailingTable.id |
6a488035 TO |
353 | WHERE $jobTable.id = $job"); |
354 | $dao->fetch(); | |
355 | ||
356 | $component = new CRM_Mailing_BAO_Component(); | |
357 | ||
358 | if ($is_domain) { | |
359 | $component->id = $dao->optout_id; | |
360 | } | |
361 | else { | |
362 | $component->id = $dao->unsubscribe_id; | |
363 | } | |
364 | $component->find(TRUE); | |
365 | ||
366 | $html = $component->body_html; | |
367 | if ($component->body_text) { | |
368 | $text = $component->body_text; | |
369 | } | |
370 | else { | |
371 | $text = CRM_Utils_String::htmlToText($component->body_html); | |
372 | } | |
373 | ||
374 | $eq = new CRM_Core_DAO(); | |
375 | $eq->query( | |
376 | "SELECT $contacts.preferred_mail_format as format, | |
377 | $contacts.id as contact_id, | |
378 | $email.email as email, | |
379 | $queue.hash as hash | |
380 | FROM $contacts | |
381 | INNER JOIN $queue ON $queue.contact_id = $contacts.id | |
382 | INNER JOIN $email ON $queue.email_id = $email.id | |
383 | WHERE $queue.id = " . CRM_Utils_Type::escape($queue_id, 'Integer') | |
384 | ); | |
385 | $eq->fetch(); | |
386 | ||
387 | if ($groups) { | |
388 | foreach ($groups as $key => $value) { | |
389 | if (!$value) { | |
390 | unset($groups[$key]); | |
391 | } | |
392 | } | |
393 | } | |
394 | ||
395 | $message = new Mail_mime("\n"); | |
396 | ||
397 | list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email); | |
398 | $bao = new CRM_Mailing_BAO_Mailing(); | |
399 | $bao->body_text = $text; | |
400 | $bao->body_html = $html; | |
401 | $tokens = $bao->getTokens(); | |
402 | if ($eq->format == 'HTML' || $eq->format == 'Both') { | |
403 | $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']); | |
404 | $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id, $eq->hash); | |
405 | $html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']); | |
406 | $html = CRM_Utils_Token::replaceMailingTokens($html, $dao, NULL, $tokens['html']); | |
407 | $message->setHTMLBody($html); | |
408 | } | |
409 | if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { | |
410 | $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']); | |
411 | $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id, $eq->hash); | |
412 | $text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']); | |
413 | $text = CRM_Utils_Token::replaceMailingTokens($text, $dao, NULL, $tokens['text']); | |
414 | $message->setTxtBody($text); | |
415 | } | |
416 | ||
417 | $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain(); | |
418 | ||
419 | $headers = array( | |
420 | 'Subject' => $component->subject, | |
576fcb9c | 421 | 'From' => "\"$domainEmailName\" <" . CRM_Core_BAO_Domain::getNoReplyEmailAddress() . '>', |
6a488035 | 422 | 'To' => $eq->email, |
576fcb9c | 423 | 'Reply-To' => CRM_Core_BAO_Domain::getNoReplyEmailAddress(), |
424 | 'Return-Path' => CRM_Core_BAO_Domain::getNoReplyEmailAddress(), | |
6a488035 TO |
425 | ); |
426 | CRM_Mailing_BAO_Mailing::addMessageIdHeader($headers, 'u', $job, $queue_id, $eq->hash); | |
427 | ||
428 | $b = CRM_Utils_Mail::setMimeParams($message); | |
429 | $h = $message->headers($headers); | |
430 | ||
048222df | 431 | $mailer = \Civi::service('pear_mail'); |
6a488035 | 432 | |
6a488035 | 433 | if (is_object($mailer)) { |
6a4257d4 | 434 | $errorScope = CRM_Core_TemporaryErrorScope::ignoreException(); |
6a488035 | 435 | $mailer->send($eq->email, $h, $b); |
6a4257d4 | 436 | unset($errorScope); |
6a488035 TO |
437 | } |
438 | } | |
439 | ||
440 | /** | |
fe482240 | 441 | * Get row count for the event selector. |
6a488035 | 442 | * |
90c8230e TO |
443 | * @param int $mailing_id |
444 | * ID of the mailing. | |
445 | * @param int $job_id | |
446 | * Optional ID of a job to filter on. | |
447 | * @param bool $is_distinct | |
448 | * Group by queue ID?. | |
2a6da8d7 | 449 | * |
ad37ac8e | 450 | * @param string $org_unsubscribe |
451 | * | |
452 | * @param string $toDate | |
6a488035 | 453 | * |
a6c01b45 CW |
454 | * @return int |
455 | * Number of rows in result set | |
6a488035 | 456 | */ |
a3d7e8ee TO |
457 | public static function getTotalCount( |
458 | $mailing_id, $job_id = NULL, | |
7811a84b | 459 | $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL |
6a488035 TO |
460 | ) { |
461 | $dao = new CRM_Core_DAO(); | |
462 | ||
463 | $unsub = self::$_tableName; | |
464 | $queueObject = new CRM_Mailing_Event_BAO_Queue(); | |
465 | $queue = $queueObject->getTableName(); | |
466 | $mailingObject = new CRM_Mailing_BAO_Mailing(); | |
467 | $mailing = $mailingObject->getTableName(); | |
9da8dc8c | 468 | $jobObject = new CRM_Mailing_BAO_MailingJob(); |
6a488035 TO |
469 | $job = $jobObject->getTableName(); |
470 | ||
471 | $query = " | |
472 | SELECT COUNT($unsub.id) as unsubs | |
473 | FROM $unsub | |
474 | INNER JOIN $queue | |
475 | ON $unsub.event_queue_id = $queue.id | |
476 | INNER JOIN $job | |
477 | ON $queue.job_id = $job.id | |
478 | INNER JOIN $mailing | |
479 | ON $job.mailing_id = $mailing.id | |
480 | AND $job.is_test = 0 | |
481 | WHERE $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
482 | ||
7811a84b | 483 | if (!empty($toDate)) { |
484 | $query .= " AND $unsub.time_stamp <= $toDate"; | |
485 | } | |
486 | ||
6a488035 TO |
487 | if (!empty($job_id)) { |
488 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
489 | } | |
490 | ||
491 | if ($org_unsubscribe !== NULL) { | |
492 | $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1); | |
493 | } | |
494 | ||
495 | if ($is_distinct) { | |
496 | $query .= " GROUP BY $queue.id "; | |
497 | } | |
498 | ||
499 | $dao->query($query); | |
500 | $dao->fetch(); | |
501 | if ($is_distinct) { | |
502 | return $dao->N; | |
503 | } | |
504 | else { | |
505 | return $dao->unsubs ? $dao->unsubs : 0; | |
506 | } | |
507 | } | |
508 | ||
509 | /** | |
fe482240 | 510 | * Get rows for the event browser. |
6a488035 | 511 | * |
90c8230e TO |
512 | * @param int $mailing_id |
513 | * ID of the mailing. | |
514 | * @param int $job_id | |
515 | * Optional ID of the job. | |
516 | * @param bool $is_distinct | |
517 | * Group by queue id?. | |
518 | * @param int $offset | |
519 | * Offset. | |
520 | * @param int $rowCount | |
521 | * Number of rows. | |
522 | * @param array $sort | |
523 | * Sort array. | |
6a488035 | 524 | * |
2a6da8d7 | 525 | * @param null $org_unsubscribe |
a6c01b45 CW |
526 | * @return array |
527 | * Result set | |
6a488035 | 528 | */ |
a3d7e8ee TO |
529 | public static function &getRows( |
530 | $mailing_id, $job_id = NULL, | |
6a488035 TO |
531 | $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, |
532 | $org_unsubscribe = NULL | |
533 | ) { | |
534 | ||
535 | $dao = new CRM_Core_Dao(); | |
536 | ||
537 | $unsub = self::$_tableName; | |
538 | $queueObject = new CRM_Mailing_Event_BAO_Queue(); | |
539 | $queue = $queueObject->getTableName(); | |
540 | $mailingObject = new CRM_Mailing_BAO_Mailing(); | |
541 | $mailing = $mailingObject->getTableName(); | |
9da8dc8c | 542 | $jobObject = new CRM_Mailing_BAO_MailingJob(); |
6a488035 TO |
543 | $job = $jobObject->getTableName(); |
544 | $contactObject = new CRM_Contact_BAO_Contact(); | |
545 | $contact = $contactObject->getTableName(); | |
546 | $emailObject = new CRM_Core_BAO_Email(); | |
547 | $email = $emailObject->getTableName(); | |
548 | ||
549 | $query = " | |
550 | SELECT $contact.display_name as display_name, | |
551 | $contact.id as contact_id, | |
552 | $email.email as email, | |
553 | $unsub.time_stamp as date, | |
554 | $unsub.org_unsubscribe as org_unsubscribe | |
555 | FROM $contact | |
556 | INNER JOIN $queue | |
557 | ON $queue.contact_id = $contact.id | |
558 | INNER JOIN $email | |
559 | ON $queue.email_id = $email.id | |
560 | INNER JOIN $unsub | |
561 | ON $unsub.event_queue_id = $queue.id | |
562 | INNER JOIN $job | |
563 | ON $queue.job_id = $job.id | |
564 | INNER JOIN $mailing | |
565 | ON $job.mailing_id = $mailing.id | |
566 | AND $job.is_test = 0 | |
567 | WHERE $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
568 | ||
569 | if (!empty($job_id)) { | |
570 | $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
571 | } | |
572 | ||
573 | if ($org_unsubscribe !== NULL) { | |
574 | $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1); | |
575 | } | |
576 | ||
577 | if ($is_distinct) { | |
0ee5581e | 578 | $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe"; |
6a488035 TO |
579 | } |
580 | ||
581 | $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC"; | |
582 | if ($sort) { | |
583 | if (is_string($sort)) { | |
21d32567 | 584 | $sort = CRM_Utils_Type::escape($sort, 'String'); |
6a488035 TO |
585 | $orderBy = $sort; |
586 | } | |
587 | else { | |
588 | $orderBy = trim($sort->orderBy()); | |
589 | } | |
590 | } | |
591 | ||
592 | $query .= " ORDER BY {$orderBy} "; | |
593 | ||
594 | if ($offset || $rowCount) { | |
595 | //Added "||$rowCount" to avoid displaying all records on first page | |
596 | $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer'); | |
597 | } | |
598 | ||
599 | $dao->query($query); | |
600 | ||
601 | $results = array(); | |
602 | ||
603 | while ($dao->fetch()) { | |
604 | $url = CRM_Utils_System::url('civicrm/contact/view', | |
605 | "reset=1&cid={$dao->contact_id}" | |
606 | ); | |
607 | $results[] = array( | |
608 | 'name' => "<a href=\"$url\">{$dao->display_name}</a>", | |
609 | 'email' => $dao->email, | |
4b95f713 DG |
610 | // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes. |
611 | 'unsubOrOptout' => ts('Yes'), | |
6a488035 TO |
612 | 'date' => CRM_Utils_Date::customFormat($dao->date), |
613 | ); | |
614 | } | |
615 | return $results; | |
616 | } | |
617 | ||
e0ef6999 | 618 | /** |
100fef9d | 619 | * @param int $queueID |
e0ef6999 EM |
620 | * |
621 | * @return array | |
622 | */ | |
6a488035 TO |
623 | public static function getContactInfo($queueID) { |
624 | $query = " | |
625 | SELECT DISTINCT(civicrm_mailing_event_queue.contact_id) as contact_id, | |
626 | civicrm_contact.display_name as display_name | |
627 | civicrm_email.email as email | |
628 | FROM civicrm_mailing_event_queue, | |
629 | civicrm_contact, | |
630 | civicrm_email | |
631 | WHERE civicrm_mailing_event_queue.contact_id = civicrm_contact.id | |
632 | AND civicrm_mailing_event_queue.email_id = civicrm_email.id | |
633 | AND civicrm_mailing_event_queue.id = " . CRM_Utils_Type::escape($queueID, 'Integer'); | |
634 | ||
33621c4f | 635 | $dao = CRM_Core_DAO::executeQuery($query); |
6a488035 TO |
636 | |
637 | $displayName = 'Unknown'; | |
638 | $email = 'Unknown'; | |
639 | if ($dao->fetch()) { | |
640 | $displayName = $dao->display_name; | |
641 | $email = $dao->email; | |
642 | } | |
643 | ||
644 | return array($displayName, $email); | |
645 | } | |
96025800 | 646 | |
6a488035 | 647 | } |