4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.5 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2014 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2014
36 class CRM_Report_Form_Event_Income
extends CRM_Report_Form_Event
{
37 CONST ROW_COUNT_LIMIT
= 2;
39 protected $_summary = NULL;
40 protected $_noFields = TRUE;
42 protected $_add2groupSupported = FALSE;
50 function __construct() {
52 $this->_columns
= array(
53 'civicrm_event' => array(
54 'dao' => 'CRM_Event_DAO_Event',
57 'title' => ts('Event'),
58 'operatorType' => CRM_Report_Form
::OP_ENTITYREF
,
59 'type' => CRM_Utils_Type
::T_INT
,
60 'attributes' => array('select' => array('minimumInputLength' => 0)),
66 parent
::__construct();
69 function preProcess() {
70 $this->_csvSupported
= FALSE;
77 function buildEventReport($eventIDs) {
79 $this->assign('events', $eventIDs);
81 $eventID = implode(',', $eventIDs);
83 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, "is_counted = 1");
84 $participantRole = CRM_Event_PseudoConstant
::participantRole();
85 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
87 $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
89 $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
90 $optionGroupDAO->name
= 'event_type';
91 $optionGroupId = NULL;
92 if ($optionGroupDAO->find(TRUE)) {
93 $optionGroupId = $optionGroupDAO->id
;
95 //show the income of active participant status (Counted = filter = 1)
96 $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array();
97 foreach ($participantStatus as $id => $label) {
98 $activeParticipantStatusIDArray[] = $id;
99 $activeParticipantStatusLabelArray[] = $label;
101 $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
102 $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
103 $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) ";
106 SELECT civicrm_event.id as event_id,
107 civicrm_event.title as event_title,
108 civicrm_event.max_participants as max_participants,
109 civicrm_event.start_date as start_date,
110 civicrm_event.end_date as end_date,
111 civicrm_option_value.label as event_type,
112 civicrm_participant.fee_currency as currency,
113 SUM(civicrm_participant.fee_amount) as total,
114 COUNT(civicrm_participant.id) as participant
117 LEFT JOIN civicrm_option_value
118 ON ( civicrm_event.event_type_id = civicrm_option_value.value AND
119 civicrm_option_value.option_group_id = {$optionGroupId} )
120 LEFT JOIN civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
121 {$activeParticipantClause} AND civicrm_participant.is_test = 0 )
123 WHERE civicrm_event.id IN( {$eventID})
125 GROUP BY civicrm_event.id
128 $eventDAO = CRM_Core_DAO
::executeQuery($sql);
130 while ($eventDAO->fetch()) {
131 $eventSummary[$eventDAO->event_id
]['Title'] = $eventDAO->event_title
;
132 $eventSummary[$eventDAO->event_id
]['Max Participants'] = $eventDAO->max_participants
;
133 $eventSummary[$eventDAO->event_id
]['Start Date'] = CRM_Utils_Date
::customFormat($eventDAO->start_date
);
134 $eventSummary[$eventDAO->event_id
]['End Date'] = CRM_Utils_Date
::customFormat($eventDAO->end_date
);
135 $eventSummary[$eventDAO->event_id
]['Event Type'] = $eventDAO->event_type
;
136 $eventSummary[$eventDAO->event_id
]['Event Income'] = CRM_Utils_Money
::format($eventDAO->total
, $eventDAO->currency
);
137 $eventSummary[$eventDAO->event_id
]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
138 $currency[$eventDAO->event_id
] = $eventDAO->currency
;
140 $this->assign_by_ref('summary', $eventSummary);
142 //Total Participant Registerd for the Event
143 $pariticipantCount = "
144 SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
146 FROM civicrm_participant
148 WHERE civicrm_participant.event_id IN( {$eventID}) AND
149 civicrm_participant.is_test = 0
150 {$activeParticipantClause}
151 GROUP BY civicrm_participant.event_id
154 $counteDAO = CRM_Core_DAO
::executeQuery($pariticipantCount);
155 while ($counteDAO->fetch()) {
156 $count[$counteDAO->event_id
] = $counteDAO->count
;
159 //Count the Participant by Role ID for Event
161 SELECT civicrm_participant.role_id as ROLEID,
162 COUNT( civicrm_participant.id ) as participant,
163 SUM(civicrm_participant.fee_amount) as amount,
164 civicrm_participant.event_id as event_id,
165 civicrm_participant.fee_currency as currency
166 FROM civicrm_participant
168 WHERE civicrm_participant.event_id IN ( {$eventID}) AND
169 civicrm_participant.is_test = 0
170 {$activeParticipantClause}
171 GROUP BY civicrm_participant.role_id, civicrm_participant.event_id
174 $roleDAO = CRM_Core_DAO
::executeQuery($role);
176 while ($roleDAO->fetch()) {
177 // fix for multiple role, CRM-6507
178 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $roleDAO->ROLEID
);
179 foreach ($roles as $roleId) {
180 if (!isset($roleRows[$roleDAO->event_id
][$participantRole[$roleId]])) {
181 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['total'] = 0;
182 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['round'] = 0;
183 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['amount'] = 0;
185 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['total'] +
= $roleDAO->participant
;
186 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['amount'] +
= $roleDAO->amount
;
190 foreach ($roleRows as $eventId => $roleInfo) {
191 foreach ($participantRole as $roleName) {
192 if (isset($roleInfo[$roleName])) {
193 $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2);
195 if (!empty($roleRows[$eventId][$roleName])) {
196 $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money
::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]);
201 $rows['Role'] = $roleRows;
203 //Count the Participant by status ID for Event
205 SELECT civicrm_participant.status_id as STATUSID,
206 COUNT( civicrm_participant.id ) as participant,
207 SUM(civicrm_participant.fee_amount) as amount,
208 civicrm_participant.event_id as event_id
210 FROM civicrm_participant
212 WHERE civicrm_participant.event_id IN ({$eventID}) AND
213 civicrm_participant.is_test = 0
214 {$activeParticipantClause}
215 GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
218 $statusDAO = CRM_Core_DAO
::executeQuery($status);
220 while ($statusDAO->fetch()) {
221 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['total'] = $statusDAO->participant
;
222 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['round'] = round(($statusDAO->participant
/ $count[$statusDAO->event_id
]) * 100, 2);
223 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['amount'] = CRM_Utils_Money
::format($statusDAO->amount
, $currency[$statusDAO->event_id
]);
226 $rows['Status'] = $statusRows;
228 //Count the Participant by payment instrument ID for Event
229 //e.g. Credit Card, Check,Cash etc
230 $paymentInstrument = "
231 SELECT c.payment_instrument_id as INSTRUMENT,
232 COUNT( civicrm_participant.id ) as participant,
233 SUM( civicrm_participant.fee_amount ) as amount,
234 civicrm_participant.event_id as event_id
236 FROM civicrm_participant,
237 civicrm_participant_payment pp
238 LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
240 WHERE civicrm_participant.event_id IN ( {$eventID} )
241 AND civicrm_participant.is_test = 0
242 {$activeParticipantClause}
243 AND ((pp.participant_id = civicrm_participant.id )
244 OR (pp.participant_id = civicrm_participant.registered_by_id ))
245 GROUP BY c.payment_instrument_id, civicrm_participant.event_id
248 $instrumentDAO = CRM_Core_DAO
::executeQuery($paymentInstrument);
250 while ($instrumentDAO->fetch()) {
251 //allow only if instrument is present in contribution table
252 if ($instrumentDAO->INSTRUMENT
) {
253 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['total'] = $instrumentDAO->participant
;
254 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['round'] = round(($instrumentDAO->participant
/ $count[$instrumentDAO->event_id
]) * 100, 2);
255 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['amount'] = CRM_Utils_Money
::format($instrumentDAO->amount
, $currency[$instrumentDAO->event_id
]);
258 $rows['Payment Method'] = $instrumentRows;
260 $this->assign_by_ref('rows', $rows);
261 if (!$this->_setVariable
) {
262 $this->_params
['id_value'] = NULL;
264 $this->assign('statistics', $this->statistics($eventIDs));
272 function statistics(&$eventIDs) {
273 $statistics = array();
274 $count = count($eventIDs);
275 $this->countStat($statistics, $count);
276 if ($this->_setVariable
) {
277 $this->filterStat($statistics);
284 * @param int $rowCount
286 function limit($rowCount = self
::ROW_COUNT_LIMIT
) {
287 parent
::limit($rowCount);
290 $pageId = $this->get(CRM_Utils_Pager
::PAGE_ID
);
292 //if pageId is greator than last page then display last page.
293 if ((($pageId * self
::ROW_COUNT_LIMIT
) - 1) > $this->_rowsFound
) {
294 $pageId = ceil((float)$this->_rowsFound
/ (float)self
::ROW_COUNT_LIMIT
);
295 $this->set(CRM_Utils_Pager
::PAGE_ID
, $pageId);
297 $this->_limit
= ($pageId - 1) * self
::ROW_COUNT_LIMIT
;
301 * @param int $rowCount
303 function setPager($rowCount = self
::ROW_COUNT_LIMIT
) {
305 'total' => $this->_rowsFound
,
306 'rowCount' => self
::ROW_COUNT_LIMIT
,
307 'status' => ts('Records %%StatusMessage%%'),
308 'buttonBottom' => 'PagerBottomButton',
309 'buttonTop' => 'PagerTopButton',
310 'pageID' => $this->get(CRM_Utils_Pager
::PAGE_ID
),
313 $pager = new CRM_Utils_Pager($params);
314 $this->assign_by_ref('pager', $pager);
317 function postProcess() {
318 $this->beginPostProcess();
319 $this->_setVariable
= TRUE;
321 $noSelection = FALSE;
322 if (empty($this->_params
['id_value'])) {
323 $this->_params
['id_value'] = array();
324 $this->_setVariable
= FALSE;
326 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
329 if (empty($events)) {
332 foreach ($events as $key => $dnt) {
333 $this->_params
['id_value'][] = $key;
337 elseif (!is_array($this->_params
['id_value'])) {
338 $this->_params
['id_value'] = explode(',', $this->_params
['id_value']);
341 $this->_rowsFound
= count($this->_params
['id_value']);
343 //set pager and limit if output mode is html
344 if ($this->_outputMode
== 'html') {
348 $showEvents = array();
350 $numRows = $this->_limit
;
352 if (CRM_Utils_Array
::value('id_op', $this->_params
, 'in') == 'in' ||
$noSelection) {
353 while ($count < self
::ROW_COUNT_LIMIT
) {
354 if (!isset($this->_params
['id_value'][$numRows])) {
358 $showEvents[] = $this->_params
['id_value'][$numRows];
362 } elseif ($this->_params
['id_op'] == 'notin') {
363 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
367 $showEvents = array_diff(array_keys($events), $this->_params
['id_value']);
370 $this->buildEventReport($showEvents);
374 $this->buildEventReport($this->_params
['id_value']);
377 parent
::endPostProcess();