CRM/Report/Form/Mailing/Bounce.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  */
  33 class CRM_Report_Form_Mailing_Bounce extends CRM_Report_Form {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_emailField = FALSE;
  38
  39   protected $_phoneField = FALSE;
  40
  41   // just a toggle we use to build the from
  42   protected $_mailingidField = FALSE;
  43
  44   protected $_customGroupExtends = array(
  45     'Contact',
  46     'Individual',
  47     'Household',
  48     'Organization',
  49   );
  50
  51   protected $_charts = array(
  52     '' => 'Tabular',
  53     'barChart' => 'Bar Chart',
  54     'pieChart' => 'Pie Chart',
  55   );
  56
  57   /**
  58    */
  59   public function __construct() {
  60     $this->_columns = array();
  61
  62     $this->_columns['civicrm_contact'] = array(
  63       'dao' => 'CRM_Contact_DAO_Contact',
  64       'fields' => array(
  65         'id' => array(
  66           'title' => ts('Contact ID'),
  67           'required' => TRUE,
  68         ),
  69         'sort_name' => array(
  70           'title' => ts('Contact Name'),
  71           'required' => TRUE,
  72         ),
  73       ),
  74       'filters' => array(
  75         'sort_name' => array(
  76           'title' => ts('Contact Name'),
  77         ),
  78         'source' => array(
  79           'title' => ts('Contact Source'),
  80           'type' => CRM_Utils_Type::T_STRING,
  81         ),
  82         'id' => array(
  83           'title' => ts('Contact ID'),
  84           'no_display' => TRUE,
  85         ),
  86       ),
  87       'order_bys' => array(
  88         'sort_name' => array(
  89           'title' => ts('Contact Name'),
  90           'default' => TRUE,
  91           'default_order' => 'ASC',
  92         ),
  93       ),
  94       'grouping' => 'contact-fields',
  95     );
  96
  97     $this->_columns['civicrm_mailing'] = array(
  98       'dao' => 'CRM_Mailing_DAO_Mailing',
  99       'fields' => array(
 100         'mailing_name' => array(
 101           'name' => 'name',
 102           'title' => ts('Mailing'),
 103           'default' => TRUE,
 104         ),
 105         'mailing_name_alias' => array(
 106           'name' => 'name',
 107           'required' => TRUE,
 108           'no_display' => TRUE,
 109         ),
 110       ),
 111       'filters' => array(
 112         'mailing_id' => array(
 113           'name' => 'id',
 114           'title' => ts('Mailing'),
 115           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 116           'type' => CRM_Utils_Type::T_INT,
 117           'options' => CRM_Mailing_BAO_Mailing::getMailingsList(),
 118           'operator' => 'like',
 119         ),
 120       ),
 121       'order_bys' => array(
 122         'mailing_name' => array(
 123           'name' => 'name',
 124           'title' => ts('Mailing'),
 125         ),
 126       ),
 127       'grouping' => 'mailing-fields',
 128     );
 129
 130     $this->_columns['civicrm_mailing_event_bounce'] = array(
 131       'dao' => 'CRM_Mailing_DAO_Mailing',
 132       'fields' => array(
 133         'bounce_reason' => array(
 134           'title' => ts('Bounce Reason'),
 135         ),
 136         'time_stamp' => array(
 137           'title' => ts('Bounce Date'),
 138         ),
 139       ),
 140       'filters' => array(
 141         'bounce_reason' => array(
 142           'title' => ts('Bounce Reason'),
 143         ),
 144         'time_stamp' => array(
 145           'title' => ts('Bounce Date'),
 146           'operatorType' => CRM_Report_Form::OP_DATE,
 147           'type' => CRM_Utils_Type::T_DATE,
 148         ),
 149       ),
 150       'order_bys' => array(
 151         'bounce_reason' => array(
 152           'title' => ts('Bounce Reason')
 153         ),
 154         'time_stamp' => array(
 155           'title' => ts('Bounce Date')
 156         ),
 157       ),
 158       'grouping' => 'mailing-fields',
 159     );
 160
 161     $this->_columns['civicrm_mailing_bounce_type'] = array(
 162       'dao' => 'CRM_Mailing_DAO_BounceType',
 163       'fields' => array(
 164         'bounce_name' => array(
 165           'name' => 'name',
 166           'title' => ts('Bounce Type'),
 167         ),
 168       ),
 169       'filters' => array(
 170         'bounce_type_name' => array(
 171           'name' => 'name',
 172           'title' => ts('Bounce Type'),
 173           'operatorType' => CRM_Report_Form::OP_SELECT,
 174           'type' => CRM_Utils_Type::T_STRING,
 175           'options' => self::bounce_type(),
 176           'operator' => 'like',
 177         ),
 178       ),
 179       'order_bys' => array(
 180         'bounce_name' => array(
 181           'name' => 'name',
 182           'title' => ts('Bounce Type'),
 183         ),
 184       ),
 185       'grouping' => 'mailing-fields',
 186     );
 187
 188     $this->_columns['civicrm_email'] = array(
 189       'dao' => 'CRM_Core_DAO_Email',
 190       'fields' => array(
 191         'email' => array(
 192           'title' => ts('Email'),
 193           'no_repeat' => TRUE,
 194           'required' => TRUE,
 195         ),
 196         'on_hold' => array(
 197           'title' => ts('On hold'),
 198         ),
 199         'hold_date' => array(
 200           'title' => ts('Hold date'),
 201         ),
 202         'reset_date' => array(
 203           'title' => ts('Hold reset date'),
 204         ),
 205       ),
 206       'filters' => array(
 207         'on_hold' => array(
 208           'title' => ts('On hold'),
 209         ),
 210         'hold_date' => array(
 211           'title' => ts('Hold date'),
 212           'operatorType' => CRM_Report_Form::OP_DATE,
 213           'type' => CRM_Utils_Type::T_DATE,
 214         ),
 215         'reset_date' => array(
 216           'title' => ts('Hold reset date'),
 217           'operatorType' => CRM_Report_Form::OP_DATE,
 218           'type' => CRM_Utils_Type::T_DATE,
 219         ),
 220       ),
 221       'order_bys' => array(
 222         'email' => array('title' => ts('Email'), 'default_order' => 'ASC'),
 223       ),
 224       'grouping' => 'contact-fields',
 225     );
 226
 227     $this->_columns['civicrm_phone'] = array(
 228       'dao' => 'CRM_Core_DAO_Phone',
 229       'fields' => array('phone' => NULL),
 230       'grouping' => 'contact-fields',
 231     );
 232
 233     $this->_groupFilter = TRUE;
 234     $this->_tagFilter = TRUE;
 235     parent::__construct();
 236   }
 237
 238   public function preProcess() {
 239     $this->assign('chartSupported', TRUE);
 240     parent::preProcess();
 241   }
 242
 243   public function select() {
 244     $select = array();
 245     $this->_columnHeaders = array();
 246
 247     foreach ($this->_columns as $tableName => $table) {
 248       if (array_key_exists('fields', $table)) {
 249         foreach ($table['fields'] as $fieldName => $field) {
 250           if (!empty($field['required']) ||
 251             !empty($this->_params['fields'][$fieldName])
 252           ) {
 253             if ($tableName == 'civicrm_email') {
 254               $this->_emailField = TRUE;
 255             }
 256             elseif ($tableName == 'civicrm_phone') {
 257               $this->_phoneField = TRUE;
 258             }
 259
 260             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 261             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 262             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 263             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 264           }
 265         }
 266       }
 267     }
 268
 269     if (!empty($this->_params['charts'])) {
 270       $select[] = "COUNT({$this->_aliases['civicrm_mailing_event_bounce']}.id) as civicrm_mailing_bounce_count";
 271       $this->_columnHeaders["civicrm_mailing_bounce_count"]['title'] = ts('Bounce Count');
 272     }
 273
 274     $this->_select = "SELECT " . implode(', ', $select) . " ";
 275   }
 276
 277   /**
 278    * @param $fields
 279    * @param $files
 280    * @param $self
 281    *
 282    * @return array
 283    */
 284   public static function formRule($fields, $files, $self) {
 285     $errors = $grouping = array();
 286     return $errors;
 287   }
 288
 289   public function from() {
 290     $this->_from = "
 291         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 292     // LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 293     // ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 294     // {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 295
 296     $this->_from .= "
 297         INNER JOIN civicrm_mailing_event_queue
 298           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 299         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 300           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 301         INNER JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 302           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id
 303         LEFT JOIN civicrm_mailing_bounce_type {$this->_aliases['civicrm_mailing_bounce_type']}
 304           ON {$this->_aliases['civicrm_mailing_event_bounce']}.bounce_type_id = {$this->_aliases['civicrm_mailing_bounce_type']}.id
 305         INNER JOIN civicrm_mailing_job
 306           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 307         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 308           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 309       ";
 310
 311     if ($this->_phoneField) {
 312       $this->_from .= "
 313             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 314                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 315                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 316     }
 317   }
 318
 319   public function where() {
 320
 321     $clauses = array();
 322
 323     // Exclude SMS mailing type
 324     $clauses[] = "{$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 325
 326     // Build date filter clauses
 327     foreach ($this->_columns as $tableName => $table) {
 328       if (array_key_exists('filters', $table)) {
 329         foreach ($table['filters'] as $fieldName => $field) {
 330           $clause = NULL;
 331           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 332             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 333             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 334             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 335
 336             $clause = $this->dateClause($this->_aliases[$tableName] . '.' . $field['name'], $relative, $from, $to, $field['type']);
 337           }
 338           else {
 339             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 340
 341             if ($op) {
 342               $clause = $this->whereClause($field,
 343                 $op,
 344                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 345                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 346                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 347               );
 348             }
 349
 350           }
 351
 352           if (!empty($clause)) {
 353             $clauses[] = $clause;
 354           }
 355         }
 356       }
 357     }
 358
 359     $this->_where = "WHERE " . implode(' AND ', $clauses);
 360   }
 361
 362   public function groupBy() {
 363     if (!empty($this->_params['charts'])) {
 364       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 365     }
 366     else {
 367       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing_event_bounce']}.id";
 368     }
 369   }
 370
 371   public function postProcess() {
 372     $this->beginPostProcess();
 373
 374     // get the acl clauses built before we assemble the query
 375     $this->buildACLClause($this->_aliases['civicrm_contact']);
 376
 377     $sql = $this->buildQuery(TRUE);
 378
 379     $rows = $graphRows = array();
 380     $this->buildRows($sql, $rows);
 381
 382     $this->formatDisplay($rows);
 383     $this->doTemplateAssignment($rows);
 384     $this->endPostProcess($rows);
 385   }
 386
 387   /**
 388    * @param $rows
 389    */
 390   public function buildChart(&$rows) {
 391     if (empty($rows)) {
 392       return;
 393     }
 394
 395     $chartInfo = array(
 396       'legend' => ts('Mail Bounce Report'),
 397       'xname' => ts('Mailing'),
 398       'yname' => ts('Bounce'),
 399       'xLabelAngle' => 20,
 400       'tip' => ts('Mail Bounce: %1', array(1 => '#val#')),
 401     );
 402     foreach ($rows as $row) {
 403       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_bounce_count'];
 404     }
 405
 406     // build the chart.
 407     CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 408     $this->assign('chartType', $this->_params['charts']);
 409   }
 410
 411   /**
 412    * @return array
 413    */
 414   public function bounce_type() {
 415
 416     $data = array('' => '--Please Select--');
 417
 418     $bounce_type = new CRM_Mailing_DAO_BounceType();
 419     $query = "SELECT name FROM civicrm_mailing_bounce_type";
 420     $bounce_type->query($query);
 421
 422     while ($bounce_type->fetch()) {
 423       $data[$bounce_type->name] = $bounce_type->name;
 424     }
 425
 426     return $data;
 427   }
 428
 429   /**
 430    * Alter display of rows.
 431    *
 432    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 433    * such as rendering contacts as links.
 434    *
 435    * @param array $rows
 436    *   Rows generated by SQL, with an array for each row.
 437    */
 438   public function alterDisplay(&$rows) {
 439
 440     $config = CRM_Core_Config::Singleton();
 441
 442     $entryFound = FALSE;
 443     foreach ($rows as $rowNum => $row) {
 444       // make count columns point to detail report
 445       // convert display name to links
 446       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 447         array_key_exists('civicrm_contact_id', $row)
 448       ) {
 449         $url = CRM_Utils_System::url('civicrm/contact/view',
 450           'reset=1&cid=' . $row['civicrm_contact_id'],
 451           $this->_absoluteUrl
 452         );
 453         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 454         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 455         $entryFound = TRUE;
 456       }
 457
 458       // Handle on_hold boolean display
 459       if (array_key_exists('civicrm_email_on_hold', $row)) {
 460         $rows[$rowNum]['civicrm_email_on_hold'] = (!empty($row['civicrm_email_on_hold'])) ? 'Yes' : 'No';
 461         $entryFound = TRUE;
 462       }
 463
 464       // Convert datetime values to custom date and time format
 465       $dateFields = array(
 466         'civicrm_mailing_event_bounce_time_stamp',
 467         'civicrm_email_hold_date',
 468         'civicrm_email_reset_date',
 469       );
 470
 471       foreach($dateFields as $dateField) {
 472         if (array_key_exists($dateField, $row)) {
 473           if (!empty($rows[$rowNum][$dateField])) {
 474             $rows[$rowNum][$dateField] = CRM_Utils_Date::customFormat($row[$dateField], $config->dateformatDatetime);
 475           }
 476           $entryFound = TRUE;
 477         }
 478       }
 479
 480       // skip looking further in rows, if first row itself doesn't
 481       // have the column we need
 482       if (!$entryFound) {
 483         break;
 484       }
 485     }
 486   }
 487
 488 }