CRM/Mailing/Event/BAO/Unsubscribe.php

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