CRM/Core/BAO/FinancialTrxn.php

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