#! /bin/bash

db=infocon

if [ $# = 0 ]
then
    year=$(date +%Y)
    month=$(date +%m)

    if [ $(($month % 3)) -eq 1 ]
    then
	if [ $month -gt 3 ]
	then
	    start=$(printf %04d%02d01 $year $(($month - 3)))
	    end=$(printf %04d%02d01 $year $month)
	else
	    start=$(printf %04d1001 $(($year - 1)))
	    end=$(printf %04d0101 $year)
	fi
    else
	echo "Keine automatische Quartalsberechnung moeglich"
	exit 2
    fi
elif [ $# = 2 ]
then
    case $2 in
	1) month=1;;
	2) month=4;;
	3) month=7;;
	4) month=10;;
	*) echo "Unbekanntes Quartal"; exit 2;;
    esac

    if [ $month -eq 10 ]
    then
	start=$(printf %04d%02d01 $1 $month)
	end=$(printf %04d%02d01 $(($1 + 1)) 1)
    else
	start=$(printf %04d%02d01 $1 $month)
	end=$(printf %04d%02d01 $1 $(($month + 3)))
    fi
else
    echo "vorsteueranmeldung [year quartal]"
    exit 1
fi

ESC="["
vid_bold="${ESC}1m${ESC}33m"
# vid_bold="${ESC}1m"
# vid_white="${ESC}1m"
# vid_brown="${ESC}33m"
vid_norm="${ESC}0m"

echo "${vid_bold}Lieferungen (Einnahmen)${vid_norm}"
echo "-----------------------"
echo
psql --pset footer=off $db <<EOF
SELECT date,description,tax_percent,tax_assigned,price
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price > 0.0 AND visible = 1
	ORDER BY date,nr
EOF

psql --pset footer=off $db <<EOF
SELECT tax_percent as percent,sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price > 0.0 AND visible = 1
	GROUP BY tax_percent
	ORDER BY tax_percent
EOF

psql --pset footer=off $db <<EOF
SELECT sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price > 0.0 AND visible = 1
EOF

ust=$(psql --tuples-only $db <<EOF
SELECT sum(tax_assigned) AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price > 0.0 AND visible = 1
EOF
)

echo "${vid_bold}Abziehbare Vorsteuerbeträge (Ausgaben)${vid_norm}"
echo "--------------------------------------"
echo
psql --pset footer=off $db <<EOF
SELECT date,description,tax_percent,tax_assigned * -1 as tax_assigned,price * -1 as price
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price < 0.0 AND visible = 1
	AND category <> 'privat'
	AND category <> 'tax'
	ORDER BY date,nr
EOF

psql --pset footer=off $db <<EOF
SELECT tax_percent AS percent,sum(price) * -1 AS brutto, sum(price) * -1 - sum(tax_assigned) * -1 AS netto, sum(tax_assigned) * -1 AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price < 0.0 AND visible = 1
	AND category <> 'privat'
	AND category <> 'tax'
	GROUP BY tax_percent
	ORDER BY tax_percent
EOF

psql --pset footer=off $db <<EOF
SELECT sum(price) * -1 AS brutto, sum(price) * -1 - sum(tax_assigned) * -1 AS netto, sum(tax_assigned) * -1 AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price < 0.0 AND visible = 1
	AND category <> 'privat'
	AND category <> 'tax'
EOF

vst=$(psql --tuples-only $db <<EOF
SELECT sum(tax_assigned) * -1 AS tax
	FROM sales
WHERE date >= '$start'  AND date < '$end'
	AND price < 0.0 AND visible = 1
	AND category <> 'privat'
	AND category <> 'tax'
EOF
)

tax=$(echo "$ust - $vst"|bc)
echo "${vid_bold}Umsatzsteuer - Vorauszahlung${vid_norm}: $tax"
echo "Sparkasse WHV (282 501 10), Kto 211 7000)"