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