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