X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=84c35f326478583da2dde0019cd47b846e3b02e3;hp=632d23195c1c79de28707d4ecfe7594ecb77a452;hb=8783c6c281d0aca08c1b88d1067d80a24a513696;hpb=ea3559a4f7ac5fefcc0e64b1e610b26004d75d9f

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 632d231..84c35f3 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1098,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;
@@ -3777,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$;
+