From: Rogan Hamby Date: Mon, 25 Mar 2019 18:31:57 +0000 (-0400) Subject: Merge branch 'master' of git.esilibrary.com:migration-tools X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=32f8a87408763d0ace21af526d68ffb02559a33d;hp=ff15f932e850a3187ce3d8beff39cc63219fb8c1 Merge branch 'master' of git.esilibrary.com:migration-tools --- diff --git a/sql/base/base.sql b/sql/base/base.sql index b0ed817..1648ea3 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -573,6 +573,98 @@ CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_midd END; $$ LANGUAGE PLPGSQL STRICT IMMUTABLE; +CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$ + DECLARE + full_name TEXT := $1; + temp TEXT; + family_name TEXT := ''; + first_given_name TEXT := ''; + second_given_name TEXT := ''; + suffix TEXT := ''; + prefix TEXT := ''; + BEGIN + temp := BTRIM(full_name); + -- Use values, not structure, for prefix/suffix, unless we come up with a better idea + --IF temp ~ '^\S{2,}\.' THEN + -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1'); + -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1')); + --END IF; + --IF temp ~ '\S{2,}\.$' THEN + -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1'); + -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1'); + --END IF; + IF temp ilike '%MR.%' THEN + prefix := 'Mr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%MRS.%' THEN + prefix := 'Mrs.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%MS.%' THEN + prefix := 'Ms.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%DR.%' THEN + prefix := 'Dr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%JR.%' THEN + suffix := 'Jr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%JR,%' THEN + suffix := 'Jr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' )); + END IF; + IF temp ilike '%SR.%' THEN + suffix := 'Sr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' )); + END IF; + IF temp ilike '%SR,%' THEN + suffix := 'Sr.'; + temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' )); + END IF; + IF temp like '%III%' THEN + suffix := 'III'; + temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' )); + END IF; + IF temp like '%II%' THEN + suffix := 'II'; + temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' )); + END IF; + + IF temp ~ ',' THEN + family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1')); + temp := BTRIM(REPLACE( temp, family_name, '' )); + family_name := REPLACE( family_name, ',', '' ); + IF temp ~ ' ' THEN + first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') ); + second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') ); + ELSE + first_given_name := temp; + second_given_name := ''; + END IF; + ELSE + IF temp ~ '^\S+\s+\S+\s+\S+$' THEN + first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') ); + second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') ); + family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') ); + ELSE + first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') ); + second_given_name := temp; + family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') ); + END IF; + END IF; + + family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"','')); + first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"','')); + second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"','')); + + RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ]; + END; +$$ LANGUAGE PLPGSQL STRICT IMMUTABLE; + CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$ DECLARE city_state_zip TEXT := $1; @@ -3609,25 +3701,25 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) IF x_org_found THEN EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_shelf = id FROM asset_copy_location b' + || ' SET x_shelf = b.id FROM asset_copy_location b' || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))' || ' AND b.owning_lib = x_org' || ' AND NOT b.deleted'; EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_shelf = id FROM asset.copy_location b' + || ' SET x_shelf = b.id FROM asset.copy_location b' || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))' || ' AND b.owning_lib = x_org' || ' AND x_shelf IS NULL' || ' AND NOT b.deleted'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_shelf = id FROM asset_copy_location b' + || ' SET x_shelf = b.id FROM asset_copy_location b' || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))' || ' AND b.owning_lib = $1' || ' AND NOT b.deleted' USING org; EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_shelf = id FROM asset_copy_location b' + || ' SET x_shelf = b.id FROM asset_copy_location b' || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))' || ' AND b.owning_lib = $1' || ' AND x_shelf IS NULL' @@ -3637,7 +3729,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) FOREACH o IN ARRAY org_list LOOP EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_shelf = id FROM asset.copy_location b' + || ' SET x_shelf = b.id FROM asset.copy_location b' || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))' || ' AND b.owning_lib = $1 AND x_shelf IS NULL' || ' AND NOT b.deleted' @@ -3768,7 +3860,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID A || ' ADD COLUMN x_org INTEGER'; EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_org = id FROM actor.org_unit b' + || ' SET x_org = b.id FROM actor.org_unit b' || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)'; EXECUTE 'SELECT migration_tools.assert(