CRM/Report/Form/Mailing/Detail.php

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