upload of csv ledgers

[confetti] / ledgers / csv_upload.awk

diff --git a/ledgers/csv_upload.awk b/ledgers/csv_upload.awk
new file mode 100755 (executable)
index 0000000..04de4d5
--- /dev/null
+++ b/ledgers/csv_upload.awk
@@ -0,0 +1,100 @@
+#!/bin/awk -f
+
+function STRING( inp ) {
+  gsub(/\\/, "\\\\", inp);
+  gsub(/\n/, "\\n",  inp);
+  gsub(/\r/, "\\r",  inp);
+  gsub(/\t/, "\\t",  inp);
+  gsub(/\+/, "\\+",  inp);
+  gsub(/ /,  "+",    inp);
+  return inp ? inp : "\\";
+}
+
+function UNSTRING( inp,    out, tmp ) {
+  while ( inp ) {
+         if ( inp ~ /^\\\\/) { out = out "\\"; sub(/^\\\\/, "", inp); }
+    else if ( inp ~ /^\\n/)  { out = out "\n"; sub(/^\\n/,  "", inp); }
+    else if ( inp ~ /^\\r/)  { out = out "\r"; sub(/^\\r/,  "", inp); }
+    else if ( inp ~ /^\\t/)  { out = out "\t"; sub(/^\\t/,  "", inp); }
+    else if ( inp ~ /^\\+/)  { out = out "+";  sub(/^\\+/,  "", inp); }
+    else if ( inp ~ /^\+/)   { out = out " ";  sub(/^\+/,   "", inp); }
+    else if ( inp ~ /^\\/)   { out = out "";   sub(/^\+/,   "", inp); }
+    else { tmp = inp; sub(/[\\+].*$/, "", tmp); out = out tmp; sub(/^[^\\+]*/, "", inp); }
+  }
+  return out;
+}
+
+function isdate( date,    dt, y, m, d ) {
+         if ( match( date, 
+    /^[0-9]{4}-((01|03|05|07|08|10|12)-(0[1-9]|[12][0-9]|3[01])|(04|06|09|11)-(0[1-9]|[12][0-9]|30)|02-(0[1-9]|[12][0-9]))$/ )) {
+    split( date, dt, "-");
+    y = dt[1]; m = dt[2]; d = dt[3];
+
+  } else if ( match( date,
+    /^((0?1|0?3|0?5|0?7|0?8|10|12)\/(0?[1-9]|[12][0-9]|3[01])|(0?4|0?6|0?9|11)\/(0?[1-9]|[12][0-9]|30)|0?2\/(0[1-9]|[12][0-9]))\/([0-9]{2}|[0-9]{4})$/ )) {
+    split( date, dt, "/");
+    m = dt[1]; d = dt[2]; y = dt[3];
+
+  } else if ( match( date,
+    /^((0?[1-9]|[12][0-9]|3[01])[\.\/](0?1|0?3|0?5|0?7|0?8|10|12)|(0?[1-9]|[12][0-9]|30)[\.\/](0?4|0?6|0?9|11)|(0[1-9]|[12][0-9])[\.\/]0?2)[\.\/]([0-9]{2}|[0-9]{4})$/ )) {
+    split( date, dt, /[\.\/]/);
+    d = dt[1]; m = dt[2]; y = dt[3];
+
+  } else return "";
+
+  if ( y < 100 && y >  50 ) y = y + 1900;
+  if (            y <= 50 ) y = y + 2000;
+
+  # leap year
+  if ( m == 2 && d == 29 ) {
+         if ( y % 400 == 0 ) y = y;
+    else if ( y % 100 == 0 ) return "";
+    else if ( y %   4 == 0 ) y = y;
+    else return "";
+  }
+
+  return sprintf("%04i-%02i-%02i", y, m, d);
+}
+
+function cents( val ) {
+  gsub(/\./, "", val); sub(/,/, ".", val);
+  return val * 100;
+}
+
+BEGIN {
+  FS = ";";
+  dtrange_end = dt_from = dt_to = balance_start = balance_end = "";
+  split("", rec);
+  rec[0] = "Date       DateU   IBAN    Name    Subject Amount"
+}
+
+/^([012]?[0-9]|30|31).(0?[1-9]|1[012]).[0-9]{4} - ([012]?[0-9]|30|31).(0?[1-9]|1[012]).[0-9]{4}$/ {
+  dtrange_end = $0; sub(/^.* - /, "", dtrange_end);
+  dt_from = $0; sub(/ - .*$/, "", dt_from); dt_from = isdate(dt_from);
+  dt_to   = $0; sub(/^.* - /, "", dt_to  ); dt_to   = isdate(dt_to  );
+}
+
+/^Letzter Kontostand;;;;[0-9\.,]+;EUR$/ {
+  balance_start = cents($5);
+}
+
+/Kontostand;[^;]+;;;[0-9\.,]+;EUR/ {
+  if ( $2 = dtrange_end ) balance_end = cents($5)
+}
+
+$18 == "EUR" {
+  rec_date = isdate($1); gsub(/-/, " ", rec_date); rec_date = mktime(rec_date " 00 00 00", "UTC");
+  rec[length(rec)] = sprintf("%s       %i      %s      %s      %s      %i",
+                             isdate($1), rec_date, $6 ? $6 : "\\", STRING($4), STRING($5), cents($12));
+}
+
+END {
+  if ( dt_from && dt_to ) {
+    dtu_from = dt_from; gsub(/-/, " ", dtu_from); dtu_from = mktime( dtu_from " 00 00 00", "UTC");
+    dtu_to   = dt_to  ; gsub(/-/, " ", dtu_to  ); dtu_to   = mktime( dtu_to   " 00 00 00", "UTC");
+
+    printf "%i %s      %i      %s      %i      %i\n",
+           dtu_from, dt_from, dtu_to, dt_to, balance_start, balance_end;
+    for ( k = 1; k < length(rec); k++ ) print rec[k];
+  }
+}