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