From 6d1029115e7a7954d735dc98d596b9475151168d Mon Sep 17 00:00:00 2001
From: Pradeep Nayak <pradeep@pradeep.(none)>
Date: Tue, 28 May 2013 17:58:52 +0530
Subject: [PATCH] -- added upgrade script to add default account relationships
 for financial type, worked on CRM-12470

----------------------------------------
* CRM-12470: Creating new financial type without AR account leads to unbalanced transactions
  http://issues.civicrm.org/jira/browse/CRM-12470
---
 CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl | 70 +++++++++++++++++++--
 1 file changed, 66 insertions(+), 4 deletions(-)

diff --git a/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl
index 7c52ef7b6d..3c0d1680fb 100644
--- a/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl
+++ b/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl
@@ -22,12 +22,74 @@ INSERT INTO civicrm_setting
 VALUES
 ({$domainID}, NULL, 1, 'CiviCRM Preferences', 'contact_smart_group_display', '{serialize}1{/serialize}');
 
+-- CRM-12665 remove options groups
+DELETE cov, cog FROM civicrm_option_group cog
+INNER JOIN civicrm_option_value cov ON cov.option_group_id = cog.id
+WHERE cog.name IN ('grant_program_status', 'allocation_algorithm');
+
 -- CRM-12470
 UPDATE civicrm_financial_account
 SET is_default = 1
 WHERE name IN ('{ts escape="sql"}Premiums{/ts}', '{ts escape="sql"}Banking Fees{/ts}', '{ts escape="sql"}Accounts Payable{/ts}', '{ts escape="sql"}Donation{/ts}');
 
--- CRM-12665 remove options groups
-DELETE cov, cog FROM civicrm_option_group cog
-INNER JOIN civicrm_option_value cov ON cov.option_group_id = cog.id
-WHERE cog.name IN ('grant_program_status', 'allocation_algorithm');
\ No newline at end of file
+SELECT @option_group_id_arel := max(id) from civicrm_option_group where name = 'account_relationship';
+SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type';
+SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID};
+
+-- for Accounts Receivable Account is
+SELECT @option_value_rel_id_ar  := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Accounts Receivable Account is';
+SELECT @arAccount := id FROM civicrm_financial_account WHERE LOWER(name) = 'accounts receivable';
+SELECT @arAccountEntity := financial_account_id FROM civicrm_entity_financial_account 
+ WHERE account_relationship = @option_value_rel_id_ar AND entity_table = 'civicrm_financial_type' LIMIT 1;
+
+INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id)
+SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_ar, IFNULL(@arAccount, @arAccountEntity)
+FROM civicrm_financial_type cft
+LEFT JOIN civicrm_entity_financial_account ceft
+ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_ar AND ceft.entity_table = 'civicrm_financial_type' 
+WHERE ceft.entity_id IS NULL;
+
+-- for income account is 
+SELECT @option_value_rel_id  := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Income Account is';
+SELECT @opval := value FROM civicrm_option_value WHERE name = 'Revenue' and option_group_id = @option_group_id_fat;
+
+-- create FA if not exists with same name as financial type
+INSERT INTO civicrm_financial_account (name, contact_id, financial_account_type_id, description, account_type_code, is_active)
+SELECT cft.name, @domainContactId, @opval, cft.name as description, 'INC', 1
+FROM civicrm_financial_type cft
+LEFT JOIN civicrm_entity_financial_account ceft
+ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' 
+LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) 
+WHERE ceft.entity_id IS NULL AND ca.id IS NULL;
+
+INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id)
+SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id, ca.id
+FROM civicrm_financial_type cft
+LEFT JOIN civicrm_entity_financial_account ceft
+ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' 
+LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) 
+WHERE ceft.entity_id IS NULL;
+
+-- for cost of sales
+SELECT @option_value_rel_id_cg := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Cost of Sales Account is';
+SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat;
+SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opCost;
+
+INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id)
+SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_cg, @financialAccountId
+FROM civicrm_financial_type cft
+LEFT JOIN civicrm_entity_financial_account ceft
+ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_cg AND ceft.entity_table = 'civicrm_financial_type' 
+WHERE ceft.entity_id IS NULL;
+
+-- for Expense Account is
+SELECT @option_value_rel_id_exp  := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Expense Account is';
+SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat;
+SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opexp;
+
+INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id)
+SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_exp, @financialAccountId
+FROM civicrm_financial_type cft
+LEFT JOIN civicrm_entity_financial_account ceft
+ON ceft.entity_id = cft.id AND ceft.account_relationship = 5 AND ceft.entity_table = 'civicrm_financial_type' 
+WHERE ceft.entity_id IS NULL;
-- 
2.25.1