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