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