CRM/Report/Form/Mailing/Bounce.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Mailing_Bounce extends CRM_Report_Form {
  37
  38   protected $_summary = NULL;
  39
  40   protected $_emailField = FALSE;
  41
  42   protected $_phoneField = FALSE;
  43
  44   // just a toggle we use to build the from
  45   protected $_mailingidField = FALSE;
  46
  47   protected $_customGroupExtends = array('Contact', 'Individual', 'Household', 'Organization');
  48
  49   protected $_charts = array(
  50     '' => 'Tabular',
  51     'barChart' => 'Bar Chart',
  52     'pieChart' => 'Pie Chart',
  53   );
  54
  55   function __construct() {
  56     $this->_columns = array();
  57
  58     $this->_columns['civicrm_contact'] = array(
  59       'dao' => 'CRM_Contact_DAO_Contact',
  60       'fields' => array(
  61         'id' => array(
  62           'title' => ts('Contact ID'),
  63           'required' => TRUE,
  64         ),
  65         'sort_name' =>
  66         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' =>
  85       array(
  86         'sort_name' =>
  87         array('title' => ts('Contact Name'), 'default' => TRUE, 'default_order' => 'ASC'),
  88       ),
  89       'grouping' => 'contact-fields',
  90     );
  91
  92     $this->_columns['civicrm_mailing'] = array(
  93       'dao' => 'CRM_Mailing_DAO_Mailing',
  94       'fields' => array(
  95         'mailing_name' => array(
  96           'name' => 'name',
  97           'title' => ts('Mailing'),
  98           'default' => TRUE,
  99         ),
 100         'mailing_name_alias' => array(
 101           'name' => 'name',
 102           'required' => TRUE,
 103           'no_display' => TRUE,
 104         ),
 105       ),
 106       'filters' => array(
 107         'mailing_id' => array(
 108           'name' => 'id',
 109           'title' => ts('Mailing'),
 110           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 111           'type' => CRM_Utils_Type::T_INT,
 112           'options' => CRM_Mailing_BAO_Mailing::getMailingsList(),
 113           'operator' => 'like',
 114         ),
 115       ),
 116       'order_bys' =>
 117       array(
 118         'mailing_name' =>
 119         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       ),
 134       'order_bys' =>
 135       array(
 136         'bounce_reason' =>
 137         array('title' => ts('Bounce Reason')),
 138       ),
 139       'grouping' => 'mailing-fields',
 140     );
 141
 142     $this->_columns['civicrm_mailing_bounce_type'] = array(
 143       'dao' => 'CRM_Mailing_DAO_BounceType',
 144       'fields' => array(
 145         'bounce_name' => array(
 146           'name' => 'name',
 147           'title' => ts('Bounce Type'),
 148         ),
 149       ),
 150       'filters' => array(
 151         'bounce_type_name' => array(
 152           'name' => 'name',
 153           'title' => ts('Bounce Type'),
 154           'operatorType' => CRM_Report_Form::OP_SELECT,
 155           'type' => CRM_Utils_Type::T_STRING,
 156           'options' => self::bounce_type(),
 157           'operator' => 'like',
 158         ),
 159       ),
 160       'order_bys' =>
 161       array(
 162         'bounce_name' =>
 163         array(
 164           'name' => 'name',
 165           'title' => ts('Bounce Type'),
 166         ),
 167       ),
 168       'grouping' => 'mailing-fields',
 169     );
 170
 171     $this->_columns['civicrm_email'] = array(
 172       'dao' => 'CRM_Core_DAO_Email',
 173       'fields' => array(
 174         'email' => array(
 175           'title' => ts('Email'),
 176           'no_repeat' => TRUE,
 177           'required' => TRUE,
 178         ),
 179       ),
 180       'order_bys' =>
 181       array(
 182         'email' =>
 183         array('title' => ts('Email'), 'default_order' => 'ASC'),
 184       ),
 185       'grouping' => 'contact-fields',
 186     );
 187
 188     $this->_columns['civicrm_phone'] = array(
 189       'dao' => 'CRM_Core_DAO_Phone',
 190       'fields' => array('phone' => NULL),
 191       'grouping' => 'contact-fields',
 192     );
 193
 194     $this->_columns['civicrm_group'] = array(
 195       'dao' => 'CRM_Contact_DAO_Group',
 196       'alias' => 'cgroup',
 197       'filters' => array(
 198         'gid' => array(
 199           'name' => 'group_id',
 200           'title' => ts('Group'),
 201           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 202           'group' => TRUE,
 203           'options' => CRM_Core_PseudoConstant::group(),
 204         ),
 205       ),
 206     );
 207
 208     $this->_tagFilter = TRUE;
 209     parent::__construct();
 210   }
 211
 212   function preProcess() {
 213     $this->assign('chartSupported', TRUE);
 214     parent::preProcess();
 215   }
 216
 217   function select() {
 218     $select = array();
 219     $this->_columnHeaders = array();
 220
 221     foreach ($this->_columns as $tableName => $table) {
 222       if (array_key_exists('fields', $table)) {
 223         foreach ($table['fields'] as $fieldName => $field) {
 224           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 225             if ($tableName == 'civicrm_email') {
 226               $this->_emailField = TRUE;
 227             }
 228             elseif ($tableName == 'civicrm_phone') {
 229               $this->_phoneField = TRUE;
 230             }
 231
 232             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 233             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 234             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 235             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 236           }
 237         }
 238       }
 239     }
 240
 241
 242     if (!empty($this->_params['charts'])) {
 243       $select[] = "COUNT({$this->_aliases['civicrm_mailing_event_bounce']}.id) as civicrm_mailing_bounce_count";
 244       $this->_columnHeaders["civicrm_mailing_bounce_count"]['title'] = ts('Bounce Count');
 245     }
 246
 247     $this->_select = "SELECT " . implode(', ', $select) . " ";
 248   }
 249
 250   static function formRule($fields, $files, $self) {
 251     $errors = $grouping = array();
 252     return $errors;
 253   }
 254
 255   function from() {
 256     $this->_from = "
 257         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 258     // LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 259     // ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 260     // {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 261
 262     $this->_from .= "
 263         INNER JOIN civicrm_mailing_event_queue
 264           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 265         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 266           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 267         INNER JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 268           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id
 269         LEFT JOIN civicrm_mailing_bounce_type {$this->_aliases['civicrm_mailing_bounce_type']}
 270           ON {$this->_aliases['civicrm_mailing_event_bounce']}.bounce_type_id = {$this->_aliases['civicrm_mailing_bounce_type']}.id
 271         INNER JOIN civicrm_mailing_job
 272           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 273         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 274           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 275       ";
 276
 277     if ($this->_phoneField) {
 278       $this->_from .= "
 279             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 280                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 281                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 282     }
 283   }
 284
 285   function where() {
 286     parent::where();
 287     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 288   }
 289
 290   function groupBy() {
 291     if (!empty($this->_params['charts'])) {
 292       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 293     }
 294     else {
 295       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing_event_bounce']}.id";
 296     }
 297   }
 298
 299   function postProcess() {
 300     $this->beginPostProcess();
 301
 302     // get the acl clauses built before we assemble the query
 303     $this->buildACLClause($this->_aliases['civicrm_contact']);
 304
 305     $sql = $this->buildQuery(TRUE);
 306
 307     $rows = $graphRows = array();
 308     $this->buildRows($sql, $rows);
 309
 310     $this->formatDisplay($rows);
 311     $this->doTemplateAssignment($rows);
 312     $this->endPostProcess($rows);
 313   }
 314
 315   function buildChart(&$rows) {
 316     if (empty($rows)) {
 317       return;
 318     }
 319
 320     $chartInfo = array('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   function bounce_type() {
 336
 337     $data = array('' => '--Please Select--');
 338
 339     $bounce_type = new CRM_Mailing_DAO_BounceType();
 340     $query = "SELECT name FROM civicrm_mailing_bounce_type";
 341     $bounce_type->query($query);
 342
 343     while ($bounce_type->fetch()) {
 344       $data[$bounce_type->name] = $bounce_type->name;
 345     }
 346
 347     return $data;
 348   }
 349
 350   function alterDisplay(&$rows) {
 351     // custom code to alter rows
 352     $entryFound = FALSE;
 353     foreach ($rows as $rowNum => $row) {
 354       // make count columns point to detail report
 355       // convert display name to links
 356       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 357         array_key_exists('civicrm_contact_id', $row)
 358       ) {
 359         $url = CRM_Utils_System::url('civicrm/contact/view',
 360           'reset=1&cid=' . $row['civicrm_contact_id'],
 361           $this->_absoluteUrl
 362         );
 363         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 364         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 365         $entryFound = TRUE;
 366       }
 367
 368       // skip looking further in rows, if first row itself doesn't
 369       // have the column we need
 370       if (!$entryFound) {
 371         break;
 372       }
 373     }
 374   }
 375 }
 376