CRM/Grant/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_Grant_BAO_Query {
  37   /**
  38    * @return array
  39    */
  40   public 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    */
  53   public static function select(&$query) {
  54     if (($query->_mode & CRM_Contact_BAO_Query::MODE_GRANT) || !empty($query->_returnProperties)) {
  55       if (!empty($query->_returnProperties['grant_status_id'])) {
  56         $query->_select['grant_status_id'] = 'grant_status.id as grant_status_id';
  57         $query->_element['grant_status'] = 1;
  58         $query->_tables['grant_status'] = $query->_whereTables['grant_status'] = 1;
  59         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  60       }
  61
  62       if (!empty($query->_returnProperties['grant_status'])) {
  63         $query->_select['grant_status'] = 'grant_status.label as grant_status';
  64         $query->_element['grant_status'] = 1;
  65         $query->_tables['grant_status'] = $query->_whereTables['grant_status'] = 1;
  66         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  67       }
  68
  69       if (!empty($query->_returnProperties['grant_type_id'])) {
  70         $query->_select['grant_type_id'] = 'grant_type.id as grant_type_id';
  71         $query->_element['grant_type'] = 1;
  72         $query->_tables['grant_type'] = $query->_whereTables['grant_type'] = 1;
  73         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  74       }
  75
  76       if (!empty($query->_returnProperties['grant_type'])) {
  77         $query->_select['grant_type'] = 'grant_type.label as grant_type';
  78         $query->_element['grant_type'] = 1;
  79         $query->_tables['grant_type'] = $query->_whereTables['grant_type'] = 1;
  80         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
  81       }
  82
  83       if (!empty($query->_returnProperties['grant_note'])) {
  84         $query->_select['grant_note'] = "civicrm_note.note as grant_note";
  85         $query->_element['grant_note'] = 1;
  86         $query->_tables['grant_note'] = 1;
  87       }
  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', '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', '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         $grantTypes = CRM_Core_OptionGroup::values('grant_type');
 189         if (is_array($value)) {
 190           foreach ($value as $k => $v) {
 191             if ($v) {
 192               $val[] = $v;
 193             }
 194           }
 195           if (count($val) > 0) {
 196             // Overwrite $value so it works with an IN where statement.
 197             $op = 'IN';
 198             $value = '(' . implode(',', $val) . ')';
 199           }
 200         }
 201         if (!empty($val)) {
 202           foreach ($val as $id) {
 203             $names[] = CRM_Utils_Array::value($id, $grantTypes);
 204           }
 205         }
 206         else {
 207           if (!empty($value)) {
 208             $names[] = $grantTypes[$value];
 209           }
 210         }
 211
 212         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_grant.grant_type_id', $op, $value, "Integer");
 213
 214         $query->_qill[$grouping][] = ts('Grant Type %2 %1', array(1 => implode(' ' . ts('or') . ' ', $names), 2 => $op));
 215         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 216
 217         return;
 218
 219       case 'grant_status_id':
 220       case 'grant_status':
 221         $grantStatus = CRM_Core_OptionGroup::values('grant_status');
 222         if (is_array($value)) {
 223           foreach ($value as $k => $v) {
 224             if ($v) {
 225               $val[] = $v;
 226             }
 227           }
 228           if (count($val) > 0) {
 229             // Overwrite $value so it works with an IN where statement.
 230             $op = 'IN';
 231             $value = '(' . implode(',', $val) . ')';
 232           }
 233         }
 234         if (!empty($val)) {
 235           foreach ($val as $id) {
 236             $names[] = CRM_Utils_Array::value($id, $grantStatus);
 237           }
 238         }
 239         else {
 240           if (!empty($value)) {
 241             $names[] = $grantStatus[$value];
 242           }
 243         }
 244
 245         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_grant.status_id', $op, $value, "Integer");
 246
 247         $query->_qill[$grouping][] = ts('Grant Status %2 %1', array(1 => implode(' ' . ts('or') . ' ', $names), 2 => $op));
 248         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 249
 250         return;
 251
 252       case 'grant_report_received':
 253
 254         if ($value == 1) {
 255           $yesNo = 'Yes';
 256           $query->_where[$grouping][] = "civicrm_grant.grant_report_received $op $value";
 257         }
 258         elseif ($value == 0) {
 259           $yesNo = 'No';
 260           $query->_where[$grouping][] = "civicrm_grant.grant_report_received IS NULL";
 261         }
 262
 263         $query->_qill[$grouping][] = "Grant Report Received = $yesNo ";
 264         $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
 265
 266         return;
 267
 268       case 'grant_amount':
 269       case 'grant_amount_low':
 270       case 'grant_amount_high':
 271         $query->numberRangeBuilder($values,
 272           'civicrm_grant', 'grant_amount', 'amount_total', 'Total Amount'
 273         );
 274     }
 275   }
 276
 277   /**
 278    * @param string $name
 279    * @param $mode
 280    * @param $side
 281    *
 282    * @return null|string
 283    */
 284   public static function from($name, $mode, $side) {
 285     $from = NULL;
 286     switch ($name) {
 287       case 'civicrm_grant':
 288         $from = " $side JOIN civicrm_grant ON civicrm_grant.contact_id = contact_a.id ";
 289         break;
 290
 291       case 'grant_status':
 292         $from .= " $side JOIN civicrm_option_group option_group_grant_status ON (option_group_grant_status.name = 'grant_status')";
 293         $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 ) ";
 294         break;
 295
 296       case 'grant_type':
 297         $from .= " $side JOIN civicrm_option_group option_group_grant_type ON (option_group_grant_type.name = 'grant_type')";
 298         if ($mode & CRM_Contact_BAO_Query::MODE_GRANT) {
 299           $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 ) ";
 300         }
 301         else {
 302           $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 ) ";
 303         }
 304         break;
 305
 306       case 'grant_note':
 307         $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_grant' AND
 308                                                         civicrm_grant.id = civicrm_note.entity_id )";
 309         break;
 310     }
 311     return $from;
 312   }
 313
 314   /**
 315    * Getter for the qill object
 316    *
 317    * @return string
 318    */
 319   public function qill() {
 320     return (isset($this->_qill)) ? $this->_qill : "";
 321   }
 322
 323   /**
 324    * @param $mode
 325    * @param bool $includeCustomFields
 326    *
 327    * @return array|null
 328    */
 329   static function defaultReturnProperties(
 330     $mode,
 331     $includeCustomFields = TRUE
 332   ) {
 333     $properties = NULL;
 334     if ($mode & CRM_Contact_BAO_Query::MODE_GRANT) {
 335       $properties = array(
 336         'contact_type' => 1,
 337         'contact_sub_type' => 1,
 338         'sort_name' => 1,
 339         'grant_id' => 1,
 340         'grant_type' => 1,
 341         'grant_status' => 1,
 342         'grant_amount_requested' => 1,
 343         'grant_application_received_date' => 1,
 344         'grant_report_received' => 1,
 345         'grant_money_transfer_date' => 1,
 346         'grant_note' => 1,
 347       );
 348     }
 349
 350     return $properties;
 351   }
 352
 353   /**
 354    * Add all the elements shared between grant search and advanaced search
 355    *
 356    *
 357    * @param CRM_Core_Form $form
 358    *
 359    * @return void
 360    * @static
 361    */
 362   public static function buildSearchForm(&$form) {
 363
 364     $grantType = CRM_Core_OptionGroup::values('grant_type');
 365     $form->add('select', 'grant_type_id', ts('Grant Type'), $grantType, FALSE,
 366       array('id' => 'grant_type_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 367     );
 368
 369     $grantStatus = CRM_Core_OptionGroup::values('grant_status');
 370     $form->add('select', 'grant_status_id', ts('Grant Status'), $grantStatus, FALSE,
 371       array('id' => 'grant_status_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 372     );
 373
 374     $form->addDate('grant_application_received_date_low', ts('App. Received Date - From'), FALSE, array('formatType' => 'searchDate'));
 375     $form->addDate('grant_application_received_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 376
 377     $form->addElement('checkbox', 'grant_application_received_notset', ts(''), NULL);
 378
 379     $form->addDate('grant_money_transfer_date_low', ts('Money Sent Date - From'), FALSE, array('formatType' => 'searchDate'));
 380     $form->addDate('grant_money_transfer_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 381
 382     $form->addElement('checkbox', 'grant_money_transfer_date_notset', ts(''), NULL);
 383
 384     $form->addDate('grant_due_date_low', ts('Report Due Date - From'), FALSE, array('formatType' => 'searchDate'));
 385     $form->addDate('grant_due_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 386
 387     $form->addElement('checkbox', 'grant_due_date_notset', ts(''), NULL);
 388
 389     $form->addDate('grant_decision_date_low', ts('Grant Decision Date - From'), FALSE, array('formatType' => 'searchDate'));
 390     $form->addDate('grant_decision_date_high', ts('To'), FALSE, array('formatType' => 'searchDate'));
 391
 392     $form->addElement('checkbox', 'grant_decision_date_notset', ts(''), NULL);
 393
 394     $form->addYesNo('grant_report_received', ts('Grant report received?'), TRUE);
 395
 396     $form->add('text', 'grant_amount_low', ts('Minimum Amount'), array('size' => 8, 'maxlength' => 8));
 397     $form->addRule('grant_amount_low', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('9.99', ' '))), 'money');
 398
 399     $form->add('text', 'grant_amount_high', ts('Maximum Amount'), array('size' => 8, 'maxlength' => 8));
 400     $form->addRule('grant_amount_high', ts('Please enter a valid money value (e.g. %1).', array(1 => CRM_Utils_Money::format('99.99', ' '))), 'money');
 401
 402     // add all the custom  searchable fields
 403     $grant = array('Grant');
 404     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $grant);
 405     if ($groupDetails) {
 406       $form->assign('grantGroupTree', $groupDetails);
 407       foreach ($groupDetails as $group) {
 408         foreach ($group['fields'] as $field) {
 409           $fieldId = $field['id'];
 410           $elementName = 'custom_' . $fieldId;
 411           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 412             $elementName,
 413             $fieldId,
 414             FALSE, FALSE, TRUE
 415           );
 416         }
 417       }
 418     }
 419
 420     $form->assign('validGrant', TRUE);
 421   }
 422
 423   /**
 424    * @param $row
 425    * @param int $id
 426    */
 427   public static function searchAction(&$row, $id) {
 428   }
 429
 430   /**
 431    * @param $tables
 432    */
 433   public static function tableNames(&$tables) {
 434   }
 435 }