CRM/Mailing/Event/BAO/Bounce.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.4                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  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,
 133     $is_distinct = FALSE
 134   ) {
 135     $dao = new CRM_Core_DAO();
 136
 137     $bounce  = self::getTableName();
 138     $queue   = CRM_Mailing_Event_BAO_Queue::getTableName();
 139     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 140     $job     = CRM_Mailing_BAO_MailingJob::getTableName();
 141
 142     $query = "
 143             SELECT      COUNT($bounce.id) as bounce
 144             FROM        $bounce
 145             INNER JOIN  $queue
 146                     ON  $bounce.event_queue_id = $queue.id
 147             INNER JOIN  $job
 148                     ON  $queue.job_id = $job.id
 149             INNER JOIN  $mailing
 150                     ON  $job.mailing_id = $mailing.id
 151             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 152
 153     if (!empty($job_id)) {
 154       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 155     }
 156
 157     if ($is_distinct) {
 158       $query .= " GROUP BY $queue.id ";
 159     }
 160
 161     // query was missing
 162     $dao->query($query);
 163
 164     if ($dao->fetch()) {
 165       return $dao->bounce;
 166     }
 167
 168     return NULL;
 169   }
 170
 171   /**
 172    * Get rows for the event browser
 173    *
 174    * @param int $mailing_id       ID of the mailing
 175    * @param int $job_id           optional ID of the job
 176    * @param boolean $is_distinct  Group by queue id?
 177    * @param int $offset           Offset
 178    * @param int $rowCount         Number of rows
 179    * @param array $sort           sort array
 180    *
 181    * @return array                Result set
 182    * @access public
 183    * @static
 184    */
 185   public static function &getRows($mailing_id, $job_id = NULL,
 186     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL
 187   ) {
 188
 189     $dao = new CRM_Core_Dao();
 190
 191     $bounce     = self::getTableName();
 192     $bounceType = CRM_Mailing_DAO_BounceType::getTableName();
 193     $queue      = CRM_Mailing_Event_BAO_Queue::getTableName();
 194     $mailing    = CRM_Mailing_BAO_Mailing::getTableName();
 195     $job        = CRM_Mailing_BAO_MailingJob::getTableName();
 196     $contact    = CRM_Contact_BAO_Contact::getTableName();
 197     $email      = CRM_Core_BAO_Email::getTableName();
 198
 199     $query = "
 200             SELECT      $contact.display_name as display_name,
 201                         $contact.id as contact_id,
 202                         $email.email as email,
 203                         $bounce.time_stamp as date,
 204                         $bounce.bounce_reason as reason,
 205                         $bounceType.name as bounce_type
 206             FROM        $contact
 207             INNER JOIN  $queue
 208                     ON  $queue.contact_id = $contact.id
 209             INNER JOIN  $email
 210                     ON  $queue.email_id = $email.id
 211             INNER JOIN  $bounce
 212                     ON  $bounce.event_queue_id = $queue.id
 213             LEFT JOIN   $bounceType
 214                     ON  $bounce.bounce_type_id = $bounceType.id
 215             INNER JOIN  $job
 216                     ON  $queue.job_id = $job.id
 217                     AND $job.is_test = 0
 218             INNER JOIN  $mailing
 219                     ON  $job.mailing_id = $mailing.id
 220             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 221
 222     if (!empty($job_id)) {
 223       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 224     }
 225
 226     if ($is_distinct) {
 227       $query .= " GROUP BY $queue.id ";
 228     }
 229
 230     $orderBy = "sort_name ASC, {$bounce}.time_stamp DESC";
 231     if ($sort) {
 232       if (is_string($sort)) {
 233         $orderBy = $sort;
 234       }
 235       else {
 236         $orderBy = trim($sort->orderBy());
 237       }
 238     }
 239     $query .= " ORDER BY {$orderBy} ";
 240
 241     if ($offset || $rowCount) {
 242       //Added "||$rowCount" to avoid displaying all records on first page
 243       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 244     }
 245
 246     $dao->query($query);
 247
 248     $results = array();
 249
 250     while ($dao->fetch()) {
 251       $url = CRM_Utils_System::url('civicrm/contact/view',
 252         "reset=1&cid={$dao->contact_id}"
 253       );
 254       $results[] = array(
 255         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 256         'email' => $dao->email,
 257         // FIXME: translate this
 258         'type' => (empty($dao->bounce_type)
 259            ? ts('Unknown') : $dao->bounce_type
 260         ),
 261         'reason' => $dao->reason,
 262         'date' => CRM_Utils_Date::customFormat($dao->date),
 263       );
 264     }
 265     return $results;
 266   }
 267 }
 268