GrantProgram.php 15.3 KB
Newer Older
1 2 3 4
<?php

/*
 +--------------------------------------------------------------------+
5
 | CiviCRM version 4.4                                                |
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2011                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2011
 * $Id$
 *
 */


/**
 * This class contains  grant program related functions.
 */
41 42 43 44 45 46 47
class CRM_Grant_BAO_GrantProgram extends CRM_Grant_DAO_GrantProgram {
  /**
   * class constructor
   */
  function __construct() {
    parent::__construct();
  }
48

Pradeep Nayak's avatar
Pradeep Nayak committed
49 50 51 52 53 54
  /**
   * grant program status
   *
   * @var array
   * @static
   */
Monish Deb's avatar
Monish Deb committed
55
  private static $grantProgramStatus;
Pradeep Nayak's avatar
Pradeep Nayak committed
56 57


58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
  /**
   * Takes a bunch of params that are needed to match certain criteria and
   * retrieves the relevant objects. It also stores all the retrieved
   * values in the default array
   *
   * @param array $params   (reference ) an assoc array of name/value pairs
   * @param array $defaults (reference ) an assoc array to hold the flattened values
   *
   * @return object CRM_Contribute_DAO_GrantProgram object on success, null otherwise
   * @access public
   * @static
   */
  static function retrieve(&$params, &$defaults) {
    $program = new CRM_Grant_DAO_GrantProgram();
    $program->copyValues($params);
    if ($program->find(TRUE)) {
      CRM_Core_DAO::storeValues($program, $defaults);
      return $program;
76
    }
77 78
    return NULL;
  }
79

80 81
  /**
   * Function  to delete Grant Program
Monish Deb's avatar
Monish Deb committed
82
   *
83
   * @param  int  $grantProgramID     ID of the par service fee to be deleted.
Monish Deb's avatar
Monish Deb committed
84
   *
85 86 87 88 89 90
   * @access public
   * @static
   */
  static function del($grantProgramID) {
    if (!$grantProgramID) {
      CRM_Core_Error::fatal(ts('Invalid value passed to delete function'));
91
    }
Monish Deb's avatar
Monish Deb committed
92

93 94 95 96
    $dao = new CRM_Grant_DAO_GrantProgram();
    $dao->id = $grantProgramID;
    if (!$dao->find(TRUE)) {
      return NULL;
97
    }
98 99
    $dao->delete();
  }
100

101
  static function getOptionValueID($optioGroupID, $value) {
Mayur Jadhav's avatar
Mayur Jadhav committed
102
    $query = "SELECT id FROM civicrm_option_value WHERE  option_group_id = {$optioGroupID} AND value = {$value}";
103 104
    return CRM_Core_DAO::singleValueQuery($query);
  }
Monish Deb's avatar
Monish Deb committed
105

106
  static function getOptionValue($id) {
Mayur Jadhav's avatar
Mayur Jadhav committed
107
    $query = "SELECT value FROM civicrm_option_value WHERE id = {$id}";
108 109
    return CRM_Core_DAO::singleValueQuery($query);
  }
110

111
  static function getOptionName ($id) {
Mayur Jadhav's avatar
Mayur Jadhav committed
112
    $query = "SELECT label FROM civicrm_option_value WHERE id = {$id}";
113 114
    return CRM_Core_DAO::singleValueQuery($query);
  }
115

116
  static function grantPrograms($id = NULL) {
117
    $where = 'WHERE is_active = 1';
118
    if (!empty($id)) {
119
      $where .= " AND id = {$id}";
120
    }
121 122 123 124 125
    $query = "SELECT id, label FROM civicrm_grant_program {$where}";
    $dao = CRM_Core_DAO::executeQuery($query);
    while ($dao->fetch()) {
      if (!empty($id)) {
        $grantPrograms = $dao->label;
Monish Deb's avatar
Monish Deb committed
126
      }
127 128
      else {
        $grantPrograms[$dao->id] = $dao->label;
129
      }
130
    }
131
    return empty($grantPrograms) ? array() : $grantPrograms;
Monish Deb's avatar
Monish Deb committed
132
  }
133 134 135 136 137 138 139 140 141

