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 = $form->dbconnect_noauto($myconfig);
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   $dbh->disconnect;
1480
1481   $main::lxdebug->leave_sub();
1482 }
1483
1484 sub payments {
1485   $main::lxdebug->enter_sub();
1486
1487   my ($self, $myconfig, $form) = @_;
1488
1489   # connect to database, turn AutoCommit off
1490   my $dbh = $form->dbconnect_noauto($myconfig);
1491
1492   my $ml = 1;
1493   my $arap;
1494   my $table;
1495   if ($form->{db} eq 'ar') {
1496     $table = 'customer';
1497     $ml = -1;
1498     $arap = 'ar';
1499   } else {
1500     $table = 'vendor';
1501     $arap = 'ap';
1502   }
1503
1504   my ($query, $sth);
1505   my $where;
1506
1507   if ($form->{department_id}) {
1508     $where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1509   }
1510
1511   if ($form->{fromdate}) {
1512     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1513   }
1514   if ($form->{todate}) {
1515     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1516   }
1517   if (!$form->{fx_transaction}) {
1518     $where .= " AND ac.fx_transaction = '0'";
1519   }
1520
1521   my $invnumber;
1522   my $reference;
1523   if ($form->{reference}) {
1524     $reference = $dbh->quote(like($form->{reference}));
1525     $invnumber = " AND (a.invnumber LIKE $reference)";
1526     $reference = " AND (a.reference LIKE $reference)";
1527   }
1528   if ($form->{source}) {
1529     $where .= " AND (ac.source ILIKE " . $dbh->quote(like($form->{source})) . ") ";
1530   }
1531   if ($form->{memo}) {
1532     $where .= " AND (ac.memo ILIKE " . $dbh->quote(like($form->{memo})) . ") ";
1533   }
1534
1535   my %sort_columns =  (
1536     'transdate'    => [ qw(transdate lower_invnumber lower_name) ],
1537     'invnumber'    => [ qw(lower_invnumber lower_name transdate) ],
1538     'name'         => [ qw(lower_name transdate)                 ],
1539     'source'       => [ qw(lower_source)                         ],
1540     'memo'         => [ qw(lower_memo)                           ],
1541     );
1542   my %lowered_columns =  (
1543     'invnumber'       => { 'gl' => 'a.reference',   'arap' => 'a.invnumber', },
1544     'memo'            => { 'gl' => 'ac.memo',       'arap' => 'ac.memo',     },
1545     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
1546     'name'            => { 'gl' => 'a.description', 'arap' => 'c.name',      },
1547     );
1548
1549   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
1550   my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
1551   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
1552
1553
1554   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
1555   foreach my $spec (@{ $sort_columns{$sortkey} }) {
1556     next if ($spec !~ m/^lower_(.*)$/);
1557
1558     my $column = $1;
1559     map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
1560   }
1561
1562   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1563   $sth = prepare_query($form, $dbh, $query);
1564
1565   my $q_details =
1566       qq|SELECT c.name, a.invnumber, a.ordnumber,
1567            ac.transdate, ac.amount * $ml AS paid, ac.source,
1568            a.invoice, a.id, ac.memo, '${arap}' AS module
1569            $columns_for_sorting{arap}
1570          FROM acc_trans ac
1571          JOIN $arap a ON (ac.trans_id = a.id)
1572          JOIN $table c ON (c.id = a.${table}_id)
1573          WHERE (ac.chart_id = ?)
1574            $where
1575            $invnumber
1576
1577          UNION
1578
1579          SELECT a.description, a.reference, NULL AS ordnumber,
1580            ac.transdate, ac.amount * $ml AS paid, ac.source,
1581            '0' as invoice, a.id, ac.memo, 'gl' AS module
1582            $columns_for_sorting{gl}
1583          FROM acc_trans ac
1584          JOIN gl a ON (a.id = ac.trans_id)
1585          WHERE (ac.chart_id = ?)
1586            $where
1587            $reference
1588            AND (ac.amount * $ml) > 0
1589
1590          ORDER BY $sortorder|;
1591   my $sth_details = prepare_query($form, $dbh, $q_details);
1592
1593   $form->{PR} = [];
1594
1595   # cycle through each id
1596   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1597     do_statement($form, $sth, $query, $accno);
1598     my $ref = $sth->fetchrow_hashref();
1599     push(@{ $form->{PR} }, $ref);
1600     $sth->finish();
1601
1602     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1603
1604     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1605     while (my $pr = $sth_details->fetchrow_hashref()) {
1606       push(@{ $form->{ $ref->{id} } }, $pr);
1607     }
1608     $sth_details->finish();
1609   }
1610
1611   $dbh->disconnect;
1612
1613   $main::lxdebug->leave_sub();
1614 }
1615
1616 sub bwa {
1617   $main::lxdebug->enter_sub();
1618
1619   my ($self, $myconfig, $form) = @_;
1620
1621   # connect to database
1622   my $dbh = $form->dbconnect($myconfig);
1623
1624   my $last_period = 0;
1625   my $category;
1626   my @categories  =
1627     qw(1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40);
1628
1629   $form->{decimalplaces} *= 1;
1630
1631   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1632
1633   # if there are any compare dates
1634   my $year;
1635   if ($form->{fromdate} || $form->{todate}) {
1636     $last_period = 1;
1637     if ($form->{fromdate}) {
1638       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1639       $year = $1;
1640     } else {
1641       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1642       $year = $1;
1643     }
1644     my $kummfromdate = $form->{comparefromdate};
1645     my $kummtodate   = $form->{comparetodate};
1646     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1647   }
1648
1649   my @periods        = qw(jetzt kumm);
1650   my @gesamtleistung = qw(1 3);
1651   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
1652   my @ergebnisse     =
1653     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1654
1655   foreach my $key (@periods) {
1656     $form->{ "$key" . "gesamtleistung" } = 0;
1657     $form->{ "$key" . "gesamtkosten" }   = 0;
1658
1659     foreach $category (@categories) {
1660
1661       if (defined($form->{$category}{$key})) {
1662         $form->{"$key$category"} =
1663           $form->format_amount($myconfig,
1664                                $form->round_amount($form->{$category}{$key}, 2
1665                                ),
1666                                $form->{decimalplaces},
1667                                '0');
1668       }
1669     }
1670     foreach my $item (@gesamtleistung) {
1671       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1672     }
1673     $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key};
1674
1675     foreach my $item (@gesamtkosten) {
1676       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1677     }
1678     $form->{ "$key" . "rohertrag" } =
1679       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1680     $form->{ "$key" . "betriebrohertrag" } =
1681       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1682     $form->{ "$key" . "betriebsergebnis" } =
1683       $form->{ "$key" . "betriebrohertrag" } -
1684       $form->{ "$key" . "gesamtkosten" };
1685     $form->{ "$key" . "neutraleraufwand" } =
1686       $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
1687     $form->{ "$key" . "neutralerertrag" } =
1688       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1689     $form->{ "$key" . "ergebnisvorsteuern" } =
1690       $form->{ "$key" . "betriebsergebnis" } -
1691       $form->{ "$key" . "neutraleraufwand" } +
1692       $form->{ "$key" . "neutralerertrag" };
1693     $form->{ "$key" . "ergebnis" } =
1694       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1695
1696     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1697       foreach $category (@categories) {
1698         if (defined($form->{$category}{$key})) {
1699           $form->{ "$key" . "gl" . "$category" } =
1700             $form->format_amount(
1701                                $myconfig,
1702                                $form->round_amount(
1703                                  ($form->{$category}{$key} /
1704                                     $form->{ "$key" . "gesamtleistung" } * 100
1705                                  ),
1706                                  $form->{decimalplaces}
1707                                ),
1708                                $form->{decimalplaces},
1709                                '0');
1710         }
1711       }
1712       foreach my $item (@ergebnisse) {
1713         $form->{ "$key" . "gl" . "$item" } =
1714           $form->format_amount($myconfig,
1715                                $form->round_amount(
1716                                  ( $form->{ "$key" . "$item" } /
1717                                      $form->{ "$key" . "gesamtleistung" } * 100
1718                                  ),
1719                                  $form->{decimalplaces}
1720                                ),
1721                                $form->{decimalplaces},
1722                                '0');
1723       }
1724     }
1725
1726     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1727       foreach $category (@categories) {
1728         if (defined($form->{$category}{$key})) {
1729           $form->{ "$key" . "gk" . "$category" } =
1730             $form->format_amount($myconfig,
1731                                  $form->round_amount(
1732                                    ($form->{$category}{$key} /
1733                                       $form->{ "$key" . "gesamtkosten" } * 100
1734                                    ),
1735                                    $form->{decimalplaces}
1736                                  ),
1737                                  $form->{decimalplaces},
1738                                  '0');
1739         }
1740       }
1741       foreach my $item (@ergebnisse) {
1742         $form->{ "$key" . "gk" . "$item" } =
1743           $form->format_amount($myconfig,
1744                                $form->round_amount(
1745                                    ($form->{ "$key" . "$item" } /
1746                                       $form->{ "$key" . "gesamtkosten" } * 100
1747                                    ),
1748                                    $form->{decimalplaces}
1749                                ),
1750                                $form->{decimalplaces},
1751                                '0');
1752       }
1753     }
1754
1755     if ($form->{10}{$key} > 0) {
1756       foreach $category (@categories) {
1757         if (defined($form->{$category}{$key})) {
1758           $form->{ "$key" . "pk" . "$category" } =
1759             $form->format_amount(
1760                         $myconfig,
1761                         $form->round_amount(
1762                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1763                           $form->{decimalplaces}
1764                         ),
1765                         $form->{decimalplaces},
1766                         '0');
1767         }
1768       }
1769       foreach my $item (@ergebnisse) {
1770         $form->{ "$key" . "pk" . "$item" } =
1771           $form->format_amount($myconfig,
1772                                $form->round_amount(
1773                                                 ($form->{ "$key" . "$item" } /
1774                                                    $form->{10}{$key} * 100
1775                                                 ),
1776                                                 $form->{decimalplaces}
1777                                ),
1778                                $form->{decimalplaces},
1779                                '0');
1780       }
1781     }
1782
1783     if ($form->{4}{$key} > 0) {
1784       foreach $category (@categories) {
1785         if (defined($form->{$category}{$key})) {
1786           $form->{ "$key" . "auf" . "$category" } =
1787             $form->format_amount(
1788                          $myconfig,
1789                          $form->round_amount(
1790                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1791                            $form->{decimalplaces}
1792                          ),
1793                          $form->{decimalplaces},
1794                          '0');
1795         }
1796       }
1797       foreach my $item (@ergebnisse) {
1798         $form->{ "$key" . "auf" . "$item" } =
1799           $form->format_amount($myconfig,
1800                                $form->round_amount(
1801                                                 ($form->{ "$key" . "$item" } /
1802                                                    $form->{4}{$key} * 100
1803                                                 ),
1804                                                 $form->{decimalplaces}
1805                                ),
1806                                $form->{decimalplaces},
1807                                '0');
1808       }
1809     }
1810
1811     foreach my $item (@ergebnisse) {
1812       $form->{ "$key" . "$item" } =
1813         $form->format_amount($myconfig,
1814                              $form->round_amount($form->{ "$key" . "$item" },
1815                                                  $form->{decimalplaces}
1816                              ),
1817                              $form->{decimalplaces},
1818                              '0');
1819     }
1820
1821   }
1822   $dbh->disconnect;
1823
1824   $main::lxdebug->leave_sub();
1825 }
1826
1827 sub income_statement {
1828   $main::lxdebug->enter_sub();
1829
1830   my ($self, $myconfig, $form) = @_;
1831
1832   # connect to database
1833   my $dbh = $form->dbconnect($myconfig);
1834
1835   my $last_period          = 0;
1836   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1837   my @categories_ausgaben  =
1838     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);
1839
1840   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1841
1842   $form->{decimalplaces} *= 1;
1843
1844
1845
1846   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1847                   $form, "pos_eur");
1848
1849
1850   foreach my $item (@categories_einnahmen) {
1851     $form->{"eur${item}"} =
1852       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1853     $form->{"sumeura"} += $form->{$item};
1854   }
1855   foreach my $item (@categories_ausgaben) {
1856     $form->{"eur${item}"} =
1857       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1858     $form->{"sumeurb"} += $form->{$item};
1859   }
1860
1861   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1862
1863   foreach my $item (@ergebnisse) {
1864     $form->{$item} =
1865       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
1866   }
1867   $main::lxdebug->leave_sub();
1868 }
1869
1870 sub erfolgsrechnung {
1871   $main::lxdebug->enter_sub();
1872
1873   my ($self, $myconfig, $form) = @_;
1874   $form->{company} = $::instance_conf->get_company;
1875   $form->{address} = $::instance_conf->get_address;
1876   $form->{fromdate} = DateTime->new(year => 2000, month => 1, day => 1)->to_kivitendo unless $form->{fromdate};
1877   $form->{todate} = $form->current_date(%{$myconfig}) unless $form->{todate};
1878
1879   my %categories = (I => "ERTRAG", E => "AUFWAND");
1880   my $fromdate = conv_dateq($form->{fromdate});
1881   my $todate = conv_dateq($form->{todate});
1882
1883   $form->{total} = 0;
1884
1885   foreach my $category ('I', 'E') {
1886     my %category = (
1887       name => $categories{$category},
1888       total => 0,
1889       accounts => get_accounts_ch($category),
1890     );
1891     foreach my $account (@{$category{accounts}}) {
1892       $account->{total} += ($account->{category} eq $category ? 1 : -1) * get_total_ch($account->{id}, $fromdate, $todate);
1893       $category{total} += $account->{total};
1894       $account->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $account->{total}), 2);
1895     }
1896     $form->{total} += $category{total};
1897     $category{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $category{total}), 2);
1898     push(@{$form->{categories}}, \%category);
1899   }
1900   $form->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $form->{total}), 2);
1901
1902   $main::lxdebug->leave_sub();
1903   return {};
1904 }
1905
1906 sub get_accounts_ch {
1907   $main::lxdebug->enter_sub();
1908
1909   my ($category) = @_;
1910   my ($inclusion);
1911
1912   if ($category eq 'I') {
1913     $inclusion = "AND pos_er = NULL OR pos_er > '0' AND pos_er <= '5'";
1914   } elsif ($category eq 'E') {
1915     $inclusion = "AND pos_er = NULL OR pos_er >= '6' AND pos_er < '100'";
1916   } else {
1917     $inclusion = "";
1918   }
1919
1920   my $query = qq|
1921     SELECT id, accno, description, category
1922     FROM chart
1923     WHERE category = ? $inclusion
1924     ORDER BY accno
1925   |;
1926   my $accounts = _query($query, $category);
1927
1928   $main::lxdebug->leave_sub();
1929   return $accounts;
1930 }
1931
1932 sub get_total_ch {
1933   $main::lxdebug->enter_sub();
1934
1935   my ($chart_id, $fromdate, $todate) = @_;
1936   my $total = 0;
1937   my $query = qq|
1938     SELECT SUM(amount)
1939     FROM acc_trans
1940     WHERE chart_id = ?
1941       AND transdate >= ?
1942       AND transdate <= ?
1943   |;
1944   $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
1945
1946   $main::lxdebug->leave_sub();
1947   return $total;
1948 }
1949
1950 sub _query {return selectall_hashref_query($::form, $::form->get_standard_dbh, @_);}
1951
1952 1;