crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Contact/Form/Search/Custom/EventAggregate.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_Contact_Form_Search_Custom_EventAggregate extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38   protected $_aclFrom = NULL;
  39   protected $_aclWhere = NULL;
  40   public $_permissionedComponent;
  41
  42   /**
  43    * @param $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   public function all(
 116     $offset = 0, $rowcount = 0, $sort = NULL,
 117     $includeContactIDs = FALSE, $justIDs = FALSE
 118   ) {
 119     // SELECT clause must include contact_id as an alias for civicrm_contact.id if you are going to use "tasks" like export etc.
 120     $select = "civicrm_participant.event_id as event_id,
 121         COUNT(civicrm_participant.id) as participant_count,
 122         GROUP_CONCAT(DISTINCT(civicrm_event.title)) as event_name,
 123         civicrm_event.event_type_id as event_type_id,
 124         civicrm_option_value.label as event_type,
 125         IF(civicrm_contribution.payment_instrument_id <>0 , 'Yes', 'No') as payment_instrument_id,
 126         SUM(civicrm_contribution.total_amount) as payment_amount,
 127         format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
 128         format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
 129
 130     $from = $this->from();
 131
 132     $onLine = CRM_Utils_Array::value('paid_online',
 133       $this->_formValues
 134     );
 135     if ($onLine) {
 136       $from .= "
 137         inner join civicrm_entity_financial_trxn
 138         on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
 139     }
 140
 141     $showPayees = CRM_Utils_Array::value('show_payees',
 142       $this->_formValues
 143     );
 144     if ($showPayees) {
 145       $select .= ",  GROUP_CONCAT(DISTINCT(civicrm_contact.display_name)) as participant ";
 146       $from .= " inner join civicrm_contact
 147                          on civicrm_contact.id = civicrm_participant.contact_id";
 148     }
 149     else {
 150       unset($this->_columns['Participant']);
 151     }
 152
 153     $where = $this->where();
 154
 155     $groupBy = "event_id";
 156     if (!empty($this->_formValues['event_type_id'])) {
 157       $groupBy = "event_type_id";
 158     }
 159
 160     $sql = "
 161         SELECT $select
 162         FROM   $from
 163         WHERE  $where
 164         GROUP BY $groupBy
 165         ";
 166     // Define ORDER BY for query in $sort, with default value
 167     if (!empty($sort)) {
 168       if (is_string($sort)) {
 169         $sql .= " ORDER BY $sort ";
 170       }
 171       else {
 172         $sql .= " ORDER BY " . trim($sort->orderBy());
 173       }
 174     }
 175     else {
 176       $sql .= "ORDER BY event_name desc";
 177     }
 178
 179     if ($rowcount > 0 && $offset >= 0) {
 180       $offset = CRM_Utils_Type::escape($offset, 'Int');
 181       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 182       $sql .= " LIMIT $offset, $rowcount ";
 183     }
 184     return $sql;
 185   }
 186
 187   /**
 188    * @return string
 189    */
 190   public function from() {
 191     $this->buildACLClause('contact_a');
 192     $from = "
 193         civicrm_participant_payment
 194         left join civicrm_participant
 195         on civicrm_participant_payment.participant_id=civicrm_participant.id
 196
 197         left join civicrm_contact contact_a
 198         on civicrm_participant.contact_id = contact_a.id
 199
 200         left join civicrm_event on
 201         civicrm_participant.event_id = civicrm_event.id
 202
 203         left join civicrm_contribution
 204         on civicrm_contribution.id = civicrm_participant_payment.contribution_id
 205
 206         left join civicrm_option_value on
 207         ( civicrm_option_value.value = civicrm_event.event_type_id AND civicrm_option_value.option_group_id = 14) {$this->_aclFrom}";
 208
 209     return $from;
 210   }
 211
 212   /**
 213    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values.
 214    *
 215    * @param bool $includeContactIDs
 216    *
 217    * @return string
 218    */
 219   public function where($includeContactIDs = FALSE) {
 220     $clauses = array();
 221
 222     $clauses[] = "civicrm_participant.status_id in ( 1 )";
 223     $clauses[] = "civicrm_contribution.is_test = 0";
 224     $onLine = CRM_Utils_Array::value('paid_online',
 225       $this->_formValues
 226     );
 227     if ($onLine) {
 228       $clauses[] = "civicrm_contribution.payment_instrument_id <> 0";
 229     }
 230
 231     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 232     if ($startDate) {
 233       $clauses[] = "civicrm_contribution.receive_date >= $startDate";
 234     }
 235
 236     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 237     if ($endDate) {
 238       $clauses[] = "civicrm_contribution.receive_date <= {$endDate}235959";
 239     }
 240
 241     if (!empty($this->_formValues['event_id'])) {
 242       $clauses[] = "civicrm_event.id = {$this->_formValues['event_id']}";
 243     }
 244
 245     if ($includeContactIDs) {
 246       $contactIDs = array();
 247       foreach ($this->_formValues as $id => $value) {
 248         if ($value &&
 249           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 250         ) {
 251           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 252         }
 253       }
 254
 255       if (!empty($contactIDs)) {
 256         $contactIDs = implode(', ', $contactIDs);
 257         $clauses[] = "contact.id IN ( $contactIDs )";
 258       }
 259     }
 260
 261     if (!empty($this->_formValues['event_type_id'])) {
 262       $event_type_ids = implode(',', array_keys($this->_formValues['event_type_id']));
 263       $clauses[] = "civicrm_event.event_type_id IN ( $event_type_ids )";
 264     }
 265     if ($this->_aclWhere) {
 266       $clauses[] = "{$this->_aclWhere} ";
 267     }
 268     return implode(' AND ', $clauses);
 269   }
 270
 271
 272   /* This function does a query to get totals for some of the search result columns and returns a totals array. */
 273   /**
 274    * @return array
 275    */
 276   public function summary() {
 277     $totalSelect = "
 278         SUM(civicrm_contribution.total_amount) as payment_amount,COUNT(civicrm_participant.id) as participant_count,
 279         format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
 280         format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
 281
 282     $from = $this->from();
 283
 284     $onLine = CRM_Utils_Array::value('paid_online',
 285       $this->_formValues
 286     );
 287     if ($onLine) {
 288       $from .= "
 289         inner join civicrm_entity_financial_trxn
 290         on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
 291     }
 292
 293     $where = $this->where();
 294
 295     $sql = "
 296         SELECT  $totalSelect
 297         FROM    $from
 298         WHERE   $where
 299         ";
 300
 301     $dao = CRM_Core_DAO::executeQuery($sql,
 302       CRM_Core_DAO::$_nullArray
 303     );
 304     $totals = array();
 305     while ($dao->fetch()) {
 306       $totals['payment_amount'] = $dao->payment_amount;
 307       $totals['fee'] = $dao->fee;
 308       $totals['net_payment'] = $dao->net_payment;
 309       $totals['participant_count'] = $dao->participant_count;
 310     }
 311     return $totals;
 312   }
 313
 314   /*
 315    * Functions below generally don't need to be modified
 316    */
 317
 318   /**
 319    * @inheritDoc
 320    */
 321   public function count() {
 322     $sql = $this->all();
 323
 324     $dao = CRM_Core_DAO::executeQuery($sql,
 325       CRM_Core_DAO::$_nullArray
 326     );
 327     return $dao->N;
 328   }
 329
 330   /**
 331    * @param int $offset
 332    * @param int $rowcount
 333    * @param null $sort
 334    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 335    *
 336    * @return string
 337    */
 338   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 339     return $this->all($offset, $rowcount, $sort);
 340   }
 341
 342   /**
 343    * @return array
 344    */
 345   public function &columns() {
 346     return $this->_columns;
 347   }
 348
 349   /**
 350    * @param $title
 351    */
 352   public function setTitle($title) {
 353     if ($title) {
 354       CRM_Utils_System::setTitle($title);
 355     }
 356     else {
 357       CRM_Utils_System::setTitle(ts('Search'));
 358     }
 359   }
 360
 361   /**
 362    * @param string $tableAlias
 363    */
 364   public function buildACLClause($tableAlias = 'contact') {
 365     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 366   }
 367
 368 }