CRM/Report/Form/Event/Summary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.6                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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 and the CiviCRM Licensing Exception.   |
  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 and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Event_Summary extends CRM_Report_Form_Event {
  37
  38   protected $_summary = NULL;
  39
  40   protected $_charts = array(
  41     '' => 'Tabular',
  42     'barChart' => 'Bar Chart',
  43     'pieChart' => 'Pie Chart',
  44   );
  45
  46   protected $_add2groupSupported = FALSE;
  47
  48   protected $_customGroupExtends = array(
  49     'Event'
  50   );
  51   public $_drilldownReport = array('event/income' => 'Link to Detail Report');
  52
  53   /**
  54    *
  55    */
  56   /**
  57    *
  58    */
  59   function __construct() {
  60
  61     $this->_columns = array(
  62       'civicrm_event' => array(
  63         'dao' => 'CRM_Event_DAO_Event',
  64         'fields' => array(
  65           'id' => array(
  66             'no_display' => TRUE,
  67             'required' => TRUE,
  68           ),
  69           'title' => array(
  70             'title' => ts('Event Title'),
  71             'required' => TRUE,
  72           ),
  73           'event_type_id' => array(
  74             'title' => ts('Event Type'),
  75             'required' => TRUE,
  76           ),
  77           'fee_label' => array('title' => ts('Fee Label')),
  78           'event_start_date' => array(
  79             'title' => ts('Event Start Date'),
  80           ),
  81           'event_end_date' => array('title' => ts('Event End Date')),
  82           'max_participants' => array(
  83             'title' => ts('Capacity'),
  84             'type' => CRM_Utils_Type::T_INT,
  85           ),
  86         ),
  87         'filters' => array(
  88           'id' => array(
  89             'title' => ts('Event'),
  90             'operatorType' => CRM_Report_Form::OP_ENTITYREF,
  91             'type' => CRM_Utils_Type::T_INT,
  92             'attributes' => array('select' => array('minimumInputLength' => 0)),
  93           ),
  94           'event_type_id' => array(
  95             'name' => 'event_type_id',
  96             'title' => ts('Event Type'),
  97             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  98             'options' => CRM_Core_OptionGroup::values('event_type'),
  99           ),
 100           'event_start_date' => array(
 101             'title' => 'Event Start Date',
 102             'operatorType' => CRM_Report_Form::OP_DATE,
 103           ),
 104           'event_end_date' => array(
 105             'title' => 'Event End Date',
 106             'operatorType' => CRM_Report_Form::OP_DATE,
 107           ),
 108         ),
 109       ),
 110     );
 111     $this->_currencyColumn = 'civicrm_participant_fee_currency';
 112     parent::__construct();
 113   }
 114
 115   function preProcess() {
 116     parent::preProcess();
 117   }
 118
 119   function select() {
 120     $select = array();
 121     foreach ($this->_columns as $tableName => $table) {
 122       if (array_key_exists('fields', $table)) {
 123         foreach ($table['fields'] as $fieldName => $field) {
 124           if (!empty($field['required']) ||
 125             !empty($this->_params['fields'][$fieldName])
 126           ) {
 127             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 128           }
 129         }
 130       }
 131     }
 132
 133     $this->_select = 'SELECT ' . implode(', ', $select);
 134   }
 135
 136   function from() {
 137     $this->_from = " FROM civicrm_event {$this->_aliases['civicrm_event']} ";
 138   }
 139
 140   function where() {
 141     $clauses = array();
 142     $this->_participantWhere = "";
 143     foreach ($this->_columns as $tableName => $table) {
 144       if (array_key_exists('filters', $table)) {
 145         foreach ($table['filters'] as $fieldName => $field) {
 146           $clause = NULL;
 147           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 148             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 149             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 150             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 151
 152             if ($relative || $from || $to) {
 153               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 154             }
 155           }
 156           else {
 157             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 158             if ($op) {
 159               $clause = $this->whereClause($field,
 160                 $op,
 161                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 162                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 163                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 164               );
 165             }
 166           }
 167           if (!empty($this->_params['id_value'])) {
 168             $this->_participantWhere = " AND civicrm_participant.event_id IN ( {$this->_params['id_value']} ) ";
 169           }
 170
 171           if (!empty($clause)) {
 172             $clauses[] = $clause;
 173           }
 174         }
 175       }
 176     }
 177     $clauses[] = "{$this->_aliases['civicrm_event']}.is_template = 0";
 178     $this->_where = 'WHERE  ' . implode(' AND ', $clauses);
 179   }
 180
 181   function groupBy() {
 182     $this->assign('chartSupported', TRUE);
 183     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id";
 184   }
 185
 186   //get participants information for events
 187   /**
 188    * @return array
 189    */
 190   function participantInfo() {
 191
 192     $statusType1 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 1');
 193     $statusType2 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 0');
 194
 195     $sql = "
 196           SELECT civicrm_participant.event_id    AS event_id,
 197                  civicrm_participant.status_id   AS statusId,
 198                  COUNT( civicrm_participant.id ) AS participant,
 199                  SUM( civicrm_participant.fee_amount ) AS amount,
 200                  civicrm_participant.fee_currency
 201
 202             FROM civicrm_participant
 203
 204             WHERE civicrm_participant.is_test = 0
 205                   $this->_participantWhere
 206
 207         GROUP BY civicrm_participant.event_id,
 208                  civicrm_participant.status_id";
 209
 210     $info = CRM_Core_DAO::executeQuery($sql);
 211     $participant_data = $participant_info = $currency = array();
 212
 213     while ($info->fetch()) {
 214       $participant_data[$info->event_id][$info->statusId]['participant'] = $info->participant;
 215       $participant_data[$info->event_id][$info->statusId]['amount'] = $info->amount;
 216       $currency[$info->event_id] = $info->fee_currency;
 217     }
 218
 219     $amt = $particiType1 = $particiType2 = 0;
 220
 221     foreach ($participant_data as $event_id => $event_data) {
 222       foreach ($event_data as $status_id => $data) {
 223
 224         if (array_key_exists($status_id, $statusType1)) {
 225           //total income of event
 226           $amt = $amt + $data['amount'];
 227
 228           //number of Registered/Attended participants
 229           $particiType1 = $particiType1 + $data['participant'];
 230         }
 231         elseif (array_key_exists($status_id, $statusType2)) {
 232
 233           //number of No-show/Cancelled/Pending participants
 234           $particiType2 = $particiType2 + $data['participant'];
 235         }
 236       }
 237
 238       $participant_info[$event_id]['totalAmount'] = $amt;
 239       $participant_info[$event_id]['statusType1'] = $particiType1;
 240       $participant_info[$event_id]['statusType2'] = $particiType2;
 241       $participant_info[$event_id]['currency'] = $currency[$event_id];
 242       $amt = $particiType1 = $particiType2 = 0;
 243     }
 244
 245     return $participant_info;
 246   }
 247
 248   //build header for table
 249   function buildColumnHeaders() {
 250
 251     $this->_columnHeaders = array();
 252     foreach ($this->_columns as $tableName => $table) {
 253       if (array_key_exists('fields', $table)) {
 254         foreach ($table['fields'] as $fieldName => $field) {
 255           if (!empty($field['required']) ||
 256             !empty($this->_params['fields'][$fieldName])
 257           ) {
 258
 259             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 260             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 261           }
 262         }
 263       }
 264     }
 265
 266     $statusType1 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 1');
 267     $statusType2 = CRM_Event_PseudoConstant::participantStatus(NULL, 'is_counted = 0');
 268
 269     //make column header for participant status  Registered/Attended
 270     $type1_header = implode('/', $statusType1);
 271
 272     //make column header for participant status No-show/Cancelled/Pending
 273     $type2_header = implode('/', $statusType2);
 274
 275     $this->_columnHeaders['statusType1'] = array(
 276       'title' => $type1_header,
 277       'type' => CRM_Utils_Type::T_INT,
 278     );
 279     $this->_columnHeaders['statusType2'] = array(
 280       'title' => $type2_header,
 281       'type' => CRM_Utils_Type::T_INT,
 282     );
 283     $this->_columnHeaders['totalAmount'] = array(
 284       'title' => 'Total Income',
 285       'type' => CRM_Utils_Type::T_STRING,
 286     );
 287   }
 288
 289   function postProcess() {
 290
 291     $this->beginPostProcess();
 292
 293     $this->buildColumnHeaders();
 294
 295     $sql = $this->buildQuery(TRUE);
 296
 297     $dao = CRM_Core_DAO::executeQuery($sql);
 298
 299     //set pager before exicution of query in function participantInfo()
 300     $this->setPager();
 301
 302     $rows = $graphRows = array();
 303     $count = 0;
 304     while ($dao->fetch()) {
 305       $row = array();
 306       foreach ($this->_columnHeaders as $key => $value) {
 307         if (($key == 'civicrm_event_start_date') ||
 308           ($key == 'civicrm_event_end_date')
 309         ) {
 310           //get event start date and end date in custom datetime format
 311           $row[$key] = CRM_Utils_Date::customFormat($dao->$key);
 312         }
 313         else {
 314           if (isset($dao->$key)) {
 315             $row[$key] = $dao->$key;
 316           }
 317         }
 318       }
 319       $rows[] = $row;
 320     }
 321     if (!empty($rows)) {
 322       $participant_info = $this->participantInfo();
 323       foreach ($rows as $key => $value) {
 324         if (array_key_exists($value['civicrm_event_id'], $participant_info)) {
 325           foreach ($participant_info[$value['civicrm_event_id']] as $k => $v) {
 326             $rows[$key][$k] = $v;
 327           }
 328         }
 329       }
 330     }
 331     // do not call pager here
 332     $this->formatDisplay($rows, FALSE);
 333     unset($this->_columnHeaders['civicrm_event_id']);
 334
 335     $this->doTemplateAssignment($rows);
 336
 337     $this->endPostProcess($rows);
 338   }
 339
 340   /**
 341    * @param $rows
 342    */
 343   function buildChart(&$rows) {
 344     $this->_interval = 'events';
 345     $countEvent = NULL;
 346     if (!empty($this->_params['charts'])) {
 347       foreach ($rows as $key => $value) {
 348         $graphRows['totalAmount'][] = $graphRows['value'][] = CRM_Utils_Array::value('totalAmount', $rows[$key]);
 349         $graphRows[$this->_interval][] =
 350           substr($rows[$key]['civicrm_event_title'], 0, 12) . "..(" .
 351           $rows[$key]['civicrm_event_id'] . ") ";
 352       }
 353
 354       if (CRM_Utils_Array::value('totalAmount', $rows[$key]) == 0) {
 355         $countEvent = count($rows);
 356       }
 357
 358       if ((!empty($rows)) && $countEvent != 1) {
 359         $config = CRM_Core_Config::Singleton();
 360         $chartInfo = array(
 361           'legend' => 'Event Summary',
 362           'xname' => 'Event',
 363           'yname' => "Total Amount ({$config->defaultCurrency})",
 364         );
 365         if (!empty($graphRows)) {
 366           foreach ($graphRows[$this->_interval] as $key => $val) {
 367             $graph[$val] = $graphRows['value'][$key];
 368           }
 369           $chartInfo['values'] = $graph;
 370           $chartInfo['xLabelAngle'] = 20;
 371
 372           // build the chart.
 373           CRM_Utils_OpenFlashChart::buildChart($chartInfo, $this->_params['charts']);
 374           $this->assign('chartType', $this->_params['charts']);
 375         }
 376       }
 377     }
 378   }
 379
 380   /**
 381    * @param $rows
 382    */
 383   function alterDisplay(&$rows) {
 384
 385     if (is_array($rows)) {
 386       $eventType = CRM_Core_OptionGroup::values('event_type');
 387
 388       foreach ($rows as $rowNum => $row) {
 389         if (array_key_exists('totalAmount', $row) &&
 390           array_key_exists('currency', $row)
 391         ) {
 392           $rows[$rowNum]['totalAmount'] = CRM_Utils_Money::format($rows[$rowNum]['totalAmount'], $rows[$rowNum]['currency']);
 393         }
 394         if (array_key_exists('civicrm_event_title', $row)) {
 395           if ($value = $row['civicrm_event_id']) {
 396             //CRM_Event_PseudoConstant::event( $value, false );
 397             $url = CRM_Report_Utils_Report::getNextUrl('event/income',
 398               'reset=1&force=1&id_op=in&id_value=' . $value,
 399               $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 400             );
 401             $rows[$rowNum]['civicrm_event_title_link'] = $url;
 402             $rows[$rowNum]['civicrm_event_title_hover'] = ts('View Event Income For this Event');
 403           }
 404         }
 405
 406         //handle event type
 407         if (array_key_exists('civicrm_event_event_type_id', $row)) {
 408           if ($value = $row['civicrm_event_event_type_id']) {
 409             $rows[$rowNum]['civicrm_event_event_type_id'] = $eventType[$value];
 410           }
 411         }
 412       }
 413     }
 414   }
 415 }
 416