CRM/Core/BAO/FinancialTrxn.php

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