  static function contributionTypes() {
    $typeDao = new CRM_Financial_DAO_FinancialType();
    $typeDao->find();
    while ($typeDao->fetch()) {
      $contributionTypes[$typeDao->id] = $typeDao->name;
    }
    return $contributionTypes;
  }
Monish Deb's avatar
Monish Deb committed
142

143 144 145 146
  static function create(&$params, &$ids) {
    if (empty($params)) {
      return;
    }
Monish Deb's avatar
Monish Deb committed
147
    $moneyFields = array(
148
      'total_amount',
Monish Deb's avatar
Monish Deb committed
149
      'remainder_amount'
150 151 152 153
    );
    foreach ($moneyFields as $field) {
      if (isset($params[$field])) {
        $params[$field] = CRM_Utils_Rule::cleanMoney($params[$field]);
154
      }
155 156 157
    }
    // convert dates to mysql format
    $dates = array('allocation_date');
Monish Deb's avatar
Monish Deb committed
158

159 160 161
    foreach ($dates as $date) {
      if (isset( $params[$date])) {
        $params[$date] = CRM_Utils_Date::processDate($params[$date], NULL, TRUE);
162
      }
163 164 165
    }
    $grantProgram = new CRM_Grant_DAO_GrantProgram();
    $grantProgram->id = CRM_Utils_Array::value('grant_program', $ids);
Monish Deb's avatar
Monish Deb committed
166

167
    $grantProgram->copyValues($params);
Monish Deb's avatar
Monish Deb committed
168

169 170
    return $result = $grantProgram->save();
  }
171

172 173 174 175
  public function getDisplayName($id) {
    $sql = "SELECT display_name FROM civicrm_contact WHERE civicrm_contact.id = $id ";
    return CRM_Core_DAO::singleValueQuery($sql);
  }
Monish Deb's avatar
Monish Deb committed
176 177


Stan Dragnev's avatar
Stan Dragnev committed
178
  public function getAddress($id, $locationTypeID = NULL, $twoLines = false) {
179
    $sql = "
180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196
   SELECT civicrm_contact.id as contact_id,
          civicrm_address.street_address as street_address,
          civicrm_address.supplemental_address_1 as supplemental_address_1,
          civicrm_address.supplemental_address_2 as supplemental_address_2,
          civicrm_address.city as city,
          civicrm_address.postal_code as postal_code,
          civicrm_address.postal_code_suffix as postal_code_suffix,
          civicrm_state_province.abbreviation as state,
          civicrm_country.name as country,
          civicrm_location_type.name as location_type
     FROM civicrm_contact
LEFT JOIN civicrm_address ON civicrm_address.contact_id = civicrm_contact.id
LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
LEFT JOIN civicrm_location_type ON civicrm_location_type.id = civicrm_address.location_type_id
WHERE civicrm_contact.id = $id ";

197 198 199
    $params = array();
    if (!$locationTypeID) {
      $sql .= " AND civicrm_address.is_primary = 1";
Monish Deb's avatar
Monish Deb committed
200
    }
201 202 203
    else {
      $sql .= " AND civicrm_address.location_type_id = %1";
      $params[1] = array($locationTypeID, 'Integer');
204
    }
205 206 207 208 209
    $dao = CRM_Core_DAO::executeQuery($sql, $params);
    $location = array();
    $config = CRM_Core_Config::singleton();
    while ($dao->fetch()) {
      $address = '';
Stan Dragnev's avatar
Stan Dragnev committed
210 211 212 213 214 215 216 217 218 219 220 221 222 223
      if ($twoLines) {
      	CRM_Utils_String::append(
      	$address, ' ',
      	array(
	      	$dao->street_address,
	      	$dao->supplemental_address_1,
	      	$dao->supplemental_address_2,
	      	'<br />',
	      	$dao->city,
	      	$dao->state,
	      	$dao->postal_code
	      	)
      	);
      } else {
Monish Deb's avatar
Monish Deb committed
224
	      CRM_Utils_String::append(
Stan Dragnev's avatar
Stan Dragnev committed
225
	        $address, ', ',
Monish Deb's avatar
Monish Deb committed
226
	        array(
Stan Dragnev's avatar
Stan Dragnev committed
227 228 229 230 231 232 233
	          $dao->street_address,
	          $dao->supplemental_address_1,
	          $dao->supplemental_address_2,
	          $dao->city,
	          $dao->state,
	          $dao->postal_code,
	          $dao->country
Monish Deb's avatar
Monish Deb committed
234
	        )
Stan Dragnev's avatar
Stan Dragnev committed
235 236
	      );
      }
237
      $location['address'] = addslashes($address);
Monish Deb's avatar
Monish Deb committed
238
    }
239
    return $location;
Monish Deb's avatar
Monish Deb committed
240
  }
Pradeep Nayak's avatar
Pradeep Nayak committed
241 242 243 244 245 246 247 248 249 250 251 252 253 254
  /**
   * Get all the n grant program statuses
   *
   * @access public
   * @return array - array reference of all grant program statuses if any
   * @static
   */
  public static function &grantProgramStatus($id = NULL) {
    if (!self::$grantProgramStatus) {
      self::$grantProgramStatus = array();
      self::$grantProgramStatus = CRM_Core_OptionGroup::values('grant_program_status');
    }
    if($id) {
      return self::$grantProgramStatus[$id];
Monish Deb's avatar
Monish Deb committed
255
    }
Pradeep Nayak's avatar
Pradeep Nayak committed
256
    return self::$grantProgramStatus;
257
  }
Monish Deb's avatar
Monish Deb committed
258 259


Pradeep Nayak's avatar
Pradeep Nayak committed
260 261 262 263 264 265
  static function getGrantPrograms($id = NULL) {
    $grantPrograms = array();
    $where = ' WHERE is_active = 1';
    if (!empty($id)) {
      $where .= " AND id = {$id}";
    }
266
    $query = "SELECT id, name FROM civicrm_grant_program " . $where;
Pradeep Nayak's avatar
Pradeep Nayak committed
267 268 269 270 271 272
    $dao = CRM_Core_DAO::executeQuery($query);
    while ($dao->fetch()) {
      $grantPrograms[$dao->id] = $dao->name;
    }
    return $grantPrograms;
  }
Monish Deb's avatar
Monish Deb committed
273

Pradeep Nayak's avatar
Pradeep Nayak committed
274
  static function getGrants($params) {
Edselopez's avatar
Edselopez committed
275
    $grants = array();
Pradeep Nayak's avatar
Pradeep Nayak committed
276
    if (!empty($params)) {
Monish Deb's avatar
Monish Deb committed
277
      $where = "WHERE ";
Pradeep Nayak's avatar
Pradeep Nayak committed
278 279 280
      foreach ($params as $key => $value) {
        if ($key == 'status_id') {
          $where .= "{$key} IN ( {$value} ) AND ";
Monish Deb's avatar
Monish Deb committed
281
        }
Pradeep Nayak's avatar
Pradeep Nayak committed
282 283 284
        else {
          if (strstr($value, 'NULL')) {
            $where .= "{$key} IS {$value} AND ";
Monish Deb's avatar
Monish Deb committed
285
          }
Pradeep Nayak's avatar
Pradeep Nayak committed
286 287 288 289 290 291 292 293 294 295 296 297 298 299 300
          else {
            $where .= "{$key} = '{$value}' AND ";
          }
        }
      }
      $where = rtrim($where ," AND ");
      $query = "SELECT * FROM civicrm_grant {$where} ORDER BY application_received_date ASC";
      $dao = CRM_Core_DAO::executeQuery($query);
      while ($dao->fetch()) {
        $grants[$dao->id]['assessment'] = $dao->assessment;
        $grants[$dao->id]['amount_total'] = $dao->amount_total;
        $grants[$dao->id]['amount_requested'] = $dao->amount_requested;
        $grants[$dao->id]['amount_granted'] = $dao->amount_granted;
        $grants[$dao->id]['status_id'] = $dao->status_id;
        $grants[$dao->id]['contact_id'] = $dao->contact_id;
Monish Deb's avatar
Monish Deb committed
301
        $grants[$dao->id]['grant_id'] = $dao->id;
Pradeep Nayak's avatar
Pradeep Nayak committed
302 303 304 305
      }
    }
    return $grants;
  }
Monish Deb's avatar
Monish Deb committed
306

Pradeep Nayak's avatar
Pradeep Nayak committed
307
  static function sendMail($contactID, &$values, $grantStatus, $grantId = FALSE, $status = '') {
308
    $value = array();
Pradeep Nayak's avatar
Pradeep Nayak committed
309 310 311
    if (CRM_Utils_Array::value('is_auto_email', $values)) {
      list($displayName, $email) = CRM_Contact_BAO_Contact_Location::getEmailDetails($contactID);
      if (isset($email)) {
312 313 314 315 316
        $grantStatuses = CRM_Core_OptionGroup::values('grant_status', FALSE, FALSE, FALSE, NULL, 'name');
        $grantStatuses = $grantStatuses[$values['status_id']];
        $valueName = strtolower($grantStatuses);
        if ($grantStatuses == 'Awaiting Information') {
          $explode = explode(' ', $grantStatuses);
Pradeep Nayak's avatar
Pradeep Nayak committed
317
          $valueName = strtolower($explode[0]) . '_info';
Monish Deb's avatar
Monish Deb committed
318
        }
319
        elseif (strstr($grantStatuses, 'Approved')) {
Mayur Jadhav's avatar
Mayur Jadhav committed
320
          $valueName = strtolower('Approved');
Pradeep Nayak's avatar
Pradeep Nayak committed
321 322 323 324 325
        }
        $sendTemplateParams = array(
          'groupName' => 'msg_tpl_workflow_grant',
          'valueName' => 'grant_'.$valueName,
          'contactId' => $contactID,
Monish Deb's avatar
Monish Deb committed
326
          'tplParams' => array_merge(['email' => $email], CRM_Utils_Array::value('tplParams', $params, [])),
Pradeep Nayak's avatar
Pradeep Nayak committed
327 328
          'PDFFilename' => '',
        );
329 330 331 332 333 334 335 336 337

        $emailQuery = "SELECT from_email_address FROM civicrm_grant_program WHERE id = %1";
        $defaultAddress = CRM_Core_DAO::singleValueQuery($emailQuery, array(
          1 => array($values['grant_program_id'], 'Integer')
        ));

        if (!isset($defaultAddress) || $defaultAddress == '') {
          $defaultAddress = CRM_Core_OptionGroup::values('from_email_address', NULL, NULL, NULL, ' AND is_default = 1');

338 339 340 341 342
          foreach ($defaultAddress as $id => $value) {
            $sendTemplateParams['from'] = $value;
          }
        } else {
          $sendTemplateParams['from'] = $defaultAddress;
343 344
        }

Pradeep Nayak's avatar
Pradeep Nayak committed
345 346 347
        $sendTemplateParams['toName'] = $displayName;
        $sendTemplateParams['toEmail'] = $email;
        $sendTemplateParams['autoSubmitted'] = TRUE;
348 349 350 351 352 353 354 355 356 357 358
        $query = 'SELECT msg_subject subject, msg_text text, msg_html html, pdf_format_id format
                      FROM civicrm_msg_template mt
                      JOIN civicrm_option_value ov ON workflow_id = ov.id
                      JOIN civicrm_option_group og ON ov.option_group_id = og.id
                      WHERE og.name = %1 AND ov.name = %2 AND mt.is_default = 1';
        $sqlParams = array(1 => array($sendTemplateParams['groupName'], 'String'), 2 => array($sendTemplateParams['valueName'], 'String'));
        $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
        $dao->fetch();
        if ($dao->N) {
          CRM_Core_BAO_MessageTemplate::sendTemplate($sendTemplateParams);
        }
Pradeep Nayak's avatar
Pradeep Nayak committed
359
        if ($grantId && $status) {
Pradeep Nayak's avatar
Pradeep Nayak committed
360
          self::createStatusChangeActivity($grantId, $grantStatus, $status, $contactID);
361
        }
Pradeep Nayak's avatar
Pradeep Nayak committed
362 363
      }
    }
Mayur Jadhav's avatar
Mayur Jadhav committed
364
  }
Mayur Jadhav's avatar
Mayur Jadhav committed
365 366 367 368 369 370 371 372 373 374

