3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
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-2016
33 class CRM_Contribute_BAO_Query
extends CRM_Core_BAO_Query
{
36 * Static field for all the export/import contribution fields.
40 static $_contributionFields = NULL;
42 static $_contribOrSoftCredit = "only_contribs";
44 static $_contribRecurPayment = NULL;
47 * Function get the import/export fields for contribution.
49 * @param bool $checkPermission
52 * Associative array of contribution fields
54 public static function getFields($checkPermission = TRUE) {
55 if (!self
::$_contributionFields) {
56 self
::$_contributionFields = array();
58 $fields = CRM_Contribute_BAO_Contribution
::exportableFields($checkPermission);
60 unset($fields['contribution_contact_id']);
62 self
::$_contributionFields = $fields;
64 return self
::$_contributionFields;
68 * If contributions are involved, add the specific contribute fields.
70 * @param CRM_Contact_BAO_Query $query
72 public static function select(&$query) {
73 // if contribute mode add contribution id
74 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
75 $query->_select
['contribution_id'] = "civicrm_contribution.id as contribution_id";
76 $query->_element
['contribution_id'] = 1;
77 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
81 if (!empty($query->_returnProperties
['financial_type'])) {
82 $query->_select
['financial_type'] = "civicrm_financial_type.name as financial_type";
83 $query->_element
['financial_type'] = 1;
84 $query->_tables
['civicrm_contribution'] = 1;
85 $query->_tables
['civicrm_financial_type'] = 1;
88 // get accounting code
89 if (!empty($query->_returnProperties
['accounting_code'])) {
90 $query->_select
['accounting_code'] = "civicrm_financial_account.accounting_code as accounting_code";
91 $query->_element
['accounting_code'] = 1;
92 $query->_tables
['civicrm_accounting_code'] = 1;
93 $query->_tables
['civicrm_financial_account'] = 1;
96 if (!empty($query->_returnProperties
['contribution_note'])) {
97 $query->_select
['contribution_note'] = "civicrm_note.note as contribution_note";
98 $query->_element
['contribution_note'] = 1;
99 $query->_tables
['contribution_note'] = 1;
100 $query->_tables
['civicrm_contribution'] = 1;
103 if (!empty($query->_returnProperties
['contribution_batch'])) {
104 $query->_select
['contribution_batch'] = "civicrm_batch.title as contribution_batch";
105 $query->_element
['contribution_batch'] = 1;
106 $query->_tables
['contribution_batch'] = 1;
109 if (!empty($query->_returnProperties
['contribution_source'])) {
110 $query->_select
['contribution_source'] = "civicrm_contribution.source as contribution_source";
111 $query->_element
['contribution_source'] = 1;
112 $query->_tables
['civicrm_contribution'] = 1;
115 // get contribution_status
116 if (!empty($query->_returnProperties
['contribution_status_id'])) {
117 $query->_select
['contribution_status_id'] = "contribution_status.value as contribution_status_id";
118 $query->_element
['contribution_status_id'] = 1;
119 $query->_tables
['civicrm_contribution'] = 1;
120 $query->_tables
['contribution_status'] = 1;
123 // get contribution_status label
124 if (!empty($query->_returnProperties
['contribution_status'])) {
125 $query->_select
['contribution_status'] = "contribution_status.label as contribution_status";
126 $query->_element
['contribution_status'] = 1;
127 $query->_tables
['civicrm_contribution'] = 1;
128 $query->_tables
['contribution_status'] = 1;
131 // get payment instrument
132 if (!empty($query->_returnProperties
['payment_instrument'])) {
133 $query->_select
['payment_instrument'] = "contribution_payment_instrument.label as payment_instrument";
134 $query->_element
['payment_instrument'] = 1;
135 $query->_tables
['civicrm_contribution'] = 1;
136 $query->_tables
['contribution_payment_instrument'] = 1;
139 // get payment instrument id
140 if (!empty($query->_returnProperties
['payment_instrument_id'])) {
141 $query->_select
['instrument_id'] = "contribution_payment_instrument.value as instrument_id";
142 $query->_select
['payment_instrument_id'] = "contribution_payment_instrument.value as payment_instrument_id";
143 $query->_element
['instrument_id'] = $query->_element
['payment_instrument_id'] = 1;
144 $query->_tables
['civicrm_contribution'] = 1;
145 $query->_tables
['contribution_payment_instrument'] = 1;
148 if (!empty($query->_returnProperties
['check_number'])) {
149 $query->_select
['contribution_check_number'] = "civicrm_contribution.check_number as contribution_check_number";
150 $query->_element
['contribution_check_number'] = 1;
151 $query->_tables
['civicrm_contribution'] = 1;
154 if (!empty($query->_returnProperties
['contribution_campaign_id'])) {
155 $query->_select
['contribution_campaign_id'] = 'civicrm_contribution.campaign_id as contribution_campaign_id';
156 $query->_element
['contribution_campaign_id'] = 1;
157 $query->_tables
['civicrm_contribution'] = 1;
161 $includeSoftCredits = self
::isSoftCreditOptionEnabled($query->_params
);
162 if (!empty($query->_returnProperties
['contribution_soft_credit_name'])) {
163 if ($includeSoftCredits) {
164 $query->_select
['contribution_soft_credit_name'] = "civicrm_contact_d.sort_name as contribution_soft_credit_name";
165 // also include contact id. Will help build hyperlinks
166 $query->_select
['contribution_soft_credit_contact_id'] = "civicrm_contact_d.id as contribution_soft_credit_contact_id";
168 $query->_element
['contribution_soft_credit_name'] = 1;
169 $query->_element
['contribution_soft_credit_contact_id'] = 1;
171 $query->_tables
['civicrm_contribution'] = 1;
172 $query->_tables
['civicrm_contribution_soft'] = 1;
173 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
176 if (!empty($query->_returnProperties
['contribution_soft_credit_contact_id'])) {
177 $query->_tables
['civicrm_contribution'] = 1;
178 $query->_tables
['civicrm_contribution_soft'] = 1;
179 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
182 if (!empty($query->_returnProperties
['contribution_soft_credit_amount'])) {
183 if ($includeSoftCredits) {
184 $query->_select
['contribution_soft_credit_amount'] = "civicrm_contribution_soft.amount as contribution_soft_credit_amount";
186 $query->_element
['contribution_soft_credit_amount'] = 1;
187 $query->_tables
['civicrm_contribution'] = 1;
188 $query->_tables
['civicrm_contribution_soft'] = 1;
191 if (!empty($query->_returnProperties
['contribution_soft_credit_type'])) {
192 if ($includeSoftCredits) {
193 $query->_select
['contribution_soft_credit_type'] = "contribution_softcredit_type.label as contribution_soft_credit_type";
195 $query->_element
['contribution_soft_credit_type'] = 1;
196 $query->_tables
['civicrm_contribution'] = 1;
197 $query->_tables
['contribution_softcredit_type'] = 1;
200 if (!empty($query->_returnProperties
['contribution_soft_credit_contribution_id'])) {
201 if ($includeSoftCredits) {
202 $query->_select
['contribution_soft_credit_contribution_id'] = "civicrm_contribution_soft.contribution_id as contribution_soft_credit_contribution_id";
204 $query->_element
['contribution_soft_credit_contribution_id'] = 1;
205 $query->_tables
['civicrm_contribution'] = 1;
206 $query->_tables
['civicrm_contribution_soft'] = 1;
209 if (!empty($query->_returnProperties
['contribution_soft_credit_email'])) {
210 $query->_select
['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
211 $query->_element
['contribution_soft_credit_email'] = 1;
212 $query->_tables
['civicrm_contribution'] = 1;
213 $query->_tables
['civicrm_contribution_soft'] = 1;
214 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
215 $query->_tables
['civicrm_contribution_soft_email'] = 1;
218 if (!empty($query->_returnProperties
['contribution_soft_credit_phone'])) {
219 $query->_select
['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
220 $query->_element
['contribution_soft_credit_phone'] = 1;
221 $query->_tables
['civicrm_contribution'] = 1;
222 $query->_tables
['civicrm_contribution_soft'] = 1;
223 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
224 $query->_tables
['civicrm_contribution_soft_phone'] = 1;
227 if (!empty($query->_returnProperties
['contribution_soft_credit_pcp_id'])) {
228 $query->_select
['contribution_soft_credit_pcp_id'] = "civicrm_contribution_soft.pcp_id as contribution_soft_credit_pcp_id";
229 $query->_element
['contribution_soft_credit_pcp_id'] = 1;
230 $query->_tables
['civicrm_contribution'] = 1;
231 $query->_tables
['civicrm_contribution_soft'] = 1;
234 if (!empty($query->_returnProperties
['contribution_campaign_title'])) {
235 $query->_select
['contribution_campaign_title'] = "civicrm_campaign.title as contribution_campaign_title";
236 $query->_element
['contribution_campaign_title'] = $query->_tables
['civicrm_campaign'] = 1;
239 // Adding address_id in a way that is more easily extendable since the above is a bit ... wordy.
240 $supportedBasicReturnValues = array('address_id');
241 foreach ($supportedBasicReturnValues as $fieldName) {
242 if (!empty($query->_returnProperties
[$fieldName]) && empty($query->_select
[$fieldName])) {
243 $query->_select
[$fieldName] = "civicrm_contribution.{$fieldName} as $fieldName";
244 $query->_element
[$fieldName] = $query->_tables
['civicrm_contribution'] = 1;
248 //CRM-16116: get financial_type_id
249 if (!empty($query->_returnProperties
['financial_type_id'])) {
250 $query->_select
['financial_type_id'] = "civicrm_contribution.financial_type_id as financial_type_id";
251 $query->_element
['financial_type_id'] = $query->_tables
['civicrm_contribution'] = 1;
259 * @param CRM_Contact_BAO_Query $query
261 public static function where(&$query) {
263 self
::initializeAnySoftCreditClause($query);
264 foreach (array_keys($query->_params
) as $id) {
265 if (empty($query->_params
[$id][0])) {
268 if (substr($query->_params
[$id][0], 0, 13) == 'contribution_' ||
substr($query->_params
[$id][0], 0, 10) == 'financial_' ||
substr($query->_params
[$id][0], 0, 8) == 'payment_') {
269 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
270 $query->_useDistinct
= TRUE;
274 $query->_params
[$id][0] == 'contribution_type_id' ||
275 $query->_params
[$id][0] == 'contribution_type'
277 CRM_Core_Session
::setStatus(
278 ts('The contribution type criteria is now obsolete, please update your smart group'),
285 $grouping = $query->_params
[$id][3];
286 self
::whereClauseSingle($query->_params
[$id], $query);
292 * Get where clause for a single value.
294 * @param array $values
295 * @param CRM_Contact_BAO_Query $query
297 public static function whereClauseSingle(&$values, &$query) {
298 list($name, $op, $value, $grouping, $wildcard) = $values;
301 $fields = array_merge(CRM_Contribute_BAO_Contribution
::fields(), self
::getFields());
303 if (!empty($value) && !is_array($value)) {
304 $quoteValue = "\"$value\"";
307 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
309 $recurrringFields = CRM_Contribute_BAO_ContributionRecur
::getRecurringFields();
310 unset($recurrringFields['contribution_recur_payment_made']);
311 foreach ($recurrringFields as $dateField => $dateFieldTitle) {
312 if (self
::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
317 case 'contribution_date':
318 case 'contribution_date_low':
319 case 'contribution_date_low_time':
320 case 'contribution_date_high':
321 case 'contribution_date_high_time':
322 // process to / from date
323 $query->dateQueryBuilder($values,
324 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
328 case 'contribution_amount':
329 case 'contribution_amount_low':
330 case 'contribution_amount_high':
331 // process min/max amount
332 $query->numberRangeBuilder($values,
333 'civicrm_contribution', 'contribution_amount',
334 'total_amount', 'Contribution Amount',
339 case 'contribution_thankyou_date_is_not_null':
342 $query->_qill
[$grouping][] = ts('Contribution Thank-you Sent');
346 $query->_qill
[$grouping][] = ts('Contribution Thank-you Not Sent');
348 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.thankyou_date", $op);
349 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
352 case 'contribution_receipt_date_is_not_null':
355 $query->_qill
[$grouping][] = ts('Contribution Receipt Sent');
359 $query->_qill
[$grouping][] = ts('Contribution Receipt Not Sent');
361 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.receipt_date", $op);
362 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
365 case 'financial_type':
366 case 'contribution_page':
367 case 'payment_instrument':
368 case 'contribution_payment_instrument':
369 case 'contribution_status':
371 case 'financial_type_id':
372 CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes($financialTypes);
373 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.$name", 'IN', array_keys($financialTypes), 'String');
375 case 'payment_instrument_id':
376 case 'contribution_payment_instrument_id':
377 case 'contribution_page_id':
378 case 'contribution_status_id':
379 case 'contribution_id':
380 case 'contribution_currency_type':
381 case 'contribution_currency':
382 case 'contribution_source':
383 case 'contribution_trxn_id':
384 case 'contribution_check_number':
385 case 'contribution_contact_id':
386 case (strpos($name, '_amount') !== FALSE):
387 case (strpos($name, '_date') !== FALSE && $name != 'contribution_fulfilled_date'):
388 case 'contribution_campaign_id':
390 $pseudoExtraParam = array();
391 // @todo including names using a switch statement & then using an 'if' to filter them out is ... odd!
392 if ((strpos($name, '_amount') !== FALSE) ||
(strpos($name, '_date') !== FALSE) ||
in_array($name,
395 'contribution_currency',
396 'contribution_source',
397 'contribution_trxn_id',
398 'contribution_check_number',
399 'contribution_payment_instrument_id',
400 'contribution_contact_id',
401 'contribution_campaign_id',
405 $name = str_replace('contribution_', '', $name);
406 if (!in_array($name, array('source', 'id', 'contact_id', 'campaign_id'))) {
407 $qillName = str_replace('contribution_', '', $qillName);
410 if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
411 $qillName = $name = 'currency';
412 $pseudoExtraParam = array('labelColumn' => 'name');
415 $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type
::typeToString($fields[$qillName]['type']) : 'String';
417 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.$name", $op, $value, $dataType);
418 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
419 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
420 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
423 case 'contribution_pcp_made_through_id':
424 case 'contribution_soft_credit_type_id':
426 if ($name == 'contribution_pcp_made_through_id') {
427 $qillName = $name = 'pcp_id';
428 $fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
430 if ($name == 'contribution_soft_credit_type_id') {
431 $qillName = str_replace('_id', '', $qillName);
432 $fields[$qillName]['type'] = $fields[$qillName]['data_type'];
433 $name = str_replace('contribution_', '', $name);
435 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_soft.$name",
436 $op, $value, CRM_Utils_Type
::typeToString($fields[$qillName]['type'])
438 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
439 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
440 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
443 case 'contribution_or_softcredits':
444 if ($value == 'only_scredits') {
445 $query->_where
[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
446 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
447 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
448 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
450 elseif ($value == 'both_related') {
451 $query->_where
[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
452 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
453 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
454 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
456 elseif ($value == 'both') {
457 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Both');
458 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
459 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
461 // default option: $value == 'only_contribs'
464 case 'contribution_is_test':
465 // By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
466 // so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
467 $key = array_search('civicrm_contribution.is_test = 0', $query->_where
[$grouping]);
469 unset($query->_where
[$grouping][$key]);
471 case 'contribution_test':
472 // We dont want to include all tests for sql OR CRM-7827
473 if (!$value ||
$query->getOperator() != 'OR') {
474 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
476 $query->_qill
[$grouping][] = ts("Only Display Test Contributions");
478 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
482 case 'contribution_is_pay_later':
483 case 'contribution_pay_later':
484 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
486 $query->_qill
[$grouping][] = ts("Find Pay Later Contributions");
489 $query->_qill
[$grouping][] = ts("Exclude Pay Later Contributions");
491 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
494 case 'contribution_recurring':
496 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
497 $query->_qill
[$grouping][] = ts("Find Recurring Contributions");
498 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
501 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
502 $query->_qill
[$grouping][] = ts("Exclude Recurring Contributions");
506 case 'contribution_recur_id':
507 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.contribution_recur_id",
508 $op, $value, "Integer"
510 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
513 case 'contribution_recur_processor_id':
514 case 'contribution_recur_trxn_id':
515 $fieldName = str_replace('contribution_recur_', '', $name);
516 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_recur.{$fieldName}",
517 $op, $value, "String"
519 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
522 case 'contribution_recur_payment_made':
523 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_recur.id", 'IS NOT EMPTY');
525 $query->_qill
[$grouping][] = ts("Recurring contributions with at least one payment");
526 self
::$_contribRecurPayment = TRUE;
529 $query->_qill
[$grouping][] = ts("All recurring contributions regardless of payments");
530 self
::$_contribRecurPayment = FALSE;
532 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
535 case 'contribution_note':
536 $value = $strtolower(CRM_Core_DAO
::escapeString($value));
541 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_note.note)" : "civicrm_note.note";
542 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
543 $query->_qill
[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
544 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = $query->_whereTables
['contribution_note'] = 1;
547 case 'contribution_membership_id':
548 $query->_where
[$grouping][] = " civicrm_membership.id $op $value";
549 $query->_tables
['contribution_membership'] = $query->_whereTables
['contribution_membership'] = 1;
553 case 'contribution_participant_id':
554 $query->_where
[$grouping][] = " civicrm_participant.id $op $value";
555 $query->_tables
['contribution_participant'] = $query->_whereTables
['contribution_participant'] = 1;
558 case 'contribution_pcp_display_in_roll':
559 $query->_where
[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
561 $query->_qill
[$grouping][] = ts("Personal Campaign Page Honor Roll");
564 $query->_qill
[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
566 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
569 case 'contribution_batch_id':
570 $batches = CRM_Contribute_PseudoConstant
::batch();
571 $query->_where
[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
572 $query->_qill
[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
573 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
574 $query->_tables
['contribution_batch'] = $query->_whereTables
['contribution_batch'] = 1;
577 case 'contribution_product_id':
578 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
580 list($operator, $productValue) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Product', $name, $value, $op);
581 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $operator, 3 => $productValue));
582 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_product.id", $op, $value);
583 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
586 case 'contribution_is_payment':
587 $query->_where
[$grouping][] = " civicrm_financial_trxn.is_payment $op $value";
588 $query->_tables
['contribution_financial_trxn'] = $query->_whereTables
['contribution_financial_trxn'] = 1;
592 //all other elements are handle in this case
593 $fldName = substr($name, 13);
594 CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes($financialTypes);
595 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.financial_type_id", 'IN', array_keys($financialTypes), 'String');
596 if (!isset($fields[$fldName])) {
598 CRM_Core_Session
::setStatus(ts(
599 'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
605 $whereTable = $fields[$fldName];
606 $value = trim($value);
608 $dataType = "String";
609 if (!empty($whereTable['type'])) {
610 $dataType = CRM_Utils_Type
::typeToString($whereTable['type']);
613 $wc = ($op != 'LIKE' && $dataType != 'Date') ?
"LOWER($whereTable[where])" : "$whereTable[where]";
614 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, $dataType);
615 $query->_qill
[$grouping][] = "$whereTable[title] $op $quoteValue";
616 list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
617 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;
618 if ($tableName == 'civicrm_contribution_product') {
619 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
620 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
623 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
631 * @param string $name
632 * @param string $mode
633 * @param string $side
635 * @return NULL|string
637 public static function from($name, $mode, $side) {
640 case 'civicrm_contribution':
641 $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
642 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
643 // switch the from table if its only soft credit search
644 $from = " $side JOIN contribution_search_scredit_combined ON contribution_search_scredit_combined.contact_id = contact_a.id ";
645 $from .= " $side JOIN civicrm_contribution ON civicrm_contribution.id = contribution_search_scredit_combined.id ";
646 $from .= " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.id = contribution_search_scredit_combined.scredit_id";
650 case 'civicrm_contribution_recur':
652 // 'Made payment for the recurring contributions?' is ticked yes
653 $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
654 if (self
::$_contribRecurPayment == TRUE) {
655 $from .= " INNER JOIN civicrm_contribution cr ON cr.contribution_recur_id = civicrm_contribution_recur.id ";
659 $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
663 case 'civicrm_financial_type':
664 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
665 $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
668 $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
672 case 'civicrm_financial_account':
673 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
674 $from = " $side JOIN civicrm_financial_account ON contact_a.id = civicrm_financial_account.contact_id ";
678 case 'civicrm_accounting_code':
679 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
680 $from = " $side JOIN civicrm_entity_financial_account ON civicrm_entity_financial_account.entity_id = civicrm_contribution.financial_type_id AND civicrm_entity_financial_account.entity_table = 'civicrm_financial_type' ";
681 $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
682 $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
683 $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
687 case 'civicrm_contribution_page':
688 $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page_id = civicrm_contribution_page.id";
691 case 'civicrm_product':
692 $from = " $side JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
693 $from .= " $side JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
696 case 'contribution_payment_instrument':
697 $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
698 $from .= " $side JOIN civicrm_option_value contribution_payment_instrument ON (civicrm_contribution.payment_instrument_id = contribution_payment_instrument.value
699 AND option_group_payment_instrument.id = contribution_payment_instrument.option_group_id ) ";
702 case 'contribution_status':
703 $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
704 $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
705 AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
708 case 'contribution_softcredit_type':
709 $from = " $side JOIN civicrm_option_group option_group_contribution_softcredit_type ON
710 (option_group_contribution_softcredit_type.name = 'soft_credit_type')";
711 $from .= " $side JOIN civicrm_option_value contribution_softcredit_type ON
712 ( civicrm_contribution_soft.soft_credit_type_id = contribution_softcredit_type.value
713 AND option_group_contribution_softcredit_type.id = contribution_softcredit_type.option_group_id )";
716 case 'contribution_note':
717 $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
718 civicrm_contribution.id = civicrm_note.entity_id )";
721 case 'contribution_membership':
722 $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
723 $from .= " $side JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
726 case 'civicrm_campaign':
727 //CRM-16764 - get survey clause from campaign bao
728 if (!CRM_Campaign_BAO_Query
::$_applySurveyClause) {
729 $from = " $side JOIN civicrm_campaign ON civicrm_campaign.id = civicrm_contribution.campaign_id";
733 case 'contribution_participant':
734 $from = " $side JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
735 $from .= " $side JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
738 case 'civicrm_contribution_soft':
739 if (!in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
740 $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
744 case 'civicrm_contribution_soft_contact':
745 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
746 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution.contact_id = civicrm_contact_d.id )
747 AND contribution_search_scredit_combined.scredit_id IS NOT NULL";
750 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
754 case 'civicrm_contribution_soft_email':
755 $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
758 case 'civicrm_contribution_soft_phone':
759 $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
762 case 'contribution_batch':
763 $from .= " $side JOIN civicrm_entity_financial_trxn ON (
764 civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
765 AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )";
767 $from .= " $side JOIN civicrm_financial_trxn ON (
768 civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )";
770 $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
771 AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )";
773 $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
780 * Initialise the soft credit clause.
782 * @param CRM_Contact_BAO_Query $query
784 public static function initializeAnySoftCreditClause(&$query) {
785 // @todo have a generic initialize on all components that gets called every query
786 // & rename this to match that fn name.
787 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
788 if (self
::isSoftCreditOptionEnabled($query->_params
)) {
789 unset($query->_distinctComponentClause
);
790 $query->_rowCountClause
= " count(civicrm_contribution.id)";
791 $query->_groupByComponentClause
= " GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.contact_id, contribution_search_scredit_combined.scredit_id ";
794 $query->_distinctComponentClause
= ' civicrm_contribution.id';
800 * Check if soft credits are enables.
802 * @param array $queryParams
806 public static function isSoftCreditOptionEnabled($queryParams = array()) {
807 static $tempTableFilled = FALSE;
808 if (!empty($queryParams)) {
809 foreach (array_keys($queryParams) as $id) {
810 if (empty($queryParams[$id][0])) {
813 if ($queryParams[$id][0] == 'contribution_or_softcredits') {
814 self
::$_contribOrSoftCredit = $queryParams[$id][2];
818 if (in_array(self
::$_contribOrSoftCredit,
819 array("only_scredits", "both_related", "both"))) {
820 if (!$tempTableFilled) {
821 // build a temp table which is union of contributions and soft credits
822 // note: group-by in first part ensures uniqueness in counts
824 CREATE TEMPORARY TABLE IF NOT EXISTS contribution_search_scredit_combined AS
825 SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id
826 FROM civicrm_contribution con
827 LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id
828 GROUP BY id, contact_id, scredit_id, cso.id
830 SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id
831 FROM civicrm_contribution_soft as scredit";
832 CRM_Core_DAO
::executeQuery($tempQuery);
833 $tempTableFilled = TRUE;
841 * Get return properties for soft credits.
843 * @param bool $isExportMode
847 public static function softCreditReturnProperties($isExportMode = FALSE) {
849 'contribution_soft_credit_name' => 1,
850 'contribution_soft_credit_amount' => 1,
851 'contribution_soft_credit_type' => 1,
854 $properties['contribution_soft_credit_contact_id'] = 1;
855 $properties['contribution_soft_credit_contribution_id'] = 1;
861 * Get the list of fields required to populate the selector.
863 * The default return properties array returns far too many fields for 'everyday use. Every field you add to this array
864 * kills a small kitten so add carefully.
866 public static function selectorReturnProperties() {
869 'contact_sub_type' => 1,
871 'financial_type' => 1,
872 'contribution_source' => 1,
876 'thankyou_date' => 1,
878 // Without this value here we get an e-notice BUT the value does not appear to be rendered anywhere.
879 'contribution_campaign_id' => 1,
880 'contribution_status_id' => 1,
881 // @todo return this & fix query to do pseudoconstant thing.
882 'contribution_status' => 1,
883 // @todo the product field got added because it suited someone's use case.
884 // ideally we would have some configurability here because I think 90% of sites would
885 // disagree this is the right field to show - but they wouldn't agree with each other
886 // on what field to show instead.
887 'contribution_product_id' => 1,
891 if (self
::isSoftCreditOptionEnabled()) {
892 $properties = array_merge($properties, self
::softCreditReturnProperties());
899 * Function you should avoid.
901 * This function returns default properties for contribution queries. However, they are
902 * far more than are required in 'most' cases and you should always try to return the return properties
903 * you actually require.
905 * It would be nice to throw an e-notice when this is called but it would trash the tests :-(.
908 * @param bool $includeCustomFields
912 public static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
914 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
919 'contact_sub_type' => 1,
925 'financial_type' => 1,
927 'contribution_source' => 1,
931 'thankyou_date' => 1,
937 'accounting_code' => 1,
939 'payment_instrument' => 1,
941 'payment_instrument_id' => 1,
945 'non_deductible_amount' => 1,
957 'cancel_reason' => 1,
965 'product_option' => 1,
967 'fulfilled_date' => 1,
969 'contribution_start_date' => 1,
971 'contribution_end_date' => 1,
977 'contribution_status' => 1,
979 'contribution_status_id' => 1,
981 'contribution_recur_id' => 1,
985 'contribution_note' => 1,
987 'contribution_batch' => 1,
989 'contribution_campaign_title' => 1,
991 'contribution_campaign_id' => 1,
993 'contribution_product_id' => 1,
996 if (self
::isSoftCreditOptionEnabled()) {
997 $properties = array_merge($properties, self
::softCreditReturnProperties());
999 if ($includeCustomFields) {
1000 // also get all the custom contribution properties
1001 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Contribution');
1002 if (!empty($fields)) {
1003 foreach ($fields as $name => $dontCare) {
1004 $properties[$name] = 1;
1013 * Add all the elements shared between contribute search and advnaced search.
1015 * @param CRM_Core_Form $form
1017 public static function buildSearchForm(&$form) {
1019 // Added contribution source
1020 $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO
::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
1022 CRM_Core_Form_Date
::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
1024 // This hidden element added for displaying Date Range error correctly. Definitely a dirty hack, but... it works.
1025 $form->addElement('hidden', 'contribution_date_range_error');
1026 $form->addFormRule(array('CRM_Contribute_BAO_Query', 'formRule'), $form);
1028 $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
1029 $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('9.99', ' '))), 'money');
1031 $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
1032 $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('99.99', ' '))), 'money');
1034 // Adding select option for curreny type -- CRM-4711
1035 $form->add('select', 'contribution_currency_type',
1036 ts('Currency Type'),
1038 '' => ts('- any -'),
1040 CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name')),
1041 FALSE, array('class' => 'crm-select2')
1045 CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes($financialTypes, CRM_Core_Action
::VIEW
);
1046 $form->addSelect('financial_type_id',
1047 array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search', 'options' => $financialTypes)
1050 $form->add('select', 'contribution_page_id',
1051 ts('Contribution Page'),
1052 CRM_Contribute_PseudoConstant
::contributionPage(),
1053 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -'))
1056 $form->addSelect('payment_instrument_id',
1057 array('entity' => 'contribution', 'multiple' => 'multiple', 'label' => ts('Payment Method'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
1060 $form->add('select',
1061 'contribution_pcp_made_through_id',
1062 ts('Personal Campaign Page'),
1063 CRM_Contribute_PseudoConstant
::pcPage(), FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -')));
1065 $statusValues = CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'contribution_status_id');
1066 // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
1067 unset($statusValues['5'], $statusValues['6']);
1068 $form->add('select', 'contribution_status_id',
1069 ts('Contribution Status'), $statusValues,
1070 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
1073 // Add fields for thank you and receipt
1074 $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'), TRUE);
1075 $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'), TRUE);
1077 $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'), TRUE);
1078 $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'), TRUE);
1080 $form->addYesNo('contribution_test', ts('Contribution is a Test?'), TRUE);
1082 // Add field for transaction ID search
1083 $form->addElement('text', 'contribution_trxn_id', ts("Transaction ID"));
1084 $form->addElement('text', 'invoice_id', ts("Invoice ID"));
1085 $form->addElement('text', 'contribution_check_number', ts('Check Number'));
1087 // Add field for pcp display in roll search
1088 $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'), TRUE);
1090 // Soft credit related fields
1092 'only_contribs' => ts('Contributions Only'),
1093 'only_scredits' => ts('Soft Credits Only'),
1094 'both_related' => ts('Soft Credits with related Hard Credit'),
1095 'both' => ts('Both'),
1097 $form->add('select', 'contribution_or_softcredits', ts('Contributions OR Soft Credits?'), $options, FALSE, array('class' => "crm-select2"));
1099 'contribution_soft_credit_type_id',
1101 'entity' => 'contribution_soft',
1102 'field' => 'soft_credit_type_id',
1104 'context' => 'search',
1108 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
1109 $form->add('select', 'contribution_product_id',
1111 CRM_Contribute_PseudoConstant
::products(),
1112 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -'))
1115 self
::addCustomFormFields($form, array('Contribution'));
1117 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'contribution_campaign_id');
1120 $batches = CRM_Contribute_PseudoConstant
::batch();
1122 if (!empty($batches)) {
1123 $form->add('select', 'contribution_batch_id',
1125 array('' => ts('- any -')) +
$batches,
1126 FALSE, array('class' => 'crm-select2')
1130 $form->assign('validCiviContribute', TRUE);
1131 $form->setDefaults(array('contribution_test' => 0));
1133 CRM_Contribute_BAO_ContributionRecur
::recurringContribution($form);
1139 * @todo better function comment needed - what IS the point of this?
1141 * @param array $tables
1143 public static function tableNames(&$tables) {
1144 // Add contribution table
1145 if (!empty($tables['civicrm_product'])) {
1146 $tables = array_merge(array('civicrm_contribution' => 1), $tables);
1149 if (!empty($tables['civicrm_contribution_product']) && empty($tables['civicrm_product'])) {
1150 $tables['civicrm_product'] = 1;
1155 * Add the where for dates.
1157 * @param array $values
1158 * Array of query values.
1159 * @param object $query
1161 * @param string $name
1162 * Query field that is set.
1163 * @param string $field
1164 * Name of field to be set.
1165 * @param string $title
1166 * Title of the field.
1170 public static function buildDateWhere(&$values, $query, $name, $field, $title) {
1171 $fieldPart = strpos($name, $field);
1172 if ($fieldPart === FALSE) {
1175 // we only have recurring dates using this ATM so lets' short cut to find the table name
1176 $table = 'contribution_recur';
1177 $fieldName = explode($table . '_', $field);
1178 $query->dateQueryBuilder($values,
1179 'civicrm_' . $table, $field, $fieldName[1], $title
1185 * Custom form rules.
1187 * @param array $fields
1188 * @param array $files
1189 * @param CRM_Core_Form $form
1191 * @return bool|array
1193 public static function formRule($fields, $files, $form) {
1196 if (empty($fields['contribution_date_high']) ||
empty($fields['contribution_date_low'])) {
1200 CRM_Utils_Rule
::validDateRange($fields, 'contribution_date', $errors, ts('Date Received'));
1202 return empty($errors) ?
TRUE : $errors;