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. |
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 along 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-2014
35 class CRM_Report_Form_Event_IncomeCountSummary
extends CRM_Report_Form_Event
{
37 protected $_summary = NULL;
39 protected $_charts = array(
41 'barChart' => 'Bar Chart',
42 'pieChart' => 'Pie Chart',
45 protected $_add2groupSupported = FALSE;
47 protected $_customGroupExtends = array(
51 public $_drilldownReport = array('event/participantlist' => 'Link to Detail Report');
59 function __construct() {
61 $this->_columns
= array(
62 'civicrm_event' => array(
63 'dao' => 'CRM_Event_DAO_Event',
66 'title' => ts('Event'),
70 'title' => 'Event ID',
74 'event_type_id' => array(
75 'title' => ts('Event Type'),
78 'title' => ts('Fee Label')
80 'event_start_date' => array(
81 'title' => ts('Event Start Date'),
83 'event_end_date' => array(
84 'title' => ts('Event End Date'),
86 'max_participants' => array(
87 'title' => ts('Capacity'),
88 'type' => CRM_Utils_Type
::T_INT
,
93 'title' => ts('Event'),
94 'operatorType' => CRM_Report_Form
::OP_ENTITYREF
,
95 'type' => CRM_Utils_Type
::T_INT
,
96 'attributes' => array('select' => array('minimumInputLength' => 0)),
98 'event_type_id' => array(
99 'name' => 'event_type_id',
100 'title' => ts('Event Type'),
101 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
102 'options' => CRM_Core_OptionGroup
::values('event_type'),
104 'event_start_date' => array(
105 'title' => ts('Event Start Date'),
106 'operatorType' => CRM_Report_Form
::OP_DATE
,
108 'event_end_date' => array(
109 'title' => ts('Event End Date'),
110 'operatorType' => CRM_Report_Form
::OP_DATE
,
114 'civicrm_line_item' => array(
115 'dao' => 'CRM_Price_DAO_LineItem',
117 'participant_count' => array(
118 'title' => ts('Participants'),
122 'count' => ts('Participants'),
125 'line_total' => array(
126 'title' => ts('Income Statistics'),
127 'type' => CRM_Utils_Type
::T_MONEY
,
131 'sum' => ts('Income'),
132 'avg' => ts('Average'),
137 'civicrm_participant' => array(
138 'dao' => 'CRM_Event_DAO_Participant',
141 'name' => 'status_id',
142 'title' => ts('Participant Status'),
143 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
144 'options' => CRM_Event_PseudoConstant
::participantStatus(),
148 'title' => ts('Participant Role'),
149 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
150 'options' => CRM_Event_PseudoConstant
::participantRole(),
152 'participant_register_date' => array(
153 'title' => ts('Registration Date'),
154 'operatorType' => CRM_Report_Form
::OP_DATE
,
159 parent
::__construct();
162 function preProcess() {
163 parent
::preProcess();
168 foreach ($this->_columns
as $tableName => $table) {
169 if (array_key_exists('fields', $table)) {
170 foreach ($table['fields'] as $fieldName => $field) {
171 if (!empty($field['required']) ||
!empty($this->_params
['fields'][$fieldName])) {
172 if (!empty($field['statistics'])) {
173 foreach ($field['statistics'] as $stat => $label) {
174 switch (strtolower($stat)) {
176 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
177 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
178 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
179 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
183 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
184 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_MONEY
;
186 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
187 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
191 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
193 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_MONEY
;
194 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
199 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
200 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
201 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
208 $this->_select
= "SELECT " . implode(', ', $select);
213 FROM civicrm_event {$this->_aliases['civicrm_event']}
214 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
215 ON {$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id AND
216 {$this->_aliases['civicrm_participant']}.is_test = 0
217 LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
218 ON {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id AND
219 {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' ";
224 $this->_participantWhere
= "";
225 foreach ($this->_columns
as $tableName => $table) {
226 if (array_key_exists('filters', $table)) {
227 foreach ($table['filters'] as $fieldName => $field) {
229 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
230 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
231 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
232 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
234 if ($relative ||
$from ||
$to) {
235 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
239 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
241 $clause = $this->whereClause($field,
243 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
244 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
245 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
249 if (!empty($this->_params
['id_value'])) {
250 $this->_participantWhere
= " AND civicrm_participant.event_id IN ( {$this->_params['id_value']} ) ";
253 if (!empty($clause)) {
254 $clauses[] = $clause;
259 $clauses[] = "{$this->_aliases['civicrm_event']}.is_template = 0";
260 $this->_where
= "WHERE " . implode(' AND ', $clauses);
268 function statistics(&$rows) {
269 $statistics = parent
::statistics($rows);
271 SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
272 SUM( {$this->_aliases['civicrm_line_item']}.line_total ) as amount";
274 $sql = "{$select} {$this->_from} {$this->_where}";
276 $dao = CRM_Core_DAO
::executeQuery($sql);
280 if ($dao->count
&& $dao->amount
) {
281 $avg = $dao->amount
/ $dao->count
;
283 $statistics['counts']['count'] = array(
284 'value' => $dao->count
,
285 'title' => 'Total Participants',
286 'type' => CRM_Utils_Type
::T_INT
,
288 $statistics['counts']['amount'] = array(
289 'value' => $dao->amount
,
290 'title' => 'Total Income',
291 'type' => CRM_Utils_Type
::T_MONEY
,
293 $statistics['counts']['avg'] = array(
295 'title' => 'Average',
296 'type' => CRM_Utils_Type
::T_MONEY
,
303 $this->assign('chartSupported', TRUE);
304 $this->_rollup
= " WITH ROLLUP";
305 $this->_groupBy
= " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}";
308 function postProcess() {
310 $this->beginPostProcess();
312 $sql = $this->buildQuery(TRUE);
314 $dao = CRM_Core_DAO
::executeQuery($sql);
316 //set pager before execution of query in function participantInfo()
319 $rows = $graphRows = array();
322 while ($dao->fetch()) {
324 foreach ($this->_columnHeaders
as $key => $value) {
325 if (($key == 'civicrm_event_start_date') ||
($key == 'civicrm_event_end_date')) {
326 //get event start date and end date in custom datetime format
327 $row[$key] = CRM_Utils_Date
::customFormat($dao->$key);
329 elseif ($key == 'civicrm_participant_fee_amount_avg') {
330 if ($dao->civicrm_participant_fee_amount_sum
&& $dao->civicrm_line_item_participant_count_count
) {
331 $row[$key] = $dao->civicrm_participant_fee_amount_sum
/ $dao->civicrm_line_item_participant_count_count
;
334 elseif ($key == 'civicrm_line_item_line_total_avg') {
335 if ($dao->civicrm_line_item_line_total_sum
&& $dao->civicrm_line_item_participant_count_count
) {
336 $row[$key] = $dao->civicrm_line_item_line_total_sum
/ $dao->civicrm_line_item_participant_count_count
;
340 if (isset($dao->$key)) {
341 $row[$key] = $dao->$key;
348 // do not call pager here
349 $this->formatDisplay($rows, FALSE);
350 unset($this->_columnHeaders
['civicrm_event_id']);
352 $this->doTemplateAssignment($rows);
354 $this->endPostProcess($rows);
360 function buildChart(&$rows) {
362 $this->_interval
= 'events';
364 if (!empty($this->_params
['charts'])) {
365 foreach ($rows as $key => $value) {
366 if ($value['civicrm_event_id']) {
367 $graphRows['totalParticipants'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
368 $graphRows[$this->_interval
][] =
369 substr($rows[$key]['civicrm_event_title'], 0, 12) . "..(" . $rows[$key]['civicrm_event_id'] . ") ";
370 $graphRows['value'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
374 if (($rows[$key]['civicrm_line_item_participant_count_count']) == 0) {
375 $countEvent = count($rows);
378 if ((!empty($rows)) && $countEvent != 1) {
380 'legend' => 'Participants Summary',
382 'yname' => 'Total Participants',
384 if (!empty($graphRows)) {
385 foreach ($graphRows[$this->_interval
] as $key => $val) {
386 $graph[$val] = $graphRows['value'][$key];
388 $chartInfo['values'] = $graph;
389 $chartInfo['tip'] = 'Participants : #val#';
390 $chartInfo['xLabelAngle'] = 20;
393 CRM_Utils_OpenFlashChart
::buildChart($chartInfo, $this->_params
['charts']);
402 function alterDisplay(&$rows) {
404 if (is_array($rows)) {
405 $eventType = CRM_Core_OptionGroup
::values('event_type');
407 foreach ($rows as $rowNum => $row) {
408 if (array_key_exists('civicrm_event_title', $row)) {
409 if ($value = $row['civicrm_event_id']) {
410 CRM_Event_PseudoConstant
::event($value, FALSE);
411 $url = CRM_Report_Utils_Report
::getNextUrl('event/participantlist',
412 'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
413 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
415 $rows[$rowNum]['civicrm_event_title_link'] = $url;
416 $rows[$rowNum]['civicrm_event_title_hover'] = ts("View Event Participants For this Event");
421 if (array_key_exists('civicrm_event_event_type_id', $row)) {
422 if ($value = $row['civicrm_event_event_type_id']) {
423 $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];