--- /dev/null
+#! /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_forcecolor = 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,
+ 'force-color' => \$opt_forcecolor,
+ '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;
+
+$opt_nocolor = !(-t STDOUT) unless $opt_forcecolor;
+
+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;
projects / infodrom.org / service.infodrom.org / blobdiff