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