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