CRM/Report/Form/Mailing/Detail.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.3                                                |
   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_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 (CRM_Utils_Array::value('required', $field) ||
 285             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 286           ) {
 287             if (in_array($fieldName, array(
 288               'unsubscribe_id', 'optout_id', 'forward_id', 'reply_id'))) {
 289               $select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
 290               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 291               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 292               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 293               unset($this->_columns[$tableName]['fields'][$fieldName]);
 294               $columns[$tableName][$fieldName] = $field;
 295             }
 296             elseif ($fieldName == 'delivery_id') {
 297               $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}";
 298               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 299               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 300               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 301               unset($this->_columns[$tableName]['fields'][$fieldName]);
 302               $columns[$tableName][$fieldName] = $field;
 303             }
 304           }
 305         }
 306       }
 307     }
 308
 309     parent::select();
 310     if (!empty($select)) {
 311       $this->_select .= ', ' . implode(', ', $select) . " ";
 312     }
 313
 314     // put the fields that were unset, back in place
 315     foreach ($columns as $tableName => $table) {
 316       foreach ($table as $fieldName => $fields) {
 317         $this->_columns[$tableName]['fields'][$fieldName] = $fields;
 318       }
 319     }
 320
 321     // simple sort
 322     ksort($this->_columnHeaders);
 323   }
 324
 325   function from() {
 326     $this->_from = "
 327         FROM civicrm_contact {$this->_aliases['civicrm_contact']}";
 328
 329     $this->_from .= "
 330         INNER JOIN civicrm_mailing_event_queue
 331           ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
 332         INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
 333           ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id";
 334
 335     if (array_key_exists('delivery_id', $this->_params['fields'])) {
 336       $this->_from .= "
 337                 LEFT JOIN  civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']}
 338                     ON  {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = civicrm_mailing_event_queue.id
 339         LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
 340           ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id";
 341       if (CRM_Utils_Array::value('delivery_status_value', $this->_params) == 'bounced') {
 342         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_bounce']}.id IS NOT NULL";
 343       }
 344       elseif (CRM_Utils_Array::value('delivery_status_value', $this->_params) == 'successful') {
 345         $this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_delivered']}.id IS NOT NULL";
 346       }
 347     }
 348     else {
 349       unset($this->_columns['civicrm_mailing_event_delivered']['filters']['delivery_status']);
 350     }
 351
 352     if (array_key_exists('reply_id', $this->_params['fields']) ||
 353       is_numeric(CRM_Utils_Array::value('is_replied_value', $this->_params))
 354     ) {
 355       if (CRM_Utils_Array::value('is_replied_value', $this->_params) == 1) {
 356         $joinType = 'INNER';
 357         $this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']['clause'] = '(1)';
 358       }
 359       else {
 360         $joinType = 'LEFT';
 361       }
 362       $this->_from .= "
 363                 {$joinType} JOIN  civicrm_mailing_event_reply {$this->_aliases['civicrm_mailing_event_reply']}
 364                     ON  {$this->_aliases['civicrm_mailing_event_reply']}.event_queue_id = civicrm_mailing_event_queue.id";
 365     }
 366     else {
 367       unset($this->_columns['civicrm_mailing_event_reply']['filters']['is_replied']);
 368     }
 369
 370     if (array_key_exists('unsubscribe_id', $this->_params['fields']) ||
 371       is_numeric(CRM_Utils_Array::value('is_unsubscribed_value', $this->_params))
 372     ) {
 373       if (CRM_Utils_Array::value('is_unsubscribed_value', $this->_params) == 1) {
 374         $joinType = 'INNER';
 375         $this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']['clause'] = '(1)';
 376       }
 377       else {
 378         $joinType = 'LEFT';
 379       }
 380       $this->_from .= "
 381                 {$joinType} JOIN  civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}
 382                     ON  {$this->_aliases['civicrm_mailing_event_unsubscribe']}.event_queue_id = civicrm_mailing_event_queue.id
 383                         AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}.org_unsubscribe = 0";
 384     }
 385     else {
 386       unset($this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']);
 387     }
 388
 389     if (array_key_exists('optout_id', $this->_params['fields']) ||
 390       is_numeric(CRM_Utils_Array::value('is_optout_value', $this->_params))
 391     ) {
 392       if (CRM_Utils_Array::value('is_optout_value', $this->_params) == 1) {
 393         $joinType = 'INNER';
 394         $this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_optout']['clause'] = '(1)';
 395       }
 396       else {
 397         $joinType = 'LEFT';
 398       }
 399       $this->_from .= "
 400                 {$joinType} JOIN  civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}2
 401                     ON  {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.event_queue_id = civicrm_mailing_event_queue.id
 402                         AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.org_unsubscribe = 1";
 403     }
 404     else {
 405       unset($this->_columns['civicrm_mailing_event_unsubscribe']['filters']['is_optout']);
 406     }
 407
 408     if (array_key_exists('forward_id', $this->_params['fields']) ||
 409       is_numeric(CRM_Utils_Array::value('is_forwarded_value', $this->_params))
 410     ) {
 411       if (CRM_Utils_Array::value('is_forwarded_value', $this->_params) == 1) {
 412         $joinType = 'INNER';
 413         $this->_columns['civicrm_mailing_event_forward']['filters']['is_forwarded']['clause'] = '(1)';
 414       }
 415       else {
 416         $joinType = 'LEFT';
 417       }
 418       $this->_from .= "
 419                 {$joinType} JOIN  civicrm_mailing_event_forward {$this->_aliases['civicrm_mailing_event_forward']}
 420                     ON  {$this->_aliases['civicrm_mailing_event_forward']}.event_queue_id = civicrm_mailing_event_queue.id";
 421     }
 422     else {
 423       unset($this->_columns['civicrm_mailing_event_forward']['filters']['is_forwarded']);
 424     }
 425
 426     $this->_from .= "
 427         INNER JOIN civicrm_mailing_job
 428           ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
 429         INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
 430           ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
 431           AND civicrm_mailing_job.is_test = 0";
 432
 433     if ($this->_phoneField) {
 434       $this->_from .= "
 435             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 436                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 437                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 438     }
 439   }
 440
 441   function where() {
 442     parent::where();
 443     $this->_where .= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
 444   }
 445
 446   function mailingList() {
 447
 448     $data    = array();
 449     $mailing = new CRM_Mailing_BAO_Mailing();
 450     $query   = "SELECT name FROM civicrm_mailing ";
 451     $mailing->query($query);
 452
 453     while ($mailing->fetch()) {
 454       $data[mysql_real_escape_string($mailing->name)] = $mailing->name;
 455     }
 456
 457     return $data;
 458   }
 459
 460   function alterDisplay(&$rows) {
 461     // custom code to alter rows
 462     $entryFound = FALSE;
 463     foreach ($rows as $rowNum => $row) {
 464       // make count columns point to detail report
 465       // convert display name to links
 466       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 467         array_key_exists('civicrm_contact_id', $row)
 468       ) {
 469         $url = CRM_Utils_System::url('civicrm/contact/view',
 470           'reset=1&cid=' . $row['civicrm_contact_id'],
 471           $this->_absoluteUrl
 472         );
 473         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 474         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 475         $entryFound = TRUE;
 476       }
 477
 478       // skip looking further in rows, if first row itself doesn't
 479       // have the column we need
 480       if (!$entryFound) {
 481         break;
 482       }
 483     }
 484   }
 485 }
 486