X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2F05-addresses.sql;h=45a2dcc408bd88863fa6a1b0286a66439470bcd2;hp=8477d295766a2cb7c49745f7b83c5882b7ac08ba;hb=9328c60b81355e653950445fbe7288e42a5d45d0;hpb=e8f5f88d5be49ce0bea85abad7c7a56badab0794

diff --git a/sql/base/05-addresses.sql b/sql/base/05-addresses.sql
index 8477d29..45a2dcc 100644
--- 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;