CRM/Report/Form/Event/ParticipantListCount.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.3                                       |
   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) && CRM_Utils_Array::value('blank_column_begin', $this->_params['options'])) {
 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 (CRM_Utils_Array::value('required', $field) ||
 332             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 333           ) {
 334             if (CRM_Utils_Array::value('statistics', $field)) {
 335               foreach ($field['statistics'] as $stat => $label) {
 336                 switch (strtolower($stat)) {
 337                   case 'sum':
 338                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 339                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 340                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 341                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 342                     break;
 343                 }
 344               }
 345             }
 346             else {
 347               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 348               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 349               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 350             }
 351           }
 352         }
 353       }
 354     }
 355     //add blank column at the end
 356     if ($blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params)) {
 357       for ($i = 1; $i <= $blankcols; $i++) {
 358         $select[] = " '' as blankColumnEnd_{$i}";
 359         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 360       }
 361     }
 362     $this->_select = "SELECT " . implode(', ', $select) . " ";
 363   }
 364
 365   static function formRule($fields, $files, $self) {
 366     $errors = $grouping = array();
 367     return $errors;
 368   }
 369
 370   function from() {
 371     $this->_from = "
 372       FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 373          LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 374               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)
 375          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 376               ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 377          {$this->_aclFrom}
 378          LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
 379               ON ({$this->_aliases['civicrm_employer']}.id  = {$this->_aliases['civicrm_contact']}.employer_id  )
 380          LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 381               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 382                 {$this->_aliases['civicrm_address']}.is_primary = 1
 383          LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
 384               ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 385                 {$this->_aliases['civicrm_email']}.is_primary = 1)
 386          LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 387               ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 388                 {$this->_aliases['civicrm_phone']}.is_primary = 1
 389          LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 390               ON {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant'";
 391   }
 392
 393   function where() {
 394     $clauses = array();
 395     foreach ($this->_columns as $tableName => $table) {
 396       if (array_key_exists('filters', $table)) {
 397         foreach ($table['filters'] as $fieldName => $field) {
 398           $clause = NULL;
 399           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 400             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 401             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 402             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 403
 404             if ($relative || $from || $to) {
 405               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 406             }
 407           }
 408           else {
 409             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 410
 411             if ($fieldName == 'rid') {
 412               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 413               if (!empty($value)) {
 414                 $clause = "( {$field['dbAlias']} REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', $value) . "[[:>:]]' )";
 415               }
 416               $op = NULL;
 417             }
 418
 419             if ($op) {
 420               $clause = $this->whereClause($field,
 421                 $op,
 422                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 423                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 424                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 425               );
 426             }
 427           }
 428
 429           if (!empty($clause)) {
 430             $clauses[] = $clause;
 431           }
 432         }
 433       }
 434     }
 435
 436     if (empty($clauses)) {
 437       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 ";
 438     }
 439     else {
 440       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 AND " . implode(' AND ', $clauses);
 441     }
 442     if ($this->_aclWhere) {
 443       $this->_where .= " AND {$this->_aclWhere} ";
 444     }
 445   }
 446
 447   function groupBy() {
 448     $this->_groupBy = "";
 449     if (CRM_Utils_Array::value('group_bys', $this->_params) &&
 450       is_array($this->_params['group_bys']) &&
 451       !empty($this->_params['group_bys'])
 452     ) {
 453       foreach ($this->_columns as $tableName => $table) {
 454         if (array_key_exists('group_bys', $table)) {
 455           foreach ($table['group_bys'] as $fieldName => $field) {
 456             if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
 457               $this->_groupBy[] = $field['dbAlias'];
 458             }
 459           }
 460         }
 461       }
 462     }
 463
 464     if (!empty($this->_groupBy)) {
 465       $this->_groupBy = "ORDER BY " . implode(', ', $this->_groupBy) . ", {$this->_aliases['civicrm_contact']}.sort_name";
 466     }
 467     else {
 468       $this->_groupBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name";
 469     }
 470     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_participant']}.id " . $this->_groupBy;
 471   }
 472
 473   function postProcess() {
 474
 475     // get ready with post process params
 476     $this->beginPostProcess();
 477
 478     // get the acl clauses built before we assemble the query
 479     $this->buildACLClause($this->_aliases['civicrm_contact']);
 480     // build query
 481     $sql = $this->buildQuery(TRUE);
 482
 483     // build array of result based on column headers. This method also allows
 484     // modifying column headers before using it to build result set i.e $rows.
 485     $this->buildRows($sql, $rows);
 486
 487     // format result set.
 488     $this->formatDisplay($rows);
 489
 490     // assign variables to templates
 491     $this->doTemplateAssignment($rows);
 492
 493     // do print / pdf / instance stuff if needed
 494     $this->endPostProcess($rows);
 495   }
 496
 497   function alterDisplay(&$rows) {
 498
 499     $entryFound = FALSE;
 500     $eventType = CRM_Core_OptionGroup::values('event_type');
 501
 502     foreach ($rows as $rowNum => $row) {
 503
 504       // convert sort name to links
 505       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 506         array_key_exists('civicrm_contact_id', $row)
 507       ) {
 508         if ($value = $row['civicrm_contact_sort_name']) {
 509           $url = CRM_Utils_System::url("civicrm/contact/view",
 510             'reset=1&cid=' . $row['civicrm_contact_id'],
 511             $this->_absoluteUrl
 512           );
 513           $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 514           $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 515         }
 516         $entryFound = TRUE;
 517       }
 518
 519       // convert participant ID to links
 520       if (array_key_exists('civicrm_participant_participant_id', $row) &&
 521         array_key_exists('civicrm_contact_id', $row)
 522       ) {
 523         if ($value = $row['civicrm_participant_participant_id']) {
 524           $url = CRM_Utils_System::url("civicrm/contact/view/participant",
 525             'reset=1&id=' . $row['civicrm_participant_participant_id'] . '&cid=' . $row['civicrm_contact_id'] . '&action=view&context=participant',
 526             $this->_absoluteUrl
 527           );
 528           $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
 529           $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
 530         }
 531         $entryFound = TRUE;
 532       }
 533
 534       // convert event name to links
 535       if (array_key_exists('civicrm_participant_event_id', $row)) {
 536         if ($value = $row['civicrm_participant_event_id']) {
 537           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($value, FALSE);
 538           $url = CRM_Report_Utils_Report::getNextUrl('event/Income',
 539             'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
 540             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 541           );
 542           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 543           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 544         }
 545         $entryFound = TRUE;
 546       }
 547
 548       // handle event type id
 549       if (array_key_exists('civicrm_event_event_type_id', $row)) {
 550         if ($value = $row['civicrm_event_event_type_id']) {
 551           $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 552         }
 553         $entryFound = TRUE;
 554       }
 555
 556       // handle participant status id
 557       if (array_key_exists('civicrm_participant_status_id', $row)) {
 558         if ($value = $row['civicrm_participant_status_id']) {
 559           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($value, FALSE);
 560         }
 561         $entryFound = TRUE;
 562       }
 563
 564       // handle participant role id
 565       if (array_key_exists('civicrm_participant_role_id', $row)) {
 566         if ($value = $row['civicrm_participant_role_id']) {
 567           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 568           $value = array();
 569           foreach ($roles as $role) {
 570             $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 571           }
 572           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
 573         }
 574         $entryFound = TRUE;
 575       }
 576
 577
 578       // skip looking further in rows, if first row itself doesn't
 579       // have the column we need
 580       if (!$entryFound) {
 581         break;
 582       }
 583     }
 584   }
 585 }
 586