CRM/Core/BAO/FinancialTrxn.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  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     $fids = array();
  62     if (!CRM_Utils_Rule::currencyCode($trxn->currency)) {
  63       $config = CRM_Core_Config::singleton();
  64       $trxn->currency = $config->defaultCurrency;
  65     }
  66
  67     $trxn->save();
  68
  69     // save to entity_financial_trxn table
  70     $entityFinancialTrxnParams
  71       = array(
  72         'entity_table' => "civicrm_contribution",
  73         'financial_trxn_id' => $trxn->id,
  74         'amount' => $params['total_amount'],
  75         'currency' => $trxn->currency,
  76       );
  77
  78     if (!empty($trxnEntityTable)) {
  79       $entityFinancialTrxnParams['entity_table'] = $trxnEntityTable['entity_table'];
  80       $entityFinancialTrxnParams['entity_id'] = $trxnEntityTable['entity_id'];
  81     }
  82     else {
  83       $entityFinancialTrxnParams['entity_id'] = $params['contribution_id'];
  84     }
  85
  86     $entityTrxn = new CRM_Financial_DAO_EntityFinancialTrxn();
  87     $entityTrxn->copyValues($entityFinancialTrxnParams);
  88     $entityTrxn->save();
  89     return $trxn;
  90   }
  91
  92   /**
  93    * @param int $contributionId
  94    * @param int $contributionFinancialTypeId
  95    *
  96    * @return array
  97    */
  98   public static function getBalanceTrxnAmt($contributionId, $contributionFinancialTypeId = NULL) {
  99     if (!$contributionFinancialTypeId) {
 100       $contributionFinancialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_BAO_Contribution', $contributionId, 'financial_type_id');
 101     }
 102     $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' "));
 103     $toFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($contributionFinancialTypeId, $relationTypeId);
 104     $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";
 105
 106     $p[1] = array($contributionId, 'Integer');
 107     $p[2] = array($toFinancialAccount, 'Integer');
 108
 109     $balanceAmtDAO = CRM_Core_DAO::executeQuery($q, $p);
 110     $ret = array();
 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 contactID.
 145    * @param string $orderBy
 146    *   To get single trxn id for a entity table i.e last or first.
 147    * @param bool $newTrxn
 148    *
 149    * @return array
 150    *   array of category id's the contact belongs to.
 151    *
 152    */
 153   public static function getFinancialTrxnId($entity_id, $orderBy = 'ASC', $newTrxn = FALSE) {
 154     $ids = array('entityFinancialTrxnId' => NULL, 'financialTrxnId' => NULL);
 155
 156     $condition = "";
 157     if (!$newTrxn) {
 158       $condition = " AND ((ceft1.entity_table IS NOT NULL) OR (cft.payment_instrument_id IS NOT NULL AND ceft1.entity_table IS NULL)) ";
 159     }
 160
 161     if ($orderBy) {
 162       $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
 163     }
 164
 165     $query = "SELECT ceft.id, ceft.financial_trxn_id FROM `civicrm_financial_trxn` cft
 166 LEFT JOIN civicrm_entity_financial_trxn ceft
 167 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 168 LEFT JOIN civicrm_entity_financial_trxn ceft1
 169 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 170 LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 171 WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL)
 172 {$condition}
 173 ORDER BY cft.id {$orderBy}
 174 LIMIT 1;";
 175
 176     $params = array(1 => array($entity_id, 'Integer'));
 177     $dao = CRM_Core_DAO::executeQuery($query, $params);
 178     if ($dao->fetch()) {
 179       $ids['entityFinancialTrxnId'] = $dao->id;
 180       $ids['financialTrxnId'] = $dao->financial_trxn_id;
 181     }
 182     return $ids;
 183   }
 184
 185   /**
 186    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 187    * @todo This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 188    *
 189    * @param int $entity_id
 190    *   Id of the entity usually the contactID.
 191    *
 192    * @return array
 193    *   array of catagory id's the contact belongs to.
 194    *
 195    */
 196   public static function getFinancialTrxnTotal($entity_id) {
 197     $query = "
 198       SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft
 199 LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id
 200 WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1
 201         ";
 202
 203     $sqlParams = array(1 => array($entity_id, 'Integer'));
 204     return CRM_Core_DAO::singleValueQuery($query, $sqlParams);
 205
 206   }
 207
 208   /**
 209    * Given an financial_trxn_id  check for previous entity_financial_trxn.
 210    *
 211    * @param $financial_trxn_id
 212    *   Id of the latest payment.
 213    *
 214    *
 215    * @return array
 216    *   array of previous payments
 217    *
 218    */
 219   public static function getPayments($financial_trxn_id) {
 220     $query = "
 221 SELECT ef1.financial_trxn_id, sum(ef1.amount) amount
 222 FROM civicrm_entity_financial_trxn ef1
 223 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id
 224 WHERE ef2.financial_trxn_id =%1
 225   AND ef2.entity_table = 'civicrm_financial_trxn'
 226   AND ef1.entity_table = 'civicrm_financial_item'
 227 GROUP BY ef1.financial_trxn_id
 228 UNION
 229 SELECT ef1.financial_trxn_id, ef1.amount
 230 FROM civicrm_entity_financial_trxn ef1
 231 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id
 232 WHERE  ef2.financial_trxn_id =%1
 233   AND ef2.entity_table = 'civicrm_financial_trxn'
 234   AND ef1.entity_table = 'civicrm_financial_trxn'";
 235
 236     $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 237     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 238     $i = 0;
 239     $result = array();
 240     while ($dao->fetch()) {
 241       $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id;
 242       $result[$i]['amount'] = $dao->amount;
 243       $i++;
 244     }
 245
 246     if (empty($result)) {
 247       $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'";
 248       $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 249       $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 250
 251       if ($dao->fetch()) {
 252         $result[0]['financial_trxn_id'] = $financial_trxn_id;
 253         $result[0]['amount'] = $dao->amount;
 254       }
 255     }
 256     return $result;
 257   }
 258
 259   /**
 260    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 261    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 262    *
 263    * @param $entity_id
 264    *   Id of the entity usually the contactID.
 265    * @param string $entity_table
 266    *   Name of the entity table usually 'civicrm_contact'.
 267    *
 268    * @return array
 269    *   array of catagory id's the contact belongs to.
 270    *
 271    */
 272   public static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') {
 273     $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft
 274 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'
 275 LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2
 276 WHERE lt.entity_id = %1 ";
 277
 278     $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String'));
 279     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 280     while ($dao->fetch()) {
 281       $result[$dao->financial_trxn_id][$dao->id] = $dao->amount;
 282     }
 283     if (!empty($result)) {
 284       return $result;
 285     }
 286     else {
 287       return NULL;
 288     }
 289   }
 290
 291   /**
 292    * Delete financial transaction
 293    *
 294    * @param int $entity_id
 295    * @return bool
 296    *   TRUE on success, FALSE otherwise.
 297    */
 298   public static function deleteFinancialTrxn($entity_id) {
 299     $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft
 300 LEFT JOIN civicrm_entity_financial_trxn ceft
 301   ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 302 LEFT JOIN civicrm_entity_financial_trxn ceft1
 303   ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 304 LEFT JOIN civicrm_financial_item cfi
 305   ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 306 WHERE ceft.entity_id = %1";
 307     CRM_Core_DAO::executeQuery($query, array(1 => array($entity_id, 'Integer')));
 308     return TRUE;
 309   }
 310
 311   /**
 312    * Create financial transaction for premium
 313    *
 314    */
 315   public static function createPremiumTrxn($params) {
 316     if ((empty($params['financial_type_id']) || empty($params['contributionId'])) && empty($params['oldPremium'])) {
 317       return;
 318     }
 319
 320     if (!empty($params['cost'])) {
 321       $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 322       $financialAccountType = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id']);
 323       $accountRelationship = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND label IN ('Premiums Inventory Account is', 'Cost of Sales Account is')");
 324       $toFinancialAccount = !empty($params['isDeleted']) ? 'Premiums Inventory Account is' : 'Cost of Sales Account is';
 325       $fromFinancialAccount = !empty($params['isDeleted']) ? 'Cost of Sales Account is' : 'Premiums Inventory Account is';
 326       $accountRelationship = array_flip($accountRelationship);
 327       $financialtrxn = array(
 328         'to_financial_account_id' => $financialAccountType[$accountRelationship[$toFinancialAccount]],
 329         'from_financial_account_id' => $financialAccountType[$accountRelationship[$fromFinancialAccount]],
 330         'trxn_date' => date('YmdHis'),
 331         'total_amount' => CRM_Utils_Array::value('cost', $params) ? $params['cost'] : 0,
 332         'currency' => CRM_Utils_Array::value('currency', $params),
 333         'status_id' => array_search('Completed', $contributionStatuses),
 334       );
 335       $trxnEntityTable['entity_table'] = 'civicrm_contribution';
 336       $trxnEntityTable['entity_id'] = $params['contributionId'];
 337       CRM_Core_BAO_FinancialTrxn::create($financialtrxn, $trxnEntityTable);
 338     }
 339
 340     if (!empty($params['oldPremium'])) {
 341       $premiumParams = array(
 342         'id' => $params['oldPremium']['product_id'],
 343       );
 344       $productDetails = array();
 345       CRM_Contribute_BAO_ManagePremiums::retrieve($premiumParams, $productDetails);
 346       $params = array(
 347         'cost' => CRM_Utils_Array::value('cost', $productDetails),
 348         'currency' => CRM_Utils_Array::value('currency', $productDetails),
 349         'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $productDetails),
 350         'contributionId' => $params['oldPremium']['contribution_id'],
 351         'isDeleted' => TRUE,
 352       );
 353       CRM_Core_BAO_FinancialTrxn::createPremiumTrxn($params);
 354     }
 355   }
 356
 357   /**
 358    * Create financial trxn and items when fee is charged
 359    *
 360    * @param array $params
 361    *   To create trxn entries.
 362    *
 363    */
 364   public static function recordFees($params) {
 365     $expenseTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
 366     $domainId = CRM_Core_Config::domainID();
 367     $amount = 0;
 368     if (!empty($params['prevContribution'])) {
 369       $amount = $params['prevContribution']->fee_amount;
 370     }
 371     $amount = $params['fee_amount'] - $amount;
 372     if (!$amount) {
 373       return FALSE;
 374     }
 375     $contributionId = isset($params['contribution']->id) ? $params['contribution']->id : $params['contribution_id'];
 376     if (empty($params['financial_type_id'])) {
 377       $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id', 'id');
 378     }
 379     else {
 380       $financialTypeId = $params['financial_type_id'];
 381     }
 382     $financialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $expenseTypeId);
 383
 384     $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id'];
 385     $params['trxnParams']['to_financial_account_id'] = $financialAccount;
 386     $params['trxnParams']['total_amount'] = $amount;
 387     $params['trxnParams']['fee_amount'] = $params['trxnParams']['net_amount'] = 0;
 388     $params['trxnParams']['status_id'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Completed', 'name');
 389     $params['trxnParams']['contribution_id'] = $contributionId;
 390     $trxn = self::create($params['trxnParams']);
 391     if (empty($params['entity_id'])) {
 392       $financialTrxnID = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC');
 393       $params['entity_id'] = $financialTrxnID['financialTrxnId'];
 394     }
 395     $fItemParams
 396       = array(
 397         'financial_account_id' => $financialAccount,
 398         'contact_id' => CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'),
 399         'created_date' => date('YmdHis'),
 400         'transaction_date' => date('YmdHis'),
 401         'amount' => $amount,
 402         'description' => 'Fee',
 403         'status_id' => CRM_Core_OptionGroup::getValue('financial_item_status', 'Paid', 'name'),
 404         'entity_table' => 'civicrm_financial_trxn',
 405         'entity_id' => $params['entity_id'],
 406         'currency' => $params['trxnParams']['currency'],
 407       );
 408     $trxnIDS['id'] = $trxn->id;
 409     $financialItem = CRM_Financial_BAO_FinancialItem::create($fItemParams, NULL, $trxnIDS);
 410   }
 411
 412   /**
 413    * get partial payment amount and type of it
 414    *
 415    * @param int $entityId
 416    * @param string $entityName
 417    * @param bool $returnType
 418    * @param int $lineItemTotal
 419    *
 420    * @return array|int|NULL|string
 421    *   [payment type => amount]
 422    *   payment type: 'amount_owed' or 'refund_due'
 423    */
 424   public static function getPartialPaymentWithType($entityId, $entityName = 'participant', $returnType = TRUE, $lineItemTotal = NULL) {
 425     $value = NULL;
 426     if (empty($entityName)) {
 427       return $value;
 428     }
 429
 430     if ($entityName == 'participant') {
 431       $contributionId = CRM_Core_DAO::getFieldValue('CRM_Event_DAO_ParticipantPayment', $entityId, 'contribution_id', 'participant_id');
 432       $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id');
 433
 434       if ($contributionId && $financialTypeId) {
 435         $statusId = CRM_Core_OptionGroup::getValue('contribution_status', 'Completed', 'name');
 436         $refundStatusId = CRM_Core_OptionGroup::getValue('contribution_status', 'Refunded', 'name');
 437
 438         $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' "));
 439         $toFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $relationTypeId);
 440         $feeRelationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
 441         $feeFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $feeRelationTypeId);
 442
 443         if (empty($lineItemTotal)) {
 444           $ids = CRM_Event_BAO_Participant::getParticipantIds($contributionId);
 445           if (count($ids) > 1) {
 446             $total = 0;
 447             foreach ($ids as $val) {
 448               $total += CRM_Price_BAO_LineItem::getLineTotal($val, 'civicrm_participant');
 449             }
 450             $lineItemTotal = $total;
 451           }
 452           else {
 453             $lineItemTotal = CRM_Price_BAO_LineItem::getLineTotal($entityId, 'civicrm_participant');
 454           }
 455         }
 456         $sqlFtTotalAmt = "
 457 SELECT SUM(ft.total_amount)
 458 FROM civicrm_financial_trxn ft
 459   LEFT JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution')
 460   LEFT JOIN civicrm_contribution c ON (eft.entity_id = c.id)
 461   LEFT JOIN civicrm_participant_payment pp ON (pp.contribution_id = c.id)
 462 WHERE pp.participant_id = {$entityId} AND ft.to_financial_account_id != {$toFinancialAccount} AND ft.to_financial_account_id != {$feeFinancialAccount}
 463   AND ft.status_id IN ({$statusId}, {$refundStatusId})
 464 ";
 465         $ftTotalAmt = CRM_Core_DAO::singleValueQuery($sqlFtTotalAmt);
 466         $value = 0;
 467         if ($ftTotalAmt) {
 468           $value = $paymentVal = $lineItemTotal - $ftTotalAmt;
 469         }
 470         if ($returnType) {
 471           $value = array();
 472           if ($paymentVal < 0) {
 473             $value['refund_due'] = $paymentVal;
 474           }
 475           elseif ($paymentVal > 0) {
 476             $value['amount_owed'] = $paymentVal;
 477           }
 478           elseif ($lineItemTotal == $ftTotalAmt) {
 479             $value['full_paid'] = $ftTotalAmt;
 480           }
 481         }
 482       }
 483     }
 484     return $value;
 485   }
 486 }