CRM/Contribute/BAO/Query.php

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