3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
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-2015
34 class CRM_Contribute_BAO_Query
{
37 * Static field for all the export/import contribution fields.
41 static $_contributionFields = NULL;
43 static $_contribOrSoftCredit = "only_contribs";
46 * Function get the import/export fields for contribution.
49 * self::$_contributionFields associative array of contribution fields
51 public static function &getFields() {
52 if (!self
::$_contributionFields) {
53 self
::$_contributionFields = array();
55 $fields = CRM_Contribute_BAO_Contribution
::exportableFields();
57 unset($fields['contribution_contact_id']);
59 self
::$_contributionFields = $fields;
61 return self
::$_contributionFields;
65 * If contributions are involved, add the specific contribute fields.
67 * @param CRM_Contact_BAO_Query $query
69 public static function select(&$query) {
70 // if contribute mode add contribution id
71 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
72 $query->_select
['contribution_id'] = "civicrm_contribution.id as contribution_id";
73 $query->_element
['contribution_id'] = 1;
74 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
78 if (!empty($query->_returnProperties
['financial_type'])) {
79 $query->_select
['financial_type'] = "civicrm_financial_type.name as financial_type";
80 $query->_element
['financial_type'] = 1;
81 $query->_tables
['civicrm_contribution'] = 1;
82 $query->_tables
['civicrm_financial_type'] = 1;
85 // get accounting code
86 if (!empty($query->_returnProperties
['accounting_code'])) {
87 $query->_select
['accounting_code'] = "civicrm_financial_account.accounting_code as accounting_code";
88 $query->_element
['accounting_code'] = 1;
89 $query->_tables
['civicrm_accounting_code'] = 1;
90 $query->_tables
['civicrm_financial_account'] = 1;
93 if (!empty($query->_returnProperties
['contribution_note'])) {
94 $query->_select
['contribution_note'] = "civicrm_note.note as contribution_note";
95 $query->_element
['contribution_note'] = 1;
96 $query->_tables
['contribution_note'] = 1;
97 $query->_tables
['civicrm_contribution'] = 1;
100 if (!empty($query->_returnProperties
['contribution_batch'])) {
101 $query->_select
['contribution_batch'] = "civicrm_batch.title as contribution_batch";
102 $query->_element
['contribution_batch'] = 1;
103 $query->_tables
['contribution_batch'] = 1;
106 if (!empty($query->_returnProperties
['contribution_source'])) {
107 $query->_select
['contribution_source'] = "civicrm_contribution.source as contribution_source";
108 $query->_element
['contribution_source'] = 1;
109 $query->_tables
['civicrm_contribution'] = 1;
112 // get contribution_status
113 if (!empty($query->_returnProperties
['contribution_status_id'])) {
114 $query->_select
['contribution_status_id'] = "contribution_status.value as contribution_status_id";
115 $query->_element
['contribution_status_id'] = 1;
116 $query->_tables
['civicrm_contribution'] = 1;
117 $query->_tables
['contribution_status'] = 1;
120 // get contribution_status label
121 if (!empty($query->_returnProperties
['contribution_status'])) {
122 $query->_select
['contribution_status'] = "contribution_status.label as contribution_status";
123 $query->_element
['contribution_status'] = 1;
124 $query->_tables
['civicrm_contribution'] = 1;
125 $query->_tables
['contribution_status'] = 1;
128 // get payment instrument
129 if (!empty($query->_returnProperties
['payment_instrument'])) {
130 $query->_select
['payment_instrument'] = "contribution_payment_instrument.label as payment_instrument";
131 $query->_element
['payment_instrument'] = 1;
132 $query->_tables
['civicrm_contribution'] = 1;
133 $query->_tables
['contribution_payment_instrument'] = 1;
136 // get payment instrument id
137 if (!empty($query->_returnProperties
['payment_instrument_id'])) {
138 $query->_select
['instrument_id'] = "contribution_payment_instrument.value as instrument_id";
139 $query->_select
['payment_instrument_id'] = "contribution_payment_instrument.value as payment_instrument_id";
140 $query->_element
['instrument_id'] = $query->_element
['payment_instrument_id'] = 1;
141 $query->_tables
['civicrm_contribution'] = 1;
142 $query->_tables
['contribution_payment_instrument'] = 1;
145 if (!empty($query->_returnProperties
['check_number'])) {
146 $query->_select
['contribution_check_number'] = "civicrm_contribution.check_number as contribution_check_number";
147 $query->_element
['contribution_check_number'] = 1;
148 $query->_tables
['civicrm_contribution'] = 1;
151 if (!empty($query->_returnProperties
['contribution_campaign_id'])) {
152 $query->_select
['contribution_campaign_id'] = 'civicrm_contribution.campaign_id as contribution_campaign_id';
153 $query->_element
['contribution_campaign_id'] = 1;
154 $query->_tables
['civicrm_contribution'] = 1;
158 if (!empty($query->_returnProperties
['contribution_soft_credit_name'])) {
159 $query->_select
['contribution_soft_credit_name'] = "civicrm_contact_d.sort_name as contribution_soft_credit_name";
160 $query->_element
['contribution_soft_credit_name'] = 1;
162 // also include contact id. Will help build hyperlinks
163 $query->_select
['contribution_soft_credit_contact_id'] = "civicrm_contact_d.id as contribution_soft_credit_contact_id";
164 $query->_element
['contribution_soft_credit_contact_id'] = 1;
166 $query->_tables
['civicrm_contribution'] = 1;
167 $query->_tables
['civicrm_contribution_soft'] = 1;
168 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
171 if (!empty($query->_returnProperties
['contribution_soft_credit_contact_id'])) {
172 $query->_tables
['civicrm_contribution'] = 1;
173 $query->_tables
['civicrm_contribution_soft'] = 1;
176 if (!empty($query->_returnProperties
['contribution_soft_credit_amount'])) {
177 $query->_select
['contribution_soft_credit_amount'] = "civicrm_contribution_soft.amount as contribution_soft_credit_amount";
178 $query->_element
['contribution_soft_credit_amount'] = 1;
179 $query->_tables
['civicrm_contribution'] = 1;
180 $query->_tables
['civicrm_contribution_soft'] = 1;
183 if (!empty($query->_returnProperties
['contribution_soft_credit_type'])) {
184 $query->_select
['contribution_soft_credit_type'] = "contribution_softcredit_type.label as contribution_soft_credit_type";
185 $query->_element
['contribution_soft_credit_type'] = 1;
186 $query->_tables
['civicrm_contribution'] = 1;
187 $query->_tables
['contribution_softcredit_type'] = 1;
190 if (!empty($query->_returnProperties
['contribution_soft_credit_contribution_id'])) {
191 $query->_select
['contribution_soft_credit_contribution_id'] = "civicrm_contribution_soft.contribution_id as contribution_soft_credit_contribution_id";
192 $query->_element
['contribution_soft_credit_contribution_id'] = 1;
193 $query->_tables
['civicrm_contribution'] = 1;
194 $query->_tables
['civicrm_contribution_soft'] = 1;
197 if (!empty($query->_returnProperties
['contribution_soft_credit_email'])) {
198 $query->_select
['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
199 $query->_element
['contribution_soft_credit_email'] = 1;
200 $query->_tables
['civicrm_contribution'] = 1;
201 $query->_tables
['civicrm_contribution_soft'] = 1;
202 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
203 $query->_tables
['civicrm_contribution_soft_email'] = 1;
206 if (!empty($query->_returnProperties
['contribution_soft_credit_phone'])) {
207 $query->_select
['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
208 $query->_element
['contribution_soft_credit_phone'] = 1;
209 $query->_tables
['civicrm_contribution'] = 1;
210 $query->_tables
['civicrm_contribution_soft'] = 1;
211 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
212 $query->_tables
['civicrm_contribution_soft_phone'] = 1;
214 if (!empty($query->_returnProperties
['contribution_campaign_title'])) {
215 $query->_select
['contribution_campaign_title'] = "civicrm_campaign.title as contribution_campaign_title";
216 $query->_element
['contribution_campaign_title'] = $query->_tables
['civicrm_campaign'] = 1;
219 // Adding address_id in a way that is more easily extendable since the above is a bit ... wordy.
220 $supportedBasicReturnValues = array('address_id');
221 foreach ($supportedBasicReturnValues as $fieldName) {
222 if (!empty($query->_returnProperties
[$fieldName])) {
223 $query->_select
[$fieldName] = "civicrm_contribution.{$fieldName} as $fieldName";
224 $query->_element
[$fieldName] = $query->_tables
['civicrm_contribution'] = 1;
228 //CRM-16116: get financial_type_id
229 if (!empty($query->_returnProperties
['financial_type_id'])) {
230 $query->_select
['financial_type_id'] = "civicrm_contribution.financial_type_id as financial_type_id";
231 $query->_element
['financial_type_id'] = $query->_tables
['civicrm_contribution'] = 1;
239 * @param CRM_Contact_BAO_Query $query
241 public static function where(&$query) {
243 self
::initializeAnySoftCreditClause($query);
244 foreach (array_keys($query->_params
) as $id) {
245 if (empty($query->_params
[$id][0])) {
248 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_') {
249 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
250 $query->_useDistinct
= TRUE;
254 $query->_params
[$id][0] == 'contribution_type_id' ||
255 $query->_params
[$id][0] == 'contribution_type'
257 CRM_Core_Session
::setStatus(
258 ts('The contribution type criteria is now obsolete, please update your smart group'),
265 $grouping = $query->_params
[$id][3];
266 self
::whereClauseSingle($query->_params
[$id], $query);
272 * Get where clause for a single value.
274 * @param array $values
275 * @param CRM_Contact_BAO_Query $query
277 public static function whereClauseSingle(&$values, &$query) {
278 list($name, $op, $value, $grouping, $wildcard) = $values;
281 $fields = array_merge(CRM_Contribute_BAO_Contribution
::fields(), self
::getFields());
283 if (!empty($value) && !is_array($value)) {
284 $quoteValue = "\"$value\"";
287 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
288 foreach (self
::getRecurringFields() as $dateField => $dateFieldTitle) {
289 if (self
::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
294 case 'contribution_date':
295 case 'contribution_date_low':
296 case 'contribution_date_low_time':
297 case 'contribution_date_high':
298 case 'contribution_date_high_time':
299 // process to / from date
300 $query->dateQueryBuilder($values,
301 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
305 case 'contribution_amount':
306 case 'contribution_amount_low':
307 case 'contribution_amount_high':
308 // process min/max amount
309 $query->numberRangeBuilder($values,
310 'civicrm_contribution', 'contribution_amount',
311 'total_amount', 'Contribution Amount',
316 case 'contribution_thankyou_date_is_not_null':
319 $query->_qill
[$grouping][] = ts('Contribution Thank-you Sent');
323 $query->_qill
[$grouping][] = ts('Contribution Thank-you Not Sent');
325 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.thankyou_date", $op);
326 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
329 case 'contribution_receipt_date_is_not_null':
332 $query->_qill
[$grouping][] = ts('Contribution Receipt Sent');
336 $query->_qill
[$grouping][] = ts('Contribution Receipt Not Sent');
338 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.receipt_date", $op);
339 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
342 case 'financial_type':
343 case 'contribution_page':
344 case 'payment_instrument':
345 case 'contribution_payment_instrument':
346 case 'contribution_status':
348 case 'financial_type_id':
350 case 'payment_instrument_id':
351 case 'contribution_payment_instrument_id':
352 case 'contribution_page_id':
353 case 'contribution_status_id':
354 case 'contribution_id':
355 case 'contribution_currency_type':
356 case 'contribution_currency':
357 case 'contribution_source':
358 case 'contribution_trxn_id':
359 case 'contribution_check_number':
360 case 'contribution_contact_id':
361 case (strpos($name, '_amount') !== FALSE):
362 case (strpos($name, '_date') !== FALSE && $name != 'contribution_fulfilled_date'):
364 $pseudoExtraParam = NULL;
365 // @todo including names using a switch statement & then using an 'if' to filter them out is ... odd!
366 if ((strpos($name, '_amount') !== FALSE) ||
(strpos($name, '_date') !== FALSE) ||
in_array($name,
369 'contribution_currency',
370 'contribution_source',
371 'contribution_trxn_id',
372 'contribution_check_number',
373 'contribution_payment_instrument_id',
374 'contribution_contact_id',
378 $name = str_replace('contribution_', '', $name);
379 if (!in_array($name, array('source', 'id', 'contact_id'))) {
380 $qillName = str_replace('contribution_', '', $qillName);
383 if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
384 $qillName = $name = 'currency';
385 $pseudoExtraParam = array('labelColumn' => 'name');
388 $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type
::typeToString($fields[$qillName]['type']) : 'String';
390 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.$name", $op, $value, $dataType);
391 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
392 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
393 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
396 case 'contribution_pcp_made_through_id':
397 case 'contribution_soft_credit_type_id':
399 if ($name == 'contribution_pcp_made_through_id') {
400 $qillName = $name = 'pcp_id';
401 $fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
403 if ($name == 'contribution_soft_credit_type_id') {
404 $qillName = str_replace('_id', '', $qillName);
405 $fields[$qillName]['type'] = $fields[$qillName]['data_type'];
406 $name = str_replace('contribution_', '', $name);
408 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution_soft.$name",
409 $op, $value, CRM_Utils_Type
::typeToString($fields[$qillName]['type'])
411 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
412 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
413 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
416 case 'contribution_or_softcredits':
417 if ($value == 'only_scredits') {
418 $query->_where
[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
419 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
420 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
421 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
423 elseif ($value == 'both_related') {
424 $query->_where
[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
425 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
426 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
427 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
429 elseif ($value == 'both') {
430 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Both');
431 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
432 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
434 // default option: $value == 'only_contribs'
437 case 'contribution_is_test':
438 // By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
439 // so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
440 $key = array_search('civicrm_contribution.is_test = 0', $query->_where
[$grouping]);
442 unset($query->_where
[$grouping][$key]);
444 case 'contribution_test':
445 // We dont want to include all tests for sql OR CRM-7827
446 if (!$value ||
$query->getOperator() != 'OR') {
447 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
449 $query->_qill
[$grouping][] = ts("Only Display Test Contributions");
451 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
455 case 'contribution_is_pay_later':
456 case 'contribution_pay_later':
457 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
459 $query->_qill
[$grouping][] = ts("Find Pay Later Contributions");
462 $query->_qill
[$grouping][] = ts("Exclude Pay Later Contributions");
464 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
467 case 'contribution_recurring':
469 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
470 $query->_qill
[$grouping][] = ts("Find Recurring Contributions");
471 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
474 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
475 $query->_qill
[$grouping][] = ts("Exclude Recurring Contributions");
479 case 'contribution_recur_id':
480 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.contribution_recur_id",
481 $op, $value, "Integer"
483 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
486 case 'contribution_note':
487 $value = $strtolower(CRM_Core_DAO
::escapeString($value));
492 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_note.note)" : "civicrm_note.note";
493 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
494 $query->_qill
[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
495 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = $query->_whereTables
['contribution_note'] = 1;
498 case 'contribution_membership_id':
499 $query->_where
[$grouping][] = " civicrm_membership.id $op $value";
500 $query->_tables
['contribution_membership'] = $query->_whereTables
['contribution_membership'] = 1;
504 case 'contribution_participant_id':
505 $query->_where
[$grouping][] = " civicrm_participant.id $op $value";
506 $query->_tables
['contribution_participant'] = $query->_whereTables
['contribution_participant'] = 1;
509 case 'contribution_pcp_display_in_roll':
510 $query->_where
[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
512 $query->_qill
[$grouping][] = ts("Personal Campaign Page Honor Roll");
515 $query->_qill
[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
517 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
520 case 'contribution_campaign_id':
523 'campaign' => $value,
524 'grouping' => $grouping,
525 'tableName' => 'civicrm_contribution',
527 CRM_Campaign_BAO_Query
::componentSearchClause($campParams, $query);
530 case 'contribution_batch_id':
531 $batches = CRM_Contribute_PseudoConstant
::batch();
532 $query->_where
[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
533 $query->_qill
[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
534 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
535 $query->_tables
['contribution_batch'] = $query->_whereTables
['contribution_batch'] = 1;
538 case 'contribution_product_id':
539 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
541 list($operator, $productValue) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contribute_DAO_Product', $name, $value, $op);
542 $query->_qill
[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $operator, 3 => $productValue));
543 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_product.id", $op, $value);
544 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
548 //all other elements are handle in this case
549 $fldName = substr($name, 13);
550 if (!isset($fields[$fldName])) {
552 CRM_Core_Session
::setStatus(ts(
553 'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
559 $whereTable = $fields[$fldName];
560 $value = trim($value);
562 $dataType = "String";
563 if (!empty($whereTable['type'])) {
564 $dataType = CRM_Utils_Type
::typeToString($whereTable['type']);
567 $wc = ($op != 'LIKE' && $dataType != 'Date') ?
"LOWER($whereTable[where])" : "$whereTable[where]";
568 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, $dataType);
569 $query->_qill
[$grouping][] = "$whereTable[title] $op $quoteValue";
570 list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
571 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;
572 if ($tableName == 'civicrm_contribution_product') {
573 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
574 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
577 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
585 * @param string $name
586 * @param string $mode
587 * @param string $side
589 * @return NULL|string
591 public static function from($name, $mode, $side) {
594 case 'civicrm_contribution':
595 $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
596 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
597 // switch the from table if its only soft credit search
598 $from = " $side JOIN contribution_search_scredit_combined ON contribution_search_scredit_combined.contact_id = contact_a.id ";
599 $from .= " $side JOIN civicrm_contribution ON civicrm_contribution.id = contribution_search_scredit_combined.id ";
600 $from .= " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.id = contribution_search_scredit_combined.scredit_id";
604 case 'civicrm_contribution_recur':
606 // in contact mode join directly onto profile - in case no contributions exist yet
607 $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
610 $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
614 case 'civicrm_financial_type':
615 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
616 $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
619 $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
623 case 'civicrm_financial_account':
624 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
625 $from = " $side JOIN civicrm_financial_account ON contact_a.id = civicrm_financial_account.contact_id ";
629 case 'civicrm_accounting_code':
630 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
631 $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' ";
632 $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
633 $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
634 $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
638 case 'civicrm_contribution_page':
639 $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page_id = civicrm_contribution_page.id";
642 case 'civicrm_product':
643 $from = " $side JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
644 $from .= " $side JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
647 case 'contribution_payment_instrument':
648 $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
649 $from .= " $side JOIN civicrm_option_value contribution_payment_instrument ON (civicrm_contribution.payment_instrument_id = contribution_payment_instrument.value
650 AND option_group_payment_instrument.id = contribution_payment_instrument.option_group_id ) ";
653 case 'contribution_status':
654 $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
655 $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
656 AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
659 case 'contribution_softcredit_type':
660 $from = " $side JOIN civicrm_option_group option_group_contribution_softcredit_type ON
661 (option_group_contribution_softcredit_type.name = 'soft_credit_type')";
662 $from .= " $side JOIN civicrm_option_value contribution_softcredit_type ON
663 ( civicrm_contribution_soft.soft_credit_type_id = contribution_softcredit_type.value
664 AND option_group_contribution_softcredit_type.id = contribution_softcredit_type.option_group_id )";
667 case 'contribution_note':
668 $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
669 civicrm_contribution.id = civicrm_note.entity_id )";
672 case 'contribution_membership':
673 $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
674 $from .= " $side JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
677 case 'civicrm_campaign':
678 //CRM-16764 - get survey clause from campaign bao
679 if (!CRM_Campaign_BAO_Query
::$_applySurveyClause) {
680 $from = " $side JOIN civicrm_campaign ON civicrm_campaign.id = civicrm_contribution.campaign_id";
684 case 'contribution_participant':
685 $from = " $side JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
686 $from .= " $side JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
689 case 'civicrm_contribution_soft':
690 if (!in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
691 $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
695 case 'civicrm_contribution_soft_contact':
696 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
697 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution.contact_id = civicrm_contact_d.id )
698 AND contribution_search_scredit_combined.scredit_id IS NOT NULL";
701 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
705 case 'civicrm_contribution_soft_email':
706 $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
709 case 'civicrm_contribution_soft_phone':
710 $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
713 case 'contribution_batch':
714 $from .= " $side JOIN civicrm_entity_financial_trxn ON (
715 civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
716 AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )";
718 $from .= " $side JOIN civicrm_financial_trxn ON (
719 civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )";
721 $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
722 AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )";
724 $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
731 * Initialise the soft credit clause.
733 * @param CRM_Contact_BAO_Query $query
735 public static function initializeAnySoftCreditClause(&$query) {
736 if (self
::isSoftCreditOptionEnabled($query->_params
)) {
737 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
738 unset($query->_distinctComponentClause
);
739 $query->_rowCountClause
= " count(civicrm_contribution.id)";
740 $query->_groupByComponentClause
= " GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.contact_id, contribution_search_scredit_combined.scredit_id ";
746 * Check if soft credits are enables.
748 * @param array $queryParams
752 public static function isSoftCreditOptionEnabled($queryParams = array()) {
753 static $tempTableFilled = FALSE;
754 if (!empty($queryParams)) {
755 foreach (array_keys($queryParams) as $id) {
756 if (empty($queryParams[$id][0])) {
759 if ($queryParams[$id][0] == 'contribution_or_softcredits') {
760 self
::$_contribOrSoftCredit = $queryParams[$id][2];
764 if (in_array(self
::$_contribOrSoftCredit,
765 array("only_scredits", "both_related", "both"))) {
766 if (!$tempTableFilled) {
767 // build a temp table which is union of contributions and soft credits
768 // note: group-by in first part ensures uniqueness in counts
770 CREATE TEMPORARY TABLE IF NOT EXISTS contribution_search_scredit_combined AS
771 SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id
772 FROM civicrm_contribution con
773 LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id
774 GROUP BY id, contact_id, scredit_id
776 SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id
777 FROM civicrm_contribution_soft as scredit";
778 CRM_Core_DAO
::executeQuery($tempQuery);
779 $tempTableFilled = TRUE;
787 * Get return properties for soft credits.
789 * @param bool $isExportMode
793 public static function softCreditReturnProperties($isExportMode = FALSE) {
795 'contribution_soft_credit_name' => 1,
796 'contribution_soft_credit_amount' => 1,
797 'contribution_soft_credit_type' => 1,
800 $properties['contribution_soft_credit_contribution_id'] = 1;
807 * @param bool $includeCustomFields
811 public static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
813 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
816 'contact_sub_type' => 1,
819 'financial_type' => 1,
820 'contribution_source' => 1,
822 'thankyou_date' => 1,
825 'accounting_code' => 1,
826 'payment_instrument' => 1,
827 'payment_instrument_id' => 1,
829 'non_deductible_amount' => 1,
835 'cancel_reason' => 1,
839 'product_option' => 1,
840 'fulfilled_date' => 1,
841 'contribution_start_date' => 1,
842 'contribution_end_date' => 1,
845 'contribution_status' => 1,
846 'contribution_status_id' => 1,
847 'contribution_recur_id' => 1,
849 'contribution_note' => 1,
850 'contribution_batch' => 1,
851 'contribution_campaign_title' => 1,
852 'contribution_campaign_id' => 1,
853 'contribution_product_id' => 1,
855 if (self
::isSoftCreditOptionEnabled()) {
856 $properties = array_merge($properties, self
::softCreditReturnProperties());
858 if ($includeCustomFields) {
859 // also get all the custom contribution properties
860 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Contribution');
861 if (!empty($fields)) {
862 foreach ($fields as $name => $dontCare) {
863 $properties[$name] = 1;
872 * Add all the elements shared between contribute search and advnaced search.
875 * @param CRM_Core_Form $form
879 public static function buildSearchForm(&$form) {
881 // Added contribution source
882 $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO
::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
884 CRM_Core_Form_Date
::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
886 $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
887 $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('9.99', ' '))), 'money');
889 $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
890 $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('99.99', ' '))), 'money');
892 // Adding select option for curreny type -- CRM-4711
893 $form->add('select', 'contribution_currency_type',
898 CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name')),
899 FALSE, array('class' => 'crm-select2')
903 $form->addSelect('financial_type_id',
904 array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search')
907 $form->add('select', 'contribution_page_id',
908 ts('Contribution Page'),
909 CRM_Contribute_PseudoConstant
::contributionPage(),
910 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -'))
913 $form->addSelect('payment_instrument_id',
914 array('entity' => 'contribution', 'multiple' => 'multiple', 'label' => ts('Payment Method'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
917 // Fixme: Not a true entityRef field. Relies on PCP.js.tpl
918 $form->add('text', 'contribution_pcp_made_through_id', ts('Personal Campaign Page'), array('class' => 'twenty', 'id' => 'pcp_made_through_id', 'placeholder' => ts('- any -')));
920 $form->add('hidden', 'pcp_made_through');
922 $statusValues = CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'contribution_status_id');
923 // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
924 unset($statusValues['5'], $statusValues['6']);
925 $form->add('select', 'contribution_status_id',
926 ts('Contribution Status'), $statusValues,
927 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
930 // Add fields for thank you and receipt
931 $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'), TRUE);
932 $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'), TRUE);
934 $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'), TRUE);
935 $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'), TRUE);
937 // Recurring contribution fields
938 foreach (self
::getRecurringFields() as $key => $label) {
939 CRM_Core_Form_Date
::buildDateRange($form, $key, 1, '_low', '_high');
940 // If data has been entered for a recurring field, tell the tpl layer to open the pane
941 if (!empty($form->_formValues
[$key . '_relative']) ||
!empty($form->_formValues
[$key . '_low']) ||
!empty($form->_formValues
[$key . '_high'])) {
942 $form->assign('contribution_recur_pane_open', TRUE);
946 $form->addYesNo('contribution_test', ts('Contribution is a Test?'), TRUE);
948 // Add field for transaction ID search
949 $form->addElement('text', 'contribution_trxn_id', ts("Transaction ID"));
950 $form->addElement('text', 'invoice_id', ts("Invoice ID"));
951 $form->addElement('text', 'contribution_check_number', ts('Check Number'));
953 // Add field for pcp display in roll search
954 $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'), TRUE);
956 // Soft credit related fields
958 'only_contribs' => ts('Contributions Only'),
959 'only_scredits' => ts('Soft Credits Only'),
960 'both_related' => ts('Soft Credits with related Hard Credit'),
961 'both' => ts('Both'),
963 $form->add('select', 'contribution_or_softcredits', ts('Contributions OR Soft Credits?'), $options, FALSE, array('class' => "crm-select2"));
965 'contribution_soft_credit_type_id',
967 'entity' => 'contribution_soft',
968 'field' => 'soft_credit_type_id',
970 'context' => 'search',
974 // CRM-16713 - contribution search by premiums on 'Find Contribution' form.
975 $form->add('select', 'contribution_product_id',
977 CRM_Contribute_PseudoConstant
::products(),
978 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -'))
981 // Add all the custom searchable fields
982 $contribution = array('Contribution');
983 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $contribution);
985 $form->assign('contributeGroupTree', $groupDetails);
986 foreach ($groupDetails as $group) {
987 foreach ($group['fields'] as $field) {
988 $fieldId = $field['id'];
989 $elementName = 'custom_' . $fieldId;
990 CRM_Core_BAO_CustomField
::addQuickFormElement($form,
999 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'contribution_campaign_id');
1002 $batches = CRM_Contribute_PseudoConstant
::batch();
1004 if (!empty($batches)) {
1005 $form->add('select', 'contribution_batch_id',
1007 array('' => ts('- any -')) +
$batches,
1008 FALSE, array('class' => 'crm-select2')
1012 $form->assign('validCiviContribute', TRUE);
1013 $form->setDefaults(array('contribution_test' => 0));
1017 * Function that may not be needed.
1022 public static function searchAction(&$row, $id) {
1028 * @todo better function comment needed - what IS the point of this?
1030 * @param array $tables
1032 public static function tableNames(&$tables) {
1033 // Add contribution table
1034 if (!empty($tables['civicrm_product'])) {
1035 $tables = array_merge(array('civicrm_contribution' => 1), $tables);
1038 if (!empty($tables['civicrm_contribution_product']) && empty($tables['civicrm_product'])) {
1039 $tables['civicrm_product'] = 1;
1044 * Add the where for dates.
1046 * @param array $values
1047 * Array of query values.
1048 * @param object $query
1050 * @param string $name
1051 * Query field that is set.
1052 * @param string $field
1053 * Name of field to be set.
1054 * @param string $title
1055 * Title of the field.
1059 public static function buildDateWhere(&$values, $query, $name, $field, $title) {
1060 $fieldPart = strpos($name, $field);
1061 if ($fieldPart === FALSE) {
1064 // we only have recurring dates using this ATM so lets' short cut to find the table name
1065 $table = 'contribution_recur';
1066 $fieldName = explode($table . '_', $field);
1067 $query->dateQueryBuilder($values,
1068 'civicrm_' . $table, $field, $fieldName[1], $title
1074 * Get fields for recurring contributions.
1078 public static function getRecurringFields() {
1080 'contribution_recur_start_date' => ts('Recurring Contribution Start Date'),
1081 'contribution_recur_next_sched_contribution_date' => ts('Next Scheduled Recurring Contribution'),
1082 'contribution_recur_cancel_date' => ts('Recurring Contribution Cancel Date'),
1083 'contribution_recur_end_date' => ts('Recurring Contribution End Date'),
1084 'contribution_recur_create_date' => ('Recurring Contribution Create Date'),
1085 'contribution_recur_modified_date' => ('Recurring Contribution Modified Date'),
1086 'contribution_recur_failure_retry_date' => ts('Failed Recurring Contribution Retry Date'),