From 5085749d50e258db329b6c267b59d0b127a4b8c4 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 15 Apr 2009 00:47:06 +0000
Subject: [PATCH] teaching record merge function how to copy over 856 tags when
 there is a MARC-mapped URI entry

git-svn-id: svn://svn.open-ils.org/ILS/trunk@12870 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/040.schema.asset.sql  |  1 +
 Open-ILS/src/sql/Pg/090.schema.action.sql | 56 +++++++++++++++++++++++
 2 files changed, 57 insertions(+)

diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql
index 16cd91edc1..47234c8adc 100644
--- a/Open-ILS/src/sql/Pg/040.schema.asset.sql
+++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql
@@ -166,6 +166,7 @@ CREATE TABLE asset.uri_call_number_map (
     call_number INT         NOT NULL REFERENCES asset.call_number (id),
     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
 );
+CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
 
 CREATE TABLE asset.call_number_note (
 	id		BIGSERIAL			PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 3d029ce75e..494dbed5b4 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -356,7 +356,63 @@ DECLARE
 	target_cn     asset.call_number%ROWTYPE;
 	metarec       metabib.metarecord%ROWTYPE;
 	hold          action.hold_request%ROWTYPE;
+    uri_count     INT := 0;
+    counter       INT := 0;
+    uri_datafield TEXT;
+    uri_text      TEXT := '';
 BEGIN
+
+    -- move any 856 entries on records that have at least one MARC-mapped URI entry
+    SELECT  INTO uri_count COUNT(*)
+      FROM  asset.uri_call_number_map m
+            JOIN asset.call_number cn ON (m.call_number = cn.id)
+      WHERE cn.record = source_record;
+
+    IF uri_count > 0 THEN
+        
+        SELECT  COUNT(*) INTO counter
+          FROM  xpath_table(
+                    'id',
+                    'marc',
+                    'acq.lineitem',
+                    '//*[@tag="856"]',
+                    'id=' || lineitem
+                ) as t(i int,c text);
+    
+        FOR i IN 1 .. counter LOOP
+            SELECT  '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
+                        array_to_string(
+                            array_accum(
+                                '<subfield code="' || subfield || '">' ||
+                                regexp_replace(
+                                    regexp_replace(
+                                        regexp_replace(data,'&','&amp;','g'),
+                                        '>', '&gt;', 'g'
+                                    ),
+                                    '<', '&lt;', 'g'
+                                ) || '</subfield>'
+                            ), ''
+                        ) || '</datafield>' INTO uri_datafield
+              FROM  xpath_table(
+                        'id',
+                        'marc',
+                        'biblio.record_entry',
+                        '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
+                        '//*[@tag="856"][position()=' || i || ']/*[@code]',
+                        'id=' || source_record
+                    ) as t(id int,subfield text,data text);
+
+            uri_text := uri_text || uri_datafield;
+        END LOOP;
+
+        IF uri_text <> '' THEN
+            UPDATE  biblio.record_entry
+              SET   marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
+              WHERE id = target_record;
+        END IF;
+
+    END IF;
+
 	-- Find and move metarecords to the target record
 	SELECT	INTO metarec *
 	  FROM	metabib.metarecord
-- 
2.43.2