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