Commit | Line | Data |
---|---|---|
e993b6e5 | 1 | {* file to handle db changes in 4.3.6 during upgrade *} |
157b21d8 PN |
2 | -- CRM-13060 |
3 | UPDATE civicrm_price_set_entity cpse | |
4 | LEFT JOIN civicrm_price_set cps ON cps.id = cpse.price_set_id | |
5 | LEFT JOIN civicrm_price_field cpf ON cps.id = cpf.price_set_id | |
6 | LEFT JOIN civicrm_price_field_value cpfv ON cpf.id = cpfv.price_field_id | |
7 | LEFT JOIN civicrm_event ce ON cpse.entity_id = ce.id AND cpse.entity_table = 'civicrm_event' | |
8 | LEFT JOIN civicrm_contribution_page ccg ON cpse.entity_id = ccg.id AND cpse.entity_table = 'civicrm_contribution_page' | |
9 | SET cpfv.financial_type_id = CASE | |
cc9b58f3 | 10 | WHEN ce.id IS NOT NULL |
157b21d8 | 11 | THEN ce.financial_type_id |
cc9b58f3 PN |
12 | WHEN ccg.id IS NOT NULL |
13 | THEN ccg.financial_type_id | |
157b21d8 PN |
14 | END, |
15 | cps.financial_type_id = CASE | |
16 | WHEN ce.id IS NOT NULL | |
17 | THEN ce.financial_type_id | |
18 | WHEN ccg.id IS NOT NULL | |
19 | THEN ccg.financial_type_id | |
20 | END | |
bf45dbe8 PN |
21 | WHERE cps.is_quick_config = 1; |
22 | ||
23 | -- CRM-12844 | |
24 | -- DELETE bad data | |
25 | DELETE cli FROM `civicrm_contribution` cc | |
26 | LEFT JOIN civicrm_line_item cli ON cli.entity_id = cc.id | |
27 | LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id AND cfi.entity_table = 'civicrm_line_item' | |
28 | LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id | |
29 | LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id | |
30 | WHERE cc.contribution_recur_id IS NOT NULL | |
31 | AND cli.entity_table = 'civicrm_contribution' AND cfi.id IS NULL | |
32 | AND cps.is_quick_config = 1; | |
33 | ||
34 | -- Set from_financial_account_id to null | |
35 | UPDATE `civicrm_contribution` cc | |
36 | LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id | |
37 | LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id | |
38 | LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id | |
39 | LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id | |
40 | LEFT JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cft.payment_processor_id | |
41 | SET cft.from_financial_account_id = NULL | |
42 | WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL | |
43 | AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = 1 AND cfi.entity_table = 'civicrm_line_item' AND cft.from_financial_account_id IS NOT NULL | |
44 | AND cefa.entity_table = 'civicrm_payment_processor' AND cefa.financial_account_id = cft.to_financial_account_id; | |
948e62a4 | 45 | |
46 | -- CRM-13096 | |
47 | DROP TABLE IF EXISTS civicrm_official_receipt; | |
3b67ab13 PN |
48 | |
49 | -- CRM-13231 | |
50 | SELECT @option_group_id_arel := max(id) from civicrm_option_group where name = 'account_relationship'; | |
51 | SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type'; | |
52 | SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat; | |
53 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opexp; | |
87f4e761 | 54 | SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID}; |
3b67ab13 PN |
55 | |
56 | SELECT @option_value_rel_id_exp := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Expense Account is'; | |
57 | ||
58 | INSERT IGNORE INTO civicrm_financial_account (id, name, contact_id, financial_account_type_id, description, account_type_code, accounting_code, is_active, is_default) | |
59 | VALUES (@financialAccountId, 'Banking Fees', @domainContactId, @opexp, 'Payment processor fees and manually recorded banking fees', 'EXP', '5200', 1, 1); | |
60 | ||
61 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opexp; | |
62 | ||
63 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) | |
64 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_exp, @financialAccountId | |
65 | FROM civicrm_financial_type cft | |
66 | LEFT JOIN civicrm_entity_financial_account ceft | |
67 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_exp AND ceft.entity_table = 'civicrm_financial_type' | |
68 | WHERE ceft.entity_id IS NULL; | |
69 | ||
70 | UPDATE civicrm_financial_trxn cft | |
71 | INNER JOIN civicrm_entity_financial_trxn ceft ON ceft.financial_trxn_id = cft .id | |
72 | INNER JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = cft .id | |
73 | INNER JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id | |
74 | INNER JOIN civicrm_contribution cc ON cc.id = ceft.entity_id | |
75 | INNER JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cc.financial_type_id | |
76 | SET cft.to_financial_account_id = cefa.financial_account_id | |
87f4e761 PN |
77 | WHERE ceft.entity_table = 'civicrm_contribution' AND ceft1.entity_table = 'civicrm_financial_item' AND cfi.entity_table = 'civicrm_financial_trxn' AND cft.to_financial_account_id IS NULL AND cefa.entity_table = 'civicrm_financial_type' AND cefa.account_relationship = @option_value_rel_id_exp; |
78 | ||
79 | -- Add COGS account relationship | |
80 | 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'; | |
81 | SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat; | |
82 | SET @financialAccountId := ''; | |
83 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opCost; | |
84 | ||
85 | -- CRM-13231 | |
86 | INSERT IGNORE INTO civicrm_financial_account (id, name, contact_id, financial_account_type_id, description, account_type_code, accounting_code, is_active, is_default) | |
87 | VALUES (@financialAccountId, 'Premiums', @domainContactId, @opCost, 'Account to record cost of premiums provided to payors', 'COGS', '5100', 1, 1); | |
88 | ||
89 | SELECT @financialAccountId := id FROM civicrm_financial_account WHERE is_default = 1 and financial_account_type_id = @opCost; | |
90 | ||
91 | INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) | |
92 | SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id_cg, @financialAccountId | |
93 | FROM civicrm_financial_type cft | |
94 | LEFT JOIN civicrm_entity_financial_account ceft | |
95 | ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id_cg AND ceft.entity_table = 'civicrm_financial_type' | |
96 | WHERE ceft.entity_id IS NULL; |