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