CRM/Report/Form/Mailing/Clicks.php

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