GrantPayment.php 17.7 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
 +--------------------------------------------------------------------+
 | 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$
 *
 */


38
class CRM_Grant_BAO_GrantPayment extends CRM_Grant_DAO_GrantPayment {
39
  const
40 41 42
    STOP = 100,
    REPRINT = 200,
    WITHDRAW = 300;
43 44 45 46 47
  /**
   * static field for all the grant information that we can potentially export
   * @var array
   * @static
   */
48
  static $_exportableFields = NULL;
49 50 51 52

  /**
   * class constructor
   */
53 54
  function __construct() {
    parent::__construct();
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
  }

  /**
   * Takes a bunch of params that are needed to match certain criteria and
   * retrieves the relevant objects. Typically the valid params are only
   * contact_id. We'll tweak this function to be more full featured over a period
   * of time. This is the inverse function of create. 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_Grant_BAO_ManageGrant object
   * @access public
   * @static
   */
71 72 73 74 75
  static function retrieve(&$params, &$defaults) {
    $grantPayment = new CRM_Grant_DAO_GrantPayment();
    $grantPayment->copyValues($params);
    if ($grantPayment->find(TRUE)) {
      CRM_Core_DAO::storeValues($grantPayment, $defaults);
76 77
      return $grantPayment;
    }
78
    return NULL;
79
  }
80

81 82 83 84
  function &exportableFields() {
    if (!self::$_exportableFields) {
      if (!self::$_exportableFields) {
        self::$_exportableFields = array();
85
      }
86 87 88

      $grantFields = array(
        'id' => array(
89 90
          'title' => 'Payment ID',
          'name' => 'id',
91
          'data_type' => CRM_Utils_Type::T_INT
92
        ),
93
        'payment_batch_number' => array(
94 95
          'title' => 'Payment Batch Nnumber',
          'name' => 'payment_batch_number',
96
          'data_type' => CRM_Utils_Type::T_INT
97
        ),
98
        'payment_number' => array(
99
          'title' => 'Check Number',
100
          'name' => 'payment_number',
101
          'data_type' => CRM_Utils_Type::T_INT
102
        ),
103
        'financial_type_id' => array(
104 105
          'title' => 'Financial Type ID',
          'name' => 'financial_type_id',
106
          'data_type' => CRM_Utils_Type::T_INT
107
        ),
108
        'contact_id' => array(
109 110
          'title' => 'Contact ID',
          'name' => 'contact_id',
111
          'data_type' => CRM_Utils_Type::T_INT
112
        ),
113
        'payment_created_date' => array(
114 115
          'title' => 'Payment Created Date',
          'name' => 'payment_created_date',
116
          'data_type' => CRM_Utils_Type::T_DATE
117
        ),
118
        'payment_date' => array(
119 120
          'title' => 'Payment Date',
          'name' => 'payment_date',
121
          'data_type' => CRM_Utils_Type::T_DATE
122 123 124 125
        ),
        'payable_to_name' => array(
          'title' => 'Payable To Name',
          'name' => 'payable_to_name',
126
          'data_type' => CRM_Utils_Type::T_STRING
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
        ),
        'payable_to_address' => array(
          'title' => 'Payable To Address',
          'name' => 'payable_to_address',
          'data_type' => CRM_Utils_Type::T_STRING
        ),
        'amount' => array(
          'title' => 'Amount',
          'name' => 'amount',
          'data_type' => CRM_Utils_Type::T_MONEY
        ),
        'currency' => array(
          'title' => 'Currency',
          'name' => 'currency',
          'data_type' => CRM_Utils_Type::T_STRING
        ),
        'payment_reason' => array(
          'title' => 'Payment Reason',
          'name' => 'payment_reason',
          'data_type' => CRM_Utils_Type::T_STRING
        ),
        'payment_status_id' => array(
          'title' => 'Payment Status ID',
          'name' => 'payment_status_id',
          'data_type' => CRM_Utils_Type::T_STRING
        ),
        'replaces_payment_id' => array(
          'title' => 'Payment Reason',
          'name' => 'replaces_payment_id',
          'data_type' => CRM_Utils_Type::T_STRING
        )
      );
159

160 161
      $fields = CRM_Grant_DAO_GrantPayment::export();
      self::$_exportableFields = $fields;
162
    }
163 164 165
    return self::$_exportableFields;
  }

166 167 168 169 170
  /**
   * function to add grant
   *
   * @param array $params reference array contains the values submitted by the form
   * @param array $ids    reference array contains the id
171
   *
172
   * @access public
173
   * @static
174
   * @return object
175
   */
176
  static function add(&$params, &$ids = []) {
177
    if (empty($params)) {
178 179
      return;
    }
180

181
    if (isset($params['total_amount'])) {
182
      $params[$field] = CRM_Utils_Rule::cleanMoney($params['total_amount']);
183 184
    }
    // convert dates to mysql format
185 186
    if (isset($params['payment_created_date'])) {
      $params['payment_created_date'] = CRM_Utils_Date::processDate($params['payment_created_date'], NULL, TRUE);
187
    }
188

189 190
    $grantPayment = new CRM_Grant_DAO_GrantPayment();
    $grantPayment->id = CRM_Utils_Array::value('id', $ids);
191

192 193
    $grantPayment->copyValues($params);
    return $grantPayment->save();
194 195
  }

196 197

