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));
226 /* https://lab.civicrm.org/dev/core/-/issues/3031
227 * When 2 separate tables are referenced in an OR clause the index will be used on one & not the other. At the sql
228 * level we usually deal with this by using UNION to join the 2 queries together - the patch is doing the same thing at
229 * the php level & probably as a result performs better than the original not-that-bad OR clause did & likely similarly to
230 * how a UNION would work.
233 SELECT grp.id as group_id,
235 grp.frontend_title as frontend_title,
236 grp.frontend_description as frontend_description,
237 grp.description as description,
238 grp.saved_search_id as saved_search_id
239 FROM civicrm_group grp
240 LEFT JOIN civicrm_group_contact_cache gcc
241 ON gcc.group_id = grp.id
242 WHERE grp.is_hidden = 0
244 AND ((grp.saved_search_id is not null AND gcc.contact_id = %1)
249 SELECT grp.id as group_id,
251 grp.frontend_title as frontend_title,
252 grp.frontend_description as frontend_description,
253 grp.description as description,
254 grp.saved_search_id as saved_search_id
255 FROM civicrm_group grp
256 LEFT JOIN civicrm_group_contact gc
257 ON gc.group_id = grp.id
258 WHERE grp.is_hidden = 0
260 AND ((gc.contact_id = %1
261 AND gc.status = 'Added')
265 1 => [$contact_id, 'Positive'],
267 $doCached = CRM_Core_DAO
::executeQuery($groupsCachedSQL, $groupsParams);
268 $doAdded = CRM_Core_DAO
::executeQuery($groupsAddedSQL, $groupsParams);
272 while ($doCached->fetch()) {
273 $returnGroups[$doCached->group_id
] = [
274 'title' => !empty($doCached->frontend_title
) ?
$doCached->frontend_title
: $doCached->title
,
275 'description' => !empty($doCached->frontend_description
) ?
$doCached->frontend_description
: $doCached->description
,
278 while ($doAdded->fetch()) {
279 $returnGroups[$doAdded->group_id
] = [
280 'title' => !empty($doAdded->frontend_title
) ?
$doAdded->frontend_title
: $doAdded->title
,
281 'description' => !empty($doAdded->frontend_description
) ?
$doAdded->frontend_description
: $doAdded->description
,
284 return $returnGroups;
287 while ($doCached->fetch()) {
288 $groups[$doCached->group_id
] = !empty($doCached->frontend_title
) ?
$doCached->frontend_title
: $doCached->title
;
290 while ($doAdded->fetch()) {
291 $groups[$doAdded->group_id
] = !empty($doAdded->frontend_title
) ?
$doAdded->frontend_title
: $doAdded->title
;
294 $transaction = new CRM_Core_Transaction();
295 $contacts = [$contact_id];
296 foreach ($groups as $group_id => $group_name) {
299 if (in_array($group_id, $baseGroupIds)) {
300 [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact
::addContactsToGroup($contacts, $group_id, 'Email', 'Removed');
303 [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact
::removeContactsFromGroup($contacts, $group_id, 'Email');
307 unset($groups[$group_id]);
311 $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
312 $ue->event_queue_id
= $queue_id;
313 $ue->org_unsubscribe
= 0;
314 $ue->time_stamp
= date('YmdHis');
317 $transaction->commit();
322 * Send a response email informing the contact of the groups from which he.
323 * has been unsubscribed.
325 * @param int $queue_id
326 * The queue event ID.
327 * @param array|null $groups
329 * @param bool $is_domain
330 * Is this domain-level?.
334 public static function send_unsub_response($queue_id, $groups, $is_domain, $job) {
335 $config = CRM_Core_Config
::singleton();
336 $domain = CRM_Core_BAO_Domain
::getDomain();
337 $jobObject = new CRM_Mailing_BAO_MailingJob();
338 $jobTable = $jobObject->getTableName();
339 $mailingObject = new CRM_Mailing_DAO_Mailing();
340 $mailingTable = $mailingObject->getTableName();
341 $contactsObject = new CRM_Contact_DAO_Contact();
342 $contacts = $contactsObject->getTableName();
343 $emailObject = new CRM_Core_DAO_Email();
344 $email = $emailObject->getTableName();
345 $queueObject = new CRM_Mailing_Event_BAO_Queue();
346 $queue = $queueObject->getTableName();
348 //get the default domain email address.
349 [$domainEmailName, $domainEmailAddress] = CRM_Core_BAO_Domain
::getNameAndEmail();
351 $dao = new CRM_Mailing_BAO_Mailing();
352 $dao->query(" SELECT * FROM $mailingTable
353 INNER JOIN $jobTable ON
354 $jobTable.mailing_id = $mailingTable.id
355 WHERE $jobTable.id = $job");
358 $component = new CRM_Mailing_BAO_MailingComponent();
361 $component->id
= $dao->optout_id
;
364 $component->id
= $dao->unsubscribe_id
;
366 $component->find(TRUE);
368 $html = $component->body_html
;
369 if ($component->body_text
) {
370 $text = $component->body_text
;
373 $text = CRM_Utils_String
::htmlToText($component->body_html
);
376 $eq = new CRM_Core_DAO();
378 "SELECT $contacts.preferred_mail_format as format,
379 $contacts.id as contact_id,
380 $email.email as email,
383 INNER JOIN $queue ON $queue.contact_id = $contacts.id
384 INNER JOIN $email ON $queue.email_id = $email.id
385 WHERE $queue.id = " . CRM_Utils_Type
::escape($queue_id, 'Integer')
390 foreach ($groups as $key => $value) {
392 unset($groups[$key]);
397 [$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']);
408 if (!$html ||
$eq->format
== 'Text' ||
$eq->format
== 'Both') {
409 $text = CRM_Utils_Token
::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
410 $text = CRM_Utils_Token
::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id
, $eq->hash
);
411 $text = CRM_Utils_Token
::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']);
412 $text = CRM_Utils_Token
::replaceMailingTokens($text, $dao, NULL, $tokens['text']);
415 $emailDomain = CRM_Core_BAO_MailSettings
::defaultDomain();
418 'subject' => $component->subject
,
419 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>",
420 'toEmail' => $eq->email
,
421 'replyTo' => CRM_Core_BAO_Domain
::getNoReplyEmailAddress(),
422 'returnPath' => CRM_Core_BAO_Domain
::getNoReplyEmailAddress(),
426 CRM_Mailing_BAO_Mailing
::addMessageIdHeader($params, 'u', $job, $queue_id, $eq->hash
);
427 if (CRM_Core_BAO_MailSettings
::includeMessageId()) {
428 $params['messageId'] = $params['Message-ID'];
430 CRM_Utils_Mail
::send($params);
434 * Get row count for the event selector.
436 * @param int $mailing_id
439 * Optional ID of a job to filter on.
440 * @param bool $is_distinct
441 * Group by queue ID?.
443 * @param string $org_unsubscribe
445 * @param string $toDate
448 * Number of rows in result set
450 public static function getTotalCount(
451 $mailing_id, $job_id = NULL,
452 $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL
454 $dao = new CRM_Core_DAO();
456 $unsub = self
::$_tableName;
457 $queueObject = new CRM_Mailing_Event_BAO_Queue();
458 $queue = $queueObject->getTableName();
459 $mailingObject = new CRM_Mailing_BAO_Mailing();
460 $mailing = $mailingObject->getTableName();
461 $jobObject = new CRM_Mailing_BAO_MailingJob();
462 $job = $jobObject->getTableName();
465 SELECT COUNT($unsub.id) as unsubs
468 ON $unsub.event_queue_id = $queue.id
470 ON $queue.job_id = $job.id
472 ON $job.mailing_id = $mailing.id
474 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
476 if (!empty($toDate)) {
477 $query .= " AND $unsub.time_stamp <= $toDate";
480 if (!empty($job_id)) {
481 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
484 if ($org_unsubscribe !== NULL) {
485 $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ?
0 : 1);
489 $query .= " GROUP BY $queue.id ";
498 return $dao->unsubs ?
$dao->unsubs
: 0;
503 * Get rows for the event browser.
505 * @param int $mailing_id
508 * Optional ID of the job.
509 * @param bool $is_distinct
510 * Group by queue id?.
513 * @param int $rowCount
518 * @param null $org_unsubscribe
522 public static function &getRows(
523 $mailing_id, $job_id = NULL,
524 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL,
525 $org_unsubscribe = NULL
528 $dao = new CRM_Core_DAO();
530 $unsub = self
::$_tableName;
531 $queueObject = new CRM_Mailing_Event_BAO_Queue();
532 $queue = $queueObject->getTableName();
533 $mailingObject = new CRM_Mailing_BAO_Mailing();
534 $mailing = $mailingObject->getTableName();
535 $jobObject = new CRM_Mailing_BAO_MailingJob();
536 $job = $jobObject->getTableName();
537 $contactObject = new CRM_Contact_BAO_Contact();
538 $contact = $contactObject->getTableName();
539 $emailObject = new CRM_Core_BAO_Email();
540 $email = $emailObject->getTableName();
543 SELECT $contact.display_name as display_name,
544 $contact.id as contact_id,
545 $email.email as email,
546 $unsub.time_stamp as date,
547 $unsub.org_unsubscribe as org_unsubscribe
550 ON $queue.contact_id = $contact.id
552 ON $queue.email_id = $email.id
554 ON $unsub.event_queue_id = $queue.id
556 ON $queue.job_id = $job.id
558 ON $job.mailing_id = $mailing.id
560 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
562 if (!empty($job_id)) {
563 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
566 if ($org_unsubscribe !== NULL) {
567 $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ?
0 : 1);
571 $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe";
574 $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
576 if (is_string($sort)) {
577 $sort = CRM_Utils_Type
::escape($sort, 'String');
581 $orderBy = trim($sort->orderBy());
585 $query .= " ORDER BY {$orderBy} ";
587 if ($offset ||
$rowCount) {
588 //Added "||$rowCount" to avoid displaying all records on first page
589 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
596 while ($dao->fetch()) {
597 $url = CRM_Utils_System
::url('civicrm/contact/view',
598 "reset=1&cid={$dao->contact_id}"
601 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
602 'email' => $dao->email
,
603 // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes.
604 'unsubOrOptout' => ts('Yes'),
605 'date' => CRM_Utils_Date
::customFormat($dao->date
),
612 * @param int $queueID
616 public static function getContactInfo($queueID) {
618 SELECT DISTINCT(civicrm_mailing_event_queue.contact_id) as contact_id,
619 civicrm_contact.display_name as display_name
620 civicrm_email.email as email
621 FROM civicrm_mailing_event_queue,
624 WHERE civicrm_mailing_event_queue.contact_id = civicrm_contact.id
625 AND civicrm_mailing_event_queue.email_id = civicrm_email.id
626 AND civicrm_mailing_event_queue.id = " . CRM_Utils_Type
::escape($queueID, 'Integer');
628 $dao = CRM_Core_DAO
::executeQuery($query);
630 $displayName = 'Unknown';
633 $displayName = $dao->display_name
;
634 $email = $dao->email
;
637 return [$displayName, $email];