Commit | Line | Data |
---|---|---|
8690f439 | 1 | {* file to handle db changes in 4.7.19 during upgrade *} |
a6a9f772 PN |
2 | -- CRM-19715 |
3 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; | |
4 | SELECT @close_acc_period_act_val := `value` FROM civicrm_option_value WHERE option_group_id = @option_group_id_act AND name = 'Close Accounting Period'; | |
5 | SELECT @close_accounting_period_activity_count := count(id) FROM `civicrm_activity` WHERE `activity_type_id` = @close_acc_period_act_val; | |
6 | ||
7 | -- Delete Close Accounting Period activity type | |
5e0343e8 | 8 | DELETE FROM civicrm_option_value |
a6a9f772 | 9 | WHERE option_group_id = @option_group_id_act AND name = 'Close Accounting Period' AND @close_accounting_period_activity_count = 0; |
403c4f8b SL |
10 | |
11 | -- CRM-19517 Disable all price fields and price field options that use disabled fianancial types | |
12 | UPDATE civicrm_price_field_value cpfv | |
13 | INNER JOIN civicrm_financial_type cft ON cft.id = cpfv.financial_type_id | |
c5eaeb33 | 14 | INNER JOIN civicrm_price_field pf ON pf.id = cpfv.price_field_id |
15 | INNER JOIN civicrm_price_set ps ON ps.id = pf.price_set_id | |
403c4f8b | 16 | SET cpfv.is_active = 0 |
c5eaeb33 | 17 | WHERE cft.is_active = 0 AND ps.is_quick_config = 0; |
403c4f8b SL |
18 | |
19 | UPDATE civicrm_price_field cpf | |
20 | LEFT JOIN (SELECT DISTINCT price_field_id AS price_field_id | |
21 | FROM civicrm_price_field_value | |
22 | WHERE is_active = 1) AS price_field | |
23 | ON price_field.price_field_id = cpf.id | |
24 | SET cpf.is_active = 0 | |
25 | WHERE price_field.price_field_id IS NULL; | |
26 | ||
c4aa623c PN |
27 | -- CRM-20400 |
28 | {include file='../CRM/Upgrade/4.7.19.msg_template/civicrm_msg_template.tpl'} | |
70625c79 SL |
29 | |
30 | -- CRM-20402 Improve dectection of spam bounces | |
31 | SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Spam'; | |
32 | UPDATE civicrm_mailing_bounce_pattern SET pattern = '(detected|rejected) (as|due to) spam' WHERE bounce_type_id = @bounceTypeID AND pattern = '(detected|rejected) as spam'; | |
7cd6ec23 AS |
33 | |
34 | -- CRM-19464 add 'Supplemental Address 3', increment weights after supplemental_address_2 to slot in this new one | |
35 | SELECT @option_group_id_adOpt := max(id) from civicrm_option_group where name = 'address_options'; | |
36 | SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_adOpt; | |
37 | SELECT @supp2_wt := weight FROM civicrm_option_value WHERE name = 'supplemental_address_2'; | |
38 | UPDATE civicrm_option_value SET weight = weight + 1 WHERE option_group_id = @option_group_id_adOpt AND weight > @supp2_wt; | |
39 | INSERT INTO | |
cab0eb57 | 40 | `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}description{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`, `icon`) |
7cd6ec23 | 41 | VALUES |
cab0eb57 | 42 | (@option_group_id_adOpt, {localize}'{ts escape="sql"}Supplemental Address 3{/ts}'{/localize}, (SELECT @max_val := @max_val + 1), 'supplemental_address_3', NULL, 0, NULL, (SELECT @supp2_wt := @supp2_wt + 1), {localize}''{/localize}, 0, 0, 1, NULL, NULL, NULL); |
5e0343e8 | 43 | |
fceb7cea AH |
44 | -- Some legacy sites have `0000-00-00 00:00:00` values in |
45 | -- `civicrm_financial_trxn.trxn_date` which correspond to the same value in | |
46 | -- `civicrm_contribution.receive_date` | |
2f144cc6 KW |
47 | -- MySQL 5.7 may bork when comparing datetime columns to '0000-00-00 00:00:00' so cast the column to a CHAR(20) when comparing |
48 | UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE CAST(trxn_date AS CHAR(20)) = '0000-00-00 00:00:00'; | |
49 | UPDATE civicrm_contribution SET receive_date = NULL WHERE CAST(receive_date AS CHAR(20)) = '0000-00-00 00:00:00'; | |
fceb7cea | 50 | |
5e0343e8 | 51 | -- CRM-20439 rename card_type to card_type_id of civicrm_financial_trxn table (IIDA-126) |
52 | ALTER TABLE `civicrm_financial_trxn` CHANGE `card_type` `card_type_id` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'FK to accept_creditcard option group values'; | |
4e0ede5a PN |
53 | |
54 | -- CRM-20465 | |
55 | ALTER TABLE `civicrm_financial_trxn` CHANGE `pan_truncation` `pan_truncation` VARCHAR( 4 ) NULL DEFAULT NULL COMMENT 'Last 4 digits of credit card'; |