  static function del($id) {
198
    CRM_Utils_Hook::pre('delete', 'GrantPayment', $id, CRM_Core_DAO::$_nullArray);
199

200
    $grantPayment = new CRM_Grant_DAO_GrantPayment();
201
    $grantPayment->id = $id;
202 203 204 205 206

    $grantPayment->find();

    // delete the recently created Grant
    $grantPaymentRecent = array(
207 208 209 210
      'id'   => $id,
      'type' => 'GrantPayment'
    );
    CRM_Utils_Recent::del($grantPaymentRecent);
211

212
    if ($grantPayment->fetch()) {
213
      $results = $grantPayment->delete();
214
      CRM_Utils_Hook::post('delete', 'GrantPayment', $grantPayment->id, $grantPayment);
215 216
      return $results;
    }
217
    return FALSE;
218
  }
219

220
  static function getMaxPayementBatchNumber() {
Mayur Jadhav's avatar
Mayur Jadhav committed
221
    $query = "SELECT MAX(payment_number) as payment_number, MAX(payment_batch_number) as payment_batch_number FROM civicrm_payment";
222 223
    $dao = CRM_Core_DAO::executeQuery($query);
    while($dao->fetch()) {
224 225 226 227 228
      $grantPrograms['payment_number'] = $dao->payment_number;
      $grantPrograms['payment_batch_number'] = $dao->payment_batch_number;
    }
    return $grantPrograms;
  }
229

230
  static function getPaymentNumber($id) {
Mayur Jadhav's avatar
Mayur Jadhav committed
231
    $query = "SELECT id FROM civicrm_payment WHERE payment_number = {$id}";
232
    return CRM_Core_DAO::singleValueQuery($query);
233
  }
234

235
  static function getPaymentBatchNumber($id) {
Mayur Jadhav's avatar
Mayur Jadhav committed
236
    $query = "SELECT id FROM civicrm_payment WHERE payment_batch_number = {$id}";
237
    return CRM_Core_DAO::singleValueQuery($query);
238
  }
239

240
  static function makeReport($fileName, $rows) {
241 242 243 244 245 246 247 248
    $config = CRM_Core_Config::singleton();
    $pdf_filename = $config->customFileUploadDir . $fileName;
    $query = "SELECT msg_subject subject, msg_text text, msg_html html, pdf_format_id format FROM civicrm_msg_template WHERE msg_title = 'Grant Payment Report'";
    $dao = CRM_Core_DAO::executeQuery($query);
    $dao->fetch();
    if (!$dao->N) {
      if ($params['messageTemplateID']) {
        CRM_Core_Error::fatal(ts('No such message template: id=%1.', array(1 => $params['messageTemplateID'])));
249
      }
Mayur Jadhav's avatar
Mayur Jadhav committed
250
      else {
251 252 253 254 255
        CRM_Core_Error::fatal(ts('No such message template: option group %1, option value %2.', array(1 => $params['groupName'], 2 => $params['valueName'])));
      }
    }

    $subject = $dao->subject;
256 257 258 259
    $text = $dao->text;
    $html = $dao->html;
    $format = $dao->format;
    $dao->free();
260

261 262 263
    civicrm_smarty_register_string_resource();
    $smarty = CRM_Core_Smarty::singleton();
    foreach(array('text', 'html') as $elem) {
264 265
      $$elem = $smarty->fetch("string:{$$elem}");
    }
266 267
    $output = file_put_contents($pdf_filename,
      CRM_Utils_PDF_Utils::html2pdf(
268 269 270 271 272 273
        $html,
        $fileName,
        true,
        'Letter'
      )
    );
274 275
    return $fileName;
  }
276

277
  static function createCSV($filename, $grantPayment) {
278 279 280 281 282 283 284 285 286 287 288 289 290 291

    $headers[] = array (
      'Contact Id',
      'Financial Type',
      'Batch Number',
      'Payment Number',
      'Payment Date',
      'Payment Created Date',
      'Payable To Name',
      'Payable To Address',
      'Amount',
      'Currency',
      'Payment Reason',
      'Payment Replaces Id',
292
    );
293

294
    $rows = array_merge($headers, $grantPayment);
295
    $fp = fopen($filename, "w");
Mayur Jadhav's avatar
Mayur Jadhav committed
296
    $line = '';
297 298
    $comma = "";
    $contributionTypes = CRM_Grant_BAO_GrantProgram::contributionTypes();
299
    foreach ($rows as $value) {
300 301
      if (isset($value['financial_type_id'])) {
        $value['financial_type_id'] = $contributionTypes[$value['financial_type_id']];
302
      }
Mayur Jadhav's avatar
Mayur Jadhav committed
303
      $line .= '"'.implode('","', $value).'"';
304 305 306 307 308
      $line .= "\n";
    }
    fputs($fp, $line);
    fclose($fp);
  }
309

310
  static function makePDF($fileName, $rows) {
311 312
    $config = CRM_Core_Config::singleton();
    $pdf_filename = $config->customFileUploadDir . $fileName;
Edselopez's avatar
Edselopez committed
313
    $query = "SELECT msg_subject subject, msg_html html, msg_text text, pdf_format_id format
314
              FROM civicrm_msg_template
Mayur Jadhav's avatar
Mayur Jadhav committed
315
              WHERE msg_title = 'Grant Payment Check' AND is_default = 1;";
316 317
    $grantDao = CRM_Core_DAO::executeQuery($query);
    $grantDao->fetch();
318

319
    if (!$grantDao->N) {
320
      CRM_Core_Error::fatal(ts('No such message template.'));
321 322
    }
    $subject = $grantDao->subject;
323
    $html = $grantDao->html;
Mayur Jadhav's avatar
Mayur Jadhav committed
324 325 326
    $text = $grantDao->text;
    $format = $grantDao->format;
    $grantDao->free();
327

Mayur Jadhav's avatar
Mayur Jadhav committed
328 329 330 331
    civicrm_smarty_register_string_resource();
    $smarty = CRM_Core_Smarty::singleton();
    foreach(array('text', 'html') as $elem) {
      $$elem = $smarty->fetch("string:{$$elem}");
332
    }
333 334 335 336

    $output = file_put_contents(
      $pdf_filename,
      CRM_Utils_PDF_Utils::html2pdf(
Mayur Jadhav's avatar
Mayur Jadhav committed
337
        $html,
338 339 340 341 342
        $fileName,
        TRUE,
        'Letter'
      )
    );
343 344
    return $fileName;
  }
345 346 347 348 349 350 351 352 353

