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