From 4281c1ee592c5b658cb47c5233c96cb8f1adecf5 Mon Sep 17 00:00:00 2001 From: Eileen McNaughton Date: Tue, 13 May 2014 21:59:44 +1200 Subject: [PATCH] CRM-14197 fill contribution_id data & fix entity references on membership data --- .../Incremental/sql/4.5.alpha1.mysql.tpl | 21 ++++++++++++++++++- 1 file changed, 20 insertions(+), 1 deletion(-) diff --git a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl index f1ddbf7734..6be98cebaa 100644 --- a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl @@ -521,10 +521,29 @@ WHERE co.id IS NULL; -- CRM-14197 Add contribution_id to civicrm_line_item -ALTER TABLE civicrm_line_item ADD contribution_id INT(10) COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)' NULL AFTER entity_id; +ALTER TABLE civicrm_line_item ADD contribution_id INT(10) COMMENT 'Contribution ID' NULL AFTER entity_id; -- FK to civicrm_contribution ALTER TABLE civicrm_line_item ADD CONSTRAINT `FK_civicrm_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES civicrm_contribution (`id`) ON DELETE SET NULL; +-- store contribution id for participant records +UPDATE civicrm_line_item li LEFT JOIN civicrm_participant_payment pp ON pp.participant_id = li.entity_id +SET li.contribution_id = pp.contribution_id +WHERE li.entity_table = 'civicrm_participant'; + +-- update membership line items to hold correct entity table & id & contribution id +UPDATE civicrm_line_item li +LEFT JOIN civicrm_membership_payment mp ON mp.contribution_id = li.entity_id +LEFT JOIN civicrm_price_field_value pv ON pv.id = li.price_field_value_id +SET li.entity_table = 'civicrm_membership', li.contribution_id = mp.contribution_id, li.entity_id = mp.membership_id +WHERE li.entity_table = 'civicrm_contribution' +AND pv.membership_type_id IS NOT NULL +AND membership_id IS NOT NULL; + +-- update line items for contributions with contribution id +UPDATE civicrm_line_item +SET contribution_id = entity_id +WHERE contribution_id IS NULL; + -- 2.25.1