CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl

   1 {* file to handle db changes in 4.7.19 during upgrade *}
   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
   8 DELETE FROM civicrm_option_value
   9     WHERE option_group_id = @option_group_id_act AND name = 'Close Accounting Period' AND @close_accounting_period_activity_count = 0;
  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
  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
  16 SET cpfv.is_active = 0
  17 WHERE cft.is_active = 0 AND ps.is_quick_config = 0;
  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
  27 -- CRM-20400
  28 {include file='../CRM/Upgrade/4.7.19.msg_template/civicrm_msg_template.tpl'}
  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';
  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
  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`)
  41 VALUES
  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);
  43
  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`
  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';
  50
  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';
  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';