CRM/Report/Form/Mailing/Detail.php

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