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';
34 my $opt_forcecolor = 0;
43 vorsteueranmeldung [-h|--help] [-q|--quarter] [-m|--month] [year quarter|month] [--no-color]
50 print color $_[0] unless $opt_nocolor;
52 print color 'reset' unless $opt_nocolor;
55 sub calculate_start_end_quarter
63 case 1 { $month = 4; }
64 case 2 { $month = 7; }
65 case 3 { $month = 10; }
66 case 4 { $month = 13; }
70 $year = $now[5] + 1900;
74 if ($month % 3 == 1) {
76 $opt_start = sprintf('%04d%02d01', $year, $month - 3);
77 $opt_end = sprintf('%04d%02d01', $year, $month);
79 $opt_start = sprintf('%04d1001', $year);
80 $opt_end = sprintf('%04d0101', $year + 1);
83 die "No automatic quarter calculation possible\n";
87 sub calculate_start_end_month
97 $year = $now[5] + 1900;
101 if ($month > 0 && $month < 13) {
103 $opt_start = sprintf('%04d%02d01', $year, $month);
104 $opt_end = sprintf('%04d%02d01', $year, $month + 1);
106 $opt_start = sprintf('%04d%02d01', $year, $month);
107 $opt_end = sprintf('%04d0101', $year + 1);
110 die "No automatic month calculation possible\n";
114 sub calculate_start_end
116 calculate_start_end_quarter @_ if $opt_mode eq 'quarter';
117 calculate_start_end_month @_ if $opt_mode eq 'month';
126 return unless $sql =~ /SELECT\s+(.*?)\s+FROM/is;
127 my @columns = split /,/, $1;
128 my $i=0; while ($i < @columns) {
129 $columns[$i] = $1 if $columns[$i] =~ /.*\sAS\s(.*)/is;
133 push @length, length $_ foreach @columns;
135 my $rows = $dbh->selectall_arrayref($sql);
137 return unless scalar @$rows && scalar @{$rows->[0]} && defined $rows->[0][0];
139 foreach my $row (@$rows) {
140 $i=0; while ($i < @$row) {
141 $row->[$i] = '' unless defined $row->[$i];
142 $length[$i] = length $row->[$i] if length $row->[$i] > $length[$i];
150 $i=0; while ($i < @columns) {
151 if (length $formatstr) {
156 $formatstr .= sprintf(' %%%s%ds ', (looks_like_number $rows->[0][$i]?'':'-'), $length[$i]);
157 $formatlen += 2 + $length[$i];
158 $tablesep .= '-' x (2 + $length[$i]);
164 printf $formatstr, @columns;
166 printf $formatstr, @$_ foreach @$rows;
175 cprint 'bold yellow', 'Lieferungen (Einnahmen)';
176 print "\n" . "~" x 23 . "\n\n";
179 SELECT date,billing_date AS billing,description,tax_percent,tax_assigned,price
181 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
182 AND price > 0.0 AND visible = 1
184 print_table $dbh, $sql;
187 SELECT tax_percent as percent,sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
189 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
190 AND price > 0.0 AND visible = 1
192 ORDER BY tax_percent};
193 print_table $dbh, $sql;
196 SELECT sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
198 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
199 AND price > 0.0 AND visible = 1};
200 print_table $dbh, $sql;
208 cprint 'bold yellow', 'Abziehbare Vorsteuerbeträge (Ausgaben)';
209 print "\n" . "~" x 38 . "\n\n";
212 SELECT date,billing_date AS billing,description,tax_percent,tax_assigned * -1 as tax_assigned,price * -1 as price
214 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
215 AND price < 0.0 AND visible = 1
216 AND category <> 'privat'
217 AND category <> 'tax'
219 print_table $dbh, $sql;
222 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
224 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
225 AND price < 0.0 AND visible = 1
226 AND category <> 'privat'
227 AND category <> 'tax'
229 ORDER BY tax_percent};
230 print_table $dbh, $sql;
233 SELECT sum(price) * -1 AS brutto, sum(price) * -1 - sum(tax_assigned) * -1 AS netto, sum(tax_assigned) * -1 AS tax
235 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
236 AND price < 0.0 AND visible = 1
237 AND category <> 'privat'
238 AND category <> 'tax'};
239 print_table $dbh, $sql;
247 cprint 'bold yellow', 'Umsatzsteuer-Voranmeldung';
248 print "\n" . "~" x 25 . "\n\n";
251 SELECT '1 Einnahmen' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
253 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
254 AND price > 0.0 AND visible = 1
256 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
258 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
259 AND price < 0.0 AND visible = 1
260 AND category <> 'privat'
261 AND category <> 'tax'
263 SELECT '3 Summe' AS type, sum(price) AS brutto, sum(price) - sum(tax_assigned) AS netto, sum(tax_assigned) AS tax
265 WHERE billing_date >= '$opt_start' AND billing_date < '$opt_end'
267 AND category <> 'privat'
268 AND category <> 'tax'
271 print_table $dbh, $sql;
275 GetOptions ('help|h' => \&help,
276 'quarter|q' => sub {$opt_mode = 'quarter'},
277 'month|m' => sub {$opt_mode = 'month'},
278 'no-color|nocolor|raw' => \$opt_nocolor,
279 'force-color' => \$opt_forcecolor,
280 'start' => \$opt_start,
282 or die("Error in command line arguments\n");
284 $cfg = new Config::Simple(CONFIG);
286 die "Cannot read config file\n" unless $cfg;
288 $opt_nocolor = !(-t STDOUT) unless $opt_forcecolor;
290 if (!defined $opt_start || !defined $opt_end) {
291 calculate_start_end @ARGV;
294 die "Unknown date format (yyyymmdd required)\n" unless length $opt_start == 8 && length $opt_end == 8;
296 $dbh = DBI->connect('dbi:Pg:dbname='.$cfg->param('local.database'), $cfg->param('local.username'));