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
 243     switch ($name) {
 244       case 'contribution_date':
 245       case 'contribution_date_low':
 246       case 'contribution_date_low_time':
 247       case 'contribution_date_high':
 248       case 'contribution_date_high_time':
 249         // process to / from date
 250         $query->dateQueryBuilder($values,
 251           'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'
 252         );
 253         return;
 254
 255       case 'contribution_amount':
 256       case 'contribution_amount_low':
 257       case 'contribution_amount_high':
 258         // process min/max amount
 259         $query->numberRangeBuilder($values,
 260           'civicrm_contribution', 'contribution_amount',
 261           'total_amount', 'Contribution Amount',
 262           NULL
 263         );
 264         return;
 265
 266       case 'contribution_total_amount':
 267         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.total_amount",
 268           $op, $value, "Money"
 269         );
 270         $query->_qill[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
 271         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 272         return;
 273
 274       case 'contribution_thankyou_date_is_not_null':
 275         if ($value) {
 276           $op = "IS NOT NULL";
 277           $query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
 278         }
 279         else {
 280           $op = "IS NULL";
 281           $query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
 282         }
 283         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
 284         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 285         return;
 286
 287       case 'contribution_receipt_date_is_not_null':
 288         if ($value) {
 289           $op = "IS NOT NULL";
 290           $query->_qill[$grouping][] = ts('Contribution Receipt Sent');
 291         }
 292         else {
 293           $op = "IS NULL";
 294           $query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
 295         }
 296         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
 297         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 298         return;
 299
 300         case 'financial_type_id':
 301         case 'financial_type':
 302         $cType = $value;
 303         $types = CRM_Contribute_PseudoConstant::financialType();
 304         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.financial_type_id",
 305           $op, $value, "Integer"
 306         );
 307         $query->_qill[$grouping ][] = ts('Financial Type - %1', array(1 => $types[$cType]));
 308         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 309         return;
 310
 311       case 'contribution_page_id':
 312         $cPage = $value;
 313         $pages = CRM_Contribute_PseudoConstant::contributionPage();
 314         $query->_where[$grouping][] = "civicrm_contribution.contribution_page_id = $cPage";
 315         $query->_qill[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
 316         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 317         return;
 318
 319       case 'contribution_pcp_made_through_id':
 320         $pcPage = $value;
 321         $pcpages = CRM_Contribute_PseudoConstant::pcPage();
 322         $query->_where[$grouping][] = "civicrm_contribution_soft.pcp_id = $pcPage";
 323         $query->_qill[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
 324         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 325         return;
 326
 327       case 'contribution_payment_instrument_id':
 328       case 'contribution_payment_instrument':
 329         $pi = $value;
 330         $pis = CRM_Contribute_PseudoConstant::paymentInstrument();
 331         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.payment_instrument_id",
 332           $op, $value, "Integer"
 333         );
 334
 335         $query->_qill[$grouping][] = ts('Paid By - %1', array(1 => $pis[$pi]));
 336         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 337         return;
 338
 339       case 'contribution_in_honor_of':
 340         $name    = trim($value);
 341         $newName = str_replace(',', " ", $name);
 342         $pieces  = explode(' ', $newName);
 343         foreach ($pieces as $piece) {
 344           $value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
 345           $value = "'%$value%'";
 346           $sub[] = " ( contact_b.sort_name LIKE $value )";
 347         }
 348
 349         $query->_where[$grouping][] = ' ( ' . implode('  OR ', $sub) . ' ) ';
 350         $query->_qill[$grouping][] = ts('Honor name like - \'%1\'', array(1 => $name));
 351         $query->_tables['civicrm_contact_b'] = $query->_whereTables['civicrm_contact_b'] = 1;
 352         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 353         return;
 354
 355       case 'contribution_status':
 356       case 'contribution_status_id':
 357         if (is_array($value)) {
 358           foreach ($value as $k => $v) {
 359             if ($v) {
 360               $val[$k] = $k;
 361             }
 362           }
 363
 364           $status = implode(',', $val);
 365
 366           if (count($val) > 1) {
 367             $op = 'IN';
 368             $status = "({$status})";
 369           }
 370         }
 371         else {
 372           $op = '=';
 373           $status = $value;
 374         }
 375
 376         $statusValues = CRM_Core_OptionGroup::values("contribution_status");
 377
 378         $names = array();
 379         if (isset($val) &&
 380           is_array($val)
 381         ) {
 382           foreach ($val as $id => $dontCare) {
 383             $names[] = $statusValues[$id];
 384           }
 385         }
 386         else {
 387           $names[] = $statusValues[$value];
 388         }
 389
 390         $query->_qill[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 391         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_status_id",
 392           $op,
 393           $status,
 394           "Integer"
 395         );
 396         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 397         return;
 398
 399       case 'contribution_source':
 400         $value = $strtolower(CRM_Core_DAO::escapeString($value));
 401         if ($wildcard) {
 402           $value = "%$value%";
 403           $op = 'LIKE';
 404         }
 405         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
 406         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 407         $query->_qill[$grouping][] = ts('Contribution Source %1 %2', array(1 => $op, 2 => $quoteValue));
 408         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 409         return;
 410
 411       case 'contribution_trxn_id':
 412       case 'contribution_transaction_id':
 413         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
 414         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 415         $query->_qill[$grouping][] = ts('Transaction ID %1 %2', array(1 => $op, 2 => $quoteValue));
 416         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 417         return;
 418
 419       case 'contribution_check_number':
 420         $wc = ($op != 'LIKE') ? "LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
 421         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 422         $query->_qill[$grouping][] = ts('Check Number %1 %2', array(1 => $op, 2 => $quoteValue));
 423         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 424         return;
 425
 426       case 'contribution_is_test':
 427       case 'contribution_test':
 428         // We dont want to include all tests for sql OR CRM-7827
 429         if (!$value || $query->getOperator() != 'OR') {
 430           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
 431           if ($value) {
 432             $query->_qill[$grouping][] = ts("Only Display Test Contributions");
 433           }
 434           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 435         }
 436         return;
 437
 438       case 'contribution_is_pay_later':
 439       case 'contribution_pay_later':
 440         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
 441         if ($value) {
 442           $query->_qill[$grouping][] = ts("Find Pay Later Contributions");
 443         }
 444         else {
 445           $query->_qill[$grouping][] = ts("Exclude Pay Later Contributions");
 446         }
 447         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 448         return;
 449
 450       case 'contribution_recurring':
 451         if ($value) {
 452           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
 453           $query->_qill[$grouping][] = ts("Find Recurring Contributions");
 454           $query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
 455         }
 456         else {
 457           $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
 458           $query->_qill[$grouping][] = ts("Exclude Recurring Contributions");
 459         }
 460         return;
 461
 462       case 'contribution_recur_id':
 463         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id",
 464           $op, $value, "Integer"
 465         );
 466         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 467         return;
 468
 469       case 'contribution_id':
 470         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.id", $op, $value, "Integer");
 471         $query->_qill[$grouping][] = ts('Contribution ID %1 %2', array(1 => $op, 2 => $quoteValue));
 472         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 473         return;
 474
 475       case 'contribution_note':
 476         $value = $strtolower(CRM_Core_DAO::escapeString($value));
 477         if ($wildcard) {
 478           $value = "%$value%";
 479           $op = 'LIKE';
 480         }
 481         $wc = ($op != 'LIKE') ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
 482         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
 483         $query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
 484         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
 485         return;
 486
 487       case 'contribution_membership_id':
 488         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 489         $query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
 490
 491         return;
 492
 493       case 'contribution_participant_id':
 494         $query->_where[$grouping][] = " civicrm_participant.id $op $value";
 495         $query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
 496         return;
 497
 498       case 'contribution_pcp_display_in_roll':
 499         $query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll $op '$value'";
 500         if ($value) {
 501           $query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll");
 502         }
 503         else {
 504           $query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
 505         }
 506         $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
 507         return;
 508
 509       // Supporting search for currency type -- CRM-4711
 510
 511       case 'contribution_currency_type':
 512         $currencySymbol = CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'));
 513         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.currency",
 514           $op, $currencySymbol[$value], "String"
 515         );
 516         $query->_qill[$grouping][] = ts('Currency Type - %1', array(1 => $currencySymbol[$value]));
 517         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 518         return;
 519
 520       case 'contribution_campaign_id':
 521         $campParams = array(
 522           'op' => $op,
 523           'campaign' => $value,
 524           'grouping' => $grouping,
 525           'tableName' => 'civicrm_contribution',
 526         );
 527         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 528         return;
 529
 530       case 'contribution_batch_id':
 531         $batches = CRM_Batch_BAO_Batch::getBatches();
 532         $query->_where[$grouping][] = " civicrm_entity_batch.batch_id $op $value";
 533         $query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
 534         $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 535         $query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1;
 536         return;
 537
 538       default:
 539         //all other elements are handle in this case
 540         $fldName    = substr($name, 13);
 541         if (!isset($fields[$fldName])) {
 542           // CRM-12597
 543           CRM_Core_Session::setStatus(ts(
 544               'We did not recognize the search field: %1. Please check and fix your contribution related smart groups.',
 545               array(1 => $fldName)
 546             )
 547           );
 548           return;
 549         }
 550         $whereTable = $fields[$fldName];
 551         $value      = trim($value);
 552
 553         //contribution fields (decimal fields) which don't require a quote in where clause.
 554         $moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
 555         //date fields
 556         $dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
 557
 558         if (in_array($fldName, $dateFields)) {
 559           $dataType = "Date";
 560         }
 561         elseif (in_array($fldName, $moneyFields)) {
 562           $dataType = "Money";
 563         }
 564         else {
 565           $dataType = "String";
 566         }
 567
 568         $wc = ($op != 'LIKE' && $dataType != 'Date') ? "LOWER($whereTable[where])" : "$whereTable[where]";
 569         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
 570         $query->_qill[$grouping][] = "$whereTable[title] $op $quoteValue";
 571         list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
 572         $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 573         if ($tableName == 'civicrm_contribution_product') {
 574           $query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
 575           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 576         }
 577         else {
 578           $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
 579         }
 580     }
 581   }
 582
 583   static function from($name, $mode, $side) {
 584     $from = NULL;
 585     switch ($name) {
 586       case 'civicrm_contribution':
 587         $from = " $side JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id ";
 588         break;
 589
 590       case 'civicrm_contribution_recur':
 591         $from = " $side JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id ";
 592         break;
 593
 594       case 'civicrm_financial_type':
 595         if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 596           $from = " INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 597         }
 598         else {
 599           $from = " $side JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id ";
 600         }
 601         break;
 602
 603       case 'civicrm_accounting_code':
 604         $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' ";
 605         $from .= " INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id ";
 606         $from .= " INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship AND cov.name = 'Income Account is' ";
 607         $from .= " INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'account_relationship' ";
 608         break;
 609
 610       case 'civicrm_contribution_page':
 611         $from = " $side JOIN civicrm_contribution_page ON civicrm_contribution.contribution_page ON civicrm_contribution.contribution_page.id";
 612         break;
 613
 614       case 'civicrm_product':
 615         $from = " $side  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id";
 616         $from .= " $side  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id ";
 617         break;
 618
 619       case 'contribution_payment_instrument':
 620         $from = " $side JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')";
 621         $from .= " $side JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
 622                                AND option_group_payment_instrument.id = payment_instrument.option_group_id ) ";
 623         break;
 624
 625       case 'civicrm_contact_b':
 626         $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_contribution.honor_contact_id = contact_b.id )";
 627         break;
 628
 629       case 'contribution_status':
 630         $from = " $side JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')";
 631         $from .= " $side JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
 632                                AND option_group_contribution_status.id = contribution_status.option_group_id ) ";
 633         break;
 634
 635       case 'contribution_note':
 636         $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution' AND
 637                                                     civicrm_contribution.id = civicrm_note.entity_id )";
 638         break;
 639
 640       case 'contribution_honor_contact_name':
 641         $from .= " $side JOIN civicrm_contact civicrm_contact_c ON (civicrm_contribution.honor_contact_id = civicrm_contact_c.id )";
 642         break;
 643
 644       case 'contribution_honor_contact_email':
 645         $from .= " $side JOIN civicrm_email as honor_email ON (civicrm_contribution.honor_contact_id = honor_email.contact_id AND honor_email.is_primary = 1 )";
 646         break;
 647
 648       case 'contribution_honor_type_label':
 649         $from = " $side JOIN civicrm_option_group option_group_honor_type ON ( option_group_honor_type.name = 'honor_type')";
 650         $from .= " $side JOIN civicrm_option_value honor_type ON (civicrm_contribution.honor_type_id = honor_type.value
 651                                AND option_group_honor_type.id = honor_type.option_group_id ) ";
 652         break;
 653
 654       case 'contribution_membership':
 655         $from = " $side  JOIN civicrm_membership_payment ON civicrm_membership_payment.contribution_id = civicrm_contribution.id";
 656         $from .= " $side  JOIN civicrm_membership ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 657         break;
 658
 659       case 'contribution_participant':
 660         $from = " $side  JOIN civicrm_participant_payment ON civicrm_participant_payment.contribution_id = civicrm_contribution.id";
 661         $from .= " $side  JOIN civicrm_participant ON civicrm_participant_payment.participant_id = civicrm_participant.id ";
 662         break;
 663
 664       case 'civicrm_contribution_soft':
 665         $from = " $side JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id";
 666         break;
 667
 668       case 'civicrm_contribution_soft_contact':
 669         $from .= " $side JOIN civicrm_contact civicrm_contact_d ON (civicrm_contribution_soft.contact_id = civicrm_contact_d.id )";
 670         break;
 671
 672       case 'civicrm_contribution_soft_email':
 673         $from .= " $side JOIN civicrm_email as soft_email ON (civicrm_contact_d.id = soft_email.contact_id )";
 674         break;
 675
 676       case 'civicrm_contribution_soft_phone':
 677         $from .= " $side JOIN civicrm_phone as soft_phone ON (civicrm_contact_d.id = soft_phone.contact_id )";
 678         break;
 679
 680       case 'contribution_batch':
 681         $from .= " $side JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_contribution' AND
 682           civicrm_contribution.id = civicrm_entity_batch.entity_id )";
 683         $from .= " $side JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id";
 684         break;
 685     }
 686     return $from;
 687   }
 688
 689   static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
 690     $properties = NULL;
 691     if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 692       $properties = array(
 693         'contact_type' => 1,
 694         'contact_sub_type' => 1,
 695         'sort_name' => 1,
 696         'display_name' => 1,
 697         'financial_type' => 1,
 698         'contribution_source' => 1,
 699         'receive_date' => 1,
 700         'thankyou_date' => 1,
 701         'cancel_date' => 1,
 702         'total_amount' => 1,
 703         'accounting_code' => 1,
 704         'payment_instrument' => 1,
 705         'check_number' => 1,
 706         'non_deductible_amount' => 1,
 707         'fee_amount' => 1,
 708         'net_amount' => 1,
 709         'trxn_id' => 1,
 710         'invoice_id' => 1,
 711         'currency' => 1,
 712         'cancel_reason' => 1,
 713         'receipt_date' => 1,
 714         'product_name' => 1,
 715         'sku' => 1,
 716         'product_option' => 1,
 717         'fulfilled_date' => 1,
 718         'contribution_start_date' => 1,
 719         'contribution_end_date' => 1,
 720         'is_test' => 1,
 721         'is_pay_later' => 1,
 722         'contribution_status' => 1,
 723         'contribution_status_id' => 1,
 724         'contribution_recur_id' => 1,
 725         'amount_level' => 1,
 726         'contribution_note' => 1,
 727         'contribution_batch' => 1,
 728         'contribution_campaign_id' => 1
 729       );
 730
 731       if ($includeCustomFields) {
 732         // also get all the custom contribution properties
 733         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Contribution');
 734         if (!empty($fields)) {
 735           foreach ($fields as $name => $dontCare) {
 736             $properties[$name] = 1;
 737           }
 738         }
 739       }
 740     }
 741     return $properties;
 742   }
 743
 744   /**
 745    * add all the elements shared between contribute search and advnaced search
 746    *
 747    * @access public
 748    *
 749    * @return void
 750    * @static
 751    */
 752   static function buildSearchForm(&$form) {
 753
 754     // Added contribution source
 755     $form->addElement('text', 'contribution_source', ts('Contribution Source'), CRM_Core_DAO::getAttribute('CRM_Contribute_DAO_Contribution', 'source'));
 756
 757     CRM_Core_Form_Date::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE);
 758
 759     $form->add('text', 'contribution_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 760     $form->addRule('contribution_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 761
 762     $form->add('text', 'contribution_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 763     $form->addRule('contribution_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 764
 765     // Adding select option for curreny type -- CRM-4711
 766     $form->add('select', 'contribution_currency_type',
 767       ts('Currency Type'),
 768       array(
 769         '' => ts('- any -')) +
 770       CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'))
 771     );
 772
 773     $form->add('select', 'financial_type_id',
 774       ts('Financial Type'),
 775       array(
 776         '' => ts('- any -')) +
 777       CRM_Contribute_PseudoConstant::financialType()
 778     );
 779
 780     $form->add('select', 'contribution_page_id',
 781       ts('Contribution Page'),
 782       array(
 783         '' => ts('- any -')) +
 784       CRM_Contribute_PseudoConstant::contributionPage()
 785     );
 786
 787
 788     $form->add('select', 'contribution_payment_instrument_id',
 789       ts('Payment Instrument'),
 790       array(
 791         '' => ts('- any -')) +
 792       CRM_Contribute_PseudoConstant::paymentInstrument()
 793     );
 794
 795     $form->add('select', 'contribution_pcp_made_through_id',
 796       ts('Personal Campaign Page'),
 797       array(
 798         '' => ts('- any -')) +
 799       CRM_Contribute_PseudoConstant::pcPage()
 800     );
 801
 802     $status = array();
 803
 804     $statusValues = CRM_Core_OptionGroup::values("contribution_status");
 805     // Remove status values that are only used for recurring contributions or pledges (In Progress, Overdue).
 806     unset($statusValues['5'], $statusValues['6']);
 807
 808     foreach ($statusValues as $key => $val) {
 809       $status[] = $form->createElement('advcheckbox', $key, NULL, $val);
 810     }
 811
 812     $form->addGroup($status, 'contribution_status_id', ts('Contribution Status'));
 813
 814     // Add fields for thank you and receipt
 815     $form->addYesNo('contribution_thankyou_date_is_not_null', ts('Thank-you sent?'));
 816     $form->addYesNo('contribution_receipt_date_is_not_null', ts('Receipt sent?'));
 817
 818     // Add fields for honor search
 819     $form->addElement('text', 'contribution_in_honor_of', ts("In Honor Of"));
 820
 821     $form->addYesNo('contribution_pay_later', ts('Contribution is Pay Later?'));
 822     $form->addYesNo('contribution_recurring', ts('Contribution is Recurring?'));
 823     $form->addYesNo('contribution_test', ts('Contribution is a Test?'));
 824
 825     // Add field for transaction ID search
 826     $form->addElement('text', 'contribution_transaction_id', ts("Transaction ID"));
 827
 828     $form->addElement('text', 'contribution_check_number', ts('Check Number'));
 829
 830     // Add field for pcp display in roll search
 831     $form->addYesNo('contribution_pcp_display_in_roll', ts('Personal Campaign Page Honor Roll?'));
 832
 833     // Add all the custom searchable fields
 834     $contribution = array('Contribution');
 835     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $contribution);
 836     if ($groupDetails) {
 837       $form->assign('contributeGroupTree', $groupDetails);
 838       foreach ($groupDetails as $group) {
 839         foreach ($group['fields'] as $field) {
 840           $fieldId = $field['id'];
 841           $elementName = 'custom_' . $fieldId;
 842           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 843             $elementName,
 844             $fieldId,
 845             FALSE, FALSE, TRUE
 846           );
 847         }
 848       }
 849     }
 850
 851     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'contribution_campaign_id');
 852
 853     // Add batch select
 854     $batches = CRM_Batch_BAO_Batch::getBatches();
 855
 856     if ( !empty( $batches ) ) {
 857       $form->add('select', 'contribution_batch_id',
 858         ts('Batch Name'),
 859         array( '' => ts('- any -')) + $batches );
 860     }
 861
 862     $form->assign('validCiviContribute', TRUE);
 863     $form->setDefaults(array('contribution_test' => 0));
 864   }
 865
 866   static function addShowHide(&$showHide) {
 867     $showHide->addHide('contributeForm');
 868     $showHide->addShow('contributeForm_show');
 869   }
 870
 871   static function searchAction(&$row, $id) {
 872   }
 873
 874   static function tableNames(&$tables) {
 875     // Add contribution table
 876     if (CRM_Utils_Array::value('civicrm_product', $tables)) {
 877       $tables = array_merge(array('civicrm_contribution' => 1), $tables);
 878     }
 879
 880     if (CRM_Utils_Array::value('civicrm_contribution_product', $tables) &&
 881       !CRM_Utils_Array::value('civicrm_product', $tables)) {
 882       $tables['civicrm_product'] = 1;
 883     }
 884   }
 885 }
 886