Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | // $Id$ | |
3 | ||
4 | /* | |
5 | +--------------------------------------------------------------------+ | |
6 | | CiviCRM version 4.3 | | |
7 | +--------------------------------------------------------------------+ | |
8 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
9 | +--------------------------------------------------------------------+ | |
10 | | This file is a part of CiviCRM. | | |
11 | | | | |
12 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
13 | | under the terms of the GNU Affero General Public License | | |
14 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
15 | | | | |
16 | | CiviCRM is distributed in the hope that it will be useful, but | | |
17 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
18 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
19 | | See the GNU Affero General Public License for more details. | | |
20 | | | | |
21 | | You should have received a copy of the GNU Affero General Public | | |
22 | | License and the CiviCRM Licensing Exception along | | |
23 | | with this program; if not, contact CiviCRM LLC | | |
24 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
25 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
26 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
27 | +--------------------------------------------------------------------+ | |
28 | */ | |
29 | ||
30 | /** | |
31 | * | |
32 | * @package CRM | |
33 | * @copyright CiviCRM LLC (c) 2004-2013 | |
34 | * $Id$ | |
35 | * | |
36 | */ | |
37 | class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event { | |
38 | CONST ROW_COUNT_LIMIT = 2; | |
39 | ||
40 | protected $_summary = NULL; | |
41 | protected $_noFields = TRUE; | |
a5611c8e DL |
42 | |
43 | protected $_add2groupSupported = FALSE; | |
44 | ||
6a488035 TO |
45 | function __construct() { |
46 | ||
47 | $this->_columns = array( | |
48 | 'civicrm_event' => | |
49 | array( | |
50 | 'dao' => 'CRM_Event_DAO_Event', | |
51 | 'filters' => | |
52 | array( | |
53 | 'id' => | |
54 | array('title' => ts('Event Title'), | |
55 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
56 | 'type' => CRM_Utils_Type::T_INT, | |
57 | 'options' => $this->getEventFilterOptions(), | |
58 | ), | |
59 | ), | |
60 | ), | |
61 | ); | |
a5611c8e | 62 | |
6a488035 TO |
63 | parent::__construct(); |
64 | } | |
65 | ||
66 | function preProcess() { | |
67 | $this->_csvSupported = FALSE; | |
68 | parent::preProcess(); | |
69 | } | |
70 | ||
71 | function buildEventReport($eventIDs) { | |
72 | ||
73 | $this->assign('events', $eventIDs); | |
74 | ||
75 | $eventID = implode(',', $eventIDs); | |
76 | ||
77 | $participantStatus = CRM_Event_PseudoConstant::participantStatus(NULL, "is_counted = 1"); | |
78 | $participantRole = CRM_Event_PseudoConstant::participantRole(); | |
79 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); | |
80 | ||
81 | $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array(); | |
82 | ||
83 | $optionGroupDAO = new CRM_Core_DAO_OptionGroup(); | |
84 | $optionGroupDAO->name = 'event_type'; | |
85 | $optionGroupId = NULL; | |
86 | if ($optionGroupDAO->find(TRUE)) { | |
87 | $optionGroupId = $optionGroupDAO->id; | |
88 | } | |
89 | //show the income of active participant status (Counted = filter = 1) | |
90 | $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array(); | |
91 | foreach ($participantStatus as $id => $label) { | |
92 | $activeParticipantStatusIDArray[] = $id; | |
93 | $activeParticipantStatusLabelArray[] = $label; | |
94 | } | |
95 | $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray); | |
96 | $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray); | |
97 | $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) "; | |
98 | ||
99 | $sql = " | |
100 | SELECT civicrm_event.id as event_id, | |
101 | civicrm_event.title as event_title, | |
a5611c8e | 102 | civicrm_event.max_participants as max_participants, |
6a488035 | 103 | civicrm_event.start_date as start_date, |
a5611c8e DL |
104 | civicrm_event.end_date as end_date, |
105 | civicrm_option_value.label as event_type, | |
33207fc5 | 106 | civicrm_participant.fee_currency as currency, |
6a488035 TO |
107 | SUM(civicrm_participant.fee_amount) as total, |
108 | COUNT(civicrm_participant.id) as participant | |
109 | ||
110 | FROM civicrm_event | |
a5611c8e | 111 | LEFT JOIN civicrm_option_value |
6a488035 TO |
112 | ON ( civicrm_event.event_type_id = civicrm_option_value.value AND |
113 | civicrm_option_value.option_group_id = {$optionGroupId} ) | |
a5611c8e | 114 | LEFT JOIN civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id |
6a488035 TO |
115 | {$activeParticipantClause} AND civicrm_participant.is_test = 0 ) |
116 | ||
a5611c8e DL |
117 | WHERE civicrm_event.id IN( {$eventID}) |
118 | ||
6a488035 TO |
119 | GROUP BY civicrm_event.id |
120 | "; | |
33207fc5 | 121 | |
6a488035 | 122 | $eventDAO = CRM_Core_DAO::executeQuery($sql); |
7baf6261 | 123 | $currency = array(); |
6a488035 TO |
124 | while ($eventDAO->fetch()) { |
125 | $eventSummary[$eventDAO->event_id]['Title'] = $eventDAO->event_title; | |
126 | $eventSummary[$eventDAO->event_id]['Max Participants'] = $eventDAO->max_participants; | |
127 | $eventSummary[$eventDAO->event_id]['Start Date'] = CRM_Utils_Date::customFormat($eventDAO->start_date); | |
128 | $eventSummary[$eventDAO->event_id]['End Date'] = CRM_Utils_Date::customFormat($eventDAO->end_date); | |
129 | $eventSummary[$eventDAO->event_id]['Event Type'] = $eventDAO->event_type; | |
33207fc5 | 130 | $eventSummary[$eventDAO->event_id]['Event Income'] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency); |
6a488035 | 131 | $eventSummary[$eventDAO->event_id]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})"; |
7baf6261 | 132 | $currency[$eventDAO->event_id] = $eventDAO->currency; |
6a488035 TO |
133 | } |
134 | $this->assign_by_ref('summary', $eventSummary); | |
135 | ||
136 | //Total Participant Registerd for the Event | |
137 | $pariticipantCount = " | |
138 | SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id | |
139 | ||
140 | FROM civicrm_participant | |
141 | ||
a5611c8e DL |
142 | WHERE civicrm_participant.event_id IN( {$eventID}) AND |
143 | civicrm_participant.is_test = 0 | |
6a488035 TO |
144 | {$activeParticipantClause} |
145 | GROUP BY civicrm_participant.event_id | |
146 | "; | |
147 | ||
148 | $counteDAO = CRM_Core_DAO::executeQuery($pariticipantCount); | |
149 | while ($counteDAO->fetch()) { | |
150 | $count[$counteDAO->event_id] = $counteDAO->count; | |
151 | } | |
152 | ||
153 | //Count the Participant by Role ID for Event | |
154 | $role = " | |
a5611c8e DL |
155 | SELECT civicrm_participant.role_id as ROLEID, |
156 | COUNT( civicrm_participant.id ) as participant, | |
6a488035 | 157 | SUM(civicrm_participant.fee_amount) as amount, |
7baf6261 | 158 | civicrm_participant.event_id as event_id, |
159 | civicrm_participant.fee_currency as currency | |
6a488035 TO |
160 | FROM civicrm_participant |
161 | ||
162 | WHERE civicrm_participant.event_id IN ( {$eventID}) AND | |
a5611c8e | 163 | civicrm_participant.is_test = 0 |
6a488035 TO |
164 | {$activeParticipantClause} |
165 | GROUP BY civicrm_participant.role_id, civicrm_participant.event_id | |
166 | "; | |
167 | ||
168 | $roleDAO = CRM_Core_DAO::executeQuery($role); | |
169 | ||
170 | while ($roleDAO->fetch()) { | |
171 | // fix for multiple role, CRM-6507 | |
172 | $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID); | |
173 | foreach ($roles as $roleId) { | |
174 | if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) { | |
175 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0; | |
176 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0; | |
177 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0; | |
178 | } | |
179 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant; | |
180 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount; | |
181 | } | |
7baf6261 | 182 | $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = CRM_Utils_Money::format($roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'], $currency[$roleDAO->event_id]); |
6a488035 TO |
183 | } |
184 | ||
185 | foreach ($roleRows as $eventId => $roleInfo) { | |
186 | foreach ($participantRole as $roleName) { | |
187 | if (isset($roleInfo[$roleName])) { | |
188 | $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2); | |
189 | } | |
190 | } | |
191 | } | |
7baf6261 | 192 | |
6a488035 TO |
193 | $rows['Role'] = $roleRows; |
194 | ||
195 | //Count the Participant by status ID for Event | |
196 | $status = " | |
a5611c8e DL |
197 | SELECT civicrm_participant.status_id as STATUSID, |
198 | COUNT( civicrm_participant.id ) as participant, | |
6a488035 TO |
199 | SUM(civicrm_participant.fee_amount) as amount, |
200 | civicrm_participant.event_id as event_id | |
201 | ||
202 | FROM civicrm_participant | |
203 | ||
204 | WHERE civicrm_participant.event_id IN ({$eventID}) AND | |
a5611c8e | 205 | civicrm_participant.is_test = 0 |
6a488035 TO |
206 | {$activeParticipantClause} |
207 | GROUP BY civicrm_participant.status_id, civicrm_participant.event_id | |
208 | "; | |
209 | ||
210 | $statusDAO = CRM_Core_DAO::executeQuery($status); | |
211 | ||
212 | while ($statusDAO->fetch()) { | |
213 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant; | |
214 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round(($statusDAO->participant / $count[$statusDAO->event_id]) * 100, 2); | |
7baf6261 | 215 | $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]); |
6a488035 TO |
216 | } |
217 | ||
218 | $rows['Status'] = $statusRows; | |
219 | ||
220 | //Count the Participant by payment instrument ID for Event | |
221 | //e.g. Credit Card, Check,Cash etc | |
222 | $paymentInstrument = " | |
a5611c8e DL |
223 | SELECT c.payment_instrument_id as INSTRUMENT, |
224 | COUNT( c.id ) as participant, | |
6a488035 TO |
225 | SUM(civicrm_participant.fee_amount) as amount, |
226 | civicrm_participant.event_id as event_id | |
227 | ||
228 | FROM civicrm_participant | |
229 | LEFT JOIN civicrm_participant_payment pp ON(pp.participant_id = civicrm_participant.id ) | |
230 | LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id) | |
231 | ||
232 | WHERE civicrm_participant.event_id IN ( {$eventID}) AND | |
233 | civicrm_participant.is_test = 0 | |
234 | {$activeParticipantClause} | |
235 | GROUP BY c.payment_instrument_id, civicrm_participant.event_id | |
236 | "; | |
237 | ||
238 | $instrumentDAO = CRM_Core_DAO::executeQuery($paymentInstrument); | |
239 | ||
240 | while ($instrumentDAO->fetch()) { | |
241 | //allow only if instrument is present in contribution table | |
242 | if ($instrumentDAO->INSTRUMENT) { | |
243 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant; | |
244 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round(($instrumentDAO->participant / $count[$instrumentDAO->event_id]) * 100, 2); | |
245 | $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = $instrumentDAO->amount; | |
246 | } | |
247 | } | |
248 | $rows['Payment Method'] = $instrumentRows; | |
249 | ||
250 | $this->assign_by_ref('rows', $rows); | |
251 | if (!$this->_setVariable) { | |
252 | $this->_params['id_value'] = NULL; | |
253 | } | |
254 | $this->assign('statistics', $this->statistics($eventIDs)); | |
255 | } | |
256 | ||
257 | function statistics(&$eventIDs) { | |
258 | $statistics = array(); | |
259 | $count = count($eventIDs); | |
260 | $this->countStat($statistics, $count); | |
261 | if ($this->_setVariable) { | |
262 | $this->filterStat($statistics); | |
263 | } | |
264 | ||
265 | return $statistics; | |
266 | } | |
267 | ||
268 | function limit($rowCount = self::ROW_COUNT_LIMIT) { | |
269 | parent::limit($rowCount); | |
270 | ||
271 | //modify limit | |
272 | $pageId = $this->get(CRM_Utils_Pager::PAGE_ID); | |
273 | ||
274 | //if pageId is greator than last page then display last page. | |
275 | if ((($pageId * self::ROW_COUNT_LIMIT) - 1) > $this->_rowsFound) { | |
276 | $pageId = ceil((float)$this->_rowsFound / (float)self::ROW_COUNT_LIMIT); | |
277 | $this->set(CRM_Utils_Pager::PAGE_ID, $pageId); | |
278 | } | |
279 | $this->_limit = ($pageId - 1) * self::ROW_COUNT_LIMIT; | |
280 | } | |
281 | ||
a5611c8e | 282 | function setPager($rowCount = self::ROW_COUNT_LIMIT) { |
6a488035 TO |
283 | $params = array( |
284 | 'total' => $this->_rowsFound, | |
285 | 'rowCount' => self::ROW_COUNT_LIMIT, | |
286 | 'status' => ts('Records %%StatusMessage%%'), | |
287 | 'buttonBottom' => 'PagerBottomButton', | |
288 | 'buttonTop' => 'PagerTopButton', | |
289 | 'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID), | |
290 | ); | |
291 | ||
292 | $pager = new CRM_Utils_Pager($params); | |
293 | $this->assign_by_ref('pager', $pager); | |
294 | } | |
295 | ||
296 | function postProcess() { | |
297 | $this->beginPostProcess(); | |
298 | $this->_setVariable = TRUE; | |
299 | ||
300 | $noSelection = FALSE; | |
301 | if (empty($this->_params['id_value'][0])) { | |
302 | $this->_params['id_value'] = array(); | |
303 | $this->_setVariable = FALSE; | |
304 | ||
305 | $events = CRM_Event_PseudoConstant::event(NULL, NULL, | |
306 | "is_template IS NULL OR is_template = 0" | |
307 | ); | |
308 | if (empty($events)) { | |
309 | return FALSE; | |
310 | } | |
311 | foreach ($events as $key => $dnt) { | |
312 | $this->_params['id_value'][] = $key; | |
313 | } | |
314 | $noSelection = TRUE; | |
a5611c8e DL |
315 | } |
316 | ||
6a488035 TO |
317 | $this->_rowsFound = count($this->_params['id_value']); |
318 | ||
319 | //set pager and limit if output mode is html | |
320 | if ($this->_outputMode == 'html') { | |
321 | $this->limit(); | |
322 | $this->setPager(); | |
a5611c8e | 323 | |
6a488035 TO |
324 | $showEvents = array(); |
325 | $count = 0; | |
326 | $numRows = $this->_limit; | |
327 | ||
328 | if (CRM_Utils_Array::value('id_op', $this->_params, 'in') == 'in' || $noSelection) { | |
329 | while ($count < self::ROW_COUNT_LIMIT) { | |
330 | if (!isset($this->_params['id_value'][$numRows])) { | |
331 | break; | |
332 | } | |
a5611c8e | 333 | |
6a488035 TO |
334 | $showEvents[] = $this->_params['id_value'][$numRows]; |
335 | $count++; | |
336 | $numRows++; | |
337 | } | |
338 | } elseif ($this->_params['id_op'] == 'notin') { | |
339 | $events = CRM_Event_PseudoConstant::event(NULL, NULL, | |
340 | "is_template IS NULL OR is_template = 0" | |
341 | ); | |
a5611c8e | 342 | |
6a488035 TO |
343 | $showEvents = array_diff(array_keys($events), $this->_params['id_value']); |
344 | } | |
345 | ||
346 | $this->buildEventReport($showEvents); | |
a5611c8e | 347 | |
6a488035 TO |
348 | } |
349 | else { | |
350 | $this->buildEventReport($this->_params['id_value']); | |
351 | } | |
352 | ||
353 | parent::endPostProcess(); | |
354 | } | |
355 | } | |
356 |