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  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Mailing_Bounce extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38
  39   protected $_emailField = FALSE;
  40
  41   protected $_phoneField = FALSE;
  42
  43   // just a toggle we use to build the from
  44   protected $_mailingidField = FALSE;
  45
  46   protected $_customGroupExtends = array(
  47     'Contact',
  48     'Individual',
  49     'Household',
  50     'Organization',
  51   );
  52
  53   protected $_charts = array(
  54     '' => 'Tabular',
  55     'barChart' => 'Bar Chart',
  56     'pieChart' => 'Pie Chart',
  57   );
  58
  59   /**
  60    */
  61   /**
  62    */
  63   public function __construct() {
  64     $this->_columns = array();
  65
  66     $this->_columns['civicrm_contact'] = array(
  67       'dao' => 'CRM_Contact_DAO_Contact',
  68       'fields' => array(
  69         'id' => array(
  70           'title' => ts('Contact ID'),
  71           'required' => TRUE,
  72         ),
  73         'sort_name' => array(
  74           'title' => ts('Contact Name'),
  75           'required' => TRUE,
  76         ),
  77       ),
  78       'filters' => array(
  79         'sort_name' => array(
  80           'title' => ts('Contact Name'),
  81         ),
  82         'source' => array(
  83           'title' => ts('Contact Source'),
  84           'type' => CRM_Utils_Type::T_STRING,
  85         ),
  86         'id' => array(
  87           'title' => ts('Contact ID'),
  88           'no_display' => TRUE,
  89         ),
  90       ),
  91       'order_bys' => array(
  92         'sort_name' => array(
  93           'title' => ts('Contact Name'),
  94           'default' => TRUE,
  95           'default_order' => 'ASC',
  96         ),
  97       ),
  98       'grouping' => 'contact-fields',
  99     );
 100
 101     $this->_columns['civicrm_mailing'] = array(
 102       'dao' => 'CRM_Mailing_DAO_Mailing',
 103       'fields' => array(
 104         'mailing_name' => array(
 105           'name' => 'name',
 106           'title' => ts('Mailing'),
 107           'default' => TRUE,
 108         ),
 109         'mailing_name_alias' => array(
 110           'name' => 'name',
 111           'required' => TRUE,
 112           'no_display' => TRUE,
 113         ),
 114       ),
 115       'filters' => array(
 116         'mailing_id' => array(
 117           'name' => 'id',
 118           'title' => ts('Mailing'),
 119           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 120           'type' => CRM_Utils_Type::T_INT,
 121           'options' => CRM_Mailing_BAO_Mailing::getMailingsList(),
 122           'operator' => 'like',
 123         ),
 124       ),
 125       'order_bys' => array(
 126         'mailing_name' => array(
 127           'name' => 'name',
 128           'title' => ts('Mailing'),
 129         ),
 130       ),
 131       'grouping' => 'mailing-fields',
 132     );
 133
 134     $this->_columns['civicrm_mailing_event_bounce'] = array(
 135       'dao' => 'CRM_Mailing_DAO_Mailing',
 136       'fields' => array(
 137         'bounce_reason' => array(
 138           'title' => ts('Bounce Reason'),
 139         ),
 140       ),
 141       'order_bys' => array(
 142         'bounce_reason' => array('title' => ts('Bounce Reason')),
 143       ),
 144       'grouping' => 'mailing-fields',
 145     );
 146
 147     $this->_columns['civicrm_mailing_bounce_type'] = array(
 148       'dao' => 'CRM_Mailing_DAO_BounceType',
 149       'fields' => array(
 150         'bounce_name' => array(
 151           'name' => 'name',
 152           'title' => ts('Bounce Type'),
 153         ),
 154       ),
 155       'filters' => array(
 156         'bounce_type_name' => array(
 157           'name' => 'name',
 158           'title' => ts('Bounce Type'),
 159           'operatorType' => CRM_Report_Form::OP_SELECT,
 160           'type' => CRM_Utils_Type::T_STRING,
 161           'options' => self::bounce_type(),
 162           'operator' => 'like',
 163         ),
 164       ),
 165       'order_bys' => array(
 166         'bounce_name' => array(
 167           'name' => 'name',
 168           'title' => ts('Bounce Type'),
 169         ),
 170       ),
 171       'grouping' => 'mailing-fields',
 172     );
 173
 174     $this->_columns['civicrm_email'] = array(
 175       'dao' => 'CRM_Core_DAO_Email',
 176       'fields' => array(
 177         'email' => array(
 178           'title' => ts('Email'),
 179           'no_repeat' => TRUE,
 180           'required' => TRUE,
 181         ),
 182       ),
 183       'order_bys' => array(
 184         'email' => array('title' => ts('Email'), 'default_order' => 'ASC'),
 185       ),
 186       'grouping' => 'contact-fields',
 187     );
 188
 189     $this->_columns['civicrm_phone'] = array(
 190       'dao' => 'CRM_Core_DAO_Phone',
 191       'fields' => array('phone' => NULL),
 192       'grouping' => 'contact-fields',
 193     );
 194
 195     $this->_groupFilter = TRUE;
 196     $this->_tagFilter = TRUE;
 197     parent::__construct();
 198   }
 199
 200   public function preProcess() {
 201     $this->assign('chartSupported', TRUE);
 202     parent::preProcess();
 203   }
 204
 205   public function select() {
 206     $select = array();
 207     $this->_columnHeaders = array();
 208
 209     foreach ($this->_columns as $tableName => $table) {
 210       if (array_key_exists('fields', $table)) {
 211         foreach ($table['fields'] as $fieldName => $field) {
 212           if (!empty($field['required']) ||
 213             !empty($this->_params['fields'][$fieldName])
 214           ) {
 215             if ($tableName == 'civicrm_email') {
 216               $this->_emailField = TRUE;
 217             }
 218             elseif ($tableName == 'civicrm_phone') {
 219               $this->_phoneField = TRUE;
 220             }
 221
 222             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 223             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 224             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 225             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 226           }
 227         }
 228       }
 229     }
 230
 231     if (!empty($this->_params['charts'])) {
 232       $select[] = "COUNT({$this->_aliases['civicrm_mailing_event_bounce']}.id) as civicrm_mailing_bounce_count";
 233       $this->_columnHeaders["civicrm_mailing_bounce_count"]['title'] = ts('Bounce Count');
 234     }
 235
 236     $this->_select = "SELECT " . implode(', ', $select) . " ";
 237   }
 238
 239   /**
 240    * @param $fields
 241    * @param $files
 242    * @param $self
 243    *
 244    * @return array
 245    */
 246   public static function formRule($fields, $files, $self) {
 247     $errors = $grouping = array();
 248     return $errors;
 249   }
 250
 251   public function from() {
 252     $this->_from = "
 253         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 254     // LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 255     // ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 256     // {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 257
 258     $this->_from .= "
 259         INNER JOIN civicrm_mailing_event_queue
 260           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 261         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 262           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 263         INNER JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 264           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id
 265         LEFT JOIN civicrm_mailing_bounce_type {$this->_aliases['civicrm_mailing_bounce_type']}
 266           ON {$this->_aliases['civicrm_mailing_event_bounce']}.bounce_type_id = {$this->_aliases['civicrm_mailing_bounce_type']}.id
 267         INNER JOIN civicrm_mailing_job
 268           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 269         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 270           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 271       ";
 272
 273     if ($this->_phoneField) {
 274       $this->_from .= "
 275             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 276                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 277                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 278     }
 279   }
 280
 281   public function where() {
 282     parent::where();
 283     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 284   }
 285
 286   public function groupBy() {
 287     if (!empty($this->_params['charts'])) {
 288       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 289     }
 290     else {
 291       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing_event_bounce']}.id";
 292     }
 293   }
 294
 295   public function postProcess() {
 296     $this->beginPostProcess();
 297
 298     // get the acl clauses built before we assemble the query
 299     $this->buildACLClause($this->_aliases['civicrm_contact']);
 300
 301     $sql = $this->buildQuery(TRUE);
 302
 303     $rows = $graphRows = array();
 304     $this->buildRows($sql, $rows);
 305
 306     $this->formatDisplay($rows);
 307     $this->doTemplateAssignment($rows);
 308     $this->endPostProcess($rows);
 309   }
 310
 311   /**
 312    * @param $rows
 313    */
 314   public function buildChart(&$rows) {
 315     if (empty($rows)) {
 316       return;
 317     }
 318
 319     $chartInfo = array(
 320       'legend' => ts('Mail Bounce Report'),
 321       'xname' => ts('Mailing'),
 322       'yname' => ts('Bounce'),
 323       'xLabelAngle' => 20,
 324       'tip' => ts('Mail Bounce: %1', array(1 => '#val#')),
 325     );
 326     foreach ($rows as $row) {
 327       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_bounce_count'];
 328     }
 329
 330     // build the chart.
 331     CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 332     $this->assign('chartType', $this->_params['charts']);
 333   }
 334
 335   /**
 336    * @return array
 337    */
 338   public function bounce_type() {
 339
 340     $data = array('' => '--Please Select--');
 341
 342     $bounce_type = new CRM_Mailing_DAO_BounceType();
 343     $query = "SELECT name FROM civicrm_mailing_bounce_type";
 344     $bounce_type->query($query);
 345
 346     while ($bounce_type->fetch()) {
 347       $data[$bounce_type->name] = $bounce_type->name;
 348     }
 349
 350     return $data;
 351   }
 352
 353   /**
 354    * Alter display of rows.
 355    *
 356    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 357    * such as rendering contacts as links.
 358    *
 359    * @param array $rows
 360    *   Rows generated by SQL, with an array for each row.
 361    */
 362   public function alterDisplay(&$rows) {
 363     $entryFound = FALSE;
 364     foreach ($rows as $rowNum => $row) {
 365       // make count columns point to detail report
 366       // convert display name to links
 367       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 368         array_key_exists('civicrm_contact_id', $row)
 369       ) {
 370         $url = CRM_Utils_System::url('civicrm/contact/view',
 371           'reset=1&cid=' . $row['civicrm_contact_id'],
 372           $this->_absoluteUrl
 373         );
 374         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 375         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 376         $entryFound = TRUE;
 377       }
 378
 379       // skip looking further in rows, if first row itself doesn't
 380       // have the column we need
 381       if (!$entryFound) {
 382         break;
 383       }
 384     }
 385   }
 386
 387 }