CRM/Core/BAO/FinancialTrxn.php

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