migration_tools.add_mod16_checkdigit

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

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 8dd7f36..84c35f3 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -607,6 +607,22 @@ CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
+CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
+    my ($address) = @_;
+
+    use Geo::StreetAddress::US;
+
+    my $a = Geo::StreetAddress::US->parse_location($address);
+
+    return [
+         "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
+        ,"$a->{sec_unit_type} $a->{sec_unit_num}"
+        ,$a->{city}
+        ,$a->{state}
+        ,$a->{zip}
+    ];
+$$ LANGUAGE PLPERLU STABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
     DECLARE
         n TEXT := o;
@@ -1082,6 +1098,51 @@ CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RET
     return $barcode . $checkdigit;
 $$ LANGUAGE PLPERLU STRICT STABLE;
 
+-- add_mod16_checkdigit
+--   $barcode      source barcode
+--
+-- https://www.activebarcode.com/codes/checkdigit/modulo16.html
+
+CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
+    my $barcode = shift;
+
+    my @digits = split //, $barcode;
+    my $total = 0;
+    foreach $digit (@digits) {
+        if ($digit =~ /[0-9]/) { $total += $digit;
+        } elsif ($digit eq '-') { $total += 10;
+        } elsif ($digit eq '$') { $total += 11;
+        } elsif ($digit eq ':') { $total += 12;
+        } elsif ($digit eq '/') { $total += 13;
+        } elsif ($digit eq '.') { $total += 14;
+        } elsif ($digit eq '+') { $total += 15;
+        } elsif ($digit eq 'A') { $total += 16;
+        } elsif ($digit eq 'B') { $total += 17;
+        } elsif ($digit eq 'C') { $total += 18;
+        } elsif ($digit eq 'D') { $total += 19;
+        } else { die "invalid digit <$digit>";
+        }
+    }
+    my $remainder = $total % 16;
+    my $difference = 16 - $remainder;
+    my $checkdigit;
+    if ($difference < 10) { $checkdigit = $difference;
+    } elsif ($difference == 10) { $checkdigit = '-';
+    } elsif ($difference == 11) { $checkdigit = '$';
+    } elsif ($difference == 12) { $checkdigit = ':';
+    } elsif ($difference == 13) { $checkdigit = '/';
+    } elsif ($difference == 14) { $checkdigit = '.';
+    } elsif ($difference == 15) { $checkdigit = '+';
+    } elsif ($difference == 16) { $checkdigit = 'A';
+    } elsif ($difference == 17) { $checkdigit = 'B';
+    } elsif ($difference == 18) { $checkdigit = 'C';
+    } elsif ($difference == 19) { $checkdigit = 'D';
+    } else { die "error calculating checkdigit";
+    }
+
+    return $barcode . $checkdigit;
+$$ LANGUAGE PLPERLU STRICT STABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
   DECLARE
     phone TEXT := $1;
@@ -3359,12 +3420,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,T
 
         IF btrim_desired THEN
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
-                || ' SET x_item = id FROM asset_copy_legacy b'
+                || ' SET x_item = b.id FROM asset_copy_legacy b'
                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
         ELSE
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
-                || ' SET x_item = id FROM asset_copy_legacy b'
+                || ' SET x_item = b.id FROM asset_copy_legacy b'
                 || ' WHERE a.' || quote_ident(foreign_column_name)
                 || ' = b.' || quote_ident(main_column_name);
         END IF;
@@ -3420,12 +3481,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,T
 
         IF btrim_desired THEN
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
-                || ' SET x_user = id FROM actor_usr_legacy b'
+                || ' SET x_user = b.id FROM actor_usr_legacy b'
                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
         ELSE
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
-                || ' SET x_user = id FROM actor_usr_legacy b'
+                || ' SET x_user = b.id FROM actor_usr_legacy b'
                 || ' WHERE a.' || quote_ident(foreign_column_name)
                 || ' = b.' || quote_ident(main_column_name);
         END IF;
@@ -3761,3 +3822,46 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TE
 
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+    c_name     TEXT;
+BEGIN
+
+    FOR c_name IN SELECT column_name FROM information_schema.columns WHERE 
+            table_name = t_name
+            AND table_schema = s_name
+            AND (data_type='text' OR data_type='character varying')
+            AND column_name like 'l_%'
+    LOOP
+       EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')'); 
+    END LOOP;  
+
+    RETURN TRUE;
+END
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+    c_name     TEXT;
+BEGIN
+
+    FOR c_name IN SELECT column_name FROM information_schema.columns WHERE 
+            table_name = t_name
+            AND table_schema = s_name
+            AND (data_type='text' OR data_type='character varying')
+            AND column_name like 'l_%'
+    LOOP
+       EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' '); 
+    END LOOP;  
+
+    RETURN TRUE;
+END
+$function$;
+