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 bool
  61    *   Was the contact successfully unsubscribed?
  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
 129    *   $groups    Array of all groups from which the contact was removed, or null if the queue event could not be found.
 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    */
 305   public static function send_unsub_response($queue_id, $groups, $is_domain = FALSE, $job) {
 306     $config = CRM_Core_Config::singleton();
 307     $domain = CRM_Core_BAO_Domain::getDomain();
 308
 309     $jobObject = new CRM_Mailing_BAO_MailingJob();
 310     $jobTable = $jobObject->getTableName();
 311     $mailingObject = new CRM_Mailing_DAO_Mailing();
 312     $mailingTable = $mailingObject->getTableName();
 313     $contactsObject = new CRM_Contact_DAO_Contact();
 314     $contacts = $contactsObject->getTableName();
 315     $emailObject = new CRM_Core_DAO_Email();
 316     $email = $emailObject->getTableName();
 317     $queueObject = new CRM_Mailing_Event_BAO_Queue();
 318     $queue = $queueObject->getTableName();
 319
 320     //get the default domain email address.
 321     list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
 322
 323     $dao = new CRM_Mailing_BAO_Mailing();
 324     $dao->query("   SELECT * FROM $mailingTable
 325                         INNER JOIN $jobTable ON
 326                             $jobTable.mailing_id = $mailingTable.id
 327                         WHERE $jobTable.id = $job");
 328     $dao->fetch();
 329
 330     $component = new CRM_Mailing_BAO_Component();
 331
 332     if ($is_domain) {
 333       $component->id = $dao->optout_id;
 334     }
 335     else {
 336       $component->id = $dao->unsubscribe_id;
 337     }
 338     $component->find(TRUE);
 339
 340     $html = $component->body_html;
 341     if ($component->body_text) {
 342       $text = $component->body_text;
 343     }
 344     else {
 345       $text = CRM_Utils_String::htmlToText($component->body_html);
 346     }
 347
 348     $eq = new CRM_Core_DAO();
 349     $eq->query(
 350       "SELECT     $contacts.preferred_mail_format as format,
 351                     $contacts.id as contact_id,
 352                     $email.email as email,
 353                     $queue.hash as hash
 354         FROM        $contacts
 355         INNER JOIN  $queue ON $queue.contact_id = $contacts.id
 356         INNER JOIN  $email ON $queue.email_id = $email.id
 357         WHERE       $queue.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')
 358     );
 359     $eq->fetch();
 360
 361     if ($groups) {
 362       foreach ($groups as $key => $value) {
 363         if (!$value) {
 364           unset($groups[$key]);
 365         }
 366       }
 367     }
 368
 369     $message = new Mail_mime("\n");
 370
 371     list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email);
 372     $bao = new CRM_Mailing_BAO_Mailing();
 373     $bao->body_text = $text;
 374     $bao->body_html = $html;
 375     $tokens = $bao->getTokens();
 376     if ($eq->format == 'HTML' || $eq->format == 'Both') {
 377       $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']);
 378       $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id, $eq->hash);
 379       $html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']);
 380       $html = CRM_Utils_Token::replaceMailingTokens($html, $dao, NULL, $tokens['html']);
 381       $message->setHTMLBody($html);
 382     }
 383     if (!$html || $eq->format == 'Text' || $eq->format == 'Both') {
 384       $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
 385       $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id, $eq->hash);
 386       $text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']);
 387       $text = CRM_Utils_Token::replaceMailingTokens($text, $dao, NULL, $tokens['text']);
 388       $message->setTxtBody($text);
 389     }
 390
 391     $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
 392
 393     $headers = array(
 394       'Subject' => $component->subject,
 395       'From' => "\"$domainEmailName\" <do-not-reply@$emailDomain>",
 396       'To' => $eq->email,
 397       'Reply-To' => "do-not-reply@$emailDomain",
 398       'Return-Path' => "do-not-reply@$emailDomain",
 399     );
 400     CRM_Mailing_BAO_Mailing::addMessageIdHeader($headers, 'u', $job, $queue_id, $eq->hash);
 401
 402     $b = CRM_Utils_Mail::setMimeParams($message);
 403     $h = $message->headers($headers);
 404
 405     $mailer = $config->getMailer();
 406
 407     if (is_object($mailer)) {
 408       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
 409       $mailer->send($eq->email, $h, $b);
 410       unset($errorScope);
 411     }
 412   }
 413
 414   /**
 415    * Get row count for the event selector
 416    *
 417    * @param int $mailing_id
 418    *   ID of the mailing.
 419    * @param int $job_id
 420    *   Optional ID of a job to filter on.
 421    * @param bool $is_distinct
 422    *   Group by queue ID?.
 423    *
 424    * @param null $org_unsubscribe
 425    *
 426    * @return int
 427    *   Number of rows in result set
 428    */
 429   public static function getTotalCount(
 430     $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
 499    *   Result set
 500    */
 501   public static function &getRows(
 502     $mailing_id, $job_id = NULL,
 503     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL,
 504     $org_unsubscribe = NULL
 505   ) {
 506
 507     $dao = new CRM_Core_Dao();
 508
 509     $unsub = self::$_tableName;
 510     $queueObject = new CRM_Mailing_Event_BAO_Queue();
 511     $queue = $queueObject->getTableName();
 512     $mailingObject = new CRM_Mailing_BAO_Mailing();
 513     $mailing = $mailingObject->getTableName();
 514     $jobObject = new CRM_Mailing_BAO_MailingJob();
 515     $job = $jobObject->getTableName();
 516     $contactObject = new CRM_Contact_BAO_Contact();
 517     $contact = $contactObject->getTableName();
 518     $emailObject = new CRM_Core_BAO_Email();
 519     $email = $emailObject->getTableName();
 520
 521     $query = "
 522             SELECT      $contact.display_name as display_name,
 523                         $contact.id as contact_id,
 524                         $email.email as email,
 525                         $unsub.time_stamp as date,
 526                         $unsub.org_unsubscribe as org_unsubscribe
 527             FROM        $contact
 528             INNER JOIN  $queue
 529                     ON  $queue.contact_id = $contact.id
 530             INNER JOIN  $email
 531                     ON  $queue.email_id = $email.id
 532             INNER JOIN  $unsub
 533                     ON  $unsub.event_queue_id = $queue.id
 534             INNER JOIN  $job
 535                     ON  $queue.job_id = $job.id
 536             INNER JOIN  $mailing
 537                     ON  $job.mailing_id = $mailing.id
 538                     AND $job.is_test = 0
 539             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 540
 541     if (!empty($job_id)) {
 542       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 543     }
 544
 545     if ($org_unsubscribe !== NULL) {
 546       $query .= " AND $unsub.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
 547     }
 548
 549     if ($is_distinct) {
 550       $query .= " GROUP BY $queue.id ";
 551     }
 552
 553     $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
 554     if ($sort) {
 555       if (is_string($sort)) {
 556         $sort = CRM_Utils_Type::escape($sort, 'String');
 557         $orderBy = $sort;
 558       }
 559       else {
 560         $orderBy = trim($sort->orderBy());
 561       }
 562     }
 563
 564     $query .= " ORDER BY {$orderBy} ";
 565
 566     if ($offset || $rowCount) {
 567       //Added "||$rowCount" to avoid displaying all records on first page
 568       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 569     }
 570
 571     $dao->query($query);
 572
 573     $results = array();
 574
 575     while ($dao->fetch()) {
 576       $url = CRM_Utils_System::url('civicrm/contact/view',
 577         "reset=1&cid={$dao->contact_id}"
 578       );
 579       $results[] = array(
 580         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 581         'email' => $dao->email,
 582         // Next value displays in selector under either Unsubscribe OR Optout column header, so always s/b Yes.
 583         'unsubOrOptout' => ts('Yes'),
 584         'date' => CRM_Utils_Date::customFormat($dao->date),
 585       );
 586     }
 587     return $results;
 588   }
 589
 590   /**
 591    * @param int $queueID
 592    *
 593    * @return array
 594    */
 595   public static function getContactInfo($queueID) {
 596     $query = "
 597 SELECT DISTINCT(civicrm_mailing_event_queue.contact_id) as contact_id,
 598        civicrm_contact.display_name as display_name
 599        civicrm_email.email as email
 600   FROM civicrm_mailing_event_queue,
 601        civicrm_contact,
 602        civicrm_email
 603  WHERE civicrm_mailing_event_queue.contact_id = civicrm_contact.id
 604    AND civicrm_mailing_event_queue.email_id = civicrm_email.id
 605    AND civicrm_mailing_event_queue.id = " . CRM_Utils_Type::escape($queueID, 'Integer');
 606
 607     $dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
 608
 609     $displayName = 'Unknown';
 610     $email = 'Unknown';
 611     if ($dao->fetch()) {
 612       $displayName = $dao->display_name;
 613       $email = $dao->email;
 614     }
 615
 616     return array($displayName, $email);
 617   }
 618
 619 }