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