3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
35 class CRM_Report_Form_Event_Income
extends CRM_Report_Form_Event
{
36 const ROW_COUNT_LIMIT
= 2;
38 protected $_summary = NULL;
39 protected $_noFields = TRUE;
41 protected $_add2groupSupported = FALSE;
46 public function __construct() {
48 $this->_columns
= array(
49 'civicrm_event' => array(
50 'dao' => 'CRM_Event_DAO_Event',
53 'title' => ts('Event'),
54 'operatorType' => CRM_Report_Form
::OP_ENTITYREF
,
55 'type' => CRM_Utils_Type
::T_INT
,
56 'attributes' => array('select' => array('minimumInputLength' => 0)),
62 parent
::__construct();
65 public function preProcess() {
66 $this->_csvSupported
= FALSE;
73 * @param array $eventIDs
75 public function buildEventReport($eventIDs) {
77 $this->assign('events', $eventIDs);
79 $eventID = implode(',', $eventIDs);
81 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, "is_counted = 1", "label");
82 $participantRole = CRM_Event_PseudoConstant
::participantRole();
83 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
85 $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
87 $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
88 $optionGroupDAO->name
= 'event_type';
89 $optionGroupId = NULL;
90 if ($optionGroupDAO->find(TRUE)) {
91 $optionGroupId = $optionGroupDAO->id
;
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;
99 $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
100 $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
101 $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) ";
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",
112 $groupBy = CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($select, 'civicrm_event.id');
114 SELECT " . implode(', ', $select) . ",
115 SUM(civicrm_participant.fee_amount) as total,
116 COUNT(civicrm_participant.id) as participant
119 LEFT JOIN civicrm_option_value
120 ON ( civicrm_event.event_type_id = civicrm_option_value.value AND
121 civicrm_option_value.option_group_id = {$optionGroupId} )
122 LEFT JOIN civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
123 {$activeParticipantClause} AND civicrm_participant.is_test = 0 )
125 WHERE civicrm_event.id IN( {$eventID}) {$groupBy}";
127 $eventDAO = $this->executeReportQuery($sql);
129 while ($eventDAO->fetch()) {
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})";
137 $currency[$eventDAO->event_id
] = $eventDAO->currency
;
139 $this->assign_by_ref('summary', $eventSummary);
141 //Total Participant Registerd for the Event
142 $pariticipantCount = "
143 SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
145 FROM civicrm_participant
147 WHERE civicrm_participant.event_id IN( {$eventID}) AND
148 civicrm_participant.is_test = 0
149 {$activeParticipantClause}
150 GROUP BY civicrm_participant.event_id
153 $counteDAO = $this->executeReportQuery($pariticipantCount);
154 while ($counteDAO->fetch()) {
155 $count[$counteDAO->event_id
] = $counteDAO->count
;
158 // Count the Participant by Role ID for Event.
160 SELECT civicrm_participant.role_id as ROLEID,
161 COUNT( civicrm_participant.id ) as participant,
162 SUM(civicrm_participant.fee_amount) as amount,
163 civicrm_participant.event_id as event_id,
164 civicrm_participant.fee_currency as currency
165 FROM civicrm_participant
167 WHERE civicrm_participant.event_id IN ( {$eventID}) AND
168 civicrm_participant.is_test = 0
169 {$activeParticipantClause}
170 GROUP BY civicrm_participant.role_id, civicrm_participant.event_id, civicrm_participant.fee_currency
173 $roleDAO = $this->executeReportQuery($role);
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;
184 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['total'] +
= $roleDAO->participant
;
185 $roleRows[$roleDAO->event_id
][$participantRole[$roleId]]['amount'] +
= $roleDAO->amount
;
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);
194 if (!empty($roleRows[$eventId][$roleName])) {
195 $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money
::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]);
200 $rows[ts('Role')] = $roleRows;
202 // Count the Participant by status ID for Event.
204 SELECT civicrm_participant.status_id as STATUSID,
205 COUNT( civicrm_participant.id ) as participant,
206 SUM(civicrm_participant.fee_amount) as amount,
207 civicrm_participant.event_id as event_id
209 FROM civicrm_participant
211 WHERE civicrm_participant.event_id IN ({$eventID}) AND
212 civicrm_participant.is_test = 0
213 {$activeParticipantClause}
214 GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
217 $statusDAO = $this->executeReportQuery($status);
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);
222 $statusRows[$statusDAO->event_id
][$participantStatus[$statusDAO->STATUSID
]]['amount'] = CRM_Utils_Money
::format($statusDAO->amount
, $currency[$statusDAO->event_id
]);
225 $rows[ts('Status')] = $statusRows;
227 //Count the Participant by payment instrument ID for Event
228 //e.g. Credit Card, Check,Cash etc
229 $paymentInstrument = "
230 SELECT c.payment_instrument_id as INSTRUMENT,
231 COUNT( civicrm_participant.id ) as participant,
232 SUM( civicrm_participant.fee_amount ) as amount,
233 civicrm_participant.event_id as event_id
235 FROM civicrm_participant,
236 civicrm_participant_payment pp
237 LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
239 WHERE civicrm_participant.event_id IN ( {$eventID} )
240 AND civicrm_participant.is_test = 0
241 {$activeParticipantClause}
242 AND ((pp.participant_id = civicrm_participant.id )
243 OR (pp.participant_id = civicrm_participant.registered_by_id ))
244 GROUP BY c.payment_instrument_id, civicrm_participant.event_id
247 $instrumentDAO = $this->executeReportQuery($paymentInstrument);
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);
254 $instrumentRows[$instrumentDAO->event_id
][$paymentInstruments[$instrumentDAO->INSTRUMENT
]]['amount'] = CRM_Utils_Money
::format($instrumentDAO->amount
, $currency[$instrumentDAO->event_id
]);
257 $rows[ts('Payment Method')] = $instrumentRows;
259 $this->assign_by_ref('rows', $rows);
260 if (!$this->_setVariable
) {
261 $this->_params
['id_value'] = NULL;
263 $this->assign('statistics', $this->statistics($eventIDs));
271 public function statistics(&$eventIDs) {
272 $statistics = array();
273 $count = count($eventIDs);
274 $this->countStat($statistics, $count);
275 if ($this->_setVariable
) {
276 $this->filterStat($statistics);
285 public function limit($rowCount = self
::ROW_COUNT_LIMIT
) {
286 parent
::limit($rowCount);
289 $pageId = $this->get(CRM_Utils_Pager
::PAGE_ID
);
291 //if pageId is greater than last page then display last page.
292 if ((($pageId * self
::ROW_COUNT_LIMIT
) - 1) > $this->_rowsFound
) {
293 $pageId = ceil((float) $this->_rowsFound
/ (float) self
::ROW_COUNT_LIMIT
);
294 $this->set(CRM_Utils_Pager
::PAGE_ID
, $pageId);
296 $this->_limit
= ($pageId - 1) * self
::ROW_COUNT_LIMIT
;
300 * @param int $rowCount
302 public function setPager($rowCount = self
::ROW_COUNT_LIMIT
) {
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
),
312 $pager = new CRM_Utils_Pager($params);
313 $this->assign_by_ref('pager', $pager);
317 * Form post process function.
321 public function postProcess() {
322 $this->beginPostProcess();
323 $this->_setVariable
= TRUE;
325 $noSelection = FALSE;
326 if (empty($this->_params
['id_value'])) {
327 $this->_params
['id_value'] = array();
328 $this->_setVariable
= FALSE;
330 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
333 if (empty($events)) {
336 foreach ($events as $key => $dnt) {
337 $this->_params
['id_value'][] = $key;
341 elseif (!is_array($this->_params
['id_value'])) {
342 $this->_params
['id_value'] = explode(',', $this->_params
['id_value']);
345 $this->_rowsFound
= count($this->_params
['id_value']);
347 //set pager and limit if output mode is html
348 if ($this->_outputMode
== 'html') {
352 $showEvents = array();
354 $numRows = $this->_limit
;
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])) {
362 $showEvents[] = $this->_params
['id_value'][$numRows];
367 elseif ($this->_params
['id_op'] == 'notin') {
368 $events = CRM_Event_PseudoConstant
::event(NULL, NULL,
372 $showEvents = array_diff(array_keys($events), $this->_params
['id_value']);
375 $this->buildEventReport($showEvents);
379 $this->buildEventReport($this->_params
['id_value']);
382 parent
::endPostProcess();