CRM/Mailing/Event/BAO/Bounce.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 class CRM_Mailing_Event_BAO_Bounce extends CRM_Mailing_Event_DAO_Bounce {
  36
  37   /**
  38    * class constructor
  39    */
  40   function __construct() {
  41     parent::__construct();
  42   }
  43
  44   /**
  45    * Create a new bounce event, update the email address if necessary
  46    */
  47   static function &create(&$params) {
  48     $q = &CRM_Mailing_Event_BAO_Queue::verify($params['job_id'],
  49       $params['event_queue_id'],
  50       $params['hash']
  51     );
  52     $success = NULL;
  53
  54     if (!$q) {
  55       return $success;
  56     }
  57
  58     $transaction        = new CRM_Core_Transaction();
  59     $bounce             = new CRM_Mailing_Event_BAO_Bounce();
  60     $bounce->time_stamp = date('YmdHis');
  61
  62     // if we dont have a valid bounce type, we should set it
  63     // to bounce_type_id 11 which is Syntax error. this allows such email
  64     // addresses to be bounce a few more time before being put on hold
  65     // CRM-4814
  66     // we changed this behavior since this bounce type might be due to some issue
  67     // with the connection or smtp server etc
  68     if (empty($params['bounce_type_id'])) {
  69       $params['bounce_type_id'] = 11;
  70       if (empty($params['bounce_reason'])) {
  71         $params['bounce_reason'] = ts('Unknown bounce type: Could not parse bounce email');
  72       }
  73     }
  74
  75     // CRM-11989
  76     $params['bounce_reason'] = substr($params['bounce_reason'], 0, 254);
  77
  78     $bounce->copyValues($params);
  79     $bounce->save();
  80     $success = TRUE;
  81
  82     $bounceTable = CRM_Mailing_Event_BAO_Bounce::getTableName();
  83     $bounceType  = CRM_Mailing_DAO_BounceType::getTableName();
  84     $emailTable  = CRM_Core_BAO_Email::getTableName();
  85     $queueTable  = CRM_Mailing_Event_BAO_Queue::getTableName();
  86
  87     $bounce->reset();
  88     // might want to put distinct inside the count
  89     $query = "SELECT     count($bounceTable.id) as bounces,
  90                             $bounceType.hold_threshold as threshold
  91                 FROM        $bounceTable
  92                 INNER JOIN  $bounceType
  93                         ON  $bounceTable.bounce_type_id = $bounceType.id
  94                 INNER JOIN  $queueTable
  95                         ON  $bounceTable.event_queue_id = $queueTable.id
  96                 INNER JOIN  $emailTable
  97                         ON  $queueTable.email_id = $emailTable.id
  98                 WHERE       $emailTable.id = {$q->email_id}
  99                     AND     ($emailTable.reset_date IS NULL
 100                         OR  $bounceTable.time_stamp >= $emailTable.reset_date)
 101                 GROUP BY    $bounceTable.bounce_type_id
 102                 ORDER BY    threshold, bounces desc";
 103
 104     $bounce->query($query);
 105
 106     while ($bounce->fetch()) {
 107       if ($bounce->bounces >= $bounce->threshold) {
 108         $email            = new CRM_Core_BAO_Email();
 109         $email->id        = $q->email_id;
 110         $email->on_hold   = TRUE;
 111         $email->hold_date = date('YmdHis');
 112         $email->save();
 113         break;
 114       }
 115     }
 116     $transaction->commit();
 117
 118     return $success;
 119   }
 120
 121   /**
 122    * Get row count for the event selector
 123    *
 124    * @param int $mailing_id       ID of the mailing
 125    * @param int $job_id           Optional ID of a job to filter on
 126    * @param boolean $is_distinct  Group by queue ID?
 127    *
 128    * @return int                  Number of rows in result set
 129    * @access public
 130    * @static
 131    */
 132   public static function getTotalCount($mailing_id, $job_id = NULL, $is_distinct = FALSE, $toDate = NULL) {
 133     $dao = new CRM_Core_DAO();
 134
 135     $bounce  = self::getTableName();
 136     $queue   = CRM_Mailing_Event_BAO_Queue::getTableName();
 137     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 138     $job     = CRM_Mailing_BAO_MailingJob::getTableName();
 139
 140     $query = "
 141             SELECT      COUNT($bounce.id) as bounce
 142             FROM        $bounce
 143             INNER JOIN  $queue
 144                     ON  $bounce.event_queue_id = $queue.id
 145             INNER JOIN  $job
 146                     ON  $queue.job_id = $job.id
 147             INNER JOIN  $mailing
 148                     ON  $job.mailing_id = $mailing.id
 149             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 150
 151     if (!empty($toDate)) {
 152       $query .= " AND $bounce.time_stamp <= $toDate";
 153     }
 154
 155     if (!empty($job_id)) {
 156       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 157     }
 158
 159     if ($is_distinct) {
 160       $query .= " GROUP BY $queue.id ";
 161     }
 162
 163     // query was missing
 164     $dao->query($query);
 165
 166     if ($dao->fetch()) {
 167       return $dao->bounce;
 168     }
 169
 170     return NULL;
 171   }
 172
 173   /**
 174    * Get rows for the event browser
 175    *
 176    * @param int $mailing_id       ID of the mailing
 177    * @param int $job_id           optional ID of the job
 178    * @param boolean $is_distinct  Group by queue id?
 179    * @param int $offset           Offset
 180    * @param int $rowCount         Number of rows
 181    * @param array $sort           sort array
 182    *
 183    * @return array                Result set
 184    * @access public
 185    * @static
 186    */
 187   public static function &getRows($mailing_id, $job_id = NULL,
 188     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL
 189   ) {
 190
 191     $dao = new CRM_Core_Dao();
 192
 193     $bounce     = self::getTableName();
 194     $bounceType = CRM_Mailing_DAO_BounceType::getTableName();
 195     $queue      = CRM_Mailing_Event_BAO_Queue::getTableName();
 196     $mailing    = CRM_Mailing_BAO_Mailing::getTableName();
 197     $job        = CRM_Mailing_BAO_MailingJob::getTableName();
 198     $contact    = CRM_Contact_BAO_Contact::getTableName();
 199     $email      = CRM_Core_BAO_Email::getTableName();
 200
 201     $query = "
 202             SELECT      $contact.display_name as display_name,
 203                         $contact.id as contact_id,
 204                         $email.email as email,
 205                         $bounce.time_stamp as date,
 206                         $bounce.bounce_reason as reason,
 207                         $bounceType.name as bounce_type
 208             FROM        $contact
 209             INNER JOIN  $queue
 210                     ON  $queue.contact_id = $contact.id
 211             INNER JOIN  $email
 212                     ON  $queue.email_id = $email.id
 213             INNER JOIN  $bounce
 214                     ON  $bounce.event_queue_id = $queue.id
 215             LEFT JOIN   $bounceType
 216                     ON  $bounce.bounce_type_id = $bounceType.id
 217             INNER JOIN  $job
 218                     ON  $queue.job_id = $job.id
 219                     AND $job.is_test = 0
 220             INNER JOIN  $mailing
 221                     ON  $job.mailing_id = $mailing.id
 222             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 223
 224     if (!empty($job_id)) {
 225       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 226     }
 227
 228     if ($is_distinct) {
 229       $query .= " GROUP BY $queue.id ";
 230     }
 231
 232     $orderBy = "sort_name ASC, {$bounce}.time_stamp DESC";
 233     if ($sort) {
 234       if (is_string($sort)) {
 235         $sort = CRM_Utils_Type::escape($sort, 'String');
 236         $orderBy = $sort;
 237       }
 238       else {
 239         $orderBy = trim($sort->orderBy());
 240       }
 241     }
 242     $query .= " ORDER BY {$orderBy} ";
 243
 244     if ($offset || $rowCount) {
 245       //Added "||$rowCount" to avoid displaying all records on first page
 246       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 247     }
 248
 249     $dao->query($query);
 250
 251     $results = array();
 252
 253     while ($dao->fetch()) {
 254       $url = CRM_Utils_System::url('civicrm/contact/view',
 255         "reset=1&cid={$dao->contact_id}"
 256       );
 257       $results[] = array(
 258         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 259         'email' => $dao->email,
 260         // FIXME: translate this
 261         'type' => (empty($dao->bounce_type)
 262            ? ts('Unknown') : $dao->bounce_type
 263         ),
 264         'reason' => $dao->reason,
 265         'date' => CRM_Utils_Date::customFormat($dao->date),
 266       );
 267     }
 268     return $results;
 269   }
 270 }