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