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