SL/RP.pm

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