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