CRM/Report/Form/Mailing/Bounce.php

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