3 # infocon - Administration tool for InfoCon
4 # Copyright (c) 2000-2016 Joey Schulze <joey@infodrom.org>
6 # This program is free software; you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by
8 # the Free Software Foundation; either version 2 of the License, or
9 # (at your option) any later version.
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program; if not, write to the Free Software
18 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
28 use Scalar::Util qw/looks_like_number/;
30 use constant CONFIG => '/etc/infocon.cfg';
32 my $opt_mode = 'month';
42 vorsteueranmeldung [-h|--help] [-q|--quarter] [-m|--month] [year quarter|month] [--no-color]
49 print color $_[0] unless $opt_nocolor;
51 print color 'reset' unless $opt_nocolor;
54 sub calculate_start_end_quarter
62 case 1 { $month = 4; }
63 case 2 { $month = 7; }
64 case 3 { $month = 10; }
65 case 4 { $month = 13; }
69 $year = $now[5] + 1900;
73 if ($month % 3 == 1) {
75 $opt_start = sprintf('%04d%02d01', $year, $month - 3);
76 $opt_end = sprintf('%04d%02d01', $year, $month);
78 $opt_start = sprintf('%04d1001', $year);
79 $opt_end = sprintf('%04d0101', $year + 1);
82 die "No automatic quarter calculation possible\n";
86 sub calculate_start_end_month
96 $year = $now[5] + 1900;
100 if ($month > 0 && $month < 13) {
102 $opt_start = sprintf('%04d%02d01', $year, $month);
103 $opt_end = sprintf('%04d%02d01', $year, $month + 1);
105 $opt_start = sprintf('%04d%02d01', $year, $month);
106 $opt_end = sprintf('%04d0101', $year + 1);
109 die "No automatic month calculation possible\n";
113 sub calculate_start_end
115 calculate_start_end_quarter @_ if $opt_mode eq 'quarter';
116 calculate_start_end_month @_ if $opt_mode eq 'month';
125 return unless $sql =~ /SELECT\s+(.*?)\s+FROM/is;
126 my @columns = split /,/, $1;
127 my $i=0; while ($i < @columns) {
128 $columns[$i] = $1 if $columns[$i] =~ /.*\sAS\s(.*)/is;
132 push @length, length $_ foreach @columns;
134 my $rows = $dbh->selectall_arrayref($sql);
136 return unless scalar @$rows && scalar @{$rows->[0]} && defined $rows->[0][0];
138 foreach my $row (@$rows) {
139 $i=0; while ($i < @$row) {
140 $row->[$i] = '' unless defined $row->[$i];
141 $length[$i] = length $row->[$i] if length $row->[$i] > $length[$i];
149 $i=0; while ($i < @columns) {
150 if (length $formatstr) {
155 $formatstr .= sprintf(' %%%s%ds ', (looks_like_number $rows->[0][$i]?'':'-'), $length[$i]);
156 $formatlen += 2 + $length[$i];
157 $tablesep .= '-' x (2 + $length[$i]);
163 printf $formatstr, @columns;
165 printf $formatstr, @$_ foreach @$rows;
174 cprint 'bold yellow', 'Lieferungen (Einnahmen)';
175 print "\n" . "~" x 23 . "\n\n";
178 SELECT date,billing_date AS billing,description,tax_percent,tax_assigned,price
180 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
181 AND price > 0.0 AND visible = 1
183 print_table $dbh, $sql;
186 SELECT tax_percent as percent,sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
188 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
189 AND price > 0.0 AND visible = 1
191 ORDER BY tax_percent};
192 print_table $dbh, $sql;
195 SELECT sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
197 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
198 AND price > 0.0 AND visible = 1};
199 print_table $dbh, $sql;
207 cprint 'bold yellow', 'Abziehbare Vorsteuerbeträge (Ausgaben)';
208 print "\n" . "~" x 38 . "\n\n";
211 SELECT date,billing_date AS billing,description,tax_percent,tax_assigned * -1 as tax_assigned,price * -1 as price
213 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
214 AND price < 0.0 AND visible = 1
215 AND category <> 'privat'
216 AND category <> 'tax'
218 print_table $dbh, $sql;
221 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
223 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
224 AND price < 0.0 AND visible = 1
225 AND category <> 'privat'
226 AND category <> 'tax'
228 ORDER BY tax_percent};
229 print_table $dbh, $sql;
232 SELECT sum(price) * -1 AS brutto, sum(price) * -1 - sum(tax_assigned) * -1 AS netto, sum(tax_assigned) * -1 AS tax
234 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
235 AND price < 0.0 AND visible = 1
236 AND category <> 'privat'
237 AND category <> 'tax'};
238 print_table $dbh, $sql;
246 cprint 'bold yellow', 'Umsatzsteuer-Voranmeldung';
247 print "\n" . "~" x 25 . "\n\n";
250 SELECT '1 Einnahmen' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
252 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
253 AND price > 0.0 AND visible = 1
255 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
257 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
258 AND price < 0.0 AND visible = 1
259 AND category <> 'privat'
260 AND category <> 'tax'
262 SELECT '3 Summe' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
264 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
266 AND category <> 'privat'
267 AND category <> 'tax'
270 print_table $dbh, $sql;
274 GetOptions ('help|h' => \&help,
275 'quarter|q' => sub {$opt_mode = 'quarter'},
276 'month|m' => sub {$opt_mode = 'month'},
277 'no-color|nocolor|raw' => \$opt_nocolor,
278 'start' => \$opt_start,
280 or die("Error in command line arguments\n");
282 $cfg = new Config::Simple(CONFIG);
284 die "Cannot read config file\n" unless $cfg;
286 if (!defined $opt_start || !defined $opt_end) {
287 calculate_start_end @ARGV;
290 die "Unknown date format (yyyymmdd required)\n" unless length $opt_start == 8 && length $opt_end == 8;
292 $dbh = DBI->connect('dbi:Pg:dbname='.$cfg->param('local.database'), $cfg->param('local.username'));