3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Contribute_BAO_Query
extends CRM_Core_BAO_Query
{
19 public static $_contribOrSoftCredit = "only_contribs";
21 public static $_contribRecurPayment = NULL;
24 * Function get the searchable fields for contribution.
26 * This is basically the contribution fields plus some related entity fields.
28 * @param bool $checkPermission
31 * Associative array of contribution fields
33 public static function getFields($checkPermission = TRUE) {
34 if (!isset(\Civi
::$statics[__CLASS__
]) ||
!isset(\Civi
::$statics[__CLASS__
]['fields']) ||
!isset(\Civi
::$statics[__CLASS__
]['fields']['contribution'])) {
35 $recurFields = CRM_Contribute_DAO_ContributionRecur
::fields();
36 foreach ($recurFields as $fieldKey => $field) {
37 // We can only safely add in those with unique names as those without could clobber others.
38 // The array is keyed by unique names so if it doesn't match the key there is no unique name & we unset
39 // Refer to CRM_Contribute_Form_SearchTest for existing tests ... and to add more!
40 if ($field['name'] === $fieldKey) {
41 unset($recurFields[$fieldKey]);
44 $fields = array_merge($recurFields, CRM_Contribute_BAO_Contribution
::exportableFields($checkPermission));
45 CRM_Contribute_BAO_Contribution
::appendPseudoConstantsToFields($fields);
46 unset($fields['contribution_contact_id']);
47 \Civi
::$statics[__CLASS__
]['fields']['contribution'] = $fields;
49 return \Civi
::$statics[__CLASS__
]['fields']['contribution'];
53 * If contributions are involved, add the specific contribute fields.
55 * @param CRM_Contact_BAO_Query $query
57 public static function select(&$query) {
58 // if contribute mode add contribution id
59 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
60 $query->_select
['contribution_id'] = "civicrm_contribution.id as contribution_id";
61 $query->_element
['contribution_id'] = 1;
62 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
65 // get accounting code
66 if (!empty($query->_returnProperties
['accounting_code'])) {
67 $query->_select
['accounting_code'] = "civicrm_financial_account.accounting_code as accounting_code";
68 $query->_element
['accounting_code'] = 1;
69 $query->_tables
['civicrm_accounting_code'] = 1;
70 $query->_tables
['civicrm_financial_account'] = 1;
73 if (!empty($query->_returnProperties
['contribution_note'])) {
74 $query->_select
['contribution_note'] = "civicrm_note.note as contribution_note";
75 $query->_element
['contribution_note'] = 1;
76 $query->_tables
['contribution_note'] = 1;
77 $query->_tables
['civicrm_contribution'] = 1;
80 if (!empty($query->_returnProperties
['contribution_batch'])) {
81 $query->_select
['contribution_batch'] = "civicrm_batch.title as contribution_batch";
82 $query->_element
['contribution_batch'] = 1;
83 $query->_tables
['civicrm_financial_trxn'] = 1;
84 $query->_tables
['contribution_batch'] = 1;
87 if (!empty($query->_returnProperties
['contribution_campaign_title'])) {
88 $query->_select
['contribution_campaign_title'] = "civicrm_campaign.title as contribution_campaign_title";
89 $query->_element
['contribution_campaign_title'] = $query->_tables
['civicrm_campaign'] = 1;
92 self
::addSoftCreditFields($query);
98 * @param CRM_Contact_BAO_Query $query
100 * @throws \CRM_Core_Exception
101 * @throws \CiviCRM_API3_Exception
103 public static function where(&$query) {
104 self
::initializeAnySoftCreditClause($query);
105 foreach (array_keys($query->_params
) as $id) {
106 if (empty($query->_params
[$id][0])) {
109 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_') {
110 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
111 $query->_useDistinct
= TRUE;
114 self
::whereClauseSingle($query->_params
[$id], $query);
120 * Get where clause for a single value.
122 * @param array $values
123 * @param CRM_Contact_BAO_Query $query
125 * @throws \CiviCRM_API3_Exception
126 * @throws \CRM_Core_Exception
128 public static function whereClauseSingle(&$values, &$query) {
129 list($name, $op, $value, $grouping, $wildcard) = $values;
132 $fields = self
::getFields();
134 if (!empty($value) && !is_array($value)) {
135 $quoteValue = "\"$value\"";
138 $fieldAliases = self
::getLegacySupportedFields();
140 $fieldName = $name = self
::getFieldName($values);
142 if (in_array($name, $fieldAliases)) {
143 $qillName = array_search($name, $fieldAliases);
145 $pseudoExtraParam = [];
146 $fieldSpec = CRM_Utils_Array
::value($fieldName, $fields, []);
147 $tableName = CRM_Utils_Array
::value('table_name', $fieldSpec, 'civicrm_contribution');
148 $dataType = CRM_Utils_Type
::typeToString(CRM_Utils_Array
::value('type', $fieldSpec));
149 if ($dataType === 'Timestamp' ||
$dataType === 'Date') {
150 $title = empty($fieldSpec['unique_title']) ?
$fieldSpec['title'] : $fieldSpec['unique_title'];
151 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
152 $query->dateQueryBuilder($values,
153 $tableName, $fieldName, $fieldSpec['name'], $title
159 case 'contribution_date':
160 case 'contribution_date_low':
161 case 'contribution_date_low_time':
162 case 'contribution_date_high':
163 case 'contribution_date_high_time':
164 CRM_Core_Error
::deprecatedFunctionWarning('search by receive_date');
165 // process to / from date
166 $query->dateQueryBuilder($values,
167 'civicrm_contribution', 'contribution_date', 'receive_date', ts('Contribution Date')
171 case 'contribution_amount':
172 case 'contribution_amount_low':
173 case 'contribution_amount_high':
174 // process min/max amount
175 $query->numberRangeBuilder($values,
176 'civicrm_contribution', 'contribution_amount',
177 'total_amount', 'Contribution Amount',
182 case 'contribution_thankyou_date_is_not_null':
185 $query->_qill
[$grouping][] = ts('Contribution Thank-you Sent');
189 $query->_qill
[$grouping][] = ts('Contribution Thank-you Not Sent');
191 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.thankyou_date", $op);
192 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
195 case 'contribution_receipt_date_is_not_null':
198 $query->_qill
[$grouping][] = ts('Contribution Receipt Sent');
202 $query->_qill
[$grouping][] = ts('Contribution Receipt Not Sent');
204 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.receipt_date", $op);
205 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
208 case 'financial_type_id':
210 case 'invoice_number':
211 case 'payment_instrument_id':
212 case 'contribution_payment_instrument_id':
213 case 'contribution_page_id':
214 case 'contribution_status_id':
215 case 'contribution_id':
216 case 'contribution_currency_type':
217 case 'contribution_currency':
218 case 'contribution_source':
219 case 'contribution_trxn_id':
220 case 'contribution_check_number':
221 case 'contribution_contact_id':
222 case (strpos($name, '_amount') !== FALSE):
223 case 'contribution_campaign_id':
225 $fieldNamesNotToStripContributionFrom = [
226 'contribution_currency_type',
227 'contribution_status_id',
228 'contribution_page_id',
230 // @todo these are mostly legacy params. Find a better way to deal with them.
231 if (!in_array($name, $fieldNamesNotToStripContributionFrom)
233 if (!isset($fields[$name])) {
234 $qillName = str_replace('contribution_', '', $qillName);
236 $name = str_replace('contribution_', '', $name);
238 if (in_array($name, ['contribution_currency', 'contribution_currency_type'])) {
239 $qillName = $name = 'currency';
240 $pseudoExtraParam = ['labelColumn' => 'name'];
243 $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type
::typeToString($fields[$qillName]['type']) : 'String';
245 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.$name", $op, $value, $dataType);
246 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
247 if (!($name == 'id' && $value == 0)) {
248 $query->_qill
[$grouping][] = ts('%1 %2 %3', [1 => $fields[$qillName]['title'], 2 => $op, 3 => $value]);
250 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
253 case 'contribution_pcp_made_through_id':
254 case 'contribution_soft_credit_type_id':
256 if ($name == 'contribution_pcp_made_through_id') {
257 $qillName = $name = 'pcp_id';
258 $fields[$name] = ['title' => ts('Personal Campaign Page'), 'type' => 2];
260 if ($name == 'contribution_soft_credit_type_id') {
261 $qillName = str_replace('_id', '', $qillName);
262 $fields[$qillName]['type'] = $fields[$qillName]['data_type'];
263 $name = str_replace('contribution_', '', $name);
265 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_soft.$name",
266 $op, $value, CRM_Utils_Type
::typeToString($fields[$qillName]['type'])
268 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
269 $query->_qill
[$grouping][] = ts('%1 %2 %3', [1 => $fields[$qillName]['title'], 2 => $op, 3 => $value]);
270 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
273 case 'contribution_or_softcredits':
274 if ($value == 'only_scredits') {
275 $query->_where
[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
276 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
277 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
278 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
280 elseif ($value == 'both_related') {
281 $query->_where
[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
282 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Contributions and their related soft credit');
283 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
284 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
286 elseif ($value == 'both') {
287 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - All');
288 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
289 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
291 elseif ($value == 'only_contribs_unsoftcredited') {
292 $query->_where
[$grouping][] = "contribution_search_scredit_combined.filter_id IS NULL";
293 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Contributions without a soft credit');
294 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
295 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
297 // default option: $value == 'only_contribs'
300 case 'contribution_is_test':
301 // By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
302 // so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
303 $key = array_search('civicrm_contribution.is_test = 0', $query->_where
[$grouping]);
305 unset($query->_where
[$grouping][$key]);
307 case 'contribution_test':
308 // We dont want to include all tests for sql OR CRM-7827
309 if (!$value ||
$query->getOperator() != 'OR') {
310 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
312 $query->_qill
[$grouping][] = ts("Only Display Test Contributions");
314 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
318 case 'contribution_is_pay_later':
319 case 'contribution_pay_later':
320 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
322 $query->_qill
[$grouping][] = ts("Find Pay Later Contributions");
325 $query->_qill
[$grouping][] = ts("Exclude Pay Later Contributions");
327 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
330 case 'contribution_recurring':
332 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
333 $query->_qill
[$grouping][] = ts("Find Recurring Contributions");
334 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
337 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
338 $query->_qill
[$grouping][] = ts("Exclude Recurring Contributions");
342 case 'contribution_recur_id':
343 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.contribution_recur_id",
344 $op, $value, "Integer"
346 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
349 case 'contribution_recur_payment_processor_id':
350 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_recur.payment_processor_id", $op, $value, "String");
351 $paymentProcessors = civicrm_api3('PaymentProcessor', 'get', []);
352 $paymentProcessorNames = [];
353 foreach ($value as $paymentProcessorId) {
354 $paymentProcessorNames[] = $paymentProcessors['values'][$paymentProcessorId]['name'];
356 $query->_qill
[$grouping][] = ts("Recurring Contribution Payment Processor %1 %2", [1 => $op, 2 => implode(', ', $paymentProcessorNames)]);
357 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
360 case 'contribution_recur_processor_id':
361 case 'contribution_recur_trxn_id':
362 $spec = $fields[$name];
363 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($spec['where'],
364 $op, $value, "String"
366 $query->_qill
[$grouping][] = ts("Recurring Contribution %1 %2 '%3'", [1 => $fields[$name]['title'], 2 => $op, 3 => $value]);
367 $query->_tables
[$spec['table_name']] = $query->_whereTables
[$spec['table_name']] = 1;
370 case 'contribution_recur_payment_made':
371 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_recur.id", 'IS NOT EMPTY');
373 $query->_qill
[$grouping][] = ts("Recurring contributions with at least one payment");
374 self
::$_contribRecurPayment = TRUE;
377 $query->_qill
[$grouping][] = ts("All recurring contributions regardless of payments");
378 self
::$_contribRecurPayment = FALSE;
380 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
383 case 'contribution_recur_contribution_status_id':
384 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_recur.contribution_status_id", $op, $value, 'String');
385 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_ContributionRecur', 'contribution_status_id', $value, $op, $pseudoExtraParam);
386 $query->_qill
[$grouping][] = ts("Recurring Contribution Status %1 '%2'", [1 => $op, 2 => $value]);
387 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
390 case 'contribution_note':
391 $value = CRM_Core_DAO
::escapeString($value);
396 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_note.note', $op, $value, "String");
397 $query->_qill
[$grouping][] = ts('Contribution Note %1 %2', [1 => $op, 2 => $quoteValue]);
398 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = $query->_whereTables
['contribution_note'] = 1;
401 case 'contribution_membership_id':
402 $query->_where
[$grouping][] = " civicrm_membership.id $op $value";
403 $query->_tables
['contribution_membership'] = $query->_whereTables
['contribution_membership'] = 1;
407 case 'contribution_participant_id':
408 $query->_where
[$grouping][] = " civicrm_participant.id $op $value";
409 $query->_tables
['contribution_participant'] = $query->_whereTables
['contribution_participant'] = 1;
412 case 'contribution_pcp_display_in_roll':
413 $query->_where
[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
415 $query->_qill
[$grouping][] = ts("Personal Campaign Page Honor Roll");
418 $query->_qill
[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
420 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
423 case 'contribution_batch_id':
424 list($qillOp, $qillValue) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Batch_BAO_EntityBatch', 'batch_id', $value, $op);
425 $query->_qill
[$grouping][] = ts('Batch Name %1 %2', [1 => $qillOp, 2 => $qillValue]);
426 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_entity_batch.batch_id', $op, $value);
427 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
428 $query->_tables
['civicrm_financial_trxn'] = $query->_whereTables
['civicrm_financial_trxn'] = 1;
429 $query->_tables
['contribution_batch'] = $query->_whereTables
['contribution_batch'] = 1;
432 case 'contribution_product_id':
433 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
435 list($operator, $productValue) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Product', $name, $value, $op);
436 $query->_qill
[$grouping][] = ts('%1 %2 %3', [1 => $fields[$qillName]['title'], 2 => $operator, 3 => $productValue]);
437 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_product.id", $op, $value);
438 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
441 case 'contribution_is_payment':
442 $query->_where
[$grouping][] = " civicrm_financial_trxn.is_payment $op $value";
443 $query->_tables
['civicrm_financial_trxn'] = $query->_whereTables
['civicrm_financial_trxn'] = 1;
446 case 'financial_trxn_card_type_id':
447 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_financial_trxn.card_type_id', $op, $value);
448 $query->_tables
['civicrm_financial_trxn'] = $query->_whereTables
['civicrm_financial_trxn'] = 1;
449 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
450 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Financial_DAO_FinancialTrxn', 'card_type_id', $value, $op);
451 $query->_qill
[$grouping][] = ts('Card Type %1 %2', [1 => $op, 2 => $value]);
454 case 'financial_trxn_pan_truncation':
455 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_financial_trxn.pan_truncation', $op, $value);
456 $query->_tables
['civicrm_financial_trxn'] = $query->_whereTables
['civicrm_financial_trxn'] = 1;
457 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
458 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Financial_DAO_FinancialTrxn', 'pan_truncation', $value, $op);
459 $query->_qill
[$grouping][] = ts('Card Number %1 %2', [1 => $op, 2 => $value]);
463 //all other elements are handle in this case
464 $fldName = substr($name, 13);
465 if (!isset($fields[$fldName])) {
468 $whereTable = $fields[$fldName];
469 if (!is_array($value)) {
470 $value = trim($value);
473 $dataType = 'String';
474 if (!empty($whereTable['type'])) {
475 $dataType = CRM_Utils_Type
::typeToString($whereTable['type']);
478 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($whereTable['where'], $op, $value, $dataType);
479 $query->_qill
[$grouping][] = "$whereTable[title] $op $quoteValue";
480 list($tableName) = explode('.', $whereTable['where'], 2);
481 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;
482 if ($tableName === 'civicrm_contribution_product') {
483 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
484 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
487 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
495 * @param string $name
496 * @param string $mode
497 * @param string $side
499 * @return NULL|string
501 public static function from($name, $mode, $side) {
504 case 'civicrm_contribution':
505 $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
506 if (in_array(self
::$_contribOrSoftCredit, ["only_scredits", "both_related", "both", "only_contribs_unsoftcredited"])) {
507 // switch the from table if its only soft credit search
508 $from = " $side JOIN " . \Civi
::$statics[__CLASS__
]['soft_credit_temp_table_name'] . " as contribution_search_scredit_combined ON contribution_search_scredit_combined.contact_id = contact_a.id ";
509 $from .= " $side JOIN civicrm_contribution ON civicrm_contribution.id = contribution_search_scredit_combined.id ";
510 $from .= " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.id = contribution_search_scredit_combined.scredit_id";
514 case 'civicrm_contribution_recur':
516 // 'Made payment for the recurring contributions?' is ticked yes
517 $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
518 if (self
::$_contribRecurPayment == TRUE) {
519 $from .= " INNER JOIN civicrm_contribution cr ON cr.contribution_recur_id = civicrm_contribution_recur.id ";
523 $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
527 case 'civicrm_financial_type':
528 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
529 $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
532 $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
536 case 'civicrm_financial_account':
537 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
538 $from = " $side JOIN civicrm_financial_account ON contact_a.id = civicrm_financial_account.contact_id ";
542 case 'civicrm_accounting_code':
543 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
544 $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' ";
545 $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
546 $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
547 $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
551 case 'civicrm_contribution_page':
552 $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page_id = civicrm_contribution_page.id";
555 case 'civicrm_product':
556 $from = " $side JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
557 $from .= " $side JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
560 case 'contribution_softcredit_type':
561 $from = " $side JOIN civicrm_option_group option_group_contribution_softcredit_type ON
562 (option_group_contribution_softcredit_type.name = 'soft_credit_type')";
563 $from .= " $side JOIN civicrm_option_value contribution_softcredit_type ON
564 ( civicrm_contribution_soft.soft_credit_type_id = contribution_softcredit_type.value
565 AND option_group_contribution_softcredit_type.id = contribution_softcredit_type.option_group_id )";
568 case 'contribution_note':
569 $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
570 civicrm_contribution.id = civicrm_note.entity_id )";
573 case 'contribution_membership':
574 $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
575 $from .= " $side JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
578 case 'civicrm_campaign':
579 //CRM-16764 - get survey clause from campaign bao
580 if (!CRM_Campaign_BAO_Query
::$_applySurveyClause) {
581 $from = " $side JOIN civicrm_campaign ON civicrm_campaign.id = civicrm_contribution.campaign_id";
585 case 'contribution_participant':
586 $from = " $side JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
587 $from .= " $side JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
590 case 'civicrm_contribution_soft':
591 if (!in_array(self
::$_contribOrSoftCredit, ["only_scredits", "both_related", "both", "only_contribs_unsoftcredited"])) {
592 $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
596 case 'civicrm_contribution_soft_contact':
597 if (in_array(self
::$_contribOrSoftCredit, ["only_scredits", "both_related", "both", "only_contribs_unsoftcredited"])) {
598 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution.contact_id = civicrm_contact_d.id )
599 AND contribution_search_scredit_combined.scredit_id IS NOT NULL";
602 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
606 case 'civicrm_contribution_soft_email':
607 $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
610 case 'civicrm_contribution_soft_phone':
611 $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
614 case 'contribution_batch':
615 $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
616 AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )";
618 $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
621 case 'civicrm_financial_trxn':
622 $from .= " $side JOIN civicrm_entity_financial_trxn ON (
623 civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
624 AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )";
626 $from .= " $side JOIN civicrm_financial_trxn ON (
627 civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )";
634 * Initialise the soft credit clause.
636 * @param CRM_Contact_BAO_Query $query
638 public static function initializeAnySoftCreditClause(&$query) {
639 // @todo have a generic initialize on all components that gets called every query
640 // & rename this to match that fn name.
641 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
642 if (self
::isSoftCreditOptionEnabled($query->_params
)) {
643 unset($query->_distinctComponentClause
);
644 $query->_rowCountClause
= " count(civicrm_contribution.id)";
645 $query->_groupByComponentClause
= " GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.contact_id, contribution_search_scredit_combined.scredit_id ";
648 $query->_distinctComponentClause
= ' civicrm_contribution.id';
654 * Check if soft credits are enables.
656 * @param array $queryParams
660 public static function isSoftCreditOptionEnabled($queryParams = []) {
661 if (!empty($queryParams)) {
662 foreach (array_keys($queryParams) as $id) {
663 if (empty($queryParams[$id][0])) {
666 if ($queryParams[$id][0] == 'contribution_or_softcredits') {
667 self
::$_contribOrSoftCredit = $queryParams[$id][2];
671 if (in_array(self
::$_contribOrSoftCredit,
672 ["only_scredits", "both_related", "both", "only_contribs_unsoftcredited"])) {
673 if (!isset(\Civi
::$statics[__CLASS__
]['soft_credit_temp_table_name'])) {
674 // build a temp table which is union of contributions and soft credits
675 // note: group-by in first part ensures uniqueness in counts
677 SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id
678 FROM civicrm_contribution con
679 LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id
680 GROUP BY id, contact_id, scredit_id, cso.id
682 SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id
683 FROM civicrm_contribution_soft as scredit';
684 \Civi
::$statics[__CLASS__
]['soft_credit_temp_table_name'] = CRM_Utils_SQL_TempTable
::build()->createWithQuery(
694 * Get return properties for soft credits.
696 * @param bool $isExportMode
700 public static function softCreditReturnProperties($isExportMode = FALSE) {
702 'contribution_soft_credit_name' => 1,
703 'contribution_soft_credit_amount' => 1,
704 'contribution_soft_credit_type' => 1,
707 $properties['contribution_soft_credit_contact_id'] = 1;
708 $properties['contribution_soft_credit_contribution_id'] = 1;
714 * Get the list of fields required to populate the selector.
716 * The default return properties array returns far too many fields for 'everyday use. Every field you add to this array
717 * kills a small kitten so add carefully.
719 * @param array $queryParams
722 public static function selectorReturnProperties($queryParams) {
725 'contact_sub_type' => 1,
727 'financial_type' => 1,
728 'contribution_source' => 1,
732 'thankyou_date' => 1,
734 // Without this value here we get an e-notice BUT the value does not appear to be rendered anywhere.
735 'contribution_campaign_id' => 1,
736 'contribution_status_id' => 1,
737 // @todo return this & fix query to do pseudoconstant thing.
738 'contribution_status' => 1,
740 'contribution_cancel_date' => 1,
741 'contribution_recur_id' => 1,
743 if (self
::isSiteHasProducts()) {
744 $properties['product_name'] = 1;
745 $properties['contribution_product_id'] = 1;
747 if (self
::isSoftCreditOptionEnabled($queryParams)) {
748 $properties = array_merge($properties, self
::softCreditReturnProperties());
755 * Do any products exist in this site's database.
759 public static function isSiteHasProducts() {
760 if (!isset(\Civi
::$statics[__CLASS__
]['has_products'])) {
761 \Civi
::$statics[__CLASS__
]['has_products'] = (bool) CRM_Core_DAO
::singleValueQuery('SELECT id FROM civicrm_contribution_product LIMIT 1');
763 return \Civi
::$statics[__CLASS__
]['has_products'];
767 * Function you should avoid.
769 * This function returns default properties for contribution queries. However, they are
770 * far more than are required in 'most' cases and you should always try to return the return properties
771 * you actually require.
773 * It would be nice to throw an e-notice when this is called but it would trash the tests :-(.
776 * @param bool $includeCustomFields
780 public static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
782 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
787 'contact_sub_type' => 1,
793 'financial_type' => 1,
795 'contribution_source' => 1,
799 'thankyou_date' => 1,
801 'contribution_cancel_date' => 1,
805 'accounting_code' => 1,
807 'payment_instrument' => 1,
809 'payment_instrument_id' => 1,
811 'contribution_check_number' => 1,
813 'non_deductible_amount' => 1,
822 'invoice_number' => 1,
826 'cancel_reason' => 1,
840 'contribution_status' => 1,
842 'contribution_status_id' => 1,
844 'contribution_recur_id' => 1,
848 'contribution_note' => 1,
850 'contribution_batch' => 1,
852 'contribution_campaign_title' => 1,
854 'contribution_campaign_id' => 1,
858 if (self
::isSiteHasProducts()) {
859 $properties['fulfilled_date'] = 1;
860 $properties['product_name'] = 1;
861 $properties['contribution_product_id'] = 1;
862 $properties['product_option'] = 1;
863 $properties['sku'] = 1;
864 $properties['contribution_start_date'] = 1;
865 $properties['contribution_end_date'] = 1;
867 if (self
::isSoftCreditOptionEnabled()) {
868 $properties = array_merge($properties, self
::softCreditReturnProperties());
870 if ($includeCustomFields) {
871 // also get all the custom contribution properties
872 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Contribution');
873 if (!empty($fields)) {
874 foreach ($fields as $name => $dontCare) {
875 $properties[$name] = 1;
884 * Get the metadata for fields to be included on the search form.
886 * @throws \CiviCRM_API3_Exception
888 public static function getSearchFieldMetadata() {
890 'contribution_source',
894 'contribution_cancel_date',
895 'contribution_page_id',
898 $metadata = civicrm_api3('Contribution', 'getfields', [])['values'];
899 $metadata['contribution_id'] = $metadata['id'];
900 return array_intersect_key($metadata, array_flip($fields));
904 * Add all the elements shared between contribute search and advanced search.
906 * @param \CRM_Contribute_Form_Search $form
908 * @throws \CRM_Core_Exception
909 * @throws \CiviCRM_API3_Exception
911 public static function buildSearchForm(&$form) {
913 $form->addSearchFieldMetadata(['Contribution' => self
::getSearchFieldMetadata()]);
914 $form->addSearchFieldMetadata(['ContributionRecur' => CRM_Contribute_BAO_ContributionRecur
::getContributionRecurSearchFieldMetadata()]);
915 $form->addFormFieldsFromMetadata();
917 $form->add('text', 'contribution_amount_low', ts('From'), ['size' => 8, 'maxlength' => 8]);
918 $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', [1 => CRM_Utils_Money
::formatLocaleNumericRoundedForDefaultCurrency('9.99')]), 'money');
920 $form->add('text', 'contribution_amount_high', ts('To'), ['size' => 8, 'maxlength' => 8]);
921 $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', [1 => CRM_Utils_Money
::formatLocaleNumericRoundedForDefaultCurrency('99.99')]), 'money');
923 // Adding select option for curreny type -- CRM-4711
924 $form->add('select', 'contribution_currency_type',
929 CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'currency', ['labelColumn' => 'name']),
930 FALSE, ['class' => 'crm-select2']
934 $form->addSelect('financial_type_id',
935 ['entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search', 'options' => CRM_Contribute_BAO_Contribution
::buildOptions('financial_type_id', 'search')]
938 // use contribution_payment_instrument_id instead of payment_instrument_id
939 // Contribution Edit form (pop-up on contribution/Contact(display Result as Contribution) open on search form),
940 // then payment method change action not working properly because of same html ID present two time on one page
941 $form->addSelect('contribution_payment_instrument_id',
942 ['entity' => 'contribution', 'field' => 'payment_instrument_id', 'multiple' => 'multiple', 'label' => ts('Payment Method'), 'option_url' => NULL, 'placeholder' => ts('- any -')]
946 'contribution_pcp_made_through_id',
947 ts('Personal Campaign Page'),
948 CRM_Contribute_PseudoConstant
::pcPage(), FALSE, ['class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -')]);
950 $statusValues = CRM_Contribute_BAO_Contribution
::buildOptions('contribution_status_id', 'search');
951 $form->add('select', 'contribution_status_id',
952 ts('Contribution Status'), $statusValues,
953 FALSE, ['class' => 'crm-select2', 'multiple' => 'multiple']
956 // Add fields for thank you and receipt
957 $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'), TRUE);
958 $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'), TRUE);
960 $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'), TRUE);
961 $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'), TRUE);
963 $form->addYesNo('contribution_test', ts('Contribution is a Test?'), TRUE);
964 // Add field for transaction ID search
965 $form->addElement('text', 'contribution_trxn_id', ts("Transaction ID"));
966 $form->addElement('text', 'contribution_check_number', ts('Check Number'));
968 // Add field for pcp display in roll search
969 $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'), TRUE);
971 // Soft credit related fields
973 'only_contribs' => ts('Contributions Only'),
974 'only_scredits' => ts('Soft Credits Only'),
975 'both_related' => ts('Contributions and their related soft credit'),
977 'only_contribs_unsoftcredited' => ts('Contributions without a soft credit'),
979 $form->add('select', 'contribution_or_softcredits', ts('Contributions OR Soft Credits?'), $options, FALSE, ['class' => "crm-select2"]);
981 'contribution_soft_credit_type_id',
983 'entity' => 'contribution_soft',
984 'field' => 'soft_credit_type_id',
986 'context' => 'search',
990 $form->addField('financial_trxn_card_type_id', ['entity' => 'FinancialTrxn', 'name' => 'card_type_id', 'action' => 'get']);
992 $form->add('text', 'financial_trxn_pan_truncation', ts('Card Number'), [
995 'autocomplete' => 'off',
998 if (CRM_Contribute_BAO_Query
::isSiteHasProducts()) {
999 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
1000 $form->add('select', 'contribution_product_id',
1002 CRM_Contribute_PseudoConstant
::products(),
1004 'class' => 'crm-select2',
1005 'multiple' => 'multiple',
1006 'placeholder' => ts('- any -'),
1011 self
::addCustomFormFields($form, ['Contribution']);
1013 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'contribution_campaign_id');
1016 $batches = CRM_Contribute_PseudoConstant
::batch();
1018 if (!empty($batches)) {
1019 $form->add('select', 'contribution_batch_id',
1022 '' => ts('- any -'),
1024 'IS NULL' => ts('None'),
1026 FALSE, ['class' => 'crm-select2']
1030 $form->assign('validCiviContribute', TRUE);
1031 $form->setDefaults(['contribution_test' => 0]);
1033 CRM_Contribute_BAO_ContributionRecur
::recurringContribution($form);
1039 * @todo better function comment needed - what IS the point of this?
1041 * @param array $tables
1043 public static function tableNames(&$tables) {
1044 // Add contribution table
1045 if (!empty($tables['civicrm_product'])) {
1046 $tables = array_merge(['civicrm_contribution' => 1], $tables);
1049 if (!empty($tables['civicrm_contribution_product']) && empty($tables['civicrm_product'])) {
1050 $tables['civicrm_product'] = 1;
1055 * Add the where for dates.
1057 * @param array $values
1058 * Array of query values.
1059 * @param object $query
1061 * @param string $name
1062 * Query field that is set.
1063 * @param string $field
1064 * Name of field to be set.
1065 * @param string $title
1066 * Title of the field.
1070 public static function buildDateWhere(&$values, $query, $name, $field, $title) {
1071 $fieldPart = strpos($name, $field);
1072 if ($fieldPart === FALSE) {
1075 // we only have recurring dates using this ATM so lets' short cut to find the table name
1076 $table = 'contribution_recur';
1077 $fieldName = explode($table . '_', $field);
1078 $query->dateQueryBuilder($values,
1079 'civicrm_' . $table, $field, $fieldName[1], $title
1085 * Add the soft credit fields to the select fields.
1087 * Extracted into separate function to improve readability of main select function.
1091 private static function addSoftCreditFields(&$query) {
1092 $includeSoftCredits = self
::isSoftCreditOptionEnabled($query->_params
);
1093 if (!empty($query->_returnProperties
['contribution_soft_credit_name'])) {
1094 if ($includeSoftCredits) {
1095 $query->_select
['contribution_soft_credit_name'] = "civicrm_contact_d.sort_name as contribution_soft_credit_name";
1096 // also include contact id. Will help build hyperlinks
1097 $query->_select
['contribution_soft_credit_contact_id'] = "civicrm_contact_d.id as contribution_soft_credit_contact_id";
1099 $query->_element
['contribution_soft_credit_name'] = 1;
1100 $query->_element
['contribution_soft_credit_contact_id'] = 1;
1102 $query->_tables
['civicrm_contribution'] = 1;
1103 $query->_tables
['civicrm_contribution_soft'] = 1;
1104 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
1107 if (!empty($query->_returnProperties
['contribution_soft_credit_contact_id'])) {
1108 $query->_tables
['civicrm_contribution'] = 1;
1109 $query->_tables
['civicrm_contribution_soft'] = 1;
1110 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
1113 if (!empty($query->_returnProperties
['contribution_soft_credit_amount'])) {
1114 if ($includeSoftCredits) {
1115 $query->_select
['contribution_soft_credit_amount'] = "civicrm_contribution_soft.amount as contribution_soft_credit_amount";
1117 $query->_element
['contribution_soft_credit_amount'] = 1;
1118 $query->_tables
['civicrm_contribution'] = 1;
1119 $query->_tables
['civicrm_contribution_soft'] = 1;
1122 if (!empty($query->_returnProperties
['contribution_soft_credit_type'])) {
1123 if ($includeSoftCredits) {
1124 $query->_select
['contribution_soft_credit_type'] = "contribution_softcredit_type.label as contribution_soft_credit_type";
1126 $query->_element
['contribution_soft_credit_type'] = 1;
1127 $query->_tables
['civicrm_contribution'] = 1;
1128 $query->_tables
['contribution_softcredit_type'] = 1;
1131 if (!empty($query->_returnProperties
['contribution_soft_credit_contribution_id'])) {
1132 if ($includeSoftCredits) {
1133 $query->_select
['contribution_soft_credit_contribution_id'] = "civicrm_contribution_soft.contribution_id as contribution_soft_credit_contribution_id";
1135 $query->_element
['contribution_soft_credit_contribution_id'] = 1;
1136 $query->_tables
['civicrm_contribution'] = 1;
1137 $query->_tables
['civicrm_contribution_soft'] = 1;
1140 if (!empty($query->_returnProperties
['contribution_soft_credit_email'])) {
1141 $query->_select
['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
1142 $query->_element
['contribution_soft_credit_email'] = 1;
1143 $query->_tables
['civicrm_contribution'] = 1;
1144 $query->_tables
['civicrm_contribution_soft'] = 1;
1145 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
1146 $query->_tables
['civicrm_contribution_soft_email'] = 1;
1149 if (!empty($query->_returnProperties
['contribution_soft_credit_phone'])) {
1150 $query->_select
['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
1151 $query->_element
['contribution_soft_credit_phone'] = 1;
1152 $query->_tables
['civicrm_contribution'] = 1;
1153 $query->_tables
['civicrm_contribution_soft'] = 1;
1154 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
1155 $query->_tables
['civicrm_contribution_soft_phone'] = 1;
1158 if (!empty($query->_returnProperties
['contribution_soft_credit_pcp_id'])) {
1159 $query->_select
['contribution_soft_credit_pcp_id'] = "civicrm_contribution_soft.pcp_id as contribution_soft_credit_pcp_id";
1160 $query->_element
['contribution_soft_credit_pcp_id'] = 1;
1161 $query->_tables
['civicrm_contribution'] = 1;
1162 $query->_tables
['civicrm_contribution_soft'] = 1;