crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Event/IncomeCountSummary.php

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