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