SL/RP.pm

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