CRM/Mailing/Event/BAO/Bounce.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Mailing_Event_BAO_Bounce extends CRM_Mailing_Event_DAO_Bounce {
  18
  19   /**
  20    * Class constructor.
  21    */
  22   public function __construct() {
  23     parent::__construct();
  24   }
  25
  26   /**
  27    * Create a new bounce event, update the email address if necessary
  28    *
  29    * @param $params
  30    *
  31    * @return bool|null
  32    */
  33   public static function create(&$params) {
  34     $q = CRM_Mailing_Event_BAO_Queue::verify($params['job_id'],
  35       $params['event_queue_id'],
  36       $params['hash']
  37     );
  38     $success = NULL;
  39
  40     if (!$q) {
  41       return $success;
  42     }
  43
  44     $transaction = new CRM_Core_Transaction();
  45     $bounce = new CRM_Mailing_Event_BAO_Bounce();
  46     $bounce->time_stamp = date('YmdHis');
  47
  48     $action = empty($params['id']) ? 'create' : 'edit';
  49     CRM_Utils_Hook::pre($action, 'MailingEventBounce', CRM_Utils_Array::value('id', $params), $params);
  50
  51     // if we dont have a valid bounce type, we should set it
  52     // to bounce_type_id 11 which is Syntax error. this allows such email
  53     // addresses to be bounce a few more time before being put on hold
  54     // CRM-4814
  55     // we changed this behavior since this bounce type might be due to some issue
  56     // with the connection or smtp server etc
  57     if (empty($params['bounce_type_id'])) {
  58       $params['bounce_type_id'] = 11;
  59       if (empty($params['bounce_reason'])) {
  60         $params['bounce_reason'] = ts('Unknown bounce type: Could not parse bounce email');
  61       }
  62     }
  63
  64     // replace any invalid unicode characters with replacement characters
  65     $params['bounce_reason'] = mb_convert_encoding($params['bounce_reason'], 'UTF-8', 'UTF-8');
  66
  67     // dev/mail#37 Replace 4-byte utf8 characaters with the unicode replacement character
  68     // while CiviCRM does not support utf8mb4 for MySQL
  69     $params['bounce_reason'] = preg_replace('/[\x{10000}-\x{10FFFF}]/u', "\xEF\xBF\xBD", $params['bounce_reason']);
  70
  71     // CRM-11989
  72     $params['bounce_reason'] = mb_strcut($params['bounce_reason'], 0, 254);
  73
  74     $bounce->copyValues($params);
  75     $bounce->save();
  76
  77     CRM_Utils_Hook::post($action, 'MailingEventBounce', $bounce->id, $bounce);
  78
  79     if ($q->email_id) {
  80       self::putEmailOnHold($q->email_id);
  81     }
  82     $transaction->commit();
  83
  84     return TRUE;
  85   }
  86
  87   /**
  88    * Get row count for the event selector.
  89    *
  90    * @param int $mailing_id
  91    *   ID of the mailing.
  92    * @param int $job_id
  93    *   Optional ID of a job to filter on.
  94    * @param bool $is_distinct
  95    *   Group by queue ID?.
  96    *
  97    * @param string|null $toDate
  98    *
  99    * @return int
 100    *   Number of rows in result set
 101    */
 102   public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
 103     $dao = new CRM_Core_DAO();
 104
 105     $bounce = self::getTableName();
 106     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 107     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 108     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 109
 110     $query = "
 111             SELECT      COUNT($bounce.id) as bounce
 112             FROM        $bounce
 113             INNER JOIN  $queue
 114                     ON  $bounce.event_queue_id = $queue.id
 115             INNER JOIN  $job
 116                     ON  $queue.job_id = $job.id
 117             INNER JOIN  $mailing
 118                     ON  $job.mailing_id = $mailing.id
 119             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 120
 121     if (!empty($toDate)) {
 122       $query .= " AND $bounce.time_stamp <= $toDate";
 123     }
 124
 125     if (!empty($job_id)) {
 126       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 127     }
 128
 129     if ($is_distinct) {
 130       $query .= " GROUP BY $queue.id ";
 131     }
 132
 133     // query was missing
 134     $dao->query($query);
 135
 136     if ($dao->fetch()) {
 137       return $dao->bounce;
 138     }
 139
 140     return NULL;
 141   }
 142
 143   /**
 144    * Get rows for the event browser.
 145    *
 146    * @param int $mailing_id
 147    *   ID of the mailing.
 148    * @param int $job_id
 149    *   Optional ID of the job.
 150    * @param bool $is_distinct
 151    *   Group by queue id?.
 152    * @param int $offset
 153    *   Offset.
 154    * @param int $rowCount
 155    *   Number of rows.
 156    * @param array $sort
 157    *   Sort array.
 158    *
 159    * @return array
 160    *   Result set
 161    */
 162   public static function &getRows(
 163     $mailing_id, $job_id = NULL,
 164     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL
 165   ) {
 166
 167     $dao = new CRM_Core_DAO();
 168
 169     $bounce = self::getTableName();
 170     $bounceType = CRM_Mailing_DAO_BounceType::getTableName();
 171     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 172     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 173     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 174     $contact = CRM_Contact_BAO_Contact::getTableName();
 175     $email = CRM_Core_BAO_Email::getTableName();
 176
 177     $query = "
 178             SELECT      $contact.display_name as display_name,
 179                         $contact.id as contact_id,
 180                         $email.email as email,
 181                         $bounce.time_stamp as date,
 182                         $bounce.bounce_reason as reason,
 183                         $bounceType.name as bounce_type
 184             FROM        $contact
 185             INNER JOIN  $queue
 186                     ON  $queue.contact_id = $contact.id
 187             INNER JOIN  $email
 188                     ON  $queue.email_id = $email.id
 189             INNER JOIN  $bounce
 190                     ON  $bounce.event_queue_id = $queue.id
 191             LEFT JOIN   $bounceType
 192                     ON  $bounce.bounce_type_id = $bounceType.id
 193             INNER JOIN  $job
 194                     ON  $queue.job_id = $job.id
 195                     AND $job.is_test = 0
 196             INNER JOIN  $mailing
 197                     ON  $job.mailing_id = $mailing.id
 198             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 199
 200     if (!empty($job_id)) {
 201       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 202     }
 203
 204     if ($is_distinct) {
 205       $query .= " GROUP BY $queue.id, $bounce.time_stamp, $bounce.bounce_reason, $bounceType.name ";
 206     }
 207
 208     $orderBy = "sort_name ASC, {$bounce}.time_stamp DESC";
 209     if ($sort) {
 210       if (is_string($sort)) {
 211         $sort = CRM_Utils_Type::escape($sort, 'String');
 212         $orderBy = $sort;
 213       }
 214       else {
 215         $orderBy = trim($sort->orderBy());
 216       }
 217     }
 218     $query .= " ORDER BY {$orderBy} ";
 219
 220     if ($offset || $rowCount) {
 221       //Added "||$rowCount" to avoid displaying all records on first page
 222       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 223     }
 224
 225     $dao->query($query);
 226
 227     $results = array();
 228
 229     while ($dao->fetch()) {
 230       $url = CRM_Utils_System::url('civicrm/contact/view',
 231         "reset=1&cid={$dao->contact_id}"
 232       );
 233       $results[] = array(
 234         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 235         'email' => $dao->email,
 236         // FIXME: translate this
 237         'type' => (empty($dao->bounce_type) ? ts('Unknown') : $dao->bounce_type
 238         ),
 239         'reason' => $dao->reason,
 240         'date' => CRM_Utils_Date::customFormat($dao->date),
 241       );
 242     }
 243     return $results;
 244   }
 245
 246   /**
 247    * Put the email on hold if it has met the threshold.
 248    *
 249    * @param int $email_id
 250    */
 251   protected static function putEmailOnHold($email_id) {
 252
 253     $bounceTable = CRM_Mailing_Event_BAO_Bounce::getTableName();
 254     $bounceType = CRM_Mailing_DAO_BounceType::getTableName();
 255     $emailTable = CRM_Core_BAO_Email::getTableName();
 256     $queueTable = CRM_Mailing_Event_BAO_Queue::getTableName();
 257
 258     // might want to put distinct inside the count
 259     $query = "SELECT     count($bounceTable.id) as bounces,
 260                             $bounceType.hold_threshold as threshold
 261                 FROM        $bounceTable
 262                 INNER JOIN  $bounceType
 263                         ON  $bounceTable.bounce_type_id = $bounceType.id
 264                 INNER JOIN  $queueTable
 265                         ON  $bounceTable.event_queue_id = $queueTable.id
 266                 INNER JOIN  $emailTable
 267                         ON  $queueTable.email_id = $emailTable.id
 268                 WHERE       $emailTable.id = $email_id
 269                     AND     ($emailTable.reset_date IS NULL
 270                         OR  $bounceTable.time_stamp >= $emailTable.reset_date)
 271                 GROUP BY    $bounceTable.bounce_type_id
 272                 ORDER BY    threshold, bounces desc";
 273
 274     $dao = CRM_Core_DAO::executeQuery($query);
 275
 276     while ($dao->fetch()) {
 277       if ($dao->bounces >= $dao->threshold) {
 278         $email = new CRM_Core_BAO_Email();
 279         $email->id = $email_id;
 280         $email->on_hold = TRUE;
 281         $email->hold_date = date('YmdHis');
 282         $email->save();
 283         break;
 284       }
 285     }
 286   }
 287
 288 }