4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.4 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2013 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2013
36 class CRM_Report_Form_Event_ParticipantListCount
extends CRM_Report_Form_Event
{
38 protected $_summary = NULL;
40 protected $_customGroupExtends = array(
43 public $_drilldownReport = array('event/income' => 'Link to Detail Report');
44 function __construct() {
45 $this->_columns
= array(
48 'dao' => 'CRM_Contact_DAO_Contact',
52 array('title' => ts('Name'),
63 'grouping' => 'contact-fields',
66 array('title' => ts('Participant Name'),
73 'dao' => 'CRM_Contact_DAO_Contact',
74 'grouping' => 'contact-fields',
80 'organization_name' => array(
81 'title' => 'Employer',
89 'dao' => 'CRM_Core_DAO_Email',
92 array('title' => ts('Email'),
96 'grouping' => 'contact-fields',
100 array('title' => ts('Participant E-mail'),
101 'operator' => 'like',
107 'dao' => 'CRM_Core_DAO_Phone',
108 'grouping' => 'contact-fields',
111 'title' => ts( 'Phone No' ),
118 'dao' => 'CRM_Core_DAO_Address',
121 'street_address' => NULL,
123 'postal_code' => NULL,
124 'state_province_id' =>
125 array('title' => ts('State/Province'),
128 array('title' => ts('Country'),
131 'grouping' => 'contact-fields',
133 'civicrm_participant' =>
135 'dao' => 'CRM_Event_DAO_Participant',
139 array('title' => ts('Participant ID'),
143 array('title' => ts('Event'),
144 'type' => CRM_Utils_Type
::T_STRING
,
147 array('title' => ts('Role'),
151 array('title' => ts('Status'),
154 'participant_register_date' =>
155 array('title' => ts('Registration Date'),
158 'grouping' => 'event-fields',
163 'name' => 'event_id',
164 'title' => ts('Event'),
165 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
166 'options' => $this->getEventFilterOptions(),
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'),
178 'title' => ts('Participant Role'),
179 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
180 'options' => CRM_Event_PseudoConstant
::participantRole(),
182 'participant_register_date' => array('title' => ts('Registration Date'),
183 'operatorType' => CRM_Report_Form
::OP_DATE
,
189 array('title' => ts('Event'),
195 'dao' => 'CRM_Event_DAO_Event',
199 array('title' => ts('Event Type'),
202 array('title' => ts('Event Start Date'),
205 array('title' => ts('Event End Date'),
208 'grouping' => 'event-fields',
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'),
217 'event_start_date' =>
219 'name' => 'start_date',
220 'title' => ts('Event Start Date'),
221 'operatorType' => CRM_Report_Form
::OP_DATE
,
225 'name' => 'end_date',
226 'title' => ts('Event End Date'),
227 'operatorType' => CRM_Report_Form
::OP_DATE
,
233 array('title' => ts('Event Type '),
237 'civicrm_line_item' =>
239 'dao' => 'CRM_Price_DAO_LineItem',
243 array('title' => ts('Income'),
246 array('sum' => ts('Amount'),
247 'avg' => ts('Average'),
250 'participant_count' =>
251 array('title' => ts('Count'),
254 array('sum' => ts('Count'),
261 $this->_options
= array(
262 'blank_column_begin' =>
263 array('title' => ts('Blank column at the Begining'),
264 'type' => 'checkbox',
266 'blank_column_end' =>
267 array('title' => ts('Blank column at the End'),
277 parent
::__construct();
280 function preProcess() {
281 parent
::preProcess();
285 function statistics(&$rows) {
287 $statistics = parent
::statistics($rows);
289 $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
290 SUM( {$this->_aliases['civicrm_line_item']}.line_total ) as amount
292 $sql = "{$select} {$this->_from} {$this->_where}";
293 $dao = CRM_Core_DAO
::executeQuery($sql);
296 if ($dao->count
&& $dao->amount
) {
297 $avg = $dao->amount
/ $dao->count
;
299 $statistics['counts']['count'] = array(
300 'value' => $dao->count
,
301 'title' => 'Total Participants',
302 'type' => CRM_Utils_Type
::T_INT
,
304 $statistics['counts']['amount'] = array(
305 'value' => $dao->amount
,
306 'title' => 'Total Income',
307 'type' => CRM_Utils_Type
::T_MONEY
,
309 $statistics['counts']['avg '] = array(
311 'title' => 'Average',
312 'type' => CRM_Utils_Type
::T_MONEY
,
321 $this->_columnHeaders
= array();
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'] = '_ _ _ _';
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']) ||
332 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
334 if (!empty($field['statistics'])) {
335 foreach ($field['statistics'] as $stat => $label) {
336 switch (strtolower($stat)) {
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}";
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'];
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'] = "_ _ _ _";
362 $this->_select
= "SELECT " . implode(', ', $select) . " ";
365 static function formRule($fields, $files, $self) {
366 $errors = $grouping = array();
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 )
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'";
395 foreach ($this->_columns
as $tableName => $table) {
396 if (array_key_exists('filters', $table)) {
397 foreach ($table['filters'] as $fieldName => $field) {
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
);
404 if ($relative ||
$from ||
$to) {
405 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
409 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
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) . "[[:>:]]' )";
420 $clause = $this->whereClause($field,
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
)
429 if (!empty($clause)) {
430 $clauses[] = $clause;
436 if (empty($clauses)) {
437 $this->_where
= "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 ";
440 $this->_where
= "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 AND " . implode(' AND ', $clauses);
442 if ($this->_aclWhere
) {
443 $this->_where
.= " AND {$this->_aclWhere} ";
448 $this->_groupBy
= "";
449 if (!empty($this->_params
['group_bys']) &&
450 is_array($this->_params
['group_bys']) &&
451 !empty($this->_params
['group_bys'])
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 (!empty($this->_params
['group_bys'][$fieldName])) {
457 $this->_groupBy
[] = $field['dbAlias'];
464 if (!empty($this->_groupBy
)) {
465 $this->_groupBy
= "ORDER BY " . implode(', ', $this->_groupBy
) . ", {$this->_aliases['civicrm_contact']}.sort_name";
468 $this->_groupBy
= "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name";
470 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_participant']}.id " . $this->_groupBy
;
473 function postProcess() {
475 // get ready with post process params
476 $this->beginPostProcess();
478 // get the acl clauses built before we assemble the query
479 $this->buildACLClause($this->_aliases
['civicrm_contact']);
481 $sql = $this->buildQuery(TRUE);
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);
487 // format result set.
488 $this->formatDisplay($rows);
490 // assign variables to templates
491 $this->doTemplateAssignment($rows);
493 // do print / pdf / instance stuff if needed
494 $this->endPostProcess($rows);
497 function alterDisplay(&$rows) {
500 $eventType = CRM_Core_OptionGroup
::values('event_type');
502 foreach ($rows as $rowNum => $row) {
504 // convert sort name to links
505 if (array_key_exists('civicrm_contact_sort_name', $row) &&
506 array_key_exists('civicrm_contact_id', $row)
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'],
513 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
514 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
519 // convert participant ID to links
520 if (array_key_exists('civicrm_participant_participant_id', $row) &&
521 array_key_exists('civicrm_contact_id', $row)
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',
528 $rows[$rowNum]['civicrm_participant_participant_id_link'] = $url;
529 $rows[$rowNum]['civicrm_participant_participant_id_hover'] = ts("View Participant Record for this Contact.");
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
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");
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];
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);
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);
569 foreach ($roles as $role) {
570 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
572 $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
578 // skip looking further in rows, if first row itself doesn't
579 // have the column we need