CRM/Report/Form/Event/ParticipantListCount.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                       |
   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_Event_ParticipantListCount extends CRM_Report_Form_Event {
  37
  38   protected $_summary = NULL;
  39   protected $_groupFilter = TRUE;
  40   protected $_tagFilter = TRUE;
  41   protected $_customGroupExtends = array(
  42     'Participant',
  43     'Event',
  44   );
  45
  46   public $_drilldownReport = array('event/income' => 'Link to Detail Report');
  47   function __construct() {
  48     $this->_columns = array(
  49       'civicrm_contact' =>
  50       array(
  51         'dao' => 'CRM_Contact_DAO_Contact',
  52         'fields' =>
  53         array(
  54           'sort_name' =>
  55           array('title' => ts('Name'),
  56             'default' => TRUE,
  57             'no_repeat' => TRUE,
  58             'required' => TRUE,
  59           ),
  60           'id' =>
  61           array(
  62             'no_display' => TRUE,
  63             'required' => TRUE,
  64           ),
  65         ),
  66         'grouping' => 'contact-fields',
  67         'filters' => array(
  68           'sort_name' =>
  69           array('title' => ts('Participant Name'),
  70             'operator' => 'like',
  71           ),
  72         ),
  73       ),
  74       'civicrm_employer' =>
  75       array(
  76         'dao'       => 'CRM_Contact_DAO_Contact',
  77         'grouping'  => 'contact-fields',
  78         'fields'    => array(
  79           'id'           => array(
  80             'no_display' => true,
  81             'required'   => true,
  82           ),
  83           'organization_name' => array(
  84             'title'      => 'Employer',
  85             'default'    => TRUE,
  86             'no_repeat'  => TRUE,
  87           ),
  88         ),
  89       ),
  90       'civicrm_email' =>
  91       array(
  92         'dao' => 'CRM_Core_DAO_Email',
  93         'fields' => array(
  94           'email' =>
  95           array('title' => ts('Email'),
  96             'no_repeat' => TRUE,
  97           ),
  98         ),
  99         'grouping' => 'contact-fields',
 100         'filters' =>
 101         array(
 102           'email' =>
 103           array('title' => ts('Participant E-mail'),
 104             'operator' => 'like',
 105           ),
 106         ),
 107       ),
 108      'civicrm_phone' =>
 109      array(
 110        'dao' => 'CRM_Core_DAO_Phone',
 111        'grouping' => 'contact-fields',
 112        'fields' => array(
 113          'phone' => array(
 114            'title' => ts( 'Phone No' ),
 115            'default' => true,
 116          ),
 117        ),
 118      ),
 119      'civicrm_address' =>
 120       array(
 121         'dao' => 'CRM_Core_DAO_Address',
 122         'fields' =>
 123         array(
 124           'street_address' => NULL,
 125           'city' => NULL,
 126           'postal_code' => NULL,
 127           'state_province_id' =>
 128           array('title' => ts('State/Province'),
 129           ),
 130           'country_id' =>
 131           array('title' => ts('Country'),
 132           ),
 133         ),
 134         'grouping' => 'contact-fields',
 135       ),
 136       'civicrm_participant' =>
 137       array(
 138         'dao' => 'CRM_Event_DAO_Participant',
 139         'fields' =>
 140         array(
 141           'participant_id' =>
 142           array('title' => ts('Participant ID'),
 143             'default' => TRUE,
 144           ),
 145           'event_id' =>
 146           array('title' => ts('Event'),
 147             'type' => CRM_Utils_Type::T_STRING,
 148           ),
 149           'role_id' =>
 150           array('title' => ts('Role'),
 151             'default' => TRUE,
 152           ),
 153           'status_id' =>
 154           array('title' => ts('Status'),
 155             'default' => TRUE,
 156           ),
 157           'participant_register_date' =>
 158           array('title' => ts('Registration Date'),
 159           ),
 160         ),
 161         'grouping' => 'event-fields',
 162         'filters' =>
 163         array(
 164           'event_id' =>
 165           array(
 166             'name' => 'event_id',
 167             'title' => ts('Event'),
 168             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 169             'options' => $this->getEventFilterOptions(),
 170           ),
 171           'sid' =>
 172           array(
 173             'name' => 'status_id',
 174             'title' => ts('Participant Status'),
 175             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 176             'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'),
 177           ),
 178           'rid' =>
 179           array(
 180             'name' => 'role_id',
 181             'title' => ts('Participant Role'),
 182             'operatorType' => CRM_Report_Form::OP_MULTISELECT_SEPARATOR,
 183             'options' => CRM_Event_PseudoConstant::participantRole(),
 184           ),
 185           'participant_register_date' => array('title' => ts('Registration Date'),
 186             'operatorType' => CRM_Report_Form::OP_DATE,
 187           ),
 188         ),
 189         'group_bys' =>
 190         array(
 191           'event_id' =>
 192           array('title' => ts('Event'),
 193           ),
 194         ),
 195       ),
 196       'civicrm_event' =>
 197       array(
 198         'dao' => 'CRM_Event_DAO_Event',
 199         'fields' =>
 200         array(
 201           'event_type_id' =>
 202           array('title' => ts('Event Type'),
 203           ),
 204           'start_date' =>
 205           array('title' => ts('Event Start Date'),
 206           ),
 207           'end_date' =>
 208           array('title' => ts('Event End Date'),
 209           ),
 210         ),
 211         'grouping' => 'event-fields',
 212         'filters' => array(
 213           'eid' =>
 214           array(
 215             'name' => 'event_type_id',
 216             'title' => ts('Event Type'),
 217             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 218             'options' => CRM_Core_OptionGroup::values('event_type'),
 219           ),
 220           'event_start_date' =>
 221           array(
 222             'name' => 'start_date',
 223             'title' => ts('Event Start Date'),
 224             'operatorType' => CRM_Report_Form::OP_DATE,
 225           ),
 226           'event_end_date' =>
 227           array(
 228             'name' => 'end_date',
 229             'title' => ts('Event End Date'),
 230             'operatorType' => CRM_Report_Form::OP_DATE,
 231           ),
 232         ),
 233         'group_bys' =>
 234         array(
 235           'event_type_id' =>
 236           array('title' => ts('Event Type '),
 237           ),
 238         ),
 239       ),
 240       'civicrm_line_item' =>
 241       array(
 242         'dao' => 'CRM_Price_DAO_LineItem',
 243         'fields' =>
 244         array(
 245           'line_total' =>
 246           array('title' => ts('Income'),
 247             'default' => TRUE,
 248             'statistics' =>
 249             array('sum' => ts('Amount'),
 250               'avg' => ts('Average'),
 251             ),
 252           ),
 253           'participant_count' =>
 254           array('title' => ts('Count'),
 255             'default' => TRUE,
 256             'statistics' =>
 257             array('sum' => ts('Count'),
 258             ),
 259           ),
 260         ),
 261       ),
 262     );
 263
 264     $this->_options = array(
 265       'blank_column_begin' =>
 266       array('title' => ts('Blank column at the Begining'),
 267         'type' => 'checkbox',
 268       ),
 269       'blank_column_end' =>
 270       array('title' => ts('Blank column at the End'),
 271         'type' => 'select',
 272         'options' => array(
 273           '' => '-select-',
 274           1 => ts('One'),
 275           2 => ts('Two'),
 276           3 => ts('Three'),
 277         ),
 278       ),
 279     );
 280     parent::__construct();
 281   }
 282
 283   function preProcess() {
 284     parent::preProcess();
 285   }
 286
 287   //Add The statistics
 288   function statistics(&$rows) {
 289
 290     $statistics = parent::statistics($rows);
 291     $avg        = NULL;
 292     $select     = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
 293                   SUM( {$this->_aliases['civicrm_line_item']}.line_total )   as amount
 294             ";
 295     $sql = "{$select} {$this->_from} {$this->_where}";
 296     $dao = CRM_Core_DAO::executeQuery($sql);
 297     if ($dao->fetch()) {
 298
 299       if ($dao->count && $dao->amount) {
 300         $avg = $dao->amount / $dao->count;
 301       }
 302       $statistics['counts']['count'] = array(
 303         'value' => $dao->count,
 304         'title' => 'Total Participants',
 305         'type' => CRM_Utils_Type::T_INT,
 306       );
 307       $statistics['counts']['amount'] = array(
 308         'value' => $dao->amount,
 309         'title' => 'Total Income',
 310         'type' => CRM_Utils_Type::T_MONEY,
 311       );
 312       $statistics['counts']['avg    '] = array(
 313         'value' => $avg,
 314         'title' => 'Average',
 315         'type' => CRM_Utils_Type::T_MONEY,
 316       );
 317     }
 318
 319     return $statistics;
 320   }
 321
 322   function select() {
 323     $select = array();
 324     $this->_columnHeaders = array();
 325
 326     //add blank column at the Start
 327     if (array_key_exists('options', $this->_params) && !empty($this->_params['options']['blank_column_begin'])) {
 328       $select[] = " '' as blankColumnBegin";
 329       $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _';
 330     }
 331     foreach ($this->_columns as $tableName => $table) {
 332       if (array_key_exists('fields', $table)) {
 333         foreach ($table['fields'] as $fieldName => $field) {
 334           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 335             if (!empty($field['statistics'])) {
 336               foreach ($field['statistics'] as $stat => $label) {
 337                 switch (strtolower($stat)) {
 338                   case 'sum':
 339                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 340                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 341                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 342                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 343                     break;
 344                 }
 345               }
 346             }
 347             else {
 348               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 349               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 350               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 351             }
 352           }
 353         }
 354       }
 355     }
 356     //add blank column at the end
 357     if ($blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params)) {
 358       for ($i = 1; $i <= $blankcols; $i++) {
 359         $select[] = " '' as blankColumnEnd_{$i}";
 360         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 361       }
 362     }
 363     $this->_select = "SELECT " . implode(', ', $select) . " ";
 364   }
 365
 366   static function formRule($fields, $files, $self) {
 367     $errors = $grouping = array();
 368     return $errors;
 369   }
 370
 371   function from() {
 372     $this->_from = "
 373       FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 374          LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 375               ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND ({$this->_aliases['civicrm_event']}.is_template IS NULL OR {$this->_aliases['civicrm_event']}.is_template = 0)
 376          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 377               ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 378          {$this->_aclFrom}
 379          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
 380               ON ({$this->_aliases['civicrm_employer']}.id  = {$this->_aliases['civicrm_contact']}.employer_id  )
 381          LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 382               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 383                 {$this->_aliases['civicrm_address']}.is_primary = 1
 384          LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
 385               ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 386                 {$this->_aliases['civicrm_email']}.is_primary = 1)
 387          LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 388               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 389                 {$this->_aliases['civicrm_phone']}.is_primary = 1
 390          LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 391               ON {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant'";
 392   }
 393
 394   function storeWhereHavingClauseArray() {
 395     parent::storeWhereHavingClauseArray();
 396     $this->_whereClauses[] = "{$this->_aliases['civicrm_participant']}.is_test = 0";
 397   }
 398
 399   function groupBy() {
 400     $this->_groupBy = "";
 401     if (!empty($this->_params['group_bys']) &&
 402       is_array($this->_params['group_bys']) &&
 403       !empty($this->_params['group_bys'])
 404     ) {
 405       foreach ($this->_columns as $tableName => $table) {
 406         if (array_key_exists('group_bys', $table)) {
 407           foreach ($table['group_bys'] as $fieldName => $field) {
 408             if (!empty($this->_params['group_bys'][$fieldName])) {
 409               $this->_groupBy[] = $field['dbAlias'];
 410             }
 411           }
 412         }
 413       }
 414     }
 415
 416     if (!empty($this->_groupBy)) {
 417       $this->_groupBy = "ORDER BY " . implode(', ', $this->_groupBy) . ", {$this->_aliases['civicrm_contact']}.sort_name";
 418     }
 419     else {
 420       $this->_groupBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name";
 421     }
 422     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_participant']}.id " . $this->_groupBy;
 423   }
 424
 425   function postProcess() {
 426
 427     // get ready with post process params
 428     $this->beginPostProcess();
 429
 430     // get the acl clauses built before we assemble the query
 431     $this->buildACLClause($this->_aliases['civicrm_contact']);
 432     // build query
 433     $sql = $this->buildQuery(TRUE);
 434
 435     // build array of result based on column headers. This method also allows
 436     // modifying column headers before using it to build result set i.e $rows.
 437     $this->buildRows($sql, $rows);
 438
 439     // format result set.
 440     $this->formatDisplay($rows);
 441
 442     // assign variables to templates
 443     $this->doTemplateAssignment($rows);
 444
 445     // do print / pdf / instance stuff if needed
 446     $this->endPostProcess($rows);
 447   }
 448
 449   function alterDisplay(&$rows) {
 450
 451     $entryFound = FALSE;
 452     $eventType = CRM_Core_OptionGroup::values('event_type');
 453
 454     foreach ($rows as $rowNum => $row) {
 455
 456       // convert sort name to links
 457       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 458         array_key_exists('civicrm_contact_id', $row)
 459       ) {
 460         if ($value = $row['civicrm_contact_sort_name']) {
 461           $url = CRM_Utils_System::url("civicrm/contact/view",
 462             'reset=1&cid=' . $row['civicrm_contact_id'],
 463             $this->_absoluteUrl
 464           );
 465           $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 466           $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 467         }
 468         $entryFound = TRUE;
 469       }
 470
 471       // convert participant ID to links
 472       if (array_key_exists('civicrm_participant_participant_id', $row) &&
 473         array_key_exists('civicrm_contact_id', $row)
 474       ) {
 475         if ($value = $row['civicrm_participant_participant_id']) {
 476           $url = CRM_Utils_System::url("civicrm/contact/view/participant",
 477             'reset=1&id=' . $row['civicrm_participant_participant_id'] . '&cid=' . $row['civicrm_contact_id'] . '&action=view&context=participant',
 478             $this->_absoluteUrl
 479           );
 480           $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
 481           $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
 482         }
 483         $entryFound = TRUE;
 484       }
 485
 486       // convert event name to links
 487       if (array_key_exists('civicrm_participant_event_id', $row)) {
 488         if ($value = $row['civicrm_participant_event_id']) {
 489           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($value, FALSE);
 490           $url = CRM_Report_Utils_Report::getNextUrl('event/Income',
 491             'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
 492             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 493           );
 494           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 495           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 496         }
 497         $entryFound = TRUE;
 498       }
 499
 500       // handle event type id
 501       if (array_key_exists('civicrm_event_event_type_id', $row)) {
 502         if ($value = $row['civicrm_event_event_type_id']) {
 503           $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 504         }
 505         $entryFound = TRUE;
 506       }
 507
 508       // handle participant status id
 509       if (array_key_exists('civicrm_participant_status_id', $row)) {
 510         if ($value = $row['civicrm_participant_status_id']) {
 511           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($value, FALSE);
 512         }
 513         $entryFound = TRUE;
 514       }
 515
 516       // handle participant role id
 517       if (array_key_exists('civicrm_participant_role_id', $row)) {
 518         if ($value = $row['civicrm_participant_role_id']) {
 519           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 520           $value = array();
 521           foreach ($roles as $role) {
 522             $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 523           }
 524           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
 525         }
 526         $entryFound = TRUE;
 527       }
 528
 529
 530       // skip looking further in rows, if first row itself doesn't
 531       // have the column we need
 532       if (!$entryFound) {
 533         break;
 534       }
 535     }
 536   }
 537 }
 538