From 4b5e60659c93e69bfe1d3a5615bddad61e731b27 Mon Sep 17 00:00:00 2001 From: Ben Ostrowsky Date: Mon, 8 Aug 2011 12:53:52 +0000 Subject: [PATCH] Added migration_tools.zip_to_city_state_county --- sql/base/base.sql | 27 +++++++++++++++++++++++++++ 1 files changed, 27 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index ca5b719..8bcff20 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 );' ); @@ -1158,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 () { + 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; + -- 1.7.2.5