CRM/Pledge/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  32  */
  33 class CRM_Pledge_BAO_Query extends CRM_Core_BAO_Query {
  34   /**
  35    * Get pledge fields.
  36    *
  37    * @param bool $checkPermission
  38    *
  39    * @return array
  40    */
  41   public static function getFields($checkPermission = TRUE) {
  42     return CRM_Pledge_BAO_Pledge::exportableFields($checkPermission);
  43   }
  44
  45   /**
  46    * Build select for Pledge.
  47    *
  48    * @param CRM_Contact_BAO_Query $query
  49    */
  50   public static function select(&$query) {
  51
  52     $statusId = implode(',', array_keys(CRM_Core_PseudoConstant::accountOptionValues("contribution_status", NULL, " AND v.name IN  ('Pending', 'Overdue')")));
  53     if (($query->_mode & CRM_Contact_BAO_Query::MODE_PLEDGE) || !empty($query->_returnProperties['pledge_id'])) {
  54       $query->_select['pledge_id'] = 'civicrm_pledge.id as pledge_id';
  55       $query->_element['pledge_id'] = 1;
  56       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  57     }
  58
  59     // add pledge select
  60     if (!empty($query->_returnProperties['pledge_amount'])) {
  61       $query->_select['pledge_amount'] = 'civicrm_pledge.amount as pledge_amount';
  62       $query->_element['pledge_amount'] = 1;
  63       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  64     }
  65
  66     if (!empty($query->_returnProperties['pledge_original_installment_amount'])) {
  67       $query->_select['pledge_original_installment_amount'] = 'civicrm_pledge.original_installment_amount as pledge_original_installment_amount';
  68       $query->_element['pledge_original_installment_amount'] = 1;
  69       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  70     }
  71
  72     if (!empty($query->_returnProperties['installments'])) {
  73       $query->_select['installments'] = 'civicrm_pledge.installments as installments';
  74       $query->_element['installments'] = 1;
  75       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  76     }
  77
  78     if (!empty($query->_returnProperties['pledge_create_date'])) {
  79       $query->_select['pledge_create_date'] = 'civicrm_pledge.create_date as pledge_create_date';
  80       $query->_element['pledge_create_date'] = 1;
  81       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  82     }
  83
  84     if (!empty($query->_returnProperties['pledge_start_date'])) {
  85       $query->_select['pledge_start_date'] = 'civicrm_pledge.start_date as pledge_start_date';
  86       $query->_element['pledge_start_date'] = 1;
  87       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  88     }
  89
  90     if (!empty($query->_returnProperties['pledge_status_id'])) {
  91       $query->_select['pledge_status_id'] = 'pledge_status.value as pledge_status_id';
  92       $query->_element['pledge_status'] = 1;
  93       $query->_tables['pledge_status'] = $query->_whereTables['pledge_status'] = 1;
  94       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  95     }
  96
  97     if (!empty($query->_returnProperties['pledge_status'])) {
  98       $query->_select['pledge_status'] = 'pledge_status.label as pledge_status';
  99       $query->_element['pledge_status'] = 1;
 100       $query->_tables['pledge_status'] = $query->_whereTables['pledge_status'] = 1;
 101       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 102     }
 103
 104     if (!empty($query->_returnProperties['pledge_total_paid'])) {
 105       $query->_select['pledge_total_paid'] = ' (SELECT sum(civicrm_pledge_payment.actual_amount) FROM civicrm_pledge_payment WHERE civicrm_pledge_payment.pledge_id = civicrm_pledge.id AND civicrm_pledge_payment.status_id = 1 ) as pledge_total_paid';
 106       $query->_element['pledge_total_paid'] = 1;
 107     }
 108
 109     if (!empty($query->_returnProperties['pledge_next_pay_date'])) {
 110       $query->_select['pledge_next_pay_date'] = " (SELECT civicrm_pledge_payment.scheduled_date FROM civicrm_pledge_payment WHERE civicrm_pledge_payment.pledge_id = civicrm_pledge.id AND civicrm_pledge_payment.status_id IN ({$statusId}) ORDER BY civicrm_pledge_payment.scheduled_date ASC LIMIT 0, 1) as pledge_next_pay_date";
 111       $query->_element['pledge_next_pay_date'] = 1;
 112     }
 113
 114     if (!empty($query->_returnProperties['pledge_next_pay_amount'])) {
 115       $query->_select['pledge_next_pay_amount'] = " (SELECT civicrm_pledge_payment.scheduled_amount FROM civicrm_pledge_payment WHERE civicrm_pledge_payment.pledge_id = civicrm_pledge.id AND civicrm_pledge_payment.status_id IN ({$statusId}) ORDER BY civicrm_pledge_payment.scheduled_date ASC LIMIT 0, 1) as pledge_next_pay_amount";
 116       $query->_element['pledge_next_pay_amount'] = 1;
 117
 118       $query->_select['pledge_outstanding_amount'] = " (SELECT sum(civicrm_pledge_payment.scheduled_amount) FROM civicrm_pledge_payment WHERE civicrm_pledge_payment.pledge_id = civicrm_pledge.id AND civicrm_pledge_payment.status_id = 6 ) as pledge_outstanding_amount";
 119       $query->_element['pledge_outstanding_amount'] = 1;
 120     }
 121
 122     if (!empty($query->_returnProperties['pledge_financial_type'])) {
 123       $query->_select['pledge_financial_type'] = "(SELECT civicrm_financial_type.name FROM civicrm_financial_type WHERE civicrm_financial_type.id = civicrm_pledge.financial_type_id) as pledge_financial_type";
 124       $query->_element['pledge_financial_type'] = 1;
 125       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 126     }
 127
 128     if (!empty($query->_returnProperties['pledge_contribution_page_id'])) {
 129       $query->_select['pledge_contribution_page_id'] = 'civicrm_pledge.contribution_page_id as pledge_contribution_page_id';
 130       $query->_element['pledge_contribution_page_id'] = 1;
 131       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 132     }
 133
 134     if (!empty($query->_returnProperties['pledge_payment_id'])) {
 135       $query->_select['pledge_payment_id'] = 'civicrm_pledge_payment.id as pledge_payment_id';
 136       $query->_element['pledge_payment_id'] = 1;
 137       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 138     }
 139
 140     if (!empty($query->_returnProperties['pledge_payment_scheduled_amount'])) {
 141       $query->_select['pledge_payment_scheduled_amount'] = 'civicrm_pledge_payment.scheduled_amount as pledge_payment_scheduled_amount';
 142       $query->_element['pledge_payment_scheduled_amount'] = 1;
 143       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 144     }
 145
 146     if (!empty($query->_returnProperties['pledge_payment_scheduled_date'])) {
 147       $query->_select['pledge_payment_scheduled_date'] = 'civicrm_pledge_payment.scheduled_date as pledge_payment_scheduled_date';
 148       $query->_element['pledge_payment_scheduled_date'] = 1;
 149       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 150     }
 151
 152     if (!empty($query->_returnProperties['pledge_payment_paid_amount'])) {
 153       $query->_select['pledge_payment_paid_amount'] = 'civicrm_pledge_payment.actual_amount as pledge_payment_paid_amount';
 154       $query->_element['pledge_payment_paid_amount'] = 1;
 155       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 156     }
 157
 158     if (!empty($query->_returnProperties['pledge_payment_paid_date'])) {
 159       $query->_select['pledge_payment_paid_date'] = 'payment_contribution.receive_date as pledge_payment_paid_date';
 160       $query->_element['pledge_payment_paid_date'] = 1;
 161       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 162       $query->_tables['payment_contribution'] = $query->_whereTables['payment_contribution'] = 1;
 163     }
 164
 165     if (!empty($query->_returnProperties['pledge_payment_reminder_date'])) {
 166       $query->_select['pledge_payment_reminder_date'] = 'civicrm_pledge_payment.reminder_date as pledge_payment_reminder_date';
 167       $query->_element['pledge_payment_reminder_date'] = 1;
 168       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 169     }
 170
 171     if (!empty($query->_returnProperties['pledge_payment_reminder_count'])) {
 172       $query->_select['pledge_payment_reminder_count'] = 'civicrm_pledge_payment.reminder_count as pledge_payment_reminder_count';
 173       $query->_element['pledge_payment_reminder_count'] = 1;
 174       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 175     }
 176
 177     if (!empty($query->_returnProperties['pledge_payment_status_id'])) {
 178       $query->_select['pledge_payment_status_id'] = 'payment_status.name as pledge_payment_status_id';
 179       $query->_element['pledge_payment_status_id'] = 1;
 180       $query->_tables['payment_status'] = $query->_whereTables['payment_status'] = 1;
 181       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 182     }
 183
 184     if (!empty($query->_returnProperties['pledge_payment_status'])) {
 185       $query->_select['pledge_payment_status'] = 'payment_status.label as pledge_payment_status';
 186       $query->_element['pledge_payment_status'] = 1;
 187       $query->_tables['payment_status'] = $query->_whereTables['payment_status'] = 1;
 188       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 189     }
 190
 191     if (!empty($query->_returnProperties['pledge_frequency_interval'])) {
 192       $query->_select['pledge_frequency_interval'] = 'civicrm_pledge.frequency_interval as pledge_frequency_interval';
 193       $query->_element['pledge_frequency_interval'] = 1;
 194       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 195     }
 196
 197     if (!empty($query->_returnProperties['pledge_frequency_unit'])) {
 198       $query->_select['pledge_frequency_unit'] = 'civicrm_pledge.frequency_unit as pledge_frequency_unit';
 199       $query->_element['pledge_frequency_unit'] = 1;
 200       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 201     }
 202
 203     if (!empty($query->_returnProperties['pledge_is_test'])) {
 204       $query->_select['pledge_is_test'] = 'civicrm_pledge.is_test as pledge_is_test';
 205       $query->_element['pledge_is_test'] = 1;
 206       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 207     }
 208     if (!empty($query->_returnProperties['pledge_campaign_id'])) {
 209       $query->_select['pledge_campaign_id'] = 'civicrm_pledge.campaign_id as pledge_campaign_id';
 210       $query->_element['pledge_campaign_id'] = 1;
 211       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 212     }
 213
 214     if (!empty($query->_returnProperties['pledge_currency'])) {
 215       $query->_select['pledge_currency'] = 'civicrm_pledge.currency as pledge_currency';
 216       $query->_element['pledge_currency'] = 1;
 217       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 218     }
 219   }
 220
 221   /**
 222    * @param $query
 223    */
 224   public static function where(&$query) {
 225     $grouping = NULL;
 226     foreach (array_keys($query->_params) as $id) {
 227       if (empty($query->_params[$id][0])) {
 228         continue;
 229       }
 230       if (substr($query->_params[$id][0], 0, 7) == 'pledge_') {
 231         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 232           $query->_useDistinct = TRUE;
 233         }
 234         $grouping = $query->_params[$id][3];
 235         self::whereClauseSingle($query->_params[$id], $query);
 236       }
 237     }
 238   }
 239
 240   /**
 241    * @param $values
 242    * @param $query
 243    */
 244   public static function whereClauseSingle(&$values, &$query) {
 245     list($name, $op, $value, $grouping, $wildcard) = $values;
 246
 247     switch ($name) {
 248       case 'pledge_create_date_low':
 249       case 'pledge_create_date_high':
 250         // process to / from date
 251         $query->dateQueryBuilder($values,
 252           'civicrm_pledge', 'pledge_create_date', 'create_date', 'Pledge Made'
 253         );
 254       case 'pledge_start_date_low':
 255       case 'pledge_start_date_high':
 256         // process to / from date
 257         $query->dateQueryBuilder($values,
 258           'civicrm_pledge', 'pledge_start_date', 'start_date', 'Pledge Start Date'
 259         );
 260         return;
 261
 262       case 'pledge_end_date_low':
 263       case 'pledge_end_date_high':
 264         // process to / from date
 265         $query->dateQueryBuilder($values,
 266           'civicrm_pledge', 'pledge_end_date', 'end_date', 'Pledge End Date'
 267         );
 268         return;
 269
 270       case 'pledge_payment_date_low':
 271       case 'pledge_payment_date_high':
 272         // process to / from date
 273         $query->dateQueryBuilder($values,
 274           'civicrm_pledge_payment', 'pledge_payment_date', 'scheduled_date', 'Payment Scheduled'
 275         );
 276         return;
 277
 278       case 'pledge_amount':
 279       case 'pledge_amount_low':
 280       case 'pledge_amount_high':
 281         // process min/max amount
 282         $query->numberRangeBuilder($values,
 283           'civicrm_pledge', 'pledge_amount', 'amount', 'Pledge Amount'
 284         );
 285         return;
 286
 287       case 'pledge_installments_low':
 288       case 'pledge_installments_high':
 289         // process min/max amount
 290         $query->numberRangeBuilder($values,
 291           'civicrm_pledge', 'pledge_installments', 'installments', 'Number of Installments'
 292         );
 293         return;
 294
 295       case 'pledge_acknowledge_date_is_not_null':
 296         if ($value) {
 297           $op = "IS NOT NULL";
 298           $query->_qill[$grouping][] = ts('Pledge Acknowledgement Sent');
 299         }
 300         else {
 301           $op = "IS NULL";
 302           $query->_qill[$grouping][] = ts('Pledge Acknowledgement  Not Sent');
 303         }
 304         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.acknowledge_date", $op);
 305         return;
 306
 307       case 'pledge_payment_status_id':
 308       case 'pledge_status_id':
 309         if ($name == 'pledge_status_id') {
 310           $tableName = 'civicrm_pledge';
 311           $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 312           $label = "Pledge Status";
 313           $qillDAO = 'CRM_Pledge_DAO_Pledge';
 314           $qillField = 'status_id';
 315         }
 316         else {
 317           $tableName = 'civicrm_pledge_payment';
 318           $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 319           $label = "Pledge Payment Status";
 320           $qillDAO = 'CRM_Contribute_DAO_Contribution';
 321           $qillField = 'contribution_status_id';
 322         }
 323         $name = 'status_id';
 324         if (!empty($value) && is_array($value) && !in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 325           $value = array('IN' => $value);
 326         }
 327
 328         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("$tableName.$name",
 329           $op,
 330           $value,
 331           'Integer'
 332         );
 333         list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($qillDAO, $qillField, $value, $op);
 334         $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $qillop, 3 => $qillVal));
 335         return;
 336
 337       case 'pledge_test':
 338       case 'pledge_is_test':
 339         // We dont want to include all tests for sql OR CRM-7827
 340         if (!$value || $query->getOperator() != 'OR') {
 341           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.is_test',
 342             $op,
 343             $value,
 344             'Boolean'
 345           );
 346           if ($value) {
 347             $query->_qill[$grouping][] = ts('Pledge is a Test');
 348           }
 349           $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 350         }
 351         return;
 352
 353       case 'pledge_financial_type_id':
 354         $type = CRM_Contribute_PseudoConstant::financialType($value);
 355         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.financial_type_id',
 356           $op,
 357           $value,
 358           'Integer'
 359         );
 360         $query->_qill[$grouping][] = ts('Financial Type - %1', array(1 => $type));
 361         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 362         return;
 363
 364       case 'pledge_contribution_page_id':
 365         $page = CRM_Contribute_PseudoConstant::contributionPage($value);
 366         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.contribution_page_id',
 367           $op,
 368           $value,
 369           'Integer'
 370         );
 371         $query->_qill[$grouping][] = ts('Financial Page - %1', array(1 => $page));
 372         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 373         return;
 374
 375       case 'pledge_id':
 376         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.id",
 377           $op,
 378           $value,
 379           "Integer"
 380         );
 381         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 382         return;
 383
 384       case 'pledge_frequency_interval':
 385         $query->_where[$grouping][] = "civicrm_pledge.frequency_interval $op $value";
 386         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 387         return;
 388
 389       case 'pledge_frequency_unit':
 390         $query->_where[$grouping][] = "civicrm_pledge.frequency_unit $op $value";
 391         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 392         return;
 393
 394       case 'pledge_contact_id':
 395       case 'pledge_campaign_id':
 396         $name = str_replace('pledge_', '', $name);
 397         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.$name", $op, $value, 'Integer');
 398         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Pledge_DAO_Pledge', $name, $value, $op);
 399         $label = ($name == 'campaign_id') ? 'Campaign' : 'Contact ID';
 400         $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $op, 3 => $value));
 401         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 402         return;
 403     }
 404   }
 405
 406   /**
 407    * From clause.
 408    *
 409    * @param string $name
 410    * @param string $mode
 411    * @param string $side
 412    *
 413    * @return null|string
 414    */
 415   public static function from($name, $mode, $side) {
 416     $from = NULL;
 417
 418     switch ($name) {
 419       case 'civicrm_pledge':
 420         $from = " $side JOIN civicrm_pledge  ON civicrm_pledge.contact_id = contact_a.id ";
 421         break;
 422
 423       case 'pledge_status':
 424         $from .= " $side JOIN civicrm_option_group option_group_pledge_status ON (option_group_pledge_status.name = 'pledge_status')";
 425         $from .= " $side JOIN civicrm_option_value pledge_status ON (civicrm_pledge.status_id = pledge_status.value AND option_group_pledge_status.id = pledge_status.option_group_id ) ";
 426         break;
 427
 428       case 'pledge_financial_type':
 429         $from .= " $side JOIN civicrm_financial_type ON civicrm_pledge.financial_type_id = civicrm_financial_type.id ";
 430         break;
 431
 432       case 'civicrm_pledge_payment':
 433         $from .= " $side JOIN civicrm_pledge_payment  ON civicrm_pledge_payment.pledge_id = civicrm_pledge.id ";
 434         break;
 435
 436       case 'payment_contribution':
 437         $from .= " $side JOIN civicrm_contribution payment_contribution ON civicrm_pledge_payment.contribution_id  = payment_contribution.id ";
 438         break;
 439
 440       case 'payment_status':
 441         $from .= " $side JOIN civicrm_option_group option_group_payment_status ON (option_group_payment_status.name = 'contribution_status')";
 442         $from .= " $side JOIN civicrm_option_value payment_status ON (civicrm_pledge_payment.status_id = payment_status.value AND option_group_payment_status.id = payment_status.option_group_id ) ";
 443         break;
 444     }
 445
 446     return $from;
 447   }
 448
 449   /**
 450    * Ideally this function should include fields that are displayed in the selector.
 451    *
 452    * @param int $mode
 453    * @param bool $includeCustomFields
 454    *
 455    * @return array|null
 456    */
 457   public static function defaultReturnProperties(
 458     $mode,
 459     $includeCustomFields = TRUE
 460   ) {
 461     $properties = NULL;
 462
 463     if ($mode & CRM_Contact_BAO_Query::MODE_PLEDGE) {
 464       $properties = array(
 465         'contact_type' => 1,
 466         'contact_sub_type' => 1,
 467         'sort_name' => 1,
 468         'display_name' => 1,
 469         'pledge_id' => 1,
 470         'pledge_amount' => 1,
 471         'pledge_total_paid' => 1,
 472         'pledge_create_date' => 1,
 473         'pledge_start_date' => 1,
 474         'pledge_next_pay_date' => 1,
 475         'pledge_next_pay_amount' => 1,
 476         'pledge_status' => 1,
 477         'pledge_status_id' => 1,
 478         'pledge_is_test' => 1,
 479         'pledge_contribution_page_id' => 1,
 480         'pledge_financial_type' => 1,
 481         'pledge_frequency_interval' => 1,
 482         'pledge_frequency_unit' => 1,
 483         'pledge_currency' => 1,
 484         'pledge_campaign_id' => 1,
 485       );
 486     }
 487     return $properties;
 488   }
 489
 490   /**
 491    * This includes any extra fields that might need for export etc.
 492    *
 493    * @param string $mode
 494    *
 495    * @return array|null
 496    */
 497   public static function extraReturnProperties($mode) {
 498     $properties = NULL;
 499
 500     if ($mode & CRM_Contact_BAO_Query::MODE_PLEDGE) {
 501       $properties = array(
 502         'pledge_balance_amount' => 1,
 503         'pledge_payment_id' => 1,
 504         'pledge_payment_scheduled_amount' => 1,
 505         'pledge_payment_scheduled_date' => 1,
 506         'pledge_payment_paid_amount' => 1,
 507         'pledge_payment_paid_date' => 1,
 508         'pledge_payment_reminder_date' => 1,
 509         'pledge_payment_reminder_count' => 1,
 510         'pledge_payment_status_id' => 1,
 511         'pledge_payment_status' => 1,
 512       );
 513
 514       // also get all the custom pledge properties
 515       $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Pledge');
 516       if (!empty($fields)) {
 517         foreach ($fields as $name => $dontCare) {
 518           $properties[$name] = 1;
 519         }
 520       }
 521     }
 522     return $properties;
 523   }
 524
 525   /**
 526    * @param CRM_Core_Form $form
 527    */
 528   public static function buildSearchForm(&$form) {
 529     // pledge related dates
 530     CRM_Core_Form_Date::buildDateRange($form, 'pledge_start_date', 1, '_low', '_high', ts('From'), FALSE);
 531     CRM_Core_Form_Date::buildDateRange($form, 'pledge_end_date', 1, '_low', '_high', ts('From'), FALSE);
 532     CRM_Core_Form_Date::buildDateRange($form, 'pledge_create_date', 1, '_low', '_high', ts('From'), FALSE);
 533
 534     // pledge payment related dates
 535     CRM_Core_Form_Date::buildDateRange($form, 'pledge_payment_date', 1, '_low', '_high', ts('From'), FALSE);
 536
 537     $form->addYesNo('pledge_test', ts('Pledge is a Test?'), TRUE);
 538     $form->add('text', 'pledge_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 539     $form->addRule('pledge_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 540
 541     $form->add('text', 'pledge_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 542     $form->addRule('pledge_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 543
 544     $form->add('select', 'pledge_status_id',
 545       ts('Pledge Status'), CRM_Pledge_BAO_Pledge::buildOptions('status_id'),
 546       FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
 547     );
 548
 549     $form->addYesNo('pledge_acknowledge_date_is_not_null', ts('Acknowledgement sent?'), TRUE);
 550
 551     $form->add('text', 'pledge_installments_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 552     $form->addRule('pledge_installments_low', ts('Please enter a number'), 'integer');
 553
 554     $form->add('text', 'pledge_installments_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 555     $form->addRule('pledge_installments_high', ts('Please enter number.'), 'integer');
 556
 557     $form->add('select', 'pledge_payment_status_id',
 558       ts('Pledge Payment Status'), CRM_Pledge_BAO_PledgePayment::buildOptions('status_id'),
 559       FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
 560     );
 561
 562     $form->add('select', 'pledge_financial_type_id',
 563       ts('Financial Type'),
 564       array('' => ts('- select -')) + CRM_Contribute_PseudoConstant::financialType(),
 565       FALSE, array('class' => 'crm-select2')
 566     );
 567
 568     $form->add('select', 'pledge_contribution_page_id',
 569       ts('Contribution Page'),
 570       array('' => ts('- any -')) + CRM_Contribute_PseudoConstant::contributionPage(),
 571       FALSE, array('class' => 'crm-select2')
 572     );
 573
 574     // add fields for pledge frequency
 575     $form->add('text', 'pledge_frequency_interval', ts('Every'), array('size' => 8, 'maxlength' => 8));
 576     $form->addRule('pledge_frequency_interval', ts('Please enter valid Pledge Frequency Interval'), 'integer');
 577     $frequencies = CRM_Core_OptionGroup::values('recur_frequency_units');
 578     foreach ($frequencies as $val => $label) {
 579       $freqUnitsDisplay["'{$val}'"] = ts('%1(s)', array(1 => $label));
 580     }
 581
 582     $form->add('select', 'pledge_frequency_unit',
 583       ts('Pledge Frequency'),
 584       array('' => ts('- any -')) + $freqUnitsDisplay
 585     );
 586
 587     self::addCustomFormFields($form, array('Pledge'));
 588
 589     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'pledge_campaign_id');
 590
 591     $form->assign('validCiviPledge', TRUE);
 592     $form->setDefaults(array('pledge_test' => 0));
 593   }
 594
 595   /**
 596    * @param $tables
 597    */
 598   public static function tableNames(&$tables) {
 599     // add status table
 600     if (!empty($tables['pledge_status']) || !empty($tables['civicrm_pledge_payment'])) {
 601       $tables = array_merge(array('civicrm_pledge' => 1), $tables);
 602     }
 603   }
 604
 605 }