CRM/Mailing/Event/BAO/Unsubscribe.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33
  34 require_once 'Mail/mime.php';
  35
  36 /**
  37  * Class CRM_Mailing_Event_BAO_Unsubscribe
  38  */
  39 class CRM_Mailing_Event_BAO_Unsubscribe extends CRM_Mailing_Event_DAO_Unsubscribe {
  40
  41   /**
  42    * Class constructor.
  43    */
  44   public function __construct() {
  45     parent::__construct();
  46   }
  47
  48   /**
  49    * Unsubscribe a contact from the domain.
  50    *
  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.
  57    *
  58    * @return bool
  59    *   Was the contact successfully unsubscribed?
  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 ";
  80         $sqlParams = [
  81           1 => [$now, 'Timestamp'],
  82           2 => [$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 = [
 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   /**
 115    * Unsubscribe a contact from all groups that received this mailing.
 116    *
 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.
 125    *
 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.
 128    */
 129   public static function &unsub_from_mailing($job_id, $queue_id, $hash, $return = FALSE) {
 130     // First make sure there's a matching queue event.
 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();
 142     $mgObject = new CRM_Mailing_DAO_MailingGroup();
 143     $mg = $mgObject->getTableName();
 144     $jobObject = new CRM_Mailing_BAO_MailingJob();
 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();
 152     $abObject = new CRM_Mailing_DAO_MailingAB();
 153     $ab = $abObject->getTableName();
 154
 155     //We Need the mailing Id for the hook...
 156     $do->query("SELECT $job.mailing_id as mailing_id
 157                      FROM   $job
 158                      WHERE $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
 159     $do->fetch();
 160     $mailing_id = $do->mailing_id;
 161     $mailing_type = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_Mailing', $mailing_id, 'mailing_type', 'id');
 162     $entity = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingGroup', $mailing_id, 'entity_table', 'mailing_id');
 163
 164     // If $entity is null and $mailing_Type is either winner or experiment then we are deailing with an AB test
 165     $abtest_types = ['experiment', 'winner'];
 166     if (empty($entity) && in_array($mailing_type, $abtest_types)) {
 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";
 175       $entity = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_MailingGroup', $mailing_id_a, 'entity_table', 'mailing_id');
 176     }
 177     else {
 178       $jobJoin = "INNER JOIN  $job ON      $job.mailing_id = $mg.mailing_id";
 179     }
 180
 181     $groupClause = '';
 182     if ($entity == $group) {
 183       $groupClause = "AND $group.is_hidden = 0";
 184     }
 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
 191             $jobJoin
 192             INNER JOIN  $entity
 193                 ON      $mg.entity_id = $entity.id
 194             WHERE       $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer') . "
 195                 AND     $mg.group_type IN ('Include', 'Base') $groupClause"
 196     );
 197
 198     // Make a list of groups and a list of prior mailings that received
 199     // this mailing.
 200
 201     $groups = [];
 202     $base_groups = [];
 203     $mailings = [];
 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
 219     // As long as we have prior mailings, find their groups and add to the
 220     // list.
 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 = [];
 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.
 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);
 249
 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.
 253     $baseGroupClause = '';
 254     if (!empty($baseGroupIds)) {
 255       $baseGroupClause = "OR  $group.id IN(" . implode(', ', $baseGroupIds) . ")";
 256     }
 257     $groupIdClause = '';
 258     if ($groupIds || $baseGroupIds) {
 259       $groupIdClause = "AND $group.id IN (" . implode(', ', array_merge($groupIds, $baseGroupIds)) . ")";
 260     }
 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
 268             WHERE       $group.is_hidden = 0
 269                         $groupIdClause
 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 = [];
 278       while ($do->fetch()) {
 279         $returnGroups[$do->group_id] = [
 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 = [$contact_id];
 293     foreach ($groups as $group_id => $group_name) {
 294       $notremoved = FALSE;
 295       if ($group_name) {
 296         if (in_array($group_id, $baseGroupIds)) {
 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   /**
 319    * Send a response email informing the contact of the groups from which he.
 320    * has been unsubscribed.
 321    *
 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.
 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
 335     $jobObject = new CRM_Mailing_BAO_MailingJob();
 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();
 350     $dao->query("   SELECT * FROM $mailingTable
 351                         INNER JOIN $jobTable ON
 352                             $jobTable.mailing_id = $mailingTable.id
 353                         WHERE $jobTable.id = $job");
 354     $dao->fetch();
 355
 356     $component = new CRM_Mailing_BAO_MailingComponent();
 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 = [
 420       'Subject' => $component->subject,
 421       'From' => "\"$domainEmailName\" <" . CRM_Core_BAO_Domain::getNoReplyEmailAddress() . '>',
 422       'To' => $eq->email,
 423       'Reply-To' => CRM_Core_BAO_Domain::getNoReplyEmailAddress(),
 424       'Return-Path' => CRM_Core_BAO_Domain::getNoReplyEmailAddress(),
 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
 431     $mailer = \Civi::service('pear_mail');
 432
 433     if (is_object($mailer)) {
 434       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
 435       $mailer->send($eq->email, $h, $b);
 436       unset($errorScope);
 437     }
 438   }
 439
 440   /**
 441    * Get row count for the event selector.
 442    *
 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?.
 449    *
 450    * @param string $org_unsubscribe
 451    *
 452    * @param string $toDate
 453    *
 454    * @return int
 455    *   Number of rows in result set
 456    */
 457   public static function getTotalCount(
 458     $mailing_id, $job_id = NULL,
 459     $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL
 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();
 468     $jobObject = new CRM_Mailing_BAO_MailingJob();
 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
 483     if (!empty($toDate)) {
 484       $query .= " AND $unsub.time_stamp <= $toDate";
 485     }
 486
 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   /**
 510    * Get rows for the event browser.
 511    *
 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.
 524    *
 525    * @param null $org_unsubscribe
 526    * @return array
 527    *   Result set
 528    */
 529   public static function &getRows(
 530     $mailing_id, $job_id = NULL,
 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();
 542     $jobObject = new CRM_Mailing_BAO_MailingJob();
 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) {
 578       $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe";
 579     }
 580
 581     $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
 582     if ($sort) {
 583       if (is_string($sort)) {
 584         $sort = CRM_Utils_Type::escape($sort, 'String');
 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 = [];
 602
 603     while ($dao->fetch()) {
 604       $url = CRM_Utils_System::url('civicrm/contact/view',
 605         "reset=1&cid={$dao->contact_id}"
 606       );
 607       $results[] = [
 608         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 609         'email' => $dao->email,
 610         // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes.
 611         'unsubOrOptout' => ts('Yes'),
 612         'date' => CRM_Utils_Date::customFormat($dao->date),
 613       ];
 614     }
 615     return $results;
 616   }
 617
 618   /**
 619    * @param int $queueID
 620    *
 621    * @return array
 622    */
 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
 635     $dao = CRM_Core_DAO::executeQuery($query);
 636
 637     $displayName = 'Unknown';
 638     $email = 'Unknown';
 639     if ($dao->fetch()) {
 640       $displayName = $dao->display_name;
 641       $email = $dao->email;
 642     }
 643
 644     return [$displayName, $email];
 645   }
 646
 647 }