CRM/Report/Form/Mailing/Detail.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_Detail extends CRM_Report_Form {
  37
  38   protected $_exposeContactID = FALSE;
  39
  40   function __construct() {
  41     $this->_columns = array();
  42
  43     $this->_columns['civicrm_contact'] = array(
  44       'dao' => 'CRM_Contact_DAO_Contact',
  45       'fields' =>
  46       array(
  47         'id' =>
  48         array(
  49           'name' => 'id',
  50           'title' => ts('Contact ID'),
  51           'required' => TRUE,
  52           'no_display' => TRUE,
  53         ),
  54         'sort_name' =>
  55         array(
  56           'title' => ts('Contact Name'),
  57           'required' => TRUE,
  58         ),
  59       ),
  60       'filters' =>
  61       array(
  62         'sort_name' =>
  63         array(
  64           'title' => ts('Contact Name'),
  65         ),
  66         'id' =>
  67         array(
  68           'title' => ts('Contact ID'),
  69           'no_display' => TRUE,
  70         ),
  71       ),
  72       'order_bys' =>
  73       array(
  74         'sort_name' =>
  75         array('title' => ts('Contact Name'),
  76           'default' => TRUE,
  77           'default_order' => 'ASC',
  78         ),
  79       ),
  80       'grouping' => 'contact-fields',
  81     );
  82
  83     $this->_columns['civicrm_mailing'] = array(
  84       'dao' => 'CRM_Mailing_DAO_Mailing',
  85       'fields' =>
  86       array(
  87         'mailing_name' =>
  88         array(
  89           'name' => 'name',
  90           'title' => ts('Mailing'),
  91           'default' => TRUE,
  92         ),
  93       ),
  94       'filters' =>
  95       array(
  96         'mailing_id' =>
  97         array(
  98           'name' => 'id',
  99           'title' => ts('Mailing'),
 100           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 101           'type' => CRM_Utils_Type::T_INT,
 102           'options' => CRM_Mailing_BAO_Mailing::getMailingsList(),
 103         ),
 104       ),
 105       'order_bys' =>
 106       array(
 107         'mailing_name' =>
 108         array(
 109           'name' => 'name',
 110           'title' => ts('Mailing'),
 111         ),
 112       ),
 113       'grouping' => 'mailing-fields',
 114     );
 115
 116     // adding dao just to have alias
 117     $this->_columns['civicrm_mailing_event_bounce'] = array(
 118       'dao' => 'CRM_Mailing_Event_DAO_Bounce',
 119     );
 120
 121     $this->_columns['civicrm_mailing_event_delivered'] = array(
 122       'dao' => 'CRM_Mailing_Event_DAO_Delivered',
 123       'fields' =>
 124       array(
 125         'delivery_id' =>
 126         array(
 127           'name' => 'id',
 128           'title' => ts('Delivery Status'),
 129           'default' => TRUE,
 130         ),
 131       ),
 132       'filters' =>
 133       array(
 134         'delivery_status' =>
 135         array(
 136           'name' => 'delivery_status',
 137           'title' => ts('Delivery Status'),
 138           'operatorType' => CRM_Report_Form::OP_SELECT,
 139           'type' => CRM_Utils_Type::T_STRING,
 140           'options' => array(
 141             '' => 'Any',
 142             'successful' => 'Successful',
 143             'bounced' => 'Bounced',
 144           ),
 145         ),
 146       ),
 147       'grouping' => 'mailing-fields',
 148     );
 149
 150     $this->_columns['civicrm_mailing_event_unsubscribe'] = array(
 151       'dao' => 'CRM_Mailing_Event_DAO_Unsubscribe',
 152       'fields' =>
 153       array(
 154         'unsubscribe_id' =>
 155         array(
 156           'name' => 'id',
 157           'title' => ts('Unsubscribe'),
 158           'default' => TRUE,
 159         ),
 160         'optout_id' =>
 161         array(
 162           'name' => 'id',
 163           'title' => ts('Opt-out'),
 164           'default' => TRUE,
 165           'alias' => 'mailing_event_unsubscribe_civireport2',
 166         ),
 167       ),
 168       'filters' =>
 169       array(
 170         'is_unsubscribed' =>
 171         array(
 172           'name' => 'id',
 173           'title' => ts('Unsubscribed'),
 174           'type' => CRM_Utils_Type::T_INT,
 175           'operatorType' => CRM_Report_Form::OP_SELECT,
 176           'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
 177           'clause' => 'mailing_event_unsubscribe_civireport.id IS NULL',
 178         ),
 179         'is_optout' =>
 180         array(
 181           'title' => ts('Opted-out'),
 182           'type' => CRM_Utils_Type::T_INT,
 183           'operatorType' => CRM_Report_Form::OP_SELECT,
 184           'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
 185           'clause' => 'mailing_event_unsubscribe_civireport2.id IS NULL',
 186         ),
 187       ),
 188       'grouping' => 'mailing-fields',
 189     );
 190
 191     $this->_columns['civicrm_mailing_event_reply'] = array(
 192       'dao' => 'CRM_Mailing_Event_DAO_Reply',
 193       'fields' =>
 194       array(
 195         'reply_id' =>
 196         array(
 197           'name' => 'id',
 198           'title' => ts('Reply'),
 199         ),
 200       ),
 201       'filters' =>
 202       array(
 203         'is_replied' =>
 204         array(
 205           'name' => 'id',
 206           'title' => ts('Replied'),
 207           'type' => CRM_Utils_Type::T_INT,
 208           'operatorType' => CRM_Report_Form::OP_SELECT,
 209           'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
 210           'clause' => 'mailing_event_reply_civireport.id IS NULL',
 211         ),
 212       ),
 213       'grouping' => 'mailing-fields',
 214     );
 215
 216     $this->_columns['civicrm_mailing_event_forward'] = array(
 217       'dao' => 'CRM_Mailing_Event_DAO_Forward',
 218       'fields' =>
 219       array(
 220         'forward_id' =>
 221         array(
 222           'name' => 'id',
 223           'title' => ts('Forwarded to Email'),
 224         ),
 225       ),
 226       'filters' =>
 227       array(
 228         'is_forwarded' =>
 229         array(
 230           'name' => 'id',
 231           'title' => ts('Forwarded'),
 232           'type' => CRM_Utils_Type::T_INT,
 233           'operatorType' => CRM_Report_Form::OP_SELECT,
 234           'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
 235           'clause' => 'mailing_event_forward_civireport.id IS NULL',
 236         ),
 237       ),
 238       'grouping' => 'mailing-fields',
 239     );
 240
 241     $this->_columns['civicrm_email'] = array(
 242       'dao' => 'CRM_Core_DAO_Email',
 243       'fields' =>
 244       array(
 245         'email' =>
 246         array(
 247           'title' => ts('Email'),
 248           'required' => TRUE,
 249         ),
 250       ),
 251       'grouping' => 'contact-fields',
 252     );
 253
 254     $this->_columns['civicrm_phone'] = array(
 255       'dao' => 'CRM_Core_DAO_Phone',
 256       'fields' => array('phone' => NULL),
 257       'grouping' => 'contact-fields',
 258     );
 259
 260     $this->_columns['civicrm_group'] = array(
 261       'dao' => 'CRM_Contact_DAO_Group',
 262       'alias' => 'cgroup',
 263       'filters' =>
 264       array(
 265         'gid' =>
 266         array(
 267           'name' => 'group_id',
 268           'title' => ts('Group'),
 269           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 270           'group' => TRUE,
 271           'options' => CRM_Core_PseudoConstant::group(),
 272         ),
 273       ),
 274     );
 275
 276     parent::__construct();
 277   }
 278
 279   function select() {
 280     $select = $columns = array();
 281     foreach ($this->_columns as $tableName => $table) {
 282       if (array_key_exists('fields', $table)) {
 283         foreach ($table['fields'] as $fieldName => $field) {
 284           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 285             if (in_array($fieldName, array(
 286               'unsubscribe_id', 'optout_id', 'forward_id', 'reply_id'))) {
 287               $select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
 288               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 289               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 290               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 291               unset($this->_columns[$tableName]['fields'][$fieldName]);
 292               $columns[$tableName][$fieldName] = $field;
 293             }
 294             elseif ($fieldName == 'delivery_id') {
 295               $select[] = "IF(mailing_event_delivered_civireport.id IS NOT NULL, 'Successful', IF(mailing_event_bounce_civireport.id IS NOT NULL, 'Bounced ', 'Unknown')) as {$tableName}_{$fieldName}";
 296               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 297               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 298               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 299               unset($this->_columns[$tableName]['fields'][$fieldName]);
 300               $columns[$tableName][$fieldName] = $field;
 301             }
 302           }
 303         }
 304       }
 305     }
 306
 307     parent::select();
 308     if (!empty($select)) {
 309       $this->_select .= ', ' . implode(', ', $select) . " ";
 310     }
 311
 312     // put the fields that were unset, back in place
 313     foreach ($columns as $tableName => $table) {
 314       foreach ($table as $fieldName => $fields) {
 315         $this->_columns[$tableName]['fields'][$fieldName] = $fields;
 316       }
 317     }
 318
 319     // simple sort
 320     ksort($this->_columnHeaders);
 321   }
 322
 323   function from() {
 324     $this->_from = "
 325         FROM civicrm_contact {$this->_aliases['civicrm_contact']}";
 326
 327     $this->_from .= "
 328         INNER JOIN civicrm_mailing_event_queue
 329           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 330         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 331           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id";
 332
 333     if (array_key_exists('delivery_id', $this->_params['fields'])) {
 334       $this->_from .= "
 335                 LEFT JOIN  civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']}
 336                     ON  {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = civicrm_mailing_event_queue.id
 337         LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 338           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id";
 339       if (CRM_Utils_Array::value('delivery_status_value', $this->_params) == 'bounced') {
 340         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_bounce']}.id IS NOT NULL";
 341       }
 342       elseif (CRM_Utils_Array::value('delivery_status_value', $this->_params) == 'successful') {
 343         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_delivered']}.id IS NOT NULL";
 344       }
 345     }
 346     else {
 347       unset($this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']);
 348     }
 349
 350     if (array_key_exists('reply_id', $this->_params['fields']) ||
 351       is_numeric(CRM_Utils_Array::value('is_replied_value', $this->_params))
 352     ) {
 353       if (CRM_Utils_Array::value('is_replied_value', $this->_params) == 1) {
 354         $joinType = 'INNER';
 355         $this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']['clause'] = '(1)';
 356       }
 357       else {
 358         $joinType = 'LEFT';
 359       }
 360       $this->_from .= "
 361                 {$joinType} JOIN  civicrm_mailing_event_reply {$this->_aliases['civicrm_mailing_event_reply']}
 362                     ON  {$this->_aliases['civicrm_mailing_event_reply']}.event_queue_id = civicrm_mailing_event_queue.id";
 363     }
 364     else {
 365       unset($this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']);
 366     }
 367
 368     if (array_key_exists('unsubscribe_id', $this->_params['fields']) ||
 369       is_numeric(CRM_Utils_Array::value('is_unsubscribed_value', $this->_params))
 370     ) {
 371       if (CRM_Utils_Array::value('is_unsubscribed_value', $this->_params) == 1) {
 372         $joinType = 'INNER';
 373         $this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']['clause'] = '(1)';
 374       }
 375       else {
 376         $joinType = 'LEFT';
 377       }
 378       $this->_from .= "
 379                 {$joinType} JOIN  civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}
 380                     ON  {$this->_aliases['civicrm_mailing_event_unsubscribe']}.event_queue_id = civicrm_mailing_event_queue.id
 381                         AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}.org_unsubscribe = 0";
 382     }
 383     else {
 384       unset($this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']);
 385     }
 386
 387     if (array_key_exists('optout_id', $this->_params['fields']) ||
 388       is_numeric(CRM_Utils_Array::value('is_optout_value', $this->_params))
 389     ) {
 390       if (CRM_Utils_Array::value('is_optout_value', $this->_params) == 1) {
 391         $joinType = 'INNER';
 392         $this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_optout']['clause'] = '(1)';
 393       }
 394       else {
 395         $joinType = 'LEFT';
 396       }
 397       $this->_from .= "
 398                 {$joinType} JOIN  civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}2
 399                     ON  {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.event_queue_id = civicrm_mailing_event_queue.id
 400                         AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.org_unsubscribe = 1";
 401     }
 402     else {
 403       unset($this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_optout']);
 404     }
 405
 406     if (array_key_exists('forward_id', $this->_params['fields']) ||
 407       is_numeric(CRM_Utils_Array::value('is_forwarded_value', $this->_params))
 408     ) {
 409       if (CRM_Utils_Array::value('is_forwarded_value', $this->_params) == 1) {
 410         $joinType = 'INNER';
 411         $this->_columns['civicrm_mailing_event_forward']['filters']['is_forwarded']['clause'] = '(1)';
 412       }
 413       else {
 414         $joinType = 'LEFT';
 415       }
 416       $this->_from .= "
 417                 {$joinType} JOIN  civicrm_mailing_event_forward {$this->_aliases['civicrm_mailing_event_forward']}
 418                     ON  {$this->_aliases['civicrm_mailing_event_forward']}.event_queue_id = civicrm_mailing_event_queue.id";
 419     }
 420     else {
 421       unset($this->_columns['civicrm_mailing_event_forward']['filters']['is_forwarded']);
 422     }
 423
 424     $this->_from .= "
 425         INNER JOIN civicrm_mailing_job
 426           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 427         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 428           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 429           AND civicrm_mailing_job.is_test = 0";
 430
 431     if ($this->_phoneField) {
 432       $this->_from .= "
 433             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 434                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 435                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 436     }
 437   }
 438
 439   function where() {
 440     parent::where();
 441     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 442   }
 443
 444   function mailingList() {
 445
 446     $data    = array();
 447     $mailing = new CRM_Mailing_BAO_Mailing();
 448     $query   = "SELECT name FROM civicrm_mailing ";
 449     $mailing->query($query);
 450
 451     while ($mailing->fetch()) {
 452       $data[mysql_real_escape_string($mailing->name)] = $mailing->name;
 453     }
 454
 455     return $data;
 456   }
 457
 458   function alterDisplay(&$rows) {
 459     // custom code to alter rows
 460     $entryFound = FALSE;
 461     foreach ($rows as $rowNum => $row) {
 462       // make count columns point to detail report
 463       // convert display name to links
 464       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 465         array_key_exists('civicrm_contact_id', $row)
 466       ) {
 467         $url = CRM_Utils_System::url('civicrm/contact/view',
 468           'reset=1&cid=' . $row['civicrm_contact_id'],
 469           $this->_absoluteUrl
 470         );
 471         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 472         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 473         $entryFound = TRUE;
 474       }
 475
 476       // skip looking further in rows, if first row itself doesn't
 477       // have the column we need
 478       if (!$entryFound) {
 479         break;
 480       }
 481     }
 482   }
 483 }
 484