CRM/Mailing/Event/BAO/Unsubscribe.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35
  36 require_once 'Mail/mime.php';
  37
  38 /**
  39  * Class CRM_Mailing_Event_BAO_Unsubscribe
  40  */
  41 class CRM_Mailing_Event_BAO_Unsubscribe extends CRM_Mailing_Event_DAO_Unsubscribe {
  42
  43   /**
  44    * Class constructor
  45    */
  46   public function __construct() {
  47     parent::__construct();
  48   }
  49
  50   /**
  51    * Unsubscribe a contact from the domain
  52    *
  53    * @param int $job_id
  54    *   The job ID.
  55    * @param int $queue_id
  56    *   The Queue Event ID of the recipient.
  57    * @param string $hash
  58    *   The hash.
  59    *
  60    * @return boolean          Was the contact successfully unsubscribed?
  61    * @static
  62    */
  63   public static function unsub_from_domain($job_id, $queue_id, $hash) {
  64     $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
  65     if (!$q) {
  66       return FALSE;
  67     }
  68
  69     $transaction = new CRM_Core_Transaction();
  70
  71     $now = date('YmdHis');
  72     if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
  73       $email = new CRM_Core_BAO_Email();
  74       $email->id = $q->email_id;
  75       if ($email->find(TRUE)) {
  76         $sql = "
  77 UPDATE civicrm_email
  78 SET    on_hold = 2,
  79        hold_date = %1
  80 WHERE  email = %2
  81 ";
  82         $sqlParams = array(
  83         1 => array($now, 'Timestamp'),
  84           2 => array($email->email, 'String'),
  85         );
  86         CRM_Core_DAO::executeQuery($sql, $sqlParams);
  87       }
  88     }
  89     else {
  90       $contact = new CRM_Contact_BAO_Contact();
  91       $contact->id = $q->contact_id;
  92       $contact->is_opt_out = TRUE;
  93       $contact->save();
  94     }
  95
  96     $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
  97     $ue->event_queue_id = $queue_id;
  98     $ue->org_unsubscribe = 1;
  99     $ue->time_stamp = $now;
 100     $ue->save();
 101
 102     $shParams = array(
 103       'contact_id' => $q->contact_id,
 104       'group_id' => NULL,
 105       'status' => 'Removed',
 106       'method' => 'Email',
 107       'tracking' => $ue->id,
 108     );
 109     CRM_Contact_BAO_SubscriptionHistory::create($shParams);
 110
 111     $transaction->commit();
 112
 113     return TRUE;
 114   }
 115
 116   /**
 117    * Unsubscribe a contact from all groups that received this mailing
 118    *
 119    * @param int $job_id
 120    *   The job ID.
 121    * @param int $queue_id
 122    *   The Queue Event ID of the recipient.
 123    * @param string $hash
 124    *   The hash.
 125    * @param bool $return
 126    *   If true return the list of groups.
 127    *
 128    * @return array|null $groups    Array of all groups from which the contact was removed, or null if the queue event could not be found.
 129    * @static
 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
 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
 162     $do->query("
 163             SELECT      $mg.entity_table as entity_table,
 164                         $mg.entity_id as entity_id,
 165                         $mg.group_type as group_type
 166             FROM        $mg
 167             INNER JOIN  $job
 168                 ON      $job.mailing_id = $mg.mailing_id
 169             INNER JOIN  $group
 170                 ON      $mg.entity_id = $group.id
 171             WHERE       $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer') . "
 172                 AND     $mg.group_type IN ('Include', 'Base')
 173                 AND     $group.is_hidden = 0"
 174     );
 175
 176     /* Make a list of groups and a list of prior mailings that received
 177          * this mailing */
 178
 179     $groups = array();
 180     $base_groups = array();
 181     $mailings = array();
 182
 183     while ($do->fetch()) {
 184       if ($do->entity_table == $group) {
 185         if ($do->group_type == 'Base') {
 186           $base_groups[$do->entity_id] = NULL;
 187         }
 188         else {
 189           $groups[$do->entity_id] = NULL;
 190         }
 191       }
 192       elseif ($do->entity_table == $mailing) {
 193         $mailings[] = $do->entity_id;
 194       }
 195     }
 196
 197     /* As long as we have prior mailings, find their groups and add to the
 198          * list */
 199
 200     while (!empty($mailings)) {
 201       $do->query("
 202                 SELECT      $mg.entity_table as entity_table,
 203                             $mg.entity_id as entity_id
 204                 FROM        $mg
 205                 WHERE       $mg.mailing_id IN (" . implode(', ', $mailings) . ")
 206                     AND     $mg.group_type = 'Include'");
 207
 208       $mailings = array();
 209
 210       while ($do->fetch()) {
 211         if ($do->entity_table == $group) {
 212           $groups[$do->entity_id] = TRUE;
 213         }
 214         elseif ($do->entity_table == $mailing) {
 215           $mailings[] = $do->entity_id;
 216         }
 217       }
 218     }
 219
 220     //Pass the groups to be unsubscribed from through a hook.
 221     $group_ids = array_keys($groups);
 222     $base_group_ids = array_keys($base_groups);
 223     CRM_Utils_Hook::unsubscribeGroups('unsubscribe', $mailing_id, $contact_id, $group_ids, $base_group_ids);
 224
 225     /* Now we have a complete list of recipient groups.  Filter out all
 226          * those except smart groups, those that the contact belongs to and
 227          * base groups from search based mailings */
 228
 229     $baseGroupClause = '';
 230     if (!empty($base_group_ids)) {
 231       $baseGroupClause = "OR  $group.id IN(" . implode(', ', $base_group_ids) . ")";
 232     }
 233     $do->query("
 234             SELECT      $group.id as group_id,
 235                         $group.title as title,
 236                         $group.description as description
 237             FROM        $group
 238             LEFT JOIN   $gc
 239                 ON      $gc.group_id = $group.id
 240             WHERE       $group.id IN (" . implode(', ', array_merge($group_ids, $base_group_ids)) . ")
 241                 AND     $group.is_hidden = 0
 242                 AND     ($group.saved_search_id is not null
 243                             OR  ($gc.contact_id = $contact_id
 244                                 AND $gc.status = 'Added')
 245                             $baseGroupClause
 246                         )");
 247
 248     if ($return) {
 249       $returnGroups = array();
 250       while ($do->fetch()) {
 251         $returnGroups[$do->group_id] = array(
 252           'title' => $do->title,
 253           'description' => $do->description,
 254         );
 255       }
 256       return $returnGroups;
 257     }
 258     else {
 259       while ($do->fetch()) {
 260         $groups[$do->group_id] = $do->title;
 261       }
 262     }
 263
 264     $contacts = array($contact_id);
 265     foreach ($groups as $group_id => $group_name) {
 266       $notremoved = FALSE;
 267       if ($group_name) {
 268         if (in_array($group_id, $base_group_ids)) {
 269           list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::addContactsToGroup($contacts, $group_id, 'Email', 'Removed');
 270         }
 271         else {
 272           list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::removeContactsFromGroup($contacts, $group_id, 'Email');
 273         }
 274       }
 275       if ($notremoved) {
 276         unset($groups[$group_id]);
 277       }
 278     }
 279
 280     $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
 281     $ue->event_queue_id = $queue_id;
 282     $ue->org_unsubscribe = 0;
 283     $ue->time_stamp = date('YmdHis');
 284     $ue->save();
 285
 286     $transaction->commit();
 287     return $groups;
 288   }
 289
 290   /**
 291    * Send a reponse email informing the contact of the groups from which he
 292    * has been unsubscribed.
 293    *
 294    * @param string $queue_id
 295    *   The queue event ID.
 296    * @param array $groups
 297    *   List of group IDs.
 298    * @param bool $is_domain
 299    *   Is this domain-level?.
 300    * @param int $job
 301    *   The job ID.
 302    *
 303    * @return void
 304    * @static
 305    */
 306   public static function send_unsub_response($queue_id, $groups, $is_domain = FALSE, $job) {
 307     $config = CRM_Core_Config::singleton();
 308     $domain = CRM_Core_BAO_Domain::getDomain();
 309
 310     $jobObject = new CRM_Mailing_BAO_MailingJob();
 311     $jobTable = $jobObject->getTableName();
 312     $mailingObject = new CRM_Mailing_DAO_Mailing();
 313     $mailingTable = $mailingObject->getTableName();
 314     $contactsObject = new CRM_Contact_DAO_Contact();
 315     $contacts = $contactsObject->getTableName();
 316     $emailObject = new CRM_Core_DAO_Email();
 317     $email = $emailObject->getTableName();
 318     $queueObject = new CRM_Mailing_Event_BAO_Queue();
 319     $queue = $queueObject->getTableName();
 320
 321     //get the default domain email address.
 322     list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
 323
 324     $dao = new CRM_Mailing_BAO_Mailing();
 325     $dao->query("   SELECT * FROM $mailingTable
 326                         INNER JOIN $jobTable ON
 327                             $jobTable.mailing_id = $mailingTable.id
 328                         WHERE $jobTable.id = $job");
 329     $dao->fetch();
 330
 331     $component = new CRM_Mailing_BAO_Component();
 332
 333     if ($is_domain) {
 334       $component->id = $dao->optout_id;
 335     }
 336     else {
 337       $component->id = $dao->unsubscribe_id;
 338     }
 339     $component->find(TRUE);
 340
 341     $html = $component->body_html;
 342     if ($component->body_text) {
 343       $text = $component->body_text;
 344     }
 345     else {
 346       $text = CRM_Utils_String::htmlToText($component->body_html);
 347     }
 348
 349     $eq = new CRM_Core_DAO();
 350     $eq->query(
 351       "SELECT     $contacts.preferred_mail_format as format,
 352                     $contacts.id as contact_id,
 353                     $email.email as email,
 354                     $queue.hash as hash
 355         FROM        $contacts
 356         INNER JOIN  $queue ON $queue.contact_id = $contacts.id
 357         INNER JOIN  $email ON $queue.email_id = $email.id
 358         WHERE       $queue.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')
 359     );
 360     $eq->fetch();
 361
 362     if ($groups) {
 363       foreach ($groups as $key => $value) {
 364         if (!$value) {
 365           unset($groups[$key]);
 366         }
 367       }
 368     }
 369
 370     $message = new Mail_mime("\n");
 371
 372     list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email);
 373     $bao = new CRM_Mailing_BAO_Mailing();
 374     $bao->body_text = $text;
 375     $bao->body_html = $html;
 376     $tokens = $bao->getTokens();
 377     if ($eq->format == 'HTML' || $eq->format == 'Both') {
 378       $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']);
 379       $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id, $eq->hash);
 380       $html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']);
 381       $html = CRM_Utils_Token::replaceMailingTokens($html, $dao, NULL, $tokens['html']);
 382       $message->setHTMLBody($html);
 383     }
 384     if (!$html || $eq->format == 'Text' || $eq->format == 'Both') {
 385       $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
 386       $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id, $eq->hash);
 387       $text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']);
 388       $text = CRM_Utils_Token::replaceMailingTokens($text, $dao, NULL, $tokens['text']);
 389       $message->setTxtBody($text);
 390     }
 391
 392     $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
 393
 394     $headers = array(
 395       'Subject' => $component->subject,
 396       'From' => "\"$domainEmailName\" <do-not-reply@$emailDomain>",
 397       'To' => $eq->email,
 398       'Reply-To' => "do-not-reply@$emailDomain",
 399       'Return-Path' => "do-not-reply@$emailDomain",
 400     );
 401     CRM_Mailing_BAO_Mailing::addMessageIdHeader($headers, 'u', $job, $queue_id, $eq->hash);
 402
 403     $b = CRM_Utils_Mail::setMimeParams($message);
 404     $h = $message->headers($headers);
 405
 406     $mailer = $config->getMailer();
 407
 408     if (is_object($mailer)) {
 409       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
 410       $mailer->send($eq->email, $h, $b);
 411       unset($errorScope);
 412     }
 413   }
 414
 415   /**
 416    * Get row count for the event selector
 417    *
 418    * @param int $mailing_id
 419    *   ID of the mailing.
 420    * @param int $job_id
 421    *   Optional ID of a job to filter on.
 422    * @param bool $is_distinct
 423    *   Group by queue ID?.
 424    *
 425    * @param null $org_unsubscribe
 426    *
 427    * @return int                  Number of rows in result set
 428    * @static
 429    */
 430   public static function getTotalCount($mailing_id, $job_id = NULL,
 431     $is_distinct = FALSE, $org_unsubscribe = NULL, $toDate = NULL
 432   ) {
 433     $dao = new CRM_Core_DAO();
 434
 435     $unsub = self::$_tableName;
 436     $queueObject = new CRM_Mailing_Event_BAO_Queue();
 437     $queue = $queueObject->getTableName();
 438     $mailingObject = new CRM_Mailing_BAO_Mailing();
 439     $mailing = $mailingObject->getTableName();
 440     $jobObject = new CRM_Mailing_BAO_MailingJob();
 441     $job = $jobObject->getTableName();
 442
 443     $query = "
 444             SELECT      COUNT($unsub.id) as unsubs
 445             FROM        $unsub
 446             INNER JOIN  $queue
 447                     ON  $unsub.event_queue_id = $queue.id
 448             INNER JOIN  $job
 449                     ON  $queue.job_id = $job.id
 450             INNER JOIN  $mailing
 451                     ON  $job.mailing_id = $mailing.id
 452                     AND $job.is_test = 0
 453             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 454
 455     if (!empty($toDate)) {
 456       $query .= " AND $unsub.time_stamp <= $toDate";
 457     }
 458
 459     if (!empty($job_id)) {
 460       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 461     }
 462
 463     if ($org_unsubscribe !== NULL) {
 464       $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
 465     }
 466
 467     if ($is_distinct) {
 468       $query .= " GROUP BY $queue.id ";
 469     }
 470
 471     $dao->query($query);
 472     $dao->fetch();
 473     if ($is_distinct) {
 474       return $dao->N;
 475     }
 476     else {
 477       return $dao->unsubs ? $dao->unsubs : 0;
 478     }
 479   }
 480
 481   /**
 482    * Get rows for the event browser
 483    *
 484    * @param int $mailing_id
 485    *   ID of the mailing.
 486    * @param int $job_id
 487    *   Optional ID of the job.
 488    * @param bool $is_distinct
 489    *   Group by queue id?.
 490    * @param int $offset
 491    *   Offset.
 492    * @param int $rowCount
 493    *   Number of rows.
 494    * @param array $sort
 495    *   Sort array.
 496    *
 497    * @param null $org_unsubscribe
 498    * @return array                Result set
 499    * @static
 500    */
 501   public static function &getRows($mailing_id, $job_id = NULL,
 502     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL,
 503     $org_unsubscribe = NULL
 504   ) {
 505
 506     $dao = new CRM_Core_Dao();
 507
 508     $unsub = self::$_tableName;
 509     $queueObject = new CRM_Mailing_Event_BAO_Queue();
 510     $queue = $queueObject->getTableName();
 511     $mailingObject = new CRM_Mailing_BAO_Mailing();
 512     $mailing = $mailingObject->getTableName();
 513     $jobObject = new CRM_Mailing_BAO_MailingJob();
 514     $job = $jobObject->getTableName();
 515     $contactObject = new CRM_Contact_BAO_Contact();
 516     $contact = $contactObject->getTableName();
 517     $emailObject = new CRM_Core_BAO_Email();
 518     $email = $emailObject->getTableName();
 519
 520     $query = "
 521             SELECT      $contact.display_name as display_name,
 522                         $contact.id as contact_id,
 523                         $email.email as email,
 524                         $unsub.time_stamp as date,
 525                         $unsub.org_unsubscribe as org_unsubscribe
 526             FROM        $contact
 527             INNER JOIN  $queue
 528                     ON  $queue.contact_id = $contact.id
 529             INNER JOIN  $email
 530                     ON  $queue.email_id = $email.id
 531             INNER JOIN  $unsub
 532                     ON  $unsub.event_queue_id = $queue.id
 533             INNER JOIN  $job
 534                     ON  $queue.job_id = $job.id
 535             INNER JOIN  $mailing
 536                     ON  $job.mailing_id = $mailing.id
 537                     AND $job.is_test = 0
 538             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 539
 540     if (!empty($job_id)) {
 541       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 542     }
 543
 544     if ($org_unsubscribe !== NULL) {
 545       $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
 546     }
 547
 548     if ($is_distinct) {
 549       $query .= " GROUP BY $queue.id ";
 550     }
 551
 552     $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
 553     if ($sort) {
 554       if (is_string($sort)) {
 555         $sort = CRM_Utils_Type::escape($sort, 'String');
 556         $orderBy = $sort;
 557       }
 558       else {
 559         $orderBy = trim($sort->orderBy());
 560       }
 561     }
 562
 563     $query .= " ORDER BY {$orderBy} ";
 564
 565     if ($offset || $rowCount) {
 566       //Added "||$rowCount" to avoid displaying all records on first page
 567       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 568     }
 569
 570     $dao->query($query);
 571
 572     $results = array();
 573
 574     while ($dao->fetch()) {
 575       $url = CRM_Utils_System::url('civicrm/contact/view',
 576         "reset=1&cid={$dao->contact_id}"
 577       );
 578       $results[] = array(
 579         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 580         'email' => $dao->email,
 581         // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes.
 582         'unsubOrOptout' => ts('Yes'),
 583         'date' => CRM_Utils_Date::customFormat($dao->date),
 584       );
 585     }
 586     return $results;
 587   }
 588
 589   /**
 590    * @param int $queueID
 591    *
 592    * @return array
 593    */
 594   public static function getContactInfo($queueID) {
 595     $query = "
 596 SELECT DISTINCT(civicrm_mailing_event_queue.contact_id) as contact_id,
 597        civicrm_contact.display_name as display_name
 598        civicrm_email.email as email
 599   FROM civicrm_mailing_event_queue,
 600        civicrm_contact,
 601        civicrm_email
 602  WHERE civicrm_mailing_event_queue.contact_id = civicrm_contact.id
 603    AND civicrm_mailing_event_queue.email_id = civicrm_email.id
 604    AND civicrm_mailing_event_queue.id = " . CRM_Utils_Type::escape($queueID, 'Integer');
 605
 606     $dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
 607
 608     $displayName = 'Unknown';
 609     $email = 'Unknown';
 610     if ($dao->fetch()) {
 611       $displayName = $dao->display_name;
 612       $email = $dao->email;
 613     }
 614
 615     return array($displayName, $email);
 616   }
 617 }