crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Event/ParticipantListCount.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  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             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 238             'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'),
 239           ),
 240           'rid' => array(
 241             'name' => 'role_id',
 242             'title' => ts('Participant Role'),
 243             'operatorType' => CRM_Report_Form::OP_MULTISELECT_SEPARATOR,
 244             'options' => CRM_Event_PseudoConstant::participantRole(),
 245           ),
 246           'participant_register_date' => array(
 247             'title' => ts('Registration Date'),
 248             'operatorType' => CRM_Report_Form::OP_DATE,
 249           ),
 250         ),
 251         'group_bys' => array(
 252           'event_id' => array(
 253             'title' => ts('Event'),
 254           ),
 255         ),
 256       ),
 257       'civicrm_event' => array(
 258         'dao' => 'CRM_Event_DAO_Event',
 259         'fields' => array(
 260           'event_type_id' => array(
 261             'title' => ts('Event Type'),
 262           ),
 263           'start_date' => array(
 264             'title' => ts('Event Start Date'),
 265           ),
 266           'end_date' => array(
 267             'title' => ts('Event End Date'),
 268           ),
 269         ),
 270         'grouping' => 'event-fields',
 271         'filters' => array(
 272           'eid' => array(
 273             'name' => 'event_type_id',
 274             'title' => ts('Event Type'),
 275             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 276             'options' => CRM_Core_OptionGroup::values('event_type'),
 277           ),
 278           'event_start_date' => array(
 279             'name' => 'start_date',
 280             'title' => ts('Event Start Date'),
 281             'operatorType' => CRM_Report_Form::OP_DATE,
 282           ),
 283           'event_end_date' => array(
 284             'name' => 'end_date',
 285             'title' => ts('Event End Date'),
 286             'operatorType' => CRM_Report_Form::OP_DATE,
 287           ),
 288         ),
 289         'group_bys' => array(
 290           'event_type_id' => array(
 291             'title' => ts('Event Type '),
 292           ),
 293         ),
 294       ),
 295       'civicrm_line_item' => array(
 296         'dao' => 'CRM_Price_DAO_LineItem',
 297         'fields' => array(
 298           'line_total' => array(
 299             'title' => ts('Income'),
 300             'default' => TRUE,
 301             'statistics' => array(
 302               'sum' => ts('Amount'),
 303               'avg' => ts('Average'),
 304             ),
 305           ),
 306           'participant_count' => array(
 307             'title' => ts('Count'),
 308             'default' => TRUE,
 309             'statistics' => array(
 310               'sum' => ts('Count'),
 311             ),
 312           ),
 313         ),
 314       ),
 315     );
 316
 317     $this->_options = array(
 318       'blank_column_begin' => array(
 319         'title' => ts('Blank column at the Begining'),
 320         'type' => 'checkbox',
 321       ),
 322       'blank_column_end' => array(
 323         'title' => ts('Blank column at the End'),
 324         'type' => 'select',
 325         'options' => array(
 326           '' => '-select-',
 327           1 => ts('One'),
 328           2 => ts('Two'),
 329           3 => ts('Three'),
 330         ),
 331       ),
 332     );
 333     parent::__construct();
 334   }
 335
 336   public function preProcess() {
 337     parent::preProcess();
 338   }
 339
 340   /**
 341    * Add The statistics.
 342    * @param $rows
 343    *
 344    * @return array
 345    */
 346   public function statistics(&$rows) {
 347
 348     $statistics = parent::statistics($rows);
 349     $avg = NULL;
 350     $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
 351                   SUM( {$this->_aliases['civicrm_line_item']}.line_total )   as amount
 352             ";
 353     $sql = "{$select} {$this->_from} {$this->_where}";
 354     $dao = CRM_Core_DAO::executeQuery($sql);
 355     if ($dao->fetch()) {
 356
 357       if ($dao->count && $dao->amount) {
 358         $avg = $dao->amount / $dao->count;
 359       }
 360       $statistics['counts']['count'] = array(
 361         'value' => $dao->count,
 362         'title' => 'Total Participants',
 363         'type' => CRM_Utils_Type::T_INT,
 364       );
 365       $statistics['counts']['amount'] = array(
 366         'value' => $dao->amount,
 367         'title' => 'Total Income',
 368         'type' => CRM_Utils_Type::T_MONEY,
 369       );
 370       $statistics['counts']['avg    '] = array(
 371         'value' => $avg,
 372         'title' => 'Average',
 373         'type' => CRM_Utils_Type::T_MONEY,
 374       );
 375     }
 376
 377     return $statistics;
 378   }
 379
 380   public function select() {
 381     $select = array();
 382     $this->_columnHeaders = array();
 383
 384     //add blank column at the Start
 385     if (array_key_exists('options', $this->_params) &&
 386       !empty($this->_params['options']['blank_column_begin'])
 387     ) {
 388       $select[] = " '' as blankColumnBegin";
 389       $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _';
 390     }
 391     foreach ($this->_columns as $tableName => $table) {
 392       if (array_key_exists('fields', $table)) {
 393         foreach ($table['fields'] as $fieldName => $field) {
 394           if (!empty($field['required']) ||
 395             !empty($this->_params['fields'][$fieldName])
 396           ) {
 397             if (!empty($field['statistics'])) {
 398               foreach ($field['statistics'] as $stat => $label) {
 399                 switch (strtolower($stat)) {
 400                   case 'sum':
 401                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 402                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 403                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 404                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 405                     break;
 406                 }
 407               }
 408             }
 409             else {
 410               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 411               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 412               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 413             }
 414           }
 415         }
 416       }
 417     }
 418     //add blank column at the end
 419     if ($blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params)) {
 420       for ($i = 1; $i <= $blankcols; $i++) {
 421         $select[] = " '' as blankColumnEnd_{$i}";
 422         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 423       }
 424     }
 425     $this->_select = "SELECT " . implode(', ', $select) . " ";
 426   }
 427
 428   /**
 429    * @param $fields
 430    * @param $files
 431    * @param $self
 432    *
 433    * @return array
 434    */
 435   public static function formRule($fields, $files, $self) {
 436     $errors = $grouping = array();
 437     return $errors;
 438   }
 439
 440   public function from() {
 441     $this->_from = "
 442       FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 443          LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 444               ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND {$this->_aliases['civicrm_event']}.is_template = 0
 445          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 446               ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 447          {$this->_aclFrom}
 448          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
 449               ON ({$this->_aliases['civicrm_employer']}.id  = {$this->_aliases['civicrm_contact']}.employer_id  )
 450          LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 451               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 452                 {$this->_aliases['civicrm_address']}.is_primary = 1
 453          LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
 454               ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 455                 {$this->_aliases['civicrm_email']}.is_primary = 1)
 456          LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 457               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 458                 {$this->_aliases['civicrm_phone']}.is_primary = 1
 459          LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 460               ON {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' AND {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id";
 461   }
 462
 463   public function storeWhereHavingClauseArray() {
 464     parent::storeWhereHavingClauseArray();
 465     $this->_whereClauses[] = "{$this->_aliases['civicrm_participant']}.is_test = 0";
 466   }
 467
 468   public function groupBy() {
 469     // We override this function because we use GROUP functions in the
 470     // SELECT clause, therefore we have to group by *something*. If the
 471     // user doesn't select a column to group by, we should group by participant id.
 472     parent::groupBy();
 473     if (empty($this->_groupBy)) {
 474       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_participant']}.id";
 475     }
 476   }
 477
 478   public function postProcess() {
 479
 480     // get ready with post process params
 481     $this->beginPostProcess();
 482
 483     // get the acl clauses built before we assemble the query
 484     $this->buildACLClause($this->_aliases['civicrm_contact']);
 485     // build query
 486     $sql = $this->buildQuery(TRUE);
 487
 488     // build array of result based on column headers. This method also allows
 489     // modifying column headers before using it to build result set i.e $rows.
 490     $this->buildRows($sql, $rows);
 491
 492     // format result set.
 493     $this->formatDisplay($rows);
 494
 495     // assign variables to templates
 496     $this->doTemplateAssignment($rows);
 497
 498     // do print / pdf / instance stuff if needed
 499     $this->endPostProcess($rows);
 500   }
 501
 502   /**
 503    * Alter display of rows.
 504    *
 505    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 506    * such as rendering contacts as links.
 507    *
 508    * @param array $rows
 509    *   Rows generated by SQL, with an array for each row.
 510    */
 511   public function alterDisplay(&$rows) {
 512     $entryFound = FALSE;
 513     $eventType = CRM_Core_OptionGroup::values('event_type');
 514
 515     foreach ($rows as $rowNum => $row) {
 516
 517       // convert sort name to links
 518       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 519         array_key_exists('civicrm_contact_id', $row)
 520       ) {
 521         if ($value = $row['civicrm_contact_sort_name']) {
 522           $url = CRM_Utils_System::url("civicrm/contact/view",
 523             'reset=1&cid=' . $row['civicrm_contact_id'],
 524             $this->_absoluteUrl
 525           );
 526           $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 527           $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 528         }
 529         $entryFound = TRUE;
 530       }
 531
 532       // convert participant ID to links
 533       if (array_key_exists('civicrm_participant_participant_id', $row) &&
 534         array_key_exists('civicrm_contact_id', $row)
 535       ) {
 536         if ($value = $row['civicrm_participant_participant_id']) {
 537           $url = CRM_Utils_System::url("civicrm/contact/view/participant",
 538             'reset=1&id=' . $row['civicrm_participant_participant_id'] .
 539             '&cid=' . $row['civicrm_contact_id'] .
 540             '&action=view&context=participant',
 541             $this->_absoluteUrl
 542           );
 543           $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
 544           $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
 545         }
 546         $entryFound = TRUE;
 547       }
 548
 549       // convert event name to links
 550       if (array_key_exists('civicrm_participant_event_id', $row)) {
 551         if ($value = $row['civicrm_participant_event_id']) {
 552           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($value, FALSE);
 553           $url = CRM_Report_Utils_Report::getNextUrl('event/Income',
 554             'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
 555             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 556           );
 557           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 558           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 559         }
 560         $entryFound = TRUE;
 561       }
 562
 563       // handle event type id
 564       if (array_key_exists('civicrm_event_event_type_id', $row)) {
 565         if ($value = $row['civicrm_event_event_type_id']) {
 566           $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 567         }
 568         $entryFound = TRUE;
 569       }
 570
 571       // handle participant status id
 572       if (array_key_exists('civicrm_participant_status_id', $row)) {
 573         if ($value = $row['civicrm_participant_status_id']) {
 574           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($value, FALSE);
 575         }
 576         $entryFound = TRUE;
 577       }
 578
 579       // handle participant role id
 580       if (array_key_exists('civicrm_participant_role_id', $row)) {
 581         if ($value = $row['civicrm_participant_role_id']) {
 582           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 583           $value = array();
 584           foreach ($roles as $role) {
 585             $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 586           }
 587           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
 588         }
 589         $entryFound = TRUE;
 590       }
 591
 592       //handle gender
 593       if (array_key_exists('civicrm_contact_gender_id', $row)) {
 594         if ($value = $row['civicrm_contact_gender_id']) {
 595           $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
 596           $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
 597         }
 598         $entryFound = TRUE;
 599       }
 600
 601       // display birthday in the configured custom format
 602       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 603         $birthDate = $row['civicrm_contact_birth_date'];
 604         if ($birthDate) {
 605           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 606         }
 607         $entryFound = TRUE;
 608       }
 609
 610       // skip looking further in rows, if first row itself doesn't
 611       // have the column we need
 612       if (!$entryFound) {
 613         break;
 614       }
 615     }
 616   }
 617
 618 }