CRM/Report/Form/Mailing/Clicks.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Mailing_Clicks extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38
  39   protected $_emailField = FALSE;
  40
  41   protected $_phoneField = FALSE;
  42
  43   protected $_customGroupExtends = array(
  44     'Contact',
  45     'Individual',
  46     'Household',
  47     'Organization',
  48   );
  49
  50   protected $_charts = array(
  51     '' => 'Tabular',
  52     'barChart' => 'Bar Chart',
  53     'pieChart' => 'Pie Chart',
  54   );
  55
  56   /**
  57    */
  58   /**
  59    */
  60   public function __construct() {
  61     $this->_columns = array();
  62
  63     $this->_columns['civicrm_contact'] = array(
  64       'dao' => 'CRM_Contact_DAO_Contact',
  65       'fields' => array(
  66         'id' => array(
  67           'title' => ts('Contact ID'),
  68           'required' => TRUE,
  69         ),
  70         'sort_name' => array(
  71           'title' => ts('Contact Name'),
  72           'required' => TRUE,
  73         ),
  74       ),
  75       'filters' => array(
  76         'sort_name' => array(
  77           'title' => ts('Contact Name'),
  78         ),
  79         'source' => array(
  80           'title' => ts('Contact Source'),
  81           'type' => CRM_Utils_Type::T_STRING,
  82         ),
  83         'id' => array(
  84           'title' => ts('Contact ID'),
  85           'no_display' => TRUE,
  86         ),
  87       ),
  88       'order_bys' => array(
  89         'sort_name' => array(
  90           'title' => ts('Contact Name'),
  91           'default' => TRUE,
  92           'default_order' => 'ASC',
  93         ),
  94       ),
  95       'grouping' => 'contact-fields',
  96     );
  97
  98     $this->_columns['civicrm_mailing'] = array(
  99       'dao' => 'CRM_Mailing_DAO_Mailing',
 100       'fields' => array(
 101         'mailing_name' => array(
 102           'name' => 'name',
 103           'title' => ts('Mailing'),
 104           'default' => TRUE,
 105         ),
 106         'mailing_name_alias' => array(
 107           'name' => 'name',
 108           'required' => TRUE,
 109           'no_display' => TRUE,
 110         ),
 111       ),
 112       'filters' => array(
 113         'mailing_id' => array(
 114           'name' => 'id',
 115           'title' => ts('Mailing'),
 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       ),
 122       'order_bys' => array(
 123         'mailing_name' => 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       'grouping' => 'contact-fields',
 141     );
 142
 143     $this->_columns['civicrm_phone'] = array(
 144       'dao' => 'CRM_Core_DAO_Phone',
 145       'fields' => array('phone' => NULL),
 146       'grouping' => 'contact-fields',
 147     );
 148
 149     $this->_columns['civicrm_mailing_trackable_url'] = array(
 150       'dao' => 'CRM_Mailing_DAO_TrackableURL',
 151       'fields' => array(
 152         'url' => array(
 153           'title' => ts('Click through URL'),
 154         ),
 155       ),
 156       // To do this filter should really be like mailing id filter a multi select, However
 157       // Not clear on how to make filter dependant on selected mailings at this stage so have set a
 158       // text filter which works for now
 159       'filters' => array(
 160         'url' => array(
 161           'title' => ts('URL'),
 162           'type' => CRM_Utils_Type::T_STRING,
 163           'operator' => 'like',
 164         ),
 165       ),
 166       'order_bys' => array(
 167         'url' => array('title' => ts('Click through URL')),
 168       ),
 169       'grouping' => 'mailing-fields',
 170     );
 171
 172     $this->_groupFilter = TRUE;
 173     $this->_tagFilter = TRUE;
 174     parent::__construct();
 175   }
 176
 177   public function preProcess() {
 178     $this->assign('chartSupported', TRUE);
 179     parent::preProcess();
 180   }
 181
 182   public function select() {
 183     $select = array();
 184     $this->_columnHeaders = array();
 185     foreach ($this->_columns as $tableName => $table) {
 186       if (array_key_exists('fields', $table)) {
 187         foreach ($table['fields'] as $fieldName => $field) {
 188           if (!empty($field['required']) ||
 189             !empty($this->_params['fields'][$fieldName])
 190           ) {
 191             if ($tableName == 'civicrm_email') {
 192               $this->_emailField = TRUE;
 193             }
 194             elseif ($tableName == 'civicrm_phone') {
 195               $this->_phoneField = TRUE;
 196             }
 197
 198             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 199             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 200             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 201             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 202           }
 203         }
 204       }
 205     }
 206
 207     if (!empty($this->_params['charts'])) {
 208       $select[] = "COUNT(civicrm_mailing_event_trackable_url_open.id) as civicrm_mailing_click_count";
 209       $this->_columnHeaders["civicrm_mailing_click_count"]['title'] = ts('Click Count');
 210     }
 211
 212     $this->_select = "SELECT " . implode(', ', $select) . " ";
 213   }
 214
 215   /**
 216    * @param $fields
 217    * @param $files
 218    * @param $self
 219    *
 220    * @return array
 221    */
 222   public static function formRule($fields, $files, $self) {
 223     $errors = $grouping = array();
 224     return $errors;
 225   }
 226
 227   public function from() {
 228     $this->_from = "
 229         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 230
 231     $this->_from .= "
 232         INNER JOIN civicrm_mailing_event_queue
 233           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 234         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 235           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 236         INNER JOIN civicrm_mailing_event_trackable_url_open
 237           ON civicrm_mailing_event_trackable_url_open.event_queue_id = civicrm_mailing_event_queue.id
 238         INNER JOIN civicrm_mailing_trackable_url {$this->_aliases['civicrm_mailing_trackable_url']}
 239           ON civicrm_mailing_event_trackable_url_open.trackable_url_id = {$this->_aliases['civicrm_mailing_trackable_url']}.id
 240         INNER JOIN civicrm_mailing_job
 241           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 242         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 243           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 244           AND civicrm_mailing_job.is_test = 0
 245       ";
 246     if ($this->_phoneField) {
 247       $this->_from .= "
 248             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 249                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 250                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 251     }
 252   }
 253
 254   public function where() {
 255     parent::where();
 256     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 257   }
 258
 259   public function groupBy() {
 260
 261     $this->_groupBy = '';
 262     if (!empty($this->_params['charts'])) {
 263       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 264     }
 265     else {
 266       $this->_groupBy = " GROUP BY civicrm_mailing_event_trackable_url_open.id";
 267     }
 268   }
 269
 270   public function postProcess() {
 271
 272     $this->beginPostProcess();
 273
 274     // get the acl clauses built before we assemble the query
 275     $this->buildACLClause($this->_aliases['civicrm_contact']);
 276
 277     $sql = $this->buildQuery(TRUE);
 278
 279     $rows = $graphRows = array();
 280     $this->buildRows($sql, $rows);
 281
 282     $this->formatDisplay($rows);
 283     $this->doTemplateAssignment($rows);
 284     $this->endPostProcess($rows);
 285   }
 286
 287   /**
 288    * @param $rows
 289    */
 290   public function buildChart(&$rows) {
 291     if (empty($rows)) {
 292       return;
 293     }
 294
 295     $chartInfo = array(
 296       'legend' => ts('Mail Clickthrough Report'),
 297       'xname' => ts('Mailing'),
 298       'yname' => ts('Clicks'),
 299       'xLabelAngle' => 20,
 300       'tip' => ts('Clicks: %1', array(1 => '#val#')),
 301     );
 302     foreach ($rows as $row) {
 303       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_click_count'];
 304     }
 305
 306     // build the chart.
 307     CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 308     $this->assign('chartType', $this->_params['charts']);
 309   }
 310
 311   /**
 312    * Alter display of rows.
 313    *
 314    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 315    * such as rendering contacts as links.
 316    *
 317    * @param array $rows
 318    *   Rows generated by SQL, with an array for each row.
 319    */
 320   public function alterDisplay(&$rows) {
 321     $entryFound = FALSE;
 322     foreach ($rows as $rowNum => $row) {
 323       // make count columns point to detail report
 324       // convert display name to links
 325       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 326         array_key_exists('civicrm_contact_id', $row)
 327       ) {
 328         $url = CRM_Utils_System::url('civicrm/contact/view',
 329           'reset=1&cid=' . $row['civicrm_contact_id'],
 330           $this->_absoluteUrl
 331         );
 332         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 333         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 334         $entryFound = TRUE;
 335       }
 336
 337       // skip looking further in rows, if first row itself doesn't
 338       // have the column we need
 339       if (!$entryFound) {
 340         break;
 341       }
 342     }
 343   }
 344
 345 }