  /**
   * Function to get sum of amount granted for a Contact
   *
   * @param int $params
   *
   * @return int sum of amount granted
   * @access public
   * @static
   */
375
  static function getUserAllocatedAmount($params, $id = NULL) {
Mayur Jadhav's avatar
Mayur Jadhav committed
376
    $where = NULL;
Mayur Jadhav's avatar
Mayur Jadhav committed
377 378
    if (!empty($params)) {
      foreach ($params as $key => $value) {
Mayur Jadhav's avatar
Mayur Jadhav committed
379 380
        $where .= " AND {$key} = {$value}";
      }
381 382 383
      if (!empty($id)) {
        $where .= " AND id != {$id}";
      }
Mayur Jadhav's avatar
Mayur Jadhav committed
384 385 386 387 388
      $query = "SELECT SUM(amount_granted) as amount_granted FROM civicrm_grant WHERE " .ltrim($where, ' AND');
      $amountGranted = CRM_Core_DAO::singleValueQuery($query);
    }
    return empty($amountGranted) ? 0 : $amountGranted;
  }
Monish Deb's avatar
Monish Deb committed
389

Mayur Jadhav's avatar
Mayur Jadhav committed
390 391 392 393 394 395 396 397 398 399 400 401 402 403
  /**
   * Function to get current grant granted amount
   *
   * @return int amount granted
   * @access public
   * @static
   */
  static function getCurrentGrantAmount($id = NULL) {
    if ($id != NULL) {
      $query = "SELECT amount_granted FROM civicrm_grant WHERE id = " . $id;
      $amountGranted = CRM_Core_DAO::singleValueQuery($query);
    }
    return empty($amountGranted) ? 0 : $amountGranted;
  }
404 405

