3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
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-2015
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 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
238 'options' => CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label'),
242 'title' => ts('Participant Role'),
243 'operatorType' => CRM_Report_Form
::OP_MULTISELECT_SEPARATOR
,
244 'options' => CRM_Event_PseudoConstant
::participantRole(),
246 'participant_register_date' => array(
247 'title' => ts('Registration Date'),
248 'operatorType' => CRM_Report_Form
::OP_DATE
,
251 'group_bys' => array(
253 'title' => ts('Event'),
257 'civicrm_event' => array(
258 'dao' => 'CRM_Event_DAO_Event',
260 'event_type_id' => array(
261 'title' => ts('Event Type'),
263 'start_date' => array(
264 'title' => ts('Event Start Date'),
267 'title' => ts('Event End Date'),
270 'grouping' => 'event-fields',
273 'name' => 'event_type_id',
274 'title' => ts('Event Type'),
275 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
276 'options' => CRM_Core_OptionGroup
::values('event_type'),
278 'event_start_date' => array(
279 'name' => 'event_start_date',
280 'title' => ts('Event Start Date'),
281 'operatorType' => CRM_Report_Form
::OP_DATE
,
283 'event_end_date' => array(
284 'name' => 'event_end_date',
285 'title' => ts('Event End Date'),
286 'operatorType' => CRM_Report_Form
::OP_DATE
,
289 'group_bys' => array(
290 'event_type_id' => array(
291 'title' => ts('Event Type '),
295 'civicrm_line_item' => array(
296 'dao' => 'CRM_Price_DAO_LineItem',
298 'line_total' => array(
299 'title' => ts('Income'),
301 'statistics' => array(
302 'sum' => ts('Amount'),
303 'avg' => ts('Average'),
306 'participant_count' => array(
307 'title' => ts('Count'),
309 'statistics' => array(
310 'sum' => ts('Count'),
317 $this->_options
= array(
318 'blank_column_begin' => array(
319 'title' => ts('Blank column at the Begining'),
320 'type' => 'checkbox',
322 'blank_column_end' => array(
323 'title' => ts('Blank column at the End'),
333 parent
::__construct();
336 public function preProcess() {
337 parent
::preProcess();
341 * Add The statistics.
346 public function statistics(&$rows) {
348 $statistics = parent
::statistics($rows);
350 $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
351 SUM( {$this->_aliases['civicrm_line_item']}.line_total ) as amount
353 $sql = "{$select} {$this->_from} {$this->_where}";
354 $dao = CRM_Core_DAO
::executeQuery($sql);
357 if ($dao->count
&& $dao->amount
) {
358 $avg = $dao->amount
/ $dao->count
;
360 $statistics['counts']['count'] = array(
361 'value' => $dao->count
,
362 'title' => 'Total Participants',
363 'type' => CRM_Utils_Type
::T_INT
,
365 $statistics['counts']['amount'] = array(
366 'value' => $dao->amount
,
367 'title' => 'Total Income',
368 'type' => CRM_Utils_Type
::T_MONEY
,
370 $statistics['counts']['avg '] = array(
372 'title' => 'Average',
373 'type' => CRM_Utils_Type
::T_MONEY
,
380 public function select() {
382 $this->_columnHeaders
= array();
384 //add blank column at the Start
385 if (array_key_exists('options', $this->_params
) &&
386 !empty($this->_params
['options']['blank_column_begin'])
388 $select[] = " '' as blankColumnBegin";
389 $this->_columnHeaders
['blankColumnBegin']['title'] = '_ _ _ _';
391 foreach ($this->_columns
as $tableName => $table) {
392 if (array_key_exists('fields', $table)) {
393 foreach ($table['fields'] as $fieldName => $field) {
394 if (!empty($field['required']) ||
395 !empty($this->_params
['fields'][$fieldName])
397 if (!empty($field['statistics'])) {
398 foreach ($field['statistics'] as $stat => $label) {
399 switch (strtolower($stat)) {
401 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
402 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
403 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
404 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
410 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
411 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
412 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
418 //add blank column at the end
419 if ($blankcols = CRM_Utils_Array
::value('blank_column_end', $this->_params
)) {
420 for ($i = 1; $i <= $blankcols; $i++
) {
421 $select[] = " '' as blankColumnEnd_{$i}";
422 $this->_columnHeaders
["blank_{$i}"]['title'] = "_ _ _ _";
425 $this->_select
= "SELECT " . implode(', ', $select) . " ";
435 public static function formRule($fields, $files, $self) {
436 $errors = $grouping = array();
440 public function from() {
442 FROM civicrm_participant {$this->_aliases['civicrm_participant']}
443 LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
444 ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND {$this->_aliases['civicrm_event']}.is_template = 0
445 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
446 ON ({$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id )
448 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
449 ON ({$this->_aliases['civicrm_employer']}.id = {$this->_aliases['civicrm_contact']}.employer_id )
450 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
451 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
452 {$this->_aliases['civicrm_address']}.is_primary = 1
453 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
454 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
455 {$this->_aliases['civicrm_email']}.is_primary = 1)
456 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
457 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
458 {$this->_aliases['civicrm_phone']}.is_primary = 1
459 LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
460 ON {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' AND {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id";
463 public function storeWhereHavingClauseArray() {
464 parent
::storeWhereHavingClauseArray();
465 $this->_whereClauses
[] = "{$this->_aliases['civicrm_participant']}.is_test = 0";
468 public function groupBy() {
469 // We override this function because we use GROUP functions in the
470 // SELECT clause, therefore we have to group by *something*. If the
471 // user doesn't select a column to group by, we should group by participant id.
473 if (empty($this->_groupBy
)) {
474 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_participant']}.id";
478 public function postProcess() {
480 // get ready with post process params
481 $this->beginPostProcess();
483 // get the acl clauses built before we assemble the query
484 $this->buildACLClause($this->_aliases
['civicrm_contact']);
486 $sql = $this->buildQuery(TRUE);
488 // build array of result based on column headers. This method also allows
489 // modifying column headers before using it to build result set i.e $rows.
490 $this->buildRows($sql, $rows);
492 // format result set.
493 $this->formatDisplay($rows);
495 // assign variables to templates
496 $this->doTemplateAssignment($rows);
498 // do print / pdf / instance stuff if needed
499 $this->endPostProcess($rows);
503 * Alter display of rows.
505 * Iterate through the rows retrieved via SQL and make changes for display purposes,
506 * such as rendering contacts as links.
509 * Rows generated by SQL, with an array for each row.
511 public function alterDisplay(&$rows) {
513 $eventType = CRM_Core_OptionGroup
::values('event_type');
515 foreach ($rows as $rowNum => $row) {
517 // convert sort name to links
518 if (array_key_exists('civicrm_contact_sort_name', $row) &&
519 array_key_exists('civicrm_contact_id', $row)
521 if ($value = $row['civicrm_contact_sort_name']) {
522 $url = CRM_Utils_System
::url("civicrm/contact/view",
523 'reset=1&cid=' . $row['civicrm_contact_id'],
526 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
527 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
532 // convert participant ID to links
533 if (array_key_exists('civicrm_participant_participant_id', $row) &&
534 array_key_exists('civicrm_contact_id', $row)
536 if ($value = $row['civicrm_participant_participant_id']) {
537 $url = CRM_Utils_System
::url("civicrm/contact/view/participant",
538 'reset=1&id=' . $row['civicrm_participant_participant_id'] .
539 '&cid=' . $row['civicrm_contact_id'] .
540 '&action=view&context=participant',
543 $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
544 $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
549 // convert event name to links
550 if (array_key_exists('civicrm_participant_event_id', $row)) {
551 if ($value = $row['civicrm_participant_event_id']) {
552 $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant
::event($value, FALSE);
553 $url = CRM_Report_Utils_Report
::getNextUrl('event/Income',
554 'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
555 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
557 $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
558 $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
563 // handle event type id
564 if (array_key_exists('civicrm_event_event_type_id', $row)) {
565 if ($value = $row['civicrm_event_event_type_id']) {
566 $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
571 // handle participant status id
572 if (array_key_exists('civicrm_participant_status_id', $row)) {
573 if ($value = $row['civicrm_participant_status_id']) {
574 $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant
::participantStatus($value, FALSE);
579 // handle participant role id
580 if (array_key_exists('civicrm_participant_role_id', $row)) {
581 if ($value = $row['civicrm_participant_role_id']) {
582 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value);
584 foreach ($roles as $role) {
585 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
587 $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
593 if (array_key_exists('civicrm_contact_gender_id', $row)) {
594 if ($value = $row['civicrm_contact_gender_id']) {
595 $gender = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
596 $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
601 // display birthday in the configured custom format
602 if (array_key_exists('civicrm_contact_birth_date', $row)) {
603 $birthDate = $row['civicrm_contact_birth_date'];
605 $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date
::customFormat($birthDate, '%Y%m%d');
610 // skip looking further in rows, if first row itself doesn't
611 // have the column we need