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

diff --git a/sql/base/base.sql b/sql/base/base.sql
index d197d9a..656015e 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -200,6 +200,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
+        PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' );
@@ -650,6 +651,27 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output TIMESTAMPTZ;
+    BEGIN
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
@@ -847,34 +869,34 @@ BEGIN
 
     -- Fetch the correct rules for this circulation
     EXECUTE ('
-      SELECT 
-        circ_lib, 
-        target_copy, 
-        usr, 
-        CASE 
-          WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE 
-          ELSE FALSE 
-        END 
-      FROM ' || tablename || ' WHERE id = ' || circ || ';') 
+      SELECT
+        circ_lib,
+        target_copy,
+        usr,
+        CASE
+          WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
+          ELSE FALSE
+        END
+      FROM ' || tablename || ' WHERE id = ' || circ || ';')
       INTO circ_lib, target_copy, usr, is_renewal ;
-    SELECT 
+    SELECT
       INTO this_duration_rule,
            this_fine_rule,
-           this_max_fine_rule 
-      duration_rule, 
-      recurring_fine_rule, 
-      max_fine_rule 
-      FROM action.find_circ_matrix_matchpoint( 
-        circ_lib, 
-        target_copy, 
-        usr, 
-        is_renewal 
+           this_max_fine_rule
+      duration_rule,
+      recurring_fine_rule,
+      max_fine_rule
+      FROM action.find_circ_matrix_matchpoint(
+        circ_lib,
+        target_copy,
+        usr,
+        is_renewal
         );
-    SELECT INTO rcd * FROM config.rule_circ_duration 
+    SELECT INTO rcd * FROM config.rule_circ_duration
       WHERE id = this_duration_rule;
-    SELECT INTO rrf * FROM config.rule_recurring_fine 
+    SELECT INTO rrf * FROM config.rule_recurring_fine
       WHERE id = this_fine_rule;
-    SELECT INTO rmf * FROM config.rule_max_fine 
+    SELECT INTO rmf * FROM config.rule_max_fine
       WHERE id = this_max_fine_rule;
 
     -- Apply the rules to this circulation
@@ -885,22 +907,28 @@ BEGIN
       max_fine_rule = rmf.name,
       duration = rcd.normal,
       recurring_fine = rrf.normal,
-      max_fine = rmf.amount,
+      max_fine =
+        CASE rmf.is_percent
+          WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+          ELSE rmf.amount
+        END,
       renewal_remaining = rcd.max_renewals
     FROM
       config.rule_circ_duration rcd,
       config.rule_recurring_fine rrf,
-      config.rule_max_fine rmf
+      config.rule_max_fine rmf,
+                        asset.copy ac
     WHERE
       rcd.id = ' || this_duration_rule || ' AND
       rrf.id = ' || this_fine_rule || ' AND
       rmf.id = ' || this_max_fine_rule || ' AND
+                        ac.id = c.target_copy AND
       c.id = ' || circ || ';');
 
     -- Keep track of where we are in the process
     n := n + 1;
-    IF (n % 100 = 0) THEN 
-      RAISE INFO '%', n || ' of ' || n_circs 
+    IF (n % 100 = 0) THEN
+      RAISE INFO '%', n || ' of ' || n_circs
         || ' (' || (100*n/n_circs) || '%) circs updated.';
     END IF;
 
@@ -1024,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;');
 
@@ -1051,8 +1079,9 @@ 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');
       }
     }
 
@@ -1130,3 +1159,241 @@ BEGIN
 END;
 
 $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
+
+	my $input = $_[0];
+	my %zipdata;
+
+	open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
+
+	while (<FH>) {
+		chomp;
+		my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
+		$zipdata{$zip} = [$city, $state, $county];
+	}
+
+	if (defined $zipdata{$input}) {
+		my ($city, $state, $county) = @{$zipdata{$input}};
+		return [$city, $state, $county];
+	} elsif (defined $zipdata{substr $input, 0, 5}) {
+		my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
+		return [$city, $state, $county];
+	} else {
+		return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
+	}
+  
+$$ 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 DISTINCT
+			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 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;
+
+