CRM/Report/Form/Event/IncomeCountSummary.php

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