CRM/Report/Form/Event/Income.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_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");
  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
 103     $sql = "
 104             SELECT  civicrm_event.id                    as event_id,
 105                     civicrm_event.title                 as event_title,
 106                     civicrm_event.max_participants      as max_participants,
 107                     civicrm_event.start_date            as start_date,
 108                     civicrm_event.end_date              as end_date,
 109                     civicrm_option_value.label          as event_type,
 110                     civicrm_participant.fee_currency    as currency,
 111                     SUM(civicrm_participant.fee_amount) as total,
 112                     COUNT(civicrm_participant.id)       as participant
 113
 114             FROM       civicrm_event
 115             LEFT JOIN  civicrm_option_value
 116                    ON  ( civicrm_event.event_type_id = civicrm_option_value.value AND
 117                          civicrm_option_value.option_group_id = {$optionGroupId} )
 118             LEFT JOIN  civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
 119                        {$activeParticipantClause} AND civicrm_participant.is_test  = 0 )
 120
 121             WHERE      civicrm_event.id IN( {$eventID})
 122
 123             GROUP BY   civicrm_event.id
 124             ";
 125
 126     $eventDAO = CRM_Core_DAO::executeQuery($sql);
 127     $currency = array();
 128     while ($eventDAO->fetch()) {
 129       $eventSummary[$eventDAO->event_id]['Title'] = $eventDAO->event_title;
 130       $eventSummary[$eventDAO->event_id]['Max Participants'] = $eventDAO->max_participants;
 131       $eventSummary[$eventDAO->event_id]['Start Date'] = CRM_Utils_Date::customFormat($eventDAO->start_date);
 132       $eventSummary[$eventDAO->event_id]['End Date'] = CRM_Utils_Date::customFormat($eventDAO->end_date);
 133       $eventSummary[$eventDAO->event_id]['Event Type'] = $eventDAO->event_type;
 134       $eventSummary[$eventDAO->event_id]['Event Income'] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency);
 135       $eventSummary[$eventDAO->event_id]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
 136       $currency[$eventDAO->event_id] = $eventDAO->currency;
 137     }
 138     $this->assign_by_ref('summary', $eventSummary);
 139
 140     //Total Participant Registerd for the Event
 141     $pariticipantCount = "
 142             SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
 143
 144             FROM     civicrm_participant
 145
 146             WHERE    civicrm_participant.event_id IN( {$eventID}) AND
 147                      civicrm_participant.is_test  = 0
 148                      {$activeParticipantClause}
 149             GROUP BY civicrm_participant.event_id
 150              ";
 151
 152     $counteDAO = CRM_Core_DAO::executeQuery($pariticipantCount);
 153     while ($counteDAO->fetch()) {
 154       $count[$counteDAO->event_id] = $counteDAO->count;
 155     }
 156
 157     // Count the Participant by Role ID for Event.
 158     $role = "
 159             SELECT civicrm_participant.role_id         as ROLEID,
 160                    COUNT( civicrm_participant.id )     as participant,
 161                    SUM(civicrm_participant.fee_amount) as amount,
 162                    civicrm_participant.event_id        as event_id,
 163                    civicrm_participant.fee_currency    as currency
 164             FROM     civicrm_participant
 165
 166             WHERE    civicrm_participant.event_id IN ( {$eventID}) AND
 167                      civicrm_participant.is_test  = 0
 168                      {$activeParticipantClause}
 169             GROUP BY civicrm_participant.role_id, civicrm_participant.event_id
 170             ";
 171
 172     $roleDAO = CRM_Core_DAO::executeQuery($role);
 173
 174     while ($roleDAO->fetch()) {
 175       // fix for multiple role, CRM-6507
 176       $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID);
 177       foreach ($roles as $roleId) {
 178         if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) {
 179           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0;
 180           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0;
 181           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0;
 182         }
 183         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant;
 184         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount;
 185       }
 186     }
 187
 188     foreach ($roleRows as $eventId => $roleInfo) {
 189       foreach ($participantRole as $roleName) {
 190         if (isset($roleInfo[$roleName])) {
 191           $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2);
 192         }
 193         if (!empty($roleRows[$eventId][$roleName])) {
 194           $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]);
 195         }
 196       }
 197     }
 198
 199     $rows['Role'] = $roleRows;
 200
 201     // Count the Participant by status ID for Event.
 202     $status = "
 203             SELECT civicrm_participant.status_id       as STATUSID,
 204                    COUNT( civicrm_participant.id )     as participant,
 205                    SUM(civicrm_participant.fee_amount) as amount,
 206                    civicrm_participant.event_id        as event_id
 207
 208             FROM     civicrm_participant
 209
 210             WHERE    civicrm_participant.event_id IN ({$eventID}) AND
 211                      civicrm_participant.is_test  = 0
 212                      {$activeParticipantClause}
 213             GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
 214             ";
 215
 216     $statusDAO = CRM_Core_DAO::executeQuery($status);
 217
 218     while ($statusDAO->fetch()) {
 219       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant;
 220       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round(($statusDAO->participant / $count[$statusDAO->event_id]) * 100, 2);
 221       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]);
 222     }
 223
 224     $rows['Status'] = $statusRows;
 225
 226     //Count the Participant by payment instrument ID for Event
 227     //e.g. Credit Card, Check,Cash etc
 228     $paymentInstrument = "
 229             SELECT c.payment_instrument_id               as INSTRUMENT,
 230                    COUNT( civicrm_participant.id )       as participant,
 231                    SUM( civicrm_participant.fee_amount ) as amount,
 232                    civicrm_participant.event_id          as event_id
 233
 234             FROM      civicrm_participant,
 235             civicrm_participant_payment pp
 236             LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
 237
 238             WHERE     civicrm_participant.event_id IN ( {$eventID} )
 239                       AND civicrm_participant.is_test  = 0
 240                       {$activeParticipantClause}
 241                       AND ((pp.participant_id = civicrm_participant.id )
 242                            OR (pp.participant_id = civicrm_participant.registered_by_id ))
 243             GROUP BY  c.payment_instrument_id, civicrm_participant.event_id
 244             ";
 245
 246     $instrumentDAO = CRM_Core_DAO::executeQuery($paymentInstrument);
 247
 248     while ($instrumentDAO->fetch()) {
 249       //allow only if instrument is present in contribution table
 250       if ($instrumentDAO->INSTRUMENT) {
 251         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant;
 252         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round(($instrumentDAO->participant / $count[$instrumentDAO->event_id]) * 100, 2);
 253         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = CRM_Utils_Money::format($instrumentDAO->amount, $currency[$instrumentDAO->event_id]);
 254       }
 255     }
 256     $rows['Payment Method'] = $instrumentRows;
 257
 258     $this->assign_by_ref('rows', $rows);
 259     if (!$this->_setVariable) {
 260       $this->_params['id_value'] = NULL;
 261     }
 262     $this->assign('statistics', $this->statistics($eventIDs));
 263   }
 264
 265   /**
 266    * @param $eventIDs
 267    *
 268    * @return array
 269    */
 270   public function statistics(&$eventIDs) {
 271     $statistics = array();
 272     $count = count($eventIDs);
 273     $this->countStat($statistics, $count);
 274     if ($this->_setVariable) {
 275       $this->filterStat($statistics);
 276     }
 277
 278     return $statistics;
 279   }
 280
 281   /**
 282    * @inheritdoc
 283    */
 284   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
 285     parent::limit($rowCount);
 286
 287     // Modify limit.
 288     $pageId = $this->get(CRM_Utils_Pager::PAGE_ID);
 289
 290     //if pageId is greater than last page then display last page.
 291     if ((($pageId * self::ROW_COUNT_LIMIT) - 1) > $this->_rowsFound) {
 292       $pageId = ceil((float) $this->_rowsFound / (float) self::ROW_COUNT_LIMIT);
 293       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
 294     }
 295     $this->_limit = ($pageId - 1) * self::ROW_COUNT_LIMIT;
 296   }
 297
 298   /**
 299    * @param int $rowCount
 300    */
 301   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 302     $params = array(
 303       'total' => $this->_rowsFound,
 304       'rowCount' => self::ROW_COUNT_LIMIT,
 305       'status' => ts('Records %%StatusMessage%%'),
 306       'buttonBottom' => 'PagerBottomButton',
 307       'buttonTop' => 'PagerTopButton',
 308       'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
 309     );
 310
 311     $pager = new CRM_Utils_Pager($params);
 312     $this->assign_by_ref('pager', $pager);
 313   }
 314
 315   /**
 316    * Form post process function.
 317    *
 318    * @return bool
 319    */
 320   public function postProcess() {
 321     $this->beginPostProcess();
 322     $this->_setVariable = TRUE;
 323
 324     $noSelection = FALSE;
 325     if (empty($this->_params['id_value'])) {
 326       $this->_params['id_value'] = array();
 327       $this->_setVariable = FALSE;
 328
 329       $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 330         "is_template = 0"
 331       );
 332       if (empty($events)) {
 333         return FALSE;
 334       }
 335       foreach ($events as $key => $dnt) {
 336         $this->_params['id_value'][] = $key;
 337       }
 338       $noSelection = TRUE;
 339     }
 340     elseif (!is_array($this->_params['id_value'])) {
 341       $this->_params['id_value'] = explode(',', $this->_params['id_value']);
 342     }
 343
 344     $this->_rowsFound = count($this->_params['id_value']);
 345
 346     //set pager and limit if output mode is html
 347     if ($this->_outputMode == 'html') {
 348       $this->limit();
 349       $this->setPager();
 350
 351       $showEvents = array();
 352       $count = 0;
 353       $numRows = $this->_limit;
 354
 355       if (CRM_Utils_Array::value('id_op', $this->_params, 'in') == 'in' || $noSelection) {
 356         while ($count < self::ROW_COUNT_LIMIT) {
 357           if (!isset($this->_params['id_value'][$numRows])) {
 358             break;
 359           }
 360
 361           $showEvents[] = $this->_params['id_value'][$numRows];
 362           $count++;
 363           $numRows++;
 364         }
 365       }
 366       elseif ($this->_params['id_op'] == 'notin') {
 367         $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 368           "is_template = 0"
 369         );
 370
 371         $showEvents = array_diff(array_keys($events), $this->_params['id_value']);
 372       }
 373
 374       $this->buildEventReport($showEvents);
 375
 376     }
 377     else {
 378       $this->buildEventReport($this->_params['id_value']);
 379     }
 380
 381     parent::endPostProcess();
 382   }
 383
 384 }