CRM/Contact/Form/Search/Custom/EventAggregate.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  */
  33 class CRM_Contact_Form_Search_Custom_EventAggregate extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  34
  35   protected $_formValues;
  36   protected $_aclFrom = NULL;
  37   protected $_aclWhere = NULL;
  38   public $_permissionedComponent;
  39
  40   /**
  41    * Class constructor.
  42    *
  43    * @param array $formValues
  44    */
  45   public function __construct(&$formValues) {
  46     $this->_formValues = $formValues;
  47     $this->_permissionedComponent = array('CiviContribute', 'CiviEvent');
  48
  49     /**
  50      * Define the columns for search result rows
  51      */
  52     $this->_columns = array(
  53       ts('Event') => 'event_name',
  54       ts('Type') => 'event_type',
  55       ts('Number of<br />Participant') => 'participant_count',
  56       ts('Total Payment') => 'payment_amount',
  57       ts('Fee') => 'fee',
  58       ts('Net Payment') => 'net_payment',
  59       ts('Participant') => 'participant',
  60     );
  61   }
  62
  63   /**
  64    * @param CRM_Core_Form $form
  65    */
  66   public function buildForm(&$form) {
  67
  68     /**
  69      * You can define a custom title for the search form
  70      */
  71     $this->setTitle('Find Totals for Events');
  72
  73     /**
  74      * Define the search form fields here
  75      */
  76
  77     $form->addElement('checkbox', 'paid_online', ts('Only show Credit Card Payments'));
  78
  79     $form->addElement('checkbox', 'show_payees', ts('Show payees'));
  80
  81     $event_type = CRM_Core_OptionGroup::values('event_type', FALSE);
  82     foreach ($event_type as $eventId => $eventName) {
  83       $form->addElement('checkbox', "event_type_id[$eventId]", 'Event Type', $eventName);
  84     }
  85     $events = CRM_Event_BAO_Event::getEvents(1);
  86     $form->add('select', 'event_id', ts('Event Name'), array('' => ts('- select -')) + $events);
  87
  88     $form->addDate('start_date', ts('Payments Date From'), FALSE, array('formatType' => 'custom'));
  89     $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
  90
  91     /**
  92      * If you are using the sample template, this array tells the template fields to render
  93      * for the search form.
  94      */
  95     $form->assign('elements', array(
  96         'paid_online',
  97         'start_date',
  98         'end_date',
  99         'show_payees',
 100         'event_type_id',
 101         'event_id',
 102       ));
 103   }
 104
 105   /**
 106    * Define the smarty template used to layout the search form and results listings.
 107    */
 108   public function templateFile() {
 109     return 'CRM/Contact/Form/Search/Custom/EventDetails.tpl';
 110   }
 111
 112   /**
 113    * Construct the search query.
 114    *
 115    * @param int $offset
 116    * @param int $rowcount
 117    * @param null $sort
 118    * @param bool $includeContactIDs
 119    * @param bool $justIDs
 120    *
 121    * @return string
 122    */
 123   public function all(
 124     $offset = 0, $rowcount = 0, $sort = NULL,
 125     $includeContactIDs = FALSE, $justIDs = FALSE
 126   ) {
 127     // SELECT clause must include contact_id as an alias for civicrm_contact.id if you are going to use "tasks" like export etc.
 128     $select = "civicrm_participant.event_id as event_id,
 129         COUNT(civicrm_participant.id) as participant_count,
 130         GROUP_CONCAT(DISTINCT(civicrm_event.title)) as event_name,
 131         civicrm_event.event_type_id as event_type_id,
 132         civicrm_option_value.label as event_type,
 133         IF(civicrm_contribution.payment_instrument_id <>0 , 'Yes', 'No') as payment_instrument_id,
 134         SUM(civicrm_contribution.total_amount) as payment_amount,
 135         format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
 136         format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
 137
 138     $from = $this->from();
 139
 140     $onLine = CRM_Utils_Array::value('paid_online',
 141       $this->_formValues
 142     );
 143     if ($onLine) {
 144       $from .= "
 145         inner join civicrm_entity_financial_trxn
 146         on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
 147     }
 148
 149     $showPayees = CRM_Utils_Array::value('show_payees',
 150       $this->_formValues
 151     );
 152     if ($showPayees) {
 153       $select .= ",  GROUP_CONCAT(DISTINCT(civicrm_contact.display_name)) as participant ";
 154       $from .= " inner join civicrm_contact
 155                          on civicrm_contact.id = civicrm_participant.contact_id";
 156     }
 157     else {
 158       unset($this->_columns[ts('Participant')]);
 159     }
 160
 161     $where = $this->where();
 162     $groupFromSelect = "civicrm_option_value.label, civicrm_contribution.payment_instrument_id";
 163
 164     $groupBy = "event_id, event_type_id, {$groupFromSelect}";
 165     if (!empty($this->_formValues['event_type_id'])) {
 166       $groupBy = "event_type_id, event_id, {$groupFromSelect}";
 167     }
 168
 169     $sql = "
 170         SELECT $select
 171         FROM   $from
 172         WHERE  $where
 173         GROUP BY $groupBy
 174         ";
 175     // Define ORDER BY for query in $sort, with default value
 176     if (!empty($sort)) {
 177       if (is_string($sort)) {
 178         $sql .= " ORDER BY $sort ";
 179       }
 180       else {
 181         $sql .= " ORDER BY " . trim($sort->orderBy());
 182       }
 183     }
 184     else {
 185       $sql .= "ORDER BY event_name desc";
 186     }
 187
 188     if ($rowcount > 0 && $offset >= 0) {
 189       $offset = CRM_Utils_Type::escape($offset, 'Int');
 190       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 191       $sql .= " LIMIT $offset, $rowcount ";
 192     }
 193     return $sql;
 194   }
 195
 196   /**
 197    * @return string
 198    */
 199   public function from() {
 200     $this->buildACLClause('contact_a');
 201     $from = "
 202         civicrm_participant_payment
 203         left join civicrm_participant
 204         on civicrm_participant_payment.participant_id=civicrm_participant.id
 205
 206         left join civicrm_contact contact_a
 207         on civicrm_participant.contact_id = contact_a.id
 208
 209         left join civicrm_event on
 210         civicrm_participant.event_id = civicrm_event.id
 211
 212         left join civicrm_contribution
 213         on civicrm_contribution.id = civicrm_participant_payment.contribution_id
 214
 215         left join civicrm_option_value on
 216         ( civicrm_option_value.value = civicrm_event.event_type_id AND civicrm_option_value.option_group_id = 14) {$this->_aclFrom}";
 217
 218     return $from;
 219   }
 220
 221   /**
 222    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values.
 223    *
 224    * @param bool $includeContactIDs
 225    *
 226    * @return string
 227    */
 228   public function where($includeContactIDs = FALSE) {
 229     $clauses = array();
 230
 231     $clauses[] = "civicrm_participant.status_id in ( 1 )";
 232     $clauses[] = "civicrm_contribution.is_test = 0";
 233     $onLine = CRM_Utils_Array::value('paid_online',
 234       $this->_formValues
 235     );
 236     if ($onLine) {
 237       $clauses[] = "civicrm_contribution.payment_instrument_id <> 0";
 238     }
 239
 240     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 241     if ($startDate) {
 242       $clauses[] = "civicrm_contribution.receive_date >= $startDate";
 243     }
 244
 245     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 246     if ($endDate) {
 247       $clauses[] = "civicrm_contribution.receive_date <= {$endDate}235959";
 248     }
 249
 250     if (!empty($this->_formValues['event_id'])) {
 251       $clauses[] = "civicrm_event.id = {$this->_formValues['event_id']}";
 252     }
 253
 254     if ($includeContactIDs) {
 255       $contactIDs = array();
 256       foreach ($this->_formValues as $id => $value) {
 257         if ($value &&
 258           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 259         ) {
 260           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 261         }
 262       }
 263
 264       if (!empty($contactIDs)) {
 265         $contactIDs = implode(', ', $contactIDs);
 266         $clauses[] = "contact.id IN ( $contactIDs )";
 267       }
 268     }
 269
 270     if (!empty($this->_formValues['event_type_id'])) {
 271       $event_type_ids = implode(',', array_keys($this->_formValues['event_type_id']));
 272       $clauses[] = "civicrm_event.event_type_id IN ( $event_type_ids )";
 273     }
 274     if ($this->_aclWhere) {
 275       $clauses[] = "{$this->_aclWhere} ";
 276     }
 277     return implode(' AND ', $clauses);
 278   }
 279
 280
 281   /* This function does a query to get totals for some of the search result columns and returns a totals array. */
 282   /**
 283    * @return array
 284    */
 285   public function summary() {
 286     $totalSelect = "
 287         SUM(civicrm_contribution.total_amount) as payment_amount,COUNT(civicrm_participant.id) as participant_count,
 288         format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
 289         format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
 290
 291     $from = $this->from();
 292
 293     $onLine = CRM_Utils_Array::value('paid_online',
 294       $this->_formValues
 295     );
 296     if ($onLine) {
 297       $from .= "
 298         inner join civicrm_entity_financial_trxn
 299         on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
 300     }
 301
 302     $where = $this->where();
 303
 304     $sql = "
 305         SELECT  $totalSelect
 306         FROM    $from
 307         WHERE   $where
 308         ";
 309
 310     $dao = CRM_Core_DAO::executeQuery($sql,
 311       CRM_Core_DAO::$_nullArray
 312     );
 313     $totals = array();
 314     while ($dao->fetch()) {
 315       $totals['payment_amount'] = $dao->payment_amount;
 316       $totals['fee'] = $dao->fee;
 317       $totals['net_payment'] = $dao->net_payment;
 318       $totals['participant_count'] = $dao->participant_count;
 319     }
 320     return $totals;
 321   }
 322
 323   /*
 324    * Functions below generally don't need to be modified
 325    */
 326
 327   /**
 328    * @inheritDoc
 329    */
 330   public function count() {
 331     $sql = $this->all();
 332
 333     $dao = CRM_Core_DAO::executeQuery($sql,
 334       CRM_Core_DAO::$_nullArray
 335     );
 336     return $dao->N;
 337   }
 338
 339   /**
 340    * @param int $offset
 341    * @param int $rowcount
 342    * @param null $sort
 343    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 344    *
 345    * @return string
 346    */
 347   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 348     return $this->all($offset, $rowcount, $sort);
 349   }
 350
 351   /**
 352    * @return array
 353    */
 354   public function &columns() {
 355     return $this->_columns;
 356   }
 357
 358   /**
 359    * @param $title
 360    */
 361   public function setTitle($title) {
 362     if ($title) {
 363       CRM_Utils_System::setTitle($title);
 364     }
 365     else {
 366       CRM_Utils_System::setTitle(ts('Search'));
 367     }
 368   }
 369
 370   /**
 371    * @param string $tableAlias
 372    */
 373   public function buildACLClause($tableAlias = 'contact') {
 374     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 375   }
 376
 377 }