From 354dde8972d4793213fcce715bc0de89f6b7a890 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Fri, 19 May 2023 00:53:59 -0700 Subject: [PATCH] (REF) civicrm_data - Extract civicrm_financial_account --- .../civicrm_financial_account.sqldata.php | 138 ++++++++++++++++++ xml/templates/civicrm_data.tpl | 22 +-- 2 files changed, 139 insertions(+), 21 deletions(-) create mode 100644 sql/civicrm_data/civicrm_financial_account.sqldata.php diff --git a/sql/civicrm_data/civicrm_financial_account.sqldata.php b/sql/civicrm_data/civicrm_financial_account.sqldata.php new file mode 100644 index 0000000000..cb0b311f20 --- /dev/null +++ b/sql/civicrm_data/civicrm_financial_account.sqldata.php @@ -0,0 +1,138 @@ +addDefaults([ + 'contact_id' => new Literal('@contactID'), + 'is_active' => 1, + 'is_reserved' => 0, + ]) + ->addValues([ + [ + 'name' => ts('Donation'), + 'financial_account_type_id' => new Literal('@opval'), + 'description' => 'Default account for donations', + 'accounting_code' => '4200', + 'account_type_code' => 'INC', + 'is_deductible' => 1, + 'is_default' => 1, + ], + [ + 'name' => ts('Member Dues'), + 'financial_account_type_id' => new Literal('@opval'), + 'description' => 'Default account for membership sales', + 'accounting_code' => '4400', + 'account_type_code' => 'INC', + 'is_deductible' => 1, + 'is_default' => 0, + ], + [ + 'name' => ts('Campaign Contribution'), + 'financial_account_type_id' => new Literal('@opval'), + 'description' => 'Sample account for recording payments to a campaign', + 'accounting_code' => '4100', + 'account_type_code' => 'INC', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Event Fee'), + 'financial_account_type_id' => new Literal('@opval'), + 'description' => 'Default account for event ticket sales', + 'accounting_code' => '4300', + 'account_type_code' => 'INC', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Banking Fees'), + 'financial_account_type_id' => new Literal('@opexp'), + 'description' => 'Payment processor fees and manually recorded banking fees', + 'accounting_code' => '5200', + 'account_type_code' => 'EXP', + 'is_deductible' => 0, + 'is_default' => 1, + ], + [ + 'name' => ts('Deposit Bank Account'), + 'financial_account_type_id' => new Literal('@opAsset'), + 'description' => 'All manually recorded cash and cheques go to this account', + 'accounting_code' => '1100', + 'account_type_code' => 'BANK', + 'is_deductible' => 0, + 'is_default' => 1, + ], + [ + 'name' => ts('Accounts Receivable'), + 'financial_account_type_id' => new Literal('@opAsset'), + 'description' => 'Amounts to be received later (eg pay later event revenues)', + 'accounting_code' => '1200', + 'account_type_code' => 'AR', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Accounts Payable'), + 'financial_account_type_id' => new Literal('@opLiability'), + 'description' => 'Amounts to be paid out such as grants and refunds', + 'accounting_code' => '2200', + 'account_type_code' => 'AP', + 'is_deductible' => 0, + 'is_default' => 1, + ], + [ + 'name' => ts('Premiums'), + 'financial_account_type_id' => new Literal('@opCost'), + 'description' => 'Account to record cost of premiums provided to payors', + 'accounting_code' => '5100', + 'account_type_code' => 'COGS', + 'is_deductible' => 0, + 'is_default' => 1, + ], + [ + 'name' => ts('Premiums inventory'), + 'financial_account_type_id' => new Literal('@opAsset'), + 'description' => 'Account representing value of premiums inventory', + 'accounting_code' => '1375', + 'account_type_code' => 'OCASSET', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Discounts'), + 'financial_account_type_id' => new Literal('@opval'), + 'description' => 'Contra-revenue account for amounts discounted from sales', + 'accounting_code' => '4900', + 'account_type_code' => 'INC', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Payment Processor Account'), + 'financial_account_type_id' => new Literal('@opAsset'), + 'description' => 'Account to record payments into a payment processor merchant account', + 'accounting_code' => '1150', + 'account_type_code' => 'BANK', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Deferred Revenue - Event Fee'), + 'financial_account_type_id' => new Literal('@opLiability'), + 'description' => 'Event revenue to be recognized in future months when the events occur', + 'accounting_code' => '2730', + 'account_type_code' => 'OCLIAB', + 'is_deductible' => 0, + 'is_default' => 0, + ], + [ + 'name' => ts('Deferred Revenue - Member Dues'), + 'financial_account_type_id' => new Literal('@opLiability'), + 'description' => 'Membership revenue to be recognized in future months', + 'accounting_code' => '2740', + 'account_type_code' => 'OCLIAB', + 'is_deductible' => 0, + 'is_default' => 0, + ], + ]); diff --git a/xml/templates/civicrm_data.tpl b/xml/templates/civicrm_data.tpl index 77aaea05d3..8891e1a95b 100644 --- a/xml/templates/civicrm_data.tpl +++ b/xml/templates/civicrm_data.tpl @@ -66,26 +66,7 @@ SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and opt SELECT @opAsset := value FROM civicrm_option_value WHERE name = 'Asset' and option_group_id = @option_group_id_fat; SELECT @opLiability := value FROM civicrm_option_value WHERE name = 'Liability' and option_group_id = @option_group_id_fat; SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat; - -INSERT INTO - `civicrm_financial_account` (`name`, `contact_id`, `financial_account_type_id`, `description`, `accounting_code`, `account_type_code`, `is_reserved`, `is_active`, `is_deductible`, `is_default`) -VALUES - ( '{ts escape="sql"}Donation{/ts}' , @contactID, @opval, 'Default account for donations', '4200', 'INC', 0, 1, 1, 1 ), - ( '{ts escape="sql"}Member Dues{/ts}' , @contactID, @opval, 'Default account for membership sales', '4400', 'INC', 0, 1, 1, 0 ), - ( '{ts escape="sql"}Campaign Contribution{/ts}', @contactID, @opval, 'Sample account for recording payments to a campaign', '4100', 'INC', 0, 1, 0, 0 ), - ( '{ts escape="sql"}Event Fee{/ts}' , @contactID, @opval, 'Default account for event ticket sales', '4300', 'INC', 0, 1, 0, 0 ), - ( '{ts escape="sql"}Banking Fees{/ts}' , @contactID, @opexp, 'Payment processor fees and manually recorded banking fees', '5200', 'EXP', 0, 1, 0, 1 ), - ( '{ts escape="sql"}Deposit Bank Account{/ts}' , @contactID, @opAsset, 'All manually recorded cash and cheques go to this account', '1100', 'BANK', 0, 1, 0, 1 ), - ( '{ts escape="sql"}Accounts Receivable{/ts}' , @contactID, @opAsset, 'Amounts to be received later (eg pay later event revenues)', '1200', 'AR', 0, 1, 0, 0 ), - ( '{ts escape="sql"}Accounts Payable{/ts}' , @contactID, @opLiability, 'Amounts to be paid out such as grants and refunds', '2200', 'AP', 0, 1, 0, 1 ), - ( '{ts escape="sql"}Premiums{/ts}' , @contactID, @opCost, 'Account to record cost of premiums provided to payors', '5100', 'COGS', 0, 1, 0, 1 ), - ( '{ts escape="sql"}Premiums inventory{/ts}' , @contactID, @opAsset, 'Account representing value of premiums inventory', '1375', 'OCASSET', 0, 1, 0, 0 ), - ( '{ts escape="sql"}Discounts{/ts}' , @contactID, @opval, 'Contra-revenue account for amounts discounted from sales', '4900', 'INC', 0, 1, 0, 0 ), - ( '{ts escape="sql"}Payment Processor Account{/ts}', @contactID, @opAsset, 'Account to record payments into a payment processor merchant account', '1150', 'BANK', 0, 1, 0, 0), - ( '{ts escape="sql"}Deferred Revenue - Event Fee{/ts}', @contactID, @opLiability, 'Event revenue to be recognized in future months when the events occur', '2730', 'OCLIAB', 0, 1, 0, 0), - ( '{ts escape="sql"}Deferred Revenue - Member Dues{/ts}', @contactID, @opLiability, 'Membership revenue to be recognized in future months', '2740', 'OCLIAB', 0, 1, 0, 0 -); - +{php}echo (include "sql/civicrm_data/civicrm_financial_account.sqldata.php")->toSQL();{/php} -- CRM-6138 {include file='languages.tpl'} @@ -97,7 +78,6 @@ VALUES echo (include "sql/civicrm_data/civicrm_payment_processor_type.sqldata.php")->toSQL(); {/php} - -- the fuzzy default dedupe rules -- IndividualSupervised uses hard-coded optimized query (CRM_Dedupe_BAO_QueryBuilder_IndividualSupervised) INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved) -- 2.25.1