Added migration_tools.zip_to_city_state_county

[migration-tools.git] / sql / base / base.sql

diff --git a/sql/base/base.sql b/sql/base/base.sql
index b4c06ac..8bcff20 100644 (file)
--- 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;
 
@@ -1131,3 +1159,29 @@ 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;
+