CRM/Mailing/Event/BAO/Unsubscribe.php

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