From e1cd45741b23d400bb44959fa8fffac25286ed58 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Paul=20H=C3=A4nsch?= Date: Wed, 17 Apr 2024 18:36:36 +0200 Subject: [PATCH] upload of csv ledgers --- index.cgi | 5 +- l10n.sh | 1 + ledgers/csv_upload.awk | 100 +++++++++++++++++++++++++++++++++++++++ ledgers/csv_upload.sh | 49 +++++++++++++++++++ ledgers/index.cgi | 11 +++++ multipart.sh | 105 +++++++++++++++++++++++++++++++++++++++++ style.css | 16 +++++-- 7 files changed, 282 insertions(+), 5 deletions(-) create mode 100755 ledgers/csv_upload.awk create mode 100755 ledgers/csv_upload.sh create mode 100755 ledgers/index.cgi create mode 100755 multipart.sh diff --git a/index.cgi b/index.cgi index 276965f..71edd85 100755 --- a/index.cgi +++ b/index.cgi @@ -34,6 +34,8 @@ _PATH="$(PATH "/${PATH_INFO}")" _PATH="${_PATH#${_BASE}}" ACTION="$(GET a)" +SESSION_COOKIE + message="$(COOKIE message)" [ "$message" ] && SET_COOKIE 0 message='' @@ -64,7 +66,8 @@ yield_page() { printf ' ] [body #top class="%s" ' "$class" - printf '[ul .menu [li [a "%s/cards/" . %s]][li [a "%s/courses/" . %s]]]' "${_BASE}" "$(l10n cards)" "${_BASE}" "$(l10n courses)" + printf '[ul .menu [li [a "%s/cards/" . %s]][li [a "%s/courses/" . %s]][li [a "%s/ledgers/" . %s]]]' \ + "${_BASE}" "$(l10n cards)" "${_BASE}" "$(l10n courses)" "${_BASE}" "$(l10n ledgers)" [ "$message" ] && printf '[p #message\n%s\n]' "$(l10n "$message")" cat printf '] ]' diff --git a/l10n.sh b/l10n.sh index bddc7b7..d9e01d0 100755 --- a/l10n.sh +++ b/l10n.sh @@ -26,6 +26,7 @@ l10n_global() { # Nav Menu cards) printf %s "Teil­neh­mende";; courses) printf %s "Kurse";; + ledgers) printf %s "Bei­trä­ge";; # VCF Default PHOTO) printf %s "Foto";; diff --git a/ledgers/csv_upload.awk b/ledgers/csv_upload.awk new file mode 100755 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]; + } +} diff --git a/ledgers/csv_upload.sh b/ledgers/csv_upload.sh new file mode 100755 index 0000000..4d25b5c --- /dev/null +++ b/ledgers/csv_upload.sh @@ -0,0 +1,49 @@ +#!/bin/sh + +# Copyright 2024 Paul Hänsch +# +# Permission to use, copy, modify, and/or distribute this software for any +# purpose with or without fee is hereby granted, provided that the above +# copyright notice and this permission notice appear in all copies. +# +# THE SOFTWARE IS PROVIDED “AS IS” AND THE AUTHOR DISCLAIMS ALL WARRANTIES +# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF +# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY +# SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES +# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN +# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR +# IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + +if [ "${CONTENT_TYPE%%;*}" != "multipart/form-data" ]; then + SET_COOKIE 0 message="Not an upload" + REDIRECT "${_BASE}/ledgers/" +fi + +. "$_EXEC/multipart.sh" +multipart_cache + +# Validate session id from form to prevent CSRF +if [ "$(multipart session_id)" != "$SESSION_ID" ]; then + rm -- "$multipart_cachefile" + SET_COOKIE 0 message="INVALID SESSION ID IN FORM" + REDIRECT "${_BASE}/ledgers/" +fi + +mkdir -p "$_DATA/ledgers/" +CSV="$(multipart "csv" 1 | "$_EXEC/ledgers/csv_upload.awk")" +rm -- "$multipart_cachefile" + +read dtu_start dt_start dtu_end dt_end balance_start balance_end <<-EOF + ${CSV%%${BR}*} +EOF + +if [ ! "$dtu_end" -o ! "$dtu_start" ] || [ "$dtu_end" -lt "$dtu_start" ]; then + SET_COOKIE 0 message="No valid date range in upload" +else + num=0; while [ ! "$filename" -o -f "$_DATA/ledgers/$filename" ]; do + num=$((num + 1)); filename="${dt_start} - ${dt_end} - $(printf '%04i' $num).tbl" + done + printf '%s\n' "$CSV" >"$_DATA/ledgers/$filename" +fi + +REDIRECT "${_BASE}/ledgers/" diff --git a/ledgers/index.cgi b/ledgers/index.cgi new file mode 100755 index 0000000..1313ef8 --- /dev/null +++ b/ledgers/index.cgi @@ -0,0 +1,11 @@ +#!/bin/sh + +{ printf ' + [form .upload action="%s/ledgers/csv_upload.sh" method="POST" enctype="multipart/form-data" + [label for=ledger_upload . %s:] + [input #ledger_upload type="file" name="csv" accept=".csv,text/csv"] + [input type=hidden name=session_id value="%s"] + [button type="submit" %s] + ]' \ + "${_BASE}" "$(l10n "Postbank CSV")" "$SESSION_ID" "$(l10n Upload)" +} | yield_page ledgers diff --git a/multipart.sh b/multipart.sh new file mode 100755 index 0000000..02f7dfb --- /dev/null +++ b/multipart.sh @@ -0,0 +1,105 @@ +#!/bin/sh + +[ "$include_multipart" ] && return 0 +inlude_multipart="$0" + +# Copyright 2022 - 2023 Paul Hänsch +# +# Permission to use, copy, modify, and/or distribute this software for any +# purpose with or without fee is hereby granted, provided that the above +# copyright notice and this permission notice appear in all copies. +# +# THE SOFTWARE IS PROVIDED “AS IS” AND THE AUTHOR DISCLAIMS ALL WARRANTIES +# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF +# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY +# SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES +# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN +# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR +# IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + +if [ "${CONTENT_TYPE}" -a ! "${CONTENT_TYPE##multipart/form-data;*}" ]; then + multipart_boundary="${CONTENT_TYPE#*; boundary=}" + multipart_boundary="${multipart_boundary%%;*}" + multipart_boundary="${multipart_boundary%${CR}}" +fi +multipart_cachefile="/tmp/multipart.$$" + +readbytes(){ + # read n bytes, like `head -c` but do not consume input + local size="$1" block + + for block in 65536 32768 16384 8192 4096 2048 1024 512 256 128 64 32 16 8 4 2 1; do + if [ $size -ge $block ]; then + dd status=none bs="$block" count="$((size / block))" + size="$((size % block))" + fi + done +} + +multipart_cache() { + multipart_cachefile="${1:-${multipart_cachefile}}" # global + + if [ "${multipart_boundary}" ]; then + # readbytes "$(( CONTENT_LENGTH ))" >"${multipart_cachefile}" + head -c "$(( CONTENT_LENGTH ))" >"${multipart_cachefile}" + else + return 1 + fi +} + +multipart(){ + local name="$1" count="${2:-1}" + local formdata state=begin + + while IFS='' read -r formdata; do case "$formdata" in + "--${multipart_boundary}--${CR}") + [ $state = data ] && return 0 \ + || return 1 + ;; + "--${multipart_boundary}${CR}") + [ $state = data ] && return 0 \ + || state=header + ;; + "Content-Disposition: form-data; name=\"${name}\""*"${CR}") + [ $state = header -a $count -eq 1 ] && state=dheader + [ $state = header -a $count -gt 1 ] && count=$((count - 1)) + [ $state = data ] && printf "%s\n" "$formdata" + ;; + "${CR}") + if [ $state = dheader ]; then + # Do not use `sed -n` (or busybox sed will "convert" NULL to LF) + sed "/--${multipart_boundary}\(--\)\?${CR}/{x;q;}" \ + | head -c-3 + return 0; + fi + [ $state = header ] && state=junk + ;; + esac; done <"${multipart_cachefile}" +} + +multipart_filename(){ + local name="$1" count="${2:-1}" + local formdata state=begin + + while read -r formdata; do case "$formdata" in + "--${multipart_boundary}--${CR}") + return 1 + ;; + "--${multipart_boundary}${CR}") + state=header + ;; + "Content-Disposition: form-data; name=\"${name}\"; filename=\""*"\""*"${CR}") + [ $state = header -a $count -eq 1 ] && break + [ $state = header -a $count -gt 1 ] && count=$((count - 1)) + ;; + "${CR}") + [ $state = header ] && state=junk + ;; + esac; done <"${multipart_cachefile}" + + filename="${formdata#*; filename=\"}" + filename="${filename%%\"${CR}}" + filename="${filename%%\";*}" + + HEX_DECODE % "$filename" +} diff --git a/style.css b/style.css index 298ae3d..275fba4 100644 --- a/style.css +++ b/style.css @@ -25,8 +25,9 @@ body > .menu a { padding: .5em 3em; box-shadow: inset 0 0 .5em #000; } -body.cards > .menu a[href$="/cards/"], -body.courses > .menu a[href$="/courses/"] { +body.ledgers > .menu a[href$="/ledgers/"], +body.courses > .menu a[href$="/courses/"], +body.cards > .menu a[href$="/cards/"] { color: #000; background-color: #FFF; box-shadow: none; @@ -34,9 +35,9 @@ body.courses > .menu a[href$="/courses/"] { /* =========== FILTER AND SEARCH Headers ========= */ -form.categories, +form.upload, form.categories, form.search, form.sort, form.filter, form.newcard, form.newcourses { - margin-top: 1em; padding: 0 1em; + margin-top: 1em; padding: .125em 1em 0 1em; z-index: 1; } form.filter > h1 { display: none; } @@ -64,10 +65,17 @@ form.filter button[type=submit] { form.filter button[value=export_csv] { margin-left: 1em; } body.courses form .order { display: inline-block; margin-right: 2em;} +body.courses form.search.sort fieldset { margin-top: .5em; } body.cards form.newcard { display: flex; } body.cards form.newcard input[name=seed] { flex: 1; } +form.upload label { + display: block; + font-weight: bold; + margin-top: .5em; +} + /* ============ LIST ITEMS, Generic ============= */ -- 2.39.2