CRM/Report/Form/Mailing/Opened.php

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