CRM/Report/Form/Mailing/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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-2016
  32  */
  33 class CRM_Report_Form_Mailing_Detail extends CRM_Report_Form {
  34
  35   protected $_customGroupExtends = array(
  36     'Contact',
  37     'Individual',
  38     'Household',
  39     'Organization',
  40   );
  41
  42   protected $_exposeContactID = FALSE;
  43
  44   /**
  45    * This report has not been optimised for group filtering.
  46    *
  47    * The functionality for group filtering has been improved but not
  48    * all reports have been adjusted to take care of it. This report has not
  49    * and will run an inefficient query until fixed.
  50    *
  51    * CRM-19170
  52    *
  53    * @var bool
  54    */
  55   protected $groupFilterNotOptimised = TRUE;
  56
  57   /**
  58    * Class constructor.
  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           'name' => 'id',
  68           'title' => ts('Contact ID'),
  69           'required' => TRUE,
  70           'no_display' => TRUE,
  71         ),
  72         'sort_name' => array(
  73           'title' => ts('Contact Name'),
  74           'required' => TRUE,
  75         ),
  76       ),
  77       'filters' => array(
  78         'sort_name' => array(
  79           'title' => ts('Contact Name'),
  80         ),
  81         'id' => array(
  82           'title' => ts('Contact ID'),
  83           'no_display' => TRUE,
  84         ),
  85       ),
  86       'order_bys' => array(
  87         'sort_name' => array(
  88           'title' => ts('Contact Name'),
  89           'default' => TRUE,
  90           'default_order' => 'ASC',
  91         ),
  92       ),
  93       'grouping' => 'contact-fields',
  94     );
  95
  96     $this->_columns['civicrm_mailing'] = array(
  97       'dao' => 'CRM_Mailing_DAO_Mailing',
  98       'fields' => array(
  99         'mailing_name' => array(
 100           'name' => 'name',
 101           'title' => ts('Mailing'),
 102           'default' => TRUE,
 103         ),
 104       ),
 105       'filters' => array(
 106         'mailing_id' => array(
 107           'name' => 'id',
 108           'title' => ts('Mailing'),
 109           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 110           'type' => CRM_Utils_Type::T_INT,
 111           'options' => CRM_Mailing_BAO_Mailing::getMailingsList(),
 112         ),
 113       ),
 114       'order_bys' => array(
 115         'mailing_name' => array(
 116           'name' => 'name',
 117           'title' => ts('Mailing'),
 118         ),
 119       ),
 120       'grouping' => 'mailing-fields',
 121     );
 122
 123     // adding dao just to have alias
 124     $this->_columns['civicrm_mailing_event_bounce'] = array(
 125       'dao' => 'CRM_Mailing_Event_DAO_Bounce',
 126     );
 127
 128     $this->_columns['civicrm_mailing_event_delivered'] = array(
 129       'dao' => 'CRM_Mailing_Event_DAO_Delivered',
 130       'fields' => array(
 131         'delivery_id' => array(
 132           'name' => 'id',
 133           'title' => ts('Delivery Status'),
 134           'default' => TRUE,
 135         ),
 136       ),
 137       'filters' => array(
 138         'delivery_status' => array(
 139           'name' => 'delivery_status',
 140           'title' => ts('Delivery Status'),
 141           'operatorType' => CRM_Report_Form::OP_SELECT,
 142           'type' => CRM_Utils_Type::T_STRING,
 143           'options' => array(
 144             '' => 'Any',
 145             'successful' => 'Successful',
 146             'bounced' => 'Bounced',
 147           ),
 148         ),
 149       ),
 150       'grouping' => 'mailing-fields',
 151     );
 152
 153     $this->_columns['civicrm_mailing_event_unsubscribe'] = array(
 154       'dao' => 'CRM_Mailing_Event_DAO_Unsubscribe',
 155       'fields' => array(
 156         'unsubscribe_id' => array(
 157           'name' => 'id',
 158           'title' => ts('Unsubscribe'),
 159           'default' => TRUE,
 160         ),
 161         'optout_id' => array(
 162           'name' => 'id',
 163           'title' => ts('Opt-out'),
 164           'default' => TRUE,
 165           'alias' => 'mailing_event_unsubscribe_civireport2',
 166         ),
 167       ),
 168       'filters' => array(
 169         'is_unsubscribed' => array(
 170           'name' => 'id',
 171           'title' => ts('Unsubscribed'),
 172           'type' => CRM_Utils_Type::T_INT,
 173           'operatorType' => CRM_Report_Form::OP_SELECT,
 174           'options' => array(
 175             '' => ts('Any'),
 176             '0' => ts('No'),
 177             '1' => ts('Yes'),
 178           ),
 179           'clause' => 'mailing_event_unsubscribe_civireport.id IS NULL',
 180         ),
 181         'is_optout' => array(
 182           'title' => ts('Opted-out'),
 183           'type' => CRM_Utils_Type::T_INT,
 184           'operatorType' => CRM_Report_Form::OP_SELECT,
 185           'options' => array(
 186             '' => ts('Any'),
 187             '0' => ts('No'),
 188             '1' => ts('Yes'),
 189           ),
 190           'clause' => 'mailing_event_unsubscribe_civireport2.id IS NULL',
 191         ),
 192       ),
 193       'grouping' => 'mailing-fields',
 194     );
 195
 196     $this->_columns['civicrm_mailing_event_reply'] = array(
 197       'dao' => 'CRM_Mailing_Event_DAO_Reply',
 198       'fields' => array(
 199         'reply_id' => array(
 200           'name' => 'id',
 201           'title' => ts('Reply'),
 202         ),
 203       ),
 204       'filters' => array(
 205         'is_replied' => array(
 206           'name' => 'id',
 207           'title' => ts('Replied'),
 208           'type' => CRM_Utils_Type::T_INT,
 209           'operatorType' => CRM_Report_Form::OP_SELECT,
 210           'options' => array(
 211             '' => ts('Any'),
 212             '0' => ts('No'),
 213             '1' => ts('Yes'),
 214           ),
 215           'clause' => 'mailing_event_reply_civireport.id IS NULL',
 216         ),
 217       ),
 218       'grouping' => 'mailing-fields',
 219     );
 220
 221     $this->_columns['civicrm_mailing_event_forward'] = array(
 222       'dao' => 'CRM_Mailing_Event_DAO_Forward',
 223       'fields' => array(
 224         'forward_id' => array(
 225           'name' => 'id',
 226           'title' => ts('Forwarded to Email'),
 227         ),
 228       ),
 229       'filters' => array(
 230         'is_forwarded' => array(
 231           'name' => 'id',
 232           'title' => ts('Forwarded'),
 233           'type' => CRM_Utils_Type::T_INT,
 234           'operatorType' => CRM_Report_Form::OP_SELECT,
 235           'options' => array(
 236             '' => ts('Any'),
 237             '0' => ts('No'),
 238             '1' => ts('Yes'),
 239           ),
 240           'clause' => 'mailing_event_forward_civireport.id IS NULL',
 241         ),
 242       ),
 243       'grouping' => 'mailing-fields',
 244     );
 245
 246     $this->_columns['civicrm_email'] = array(
 247       'dao' => 'CRM_Core_DAO_Email',
 248       'fields' => array(
 249         'email' => array(
 250           'title' => ts('Email'),
 251         ),
 252       ),
 253       'grouping' => 'contact-fields',
 254     );
 255
 256     $this->_columns['civicrm_phone'] = array(
 257       'dao' => 'CRM_Core_DAO_Phone',
 258       'fields' => array('phone' => NULL),
 259       'grouping' => 'contact-fields',
 260     );
 261
 262     $this->_groupFilter = TRUE;
 263     $this->_tagFilter = TRUE;
 264
 265     parent::__construct();
 266   }
 267
 268   public function select() {
 269     $select = $columns = array();
 270     foreach ($this->_columns as $tableName => $table) {
 271       if (array_key_exists('fields', $table)) {
 272         foreach ($table['fields'] as $fieldName => $field) {
 273           if (!empty($field['required']) ||
 274             !empty($this->_params['fields'][$fieldName])
 275           ) {
 276             if (in_array($fieldName, array(
 277               'unsubscribe_id',
 278               'optout_id',
 279               'forward_id',
 280               'reply_id',
 281             ))) {
 282               $select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
 283               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 284               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 285               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 286               unset($this->_columns[$tableName]['fields'][$fieldName]);
 287               $columns[$tableName][$fieldName] = $field;
 288             }
 289             elseif ($fieldName == 'delivery_id') {
 290               $select[] = "IF(mailing_event_bounce_civireport.id IS NOT NULL, 'Bounced', IF(mailing_event_delivered_civireport.id IS NOT NULL, 'Successful', 'Unknown')) as {$tableName}_{$fieldName}";
 291               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 292               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 293               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 294               unset($this->_columns[$tableName]['fields'][$fieldName]);
 295               $columns[$tableName][$fieldName] = $field;
 296             }
 297           }
 298         }
 299       }
 300     }
 301
 302     parent::select();
 303     if (!empty($select)) {
 304       $this->_select .= ', ' . implode(', ', $select) . " ";
 305     }
 306
 307     // put the fields that were unset, back in place
 308     foreach ($columns as $tableName => $table) {
 309       foreach ($table as $fieldName => $fields) {
 310         $this->_columns[$tableName]['fields'][$fieldName] = $fields;
 311       }
 312     }
 313
 314     // simple sort
 315     ksort($this->_columnHeaders);
 316   }
 317
 318   public function from() {
 319     $this->_from = "
 320         FROM civicrm_contact {$this->_aliases['civicrm_contact']}";
 321
 322     $this->_from .= "
 323         INNER JOIN civicrm_mailing_event_queue
 324           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 325         LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 326           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id";
 327
 328     if (array_key_exists('delivery_id', $this->_params['fields'])) {
 329       $this->_from .= "
 330                 LEFT JOIN  civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']}
 331                     ON  {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = civicrm_mailing_event_queue.id
 332         LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 333           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id";
 334       if (CRM_Utils_Array::value('delivery_status_value', $this->_params) ==
 335         'bounced'
 336       ) {
 337         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_bounce']}.id IS NOT NULL";
 338       }
 339       elseif (CRM_Utils_Array::value('delivery_status_value', $this->_params) ==
 340         'successful'
 341       ) {
 342         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_delivered']}.id IS NOT NULL AND {$this->_aliases['civicrm_mailing_event_bounce']}.id IS NULL";
 343       }
 344     }
 345     else {
 346       unset($this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']);
 347     }
 348
 349     if (array_key_exists('reply_id', $this->_params['fields']) ||
 350       is_numeric(CRM_Utils_Array::value('is_replied_value', $this->_params))
 351     ) {
 352       if (CRM_Utils_Array::value('is_replied_value', $this->_params) == 1) {
 353         $joinType = 'INNER';
 354         $this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']['clause'] = '(1)';
 355       }
 356       else {
 357         $joinType = 'LEFT';
 358       }
 359       $this->_from .= "
 360                 {$joinType} JOIN  civicrm_mailing_event_reply {$this->_aliases['civicrm_mailing_event_reply']}
 361                     ON  {$this->_aliases['civicrm_mailing_event_reply']}.event_queue_id = civicrm_mailing_event_queue.id";
 362     }
 363     else {
 364       unset($this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']);
 365     }
 366
 367     if (array_key_exists('unsubscribe_id', $this->_params['fields']) ||
 368       is_numeric(CRM_Utils_Array::value('is_unsubscribed_value', $this->_params))
 369     ) {
 370       if (CRM_Utils_Array::value('is_unsubscribed_value', $this->_params) == 1
 371       ) {
 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   public function where() {
 440     parent::where();
 441     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 442   }
 443
 444   /**
 445    * @return array
 446    */
 447   public function mailingList() {
 448
 449     $data = array();
 450     $mailing = new CRM_Mailing_BAO_Mailing();
 451     $query = "SELECT name FROM civicrm_mailing ";
 452     $mailing->query($query);
 453
 454     while ($mailing->fetch()) {
 455       $data[CRM_Core_DAO::escapeString($mailing->name)] = $mailing->name;
 456     }
 457
 458     return $data;
 459   }
 460
 461   /**
 462    * Alter display of rows.
 463    *
 464    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 465    * such as rendering contacts as links.
 466    *
 467    * @param array $rows
 468    *   Rows generated by SQL, with an array for each row.
 469    */
 470   public function alterDisplay(&$rows) {
 471     $entryFound = FALSE;
 472     foreach ($rows as $rowNum => $row) {
 473
 474       // If the email address has been deleted
 475       if (array_key_exists('civicrm_email_email', $row)) {
 476         if (empty($rows[$rowNum]['civicrm_email_email'])) {
 477           $rows[$rowNum]['civicrm_email_email'] = '<del>Email address deleted</del>';
 478         }
 479         $entryFound = TRUE;
 480       }
 481
 482       // make count columns point to detail report
 483       // convert display name to links
 484       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 485         array_key_exists('civicrm_contact_id', $row)
 486       ) {
 487         $url = CRM_Utils_System::url('civicrm/contact/view',
 488           'reset=1&cid=' . $row['civicrm_contact_id'],
 489           $this->_absoluteUrl
 490         );
 491         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 492         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 493         $entryFound = TRUE;
 494       }
 495
 496       // skip looking further in rows, if first row itself doesn't
 497       // have the column we need
 498       if (!$entryFound) {
 499         break;
 500       }
 501     }
 502   }
 503
 504 }