CRM/Grant/BAO/Query.php

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