SL/RP.pm

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