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