CRM/Report/Form/Mailing/Bounce.php

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