SL/RP.pm

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