Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
8c9251b3 | 6 | | Copyright CiviCRM LLC (c) 2004-2018 | |
6a488035 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
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. | | |
13 | | | | |
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. | | |
18 | | | | |
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 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
8c9251b3 | 31 | * @copyright CiviCRM LLC (c) 2004-2018 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event { | |
7da04cde | 36 | const ROW_COUNT_LIMIT = 2; |
6a488035 TO |
37 | |
38 | protected $_summary = NULL; | |
39 | protected $_noFields = TRUE; | |
a5611c8e DL |
40 | |
41 | protected $_add2groupSupported = FALSE; | |
42 | ||
74cf4551 | 43 | /** |
d1c14698 | 44 | * Class constructor. |
74cf4551 | 45 | */ |
00be9182 | 46 | public function __construct() { |
6a488035 TO |
47 | |
48 | $this->_columns = array( | |
2107cde9 | 49 | 'civicrm_event' => array( |
6a488035 | 50 | 'dao' => 'CRM_Event_DAO_Event', |
2107cde9 CW |
51 | 'filters' => array( |
52 | 'id' => array( | |
53 | 'title' => ts('Event'), | |
54 | 'operatorType' => CRM_Report_Form::OP_ENTITYREF, | |
6a488035 | 55 | 'type' => CRM_Utils_Type::T_INT, |
2107cde9 | 56 | 'attributes' => array('select' => array('minimumInputLength' => 0)), |
6a488035 TO |
57 | ), |
58 | ), | |
59 | ), | |
60 | ); | |
a5611c8e | 61 | |
6a488035 TO |
62 | parent::__construct(); |
63 | } | |
64 | ||
00be9182 | 65 | public function preProcess() { |
6a488035 TO |
66 | $this->_csvSupported = FALSE; |
67 | parent::preProcess(); | |
68 | } | |
69 | ||
74cf4551 | 70 | /** |
d1c14698 EM |
71 | * Build event report. |
72 | * | |
73 | * @param array $eventIDs | |
74cf4551 | 74 | */ |
00be9182 | 75 | public function buildEventReport($eventIDs) { |
6a488035 TO |
76 | |
77 | $this->assign('events', $eventIDs); | |
78 | ||
79 | $eventID = implode(',', $eventIDs); | |
80 | ||
d05af386 | 81 | $participantStatus = CRM_Event_PseudoConstant::participantStatus(NULL, "is_counted = 1", "label"); |
353ffa53 | 82 | $participantRole = CRM_Event_PseudoConstant::participantRole(); |
6a488035 TO |
83 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); |
84 | ||
85 | $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array(); | |
86 | ||
87 | $optionGroupDAO = new CRM_Core_DAO_OptionGroup(); | |
88 | $optionGroupDAO->name = 'event_type'; | |
89 | $optionGroupId = NULL; | |
90 | if ($optionGroupDAO->find(TRUE)) { | |
91 | $optionGroupId = $optionGroupDAO->id; | |
92 | } | |
93 | //show the income of active participant status (Counted = filter = 1) | |
94 | $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array(); | |
95 | foreach ($participantStatus as $id => $label) { | |
96 | $activeParticipantStatusIDArray[] = $id; | |
97 | $activeParticipantStatusLabelArray[] = $label; | |
98 | } | |
99 | $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray); | |
100 | $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray); | |
101 | $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) "; | |
d1641c51 | 102 | $select = array( |
103 | "civicrm_event.id as event_id", | |
104 | "civicrm_event.title as event_title", | |
105 | "civicrm_event.max_participants as max_participants", | |
106 | "civicrm_event.start_date as start_date", | |
107 | "civicrm_event.end_date as end_date", | |
108 | "civicrm_option_value.label as event_type", | |
109 | "civicrm_participant.fee_currency as currency", | |
110 | ); | |
6a488035 | 111 | |
0848e4b1 | 112 | $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, 'civicrm_event.id'); |
6a488035 | 113 | $sql = " |
d1641c51 | 114 | SELECT " . implode(', ', $select) . ", |
6a488035 TO |
115 | SUM(civicrm_participant.fee_amount) as total, |
116 | COUNT(civicrm_participant.id) as participant | |
117 | ||
118 | FROM civicrm_event | |
a5611c8e | 119 | LEFT JOIN civicrm_option_value |
6a488035 TO |
120 | ON ( civicrm_event.event_type_id = civicrm_option_value.value AND |
121 | civicrm_option_value.option_group_id = {$optionGroupId} ) | |
a5611c8e | 122 | LEFT JOIN civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id |
6a488035 TO |
123 | {$activeParticipantClause} AND civicrm_participant.is_test = 0 ) |
124 | ||
0848e4b1 | 125 | WHERE civicrm_event.id IN( {$eventID}) {$groupBy}"; |
33207fc5 | 126 | |
5b0a2753 | 127 | $eventDAO = $this->executeReportQuery($sql); |
7baf6261 | 128 | $currency = array(); |
6a488035 | 129 | while ($eventDAO->fetch()) { |
d05af386 FBB |
130 | $eventSummary[$eventDAO->event_id][ts('Title')] = $eventDAO->event_title; |
131 | $eventSummary[$eventDAO->event_id][ts('Max Participants')] = $eventDAO->max_participants; | |
132 | $eventSummary[$eventDAO->event_id][ts('Start Date')] = CRM_Utils_Date::customFormat($eventDAO->start_date); | |
133 | $eventSummary[$eventDAO->event_id][ts('End Date')] = CRM_Utils_Date::customFormat($eventDAO->end_date); | |
134 | $eventSummary[$eventDAO->event_id][ts('Event Type')] = $eventDAO->event_type; | |
135 | $eventSummary[$eventDAO->event_id][ts('Event Income')] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency); | |
136 | $eventSummary[$eventDAO->event_id][ts('Registered Participant')] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})"; | |
7baf6261 | 137 | $currency[$eventDAO->event_id] = $eventDAO->currency; |
6a488035 TO |
138 | } |
139 | $this->assign_by_ref('summary', $eventSummary); | |
140 | ||
141 | //Total Participant Registerd for the Event | |
142 | $pariticipantCount = " | |
143 | SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id | |
144 | ||
145 | FROM civicrm_participant | |
146 | ||
a5611c8e DL |
147 | WHERE civicrm_participant.event_id IN( {$eventID}) AND |
148 | civicrm_participant.is_test = 0 | |
6a488035 TO |
149 | {$activeParticipantClause} |
150 | GROUP BY civicrm_participant.event_id | |
151 | "; | |
152 | ||
5b0a2753 | 153 | $counteDAO = $this->executeReportQuery($pariticipantCount); |
6a488035 TO |
154 | while ($counteDAO->fetch()) { |
155 | $count[$counteDAO->event_id] = $counteDAO->count; | |
156 | } | |
157 | ||
d1c14698 | 158 | // Count the Participant by Role ID for Event. |
6a488035 | 159 | $role = " |
a5611c8e DL |
160 | SELECT civicrm_participant.role_id as ROLEID, |
161 | COUNT( civicrm_participant.id ) as participant, | |
6a488035 | 162 | SUM(civicrm_participant.fee_amount) as amount, |
7baf6261 | 163 | civicrm_participant.event_id as event_id, |
164 | civicrm_participant.fee_currency as currency | |
6a488035 TO |
165 | FROM civicrm_participant |
166 | ||
167 | WHERE civicrm_participant.event_id IN ( {$eventID}) AND | |
a5611c8e | 168 | civicrm_participant.is_test = 0 |
6a488035 | 169 | {$activeParticipantClause} |
d1641c51 | 170 | GROUP BY civicrm_participant.role_id, civicrm_participant.event_id, civicrm_participant.fee_currency |
6a488035 TO |
171 | "; |
172 | ||
5b0a2753 | 173 | $roleDAO = $this->executeReportQuery($role); |
6a488035 TO |
174 | |
175 | while ($roleDAO->fetch()) { | |
176 | // fix for multiple role, CRM-6507 | |
177 | $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID); | |
178 | foreach ($roles as $roleId) { | |
179 | if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) { | |
180 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0; | |
181 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0; | |
182 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0; | |
183 | } | |
184 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant; | |
185 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount; | |
186 | } | |
187 | } | |
188 | ||
189 | foreach ($roleRows as $eventId => $roleInfo) { | |
190 | foreach ($participantRole as $roleName) { | |
191 | if (isset($roleInfo[$roleName])) { | |
192 | $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2); | |
193 | } | |
a38dd72d RK |
194 | if (!empty($roleRows[$eventId][$roleName])) { |
195 | $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]); | |
196 | } | |
6a488035 TO |
197 | } |
198 | } | |
2f4c2f5d | 199 | |
d05af386 | 200 | $rows[ts('Role')] = $roleRows; |
6a488035 | 201 | |
d1c14698 | 202 | // Count the Participant by status ID for Event. |
6a488035 | 203 | $status = " |
a5611c8e DL |
204 | SELECT civicrm_participant.status_id as STATUSID, |
205 | COUNT( civicrm_participant.id ) as participant, | |
6a488035 TO |
206 | SUM(civicrm_participant.fee_amount) as amount, |
207 | civicrm_participant.event_id as event_id | |
208 | ||
209 | FROM civicrm_participant | |
210 | ||
211 | WHERE civicrm_participant.event_id IN ({$eventID}) AND | |
a5611c8e | 212 | civicrm_participant.is_test = 0 |
6a488035 TO |
213 | {$activeParticipantClause} |
214 | GROUP BY civicrm_participant.status_id, civicrm_participant.event_id | |
215 | "; | |
216 | ||
5b0a2753 | 217 | $statusDAO = $this->executeReportQuery($status); |
6a488035 TO |
218 | |
219 | while ($statusDAO->fetch()) { | |
220 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant; | |
221 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round(($statusDAO->participant / $count[$statusDAO->event_id]) * 100, 2); | |
7baf6261 | 222 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]); |
6a488035 TO |
223 | } |
224 | ||
d05af386 | 225 | $rows[ts('Status')] = $statusRows; |
6a488035 TO |
226 | |
227 | //Count the Participant by payment instrument ID for Event | |
228 | //e.g. Credit Card, Check,Cash etc | |
229 | $paymentInstrument = " | |
a5611c8e | 230 | SELECT c.payment_instrument_id as INSTRUMENT, |
9341d544 DL |
231 | COUNT( civicrm_participant.id ) as participant, |
232 | SUM( civicrm_participant.fee_amount ) as amount, | |
6a488035 TO |
233 | civicrm_participant.event_id as event_id |
234 | ||
9341d544 DL |
235 | FROM civicrm_participant, |
236 | civicrm_participant_payment pp | |
6a488035 TO |
237 | LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id) |
238 | ||
9341d544 DL |
239 | WHERE civicrm_participant.event_id IN ( {$eventID} ) |
240 | AND civicrm_participant.is_test = 0 | |
6a488035 | 241 | {$activeParticipantClause} |
9341d544 DL |
242 | AND ((pp.participant_id = civicrm_participant.id ) |
243 | OR (pp.participant_id = civicrm_participant.registered_by_id )) | |
6a488035 TO |
244 | GROUP BY c.payment_instrument_id, civicrm_participant.event_id |
245 | "; | |
246 | ||
5b0a2753 | 247 | $instrumentDAO = $this->executeReportQuery($paymentInstrument); |
6a488035 TO |
248 | |
249 | while ($instrumentDAO->fetch()) { | |
250 | //allow only if instrument is present in contribution table | |
251 | if ($instrumentDAO->INSTRUMENT) { | |
252 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant; | |
253 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round(($instrumentDAO->participant / $count[$instrumentDAO->event_id]) * 100, 2); | |
9341d544 | 254 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = CRM_Utils_Money::format($instrumentDAO->amount, $currency[$instrumentDAO->event_id]); |
6a488035 TO |
255 | } |
256 | } | |
d05af386 | 257 | $rows[ts('Payment Method')] = $instrumentRows; |
6a488035 TO |
258 | |
259 | $this->assign_by_ref('rows', $rows); | |
260 | if (!$this->_setVariable) { | |
261 | $this->_params['id_value'] = NULL; | |
262 | } | |
263 | $this->assign('statistics', $this->statistics($eventIDs)); | |
264 | } | |
265 | ||
74cf4551 EM |
266 | /** |
267 | * @param $eventIDs | |
268 | * | |
269 | * @return array | |
270 | */ | |
00be9182 | 271 | public function statistics(&$eventIDs) { |
6a488035 TO |
272 | $statistics = array(); |
273 | $count = count($eventIDs); | |
274 | $this->countStat($statistics, $count); | |
275 | if ($this->_setVariable) { | |
276 | $this->filterStat($statistics); | |
277 | } | |
278 | ||
279 | return $statistics; | |
280 | } | |
281 | ||
74cf4551 | 282 | /** |
3dde2c6c | 283 | * @inheritDoc |
74cf4551 | 284 | */ |
00be9182 | 285 | public function limit($rowCount = self::ROW_COUNT_LIMIT) { |
6a488035 TO |
286 | parent::limit($rowCount); |
287 | ||
d1c14698 | 288 | // Modify limit. |
6a488035 TO |
289 | $pageId = $this->get(CRM_Utils_Pager::PAGE_ID); |
290 | ||
d1c14698 | 291 | //if pageId is greater than last page then display last page. |
6a488035 | 292 | if ((($pageId * self::ROW_COUNT_LIMIT) - 1) > $this->_rowsFound) { |
84178120 | 293 | $pageId = ceil((float) $this->_rowsFound / (float) self::ROW_COUNT_LIMIT); |
6a488035 TO |
294 | $this->set(CRM_Utils_Pager::PAGE_ID, $pageId); |
295 | } | |
296 | $this->_limit = ($pageId - 1) * self::ROW_COUNT_LIMIT; | |
297 | } | |
298 | ||
74cf4551 EM |
299 | /** |
300 | * @param int $rowCount | |
301 | */ | |
00be9182 | 302 | public function setPager($rowCount = self::ROW_COUNT_LIMIT) { |
6a488035 TO |
303 | $params = array( |
304 | 'total' => $this->_rowsFound, | |
305 | 'rowCount' => self::ROW_COUNT_LIMIT, | |
306 | 'status' => ts('Records %%StatusMessage%%'), | |
307 | 'buttonBottom' => 'PagerBottomButton', | |
308 | 'buttonTop' => 'PagerTopButton', | |
309 | 'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID), | |
310 | ); | |
311 | ||
312 | $pager = new CRM_Utils_Pager($params); | |
313 | $this->assign_by_ref('pager', $pager); | |
314 | } | |
315 | ||
ced9bfed EM |
316 | /** |
317 | * Form post process function. | |
318 | * | |
319 | * @return bool | |
320 | */ | |
00be9182 | 321 | public function postProcess() { |
6a488035 TO |
322 | $this->beginPostProcess(); |
323 | $this->_setVariable = TRUE; | |
324 | ||
325 | $noSelection = FALSE; | |
2107cde9 | 326 | if (empty($this->_params['id_value'])) { |
6a488035 TO |
327 | $this->_params['id_value'] = array(); |
328 | $this->_setVariable = FALSE; | |
329 | ||
330 | $events = CRM_Event_PseudoConstant::event(NULL, NULL, | |
99f13d8d | 331 | "is_template = 0" |
6a488035 TO |
332 | ); |
333 | if (empty($events)) { | |
334 | return FALSE; | |
335 | } | |
336 | foreach ($events as $key => $dnt) { | |
337 | $this->_params['id_value'][] = $key; | |
338 | } | |
339 | $noSelection = TRUE; | |
a5611c8e | 340 | } |
7c55abd7 BS |
341 | elseif (!is_array($this->_params['id_value'])) { |
342 | $this->_params['id_value'] = explode(',', $this->_params['id_value']); | |
343 | } | |
a5611c8e | 344 | |
6a488035 TO |
345 | $this->_rowsFound = count($this->_params['id_value']); |
346 | ||
347 | //set pager and limit if output mode is html | |
348 | if ($this->_outputMode == 'html') { | |
349 | $this->limit(); | |
350 | $this->setPager(); | |
a5611c8e | 351 | |
6a488035 | 352 | $showEvents = array(); |
353ffa53 TO |
353 | $count = 0; |
354 | $numRows = $this->_limit; | |
6a488035 TO |
355 | |
356 | if (CRM_Utils_Array::value('id_op', $this->_params, 'in') == 'in' || $noSelection) { | |
357 | while ($count < self::ROW_COUNT_LIMIT) { | |
358 | if (!isset($this->_params['id_value'][$numRows])) { | |
359 | break; | |
360 | } | |
a5611c8e | 361 | |
6a488035 TO |
362 | $showEvents[] = $this->_params['id_value'][$numRows]; |
363 | $count++; | |
364 | $numRows++; | |
365 | } | |
0db6c3e1 TO |
366 | } |
367 | elseif ($this->_params['id_op'] == 'notin') { | |
6a488035 | 368 | $events = CRM_Event_PseudoConstant::event(NULL, NULL, |
99f13d8d | 369 | "is_template = 0" |
6a488035 | 370 | ); |
a5611c8e | 371 | |
6a488035 TO |
372 | $showEvents = array_diff(array_keys($events), $this->_params['id_value']); |
373 | } | |
374 | ||
375 | $this->buildEventReport($showEvents); | |
a5611c8e | 376 | |
6a488035 TO |
377 | } |
378 | else { | |
379 | $this->buildEventReport($this->_params['id_value']); | |
380 | } | |
381 | ||
382 | parent::endPostProcess(); | |
383 | } | |
96025800 | 384 | |
6a488035 | 385 | } |