X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=1648ea3a8457163df2f39fdaccd6e7fe5615e619;hp=b0ed817314462015c484650dfc28f41ec66f26d5;hb=4ec23f88c7f20557b13ec6f5be637e726904e114;hpb=69e7b6a2ce5a060bb2e471428136217e0f9ab9d2 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(