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