#!/bin/bash
# GEHOERT ZUM KASSENBUCHPROJEKT auf http://www.nofalab.ch
# Version 0 20071203
# Autor: Lukas Gantert admin@nofalab.ch http://www.nofalab.ch
# Lizenz: GPL http://www.gnu.org/licenses/gpl.html
# Todo: -Bug: bei Eintrag in DB ohne Kontoangabe Fehlberechnungen;
# - Waehrung als Option angeben;
# - waehlen zwischen stdout (-) und Datei
# - weiterarbeiten an plot-report line 155
# - Bei "Query Filters" last modifyed date von PdbFile angeben (mit ls -l ?)
# 20090804 neu mit Kind-function: Summe für Konto-Art - Einnahmen, Ausgaben, Bank
# 20100230 Sort-function: Listet für jedes Konto die Veränderung und den Kontostand, nach Datum sortiert
# bug1: Klammern im Text (z.B im Kontonamen) verursachen leere Liste in 'asort', in 'split' ?
version=20100208
CURRENCY=CHF
SEP="°°°"
DateFormat='%Y%m%d'
Begin=11111111
End=99999999
PdbFile="$HOME/palm/backup/DB4A-Kassenbuch.pdb" # default-pdb-file
Help () {
exit
}
# Version anzeigen
Version () {
echo Das ist Version $version von `basename $0`
exit
}
while getopts "hvdb:e:f:c:u:n:a:o:" opt
do
case $opt in
h) Help;;
b) Begin=$(date -d $OPTARG "+$DateFormat");;
e) End=$(date -d $OPTARG "+$DateFormat");;
f) PdbFile=$OPTARG;;
c) Categorie=$OPTARG;;
u) User=$OPTARG;;
a) Account=$OPTARG;;
n) Notes=$OPTARG;;
o) Outputfile=$OPTARG;;
d) Detail=1;;
v) Version;;
?) echo Fehler; exit;;
*) echo keine gültige Option;;
esac
done
shift $(( $OPTIND - 1 ))
# Abhaengigkeitstest
pdb2csv -v > /dev/null
if [ $? -ne 0 ]; then
echo -e "\a
kann Programm nicht ausfuehren.
Unerfuellte Abhaengigkeit.
bitte palm-db-tools installieren.
http://pilot-db.sourceforge.net
"
exit
fi
pdb2csv -s "$SEP" -d "$DateFormat" $PdbFile |
gawk \
-v sep="$SEP" \
-v Begin="$Begin" \
-v End="$End" \
-v Account="$Account" \
-v Kind="$Kind" \
-v User="$User" \
-v Notes="$Notes" \
-v Detail="$Detail" \
-v PdbFile="$PdbFile" \
-v Outputfile="$Outputfile" \
-v Categorie="$Categorie" '
BEGIN {
FS=sep # same as in the output of pdb2csv
SUBSEP="\034" # to split the multidimensional arrays
datemin = 99999999 # needed by the Begin date Filter
########################################################################
# HEADER FOR THE "SHOW ALL DETAILS" BLOCK ( OPTION -d )
########################################################################
if ( Detail == 1 ) {
printdetailtitle("ALL TRANSACTION - DETAILS")
printdetailtext("DATE", "IN-ACCOUNT", "TYPE", "AMOUNT", "OUT_ACCOUNT", "TYPE", "AMOUNT", "CATEGORIE", "END-USER", "DESCRIPTION")
}
}
{
#####################################################################
# TO SKIP CSV DECORATIONS AND THE "N/A" IN CALCULATED FIELDS
#####################################################################
#gsub(/[[:space:]]* /, "_")
gsub(/\"/, "")
gsub(/\(/, "") # workaround bug1
gsub(/\)/, "") # workaround bug1
gsub(/N\/A/, "")
#####################################################################
# FILTER, GIVEN ON CMD-LINE
#####################################################################
if ($9 >= Begin && $9 <= End && $8 ~ User && $7 ~ Categorie && $10 ~ Notes) {
if ($1 ~ Account || $4 ~ Account ) {
###############################################################
# READ IN ARRAYS
###############################################################
konto[$1]+=$3
konto[$4]+=$6
accin[$1]+=1
accout[$4]+=1
accinsum[$1]+=$3
accoutsum[$4]+=$6
kind[$2]+=$3
kind[$5]+=$6
tp[$2]+=1
tp[$5]+=1
tpin[$2]+=1
tpout[$5]+=1
tpinsum[$2]+=$3
tpoutsum[$5]+=$6
users[$8]+=1
enduser[$8 SUBSEP $2]+=$3
enduser[$8 SUBSEP $5]+=$6
categorie[$7 SUBSEP $2]+=$3
categorie[$7 SUBSEP $5]+=$6
# counters
categorie_numbers[$7]+=1
insum+=$3
outsum+=$6
# THE HOLE TRANSACTION, UNIDIRECTIONAL
# to give to each line, even equal ones, a uniq Adresse (NR"a",NR"b)
line[NR"a"]=$1 SUBSEP $9 SUBSEP $3 SUBSEP $2 SUBSEP $7 SUBSEP $8 SUBSEP $10
line[NR"b"]=$4 SUBSEP $9 SUBSEP $6 SUBSEP $5 SUBSEP $7 SUBSEP $8 SUBSEP $10
###############################################################
# READ IN EARLIEST AND LAST DATE IN THE DB
###############################################################
if (datemin > $9) {
datemin = $9
}
if (datemax < $9) {
datemax = $9
}
if ( Detail == 1 ) {
printdetailbody($9,$1,$2,$3,$4,$5,$6,$7,$8,$10)
}
}
}
}
#######################################################################
# PRINTF FORMAT FUNCTIONS
#######################################################################
########## Columns = 120 ? ##################
function printdetailbody(a, b, c, d, e, f, g, h, i, j) {
printf ("\033[37;40m%-10s%-16s%-9s%10.2f %-16s%-9s%10.2f %-12s%-12s%-12s\033[0m\n",
a, b, c, d, e, f, g, h, i, j)
}
function printdetailtext(a, b, c, d, e, f, g, h, i, j) {
printf ("\033[1;34;40m%-10s%-16s%-9s%10s %-16s%-9s%10s %-12s%-12s%-12s\033[0m\n",
a, b, c, d, e, f, g, h, i, j)
}
function printdetailtitle(a) {
printf("\033[1;36;40m%15s%-60s%45s\033[0m\n",
"", a, "")
}
########## Columns = 72 ? ##################
function printbody(a, b, c, d) {
printf("\033[37;40m %-17s%18s%18.2f%17.2f \033[0m\n",
a, b, c, d)
}
function printtrans(a, b, c, d) {
printf("\033[37;40m %-17s%18s%18s%17.2f \033[0m\n",
a, b, c, d)
}
function printquery(a, b, c, d) {
printf("\033[1;34;40m %-17s\033[0;37;40m%-53s%s%s \033[0m\n",
a, b, c, d)
}
function printtext(a, b, c, d) {
printf("\033[1;34;40m %-17s%18s%18s%17s \033[0m\n",
a, b, c, d)
}
function printsum(a, b, c, d) {
printf("\033[1;34;40m %-17s%18s%18.2f%17s \033[0m\n",
a, b, c, d)
}
function printtitle(a) {
printf("\033[1;36;40m %-5s%-65s%s \033[0m\n",
"", a, "")
}
END {
#######################################################################
# SORT ARRAYS
#######################################################################
n_lines = asort(line)
n_users = asorti(enduser, endusers_sort)
n_categories = asorti(categorie, categories_sort)
#######################################################################
# SHOW QUERY FILTERS
#######################################################################
print ""
printtitle("QUERY FILTERS")
printquery("DATE OF QUERY", strftime( "%c"), "", "")
printquery("PDB FILE", PdbFile, "", "")
printquery("BEGIN QUERY", Begin, "", "")
printquery("END QUERY", End, "", "")
printquery("FIRST TRANS", datemin, "", "")
printquery("LAST TRANS", datemax, "", "")
printquery("ACCOUNT", Account, "", "")
printquery("CATEGORIE", Categorie, "", "")
printquery("ENDUSER", User, "", "")
printquery("DESCRIPTION", Notes, "", "")
print ""
#######################################################################
# SHOW NUMBER OF TRANSACTIONS
#######################################################################
printtitle("NUMBER OF TRANSACTIONS")
printtext("","TOTAL","NUMBER","")
printtrans("","TOTAL", n_lines/2,"")
printtext("","BY END-USER","NUMBER","")
for (U in users) {
printtrans("",U, users[U],"")
}
printtext("","BY CATECORIE","NUMBER","")
for (C in categorie_numbers) {
printtrans("",C, categorie_numbers[C],"")
}
print""
printtitle("BY NUMBER OF INCOMS")
printtext("","TOTAL","NUMBER","SUM")
printtrans("","TOTAL", n_lines/2, insum)
printtext("","BY ACCOUNT","NUMBER","SUM")
for (A in accin) {
for (As in accinsum) {
if (A == As) {
printtrans("",A, accin[A], accinsum[As])
}
}
}
printtext("","BY ACCOUNT-TYPE","NUMBER","SUM")
for (T in tpin) {
for (Ts in tpinsum) {
if (T == Ts) {
printtrans("",T, tpin[T], tpinsum[Ts])
}
}
}
print""
printtitle("NUMBER OF OUTGOINGS")
printtext("","TOTAL","NUMBER","SUM")
printtrans("","TOTAL", n_lines/2, outsum)
printtext("","BY ACCOUNT","NUMBER",""SUM)
for (A in accout) {
for (As in accoutsum) {
if (A ==As) {
printtrans("",A, accout[A], accoutsum[As])
}
}
}
printtext("","BY ACCOUNT-TYPE","NUMBER","SUM")
for (T in tpout) {
for (Ts in tpoutsum) {
if ( T == Ts) {
printtrans("",T, tpout[T], tpoutsum[Ts])
}
}
}
print""
#######################################################################
# SHOW TRANSACTIONS FOR EACH ACCOUNT AND THIS FOR EACH MONTH
#######################################################################
# clear out the oldsum-array
delete oldsum
for (year = 2000; year <= 2020; year++) {
for (month = 1; month <= 12; month++) {
month_begin = mktime(year " " month " " "01 00 00 00")
month_end = mktime(year " " month+1 " " "01 00 00 -01")
Begin = strftime("%Y%m%d", month_begin)
End = strftime("%Y%m%d", month_end)
for (F = 1; F <= n_lines; F++ ) {
split(line[F], arr, SUBSEP)
if (arr[2] >= Begin && arr[2] <= End ) {
printtitle(strftime("%B %Y", month_begin))
printtext("", "ACCOUNT", "RATE OF CHANGE", "BALANCE")
Total=0
for (K in konto) {
sum = 0
count = 0
Total+=konto[K]
for (F = 1; F <= n_lines; F++ ) {
split(line[F], arr, SUBSEP)
if (arr[1] == K) {
if (arr[2] >= Begin && arr[2] <= End ) {
sum+=arr[3]
oldsum[K]+=arr[3]
}
}
}
printbody("", K, sum, oldsum[K])
}
printsum("", "Sum", Total, "")
}
}
}
}
#######################################################################
# SHOW CHANGES OF AMOUNT FOR EACH ACCOUNT AND THIS FOR EACH YEAR
#######################################################################
print ""
# clear out the oldsum-array
delete oldsum
for (year = 2000; year <= 2020; year++) {
year_begin = mktime(year " " "01 01 00 00 00")
year_end = mktime(year+1 " " "01 01 00 00 -01")
Begin = strftime("%Y%m%d", year_begin)
End = strftime("%Y%m%d", year_end)
for (F = 1; F <= n_lines; F++ ) {
split(line[F], arr, SUBSEP)
if (arr[2] >= Begin && arr[2] <= End ) {
printtitle(strftime("%Y", year_begin))
printtext("", "ACCOUNT", "RATE OF CHANGE", "BALANCE")
Total=0
for (K in konto) {
sum = 0
Total+=konto[K]
for (F = 1; F <= n_lines; F++ ) {
split(line[F], arr, SUBSEP)
if (arr[1] == K) {
if (arr[2] >= Begin && arr[2] <= End ) {
sum+=arr[3]
oldsum[K]+=arr[3]
}
}
}
printbody("", K, sum, oldsum[K])
}
printsum("", "Sum", Total, "")
}
}
}
print ""
#######################################################################
# SHOW TOTALS BY ACCOUNT_TYPE
#######################################################################
printtitle("TOTAL BY ACCOUNT-TYPE")
printtext("", "ACCOUNT-TYPE", "BALANCE", "")
Total=0
for (F in kind) {
Total+=kind[F]
printbody("", F, kind[F], "")
}
printsum("", "Sum", Total, "")
print ""
#######################################################################
# SHOW TOTALS BY ENDUSSER AND ACCOUNT TYPE
#######################################################################
printtitle("TOTAL BY ENDUSER AND ACCOUNT TYPE")
printtext("ENDUSER", "TYPE", "BALANCE" "")
Total=0
for (i = 1; i <= n_users; i++) {
Total+=endusers_sort[i]
split (endusers_sort[i], arr, SUBSEP)
printbody(arr[1], arr[2], enduser[endusers_sort[i]], "")
}
printsum("", "Sum", Total, "")
print ""
#######################################################################
# SHOW TOTALS BY CATECORIE AND ACCOUNT TYPE
#######################################################################
printtitle("TOTAL BY CATEGORIE AND ACCOUNT TYPE")
printtext("CATEGORIE", "TYPE", "BALANCE", "")
Total=0
for (i = 1; i <= n_categories; i++) {
Total+=categories_sort[i]
split (categories_sort[i], arr, SUBSEP)
printbody(arr[1], arr[2], categorie[categories_sort[i]], "")
}
printsum("", "Sum", Total, "")
print ""
#######################################################################
# SHOW TOTALS BY ACCOUNT, SORTED BY ACCOUNT NAME
#######################################################################
printtitle("TOTAL BY ACCOUNT, SORTED BY ACCOUNT NAME")
printtext("", "ACCOUNT", "BALANCE" "")
Total=0
# to uniq the sorted account names
for (F = 1; F <= n_lines; F++ ) {
split(line[F], arr, SUBSEP)
if ( ACC != arr[1] ) {
ACC=arr[1]
# to print the sum for each account
for (K in konto) {
if ( K == arr[1] ) {
printbody("", K, konto[K], "")
Total+=konto[K]
}
}
}
}
printsum("", "Sum", Total, "")
print ""
}'