From: Joey Schulze Date: Mon, 25 Apr 2016 15:56:05 +0000 (+0200) Subject: Rewrite in Perl X-Git-Url: https://git.infodrom.org/?p=infodrom.org%2Fservice.infodrom.org;a=commitdiff_plain;h=9d7c244929867844baeb80362330796e55bf62e9 Rewrite in Perl --- diff --git a/src/InfoCon/buch/vorsteueranmeldung b/src/InfoCon/buch/vorsteueranmeldung index d123398..dbf9abd 100755 --- a/src/InfoCon/buch/vorsteueranmeldung +++ b/src/InfoCon/buch/vorsteueranmeldung @@ -1,134 +1,296 @@ -#! /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 < +# +# 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 < 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 <= '$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 <= '$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 <= '$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 <= '$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 <= '$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 <= '$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 <= '$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;