3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2014
35 class CRM_Contribute_BAO_Query
{
38 * Static field for all the export/import contribution fields
43 static $_contributionFields = NULL;
45 static $_contribOrSoftCredit = "only_contribs";
48 * Function get the import/export fields for contribution
50 * @return array self::$_contributionFields associative array of contribution fields
53 static function &getFields() {
54 if (!self
::$_contributionFields) {
55 self
::$_contributionFields = array();
57 $fields = CRM_Contribute_BAO_Contribution
::exportableFields();
59 unset($fields['contribution_contact_id']);
61 self
::$_contributionFields = $fields;
63 return self
::$_contributionFields;
67 * If contributions are involved, add the specific contribute fields
74 static function select(&$query) {
75 // if contribute mode add contribution id
76 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
77 $query->_select
['contribution_id'] = "civicrm_contribution.id as contribution_id";
78 $query->_element
['contribution_id'] = 1;
79 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
83 if (!empty($query->_returnProperties
['financial_type'])) {
84 $query->_select
['financial_type'] = "civicrm_financial_type.name as financial_type";
85 $query->_element
['financial_type'] = 1;
86 $query->_tables
['civicrm_contribution'] = 1;
87 $query->_tables
['civicrm_financial_type'] = 1;
90 // get accounting code
91 if (!empty($query->_returnProperties
['accounting_code'])) {
92 $query->_select
['accounting_code'] = "civicrm_financial_account.accounting_code as accounting_code";
93 $query->_element
['accounting_code'] = 1;
94 $query->_tables
['civicrm_accounting_code'] = 1;
95 $query->_tables
['civicrm_financial_account'] = 1;
98 if (!empty($query->_returnProperties
['contribution_note'])) {
99 $query->_select
['contribution_note'] = "civicrm_note.note as contribution_note";
100 $query->_element
['contribution_note'] = 1;
101 $query->_tables
['contribution_note'] = 1;
102 $query->_tables
['civicrm_contribution'] = 1;
105 if (!empty($query->_returnProperties
['contribution_batch'])) {
106 $query->_select
['contribution_batch'] = "civicrm_batch.title as contribution_batch";
107 $query->_element
['contribution_batch'] = 1;
108 $query->_tables
['contribution_batch'] = 1;
111 // get contribution_status
112 if (!empty($query->_returnProperties
['contribution_status_id'])) {
113 $query->_select
['contribution_status_id'] = "contribution_status.value as contribution_status_id";
114 $query->_element
['contribution_status_id'] = 1;
115 $query->_tables
['civicrm_contribution'] = 1;
116 $query->_tables
['contribution_status'] = 1;
119 // get contribution_status label
120 if (!empty($query->_returnProperties
['contribution_status'])) {
121 $query->_select
['contribution_status'] = "contribution_status.label as contribution_status";
122 $query->_element
['contribution_status'] = 1;
123 $query->_tables
['civicrm_contribution'] = 1;
124 $query->_tables
['contribution_status'] = 1;
127 // get payment instruments
128 if (!empty($query->_returnProperties
['payment_instrument'])) {
129 $query->_select
['contribution_payment_instrument'] = "payment_instrument.name as contribution_payment_instrument";
130 $query->_element
['contribution_payment_instrument'] = 1;
131 $query->_tables
['civicrm_contribution'] = 1;
132 $query->_tables
['contribution_payment_instrument'] = 1;
135 if (!empty($query->_returnProperties
['check_number'])) {
136 $query->_select
['contribution_check_number'] = "civicrm_contribution.check_number as contribution_check_number";
137 $query->_element
['contribution_check_number'] = 1;
138 $query->_tables
['civicrm_contribution'] = 1;
141 if (!empty($query->_returnProperties
['contribution_campaign_id'])) {
142 $query->_select
['contribution_campaign_id'] = 'civicrm_contribution.campaign_id as contribution_campaign_id';
143 $query->_element
['contribution_campaign_id'] = 1;
144 $query->_tables
['civicrm_contribution'] = 1;
148 if (!empty($query->_returnProperties
['contribution_soft_credit_name'])) {
149 $query->_select
['contribution_soft_credit_name'] = "civicrm_contact_d.sort_name as contribution_soft_credit_name";
150 $query->_element
['contribution_soft_credit_name'] = 1;
152 // also include contact id. Will help build hyperlinks
153 $query->_select
['contribution_soft_credit_contact_id'] = "civicrm_contact_d.id as contribution_soft_credit_contact_id";
154 $query->_element
['contribution_soft_credit_contact_id'] = 1;
156 $query->_tables
['civicrm_contribution'] = 1;
157 $query->_tables
['civicrm_contribution_soft'] = 1;
158 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
161 if (!empty($query->_returnProperties
['contribution_soft_credit_contact_id'])) {
162 $query->_tables
['civicrm_contribution'] = 1;
163 $query->_tables
['civicrm_contribution_soft'] = 1;
166 if (!empty($query->_returnProperties
['contribution_soft_credit_amount'])) {
167 $query->_select
['contribution_soft_credit_amount'] = "civicrm_contribution_soft.amount as contribution_soft_credit_amount";
168 $query->_element
['contribution_soft_credit_amount'] = 1;
169 $query->_tables
['civicrm_contribution'] = 1;
170 $query->_tables
['civicrm_contribution_soft'] = 1;
173 if (!empty($query->_returnProperties
['contribution_soft_credit_type'])) {
174 $query->_select
['contribution_soft_credit_type'] = "contribution_softcredit_type.label as contribution_soft_credit_type";
175 $query->_element
['contribution_soft_credit_type'] = 1;
176 $query->_tables
['civicrm_contribution'] = 1;
177 $query->_tables
['contribution_softcredit_type'] = 1;
180 if (!empty($query->_returnProperties
['contribution_soft_credit_contribution_id'])) {
181 $query->_select
['contribution_soft_credit_contribution_id'] = "civicrm_contribution_soft.contribution_id as contribution_soft_credit_contribution_id";
182 $query->_element
['contribution_soft_credit_contribution_id'] = 1;
183 $query->_tables
['civicrm_contribution'] = 1;
184 $query->_tables
['civicrm_contribution_soft'] = 1;
187 if (!empty($query->_returnProperties
['contribution_soft_credit_email'])) {
188 $query->_select
['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
189 $query->_element
['contribution_soft_credit_email'] = 1;
190 $query->_tables
['civicrm_contribution'] = 1;
191 $query->_tables
['civicrm_contribution_soft'] = 1;
192 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
193 $query->_tables
['civicrm_contribution_soft_email'] = 1;
196 if (!empty($query->_returnProperties
['contribution_soft_credit_phone'])) {
197 $query->_select
['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
198 $query->_element
['contribution_soft_credit_phone'] = 1;
199 $query->_tables
['civicrm_contribution'] = 1;
200 $query->_tables
['civicrm_contribution_soft'] = 1;
201 $query->_tables
['civicrm_contribution_soft_contact'] = 1;
202 $query->_tables
['civicrm_contribution_soft_phone'] = 1;
210 static function where(&$query) {
213 self
::initializeAnySoftCreditClause($query);
214 foreach (array_keys($query->_params
) as $id) {
215 if (empty($query->_params
[$id][0])) {
218 if (substr($query->_params
[$id][0], 0, 13) == 'contribution_' ||
substr($query->_params
[$id][0], 0, 10) == 'financial_') {
219 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
220 $query->_useDistinct
= TRUE;
224 $query->_params
[$id][0] == 'contribution_type_id' ||
225 $query->_params
[$id][0] == 'contribution_type'
227 CRM_Core_Session
::setStatus(
228 ts('The contribution type criteria is now obsolete, please update your smart group'),
235 $grouping = $query->_params
[$id][3];
236 self
::whereClauseSingle($query->_params
[$id], $query);
245 static function whereClauseSingle(&$values, &$query) {
246 list($name, $op, $value, $grouping, $wildcard) = $values;
249 $fields = self
::getFields();
251 if (!empty($value) && !is_array($value)) {
252 $quoteValue = "\"$value\"";
255 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
256 foreach (self
::getRecurringFields() as $dateField => $dateFieldTitle) {
257 if(self
::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
262 case 'contribution_date':
263 case 'contribution_date_low':
264 case 'contribution_date_low_time':
265 case 'contribution_date_high':
266 case 'contribution_date_high_time':
267 // process to / from date
268 $query->dateQueryBuilder($values,
269 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
273 case 'contribution_amount':
274 case 'contribution_amount_low':
275 case 'contribution_amount_high':
276 // process min/max amount
277 $query->numberRangeBuilder($values,
278 'civicrm_contribution', 'contribution_amount',
279 'total_amount', 'Contribution Amount',
284 case 'contribution_total_amount':
285 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.total_amount",
288 $query->_qill
[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
289 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
292 case 'contribution_thankyou_date_is_not_null':
295 $query->_qill
[$grouping][] = ts('Contribution Thank-you Sent');
299 $query->_qill
[$grouping][] = ts('Contribution Thank-you Not Sent');
301 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.thankyou_date", $op);
302 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
305 case 'contribution_receipt_date_is_not_null':
308 $query->_qill
[$grouping][] = ts('Contribution Receipt Sent');
312 $query->_qill
[$grouping][] = ts('Contribution Receipt Not Sent');
314 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.receipt_date", $op);
315 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
318 case 'financial_type_id':
319 case 'financial_type':
320 // The financial_type_id might be an array (from aggregate contributions custom search)
321 // In this case, we need to change the query.
322 if (is_array($value)) {
324 // Rebuild the array to get the data we're interested in as array
325 // values not array keys.
326 foreach ($value as $k => $v) {
331 if (count($val) > 0) {
332 // Overwrite $value so it works with an IN where statement.
334 $value = '(' . implode(',', $val) . ')';
337 // If we somehow have an empty array, just return
342 $types = CRM_Contribute_PseudoConstant
::financialType();
344 // Ensure we have a sensible string to display to the user.
346 if (isset($val) && is_array($val)) {
347 foreach($val as $id) {
348 $names[] = CRM_Utils_Array
::value($id, $types);
352 if (!empty($value)) {
353 $names[] = $types[$value];
357 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.financial_type_id",
358 $op, $value, "Integer"
360 $query->_qill
[$grouping][] = ts('Financial Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
361 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
364 case 'contribution_page_id':
366 $pages = CRM_Contribute_PseudoConstant
::contributionPage();
367 $query->_where
[$grouping][] = "civicrm_contribution.contribution_page_id = $cPage";
368 $query->_qill
[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
369 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
372 case 'contribution_pcp_made_through_id':
374 $pcpages = CRM_Contribute_PseudoConstant
::pcPage();
375 $query->_where
[$grouping][] = "civicrm_contribution_soft.pcp_id = $pcPage";
376 $query->_qill
[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
377 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
380 case 'contribution_or_softcredits':
381 if ($value == 'only_scredits') {
382 $query->_where
[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
383 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
384 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
385 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
386 } else if ($value == 'both_related') {
387 $query->_where
[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
388 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
389 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
390 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
391 } else if ($value == 'both') {
392 $query->_qill
[$grouping][] = ts('Contributions OR Soft Credits? - Both');
393 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
394 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
396 // default option: $value == 'only_contribs'
399 case 'contribution_soft_credit_type_id':
401 $softCreditTypes = CRM_Core_OptionGroup
::values("soft_credit_type");
402 if (is_array($value)) {
404 foreach ($value as $k => $v) {
407 $names[] = $softCreditTypes[$v];
410 $scTypes = (count($val) > 0) ?
implode(',', $val) : '';
413 $scTypes = "({$scTypes})";
418 $names[] = $softCreditTypes[$value];
420 $query->_qill
[$grouping][] = ts('Soft Credit Type %1', array(1 => $op)) . " '" . implode("' " . ts('or') . " '", $names) . "'";
421 $query->_where
[$grouping][] =
422 CRM_Contact_BAO_Query
::buildClause(
423 "civicrm_contribution_soft.soft_credit_type_id",
428 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
431 case 'contribution_payment_instrument_id':
432 case 'contribution_payment_instrument':
434 $pis = CRM_Contribute_PseudoConstant
::paymentInstrument();
435 if (is_array($value)) {
436 foreach ($value as $k => $v) {
444 if (!empty($value)) {
445 $pi[] = $pis[$value];
449 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.payment_instrument_id",
450 $op, $value, "Integer"
453 $query->_qill
[$grouping][] = ts('Paid By - %1', array(1 => $op)) . " '" . implode("' " . ts('or') . " '", $pi) . "'";
454 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
457 case 'contribution_status':
458 case 'contribution_status_id':
459 if (is_array($value)) {
460 foreach ($value as $k => $v) {
466 $status = implode(',', $val);
468 if (count($val) > 0) {
470 $status = "({$status})";
477 $statusValues = CRM_Core_OptionGroup
::values("contribution_status");
483 foreach ($val as $id => $dontCare) {
484 $names[] = $statusValues[$id];
488 if (!empty($value)) {
489 $names[] = $statusValues[$value];
493 $query->_qill
[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
494 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.contribution_status_id",
499 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
502 case 'contribution_source':
503 $value = $strtolower(CRM_Core_DAO
::escapeString($value));
508 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
509 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
510 $query->_qill
[$grouping][] = ts('Contribution Source %1 %2', array(1 => $op, 2 => $quoteValue));
511 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
514 case 'contribution_trxn_id':
515 case 'contribution_transaction_id':
516 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
517 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
518 $query->_qill
[$grouping][] = ts('Transaction ID %1 %2', array(1 => $op, 2 => $quoteValue));
519 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
522 case 'contribution_check_number':
523 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
524 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
525 $query->_qill
[$grouping][] = ts('Check Number %1 %2', array(1 => $op, 2 => $quoteValue));
526 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
529 case 'contribution_is_test':
530 case 'contribution_test':
531 // We dont want to include all tests for sql OR CRM-7827
532 if (!$value ||
$query->getOperator() != 'OR') {
533 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
535 $query->_qill
[$grouping][] = ts("Only Display Test Contributions");
537 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
541 case 'contribution_is_pay_later':
542 case 'contribution_pay_later':
543 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
545 $query->_qill
[$grouping][] = ts("Find Pay Later Contributions");
548 $query->_qill
[$grouping][] = ts("Exclude Pay Later Contributions");
550 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
553 case 'contribution_recurring':
555 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
556 $query->_qill
[$grouping][] = ts("Find Recurring Contributions");
557 $query->_tables
['civicrm_contribution_recur'] = $query->_whereTables
['civicrm_contribution_recur'] = 1;
560 $query->_where
[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
561 $query->_qill
[$grouping][] = ts("Exclude Recurring Contributions");
565 case 'contribution_recur_id':
566 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.contribution_recur_id",
567 $op, $value, "Integer"
569 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
572 case 'contribution_id':
573 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.id", $op, $value, "Integer");
574 $query->_qill
[$grouping][] = ts('Contribution ID %1 %2', array(1 => $op, 2 => $quoteValue));
575 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
578 case 'contribution_note':
579 $value = $strtolower(CRM_Core_DAO
::escapeString($value));
584 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_note.note)" : "civicrm_note.note";
585 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, "String");
586 $query->_qill
[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
587 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = $query->_whereTables
['contribution_note'] = 1;
590 case 'contribution_membership_id':
591 $query->_where
[$grouping][] = " civicrm_membership.id $op $value";
592 $query->_tables
['contribution_membership'] = $query->_whereTables
['contribution_membership'] = 1;
596 case 'contribution_participant_id':
597 $query->_where
[$grouping][] = " civicrm_participant.id $op $value";
598 $query->_tables
['contribution_participant'] = $query->_whereTables
['contribution_participant'] = 1;
601 case 'contribution_pcp_display_in_roll':
602 $query->_where
[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
604 $query->_qill
[$grouping][] = ts("Personal Campaign Page Honor Roll");
607 $query->_qill
[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
609 $query->_tables
['civicrm_contribution_soft'] = $query->_whereTables
['civicrm_contribution_soft'] = 1;
612 // Supporting search for currency type -- CRM-4711
614 case 'contribution_currency_type':
615 $currencySymbol = CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'));
616 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_contribution.currency",
617 $op, $currencySymbol[$value], "String"
619 $query->_qill
[$grouping][] = ts('Currency Type - %1', array(1 => $currencySymbol[$value]));
620 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
623 case 'contribution_campaign_id':
626 'campaign' => $value,
627 'grouping' => $grouping,
628 'tableName' => 'civicrm_contribution',
630 CRM_Campaign_BAO_Query
::componentSearchClause($campParams, $query);
633 case 'contribution_batch_id':
634 $batches = CRM_Contribute_PseudoConstant
::batch();
635 $query->_where
[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
636 $query->_qill
[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
637 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
638 $query->_tables
['contribution_batch'] = $query->_whereTables
['contribution_batch'] = 1;
642 //all other elements are handle in this case
643 $fldName = substr($name, 13);
644 if (!isset($fields[$fldName])) {
646 CRM_Core_Session
::setStatus(ts(
647 'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
653 $whereTable = $fields[$fldName];
654 $value = trim($value);
656 //contribution fields (decimal fields) which don't require a quote in where clause.
657 $moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
659 $dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
661 if (in_array($fldName, $dateFields)) {
664 elseif (in_array($fldName, $moneyFields)) {
668 $dataType = "String";
671 $wc = ($op != 'LIKE' && $dataType != 'Date') ?
"LOWER($whereTable[where])" : "$whereTable[where]";
672 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($wc, $op, $value, $dataType);
673 $query->_qill
[$grouping][] = "$whereTable[title] $op $quoteValue";
674 list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
675 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;
676 if ($tableName == 'civicrm_contribution_product') {
677 $query->_tables
['civicrm_product'] = $query->_whereTables
['civicrm_product'] = 1;
678 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
681 $query->_tables
['civicrm_contribution'] = $query->_whereTables
['civicrm_contribution'] = 1;
687 * @param string $name
691 * @return null|string
693 static function from($name, $mode, $side) {
696 case 'civicrm_contribution':
697 $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
698 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
699 // switch the from table if its only soft credit search
700 $from = " $side JOIN contribution_search_scredit_combined ON contribution_search_scredit_combined.contact_id = contact_a.id ";
701 $from .= " $side JOIN civicrm_contribution ON civicrm_contribution.id = contribution_search_scredit_combined.id ";
702 $from .= " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.id = contribution_search_scredit_combined.scredit_id";
706 case 'civicrm_contribution_recur':
708 // in contact mode join directly onto profile - in case no contributions exist yet
709 $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
712 $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
716 case 'civicrm_financial_type':
717 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
718 $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
721 $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
725 case 'civicrm_financial_account':
726 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
727 $from = " $side JOIN civicrm_financial_account ON contact_a.id = civicrm_financial_account.contact_id ";
731 case 'civicrm_accounting_code':
732 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
733 $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' ";
734 $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
735 $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
736 $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
740 case 'civicrm_contribution_page':
741 $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page ON civicrm_contribution.contribution_page.id";
744 case 'civicrm_product':
745 $from = " $side JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
746 $from .= " $side JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
749 case 'contribution_payment_instrument':
750 $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
751 $from .= " $side JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
752 AND option_group_payment_instrument.id = payment_instrument.option_group_id ) ";
755 case 'contribution_status':
756 $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
757 $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
758 AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
761 case 'contribution_softcredit_type':
762 $from = " $side JOIN civicrm_option_group option_group_contribution_softcredit_type ON
763 (option_group_contribution_softcredit_type.name = 'soft_credit_type')";
764 $from .= " $side JOIN civicrm_option_value contribution_softcredit_type ON
765 ( civicrm_contribution_soft.soft_credit_type_id = contribution_softcredit_type.value
766 AND option_group_contribution_softcredit_type.id = contribution_softcredit_type.option_group_id )";
769 case 'contribution_note':
770 $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
771 civicrm_contribution.id = civicrm_note.entity_id )";
774 case 'contribution_membership':
775 $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
776 $from .= " $side JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
779 case 'contribution_participant':
780 $from = " $side JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
781 $from .= " $side JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
784 case 'civicrm_contribution_soft':
785 if (!in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
786 $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
790 case 'civicrm_contribution_soft_contact':
791 if (in_array(self
::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
792 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution.contact_id = civicrm_contact_d.id )
793 AND contribution_search_scredit_combined.scredit_id IS NOT NULL";
795 $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
799 case 'civicrm_contribution_soft_email':
800 $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
803 case 'civicrm_contribution_soft_phone':
804 $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
807 case 'contribution_batch':
808 $from .= " $side JOIN civicrm_entity_financial_trxn ON (
809 civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
810 AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )";
812 $from .= " $side JOIN civicrm_financial_trxn ON (
813 civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )";
815 $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
816 AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )";
818 $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
827 static function initializeAnySoftCreditClause(&$query) {
828 if (self
::isSoftCreditOptionEnabled($query->_params
)) {
829 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
830 unset($query->_distinctComponentClause
);
831 $query->_rowCountClause
= " count(civicrm_contribution.id)";
832 $query->_groupByComponentClause
= " GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.contact_id, contribution_search_scredit_combined.scredit_id ";
838 * @param array $queryParams
842 static function isSoftCreditOptionEnabled($queryParams = array()) {
843 static $tempTableFilled = FALSE;
844 if (!empty($queryParams)) {
845 foreach (array_keys($queryParams) as $id) {
846 if (empty($queryParams[$id][0])) {
849 if ($queryParams[$id][0] == 'contribution_or_softcredits') {
850 self
::$_contribOrSoftCredit = $queryParams[$id][2];
854 if (in_array(self
::$_contribOrSoftCredit,
855 array("only_scredits", "both_related", "both"))) {
856 if (!$tempTableFilled) {
857 // build a temp table which is union of contributions and soft credits
858 // note: group-by in first part ensures uniqueness in counts
860 CREATE TEMPORARY TABLE IF NOT EXISTS contribution_search_scredit_combined AS
861 SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id
862 FROM civicrm_contribution con
863 LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id
864 GROUP BY id, contact_id, scredit_id
866 SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id
867 FROM civicrm_contribution_soft as scredit";
868 CRM_Core_DAO
::executeQuery($tempQuery);
869 $tempTableFilled = TRUE;
877 * @param bool $isExportMode
881 static function softCreditReturnProperties($isExportMode = False) {
883 'contribution_soft_credit_name' => 1,
884 'contribution_soft_credit_amount' => 1,
885 'contribution_soft_credit_type' => 1,
888 $properties['contribution_soft_credit_contribution_id'] = 1;
895 * @param bool $includeCustomFields
899 static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
901 if ($mode & CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
904 'contact_sub_type' => 1,
907 'financial_type' => 1,
908 'contribution_source' => 1,
910 'thankyou_date' => 1,
913 'accounting_code' => 1,
914 'payment_instrument' => 1,
916 'non_deductible_amount' => 1,
922 'cancel_reason' => 1,
926 'product_option' => 1,
927 'fulfilled_date' => 1,
928 'contribution_start_date' => 1,
929 'contribution_end_date' => 1,
932 'contribution_status' => 1,
933 'contribution_status_id' => 1,
934 'contribution_recur_id' => 1,
936 'contribution_note' => 1,
937 'contribution_batch' => 1,
938 'contribution_campaign_id' => 1,
940 if (self
::isSoftCreditOptionEnabled()) {
941 $properties = array_merge($properties, self
::softCreditReturnProperties());
943 if ($includeCustomFields) {
944 // also get all the custom contribution properties
945 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Contribution');
946 if (!empty($fields)) {
947 foreach ($fields as $name => $dontCare) {
948 $properties[$name] = 1;
957 * Add all the elements shared between contribute search and advnaced search
961 * @param CRM_Core_Form $form
966 static function buildSearchForm(&$form) {
968 // Added contribution source
969 $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO
::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
971 CRM_Core_Form_Date
::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
973 $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
974 $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('9.99', ' '))), 'money');
976 $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
977 $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money
::format('99.99', ' '))), 'money');
979 // Adding select option for curreny type -- CRM-4711
980 $form->add('select', 'contribution_currency_type',
983 '' => ts('- any -')) +
984 CRM_Core_PseudoConstant
::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name')),
985 FALSE, array('class' => 'crm-select2')
989 $form->addSelect('financial_type_id',
990 array('entity' => 'contribution', 'multiple' => 'multiple', 'option_url' => NULL, 'placeholder' => ts('- any -'))
993 $form->add('select', 'contribution_page_id',
994 ts('Contribution Page'),
996 '' => ts('- any -')) +
997 CRM_Contribute_PseudoConstant
::contributionPage(),
998 FALSE, array('class' => 'crm-select2')
1002 $form->add('select', 'contribution_payment_instrument_id',
1003 ts('Payment Instrument'),
1005 '' => ts('- any -')) +
1006 CRM_Contribute_PseudoConstant
::paymentInstrument(),
1007 FALSE, array('class' => 'crm-select2')
1010 $form->add('select', 'contribution_pcp_made_through_id',
1011 ts('Personal Campaign Page'),
1013 '' => ts('- any -')) +
1014 CRM_Contribute_PseudoConstant
::pcPage(),
1015 FALSE, array('class' => 'crm-select2')
1020 $statusValues = CRM_Core_OptionGroup
::values("contribution_status");
1021 // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
1022 unset($statusValues['5'], $statusValues['6']);
1024 foreach ($statusValues as $key => $val) {
1025 $status[] = $form->createElement('advcheckbox', $key, NULL, $val);
1028 $form->addGroup($status, 'contribution_status_id', ts('Contribution Status'));
1030 // Add fields for thank you and receipt
1031 $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'), TRUE);
1032 $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'), TRUE);
1034 $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'), TRUE);
1035 $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'), TRUE);
1037 // Recurring contribution fields
1038 foreach (self
::getRecurringFields() as $key => $label) {
1039 CRM_Core_Form_Date
::buildDateRange($form, $key, 1, '_low', '_high');
1040 // If data has been entered for a recurring field, tell the tpl layer to open the pane
1041 if (!empty($form->_formValues
[$key . '_relative']) ||
!empty($form->_formValues
[$key . '_low']) ||
!empty($form->_formValues
[$key . '_high'])) {
1042 $form->assign('contribution_recur_pane_open', TRUE);
1046 $form->addYesNo('contribution_test', ts('Contribution is a Test?'), TRUE);
1048 // Add field for transaction ID search
1049 $form->addElement('text', 'contribution_transaction_id', ts("Transaction ID"));
1051 $form->addElement('text', 'contribution_check_number', ts('Check Number'));
1053 // Add field for pcp display in roll search
1054 $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'), TRUE);
1056 // Soft credit related fields
1058 'only_contribs' => ts('Contributions Only'),
1059 'only_scredits' => ts('Soft Credits Only'),
1060 'both_related' => ts('Soft Credits with related Hard Credit'),
1061 'both' => ts('Both'),
1063 $form->add('select', 'contribution_or_softcredits', ts('Contributions OR Soft Credits?'), $options, FALSE, array('class' => "crm-select2"));
1065 'contribution_soft_credit_type_id',
1067 'entity' => 'contribution_soft',
1068 'field' => 'soft_credit_type_id',
1069 'multiple' => 'multiple',
1070 'option_url' => NULL,
1071 'placeholder' => ts('- any -')
1075 // Add all the custom searchable fields
1076 $contribution = array('Contribution');
1077 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $contribution);
1078 if ($groupDetails) {
1079 $form->assign('contributeGroupTree', $groupDetails);
1080 foreach ($groupDetails as $group) {
1081 foreach ($group['fields'] as $field) {
1082 $fieldId = $field['id'];
1083 $elementName = 'custom_' . $fieldId;
1084 CRM_Core_BAO_CustomField
::addQuickFormElement($form,
1093 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'contribution_campaign_id');
1096 $batches = CRM_Contribute_PseudoConstant
::batch();
1098 if ( !empty( $batches ) ) {
1099 $form->add('select', 'contribution_batch_id',
1101 array('' => ts('- any -')) +
$batches,
1102 FALSE, array('class' => 'crm-select2')
1106 $form->assign('validCiviContribute', TRUE);
1107 $form->setDefaults(array('contribution_test' => 0));
1114 static function searchAction(&$row, $id) {
1120 static function tableNames(&$tables) {
1121 // Add contribution table
1122 if (!empty($tables['civicrm_product'])) {
1123 $tables = array_merge(array('civicrm_contribution' => 1), $tables);
1126 if (!empty($tables['civicrm_contribution_product']) && empty($tables['civicrm_product'])) {
1127 $tables['civicrm_product'] = 1;
1132 * Add the where for dates
1134 * @param array $values array of query values
1135 * @param object $query the query object
1136 * @param string $name query field that is set
1137 * @param string $field name of field to be set
1138 * @param string $title title of the field
1142 static function buildDateWhere(&$values, $query, $name, $field, $title) {
1143 $fieldPart = strpos($name, $field);
1144 if($fieldPart === FALSE) {
1147 // we only have recurring dates using this ATM so lets' short cut to find the table name
1148 $table = 'contribution_recur';
1149 $fieldName = split($table . '_', $field);
1150 $query->dateQueryBuilder($values,
1151 'civicrm_' . $table, $field, $fieldName[1], $title
1159 static function getRecurringFields() {
1161 'contribution_recur_start_date' => ts('Recurring Contribution Start Date'),
1162 'contribution_recur_next_sched_contribution_date' => ts('Next Scheduled Recurring Contribution'),
1163 'contribution_recur_cancel_date' => ts('Recurring Contribution Cancel Date'),
1164 'contribution_recur_end_date' => ts('Recurring Contribution End Date'),
1165 'contribution_recur_create_date' => ('Recurring Contribution Create Date'),
1166 'contribution_recur_modified_date' => ('Recurring Contribution Modified Date'),
1167 'contribution_recur_failure_retry_date' => ts('Failed Recurring Contribution Retry Date'),