CRM/Report/Form/Mailing/Bounce.php

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