CRM/Mailing/Event/BAO/Unsubscribe.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17
  18 require_once 'Mail/mime.php';
  19
  20 /**
  21  * Class CRM_Mailing_Event_BAO_Unsubscribe
  22  */
  23 class CRM_Mailing_Event_BAO_Unsubscribe extends CRM_Mailing_Event_DAO_Unsubscribe {
  24
  25   /**
  26    * Unsubscribe a contact from the domain.
  27    *
  28    * @param int $job_id
  29    *   The job ID.
  30    * @param int $queue_id
  31    *   The Queue Event ID of the recipient.
  32    * @param string $hash
  33    *   The hash.
  34    *
  35    * @return bool
  36    *   Was the contact successfully unsubscribed?
  37    */
  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);
  40     if (!$q) {
  41       return FALSE;
  42     }
  43
  44     $transaction = new CRM_Core_Transaction();
  45
  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)) {
  51         $sql = "
  52 UPDATE civicrm_email
  53 SET    on_hold = 2,
  54        hold_date = %1
  55 WHERE  email = %2
  56 ";
  57         $sqlParams = [
  58           1 => [$now, 'Timestamp'],
  59           2 => [$email->email, 'String'],
  60         ];
  61         CRM_Core_DAO::executeQuery($sql, $sqlParams);
  62       }
  63     }
  64     else {
  65       $contact = new CRM_Contact_BAO_Contact();
  66       $contact->id = $q->contact_id;
  67       $contact->is_opt_out = TRUE;
  68       $contact->save();
  69     }
  70
  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;
  75     $ue->save();
  76
  77     $shParams = [
  78       'contact_id' => $q->contact_id,
  79       'group_id' => NULL,
  80       'status' => 'Removed',
  81       'method' => 'Email',
  82       'tracking' => $ue->id,
  83     ];
  84     CRM_Contact_BAO_SubscriptionHistory::create($shParams);
  85
  86     $transaction->commit();
  87
  88     return TRUE;
  89   }
  90
  91   /**
  92    * Unsubscribe a contact from all groups that received this mailing.
  93    *
  94    * @param int $job_id
  95    *   The job ID.
  96    * @param int $queue_id
  97    *   The Queue Event ID of the recipient.
  98    * @param string $hash
  99    *   The hash.
 100    * @param bool $return
 101    *   If true return the list of groups.
 102    *
 103    * @return array|null
 104    *   $groups    Array of all groups from which the contact was removed, or null if the queue event could not be found.
 105    *
 106    * @throws \CRM_Core_Exception
 107    * @throws \CiviCRM_API3_Exception
 108    */
 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.
 111
 112     $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
 113     if (!$q) {
 114       return NULL;
 115     }
 116
 117     $contact_id = $q->contact_id;
 118
 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');
 121
 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;
 126
 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;
 135       }
 136       else {
 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;
 143         }
 144         // (otherwise we were passed in variant A so we already have the relevant_mailing_id correct already.)
 145       }
 146     }
 147
 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
 150     // UI.
 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')");
 158
 159     $groups = [];
 160     $base_groups = [];
 161     $mailings = [];
 162
 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;
 168         }
 169         else {
 170           $groups[$do->entity_id] = NULL;
 171         }
 172       }
 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;
 176       }
 177     }
 178
 179     // As long as we have prior mailings, find their groups and add to the
 180     // list.
 181
 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'");
 189
 190       $mailings = [];
 191
 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;
 196         }
 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;
 200         }
 201       }
 202     }
 203
 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));
 208
 209     $baseGroupIds = array_keys($base_groups);
 210     CRM_Utils_Hook::unsubscribeGroups('unsubscribe', $mailing_id, $contact_id, $groupIds, $baseGroupIds);
 211
 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) . ")";
 218     }
 219     $groupIdClause = '';
 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));
 224     }
 225
 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.
 231      */
 232     $groupsCachedSQL = "
 233             SELECT      grp.id as group_id,
 234                         grp.title as title,
 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
 243                         $groupIdClause
 244                 AND     ((grp.saved_search_id is not null AND gcc.contact_id = %1)
 245                             $baseGroupClause
 246                         ) GROUP BY grp.id";
 247
 248     $groupsAddedSQL = "
 249             SELECT      grp.id as group_id,
 250                         grp.title as title,
 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
 259                         $groupIdClause
 260                 AND     ((gc.contact_id = %1
 261                                 AND gc.status = 'Added')
 262                             $baseGroupClause
 263                         ) GROUP BY grp.id";
 264     $groupsParams = [
 265       1 => [$contact_id, 'Positive'],
 266     ];
 267     $doCached = CRM_Core_DAO::executeQuery($groupsCachedSQL, $groupsParams);
 268     $doAdded = CRM_Core_DAO::executeQuery($groupsAddedSQL, $groupsParams);
 269
 270     if ($return) {
 271       $returnGroups = [];
 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,
 276         ];
 277       }
 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,
 282         ];
 283       }
 284       return $returnGroups;
 285     }
 286     else {
 287       while ($doCached->fetch()) {
 288         $groups[$doCached->group_id] = !empty($doCached->frontend_title) ? $doCached->frontend_title : $doCached->title;
 289       }
 290       while ($doAdded->fetch()) {
 291         $groups[$doAdded->group_id] = !empty($doAdded->frontend_title) ? $doAdded->frontend_title : $doAdded->title;
 292       }
 293     }
 294     $transaction = new CRM_Core_Transaction();
 295     $contacts = [$contact_id];
 296     foreach ($groups as $group_id => $group_name) {
 297       $notremoved = FALSE;
 298       if ($group_name) {
 299         if (in_array($group_id, $baseGroupIds)) {
 300           [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact::addContactsToGroup($contacts, $group_id, 'Email', 'Removed');
 301         }
 302         else {
 303           [$total, $removed, $notremoved] = CRM_Contact_BAO_GroupContact::removeContactsFromGroup($contacts, $group_id, 'Email');
 304         }
 305       }
 306       if ($notremoved) {
 307         unset($groups[$group_id]);
 308       }
 309     }
 310
 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');
 315     $ue->save();
 316
 317     $transaction->commit();
 318     return $groups;
 319   }
 320
 321   /**
 322    * Send a response email informing the contact of the groups from which he.
 323    * has been unsubscribed.
 324    *
 325    * @param int $queue_id
 326    *   The queue event ID.
 327    * @param array|null $groups
 328    *   List of group IDs.
 329    * @param bool $is_domain
 330    *   Is this domain-level?.
 331    * @param int $job
 332    *   The job ID.
 333    */
 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();
 347
 348     //get the default domain email address.
 349     [$domainEmailName, $domainEmailAddress] = CRM_Core_BAO_Domain::getNameAndEmail();
 350
 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");
 356     $dao->fetch();
 357
 358     $component = new CRM_Mailing_BAO_MailingComponent();
 359
 360     if ($is_domain) {
 361       $component->id = $dao->optout_id;
 362     }
 363     else {
 364       $component->id = $dao->unsubscribe_id;
 365     }
 366     $component->find(TRUE);
 367
 368     $html = $component->body_html;
 369     if ($component->body_text) {
 370       $text = $component->body_text;
 371     }
 372     else {
 373       $text = CRM_Utils_String::htmlToText($component->body_html);
 374     }
 375
 376     $eq = new CRM_Core_DAO();
 377     $eq->query(
 378       "SELECT     $contacts.preferred_mail_format as format,
 379                     $contacts.id as contact_id,
 380                     $email.email as email,
 381                     $queue.hash as hash
 382         FROM        $contacts
 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')
 386     );
 387     $eq->fetch();
 388
 389     if ($groups) {
 390       foreach ($groups as $key => $value) {
 391         if (!$value) {
 392           unset($groups[$key]);
 393         }
 394       }
 395     }
 396
 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']);
 407     }
 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']);
 413     }
 414
 415     $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
 416
 417     $params = [
 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(),
 423       'html' => $html,
 424       'text' => $text,
 425     ];
 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'];
 429     }
 430     CRM_Utils_Mail::send($params);
 431   }
 432
 433   /**
 434    * Get row count for the event selector.
 435    *
 436    * @param int $mailing_id
 437    *   ID of the mailing.
 438    * @param int $job_id
 439    *   Optional ID of a job to filter on.
 440    * @param bool $is_distinct
 441    *   Group by queue ID?.
 442    *
 443    * @param string $org_unsubscribe
 444    *
 445    * @param string $toDate
 446    *
 447    * @return int
 448    *   Number of rows in result set
 449    */
 450   public static function getTotalCount(
 451     $mailing_id, $job_id = NULL,
 452     $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL
 453   ) {
 454     $dao = new CRM_Core_DAO();
 455
 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();
 463
 464     $query = "
 465             SELECT      COUNT($unsub.id) as unsubs
 466             FROM        $unsub
 467             INNER JOIN  $queue
 468                     ON  $unsub.event_queue_id = $queue.id
 469             INNER JOIN  $job
 470                     ON  $queue.job_id = $job.id
 471             INNER JOIN  $mailing
 472                     ON  $job.mailing_id = $mailing.id
 473                     AND $job.is_test = 0
 474             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 475
 476     if (!empty($toDate)) {
 477       $query .= " AND $unsub.time_stamp <= $toDate";
 478     }
 479
 480     if (!empty($job_id)) {
 481       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 482     }
 483
 484     if ($org_unsubscribe !== NULL) {
 485       $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
 486     }
 487
 488     if ($is_distinct) {
 489       $query .= " GROUP BY $queue.id ";
 490     }
 491
 492     $dao->query($query);
 493     $dao->fetch();
 494     if ($is_distinct) {
 495       return $dao->N;
 496     }
 497     else {
 498       return $dao->unsubs ? $dao->unsubs : 0;
 499     }
 500   }
 501
 502   /**
 503    * Get rows for the event browser.
 504    *
 505    * @param int $mailing_id
 506    *   ID of the mailing.
 507    * @param int $job_id
 508    *   Optional ID of the job.
 509    * @param bool $is_distinct
 510    *   Group by queue id?.
 511    * @param int $offset
 512    *   Offset.
 513    * @param int $rowCount
 514    *   Number of rows.
 515    * @param array $sort
 516    *   Sort array.
 517    *
 518    * @param null $org_unsubscribe
 519    * @return array
 520    *   Result set
 521    */
 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
 526   ) {
 527
 528     $dao = new CRM_Core_DAO();
 529
 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();
 541
 542     $query = "
 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
 548             FROM        $contact
 549             INNER JOIN  $queue
 550                     ON  $queue.contact_id = $contact.id
 551             INNER JOIN  $email
 552                     ON  $queue.email_id = $email.id
 553             INNER JOIN  $unsub
 554                     ON  $unsub.event_queue_id = $queue.id
 555             INNER JOIN  $job
 556                     ON  $queue.job_id = $job.id
 557             INNER JOIN  $mailing
 558                     ON  $job.mailing_id = $mailing.id
 559                     AND $job.is_test = 0
 560             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 561
 562     if (!empty($job_id)) {
 563       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 564     }
 565
 566     if ($org_unsubscribe !== NULL) {
 567       $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
 568     }
 569
 570     if ($is_distinct) {
 571       $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe";
 572     }
 573
 574     $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
 575     if ($sort) {
 576       if (is_string($sort)) {
 577         $sort = CRM_Utils_Type::escape($sort, 'String');
 578         $orderBy = $sort;
 579       }
 580       else {
 581         $orderBy = trim($sort->orderBy());
 582       }
 583     }
 584
 585     $query .= " ORDER BY {$orderBy} ";
 586
 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');
 590     }
 591
 592     $dao->query($query);
 593
 594     $results = [];
 595
 596     while ($dao->fetch()) {
 597       $url = CRM_Utils_System::url('civicrm/contact/view',
 598         "reset=1&cid={$dao->contact_id}"
 599       );
 600       $results[] = [
 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),
 606       ];
 607     }
 608     return $results;
 609   }
 610
 611   /**
 612    * @param int $queueID
 613    *
 614    * @return array
 615    */
 616   public static function getContactInfo($queueID) {
 617     $query = "
 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,
 622        civicrm_contact,
 623        civicrm_email
 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');
 627
 628     $dao = CRM_Core_DAO::executeQuery($query);
 629
 630     $displayName = 'Unknown';
 631     $email = 'Unknown';
 632     if ($dao->fetch()) {
 633       $displayName = $dao->display_name;
 634       $email = $dao->email;
 635     }
 636
 637     return [$displayName, $email];
 638   }
 639
 640 }