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