CRM/Grant/BAO/Query.php

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