static function currencyCode($value)
{
static $currencyCodes = NULL;
if (!$currencyCodes) {
$currencyCodes = CRM_Core_PseudoConstant::currencyCode();
}
if (in_array($value, $currencyCodes)) {
return TRUE;
}
return FALSE;
}
/**
* @return bool
*/
public function createFinancialRecords()
{
$upgrade = new CRM_Upgrade_Form();
// update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
$query = "\nUPDATE civicrm_entity_financial_trxn ceft\nLEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id\nSET ceft.amount = total_amount\nWHERE cft.net_amount IS NOT NULL\nAND ceft.entity_table = 'civicrm_contribution'\n";
CRM_Core_DAO::executeQuery($query);
$contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
$completedStatus = array_search('Completed', $contributionStatus);
$pendingStatus = array_search('Pending', $contributionStatus);
$cancelledStatus = array_search('Cancelled', $contributionStatus);
$queryParams = array(1 => array($completedStatus, 'Integer'), 2 => array($pendingStatus, 'Integer'), 3 => array($cancelledStatus, 'Integer'));
$accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
$query = "\nSELECT id\nFROM civicrm_financial_account\nWHERE is_default = 1\nAND financial_account_type_id = {$accountType}\n";
$financialAccountId = CRM_Core_DAO::singleValueQuery($query);
$accountRelationsips = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount', 'account_relationship', CRM_Core_DAO::$_nullArray, 'validate');
$accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
$incomeAccountIs = array_search('Income Account is', $accountRelationsips);
$assetAccountIs = array_search('Asset Account is', $accountRelationsips);
$expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
$financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id', CRM_Core_DAO::$_nullArray, 'validate');
$unpaidStatus = array_search('Unpaid', $financialItemStatus);
$paidStatus = array_search('Paid', $financialItemStatus);
$validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode();
$validCurrencyCodes = implode("','", $validCurrencyCodes);
$config = CRM_Core_Config::singleton();
$defaultCurrency = $config->defaultCurrency;
$now = date('YmdHis');
//adding financial_trxn records and entity_financial_trxn records related to contribution
//Add temp column for easy entry in entity_financial_trxn
$sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
CRM_Core_DAO::executeQuery($sql);
//pending pay later status handling
$sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,\n check_number, to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT con.id as contribution_id, con.payment_instrument_id,\n IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,\n con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,\n con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,\n REPLACE(REPLACE(REPLACE(\n CASE\n WHEN con.receive_date IS NOT NULL THEN\n con.receive_date\n WHEN con.receipt_date IS NOT NULL THEN\n con.receipt_date\n ELSE\n {$now}\n END\n , '-', ''), ':', ''), ' ', '') as trxn_date\nFROM civicrm_contribution con\n LEFT JOIN civicrm_entity_financial_account efa\n ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'\n AND efa.account_relationship = {$accountsReceivableAccount})\nWHERE con.is_pay_later = 1\nAND con.contribution_status_id = {$pendingStatus}\n";
CRM_Core_DAO::executeQuery($sql);
//create a temp table to hold financial account id related to payment instruments
$tempTableName1 = CRM_Core_DAO::createTempTableName();
$sql = "\nCREATE TEMPORARY TABLE {$tempTableName1}\nSELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id\nFROM civicrm_entity_financial_account ceft\nINNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'\nINNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id\nWHERE cog.name = 'payment_instrument'\n";
CRM_Core_DAO::executeQuery($sql);
//CRM-12141
$sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
CRM_Core_DAO::executeQuery($sql);
//create temp table to process completed / cancelled contribution
$tempTableName2 = CRM_Core_DAO::createTempTableName();
$sql = "\nCREATE TEMPORARY TABLE {$tempTableName2}\nSELECT con.id as contribution_id, con.payment_instrument_id,\n IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,\n con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,\n con.check_number, NULL as from_financial_account_id,\n REPLACE(REPLACE(REPLACE(\n CASE\n WHEN con.receive_date IS NOT NULL THEN\n con.receive_date\n WHEN con.receipt_date IS NOT NULL THEN\n con.receipt_date\n ELSE\n {$now}\n END\n , '-', ''), ':', ''), ' ', '') as trxn_date,\n CASE\n WHEN con.payment_instrument_id IS NULL THEN\n {$financialAccountId}\n WHEN con.payment_instrument_id IS NOT NULL THEN\n tpi.financial_account_id\n END as to_financial_account_id,\n IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action\nFROM civicrm_contribution con\nLEFT JOIN civicrm_entity_financial_trxn eft\n ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)\nLEFT JOIN {$tempTableName1} tpi\n ON con.payment_instrument_id = tpi.instrument_id\nWHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})\n";
CRM_Core_DAO::executeQuery($sql);
// CRM-12141
$sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
CRM_Core_DAO::executeQuery($sql);
//handling for completed contribution and cancelled contribution
//insertion of new records
$sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,\n to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,\n tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,\n tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date\nFROM {$tempTableName2} tempI\nWHERE tempI.action = 'insert'\n";
CRM_Core_DAO::executeQuery($sql);
//update of existing records
$sql = "\nUPDATE civicrm_financial_trxn ft\n INNER JOIN {$tempTableName2} tempU\n ON (tempU.action != 'insert' AND ft.id = tempU.action)\nSET ft.from_financial_account_id = NULL,\n ft.to_financial_account_id = tempU.to_financial_account_id,\n ft.status_id = tempU.contribution_status_id,\n ft.payment_instrument_id = tempU.payment_instrument_id,\n ft.check_number = tempU.check_number,\n ft.contribution_id = tempU.contribution_id;";
CRM_Core_DAO::executeQuery($sql);
//insert the -ve transaction rows for cancelled contributions
$sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,\n check_number, to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,\n ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date\nFROM civicrm_financial_trxn ft\nWHERE ft.status_id = {$cancelledStatus};";
CRM_Core_DAO::executeQuery($sql);
//inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
//this also handles +ve and -ve both transaction entries for a cancelled contribution
$sql = "\nINSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount\nFROM civicrm_financial_trxn ft\nWHERE contribution_id IS NOT NULL AND\n ft.id NOT IN (SELECT financial_trxn_id\n FROM civicrm_entity_financial_trxn\n WHERE entity_table = 'civicrm_contribution'\n AND entity_id = ft.contribution_id)";
CRM_Core_DAO::executeQuery($sql);
//end of adding financial_trxn records and entity_financial_trxn records related to contribution
//update all linked line_item rows
// set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
// set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
// set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
// set line_item.financial_type_id = price_field_value.financial_type_id if its participant line item and line_item.price_field_value_id is set
$updateLineItemSql = "\nUPDATE civicrm_line_item li\n LEFT JOIN civicrm_contribution con\n ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')\n LEFT JOIN civicrm_price_field_value cpfv\n ON li.price_field_value_id = cpfv.id\n LEFT JOIN civicrm_participant cp\n ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')\n LEFT JOIN civicrm_event ce\n ON ce.id = cp.event_id\nSET li.financial_type_id = CASE\n WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN\n con.financial_type_id\n WHEN (con.contribution_page_id IS NOT NULL AND cp.id IS NULL) || (cp.id IS NOT NULL AND li.price_field_value_id IS NOT NULL) THEN\n cpfv.financial_type_id\n WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN\n ce.financial_type_id\n END";
CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams);
//add the financial_item entries
//add a temp column so that inserting entity_financial_trxn entries gets easy
$sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
CRM_Core_DAO::executeQuery($sql);
//add financial_item entries for contribution completed / pending pay later / cancelled
$contributionlineItemSql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\n\nSELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,\n IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total, con.currency, 'civicrm_line_item',\n li.id as line_item_id, li.label as line_item_label,\n IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,\n ft.id as f_trxn_id\nFROM civicrm_line_item li\n INNER JOIN civicrm_contribution con\n ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')\n INNER JOIN civicrm_financial_trxn ft\n ON (con.id = ft.contribution_id)\n LEFT JOIN civicrm_entity_financial_account efa\n ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'\n AND efa.account_relationship = {$incomeAccountIs})\nWHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams);
//add financial_item entries for event
$participantLineItemSql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\n\nSELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,\n IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,\n con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,\n IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,\n efa.financial_account_id as financial_account_id, ft.id as f_trxn_id\nFROM civicrm_line_item li\n INNER JOIN civicrm_participant par\n ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')\n INNER JOIN civicrm_participant_payment pp\n ON (pp.participant_id = par.id)\n INNER JOIN civicrm_contribution con\n ON (pp.contribution_id = con.id)\n INNER JOIN civicrm_financial_trxn ft\n ON (con.id = ft.contribution_id)\n LEFT JOIN civicrm_entity_financial_account efa\n ON (li.financial_type_id = efa.entity_id AND\n efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})\nWHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams);
//fee handling for contributions
//insert fee entries in financial_trxn for contributions
$sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
CRM_Core_DAO::executeQuery($sql);
$sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,\n to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)\n\nSELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,\n ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE\n WHEN efaPP.financial_account_id IS NOT NULL THEN\n efaPP.financial_account_id\n WHEN tpi.financial_account_id IS NOT NULL THEN\n tpi.financial_account_id\n ELSE\n {$financialAccountId}\n END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee\nFROM civicrm_contribution con\n INNER JOIN civicrm_financial_trxn ft\n ON (ft.contribution_id = con.id)\n LEFT JOIN civicrm_entity_financial_account efaFT\n ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'\n AND efaFT.account_relationship = {$expenseAccountIs})\n LEFT JOIN civicrm_entity_financial_account efaPP\n ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'\n AND efaPP.account_relationship = {$assetAccountIs})\n LEFT JOIN {$tempTableName1} tpi\n ON ft.payment_instrument_id = tpi.instrument_id\nWHERE ft.fee_amount IS NOT NULL AND ft.fee_amount != 0 AND (con.contribution_status_id IN (%1, %3) OR (con.contribution_status_id =%2 AND con.is_pay_later = 1))\nGROUP BY con.id";
CRM_Core_DAO::executeQuery($sql, $queryParams);
//link financial_trxn to contribution
$sql = "\nINSERT INTO civicrm_entity_financial_trxn\n (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount\nFROM civicrm_financial_trxn ft\nWHERE ft.is_fee = 1";
CRM_Core_DAO::executeQuery($sql);
//add fee related entries to financial item table
$domainId = CRM_Core_Config::domainID();
$domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
$sql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\nSELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,\n 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id\nFROM civicrm_financial_trxn ft\nWHERE ft.is_fee = 1;";
CRM_Core_DAO::executeQuery($sql);
//add entries to entity_financial_trxn table
$sql = "\nINSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount\nFROM civicrm_financial_item fi";
CRM_Core_DAO::executeQuery($sql);
//drop the temparory columns
$sql = "ALTER TABLE civicrm_financial_trxn\n DROP COLUMN contribution_id,\n DROP COLUMN is_fee;";
CRM_Core_DAO::executeQuery($sql);
$sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
CRM_Core_DAO::executeQuery($sql);
return TRUE;
}
static function currencyCode($value)
{
static $currencyCodes = null;
if (!$currencyCodes) {
$currencyCodes =& CRM_Core_PseudoConstant::currencyCode();
}
if (in_array($value, $currencyCodes)) {
return true;
}
return false;
}
/**
* get all the ISO 4217 currency codes
*
* so far, we use this for validation only, so there's no point of putting this into the database
*
* @access public
*
* @return array - array reference of all currency codes
* @static
*/
public static function ¤cyCode()
{
if (!self::$currencyCode) {
self::$currencyCode = array('AFN', 'ALL', 'DZD', 'USD', 'EUR', 'AOA', 'XCD', 'XCD', 'ARS', 'AMD', 'AWG', 'AUD', 'EUR', 'AZM', 'BSD', 'BHD', 'BDT', 'BBD', 'BYR', 'EUR', 'BZD', 'XOF', 'BMD', 'INR', 'BTN', 'BOB', 'BOV', 'BAM', 'BWP', 'NOK', 'BRL', 'USD', 'BND', 'BGN', 'XOF', 'BIF', 'KHR', 'XAF', 'CAD', 'CVE', 'KYD', 'XAF', 'XAF', 'CLP', 'CLF', 'CNY', 'AUD', 'AUD', 'COP', 'COU', 'KMF', 'XAF', 'CDF', 'NZD', 'CRC', 'XOF', 'HRK', 'CUP', 'CYP', 'CZK', 'DKK', 'DJF', 'XCD', 'DOP', 'USD', 'EGP', 'SVC', 'USD', 'XAF', 'ERN', 'EEK', 'ETB', 'FKP', 'DKK', 'FJD', 'EUR', 'EUR', 'EUR', 'XPF', 'EUR', 'XAF', 'GMD', 'GEL', 'EUR', 'GHC', 'GIP', 'EUR', 'DKK', 'XCD', 'EUR', 'USD', 'GTQ', 'GNF', 'GWP', 'XOF', 'GYD', 'HTG', 'USD', 'AUD', 'EUR', 'HNL', 'HKD', 'HUF', 'ISK', 'INR', 'IDR', 'XDR', 'IRR', 'IQD', 'EUR', 'ILS', 'EUR', 'JMD', 'JPY', 'JOD', 'KZT', 'KES', 'AUD', 'KPW', 'KRW', 'KWD', 'KGS', 'LAK', 'LVL', 'LBP', 'ZAR', 'LSL', 'LRD', 'LYD', 'CHF', 'LTL', 'EUR', 'MOP', 'MKD', 'MGA', 'MWK', 'MYR', 'MVR', 'XOF', 'MTL', 'USD', 'EUR', 'MRO', 'MUR', 'EUR', 'MXN', 'MXV', 'USD', 'MDL', 'EUR', 'MNT', 'XCD', 'MAD', 'MZM', 'MMK', 'ZAR', 'NAD', 'AUD', 'NPR', 'EUR', 'ANG', 'XPF', 'NZD', 'NIO', 'XOF', 'NGN', 'NZD', 'AUD', 'USD', 'NOK', 'OMR', 'PKR', 'USD', 'PAB', 'USD', 'PGK', 'PYG', 'PEN', 'PHP', 'NZD', 'PLN', 'EUR', 'USD', 'QAR', 'EUR', 'ROL', 'RON', 'RUB', 'RWF', 'SHP', 'XCD', 'XCD', 'EUR', 'XCD', 'WST', 'EUR', 'STD', 'SAR', 'XOF', 'CSD', 'EUR', 'SCR', 'SLL', 'SGD', 'SKK', 'SIT', 'SBD', 'SOS', 'ZAR', 'EUR', 'LKR', 'SDD', 'SRD', 'NOK', 'SZL', 'SEK', 'CHF', 'CHW', 'CHE', 'SYP', 'TWD', 'TJS', 'TZS', 'THB', 'USD', 'XOF', 'NZD', 'TOP', 'TTD', 'TND', 'TRY', 'TRL', 'TMM', 'USD', 'AUD', 'UGX', 'UAH', 'AED', 'GBP', 'USD', 'USS', 'USN', 'USD', 'UYU', 'UZS', 'VUV', 'VEB', 'VND', 'USD', 'USD', 'XPF', 'MAD', 'YER', 'ZMK', 'ZWD', 'XAU', 'XBA', 'XBB', 'XBC', 'XBD', 'XPD', 'XPT', 'XAG', 'XFU', 'XFO', 'XTS', 'XXX');
}
return self::$currencyCode;
}
function currencyCode($value)
{
if (!$GLOBALS['_CRM_UTILS_RULE']['currencyCodes']) {
$GLOBALS['_CRM_UTILS_RULE']['currencyCodes'] =& CRM_Core_PseudoConstant::currencyCode();
}
if (in_array($value, $GLOBALS['_CRM_UTILS_RULE']['currencyCodes'])) {
return true;
}
return false;
}