X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=656015ef7009b12b018165729806c93efba9bf32;hp=297c687942fbc745054306e2974238dd0aabfc42;hb=0b30967a52114d24dbe09fe07e5df77090a2bf4d;hpb=08cd31db39000e341ae52d70127f0d25052387ca

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 297c687..656015e 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1052,7 +1052,7 @@ DECLARE
   
 BEGIN
 
-	EXECUTE ('TRUNCATE ' || c || ';');
+	EXECUTE ('DELETE FROM ' || c || ';');
 	EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
 	EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
 
@@ -1275,7 +1275,7 @@ BEGIN
 	DELETE FROM asset.opac_visible_copies;
 
 	INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
-		SELECT
+		SELECT DISTINCT
 			cp.id, cp.circ_lib, cn.record
 		FROM
 			asset.copy cp
@@ -1291,8 +1291,109 @@ BEGIN
 			cs.opac_visible AND
 			cl.opac_visible AND
 			cp.opac_visible AND
-			a.opac_visible;
+			a.opac_visible AND
+			cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
 
 END;
 
 $$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
+
+DECLARE
+  old_volume     BIGINT;
+  new_volume     BIGINT;
+  bib            BIGINT;
+  old_owning_lib INTEGER;
+	old_label      TEXT;
+  remainder      BIGINT;
+
+BEGIN
+
+  -- Gather information
+  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
+  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
+  SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
+
+	-- Bail out if the new_owning_lib is not the ID of an org_unit
+	IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
+		RAISE WARNING 
+			'% is not a valid actor.org_unit ID; no change made.', 
+				new_owning_lib;
+		RETURN;
+	END IF;
+
+  -- Bail out discreetly if the owning_lib is already correct
+  IF new_owning_lib = old_owning_lib THEN
+    RETURN;
+  END IF;
+
+  -- Check whether we already have a destination volume available
+  SELECT id INTO new_volume FROM asset.call_number 
+    WHERE 
+      record = bib AND
+      owning_lib = new_owning_lib AND
+      label = old_label AND
+      NOT deleted;
+
+  -- Create destination volume if needed
+  IF NOT FOUND THEN
+    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
+      VALUES (1, 1, bib, new_owning_lib, old_label);
+    SELECT id INTO new_volume FROM asset.call_number
+      WHERE 
+        record = bib AND
+        owning_lib = new_owning_lib AND
+        label = old_label AND
+        NOT deleted;
+  END IF;
+
+  -- Move copy to destination
+  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
+
+  -- Delete source volume if it is now empty
+  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
+  IF NOT FOUND THEN
+    DELETE FROM asset.call_number WHERE id = old_volume;
+  END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
+
+-- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
+
+DECLARE
+	new_owning_lib	INTEGER;
+
+BEGIN
+
+	-- Parse the new_owner as an org unit ID or shortname
+	IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
+		SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
+		PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
+	ELSIF new_owner ~ E'^[0-9]+$' THEN
+		IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
+			RAISE INFO 
+				'%',
+				E'You don\'t need to put the actor.org_unit ID in quotes; '
+					|| E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
+			new_owning_lib := new_owner::INTEGER;
+		PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
+		END IF;
+	ELSE
+		RAISE WARNING 
+			'% is not a valid actor.org_unit shortname or ID; no change made.', 
+			new_owning_lib;
+		RETURN;
+	END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+