3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
35 class CRM_Core_BAO_FinancialTrxn
extends CRM_Financial_DAO_FinancialTrxn
{
39 * @return \CRM_Financial_DAO_FinancialTrxn
43 public function __construct() {
44 parent
::__construct();
48 * Takes an associative array and creates a financial transaction object.
50 * @param array $params
51 * (reference ) an assoc array of name/value pairs.
53 * @param string $trxnEntityTable
56 * @return CRM_Core_BAO_FinancialTrxn
58 public static function create(&$params, $trxnEntityTable = NULL) {
59 $trxn = new CRM_Financial_DAO_FinancialTrxn();
60 $trxn->copyValues($params);
62 if (!CRM_Utils_Rule
::currencyCode($trxn->currency
)) {
63 $config = CRM_Core_Config
::singleton();
64 $trxn->currency
= $config->defaultCurrency
;
69 // save to entity_financial_trxn table
70 $entityFinancialTrxnParams
72 'entity_table' => "civicrm_contribution",
73 'financial_trxn_id' => $trxn->id
,
74 'amount' => $params['total_amount'],
75 'currency' => $trxn->currency
,
78 if (!empty($trxnEntityTable)) {
79 $entityFinancialTrxnParams['entity_table'] = $trxnEntityTable['entity_table'];
80 $entityFinancialTrxnParams['entity_id'] = $trxnEntityTable['entity_id'];
83 $entityFinancialTrxnParams['entity_id'] = $params['contribution_id'];
86 $entityTrxn = new CRM_Financial_DAO_EntityFinancialTrxn();
87 $entityTrxn->copyValues($entityFinancialTrxnParams);
93 * @param int $contributionId
94 * @param int $contributionFinancialTypeId
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');
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";
106 $p[1] = array($contributionId, 'Integer');
107 $p[2] = array($toFinancialAccount, 'Integer');
109 $balanceAmtDAO = CRM_Core_DAO
::executeQuery($q, $p);
111 if ($balanceAmtDAO->N
) {
112 $ret['total_amount'] = 0;
114 while ($balanceAmtDAO->fetch()) {
115 $ret['trxn_id'] = $balanceAmtDAO->id
;
116 $ret['total_amount'] +
= $balanceAmtDAO->total_amount
;
123 * Fetch object based on array of properties.
125 * @param array $params
126 * (reference ) an assoc array of name/value pairs.
127 * @param array $defaults
128 * (reference ) an assoc array to hold the flattened values.
130 * @return CRM_Contribute_BAO_ContributionType
132 public 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;
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.
147 * Id of the entity usually the contactID.
148 * @param string $orderBy
149 * To get single trxn id for a entity table i.e last or first.
150 * @param bool $newTrxn
153 * array of category id's the contact belongs to.
156 public static function getFinancialTrxnId($entity_id, $orderBy = 'ASC', $newTrxn = FALSE) {
157 $ids = array('entityFinancialTrxnId' => NULL, 'financialTrxnId' => NULL);
161 $condition = " AND ((ceft1.entity_table IS NOT NULL) OR (cft.payment_instrument_id IS NOT NULL AND ceft1.entity_table IS NULL)) ";
165 $orderBy = CRM_Utils_Type
::escape($orderBy, 'String');
168 $query = "SELECT ceft.id, ceft.financial_trxn_id FROM `civicrm_financial_trxn` cft
169 LEFT JOIN civicrm_entity_financial_trxn ceft
170 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
171 LEFT JOIN civicrm_entity_financial_trxn ceft1
172 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
173 LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
174 WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL)
176 ORDER BY cft.id {$orderBy}
179 $params = array(1 => array($entity_id, 'Integer'));
180 $dao = CRM_Core_DAO
::executeQuery($query, $params);
182 $ids['entityFinancialTrxnId'] = $dao->id
;
183 $ids['financialTrxnId'] = $dao->financial_trxn_id
;
189 * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
190 * @todo This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
192 * @param int $entity_id
193 * Id of the entity usually the contactID.
196 * array of catagory id's the contact belongs to.
199 public static function getFinancialTrxnTotal($entity_id) {
201 SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft
202 LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id
203 WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1
206 $sqlParams = array(1 => array($entity_id, 'Integer'));
207 return CRM_Core_DAO
::singleValueQuery($query, $sqlParams);
212 * Given an financial_trxn_id check for previous entity_financial_trxn.
214 * @param $financial_trxn_id
215 * Id of the latest payment.
219 * array of previous payments
222 public static function getPayments($financial_trxn_id) {
224 SELECT ef1.financial_trxn_id, sum(ef1.amount) amount
225 FROM civicrm_entity_financial_trxn ef1
226 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id
227 WHERE ef2.financial_trxn_id =%1
228 AND ef2.entity_table = 'civicrm_financial_trxn'
229 AND ef1.entity_table = 'civicrm_financial_item'
230 GROUP BY ef1.financial_trxn_id
232 SELECT ef1.financial_trxn_id, ef1.amount
233 FROM civicrm_entity_financial_trxn ef1
234 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id
235 WHERE ef2.financial_trxn_id =%1
236 AND ef2.entity_table = 'civicrm_financial_trxn'
237 AND ef1.entity_table = 'civicrm_financial_trxn'";
239 $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
240 $dao = CRM_Core_DAO
::executeQuery($query, $sqlParams);
243 while ($dao->fetch()) {
244 $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id
;
245 $result[$i]['amount'] = $dao->amount
;
249 if (empty($result)) {
250 $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'";
251 $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
252 $dao = CRM_Core_DAO
::executeQuery($query, $sqlParams);
255 $result[0]['financial_trxn_id'] = $financial_trxn_id;
256 $result[0]['amount'] = $dao->amount
;
263 * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
264 * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
267 * Id of the entity usually the contactID.
268 * @param string $entity_table
269 * Name of the entity table usually 'civicrm_contact'.
272 * array of catagory id's the contact belongs to.
275 public static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') {
276 $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft
277 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'
278 LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2
279 WHERE lt.entity_id = %1 ";
281 $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String'));
282 $dao = CRM_Core_DAO
::executeQuery($query, $sqlParams);
283 while ($dao->fetch()) {
284 $result[$dao->financial_trxn_id
][$dao->id
] = $dao->amount
;
286 if (!empty($result)) {
295 * Delete financial transaction.
297 * @param int $entity_id
299 * TRUE on success, FALSE otherwise.
301 public static function deleteFinancialTrxn($entity_id) {
302 $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft
303 LEFT JOIN civicrm_entity_financial_trxn ceft
304 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
305 LEFT JOIN civicrm_entity_financial_trxn ceft1
306 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
307 LEFT JOIN civicrm_financial_item cfi
308 ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
309 WHERE ceft.entity_id = %1";
310 CRM_Core_DAO
::executeQuery($query, array(1 => array($entity_id, 'Integer')));
315 * Create financial transaction for premium.
318 public static function createPremiumTrxn($params) {
319 if ((empty($params['financial_type_id']) ||
empty($params['contributionId'])) && empty($params['oldPremium'])) {
323 if (!empty($params['cost'])) {
324 $contributionStatuses = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
325 $financialAccountType = CRM_Contribute_PseudoConstant
::financialAccountType($params['financial_type_id']);
326 $accountRelationship = CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND label IN ('Premiums Inventory Account is', 'Cost of Sales Account is')");
327 $toFinancialAccount = !empty($params['isDeleted']) ?
'Premiums Inventory Account is' : 'Cost of Sales Account is';
328 $fromFinancialAccount = !empty($params['isDeleted']) ?
'Cost of Sales Account is' : 'Premiums Inventory Account is';
329 $accountRelationship = array_flip($accountRelationship);
330 $financialtrxn = array(
331 'to_financial_account_id' => $financialAccountType[$accountRelationship[$toFinancialAccount]],
332 'from_financial_account_id' => $financialAccountType[$accountRelationship[$fromFinancialAccount]],
333 'trxn_date' => date('YmdHis'),
334 'total_amount' => CRM_Utils_Array
::value('cost', $params) ?
$params['cost'] : 0,
335 'currency' => CRM_Utils_Array
::value('currency', $params),
336 'status_id' => array_search('Completed', $contributionStatuses),
338 $trxnEntityTable['entity_table'] = 'civicrm_contribution';
339 $trxnEntityTable['entity_id'] = $params['contributionId'];
340 CRM_Core_BAO_FinancialTrxn
::create($financialtrxn, $trxnEntityTable);
343 if (!empty($params['oldPremium'])) {
344 $premiumParams = array(
345 'id' => $params['oldPremium']['product_id'],
347 $productDetails = array();
348 CRM_Contribute_BAO_ManagePremiums
::retrieve($premiumParams, $productDetails);
350 'cost' => CRM_Utils_Array
::value('cost', $productDetails),
351 'currency' => CRM_Utils_Array
::value('currency', $productDetails),
352 'financial_type_id' => CRM_Utils_Array
::value('financial_type_id', $productDetails),
353 'contributionId' => $params['oldPremium']['contribution_id'],
356 CRM_Core_BAO_FinancialTrxn
::createPremiumTrxn($params);
361 * Create financial trxn and items when fee is charged.
363 * @param array $params
364 * To create trxn entries.
368 public static function recordFees($params) {
369 $expenseTypeId = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
370 $domainId = CRM_Core_Config
::domainID();
372 if (!empty($params['prevContribution'])) {
373 $amount = $params['prevContribution']->fee_amount
;
375 $amount = $params['fee_amount'] - $amount;
379 $contributionId = isset($params['contribution']->id
) ?
$params['contribution']->id
: $params['contribution_id'];
380 if (empty($params['financial_type_id'])) {
381 $financialTypeId = CRM_Core_DAO
::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id', 'id');
384 $financialTypeId = $params['financial_type_id'];
386 $financialAccount = CRM_Contribute_PseudoConstant
::financialAccountType($financialTypeId, $expenseTypeId);
388 $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id'];
389 $params['trxnParams']['to_financial_account_id'] = $financialAccount;
390 $params['trxnParams']['total_amount'] = $amount;
391 $params['trxnParams']['fee_amount'] = $params['trxnParams']['net_amount'] = 0;
392 $params['trxnParams']['status_id'] = CRM_Core_OptionGroup
::getValue('contribution_status', 'Completed', 'name');
393 $params['trxnParams']['contribution_id'] = $contributionId;
394 $trxn = self
::create($params['trxnParams']);
395 if (empty($params['entity_id'])) {
396 $financialTrxnID = CRM_Core_BAO_FinancialTrxn
::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC');
397 $params['entity_id'] = $financialTrxnID['financialTrxnId'];
401 'financial_account_id' => $financialAccount,
402 'contact_id' => CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'),
403 'created_date' => date('YmdHis'),
404 'transaction_date' => date('YmdHis'),
406 'description' => 'Fee',
407 'status_id' => CRM_Core_OptionGroup
::getValue('financial_item_status', 'Paid', 'name'),
408 'entity_table' => 'civicrm_financial_trxn',
409 'entity_id' => $params['entity_id'],
410 'currency' => $params['trxnParams']['currency'],
412 $trxnIDS['id'] = $trxn->id
;
413 $financialItem = CRM_Financial_BAO_FinancialItem
::create($fItemParams, NULL, $trxnIDS);
417 * get partial payment amount and type of it.
419 * @param int $entityId
420 * @param string $entityName
421 * @param bool $returnType
422 * @param int $lineItemTotal
424 * @return array|int|NULL|string
425 * [payment type => amount]
426 * payment type: 'amount_owed' or 'refund_due'
428 public static function getPartialPaymentWithType($entityId, $entityName = 'participant', $returnType = TRUE, $lineItemTotal = NULL) {
430 if (empty($entityName)) {
434 if ($entityName == 'participant') {
435 $contributionId = CRM_Core_DAO
::getFieldValue('CRM_Event_DAO_ParticipantPayment', $entityId, 'contribution_id', 'participant_id');
436 $financialTypeId = CRM_Core_DAO
::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id');
438 if ($contributionId && $financialTypeId) {
439 $statusId = CRM_Core_OptionGroup
::getValue('contribution_status', 'Completed', 'name');
440 $refundStatusId = CRM_Core_OptionGroup
::getValue('contribution_status', 'Refunded', 'name');
442 $relationTypeId = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' "));
443 $toFinancialAccount = CRM_Contribute_PseudoConstant
::financialAccountType($financialTypeId, $relationTypeId);
444 $feeRelationTypeId = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
445 $feeFinancialAccount = CRM_Contribute_PseudoConstant
::financialAccountType($financialTypeId, $feeRelationTypeId);
447 if (empty($lineItemTotal)) {
448 $ids = CRM_Event_BAO_Participant
::getParticipantIds($contributionId);
449 if (count($ids) > 1) {
451 foreach ($ids as $val) {
452 $total +
= CRM_Price_BAO_LineItem
::getLineTotal($val, 'civicrm_participant');
454 $lineItemTotal = $total;
457 $lineItemTotal = CRM_Price_BAO_LineItem
::getLineTotal($entityId, 'civicrm_participant');
461 SELECT SUM(ft.total_amount)
462 FROM civicrm_financial_trxn ft
463 LEFT JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution')
464 LEFT JOIN civicrm_contribution c ON (eft.entity_id = c.id)
465 LEFT JOIN civicrm_participant_payment pp ON (pp.contribution_id = c.id)
466 WHERE pp.participant_id = {$entityId} AND ft.to_financial_account_id != {$toFinancialAccount} AND ft.to_financial_account_id != {$feeFinancialAccount}
467 AND ft.status_id IN ({$statusId}, {$refundStatusId})
469 $ftTotalAmt = CRM_Core_DAO
::singleValueQuery($sqlFtTotalAmt);
472 $value = $paymentVal = $lineItemTotal - $ftTotalAmt;
476 if ($paymentVal < 0) {
477 $value['refund_due'] = $paymentVal;
479 elseif ($paymentVal > 0) {
480 $value['amount_owed'] = $paymentVal;
482 elseif ($lineItemTotal == $ftTotalAmt) {
483 $value['full_paid'] = $ftTotalAmt;