CRM/Report/Form/Event/IncomeCountSummary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  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.                                       |
  14  |                                                                    |
  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.        |
  19  |                                                                    |
  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  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Event_IncomeCountSummary extends CRM_Report_Form_Event {
  36
  37   protected $_summary = NULL;
  38
  39   protected $_charts = array(
  40     '' => 'Tabular',
  41     'barChart' => 'Bar Chart',
  42     'pieChart' => 'Pie Chart',
  43   );
  44
  45   protected $_add2groupSupported = FALSE;
  46
  47   protected $_customGroupExtends = array(
  48     'Event');
  49
  50   public $_drilldownReport = array('event/participantlist' => 'Link to Detail Report');
  51
  52   function __construct() {
  53
  54     $this->_columns = array(
  55       'civicrm_event' =>
  56       array(
  57         'dao' => 'CRM_Event_DAO_Event',
  58         'fields' =>
  59         array(
  60           'title' => array('title' => ts('Event'),
  61             'required' => TRUE,
  62           ),
  63           'id' => array(
  64             'no_display' => TRUE,
  65             'required' => TRUE,
  66           ),
  67           'event_type_id' => array('title' => ts('Event Type'),
  68           ),
  69           'fee_label' => array('title' => ts('Fee Label')),
  70           'event_start_date' => array('title' => ts('Event Start Date'),
  71           ),
  72           'event_end_date' => array('title' => ts('Event End Date'),
  73           ),
  74           'max_participants' => array('title' => ts('Capacity'),
  75             'type' => CRM_Utils_Type::T_INT,
  76           ),
  77         ),
  78         'filters' =>
  79         array(
  80           'id' => array('title' => ts('Event Title'),
  81             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  82             'options' => $this->getEventFilterOptions(),
  83           ),
  84           'event_type_id' => array(
  85             'name' => 'event_type_id',
  86             'title' => ts('Event Type'),
  87             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  88             'options' => CRM_Core_OptionGroup::values('event_type'),
  89           ),
  90           'event_start_date' => array('title' => ts('Event Start Date'),
  91             'operatorType' => CRM_Report_Form::OP_DATE,
  92           ),
  93           'event_end_date' => array('title' => ts('Event End Date'),
  94             'operatorType' => CRM_Report_Form::OP_DATE,
  95           ),
  96         ),
  97       ),
  98       'civicrm_line_item' =>
  99       array(
 100         'dao' => 'CRM_Price_DAO_LineItem',
 101         'fields' =>
 102         array(
 103           'participant_count' => array(
 104             'title' => ts('Participants'),
 105             'default' => TRUE,
 106             'statistics' =>
 107             array('count' => ts('Participants'),
 108             ),
 109           ),
 110           'line_total' => array(
 111             'title' => ts('Income Statistics'),
 112             'type' => CRM_Utils_Type::T_MONEY,
 113             'default' => TRUE,
 114             'statistics' =>
 115             array('sum' => ts('Income'),
 116               'avg' => ts('Average'),
 117             ),
 118           ),
 119         ),
 120       ),
 121       'civicrm_participant' =>
 122       array(
 123         'dao' => 'CRM_Event_DAO_Participant',
 124         'filters' =>
 125         array(
 126           'sid' => array('name' => 'status_id',
 127             'title' => ts('Participant Status'),
 128             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 129             'options' => CRM_Event_PseudoConstant::participantStatus(),
 130           ),
 131           'rid' => array(
 132             'name' => 'role_id',
 133             'title' => ts('Participant Role'),
 134             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 135             'options' => CRM_Event_PseudoConstant::participantRole(),
 136           ),
 137           'participant_register_date' => array('title' => ts('Registration Date'),
 138             'operatorType' => CRM_Report_Form::OP_DATE,
 139           ),
 140         ),
 141       ),
 142     );
 143     parent::__construct();
 144   }
 145
 146   function preProcess() {
 147     parent::preProcess();
 148   }
 149
 150   function select() {
 151     $select = array();
 152     foreach ($this->_columns as $tableName => $table) {
 153       if (array_key_exists('fields', $table)) {
 154         foreach ($table['fields'] as $fieldName => $field) {
 155           if (!empty($field['required']) ||
 156             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 157           ) {
 158             if (!empty($field['statistics'])) {
 159               foreach ($field['statistics'] as $stat => $label) {
 160                 switch (strtolower($stat)) {
 161                   case 'count':
 162                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 163                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 164                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 165                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 166                     break;
 167
 168                   case 'sum':
 169                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 170                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_MONEY;
 171
 172                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 173                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 174                     break;
 175
 176                   case 'avg':
 177                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 178
 179                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_MONEY;
 180                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 181                 }
 182               }
 183             }
 184             else {
 185               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 186               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 187               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 188             }
 189           }
 190         }
 191       }
 192     }
 193
 194     $this->_select = "SELECT " . implode(', ', $select);
 195   }
 196
 197   function from() {
 198     $this->_from = "
 199         FROM civicrm_event {$this->_aliases['civicrm_event']}
 200              LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
 201                     ON {$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id AND
 202                        {$this->_aliases['civicrm_participant']}.is_test = 0
 203              LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 204                     ON {$this->_aliases['civicrm_participant']}.id ={$this->_aliases['civicrm_line_item']}.entity_id AND
 205                        {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant' ";
 206   }
 207
 208   function where() {
 209     $clauses = array();
 210     $this->_participantWhere = "";
 211     foreach ($this->_columns as $tableName => $table) {
 212       if (array_key_exists('filters', $table)) {
 213         foreach ($table['filters'] as $fieldName => $field) {
 214           $clause = NULL;
 215           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 216             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 217             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 218             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 219
 220             if ($relative || $from || $to) {
 221               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 222             }
 223           }
 224           else {
 225             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 226             if ($op) {
 227               $clause = $this->whereClause($field,
 228                 $op,
 229                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 230                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 231                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 232               );
 233             }
 234           }
 235           if (!empty($this->_params['id_value'])) {
 236             $participant = implode(', ', $this->_params['id_value']);
 237             $this->_participantWhere = " AND civicrm_participant.event_id IN ( {$participant} ) ";
 238           }
 239
 240           if (!empty($clause)) {
 241             $clauses[] = $clause;
 242           }
 243         }
 244       }
 245     }
 246     $clauses[] = "({$this->_aliases['civicrm_event']}.is_template IS NULL OR {$this->_aliases['civicrm_event']}.is_template = 0)";
 247     $this->_where = "WHERE  " . implode(' AND ', $clauses);
 248   }
 249
 250   function statistics(&$rows) {
 251     $statistics = parent::statistics($rows);
 252     $select = "
 253          SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,
 254                 SUM( {$this->_aliases['civicrm_line_item']}.line_total )  as amount";
 255
 256     $sql = "{$select} {$this->_from} {$this->_where}";
 257
 258     $dao = CRM_Core_DAO::executeQuery($sql);
 259
 260     if ($dao->fetch()) {
 261       if ($dao->count && $dao->amount) {
 262         $avg = $dao->amount / $dao->count;
 263       }
 264       $statistics['counts']['count'] = array(
 265         'value' => $dao->count,
 266         'title' => 'Total Participants',
 267         'type' => CRM_Utils_Type::T_INT,
 268       );
 269       $statistics['counts']['amount'] = array(
 270         'value' => $dao->amount,
 271         'title' => 'Total Income',
 272         'type' => CRM_Utils_Type::T_MONEY,
 273       );
 274       $statistics['counts']['avg   '] = array(
 275         'value' => $avg,
 276         'title' => 'Average',
 277         'type' => CRM_Utils_Type::T_MONEY,
 278       );
 279     }
 280     return $statistics;
 281   }
 282
 283   function groupBy() {
 284     $this->assign('chartSupported', TRUE);
 285     $this->_rollup = " WITH ROLLUP";
 286     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id  {$this->_rollup}";
 287   }
 288
 289   function postProcess() {
 290
 291     $this->beginPostProcess();
 292
 293     $sql = $this->buildQuery(TRUE);
 294
 295     $dao = CRM_Core_DAO::executeQuery($sql);
 296
 297     //set pager before execution of query in function participantInfo()
 298     $this->setPager();
 299
 300     $rows = $graphRows = array();
 301     $count = 0;
 302
 303     while ($dao->fetch()) {
 304       $row = array();
 305       foreach ($this->_columnHeaders as $key => $value) {
 306         if (($key == 'civicrm_event_start_date') || ($key == 'civicrm_event_end_date')) {
 307           //get event start date and end date in custom datetime format
 308           $row[$key] = CRM_Utils_Date::customFormat($dao->$key);
 309         }
 310         elseif ($key == 'civicrm_participant_fee_amount_avg') {
 311           if ($dao->civicrm_participant_fee_amount_sum && $dao->civicrm_line_item_participant_count_count) {
 312             $row[$key] = $dao->civicrm_participant_fee_amount_sum / $dao->civicrm_line_item_participant_count_count;
 313           }
 314         }
 315         elseif ($key == 'civicrm_line_item_line_total_avg') {
 316           if ($dao->civicrm_line_item_line_total_sum && $dao->civicrm_line_item_participant_count_count) {
 317             $row[$key] = $dao->civicrm_line_item_line_total_sum / $dao->civicrm_line_item_participant_count_count;
 318           }
 319         }
 320         else {
 321           if (isset($dao->$key)) {
 322             $row[$key] = $dao->$key;
 323           }
 324         }
 325       }
 326       $rows[] = $row;
 327     }
 328
 329     // do not call pager here
 330     $this->formatDisplay($rows, FALSE);
 331     unset($this->_columnHeaders['civicrm_event_id']);
 332
 333     $this->doTemplateAssignment($rows);
 334
 335     $this->endPostProcess($rows);
 336   }
 337
 338   function buildChart(&$rows) {
 339
 340     $this->_interval = 'events';
 341     $countEvent = NULL;
 342     if (!empty($this->_params['charts'])) {
 343       foreach ($rows as $key => $value) {
 344         if ($value['civicrm_event_id']) {
 345           $graphRows['totalParticipants'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
 346           $graphRows[$this->_interval][] = substr($rows[$key]['civicrm_event_title'], 0, 12) . "..(" . $rows[$key]['civicrm_event_id'] . ") ";
 347           $graphRows['value'][] = ($rows[$key]['civicrm_line_item_participant_count_count']);
 348         }
 349       }
 350
 351       if (($rows[$key]['civicrm_line_item_participant_count_count']) == 0) {
 352         $countEvent = count($rows);
 353       }
 354
 355       if ((!empty($rows)) && $countEvent != 1) {
 356         $chartInfo = array(
 357           'legend' => 'Participants Summary',
 358           'xname' => 'Event',
 359           'yname' => 'Total Participants',
 360         );
 361         if (!empty($graphRows)) {
 362           foreach ($graphRows[$this->_interval] as $key => $val) {
 363             $graph[$val] = $graphRows['value'][$key];
 364           }
 365           $chartInfo['values'] = $graph;
 366           $chartInfo['tip'] = 'Participants : #val#';
 367           $chartInfo['xLabelAngle'] = 20;
 368
 369           // build the chart.
 370           CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 371         }
 372       }
 373     }
 374   }
 375
 376   function alterDisplay(&$rows) {
 377
 378     if (is_array($rows)) {
 379       $eventType = CRM_Core_OptionGroup::values('event_type');
 380
 381       foreach ($rows as $rowNum => $row) {
 382         if (array_key_exists('civicrm_event_title', $row)) {
 383           if ($value = $row['civicrm_event_id']) {
 384             CRM_Event_PseudoConstant::event($value, FALSE);
 385             $url = CRM_Report_Utils_Report::getNextUrl('event/participantlist',
 386               'reset=1&force=1&event_id_op=eq&event_id_value=' . $value,
 387               $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 388             );
 389             $rows[$rowNum]['civicrm_event_title_link'] = $url;
 390             $rows[$rowNum]['civicrm_event_title_hover'] = ts("View Event Participants For this Event");
 391           }
 392         }
 393
 394         //handle event type
 395         if (array_key_exists('civicrm_event_event_type_id', $row)) {
 396           if ($value = $row['civicrm_event_event_type_id']) {
 397             $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 398           }
 399         }
 400       }
 401     }
 402   }
 403 }
 404