-#! /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="\e["
-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
+#! /usr/bin/perl
+
+# infocon - Administration tool for InfoCon
+# Copyright (c) 2000-2016 Joey Schulze <joey@infodrom.org>
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
+
+use strict;
+use warnings;
+
+use DBI;
+use Switch;
+use Getopt::Long;
+use Config::Simple;
+use Term::ANSIColor;
+use Scalar::Util qw/looks_like_number/;
+
+use constant CONFIG => '/etc/infocon.cfg';
+
+my $opt_mode = 'month';
+my $opt_nocolor = 0;
+my $opt_start;
+my $opt_end;
+my $cfg;
+my $dbh;
+
+sub help()
+{
+ print <<EOT;
+vorsteueranmeldung [-h|--help] [-q|--quarter] [-m|--month] [year quarter|month] [--no-color]
+EOT
+ exit;
+}
+
+sub cprint
+{
+ print color $_[0] unless $opt_nocolor;
+ print $_[1];
+ print color 'reset' unless $opt_nocolor;
+}
+
+sub calculate_start_end_quarter
+{
+ my $year;
+ my $month;
+
+ if (scalar @_ == 2) {
+ $year = $_[0];
+ switch ($_[1]) {
+ case 1 { $month = 4; }
+ case 2 { $month = 7; }
+ case 3 { $month = 10; }
+ case 4 { $month = 13; }
+ }
+ } else {
+ my @now = localtime;
+ $year = $now[5] + 1900;
+ $month = $now[4] + 1;
+ }
+
+ if ($month % 3 == 1) {
+ if ($month < 13 ) {
+ $opt_start = sprintf('%04d%02d01', $year, $month - 3);
+ $opt_end = sprintf('%04d%02d01', $year, $month);
+ } else {
+ $opt_start = sprintf('%04d1001', $year);
+ $opt_end = sprintf('%04d0101', $year + 1);
+ }
+ } else {
+ die "No automatic quarter calculation possible\n";
+ }
+}
+
+sub calculate_start_end_month
+{
+ my $year;
+ my $month;
+
+ if (scalar @_ == 2) {
+ $year = $_[0];
+ $month = $_[1];
+ } else {
+ my @now = localtime;
+ $year = $now[5] + 1900;
+ $month = $now[4];
+ }
+
+ if ($month > 0 && $month < 13) {
+ if ($month < 12 ) {
+ $opt_start = sprintf('%04d%02d01', $year, $month);
+ $opt_end = sprintf('%04d%02d01', $year, $month + 1);
+ } else {
+ $opt_start = sprintf('%04d%02d01', $year, $month);
+ $opt_end = sprintf('%04d0101', $year + 1);
+ }
+ } else {
+ die "No automatic month calculation possible\n";
+ }
+}
+
+sub calculate_start_end
+{
+ calculate_start_end_quarter @_ if $opt_mode eq 'quarter';
+ calculate_start_end_month @_ if $opt_mode eq 'month';
+}
+
+sub print_table
+{
+ my $dbh = shift;
+ my $sql = shift;
+ my @length;
+
+ return unless $sql =~ /SELECT\s+(.*?)\s+FROM/is;
+ my @columns = split /,/, $1;
+ my $i=0; while ($i < @columns) {
+ $columns[$i] = $1 if $columns[$i] =~ /.*\sAS\s(.*)/is;
+ $i++;
+ }
+
+ push @length, length $_ foreach @columns;
+
+ my $rows = $dbh->selectall_arrayref($sql);
+
+ return unless scalar @$rows && scalar @{$rows->[0]} && defined $rows->[0][0];
+
+ foreach my $row (@$rows) {
+ $i=0; while ($i < @$row) {
+ $row->[$i] = '' unless defined $row->[$i];
+ $length[$i] = length $row->[$i] if length $row->[$i] > $length[$i];
+ $i++;
+ }
+ }
+
+ my $formatstr = '';
+ my $formatlen = 0;
+ my $tablesep = '';
+ $i=0; while ($i < @columns) {
+ if (length $formatstr) {
+ $formatstr .= '|';
+ $formatlen += 1;
+ $tablesep .= '+';
+ }
+ $formatstr .= sprintf(' %%%s%ds ', (looks_like_number $rows->[0][$i]?'':'-'), $length[$i]);
+ $formatlen += 2 + $length[$i];
+ $tablesep .= '-' x (2 + $length[$i]);
+ $i++;
+ }
+ $formatstr .= "\n";
+ $tablesep .= "\n";
+
+ printf $formatstr, @columns;
+ print $tablesep;
+ printf $formatstr, @$_ foreach @$rows;
+ print "\n";
+}
+
+sub print_income
+{
+ my $dbh = shift;
+ my $sql;
+
+ cprint 'bold yellow', 'Lieferungen (Einnahmen)';
+ print "\n" . "~" x 23 . "\n\n";
+
+ $sql = qq{
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
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price > 0.0 AND visible = 1
+ ORDER BY date,nr};
+ print_table $dbh, $sql;
-psql --pset footer=off $db <<EOF
+ $sql = qq{
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
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price > 0.0 AND visible = 1
+ GROUP BY tax_percent
+ ORDER BY tax_percent};
+ print_table $dbh, $sql;
+
+ $sql = qq{
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
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price > 0.0 AND visible = 1};
+ print_table $dbh, $sql;
+}
+
+sub print_outgoing
+{
+ my $dbh = shift;
+ my $sql;
+
+ cprint 'bold yellow', 'Abziehbare Vorsteuerbeträge (Ausgaben)';
+ print "\n" . "~" x 38 . "\n\n";
+
+ $sql = qq{
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
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price < 0.0 AND visible = 1
+ AND category <> 'privat'
+ AND category <> 'tax'
+ ORDER BY date,nr};
+ print_table $dbh, $sql;
+
+ $sql = qq{
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
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price < 0.0 AND visible = 1
+ AND category <> 'privat'
+ AND category <> 'tax'
+ GROUP BY tax_percent
+ ORDER BY tax_percent};
+ print_table $dbh, $sql;
+
+ $sql = qq{
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)"
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price < 0.0 AND visible = 1
+ AND category <> 'privat'
+ AND category <> 'tax'};
+ print_table $dbh, $sql;
+}
+
+sub print_summary
+{
+ my $dbh = shift;
+ my $sql;
+
+ cprint 'bold yellow', 'Umsatzsteuer-Voranmeldung';
+ print "\n" . "~" x 25 . "\n\n";
+
+ $sql = qq{
+SELECT '1 Einnahmen' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price > 0.0 AND visible = 1
+UNION
+SELECT '2 Ausgaben' AS type, sum(price) * -1 AS brutto, sum(price) * -1 - sum(tax_assigned) * -1 AS netto, sum(tax_assigned) * -1 AS tax
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND price < 0.0 AND visible = 1
+ AND category <> 'privat'
+ AND category <> 'tax'
+UNION
+SELECT '3 Summe' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
+ FROM sales
+WHERE date >= '$opt_start' AND date < '$opt_end'
+ AND visible = 1
+ AND category <> 'privat'
+ AND category <> 'tax'
+ORDER BY type
+};
+ print_table $dbh, $sql;
+
+}
+
+GetOptions ('help|h' => \&help,
+ 'quarter|q' => sub {$opt_mode = 'quarter'},
+ 'month|m' => sub {$opt_mode = 'month'},
+ 'no-color|nocolor' => \$opt_nocolor,
+ 'start' => \$opt_start,
+ 'end' => \$opt_end)
+ or die("Error in command line arguments\n");
+
+$cfg = new Config::Simple(CONFIG);
+
+if (!defined $opt_start || !defined $opt_end) {
+ calculate_start_end @ARGV;
+}
+
+die "Unknown date format (yyyymmdd required)\n" unless length $opt_start == 8 && length $opt_end == 8;
+
+$dbh = DBI->connect('dbi:Pg:dbname='.$cfg->param('local.database'), $cfg->param('local.username'));
+
+print_income $dbh;
+print_outgoing $dbh;
+print_summary $dbh;
+
+$dbh->disconnect;