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

   1 {* file to handle db changes in 4.3.6 during upgrade *}
   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
  10   WHEN ce.id IS NOT NULL
  11     THEN ce.financial_type_id
  12   WHEN ccg.id IS NOT NULL
  13     THEN ccg.financial_type_id
  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
  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;
  45
  46 -- CRM-13096
  47 DROP TABLE IF EXISTS civicrm_official_receipt;