#! /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,billing_date AS billing,description,tax_percent,tax_assigned,price
        FROM sales
WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
        AND price > 0.0 AND visible = 1
        ORDER BY date,nr};
    print_table $dbh, $sql;

    $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 billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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,billing_date AS billing,description,tax_percent,tax_assigned * -1 as tax_assigned,price * -1 as price
        FROM sales
WHERE billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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 billing_date >= '$opt_start' AND billing_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|raw' => \$opt_nocolor,
	    'start' => \$opt_start,
	    'end' => \$opt_end)
    or die("Error in command line arguments\n");

$cfg = new Config::Simple(CONFIG);

die "Cannot read config file\n" unless $cfg;

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;