CRM/Report/Form/Mailing/Bounce.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  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 (CRM_Utils_Array::value('required', $field) ||
 225             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 226           ) {
 227             if ($tableName == 'civicrm_email') {
 228               $this->_emailField = TRUE;
 229             }
 230             elseif ($tableName == 'civicrm_phone') {
 231               $this->_phoneField = TRUE;
 232             }
 233
 234             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 235             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 236             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 237             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 238           }
 239         }
 240       }
 241     }
 242
 243
 244     if (CRM_Utils_Array::value('charts', $this->_params)) {
 245       $select[] = "COUNT({$this->_aliases['civicrm_mailing_event_bounce']}.id) as civicrm_mailing_bounce_count";
 246       $this->_columnHeaders["civicrm_mailing_bounce_count"]['title'] = ts('Bounce Count');
 247     }
 248
 249     $this->_select = "SELECT " . implode(', ', $select) . " ";
 250   }
 251
 252   static function formRule($fields, $files, $self) {
 253     $errors = $grouping = array();
 254     return $errors;
 255   }
 256
 257   function from() {
 258     $this->_from = "
 259         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 260     // LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 261     // ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 262     // {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 263
 264     $this->_from .= "
 265         INNER JOIN civicrm_mailing_event_queue
 266           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 267         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 268           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 269         INNER JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 270           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id
 271         LEFT JOIN civicrm_mailing_bounce_type {$this->_aliases['civicrm_mailing_bounce_type']}
 272           ON {$this->_aliases['civicrm_mailing_event_bounce']}.bounce_type_id = {$this->_aliases['civicrm_mailing_bounce_type']}.id
 273         INNER JOIN civicrm_mailing_job
 274           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 275         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 276           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 277       ";
 278
 279     if ($this->_phoneField) {
 280       $this->_from .= "
 281             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 282                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 283                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 284     }
 285   }
 286
 287   function where() {
 288     parent::where();
 289     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 290   }
 291
 292   function groupBy() {
 293     if (CRM_Utils_Array::value('charts', $this->_params)) {
 294       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 295     }
 296     else {
 297       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing_event_bounce']}.id";
 298     }
 299   }
 300
 301   function postProcess() {
 302     $this->beginPostProcess();
 303
 304     // get the acl clauses built before we assemble the query
 305     $this->buildACLClause($this->_aliases['civicrm_contact']);
 306
 307     $sql = $this->buildQuery(TRUE);
 308
 309     $rows = $graphRows = array();
 310     $this->buildRows($sql, $rows);
 311
 312     $this->formatDisplay($rows);
 313     $this->doTemplateAssignment($rows);
 314     $this->endPostProcess($rows);
 315   }
 316
 317   function buildChart(&$rows) {
 318     if (empty($rows)) {
 319       return;
 320     }
 321
 322     $chartInfo = array('legend' => ts('Mail Bounce Report'),
 323       'xname' => ts('Mailing'),
 324       'yname' => ts('Bounce'),
 325       'xLabelAngle' => 20,
 326       'tip' => ts('Mail Bounce: %1', array(1 => '#val#')),
 327     );
 328     foreach ($rows as $row) {
 329       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_bounce_count'];
 330     }
 331
 332     // build the chart.
 333     CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 334     $this->assign('chartType', $this->_params['charts']);
 335   }
 336
 337   function bounce_type() {
 338
 339     $data = array('' => '--Please Select--');
 340
 341     $bounce_type = new CRM_Mailing_DAO_BounceType();
 342     $query = "SELECT name FROM civicrm_mailing_bounce_type";
 343     $bounce_type->query($query);
 344
 345     while ($bounce_type->fetch()) {
 346       $data[$bounce_type->name] = $bounce_type->name;
 347     }
 348
 349     return $data;
 350   }
 351
 352   function alterDisplay(&$rows) {
 353     // custom code to alter rows
 354     $entryFound = FALSE;
 355     foreach ($rows as $rowNum => $row) {
 356       // make count columns point to detail report
 357       // convert display name to links
 358       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 359         array_key_exists('civicrm_contact_id', $row)
 360       ) {
 361         $url = CRM_Utils_System::url('civicrm/contact/view',
 362           'reset=1&cid=' . $row['civicrm_contact_id'],
 363           $this->_absoluteUrl
 364         );
 365         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 366         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 367         $entryFound = TRUE;
 368       }
 369
 370       // skip looking further in rows, if first row itself doesn't
 371       // have the column we need
 372       if (!$entryFound) {
 373         break;
 374       }
 375     }
 376   }
 377 }
 378