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