From fc215d65e68f866e03a6316a53c3e3fb7125f261 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 31 Jul 2006 19:24:21 +0000
Subject: [PATCH] hold import stuff

git-svn-id: svn://svn.open-ils.org/ILS/trunk@5195 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Evergreen/src/extras/import/legacy_hold.sql | 94 +++++++++++++++++++++
 1 file changed, 94 insertions(+)
 create mode 100644 Evergreen/src/extras/import/legacy_hold.sql

diff --git a/Evergreen/src/extras/import/legacy_hold.sql b/Evergreen/src/extras/import/legacy_hold.sql
new file mode 100644
index 0000000000..d784c5e1dc
--- /dev/null
+++ b/Evergreen/src/extras/import/legacy_hold.sql
@@ -0,0 +1,94 @@
+BEGIN;
+
+-- These are copy level holds
+CREATE TABLE legacy_copy_hold_insert AS
+	SELECT	DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
+		cp.id AS current_copy,
+		cp.id AS target,
+		'C'::TEXT AS hold_type,
+		pou.id AS pickup_lib,
+		pou.id AS selection_lib,
+		CASE	WHEN lh.hold_range = 'SYSTEM' THEN 0
+			WHEN lh.hold_range = 'GROUP' THEN 1
+			ELSE 2
+		END AS selection_depth,
+		lh.hold_date AS request_time,
+		CASE	WHEN lh.available IN ('Y','I') THEN now()
+			ELSE NULL
+		END AS capture_time,
+		rou.id AS request_lib,
+		au.id AS requestor,
+		au.id AS usr
+	  FROM	legacy_hold lh
+		JOIN joined_legacy jl
+			ON (	jl.cat_key = lh.cat_key
+				AND jl.call_key = lh.call_key
+				AND jl.item_key = lh.call_key )
+		JOIN asset.copy cp ON (cp.barcode = jl.item_id)
+		JOIN actor.usr au ON (au.id = lh.user_key)
+		JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
+		JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
+	  WHERE	lh.hold_level = 'C';
+
+-- And these are CN level holds
+CREATE TABLE legacy_cn_hold_insert AS
+	SELECT	DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
+		cp.id AS current_copy,
+		cp.call_number AS target,
+		'V'::TEXT AS hold_type,
+		pou.id AS pickup_lib,
+		pou.id AS selection_lib,
+		CASE	WHEN lh.hold_range = 'SYSTEM' THEN 0
+			WHEN lh.hold_range = 'GROUP' THEN 1
+			ELSE 2
+		END AS selection_depth,
+		lh.hold_date AS request_time,
+		CASE	WHEN lh.available = 'Y' THEN now()
+			ELSE NULL
+		END AS capture_time,
+		rou.id AS request_lib,
+		au.id AS requestor,
+		au.id AS usr
+	  FROM	legacy_hold lh
+		JOIN joined_legacy jl
+			ON (	jl.cat_key = lh.cat_key
+				AND jl.call_key = lh.call_key
+				AND jl.item_key = lh.call_key )
+		JOIN asset.copy cp ON (cp.barcode = jl.item_id)
+		JOIN actor.usr au ON (au.id = lh.user_key)
+		JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
+		JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
+	  WHERE	lh.hold_level = 'A';
+
+-- And these are CN level holds
+CREATE TABLE legacy_title_hold_insert AS
+	SELECT	DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
+		cp.id AS current_copy,
+		cn.record AS target,
+		'T'::TEXT AS hold_type,
+		pou.id AS pickup_lib,
+		pou.id AS selection_lib,
+		CASE	WHEN lh.hold_range = 'SYSTEM' THEN 0
+			WHEN lh.hold_range = 'GROUP' THEN 1
+			ELSE 2
+		END AS selection_depth,
+		lh.hold_date AS request_time,
+		CASE	WHEN lh.available IN ('Y','I') THEN now()
+			ELSE NULL
+		END AS capture_time,
+		rou.id AS request_lib,
+		au.id AS requestor,
+		au.id AS usr
+	  FROM	legacy_hold lh
+		JOIN joined_legacy jl
+			ON (	jl.cat_key = lh.cat_key
+				AND jl.call_key = lh.call_key
+				AND jl.item_key = lh.call_key )
+		JOIN asset.copy cp ON (cp.barcode = jl.item_id)
+		JOIN asset.call_number cn ON (cp.call_number = cn.id)
+		JOIN actor.usr au ON (au.id = lh.user_key)
+		JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
+		JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
+	  WHERE	lh.hold_level = 'T';
+
+--COMMIT;
-- 
2.43.2