SL/RP.pm

   1 #=====================================================================
   2 # LX-Office ERP
   3 # Copyright (C) 2004
   4 # Based on SQL-Ledger Version 2.1.9
   5 # Web http://www.lx-office.org
   6 #
   7 #=====================================================================
   8 # SQL-Ledger Accounting
   9 # Copyright (C) 1998-2002
  10 #
  11 #  Author: Dieter Simader
  12 #   Email: dsimader@sql-ledger.org
  13 #     Web: http://www.sql-ledger.org
  14 #
  15 #  Contributors: Benjamin Lee <benjaminlee@consultant.com>
  16 #
  17 # This program is free software; you can redistribute it and/or modify
  18 # it under the terms of the GNU General Public License as published by
  19 # the Free Software Foundation; either version 2 of the License, or
  20 # (at your option) any later version.
  21 #
  22 # This program is distributed in the hope that it will be useful,
  23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 # GNU General Public License for more details.
  26 # You should have received a copy of the GNU General Public License
  27 # along with this program; if not, write to the Free Software
  28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  29 #======================================================================
  30 #
  31 # backend code for reports
  32 #
  33 #======================================================================
  34
  35 package RP;
  36
  37 use SL::DBUtils;
  38 use Data::Dumper;
  39 use List::Util qw(sum);
  40
  41 # use warnings;
  42 use strict;
  43
  44 # new implementation of balance sheet
  45 # readme!
  46 #
  47 # stuff missing from the original implementation:
  48 # - bold stuff
  49 # - subdescription
  50 # - proper testing for heading charts
  51 # - transmission from $form to TMPL realm is not as clear as i'd like
  52
  53 sub get_openbalance_date {
  54   my ($closedto, $target) = map { $::locale->parse_date_to_object(\%::myconfig, $_) } @_;
  55
  56   return unless $closedto;
  57
  58   $closedto->subtract(years => 1) while ($target - $closedto)->is_negative;
  59   $closedto->add(days => 1);
  60   return $::locale->format_date(\%::myconfig, $closedto);
  61 }
  62
  63 sub balance_sheet {
  64   $main::lxdebug->enter_sub();
  65
  66   my $myconfig = \%main::myconfig;
  67   my $form     = $main::form;
  68   my $dbh      = $::form->get_standard_dbh;
  69
  70   my $last_period = 0;
  71   my @categories  = qw(A C L Q);
  72
  73   # if there are any dates construct a where
  74   if ($form->{asofdate}) {
  75     $form->{period} = $form->{this_period} = conv_dateq($form->{asofdate});
  76   }
  77
  78   # get end of financial year and convert to Date format
  79   my ($closedto) = selectfirst_array_query($form, $dbh, 'SELECT closedto FROM defaults');
  80
  81   # get date of last opening balance
  82   my $startdate = get_openbalance_date($closedto, $form->{asofdate});
  83
  84   get_accounts($dbh, $last_period, $startdate, $form->{asofdate}, $form, \@categories);
  85
  86   # if there are any compare dates
  87   if ($form->{compareasofdate}) {
  88     $last_period = 1;
  89
  90     $startdate = get_openbalance_date($closedto, $form->{compareasofdate});
  91
  92     get_accounts($dbh, $last_period, $startdate, $form->{compareasofdate}, $form, \@categories);
  93     $form->{last_period} = conv_dateq($form->{compareasofdate});
  94   }
  95
  96   # now we got $form->{A}{accno}{ }    assets
  97   # and $form->{L}{accno}{ }           liabilities
  98   # and $form->{Q}{accno}{ }           equity
  99   # build asset accounts
 100
 101   my %account = ('A' => { 'ml'     => -1 },
 102                  'L' => { 'ml'     =>  1 },
 103                  'Q' => { 'ml'     =>  1 });
 104
 105   my $TMPL_DATA = {};
 106
 107   foreach my $category (grep { !/C/ } @categories) {
 108
 109     $TMPL_DATA->{$category} = [];
 110     my $ml  = $account{$category}{ml};
 111
 112     foreach my $key (sort keys %{ $form->{$category} }) {
 113
 114       my $row = { %{ $form->{$category}{$key} } };
 115
 116       # if charttype "heading" - calculate this entry, start a new batch of charts belonging to this heading and skip the rest bo the loop
 117       # header charts are not real charts. start a sub aggregation with them, but don't calculate anything with them
 118       if ($row->{charttype} eq "H") {
 119         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
 120           $row->{subdescription} = $account{$category}{subdescription};
 121           $row->{this}           = $account{$category}{subthis} * $ml;                   # format: $dec, $dash
 122           $row->{last}           = $account{$category}{sublast} * $ml if $last_period;   # format: $dec, $dash
 123         }
 124
 125         $row->{subheader} = 1;
 126         $account{$category}{subthis}        = $row->{this};
 127         $account{$category}{sublast}        = $row->{last};
 128         $account{$category}{subdescription} = $row->{description};
 129         $account{$category}{subtotal} = 1;
 130
 131         $row->{this} = 0;
 132         $row->{last} = 0;
 133
 134         next unless $form->{l_heading};
 135       }
 136
 137       for my $period (qw(this last)) {
 138         next if ($period eq 'last' && !$last_period);
 139         # only add assets
 140         $row->{$period}                    *= $ml;
 141       }
 142
 143       push @{ $TMPL_DATA->{$category} }, $row;
 144     } # foreach
 145
 146     # resolve heading/subtotal
 147     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
 148       $TMPL_DATA->{$category}[-1]{subdescription} = $account{$category}{subdescription};
 149       $TMPL_DATA->{$category}[-1]{this}           = $account{$category}{subthis} * $ml;                   # format: $dec, $dash
 150       $TMPL_DATA->{$category}[-1]{last}           = $account{$category}{sublast} * $ml if $last_period;   # format: $dec, $dash
 151     }
 152
 153     $TMPL_DATA->{total}{$category}{this} = sum map { $_->{this} } @{ $TMPL_DATA->{$category} };
 154     $TMPL_DATA->{total}{$category}{last} = sum map { $_->{last} } @{ $TMPL_DATA->{$category} };
 155   }
 156
 157   for my $period (qw(this last)) {
 158     next if ($period eq 'last' && !$last_period);
 159
 160     $form->{E}{$period}             = $TMPL_DATA->{total}{A}{$period} - $TMPL_DATA->{total}{L}{$period} - $TMPL_DATA->{total}{Q}{$period};
 161     $TMPL_DATA->{total}{Q}{$period}     += $form->{E}{$period};
 162     $TMPL_DATA->{total}{$period}    = $TMPL_DATA->{total}{L}{$period} + $TMPL_DATA->{total}{Q}{$period};
 163   }
 164     $form->{E}{description}='nicht verbuchter Gewinn/Verlust';
 165   push @{ $TMPL_DATA->{Q} }, $form->{E};
 166
 167   $main::lxdebug->leave_sub();
 168
 169   return $TMPL_DATA;
 170 }
 171
 172 sub get_accounts {
 173   $main::lxdebug->enter_sub();
 174
 175   my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
 176
 177   my ($null, $department_id) = split /--/, $form->{department};
 178
 179   my $query;
 180   my $dpt_where = '';
 181   my $dpt_join  = '';
 182   my $project   = '';
 183   my $where     = "1 = 1";
 184   my $glwhere   = "";
 185   my $subwhere  = "";
 186   my $item;
 187   my $sth;
 188   my $dec = $form->{decimalplaces};
 189
 190   my $category = qq| AND (| . join(" OR ", map({ "(c.category = " . $dbh->quote($_) . ")" } @{$categories})) . qq|) |;
 191
 192   # get headings
 193   $query =
 194     qq|SELECT c.accno, c.description, c.category
 195        FROM chart c
 196        WHERE (c.charttype = 'H')
 197          $category
 198        ORDER by c.accno|;
 199
 200   $sth = prepare_execute_query($form, $dbh, $query);
 201
 202   my @headingaccounts = ();
 203   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 204     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
 205       "$ref->{description}";
 206     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
 207     $form->{ $ref->{category} }{ $ref->{accno} }{accno}     = $ref->{accno};
 208
 209     push @headingaccounts, $ref->{accno};
 210   }
 211
 212   $sth->finish;
 213
 214   if ($fromdate) {
 215     $fromdate = conv_dateq($fromdate);
 216     if ($form->{method} eq 'cash') {
 217       $subwhere .= " AND (transdate >= $fromdate)";
 218       $glwhere = " AND (ac.transdate >= $fromdate)";
 219     } else {
 220       $where .= " AND (ac.transdate >= $fromdate)";
 221     }
 222   }
 223
 224   if ($todate) {
 225     $todate = conv_dateq($todate);
 226     $where    .= " AND (ac.transdate <= $todate)";
 227     $subwhere .= " AND (transdate <= $todate)";
 228   }
 229
 230   if ($department_id) {
 231     $dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
 232     $dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|)|;
 233   }
 234
 235   if ($form->{project_id}) {
 236     # Diese Bedingung wird derzeit niemals wahr sein, da man in Bericht->Bilanz keine
 237     # Projekte auswählen kann
 238     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
 239   }
 240
 241   if ($form->{method} eq 'cash') {
 242     $query =
 243       qq|SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
 244          FROM acc_trans ac
 245          JOIN chart c ON (c.id = ac.chart_id)
 246          JOIN ar a ON (a.id = ac.trans_id)
 247          $dpt_join
 248          WHERE $where
 249            $dpt_where
 250            $category
 251            AND ac.trans_id IN
 252              (
 253                SELECT trans_id
 254                FROM acc_trans a
 255                JOIN chart c ON (a.chart_id = c.id)
 256                WHERE (link LIKE '%AR_paid%')
 257                $subwhere
 258              )
 259            $project
 260          GROUP BY c.accno, c.description, c.category
 261
 262          UNION ALL
 263
 264          SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
 265          FROM acc_trans ac
 266          JOIN chart c ON (c.id = ac.chart_id)
 267          JOIN ap a ON (a.id = ac.trans_id)
 268          $dpt_join
 269          WHERE $where
 270            $dpt_where
 271            $category
 272            AND ac.trans_id IN
 273              (
 274                SELECT trans_id
 275                FROM acc_trans a
 276                JOIN chart c ON (a.chart_id = c.id)
 277                WHERE (link LIKE '%AP_paid%')
 278                $subwhere
 279              )
 280            $project
 281          GROUP BY c.accno, c.description, c.category
 282
 283          UNION ALL
 284
 285          SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
 286          FROM acc_trans ac
 287          JOIN chart c ON (c.id = ac.chart_id)
 288          JOIN gl a ON (a.id = ac.trans_id)
 289          $dpt_join
 290          WHERE $where
 291            $glwhere
 292            $dpt_where
 293            $category
 294              AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
 295            $project
 296          GROUP BY c.accno, c.description, c.category |;
 297
 298     if ($form->{project_id}) {
 299       # s.o. keine Projektauswahl in Bilanz
 300       $query .=
 301         qq|
 302          UNION ALL
 303
 304          SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
 305          FROM invoice ac
 306          JOIN ar a ON (a.id = ac.trans_id)
 307          JOIN parts p ON (ac.parts_id = p.id)
 308          JOIN chart c on (p.income_accno_id = c.id)
 309          $dpt_join
 310          -- use transdate from subwhere
 311          WHERE (c.category = 'I')
 312            $subwhere
 313            $dpt_where
 314            AND ac.trans_id IN
 315              (
 316                SELECT trans_id
 317                FROM acc_trans a
 318                JOIN chart c ON (a.chart_id = c.id)
 319                WHERE (link LIKE '%AR_paid%')
 320                $subwhere
 321              )
 322            $project
 323          GROUP BY c.accno, c.description, c.category
 324
 325          UNION ALL
 326
 327          SELECT c.accno AS accno, SUM(ac.sellprice) AS amount, c.description AS description, c.category
 328          FROM invoice ac
 329          JOIN ap a ON (a.id = ac.trans_id)
 330          JOIN parts p ON (ac.parts_id = p.id)
 331          JOIN chart c on (p.expense_accno_id = c.id)
 332          $dpt_join
 333          WHERE (c.category = 'E')
 334            $subwhere
 335            $dpt_where
 336            AND ac.trans_id IN
 337              (
 338                SELECT trans_id
 339                FROM acc_trans a
 340                JOIN chart c ON (a.chart_id = c.id)
 341                WHERE link LIKE '%AP_paid%'
 342                $subwhere
 343              )
 344            $project
 345          GROUP BY c.accno, c.description, c.category |;
 346     }
 347
 348   } else {                      # if ($form->{method} eq 'cash')
 349     if ($department_id) {
 350       $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
 351       $dpt_where = qq| AND t.department_id = | . conv_i($department_id);
 352     }
 353
 354     $query = qq|
 355       SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
 356       FROM acc_trans ac
 357       JOIN chart c ON (c.id = ac.chart_id)
 358       $dpt_join
 359       WHERE $where
 360         $dpt_where
 361         $category
 362         $project
 363       GROUP BY c.accno, c.description, c.category |;
 364
 365     if ($form->{project_id}) {
 366       # s.o. keine Projektauswahl in Bilanz
 367       $query .= qq|
 368       UNION ALL
 369
 370       SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
 371       FROM invoice ac
 372       JOIN ar a ON (a.id = ac.trans_id)
 373       JOIN parts p ON (ac.parts_id = p.id)
 374       JOIN chart c on (p.income_accno_id = c.id)
 375       $dpt_join
 376       -- use transdate from subwhere
 377       WHERE (c.category = 'I')
 378         $subwhere
 379         $dpt_where
 380         $project
 381       GROUP BY c.accno, c.description, c.category
 382
 383       UNION ALL
 384
 385       SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, c.description AS description, c.category
 386       FROM invoice ac
 387       JOIN ap a ON (a.id = ac.trans_id)
 388       JOIN parts p ON (ac.parts_id = p.id)
 389       JOIN chart c on (p.expense_accno_id = c.id)
 390       $dpt_join
 391       WHERE (c.category = 'E')
 392         $subwhere
 393         $dpt_where
 394         $project
 395       GROUP BY c.accno, c.description, c.category |;
 396     }
 397   }
 398
 399   my @accno;
 400   my $accno;
 401   my $ref;
 402
 403   $sth = prepare_execute_query($form, $dbh, $query);
 404
 405   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
 406
 407     if ($ref->{category} eq 'C') {
 408       $ref->{category} = 'A';
 409     }
 410
 411     # get last heading account
 412     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
 413     $accno = pop @accno;
 414     if ($accno) {
 415       if ($last_period) {
 416         $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
 417       } else {
 418         $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
 419       }
 420     }
 421
 422     $form->{ $ref->{category} }{ $ref->{accno} }{accno}       = $ref->{accno};
 423     $form->{ $ref->{category} }{ $ref->{accno} }{description} = $ref->{description};
 424     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
 425
 426     if ($last_period) {
 427       $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
 428     } else {
 429       $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
 430     }
 431   }
 432   $sth->finish;
 433
 434   # remove accounts with zero balance
 435   foreach $category (@{$categories}) {
 436     foreach $accno (keys %{ $form->{$category} }) {
 437       $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $dec);
 438       $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $dec);
 439
 440       delete $form->{$category}{$accno}
 441         if (   $form->{$category}{$accno}{this} == 0
 442             && $form->{$category}{$accno}{last} == 0);
 443     }
 444   }
 445
 446   $main::lxdebug->leave_sub();
 447 }
 448
 449 sub get_accounts_g {
 450   $main::lxdebug->enter_sub();
 451
 452   my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
 453
 454   my ($null, $department_id) = split /--/, $form->{department};
 455
 456   my $query;
 457   my $dpt_where;
 458   my $dpt_join;
 459   my $project;
 460   my $where    = "1 = 1";
 461   my $glwhere  = "";
 462   my $prwhere  = "";
 463   my $subwhere = "";
 464   my $inwhere = "";
 465   my $item;
 466
 467   if ($fromdate) {
 468     $fromdate = conv_dateq($fromdate);
 469     if ($form->{method} eq 'cash') {
 470       $subwhere .= " AND (transdate    >= $fromdate)";
 471       $glwhere   = " AND (ac.transdate >= $fromdate)";
 472       $prwhere   = " AND (a.transdate  >= $fromdate)";
 473       $inwhere   = " AND (acc.transdate >= $fromdate)";
 474     } else {
 475       $where    .= " AND (ac.transdate >= $fromdate)";
 476     }
 477   }
 478
 479   if ($todate) {
 480     $todate = conv_dateq($todate);
 481     $subwhere   .= " AND (transdate    <= $todate)";
 482     $where      .= " AND (ac.transdate <= $todate)";
 483     $prwhere    .= " AND (a.transdate  <= $todate)";
 484     $inwhere    .= " AND (acc.transdate <= $todate)";
 485   }
 486
 487   if ($department_id) {
 488     $dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
 489     $dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|) |;
 490   }
 491
 492   if ($form->{project_id}) {
 493     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |;
 494   }
 495
 496 #
 497 # GUV patch by Ronny Rentner (Bug 1190)
 498 #
 499 # GUV IST-Versteuerung
 500 #
 501 # Alle tatsaechlichen _Zahlungseingaenge_
 502 # im Zeitraum erfassen
 503 # (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
 504 #
 505 #
 506
 507   if ($form->{method} eq 'cash') {
 508     $query =
 509       qq|
 510        SELECT SUM( ac.amount *
 511                     (SELECT SUM(acc.amount) * -1
 512                      FROM acc_trans acc
 513                      INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%')
 514                      WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id)
 515                   / (SELECT amount FROM ar WHERE id = ac.trans_id)
 516                 ) AS amount, c.pos_eur
 517        FROM acc_trans ac
 518        LEFT JOIN chart c ON (c.id  = ac.chart_id)
 519        LEFT JOIN ar      ON (ar.id = ac.trans_id)
 520        LEFT JOIN taxkeys tk ON (tk.id = (
 521                                   SELECT id FROM taxkeys
 522                                   WHERE chart_id = ac.chart_id
 523                                   AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
 524                                   ORDER BY startdate DESC LIMIT 1
 525                                   )
 526                                 )
 527       WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere)
 528
 529       GROUP BY c.pos_eur
 530
 531 /*
 532        SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
 533          FROM acc_trans ac
 534          JOIN chart c ON (c.id = ac.chart_id)
 535          JOIN ar a ON (a.id = ac.trans_id)
 536          $dpt_join
 537          WHERE $where $dpt_where
 538            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
 539            $project
 540          GROUP BY c.$category
 541 */
 542          UNION
 543
 544          SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
 545          FROM acc_trans ac
 546          JOIN chart c ON (c.id = ac.chart_id)
 547          JOIN ap a ON (a.id = ac.trans_id)
 548          $dpt_join
 549          WHERE $where $dpt_where
 550            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
 551            $project
 552          GROUP BY c.$category
 553
 554          UNION
 555
 556          SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
 557          FROM acc_trans ac
 558          JOIN chart c ON (c.id = ac.chart_id)
 559          JOIN gl a ON (a.id = ac.trans_id)
 560          $dpt_join
 561          WHERE $where $dpt_where $glwhere
 562            AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
 563            $project
 564          GROUP BY c.$category
 565         |;
 566
 567     if ($form->{project_id}) {
 568       $query .= qq|
 569          UNION
 570
 571          SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
 572          FROM invoice ac
 573          JOIN ar a ON (a.id = ac.trans_id)
 574          JOIN parts p ON (ac.parts_id = p.id)
 575          JOIN chart c on (p.income_accno_id = c.id)
 576          $dpt_join
 577          WHERE (c.category = 'I') $prwhere $dpt_where
 578            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
 579            $project
 580          GROUP BY c.$category
 581
 582          UNION
 583
 584          SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
 585          FROM invoice ac
 586          JOIN ap a ON (a.id = ac.trans_id)
 587          JOIN parts p ON (ac.parts_id = p.id)
 588          JOIN chart c on (p.expense_accno_id = c.id)
 589          $dpt_join
 590          WHERE (c.category = 'E') $prwhere $dpt_where
 591            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
 592          $project
 593          GROUP BY c.$category
 594          |;
 595     }
 596
 597   } else {                      # if ($form->{method} eq 'cash')
 598     if ($department_id) {
 599       $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
 600       $dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
 601     }
 602
 603     $query = qq|
 604         SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
 605         FROM acc_trans ac
 606         JOIN chart c ON (c.id = ac.chart_id)
 607         $dpt_join
 608         WHERE $where
 609           $dpt_where
 610           $project
 611         GROUP BY c.$category |;
 612
 613     if ($form->{project_id}) {
 614       $query .= qq|
 615         UNION
 616
 617         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
 618         FROM invoice ac
 619         JOIN ar a ON (a.id = ac.trans_id)
 620         JOIN parts p ON (ac.parts_id = p.id)
 621         JOIN chart c on (p.income_accno_id = c.id)
 622         $dpt_join
 623         WHERE (c.category = 'I')
 624           $prwhere
 625           $dpt_where
 626           $project
 627         GROUP BY c.$category
 628
 629         UNION
 630
 631         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
 632         FROM invoice ac
 633         JOIN ap a ON (a.id = ac.trans_id)
 634         JOIN parts p ON (ac.parts_id = p.id)
 635         JOIN chart c on (p.expense_accno_id = c.id)
 636         $dpt_join
 637         WHERE (c.category = 'E')
 638           $prwhere
 639           $dpt_where
 640           $project
 641         GROUP BY c.$category |;
 642     }
 643   }
 644
 645   my @accno;
 646   my $accno;
 647   my $ref;
 648
 649   foreach my $ref (selectall_hashref_query($form, $dbh, $query)) {
 650     if ($category eq "pos_bwa") {
 651       if ($last_period) {
 652         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
 653       } else {
 654         $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
 655       }
 656     } else {
 657       $form->{ $ref->{$category} } += $ref->{amount};
 658     }
 659   }
 660
 661   $main::lxdebug->leave_sub();
 662 }
 663
 664 sub trial_balance {
 665   $main::lxdebug->enter_sub();
 666
 667   my ($self, $myconfig, $form, %options) = @_;
 668
 669   my $dbh = $form->dbconnect($myconfig);
 670
 671   my ($query, $sth, $ref);
 672   my %balance = ();
 673   my %trb     = ();
 674   my ($null, $department_id) = split /--/, $form->{department};
 675   my @headingaccounts = ();
 676   my $dpt_where;
 677   my $dpt_join;
 678   my $project;
 679
 680   my $where    = "1 = 1";
 681   my $invwhere = $where;
 682
 683   if ($department_id) {
 684     $dpt_join = qq| JOIN dpt_trans t ON (ac.trans_id = t.trans_id) |;
 685     $dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
 686   }
 687
 688   # project_id only applies to getting transactions
 689   # it has nothing to do with a trial balance
 690   # but we use the same function to collect information
 691
 692   if ($form->{project_id}) {
 693     $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
 694   }
 695
 696   my $acc_cash_where = "";
 697 #  my $ar_cash_where = "";
 698 #  my $ap_cash_where = "";
 699
 700
 701   if ($form->{method} eq "cash") {
 702     $acc_cash_where =
 703       qq| AND (ac.trans_id IN (
 704             SELECT id
 705             FROM ar
 706             WHERE datepaid >= '$form->{fromdate}'
 707               AND datepaid <= '$form->{todate}'
 708
 709             UNION
 710
 711             SELECT id
 712             FROM ap
 713             WHERE datepaid >= '$form->{fromdate}'
 714               AND datepaid <= '$form->{todate}'
 715
 716             UNION
 717
 718             SELECT id
 719             FROM gl
 720             WHERE transdate >= '$form->{fromdate}'
 721               AND transdate <= '$form->{todate}'
 722           )) |;
 723 #    $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |;
 724   }
 725
 726   if ($options{beginning_balances}) {
 727     foreach my $prefix (qw(from to)) {
 728       next if ($form->{"${prefix}date"});
 729
 730       my $min_max = $prefix eq 'from' ? 'min' : 'max';
 731       $query      = qq|SELECT ${min_max}(transdate)
 732                        FROM acc_trans ac
 733                        $dpt_join
 734                        WHERE (1 = 1)
 735                          $dpt_where
 736                          $project|;
 737       ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
 738     }
 739
 740     # get beginning balances
 741     $query =
 742       qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
 743           FROM acc_trans ac
 744           LEFT JOIN chart c ON (ac.chart_id = c.id)
 745           $dpt_join
 746           WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
 747             $dpt_where
 748             $project
 749           GROUP BY c.accno, c.category, c.description |;
 750
 751     $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
 752
 753     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 754
 755       if ($ref->{amount} != 0 || $form->{all_accounts}) {
 756         $trb{ $ref->{accno} }{description} = $ref->{description};
 757         $trb{ $ref->{accno} }{charttype}   = 'A';
 758         $trb{ $ref->{accno} }{beginning_balance} = $ref->{amount};
 759
 760         if ($ref->{amount} > 0) {
 761           $trb{ $ref->{accno} }{haben_eb}   = $ref->{amount};
 762         } else {
 763           $trb{ $ref->{accno} }{soll_eb}   = $ref->{amount} * -1;
 764         }
 765         $trb{ $ref->{accno} }{category}    = $ref->{category};
 766       }
 767
 768     }
 769     $sth->finish;
 770   }
 771
 772   # get headings
 773   $query =
 774     qq|SELECT c.accno, c.description, c.category
 775        FROM chart c
 776        WHERE c.charttype = 'H'
 777        ORDER by c.accno|;
 778
 779   $sth = prepare_execute_query($form, $dbh, $query);
 780
 781   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
 782     $trb{ $ref->{accno} }{description} = $ref->{description};
 783     $trb{ $ref->{accno} }{charttype}   = 'H';
 784     $trb{ $ref->{accno} }{category}    = $ref->{category};
 785
 786     push @headingaccounts, $ref->{accno};
 787   }
 788
 789   $sth->finish;
 790
 791   $where = " 1 = 1 ";
 792   my $saldowhere    = " 1 = 1 ";
 793   my $sumwhere      = " 1 = 1 ";
 794   my $subwhere      = '';
 795   my $sumsubwhere   = '';
 796   my $saldosubwhere = '';
 797   my $glsaldowhere  = '';
 798   my $glsubwhere    = '';
 799   my $glwhere       = '';
 800   my $glsumwhere    = '';
 801   my $tofrom;
 802   my ($fromdate, $todate);
 803
 804   if ($form->{fromdate} || $form->{todate}) {
 805     if ($form->{fromdate}) {
 806       $fromdate = conv_dateq($form->{fromdate});
 807       $tofrom        .= " AND (ac.transdate >= $fromdate)";
 808       $subwhere      .= " AND (ac.transdate >= $fromdate)";
 809       $sumsubwhere   .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
 810       $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate)))  ";
 811       $invwhere      .= " AND (a.transdate >= $fromdate)";
 812       $glsaldowhere  .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) ";
 813       $glwhere        = " AND (ac.transdate >= $fromdate)";
 814       $glsumwhere     = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
 815     }
 816     if ($form->{todate}) {
 817       $todate = conv_dateq($form->{todate});
 818       $tofrom        .= " AND (ac.transdate <= $todate)";
 819       $invwhere      .= " AND (a.transdate <= $todate)";
 820       $saldosubwhere .= " AND (ac.transdate <= $todate)";
 821       $sumsubwhere   .= " AND (ac.transdate <= $todate)";
 822       $subwhere      .= " AND (ac.transdate <= $todate)";
 823       $glwhere       .= " AND (ac.transdate <= $todate)";
 824       $glsumwhere    .= " AND (ac.transdate <= $todate) ";
 825       $glsaldowhere  .= " AND (ac.transdate <= $todate) ";
 826    }
 827   }
 828
 829   if ($form->{method} eq "cash") {
 830     $where .=
 831       qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;
 832     $saldowhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate))  AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;
 833
 834     $sumwhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;
 835   } else {
 836     $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
 837     $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
 838     $sumwhere .= $glsumwhere . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)";
 839   }
 840
 841   $query = qq|
 842        SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
 843        FROM acc_trans ac
 844        JOIN chart c ON (c.id = ac.chart_id)
 845        $dpt_join
 846        WHERE $where
 847          $dpt_where
 848          $project
 849        GROUP BY c.accno, c.description, c.category |;
 850
 851   if ($form->{project_id}) {
 852     $query .= qq|
 853       -- add project transactions from invoice
 854
 855       UNION ALL
 856
 857       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
 858       FROM invoice ac
 859       JOIN ar a ON (ac.trans_id = a.id)
 860       JOIN parts p ON (ac.parts_id = p.id)
 861       JOIN chart c ON (p.income_accno_id = c.id)
 862       $dpt_join
 863       WHERE $invwhere
 864         $dpt_where
 865         $project
 866       GROUP BY c.accno, c.description, c.category
 867
 868       UNION ALL
 869
 870       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
 871       FROM invoice ac
 872       JOIN ap a ON (ac.trans_id = a.id)
 873       JOIN parts p ON (ac.parts_id = p.id)
 874       JOIN chart c ON (p.expense_accno_id = c.id)
 875       $dpt_join
 876       WHERE $invwhere
 877         $dpt_where
 878         $project
 879       GROUP BY c.accno, c.description, c.category
 880       |;
 881     }
 882
 883   $query .= qq| ORDER BY accno|;
 884
 885   $sth = prepare_execute_query($form, $dbh, $query);
 886
 887   # calculate the debit and credit in the period
 888   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
 889     $trb{ $ref->{accno} }{description} = $ref->{description};
 890     $trb{ $ref->{accno} }{charttype}   = 'A';
 891     $trb{ $ref->{accno} }{category}    = $ref->{category};
 892     $trb{ $ref->{accno} }{amount} += $ref->{amount};
 893   }
 894   $sth->finish;
 895
 896   # prepare query for each account
 897   my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
 898
 899   $q_drcr =
 900     qq|SELECT
 901          (SELECT SUM(ac.amount) * -1
 902           FROM acc_trans ac
 903           JOIN chart c ON (c.id = ac.chart_id)
 904           $dpt_join
 905           WHERE $where
 906             $dpt_where
 907             $project
 908           AND (ac.amount < 0)
 909           AND (c.accno = ?)) AS debit,
 910
 911          (SELECT SUM(ac.amount)
 912           FROM acc_trans ac
 913           JOIN chart c ON (c.id = ac.chart_id)
 914           $dpt_join
 915           WHERE $where
 916             $dpt_where
 917             $project
 918           AND ac.amount > 0
 919           AND c.accno = ?) AS credit,
 920         (SELECT SUM(ac.amount)
 921          FROM acc_trans ac
 922          JOIN chart c ON (ac.chart_id = c.id)
 923          $dpt_join
 924          WHERE $saldowhere
 925            $dpt_where
 926            $project
 927          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
 928
 929         (SELECT SUM(ac.amount)
 930          FROM acc_trans ac
 931          JOIN chart c ON (ac.chart_id = c.id)
 932          $dpt_join
 933          WHERE $sumwhere
 934            $dpt_where
 935            $project
 936          AND amount > 0
 937          AND c.accno = ?) AS sum_credit,
 938
 939         (SELECT SUM(ac.amount)
 940          FROM acc_trans ac
 941          JOIN chart c ON (ac.chart_id = c.id)
 942          $dpt_join
 943          WHERE $sumwhere
 944            $dpt_where
 945            $project
 946          AND amount < 0
 947          AND c.accno = ?) AS sum_debit,
 948
 949         (SELECT max(ac.transdate) FROM acc_trans ac
 950         JOIN chart c ON (ac.chart_id = c.id)
 951         $dpt_join
 952         WHERE $where
 953           $dpt_where
 954           $project
 955         AND c.accno = ?) AS last_transaction
 956
 957
 958  |;
 959
 960   $drcr = prepare_query($form, $dbh, $q_drcr);
 961
 962   if ($form->{project_id}) {
 963     # prepare query for each account
 964     $q_project_drcr =
 965       qq|SELECT
 966           (SELECT SUM(ac.sellprice * ac.qty) * -1
 967            FROM invoice ac
 968            JOIN parts p ON (ac.parts_id = p.id)
 969            JOIN ap a ON (ac.trans_id = a.id)
 970            JOIN chart c ON (p.expense_accno_id = c.id)
 971            $dpt_join
 972            WHERE $invwhere
 973              $dpt_where
 974              $project
 975            AND c.accno = ?) AS debit,
 976
 977           (SELECT SUM(ac.sellprice * ac.qty)
 978            FROM invoice ac
 979            JOIN parts p ON (ac.parts_id = p.id)
 980            JOIN ar a ON (ac.trans_id = a.id)
 981            JOIN chart c ON (p.income_accno_id = c.id)
 982            $dpt_join
 983            WHERE $invwhere
 984              $dpt_where
 985              $project
 986            AND c.accno = ?) AS credit,
 987
 988         (SELECT SUM(ac.amount)
 989          FROM acc_trans ac
 990          JOIN chart c ON (ac.chart_id = c.id)
 991          $dpt_join
 992          WHERE $saldowhere
 993            $dpt_where
 994            $project
 995          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
 996
 997         (SELECT SUM(ac.amount)
 998          FROM acc_trans ac
 999          JOIN chart c ON (ac.chart_id = c.id)
1000          $dpt_join
1001          WHERE $sumwhere
1002            $dpt_where
1003            $project
1004          AND amount > 0
1005          AND c.accno = ?) AS sum_credit,
1006
1007         (SELECT SUM(ac.amount)
1008          FROM acc_trans ac
1009          JOIN chart c ON (ac.chart_id = c.id)
1010          $dpt_join
1011          WHERE $sumwhere
1012            $dpt_where
1013            $project
1014          AND amount < 0
1015          AND c.accno = ?) AS sum_debit,
1016
1017
1018         (SELECT max(ac.transdate) FROM acc_trans ac
1019         JOIN chart c ON (ac.chart_id = c.id)
1020         $dpt_join
1021         WHERE $where
1022           $dpt_where
1023           $project
1024         AND c.accno = ?) AS last_transaction
1025  |;
1026
1027     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1028   }
1029
1030
1031   my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
1032
1033   foreach my $accno (sort keys %trb) {
1034     $ref = {};
1035
1036     $ref->{accno} = $accno;
1037     map { $ref->{$_} = $trb{$accno}{$_} }
1038       qw(description category charttype amount soll_eb haben_eb beginning_balance);
1039
1040     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1041
1042     if ($trb{$accno}{charttype} eq 'A') {
1043
1044       # get DR/CR
1045       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1046
1047       ($debit, $credit, $saldo, $haben_saldo, $soll_saldo) = (0, 0, 0, 0, 0);
1048       my ($soll_kumuliert, $haben_kumuliert) = (0, 0);
1049       $last_transaction = "";
1050       while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1051         $ref->{debit}  += $debit;
1052         $ref->{credit} += $credit;
1053         if ($saldo >= 0) {
1054           $ref->{haben_saldo} += $saldo;
1055         } else {
1056           $ref->{soll_saldo} += $saldo * -1;
1057         }
1058         $ref->{last_transaction} = $last_transaction;
1059         $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1060         $ref->{haben_kumuliert} = $haben_kumuliert;
1061       }
1062       $drcr->finish;
1063
1064       if ($form->{project_id}) {
1065
1066         # get DR/CR
1067         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1068
1069         ($debit, $credit) = (0, 0);
1070         while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1071           $ref->{debit}  += $debit;
1072           $ref->{credit} += $credit;
1073           if ($saldo >= 0) {
1074             $ref->{haben_saldo} += $saldo;
1075           } else {
1076             $ref->{soll_saldo} += $saldo * -1;
1077           }
1078           $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1079           $ref->{haben_kumuliert} += $haben_kumuliert;
1080         }
1081         $project_drcr->finish;
1082       }
1083
1084       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1085       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1086
1087       if ($ref->{haben_saldo} != 0) {
1088         $ref->{haben_saldo}  = $ref->{haben_saldo} + $ref->{beginning_balance};
1089         if ($ref->{haben_saldo} < 0) {
1090           $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
1091           $ref->{haben_saldo} = 0;
1092         }
1093       } else {
1094         $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
1095         if ($ref->{soll_saldo} < 0) {
1096           $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2);
1097           $ref->{soll_saldo} = 0;
1098         }
1099      }
1100       $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1101       $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1102       $ref->{haben_kumuliert}  = $form->round_amount($ref->{haben_kumuliert},  2);
1103       $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1104     }
1105
1106     # add subtotal
1107     my @accno;
1108     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1109     $accno = pop @accno;
1110     if ($accno) {
1111       $trb{$accno}{debit}  += $ref->{debit};
1112       $trb{$accno}{credit} += $ref->{credit};
1113       $trb{$accno}{soll_saldo}  += $ref->{soll_saldo};
1114       $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1115       $trb{$accno}{soll_kumuliert}  += $ref->{soll_kumuliert};
1116       $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1117     }
1118
1119     push @{ $form->{TB} }, $ref;
1120
1121   }
1122
1123   $dbh->disconnect;
1124
1125   # debits and credits for headings
1126   foreach my $accno (@headingaccounts) {
1127     foreach $ref (@{ $form->{TB} }) {
1128       if ($accno eq $ref->{accno}) {
1129         $ref->{debit}           = $trb{$accno}{debit};
1130         $ref->{credit}          = $trb{$accno}{credit};
1131         $ref->{soll_saldo}      = $trb{$accno}{soll_saldo};
1132         $ref->{haben_saldo}     = $trb{$accno}{haben_saldo};
1133         $ref->{soll_kumuliert}  = $trb{$accno}{soll_kumuliert};
1134         $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
1135       }
1136     }
1137   }
1138
1139   $main::lxdebug->leave_sub();
1140 }
1141
1142 sub get_storno {
1143   $main::lxdebug->enter_sub();
1144   my ($self, $dbh, $form) = @_;
1145   my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1146   my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1147   my $sth =  $dbh->prepare($query);
1148   while(my $ref = $sth->fetchrow_hashref()) {
1149     $ref->{invnumer} =~ s/Storno zu //g;
1150     $form->{storno}{$ref->{invnumber}} = 1;
1151   }
1152   $main::lxdebug->leave_sub();
1153 }
1154
1155 sub aging {
1156   $main::lxdebug->enter_sub();
1157
1158   my ($self, $myconfig, $form) = @_;
1159
1160   # connect to database
1161   my $dbh     = $form->dbconnect($myconfig);
1162
1163   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
1164
1165   if ($form->{ct} eq "customer") {
1166     $invoice = "is";
1167     $arap = "ar";
1168     $buysell = "buy";
1169     $ct = "customer";
1170     $ml = -1;
1171   } else {
1172     $invoice = "ir";
1173     $arap = "ap";
1174     $buysell = "sell";
1175     $ct = "vendor";
1176     $ml = 1;
1177   }
1178   $ct_id = "${ct}_id";
1179
1180   $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1181   my $todate = conv_dateq($form->{todate});
1182   my $fromdate = conv_dateq($form->{fromdate});
1183
1184   my $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
1185
1186   my $where = " 1 = 1 ";
1187   my ($name, $null);
1188
1189   if ($form->{$ct_id}) {
1190     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1191   } elsif ($form->{ $form->{ct} }) {
1192     $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1193   }
1194
1195   my $dpt_join;
1196   my $where_dpt;
1197   if ($form->{department}) {
1198     my ($null, $department_id) = split /--/, $form->{department};
1199     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1200     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1201     $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1202   }
1203   my $review_of_aging_list;
1204   if ($form->{review_of_aging_list}) {
1205     if ($form->{review_of_aging_list} =~ m "-"){
1206       my @period = split(/-/, $form->{review_of_aging_list});
1207       $review_of_aging_list = " AND $period[0] < date_part('days', now() - duedate)
1208                                 AND date_part('days', now() - duedate)  < $period[1]";
1209     } else {
1210       $form->{review_of_aging_list} =~ s/[^0-9]//g;
1211       $review_of_aging_list = " AND $form->{review_of_aging_list} < date_part('days', now() - duedate)";
1212     }
1213   }
1214
1215   my $q_details = qq|
1216
1217     SELECT ${ct}.id AS ctid, ${ct}.name,
1218       street, zipcode, city, country, contact, email,
1219       phone as customerphone, fax as customerfax, ${ct}number,
1220       "invnumber", "transdate",
1221       (amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
1222       "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
1223       (SELECT $buysell
1224        FROM exchangerate
1225        WHERE (${arap}.curr = exchangerate.curr)
1226          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1227     FROM ${arap}, ${ct}
1228     WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
1229       AND NOT COALESCE (${arap}.storno, 'f')
1230       AND (${arap}.${ct}_id = ${ct}.id)
1231       $where_dpt
1232       AND (${ct}.id = ?)
1233       AND (transdate <= (date $todate) $fromwhere )
1234       $review_of_aging_list
1235     ORDER BY ctid, transdate, invnumber |;
1236
1237   my $sth_details = prepare_query($form, $dbh, $q_details);
1238
1239   # select outstanding vendors or customers, depends on $ct
1240   my $query =
1241     qq|SELECT DISTINCT ct.id, ct.name
1242        FROM $ct ct, $arap a
1243        $dpt_join
1244        WHERE $where
1245          AND (a.${ct_id} = ct.id)
1246          AND ((a.paid != a.amount) OR ((a.datepaid > $todate) AND (datepaid is NOT NULL)))
1247          AND (a.transdate <= $todate $fromwhere)
1248        ORDER BY ct.name|;
1249
1250   my $sth = prepare_execute_query($form, $dbh, $query);
1251
1252   $form->{AG} = [];
1253   # for each company that has some stuff outstanding
1254   while (my ($id) = $sth->fetchrow_array) {
1255     do_statement($form, $sth_details, $q_details, $id);
1256
1257     while (my $ref = $sth_details->fetchrow_hashref("NAME_lc")) {
1258       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1259       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1260       push @{ $form->{AG} }, $ref;
1261     }
1262
1263     $sth_details->finish;
1264
1265   }
1266
1267   $sth->finish;
1268
1269   # disconnect
1270   $dbh->disconnect;
1271
1272   $main::lxdebug->leave_sub();
1273 }
1274
1275 sub get_customer {
1276   $main::lxdebug->enter_sub();
1277
1278   my ($self, $myconfig, $form) = @_;
1279
1280   # connect to database
1281   my $dbh = $form->dbconnect($myconfig);
1282
1283   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1284
1285   my $query =
1286     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1287        FROM $ct ct
1288        WHERE ct.id = ?|;
1289   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1290     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1291   $dbh->disconnect;
1292
1293   $main::lxdebug->leave_sub();
1294 }
1295
1296 sub get_taxaccounts {
1297   $main::lxdebug->enter_sub();
1298
1299   my ($self, $myconfig, $form) = @_;
1300
1301   # connect to database
1302   my $dbh = $form->dbconnect($myconfig);
1303
1304   # get tax accounts
1305   my $query =
1306     qq|SELECT c.accno, c.description, t.rate
1307        FROM chart c, tax t
1308        WHERE (c.link LIKE '%CT_tax%') AND (c.id = t.chart_id)
1309        ORDER BY c.accno|;
1310   $form->{taxaccounts} = selectall_hashref_quert($form, $dbh, $query);
1311
1312   $dbh->disconnect;
1313
1314   $main::lxdebug->leave_sub();
1315 }
1316
1317 sub tax_report {
1318   $main::lxdebug->enter_sub();
1319
1320   my ($self, $myconfig, $form) = @_;
1321
1322   # connect to database
1323   my $dbh = $form->dbconnect($myconfig);
1324
1325   my ($null, $department_id) = split /--/, $form->{department};
1326
1327   # build WHERE
1328   my $where = "1 = 1";
1329
1330   if ($department_id) {
1331     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1332   }
1333
1334   my ($accno, $rate);
1335
1336   if ($form->{accno}) {
1337     $accno = $form->{accno};
1338     $rate  = $form->{"$form->{accno}_rate"};
1339     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1340   }
1341   $rate *= 1;
1342
1343   my ($table, $ARAP);
1344
1345   if ($form->{db} eq 'ar') {
1346     $table = "customer";
1347     $ARAP  = "AR";
1348   } else {
1349     $table = "vendor";
1350     $ARAP  = "AP";
1351   }
1352
1353   my $arap = lc($ARAP);
1354
1355   my $transdate = "a.transdate";
1356
1357   if ($form->{method} eq 'cash') {
1358     $transdate = "a.datepaid";
1359
1360     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1361
1362     $where .= qq|
1363       AND ac.trans_id IN
1364         (
1365           SELECT trans_id
1366           FROM acc_trans a
1367           JOIN chart c ON (a.chart_id = c.id)
1368           WHERE (link LIKE '%${ARAP}_paid%')
1369           AND (transdate <= $todate)
1370         )
1371       |;
1372   }
1373
1374   # if there are any dates construct a where
1375   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1376   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1377
1378   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1379
1380   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1381   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1382
1383   my $query = '';
1384   if ($form->{report} !~ /nontaxable/) {
1385     $query =
1386       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1387           ac.amount * $ml AS tax
1388          FROM acc_trans ac
1389          JOIN ${arap} a ON (a.id = ac.trans_id)
1390          JOIN chart ch ON (ch.id = ac.chart_id)
1391          JOIN $table n ON (n.id = a.${table}_id)
1392          WHERE
1393            $where
1394            $accno
1395            AND (a.invoice = '0')
1396
1397          UNION
1398
1399          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1400            i.sellprice * i.qty * $rate * $ml AS tax
1401          FROM acc_trans ac
1402          JOIN ${arap} a ON (a.id = ac.trans_id)
1403          JOIN chart ch ON (ch.id = ac.chart_id)
1404          JOIN $table n ON (n.id = a.${table}_id)
1405          JOIN ${table}tax t ON (t.${table}_id = n.id)
1406          JOIN invoice i ON (i.trans_id = a.id)
1407          JOIN partstax p ON (p.parts_id = i.parts_id)
1408          WHERE
1409            $where
1410            $accno
1411            AND (a.invoice = '1')
1412          ORDER BY $sortorder|;
1413   } else {
1414     # only gather up non-taxable transactions
1415     $query =
1416       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount
1417          FROM acc_trans ac
1418          JOIN ${arap} a ON (a.id = ac.trans_id)
1419          JOIN $table n ON (n.id = a.${table}_id)
1420          WHERE
1421            $where
1422            AND (a.invoice = '0')
1423            AND (a.netamount = a.amount)
1424
1425          UNION
1426
1427          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount
1428          FROM acc_trans ac
1429          JOIN ${arap} a ON (a.id = ac.trans_id)
1430          JOIN $table n ON (n.id = a.${table}_id)
1431          JOIN invoice i ON (i.trans_id = a.id)
1432          WHERE
1433            $where
1434            AND (a.invoice = '1')
1435            AND (
1436              a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
1437              OR
1438              i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id))
1439            )
1440          GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1441          ORDER by $sortorder|;
1442   }
1443
1444   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1445
1446   $dbh->disconnect;
1447
1448   $main::lxdebug->leave_sub();
1449 }
1450
1451 sub paymentaccounts {
1452   $main::lxdebug->enter_sub();
1453
1454   my ($self, $myconfig, $form) = @_;
1455
1456   # connect to database, turn AutoCommit off
1457   my $dbh = $form->dbconnect_noauto($myconfig);
1458
1459   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1460
1461   # get A(R|P)_paid accounts
1462   my $query =
1463     qq|SELECT accno, description
1464        FROM chart
1465        WHERE link LIKE '%${ARAP}_paid%'|;
1466   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1467
1468   $dbh->disconnect;
1469
1470   $main::lxdebug->leave_sub();
1471 }
1472
1473 sub payments {
1474   $main::lxdebug->enter_sub();
1475
1476   my ($self, $myconfig, $form) = @_;
1477
1478   # connect to database, turn AutoCommit off
1479   my $dbh = $form->dbconnect_noauto($myconfig);
1480
1481   my $ml = 1;
1482   my $arap;
1483   my $table;
1484   if ($form->{db} eq 'ar') {
1485     $table = 'customer';
1486     $ml = -1;
1487     $arap = 'ar';
1488   } else {
1489     $table = 'vendor';
1490     $arap = 'ap';
1491   }
1492
1493   my ($query, $sth);
1494   my $dpt_join;
1495   my $where;
1496
1497   if ($form->{department_id}) {
1498     $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
1499     $where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1500   }
1501
1502   if ($form->{fromdate}) {
1503     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1504   }
1505   if ($form->{todate}) {
1506     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1507   }
1508   if (!$form->{fx_transaction}) {
1509     $where .= " AND ac.fx_transaction = '0'";
1510   }
1511
1512   my $invnumber;
1513   my $reference;
1514   if ($form->{reference}) {
1515     $reference = $dbh->quote('%' . $form->{reference} . '%');
1516     $invnumber = " AND (a.invnumber LIKE $reference)";
1517     $reference = " AND (g.reference LIKE $reference)";
1518   }
1519   if ($form->{source}) {
1520     $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1521   }
1522   if ($form->{memo}) {
1523     $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1524   }
1525
1526   my %sort_columns =  (
1527     'transdate'    => [ qw(transdate lower_invnumber lower_name) ],
1528     'invnumber'    => [ qw(lower_invnumber lower_name transdate) ],
1529     'name'         => [ qw(lower_name transdate)                 ],
1530     'source'       => [ qw(lower_source)                         ],
1531     'memo'         => [ qw(lower_memo)                           ],
1532     );
1533   my %lowered_columns =  (
1534     'invnumber'       => { 'gl' => 'g.reference',   'arap' => 'a.invnumber', },
1535     'memo'            => { 'gl' => 'ac.memo',       'arap' => 'ac.memo',     },
1536     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
1537     'name'            => { 'gl' => 'g.description', 'arap' => 'c.name',      },
1538     );
1539
1540   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1541   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1542   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1543
1544
1545   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1546   foreach my $spec (@{ $sort_columns{$sortkey} }) {
1547     next if ($spec !~ m/^lower_(.*)$/);
1548
1549     my $column = $1;
1550     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1551   }
1552
1553   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1554   $sth = prepare_query($form, $dbh, $query);
1555
1556   my $q_details =
1557       qq|SELECT c.name, a.invnumber, a.ordnumber,
1558            ac.transdate, ac.amount * $ml AS paid, ac.source,
1559            a.invoice, a.id, ac.memo, '${arap}' AS module
1560            $columns_for_sorting{arap}
1561          FROM acc_trans ac
1562          JOIN $arap a ON (ac.trans_id = a.id)
1563          JOIN $table c ON (c.id = a.${table}_id)
1564          $dpt_join
1565          WHERE (ac.chart_id = ?)
1566            $where
1567            $invnumber
1568
1569          UNION
1570
1571          SELECT g.description, g.reference, NULL AS ordnumber,
1572            ac.transdate, ac.amount * $ml AS paid, ac.source,
1573            '0' as invoice, g.id, ac.memo, 'gl' AS module
1574            $columns_for_sorting{gl}
1575          FROM acc_trans ac
1576          JOIN gl g ON (g.id = ac.trans_id)
1577          $dpt_join
1578          WHERE (ac.chart_id = ?)
1579            $where
1580            $reference
1581            AND (ac.amount * $ml) > 0
1582
1583          ORDER BY $sortorder|;
1584   my $sth_details = prepare_query($form, $dbh, $q_details);
1585
1586   $form->{PR} = [];
1587
1588   # cycle through each id
1589   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1590     do_statement($form, $sth, $query, $accno);
1591     my $ref = $sth->fetchrow_hashref();
1592     push(@{ $form->{PR} }, $ref);
1593     $sth->finish();
1594
1595     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1596
1597     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1598     while (my $pr = $sth_details->fetchrow_hashref()) {
1599       push(@{ $form->{ $ref->{id} } }, $pr);
1600     }
1601     $sth_details->finish();
1602   }
1603
1604   $dbh->disconnect;
1605
1606   $main::lxdebug->leave_sub();
1607 }
1608
1609 sub bwa {
1610   $main::lxdebug->enter_sub();
1611
1612   my ($self, $myconfig, $form) = @_;
1613
1614   # connect to database
1615   my $dbh = $form->dbconnect($myconfig);
1616
1617   my $last_period = 0;
1618   my $category;
1619   my @categories  =
1620     qw(1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40);
1621
1622   $form->{decimalplaces} *= 1;
1623
1624   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1625
1626   # if there are any compare dates
1627   my $year;
1628   if ($form->{fromdate} || $form->{todate}) {
1629     $last_period = 1;
1630     if ($form->{fromdate}) {
1631       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1632       $year = $1;
1633     } else {
1634       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1635       $year = $1;
1636     }
1637     my $kummfromdate = $form->{comparefromdate};
1638     my $kummtodate   = $form->{comparetodate};
1639     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1640   }
1641
1642   my @periods        = qw(jetzt kumm);
1643   my @gesamtleistung = qw(1 3);
1644   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
1645   my @ergebnisse     =
1646     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1647
1648   foreach my $key (@periods) {
1649     $form->{ "$key" . "gesamtleistung" } = 0;
1650     $form->{ "$key" . "gesamtkosten" }   = 0;
1651
1652     foreach $category (@categories) {
1653
1654       if (defined($form->{$category}{$key})) {
1655         $form->{"$key$category"} =
1656           $form->format_amount($myconfig,
1657                                $form->round_amount($form->{$category}{$key}, 2
1658                                ),
1659                                $form->{decimalplaces},
1660                                '0');
1661       }
1662     }
1663     foreach my $item (@gesamtleistung) {
1664       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1665     }
1666     $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1667
1668     foreach my $item (@gesamtkosten) {
1669       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1670     }
1671     $form->{ "$key" . "rohertrag" } =
1672       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1673     $form->{ "$key" . "betriebrohertrag" } =
1674       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1675     $form->{ "$key" . "betriebsergebnis" } =
1676       $form->{ "$key" . "betriebrohertrag" } -
1677       $form->{ "$key" . "gesamtkosten" };
1678     $form->{ "$key" . "neutraleraufwand" } =
1679       $form->{30}{$key} + $form->{31}{$key};
1680     $form->{ "$key" . "neutralertrag" } =
1681       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1682     $form->{ "$key" . "ergebnisvorsteuern" } =
1683       $form->{ "$key" . "betriebsergebnis" } -
1684       $form->{ "$key" . "neutraleraufwand" } +
1685       $form->{ "$key" . "neutralertrag" };
1686     $form->{ "$key" . "ergebnis" } =
1687       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1688
1689     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1690       foreach $category (@categories) {
1691         if (defined($form->{$category}{$key})) {
1692           $form->{ "$key" . "gl" . "$category" } =
1693             $form->format_amount(
1694                                $myconfig,
1695                                $form->round_amount(
1696                                  ($form->{$category}{$key} /
1697                                     $form->{ "$key" . "gesamtleistung" } * 100
1698                                  ),
1699                                  $form->{decimalplaces}
1700                                ),
1701                                $form->{decimalplaces},
1702                                '0');
1703         }
1704       }
1705       foreach my $item (@ergebnisse) {
1706         $form->{ "$key" . "gl" . "$item" } =
1707           $form->format_amount($myconfig,
1708                                $form->round_amount(
1709                                  ( $form->{ "$key" . "$item" } /
1710                                      $form->{ "$key" . "gesamtleistung" } * 100
1711                                  ),
1712                                  $form->{decimalplaces}
1713                                ),
1714                                $form->{decimalplaces},
1715                                '0');
1716       }
1717     }
1718
1719     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1720       foreach $category (@categories) {
1721         if (defined($form->{$category}{$key})) {
1722           $form->{ "$key" . "gk" . "$category" } =
1723             $form->format_amount($myconfig,
1724                                  $form->round_amount(
1725                                    ($form->{$category}{$key} /
1726                                       $form->{ "$key" . "gesamtkosten" } * 100
1727                                    ),
1728                                    $form->{decimalplaces}
1729                                  ),
1730                                  $form->{decimalplaces},
1731                                  '0');
1732         }
1733       }
1734       foreach my $item (@ergebnisse) {
1735         $form->{ "$key" . "gk" . "$item" } =
1736           $form->format_amount($myconfig,
1737                                $form->round_amount(
1738                                    ($form->{ "$key" . "$item" } /
1739                                       $form->{ "$key" . "gesamtkosten" } * 100
1740                                    ),
1741                                    $form->{decimalplaces}
1742                                ),
1743                                $form->{decimalplaces},
1744                                '0');
1745       }
1746     }
1747
1748     if ($form->{10}{$key} > 0) {
1749       foreach $category (@categories) {
1750         if (defined($form->{$category}{$key})) {
1751           $form->{ "$key" . "pk" . "$category" } =
1752             $form->format_amount(
1753                         $myconfig,
1754                         $form->round_amount(
1755                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1756                           $form->{decimalplaces}
1757                         ),
1758                         $form->{decimalplaces},
1759                         '0');
1760         }
1761       }
1762       foreach my $item (@ergebnisse) {
1763         $form->{ "$key" . "pk" . "$item" } =
1764           $form->format_amount($myconfig,
1765                                $form->round_amount(
1766                                                 ($form->{ "$key" . "$item" } /
1767                                                    $form->{10}{$key} * 100
1768                                                 ),
1769                                                 $form->{decimalplaces}
1770                                ),
1771                                $form->{decimalplaces},
1772                                '0');
1773       }
1774     }
1775
1776     if ($form->{4}{$key} > 0) {
1777       foreach $category (@categories) {
1778         if (defined($form->{$category}{$key})) {
1779           $form->{ "$key" . "auf" . "$category" } =
1780             $form->format_amount(
1781                          $myconfig,
1782                          $form->round_amount(
1783                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1784                            $form->{decimalplaces}
1785                          ),
1786                          $form->{decimalplaces},
1787                          '0');
1788         }
1789       }
1790       foreach my $item (@ergebnisse) {
1791         $form->{ "$key" . "auf" . "$item" } =
1792           $form->format_amount($myconfig,
1793                                $form->round_amount(
1794                                                 ($form->{ "$key" . "$item" } /
1795                                                    $form->{4}{$key} * 100
1796                                                 ),
1797                                                 $form->{decimalplaces}
1798                                ),
1799                                $form->{decimalplaces},
1800                                '0');
1801       }
1802     }
1803
1804     foreach my $item (@ergebnisse) {
1805       $form->{ "$key" . "$item" } =
1806         $form->format_amount($myconfig,
1807                              $form->round_amount($form->{ "$key" . "$item" },
1808                                                  $form->{decimalplaces}
1809                              ),
1810                              $form->{decimalplaces},
1811                              '0');
1812     }
1813
1814   }
1815   $dbh->disconnect;
1816
1817   $main::lxdebug->leave_sub();
1818 }
1819
1820 sub ustva {
1821   $main::lxdebug->enter_sub();
1822
1823   my ($self, $myconfig, $form) = @_;
1824
1825   # connect to database
1826   my $dbh = $form->dbconnect($myconfig);
1827
1828   my $last_period     = 0;
1829   my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
1830     96 66 43 45 53 62 65 67);
1831   my @categories_euro = qw(48 51 86 91 97 93 94);
1832   $form->{decimalplaces} *= 1;
1833
1834   foreach my $item (@categories_cent) {
1835     $form->{"$item"} = 0;
1836   }
1837   foreach my $item (@categories_euro) {
1838     $form->{"$item"} = 0;
1839   }
1840
1841   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_ustva");
1842
1843   #   foreach $item (@categories_cent) {
1844   #     if ($form->{$item}{"jetzt"} > 0) {
1845   #       $form->{$item} = $form->{$item}{"jetzt"};
1846   #       delete $form->{$item}{"jetzt"};
1847   #     }
1848   #   }
1849   #   foreach $item (@categories_euro) {
1850   #     if ($form->{$item}{"jetzt"} > 0) {
1851   #       $form->{$item} = $form->{$item}{"jetzt"};
1852   #       delete $form->{$item}{"jetzt"};
1853   #     }  foreach $item (@categories_cent) {
1854   #     if ($form->{$item}{"jetzt"} > 0) {
1855   #       $form->{$item} = $form->{$item}{"jetzt"};
1856   #       delete $form->{$item}{"jetzt"};
1857   #     }
1858   #   }
1859   #   foreach $item (@categories_euro) {
1860   #     if ($form->{$item}{"jetzt"} > 0) {
1861   #       $form->{$item} = $form->{$item}{"jetzt"};
1862   #       delete $form->{$item}{"jetzt"};
1863   #     }
1864   #   }
1865   #
1866   #    }
1867
1868   #
1869   # Berechnung der USTVA Formularfelder
1870   #
1871   $form->{"51r"} = $form->{"511"};
1872   $form->{"86r"} = $form->{"861"};
1873   $form->{"97r"} = $form->{"971"};
1874   $form->{"93r"} = $form->{"931"};
1875
1876   #$form->{"96"}  = $form->{"94"} * 0.16;
1877   $form->{"43"} =
1878     $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
1879     $form->{"96"};
1880   $form->{"45"} = $form->{"43"};
1881   $form->{"53"} = $form->{"43"};
1882   $form->{"62"} = $form->{"43"} - $form->{"66"};
1883   $form->{"65"} = $form->{"43"} - $form->{"66"};
1884   $form->{"67"} = $form->{"43"} - $form->{"66"};
1885
1886   foreach my $item (@categories_cent) {
1887     $form->{$item} =
1888       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
1889                            2, '0');
1890   }
1891
1892   foreach my $item (@categories_euro) {
1893     $form->{$item} =
1894       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
1895                            0, '0');
1896   }
1897
1898   $dbh->disconnect;
1899
1900   $main::lxdebug->leave_sub();
1901 }
1902
1903 sub income_statement {
1904   $main::lxdebug->enter_sub();
1905
1906   my ($self, $myconfig, $form) = @_;
1907
1908   # connect to database
1909   my $dbh = $form->dbconnect($myconfig);
1910
1911   my $last_period          = 0;
1912   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1913   my @categories_ausgaben  =
1914     qw(8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31);
1915
1916   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1917
1918   $form->{decimalplaces} *= 1;
1919
1920
1921
1922   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1923                   $form, "pos_eur");
1924
1925
1926   foreach my $item (@categories_einnahmen) {
1927     $form->{"eur${item}"} =
1928       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1929     $form->{"sumeura"} += $form->{$item};
1930   }
1931   foreach my $item (@categories_ausgaben) {
1932     $form->{"eur${item}"} =
1933       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1934     $form->{"sumeurb"} += $form->{$item};
1935   }
1936
1937   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1938
1939   foreach my $item (@ergebnisse) {
1940     $form->{$item} =
1941       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1942   }
1943   $main::lxdebug->leave_sub();
1944 }
1945 1;