CRM/Report/Form/Event/ParticipantListCount.php

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