CRM/Contribute/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contribute_BAO_Query {
  36
  37   /**
  38    * static field for all the export/import contribution fields
  39    *
  40    * @var array
  41    * @static
  42    */
  43   static $_contributionFields = NULL;
  44
  45   /**
  46    * Function get the import/export fields for contribution
  47    *
  48    * @return array self::$_contributionFields  associative array of contribution fields
  49    * @static
  50    */
  51   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    * @return void
  68    * @access public
  69    */
  70   static function select(&$query) {
  71     // if contribute mode add contribution id
  72     if ($query->_mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
  73       $query->_select['contribution_id'] = "civicrm_contribution.id as contribution_id";
  74       $query->_element['contribution_id'] = 1;
  75       $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
  76     }
  77
  78     // get financial_type
  79     if (CRM_Utils_Array::value('financial_type', $query->_returnProperties)) {
  80       $query->_select['financial_type']  = "civicrm_financial_type.name as financial_type";
  81       $query->_element['financial_type'] = 1;
  82       $query->_tables['civicrm_contribution'] = 1;
  83       $query->_tables['civicrm_financial_type'] = 1;
  84     }
  85
  86     // get accounting code
  87     if (CRM_Utils_Array::value( 'accounting_code', $query->_returnProperties)) {
  88       $query->_select['accounting_code']  = "civicrm_financial_account.accounting_code as accounting_code";
  89       $query->_element['accounting_code'] = 1;
  90       $query->_tables['civicrm_accounting_code'] = 1;
  91       $query->_tables['civicrm_financial_account'] = 1;
  92     }
  93
  94     if (CRM_Utils_Array::value('contribution_note', $query->_returnProperties)) {
  95       $query->_select['contribution_note'] = "civicrm_note.note as contribution_note";
  96       $query->_element['contribution_note'] = 1;
  97       $query->_tables['contribution_note'] = 1;
  98     }
  99
 100     if (CRM_Utils_Array::value('contribution_batch', $query->_returnProperties)) {
 101       $query->_select['contribution_batch'] = "civicrm_batch.title as contribution_batch";
 102       $query->_element['contribution_batch'] = 1;
 103       $query->_tables['contribution_batch'] = 1;
 104     }
 105
 106     // get contribution_status
 107     if (CRM_Utils_Array::value('contribution_status_id', $query->_returnProperties)) {
 108       $query->_select['contribution_status_id'] = "contribution_status.value as contribution_status_id";
 109       $query->_element['contribution_status_id'] = 1;
 110       $query->_tables['civicrm_contribution'] = 1;
 111       $query->_tables['contribution_status'] = 1;
 112     }
 113
 114     // get contribution_status label
 115     if (CRM_Utils_Array::value('contribution_status', $query->_returnProperties)) {
 116       $query->_select['contribution_status'] = "contribution_status.label as contribution_status";
 117       $query->_element['contribution_status'] = 1;
 118       $query->_tables['civicrm_contribution'] = 1;
 119       $query->_tables['contribution_status'] = 1;
 120     }
 121
 122     // get payment instruments
 123     if (CRM_Utils_Array::value('payment_instrument', $query->_returnProperties)) {
 124       $query->_select['contribution_payment_instrument'] = "payment_instrument.name as contribution_payment_instrument";
 125       $query->_element['contribution_payment_instrument'] = 1;
 126       $query->_tables['civicrm_contribution'] = 1;
 127       $query->_tables['contribution_payment_instrument'] = 1;
 128     }
 129
 130     // get honor contact name
 131     if (CRM_Utils_Array::value('honor_contact_name', $query->_returnProperties)) {
 132       $query->_select['contribution_honor_contact_name'] = "civicrm_contact_c.display_name as contribution_honor_contact_name";
 133       $query->_element['contribution_honor_contact_name'] = 1;
 134       $query->_tables['civicrm_contribution'] = 1;
 135       $query->_tables['contribution_honor_contact_name'] = 1;
 136     }
 137
 138     // get honor type label
 139     if (CRM_Utils_Array::value('honor_type_label', $query->_returnProperties)) {
 140       $query->_select['contribution_honor_type_label'] = "honor_type.label as contribution_honor_type_label";
 141       $query->_element['contribution_honor_type_label'] = 1;
 142       $query->_tables['civicrm_contribution'] = 1;
 143       $query->_tables['contribution_honor_type_label'] = 1;
 144     }
 145
 146     // get honor contact email
 147     if (CRM_Utils_Array::value('honor_contact_email', $query->_returnProperties)) {
 148       $query->_select['contribution_honor_contact_email'] = "honor_email.email as contribution_honor_contact_email";
 149       $query->_element['contribution_honor_contact_email'] = 1;
 150       $query->_tables['civicrm_contribution'] = 1;
 151       $query->_tables['contribution_honor_contact_email'] = 1;
 152     }
 153
 154     // get honor contact id
 155     if (CRM_Utils_Array::value('honor_contact_id', $query->_returnProperties)) {
 156       $query->_select['contribution_honor_contact_id'] = "civicrm_contribution.honor_contact_id as contribution_honor_contact_id";
 157       $query->_element['contribution_honor_contact_id'] = 1;
 158       $query->_tables['civicrm_contribution'] = 1;
 159     }
 160
 161
 162     if (CRM_Utils_Array::value('check_number', $query->_returnProperties)) {
 163       $query->_select['contribution_check_number'] = "civicrm_contribution.check_number as contribution_check_number";
 164       $query->_element['contribution_check_number'] = 1;
 165       $query->_tables['civicrm_contribution'] = 1;
 166     }
 167
 168     if (CRM_Utils_Array::value('contribution_campaign_id', $query->_returnProperties)) {
 169       $query->_select['contribution_campaign_id'] = 'civicrm_contribution.campaign_id as contribution_campaign_id';
 170       $query->_element['contribution_campaign_id'] = 1;
 171       $query->_tables['civicrm_contribution'] = 1;
 172     }
 173
 174     // LCD 716
 175     if (CRM_Utils_Array::value('soft_credit_name', $query->_returnProperties)) {
 176       $query->_select['contribution_soft_credit_name'] = "civicrm_contact_d.display_name as contribution_soft_credit_name";
 177       $query->_element['contribution_soft_credit_name'] = 1;
 178       $query->_tables['civicrm_contribution'] = 1;
 179       $query->_tables['civicrm_contribution_soft'] = 1;
 180       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 181     }
 182
 183     if (CRM_Utils_Array::value('soft_credit_email', $query->_returnProperties)) {
 184       $query->_select['contribution_soft_credit_email'] = "soft_email.email as contribution_soft_credit_email";
 185       $query->_element['contribution_soft_credit_email'] = 1;
 186       $query->_tables['civicrm_contribution'] = 1;
 187       $query->_tables['civicrm_contribution_soft'] = 1;
 188       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 189       $query->_tables['civicrm_contribution_soft_email'] = 1;
 190     }
 191
 192     if (CRM_Utils_Array::value('soft_credit_phone', $query->_returnProperties)) {
 193       $query->_select['contribution_soft_credit_email'] = "soft_phone.phone as contribution_soft_credit_phone";
 194       $query->_element['contribution_soft_credit_phone'] = 1;
 195       $query->_tables['civicrm_contribution'] = 1;
 196       $query->_tables['civicrm_contribution_soft'] = 1;
 197       $query->_tables['civicrm_contribution_soft_contact'] = 1;
 198       $query->_tables['civicrm_contribution_soft_phone'] = 1;
 199     }
 200     // LCD 716 END
 201   }
 202
 203   static function where(&$query) {
 204     $grouping = NULL;
 205     foreach (array_keys($query->_params) as $id) {
 206       if (!CRM_Utils_Array::value(0, $query->_params[$id])) {
 207         continue;
 208       }
 209       if (substr($query->_params[$id][0], 0, 13) == 'contribution_' || substr($query->_params[$id][0], 0, 10) == 'financial_') {
 210         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 211           $query->_useDistinct = TRUE;
 212         }
 213         // CRM-12065
 214         if (
 215           $query->_params[$id][0] == 'contribution_type_id' ||
 216           $query->_params[$id][0] == 'contribution_type'
 217         ) {
 218           CRM_Core_Session::setStatus(
 219             ts('The contribution type criteria is now obsolete, please update your smart group'),
 220             '',
 221             'alert'
 222           );
 223           continue;
 224         }
 225
 226         $grouping = $query->_params[$id][3];
 227         self::whereClauseSingle($query->_params[$id], $query);
 228       }
 229     }
 230   }
 231
 232   static function whereClauseSingle(&$values, &$query) {
 233     list($name, $op, $value, $grouping, $wildcard) = $values;
 234
 235     $fields = self::getFields();
 236
 237     if (!empty($value) && !is_array($value)) {
 238       $quoteValue = "\"$value\"";
 239     }
 240
 241     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 242     $datesToBuild = array(
 243       'contribution_recur_start_date' => 'Recurring Contribution Start Date',
 244       'contribution_recur_next_sched_contribution_date' => 'Next Scheduled Recurring Payment',
 245       'contribution_recur_cancel_date' => 'Recurring Contribution Cancel Date',
 246       'contribution_recur_end_date' => 'Recurring Contribution End Date',
 247       'contribution_recur_create_date' => 'Recurring Contribution Create Date',
 248       'contribution_recur_modified_date' => 'Recurring Contribution Modified Date',
 249       'contribution_recur_failure_retry_date' => 'Recurring Contribution Failure Retry Date',
 250     );
 251     foreach ($datesToBuild as $dateField => $dateFieldTitle) {
 252       if(self::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
 253         return;
 254       }
 255     }
 256     switch ($name) {
 257       case 'contribution_date':
 258       case 'contribution_date_low':
 259       case 'contribution_date_low_time':
 260       case 'contribution_date_high':
 261       case 'contribution_date_high_time':
 262         // process to / from date
 263         $query->dateQueryBuilder($values,
 264           'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
 265         );
 266         return;
 267
 268       case 'contribution_amount':
 269       case 'contribution_amount_low':
 270       case 'contribution_amount_high':
 271         // process min/max amount
 272         $query->numberRangeBuilder($values,
 273           'civicrm_contribution', 'contribution_amount',
 274           'total_amount', 'Contribution Amount',
 275           NULL
 276         );
 277         return;
 278
 279       case 'contribution_total_amount':
 280         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.total_amount",
 281           $op, $value, "Money"
 282         );
 283         $query->_qill[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
 284         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 285         return;
 286
 287       case 'contribution_thankyou_date_is_not_null':
 288         if ($value) {
 289           $op = "IS NOT NULL";
 290           $query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
 291         }
 292         else {
 293           $op = "IS NULL";
 294           $query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
 295         }
 296         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
 297         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 298         return;
 299
 300       case 'contribution_receipt_date_is_not_null':
 301         if ($value) {
 302           $op = "IS NOT NULL";
 303           $query->_qill[$grouping][] = ts('Contribution Receipt Sent');
 304         }
 305         else {
 306           $op = "IS NULL";
 307           $query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
 308         }
 309         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
 310         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 311         return;
 312
 313         case 'financial_type_id':
 314         case 'financial_type':
 315         $cType = $value;
 316         $types = CRM_Contribute_PseudoConstant::financialType();
 317         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.financial_type_id",
 318           $op, $value, "Integer"
 319         );
 320         $query->_qill[$grouping ][] = ts('Financial Type - %1', array(1 => $types[$cType]));
 321         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 322         return;
 323
 324       case 'contribution_page_id':
 325         $cPage = $value;
 326         $pages = CRM_Contribute_PseudoConstant::contributionPage();
 327         $query->_where[$grouping][] = "civicrm_contribution.contribution_page_id = $cPage";
 328         $query->_qill[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
 329         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 330         return;
 331
 332       case 'contribution_pcp_made_through_id':
 333         $pcPage = $value;
 334         $pcpages = CRM_Contribute_PseudoConstant::pcPage();
 335         $query->_where[$grouping][] = "civicrm_contribution_soft.pcp_id = $pcPage";
 336         $query->_qill[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
 337         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 338         return;
 339
 340       case 'contribution_payment_instrument_id':
 341       case 'contribution_payment_instrument':
 342         $pi = $value;
 343         $pis = CRM_Contribute_PseudoConstant::paymentInstrument();
 344         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.payment_instrument_id",
 345           $op, $value, "Integer"
 346         );
 347
 348         $query->_qill[$grouping][] = ts('Paid By - %1', array(1 => $pis[$pi]));
 349         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 350         return;
 351
 352       case 'contribution_in_honor_of':
 353         $name    = trim($value);
 354         $newName = str_replace(',', " ", $name);
 355         $pieces  = explode(' ', $newName);
 356         foreach ($pieces as $piece) {
 357           $value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
 358           $value = "'%$value%'";
 359           $sub[] = " ( contact_b.sort_name LIKE $value )";
 360         }
 361
 362         $query->_where[$grouping][] = ' ( ' . implode('  OR ', $sub) . ' ) ';
 363         $query->_qill[$grouping][] = ts('Honor name like - \'%1\'', array(1 => $name));
 364         $query->_tables['civicrm_contact_b'] = $query->_whereTables['civicrm_contact_b'] = 1;
 365         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 366         return;
 367
 368       case 'contribution_status':
 369       case 'contribution_status_id':
 370         if (is_array($value)) {
 371           foreach ($value as $k => $v) {
 372             if ($v) {
 373               $val[$k] = $k;
 374             }
 375           }
 376
 377           $status = implode(',', $val);
 378
 379           if (count($val) > 1) {
 380             $op = 'IN';
 381             $status = "({$status})";
 382           }
 383         }
 384         else {
 385           $op = '=';
 386           $status = $value;
 387         }
 388
 389         $statusValues = CRM_Core_OptionGroup::values("contribution_status");
 390
 391         $names = array();
 392         if (isset($val) &&
 393           is_array($val)
 394         ) {
 395           foreach ($val as $id => $dontCare) {
 396             $names[] = $statusValues[$id];
 397           }
 398         }
 399         else {
 400           $names[] = $statusValues[$value];
 401         }
 402
 403         $query->_qill[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 404         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_status_id",
 405           $op,
 406           $status,
 407           "Integer"
 408         );
 409         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 410         return;
 411
 412       case 'contribution_source':
 413         $value = $strtolower(CRM_Core_DAO::escapeString($value));
 414         if ($wildcard) {
 415           $value = "%$value%";
 416           $op = 'LIKE';
 417         }
 418         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
 419         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 420         $query->_qill[$grouping][] = ts('Contribution Source %1 %2', array(1 => $op, 2 => $quoteValue));
 421         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 422         return;
 423
 424       case 'contribution_trxn_id':
 425       case 'contribution_transaction_id':
 426         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
 427         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 428         $query->_qill[$grouping][] = ts('Transaction ID %1 %2', array(1 => $op, 2 => $quoteValue));
 429         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 430         return;
 431
 432       case 'contribution_check_number':
 433         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
 434         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 435         $query->_qill[$grouping][] = ts('Check Number %1 %2', array(1 => $op, 2 => $quoteValue));
 436         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 437         return;
 438
 439       case 'contribution_is_test':
 440       case 'contribution_test':
 441         // We dont want to include all tests for sql OR CRM-7827
 442         if (!$value || $query->getOperator() != 'OR') {
 443           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
 444           if ($value) {
 445             $query->_qill[$grouping][] = ts("Only Display Test Contributions");
 446           }
 447           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 448         }
 449         return;
 450
 451       case 'contribution_is_pay_later':
 452       case 'contribution_pay_later':
 453         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
 454         if ($value) {
 455           $query->_qill[$grouping][] = ts("Find Pay Later Contributions");
 456         }
 457         else {
 458           $query->_qill[$grouping][] = ts("Exclude Pay Later Contributions");
 459         }
 460         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 461         return;
 462
 463       case 'contribution_recurring':
 464         if ($value) {
 465           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
 466           $query->_qill[$grouping][] = ts("Find Recurring Contributions");
 467           $query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
 468         }
 469         else {
 470           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
 471           $query->_qill[$grouping][] = ts("Exclude Recurring Contributions");
 472         }
 473         return;
 474
 475       case 'contribution_recur_id':
 476         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id",
 477           $op, $value, "Integer"
 478         );
 479         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 480         return;
 481
 482       case 'contribution_id':
 483         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.id", $op, $value, "Integer");
 484         $query->_qill[$grouping][] = ts('Contribution ID %1 %2', array(1 => $op, 2 => $quoteValue));
 485         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 486         return;
 487
 488       case 'contribution_note':
 489         $value = $strtolower(CRM_Core_DAO::escapeString($value));
 490         if ($wildcard) {
 491           $value = "%$value%";
 492           $op = 'LIKE';
 493         }
 494         $wc = ($op != 'LIKE') ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
 495         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 496         $query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
 497         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
 498         return;
 499
 500       case 'contribution_membership_id':
 501         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 502         $query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
 503
 504         return;
 505
 506       case 'contribution_participant_id':
 507         $query->_where[$grouping][] = " civicrm_participant.id $op $value";
 508         $query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
 509         return;
 510
 511       case 'contribution_pcp_display_in_roll':
 512         $query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
 513         if ($value) {
 514           $query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll");
 515         }
 516         else {
 517           $query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
 518         }
 519         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 520         return;
 521
 522       // Supporting search for currency type -- CRM-4711
 523
 524       case 'contribution_currency_type':
 525         $currencySymbol = CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'));
 526         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.currency",
 527           $op, $currencySymbol[$value], "String"
 528         );
 529         $query->_qill[$grouping][] = ts('Currency Type - %1', array(1 => $currencySymbol[$value]));
 530         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 531         return;
 532
 533       case 'contribution_campaign_id':
 534         $campParams = array(
 535           'op' => $op,
 536           'campaign' => $value,
 537           'grouping' => $grouping,
 538           'tableName' => 'civicrm_contribution',
 539         );
 540         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 541         return;
 542
 543       case 'contribution_batch_id':
 544         $batches = CRM_Batch_BAO_Batch::getBatches();
 545         $query->_where[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
 546         $query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
 547         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 548         $query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1;
 549         return;
 550
 551       default:
 552         //all other elements are handle in this case
 553         $fldName    = substr($name, 13);
 554         if (!isset($fields[$fldName])) {
 555           // CRM-12597
 556           CRM_Core_Session::setStatus(ts(
 557               'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
 558               array(1 => $fldName)
 559             )
 560           );
 561           return;
 562         }
 563         $whereTable = $fields[$fldName];
 564         $value      = trim($value);
 565
 566         //contribution fields (decimal fields) which don't require a quote in where clause.
 567         $moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
 568         //date fields
 569         $dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
 570
 571         if (in_array($fldName, $dateFields)) {
 572           $dataType = "Date";
 573         }
 574         elseif (in_array($fldName, $moneyFields)) {
 575           $dataType = "Money";
 576         }
 577         else {
 578           $dataType = "String";
 579         }
 580
 581         $wc = ($op != 'LIKE' && $dataType != 'Date') ? "LOWER($whereTable[where])" : "$whereTable[where]";
 582         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
 583         $query->_qill[$grouping][] = "$whereTable[title] $op $quoteValue";
 584         list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
 585         $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 586         if ($tableName == 'civicrm_contribution_product') {
 587           $query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
 588           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 589         }
 590         else {
 591           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 592         }
 593     }
 594   }
 595
 596   static function from($name, $mode, $side) {
 597     $from = NULL;
 598     switch ($name) {
 599       case 'civicrm_contribution':
 600         $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
 601         break;
 602
 603       case 'civicrm_contribution_recur':
 604         if ($mode == 1) {
 605           // in contact mode join directly onto profile - in case no contributions exist yet
 606           $from = " $side JOIN civicrm_contribution_recur ON contact_a.id = civicrm_contribution_recur.contact_id ";
 607         }
 608         else {
 609           $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
 610         }
 611         break;
 612
 613       case 'civicrm_financial_type':
 614         if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 615           $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 616         }
 617         else {
 618           $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 619         }
 620         break;
 621
 622       case 'civicrm_accounting_code':
 623         $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' ";
 624         $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
 625         $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
 626         $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
 627         break;
 628
 629       case 'civicrm_contribution_page':
 630         $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page ON civicrm_contribution.contribution_page.id";
 631         break;
 632
 633       case 'civicrm_product':
 634         $from = " $side  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
 635         $from .= " $side  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
 636         break;
 637
 638       case 'contribution_payment_instrument':
 639         $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
 640         $from .= " $side JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
 641                                AND option_group_payment_instrument.id = payment_instrument.option_group_id ) ";
 642         break;
 643
 644       case 'civicrm_contact_b':
 645         $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_contribution.honor_contact_id = contact_b.id )";
 646         break;
 647
 648       case 'contribution_status':
 649         $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
 650         $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
 651                                AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
 652         break;
 653
 654       case 'contribution_note':
 655         $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
 656                                                     civicrm_contribution.id = civicrm_note.entity_id )";
 657         break;
 658
 659       case 'contribution_honor_contact_name':
 660         $from .= " $side JOIN civicrm_contact civicrm_contact_c ON (civicrm_contribution.honor_contact_id = civicrm_contact_c.id )";
 661         break;
 662
 663       case 'contribution_honor_contact_email':
 664         $from .= " $side JOIN civicrm_email as honor_email ON (civicrm_contribution.honor_contact_id = honor_email.contact_id AND honor_email.is_primary = 1 )";
 665         break;
 666
 667       case 'contribution_honor_type_label':
 668         $from = " $side JOIN civicrm_option_group option_group_honor_type ON ( option_group_honor_type.name = 'honor_type')";
 669         $from .= " $side JOIN civicrm_option_value honor_type ON (civicrm_contribution.honor_type_id = honor_type.value
 670                                AND option_group_honor_type.id = honor_type.option_group_id ) ";
 671         break;
 672
 673       case 'contribution_membership':
 674         $from = " $side  JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
 675         $from .= " $side  JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 676         break;
 677
 678       case 'contribution_participant':
 679         $from = " $side  JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
 680         $from .= " $side  JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
 681         break;
 682
 683       case 'civicrm_contribution_soft':
 684         $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
 685         break;
 686
 687       case 'civicrm_contribution_soft_contact':
 688         $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
 689         break;
 690
 691       case 'civicrm_contribution_soft_email':
 692         $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
 693         break;
 694
 695       case 'civicrm_contribution_soft_phone':
 696         $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
 697         break;
 698
 699       case 'contribution_batch':
 700         $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_contribution' AND
 701           civicrm_contribution.id = civicrm_entity_batch.entity_id )";
 702         $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
 703         break;
 704     }
 705     return $from;
 706   }
 707
 708   static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
 709     $properties = NULL;
 710     if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 711       $properties = array(
 712         'contact_type' => 1,
 713         'contact_sub_type' => 1,
 714         'sort_name' => 1,
 715         'display_name' => 1,
 716         'financial_type' => 1,
 717         'contribution_source' => 1,
 718         'receive_date' => 1,
 719         'thankyou_date' => 1,
 720         'cancel_date' => 1,
 721         'total_amount' => 1,
 722         'accounting_code' => 1,
 723         'payment_instrument' => 1,
 724         'check_number' => 1,
 725         'non_deductible_amount' => 1,
 726         'fee_amount' => 1,
 727         'net_amount' => 1,
 728         'trxn_id' => 1,
 729         'invoice_id' => 1,
 730         'currency' => 1,
 731         'cancel_reason' => 1,
 732         'receipt_date' => 1,
 733         'product_name' => 1,
 734         'sku' => 1,
 735         'product_option' => 1,
 736         'fulfilled_date' => 1,
 737         'contribution_start_date' => 1,
 738         'contribution_end_date' => 1,
 739         'is_test' => 1,
 740         'is_pay_later' => 1,
 741         'contribution_status' => 1,
 742         'contribution_status_id' => 1,
 743         'contribution_recur_id' => 1,
 744         'amount_level' => 1,
 745         'contribution_note' => 1,
 746         'contribution_batch' => 1,
 747         'contribution_campaign_id' => 1
 748       );
 749
 750       if ($includeCustomFields) {
 751         // also get all the custom contribution properties
 752         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Contribution');
 753         if (!empty($fields)) {
 754           foreach ($fields as $name => $dontCare) {
 755             $properties[$name] = 1;
 756           }
 757         }
 758       }
 759     }
 760     return $properties;
 761   }
 762
 763   /**
 764    * add all the elements shared between contribute search and advnaced search
 765    *
 766    * @access public
 767    *
 768    * @return void
 769    * @static
 770    */
 771   static function buildSearchForm(&$form) {
 772
 773     // Added contribution source
 774     $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
 775
 776     CRM_Core_Form_Date::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
 777
 778     $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 779     $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 780
 781     $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 782     $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 783
 784     // Adding select option for curreny type -- CRM-4711
 785     $form->add('select', 'contribution_currency_type',
 786       ts('Currency Type'),
 787       array(
 788         '' => ts('- any -')) +
 789       CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'))
 790     );
 791
 792     $form->add('select', 'financial_type_id',
 793       ts('Financial Type'),
 794       array(
 795         '' => ts('- any -')) +
 796       CRM_Contribute_PseudoConstant::financialType()
 797     );
 798
 799     $form->add('select', 'contribution_page_id',
 800       ts('Contribution Page'),
 801       array(
 802         '' => ts('- any -')) +
 803       CRM_Contribute_PseudoConstant::contributionPage()
 804     );
 805
 806
 807     $form->add('select', 'contribution_payment_instrument_id',
 808       ts('Payment Instrument'),
 809       array(
 810         '' => ts('- any -')) +
 811       CRM_Contribute_PseudoConstant::paymentInstrument()
 812     );
 813
 814     $form->add('select', 'contribution_pcp_made_through_id',
 815       ts('Personal Campaign Page'),
 816       array(
 817         '' => ts('- any -')) +
 818       CRM_Contribute_PseudoConstant::pcPage()
 819     );
 820
 821     $status = array();
 822
 823     $statusValues = CRM_Core_OptionGroup::values("contribution_status");
 824     // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
 825     unset($statusValues['5'], $statusValues['6']);
 826
 827     foreach ($statusValues as $key => $val) {
 828       $status[] = $form->createElement('advcheckbox', $key, NULL, $val);
 829     }
 830
 831     $form->addGroup($status, 'contribution_status_id', ts('Contribution Status'));
 832
 833     // Add fields for thank you and receipt
 834     $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'));
 835     $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'));
 836
 837     // Add fields for honor search
 838     $form->addElement('text', 'contribution_in_honor_of', ts("In Honor Of"));
 839
 840     $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'));
 841     $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'));
 842 //    $form->addElement('contribution_recur_frequency_unit', ts('Recurring Frequency Unit?'));
 843 //    $form->addElement('contribution_recur_frequency_interval', ts('Recurring Frequency Interval?'));
 844 //    $form->addElement('contribution_recur_frequency_installments', ts('Number of Recurring Installments?'));
 845     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_start_date', 1, '_low', '_high', ts('Recurring Start Date?'), FALSE);
 846     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_next_sched_contribution_date', 1, '_low', '_high', ts('Next scheduled Recurring Contribution?'), FALSE);
 847     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_failure_retry_date', 1, '_low', '_high', ts('Retry Date for  Recurring Contribution?'), FALSE);
 848     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_end_date', 1, '_low', '_high', ts('End Date for  Recurring Contribution?'), FALSE);
 849     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_cancel_date', 1, '_low', '_high', ts('Cancel Date for  Recurring Contribution?'), FALSE);
 850     CRM_Core_Form_Date::buildDateRange($form, 'contribution_recur_modified_date', 1, '_low', '_high', ts('Modified Date for  Recurring Contribution?'), FALSE);
 851
 852     $form->addYesNo('contribution_test', ts('Contribution is a Test?'));
 853
 854     // Add field for transaction ID search
 855     $form->addElement('text', 'contribution_transaction_id', ts("Transaction ID"));
 856
 857     $form->addElement('text', 'contribution_check_number', ts('Check Number'));
 858
 859     // Add field for pcp display in roll search
 860     $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'));
 861
 862     // Add all the custom searchable fields
 863     $contribution = array('Contribution');
 864     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $contribution);
 865     if ($groupDetails) {
 866       $form->assign('contributeGroupTree', $groupDetails);
 867       foreach ($groupDetails as $group) {
 868         foreach ($group['fields'] as $field) {
 869           $fieldId = $field['id'];
 870           $elementName = 'custom_' . $fieldId;
 871           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 872             $elementName,
 873             $fieldId,
 874             FALSE, FALSE, TRUE
 875           );
 876         }
 877       }
 878     }
 879
 880     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'contribution_campaign_id');
 881
 882     // Add batch select
 883     $batches = CRM_Batch_BAO_Batch::getBatches();
 884
 885     if ( !empty( $batches ) ) {
 886       $form->add('select', 'contribution_batch_id',
 887         ts('Batch Name'),
 888         array( '' => ts('- any -')) + $batches );
 889     }
 890
 891     $form->assign('validCiviContribute', TRUE);
 892     $form->setDefaults(array('contribution_test' => 0));
 893   }
 894
 895   static function addShowHide(&$showHide) {
 896     $showHide->addHide('contributeForm');
 897     $showHide->addShow('contributeForm_show');
 898   }
 899
 900   static function searchAction(&$row, $id) {
 901   }
 902
 903   static function tableNames(&$tables) {
 904     // Add contribution table
 905     if (CRM_Utils_Array::value('civicrm_product', $tables)) {
 906       $tables = array_merge(array('civicrm_contribution' => 1), $tables);
 907     }
 908
 909     if (CRM_Utils_Array::value('civicrm_contribution_product', $tables) &&
 910       !CRM_Utils_Array::value('civicrm_product', $tables)) {
 911       $tables['civicrm_product'] = 1;
 912     }
 913   }
 914
 915   /**
 916    * Add the where for dates
 917    * @param array $values array of query values
 918    * @param object $query the query object
 919    * @param string $name query field that is set
 920    * @param string $field name of field to be set
 921    * @param string $title title of the field
 922    */
 923   static function buildDateWhere(&$values, $query, $name, $field, $title) {
 924     $fieldPart = strpos($name, $field);
 925     if($fieldPart === FALSE) {
 926       return;
 927     }
 928     // we only have recurring dates using this ATM so lets' short cut to find the table name
 929     $table = 'contribution_recur';
 930     $fieldName = split($table . '_', $field);
 931     $query->dateQueryBuilder($values,
 932       'civicrm_' . $table, $field, $fieldName[1], $title
 933     );
 934     return TRUE;
 935   }
 936 }
 937