CRM/Report/Form/Event/ParticipantListCount.php

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