3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
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-2018
33 class CRM_Report_Form_Event_IncomeCountSummary
extends CRM_Report_Form_Event
{
35 protected $_summary = NULL;
37 protected $_charts = array(
39 'barChart' => 'Bar Chart',
40 'pieChart' => 'Pie Chart',
43 protected $_add2groupSupported = FALSE;
45 protected $_customGroupExtends = array(
49 public $_drilldownReport = array('event/participantlist' => 'Link to Detail Report');
54 public function __construct() {
56 $this->_columns
= array(
57 'civicrm_event' => array(
58 'dao' => 'CRM_Event_DAO_Event',
61 'title' => ts('Event'),
65 'title' => ts('Event ID'),
69 'event_type_id' => array(
70 'title' => ts('Event Type'),
73 'title' => ts('Fee Label'),
75 'event_start_date' => array(
76 'title' => ts('Event Start Date'),
78 'event_end_date' => array(
79 'title' => ts('Event End Date'),
81 'max_participants' => array(
82 'title' => ts('Capacity'),
83 'type' => CRM_Utils_Type
::T_INT
,
88 'title' => ts('Event'),
89 'operatorType' => CRM_Report_Form
::OP_ENTITYREF
,
90 'type' => CRM_Utils_Type
::T_INT
,
91 'attributes' => array('select' => array('minimumInputLength' => 0)),
93 'event_type_id' => array(
94 'name' => 'event_type_id',
95 'title' => ts('Event Type'),
96 'type' => CRM_Utils_Type
::T_INT
,
97 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
98 'options' => CRM_Core_OptionGroup
::values('event_type'),
100 'event_start_date' => array(
101 'title' => ts('Event Start Date'),
102 'operatorType' => CRM_Report_Form
::OP_DATE
,
104 'event_end_date' => array(
105 'title' => ts('Event End Date'),
106 'operatorType' => CRM_Report_Form
::OP_DATE
,
110 'civicrm_line_item' => array(
111 'dao' => 'CRM_Price_DAO_LineItem',
113 'participant_count' => array(
114 'title' => ts('Participants'),
116 'statistics' => array(
117 'count' => ts('Participants'),
120 'line_total' => array(
121 'title' => ts('Income Statistics'),
122 'type' => CRM_Utils_Type
::T_MONEY
,
124 'statistics' => array(
125 'sum' => ts('Income'),
126 'avg' => ts('Average'),
131 'civicrm_participant' => array(
132 'dao' => 'CRM_Event_DAO_Participant',
135 'name' => 'status_id',
136 'title' => ts('Participant Status'),
137 'type' => CRM_Utils_Type
::T_INT
,
138 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
139 'options' => CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, "label"),
143 'title' => ts('Participant Role'),
144 'type' => CRM_Utils_Type
::T_INT
,
145 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
146 'options' => CRM_Event_PseudoConstant
::participantRole(),
148 'participant_register_date' => array(
149 'title' => ts('Registration Date'),
150 'operatorType' => CRM_Report_Form
::OP_DATE
,
155 parent
::__construct();
158 public function preProcess() {
159 parent
::preProcess();
162 public function select() {
164 foreach ($this->_columns
as $tableName => $table) {
165 if (array_key_exists('fields', $table)) {
166 foreach ($table['fields'] as $fieldName => $field) {
167 if (!empty($field['required']) ||
168 !empty($this->_params
['fields'][$fieldName])
170 if (!empty($field['statistics'])) {
171 foreach ($field['statistics'] as $stat => $label) {
172 switch (strtolower($stat)) {
174 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
175 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
176 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
177 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
181 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
182 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_MONEY
;
184 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
185 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
189 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
191 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_MONEY
;
192 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
197 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
198 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
199 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
205 $this->_selectClauses
= $select;
207 $this->_select
= "SELECT " . implode(', ', $select);
210 public function from() {
212 FROM civicrm_event {$this->_aliases['civicrm_event']}
213 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
214 ON {$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id AND
215 {$this->_aliases['civicrm_participant']}.is_test = 0
216 LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
217 ON {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id AND
218 {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' ";
221 public function where() {
223 $this->_participantWhere
= "";
224 foreach ($this->_columns
as $tableName => $table) {
225 if (array_key_exists('filters', $table)) {
226 foreach ($table['filters'] as $fieldName => $field) {
228 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
229 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
230 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
231 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
233 if ($relative ||
$from ||
$to) {
234 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
238 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
240 $clause = $this->whereClause($field,
242 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
243 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
244 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
248 if (!empty($this->_params
['id_value'])) {
249 $idValue = is_array($this->_params
['id_value']) ?
implode(',', $this->_params
['id_value']) : $this->_params
['id_value'];
250 $this->_participantWhere
= " AND civicrm_participant.event_id IN ( $idValue ) ";
253 if (!empty($clause)) {
254 $clauses[] = $clause;
259 $clauses[] = "{$this->_aliases['civicrm_event']}.is_template = 0";
260 $this->_where
= "WHERE " . implode(' AND ', $clauses);
268 public function statistics(&$rows) {
269 $statistics = parent
::statistics($rows);
271 SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
272 SUM( {$this->_aliases['civicrm_line_item']}.line_total ) as amount";
274 $sql = "{$select} {$this->_from} {$this->_where}";
276 $dao = CRM_Core_DAO
::executeQuery($sql);
280 if ($dao->count
&& $dao->amount
) {
281 $avg = $dao->amount
/ $dao->count
;
283 $statistics['counts']['count'] = array(
284 'value' => $dao->count
,
285 'title' => ts('Total Participants'),
286 'type' => CRM_Utils_Type
::T_INT
,
288 $statistics['counts']['amount'] = array(
289 'value' => $dao->amount
,
290 'title' => ts('Total Income'),
291 'type' => CRM_Utils_Type
::T_MONEY
,
293 $statistics['counts']['avg'] = array(
295 'title' => ts('Average'),
296 'type' => CRM_Utils_Type
::T_MONEY
,
302 public function groupBy() {
303 $this->assign('chartSupported', TRUE);
304 $this->_rollup
= " WITH ROLLUP";
305 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($this->_selectClauses
, "{$this->_aliases['civicrm_event']}.id");
306 $this->_groupBy
= " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}";
309 public function postProcess() {
311 $this->beginPostProcess();
313 $sql = $this->buildQuery(TRUE);
315 $dao = CRM_Core_DAO
::executeQuery($sql);
317 //set pager before execution of query in function participantInfo()
320 $rows = $graphRows = array();
323 while ($dao->fetch()) {
325 foreach ($this->_columnHeaders
as $key => $value) {
326 if (($key == 'civicrm_event_start_date') ||
327 ($key == 'civicrm_event_end_date')
329 //get event start date and end date in custom datetime format
330 $row[$key] = CRM_Utils_Date
::customFormat($dao->$key);
332 elseif ($key == 'civicrm_participant_fee_amount_avg') {
333 if ($dao->civicrm_participant_fee_amount_sum
&&
334 $dao->civicrm_line_item_participant_count_count
336 $row[$key] = $dao->civicrm_participant_fee_amount_sum
/
337 $dao->civicrm_line_item_participant_count_count
;
340 elseif ($key == 'civicrm_line_item_line_total_avg') {
341 if ($dao->civicrm_line_item_line_total_sum
&&
342 $dao->civicrm_line_item_participant_count_count
344 $row[$key] = $dao->civicrm_line_item_line_total_sum
/
345 $dao->civicrm_line_item_participant_count_count
;
349 if (isset($dao->$key)) {
350 $row[$key] = $dao->$key;
357 // do not call pager here
358 $this->formatDisplay($rows, FALSE);
359 unset($this->_columnHeaders
['civicrm_event_id']);
361 $this->doTemplateAssignment($rows);
363 $this->endPostProcess($rows);
369 public function buildChart(&$rows) {
371 $this->_interval
= 'events';
373 if (!empty($this->_params
['charts'])) {
374 foreach ($rows as $key => $value) {
375 if ($value['civicrm_event_id']) {
376 $graphRows['totalParticipants'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
377 $graphRows[$this->_interval
][] = substr($rows[$key]['civicrm_event_title'], 0, 12) . "..(" .
378 $rows[$key]['civicrm_event_id'] . ") ";
379 $graphRows['value'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
383 if (($rows[$key]['civicrm_line_item_participant_count_count']) == 0) {
384 $countEvent = count($rows);
387 if ((!empty($rows)) && $countEvent != 1) {
389 'legend' => ts('Participants Summary'),
390 'xname' => ts('Event'),
391 'yname' => ts('Total Participants'),
393 if (!empty($graphRows)) {
394 foreach ($graphRows[$this->_interval
] as $key => $val) {
395 $graph[$val] = $graphRows['value'][$key];
397 $chartInfo['values'] = $graph;
398 $chartInfo['tip'] = ts('Participants : %1', array(1 => '#val#'));
399 $chartInfo['xLabelAngle'] = 20;
402 CRM_Utils_OpenFlashChart
::buildChart($chartInfo, $this->_params
['charts']);
409 * Alter display of rows.
411 * Iterate through the rows retrieved via SQL and make changes for display purposes,
412 * such as rendering contacts as links.
415 * Rows generated by SQL, with an array for each row.
417 public function alterDisplay(&$rows) {
419 if (is_array($rows)) {
420 $eventType = CRM_Core_OptionGroup
::values('event_type');
422 foreach ($rows as $rowNum => $row) {
423 if (array_key_exists('civicrm_event_title', $row)) {
424 if ($value = $row['civicrm_event_id']) {
425 CRM_Event_PseudoConstant
::event($value, FALSE);
426 $url = CRM_Report_Utils_Report
::getNextUrl('event/participantlist',
427 'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
428 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
430 $rows[$rowNum]['civicrm_event_title_link'] = $url;
431 $rows[$rowNum]['civicrm_event_title_hover'] = ts("View Event Participants For this Event");
436 if (array_key_exists('civicrm_event_event_type_id', $row)) {
437 if ($value = $row['civicrm_event_event_type_id']) {
438 $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];