CRM/Report/Form/Event/Income.php

   1 <?php
   2 // $Id$
   3
   4 /*
   5  +--------------------------------------------------------------------+
   6  | CiviCRM version 4.3                                                |
   7  +--------------------------------------------------------------------+
   8  | Copyright CiviCRM LLC (c) 2004-2013                                |
   9  +--------------------------------------------------------------------+
  10  | This file is a part of CiviCRM.                                    |
  11  |                                                                    |
  12  | CiviCRM is free software; you can copy, modify, and distribute it  |
  13  | under the terms of the GNU Affero General Public License           |
  14  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  15  |                                                                    |
  16  | CiviCRM is distributed in the hope that it will be useful, but     |
  17  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  18  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  19  | See the GNU Affero General Public License for more details.        |
  20  |                                                                    |
  21  | You should have received a copy of the GNU Affero General Public   |
  22  | License and the CiviCRM Licensing Exception along                  |
  23  | with this program; if not, contact CiviCRM LLC                     |
  24  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  25  | GNU Affero General Public License or the licensing of CiviCRM,     |
  26  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  27  +--------------------------------------------------------------------+
  28 */
  29
  30 /**
  31  *
  32  * @package CRM
  33  * @copyright CiviCRM LLC (c) 2004-2013
  34  * $Id$
  35  *
  36  */
  37 class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event {
  38   CONST ROW_COUNT_LIMIT = 2;
  39
  40   protected $_summary = NULL;
  41   protected $_noFields = TRUE;
  42
  43   protected $_add2groupSupported = FALSE;
  44
  45   function __construct() {
  46
  47     $this->_columns = array(
  48       'civicrm_event' =>
  49       array(
  50         'dao' => 'CRM_Event_DAO_Event',
  51         'filters' =>
  52         array(
  53           'id' =>
  54           array('title' => ts('Event Title'),
  55             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  56             'type' => CRM_Utils_Type::T_INT,
  57             'options' => $this->getEventFilterOptions(),
  58           ),
  59         ),
  60       ),
  61     );
  62
  63     parent::__construct();
  64   }
  65
  66   function preProcess() {
  67     $this->_csvSupported = FALSE;
  68     parent::preProcess();
  69   }
  70
  71   function buildEventReport($eventIDs) {
  72
  73     $this->assign('events', $eventIDs);
  74
  75     $eventID = implode(',', $eventIDs);
  76
  77     $participantStatus  = CRM_Event_PseudoConstant::participantStatus(NULL, "is_counted = 1");
  78     $participantRole    = CRM_Event_PseudoConstant::participantRole();
  79     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
  80
  81     $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
  82
  83     $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
  84     $optionGroupDAO->name = 'event_type';
  85     $optionGroupId = NULL;
  86     if ($optionGroupDAO->find(TRUE)) {
  87       $optionGroupId = $optionGroupDAO->id;
  88     }
  89     //show the income of active participant status (Counted = filter = 1)
  90     $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array();
  91     foreach ($participantStatus as $id => $label) {
  92       $activeParticipantStatusIDArray[] = $id;
  93       $activeParticipantStatusLabelArray[] = $label;
  94     }
  95     $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
  96     $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
  97     $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) ";
  98
  99     $sql = "
 100             SELECT  civicrm_event.id                    as event_id,
 101                     civicrm_event.title                 as event_title,
 102                     civicrm_event.max_participants      as max_participants,
 103                     civicrm_event.start_date            as start_date,
 104                     civicrm_event.end_date              as end_date,
 105                     civicrm_option_value.label          as event_type,
 106                     civicrm_participant.fee_currency    as currency,
 107                     SUM(civicrm_participant.fee_amount) as total,
 108                     COUNT(civicrm_participant.id)       as participant
 109
 110             FROM       civicrm_event
 111             LEFT JOIN  civicrm_option_value
 112                    ON  ( civicrm_event.event_type_id = civicrm_option_value.value AND
 113                          civicrm_option_value.option_group_id = {$optionGroupId} )
 114             LEFT JOIN  civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id
 115                        {$activeParticipantClause} AND civicrm_participant.is_test  = 0 )
 116
 117             WHERE      civicrm_event.id IN( {$eventID})
 118
 119             GROUP BY   civicrm_event.id
 120             ";
 121
 122     $eventDAO = CRM_Core_DAO::executeQuery($sql);
 123     $currency = array();
 124     while ($eventDAO->fetch()) {
 125       $eventSummary[$eventDAO->event_id]['Title'] = $eventDAO->event_title;
 126       $eventSummary[$eventDAO->event_id]['Max Participants'] = $eventDAO->max_participants;
 127       $eventSummary[$eventDAO->event_id]['Start Date'] = CRM_Utils_Date::customFormat($eventDAO->start_date);
 128       $eventSummary[$eventDAO->event_id]['End Date'] = CRM_Utils_Date::customFormat($eventDAO->end_date);
 129       $eventSummary[$eventDAO->event_id]['Event Type'] = $eventDAO->event_type;
 130       $eventSummary[$eventDAO->event_id]['Event Income'] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency);
 131       $eventSummary[$eventDAO->event_id]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
 132       $currency[$eventDAO->event_id] = $eventDAO->currency;
 133     }
 134     $this->assign_by_ref('summary', $eventSummary);
 135
 136     //Total Participant Registerd for the Event
 137     $pariticipantCount = "
 138             SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id
 139
 140             FROM     civicrm_participant
 141
 142             WHERE    civicrm_participant.event_id IN( {$eventID}) AND
 143                      civicrm_participant.is_test  = 0
 144                      {$activeParticipantClause}
 145             GROUP BY civicrm_participant.event_id
 146              ";
 147
 148     $counteDAO = CRM_Core_DAO::executeQuery($pariticipantCount);
 149     while ($counteDAO->fetch()) {
 150       $count[$counteDAO->event_id] = $counteDAO->count;
 151     }
 152
 153     //Count the Participant by Role ID for Event
 154     $role = "
 155             SELECT civicrm_participant.role_id         as ROLEID,
 156                    COUNT( civicrm_participant.id )     as participant,
 157                    SUM(civicrm_participant.fee_amount) as amount,
 158                    civicrm_participant.event_id        as event_id,
 159                    civicrm_participant.fee_currency    as currency
 160             FROM     civicrm_participant
 161
 162             WHERE    civicrm_participant.event_id IN ( {$eventID}) AND
 163                      civicrm_participant.is_test  = 0
 164                      {$activeParticipantClause}
 165             GROUP BY civicrm_participant.role_id, civicrm_participant.event_id
 166             ";
 167
 168     $roleDAO = CRM_Core_DAO::executeQuery($role);
 169
 170     while ($roleDAO->fetch()) {
 171       // fix for multiple role, CRM-6507
 172       $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID);
 173       foreach ($roles as $roleId) {
 174         if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) {
 175           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0;
 176           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0;
 177           $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0;
 178         }
 179         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant;
 180         $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount;
 181       }
 182       $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = CRM_Utils_Money::format($roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'], $currency[$roleDAO->event_id]);
 183     }
 184
 185     foreach ($roleRows as $eventId => $roleInfo) {
 186       foreach ($participantRole as $roleName) {
 187         if (isset($roleInfo[$roleName])) {
 188           $roleRows[$eventId][$roleName]['round'] = round(($roleRows[$eventId][$roleName]['total'] / $count[$eventId]) * 100, 2);
 189         }
 190       }
 191     }
 192
 193     $rows['Role'] = $roleRows;
 194
 195     //Count the Participant by status ID for Event
 196     $status = "
 197             SELECT civicrm_participant.status_id       as STATUSID,
 198                    COUNT( civicrm_participant.id )     as participant,
 199                    SUM(civicrm_participant.fee_amount) as amount,
 200                    civicrm_participant.event_id        as event_id
 201
 202             FROM     civicrm_participant
 203
 204             WHERE    civicrm_participant.event_id IN ({$eventID}) AND
 205                      civicrm_participant.is_test  = 0
 206                      {$activeParticipantClause}
 207             GROUP BY civicrm_participant.status_id, civicrm_participant.event_id
 208             ";
 209
 210     $statusDAO = CRM_Core_DAO::executeQuery($status);
 211
 212     while ($statusDAO->fetch()) {
 213       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant;
 214       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round(($statusDAO->participant / $count[$statusDAO->event_id]) * 100, 2);
 215       $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]);
 216     }
 217
 218     $rows['Status'] = $statusRows;
 219
 220     //Count the Participant by payment instrument ID for Event
 221     //e.g. Credit Card, Check,Cash etc
 222     $paymentInstrument = "
 223             SELECT c.payment_instrument_id               as INSTRUMENT,
 224                    COUNT( c.id )                         as participant,
 225                    SUM(civicrm_participant.fee_amount)   as amount,
 226                    civicrm_participant.event_id          as event_id
 227
 228             FROM      civicrm_participant
 229             LEFT JOIN civicrm_participant_payment pp ON(pp.participant_id = civicrm_participant.id )
 230             LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)
 231
 232             WHERE     civicrm_participant.event_id IN ( {$eventID}) AND
 233                       civicrm_participant.is_test  = 0
 234                       {$activeParticipantClause}
 235             GROUP BY  c.payment_instrument_id, civicrm_participant.event_id
 236             ";
 237
 238     $instrumentDAO = CRM_Core_DAO::executeQuery($paymentInstrument);
 239
 240     while ($instrumentDAO->fetch()) {
 241       //allow only if instrument is present in contribution table
 242       if ($instrumentDAO->INSTRUMENT) {
 243         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant;
 244         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round(($instrumentDAO->participant / $count[$instrumentDAO->event_id]) * 100, 2);
 245         $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = $instrumentDAO->amount;
 246       }
 247     }
 248     $rows['Payment Method'] = $instrumentRows;
 249
 250     $this->assign_by_ref('rows', $rows);
 251     if (!$this->_setVariable) {
 252       $this->_params['id_value'] = NULL;
 253     }
 254     $this->assign('statistics', $this->statistics($eventIDs));
 255   }
 256
 257   function statistics(&$eventIDs) {
 258     $statistics = array();
 259     $count = count($eventIDs);
 260     $this->countStat($statistics, $count);
 261     if ($this->_setVariable) {
 262       $this->filterStat($statistics);
 263     }
 264
 265     return $statistics;
 266   }
 267
 268   function limit($rowCount = self::ROW_COUNT_LIMIT) {
 269     parent::limit($rowCount);
 270
 271     //modify limit
 272     $pageId = $this->get(CRM_Utils_Pager::PAGE_ID);
 273
 274     //if pageId is greator than last page then display last page.
 275     if ((($pageId * self::ROW_COUNT_LIMIT) - 1) > $this->_rowsFound) {
 276       $pageId = ceil((float)$this->_rowsFound / (float)self::ROW_COUNT_LIMIT);
 277       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
 278     }
 279     $this->_limit = ($pageId - 1) * self::ROW_COUNT_LIMIT;
 280   }
 281
 282   function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 283     $params = array(
 284       'total' => $this->_rowsFound,
 285       'rowCount' => self::ROW_COUNT_LIMIT,
 286       'status' => ts('Records %%StatusMessage%%'),
 287       'buttonBottom' => 'PagerBottomButton',
 288       'buttonTop' => 'PagerTopButton',
 289       'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
 290     );
 291
 292     $pager = new CRM_Utils_Pager($params);
 293     $this->assign_by_ref('pager', $pager);
 294   }
 295
 296   function postProcess() {
 297     $this->beginPostProcess();
 298     $this->_setVariable = TRUE;
 299
 300     $noSelection = FALSE;
 301     if (empty($this->_params['id_value'][0])) {
 302       $this->_params['id_value'] = array();
 303       $this->_setVariable = FALSE;
 304
 305       $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 306         "is_template IS NULL OR is_template = 0"
 307       );
 308       if (empty($events)) {
 309         return FALSE;
 310       }
 311       foreach ($events as $key => $dnt) {
 312         $this->_params['id_value'][] = $key;
 313       }
 314       $noSelection = TRUE;
 315     }
 316
 317     $this->_rowsFound = count($this->_params['id_value']);
 318
 319     //set pager and limit if output mode is html
 320     if ($this->_outputMode == 'html') {
 321       $this->limit();
 322       $this->setPager();
 323
 324       $showEvents = array();
 325       $count      = 0;
 326       $numRows    = $this->_limit;
 327
 328       if (CRM_Utils_Array::value('id_op', $this->_params, 'in') == 'in' || $noSelection) {
 329         while ($count < self::ROW_COUNT_LIMIT) {
 330           if (!isset($this->_params['id_value'][$numRows])) {
 331             break;
 332           }
 333
 334           $showEvents[] = $this->_params['id_value'][$numRows];
 335           $count++;
 336           $numRows++;
 337         }
 338       } elseif ($this->_params['id_op'] == 'notin') {
 339         $events = CRM_Event_PseudoConstant::event(NULL, NULL,
 340           "is_template IS NULL OR is_template = 0"
 341         );
 342
 343         $showEvents = array_diff(array_keys($events), $this->_params['id_value']);
 344       }
 345
 346       $this->buildEventReport($showEvents);
 347
 348     }
 349     else {
 350       $this->buildEventReport($this->_params['id_value']);
 351     }
 352
 353     parent::endPostProcess();
 354   }
 355 }
 356