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, $hotfix_query);
 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
 927     $hotfix_query = qq|SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
 928                        FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE 1 = 1 AND (ac.transdate <= $fromdate)
 929                         AND (ac.transdate >= (SELECT date_trunc('YEAR', | . $fromdate . qq|::date)))
 930                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)
 931                        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)
 932                        AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL))
 933                        GROUP BY c.accno, c.description, c.category ORDER BY accno|;
 934   }
 935
 936   $query = qq|
 937        SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
 938        FROM acc_trans ac
 939        JOIN chart c ON (c.id = ac.chart_id)
 940        $customer_join
 941        WHERE $where
 942          $dpt_where_without_arapgl
 943          $project
 944        GROUP BY c.accno, c.description, c.category |;
 945
 946   if ($form->{project_id}) {
 947     $query .= qq|
 948       -- add project transactions from invoice
 949
 950       UNION ALL
 951
 952       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
 953       FROM invoice ac
 954       JOIN ar a ON (ac.trans_id = a.id)
 955       JOIN parts p ON (ac.parts_id = p.id)
 956       JOIN chart c ON (p.income_accno_id = c.id)
 957       WHERE $invwhere
 958         $dpt_where
 959         $customer_where
 960         $project
 961       GROUP BY c.accno, c.description, c.category
 962
 963       UNION ALL
 964
 965       SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
 966       FROM invoice ac
 967       JOIN ap a ON (ac.trans_id = a.id)
 968       JOIN parts p ON (ac.parts_id = p.id)
 969       JOIN chart c ON (p.expense_accno_id = c.id)
 970       WHERE $invwhere
 971         $dpt_where
 972         $customer_no_union
 973         $project
 974       GROUP BY c.accno, c.description, c.category
 975       |;
 976     }
 977
 978   $query .= qq| ORDER BY accno|;
 979
 980   $sth = prepare_execute_query($form, $dbh, $query);
 981
 982   # calculate the debit and credit in the period
 983   while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
 984     $trb{ $ref->{accno} }{description} = $ref->{description};
 985     $trb{ $ref->{accno} }{charttype}   = 'A';
 986     $trb{ $ref->{accno} }{category}    = $ref->{category};
 987     $trb{ $ref->{accno} }{amount} += $ref->{amount};
 988   }
 989   $sth->finish;
 990
 991   if (! $form->{method} eq "cash") {
 992     $sth = prepare_execute_query($form, $dbh, $hotfix_query);
 993     while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
 994       $trb{ $ref->{accno} }{description} = $ref->{description};
 995       $trb{ $ref->{accno} }{charttype}   = 'A';
 996       $trb{ $ref->{accno} }{category}    = $ref->{category};
 997       $trb{ $ref->{accno} }{amount} += $ref->{amount};
 998     }
 999     $sth->finish;