  /**
   * Function to get events Summary
   *
   * @static
   *
   * @return array Array of event summary values
   */
  static function getGrantSummary($admin = FALSE) {
Monish Deb's avatar
Monish Deb committed
354 355 356 357 358 359 360 361 362 363 364
      $query = "
      SELECT
        p.id,
        p.label,
        g.status_id,
        count(g.id) AS status_total,
        sum(g.amount_total) AS amount_requested,
        sum(g.amount_granted) AS amount_granted,
        sum(ft.total_amount) AS total_paid,
        sum(g.amount_granted)/count(g.id) AS average_amount

365 366
      FROM civicrm_grant_program p
      LEFT JOIN civicrm_grant g ON g.grant_program_id = p.id
Monish Deb's avatar
Monish Deb committed
367 368
      LEFT JOIN civicrm_entity_financial_trxn eft ON eft.entity_id = g.id AND eft.entity_table = 'civicrm_grant'
      LEFT JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id
369
      WHERE g.status_id IS NOT NULL
Monish Deb's avatar
Monish Deb committed
370 371
      GROUP BY g.grant_program_id, g.status_id WITH ROLLUP
      ";
372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413

    $dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);

    $status = array( );
    $summary = array( );
    $summary['total_grants'] = $programs = NULL;
    $summary['no_of_grants'] = NULL;
    $querys = "SELECT
      v.label as label,
      v.weight as value,
      v.value as info
      FROM civicrm_option_value v, civicrm_option_group g
      WHERE  v.option_group_id = g.id
      AND  g.name = 'grant_status'
      AND  g.is_active = 1
      ORDER BY v.weight";
    $daos = CRM_Core_DAO::executeQuery($querys, CRM_Core_DAO::$_nullArray);
    while ($daos->fetch()) {
      $status[$daos->value] = array(
        'weight' => $daos->value,
        'value' => $daos->info,
        'label' => $daos->label,
        'total' => 0,
      );
    }
    foreach ($status as $id => $name) {
      $stats[$status[$id]['value']] = array(
        'label' => $name['label'],
        'value' => $name['value'],
        'weight' => $name['weight'],
        'total' => 0
      );
    }
    $count = 1;
    while ($dao->fetch()) {
      if ($dao->N == $count) {
        $summary['total_grants']['total_requested'] = $dao->amount_requested ? CRM_Utils_Money::format($dao->amount_requested) : CRM_Utils_Money::format(0);
        $summary['total_grants']['total_granted'] = $dao->amount_granted ? CRM_Utils_Money::format($dao->amount_granted) : CRM_Utils_Money::format(0);
        $summary['total_grants']['total_paid'] = $dao->total_paid ? CRM_Utils_Money::format($dao->total_paid) : CRM_Utils_Money::format(0);
        $summary['total_grants']['total_average'] = $dao->average_amount ? CRM_Utils_Money::format($dao->average_amount) : CRM_Utils_Money::format(0);
        continue;
      }
Monish Deb's avatar
Monish Deb committed
414
      if (!empty($dao->status_id) && !empty($stats[$dao->status_id])) {
415
        $programs[$dao->label][$stats[$dao->status_id]['weight']] = array(
Monish Deb's avatar
Monish Deb committed
416
          'label' => CRM_Utils_Array::value('label', CRM_Utils_Array::value($dao->status_id, $stats)),
417
          'total' => $dao->status_total,
Monish Deb's avatar
Monish Deb committed
418
          'value' => CRM_Utils_Array::value('value', CRM_Utils_Array::value($dao->status_id, $stats)),
419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445
          'amount_requested' => $dao->amount_requested ? CRM_Utils_Money::format($dao->amount_requested) : CRM_Utils_Money::format(0),
          'amount_granted' => $dao->amount_granted ? CRM_Utils_Money::format($dao->amount_granted) : CRM_Utils_Money::format(0),
          'total_paid' => $dao->total_paid ? CRM_Utils_Money::format($dao->total_paid) : CRM_Utils_Money::format(0),
          'average_amount' => $dao->average_amount ? CRM_Utils_Money::format($dao->average_amount) : CRM_Utils_Money::format(0),
          'pid' => $dao->id,
        );
        $programs[$dao->label] = $programs[$dao->label] + array_diff_key($status, $programs[$dao->label]); //add the two arrays
        ksort($programs[$dao->label]);
        $summary['total_grants']['all'] = 'All';
        $summary['no_of_grants'] += $dao->status_total;
      }
      else {
        $programs["<b>Subtotal $dao->label </b>"]['subtotal'] = array(
          'label' => '',
          'total' => $dao->status_total,
          'amount_requested' => $dao->amount_requested ? CRM_Utils_Money::format($dao->amount_requested) : CRM_Utils_Money::format(0),
          'amount_granted' => $dao->amount_granted ? CRM_Utils_Money::format($dao->amount_granted) : CRM_Utils_Money::format(0),
          'total_paid' => $dao->total_paid ? CRM_Utils_Money::format($dao->total_paid) : CRM_Utils_Money::format(0),
          'average_amount' => $dao->average_amount ? CRM_Utils_Money::format($dao->average_amount) : CRM_Utils_Money::format(0),
        );
      }
      $count++;
    }
    $summary['per_status'] = $programs;
    return $summary;
  }

