CRM/Report/Form/Event/ParticipantListCount.php

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