SL/RP.pm

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