CRM/Core/BAO/FinancialTrxn.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  */
  33 class CRM_Core_BAO_FinancialTrxn extends CRM_Financial_DAO_FinancialTrxn {
  34   /**
  35    * Class constructor.
  36    *
  37    * @return \CRM_Financial_DAO_FinancialTrxn
  38    */
  39   /**
  40    */
  41   public function __construct() {
  42     parent::__construct();
  43   }
  44
  45   /**
  46    * Takes an associative array and creates a financial transaction object.
  47    *
  48    * @param array $params
  49    *   (reference ) an assoc array of name/value pairs.
  50    *
  51    * @return CRM_Core_BAO_FinancialTrxn
  52    */
  53   public static function create($params) {
  54     $trxn = new CRM_Financial_DAO_FinancialTrxn();
  55     $trxn->copyValues($params);
  56
  57     if (empty($params['id']) && !CRM_Utils_Rule::currencyCode($trxn->currency)) {
  58       $trxn->currency = CRM_Core_Config::singleton()->defaultCurrency;
  59     }
  60
  61     $trxn->save();
  62
  63     if (!empty($params['id'])) {
  64       // For an update entity financial transaction record will already exist. Return early.
  65       return $trxn;
  66     }
  67
  68     // Save to entity_financial_trxn table.
  69     $entityFinancialTrxnParams = array(
  70       'entity_table' => CRM_Utils_Array::value('entity_table', $params, 'civicrm_contribution'),
  71       'entity_id' => CRM_Utils_Array::value('entity_id', $params, CRM_Utils_Array::value('contribution_id', $params)),
  72       'financial_trxn_id' => $trxn->id,
  73       'amount' => $params['total_amount'],
  74     );
  75
  76     $entityTrxn = new CRM_Financial_DAO_EntityFinancialTrxn();
  77     $entityTrxn->copyValues($entityFinancialTrxnParams);
  78     $entityTrxn->save();
  79     return $trxn;
  80   }
  81
  82   /**
  83    * @param int $contributionId
  84    * @param int $contributionFinancialTypeId
  85    *
  86    * @return array
  87    */
  88   public static function getBalanceTrxnAmt($contributionId, $contributionFinancialTypeId = NULL) {
  89     if (!$contributionFinancialTypeId) {
  90       $contributionFinancialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_BAO_Contribution', $contributionId, 'financial_type_id');
  91     }
  92     $toFinancialAccount = CRM_Contribute_PseudoConstant::getRelationalFinancialAccount($contributionFinancialTypeId, 'Accounts Receivable Account is');
  93     $q = "SELECT ft.id, ft.total_amount FROM civicrm_financial_trxn ft INNER JOIN civicrm_entity_financial_trxn eft ON (eft.financial_trxn_id = ft.id AND eft.entity_table = 'civicrm_contribution') WHERE eft.entity_id = %1 AND ft.to_financial_account_id = %2";
  94
  95     $p[1] = array($contributionId, 'Integer');
  96     $p[2] = array($toFinancialAccount, 'Integer');
  97
  98     $balanceAmtDAO = CRM_Core_DAO::executeQuery($q, $p);
  99     $ret = array();
 100     if ($balanceAmtDAO->N) {
 101       $ret['total_amount'] = 0;
 102     }
 103     while ($balanceAmtDAO->fetch()) {
 104       $ret['trxn_id'] = $balanceAmtDAO->id;
 105       $ret['total_amount'] += $balanceAmtDAO->total_amount;
 106     }
 107
 108     return $ret;
 109   }
 110
 111   /**
 112    * Fetch object based on array of properties.
 113    *
 114    * @param array $params
 115    *   (reference ) an assoc array of name/value pairs.
 116    * @param array $defaults
 117    *   (reference ) an assoc array to hold the flattened values.
 118    *
 119    * @return CRM_Contribute_BAO_ContributionType
 120    */
 121   public static function retrieve(&$params, &$defaults) {
 122     $financialItem = new CRM_Financial_DAO_FinancialTrxn();
 123     $financialItem->copyValues($params);
 124     if ($financialItem->find(TRUE)) {
 125       CRM_Core_DAO::storeValues($financialItem, $defaults);
 126       return $financialItem;
 127     }
 128     return NULL;
 129   }
 130
 131   /**
 132    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 133    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 134    *
 135    * @param $entity_id
 136    *   Id of the entity usually the contributionID.
 137    * @param string $orderBy
 138    *   To get single trxn id for a entity table i.e last or first.
 139    * @param bool $newTrxn
 140    * @param string $whereClause
 141    *   Additional where parameters
 142    *
 143    * @return array
 144    *   array of category id's the contact belongs to.
 145    *
 146    */
 147   public static function getFinancialTrxnId($entity_id, $orderBy = 'ASC', $newTrxn = FALSE, $whereClause = '', $fromAccountID = NULL) {
 148     $ids = array('entityFinancialTrxnId' => NULL, 'financialTrxnId' => NULL);
 149
 150     $params = array(1 => array($entity_id, 'Integer'));
 151     $condition = "";
 152     if (!$newTrxn) {
 153       $condition = " AND ((ceft1.entity_table IS NOT NULL) OR (cft.payment_instrument_id IS NOT NULL AND ceft1.entity_table IS NULL)) ";
 154     }
 155
 156     if ($fromAccountID) {
 157       $condition .= " AND (cft.from_financial_account_id <> %2 OR cft.from_financial_account_id IS NULL)";
 158       $params[2] = array($fromAccountID, 'Integer');
 159     }
 160     if ($orderBy) {
 161       $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
 162     }
 163
 164     $query = "SELECT ceft.id, ceft.financial_trxn_id, cft.trxn_id FROM `civicrm_financial_trxn` cft
 165 LEFT JOIN civicrm_entity_financial_trxn ceft
 166 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 167 LEFT JOIN civicrm_entity_financial_trxn ceft1
 168 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 169 LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 170 WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL)
 171 {$condition}
 172 {$whereClause}
 173 ORDER BY cft.id {$orderBy}
 174 LIMIT 1;";
 175
 176     $dao = CRM_Core_DAO::executeQuery($query, $params);
 177     if ($dao->fetch()) {
 178       $ids['entityFinancialTrxnId'] = $dao->id;
 179       $ids['financialTrxnId'] = $dao->financial_trxn_id;
 180       $ids['trxn_id'] = $dao->trxn_id;
 181     }
 182     return $ids;
 183   }
 184
 185   /**
 186    * Get the transaction id for the (latest) refund associated with a contribution.
 187    *
 188    * @param int $contributionID
 189    * @return string
 190    */
 191   public static function getRefundTransactionTrxnID($contributionID) {
 192     $ids = self::getRefundTransactionIDs($contributionID);
 193     return isset($ids['trxn_id']) ? $ids['trxn_id'] : NULL;
 194   }
 195
 196   /**
 197    * Get the transaction id for the (latest) refund associated with a contribution.
 198    *
 199    * @param int $contributionID
 200    * @return array
 201    */
 202   public static function getRefundTransactionIDs($contributionID) {
 203     $refundStatusID = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Refunded');
 204     return self::getFinancialTrxnId($contributionID, 'DESC', FALSE, " AND cft.status_id = $refundStatusID");
 205   }
 206
 207   /**
 208    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 209    * @todo This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 210    *
 211    * @param int $entity_id
 212    *   Id of the entity usually the contactID.
 213    *
 214    * @return array
 215    *   array of category id's the contact belongs to.
 216    *
 217    */
 218   public static function getFinancialTrxnTotal($entity_id) {
 219     $query = "
 220       SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft
 221 LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id
 222 WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1
 223         ";
 224
 225     $sqlParams = array(1 => array($entity_id, 'Integer'));
 226     return CRM_Core_DAO::singleValueQuery($query, $sqlParams);
 227
 228   }
 229
 230   /**
 231    * Given an financial_trxn_id  check for previous entity_financial_trxn.
 232    *
 233    * @param $financial_trxn_id
 234    *   Id of the latest payment.
 235    *
 236    *
 237    * @return array
 238    *   array of previous payments
 239    *
 240    */
 241   public static function getPayments($financial_trxn_id) {
 242     $query = "
 243 SELECT ef1.financial_trxn_id, sum(ef1.amount) amount
 244 FROM civicrm_entity_financial_trxn ef1
 245 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id
 246 WHERE ef2.financial_trxn_id =%1
 247   AND ef2.entity_table = 'civicrm_financial_trxn'
 248   AND ef1.entity_table = 'civicrm_financial_item'
 249 GROUP BY ef1.financial_trxn_id
 250 UNION
 251 SELECT ef1.financial_trxn_id, ef1.amount
 252 FROM civicrm_entity_financial_trxn ef1
 253 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id
 254 WHERE  ef2.financial_trxn_id =%1
 255   AND ef2.entity_table = 'civicrm_financial_trxn'
 256   AND ef1.entity_table = 'civicrm_financial_trxn'";
 257
 258     $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 259     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 260     $i = 0;
 261     $result = array();
 262     while ($dao->fetch()) {
 263       $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id;
 264       $result[$i]['amount'] = $dao->amount;
 265       $i++;
 266     }
 267
 268     if (empty($result)) {
 269       $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'";
 270       $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 271       $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 272
 273       if ($dao->fetch()) {
 274         $result[0]['financial_trxn_id'] = $financial_trxn_id;
 275         $result[0]['amount'] = $dao->amount;
 276       }
 277     }
 278     return $result;
 279   }
 280
 281   /**
 282    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 283    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 284    *
 285    * @param $entity_id
 286    *   Id of the entity usually the contactID.
 287    * @param string $entity_table
 288    *   Name of the entity table usually 'civicrm_contact'.
 289    *
 290    * @return array
 291    *   array of category id's the contact belongs to.
 292    *
 293    */
 294   public static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') {
 295     $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft
 296 LEFT JOIN civicrm_financial_item AS fi ON fi.id = ft.entity_id AND fi.entity_table = 'civicrm_line_item' AND ft.entity_table = 'civicrm_financial_item'
 297 LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2
 298 WHERE lt.entity_id = %1 ";
 299
 300     $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String'));
 301     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 302     while ($dao->fetch()) {
 303       $result[$dao->financial_trxn_id][$dao->id] = $dao->amount;
 304     }
 305     if (!empty($result)) {
 306       return $result;
 307     }
 308     else {
 309       return NULL;
 310     }
 311   }
 312
 313   /**
 314    * Delete financial transaction.
 315    *
 316    * @param int $entity_id
 317    * @return bool
 318    *   TRUE on success, FALSE otherwise.
 319    */
 320   public static function deleteFinancialTrxn($entity_id) {
 321     $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft
 322 LEFT JOIN civicrm_entity_financial_trxn ceft
 323   ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 324 LEFT JOIN civicrm_entity_financial_trxn ceft1
 325   ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 326 LEFT JOIN civicrm_financial_item cfi
 327   ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 328 WHERE ceft.entity_id = %1";
 329     CRM_Core_DAO::executeQuery($query, array(1 => array($entity_id, 'Integer')));
 330     return TRUE;
 331   }
 332
 333   /**
 334    * Create financial transaction for premium.
 335    *
 336    * @param array $params
 337    *   - oldPremium
 338    *   - financial_type_id
 339    *   - contributionId
 340    *   - isDeleted
 341    *   - cost
 342    *   - currency
 343    */
 344   public static function createPremiumTrxn($params) {
 345     if ((empty($params['financial_type_id']) || empty($params['contributionId'])) && empty($params['oldPremium'])) {
 346       return;
 347     }
 348
 349     if (!empty($params['cost'])) {
 350       $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 351       $toFinancialAccountType = !empty($params['isDeleted']) ? 'Premiums Inventory Account is' : 'Cost of Sales Account is';
 352       $fromFinancialAccountType = !empty($params['isDeleted']) ? 'Cost of Sales Account is' : 'Premiums Inventory Account is';
 353       $accountRelationship = array_flip($accountRelationship);
 354       $financialtrxn = array(
 355         'to_financial_account_id' => CRM_Contribute_PseudoConstant::getRelationalFinancialAccount($params['financial_type_id'], $toFinancialAccountType),
 356         'from_financial_account_id' => CRM_Contribute_PseudoConstant::getRelationalFinancialAccount($params['financial_type_id'], $fromFinancialAccountType),
 357         'trxn_date' => date('YmdHis'),
 358         'total_amount' => CRM_Utils_Array::value('cost', $params) ? $params['cost'] : 0,
 359         'currency' => CRM_Utils_Array::value('currency', $params),
 360         'status_id' => array_search('Completed', $contributionStatuses),
 361         'entity_table' => 'civicrm_contribution',
 362         'entity_id' => $params['contributionId'],
 363       );
 364       CRM_Core_BAO_FinancialTrxn::create($financialtrxn);
 365     }
 366
 367     if (!empty($params['oldPremium'])) {
 368       $premiumParams = array(
 369         'id' => $params['oldPremium']['product_id'],
 370       );
 371       $productDetails = array();
 372       CRM_Contribute_BAO_ManagePremiums::retrieve($premiumParams, $productDetails);
 373       $params = array(
 374         'cost' => CRM_Utils_Array::value('cost', $productDetails),
 375         'currency' => CRM_Utils_Array::value('currency', $productDetails),
 376         'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $productDetails),
 377         'contributionId' => $params['oldPremium']['contribution_id'],
 378         'isDeleted' => TRUE,
 379       );
 380       CRM_Core_BAO_FinancialTrxn::createPremiumTrxn($params);
 381     }
 382   }
 383
 384   /**
 385    * Create financial trxn and items when fee is charged.
 386    *
 387    * @param array $params
 388    *   To create trxn entries.
 389    *
 390    * @return bool
 391    */
 392   public static function recordFees($params) {
 393     $domainId = CRM_Core_Config::domainID();
 394     $amount = 0;
 395     if (!empty($params['prevContribution'])) {
 396       $amount = $params['prevContribution']->fee_amount;
 397     }
 398     $amount = $params['fee_amount'] - $amount;
 399     if (!$amount) {
 400       return FALSE;
 401     }
 402     $contributionId = isset($params['contribution']->id) ? $params['contribution']->id : $params['contribution_id'];
 403     if (empty($params['financial_type_id'])) {
 404       $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id', 'id');
 405     }
 406     else {
 407       $financialTypeId = $params['financial_type_id'];
 408     }
 409     $financialAccount = CRM_Contribute_PseudoConstant::getRelationalFinancialAccount($financialTypeId, 'Expense Account is');
 410
 411     $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id'];
 412     $params['trxnParams']['to_financial_account_id'] = $financialAccount;
 413     $params['trxnParams']['total_amount'] = $amount;
 414     $params['trxnParams']['fee_amount'] = $params['trxnParams']['net_amount'] = 0;
 415     $params['trxnParams']['status_id'] = $params['contribution_status_id'];
 416     $params['trxnParams']['contribution_id'] = $contributionId;
 417     $params['trxnParams']['is_payment'] = FALSE;
 418     $trxn = self::create($params['trxnParams']);
 419     if (empty($params['entity_id'])) {
 420       $financialTrxnID = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC');
 421       $params['entity_id'] = $financialTrxnID['financialTrxnId'];
 422     }
 423     $fItemParams
 424       = array(
 425         'financial_account_id' => $financialAccount,
 426         'contact_id' => CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'),
 427         'created_date' => date('YmdHis'),
 428         'transaction_date' => date('YmdHis'),
 429         'amount' => $amount,
 430         'description' => 'Fee',
 431         'status_id' => CRM_Core_Pseudoconstant::getKey('CRM_Financial_BAO_FinancialItem', 'status_id', 'Paid'),
 432         'entity_table' => 'civicrm_financial_trxn',
 433         'entity_id' => $params['entity_id'],
 434         'currency' => $params['trxnParams']['currency'],
 435       );
 436     $trxnIDS['id'] = $trxn->id;
 437     CRM_Financial_BAO_FinancialItem::create($fItemParams, NULL, $trxnIDS);
 438   }
 439
 440   /**
 441    * get partial payment amount and type of it.
 442    *
 443    * @param int $entityId
 444    * @param string $entityName
 445    * @param bool $returnType
 446    * @param int $lineItemTotal
 447    *
 448    * @return array|int|NULL|string
 449    *   [payment type => amount]
 450    *   payment type: 'amount_owed' or 'refund_due'
 451    */
 452   public static function getPartialPaymentWithType($entityId, $entityName = 'participant', $returnType = TRUE, $lineItemTotal = NULL) {
 453     $value = NULL;
 454     if (empty($entityName)) {
 455       return $value;
 456     }
 457
 458     if ($entityName == 'participant') {
 459       $contributionId = CRM_Core_DAO::getFieldValue('CRM_Event_DAO_ParticipantPayment', $entityId, 'contribution_id', 'participant_id');
 460     }
 461     elseif ($entityName == 'membership') {
 462       $contributionId = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipPayment', $entityId, 'contribution_id', 'membership_id');
 463     }
 464     else {
 465       $contributionId = $entityId;
 466     }
 467     $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id');
 468
 469     if ($contributionId && $financialTypeId) {
 470       $statusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed');
 471       $refundStatusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Refunded');
 472
 473       if (empty($lineItemTotal)) {
 474         $lineItemTotal = CRM_Price_BAO_LineItem::getLineTotal($contributionId);
 475       }
 476       $sqlFtTotalAmt = "
 477 SELECT SUM(ft.total_amount)
 478 FROM civicrm_financial_trxn ft
 479   INNER JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution' AND eft.entity_id = {$contributionId})
 480 WHERE ft.is_payment = 1
 481   AND ft.status_id IN ({$statusId}, {$refundStatusId})
 482 ";
 483
 484       $ftTotalAmt = CRM_Core_DAO::singleValueQuery($sqlFtTotalAmt);
 485       $value = 0;
 486       if (!$ftTotalAmt) {
 487         $ftTotalAmt = 0;
 488       }
 489       $currency = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'currency');
 490       $value = $paymentVal = CRM_Utils_Money::subtractCurrencies($lineItemTotal, $ftTotalAmt, $currency);
 491       if ($returnType) {
 492         $value = array();
 493         if ($paymentVal < 0) {
 494           $value['refund_due'] = $paymentVal;
 495         }
 496         elseif ($paymentVal > 0) {
 497           $value['amount_owed'] = $paymentVal;
 498         }
 499         elseif ($lineItemTotal == $ftTotalAmt) {
 500           $value['full_paid'] = $ftTotalAmt;
 501         }
 502       }
 503     }
 504     return $value;
 505   }
 506
 507   /**
 508    * @param int $contributionId
 509    *
 510    * @return array
 511    */
 512   public static function getTotalPayments($contributionId) {
 513     $statusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed');
 514
 515     $sql = "SELECT SUM(ft.total_amount) FROM civicrm_financial_trxn ft
 516       INNER JOIN civicrm_entity_financial_trxn eft ON (eft.financial_trxn_id = ft.id AND eft.entity_table = 'civicrm_contribution')
 517       WHERE eft.entity_id = %1 AND ft.is_payment = 1 AND ft.status_id = %2";
 518
 519     $params = array(
 520       1 => array($contributionId, 'Integer'),
 521       2 => array($statusId, 'Integer'),
 522     );
 523
 524     return CRM_Core_DAO::singleValueQuery($sql, $params);
 525   }
 526
 527   /**
 528    * Function records partial payment, complete's contribution if payment is fully paid
 529    * and returns latest payment ie financial trxn
 530    *
 531    * @param array $contribution
 532    * @param array $params
 533    *
 534    * @return CRM_Core_BAO_FinancialTrxn
 535    */
 536   public static function getPartialPaymentTrxn($contribution, $params) {
 537     $trxn = CRM_Contribute_BAO_Contribution::recordPartialPayment($contribution, $params);
 538     $paid = CRM_Core_BAO_FinancialTrxn::getTotalPayments($params['contribution_id']);
 539     $total = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $params['contribution_id'], 'total_amount');
 540     $cmp = bccomp($total, $paid, 5);
 541     if ($cmp == 0 || $cmp == -1) {// If paid amount is greater or equal to total amount
 542       civicrm_api3('Contribution', 'completetransaction', array('id' => $contribution['id']));
 543     }
 544     return $trxn;
 545   }
 546
 547   /**
 548    * Get revenue amount for membership.
 549    *
 550    * @param array $lineItem
 551    *
 552    * @return array
 553    */
 554   public static function getMembershipRevenueAmount($lineItem) {
 555     $revenueAmount = array();
 556     $membershipDetail = civicrm_api3('Membership', 'getsingle', array(
 557       'id' => $lineItem['entity_id'],
 558     ));
 559     if (empty($membershipDetail['end_date'])) {
 560       return $revenueAmount;
 561     }
 562
 563     $startDate = strtotime($membershipDetail['start_date']);
 564     $endDate = strtotime($membershipDetail['end_date']);
 565     $startYear = date('Y', $startDate);
 566     $endYear = date('Y', $endDate);
 567     $startMonth = date('m', $startDate);
 568     $endMonth = date('m', $endDate);
 569
 570     $monthOfService = (($endYear - $startYear) * 12) + ($endMonth - $startMonth);
 571     $startDateOfRevenue = $membershipDetail['start_date'];
 572     $typicalPayment = round(($lineItem['line_total'] / $monthOfService), 2);
 573     for ($i = 0; $i <= $monthOfService - 1; $i++) {
 574       $revenueAmount[$i]['amount'] = $typicalPayment;
 575       if ($i == 0) {
 576         $revenueAmount[$i]['amount'] -= (($typicalPayment * $monthOfService) - $lineItem['line_total']);
 577       }
 578       $revenueAmount[$i]['revenue_date'] = $startDateOfRevenue;
 579       $startDateOfRevenue = date('Y-m', strtotime('+1 month', strtotime($startDateOfRevenue))) . '-01';
 580     }
 581     return $revenueAmount;
 582   }
 583
 584   /**
 585    * Create transaction for deferred revenue.
 586    *
 587    * @param array $lineItems
 588    *
 589    * @param CRM_Contribute_BAO_Contribution $contributionDetails
 590    *
 591    * @param bool $update
 592    *
 593    * @param string $context
 594    *
 595    */
 596   public static function createDeferredTrxn($lineItems, $contributionDetails, $update = FALSE, $context = NULL) {
 597     if (empty($lineItems)) {
 598       return;
 599     }
 600     $revenueRecognitionDate = $contributionDetails->revenue_recognition_date;
 601     if (!CRM_Utils_System::isNull($revenueRecognitionDate)) {
 602       $statuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 603       if (!$update
 604         && (CRM_Utils_Array::value($contributionDetails->contribution_status_id, $statuses) != 'Completed'
 605           || (CRM_Utils_Array::value($contributionDetails->contribution_status_id, $statuses) != 'Pending'
 606             && $contributionDetails->is_pay_later)
 607           )
 608       ) {
 609         return;
 610       }
 611       $trxnParams = array(
 612         'contribution_id' => $contributionDetails->id,
 613         'fee_amount' => '0.00',
 614         'currency' => $contributionDetails->currency,
 615         'trxn_id' => $contributionDetails->trxn_id,
 616         'status_id' => $contributionDetails->contribution_status_id,
 617         'payment_instrument_id' => $contributionDetails->payment_instrument_id,
 618         'check_number' => $contributionDetails->check_number,
 619       );
 620
 621       $deferredRevenues = array();
 622       foreach ($lineItems as $priceSetID => $lineItem) {
 623         if (!$priceSetID) {
 624           continue;
 625         }
 626         foreach ($lineItem as $key => $item) {
 627           $lineTotal = !empty($item['deferred_line_total']) ? $item['deferred_line_total'] : $item['line_total'];
 628           if ($lineTotal <= 0 && !$update) {
 629             continue;
 630           }
 631           $deferredRevenues[$key] = $item;
 632           if ($context == 'changeFinancialType') {
 633             $deferredRevenues[$key]['financial_type_id'] = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_LineItem', $item['id'], 'financial_type_id');
 634           }
 635           if (in_array($item['entity_table'],
 636             array('civicrm_participant', 'civicrm_contribution'))
 637           ) {
 638             $deferredRevenues[$key]['revenue'][] = array(
 639               'amount' => $lineTotal,
 640               'revenue_date' => $revenueRecognitionDate,
 641             );
 642           }
 643           else {
 644             // for membership
 645             $item['line_total'] = $lineTotal;
 646             $deferredRevenues[$key]['revenue'] = self::getMembershipRevenueAmount($item);
 647           }
 648         }
 649       }
 650       $accountRel = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
 651
 652       CRM_Utils_Hook::alterDeferredRevenueItems($deferredRevenues, $contributionDetails, $update, $context);
 653
 654       foreach ($deferredRevenues as $key => $deferredRevenue) {
 655         $results = civicrm_api3('EntityFinancialAccount', 'get', array(
 656           'entity_table' => 'civicrm_financial_type',
 657           'entity_id' => $deferredRevenue['financial_type_id'],
 658           'account_relationship' => array('IN' => array('Income Account is', 'Deferred Revenue Account is')),
 659         ));
 660         if ($results['count'] != 2) {
 661           continue;
 662         }
 663         foreach ($results['values'] as $result) {
 664           if ($result['account_relationship'] == $accountRel) {
 665             $trxnParams['from_financial_account_id'] = $result['financial_account_id'];
 666           }
 667           else {
 668             $trxnParams['to_financial_account_id'] = $result['financial_account_id'];
 669           }
 670         }
 671         foreach ($deferredRevenue['revenue'] as $revenue) {
 672           $trxnParams['total_amount'] = $trxnParams['net_amount'] = $revenue['amount'];
 673           $trxnParams['trxn_date'] = CRM_Utils_Date::isoToMysql($revenue['revenue_date']);
 674           $financialTxn = CRM_Core_BAO_FinancialTrxn::create($trxnParams);
 675           $entityParams = array(
 676             'entity_id' => $deferredRevenue['financial_item_id'],
 677             'entity_table' => 'civicrm_financial_item',
 678             'amount' => $revenue['amount'],
 679             'financial_trxn_id' => $financialTxn->id,
 680           );
 681           civicrm_api3('EntityFinancialTrxn', 'create', $entityParams);
 682         }
 683       }
 684     }
 685   }
 686
 687   /**
 688    * Update Credit Card Details in civicrm_financial_trxn table.
 689    *
 690    * @param int $contributionID
 691    * @param int $panTruncation
 692    * @param int $cardType
 693    *
 694    */
 695   public static function updateCreditCardDetails($contributionID, $panTruncation, $cardType) {
 696     $financialTrxn = civicrm_api3('EntityFinancialTrxn', 'get', array(
 697       'return' => array('financial_trxn_id.payment_processor_id', 'financial_trxn_id'),
 698       'entity_table' => 'civicrm_contribution',
 699       'entity_id' => $contributionID,
 700       'financial_trxn_id.is_payment' => TRUE,
 701       'options' => array('sort' => 'financial_trxn_id DESC', 'limit' => 1),
 702     ));
 703
 704     // In case of Contribution status is Pending From Incomplete Transaction or Failed there is no Financial Entries created for Contribution.
 705     // Above api will return 0 count, in such case we won't update card type and pan truncation field.
 706     if (!$financialTrxn['count']) {
 707       return NULL;
 708     }
 709
 710     $financialTrxn = $financialTrxn['values'][$financialTrxn['id']];
 711     $paymentProcessorID = CRM_Utils_Array::value('financial_trxn_id.payment_processor_id', $financialTrxn);
 712
 713     if ($paymentProcessorID) {
 714       return NULL;
 715     }
 716
 717     $financialTrxnId = $financialTrxn['financial_trxn_id'];
 718     $trxnparams = array('id' => $financialTrxnId);
 719     if (isset($cardType)) {
 720       $trxnparams['card_type_id'] = $cardType;
 721     }
 722     if (isset($panTruncation)) {
 723       $trxnparams['pan_truncation'] = $panTruncation;
 724     }
 725     civicrm_api3('FinancialTrxn', 'create', $trxnparams);
 726   }
 727
 728   /**
 729    * The function is responsible for handling financial entries if payment instrument is changed
 730    *
 731    * @param array $inputParams
 732    *
 733    */
 734   public static function updateFinancialAccountsOnPaymentInstrumentChange($inputParams) {
 735     $prevContribution = $inputParams['prevContribution'];
 736     $currentContribution = $inputParams['contribution'];
 737     // ensure that there are all the information in updated contribution object identified by $currentContribution
 738     $currentContribution->find(TRUE);
 739
 740     $deferredFinancialAccount = CRM_Utils_Array::value('deferred_financial_account_id', $inputParams);
 741     if (empty($deferredFinancialAccount)) {
 742       $deferredFinancialAccount = CRM_Contribute_PseudoConstant::getRelationalFinancialAccount($prevContribution->financial_type_id, 'Deferred Revenue Account is');
 743     }
 744
 745     $lastFinancialTrxnId = self::getFinancialTrxnId($prevContribution->id, 'DESC', FALSE, NULL, $deferredFinancialAccount);
 746
 747     // there is no point to proceed as we can't find the last payment made
 748     // @todo we should throw an exception here rather than return false.
 749     if (empty($lastFinancialTrxnId['financialTrxnId'])) {
 750       return FALSE;
 751     }
 752
 753     // If payment instrument is changed reverse the last payment
 754     //  in terms of reversing financial item and trxn
 755     $lastFinancialTrxn = civicrm_api3('FinancialTrxn', 'getsingle', array('id' => $lastFinancialTrxnId['financialTrxnId']));
 756     unset($lastFinancialTrxn['id']);
 757     $lastFinancialTrxn['trxn_date'] = $inputParams['trxnParams']['trxn_date'];
 758     $lastFinancialTrxn['total_amount'] = -$inputParams['trxnParams']['total_amount'];
 759     $lastFinancialTrxn['net_amount'] = -$inputParams['trxnParams']['net_amount'];
 760     $lastFinancialTrxn['fee_amount'] = -$inputParams['trxnParams']['fee_amount'];
 761     $lastFinancialTrxn['contribution_id'] = $prevContribution->id;
 762     foreach (array($lastFinancialTrxn, $inputParams['trxnParams']) as $financialTrxnParams) {
 763       $trxn = CRM_Core_BAO_FinancialTrxn::create($financialTrxnParams);
 764       $trxnParams = array(
 765         'total_amount' => $trxn->total_amount,
 766         'contribution_id' => $currentContribution->id,
 767       );
 768       CRM_Contribute_BAO_Contribution::assignProportionalLineItems($trxnParams, $trxn->id, $prevContribution->total_amount);
 769     }
 770
 771     self::createDeferredTrxn(CRM_Utils_Array::value('line_item', $inputParams), $currentContribution, TRUE, 'changePaymentInstrument');
 772
 773     return TRUE;
 774   }
 775
 776 }