CRM/Report/Form/Mailing/Opened.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Mailing_Opened 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       'order_bys' => array(
 141         'email' => array('title' => ts('Email'), 'default_order' => 'ASC'),
 142       ),
 143       'grouping' => 'contact-fields',
 144     );
 145
 146     $this->_columns['civicrm_phone'] = array(
 147       'dao' => 'CRM_Core_DAO_Phone',
 148       'fields' => array('phone' => NULL),
 149       'grouping' => 'contact-fields',
 150     );
 151
 152     $this->_groupFilter = TRUE;
 153     $this->_tagFilter = TRUE;
 154     parent::__construct();
 155   }
 156
 157   public function preProcess() {
 158     $this->assign('chartSupported', TRUE);
 159     parent::preProcess();
 160   }
 161
 162   public function select() {
 163     $select = array();
 164     $this->_columnHeaders = array();
 165     foreach ($this->_columns as $tableName => $table) {
 166       if (array_key_exists('fields', $table)) {
 167         foreach ($table['fields'] as $fieldName => $field) {
 168           if (!empty($field['required']) ||
 169             !empty($this->_params['fields'][$fieldName])
 170           ) {
 171             if ($tableName == 'civicrm_email') {
 172               $this->_emailField = TRUE;
 173             }
 174             elseif ($tableName == 'civicrm_phone') {
 175               $this->_phoneField = TRUE;
 176             }
 177
 178             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 179             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 180             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 181             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 182           }
 183         }
 184       }
 185     }
 186
 187     if (!empty($this->_params['charts'])) {
 188       $select[] = "COUNT(civicrm_mailing_event_opened.id) as civicrm_mailing_opened_count";
 189       $this->_columnHeaders["civicrm_mailing_opened_count"]['title'] = ts('Opened Count');
 190     }
 191
 192     $this->_select = "SELECT " . implode(', ', $select) . " ";
 193   }
 194
 195   /**
 196    * @param $fields
 197    * @param $files
 198    * @param $self
 199    *
 200    * @return array
 201    */
 202   public static function formRule($fields, $files, $self) {
 203     $errors = $grouping = array();
 204     return $errors;
 205   }
 206
 207   public function from() {
 208     $this->_from = "
 209         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 210
 211     $this->_from .= "
 212         INNER JOIN civicrm_mailing_event_queue
 213           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 214         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 215           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id
 216         INNER JOIN civicrm_mailing_event_opened
 217           ON civicrm_mailing_event_opened.event_queue_id = civicrm_mailing_event_queue.id
 218         INNER JOIN civicrm_mailing_job
 219           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 220         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 221           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 222           AND civicrm_mailing_job.is_test = 0
 223       ";
 224
 225     if ($this->_phoneField) {
 226       $this->_from .= "
 227             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 228                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 229                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 230     }
 231   }
 232
 233   public function where() {
 234     parent::where();
 235     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 236   }
 237
 238   public function groupBy() {
 239     if (!empty($this->_params['charts'])) {
 240       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id";
 241     }
 242     else {
 243       $this->_groupBy = " GROUP BY civicrm_mailing_event_queue.email_id";
 244     }
 245   }
 246
 247   public function postProcess() {
 248
 249     $this->beginPostProcess();
 250
 251     // get the acl clauses built before we assemble the query
 252     $this->buildACLClause($this->_aliases['civicrm_contact']);
 253
 254     $sql = $this->buildQuery(TRUE);
 255
 256     $rows = $graphRows = array();
 257     $this->buildRows($sql, $rows);
 258
 259     $this->formatDisplay($rows);
 260     $this->doTemplateAssignment($rows);
 261     $this->endPostProcess($rows);
 262   }
 263
 264   /**
 265    * @param $rows
 266    */
 267   public function buildChart(&$rows) {
 268     if (empty($rows)) {
 269       return;
 270     }
 271
 272     $chartInfo = array(
 273       'legend' => ts('Mail Opened Report'),
 274       'xname' => ts('Mailing'),
 275       'yname' => ts('Opened'),
 276       'xLabelAngle' => 20,
 277       'tip' => ts('Mail Opened: %1', array(1 => '#val#')),
 278     );
 279     foreach ($rows as $row) {
 280       $chartInfo['values'][$row['civicrm_mailing_mailing_name_alias']] = $row['civicrm_mailing_opened_count'];
 281     }
 282
 283     // build the chart.
 284     CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 285     $this->assign('chartType', $this->_params['charts']);
 286   }
 287
 288   /**
 289    * Alter display of rows.
 290    *
 291    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 292    * such as rendering contacts as links.
 293    *
 294    * @param array $rows
 295    *   Rows generated by SQL, with an array for each row.
 296    */
 297   public function alterDisplay(&$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 }