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