5 +--------------------------------------------------------------------+
6 | CiviCRM version 4.3 |
7 +--------------------------------------------------------------------+
8 | Copyright CiviCRM LLC (c) 2004-2013 |
9 +--------------------------------------------------------------------+
10 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
33 * @copyright CiviCRM LLC (c) 2004-2013
37 class CRM_Report_Form_Event_Income
extends CRM_Report_Form_Event
{
38 CONST ROW_COUNT_LIMIT
= 2;
40 protected $_summary = NULL;
41 protected $_noFields = TRUE;
43 protected $_add2groupSupported = FALSE;
45 function __construct() {
47 $this->_columns
= array(
50 'dao' => 'CRM_Event_DAO_Event',
54 array('title' => ts('Event Title'),
55 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
56 'type' => CRM_Utils_Type
::T_INT
,
57 'options' => $this->getEventFilterOptions(),
63 parent
::__construct();
66 function preProcess() {
67 $this->_csvSupported
= FALSE;
71 function buildEventReport($eventIDs) {
73 $this->assign('events', $eventIDs);
75 $eventID = implode(',', $eventIDs);
77 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, "is_counted = 1");
78 $participantRole = CRM_Event_PseudoConstant
::participantRole();
79 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
81 $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
83 $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
84 $optionGroupDAO->name
= 'event_type';
85 $optionGroupId = NULL;
86 if ($optionGroupDAO->find(TRUE)) {
87 $optionGroupId = $optionGroupDAO->id
;
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;
95 $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
96 $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
97 $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) ";
100 SELECT civicrm_event.id as event_id,
101 civicrm_event.title as event_title,
102 civicrm_event.max_participants as max_participants,
103 civicrm_event.start_date as start_date,
104 civicrm_event.end_date as end_date,
105 civicrm_option_value.label as event_type,
106 civicrm_participant.fee_currency as currency,
107 SUM(civicrm_participant.fee_amount) as total,
108 COUNT(civicrm_participant.id) as participant
111 LEFT JOIN civicrm_option_value
112 ON ( civicrm_event.event_type_id = civicrm_option_value.value AND
113 civicrm_option_value.option_group_id = {$optionGroupId} )
114 LEFT JOIN civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
115 {$activeParticipantClause} AND civicrm_participant.is_test = 0 )
117 WHERE civicrm_event.id IN( {$eventID})
119 GROUP BY civicrm_event.id
122 $eventDAO = CRM_Core_DAO
::executeQuery($sql);
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
;
130 $eventSummary[$eventDAO->event_id
]['Event Income'] = CRM_Utils_Money
::format($eventDAO->total
, $eventDAO->currency
);
131 $eventSummary[$eventDAO->event_id
]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
133 $this->assign_by_ref('summary', $eventSummary);
135 //Total Participant Registerd for the Event
136 $pariticipantCount = "
137 SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
139 FROM civicrm_participant
141 WHERE civicrm_participant.event_id IN( {$eventID}) AND
142 civicrm_participant.is_test = 0
143 {$activeParticipantClause}
144 GROUP BY civicrm_participant.event_id
147 $counteDAO = CRM_Core_DAO
::executeQuery($pariticipantCount);
148 while ($counteDAO->fetch()) {
149 $count[$counteDAO->event_id
] = $counteDAO->count
;
152 //Count the Participant by Role ID for Event
154 SELECT civicrm_participant.role_id as ROLEID,
155 COUNT( civicrm_participant.id ) as participant,
156 SUM(civicrm_participant.fee_amount) as amount,
157 civicrm_participant.event_id as event_id
159 FROM civicrm_participant
161 WHERE civicrm_participant.event_id IN ( {$eventID}) AND
162 civicrm_participant.is_test = 0
163 {$activeParticipantClause}
164 GROUP BY civicrm_participant.role_id, civicrm_participant.event_id
167 $roleDAO = CRM_Core_DAO
::executeQuery($role);
169 while ($roleDAO->fetch()) {
170 // fix for multiple role, CRM-6507
171 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $roleDAO->ROLEID
);
172 foreach ($roles as $roleId) {
173 if (!isset($roleRows[$roleDAO->event_id
][$participantRole[$roleId]])) {
174 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['total'] = 0;
175 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['round'] = 0;
176 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['amount'] = 0;
178 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['total'] +
= $roleDAO->participant
;
179 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['amount'] +
= $roleDAO->amount
;
183 foreach ($roleRows as $eventId => $roleInfo) {
184 foreach ($participantRole as $roleName) {
185 if (isset($roleInfo[$roleName])) {
186 $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2);
191 $rows['Role'] = $roleRows;
193 //Count the Participant by status ID for Event
195 SELECT civicrm_participant.status_id as STATUSID,
196 COUNT( civicrm_participant.id ) as participant,
197 SUM(civicrm_participant.fee_amount) as amount,
198 civicrm_participant.event_id as event_id
200 FROM civicrm_participant
202 WHERE civicrm_participant.event_id IN ({$eventID}) AND
203 civicrm_participant.is_test = 0
204 {$activeParticipantClause}
205 GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
208 $statusDAO = CRM_Core_DAO
::executeQuery($status);
210 while ($statusDAO->fetch()) {
211 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['total'] = $statusDAO->participant
;
212 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['round'] = round(($statusDAO->participant
/ $count[$statusDAO->event_id
]) * 100, 2);
213 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['amount'] = $statusDAO->amount
;
216 $rows['Status'] = $statusRows;
218 //Count the Participant by payment instrument ID for Event
219 //e.g. Credit Card, Check,Cash etc
220 $paymentInstrument = "
221 SELECT c.payment_instrument_id as INSTRUMENT,
222 COUNT( c.id ) as participant,
223 SUM(civicrm_participant.fee_amount) as amount,
224 civicrm_participant.event_id as event_id
226 FROM civicrm_participant
227 LEFT JOIN civicrm_participant_payment pp ON(pp.participant_id = civicrm_participant.id )
228 LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
230 WHERE civicrm_participant.event_id IN ( {$eventID}) AND
231 civicrm_participant.is_test = 0
232 {$activeParticipantClause}
233 GROUP BY c.payment_instrument_id, civicrm_participant.event_id
236 $instrumentDAO = CRM_Core_DAO
::executeQuery($paymentInstrument);
238 while ($instrumentDAO->fetch()) {
239 //allow only if instrument is present in contribution table
240 if ($instrumentDAO->INSTRUMENT
) {
241 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['total'] = $instrumentDAO->participant
;
242 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['round'] = round(($instrumentDAO->participant
/ $count[$instrumentDAO->event_id
]) * 100, 2);
243 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['amount'] = $instrumentDAO->amount
;
246 $rows['Payment Method'] = $instrumentRows;
248 $this->assign_by_ref('rows', $rows);
249 if (!$this->_setVariable
) {
250 $this->_params
['id_value'] = NULL;
252 $this->assign('statistics', $this->statistics($eventIDs));
255 function statistics(&$eventIDs) {
256 $statistics = array();
257 $count = count($eventIDs);
258 $this->countStat($statistics, $count);
259 if ($this->_setVariable
) {
260 $this->filterStat($statistics);
266 function limit($rowCount = self
::ROW_COUNT_LIMIT
) {
267 parent
::limit($rowCount);
270 $pageId = $this->get(CRM_Utils_Pager
::PAGE_ID
);
272 //if pageId is greator than last page then display last page.
273 if ((($pageId * self
::ROW_COUNT_LIMIT
) - 1) > $this->_rowsFound
) {
274 $pageId = ceil((float)$this->_rowsFound
/ (float)self
::ROW_COUNT_LIMIT
);
275 $this->set(CRM_Utils_Pager
::PAGE_ID
, $pageId);
277 $this->_limit
= ($pageId - 1) * self
::ROW_COUNT_LIMIT
;
280 function setPager($rowCount = self
::ROW_COUNT_LIMIT
) {
282 'total' => $this->_rowsFound
,
283 'rowCount' => self
::ROW_COUNT_LIMIT
,
284 'status' => ts('Records %%StatusMessage%%'),
285 'buttonBottom' => 'PagerBottomButton',
286 'buttonTop' => 'PagerTopButton',
287 'pageID' => $this->get(CRM_Utils_Pager
::PAGE_ID
),
290 $pager = new CRM_Utils_Pager($params);
291 $this->assign_by_ref('pager', $pager);
294 function postProcess() {
295 $this->beginPostProcess();
296 $this->_setVariable
= TRUE;
298 $noSelection = FALSE;
299 if (empty($this->_params
['id_value'][0])) {
300 $this->_params
['id_value'] = array();
301 $this->_setVariable
= FALSE;
303 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
304 "is_template IS NULL OR is_template = 0"
306 if (empty($events)) {
309 foreach ($events as $key => $dnt) {
310 $this->_params
['id_value'][] = $key;
315 $this->_rowsFound
= count($this->_params
['id_value']);
317 //set pager and limit if output mode is html
318 if ($this->_outputMode
== 'html') {
322 $showEvents = array();
324 $numRows = $this->_limit
;
326 if (CRM_Utils_Array
::value('id_op', $this->_params
, 'in') == 'in' ||
$noSelection) {
327 while ($count < self
::ROW_COUNT_LIMIT
) {
328 if (!isset($this->_params
['id_value'][$numRows])) {
332 $showEvents[] = $this->_params
['id_value'][$numRows];
336 } elseif ($this->_params
['id_op'] == 'notin') {
337 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
338 "is_template IS NULL OR is_template = 0"
341 $showEvents = array_diff(array_keys($events), $this->_params
['id_value']);
344 $this->buildEventReport($showEvents);
348 $this->buildEventReport($this->_params
['id_value']);
351 parent
::endPostProcess();