CRM/Report/Form/Event/Income.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event {
  36   const ROW_COUNT_LIMIT = 2;
  37
  38   protected $_summary = NULL;
  39   protected $_noFields = TRUE;
  40
  41   protected $_add2groupSupported = FALSE;
  42
  43   /**
  44    * Class constructor.
  45    */
  46   public function __construct() {
  47
  48     $this->_columns = array(
  49       'civicrm_event' => array(
  50         'dao' => 'CRM_Event_DAO_Event',
  51         'filters' => array(
  52           'id' => array(
  53             'title' => ts('Event'),
  54             'operatorType' => CRM_Report_Form::OP_ENTITYREF,
  55             'type' => CRM_Utils_Type::T_INT,
  56             'attributes' => array('select' => array('minimumInputLength' => 0)),
  57           ),
  58         ),
  59       ),
  60     );
  61
  62     parent::__construct();
  63   }
  64
  65   public function preProcess() {
  66     $this->_csvSupported = FALSE;
  67     parent::preProcess();
  68   }
  69
  70   /**
  71    * Build event report.
  72    *
  73    * @param array $eventIDs
  74    */
  75   public function buildEventReport($eventIDs) {
  76
  77     $this->assign('events', $eventIDs);
  78
  79     $eventID = implode(',', $eventIDs);
  80
  81     $participantStatus = CRM_Event_PseudoConstant::participantStatus(NULL, "is_counted = 1", "label");
  82     $participantRole = CRM_Event_PseudoConstant::participantRole();
  83     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
  84
  85     $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
  86
  87     $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
  88     $optionGroupDAO->name = 'event_type';
  89     $optionGroupId = NULL;
  90     if ($optionGroupDAO->find(TRUE)) {
  91       $optionGroupId = $optionGroupDAO->id;
  92     }
  93     //show the income of active participant status (Counted = filter = 1)
  94     $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array();
  95     foreach ($participantStatus as $id => $label) {
  96       $activeParticipantStatusIDArray[] = $id;
  97       $activeParticipantStatusLabelArray[] = $label;
  98     }
  99     $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
 100     $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
 101     $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) ";
 102     $select = array(
 103       "civicrm_event.id as event_id",
 104       "civicrm_event.title as event_title",
 105       "civicrm_event.max_participants as max_participants",
 106       "civicrm_event.start_date as start_date",
 107       "civicrm_event.end_date as end_date",
 108       "civicrm_option_value.label as event_type",
 109       "civicrm_participant.fee_currency as currency",
 110     );
 111
 112     $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, 'civicrm_event.id');
 113     $sql = "
 114             SELECT  " . implode(', ', $select) . ",
 115                     SUM(civicrm_participant.fee_amount) as total,
 116                     COUNT(civicrm_participant.id)       as participant
 117
 118             FROM       civicrm_event
 119             LEFT JOIN  civicrm_option_value
 120                    ON  ( civicrm_event.event_type_id = civicrm_option_value.value AND
 121                          civicrm_option_value.option_group_id = {$optionGroupId} )
 122             LEFT JOIN  civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
 123                        {$activeParticipantClause} AND civicrm_participant.is_test  = 0 )
 124
 125             WHERE      civicrm_event.id IN( {$eventID}) {$groupBy}";
 126
 127     $eventDAO = $this->executeReportQuery($sql);
 128     $currency = array();
 129     while ($eventDAO->fetch()) {
 130       $eventSummary[$eventDAO->event_id][ts('Title')] = $eventDAO->event_title;
 131       $eventSummary[$eventDAO->event_id][ts('Max Participants')] = $eventDAO->max_participants;
 132       $eventSummary[$eventDAO->event_id][ts('Start Date')] = CRM_Utils_Date::customFormat($eventDAO->start_date);
 133       $eventSummary[$eventDAO->event_id][ts('End Date')] = CRM_Utils_Date::customFormat($eventDAO->end_date);
 134       $eventSummary[$eventDAO->event_id][ts('Event Type')] = $eventDAO->event_type;
 135       $eventSummary[$eventDAO->event_id][ts('Event Income')] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency);
 136       $eventSummary[$eventDAO->event_id][ts('Registered Participant')] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
 137       $currency[$eventDAO->event_id] = $eventDAO->currency;
 138     }
 139     $this->assign_by_ref('summary', $eventSummary);
 140
 141     //Total Participant Registerd for the Event
 142     $pariticipantCount = "
 143             SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
 144
 145             FROM     civicrm_participant
 146
 147             WHERE    civicrm_participant.event_id IN( {$eventID}) AND
 148                      civicrm_participant.is_test  = 0
 149                      {$activeParticipantClause}
 150             GROUP BY civicrm_participant.event_id
 151              ";
 152
 153     $counteDAO = $this->executeReportQuery($pariticipantCount);
 154     while ($counteDAO->fetch()) {
 155       $count[$counteDAO->event_id] = $counteDAO->count;
 156     }
 157
 158     // Count the Participant by Role ID for Event.
 159     $role = "
 160             SELECT civicrm_participant.role_id         as ROLEID,
 161                    COUNT( civicrm_participant.id )     as participant,
 162                    SUM(civicrm_participant.fee_amount) as amount,
 163                    civicrm_participant.event_id        as event_id,
 164                    civicrm_participant.fee_currency    as currency
 165             FROM     civicrm_participant
 166
 167             WHERE    civicrm_participant.event_id IN ( {$eventID}) AND
 168                      civicrm_participant.is_test  = 0
 169                      {$activeParticipantClause}
 170             GROUP BY civicrm_participant.role_id, civicrm_participant.event_id, civicrm_participant.fee_currency
 171             ";
 172
 173     $roleDAO = $this->executeReportQuery($role);
 174
 175     while ($roleDAO->fetch()) {
 176       // fix for multiple role, CRM-6507
 177       $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID);
 178       foreach ($roles as $roleId) {
 179         if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) {
 180           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0;
 181           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0;
 182           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0;
 183         }
 184         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant;
 185         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount;
 186       }
 187     }
 188
 189     foreach ($roleRows as $eventId => $roleInfo) {
 190       foreach ($participantRole as $roleName) {
 191         if (isset($roleInfo[$roleName])) {
 192           $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2);
 193         }
 194         if (!empty($roleRows[$eventId][$roleName])) {
 195           $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]);
 196         }
 197       }
 198     }
 199
 200     $rows[ts('Role')] = $roleRows;
 201
 202     // Count the Participant by status ID for Event.
 203     $status = "
 204             SELECT civicrm_participant.status_id       as STATUSID,
 205                    COUNT( civicrm_participant.id )     as participant,
 206                    SUM(civicrm_participant.fee_amount) as amount,
 207                    civicrm_participant.event_id        as event_id
 208
 209             FROM     civicrm_participant
 210
 211             WHERE    civicrm_participant.event_id IN ({$eventID}) AND
 212                      civicrm_participant.is_test  = 0
 213                      {$activeParticipantClause}
 214             GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
 215             ";
 216
 217     $statusDAO = $this->executeReportQuery($status);
 218
 219     while ($statusDAO->fetch()) {
 220       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant;
 221       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round(($statusDAO->participant / $count[$statusDAO->event_id]) * 100, 2);
 222       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]);
 223     }
 224
 225     $rows[ts('Status')] = $statusRows;
 226
 227     //Count the Participant by payment instrument ID for Event
 228     //e.g. Credit Card, Check,Cash etc
 229     $paymentInstrument = "
 230             SELECT c.payment_instrument_id               as INSTRUMENT,
 231                    COUNT( civicrm_participant.id )       as participant,
 232                    SUM( civicrm_participant.fee_amount ) as amount,
 233                    civicrm_participant.event_id          as event_id
 234
 235             FROM      civicrm_participant,
 236             civicrm_participant_payment pp
 237             LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
 238
 239             WHERE     civicrm_participant.event_id IN ( {$eventID} )
 240                       AND civicrm_participant.is_test  = 0
 241                       {$activeParticipantClause}
 242                       AND ((pp.participant_id = civicrm_participant.id )
 243                            OR (pp.participant_id = civicrm_participant.registered_by_id ))
 244             GROUP BY  c.payment_instrument_id, civicrm_participant.event_id
 245             ";
 246
 247     $instrumentDAO = $this->executeReportQuery($paymentInstrument);
 248
 249     while ($instrumentDAO->fetch()) {
 250       //allow only if instrument is present in contribution table
 251       if ($instrumentDAO->INSTRUMENT) {
 252         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant;
 253         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round(($instrumentDAO->participant / $count[$instrumentDAO->event_id]) * 100, 2);
 254         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = CRM_Utils_Money::format($instrumentDAO->amount, $currency[$instrumentDAO->event_id]);
 255       }
 256     }
 257     $rows[ts('Payment Method')] = $instrumentRows;
 258
 259     $this->assign_by_ref('rows', $rows);
 260     if (!$this->_setVariable) {
 261       $this->_params['id_value'] = NULL;
 262     }
 263     $this->assign('statistics', $this->statistics($eventIDs));
 264   }
 265
 266   /**
 267    * @param $eventIDs
 268    *
 269    * @return array
 270    */
 271   public function statistics(&$eventIDs) {
 272     $statistics = array();
 273     $count = count($eventIDs);
 274     $this->countStat($statistics, $count);
 275     if ($this->_setVariable) {
 276       $this->filterStat($statistics);
 277     }
 278
 279     return $statistics;
 280   }
 281
 282   /**
 283    * @inheritDoc
 284    */
 285   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
 286     parent::limit($rowCount);
 287
 288     // Modify limit.
 289     $pageId = $this->get(CRM_Utils_Pager::PAGE_ID);
 290
 291     //if pageId is greater than last page then display last page.
 292     if ((($pageId * self::ROW_COUNT_LIMIT) - 1) > $this->_rowsFound) {
 293       $pageId = ceil((float) $this->_rowsFound / (float) self::ROW_COUNT_LIMIT);
 294       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
 295     }
 296     $this->_limit = ($pageId - 1) * self::ROW_COUNT_LIMIT;
 297   }
 298
 299   /**
 300    * @param int $rowCount
 301    */
 302   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 303     $params = array(
 304       'total' => $this->_rowsFound,
 305       'rowCount' => self::ROW_COUNT_LIMIT,
 306       'status' => ts('Records %%StatusMessage%%'),
 307       'buttonBottom' => 'PagerBottomButton',
 308       'buttonTop' => 'PagerTopButton',
 309       'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
 310     );
 311
 312     $pager = new CRM_Utils_Pager($params);
 313     $this->assign_by_ref('pager', $pager);
 314   }
 315
 316   /**
 317    * Form post process function.
 318    *
 319    * @return bool
 320    */
 321   public function postProcess() {
 322     $this->beginPostProcess();
 323     $this->_setVariable = TRUE;
 324
 325     $noSelection = FALSE;
 326     if (empty($this->_params['id_value'])) {
 327       $this->_params['id_value'] = array();
 328       $this->_setVariable = FALSE;
 329
 330       $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 331         "is_template = 0"
 332       );
 333       if (empty($events)) {
 334         return FALSE;
 335       }
 336       foreach ($events as $key => $dnt) {
 337         $this->_params['id_value'][] = $key;
 338       }
 339       $noSelection = TRUE;
 340     }
 341     elseif (!is_array($this->_params['id_value'])) {
 342       $this->_params['id_value'] = explode(',', $this->_params['id_value']);
 343     }
 344
 345     $this->_rowsFound = count($this->_params['id_value']);
 346
 347     //set pager and limit if output mode is html
 348     if ($this->_outputMode == 'html') {
 349       $this->limit();
 350       $this->setPager();
 351
 352       $showEvents = array();
 353       $count = 0;
 354       $numRows = $this->_limit;
 355
 356       if (CRM_Utils_Array::value('id_op', $this->_params, 'in') == 'in' || $noSelection) {
 357         while ($count < self::ROW_COUNT_LIMIT) {
 358           if (!isset($this->_params['id_value'][$numRows])) {
 359             break;
 360           }
 361
 362           $showEvents[] = $this->_params['id_value'][$numRows];
 363           $count++;
 364           $numRows++;
 365         }
 366       }
 367       elseif ($this->_params['id_op'] == 'notin') {
 368         $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 369           "is_template = 0"
 370         );
 371
 372         $showEvents = array_diff(array_keys($events), $this->_params['id_value']);
 373       }
 374
 375       $this->buildEventReport($showEvents);
 376
 377     }
 378     else {
 379       $this->buildEventReport($this->_params['id_value']);
 380     }
 381
 382     parent::endPostProcess();
 383   }
 384
 385 }