CRM/Grant/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  * $Id$
  33  *
  34  */
  35 class CRM_Grant_BAO_Query extends CRM_Core_BAO_Query {
  36   /**
  37    * @return array
  38    */
  39   public static function &getFields() {
  40     $fields = array();
  41     $fields = CRM_Grant_BAO_Grant::exportableFields();
  42     return $fields;
  43   }
  44
  45   /**
  46    * Build select for CiviGrant.
  47    *
  48    * @param $query
  49    *
  50    * @return void
  51    */
  52   public static function select(&$query) {
  53     if (!empty($query->_returnProperties['grant_status_id'])) {
  54       $query->_select['grant_status_id'] = 'grant_status.id as grant_status_id';
  55       $query->_element['grant_status'] = 1;
  56       $query->_tables['grant_status'] = $query->_whereTables['grant_status'] = 1;
  57       $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  58     }
  59
  60     if (!empty($query->_returnProperties['grant_status'])) {
  61       $query->_select['grant_status'] = 'grant_status.label as grant_status';
  62       $query->_element['grant_status'] = 1;
  63       $query->_tables['grant_status'] = $query->_whereTables['grant_status'] = 1;
  64       $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  65     }
  66
  67     if (!empty($query->_returnProperties['grant_type_id'])) {
  68       $query->_select['grant_type_id'] = 'grant_type.id as grant_type_id';
  69       $query->_element['grant_type'] = 1;
  70       $query->_tables['grant_type'] = $query->_whereTables['grant_type'] = 1;
  71       $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  72     }
  73
  74     if (!empty($query->_returnProperties['grant_type'])) {
  75       $query->_select['grant_type'] = 'grant_type.label as grant_type';
  76       $query->_element['grant_type'] = 1;
  77       $query->_tables['grant_type'] = $query->_whereTables['grant_type'] = 1;
  78       $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  79     }
  80
  81     if (!empty($query->_returnProperties['grant_note'])) {
  82       $query->_select['grant_note'] = "civicrm_note.note as grant_note";
  83       $query->_element['grant_note'] = 1;
  84       $query->_tables['grant_note'] = 1;
  85     }
  86
  87     if ($query->_mode & CRM_Contact_BAO_Query::MODE_GRANT) {
  88       $query->_select['grant_amount_requested'] = 'civicrm_grant.amount_requested as grant_amount_requested';
  89       $query->_select['grant_amount_granted'] = 'civicrm_grant.amount_granted as grant_amount_granted';
  90       $query->_select['grant_amount_total'] = 'civicrm_grant.amount_total as grant_amount_total';
  91       $query->_select['grant_application_received_date'] = 'civicrm_grant.application_received_date as grant_application_received_date ';
  92       $query->_select['grant_report_received'] = 'civicrm_grant.grant_report_received as grant_report_received';
  93       $query->_select['grant_money_transfer_date'] = 'civicrm_grant.money_transfer_date as grant_money_transfer_date';
  94       $query->_element['grant_type_id'] = 1;
  95       $query->_element['grant_status_id'] = 1;
  96       $query->_tables['civicrm_grant'] = 1;
  97       $query->_whereTables['civicrm_grant'] = 1;
  98     }
  99   }
 100
 101   /**
 102    * Given a list of conditions in params generate the required.
 103    * where clause
 104    *
 105    * @param $query
 106    *
 107    * @return void
 108    */
 109   public static function where(&$query) {
 110     foreach ($query->_params as $id => $values) {
 111       if (!is_array($values) || count($values) != 5) {
 112         continue;
 113       }
 114
 115       if (substr($values[0], 0, 6) == 'grant_') {
 116         self::whereClauseSingle($values, $query);
 117       }
 118     }
 119   }
 120
 121   /**
 122    * @param $values
 123    * @param $query
 124    */
 125   public static function whereClauseSingle(&$values, &$query) {
 126     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 127     list($name, $op, $value, $grouping, $wildcard) = $values;
 128     $val = $names = array();
 129     switch ($name) {
 130       case 'grant_money_transfer_date_low':
 131       case 'grant_money_transfer_date_high':
 132         $query->dateQueryBuilder($values, 'civicrm_grant',
 133           'grant_money_transfer_date', 'money_transfer_date',
 134           'Money Transfer Date'
 135         );
 136         return;
 137
 138       case 'grant_money_transfer_date_notset':
 139         $query->_where[$grouping][] = "civicrm_grant.money_transfer_date IS NULL";
 140         $query->_qill[$grouping][] = ts("Grant Money Transfer Date is NULL");
 141         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 142         return;
 143
 144       case 'grant_application_received_date_low':
 145       case 'grant_application_received_date_high':
 146         $query->dateQueryBuilder($values, 'civicrm_grant',
 147           'grant_application_received_date',
 148           'application_received_date', 'Application Received Date'
 149         );
 150         return;
 151
 152       case 'grant_application_received_notset':
 153         $query->_where[$grouping][] = "civicrm_grant.application_received_date IS NULL";
 154         $query->_qill[$grouping][] = ts("Grant Application Received Date is NULL");
 155         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 156         return;
 157
 158       case 'grant_due_date_low':
 159       case 'grant_due_date_high':
 160         $query->dateQueryBuilder($values, 'civicrm_grant',
 161           'grant_due_date',
 162           'grant_due_date', ts('Grant Due Date')
 163         );
 164         return;
 165
 166       case 'grant_due_date_notset':
 167         $query->_where[$grouping][] = "civicrm_grant.grant_due_date IS NULL";
 168         $query->_qill[$grouping][] = ts("Grant Due Date is NULL");
 169         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 170         return;
 171
 172       case 'grant_decision_date_low':
 173       case 'grant_decision_date_high':
 174         $query->dateQueryBuilder($values, 'civicrm_grant',
 175           'grant_decision_date',
 176           'decision_date', ts('Grant Decision Date')
 177         );
 178         return;
 179
 180       case 'grant_decision_date_notset':
 181         $query->_where[$grouping][] = "civicrm_grant.decision_date IS NULL";
 182         $query->_qill[$grouping][] = ts("Grant Decision Date is NULL");
 183         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 184         return;
 185
 186       case 'grant_type_id':
 187       case 'grant_type':
 188       case 'grant_status_id':
 189       case 'grant_status':
 190
 191         if (strstr($name, 'type')) {
 192           $name = 'grant_type_id';
 193           $label = ts('Grant Type(s)');
 194         }
 195         else {
 196           $name = 'status_id';
 197           $label = ts('Grant Status(s)');
 198         }
 199
 200         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_grant.$name", $op, $value, "Integer");
 201
 202         list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Grant_DAO_Grant', $name, $value, $op);
 203         $query->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
 204         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 205
 206         return;
 207
 208       case 'grant_report_received':
 209
 210         if ($value == 1) {
 211           $yesNo = ts('Yes');
 212           $query->_where[$grouping][] = "civicrm_grant.grant_report_received $op $value";
 213         }
 214         elseif ($value == 0) {
 215           $yesNo = ts('No');
 216           $query->_where[$grouping][] = "civicrm_grant.grant_report_received IS NULL";
 217         }
 218
 219         $query->_qill[$grouping][] = ts('Grant Report Received = %1', array(1 => $yesNo));
 220         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 221
 222         return;
 223
 224       case 'grant_amount':
 225       case 'grant_amount_low':
 226       case 'grant_amount_high':
 227         $query->numberRangeBuilder($values,
 228           'civicrm_grant', 'grant_amount', 'amount_total', 'Total Amount'
 229         );
 230     }
 231   }
 232
 233   /**
 234    * @param string $name
 235    * @param $mode
 236    * @param $side
 237    *
 238    * @return null|string
 239    */
 240   public static function from($name, $mode, $side) {
 241     $from = NULL;
 242     switch ($name) {
 243       case 'civicrm_grant':
 244         $from = " $side JOIN civicrm_grant ON civicrm_grant.contact_id = contact_a.id ";
 245         break;
 246
 247       case 'grant_status':
 248         $from .= " $side JOIN civicrm_option_group option_group_grant_status ON (option_group_grant_status.name = 'grant_status')";
 249         $from .= " $side JOIN civicrm_option_value grant_status ON (civicrm_grant.status_id = grant_status.value AND option_group_grant_status.id = grant_status.option_group_id ) ";
 250         break;
 251
 252       case 'grant_type':
 253         $from .= " $side JOIN civicrm_option_group option_group_grant_type ON (option_group_grant_type.name = 'grant_type')";
 254         if ($mode & CRM_Contact_BAO_Query::MODE_GRANT) {
 255           $from .= " INNER JOIN civicrm_option_value grant_type ON (civicrm_grant.grant_type_id = grant_type.value AND option_group_grant_type.id = grant_type.option_group_id ) ";
 256         }
 257         else {
 258           $from .= " $side JOIN civicrm_option_value grant_type ON (civicrm_grant.grant_type_id = grant_type.value AND option_group_grant_type.id = grant_type.option_group_id ) ";
 259         }
 260         break;
 261
 262       case 'grant_note':
 263         $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_grant' AND
 264                                                         civicrm_grant.id = civicrm_note.entity_id )";
 265         break;
 266     }
 267     return $from;
 268   }
 269
 270   /**
 271    * @param $mode
 272    * @param bool $includeCustomFields
 273    *
 274    * @return array|null
 275    */
 276   public static function defaultReturnProperties(
 277     $mode,
 278     $includeCustomFields = TRUE
 279   ) {
 280     $properties = NULL;
 281     if ($mode & CRM_Contact_BAO_Query::MODE_GRANT) {
 282       $properties = array(
 283         'contact_type' => 1,
 284         'contact_sub_type' => 1,
 285         'sort_name' => 1,
 286         'grant_id' => 1,
 287         'grant_type' => 1,
 288         'grant_status' => 1,
 289         'grant_amount_requested' => 1,
 290         'grant_application_received_date' => 1,
 291         'grant_report_received' => 1,
 292         'grant_money_transfer_date' => 1,
 293         'grant_note' => 1,
 294       );
 295     }
 296
 297     return $properties;
 298   }
 299
 300   /**
 301    * Add all the elements shared between grant search and advanaced search.
 302    *
 303    *
 304    * @param CRM_Core_Form $form
 305    *
 306    * @return void
 307    */
 308   public static function buildSearchForm(&$form) {
 309
 310     $grantType = CRM_Core_OptionGroup::values('grant_type');
 311     $form->add('select', 'grant_type_id', ts('Grant Type'), $grantType, FALSE,
 312       array('id' => 'grant_type_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 313     );
 314
 315     $grantStatus = CRM_Core_OptionGroup::values('grant_status');
 316     $form->add('select', 'grant_status_id', ts('Grant Status'), $grantStatus, FALSE,
 317       array('id' => 'grant_status_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 318     );
 319
 320     $form->addDate('grant_application_received_date_low', ts('App. Received Date - From'), FALSE, array('formatType' => 'searchDate'));
 321     $form->addDate('grant_application_received_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 322
 323     $form->addElement('checkbox', 'grant_application_received_notset', ts('Date is not set'), NULL);
 324
 325     $form->addDate('grant_money_transfer_date_low', ts('Money Sent Date - From'), FALSE, array('formatType' => 'searchDate'));
 326     $form->addDate('grant_money_transfer_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 327
 328     $form->addElement('checkbox', 'grant_money_transfer_date_notset', ts('Date is not set'), NULL);
 329
 330     $form->addDate('grant_due_date_low', ts('Report Due Date - From'), FALSE, array('formatType' => 'searchDate'));
 331     $form->addDate('grant_due_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 332
 333     $form->addElement('checkbox', 'grant_due_date_notset', ts('Date is not set'), NULL);
 334
 335     $form->addDate('grant_decision_date_low', ts('Grant Decision Date - From'), FALSE, array('formatType' => 'searchDate'));
 336     $form->addDate('grant_decision_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 337
 338     $form->addElement('checkbox', 'grant_decision_date_notset', ts('Date is not set'), NULL);
 339
 340     $form->addYesNo('grant_report_received', ts('Grant report received?'), TRUE);
 341
 342     $form->add('text', 'grant_amount_low', ts('Minimum Amount'), array('size' => 8, 'maxlength' => 8));
 343     $form->addRule('grant_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 344
 345     $form->add('text', 'grant_amount_high', ts('Maximum Amount'), array('size' => 8, 'maxlength' => 8));
 346     $form->addRule('grant_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 347
 348     self::addCustomFormFields($form, array('Grant'));
 349
 350     $form->assign('validGrant', TRUE);
 351   }
 352
 353 }