copyValues($params); $fids = array(); if (!CRM_Utils_Rule::currencyCode($trxn->currency)) { $config = CRM_Core_Config::singleton(); $trxn->currency = $config->defaultCurrency; } $trxn->save(); // save to entity_financial_trxn table $entityFinancialTrxnParams = array( 'entity_table' => "civicrm_contribution", 'financial_trxn_id' => $trxn->id, 'amount' => $params['total_amount'], 'currency' => $trxn->currency, ); if (!empty($trxnEntityTable)) { $entityFinancialTrxnParams['entity_table'] = $trxnEntityTable['entity_table']; $entityFinancialTrxnParams['entity_id'] = $trxnEntityTable['entity_id']; } else { $entityFinancialTrxnParams['entity_id'] = $params['contribution_id']; } $entityTrxn = new CRM_Financial_DAO_EntityFinancialTrxn(); $entityTrxn->copyValues($entityFinancialTrxnParams); $entityTrxn->save(); return $trxn; } /** * @param int $contributionId * @param int $contributionFinancialTypeId * * @return array */ public static function getBalanceTrxnAmt($contributionId, $contributionFinancialTypeId = NULL) { if (!$contributionFinancialTypeId) { $contributionFinancialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_BAO_Contribution', $contributionId, 'financial_type_id'); } $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' ")); $toFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($contributionFinancialTypeId, $relationTypeId); $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"; $p[1] = array($contributionId, 'Integer'); $p[2] = array($toFinancialAccount, 'Integer'); $balanceAmtDAO = CRM_Core_DAO::executeQuery($q, $p); $ret = array(); while ($balanceAmtDAO->fetch()) { $ret['trxn_id'] = $balanceAmtDAO->id; $ret['total_amount'] = $balanceAmtDAO->total_amount; } return $ret; } /** * Fetch object based on array of properties * * @param array $params * (reference ) an assoc array of name/value pairs. * @param array $defaults * (reference ) an assoc array to hold the flattened values. * * @return CRM_Contribute_BAO_ContributionType object * @static */ public static function retrieve(&$params, &$defaults) { $financialItem = new CRM_Financial_DAO_FinancialTrxn(); $financialItem->copyValues($params); if ($financialItem->find(TRUE)) { CRM_Core_DAO::storeValues($financialItem, $defaults); return $financialItem; } return NULL; } /** * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record. * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object. * * @param $entity_id * Id of the entity usually the contactID. * @param string $orderBy * To get single trxn id for a entity table i.e last or first. * @param bool $newTrxn * * @return array $tag array of category id's the contact belongs to. * * @static */ public static function getFinancialTrxnId($entity_id, $orderBy = 'ASC', $newTrxn = FALSE) { $ids = array('entityFinancialTrxnId' => NULL, 'financialTrxnId' => NULL); $condition = ""; if (!$newTrxn) { $condition = " AND ((ceft1.entity_table IS NOT NULL) OR (cft.payment_instrument_id IS NOT NULL AND ceft1.entity_table IS NULL)) "; } if ($orderBy) { $orderBy = CRM_Utils_Type::escape($orderBy, 'String'); } $query = "SELECT ceft.id, ceft.financial_trxn_id FROM `civicrm_financial_trxn` cft LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution' LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item' LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL) {$condition} ORDER BY cft.id {$orderBy} LIMIT 1;"; $params = array(1 => array($entity_id, 'Integer')); $dao = CRM_Core_DAO::executeQuery($query, $params); if ($dao->fetch()) { $ids['entityFinancialTrxnId'] = $dao->id; $ids['financialTrxnId'] = $dao->financial_trxn_id; } return $ids; } /** * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record. * @todo This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object. * * @param int $entity_id * Id of the entity usually the contactID. * * @return array $tag array of catagory id's the contact belongs to. * * @static */ public static function getFinancialTrxnTotal($entity_id) { $query = " SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1 "; $sqlParams = array(1 => array($entity_id, 'Integer')); return CRM_Core_DAO::singleValueQuery($query, $sqlParams); } /** * Given an financial_trxn_id check for previous entity_financial_trxn. * * @param $financial_trxn_id * Id of the latest payment. * * * @return array $payment array of previous payments * * @static */ public static function getPayments($financial_trxn_id) { $query = " SELECT ef1.financial_trxn_id, sum(ef1.amount) amount FROM civicrm_entity_financial_trxn ef1 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id WHERE ef2.financial_trxn_id =%1 AND ef2.entity_table = 'civicrm_financial_trxn' AND ef1.entity_table = 'civicrm_financial_item' GROUP BY ef1.financial_trxn_id UNION SELECT ef1.financial_trxn_id, ef1.amount FROM civicrm_entity_financial_trxn ef1 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id WHERE ef2.financial_trxn_id =%1 AND ef2.entity_table = 'civicrm_financial_trxn' AND ef1.entity_table = 'civicrm_financial_trxn'"; $sqlParams = array(1 => array($financial_trxn_id, 'Integer')); $dao = CRM_Core_DAO::executeQuery($query, $sqlParams); $i = 0; $result = array(); while ($dao->fetch()) { $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id; $result[$i]['amount'] = $dao->amount; $i++; } if (empty($result)) { $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'"; $sqlParams = array(1 => array($financial_trxn_id, 'Integer')); $dao = CRM_Core_DAO::executeQuery($query, $sqlParams); if ($dao->fetch()) { $result[0]['financial_trxn_id'] = $financial_trxn_id; $result[0]['amount'] = $dao->amount; } } return $result; } /** * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record. * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object. * * @param $entity_id * Id of the entity usually the contactID. * @param string $entity_table * Name of the entity table usually 'civicrm_contact'. * * @return array $tag array of catagory id's the contact belongs to. * * @static */ public static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') { $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft 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' LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2 WHERE lt.entity_id = %1 "; $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String')); $dao = CRM_Core_DAO::executeQuery($query, $sqlParams); while ($dao->fetch()) { $result[$dao->financial_trxn_id][$dao->id] = $dao->amount; } if (!empty($result)) { return $result; } else { return NULL; } } /** * Delete financial transaction * * @param int $entity_id * @return bool * TRUE on success, FALSE otherwise. * @static */ public static function deleteFinancialTrxn($entity_id) { $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution' LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item' LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id WHERE ceft.entity_id = %1"; CRM_Core_DAO::executeQuery($query, array(1 => array($entity_id, 'Integer'))); return TRUE; } /** * Create financial transaction for premium * * @static */ public static function createPremiumTrxn($params) { if ((empty($params['financial_type_id']) || empty($params['contributionId'])) && empty($params['oldPremium'])) { return; } if (!empty($params['cost'])) { $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); $financialAccountType = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id']); $accountRelationship = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND label IN ('Premiums Inventory Account is', 'Cost of Sales Account is')"); $toFinancialAccount = !empty($params['isDeleted']) ? 'Premiums Inventory Account is' : 'Cost of Sales Account is'; $fromFinancialAccount = !empty($params['isDeleted']) ? 'Cost of Sales Account is' : 'Premiums Inventory Account is'; $accountRelationship = array_flip($accountRelationship); $financialtrxn = array( 'to_financial_account_id' => $financialAccountType[$accountRelationship[$toFinancialAccount]], 'from_financial_account_id' => $financialAccountType[$accountRelationship[$fromFinancialAccount]], 'trxn_date' => date('YmdHis'), 'total_amount' => CRM_Utils_Array::value('cost', $params) ? $params['cost'] : 0, 'currency' => CRM_Utils_Array::value('currency', $params), 'status_id' => array_search('Completed', $contributionStatuses), ); $trxnEntityTable['entity_table'] = 'civicrm_contribution'; $trxnEntityTable['entity_id'] = $params['contributionId']; CRM_Core_BAO_FinancialTrxn::create($financialtrxn, $trxnEntityTable); } if (!empty($params['oldPremium'])) { $premiumParams = array( 'id' => $params['oldPremium']['product_id'], ); $productDetails = array(); CRM_Contribute_BAO_ManagePremiums::retrieve($premiumParams, $productDetails); $params = array( 'cost' => CRM_Utils_Array::value('cost', $productDetails), 'currency' => CRM_Utils_Array::value('currency', $productDetails), 'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $productDetails), 'contributionId' => $params['oldPremium']['contribution_id'], 'isDeleted' => TRUE, ); CRM_Core_BAO_FinancialTrxn::createPremiumTrxn($params); } } /** * Create financial trxn and items when fee is charged * * @param array $params * To create trxn entries. * * @static */ public static function recordFees($params) { $expenseTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' ")); $domainId = CRM_Core_Config::domainID(); $amount = 0; if (!empty($params['prevContribution'])) { $amount = $params['prevContribution']->fee_amount; } $amount = $params['fee_amount'] - $amount; if (!$amount) { return FALSE; } $contributionId = isset($params['contribution']->id) ? $params['contribution']->id : $params['contribution_id']; if (empty($params['financial_type_id'])) { $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id', 'id'); } else { $financialTypeId = $params['financial_type_id']; } $financialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $expenseTypeId); $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id']; $params['trxnParams']['to_financial_account_id'] = $financialAccount; $params['trxnParams']['total_amount'] = $amount; $params['trxnParams']['fee_amount'] = $params['trxnParams']['net_amount'] = 0; $params['trxnParams']['status_id'] = CRM_Core_OptionGroup::getValue('contribution_status','Completed','name'); $params['trxnParams']['contribution_id'] = $contributionId; $trxn = self::create($params['trxnParams']); if (empty($params['entity_id'])) { $financialTrxnID = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC'); $params['entity_id'] = $financialTrxnID['financialTrxnId']; } $fItemParams = array( 'financial_account_id' => $financialAccount, 'contact_id' => CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'), 'created_date' => date('YmdHis'), 'transaction_date' => date('YmdHis'), 'amount' => $amount, 'description' => 'Fee', 'status_id' => CRM_Core_OptionGroup::getValue('financial_item_status','Paid','name'), 'entity_table' => 'civicrm_financial_trxn', 'entity_id' => $params['entity_id'], 'currency' => $params['trxnParams']['currency'], ); $trxnIDS['id'] = $trxn->id; $financialItem = CRM_Financial_BAO_FinancialItem::create($fItemParams, NULL, $trxnIDS); } /* * get partial payment amount and type of it * return @array : payment type => amount * payment type : 'amount_owed' or 'refund_due' */ /** * @param int $entityId * @param string $entityName * @param bool $returnType * @param NULL $lineItemTotal * * @return array|int|NULL|string */ public static function getPartialPaymentWithType($entityId, $entityName = 'participant', $returnType = TRUE, $lineItemTotal = NULL) { $value = NULL; if (empty($entityName)) { return $value; } if ($entityName == 'participant') { $contributionId = CRM_Core_DAO::getFieldValue('CRM_Event_DAO_ParticipantPayment', $entityId, 'contribution_id', 'participant_id'); $financialTypeId = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'financial_type_id'); if ($contributionId && $financialTypeId) { $statusId = CRM_Core_OptionGroup::getValue('contribution_status', 'Completed', 'name'); $refundStatusId = CRM_Core_OptionGroup::getValue('contribution_status', 'Refunded', 'name'); $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' ")); $toFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $relationTypeId); $feeRelationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' ")); $feeFinancialAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $feeRelationTypeId); if (empty($lineItemTotal)) { $ids = CRM_Event_BAO_Participant::getParticipantIds($contributionId); if (count($ids) > 1) { $total = 0; foreach ($ids as $val) { $total += CRM_Price_BAO_LineItem::getLineTotal($val, 'civicrm_participant'); } $lineItemTotal = $total; } else { $lineItemTotal = CRM_Price_BAO_LineItem::getLineTotal($entityId, 'civicrm_participant'); } } $sqlFtTotalAmt = " SELECT SUM(ft.total_amount) FROM civicrm_financial_trxn ft LEFT JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution') LEFT JOIN civicrm_contribution c ON (eft.entity_id = c.id) LEFT JOIN civicrm_participant_payment pp ON (pp.contribution_id = c.id) WHERE pp.participant_id = {$entityId} AND ft.to_financial_account_id != {$toFinancialAccount} AND ft.to_financial_account_id != {$feeFinancialAccount} AND ft.status_id IN ({$statusId}, {$refundStatusId}) "; $ftTotalAmt = CRM_Core_DAO::singleValueQuery($sqlFtTotalAmt); $value = 0; if ($ftTotalAmt) { $value = $paymentVal = $lineItemTotal - $ftTotalAmt; } if ($returnType) { $value = array(); if ($paymentVal < 0) { $value['refund_due'] = $paymentVal; } elseif ($paymentVal > 0) { $value['amount_owed'] = $paymentVal; } elseif ($lineItemTotal == $ftTotalAmt) { $value['full_paid'] = $ftTotalAmt; } } } } return $value; } }