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