Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 | 16 | */ |
70d00fce | 17 | class CRM_Report_Form_Event_Summary extends CRM_Report_Form { |
6a488035 TO |
18 | |
19 | protected $_summary = NULL; | |
20 | ||
be2fb01f | 21 | protected $_charts = [ |
6a488035 TO |
22 | '' => 'Tabular', |
23 | 'barChart' => 'Bar Chart', | |
24 | 'pieChart' => 'Pie Chart', | |
be2fb01f | 25 | ]; |
6a488035 TO |
26 | |
27 | protected $_add2groupSupported = FALSE; | |
28 | ||
be2fb01f | 29 | protected $_customGroupExtends = [ |
21dfd5f5 | 30 | 'Event', |
be2fb01f CW |
31 | ]; |
32 | public $_drilldownReport = ['event/income' => 'Link to Detail Report']; | |
2f4c2f5d | 33 | |
74cf4551 | 34 | /** |
73b448bf | 35 | * Class constructor. |
74cf4551 | 36 | */ |
00be9182 | 37 | public function __construct() { |
6a488035 | 38 | |
be2fb01f CW |
39 | $this->_columns = [ |
40 | 'civicrm_event' => [ | |
6a488035 | 41 | 'dao' => 'CRM_Event_DAO_Event', |
be2fb01f CW |
42 | 'fields' => [ |
43 | 'id' => [ | |
6a488035 TO |
44 | 'no_display' => TRUE, |
45 | 'required' => TRUE, | |
be2fb01f CW |
46 | ], |
47 | 'title' => [ | |
9d72cede | 48 | 'title' => ts('Event Title'), |
6a488035 | 49 | 'required' => TRUE, |
be2fb01f CW |
50 | ], |
51 | 'event_type_id' => [ | |
9d72cede | 52 | 'title' => ts('Event Type'), |
6a488035 | 53 | 'required' => TRUE, |
be2fb01f CW |
54 | ], |
55 | 'fee_label' => ['title' => ts('Fee Label')], | |
56 | 'event_start_date' => [ | |
9d72cede | 57 | 'title' => ts('Event Start Date'), |
be2fb01f CW |
58 | ], |
59 | 'event_end_date' => ['title' => ts('Event End Date')], | |
60 | 'max_participants' => [ | |
9d72cede | 61 | 'title' => ts('Capacity'), |
6a488035 | 62 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
63 | ], |
64 | ], | |
65 | 'filters' => [ | |
66 | 'id' => [ | |
2107cde9 CW |
67 | 'title' => ts('Event'), |
68 | 'operatorType' => CRM_Report_Form::OP_ENTITYREF, | |
69 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f CW |
70 | 'attributes' => ['select' => ['minimumInputLength' => 0]], |
71 | ], | |
72 | 'event_type_id' => [ | |
6a488035 TO |
73 | 'name' => 'event_type_id', |
74 | 'title' => ts('Event Type'), | |
525ae77a | 75 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
76 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
77 | 'options' => CRM_Core_OptionGroup::values('event_type'), | |
be2fb01f CW |
78 | ], |
79 | 'event_start_date' => [ | |
fd6a6828 | 80 | 'title' => ts('Event Start Date'), |
6a488035 | 81 | 'operatorType' => CRM_Report_Form::OP_DATE, |
be2fb01f CW |
82 | ], |
83 | 'event_end_date' => [ | |
fd6a6828 | 84 | 'title' => ts('Event End Date'), |
6a488035 | 85 | 'operatorType' => CRM_Report_Form::OP_DATE, |
be2fb01f CW |
86 | ], |
87 | ], | |
88 | ], | |
89 | ]; | |
9bf1940a | 90 | $this->_currencyColumn = 'civicrm_participant_fee_currency'; |
6a488035 TO |
91 | parent::__construct(); |
92 | } | |
93 | ||
00be9182 | 94 | public function preProcess() { |
6a488035 TO |
95 | parent::preProcess(); |
96 | } | |
97 | ||
00be9182 | 98 | public function select() { |
be2fb01f | 99 | $select = []; |
6a488035 TO |
100 | foreach ($this->_columns as $tableName => $table) { |
101 | if (array_key_exists('fields', $table)) { | |
102 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
103 | if (!empty($field['required']) || |
104 | !empty($this->_params['fields'][$fieldName]) | |
105 | ) { | |
6a488035 TO |
106 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
107 | } | |
108 | } | |
109 | } | |
110 | } | |
111 | ||
d1641c51 | 112 | $this->_selectClauses = $select; |
1b06955f | 113 | $this->_select = 'SELECT ' . implode(', ', $select); |
6a488035 TO |
114 | } |
115 | ||
00be9182 | 116 | public function from() { |
6a488035 TO |
117 | $this->_from = " FROM civicrm_event {$this->_aliases['civicrm_event']} "; |
118 | } | |
119 | ||
00be9182 | 120 | public function where() { |
be2fb01f | 121 | $clauses = []; |
6a488035 TO |
122 | $this->_participantWhere = ""; |
123 | foreach ($this->_columns as $tableName => $table) { | |
124 | if (array_key_exists('filters', $table)) { | |
125 | foreach ($table['filters'] as $fieldName => $field) { | |
126 | $clause = NULL; | |
127 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
128 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
129 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
130 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 TO |
131 | |
132 | if ($relative || $from || $to) { | |
133 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
134 | } | |
135 | } | |
136 | else { | |
9c1bc317 | 137 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
138 | if ($op) { |
139 | $clause = $this->whereClause($field, | |
140 | $op, | |
141 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
142 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
143 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
144 | ); | |
145 | } | |
146 | } | |
147 | if (!empty($this->_params['id_value'])) { | |
77f60b00 FG |
148 | $idValue = is_array($this->_params['id_value']) ? implode(',', $this->_params['id_value']) : $this->_params['id_value']; |
149 | $this->_participantWhere = " AND civicrm_participant.event_id IN ( $idValue ) "; | |
6a488035 TO |
150 | } |
151 | ||
152 | if (!empty($clause)) { | |
153 | $clauses[] = $clause; | |
154 | } | |
155 | } | |
156 | } | |
157 | } | |
99f13d8d | 158 | $clauses[] = "{$this->_aliases['civicrm_event']}.is_template = 0"; |
1b06955f | 159 | $this->_where = 'WHERE ' . implode(' AND ', $clauses); |
6a488035 TO |
160 | } |
161 | ||
00be9182 | 162 | public function groupBy() { |
6a488035 | 163 | $this->assign('chartSupported', TRUE); |
b708c08d | 164 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id"); |
6a488035 TO |
165 | } |
166 | ||
74cf4551 | 167 | /** |
fe482240 | 168 | * get participants information for events. |
74cf4551 EM |
169 | * @return array |
170 | */ | |
00be9182 | 171 | public function participantInfo() { |
6a488035 | 172 | |
1b06955f | 173 | $statusType1 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 1'); |
174 | $statusType2 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 0'); | |
6a488035 TO |
175 | |
176 | $sql = " | |
2f4c2f5d | 177 | SELECT civicrm_participant.event_id AS event_id, |
178 | civicrm_participant.status_id AS statusId, | |
179 | COUNT( civicrm_participant.id ) AS participant, | |
1b06955f | 180 | SUM( civicrm_participant.fee_amount ) AS amount, |
181 | civicrm_participant.fee_currency | |
6a488035 TO |
182 | |
183 | FROM civicrm_participant | |
184 | ||
2f4c2f5d | 185 | WHERE civicrm_participant.is_test = 0 |
6a488035 TO |
186 | $this->_participantWhere |
187 | ||
2f4c2f5d | 188 | GROUP BY civicrm_participant.event_id, |
ec799044 J |
189 | civicrm_participant.status_id, |
190 | civicrm_participant.fee_currency"; | |
6a488035 TO |
191 | |
192 | $info = CRM_Core_DAO::executeQuery($sql); | |
be2fb01f | 193 | $participant_data = $participant_info = $currency = []; |
6a488035 TO |
194 | |
195 | while ($info->fetch()) { | |
196 | $participant_data[$info->event_id][$info->statusId]['participant'] = $info->participant; | |
197 | $participant_data[$info->event_id][$info->statusId]['amount'] = $info->amount; | |
1b06955f | 198 | $currency[$info->event_id] = $info->fee_currency; |
6a488035 TO |
199 | } |
200 | ||
201 | $amt = $particiType1 = $particiType2 = 0; | |
202 | ||
203 | foreach ($participant_data as $event_id => $event_data) { | |
204 | foreach ($event_data as $status_id => $data) { | |
205 | ||
206 | if (array_key_exists($status_id, $statusType1)) { | |
207 | //total income of event | |
208 | $amt = $amt + $data['amount']; | |
209 | ||
210 | //number of Registered/Attended participants | |
211 | $particiType1 = $particiType1 + $data['participant']; | |
212 | } | |
213 | elseif (array_key_exists($status_id, $statusType2)) { | |
214 | ||
215 | //number of No-show/Cancelled/Pending participants | |
216 | $particiType2 = $particiType2 + $data['participant']; | |
217 | } | |
218 | } | |
219 | ||
2437f726 | 220 | $participant_info[$event_id]['totalAmount'] = $amt; |
6a488035 TO |
221 | $participant_info[$event_id]['statusType1'] = $particiType1; |
222 | $participant_info[$event_id]['statusType2'] = $particiType2; | |
9d72cede | 223 | $participant_info[$event_id]['currency'] = $currency[$event_id]; |
6a488035 TO |
224 | $amt = $particiType1 = $particiType2 = 0; |
225 | } | |
226 | ||
227 | return $participant_info; | |
228 | } | |
229 | ||
795492f3 TO |
230 | /** |
231 | * Build header for table. | |
232 | */ | |
00be9182 | 233 | public function buildColumnHeaders() { |
be2fb01f | 234 | $this->_columnHeaders = []; |
6a488035 TO |
235 | foreach ($this->_columns as $tableName => $table) { |
236 | if (array_key_exists('fields', $table)) { | |
237 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
238 | if (!empty($field['required']) || |
239 | !empty($this->_params['fields'][$fieldName]) | |
240 | ) { | |
6a488035 | 241 | |
9c1bc317 CW |
242 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
243 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL; | |
6a488035 TO |
244 | } |
245 | } | |
246 | } | |
247 | } | |
248 | ||
65fb0127 | 249 | $statusType1 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 1', 'label'); |
250 | $statusType2 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 0', 'label'); | |
6a488035 TO |
251 | |
252 | //make column header for participant status Registered/Attended | |
253 | $type1_header = implode('/', $statusType1); | |
254 | ||
255 | //make column header for participant status No-show/Cancelled/Pending | |
256 | $type2_header = implode('/', $statusType2); | |
257 | ||
be2fb01f | 258 | $this->_columnHeaders['statusType1'] = [ |
6a488035 TO |
259 | 'title' => $type1_header, |
260 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f CW |
261 | ]; |
262 | $this->_columnHeaders['statusType2'] = [ | |
6a488035 TO |
263 | 'title' => $type2_header, |
264 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f CW |
265 | ]; |
266 | $this->_columnHeaders['totalAmount'] = [ | |
ccc29f8e | 267 | 'title' => ts('Total Income'), |
1b06955f | 268 | 'type' => CRM_Utils_Type::T_STRING, |
be2fb01f | 269 | ]; |
6a488035 TO |
270 | } |
271 | ||
00be9182 | 272 | public function postProcess() { |
6a488035 TO |
273 | |
274 | $this->beginPostProcess(); | |
275 | ||
276 | $this->buildColumnHeaders(); | |
277 | ||
278 | $sql = $this->buildQuery(TRUE); | |
279 | ||
280 | $dao = CRM_Core_DAO::executeQuery($sql); | |
281 | ||
282 | //set pager before exicution of query in function participantInfo() | |
283 | $this->setPager(); | |
284 | ||
be2fb01f | 285 | $rows = $graphRows = []; |
6a488035 TO |
286 | $count = 0; |
287 | while ($dao->fetch()) { | |
be2fb01f | 288 | $row = []; |
6a488035 | 289 | foreach ($this->_columnHeaders as $key => $value) { |
9d72cede EM |
290 | if (($key == 'civicrm_event_start_date') || |
291 | ($key == 'civicrm_event_end_date') | |
292 | ) { | |
6a488035 TO |
293 | //get event start date and end date in custom datetime format |
294 | $row[$key] = CRM_Utils_Date::customFormat($dao->$key); | |
295 | } | |
296 | else { | |
297 | if (isset($dao->$key)) { | |
298 | $row[$key] = $dao->$key; | |
299 | } | |
300 | } | |
301 | } | |
302 | $rows[] = $row; | |
303 | } | |
304 | if (!empty($rows)) { | |
305 | $participant_info = $this->participantInfo(); | |
306 | foreach ($rows as $key => $value) { | |
307 | if (array_key_exists($value['civicrm_event_id'], $participant_info)) { | |
308 | foreach ($participant_info[$value['civicrm_event_id']] as $k => $v) { | |
309 | $rows[$key][$k] = $v; | |
310 | } | |
311 | } | |
312 | } | |
313 | } | |
314 | // do not call pager here | |
315 | $this->formatDisplay($rows, FALSE); | |
316 | unset($this->_columnHeaders['civicrm_event_id']); | |
317 | ||
318 | $this->doTemplateAssignment($rows); | |
319 | ||
320 | $this->endPostProcess($rows); | |
321 | } | |
322 | ||
74cf4551 EM |
323 | /** |
324 | * @param $rows | |
325 | */ | |
00be9182 | 326 | public function buildChart(&$rows) { |
6a488035 TO |
327 | $this->_interval = 'events'; |
328 | $countEvent = NULL; | |
a7488080 | 329 | if (!empty($this->_params['charts'])) { |
6a488035 | 330 | foreach ($rows as $key => $value) { |
9c1bc317 | 331 | $graphRows['totalAmount'][] = $graphRows['value'][] = $rows[$key]['totalAmount'] ?? NULL; |
795492f3 | 332 | $graphRows[$this->_interval][] = substr($rows[$key]['civicrm_event_title'], 0, 12) . "..(" . |
9d72cede | 333 | $rows[$key]['civicrm_event_id'] . ") "; |
6a488035 TO |
334 | } |
335 | ||
de6c59ca | 336 | if (empty($rows[$key]['totalAmount'])) { |
6a488035 TO |
337 | $countEvent = count($rows); |
338 | } | |
339 | ||
340 | if ((!empty($rows)) && $countEvent != 1) { | |
341 | $config = CRM_Core_Config::Singleton(); | |
be2fb01f | 342 | $chartInfo = [ |
e60e0c01 | 343 | 'legend' => ts('Event Summary'), |
344 | 'xname' => ts('Event'), | |
be2fb01f CW |
345 | 'yname' => ts('Total Amount (%1)', [1 => $config->defaultCurrency]), |
346 | ]; | |
6a488035 TO |
347 | if (!empty($graphRows)) { |
348 | foreach ($graphRows[$this->_interval] as $key => $val) { | |
349 | $graph[$val] = $graphRows['value'][$key]; | |
350 | } | |
351 | $chartInfo['values'] = $graph; | |
352 | $chartInfo['xLabelAngle'] = 20; | |
353 | ||
354 | // build the chart. | |
dc61ee93 | 355 | CRM_Utils_Chart::buildChart($chartInfo, $this->_params['charts']); |
6a488035 TO |
356 | $this->assign('chartType', $this->_params['charts']); |
357 | } | |
358 | } | |
359 | } | |
360 | } | |
361 | ||
74cf4551 | 362 | /** |
ced9bfed EM |
363 | * Alter display of rows. |
364 | * | |
365 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
366 | * such as rendering contacts as links. | |
367 | * | |
368 | * @param array $rows | |
369 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 370 | */ |
00be9182 | 371 | public function alterDisplay(&$rows) { |
6a488035 TO |
372 | |
373 | if (is_array($rows)) { | |
374 | $eventType = CRM_Core_OptionGroup::values('event_type'); | |
6a488035 | 375 | |
2437f726 | 376 | foreach ($rows as $rowNum => $row) { |
9d72cede EM |
377 | if (array_key_exists('totalAmount', $row) && |
378 | array_key_exists('currency', $row) | |
379 | ) { | |
2437f726 PJ |
380 | $rows[$rowNum]['totalAmount'] = CRM_Utils_Money::format($rows[$rowNum]['totalAmount'], $rows[$rowNum]['currency']); |
381 | } | |
6a488035 TO |
382 | if (array_key_exists('civicrm_event_title', $row)) { |
383 | if ($value = $row['civicrm_event_id']) { | |
384 | //CRM_Event_PseudoConstant::event( $value, false ); | |
385 | $url = CRM_Report_Utils_Report::getNextUrl('event/income', | |
386 | 'reset=1&force=1&id_op=in&id_value=' . $value, | |
387 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
388 | ); | |
389 | $rows[$rowNum]['civicrm_event_title_link'] = $url; | |
1b06955f | 390 | $rows[$rowNum]['civicrm_event_title_hover'] = ts('View Event Income For this Event'); |
6a488035 TO |
391 | } |
392 | } | |
393 | ||
394 | //handle event type | |
395 | if (array_key_exists('civicrm_event_event_type_id', $row)) { | |
396 | if ($value = $row['civicrm_event_event_type_id']) { | |
397 | $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value]; | |
398 | } | |
399 | } | |
400 | } | |
401 | } | |
402 | } | |
96025800 | 403 | |
6a488035 | 404 | } |