CRM/Report/Form/Event/ParticipantListCount.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Event_ParticipantListCount extends CRM_Report_Form {
  18
  19   protected $_summary = NULL;
  20   protected $_groupFilter = TRUE;
  21   protected $_tagFilter = TRUE;
  22   protected $_customGroupExtends = [
  23     'Participant',
  24     'Event',
  25   ];
  26   /**
  27    * This report has not been optimised for group filtering.
  28    *
  29    * The functionality for group filtering has been improved but not
  30    * all reports have been adjusted to take care of it. This report has not
  31    * and will run an inefficient query until fixed.
  32    *
  33    * @var bool
  34    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  35    */
  36   protected $groupFilterNotOptimised = TRUE;
  37
  38   public $_drilldownReport = ['event/income' => 'Link to Detail Report'];
  39
  40   /**
  41    * Class constructor.
  42    */
  43   public function __construct() {
  44     $this->_columns = [
  45       'civicrm_contact' => [
  46         'dao' => 'CRM_Contact_DAO_Contact',
  47         'fields' => [
  48           'sort_name' => [
  49             'title' => ts('Name'),
  50             'default' => TRUE,
  51             'no_repeat' => TRUE,
  52             'required' => TRUE,
  53           ],
  54           'first_name' => [
  55             'title' => ts('First Name'),
  56           ],
  57           'middle_name' => [
  58             'title' => ts('Middle Name'),
  59           ],
  60           'last_name' => [
  61             'title' => ts('Last Name'),
  62           ],
  63           'id' => [
  64             'no_display' => TRUE,
  65             'required' => TRUE,
  66           ],
  67           'gender_id' => [
  68             'title' => ts('Gender'),
  69           ],
  70           'birth_date' => [
  71             'title' => ts('Birth Date'),
  72           ],
  73           'age' => [
  74             'title' => ts('Age'),
  75             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  76           ],
  77           'contact_type' => [
  78             'title' => ts('Contact Type'),
  79           ],
  80           'contact_sub_type' => [
  81             'title' => ts('Contact Subtype'),
  82           ],
  83         ],
  84         'filters' => [
  85           'sort_name' => [
  86             'title' => ts('Participant Name'),
  87             'operator' => 'like',
  88           ],
  89           'id' => [
  90             'title' => ts('Contact ID'),
  91             'no_display' => TRUE,
  92           ],
  93           'gender_id' => [
  94             'title' => ts('Gender'),
  95             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  96             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
  97           ],
  98           'birth_date' => [
  99             'title' => ts('Birth Date'),
 100             'operatorType' => CRM_Report_Form::OP_DATE,
 101           ],
 102           'contact_type' => [
 103             'title' => ts('Contact Type'),
 104           ],
 105           'contact_sub_type' => [
 106             'title' => ts('Contact Subtype'),
 107           ],
 108         ],
 109         'grouping' => 'contact-fields',
 110         'order_bys' => [
 111           'sort_name' => [
 112             'title' => ts('Last Name, First Name'),
 113             'default' => '1',
 114             'default_weight' => '0',
 115             'default_order' => 'ASC',
 116           ],
 117           'first_name' => [
 118             'name' => 'first_name',
 119             'title' => ts('First Name'),
 120           ],
 121           'gender_id' => [
 122             'name' => 'gender_id',
 123             'title' => ts('Gender'),
 124           ],
 125           'birth_date' => [
 126             'name' => 'birth_date',
 127             'title' => ts('Birth Date'),
 128           ],
 129           'contact_type' => [
 130             'title' => ts('Contact Type'),
 131           ],
 132           'contact_sub_type' => [
 133             'title' => ts('Contact Subtype'),
 134           ],
 135         ],
 136       ],
 137       'civicrm_employer' => [
 138         'dao' => 'CRM_Contact_DAO_Contact',
 139         'grouping' => 'contact-fields',
 140         'fields' => [
 141           'id' => [
 142             'no_display' => TRUE,
 143             'required' => TRUE,
 144           ],
 145           'organization_name' => [
 146             'title' => ts('Employer'),
 147             'default' => TRUE,
 148             'no_repeat' => TRUE,
 149           ],
 150         ],
 151       ],
 152       'civicrm_email' => [
 153         'dao' => 'CRM_Core_DAO_Email',
 154         'fields' => [
 155           'email' => [
 156             'title' => ts('Email'),
 157             'no_repeat' => TRUE,
 158           ],
 159         ],
 160         'grouping' => 'contact-fields',
 161         'filters' => [
 162           'email' => [
 163             'title' => ts('Participant E-mail'),
 164             'operator' => 'like',
 165           ],
 166         ],
 167       ],
 168       'civicrm_phone' => [
 169         'dao' => 'CRM_Core_DAO_Phone',
 170         'grouping' => 'contact-fields',
 171         'fields' => [
 172           'phone' => [
 173             'title' => ts('Phone No'),
 174             'default' => TRUE,
 175           ],
 176         ],
 177       ],
 178       'civicrm_address' => [
 179         'dao' => 'CRM_Core_DAO_Address',
 180         'fields' => [
 181           'street_address' => NULL,
 182           'city' => NULL,
 183           'postal_code' => NULL,
 184           'state_province_id' => [
 185             'title' => ts('State/Province'),
 186           ],
 187           'country_id' => [
 188             'title' => ts('Country'),
 189           ],
 190         ],
 191         'grouping' => 'contact-fields',
 192       ],
 193       'civicrm_participant' => [
 194         'dao' => 'CRM_Event_DAO_Participant',
 195         'fields' => [
 196           'participant_id' => [
 197             'title' => ts('Participant ID'),
 198             'default' => TRUE,
 199           ],
 200           'event_id' => [
 201             'title' => ts('Event'),
 202             'type' => CRM_Utils_Type::T_STRING,
 203           ],
 204           'role_id' => [
 205             'title' => ts('Role'),
 206             'default' => TRUE,
 207           ],
 208           'status_id' => [
 209             'title' => ts('Status'),
 210             'default' => TRUE,
 211           ],
 212           'participant_register_date' => [
 213             'title' => ts('Registration Date'),
 214           ],
 215         ],
 216         'grouping' => 'event-fields',
 217         'filters' => [
 218           'event_id' => [
 219             'name' => 'event_id',
 220             'title' => ts('Event'),
 221             'operatorType' => CRM_Report_Form::OP_ENTITYREF,
 222             'type' => CRM_Utils_Type::T_INT,
 223             'attributes' => [
 224               'entity' => 'Event',
 225               'select' => ['minimumInputLength' => 0],
 226             ],
 227           ],
 228           'sid' => [
 229             'name' => 'status_id',
 230             'title' => ts('Participant Status'),
 231             'type' => CRM_Utils_Type::T_INT,
 232             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 233             'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'),
 234           ],
 235           'rid' => [
 236             'name' => 'role_id',
 237             'title' => ts('Participant Role'),
 238             'operatorType' => CRM_Report_Form::OP_MULTISELECT_SEPARATOR,
 239             'type' => CRM_Utils_Type::T_INT,
 240             'options' => CRM_Event_PseudoConstant::participantRole(),
 241           ],
 242           'participant_register_date' => [
 243             'title' => ts('Registration Date'),
 244             'operatorType' => CRM_Report_Form::OP_DATE,
 245           ],
 246         ],
 247         'group_bys' => [
 248           'event_id' => [
 249             'title' => ts('Event'),
 250           ],
 251         ],
 252       ],
 253       'civicrm_event' => [
 254         'dao' => 'CRM_Event_DAO_Event',
 255         'fields' => [
 256           'event_type_id' => [
 257             'title' => ts('Event Type'),
 258           ],
 259           'start_date' => [
 260             'title' => ts('Event Start Date'),
 261           ],
 262           'end_date' => [
 263             'title' => ts('Event End Date'),
 264           ],
 265         ],
 266         'grouping' => 'event-fields',
 267         'filters' => [
 268           'eid' => [
 269             'name' => 'event_type_id',
 270             'title' => ts('Event Type'),
 271             'type' => CRM_Utils_Type::T_INT,
 272             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 273             'options' => CRM_Core_OptionGroup::values('event_type'),
 274           ],
 275           'event_start_date' => [
 276             'name' => 'event_start_date',
 277             'title' => ts('Event Start Date'),
 278             'operatorType' => CRM_Report_Form::OP_DATE,
 279           ],
 280           'event_end_date' => [
 281             'name' => 'event_end_date',
 282             'title' => ts('Event End Date'),
 283             'operatorType' => CRM_Report_Form::OP_DATE,
 284           ],
 285         ],
 286         'group_bys' => [
 287           'event_type_id' => [
 288             'title' => ts('Event Type '),
 289           ],
 290         ],
 291       ],
 292       'civicrm_line_item' => [
 293         'dao' => 'CRM_Price_DAO_LineItem',
 294         'fields' => [
 295           'line_total' => [
 296             'title' => ts('Income'),
 297             'default' => TRUE,
 298             'statistics' => [
 299               'sum' => ts('Amount'),
 300               'avg' => ts('Average'),
 301             ],
 302           ],
 303           'participant_count' => [
 304             'title' => ts('Count'),
 305             'default' => TRUE,
 306             'statistics' => [
 307               'sum' => ts('Count'),
 308             ],
 309           ],
 310         ],
 311       ],
 312     ];
 313
 314     $this->_options = [
 315       'blank_column_begin' => [
 316         'title' => ts('Blank column at the Begining'),
 317         'type' => 'checkbox',
 318       ],
 319       'blank_column_end' => [
 320         'title' => ts('Blank column at the End'),
 321         'type' => 'select',
 322         'options' => [
 323           '' => '-select-',
 324           1 => ts('One'),
 325           2 => ts('Two'),
 326           3 => ts('Three'),
 327         ],
 328       ],
 329     ];
 330     parent::__construct();
 331   }
 332
 333   public function preProcess() {
 334     parent::preProcess();
 335   }
 336
 337   /**
 338    * @param array $rows
 339    *
 340    * @return array
 341    */
 342   public function statistics(&$rows) {
 343
 344     $statistics = parent::statistics($rows);
 345     $avg = NULL;
 346     $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
 347                   SUM( {$this->_aliases['civicrm_line_item']}.line_total )   as amount
 348             ";
 349     $sql = "{$select} {$this->_from} {$this->_where}";
 350     $dao = CRM_Core_DAO::executeQuery($sql);
 351     if ($dao->fetch()) {
 352
 353       if ($dao->count && $dao->amount) {
 354         $avg = $dao->amount / $dao->count;
 355       }
 356       $statistics['counts']['count'] = [
 357         'value' => $dao->count,
 358         'title' => ts('Total Participants'),
 359         'type' => CRM_Utils_Type::T_INT,
 360       ];
 361       $statistics['counts']['amount'] = [
 362         'value' => $dao->amount,
 363         'title' => ts('Total Income'),
 364         'type' => CRM_Utils_Type::T_MONEY,
 365       ];
 366       $statistics['counts']['avg'] = [
 367         'value' => $avg,
 368         'title' => ts('Average'),
 369         'type' => CRM_Utils_Type::T_MONEY,
 370       ];
 371     }
 372
 373     return $statistics;
 374   }
 375
 376   public function select() {
 377     $select = [];
 378     $this->_columnHeaders = [];
 379
 380     //add blank column at the Start
 381     if (array_key_exists('options', $this->_params) &&
 382       !empty($this->_params['options']['blank_column_begin'])
 383     ) {
 384       $select[] = " '' as blankColumnBegin";
 385       $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _';
 386     }
 387     foreach ($this->_columns as $tableName => $table) {
 388       if (array_key_exists('fields', $table)) {
 389         foreach ($table['fields'] as $fieldName => $field) {
 390           if (!empty($field['required']) ||
 391             !empty($this->_params['fields'][$fieldName])
 392           ) {
 393             if (!empty($field['statistics'])) {
 394               foreach ($field['statistics'] as $stat => $label) {
 395                 switch (strtolower($stat)) {
 396                   case 'sum':
 397                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 398                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 399                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 400                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 401                     break;
 402                 }
 403               }
 404             }
 405             else {
 406               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 407               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 408               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 409             }
 410           }
 411         }
 412       }
 413     }
 414     //add blank column at the end
 415     if ($blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params)) {
 416       for ($i = 1; $i <= $blankcols; $i++) {
 417         $select[] = " '' as blankColumnEnd_{$i}";
 418         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 419       }
 420     }
 421     $this->_selectClauses = $select;
 422     $this->_select = "SELECT " . implode(', ', $select) . " ";
 423   }
 424
 425   /**
 426    * @param $fields
 427    * @param $files
 428    * @param self $self
 429    *
 430    * @return array
 431    */
 432   public static function formRule($fields, $files, $self) {
 433     $errors = $grouping = [];
 434     return $errors;
 435   }
 436
 437   public function from() {
 438     $this->_from = "
 439       FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 440          LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 441               ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND {$this->_aliases['civicrm_event']}.is_template = 0
 442          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 443               ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 444          {$this->_aclFrom}
 445          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
 446               ON ({$this->_aliases['civicrm_employer']}.id  = {$this->_aliases['civicrm_contact']}.employer_id  )
 447          LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 448               ON {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' AND {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id";
 449
 450     $this->joinAddressFromContact();
 451     $this->joinPhoneFromContact();
 452     $this->joinEmailFromContact();
 453   }
 454
 455   public function storeWhereHavingClauseArray() {
 456     parent::storeWhereHavingClauseArray();
 457     $this->_whereClauses[] = "{$this->_aliases['civicrm_participant']}.is_test = 0";
 458   }
 459
 460   public function groupBy() {
 461     // We override this function because we use GROUP functions in the
 462     // SELECT clause, therefore we have to group by *something*. If the
 463     // user doesn't select a column to group by, we should group by participant id.
 464     parent::groupBy();
 465     if (empty($this->_groupBy)) {
 466       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id");
 467     }
 468   }
 469
 470   public function postProcess() {
 471
 472     // get ready with post process params
 473     $this->beginPostProcess();
 474
 475     // get the acl clauses built before we assemble the query
 476     $this->buildACLClause($this->_aliases['civicrm_contact']);
 477     // build query
 478     $sql = $this->buildQuery(TRUE);
 479
 480     // build array of result based on column headers. This method also allows
 481     // modifying column headers before using it to build result set i.e $rows.
 482     $this->buildRows($sql, $rows);
 483
 484     // format result set.
 485     $this->formatDisplay($rows);
 486
 487     // assign variables to templates
 488     $this->doTemplateAssignment($rows);
 489
 490     // do print / pdf / instance stuff if needed
 491     $this->endPostProcess($rows);
 492   }
 493
 494   /**
 495    * Alter display of rows.
 496    *
 497    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 498    * such as rendering contacts as links.
 499    *
 500    * @param array $rows
 501    *   Rows generated by SQL, with an array for each row.
 502    */
 503   public function alterDisplay(&$rows) {
 504     $entryFound = FALSE;
 505     $eventType = CRM_Core_OptionGroup::values('event_type');
 506
 507     foreach ($rows as $rowNum => $row) {
 508
 509       // convert sort name to links
 510       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 511         array_key_exists('civicrm_contact_id', $row)
 512       ) {
 513         if ($value = $row['civicrm_contact_sort_name']) {
 514           $url = CRM_Utils_System::url("civicrm/contact/view",
 515             'reset=1&cid=' . $row['civicrm_contact_id'],
 516             $this->_absoluteUrl
 517           );
 518           $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 519           $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 520         }
 521         $entryFound = TRUE;
 522       }
 523
 524       // convert participant ID to links
 525       if (array_key_exists('civicrm_participant_participant_id', $row) &&
 526         array_key_exists('civicrm_contact_id', $row)
 527       ) {
 528         if ($value = $row['civicrm_participant_participant_id']) {
 529           $url = CRM_Utils_System::url("civicrm/contact/view/participant",
 530             'reset=1&id=' . $row['civicrm_participant_participant_id'] .
 531             '&cid=' . $row['civicrm_contact_id'] .
 532             '&action=view&context=participant',
 533             $this->_absoluteUrl
 534           );
 535           $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
 536           $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
 537         }
 538         $entryFound = TRUE;
 539       }
 540
 541       // convert event name to links
 542       if (array_key_exists('civicrm_participant_event_id', $row)) {
 543         if ($value = $row['civicrm_participant_event_id']) {
 544           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($value, FALSE);
 545           $url = CRM_Report_Utils_Report::getNextUrl('event/Income',
 546             'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
 547             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 548           );
 549           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 550           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 551         }
 552         $entryFound = TRUE;
 553       }
 554
 555       // handle event type id
 556       if (array_key_exists('civicrm_event_event_type_id', $row)) {
 557         if ($value = $row['civicrm_event_event_type_id']) {
 558           $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 559         }
 560         $entryFound = TRUE;
 561       }
 562
 563       // handle participant status id
 564       if (array_key_exists('civicrm_participant_status_id', $row)) {
 565         if ($value = $row['civicrm_participant_status_id']) {
 566           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($value, FALSE);
 567         }
 568         $entryFound = TRUE;
 569       }
 570
 571       // handle participant role id
 572       if (array_key_exists('civicrm_participant_role_id', $row)) {
 573         if ($value = $row['civicrm_participant_role_id']) {
 574           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 575           $value = [];
 576           foreach ($roles as $role) {
 577             $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 578           }
 579           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
 580         }
 581         $entryFound = TRUE;
 582       }
 583
 584       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 585
 586       // skip looking further in rows, if first row itself doesn't
 587       // have the column we need
 588       if (!$entryFound) {
 589         break;
 590       }
 591     }
 592   }
 593
 594 }