CRM/Pledge/BAO/Query.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.6                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Pledge_BAO_Query {
  37   /**
  38    * @return array
  39    */
  40   public static function &getFields() {
  41     $fields = CRM_Pledge_BAO_Pledge::exportableFields();
  42     return $fields;
  43   }
  44
  45   /**
  46    * Build select for Pledge
  47    *
  48    * @param $query
  49    *
  50    * @return void
  51    */
  52   public static function select(&$query) {
  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_create_date'])) {
  67       $query->_select['pledge_create_date'] = 'civicrm_pledge.create_date as pledge_create_date';
  68       $query->_element['pledge_create_date'] = 1;
  69       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  70     }
  71
  72     if (!empty($query->_returnProperties['pledge_status_id'])) {
  73       $query->_select['pledge_status_id'] = 'pledge_status.value as pledge_status_id';
  74       $query->_element['pledge_status'] = 1;
  75       $query->_tables['pledge_status'] = $query->_whereTables['pledge_status'] = 1;
  76       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  77     }
  78
  79     if (!empty($query->_returnProperties['pledge_status'])) {
  80       $query->_select['pledge_status'] = 'pledge_status.label as pledge_status';
  81       $query->_element['pledge_status'] = 1;
  82       $query->_tables['pledge_status'] = $query->_whereTables['pledge_status'] = 1;
  83       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
  84     }
  85
  86     if (!empty($query->_returnProperties['pledge_total_paid'])) {
  87       $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';
  88       $query->_element['pledge_total_paid'] = 1;
  89     }
  90
  91     if (!empty($query->_returnProperties['pledge_next_pay_date'])) {
  92       $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 ( 2, 6 ) ORDER BY civicrm_pledge_payment.scheduled_date ASC LIMIT 0, 1) as pledge_next_pay_date";
  93       $query->_element['pledge_next_pay_date'] = 1;
  94     }
  95
  96     if (!empty($query->_returnProperties['pledge_next_pay_amount'])) {
  97       $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 = 2 ORDER BY civicrm_pledge_payment.scheduled_date ASC LIMIT 0, 1) as pledge_next_pay_amount";
  98       $query->_element['pledge_next_pay_amount'] = 1;
  99
 100       $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";
 101       $query->_element['pledge_outstanding_amount'] = 1;
 102     }
 103
 104     if (!empty($query->_returnProperties['pledge_financial_type'])) {
 105       $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";
 106       $query->_element['pledge_financial_type'] = 1;
 107       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 108     }
 109
 110     if (!empty($query->_returnProperties['pledge_contribution_page_id'])) {
 111       $query->_select['pledge_contribution_page_id'] = 'civicrm_pledge.contribution_page_id as pledge_contribution_page_id';
 112       $query->_element['pledge_contribution_page_id'] = 1;
 113       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 114     }
 115
 116     if (!empty($query->_returnProperties['pledge_payment_id'])) {
 117       $query->_select['pledge_payment_id'] = 'civicrm_pledge_payment.id as pledge_payment_id';
 118       $query->_element['pledge_payment_id'] = 1;
 119       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 120     }
 121
 122     if (!empty($query->_returnProperties['pledge_payment_scheduled_amount'])) {
 123       $query->_select['pledge_payment_scheduled_amount'] = 'civicrm_pledge_payment.scheduled_amount as pledge_payment_scheduled_amount';
 124       $query->_element['pledge_payment_scheduled_amount'] = 1;
 125       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 126     }
 127
 128     if (!empty($query->_returnProperties['pledge_payment_scheduled_date'])) {
 129       $query->_select['pledge_payment_scheduled_date'] = 'civicrm_pledge_payment.scheduled_date as pledge_payment_scheduled_date';
 130       $query->_element['pledge_payment_scheduled_date'] = 1;
 131       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 132     }
 133
 134     if (!empty($query->_returnProperties['pledge_payment_paid_amount'])) {
 135       $query->_select['pledge_payment_paid_amount'] = 'civicrm_pledge_payment.actual_amount as pledge_payment_paid_amount';
 136       $query->_element['pledge_payment_paid_amount'] = 1;
 137       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 138     }
 139
 140     if (!empty($query->_returnProperties['pledge_payment_paid_date'])) {
 141       $query->_select['pledge_payment_paid_date'] = 'payment_contribution.receive_date as pledge_payment_paid_date';
 142       $query->_element['pledge_payment_paid_date'] = 1;
 143       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 144       $query->_tables['payment_contribution'] = $query->_whereTables['payment_contribution'] = 1;
 145     }
 146
 147     if (!empty($query->_returnProperties['pledge_payment_reminder_date'])) {
 148       $query->_select['pledge_payment_reminder_date'] = 'civicrm_pledge_payment.reminder_date as pledge_payment_reminder_date';
 149       $query->_element['pledge_payment_reminder_date'] = 1;
 150       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 151     }
 152
 153     if (!empty($query->_returnProperties['pledge_payment_reminder_count'])) {
 154       $query->_select['pledge_payment_reminder_count'] = 'civicrm_pledge_payment.reminder_count as pledge_payment_reminder_count';
 155       $query->_element['pledge_payment_reminder_count'] = 1;
 156       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 157     }
 158
 159     if (!empty($query->_returnProperties['pledge_payment_status_id'])) {
 160       $query->_select['pledge_payment_status_id'] = 'payment_status.name as pledge_payment_status_id';
 161       $query->_element['pledge_payment_status_id'] = 1;
 162       $query->_tables['payment_status'] = $query->_whereTables['payment_status'] = 1;
 163       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 164     }
 165
 166     if (!empty($query->_returnProperties['pledge_payment_status'])) {
 167       $query->_select['pledge_payment_status'] = 'payment_status.label as pledge_payment_status';
 168       $query->_element['pledge_payment_status'] = 1;
 169       $query->_tables['payment_status'] = $query->_whereTables['payment_status'] = 1;
 170       $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 171     }
 172
 173     if (!empty($query->_returnProperties['pledge_frequency_interval'])) {
 174       $query->_select['pledge_frequency_interval'] = 'civicrm_pledge.frequency_interval as pledge_frequency_interval';
 175       $query->_element['pledge_frequency_interval'] = 1;
 176       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 177     }
 178
 179     if (!empty($query->_returnProperties['pledge_frequency_unit'])) {
 180       $query->_select['pledge_frequency_unit'] = 'civicrm_pledge.frequency_unit as pledge_frequency_unit';
 181       $query->_element['pledge_frequency_unit'] = 1;
 182       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 183     }
 184
 185     if (!empty($query->_returnProperties['pledge_is_test'])) {
 186       $query->_select['pledge_is_test'] = 'civicrm_pledge.is_test as pledge_is_test';
 187       $query->_element['pledge_is_test'] = 1;
 188       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 189     }
 190     if (!empty($query->_returnProperties['pledge_campaign_id'])) {
 191       $query->_select['pledge_campaign_id'] = 'civicrm_pledge.campaign_id as pledge_campaign_id';
 192       $query->_element['pledge_campaign_id'] = 1;
 193       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 194     }
 195
 196     if (!empty($query->_returnProperties['pledge_currency'])) {
 197       $query->_select['pledge_currency'] = 'civicrm_pledge.currency as pledge_currency';
 198       $query->_element['pledge_currency'] = 1;
 199       $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 200     }
 201   }
 202
 203   /**
 204    * @param $query
 205    */
 206   public static function where(&$query) {
 207     $grouping = NULL;
 208     foreach (array_keys($query->_params) as $id) {
 209       if (empty($query->_params[$id][0])) {
 210         continue;
 211       }
 212       if (substr($query->_params[$id][0], 0, 7) == 'pledge_') {
 213         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 214           $query->_useDistinct = TRUE;
 215         }
 216         $grouping = $query->_params[$id][3];
 217         self::whereClauseSingle($query->_params[$id], $query);
 218       }
 219     }
 220   }
 221
 222   /**
 223    * @param $values
 224    * @param $query
 225    */
 226   public static function whereClauseSingle(&$values, &$query) {
 227     list($name, $op, $value, $grouping, $wildcard) = $values;
 228
 229     switch ($name) {
 230       case 'pledge_create_date_low':
 231       case 'pledge_create_date_high':
 232         // process to / from date
 233         $query->dateQueryBuilder($values,
 234           'civicrm_pledge', 'pledge_create_date', 'create_date', 'Pledge Made'
 235         );
 236       case 'pledge_start_date_low':
 237       case 'pledge_start_date_high':
 238         // process to / from date
 239         $query->dateQueryBuilder($values,
 240           'civicrm_pledge', 'pledge_start_date', 'start_date', 'Pledge Start Date'
 241         );
 242         return;
 243
 244       case 'pledge_end_date_low':
 245       case 'pledge_end_date_high':
 246         // process to / from date
 247         $query->dateQueryBuilder($values,
 248           'civicrm_pledge', 'pledge_end_date', 'end_date', 'Pledge End Date'
 249         );
 250         return;
 251
 252       case 'pledge_payment_date_low':
 253       case 'pledge_payment_date_high':
 254         // process to / from date
 255         $query->dateQueryBuilder($values,
 256           'civicrm_pledge_payment', 'pledge_payment_date', 'scheduled_date', 'Payment Scheduled'
 257         );
 258         return;
 259
 260       case 'pledge_amount':
 261       case 'pledge_amount_low':
 262       case 'pledge_amount_high':
 263         // process min/max amount
 264         $query->numberRangeBuilder($values,
 265           'civicrm_pledge', 'pledge_amount', 'amount', 'Pledge Amount'
 266         );
 267         return;
 268
 269       case 'pledge_status_id':
 270         if (is_array($value)) {
 271           foreach ($value as $k => $v) {
 272             if ($v) {
 273               $val[$k] = $k;
 274             }
 275           }
 276
 277           $status = implode(',', $val);
 278
 279           if (count($val) > 0) {
 280             $op = 'IN';
 281             $status = "({$status})";
 282           }
 283         }
 284         else {
 285           $status = $value;
 286         }
 287
 288         $statusValues = CRM_Core_OptionGroup::values('contribution_status');
 289
 290         $names = array();
 291         if (isset($val) && is_array($val)) {
 292           foreach ($val as $id => $dontCare) {
 293             $names[] = $statusValues[$id];
 294           }
 295         }
 296         else {
 297           if (!empty($value) ) {
 298             $names[] = $statusValues[$value];
 299           }
 300         }
 301
 302         $query->_qill[$grouping][] = ts('Pledge Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 303         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.status_id',
 304           $op,
 305           $status,
 306           'Integer'
 307         );
 308         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 309         return;
 310
 311       case 'pledge_payment_status_id':
 312         if (is_array($value)) {
 313           foreach ($value as $k => $v) {
 314             if ($v) {
 315               $val[$k] = $k;
 316             }
 317           }
 318
 319           $status = implode(',', $val);
 320
 321           if (count($val) > 1) {
 322             $op = 'IN';
 323             $status = "({$status})";
 324           }
 325         }
 326         else {
 327           $status = $value;
 328         }
 329
 330         $statusValues = CRM_Core_OptionGroup::values('contribution_status');
 331
 332         $names = array();
 333         if (is_array($val)) {
 334           foreach ($val as $id => $dontCare) {
 335             $names[] = $statusValues[$id];
 336           }
 337         }
 338         else {
 339           if (!empty($value) ) {
 340             $names[] = $statusValues[$value];
 341           }
 342         }
 343
 344         $query->_qill[$grouping][] = ts('Pledge Payment Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 345         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge_payment.status_id',
 346           $op,
 347           $status,
 348           'Integer'
 349         );
 350         $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
 351         return;
 352
 353       case 'pledge_test':
 354       case 'pledge_is_test':
 355         // We dont want to include all tests for sql OR CRM-7827
 356         if (!$value || $query->getOperator() != 'OR') {
 357           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.is_test',
 358             $op,
 359             $value,
 360             'Boolean'
 361           );
 362           if ($value) {
 363             $query->_qill[$grouping][] = ts('Pledge is a Test');
 364           }
 365           $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 366         }
 367         return;
 368
 369       case 'pledge_financial_type_id':
 370         $type = CRM_Contribute_PseudoConstant::financialType($value);
 371         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.financial_type_id',
 372           $op,
 373           $value,
 374           'Integer'
 375         );
 376         $query->_qill[$grouping][] = ts( 'Financial Type - %1', array( 1 => $type ) );
 377         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 378         return;
 379
 380       case 'pledge_contribution_page_id':
 381         $page = CRM_Contribute_PseudoConstant::contributionPage($value);
 382         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.contribution_page_id',
 383           $op,
 384           $value,
 385           'Integer'
 386         );
 387         $query->_qill[$grouping][] = ts('Financial Page - %1', array(1 => $page));
 388         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 389         return;
 390
 391       case 'pledge_id':
 392         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.id",
 393           $op,
 394           $value,
 395           "Integer"
 396         );
 397         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 398         return;
 399
 400       case 'pledge_frequency_interval':
 401         $query->_where[$grouping][] = "civicrm_pledge.frequency_interval $op $value";
 402         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 403         return;
 404
 405       case 'pledge_frequency_unit':
 406         $query->_where[$grouping][] = "civicrm_pledge.frequency_unit $op $value";
 407         $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
 408         return;
 409
 410       case 'pledge_campaign_id':
 411         $campParams = array(
 412           'op' => $op,
 413           'campaign' => $value,
 414           'grouping' => $grouping,
 415           'tableName' => 'civicrm_pledge',
 416         );
 417         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 418         return;
 419     }
 420   }
 421
 422   /**
 423    * @param string $name
 424    * @param $mode
 425    * @param $side
 426    *
 427    * @return null|string
 428    */
 429   public static function from($name, $mode, $side) {
 430     $from = NULL;
 431
 432     switch ($name) {
 433       case 'civicrm_pledge':
 434         $from = " $side JOIN civicrm_pledge  ON civicrm_pledge.contact_id = contact_a.id ";
 435         break;
 436
 437       case 'pledge_status':
 438         $from .= " $side JOIN civicrm_option_group option_group_pledge_status ON (option_group_pledge_status.name = 'contribution_status')";
 439         $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 ) ";
 440         break;
 441
 442       case 'pledge_financial_type':
 443         $from .= " $side JOIN civicrm_financial_type ON civicrm_pledge.financial_type_id = civicrm_financial_type.id ";
 444         break;
 445
 446       case 'civicrm_pledge_payment':
 447         $from .= " $side JOIN civicrm_pledge_payment  ON civicrm_pledge_payment.pledge_id = civicrm_pledge.id ";
 448         break;
 449
 450       case 'payment_contribution':
 451         $from .= " $side JOIN civicrm_contribution payment_contribution ON civicrm_pledge_payment.contribution_id  = payment_contribution.id ";
 452         break;
 453
 454       case 'payment_status':
 455         $from .= " $side JOIN civicrm_option_group option_group_payment_status ON (option_group_payment_status.name = 'contribution_status')";
 456         $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 ) ";
 457         break;
 458     }
 459
 460     return $from;
 461   }
 462
 463   /**
 464    * Getter for the qill object
 465    *
 466    * @return string
 467    */
 468   public function qill() {
 469     return (isset($this->_qill)) ? $this->_qill : "";
 470   }
 471
 472   /**
 473    * Ideally this function should include fields that are displayed in the selector
 474    */
 475   static function defaultReturnProperties(
 476     $mode,
 477     $includeCustomFields = TRUE
 478   ) {
 479     $properties = NULL;
 480
 481     if ($mode & CRM_Contact_BAO_Query::MODE_PLEDGE) {
 482       $properties = array(
 483         'contact_type' => 1,
 484         'contact_sub_type' => 1,
 485         'sort_name' => 1,
 486         'display_name' => 1,
 487         'pledge_id' => 1,
 488         'pledge_amount' => 1,
 489         'pledge_total_paid' => 1,
 490         'pledge_create_date' => 1,
 491         'pledge_next_pay_date' => 1,
 492         'pledge_next_pay_amount' => 1,
 493         'pledge_status' => 1,
 494         'pledge_status_id' => 1,
 495         'pledge_is_test' => 1,
 496         'pledge_contribution_page_id' => 1,
 497         'pledge_financial_type' => 1,
 498         'pledge_frequency_interval' => 1,
 499         'pledge_frequency_unit' => 1,
 500         'pledge_currency' => 1,
 501         'pledge_campaign_id' => 1,
 502       );
 503     }
 504     return $properties;
 505   }
 506
 507   /**
 508    * This includes any extra fields that might need for export etc
 509    */
 510   public static function extraReturnProperties($mode) {
 511     $properties = NULL;
 512
 513     if ($mode & CRM_Contact_BAO_Query::MODE_PLEDGE) {
 514       $properties = array(
 515         'pledge_balance_amount' => 1,
 516         'pledge_payment_id' => 1,
 517         'pledge_payment_scheduled_amount' => 1,
 518         'pledge_payment_scheduled_date' => 1,
 519         'pledge_payment_paid_amount' => 1,
 520         'pledge_payment_paid_date' => 1,
 521         'pledge_payment_reminder_date' => 1,
 522         'pledge_payment_reminder_count' => 1,
 523         'pledge_payment_status_id' => 1,
 524         'pledge_payment_status' => 1,
 525       );
 526
 527       // also get all the custom pledge properties
 528       $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Pledge');
 529       if (!empty($fields)) {
 530         foreach ($fields as $name => $dontCare) {
 531           $properties[$name] = 1;
 532         }
 533       }
 534     }
 535     return $properties;
 536   }
 537
 538   /**
 539    * @param CRM_Core_Form $form
 540    */
 541   public static function buildSearchForm(&$form) {
 542     // pledge related dates
 543     CRM_Core_Form_Date::buildDateRange($form, 'pledge_start_date', 1, '_low', '_high', ts('From'), FALSE);
 544     CRM_Core_Form_Date::buildDateRange($form, 'pledge_end_date', 1, '_low', '_high', ts('From'), FALSE);
 545     CRM_Core_Form_Date::buildDateRange($form, 'pledge_create_date', 1, '_low', '_high', ts('From'), FALSE);
 546
 547     // pledge payment related dates
 548     CRM_Core_Form_Date::buildDateRange($form, 'pledge_payment_date', 1, '_low', '_high', ts('From'), FALSE);
 549
 550     $form->addYesNo('pledge_test', ts('Pledge is a Test?'), TRUE);
 551     $form->add('text', 'pledge_amount_low', ts('From'), array('size' => 8, 'maxlength' => 8));
 552     $form->addRule('pledge_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 553
 554     $form->add('text', 'pledge_amount_high', ts('To'), array('size' => 8, 'maxlength' => 8));
 555     $form->addRule('pledge_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 556
 557     $statusValues = CRM_Contribute_PseudoConstant::contributionStatus();
 558
 559     // Remove status values that are only used for recurring contributions for now (Failed and In Progress).
 560     unset($statusValues['4']);
 561
 562     foreach ($statusValues as $key => $val) {
 563       $status[] = $form->createElement('advcheckbox', $key, NULL, $val);
 564     }
 565
 566     $form->addGroup($status, 'pledge_status_id', ts('Pledge Status'));
 567
 568     //unset in progress for payment
 569     unset($statusValues['5']);
 570
 571     foreach ($statusValues as $key => $val) {
 572       $paymentStatus[] = $form->createElement('advcheckbox', $key, NULL, $val);
 573     }
 574
 575     $form->addGroup($paymentStatus, 'pledge_payment_status_id', ts('Pledge Payment Status'));
 576
 577     $form->add('select', 'pledge_financial_type_id',
 578       ts( 'Financial Type' ),
 579       array('' => ts('- select -')) + CRM_Contribute_PseudoConstant::financialType(),
 580       FALSE, array('class' => 'crm-select2')
 581     );
 582
 583     $form->add('select', 'pledge_contribution_page_id',
 584       ts('Contribution Page'),
 585       array('' => ts('- any -')) + CRM_Contribute_PseudoConstant::contributionPage(),
 586       FALSE, array('class' => 'crm-select2')
 587     );
 588
 589     //add fields for pledge frequency
 590     $form->add('text', 'pledge_frequency_interval', ts('Every'), array('size' => 8, 'maxlength' => 8));
 591     $form->addRule('pledge_frequency_interval', ts('Please enter valid Pledge Frequency Interval'), 'integer');
 592     $frequencies = CRM_Core_OptionGroup::values('recur_frequency_units');
 593     foreach ($frequencies as $val => $label) {
 594       $freqUnitsDisplay["'{$val}'"] = ts('%1(s)', array(1 => $label));
 595     }
 596
 597     $form->add('select', 'pledge_frequency_unit',
 598       ts('Pledge Frequency'),
 599       array('' => ts('- any -')) + $freqUnitsDisplay
 600     );
 601
 602     // add all the custom  searchable fields
 603     $pledge = array('Pledge');
 604     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $pledge);
 605     if ($groupDetails) {
 606       $form->assign('pledgeGroupTree', $groupDetails);
 607       foreach ($groupDetails as $group) {
 608         foreach ($group['fields'] as $field) {
 609           $fieldId = $field['id'];
 610           $elementName = 'custom_' . $fieldId;
 611           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 612             $elementName,
 613             $fieldId,
 614             FALSE, FALSE, TRUE
 615           );
 616         }
 617       }
 618     }
 619
 620     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'pledge_campaign_id');
 621
 622     $form->assign('validCiviPledge', TRUE);
 623     $form->setDefaults(array('pledge_test' => 0));
 624   }
 625
 626   /**
 627    * @param $row
 628    * @param int $id
 629    */
 630   public static function searchAction(&$row, $id) {
 631   }
 632
 633   /**
 634    * @param $tables
 635    */
 636   public static function tableNames(&$tables) {
 637     //add status table
 638     if (!empty($tables['pledge_status']) || !empty($tables['civicrm_pledge_payment'])) {
 639       $tables = array_merge(array('civicrm_pledge' => 1), $tables);
 640     }
 641   }
 642 }