3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
18 require_once 'Mail/mime.php';
21 * Class CRM_Mailing_Event_BAO_Unsubscribe
23 class CRM_Mailing_Event_BAO_Unsubscribe
extends CRM_Mailing_Event_DAO_Unsubscribe
{
26 * Unsubscribe a contact from the domain.
30 * @param int $queue_id
31 * The Queue Event ID of the recipient.
36 * Was the contact successfully unsubscribed?
38 public static function unsub_from_domain($job_id, $queue_id, $hash) {
39 $q = CRM_Mailing_Event_BAO_Queue
::verify($job_id, $queue_id, $hash);
44 $transaction = new CRM_Core_Transaction();
46 $now = date('YmdHis');
47 if (CRM_Core_BAO_Email
::isMultipleBulkMail()) {
48 $email = new CRM_Core_BAO_Email();
49 $email->id
= $q->email_id
;
50 if ($email->find(TRUE)) {
58 1 => [$now, 'Timestamp'],
59 2 => [$email->email
, 'String'],
61 CRM_Core_DAO
::executeQuery($sql, $sqlParams);
65 $contact = new CRM_Contact_BAO_Contact();
66 $contact->id
= $q->contact_id
;
67 $contact->is_opt_out
= TRUE;
71 $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
72 $ue->event_queue_id
= $queue_id;
73 $ue->org_unsubscribe
= 1;
74 $ue->time_stamp
= $now;
78 'contact_id' => $q->contact_id
,
80 'status' => 'Removed',
82 'tracking' => $ue->id
,
84 CRM_Contact_BAO_SubscriptionHistory
::create($shParams);
86 $transaction->commit();
92 * Unsubscribe a contact from all groups that received this mailing.
96 * @param int $queue_id
97 * The Queue Event ID of the recipient.
100 * @param bool $return
101 * If true return the list of groups.
104 * $groups Array of all groups from which the contact was removed, or null if the queue event could not be found.
106 * @throws \CRM_Core_Exception
107 * @throws \CiviCRM_API3_Exception
109 public static function unsub_from_mailing($job_id, $queue_id, $hash, $return = FALSE): ?
array {
110 // First make sure there's a matching queue event.
112 $q = CRM_Mailing_Event_BAO_Queue
::verify($job_id, $queue_id, $hash);
117 $contact_id = $q->contact_id
;
119 $mailing_id = (int) civicrm_api3('MailingJob', 'getvalue', ['id' => $job_id, 'return' => 'mailing_id']);
120 $mailing_type = CRM_Core_DAO
::getFieldValue('CRM_Mailing_DAO_Mailing', $mailing_id, 'mailing_type', 'id');
122 // We need a mailing id that points to the mailing that defined the recipients.
123 // This is usually just the passed-in mailing_id, however in the case of AB
124 // tests, it's the variant 'A' one.
125 $relevant_mailing_id = $mailing_id;
127 // Special case for AB Tests:
128 if (in_array($mailing_type, ['experiment', 'winner'])) {
129 // The mailing belongs to an AB test.
130 // See if we can find an AB test where this is variant B.
131 $mailing_id_a = CRM_Core_DAO
::getFieldValue('CRM_Mailing_DAO_MailingAB', $mailing_id, 'mailing_id_a', 'mailing_id_b');
132 if (!empty($mailing_id_a)) {
133 // OK, we were given mailing B and we looked up variant A which is the relevant one.
134 $relevant_mailing_id = $mailing_id_a;
137 // No, it wasn't variant B, let's see if we can find an AB test where
138 // the given mailing was the winner (C).
139 $mailing_id_a = CRM_Core_DAO
::getFieldValue('CRM_Mailing_DAO_MailingAB', $mailing_id, 'mailing_id_a', 'mailing_id_c');
140 if (!empty($mailing_id_a)) {
141 // OK, this was the winner and we looked up variant A which is the relevant one.
142 $relevant_mailing_id = $mailing_id_a;
144 // (otherwise we were passed in variant A so we already have the relevant_mailing_id correct already.)
148 // Make a list of groups and a list of prior mailings that received this
149 // mailing. Nb. the 'Base' group is called the 'Unsubscribe group' in the
151 // Just to definitely make it SQL safe.
152 $relevant_mailing_id = (int) $relevant_mailing_id;
153 $do = CRM_Core_DAO
::executeQuery(
154 "SELECT entity_table, entity_id, group_type
155 FROM civicrm_mailing_group
156 WHERE mailing_id = $relevant_mailing_id
157 AND group_type IN ('Include', 'Base')");
163 while ($do->fetch()) {
164 // @todo this is should be a temporary measure until we stop storing the translated table name in the database
165 if (substr($do->entity_table
, 0, 13) === 'civicrm_group') {
166 if ($do->group_type
=== 'Base') {
167 $base_groups[$do->entity_id
] = NULL;
170 $groups[$do->entity_id
] = NULL;
173 elseif (substr($do->entity_table
, 0, 15) === 'civicrm_mailing') {
174 // @todo this is should be a temporary measure until we stop storing the translated table name in the database
175 $mailings[] = $do->entity_id
;
179 // As long as we have prior mailings, find their groups and add to the
182 while (!empty($mailings)) {
183 $do = CRM_Core_DAO
::executeQuery("
184 SELECT entity_table as entity_table,
185 entity_id as entity_id
186 FROM civicrm_mailing_group
187 WHERE mailing_id IN (" . implode(', ', $mailings) . ")
188 AND group_type = 'Include'");
192 while ($do->fetch()) {
193 // @todo this is should be a temporary measure until we stop storing the translated table name in the database
194 if (substr($do->entity_table
, 0, 13) === 'civicrm_group') {
195 $groups[$do->entity_id
] = TRUE;
197 elseif (substr($do->entity_table
, 0, 15) === 'civicrm_mailing') {
198 // @todo this is should be a temporary measure until we stop storing the translated table name in the database
199 $mailings[] = $do->entity_id
;
204 //Pass the groups to be unsubscribed from through a hook.
205 $groupIds = array_keys($groups);
206 //include child groups if any
207 $groupIds = array_merge($groupIds, CRM_Contact_BAO_Group
::getChildGroupIds($groupIds));
209 $baseGroupIds = array_keys($base_groups);
210 CRM_Utils_Hook
::unsubscribeGroups('unsubscribe', $mailing_id, $contact_id, $groupIds, $baseGroupIds);
212 // Now we have a complete list of recipient groups. Filter out all
213 // those except smart groups, those that the contact belongs to and
214 // base groups from search based mailings.
215 $baseGroupClause = '';
216 if (!empty($baseGroupIds)) {
217 $baseGroupClause = "OR grp.id IN(" . implode(', ', $baseGroupIds) . ")";
220 if ($groupIds ||
$baseGroupIds) {
221 $groupIdClause = "AND grp.id IN (" . implode(', ', array_merge($groupIds, $baseGroupIds)) . ")";
222 // Check that groupcontactcache is up to date so we can get smartgroups
223 CRM_Contact_BAO_GroupContactCache
::check(array_merge($groupIds, $baseGroupIds));
227 SELECT grp.id as group_id,
229 grp.frontend_title as frontend_title,
230 grp.frontend_description as frontend_description,
231 grp.description as description,
232 grp.saved_search_id as saved_search_id
233 FROM civicrm_group grp
234 LEFT JOIN civicrm_group_contact gc
235 ON gc.group_id = grp.id
236 LEFT JOIN civicrm_group_contact_cache gcc
237 ON gcc.group_id = grp.id
238 WHERE grp.is_hidden = 0
240 AND ((grp.saved_search_id is not null AND gcc.contact_id = %1)
241 OR (gc.contact_id = %1
242 AND gc.status = 'Added')
246 1 => [$contact_id, 'Positive'],
248 $do = CRM_Core_DAO
::executeQuery($groupsSQL, $groupsParams);
252 while ($do->fetch()) {
253 $returnGroups[$do->group_id
] = [
254 'title' => !empty($do->frontend_title
) ?
$do->frontend_title
: $do->title
,
255 'description' => !empty($do->frontend_description
) ?
$do->frontend_description
: $do->description
,
258 return $returnGroups;
261 while ($do->fetch()) {
262 $groups[$do->group_id
] = !empty($do->frontend_title
) ?
$do->frontend_title
: $do->title
;
265 $transaction = new CRM_Core_Transaction();
266 $contacts = [$contact_id];
267 foreach ($groups as $group_id => $group_name) {
270 if (in_array($group_id, $baseGroupIds)) {
271 [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact
::addContactsToGroup($contacts, $group_id, 'Email', 'Removed');
274 [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact
::removeContactsFromGroup($contacts, $group_id, 'Email');
278 unset($groups[$group_id]);
282 $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
283 $ue->event_queue_id
= $queue_id;
284 $ue->org_unsubscribe
= 0;
285 $ue->time_stamp
= date('YmdHis');
288 $transaction->commit();
293 * Send a response email informing the contact of the groups from which he.
294 * has been unsubscribed.
296 * @param string $queue_id
297 * The queue event ID.
298 * @param array $groups
300 * @param bool $is_domain
301 * Is this domain-level?.
305 public static function send_unsub_response($queue_id, $groups, $is_domain, $job) {
306 $config = CRM_Core_Config
::singleton();
307 $domain = CRM_Core_BAO_Domain
::getDomain();
308 $jobObject = new CRM_Mailing_BAO_MailingJob();
309 $jobTable = $jobObject->getTableName();
310 $mailingObject = new CRM_Mailing_DAO_Mailing();
311 $mailingTable = $mailingObject->getTableName();
312 $contactsObject = new CRM_Contact_DAO_Contact();
313 $contacts = $contactsObject->getTableName();
314 $emailObject = new CRM_Core_DAO_Email();
315 $email = $emailObject->getTableName();
316 $queueObject = new CRM_Mailing_Event_BAO_Queue();
317 $queue = $queueObject->getTableName();
319 //get the default domain email address.
320 [$domainEmailName, $domainEmailAddress] = CRM_Core_BAO_Domain
::getNameAndEmail();
322 $dao = new CRM_Mailing_BAO_Mailing();
323 $dao->query(" SELECT * FROM $mailingTable
324 INNER JOIN $jobTable ON
325 $jobTable.mailing_id = $mailingTable.id
326 WHERE $jobTable.id = $job");
329 $component = new CRM_Mailing_BAO_MailingComponent();
332 $component->id
= $dao->optout_id
;
335 $component->id
= $dao->unsubscribe_id
;
337 $component->find(TRUE);
339 $html = $component->body_html
;
340 if ($component->body_text
) {
341 $text = $component->body_text
;
344 $text = CRM_Utils_String
::htmlToText($component->body_html
);
347 $eq = new CRM_Core_DAO();
349 "SELECT $contacts.preferred_mail_format as format,
350 $contacts.id as contact_id,
351 $email.email as email,
354 INNER JOIN $queue ON $queue.contact_id = $contacts.id
355 INNER JOIN $email ON $queue.email_id = $email.id
356 WHERE $queue.id = " . CRM_Utils_Type
::escape($queue_id, 'Integer')
361 foreach ($groups as $key => $value) {
363 unset($groups[$key]);
368 [$addresses, $urls] = CRM_Mailing_BAO_Mailing
::getVerpAndUrls($job, $queue_id, $eq->hash
, $eq->email
);
369 $bao = new CRM_Mailing_BAO_Mailing();
370 $bao->body_text
= $text;
371 $bao->body_html
= $html;
372 $tokens = $bao->getTokens();
373 if ($eq->format
== 'HTML' ||
$eq->format
== 'Both') {
374 $html = CRM_Utils_Token
::replaceDomainTokens($html, $domain, TRUE, $tokens['html']);
375 $html = CRM_Utils_Token
::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id
, $eq->hash
);
376 $html = CRM_Utils_Token
::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']);
377 $html = CRM_Utils_Token
::replaceMailingTokens($html, $dao, NULL, $tokens['html']);
379 if (!$html ||
$eq->format
== 'Text' ||
$eq->format
== 'Both') {
380 $text = CRM_Utils_Token
::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
381 $text = CRM_Utils_Token
::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id
, $eq->hash
);
382 $text = CRM_Utils_Token
::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']);
383 $text = CRM_Utils_Token
::replaceMailingTokens($text, $dao, NULL, $tokens['text']);
386 $emailDomain = CRM_Core_BAO_MailSettings
::defaultDomain();
389 'subject' => $component->subject
,
390 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>",
391 'toEmail' => $eq->email
,
392 'replyTo' => CRM_Core_BAO_Domain
::getNoReplyEmailAddress(),
393 'returnPath' => CRM_Core_BAO_Domain
::getNoReplyEmailAddress(),
397 CRM_Mailing_BAO_Mailing
::addMessageIdHeader($params, 'u', $job, $queue_id, $eq->hash
);
398 if (CRM_Core_BAO_MailSettings
::includeMessageId()) {
399 $params['messageId'] = $params['Message-ID'];
401 CRM_Utils_Mail
::send($params);
405 * Get row count for the event selector.
407 * @param int $mailing_id
410 * Optional ID of a job to filter on.
411 * @param bool $is_distinct
412 * Group by queue ID?.
414 * @param string $org_unsubscribe
416 * @param string $toDate
419 * Number of rows in result set
421 public static function getTotalCount(
422 $mailing_id, $job_id = NULL,
423 $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL
425 $dao = new CRM_Core_DAO();
427 $unsub = self
::$_tableName;
428 $queueObject = new CRM_Mailing_Event_BAO_Queue();
429 $queue = $queueObject->getTableName();
430 $mailingObject = new CRM_Mailing_BAO_Mailing();
431 $mailing = $mailingObject->getTableName();
432 $jobObject = new CRM_Mailing_BAO_MailingJob();
433 $job = $jobObject->getTableName();
436 SELECT COUNT($unsub.id) as unsubs
439 ON $unsub.event_queue_id = $queue.id
441 ON $queue.job_id = $job.id
443 ON $job.mailing_id = $mailing.id
445 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
447 if (!empty($toDate)) {
448 $query .= " AND $unsub.time_stamp <= $toDate";
451 if (!empty($job_id)) {
452 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
455 if ($org_unsubscribe !== NULL) {
456 $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ?
0 : 1);
460 $query .= " GROUP BY $queue.id ";
469 return $dao->unsubs ?
$dao->unsubs
: 0;
474 * Get rows for the event browser.
476 * @param int $mailing_id
479 * Optional ID of the job.
480 * @param bool $is_distinct
481 * Group by queue id?.
484 * @param int $rowCount
489 * @param null $org_unsubscribe
493 public static function &getRows(
494 $mailing_id, $job_id = NULL,
495 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL,
496 $org_unsubscribe = NULL
499 $dao = new CRM_Core_DAO();
501 $unsub = self
::$_tableName;
502 $queueObject = new CRM_Mailing_Event_BAO_Queue();
503 $queue = $queueObject->getTableName();
504 $mailingObject = new CRM_Mailing_BAO_Mailing();
505 $mailing = $mailingObject->getTableName();
506 $jobObject = new CRM_Mailing_BAO_MailingJob();
507 $job = $jobObject->getTableName();
508 $contactObject = new CRM_Contact_BAO_Contact();
509 $contact = $contactObject->getTableName();
510 $emailObject = new CRM_Core_BAO_Email();
511 $email = $emailObject->getTableName();
514 SELECT $contact.display_name as display_name,
515 $contact.id as contact_id,
516 $email.email as email,
517 $unsub.time_stamp as date,
518 $unsub.org_unsubscribe as org_unsubscribe
521 ON $queue.contact_id = $contact.id
523 ON $queue.email_id = $email.id
525 ON $unsub.event_queue_id = $queue.id
527 ON $queue.job_id = $job.id
529 ON $job.mailing_id = $mailing.id
531 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
533 if (!empty($job_id)) {
534 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
537 if ($org_unsubscribe !== NULL) {
538 $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ?
0 : 1);
542 $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe";
545 $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
547 if (is_string($sort)) {
548 $sort = CRM_Utils_Type
::escape($sort, 'String');
552 $orderBy = trim($sort->orderBy());
556 $query .= " ORDER BY {$orderBy} ";
558 if ($offset ||
$rowCount) {
559 //Added "||$rowCount" to avoid displaying all records on first page
560 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
567 while ($dao->fetch()) {
568 $url = CRM_Utils_System
::url('civicrm/contact/view',
569 "reset=1&cid={$dao->contact_id}"
572 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
573 'email' => $dao->email
,
574 // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes.
575 'unsubOrOptout' => ts('Yes'),
576 'date' => CRM_Utils_Date
::customFormat($dao->date
),
583 * @param int $queueID
587 public static function getContactInfo($queueID) {
589 SELECT DISTINCT(civicrm_mailing_event_queue.contact_id) as contact_id,
590 civicrm_contact.display_name as display_name
591 civicrm_email.email as email
592 FROM civicrm_mailing_event_queue,
595 WHERE civicrm_mailing_event_queue.contact_id = civicrm_contact.id
596 AND civicrm_mailing_event_queue.email_id = civicrm_email.id
597 AND civicrm_mailing_event_queue.id = " . CRM_Utils_Type
::escape($queueID, 'Integer');
599 $dao = CRM_Core_DAO
::executeQuery($query);
601 $displayName = 'Unknown';
604 $displayName = $dao->display_name
;
605 $email = $dao->email
;
608 return [$displayName, $email];