CRM/Report/Form/Mailing/Opened.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Mailing_Opened extends CRM_Report_Form {
  18
  19   protected $_summary = NULL;
  20
  21   protected $_emailField = FALSE;
  22
  23   protected $_phoneField = FALSE;
  24
  25   protected $_customGroupExtends = [
  26     'Contact',
  27     'Individual',
  28     'Household',
  29     'Organization',
  30   ];
  31
  32   protected $_charts = [
  33     '' => 'Tabular',
  34     'barChart' => 'Bar Chart',
  35     'pieChart' => 'Pie Chart',
  36   ];
  37
  38   /**
  39    * This report has not been optimised for group filtering.
  40    *
  41    * The functionality for group filtering has been improved but not
  42    * all reports have been adjusted to take care of it. This report has not
  43    * and will run an inefficient query until fixed.
  44    *
  45    * CRM-19170
  46    *
  47    * @var bool
  48    */
  49   protected $groupFilterNotOptimised = TRUE;
  50
  51   /**
  52    * Class constructor.
  53    */
  54   public function __construct() {
  55     $this->optimisedForOnlyFullGroupBy = FALSE;
  56     $this->_columns = [];
  57
  58     $this->_columns['civicrm_contact'] = [
  59       'dao' => 'CRM_Contact_DAO_Contact',
  60       'fields' => [
  61         'id' => [
  62           'title' => ts('Contact ID'),
  63           'required' => TRUE,
  64         ],
  65         'sort_name' => [
  66           'title' => ts('Contact Name'),
  67           'required' => TRUE,
  68         ],
  69       ],
  70       'filters' => [
  71         'sort_name' => [
  72           'title' => ts('Contact Name'),
  73         ],
  74         'source' => [
  75           'title' => ts('Contact Source'),
  76           'type' => CRM_Utils_Type::T_STRING,
  77         ],
  78         'id' => [
  79           'title' => ts('Contact ID'),
  80           'no_display' => TRUE,
  81         ],
  82       ],
  83       'order_bys' => [
  84         'sort_name' => [
  85           'title' => ts('Contact Name'),
  86           'default' => TRUE,
  87           'default_order' => 'ASC',
  88         ],
  89       ],
  90       'grouping' => 'contact-fields',
  91     ];
  92
  93     $this->_columns['civicrm_mailing'] = [
  94       'dao' => 'CRM_Mailing_DAO_Mailing',
  95       'fields' => [
  96         'mailing_name' => [
  97           'name' => 'name',
  98           'title' => ts('Mailing Name'),
  99           'default' => TRUE,
 100         ],
 101         'mailing_name_alias' => [
 102           'name' => 'name',
 103           'required' => TRUE,
 104           'no_display' => TRUE,
 105         ],
 106         'mailing_subject' => [
 107           'name' => 'subject',
 108           'title' => ts('Mailing Subject'),
 109           'default' => TRUE,
 110         ],
 111       ],
 112       'filters' => [
 113         'mailing_id' => [
 114           'name' => 'id',
 115           'title' => ts('Mailing Name'),
 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         'mailing_subject' => [
 122           'name' => 'subject',
 123           'title' => ts('Mailing Subject'),
 124           'type' => CRM_Utils_Type::T_STRING,
 125           'operator' => 'like',
 126         ],
 127       ],
 128       'order_bys' => [
 129         'mailing_name' => [
 130           'name' => 'name',
 131           'title' => ts('Mailing Name'),
 132         ],
 133         'mailing_subject' => [
 134           'name' => 'subject',
 135           'title' => ts('Mailing Subject'),
 136         ],
 137       ],
 138       'grouping' => 'mailing-fields',
 139     ];
 140
 141     $this->_columns['civicrm_email'] = [
 142       'dao' => 'CRM_Core_DAO_Email',
 143       'fields' => [
 144         'email' => [
 145           'title' => ts('Email'),
 146           'no_repeat' => TRUE,
 147         ],
 148       ],
 149       'order_bys' => [
 150         'email' => ['title' => ts('Email'), 'default_order' => 'ASC'],
 151       ],
 152       'grouping' => 'contact-fields',
 153     ];
 154
 155     $this->_columns['civicrm_phone'] = [
 156       'dao' => 'CRM_Core_DAO_Phone',
 157       'fields' => ['phone' => NULL],
 158       'grouping' => 'contact-fields',
 159     ];
 160
 161     $this->_columns['civicrm_mailing_event_opened'] = [
 162       'dao' => 'CRM_Mailing_Event_DAO_Opened',
 163       'fields' => [
 164         'id' => [
 165           'required' => TRUE,
 166           'no_display' => TRUE,
 167           'dbAlias' => CRM_Utils_SQL::supportsFullGroupBy() ? 'ANY_VALUE(mailing_event_opened_civireport.id)' : NULL,
 168         ],
 169         'time_stamp' => [
 170           'title' => ts('Open Date'),
 171           'default' => TRUE,
 172         ],
 173       ],
 174       'filters' => [
 175         'time_stamp' => [
 176           'title' => ts('Open Date'),
 177           'operatorType' => CRM_Report_Form::OP_DATE,
 178           'type' => CRM_Utils_Type::T_DATE,
 179         ],
 180         'unique_opens' => [
 181           'title' => ts('Unique Opens'),
 182           'type' => CRM_Utils_Type::T_BOOLEAN,
 183           'pseudofield' => TRUE,
 184         ],
 185       ],
 186       'order_bys' => [
 187         'time_stamp' => [
 188           'title' => ts('Open Date'),
 189         ],
 190       ],
 191       'grouping' => 'mailing-fields',
 192     ];
 193
 194     $this->_groupFilter = TRUE;
 195     $this->_tagFilter = TRUE;
 196     parent::__construct();
 197   }
 198
 199   public function preProcess() {
 200     $this->assign('chartSupported', TRUE);
 201     parent::preProcess();
 202   }
 203
 204   public function select() {
 205     $select = [];
 206     $this->_columnHeaders = [];
 207     foreach ($this->_columns as $tableName => $table) {
 208       if (array_key_exists('fields', $table)) {
 209         foreach ($table['fields'] as $fieldName => $field) {
 210           if (!empty($field['required']) ||
 211             !empty($this->_params['fields'][$fieldName])
 212           ) {
 213             if ($tableName == 'civicrm_email') {
 214               $this->_emailField = TRUE;
 215             }
 216             elseif ($tableName == 'civicrm_phone') {
 217               $this->_phoneField = TRUE;
 218             }
 219
 220             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 221             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 222             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = $field['no_display'] ?? NULL;
 223             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 224           }
 225         }
 226       }
 227     }
 228
 229     if (!empty($this->_params['charts'])) {
 230       $select[] = "COUNT({$this->_aliases['civicrm_mailing_event_opened']}.id) as civicrm_mailing_opened_count";
 231       $this->_columnHeaders["civicrm_mailing_opened_count"]['title'] = ts('Opened Count');
 232     }
 233
 234     $this->_selectClauses = $select;
 235     $this->_select = "SELECT " . implode(', ', $select) . " ";
 236   }
 237
 238   /**
 239    * @param $fields
 240    * @param $files
 241    * @param $self
 242    *
 243    * @return array
 244    */
 245   public static function formRule($fields, $files, $self) {
 246     $errors = $grouping = [];
 247     return $errors;
 248   }
 249
 250   public function from() {
 251     $this->_from = "
 252       FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 253
 254     $this->_from .= "
 255       INNER JOIN civicrm_mailing_event_queue
 256         ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 257       LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 258         ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 259       INNER JOIN civicrm_mailing_event_opened {$this->_aliases['civicrm_mailing_event_opened']}
 260         ON {$this->_aliases['civicrm_mailing_event_opened']}.event_queue_id = civicrm_mailing_event_queue.id
 261       INNER JOIN civicrm_mailing_job
 262         ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 263       INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 264         ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 265         AND civicrm_mailing_job.is_test = 0
 266     ";
 267     $this->joinPhoneFromContact();
 268   }
 269
 270   public function where() {
 271     parent::where();
 272     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 273   }
 274
 275   public function groupBy() {
 276     $groupBys = [];
 277     // Do not use group by clause if distinct = 0 mentioned in url params. flag is used in mailing report screen, default value is TRUE
 278     // this report is used to show total opened and unique opened
 279     if (CRM_Utils_Request::retrieve('distinct', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, TRUE)) {
 280       $groupBys = empty($this->_params['charts']) ? ["civicrm_mailing_event_queue.email_id"] : ["{$this->_aliases['civicrm_mailing']}.id"];
 281       if (!empty($this->_params['unique_opens_value'])) {
 282         $groupBys[] = "civicrm_mailing_event_queue.id";
 283       }
 284     }
 285     if (!empty($groupBys)) {
 286       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
 287     }
 288   }
 289
 290   public function postProcess() {
 291
 292     $this->beginPostProcess();
 293
 294     // get the acl clauses built before we assemble the query
 295     $this->buildACLClause($this->_aliases['civicrm_contact']);
 296
 297     $sql = $this->buildQuery(TRUE);
 298
 299     $rows = $graphRows = [];
 300     $this->buildRows($sql, $rows);
 301
 302     $this->formatDisplay($rows);
 303     $this->doTemplateAssignment($rows);
 304     $this->endPostProcess($rows);
 305   }
 306
 307   /**
 308    * @param $rows
 309    */
 310   public function buildChart(&$rows) {
 311     if (empty($rows)) {
 312       return;
 313     }
 314
 315     $chartInfo = [
 316       'legend' => ts('Mail Opened Report'),
 317       'xname' => ts('Mailing'),
 318       'yname' => ts('Opened'),
 319       'xLabelAngle' => 20,
 320       'tip' => ts('Mail Opened: %1', [1 => '#val#']),
 321     ];
 322     foreach ($rows as $row) {
 323       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_opened_count'];
 324     }
 325
 326     // build the chart.
 327     CRM_Utils_Chart::buildChart($chartInfo, $this->_params['charts']);
 328     $this->assign('chartType', $this->_params['charts']);
 329   }
 330
 331   /**
 332    * Alter display of rows.
 333    *
 334    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 335    * such as rendering contacts as links.
 336    *
 337    * @param array $rows
 338    *   Rows generated by SQL, with an array for each row.
 339    */
 340   public function alterDisplay(&$rows) {
 341     $entryFound = FALSE;
 342     foreach ($rows as $rowNum => $row) {
 343
 344       // If the email address has been deleted
 345       if (array_key_exists('civicrm_email_email', $row)) {
 346         if (empty($rows[$rowNum]['civicrm_email_email'])) {
 347           $rows[$rowNum]['civicrm_email_email'] = '<del>Email address deleted</del>';
 348         }
 349         $entryFound = TRUE;
 350       }
 351
 352       // make count columns point to detail report
 353       // convert display name to links
 354       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 355         array_key_exists('civicrm_contact_id', $row)
 356       ) {
 357         $url = CRM_Utils_System::url('civicrm/contact/view',
 358           'reset=1&cid=' . $row['civicrm_contact_id'],
 359           $this->_absoluteUrl
 360         );
 361         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 362         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 363         $entryFound = TRUE;
 364       }
 365
 366       // skip looking further in rows, if first row itself doesn't
 367       // have the column we need
 368       if (!$entryFound) {
 369         break;
 370       }
 371     }
 372   }
 373
 374 }