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

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 8bcff20..297c687 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1079,7 +1079,7 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX
 
     foreach my $field ( $marc->field('856') ) {
       if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
-           scalar(grep( $shortname, $field->subfield('9'))) == 0 ) {
+           ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
         $field->add_subfields( '9' => $shortname );
 				$field->update( ind2 => '0');
       }
@@ -1185,3 +1185,114 @@ CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETUR
   
 $$ LANGUAGE PLPERLU STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+  ou  INT;
+	org_unit_depth INT;
+	ou_parent INT;
+	parent_depth INT;
+  errors_found BOOLEAN;
+	ou_shortname TEXT;
+	parent_shortname TEXT;
+	ou_type_name TEXT;
+	parent_type TEXT;
+	type_id INT;
+	type_depth INT;
+	type_parent INT;
+	type_parent_depth INT;
+	proper_parent TEXT;
+
+BEGIN
+
+	errors_found := FALSE;
+
+-- Checking actor.org_unit_type
+
+	FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
+
+		SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
+		SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
+
+		IF type_parent IS NOT NULL THEN
+
+			SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
+
+			IF type_depth - type_parent_depth <> 1 THEN
+				SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
+				SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
+				RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
+					ou_type_name, type_depth, parent_type, type_parent_depth;
+				errors_found := TRUE;
+
+			END IF;
+
+		END IF;
+
+	END LOOP;
+
+-- Checking actor.org_unit
+
+  FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
+
+		SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+		SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
+		SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
+		SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+		SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+		SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+		SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+
+		IF ou_parent IS NOT NULL THEN
+
+			IF	(org_unit_depth - parent_depth <> 1) OR (
+				(SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
+			) THEN
+				RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
+					ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+				errors_found := TRUE;
+			END IF;
+
+		END IF;
+
+  END LOOP;
+
+	IF NOT errors_found THEN
+		RAISE INFO 'No errors found.';
+	END IF;
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
+
+BEGIN	
+
+	DELETE FROM asset.opac_visible_copies;
+
+	INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
+		SELECT
+			cp.id, cp.circ_lib, cn.record
+		FROM
+			asset.copy cp
+			JOIN asset.call_number cn ON (cn.id = cp.call_number)
+			JOIN actor.org_unit a ON (cp.circ_lib = a.id)
+			JOIN asset.copy_location cl ON (cp.location = cl.id)
+			JOIN config.copy_status cs ON (cp.status = cs.id)
+			JOIN biblio.record_entry b ON (cn.record = b.id)
+		WHERE 
+			NOT cp.deleted AND
+			NOT cn.deleted AND
+			NOT b.deleted AND
+			cs.opac_visible AND
+			cl.opac_visible AND
+			cp.opac_visible AND
+			a.opac_visible;
+
+END;
+
+$$ LANGUAGE plpgsql;