Monish Deb's avatar
Monish Deb committed
446 447 448 449 450 451 452 453 454 455 456 457 458
  public static function actions() {
    $params = $_GET;
    if ($params['action'] == CRM_Grant_BAO_GrantPayment::STOP) {
      $dao = new CRM_Grant_DAO_GrantPayment();
      $dao->id = $params['id'];
      $dao->payment_status_id = CRM_Core_PseudoConstant::getKey('CRM_Grant_BAO_GrantPayment', 'payment_status_id', 'Stopped');
      $dao->save();
      CRM_Core_Session::setStatus(ts('Selected Grant Payment has been stopped successfully'));
      CRM_Utils_System::redirect(CRM_Utils_System::url('civicrm/grant/payment/search', '_qf_PaymentSearch_display=true&force=1&qfKey=' . $params['key']));
    }
    elseif ($params['action'] == CRM_Grant_BAO_GrantPayment::REPRINT) {
      CRM_Utils_System::redirect(CRM_Utils_System::url('civicrm/grant/payment/reprint', 'reset=1&prid=' . $params['id']));
    }
Monish Deb's avatar
Monish Deb committed
459
  }
Monish Deb's avatar
Monish Deb committed
460

Monish Deb's avatar
Monish Deb committed
461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483
  public static function deleteGrantFinancialEntries($grantID) {
    $sql = "SELECT fi.id as fi_id, GROUP_CONCAT(DISTINCT ft.id) as ft_id, eb.batch_id
      FROM civicrm_entity_financial_trxn eft
      INNER JOIN civicrm_financial_trxn ft ON eft.financial_trxn_id = ft.id AND eft.entity_table = 'civicrm_grant' AND eft.entity_id = $grantID
      LEFT JOIN civicrm_entity_financial_trxn eft1 ON eft1.financial_trxn_id = ft.id AND eft1.entity_table = 'civicrm_financial_item'
      LEFT JOIN civicrm_financial_item fi ON eft1.entity_id = fi.id
      LEFT JOIN civicrm_entity_batch eb ON eb.entity_table ='civicrm_financial_trxn' AND eb.entity_id = ft.id
      LEFT JOIN civicrm_batch b ON b.id = eb.batch_id
      GROUP BY eft.entity_id
    ";
    $dao = CRM_Core_DAO::executeQuery($sql);
    while($dao->fetch()) {
      $ftIDs = explode(',', $dao->ft_id);
      foreach ($ftIDs as $id) {
        civicrm_api3('FinancialTrxn', 'delete', ['id' => $id]);
      }
      civicrm_api3('FinancialItem', 'delete', ['id' => $dao->fi_id]);
      CRM_Core_DAO::executeQuery("DELETE FROM civicrm_payment WHERE financial_trxn_id IN ($dao->ft_id)");
      CRM_Core_DAO::executeQuery("DELETE FROM civicrm_entity_financial_trxn WHERE financial_trxn_id IN ($dao->ft_id)");
      if ($dao->batch_id) {
        CRM_Core_DAO::executeQuery("DELETE FROM civicrm_entity_batch WHERE entity_id IN ($dao->ft_id) AND entity_table = 'civicrm_financial_trxn' AND batch_id = $dao->batch_id ");
      }
    }
Monish Deb's avatar
Monish Deb committed
484 485
  }

486
}