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