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