CRM/Contribute/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  * $Id$
  33  */
  34 class CRM_Contribute_BAO_Query {
  35
  36   /**
  37    * Static field for all the export/import contribution fields.
  38    *
  39    * @var array
  40    */
  41   static $_contributionFields = NULL;
  42
  43   static $_contribOrSoftCredit = "only_contribs";
  44
  45   /**
  46    * Function get the import/export fields for contribution
  47    *
  48    * @return array
  49    *   self::$_contributionFields  associative array of contribution fields
  50    */
  51   public static function &getFields() {
  52     if (!self::$_contributionFields) {
  53       self::$_contributionFields = array();
  54
  55       $fields = CRM_Contribute_BAO_Contribution::exportableFields();
  56
  57       unset($fields['contribution_contact_id']);
  58
  59       self::$_contributionFields = $fields;
  60     }
  61     return self::$_contributionFields;
  62   }
  63
  64   /**
  65    * If contributions are involved, add the specific contribute fields.
  66    *
  67    * @param $query
  68    *
  69    * @return void
  70    */
  71   public static function select(&$query) {
  72     // if contribute mode add contribution id
  73     if ($query->_mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
  74       $query->_select['contribution_id'] = "civicrm_contribution.id as contribution_id";
  75       $query->_element['contribution_id'] = 1;
  76       $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
  77     }
  78
  79     // get financial_type
  80     if (!empty($query->_returnProperties['financial_type'])) {
  81       $query->_select['financial_type'] = "civicrm_financial_type.name as financial_type";
  82       $query->_element['financial_type'] = 1;
  83       $query->_tables['civicrm_contribution'] = 1;
  84       $query->_tables['civicrm_financial_type'] = 1;
  85     }
  86
  87     // get accounting code
  88     if (!empty($query->_returnProperties['accounting_code'])) {
  89       $query->_select['accounting_code'] = "civicrm_financial_account.accounting_code as accounting_code";
  90       $query->_element['accounting_code'] = 1;
  91       $query->_tables['civicrm_accounting_code'] = 1;
  92       $query->_tables['civicrm_financial_account'] = 1;
  93     }
  94
  95     if (!empty($query->_returnProperties['contribution_note'])) {
  96       $query->_select['contribution_note'] = "civicrm_note.note as contribution_note";
  97       $query->_element['contribution_note'] = 1;
  98       $query->_tables['contribution_note'] = 1;
  99       $query->_tables['civicrm_contribution'] = 1;
 100     }
 101
 102     if (!empty($query->_returnProperties['contribution_batch'])) {
 103       $query->_select['contribution_batch'] = "civicrm_batch.title as contribution_batch";
 104       $query->_element['contribution_batch'] = 1;
 105       $query->_tables['contribution_batch'] = 1;
 106     }
 107
 108     if (!empty($query->_returnProperties['contribution_source'])) {
 109       $query->_select['contribution_source'] = "civicrm_contribution.source as contribution_source";
 110       $query->_element['contribution_source'] = 1;
 111       $query->_tables['civicrm_contribution'] = 1;
 112     }
 113
 114     // get contribution_status
 115     if (!empty($query->_returnProperties['contribution_status_id'])) {
 116       $query->_select['contribution_status_id'] = "contribution_status.value as contribution_status_id";
 117       $query->_element['contribution_status_id'] = 1;
 118       $query->_tables['civicrm_contribution'] = 1;
 119       $query->_tables['contribution_status'] = 1;
 120     }
 121
 122     // get contribution_status label
 123     if (!empty($query->_returnProperties['contribution_status'])) {
 124       $query->_select['contribution_status'] = "contribution_status.label as contribution_status";
 125       $query->_element['contribution_status'] = 1;
 126       $query->_tables['civicrm_contribution'] = 1;
 127       $query->_tables['contribution_status'] = 1;
 128     }
 129
 130     // get payment instrument
 131     if (!empty($query->_returnProperties['payment_instrument'])) {
 132       $query->_select['payment_instrument'] = "contribution_payment_instrument.label as payment_instrument";
 133       $query->_element['payment_instrument'] = 1;
 134       $query->_tables['civicrm_contribution'] = 1;
 135       $query->_tables['contribution_payment_instrument'] = 1;
 136     }
 137
 138     // get payment instrument id
 139     if (!empty($query->_returnProperties['payment_instrument_id'])) {
 140       $query->_select['instrument_id'] = "contribution_payment_instrument.value as instrument_id";
 141       $query->_select['payment_instrument_id'] = "contribution_payment_instrument.value as payment_instrument_id";
 142       $query->_element['instrument_id'] = $query->_element['payment_instrument_id'] = 1;
 143       $query->_tables['civicrm_contribution'] = 1;
 144       $query->_tables['contribution_payment_instrument'] = 1;
 145     }
 146
 147     if (!empty($query->_returnProperties['check_number'])) {
 148       $query->_select['contribution_check_number'] = "civicrm_contribution.check_number as contribution_check_number";
 149       $query->_element['contribution_check_number'] = 1;
 150       $query->_tables['civicrm_contribution'] = 1;
 151     }
 152
 153     if (!empty($query->_returnProperties['contribution_campaign_id'])) {
 154       $query->_select['contribution_campaign_id'] = 'civicrm_contribution.campaign_id as contribution_campaign_id';
 155       $query->_element['contribution_campaign_id'] = 1;
 156       $query->_tables['civicrm_contribution'] = 1;
 157     }
 158
 159     // LCD 716
 160     if (!empty($query->_returnProperties['contribution_soft_credit_name'])) {
 161       $query->_select['contribution_soft_credit_name'] = "civicrm_contact_d.sort_name as contribution_soft_credit_name";
 162       $query->_element['contribution_soft_credit_name'] = 1;
 163
 164       // also include contact id. Will help build hyperlinks
 165       $query->_select['contribution_soft_credit_contact_id'] = "civicrm_contact_d.id as contribution_soft_credit_contact_id";
 166       $query->_element['contribution_soft_credit_contact_id'] = 1;
 167
 168       $query->_tables['civicrm_contribution'] = 1;
 169       $query->_tables['civicrm_contribution_soft'] = 1;
 170       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 171     }
 172
 173     if (!empty($query->_returnProperties['contribution_soft_credit_contact_id'])) {
 174       $query->_tables['civicrm_contribution'] = 1;
 175       $query->_tables['civicrm_contribution_soft'] = 1;
 176     }
 177
 178     if (!empty($query->_returnProperties['contribution_soft_credit_amount'])) {
 179       $query->_select['contribution_soft_credit_amount'] = "civicrm_contribution_soft.amount as contribution_soft_credit_amount";
 180       $query->_element['contribution_soft_credit_amount'] = 1;
 181       $query->_tables['civicrm_contribution'] = 1;
 182       $query->_tables['civicrm_contribution_soft'] = 1;
 183     }
 184
 185     if (!empty($query->_returnProperties['contribution_soft_credit_type'])) {
 186       $query->_select['contribution_soft_credit_type'] = "contribution_softcredit_type.label as contribution_soft_credit_type";
 187       $query->_element['contribution_soft_credit_type'] = 1;
 188       $query->_tables['civicrm_contribution'] = 1;
 189       $query->_tables['contribution_softcredit_type'] = 1;
 190     }
 191
 192     if (!empty($query->_returnProperties['contribution_soft_credit_contribution_id'])) {
 193       $query->_select['contribution_soft_credit_contribution_id'] = "civicrm_contribution_soft.contribution_id as contribution_soft_credit_contribution_id";
 194       $query->_element['contribution_soft_credit_contribution_id'] = 1;
 195       $query->_tables['civicrm_contribution'] = 1;
 196       $query->_tables['civicrm_contribution_soft'] = 1;
 197     }
 198
 199     if (!empty($query->_returnProperties['contribution_soft_credit_email'])) {
 200       $query->_select['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
 201       $query->_element['contribution_soft_credit_email'] = 1;
 202       $query->_tables['civicrm_contribution'] = 1;
 203       $query->_tables['civicrm_contribution_soft'] = 1;
 204       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 205       $query->_tables['civicrm_contribution_soft_email'] = 1;
 206     }
 207
 208     if (!empty($query->_returnProperties['contribution_soft_credit_phone'])) {
 209       $query->_select['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
 210       $query->_element['contribution_soft_credit_phone'] = 1;
 211       $query->_tables['civicrm_contribution'] = 1;
 212       $query->_tables['civicrm_contribution_soft'] = 1;
 213       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 214       $query->_tables['civicrm_contribution_soft_phone'] = 1;
 215     }
 216     // LCD 716 END
 217   }
 218
 219   /**
 220    * @param $query
 221    */
 222   public static function where(&$query) {
 223     $grouping = NULL;
 224     self::initializeAnySoftCreditClause($query);
 225     foreach (array_keys($query->_params) as $id) {
 226       if (empty($query->_params[$id][0])) {
 227         continue;
 228       }
 229       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_') {
 230         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 231           $query->_useDistinct = TRUE;
 232         }
 233         // CRM-12065
 234         if (
 235           $query->_params[$id][0] == 'contribution_type_id' ||
 236           $query->_params[$id][0] == 'contribution_type'
 237         ) {
 238           CRM_Core_Session::setStatus(
 239             ts('The contribution type criteria is now obsolete, please update your smart group'),
 240             '',
 241             'alert'
 242           );
 243           continue;
 244         }
 245
 246         $grouping = $query->_params[$id][3];
 247         self::whereClauseSingle($query->_params[$id], $query);
 248       }
 249     }
 250   }
 251
 252   /**
 253    * @param $values
 254    * @param $query
 255    */
 256   public static function whereClauseSingle(&$values, &$query) {
 257     list($name, $op, $value, $grouping, $wildcard) = $values;
 258
 259     $quoteValue = NULL;
 260     $fields = array_merge(CRM_Contribute_BAO_Contribution::fields(), self::getFields());
 261
 262     if (!empty($value) && !is_array($value)) {
 263       $quoteValue = "\"$value\"";
 264     }
 265
 266     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 267     foreach (self::getRecurringFields() as $dateField => $dateFieldTitle) {
 268       if (self::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
 269         return;
 270       }
 271     }
 272     switch ($name) {
 273       case 'contribution_date':
 274       case 'contribution_date_low':
 275       case 'contribution_date_low_time':
 276       case 'contribution_date_high':
 277       case 'contribution_date_high_time':
 278         // process to / from date
 279         $query->dateQueryBuilder($values,
 280           'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
 281         );
 282         return;
 283
 284       case 'contribution_amount':
 285       case 'contribution_amount_low':
 286       case 'contribution_amount_high':
 287         // process min/max amount
 288         $query->numberRangeBuilder($values,
 289           'civicrm_contribution', 'contribution_amount',
 290           'total_amount', 'Contribution Amount',
 291           NULL
 292         );
 293         return;
 294
 295       case 'contribution_thankyou_date_is_not_null':
 296         if ($value) {
 297           $op = "IS NOT NULL";
 298           $query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
 299         }
 300         else {
 301           $op = "IS NULL";
 302           $query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
 303         }
 304         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
 305         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 306         return;
 307
 308       case 'contribution_receipt_date_is_not_null':
 309         if ($value) {
 310           $op = "IS NOT NULL";
 311           $query->_qill[$grouping][] = ts('Contribution Receipt Sent');
 312         }
 313         else {
 314           $op = "IS NULL";
 315           $query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
 316         }
 317         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
 318         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 319         return;
 320
 321       case 'financial_type':
 322       case 'contribution_page':
 323       case 'payment_instrument':
 324       case 'contribution_payment_instrument':
 325       case 'contribution_status':
 326         $name .= '_id';
 327       case 'financial_type_id':
 328       case 'payment_instrument_id':
 329       case 'contribution_payment_instrument_id':
 330       case 'contribution_page_id':
 331       case 'contribution_status_id':
 332       case 'contribution_id':
 333       case 'contribution_currency_type':
 334       case 'contribution_currency':
 335       case 'contribution_source':
 336       case 'contribution_trxn_id':
 337       case 'contribution_check_number':
 338       case 'contribution_contact_id':
 339       case (strpos($name, '_amount') !== FALSE):
 340       case (strpos($name, '_date') !== FALSE):
 341         $qillName = $name;
 342         $pseudoExtraParam = NULL;
 343         if ((strpos($name, '_amount') !== FALSE) || (strpos($name, '_date') !== FALSE) || in_array($name,
 344             array(
 345               'contribution_id',
 346               'contribution_currency',
 347               'contribution_source',
 348               'contribution_trxn_id',
 349               'contribution_check_number',
 350               'contribution_payment_instrument_id',
 351               'contribution_contact_id',
 352             )
 353           )
 354         ) {
 355           $name = str_replace('contribution_', '', $name);
 356           if (!in_array($name, array('source', 'id', 'contact_id'))) {
 357             $qillName = str_replace('contribution_', '', $qillName);
 358           }
 359         }
 360         if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
 361           $qillName = $name = 'currency';
 362           $pseudoExtraParam = array('labelColumn' => 'name');
 363         }
 364
 365         $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
 366
 367         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.$name", $op, $value, $dataType);
 368         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
 369         $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
 370         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 371         return;
 372
 373       case 'contribution_pcp_made_through_id':
 374       case 'contribution_soft_credit_type_id':
 375         $qillName = $name;
 376         if ($name == 'contribution_pcp_made_through_id') {
 377           $qillName = $name = 'pcp_id';
 378           $fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
 379         }
 380         if ($name == 'contribution_soft_credit_type_id') {
 381           $qillName = str_replace('_id', '', $qillName);
 382           $fields[$qillName]['type'] = $fields[$qillName]['data_type'];
 383           $name = str_replace('contribution_', '', $name);
 384         }
 385         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution_soft.$name",
 386           $op, $value, CRM_Utils_Type::typeToString($fields[$qillName]['type'])
 387         );
 388         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
 389         $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
 390         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 391         return;
 392
 393       case 'contribution_or_softcredits':
 394         if ($value == 'only_scredits') {
 395           $query->_where[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
 396           $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
 397           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 398           $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 399         }
 400         elseif ($value == 'both_related') {
 401           $query->_where[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
 402           $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
 403           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 404           $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 405         }
 406         elseif ($value == 'both') {
 407           $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Both');
 408           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 409           $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 410         }
 411         // default option: $value == 'only_contribs'
 412         return;
 413
 414       case 'contribution_is_test':
 415         // By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
 416         // so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
 417         $key = array_search('civicrm_contribution.is_test = 0', $query->_where[$grouping]);
 418         if (!empty($key)) {
 419           unset($query->_where[$grouping][$key]);
 420         }
 421       case 'contribution_test':
 422         // We dont want to include all tests for sql OR CRM-7827
 423         if (!$value || $query->getOperator() != 'OR') {
 424           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
 425           if ($value) {
 426             $query->_qill[$grouping][] = ts("Only Display Test Contributions");
 427           }
 428           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 429         }
 430         return;
 431
 432       case 'contribution_is_pay_later':
 433       case 'contribution_pay_later':
 434         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
 435         if ($value) {
 436           $query->_qill[$grouping][] = ts("Find Pay Later Contributions");
 437         }
 438         else {
 439           $query->_qill[$grouping][] = ts("Exclude Pay Later Contributions");
 440         }
 441         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 442         return;
 443
 444       case 'contribution_recurring':
 445         if ($value) {
 446           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
 447           $query->_qill[$grouping][] = ts("Find Recurring Contributions");
 448           $query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
 449         }
 450         else {
 451           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
 452           $query->_qill[$grouping][] = ts("Exclude Recurring Contributions");
 453         }
 454         return;
 455
 456       case 'contribution_recur_id':
 457         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id",
 458           $op, $value, "Integer"
 459         );
 460         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 461         return;
 462
 463       case 'contribution_note':
 464         $value = $strtolower(CRM_Core_DAO::escapeString($value));
 465         if ($wildcard) {
 466           $value = "%$value%";
 467           $op = 'LIKE';
 468         }
 469         $wc = ($op != 'LIKE') ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
 470         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 471         $query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
 472         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
 473         return;
 474
 475       case 'contribution_membership_id':
 476         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 477         $query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
 478
 479         return;
 480
 481       case 'contribution_participant_id':
 482         $query->_where[$grouping][] = " civicrm_participant.id $op $value";
 483         $query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
 484         return;
 485
 486       case 'contribution_pcp_display_in_roll':
 487         $query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
 488         if ($value) {
 489           $query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll");
 490         }
 491         else {
 492           $query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
 493         }
 494         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 495         return;
 496
 497       case 'contribution_campaign_id':
 498         $campParams = array(
 499           'op' => $op,
 500           'campaign' => $value,
 501           'grouping' => $grouping,
 502           'tableName' => 'civicrm_contribution',
 503         );
 504         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 505         return;
 506
 507       case 'contribution_batch_id':
 508         $batches = CRM_Contribute_PseudoConstant::batch();
 509         $query->_where[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
 510         $query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
 511         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 512         $query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1;
 513         return;
 514
 515       default:
 516         //all other elements are handle in this case
 517         $fldName = substr($name, 13);
 518         if (!isset($fields[$fldName])) {
 519           // CRM-12597
 520           CRM_Core_Session::setStatus(ts(
 521               'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
 522               array(1 => $fldName)
 523             )
 524           );
 525           return;
 526         }
 527         $whereTable = $fields[$fldName];
 528         $value = trim($value);
 529
 530         $dataType = "String";
 531         if (!empty($whereTable['type'])) {
 532           $dataType = CRM_Utils_Type::typeToString($whereTable['type']);
 533         }
 534
 535         $wc = ($op != 'LIKE' && $dataType != 'Date') ? "LOWER($whereTable[where])" : "$whereTable[where]";
 536         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
 537         $query->_qill[$grouping][] = "$whereTable[title] $op $quoteValue";
 538         list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
 539         $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 540         if ($tableName == 'civicrm_contribution_product') {
 541           $query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
 542           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 543         }
 544         else {
 545           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 546         }
 547     }
 548   }
 549
 550   /**
 551    * @param string $name
 552    * @param $mode
 553    * @param $side
 554    *
 555    * @return NULL|string
 556    */
 557   public static function from($name, $mode, $side) {
 558     $from = NULL;
 559     switch ($name) {
 560       case 'civicrm_contribution':
 561         $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
 562         if (in_array(self::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
 563           // switch the from table if its only soft credit search
 564           $from = " $side JOIN contribution_search_scredit_combined ON contribution_search_scredit_combined.contact_id = contact_a.id ";
 565           $from .= " $side JOIN civicrm_contribution ON civicrm_contribution.id = contribution_search_scredit_combined.id ";
 566           $from .= " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.id = contribution_search_scredit_combined.scredit_id";
 567         }
 568         break;
 569
 570       case 'civicrm_contribution_recur':
 571         if ($mode == 1) {
 572           // in contact mode join directly onto profile - in case no contributions exist yet
 573           $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
 574         }
 575         else {
 576           $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
 577         }
 578         break;
 579
 580       case 'civicrm_financial_type':
 581         if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 582           $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 583         }
 584         else {
 585           $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 586         }
 587         break;
 588
 589       case 'civicrm_financial_account':
 590         if ($mode & CRM_Contact_BAO_Query::MODE_CONTACTS) {
 591           $from = " $side JOIN civicrm_financial_account ON contact_a.id = civicrm_financial_account.contact_id ";
 592         }
 593         break;
 594
 595       case 'civicrm_accounting_code':
 596         if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 597           $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' ";
 598           $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
 599           $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
 600           $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
 601         }
 602         break;
 603
 604       case 'civicrm_contribution_page':
 605         $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page_id = civicrm_contribution_page.id";
 606         break;
 607
 608       case 'civicrm_product':
 609         $from = " $side  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
 610         $from .= " $side  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
 611         break;
 612
 613       case 'contribution_payment_instrument':
 614         $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
 615         $from .= " $side JOIN civicrm_option_value contribution_payment_instrument ON (civicrm_contribution.payment_instrument_id = contribution_payment_instrument.value
 616                                AND option_group_payment_instrument.id = contribution_payment_instrument.option_group_id ) ";
 617         break;
 618
 619       case 'contribution_status':
 620         $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
 621         $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
 622                                AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
 623         break;
 624
 625       case 'contribution_softcredit_type':
 626         $from = " $side JOIN civicrm_option_group option_group_contribution_softcredit_type ON
 627           (option_group_contribution_softcredit_type.name = 'soft_credit_type')";
 628         $from .= " $side JOIN civicrm_option_value contribution_softcredit_type ON
 629           ( civicrm_contribution_soft.soft_credit_type_id = contribution_softcredit_type.value
 630           AND option_group_contribution_softcredit_type.id = contribution_softcredit_type.option_group_id )";
 631         break;
 632
 633       case 'contribution_note':
 634         $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
 635                                                     civicrm_contribution.id = civicrm_note.entity_id )";
 636         break;
 637
 638       case 'contribution_membership':
 639         $from = " $side  JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
 640         $from .= " $side  JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 641         break;
 642
 643       case 'contribution_participant':
 644         $from = " $side  JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
 645         $from .= " $side  JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
 646         break;
 647
 648       case 'civicrm_contribution_soft':
 649         if (!in_array(self::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
 650           $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
 651         }
 652         break;
 653
 654       case 'civicrm_contribution_soft_contact':
 655         if (in_array(self::$_contribOrSoftCredit, array("only_scredits", "both_related", "both"))) {
 656           $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution.contact_id = civicrm_contact_d.id )
 657             AND contribution_search_scredit_combined.scredit_id IS NOT NULL";
 658         }
 659         else {
 660           $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
 661         }
 662         break;
 663
 664       case 'civicrm_contribution_soft_email':
 665         $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
 666         break;
 667
 668       case 'civicrm_contribution_soft_phone':
 669         $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
 670         break;
 671
 672       case 'contribution_batch':
 673         $from .= " $side JOIN civicrm_entity_financial_trxn ON (
 674         civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
 675         AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )";
 676
 677         $from .= " $side JOIN civicrm_financial_trxn ON (
 678         civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )";
 679
 680         $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
 681         AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )";
 682
 683         $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
 684         break;
 685     }
 686     return $from;
 687   }
 688
 689   /**
 690    * @param $query
 691    */
 692   public static function initializeAnySoftCreditClause(&$query) {
 693     if (self::isSoftCreditOptionEnabled($query->_params)) {
 694       if ($query->_mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 695         unset($query->_distinctComponentClause);
 696         $query->_rowCountClause = " count(civicrm_contribution.id)";
 697         $query->_groupByComponentClause = " GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.contact_id, contribution_search_scredit_combined.scredit_id ";
 698       }
 699     }
 700   }
 701
 702   /**
 703    * @param array $queryParams
 704    *
 705    * @return bool
 706    */
 707   public static function isSoftCreditOptionEnabled($queryParams = array()) {
 708     static $tempTableFilled = FALSE;
 709     if (!empty($queryParams)) {
 710       foreach (array_keys($queryParams) as $id) {
 711         if (empty($queryParams[$id][0])) {
 712           continue;
 713         }
 714         if ($queryParams[$id][0] == 'contribution_or_softcredits') {
 715           self::$_contribOrSoftCredit = $queryParams[$id][2];
 716         }
 717       }
 718     }
 719     if (in_array(self::$_contribOrSoftCredit,
 720       array("only_scredits", "both_related", "both"))) {
 721       if (!$tempTableFilled) {
 722         // build a temp table which is union of contributions and soft credits
 723         // note: group-by in first part ensures uniqueness in counts
 724         $tempQuery = "
 725             CREATE TEMPORARY TABLE IF NOT EXISTS contribution_search_scredit_combined AS
 726                SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id
 727                  FROM civicrm_contribution con
 728             LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id
 729              GROUP BY id, contact_id, scredit_id
 730             UNION ALL
 731                SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id
 732                  FROM civicrm_contribution_soft as scredit";
 733         CRM_Core_DAO::executeQuery($tempQuery);
 734         $tempTableFilled = TRUE;
 735       }
 736       return TRUE;
 737     }
 738     return FALSE;
 739   }
 740
 741   /**
 742    * @param bool $isExportMode
 743    *
 744    * @return array
 745    */
 746   public static function softCreditReturnProperties($isExportMode = FALSE) {
 747     $properties = array(
 748       'contribution_soft_credit_name' => 1,
 749       'contribution_soft_credit_amount' => 1,
 750       'contribution_soft_credit_type' => 1,
 751     );
 752     if ($isExportMode) {
 753       $properties['contribution_soft_credit_contribution_id'] = 1;
 754     }
 755     return $properties;
 756   }
 757
 758   /**
 759    * @param $mode
 760    * @param bool $includeCustomFields
 761    *
 762    * @return array|NULL
 763    */
 764   public static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
 765     $properties = NULL;
 766     if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 767       $properties = array(
 768         'contact_type' => 1,
 769         'contact_sub_type' => 1,
 770         'sort_name' => 1,
 771         'display_name' => 1,
 772         'financial_type' => 1,
 773         'contribution_source' => 1,
 774         'receive_date' => 1,
 775         'thankyou_date' => 1,
 776         'cancel_date' => 1,
 777         'total_amount' => 1,
 778         'accounting_code' => 1,
 779         'payment_instrument' => 1,
 780         'payment_instrument_id' => 1,
 781         'check_number' => 1,
 782         'non_deductible_amount' => 1,
 783         'fee_amount' => 1,
 784         'net_amount' => 1,
 785         'trxn_id' => 1,
 786         'invoice_id' => 1,
 787         'currency' => 1,
 788         'cancel_reason' => 1,
 789         'receipt_date' => 1,
 790         'product_name' => 1,
 791         'sku' => 1,
 792         'product_option' => 1,
 793         'fulfilled_date' => 1,
 794         'contribution_start_date' => 1,
 795         'contribution_end_date' => 1,
 796         'is_test' => 1,
 797         'is_pay_later' => 1,
 798         'contribution_status' => 1,
 799         'contribution_status_id' => 1,
 800         'contribution_recur_id' => 1,
 801         'amount_level' => 1,
 802         'contribution_note' => 1,
 803         'contribution_batch' => 1,
 804         'contribution_campaign_id' => 1,
 805       );
 806       if (self::isSoftCreditOptionEnabled()) {
 807         $properties = array_merge($properties, self::softCreditReturnProperties());
 808       }
 809       if ($includeCustomFields) {
 810         // also get all the custom contribution properties
 811         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Contribution');
 812         if (!empty($fields)) {
 813           foreach ($fields as $name => $dontCare) {
 814             $properties[$name] = 1;
 815           }
 816         }
 817       }
 818     }
 819     return $properties;
 820   }
 821
 822   /**
 823    * Add all the elements shared between contribute search and advnaced search.
 824    *
 825    *
 826    * @param CRM_Core_Form $form
 827    *
 828    * @return void
 829    */
 830   public static function buildSearchForm(&$form) {
 831
 832     // Added contribution source
 833     $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
 834
 835     CRM_Core_Form_Date::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
 836
 837     $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 838     $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 839
 840     $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 841     $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 842
 843     // Adding select option for curreny type -- CRM-4711
 844     $form->add('select', 'contribution_currency_type',
 845       ts('Currency Type'),
 846       array(
 847         '' => ts('- any -'),
 848       ) +
 849       CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name')),
 850       FALSE, array('class' => 'crm-select2')
 851     );
 852
 853     // CRM-13848
 854     $form->addSelect('financial_type_id',
 855       array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search')
 856     );
 857
 858     $form->add('select', 'contribution_page_id',
 859       ts('Contribution Page'),
 860       array(
 861         '' => ts('- any -'),
 862       ) +
 863       CRM_Contribute_PseudoConstant::contributionPage(),
 864       FALSE, array('class' => 'crm-select2')
 865     );
 866
 867     $form->addSelect('payment_instrument_id',
 868       array('entity' => 'contribution', 'label' => ts('Payment Method'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
 869     );
 870
 871     $form->add('select', 'contribution_pcp_made_through_id',
 872       ts('Personal Campaign Page'),
 873       array(
 874         '' => ts('- any -'),
 875       ) +
 876       CRM_Contribute_PseudoConstant::pcPage(),
 877       FALSE, array('class' => 'crm-select2')
 878     );
 879
 880     $status = array();
 881
 882     $statusValues = CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'contribution_status');
 883     // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
 884     unset($statusValues['5'], $statusValues['6']);
 885
 886     $form->addSelect('contribution_status_id',
 887       array('entity' => 'contribution', 'multiple' => 'multiple', 'label' => ts('Contribution Status(s)'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
 888     );
 889
 890     // Add fields for thank you and receipt
 891     $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'), TRUE);
 892     $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'), TRUE);
 893
 894     $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'), TRUE);
 895     $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'), TRUE);
 896
 897     // Recurring contribution fields
 898     foreach (self::getRecurringFields() as $key => $label) {
 899       CRM_Core_Form_Date::buildDateRange($form, $key, 1, '_low', '_high');
 900       // If data has been entered for a recurring field, tell the tpl layer to open the pane
 901       if (!empty($form->_formValues[$key . '_relative']) || !empty($form->_formValues[$key . '_low']) || !empty($form->_formValues[$key . '_high'])) {
 902         $form->assign('contribution_recur_pane_open', TRUE);
 903       }
 904     }
 905
 906     $form->addYesNo('contribution_test', ts('Contribution is a Test?'), TRUE);
 907
 908     // Add field for transaction ID search
 909     $form->addElement('text', 'contribution_trxn_id', ts("Transaction ID"));
 910
 911     $form->addElement('text', 'contribution_check_number', ts('Check Number'));
 912
 913     // Add field for pcp display in roll search
 914     $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'), TRUE);
 915
 916     // Soft credit related fields
 917     $options = array(
 918       'only_contribs' => ts('Contributions Only'),
 919       'only_scredits' => ts('Soft Credits Only'),
 920       'both_related' => ts('Soft Credits with related Hard Credit'),
 921       'both' => ts('Both'),
 922     );
 923     $form->add('select', 'contribution_or_softcredits', ts('Contributions OR Soft Credits?'), $options, FALSE, array('class' => "crm-select2"));
 924     $form->addSelect(
 925       'contribution_soft_credit_type_id',
 926       array(
 927         'entity' => 'contribution_soft',
 928         'field' => 'soft_credit_type_id',
 929         'multiple' => TRUE,
 930         'context' => 'search',
 931       )
 932     );
 933
 934     // Add all the custom searchable fields
 935     $contribution = array('Contribution');
 936     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $contribution);
 937     if ($groupDetails) {
 938       $form->assign('contributeGroupTree', $groupDetails);
 939       foreach ($groupDetails as $group) {
 940         foreach ($group['fields'] as $field) {
 941           $fieldId = $field['id'];
 942           $elementName = 'custom_' . $fieldId;
 943           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 944             $elementName,
 945             $fieldId,
 946             FALSE, FALSE, TRUE
 947           );
 948         }
 949       }
 950     }
 951
 952     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'contribution_campaign_id');
 953
 954     // Add batch select
 955     $batches = CRM_Contribute_PseudoConstant::batch();
 956
 957     if (!empty($batches)) {
 958       $form->add('select', 'contribution_batch_id',
 959         ts('Batch Name'),
 960         array('' => ts('- any -')) + $batches,
 961         FALSE, array('class' => 'crm-select2')
 962       );
 963     }
 964
 965     $form->assign('validCiviContribute', TRUE);
 966     $form->setDefaults(array('contribution_test' => 0));
 967   }
 968
 969   /**
 970    * @param $row
 971    * @param int $id
 972    */
 973   public static function searchAction(&$row, $id) {
 974   }
 975
 976   /**
 977    * @param $tables
 978    */
 979   public static function tableNames(&$tables) {
 980     // Add contribution table
 981     if (!empty($tables['civicrm_product'])) {
 982       $tables = array_merge(array('civicrm_contribution' => 1), $tables);
 983     }
 984
 985     if (!empty($tables['civicrm_contribution_product']) && empty($tables['civicrm_product'])) {
 986       $tables['civicrm_product'] = 1;
 987     }
 988   }
 989
 990   /**
 991    * Add the where for dates.
 992    *
 993    * @param array $values
 994    *   Array of query values.
 995    * @param object $query
 996    *   The query object.
 997    * @param string $name
 998    *   Query field that is set.
 999    * @param string $field
1000    *   Name of field to be set.
1001    * @param string $title
1002    *   Title of the field.
1003    *
1004    * @return bool
1005    */
1006   public static function buildDateWhere(&$values, $query, $name, $field, $title) {
1007     $fieldPart = strpos($name, $field);
1008     if ($fieldPart === FALSE) {
1009       return NULL;
1010     }
1011     // we only have recurring dates using this ATM so lets' short cut to find the table name
1012     $table = 'contribution_recur';
1013     $fieldName = explode($table . '_', $field);
1014     $query->dateQueryBuilder($values,
1015       'civicrm_' . $table, $field, $fieldName[1], $title
1016     );
1017     return TRUE;
1018   }
1019
1020   /**
1021    * @return array
1022    */
1023   public static function getRecurringFields() {
1024     return array(
1025       'contribution_recur_start_date' => ts('Recurring Contribution Start Date'),
1026       'contribution_recur_next_sched_contribution_date' => ts('Next Scheduled Recurring Contribution'),
1027       'contribution_recur_cancel_date' => ts('Recurring Contribution Cancel Date'),
1028       'contribution_recur_end_date' => ts('Recurring Contribution End Date'),
1029       'contribution_recur_create_date' => ('Recurring Contribution Create Date'),
1030       'contribution_recur_modified_date' => ('Recurring Contribution Modified Date'),
1031       'contribution_recur_failure_retry_date' => ts('Failed Recurring Contribution Retry Date'),
1032     );
1033   }
1034
1035 }