3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2016
35 class CRM_Report_Form_Event_ParticipantListCount
extends CRM_Report_Form_Event
{
37 protected $_summary = NULL;
38 protected $_groupFilter = TRUE;
39 protected $_tagFilter = TRUE;
40 protected $_customGroupExtends = array(
45 public $_drilldownReport = array('event/income' => 'Link to Detail Report');
49 public function __construct() {
50 $this->_columns
= array(
51 'civicrm_contact' => array(
52 'dao' => 'CRM_Contact_DAO_Contact',
55 'title' => ts('Name'),
60 'first_name' => array(
61 'title' => ts('First Name'),
63 'middle_name' => array(
64 'title' => ts('Middle Name'),
67 'title' => ts('Last Name'),
74 'title' => ts('Gender'),
76 'birth_date' => array(
77 'title' => ts('Birth Date'),
81 'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
83 'contact_type' => array(
84 'title' => ts('Contact Type'),
86 'contact_sub_type' => array(
87 'title' => ts('Contact Subtype'),
92 'title' => ts('Participant Name'),
96 'title' => ts('Contact ID'),
100 'title' => ts('Gender'),
101 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
102 'options' => CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id'),
104 'birth_date' => array(
105 'title' => ts('Birth Date'),
106 'operatorType' => CRM_Report_Form
::OP_DATE
,
108 'contact_type' => array(
109 'title' => ts('Contact Type'),
111 'contact_sub_type' => array(
112 'title' => ts('Contact Subtype'),
115 'grouping' => 'contact-fields',
116 'order_bys' => array(
117 'sort_name' => array(
118 'title' => ts('Last Name, First Name'),
120 'default_weight' => '0',
121 'default_order' => 'ASC',
123 'first_name' => array(
124 'name' => 'first_name',
125 'title' => ts('First Name'),
127 'gender_id' => array(
128 'name' => 'gender_id',
129 'title' => ts('Gender'),
131 'birth_date' => array(
132 'name' => 'birth_date',
133 'title' => ts('Birth Date'),
135 'contact_type' => array(
136 'title' => ts('Contact Type'),
138 'contact_sub_type' => array(
139 'title' => ts('Contact Subtype'),
143 'civicrm_employer' => array(
144 'dao' => 'CRM_Contact_DAO_Contact',
145 'grouping' => 'contact-fields',
148 'no_display' => TRUE,
151 'organization_name' => array(
152 'title' => 'Employer',
158 'civicrm_email' => array(
159 'dao' => 'CRM_Core_DAO_Email',
162 'title' => ts('Email'),
166 'grouping' => 'contact-fields',
169 'title' => ts('Participant E-mail'),
170 'operator' => 'like',
174 'civicrm_phone' => array(
175 'dao' => 'CRM_Core_DAO_Phone',
176 'grouping' => 'contact-fields',
179 'title' => ts('Phone No'),
184 'civicrm_address' => array(
185 'dao' => 'CRM_Core_DAO_Address',
187 'street_address' => NULL,
189 'postal_code' => NULL,
190 'state_province_id' => array(
191 'title' => ts('State/Province'),
193 'country_id' => array(
194 'title' => ts('Country'),
197 'grouping' => 'contact-fields',
199 'civicrm_participant' => array(
200 'dao' => 'CRM_Event_DAO_Participant',
202 'participant_id' => array(
203 'title' => ts('Participant ID'),
207 'title' => ts('Event'),
208 'type' => CRM_Utils_Type
::T_STRING
,
211 'title' => ts('Role'),
214 'status_id' => array(
215 'title' => ts('Status'),
218 'participant_register_date' => array(
219 'title' => ts('Registration Date'),
222 'grouping' => 'event-fields',
225 'name' => 'event_id',
226 'title' => ts('Event'),
227 'operatorType' => CRM_Report_Form
::OP_ENTITYREF
,
228 'type' => CRM_Utils_Type
::T_INT
,
229 'attributes' => array(
231 'select' => array('minimumInputLength' => 0),
235 'name' => 'status_id',
236 'title' => ts('Participant Status'),
237 'type' => CRM_Utils_Type
::T_INT
,
238 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
239 'options' => CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label'),
243 'title' => ts('Participant Role'),
244 'operatorType' => CRM_Report_Form
::OP_MULTISELECT_SEPARATOR
,
245 'type' => CRM_Utils_Type
::T_INT
,
246 'options' => CRM_Event_PseudoConstant
::participantRole(),
248 'participant_register_date' => array(
249 'title' => ts('Registration Date'),
250 'operatorType' => CRM_Report_Form
::OP_DATE
,
253 'group_bys' => array(
255 'title' => ts('Event'),
259 'civicrm_event' => array(
260 'dao' => 'CRM_Event_DAO_Event',
262 'event_type_id' => array(
263 'title' => ts('Event Type'),
265 'start_date' => array(
266 'title' => ts('Event Start Date'),
269 'title' => ts('Event End Date'),
272 'grouping' => 'event-fields',
275 'name' => 'event_type_id',
276 'title' => ts('Event Type'),
277 'type' => CRM_Utils_Type
::T_INT
,
278 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
279 'options' => CRM_Core_OptionGroup
::values('event_type'),
281 'event_start_date' => array(
282 'name' => 'event_start_date',
283 'title' => ts('Event Start Date'),
284 'operatorType' => CRM_Report_Form
::OP_DATE
,
286 'event_end_date' => array(
287 'name' => 'event_end_date',
288 'title' => ts('Event End Date'),
289 'operatorType' => CRM_Report_Form
::OP_DATE
,
292 'group_bys' => array(
293 'event_type_id' => array(
294 'title' => ts('Event Type '),
298 'civicrm_line_item' => array(
299 'dao' => 'CRM_Price_DAO_LineItem',
301 'line_total' => array(
302 'title' => ts('Income'),
304 'statistics' => array(
305 'sum' => ts('Amount'),
306 'avg' => ts('Average'),
309 'participant_count' => array(
310 'title' => ts('Count'),
312 'statistics' => array(
313 'sum' => ts('Count'),
320 $this->_options
= array(
321 'blank_column_begin' => array(
322 'title' => ts('Blank column at the Begining'),
323 'type' => 'checkbox',
325 'blank_column_end' => array(
326 'title' => ts('Blank column at the End'),
336 parent
::__construct();
339 public function preProcess() {
340 parent
::preProcess();
344 * Add The statistics.
349 public function statistics(&$rows) {
351 $statistics = parent
::statistics($rows);
353 $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
354 SUM( {$this->_aliases['civicrm_line_item']}.line_total ) as amount
356 $sql = "{$select} {$this->_from} {$this->_where}";
357 $dao = CRM_Core_DAO
::executeQuery($sql);
360 if ($dao->count
&& $dao->amount
) {
361 $avg = $dao->amount
/ $dao->count
;
363 $statistics['counts']['count'] = array(
364 'value' => $dao->count
,
365 'title' => 'Total Participants',
366 'type' => CRM_Utils_Type
::T_INT
,
368 $statistics['counts']['amount'] = array(
369 'value' => $dao->amount
,
370 'title' => 'Total Income',
371 'type' => CRM_Utils_Type
::T_MONEY
,
373 $statistics['counts']['avg '] = array(
375 'title' => 'Average',
376 'type' => CRM_Utils_Type
::T_MONEY
,
383 public function select() {
385 $this->_columnHeaders
= array();
387 //add blank column at the Start
388 if (array_key_exists('options', $this->_params
) &&
389 !empty($this->_params
['options']['blank_column_begin'])
391 $select[] = " '' as blankColumnBegin";
392 $this->_columnHeaders
['blankColumnBegin']['title'] = '_ _ _ _';
394 foreach ($this->_columns
as $tableName => $table) {
395 if (array_key_exists('fields', $table)) {
396 foreach ($table['fields'] as $fieldName => $field) {
397 if (!empty($field['required']) ||
398 !empty($this->_params
['fields'][$fieldName])
400 if (!empty($field['statistics'])) {
401 foreach ($field['statistics'] as $stat => $label) {
402 switch (strtolower($stat)) {
404 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
405 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
406 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
407 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
413 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
414 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
415 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
421 //add blank column at the end
422 if ($blankcols = CRM_Utils_Array
::value('blank_column_end', $this->_params
)) {
423 for ($i = 1; $i <= $blankcols; $i++
) {
424 $select[] = " '' as blankColumnEnd_{$i}";
425 $this->_columnHeaders
["blank_{$i}"]['title'] = "_ _ _ _";
428 $this->_select
= "SELECT " . implode(', ', $select) . " ";
438 public static function formRule($fields, $files, $self) {
439 $errors = $grouping = array();
443 public function from() {
445 FROM civicrm_participant {$this->_aliases['civicrm_participant']}
446 LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
447 ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND {$this->_aliases['civicrm_event']}.is_template = 0
448 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
449 ON ({$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id )
451 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
452 ON ({$this->_aliases['civicrm_employer']}.id = {$this->_aliases['civicrm_contact']}.employer_id )
453 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
454 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
455 {$this->_aliases['civicrm_address']}.is_primary = 1
456 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
457 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
458 {$this->_aliases['civicrm_email']}.is_primary = 1)
459 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
460 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
461 {$this->_aliases['civicrm_phone']}.is_primary = 1
462 LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
463 ON {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' AND {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id";
466 public function storeWhereHavingClauseArray() {
467 parent
::storeWhereHavingClauseArray();
468 $this->_whereClauses
[] = "{$this->_aliases['civicrm_participant']}.is_test = 0";
471 public function groupBy() {
472 // We override this function because we use GROUP functions in the
473 // SELECT clause, therefore we have to group by *something*. If the
474 // user doesn't select a column to group by, we should group by participant id.
476 if (empty($this->_groupBy
)) {
477 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_participant']}.id";
481 public function postProcess() {
483 // get ready with post process params
484 $this->beginPostProcess();
486 // get the acl clauses built before we assemble the query
487 $this->buildACLClause($this->_aliases
['civicrm_contact']);
489 $sql = $this->buildQuery(TRUE);
491 // build array of result based on column headers. This method also allows
492 // modifying column headers before using it to build result set i.e $rows.
493 $this->buildRows($sql, $rows);
495 // format result set.
496 $this->formatDisplay($rows);
498 // assign variables to templates
499 $this->doTemplateAssignment($rows);
501 // do print / pdf / instance stuff if needed
502 $this->endPostProcess($rows);
506 * Alter display of rows.
508 * Iterate through the rows retrieved via SQL and make changes for display purposes,
509 * such as rendering contacts as links.
512 * Rows generated by SQL, with an array for each row.
514 public function alterDisplay(&$rows) {
516 $eventType = CRM_Core_OptionGroup
::values('event_type');
518 foreach ($rows as $rowNum => $row) {
520 // convert sort name to links
521 if (array_key_exists('civicrm_contact_sort_name', $row) &&
522 array_key_exists('civicrm_contact_id', $row)
524 if ($value = $row['civicrm_contact_sort_name']) {
525 $url = CRM_Utils_System
::url("civicrm/contact/view",
526 'reset=1&cid=' . $row['civicrm_contact_id'],
529 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
530 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
535 // convert participant ID to links
536 if (array_key_exists('civicrm_participant_participant_id', $row) &&
537 array_key_exists('civicrm_contact_id', $row)
539 if ($value = $row['civicrm_participant_participant_id']) {
540 $url = CRM_Utils_System
::url("civicrm/contact/view/participant",
541 'reset=1&id=' . $row['civicrm_participant_participant_id'] .
542 '&cid=' . $row['civicrm_contact_id'] .
543 '&action=view&context=participant',
546 $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
547 $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
552 // convert event name to links
553 if (array_key_exists('civicrm_participant_event_id', $row)) {
554 if ($value = $row['civicrm_participant_event_id']) {
555 $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant
::event($value, FALSE);
556 $url = CRM_Report_Utils_Report
::getNextUrl('event/Income',
557 'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
558 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
560 $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
561 $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
566 // handle event type id
567 if (array_key_exists('civicrm_event_event_type_id', $row)) {
568 if ($value = $row['civicrm_event_event_type_id']) {
569 $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
574 // handle participant status id
575 if (array_key_exists('civicrm_participant_status_id', $row)) {
576 if ($value = $row['civicrm_participant_status_id']) {
577 $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant
::participantStatus($value, FALSE);
582 // handle participant role id
583 if (array_key_exists('civicrm_participant_role_id', $row)) {
584 if ($value = $row['civicrm_participant_role_id']) {
585 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value);
587 foreach ($roles as $role) {
588 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
590 $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
596 if (array_key_exists('civicrm_contact_gender_id', $row)) {
597 if ($value = $row['civicrm_contact_gender_id']) {
598 $gender = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
599 $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
604 // display birthday in the configured custom format
605 if (array_key_exists('civicrm_contact_birth_date', $row)) {
606 $birthDate = $row['civicrm_contact_birth_date'];
608 $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date
::customFormat($birthDate, '%Y%m%d');
613 // skip looking further in rows, if first row itself doesn't
614 // have the column we need