1000   }
1001
1002   # prepare query for each account
1003   my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
1004
1005   $q_drcr =
1006     qq|SELECT
1007          (SELECT SUM(ac.amount) * -1
1008           FROM acc_trans ac
1009           JOIN chart c ON (c.id = ac.chart_id)
1010           $customer_join
1011           WHERE $where
1012             $dpt_where_without_arapgl
1013             $dpt_where
1014             $customer_where
1015             $project
1016           AND (ac.amount < 0)
1017           AND (c.accno = ?)) AS debit,
1018
1019          (SELECT SUM(ac.amount)
1020           FROM acc_trans ac
1021           JOIN chart c ON (c.id = ac.chart_id)
1022           $customer_join
1023           WHERE $where
1024             $dpt_where_without_arapgl
1025             $dpt_where
1026             $customer_where
1027             $project
1028           AND ac.amount > 0
1029           AND c.accno = ?) AS credit,
1030         (SELECT SUM(ac.amount)
1031          FROM acc_trans ac
1032          JOIN chart c ON (ac.chart_id = c.id)
1033          $customer_join
1034          WHERE $saldowhere
1035            $dpt_where_without_arapgl
1036            $dpt_where
1037            $customer_where
1038            $project
1039          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
1040
1041         (SELECT SUM(ac.amount)
1042          FROM acc_trans ac
1043          JOIN chart c ON (ac.chart_id = c.id)
1044          $customer_join
1045          WHERE $sumwhere
1046            $dpt_where_without_arapgl
1047            $dpt_where
1048            $customer_where
1049            $project
1050          AND ac.amount > 0
1051          AND c.accno = ?) AS sum_credit,
1052
1053         (SELECT SUM(ac.amount)
1054          FROM acc_trans ac
1055          JOIN chart c ON (ac.chart_id = c.id)
1056          $customer_join
1057          WHERE $sumwhere
1058            $dpt_where_without_arapgl
1059            $dpt_where
1060            $customer_where
1061            $project
1062          AND ac.amount < 0
1063          AND c.accno = ?) AS sum_debit,
1064
1065         (SELECT max(ac.transdate) FROM acc_trans ac
1066         JOIN chart c ON (ac.chart_id = c.id)
1067         $customer_join
1068         WHERE $where
1069           $dpt_where_without_arapgl
1070           $dpt_where
1071           $customer_where
1072           $project
1073         AND c.accno = ?) AS last_transaction
1074
1075
1076  |;
1077
1078   $drcr = prepare_query($form, $dbh, $q_drcr);
1079
1080   if ($form->{project_id}) {
1081     # prepare query for each account
1082     $q_project_drcr =
1083       qq|SELECT
1084           (SELECT SUM(ac.sellprice * ac.qty) * -1
1085            FROM invoice ac
1086            JOIN parts p ON (ac.parts_id = p.id)
1087            JOIN ap a ON (ac.trans_id = a.id)
1088            JOIN chart c ON (p.expense_accno_id = c.id)
1089            WHERE $invwhere
1090              $dpt_where
1091              $customer_no_union
1092              $project
1093            AND c.accno = ?) AS debit,
1094
1095           (SELECT SUM(ac.sellprice * ac.qty)
1096            FROM invoice ac
1097            JOIN parts p ON (ac.parts_id = p.id)
1098            JOIN ar a ON (ac.trans_id = a.id)
1099            JOIN chart c ON (p.income_accno_id = c.id)
1100            WHERE $invwhere
1101              $dpt_where
1102              $customer_where
1103              $project
1104            AND c.accno = ?) AS credit,
1105
1106         (SELECT SUM(ac.amount)
1107          FROM acc_trans ac
1108          JOIN chart c ON (ac.chart_id = c.id)
1109          $customer_join
1110          WHERE $saldowhere
1111            $dpt_where_without_arapgl
1112            $dpt_where
1113            $customer_where
1114            $project
1115          AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
1116
1117         (SELECT SUM(ac.amount)
1118          FROM acc_trans ac
1119          JOIN chart c ON (ac.chart_id = c.id)
1120          $customer_join
1121          WHERE $sumwhere
1122            $dpt_where_without_arapgl
1123            $dpt_where
1124            $customer_where
1125            $project
1126          AND ac.amount > 0
1127          AND c.accno = ?) AS sum_credit,
1128
1129         (SELECT SUM(ac.amount)
1130          FROM acc_trans ac
1131          JOIN chart c ON (ac.chart_id = c.id)
1132          $customer_join
1133          WHERE $sumwhere
1134            $dpt_where
1135            $dpt_where_without_arapgl
1136            $customer_where
1137            $project
1138          AND ac.amount < 0
1139          AND c.accno = ?) AS sum_debit,
1140
1141
1142         (SELECT max(ac.transdate) FROM acc_trans ac
1143         JOIN chart c ON (ac.chart_id = c.id)
1144         $customer_join
1145         WHERE $where
1146           $dpt_where_without_arapgl
1147           $dpt_where
1148           $customer_where
1149           $project
1150         AND c.accno = ?) AS last_transaction
1151  |;
1152
1153     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1154   }
1155
1156
1157   my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
1158
1159   foreach my $accno (sort keys %trb) {
1160     $ref = {};
1161
1162     $ref->{accno} = $accno;
1163     map { $ref->{$_} = $trb{$accno}{$_} }
1164       qw(description category charttype amount soll_eb haben_eb beginning_balance);
1165
1166     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1167
1168     if ($trb{$accno}{charttype} eq 'A') {
1169
1170       # get DR/CR
1171       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1172
1173       ($debit, $credit, $saldo, $haben_saldo, $soll_saldo) = (0, 0, 0, 0, 0);
1174       my ($soll_kumuliert, $haben_kumuliert) = (0, 0);
1175       $last_transaction = "";
1176       while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1177         $ref->{debit}  += $debit;
1178         $ref->{credit} += $credit;
1179         if ($saldo >= 0) {
1180           $ref->{haben_saldo} += $saldo;
1181         } else {
1182           $ref->{soll_saldo} += $saldo * -1;
1183         }
1184         $ref->{last_transaction} = $last_transaction;
1185         $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1186         $ref->{haben_kumuliert} = $haben_kumuliert;
1187       }
1188       $drcr->finish;
1189
1190       if ($form->{project_id}) {
1191
1192         # get DR/CR
1193         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1194
1195         ($debit, $credit) = (0, 0);
1196         while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1197           $ref->{debit}  += $debit;
1198           $ref->{credit} += $credit;
1199           if ($saldo >= 0) {
1200             $ref->{haben_saldo} += $saldo;
1201           } else {
1202             $ref->{soll_saldo} += $saldo * -1;
1203           }
1204           $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1205           $ref->{haben_kumuliert} += $haben_kumuliert;
1206         }
1207         $project_drcr->finish;
1208       }
1209
1210       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1211       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1212
1213       if ($ref->{haben_saldo} != 0) {
1214         $ref->{haben_saldo}  = $ref->{haben_saldo} + $ref->{beginning_balance};
1215         if ($ref->{haben_saldo} < 0) {
1216           $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2);
1217           $ref->{haben_saldo} = 0;
1218         }
1219       } else {
1220         $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance};
1221         if ($ref->{soll_saldo} < 0) {
1222           $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2);
1223           $ref->{soll_saldo} = 0;
1224         }
1225      }
1226       $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1227       $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1228       $ref->{haben_kumuliert}  = $form->round_amount($ref->{haben_kumuliert},  2);
1229       $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1230     }
1231
1232     # add subtotal
1233     my @accno;
1234     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1235     $accno = pop @accno;
1236     if ($accno) {
1237       $trb{$accno}{debit}  += $ref->{debit};
1238       $trb{$accno}{credit} += $ref->{credit};
1239       $trb{$accno}{soll_saldo}  += $ref->{soll_saldo};
1240       $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1241       $trb{$accno}{soll_kumuliert}  += $ref->{soll_kumuliert};
1242       $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1243     }
1244
1245     push @{ $form->{TB} }, $ref;
1246
1247   }
1248
1249   $dbh->disconnect;
1250
1251   # debits and credits for headings
1252   foreach my $accno (@headingaccounts) {
1253     foreach $ref (@{ $form->{TB} }) {
1254       if ($accno eq $ref->{accno}) {
1255         $ref->{debit}           = $trb{$accno}{debit};
1256         $ref->{credit}          = $trb{$accno}{credit};
1257         $ref->{soll_saldo}      = $trb{$accno}{soll_saldo};
1258         $ref->{haben_saldo}     = $trb{$accno}{haben_saldo};
1259         $ref->{soll_kumuliert}  = $trb{$accno}{soll_kumuliert};
1260         $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert};
1261       }
1262     }
1263   }
1264
1265   $main::lxdebug->leave_sub();
1266 }
1267
1268 sub get_storno {
1269   $main::lxdebug->enter_sub();
1270   my ($self, $dbh, $form) = @_;
1271   my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1272   my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1273   my $sth =  $dbh->prepare($query);
1274   while(my $ref = $sth->fetchrow_hashref()) {
1275     $ref->{invnumer} =~ s/Storno zu //g;
1276     $form->{storno}{$ref->{invnumber}} = 1;
1277   }
1278   $main::lxdebug->leave_sub();
1279 }
1280
1281 sub aging {
1282   $main::lxdebug->enter_sub();
1283
1284   my ($self, $myconfig, $form) = @_;
1285
1286   # connect to database
1287   my $dbh     = $form->dbconnect($myconfig);
1288
1289   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
1290
1291   # falls customer ziehen wir die offene forderungsliste
1292   # anderfalls für die lieferanten die offenen verbindlichkeitne
1293   if ($form->{ct} eq "customer") {
1294     $invoice = "is";
1295     $arap = "ar";
1296     $buysell = "buy";
1297     $ct = "customer";
1298     $ml = -1;
1299   } else {
1300     $invoice = "ir";
1301     $arap = "ap";
1302     $buysell = "sell";
1303     $ct = "vendor";
1304     $ml = 1;
1305   }
1306   $ct_id = "${ct}_id";
1307
1308   # erweiterung um einen freien zeitraum oder einen stichtag
1309   # mit entsprechender altersstrukturliste (s.a. Bug 1842)
1310   # eine neue variable an der oberfläche eingeführt, somit ist
1311   # todate == freier zeitrau und fordate == stichtag
1312   # duedate_where == nur fällige rechnungen anzeigen
1313
1314   my ($review_of_aging_list, $todate, $fromdate, $fromwhere, $fordate,
1315       $duedate_where);
1316
1317   if ($form->{reporttype} eq 'custom') {  # altersstrukturliste, nur fällige
1318
1319     # explizit rausschmeissen was man für diesen bericht nicht braucht
1320     delete $form->{fromdate};
1321     delete $form->{todate};
1322
1323     # an der oberfläche ist das tagesaktuelle datum vorausgewählt
1324     # falls es dennoch per Benutzereingabe gelöscht wird, lieber wieder vorbelegen
1325     # ferner muss für die spätere DB-Abfrage muss todate gesetzt sein.
1326     $form->{fordate}  = $form->current_date($myconfig) unless ($form->{fordate});
1327     $fordate          = conv_dateq($form->{fordate});
1328     $todate           = $fordate;
1329
1330     if ($form->{review_of_aging_list}) { # falls die liste leer ist, alles anzeigen
1331       if ($form->{review_of_aging_list} =~ m "-") {             # ..  periode von bis
1332         my @period = split(/-/, $form->{review_of_aging_list}); # ... von periode bis periode
1333         $review_of_aging_list = " AND $period[0] <  (date $fordate) - duedate
1334                                   AND (date $fordate) - duedate  < $period[1]";
1335       } else {
1336         $form->{review_of_aging_list} =~ s/[^0-9]//g;   # größer 120 das substitute ist nur für das '>' zeichen
1337         $review_of_aging_list = " AND $form->{review_of_aging_list} < (date $fordate) - duedate";
1338       }
1339     }
1340     $duedate_where = " AND (date $fordate) - duedate >= 0 ";
1341   } else {  # freier zeitraum, nur rechnungsdatum und OHNE review_of_aging_list
1342     $form->{todate}  = $form->current_date($myconfig) unless ($form->{todate});
1343     $todate = conv_dateq($form->{todate});
1344     $fromdate = conv_dateq($form->{fromdate});
1345     $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
1346   }
1347   my $where = " 1 = 1 ";
1348   my ($name, $null);
1349
1350   if ($form->{$ct_id}) {
1351     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1352   } elsif ($form->{ $form->{ct} }) {
1353     $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1354   }
1355
1356   my $dpt_join;
1357   my $where_dpt;
1358   if ($form->{department}) {
1359     my ($null, $department_id) = split /--/, $form->{department};
1360     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1361     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1362     $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1363   }
1364  my $q_details = qq|
1365
1366     SELECT ${ct}.id AS ctid, ${ct}.name,
1367       street, zipcode, city, country, contact, email,
1368       phone as customerphone, fax as customerfax, ${ct}number,
1369       "invnumber", "transdate",
1370       (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",
1371       "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
1372       (SELECT $buysell
1373        FROM exchangerate
1374        WHERE (${arap}.currency_id = exchangerate.currency_id)
1375          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1376     FROM ${arap}, ${ct}
1377     WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
1378       AND NOT COALESCE (${arap}.storno, 'f')
1379       AND (${arap}.${ct}_id = ${ct}.id)
1380       $where_dpt
1381       AND (${ct}.id = ?)
1382       AND (transdate <= (date $todate) $fromwhere )
1383       $review_of_aging_list
1384       $duedate_where
1385     ORDER BY ctid, transdate, invnumber |;
1386
1387   my $sth_details = prepare_query($form, $dbh, $q_details);
1388
1389   # select outstanding vendors or customers, depends on $ct
1390   my $query =
1391     qq|SELECT DISTINCT ct.id, ct.name
1392        FROM $ct ct, $arap a
1393        $dpt_join
1394        WHERE $where
1395          AND (a.${ct_id} = ct.id)
1396          AND ((a.paid != a.amount) OR ((a.datepaid > $todate) AND (datepaid is NOT NULL)))
1397          AND (a.transdate <= $todate $fromwhere)
1398        ORDER BY ct.name|;
1399
1400   my $sth = prepare_execute_query($form, $dbh, $query);
1401
1402   $form->{AG} = [];
1403   # for each company that has some stuff outstanding
1404   while (my ($id) = $sth->fetchrow_array) {
1405     do_statement($form, $sth_details, $q_details, $id);
1406
1407     while (my $ref = $sth_details->fetchrow_hashref("NAME_lc")) {
1408       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1409       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1410       push @{ $form->{AG} }, $ref;
1411     }
1412
1413     $sth_details->finish;
1414
1415   }
1416
1417   $sth->finish;
1418
1419   # disconnect
1420   $dbh->disconnect;
1421
1422   $main::lxdebug->leave_sub();
1423 }
1424
1425 sub get_customer {
1426   $main::lxdebug->enter_sub();
1427
1428   my ($self, $myconfig, $form) = @_;
1429
1430   # connect to database
1431   my $dbh = $form->dbconnect($myconfig);
1432
1433   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1434
1435   my $query =
1436     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1437        FROM $ct ct
1438        WHERE ct.id = ?|;
1439   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1440     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1441   $dbh->disconnect;
1442
1443   $main::lxdebug->leave_sub();
1444 }
1445
1446 sub tax_report {
1447   $main::lxdebug->enter_sub();
1448
1449   my ($self, $myconfig, $form) = @_;
1450
1451   # connect to database
1452   my $dbh = $form->dbconnect($myconfig);
1453
1454   my ($null, $department_id) = split /--/, $form->{department};
1455
1456   # build WHERE
1457   my $where = "1 = 1";
1458
1459   if ($department_id) {
1460     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1461   }
1462
1463   my ($accno, $rate);
1464
1465   if ($form->{accno}) {
1466     $accno = $form->{accno};
1467     $rate  = $form->{"$form->{accno}_rate"};
1468     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1469   }
1470   $rate *= 1;
1471
1472   my ($table, $ARAP);
1473
1474   if ($form->{db} eq 'ar') {
1475     $table = "customer";
1476     $ARAP  = "AR";
1477   } else {
1478     $table = "vendor";
1479     $ARAP  = "AP";
1480   }
1481
1482   my $arap = lc($ARAP);
1483
1484   my $transdate = "a.transdate";
1485
1486   if ($form->{method} eq 'cash') {
1487     $transdate = "a.datepaid";
1488
1489     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1490
1491     $where .= qq|
1492       AND ac.trans_id IN
1493         (
1494           SELECT trans_id
1495           FROM acc_trans a
1496           WHERE (a.chart_link LIKE '%${ARAP}_paid%')
1497           AND (transdate <= $todate)
1498         )
1499       |;
1500   }
1501
1502   # if there are any dates construct a where
1503   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1504   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1505
1506   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1507
1508   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1509   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1510
1511   my $query =
1512       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1513           ac.amount * $ml AS tax
1514          FROM acc_trans ac
1515          JOIN ${arap} a ON (a.id = ac.trans_id)
1516          JOIN chart ch ON (ch.id = ac.chart_id)
1517          JOIN $table n ON (n.id = a.${table}_id)
1518          WHERE
1519            $where
1520            $accno
1521            AND (a.invoice = '0')
1522
1523          UNION
1524
1525          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1526            i.sellprice * i.qty * $rate * $ml AS tax
1527          FROM acc_trans ac
1528          JOIN ${arap} a ON (a.id = ac.trans_id)
1529          JOIN chart ch ON (ch.id = ac.chart_id)
1530          JOIN $table n ON (n.id = a.${table}_id)
1531          JOIN ${table}tax t ON (t.${table}_id = n.id)
1532          JOIN invoice i ON (i.trans_id = a.id)
1533          WHERE
1534            $where
1535            $accno
1536            AND (a.invoice = '1')
1537          ORDER BY $sortorder|;
1538
1539   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1540
1541   $dbh->disconnect;
1542
1543   $main::lxdebug->leave_sub();
1544 }
1545
1546 sub paymentaccounts {
1547   $main::lxdebug->enter_sub();
1548
1549   my ($self, $myconfig, $form) = @_;
1550
1551   # connect to database, turn AutoCommit off
1552   my $dbh = $form->dbconnect_noauto($myconfig);
1553
1554   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1555
1556   # get A(R|P)_paid accounts
1557   my $query =
1558     qq|SELECT accno, description
1559        FROM chart
1560        WHERE link LIKE '%${ARAP}_paid%'|;
1561   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1562
1563   $dbh->disconnect;
1564
1565   $main::lxdebug->leave_sub();
1566 }
1567
1568 sub payments {
1569   $main::lxdebug->enter_sub();
1570
1571   my ($self, $myconfig, $form) = @_;
1572
1573   # connect to database, turn AutoCommit off
1574   my $dbh = $form->dbconnect_noauto($myconfig);
1575
1576   my $ml = 1;
1577   my $arap;
1578   my $table;
1579   if ($form->{db} eq 'ar') {
1580     $table = 'customer';
1581     $ml = -1;
1582     $arap = 'ar';
1583   } else {
1584     $table = 'vendor';
1585     $arap = 'ap';
1586   }
1587
1588   my ($query, $sth);
1589   my $where;
1590
1591   if ($form->{department_id}) {
1592     $where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1593   }
1594
1595   if ($form->{fromdate}) {
1596     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1597   }
1598   if ($form->{todate}) {
1599     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1600   }
1601   if (!$form->{fx_transaction}) {
1602     $where .= " AND ac.fx_transaction = '0'";
1603   }
1604
1605   my $invnumber;
1606   my $reference;
1607   if ($form->{reference}) {
1608     $reference = $dbh->quote('%' . $form->{reference} . '%');
1609     $invnumber = " AND (a.invnumber LIKE $reference)";
1610     $reference = " AND (a.reference LIKE $reference)";
1611   }
1612   if ($form->{source}) {
1613     $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1614   }
1615   if ($form->{memo}) {
1616     $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1617   }
1618
1619   my %sort_columns =  (
1620     'transdate'    => [ qw(transdate lower_invnumber lower_name) ],
1621     'invnumber'    => [ qw(lower_invnumber lower_name transdate) ],
1622     'name'         => [ qw(lower_name transdate)                 ],
1623     'source'       => [ qw(lower_source)                         ],
1624     'memo'         => [ qw(lower_memo)                           ],
1625     );
1626   my %lowered_columns =  (
1627     'invnumber'       => { 'gl' => 'a.reference',   'arap' => 'a.invnumber', },
1628     'memo'            => { 'gl' => 'ac.memo',       'arap' => 'ac.memo',     },
1629     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
1630     'name'            => { 'gl' => 'a.description', 'arap' => 'c.name',      },
1631     );
1632
1633   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1634   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1635   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1636
1637
1638   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1639   foreach my $spec (@{ $sort_columns{$sortkey} }) {
1640     next if ($spec !~ m/^lower_(.*)$/);
1641
1642     my $column = $1;
1643     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1644   }
1645
1646   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1647   $sth = prepare_query($form, $dbh, $query);
1648
1649   my $q_details =
1650       qq|SELECT c.name, a.invnumber, a.ordnumber,
1651            ac.transdate, ac.amount * $ml AS paid, ac.source,
1652            a.invoice, a.id, ac.memo, '${arap}' AS module
1653            $columns_for_sorting{arap}
1654          FROM acc_trans ac
1655          JOIN $arap a ON (ac.trans_id = a.id)
1656          JOIN $table c ON (c.id = a.${table}_id)
1657          WHERE (ac.chart_id = ?)
1658            $where
1659            $invnumber
1660
1661          UNION
1662
1663          SELECT a.description, a.reference, NULL AS ordnumber,
1664            ac.transdate, ac.amount * $ml AS paid, ac.source,
1665            '0' as invoice, a.id, ac.memo, 'gl' AS module
1666            $columns_for_sorting{gl}
1667          FROM acc_trans ac
1668          JOIN gl a ON (a.id = ac.trans_id)
1669          WHERE (ac.chart_id = ?)
1670            $where
1671            $reference
1672            AND (ac.amount * $ml) > 0
1673
1674          ORDER BY $sortorder|;
1675   my $sth_details = prepare_query($form, $dbh, $q_details);
1676
1677   $form->{PR} = [];
1678
1679   # cycle through each id
1680   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1681     do_statement($form, $sth, $query, $accno);
1682     my $ref = $sth->fetchrow_hashref();
1683     push(@{ $form->{PR} }, $ref);
1684     $sth->finish();
1685
1686     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1687
1688     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1689     while (my $pr = $sth_details->fetchrow_hashref()) {
1690       push(@{ $form->{ $ref->{id} } }, $pr);
1691     }
1692     $sth_details->finish();
1693   }
1694
1695   $dbh->disconnect;
1696
1697   $main::lxdebug->leave_sub();
1698 }
1699
1700 sub bwa {
1701   $main::lxdebug->enter_sub();
1702
1703   my ($self, $myconfig, $form) = @_;
1704
1705   # connect to database
1706   my $dbh = $form->dbconnect($myconfig);
1707
1708   my $last_period = 0;
1709   my $category;
1710   my @categories  =
1711     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);
1712
1713   $form->{decimalplaces} *= 1;
1714
1715   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1716
1717   # if there are any compare dates
1718   my $year;
1719   if ($form->{fromdate} || $form->{todate}) {
1720     $last_period = 1;
1721     if ($form->{fromdate}) {
1722       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1723       $year = $1;
1724     } else {
1725       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1726       $year = $1;
1727     }
1728     my $kummfromdate = $form->{comparefromdate};
1729     my $kummtodate   = $form->{comparetodate};
1730     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1731   }
1732
1733   my @periods        = qw(jetzt kumm);
1734   my @gesamtleistung = qw(1 3);
1735   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
1736   my @ergebnisse     =
1737     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1738
1739   foreach my $key (@periods) {
1740     $form->{ "$key" . "gesamtleistung" } = 0;
1741     $form->{ "$key" . "gesamtkosten" }   = 0;
1742
1743     foreach $category (@categories) {
1744
1745       if (defined($form->{$category}{$key})) {
1746         $form->{"$key$category"} =
1747           $form->format_amount($myconfig,
1748                                $form->round_amount($form->{$category}{$key}, 2
1749                                ),
1750                                $form->{decimalplaces},
1751                                '0');
1752       }
1753     }
1754     foreach my $item (@gesamtleistung) {
1755       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1756     }
1757     $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1758
1759     foreach my $item (@gesamtkosten) {
1760       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1761     }
1762     $form->{ "$key" . "rohertrag" } =
1763       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1764     $form->{ "$key" . "betriebrohertrag" } =
1765       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1766     $form->{ "$key" . "betriebsergebnis" } =
1767       $form->{ "$key" . "betriebrohertrag" } -
1768       $form->{ "$key" . "gesamtkosten" };
1769     $form->{ "$key" . "neutraleraufwand" } =
1770       $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
1771     $form->{ "$key" . "neutralerertrag" } =
1772       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1773     $form->{ "$key" . "ergebnisvorsteuern" } =
1774       $form->{ "$key" . "betriebsergebnis" } -
1775       $form->{ "$key" . "neutraleraufwand" } +
1776       $form->{ "$key" . "neutralerertrag" };
1777     $form->{ "$key" . "ergebnis" } =
1778       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1779
1780     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1781       foreach $category (@categories) {
1782         if (defined($form->{$category}{$key})) {
1783           $form->{ "$key" . "gl" . "$category" } =
1784             $form->format_amount(
1785                                $myconfig,
1786                                $form->round_amount(
1787                                  ($form->{$category}{$key} /
1788                                     $form->{ "$key" . "gesamtleistung" } * 100
1789                                  ),
1790                                  $form->{decimalplaces}
1791                                ),
1792                                $form->{decimalplaces},
1793                                '0');
1794         }
1795       }
1796       foreach my $item (@ergebnisse) {
1797         $form->{ "$key" . "gl" . "$item" } =
1798           $form->format_amount($myconfig,
1799                                $form->round_amount(
1800                                  ( $form->{ "$key" . "$item" } /
1801                                      $form->{ "$key" . "gesamtleistung" } * 100
1802                                  ),
1803                                  $form->{decimalplaces}
1804                                ),
1805                                $form->{decimalplaces},
1806                                '0');
1807       }
1808     }
1809
1810     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1811       foreach $category (@categories) {
1812         if (defined($form->{$category}{$key})) {
1813           $form->{ "$key" . "gk" . "$category" } =
1814             $form->format_amount($myconfig,
1815                                  $form->round_amount(
1816                                    ($form->{$category}{$key} /
1817                                       $form->{ "$key" . "gesamtkosten" } * 100
1818                                    ),
1819                                    $form->{decimalplaces}
1820                                  ),
1821                                  $form->{decimalplaces},
1822                                  '0');
1823         }
1824       }
1825       foreach my $item (@ergebnisse) {
1826         $form->{ "$key" . "gk" . "$item" } =
1827           $form->format_amount($myconfig,
1828                                $form->round_amount(
1829                                    ($form->{ "$key" . "$item" } /
1830                                       $form->{ "$key" . "gesamtkosten" } * 100
1831                                    ),
1832                                    $form->{decimalplaces}
1833                                ),
1834                                $form->{decimalplaces},
1835                                '0');
1836       }
1837     }
1838
1839     if ($form->{10}{$key} > 0) {
1840       foreach $category (@categories) {
1841         if (defined($form->{$category}{$key})) {
1842           $form->{ "$key" . "pk" . "$category" } =
1843             $form->format_amount(
1844                         $myconfig,
1845                         $form->round_amount(
1846                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1847                           $form->{decimalplaces}
1848                         ),
1849                         $form->{decimalplaces},
1850                         '0');
1851         }
1852       }
1853       foreach my $item (@ergebnisse) {
1854         $form->{ "$key" . "pk" . "$item" } =
1855           $form->format_amount($myconfig,
1856                                $form->round_amount(
1857                                                 ($form->{ "$key" . "$item" } /
1858                                                    $form->{10}{$key} * 100
1859                                                 ),
1860                                                 $form->{decimalplaces}
1861                                ),
1862                                $form->{decimalplaces},
1863                                '0');
1864       }
1865     }
1866
1867     if ($form->{4}{$key} > 0) {
1868       foreach $category (@categories) {
1869         if (defined($form->{$category}{$key})) {
1870           $form->{ "$key" . "auf" . "$category" } =
1871             $form->format_amount(
1872                          $myconfig,
1873                          $form->round_amount(
1874                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1875                            $form->{decimalplaces}
1876                          ),
1877                          $form->{decimalplaces},
1878                          '0');
1879         }
1880       }
1881       foreach my $item (@ergebnisse) {
1882         $form->{ "$key" . "auf" . "$item" } =
1883           $form->format_amount($myconfig,
1884                                $form->round_amount(
1885                                                 ($form->{ "$key" . "$item" } /
1886                                                    $form->{4}{$key} * 100
1887                                                 ),
1888                                                 $form->{decimalplaces}
1889                                ),
1890                                $form->{decimalplaces},
1891                                '0');
1892       }
1893     }
1894
1895     foreach my $item (@ergebnisse) {
1896       $form->{ "$key" . "$item" } =
1897         $form->format_amount($myconfig,
1898                              $form->round_amount($form->{ "$key" . "$item" },
1899                                                  $form->{decimalplaces}
1900                              ),
1901                              $form->{decimalplaces},
1902                              '0');
1903     }
1904
1905   }
1906   $dbh->disconnect;
1907
1908   $main::lxdebug->leave_sub();
1909 }
1910
1911 sub income_statement {
1912   $main::lxdebug->enter_sub();
1913
1914   my ($self, $myconfig, $form) = @_;
1915
1916   # connect to database
1917   my $dbh = $form->dbconnect($myconfig);
1918
1919   my $last_period          = 0;
1920   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1921   my @categories_ausgaben  =
1922     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);
1923
1924   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1925
1926   $form->{decimalplaces} *= 1;
1927
1928
1929
1930   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1931                   $form, "pos_eur");
1932
1933
1934   foreach my $item (@categories_einnahmen) {
1935     $form->{"eur${item}"} =
1936       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1937     $form->{"sumeura"} += $form->{$item};
1938   }
1939   foreach my $item (@categories_ausgaben) {
1940     $form->{"eur${item}"} =
1941       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1942     $form->{"sumeurb"} += $form->{$item};
1943   }
1944
1945   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1946
1947   foreach my $item (@ergebnisse) {
1948     $form->{$item} =
1949       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1950   }
1951   $main::lxdebug->leave_sub();
1952 }
1953 1;