  static function getPriorities($id, $contactId) {
Edselopez's avatar
Edselopez committed
406
    $priority = 10;
407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422
    $prevGrantProgram = CRM_Core_DAO::getFieldValue('CRM_Grant_DAO_GrantProgram', $id, 'grant_program_id');
    $amount = 0;
    $params = array(
      'grant_program_id' => $prevGrantProgram,
      'contact_id' => $contactId,
    );
    $grants = CRM_Grant_BAO_GrantProgram::getGrants($params);
    if (!empty($grants)) {
      foreach ($grants as $values) {
        $amount += $values['amount_granted'];
      }
    }
    $grantThresholds = CRM_Core_OptionGroup::values('grant_thresholds', TRUE);
    if (!empty($amount)) {
      if ($amount == $grantThresholds['Maximum Grant']) {
        $priority = -10;
Monish Deb's avatar
Monish Deb committed
423
      }
424 425 426 427 428 429
      elseif ($amount == 0) {
        $priority = 10;
      }
      elseif ((0 <= $amount) && ($amount <= $grantThresholds['Maximum Grant'])) {
        $priority = 0;
      }
Monish Deb's avatar
Monish Deb committed
430
    }
431 432
    return $priority;
  }
Pradeep Nayak's avatar
Pradeep Nayak committed
433 434 435 436 437 438 439 440

  static function createStatusChangeActivity($grantId, $newStatus, $oldStatus, $contactID) {
    if (($oldStatus == 'Draft' && $newStatus == 'Submitted') || $newStatus == $oldStatus || !$oldStatus) {
      return;
    }
    $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
    $activityType = CRM_Core_PseudoConstant::activityType();
    $session = CRM_Core_Session::singleton();
Monish Deb's avatar
Monish Deb committed
441
    $params = array(
Pradeep Nayak's avatar
Pradeep Nayak committed
442 443 444 445 446 447 448 449 450 451 452 453
      'source_contact_id'=> $session->get('userID'),
      'source_record_id' => $grantId,
      'activity_type_id'=> array_search('Grant Status Change', $activityType),
      'assignee_contact_id'=> array($contactID),
      'subject'=> "Grant status changed from {$oldStatus} to {$newStatus}",
      'activity_date_time'=> date('Ymdhis'),
      'status_id'=> array_search('Completed', $activityStatus),
      'priority_id'=> 2,
      'details'=> CRM_Core_Smarty::singleton()->get_template_vars('messageBody'),
    );
    CRM_Activity_BAO_Activity::create($params);
  }
454
}