great shuffling, not finished

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

diff --git a/sql/base/05-addresses.sql b/sql/base/05-addresses.sql
index 8477d29..45a2dcc 100644 (file)
--- a/sql/base/05-addresses.sql
+++ b/sql/base/05-addresses.sql
@@ -1,3 +1,26 @@
+CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
+  DECLARE
+    phone TEXT := $1;
+    areacode TEXT := $2;
+    temp TEXT := '';
+    output TEXT := '';
+    n_digits INTEGER := 0;
+  BEGIN
+    temp := phone;
+    temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+    temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+    n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+    IF n_digits = 7 AND areacode <> '' THEN
+      temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+      output := (areacode || '-' || temp);
+    ELSE
+      output := temp;
+    END IF;
+    RETURN output;
+  END;
+
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
 CREATE OR REPLACE FUNCTION msgration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
     DECLARE
         city_state_zip TEXT := $1;
@@ -670,3 +693,27 @@ CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETUR
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+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;