Commit | Line | Data |
---|---|---|
306d0516 KJ |
1 | {* file to handle db changes in 4.3.4 during upgrade*} |
2 | ||
3 | -- CRM-12466 | |
4 | INSERT INTO | |
5 | civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active) | |
6 | VALUES | |
7 | ('contact_smart_group_display', {localize}'{ts escape="sql"}Contact Smart Group View Options{/ts}'{/localize}, 1, 1); | |
8 | ||
9 | SELECT @option_group_id_csgOpt := max(id) FROM civicrm_option_group WHERE name = 'contact_smart_group_display'; | |
10 | ||
11 | INSERT INTO | |
12 | civicrm_option_value (option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, | |
13 | is_default, weight) | |
14 | VALUES | |
15 | (@option_group_id_csgOpt, {localize}'Show Smart Groups on Demand'{/localize}, 1, 'showondemand', NULL, 0, 0, 1), | |
16 | (@option_group_id_csgOpt, {localize}'Always Show Smart Groups'{/localize}, 2, 'alwaysshow', NULL, 0, 0, 2), | |
17 | (@option_group_id_csgOpt, {localize}'Hide Smart Groups'{/localize}, 3, 'hide' , NULL, 0, 0, 3); | |
18 | ||
19 | ||
20 | INSERT INTO civicrm_setting | |
21 | (domain_id, contact_id, is_domain, group_name, name, value) | |
22 | VALUES | |
23 | ({$domainID}, NULL, 1, 'CiviCRM Preferences', 'contact_smart_group_display', '{serialize}1{/serialize}'); | |
24 | ||
6d102911 PN |
25 | -- CRM-12665 remove options groups |
26 | DELETE cov, cog FROM civicrm_option_group cog | |
27 | INNER JOIN civicrm_option_value cov ON cov.option_group_id = cog.id | |
28 | WHERE cog.name IN ('grant_program_status', 'allocation_algorithm'); | |
29 | ||
f17d75bb PN |
30 | -- CRM-12470 |
31 | UPDATE civicrm_financial_account | |
32 | SET is_default = 1 | |
4ae33ee6 | 33 | WHERE name IN ('Premiums', 'Banking Fees', 'Accounts Payable', 'Donation'); |
50638468 | 34 | |
6d102911 PN |
35 | SELECT @option_group_id_arel := max(id) from civicrm_option_group where name = 'account_relationship'; |
36 | SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type'; | |
37 | SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID}; | |
38 | ||
39 | -- for Accounts Receivable Account is | |
40 | 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'; | |
41 | SELECT @arAccount := id FROM civicrm_financial_account WHERE LOWER(name) = 'accounts receivable'; | |
8ef12e64 | 42 | SELECT @arAccountEntity := financial_account_id FROM civicrm_entity_financial_account |
6d102911 PN |
43 | WHERE account_relationship = @option_value_rel_id_ar AND entity_table = 'civicrm_financial_type' LIMIT 1; |
44 | ||
45 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) | |
46 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_ar, IFNULL(@arAccount, @arAccountEntity) | |
47 | FROM civicrm_financial_type cft | |
48 | LEFT JOIN civicrm_entity_financial_account ceft | |
8ef12e64 | 49 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_ar AND ceft.entity_table = 'civicrm_financial_type' |
6d102911 PN |
50 | WHERE ceft.entity_id IS NULL; |
51 | ||
8ef12e64 | 52 | -- for income account is |
6d102911 PN |
53 | SELECT @option_value_rel_id := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Income Account is'; |
54 | SELECT @opval := value FROM civicrm_option_value WHERE name = 'Revenue' and option_group_id = @option_group_id_fat; | |
55 | ||
56 | -- create FA if not exists with same name as financial type | |
57 | INSERT INTO civicrm_financial_account (name, contact_id, financial_account_type_id, description, account_type_code, is_active) | |
58 | SELECT cft.name, @domainContactId, @opval, cft.name as description, 'INC', 1 | |
59 | FROM civicrm_financial_type cft | |
60 | LEFT JOIN civicrm_entity_financial_account ceft | |
8ef12e64 | 61 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' |
62 | LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) | |
6d102911 PN |
63 | WHERE ceft.entity_id IS NULL AND ca.id IS NULL; |
64 | ||
65 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) | |
66 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id, ca.id | |
67 | FROM civicrm_financial_type cft | |
68 | LEFT JOIN civicrm_entity_financial_account ceft | |
8ef12e64 | 69 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' |
70 | LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) | |
6d102911 PN |
71 | WHERE ceft.entity_id IS NULL; |
72 | ||
73 | -- for cost of sales | |
74 | 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'; | |
75 | SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat; | |
76 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opCost; | |
77 | ||
3b67ab13 PN |
78 | -- CRM-13231 |
79 | INSERT IGNORE INTO civicrm_financial_account (id, name, contact_id, financial_account_type_id, description, account_type_code, accounting_code, is_active, is_default) | |
80 | VALUES (@financialAccountId, 'Premiums', @domainContactId, @opCost, 'Account to record cost of premiums provided to payors', 'COGS', '5100', 1, 1); | |
81 | ||
82 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opCost; | |
83 | ||
6d102911 PN |
84 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) |
85 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_cg, @financialAccountId | |
86 | FROM civicrm_financial_type cft | |
87 | LEFT JOIN civicrm_entity_financial_account ceft | |
8ef12e64 | 88 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_cg AND ceft.entity_table = 'civicrm_financial_type' |
6d102911 PN |
89 | WHERE ceft.entity_id IS NULL; |
90 | ||
91 | -- for Expense Account is | |
92 | SELECT @option_value_rel_id_exp := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Expense Account is'; | |
93 | SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat; | |
3b67ab13 | 94 | SET @financialAccountId := ''; |
6d102911 PN |
95 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opexp; |
96 | ||
3b67ab13 PN |
97 | -- CRM-13231 |
98 | INSERT IGNORE INTO civicrm_financial_account (id, name, contact_id, financial_account_type_id, description, account_type_code, accounting_code, is_active, is_default) | |
99 | VALUES (@financialAccountId, 'Banking Fees', @domainContactId, @opexp, 'Payment processor fees and manually recorded banking fees', 'EXP', '5200', 1, 1); | |
100 | ||
101 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opexp; | |
102 | ||
103 | ||
6d102911 PN |
104 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) |
105 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_exp, @financialAccountId | |
106 | FROM civicrm_financial_type cft | |
107 | LEFT JOIN civicrm_entity_financial_account ceft | |
03a8c3dc | 108 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_exp AND ceft.entity_table = 'civicrm_financial_type' |
6d102911 | 109 | WHERE ceft.entity_id IS NULL; |