3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2014
35 class CRM_Contact_Form_Search_Custom_EventAggregate
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
37 protected $_formValues;
38 public $_permissionedComponent;
43 public function __construct(&$formValues) {
44 $this->_formValues
= $formValues;
45 $this->_permissionedComponent
= array('CiviContribute', 'CiviEvent');
48 * Define the columns for search result rows
50 $this->_columns
= array(
51 ts('Event') => 'event_name',
52 ts('Type') => 'event_type',
53 ts('Number of<br />Participant') => 'participant_count',
54 ts('Total Payment') => 'payment_amount',
56 ts('Net Payment') => 'net_payment',
57 ts('Participant') => 'participant',
62 * @param CRM_Core_Form $form
64 public function buildForm(&$form) {
67 * You can define a custom title for the search form
69 $this->setTitle('Find Totals for Events');
72 * Define the search form fields here
75 $form->addElement('checkbox', 'paid_online', ts('Only show Credit Card Payments'));
77 $form->addElement('checkbox', 'show_payees', ts('Show payees'));
79 $event_type = CRM_Core_OptionGroup
::values('event_type', FALSE);
80 foreach ($event_type as $eventId => $eventName) {
81 $form->addElement('checkbox', "event_type_id[$eventId]", 'Event Type', $eventName);
83 $events = CRM_Event_BAO_Event
::getEvents(1);
84 $form->add('select', 'event_id', ts('Event Name'), array('' => ts('- select -')) +
$events);
86 $form->addDate('start_date', ts('Payments Date From'), FALSE, array('formatType' => 'custom'));
87 $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
90 * If you are using the sample template, this array tells the template fields to render
91 * for the search form.
93 $form->assign('elements', array('paid_online', 'start_date', 'end_date', 'show_payees', 'event_type_id', 'event_id'));
97 * Define the smarty template used to layout the search form and results listings.
99 public function templateFile() {
100 return 'CRM/Contact/Form/Search/Custom/EventDetails.tpl';
104 * Construct the search query
107 $offset = 0, $rowcount = 0, $sort = NULL,
108 $includeContactIDs = FALSE, $justIDs = FALSE
110 // SELECT clause must include contact_id as an alias for civicrm_contact.id if you are going to use "tasks" like export etc.
111 $select = "civicrm_participant.event_id as event_id,
112 COUNT(civicrm_participant.id) as participant_count,
113 GROUP_CONCAT(DISTINCT(civicrm_event.title)) as event_name,
114 civicrm_event.event_type_id as event_type_id,
115 civicrm_option_value.label as event_type,
116 IF(civicrm_contribution.payment_instrument_id <>0 , 'Yes', 'No') as payment_instrument_id,
117 SUM(civicrm_contribution.total_amount) as payment_amount,
118 format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
119 format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
121 $from = $this->from();
123 $onLine = CRM_Utils_Array
::value('paid_online',
128 inner join civicrm_entity_financial_trxn
129 on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
132 $showPayees = CRM_Utils_Array
::value('show_payees',
136 $select .= ", GROUP_CONCAT(DISTINCT(civicrm_contact.display_name)) as participant ";
137 $from .= " inner join civicrm_contact
138 on civicrm_contact.id = civicrm_participant.contact_id";
141 unset($this->_columns
['Participant']);
144 $where = $this->where();
146 $groupBy = "event_id";
147 if (!empty($this->_formValues
['event_type_id'])) {
148 $groupBy = "event_type_id";
157 // Define ORDER BY for query in $sort, with default value
159 if (is_string($sort)) {
160 $sql .= " ORDER BY $sort ";
163 $sql .= " ORDER BY " . trim($sort->orderBy());
167 $sql .= "ORDER BY event_name desc";
170 if ($rowcount > 0 && $offset >= 0) {
171 $offset = CRM_Utils_Type
::escape($offset, 'Int');
172 $rowcount = CRM_Utils_Type
::escape($rowcount, 'Int');
173 $sql .= " LIMIT $offset, $rowcount ";
181 public function from() {
183 civicrm_participant_payment
184 left join civicrm_participant
185 on civicrm_participant_payment.participant_id=civicrm_participant.id
187 left join civicrm_event on
188 civicrm_participant.event_id = civicrm_event.id
190 left join civicrm_contribution
191 on civicrm_contribution.id = civicrm_participant_payment.contribution_id
193 left join civicrm_option_value on
194 ( civicrm_option_value.value = civicrm_event.event_type_id AND civicrm_option_value.option_group_id = 14)";
198 * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
200 * @param bool $includeContactIDs
204 public function where($includeContactIDs = FALSE) {
207 $clauses[] = "civicrm_participant.status_id in ( 1 )";
208 $clauses[] = "civicrm_contribution.is_test = 0";
209 $onLine = CRM_Utils_Array
::value('paid_online',
213 $clauses[] = "civicrm_contribution.payment_instrument_id <> 0";
216 $startDate = CRM_Utils_Date
::processDate($this->_formValues
['start_date']);
218 $clauses[] = "civicrm_contribution.receive_date >= $startDate";
221 $endDate = CRM_Utils_Date
::processDate($this->_formValues
['end_date']);
223 $clauses[] = "civicrm_contribution.receive_date <= {$endDate}235959";
226 if (!empty($this->_formValues
['event_id'])) {
227 $clauses[] = "civicrm_event.id = {$this->_formValues['event_id']}";
230 if ($includeContactIDs) {
231 $contactIDs = array();
232 foreach ($this->_formValues
as $id => $value) {
234 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
236 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
240 if (!empty($contactIDs)) {
241 $contactIDs = implode(', ', $contactIDs);
242 $clauses[] = "contact.id IN ( $contactIDs )";
246 if (!empty($this->_formValues
['event_type_id'])) {
247 $event_type_ids = implode(',', array_keys($this->_formValues
['event_type_id']));
248 $clauses[] = "civicrm_event.event_type_id IN ( $event_type_ids )";
250 return implode(' AND ', $clauses);
254 /* This function does a query to get totals for some of the search result columns and returns a totals array. */
258 public function summary() {
260 SUM(civicrm_contribution.total_amount) as payment_amount,COUNT(civicrm_participant.id) as participant_count,
261 format(sum(if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0)),2) as fee,
262 format(sum(civicrm_contribution.total_amount - (if(civicrm_contribution.payment_instrument_id <>0,(civicrm_contribution.total_amount *.034) +.45,0))),2) as net_payment";
264 $from = $this->from();
266 $onLine = CRM_Utils_Array
::value('paid_online',
271 inner join civicrm_entity_financial_trxn
272 on (civicrm_entity_financial_trxn.entity_id = civicrm_participant_payment.contribution_id and civicrm_entity_financial_trxn.entity_table='civicrm_contribution')";
275 $where = $this->where();
283 $dao = CRM_Core_DAO
::executeQuery($sql,
284 CRM_Core_DAO
::$_nullArray
287 while ($dao->fetch()) {
288 $totals['payment_amount'] = $dao->payment_amount
;
289 $totals['fee'] = $dao->fee
;
290 $totals['net_payment'] = $dao->net_payment
;
291 $totals['participant_count'] = $dao->participant_count
;
297 * Functions below generally don't need to be modified
299 public function count() {
302 $dao = CRM_Core_DAO
::executeQuery($sql,
303 CRM_Core_DAO
::$_nullArray
310 * @param int $rowcount
315 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
316 return $this->all($offset, $rowcount, $sort);
322 public function &columns() {
323 return $this->_columns
;
329 public function setTitle($title) {
331 CRM_Utils_System
::setTitle($title);
334 CRM_Utils_System
::setTitle(ts('Search'));