From: Ben Ostrowsky <ben@esilibrary.com>
Date: Fri, 6 Jan 2012 17:32:30 +0000 (+0000)
Subject: Added two related functions for updating a copy's owning_lib, both called
X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=0b30967a52114d24dbe09fe07e5df77090a2bf4d

Added two related functions for updating a copy's owning_lib, both called
migration_tools.change_owning_lib. The difference is that if you call it with
the arguments (BIGINT, TEXT), it will try to look it up as a shortname and
pass the resulting actor.org_unit ID to change_owning_lib(BIGINT, INTEGER).
---

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 0ebfb54..656015e 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1297,3 +1297,103 @@ BEGIN
 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;
+
+