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
 984   $drcr = prepare_query($form, $dbh, $q_drcr);
 985
 986   if ($form->{project_id}) {
 987     # prepare query for each account
 988     $q_project_drcr =
 989       qq|SELECT
 990           (SELECT SUM(ac.sellprice * ac.qty) * -1
 991            FROM invoice ac
 992            JOIN parts p ON (ac.parts_id = p.id)
 993            JOIN ap a ON (ac.trans_id = a.id)
 994            JOIN chart c ON (p.expense_accno_id = c.id)
 995            $dpt_join
 996            WHERE $invwhere
 997              $dpt_where
 998              $project
 999            AND c.accno = ?) AS debit,
1000
1001           (SELECT SUM(ac.sellprice * ac.qty)
1002            FROM invoice ac
1003            JOIN parts p ON (ac.parts_id = p.id)
1004            JOIN ar a ON (ac.trans_id = a.id)
1005            JOIN chart c ON (p.income_accno_id = c.id)
1006            $dpt_join
1007            WHERE $invwhere
1008              $dpt_where
1009              $project
1010            AND c.accno = ?) AS credit |;
1011
1012     $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1013   }
1014
1015   my ($debit, $credit);
1016
1017   foreach my $accno (sort keys %trb) {
1018     $ref = {};
1019
1020     $ref->{accno} = $accno;
1021     map { $ref->{$_} = $trb{$accno}{$_} }
1022       qw(description category charttype amount);
1023
1024     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1025
1026     if ($trb{$accno}{charttype} eq 'A') {
1027
1028       # get DR/CR
1029       do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno});
1030
1031       ($debit, $credit) = (0, 0);
1032       while (($debit, $credit) = $drcr->fetchrow_array) {
1033         $ref->{debit}  += $debit;
1034         $ref->{credit} += $credit;
1035       }
1036       $drcr->finish;
1037
1038       if ($form->{project_id}) {
1039
1040         # get DR/CR
1041         do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno});
1042
1043         ($debit, $credit) = (0, 0);
1044         while (($debit, $credit) = $project_drcr->fetchrow_array) {
1045           $ref->{debit}  += $debit;
1046           $ref->{credit} += $credit;
1047         }
1048         $project_drcr->finish;
1049       }
1050
1051       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1052       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1053
1054     }
1055
1056     # add subtotal
1057     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1058     $accno = pop @accno;
1059     if ($accno) {
1060       $trb{$accno}{debit}  += $ref->{debit};
1061       $trb{$accno}{credit} += $ref->{credit};
1062     }
1063
1064     push @{ $form->{TB} }, $ref;
1065
1066   }
1067
1068   $dbh->disconnect;
1069
1070   # debits and credits for headings
1071   foreach $accno (@headingaccounts) {
1072     foreach $ref (@{ $form->{TB} }) {
1073       if ($accno eq $ref->{accno}) {
1074         $ref->{debit}  = $trb{$accno}{debit};
1075         $ref->{credit} = $trb{$accno}{credit};
1076       }
1077     }
1078   }
1079
1080   $main::lxdebug->leave_sub();
1081 }
1082
1083 sub get_storno {
1084   $main::lxdebug->enter_sub();
1085   my ($self, $dbh, $form) = @_;
1086   my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1087   my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1088   my $sth =  $dbh->prepare($query);
1089   while(my $ref = $sth->fetchrow_hashref()) {
1090     $ref->{invnumer} =~ s/Storno zu //g;
1091     $form->{storno}{$ref->{invnumber}} = 1;
1092   }
1093   $main::lxdebug->leave_sub();
1094 }
1095
1096 sub aging {
1097   $main::lxdebug->enter_sub();
1098
1099   my ($self, $myconfig, $form) = @_;
1100
1101   # connect to database
1102   my $dbh     = $form->dbconnect($myconfig);
1103
1104   my ($invoice, $arap, $buysell, $ct, $ct_id);
1105
1106   if ($form->{ct} eq "customer") {
1107     $invoice = "is";
1108     $arap = "ar";
1109     $buysell = "buy";
1110     $ct = "customer";
1111   } else {
1112     $invoice = "ir";
1113     $arap = "ap";
1114     $buysell = "sell";
1115     $ct = "vendor";
1116   }
1117   $ct_id = "${ct}_id";
1118
1119   $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1120   my $todate = conv_dateq($form->{todate});
1121
1122   my $where = " 1 = 1 ";
1123   my ($name, $null);
1124
1125   if ($form->{$ct_id}) {
1126     $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1127   } elsif ($form->{ $form->{ct} }) {
1128     $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1129   }
1130
1131   my $dpt_join;
1132   if ($form->{department}) {
1133     ($null, $department_id) = split /--/, $form->{department};
1134     $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1135     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1136   }
1137
1138   my $q_details = qq|
1139     -- between 0-30 days
1140
1141     SELECT ${ct}.id AS ctid, ${ct}.name,
1142       street, zipcode, city, country, contact, email,
1143       phone as customerphone, fax as customerfax, ${ct}number,
1144       "invnumber", "transdate",
1145       (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1146       "duedate", invoice, ${arap}.id,
1147       (SELECT $buysell
1148        FROM exchangerate
1149        WHERE (${arap}.curr = exchangerate.curr)
1150          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1151     FROM ${arap}, ${ct}
1152     WHERE (paid != amount)
1153       AND (${arap}.storno IS FALSE)
1154       AND (${arap}.${ct}_id = ${ct}.id)
1155       AND (${ct}.id = ?)
1156       AND (transdate <= (date $todate - interval '0 days'))
1157       AND (transdate >= (date $todate - interval '30 days'))
1158
1159     UNION
1160
1161     -- between 31-60 days
1162
1163     SELECT ${ct}.id AS ctid, ${ct}.name,
1164       street, zipcode, city, country, contact, email,
1165       phone as customerphone, fax as customerfax, ${ct}number,
1166       "invnumber", "transdate",
1167       0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1168       "duedate", invoice, ${arap}.id,
1169       (SELECT $buysell
1170        FROM exchangerate
1171        WHERE (${arap}.curr = exchangerate.curr)
1172          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1173     FROM ${arap}, ${ct}
1174     WHERE (paid != amount)
1175       AND (${arap}.storno IS FALSE)
1176       AND (${arap}.${ct}_id = ${ct}.id)
1177       AND (${ct}.id = ?)
1178       AND (transdate < (date $todate - interval '30 days'))
1179       AND (transdate >= (date $todate - interval '60 days'))
1180
1181     UNION
1182
1183     -- between 61-90 days
1184
1185     SELECT ${ct}.id AS ctid, ${ct}.name,
1186       street, zipcode, city, country, contact, email,
1187       phone as customerphone, fax as customerfax, ${ct}number,
1188       "invnumber", "transdate",
1189       0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1190       "duedate", invoice, ${arap}.id,
1191       (SELECT $buysell
1192        FROM exchangerate
1193        WHERE (${arap}.curr = exchangerate.curr)
1194          AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1195     FROM ${arap}, ${ct}
1196     WHERE (paid != amount)
1197       AND (${arap}.storno IS FALSE)
1198       AND (${arap}.${ct}_id = ${ct}.id)
1199       AND (${ct}.id = ?)
1200       AND (transdate < (date $todate - interval '60 days'))
1201       AND (transdate >= (date $todate - interval '90 days'))
1202
1203     UNION
1204
1205     -- over 90 days
1206
1207     SELECT ${ct}.id AS ctid, ${ct}.name,
1208       street, zipcode, city, country, contact, email,
1209       phone as customerphone, fax as customerfax, ${ct}number,
1210       "invnumber", "transdate",
1211       0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1212       "duedate", invoice, ${arap}.id,
1213       (SELECT $buysell
1214        FROM exchangerate
1215        WHERE (${arap}.curr = exchangerate.curr)
1216        AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1217     FROM ${arap}, ${ct}
1218     WHERE (paid != amount)
1219       AND (${arap}.storno IS FALSE)
1220       AND (${arap}.${ct}_id = ${ct}.id)
1221       AND (${ct}.id = ?)
1222       AND (transdate < (date $todate - interval '90 days'))
1223
1224     ORDER BY ctid, transdate, invnumber |;
1225
1226   my $sth_details = prepare_query($form, $dbh, $q_details);
1227
1228   # select outstanding vendors or customers, depends on $ct
1229   my $query =
1230     qq|SELECT DISTINCT ct.id, ct.name
1231        FROM $ct ct, $arap a
1232        $dpt_join
1233        WHERE $where
1234          AND (a.${ct_id} = ct.id)
1235          AND (a.paid != a.amount)
1236          AND (a.transdate <= $todate)
1237        ORDER BY ct.name|;
1238
1239   my $sth = prepare_execute_query($form, $dbh, $query);
1240
1241   $form->{AG} = [];
1242   # for each company that has some stuff outstanding
1243   while (my ($id) = $sth->fetchrow_array) {
1244     do_statement($form, $sth_details, $q_details, $id, $id, $id, $id);
1245
1246     while (my $ref = $sth_details->fetchrow_hashref(NAME_lc)) {
1247       $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1248       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1249       push @{ $form->{AG} }, $ref;
1250     }
1251
1252     $sth_details->finish;
1253
1254   }
1255
1256   $sth->finish;
1257
1258   # disconnect
1259   $dbh->disconnect;
1260
1261   $main::lxdebug->leave_sub();
1262 }
1263
1264 sub get_customer {
1265   $main::lxdebug->enter_sub();
1266
1267   my ($self, $myconfig, $form) = @_;
1268
1269   # connect to database
1270   my $dbh = $form->dbconnect($myconfig);
1271
1272   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1273
1274   my $query =
1275     qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1276        FROM $ct ct
1277        WHERE ct.id = ?|;
1278   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1279     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1280   $dbh->disconnect;
1281
1282   $main::lxdebug->leave_sub();
1283 }
1284
1285 sub get_taxaccounts {
1286   $main::lxdebug->enter_sub();
1287
1288   my ($self, $myconfig, $form) = @_;
1289
1290   # connect to database
1291   my $dbh = $form->dbconnect($myconfig);
1292
1293   # get tax accounts
1294   my $query =
1295     qq|SELECT c.accno, c.description, t.rate
1296        FROM chart c, tax t
1297        WHERE (c.link LIKE '%CT_tax%') AND (c.id = t.chart_id)
1298        ORDER BY c.accno|;
1299   $form->{taxaccounts} = selectall_hashref_quert($form, $dbh, $query);
1300
1301   $dbh->disconnect;
1302
1303   $main::lxdebug->leave_sub();
1304 }
1305
1306 sub tax_report {
1307   $main::lxdebug->enter_sub();
1308
1309   my ($self, $myconfig, $form) = @_;
1310
1311   # connect to database
1312   my $dbh = $form->dbconnect($myconfig);
1313
1314   my ($null, $department_id) = split /--/, $form->{department};
1315
1316   # build WHERE
1317   my $where = "1 = 1";
1318
1319   if ($department_id) {
1320     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1321   }
1322
1323   my ($accno, $rate);
1324
1325   if ($form->{accno}) {
1326     $accno = $form->{accno};
1327     $rate  = $form->{"$form->{accno}_rate"};
1328     $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1329   }
1330   $rate *= 1;
1331
1332   my ($table, $ARAP);
1333
1334   if ($form->{db} eq 'ar') {
1335     $table = "customer";
1336     $ARAP  = "AR";
1337   } else {
1338     $table = "vendor";
1339     $ARAP  = "AP";
1340   }
1341
1342   my $arap = lc($ARAP);
1343
1344   my $transdate = "a.transdate";
1345
1346   if ($form->{method} eq 'cash') {
1347     $transdate = "a.datepaid";
1348
1349     my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1350
1351     $where .= qq|
1352       AND ac.trans_id IN
1353         (
1354           SELECT trans_id
1355           FROM acc_trans
1356           JOIN chart ON (chart_id = id)
1357           WHERE (link LIKE '%${ARAP}_paid%')
1358           AND (transdate <= $todate)
1359         )
1360       |;
1361   }
1362
1363   # if there are any dates construct a where
1364   $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1365   $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1366
1367   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1368
1369   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1370   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1371
1372   if ($form->{report} !~ /nontaxable/) {
1373     $query =
1374       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1375           ac.amount * $ml AS tax
1376          FROM acc_trans ac
1377          JOIN ${arap} a ON (a.id = ac.trans_id)
1378          JOIN chart ch ON (ch.id = ac.chart_id)
1379          JOIN $table n ON (n.id = a.${table}_id)
1380          WHERE
1381            $where
1382            $accno
1383            AND (a.invoice = '0')
1384
1385          UNION
1386
1387          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1388            i.sellprice * i.qty * $rate * $ml AS tax
1389          FROM acc_trans ac
1390          JOIN ${arap} a ON (a.id = ac.trans_id)
1391          JOIN chart ch ON (ch.id = ac.chart_id)
1392          JOIN $table n ON (n.id = a.${table}_id)
1393          JOIN ${table}tax t ON (t.${table}_id = n.id)
1394          JOIN invoice i ON (i.trans_id = a.id)
1395          JOIN partstax p ON (p.parts_id = i.parts_id)
1396          WHERE
1397            $where
1398            $accno
1399            AND (a.invoice = '1')
1400          ORDER BY $sortorder|;
1401   } else {
1402     # only gather up non-taxable transactions
1403     $query =
1404       qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount
1405          FROM acc_trans ac
1406          JOIN ${arap} a ON (a.id = ac.trans_id)
1407          JOIN $table n ON (n.id = a.${table}_id)
1408          WHERE
1409            $where
1410            AND (a.invoice = '0')
1411            AND (a.netamount = a.amount)
1412
1413          UNION
1414
1415          SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount
1416          FROM acc_trans ac
1417          JOIN ${arap} a ON (a.id = ac.trans_id)
1418          JOIN $table n ON (n.id = a.${table}_id)
1419          JOIN invoice i ON (i.trans_id = a.id)
1420          WHERE
1421            $where
1422            AND (a.invoice = '1')
1423            AND (
1424              a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
1425              OR
1426              i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id))
1427            )
1428          GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1429          ORDER by $sortorder|;
1430   }
1431
1432   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1433
1434   $dbh->disconnect;
1435
1436   $main::lxdebug->leave_sub();
1437 }
1438
1439 sub paymentaccounts {
1440   $main::lxdebug->enter_sub();
1441
1442   my ($self, $myconfig, $form) = @_;
1443
1444   # connect to database, turn AutoCommit off
1445   my $dbh = $form->dbconnect_noauto($myconfig);
1446
1447   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1448
1449   # get A(R|P)_paid accounts
1450   my $query =
1451     qq|SELECT accno, description
1452        FROM chart
1453        WHERE link LIKE '%${ARAP}_paid%'|;
1454   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1455
1456   $dbh->disconnect;
1457
1458   $main::lxdebug->leave_sub();
1459 }
1460
1461 sub payments {
1462   $main::lxdebug->enter_sub();
1463
1464   my ($self, $myconfig, $form) = @_;
1465
1466   # connect to database, turn AutoCommit off
1467   my $dbh = $form->dbconnect_noauto($myconfig);
1468
1469   my $ml = 1;
1470   my $arap;
1471   if ($form->{db} eq 'ar') {
1472     $table = 'customer';
1473     $ml = -1;
1474     $arap = 'ar';
1475   } else {
1476     $table = 'vendor';
1477     $arap = 'ap';
1478   }
1479
1480   my ($query, $sth);
1481   my $dpt_join;
1482   my $where;
1483
1484   if ($form->{department_id}) {
1485     $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
1486     $where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1487   }
1488
1489   if ($form->{fromdate}) {
1490     $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1491   }
1492   if ($form->{todate}) {
1493     $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1494   }
1495   if (!$form->{fx_transaction}) {
1496     $where .= " AND ac.fx_transaction = '0'";
1497   }
1498
1499   my $invnumber;
1500   my $reference;
1501   if ($form->{reference}) {
1502     $reference = $dbh->quote('%' . $form->{reference} . '%');
1503     $invnumber = " AND (a.invnumber LIKE $reference)";
1504     $reference = " AND (g.reference LIKE $reference)";
1505   }
1506   if ($form->{source}) {
1507     $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1508   }
1509   if ($form->{memo}) {
1510     $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1511   }
1512
1513   my $sortorder = join(', ', qw(name invnumber ordnumber transdate source));
1514   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(transdate invnumber name source memo)));
1515
1516   $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1517   my $sth = prepare_query($form, $dbh, $query);
1518
1519   my $q_details =
1520       qq|SELECT c.name, a.invnumber, a.ordnumber,
1521            ac.transdate, ac.amount * $ml AS paid, ac.source,
1522            a.invoice, a.id, ac.memo, '${arap}' AS module
1523          FROM acc_trans ac
1524          JOIN $arap a ON (ac.trans_id = a.id)
1525          JOIN $table c ON (c.id = a.${table}_id)
1526          $dpt_join
1527          WHERE (ac.chart_id = ?)
1528            $where
1529            $invnumber
1530
1531          UNION
1532
1533          SELECT g.description, g.reference, NULL AS ordnumber,
1534            ac.transdate, ac.amount * $ml AS paid, ac.source,
1535            '0' as invoice, g.id, ac.memo, 'gl' AS module
1536          FROM acc_trans ac
1537          JOIN gl g ON (g.id = ac.trans_id)
1538          $dpt_join
1539          WHERE (ac.chart_id = ?)
1540            $where
1541            $reference
1542            AND (ac.amount * $ml) > 0
1543
1544          ORDER BY $sortorder|;
1545   my $sth_details = prepare_query($form, $dbh, $q_details);
1546
1547   $form->{PR} = [];
1548
1549   # cycle through each id
1550   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1551     do_statement($form, $sth, $query, $accno);
1552     my $ref = $sth->fetchrow_hashref();
1553     push(@{ $form->{PR} }, $ref);
1554     $sth->finish();
1555
1556     $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1557
1558     do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1559     while (my $pr = $sth_details->fetchrow_hashref()) {
1560       push(@{ $form->{ $ref->{id} } }, $pr);
1561     }
1562     $sth_details->finish();
1563   }
1564
1565   $dbh->disconnect;
1566
1567   $main::lxdebug->leave_sub();
1568 }
1569
1570 sub bwa {
1571   $main::lxdebug->enter_sub();
1572
1573   my ($self, $myconfig, $form) = @_;
1574
1575   # connect to database
1576   my $dbh = $form->dbconnect($myconfig);
1577
1578   my $last_period = 0;
1579   my $category;
1580   my @categories  =
1581     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);
1582
1583   $form->{decimalplaces} *= 1;
1584
1585   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1586
1587   # if there are any compare dates
1588   if ($form->{fromdate} || $form->{todate}) {
1589     $last_period = 1;
1590     if ($form->{fromdate}) {
1591       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1592       $year = $1;
1593     } else {
1594       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1595       $year = $1;
1596     }
1597     $kummfromdate = $form->{comparefromdate};
1598     $kummtodate   = $form->{comparetodate};
1599     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1600   }
1601
1602   @periods        = qw(jetzt kumm);
1603   @gesamtleistung = qw(1 2 3);
1604   @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
1605   @ergebnisse     =
1606     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1607
1608   foreach $key (@periods) {
1609     $form->{ "$key" . "gesamtleistung" } = 0;
1610     $form->{ "$key" . "gesamtkosten" }   = 0;
1611
1612     foreach $category (@categories) {
1613
1614       if (defined($form->{$category}{$key})) {
1615         $form->{"$key$category"} =
1616           $form->format_amount($myconfig,
1617                                $form->round_amount($form->{$category}{$key}, 2
1618                                ),
1619                                $form->{decimalplaces},
1620                                '0');
1621       }
1622     }
1623     foreach $item (@gesamtleistung) {
1624       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1625     }
1626     foreach $item (@gesamtkosten) {
1627       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1628     }
1629     $form->{ "$key" . "rohertrag" } =
1630       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1631     $form->{ "$key" . "betriebrohertrag" } =
1632       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1633     $form->{ "$key" . "betriebsergebnis" } =
1634       $form->{ "$key" . "betriebrohertrag" } -
1635       $form->{ "$key" . "gesamtkosten" };
1636     $form->{ "$key" . "neutraleraufwand" } =
1637       $form->{30}{$key} + $form->{31}{$key};
1638     $form->{ "$key" . "neutralertrag" } =
1639       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1640     $form->{ "$key" . "ergebnisvorsteuern" } =
1641       $form->{ "$key" . "betriebsergebnis" } -
1642       $form->{ "$key" . "neutraleraufwand" } +
1643       $form->{ "$key" . "neutralertrag" };
1644     $form->{ "$key" . "ergebnis" } =
1645       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1646
1647     if ($form->{ "$key" . "gesamtleistung" } > 0) {
1648       foreach $category (@categories) {
1649         if (defined($form->{$category}{$key})) {
1650           $form->{ "$key" . "gl" . "$category" } =
1651             $form->format_amount(
1652                                $myconfig,
1653                                $form->round_amount(
1654                                  ($form->{$category}{$key} /
1655                                     $form->{ "$key" . "gesamtleistung" } * 100
1656                                  ),
1657                                  $form->{decimalplaces}
1658                                ),
1659                                $form->{decimalplaces},
1660                                '0');
1661         }
1662       }
1663       foreach $item (@ergebnisse) {
1664         $form->{ "$key" . "gl" . "$item" } =
1665           $form->format_amount($myconfig,
1666                                $form->round_amount(
1667                                  ( $form->{ "$key" . "$item" } /
1668                                      $form->{ "$key" . "gesamtleistung" } * 100
1669                                  ),
1670                                  $form->{decimalplaces}
1671                                ),
1672                                $form->{decimalplaces},
1673                                '0');
1674       }
1675     }
1676
1677     if ($form->{ "$key" . "gesamtkosten" } > 0) {
1678       foreach $category (@categories) {
1679         if (defined($form->{$category}{$key})) {
1680           $form->{ "$key" . "gk" . "$category" } =
1681             $form->format_amount($myconfig,
1682                                  $form->round_amount(
1683                                    ($form->{$category}{$key} /
1684                                       $form->{ "$key" . "gesamtkosten" } * 100
1685                                    ),
1686                                    $form->{decimalplaces}
1687                                  ),
1688                                  $form->{decimalplaces},
1689                                  '0');
1690         }
1691       }
1692       foreach $item (@ergebnisse) {
1693         $form->{ "$key" . "gk" . "$item" } =
1694           $form->format_amount($myconfig,
1695                                $form->round_amount(
1696                                    ($form->{ "$key" . "$item" } /
1697                                       $form->{ "$key" . "gesamtkosten" } * 100
1698                                    ),
1699                                    $form->{decimalplaces}
1700                                ),
1701                                $form->{decimalplaces},
1702                                '0');
1703       }
1704     }
1705
1706     if ($form->{10}{$key} > 0) {
1707       foreach $category (@categories) {
1708         if (defined($form->{$category}{$key})) {
1709           $form->{ "$key" . "pk" . "$category" } =
1710             $form->format_amount(
1711                         $myconfig,
1712                         $form->round_amount(
1713                           ($form->{$category}{$key} / $form->{10}{$key} * 100),
1714                           $form->{decimalplaces}
1715                         ),
1716                         $form->{decimalplaces},
1717                         '0');
1718         }
1719       }
1720       foreach $item (@ergebnisse) {
1721         $form->{ "$key" . "pk" . "$item" } =
1722           $form->format_amount($myconfig,
1723                                $form->round_amount(
1724                                                 ($form->{ "$key" . "$item" } /
1725                                                    $form->{10}{$key} * 100
1726                                                 ),
1727                                                 $form->{decimalplaces}
1728                                ),
1729                                $form->{decimalplaces},
1730                                '0');
1731       }
1732     }
1733
1734     if ($form->{4}{$key} > 0) {
1735       foreach $category (@categories) {
1736         if (defined($form->{$category}{$key})) {
1737           $form->{ "$key" . "auf" . "$category" } =
1738             $form->format_amount(
1739                          $myconfig,
1740                          $form->round_amount(
1741                            ($form->{$category}{$key} / $form->{4}{$key} * 100),
1742                            $form->{decimalplaces}
1743                          ),
1744                          $form->{decimalplaces},
1745                          '0');
1746         }
1747       }
1748       foreach $item (@ergebnisse) {
1749         $form->{ "$key" . "auf" . "$item" } =
1750           $form->format_amount($myconfig,
1751                                $form->round_amount(
1752                                                 ($form->{ "$key" . "$item" } /
1753                                                    $form->{4}{$key} * 100
1754                                                 ),
1755                                                 $form->{decimalplaces}
1756                                ),
1757                                $form->{decimalplaces},
1758                                '0');
1759       }
1760     }
1761
1762     foreach $item (@ergebnisse) {
1763       $form->{ "$key" . "$item" } =
1764         $form->format_amount($myconfig,
1765                              $form->round_amount($form->{ "$key" . "$item" },
1766                                                  $form->{decimalplaces}
1767                              ),
1768                              $form->{decimalplaces},
1769                              '0');
1770     }
1771
1772   }
1773   $dbh->disconnect;
1774
1775   $main::lxdebug->leave_sub();
1776 }
1777
1778 sub ustva {
1779   $main::lxdebug->enter_sub();
1780
1781   my ($self, $myconfig, $form) = @_;
1782
1783   # connect to database
1784   my $dbh = $form->dbconnect($myconfig);
1785
1786   my $last_period     = 0;
1787   my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
1788     96 66 43 45 53 62 65 67);
1789   my @categories_euro = qw(48 51 86 91 97 93 94);
1790   $form->{decimalplaces} *= 1;
1791
1792   foreach $item (@categories_cent) {
1793     $form->{"$item"} = 0;
1794   }
1795   foreach $item (@categories_euro) {
1796     $form->{"$item"} = 0;
1797   }
1798
1799   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_ustva");
1800
1801   #   foreach $item (@categories_cent) {
1802   #     if ($form->{$item}{"jetzt"} > 0) {
1803   #             $form->{$item} = $form->{$item}{"jetzt"};
1804   #             delete $form->{$item}{"jetzt"};
1805   #     }
1806   #   }
1807   #   foreach $item (@categories_euro) {
1808   #     if ($form->{$item}{"jetzt"} > 0) {
1809   #             $form->{$item} = $form->{$item}{"jetzt"};
1810   #             delete $form->{$item}{"jetzt"};
1811   #     }  foreach $item (@categories_cent) {
1812   #     if ($form->{$item}{"jetzt"} > 0) {
1813   #             $form->{$item} = $form->{$item}{"jetzt"};
1814   #             delete $form->{$item}{"jetzt"};
1815   #     }
1816   #   }
1817   #   foreach $item (@categories_euro) {
1818   #     if ($form->{$item}{"jetzt"} > 0) {
1819   #             $form->{$item} = $form->{$item}{"jetzt"};
1820   #             delete $form->{$item}{"jetzt"};
1821   #     }
1822   #   }
1823   #
1824   #    }
1825
1826   #
1827   # Berechnung der USTVA Formularfelder
1828   #
1829   $form->{"51r"} = $form->{"511"};
1830   $form->{"86r"} = $form->{"861"};
1831   $form->{"97r"} = $form->{"971"};
1832   $form->{"93r"} = $form->{"931"};
1833
1834   #$form->{"96"}  = $form->{"94"} * 0.16;
1835   $form->{"43"} =
1836     $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
1837     $form->{"96"};
1838   $form->{"45"} = $form->{"43"};
1839   $form->{"53"} = $form->{"43"};
1840   $form->{"62"} = $form->{"43"} - $form->{"66"};
1841   $form->{"65"} = $form->{"43"} - $form->{"66"};
1842   $form->{"67"} = $form->{"43"} - $form->{"66"};
1843
1844   foreach $item (@categories_cent) {
1845     $form->{$item} =
1846       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
1847                            2, '0');
1848   }
1849
1850   foreach $item (@categories_euro) {
1851     $form->{$item} =
1852       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
1853                            0, '0');
1854   }
1855
1856   $dbh->disconnect;
1857
1858   $main::lxdebug->leave_sub();
1859 }
1860
1861 sub income_statement {
1862   $main::lxdebug->enter_sub();
1863
1864   my ($self, $myconfig, $form) = @_;
1865
1866   # connect to database
1867   my $dbh = $form->dbconnect($myconfig);
1868
1869   my $last_period          = 0;
1870   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
1871   my @categories_ausgaben  =
1872     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);
1873
1874   my @ergebnisse = qw(sumeura sumeurb guvsumme);
1875
1876   $form->{decimalplaces} *= 1;
1877
1878   foreach $item (@categories_einnahmen) {
1879     $form->{$item} = 0;
1880   }
1881   foreach $item (@categories_ausgaben) {
1882     $form->{$item} = 0;
1883   }
1884
1885   foreach $item (@ergebnisse) {
1886     $form->{$item} = 0;
1887   }
1888
1889   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1890                   $form, "pos_eur");
1891
1892   foreach $item (@categories_einnahmen) {
1893     $form->{"eur${item}"} =
1894       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1895     $form->{"sumeura"} += $form->{$item};
1896   }
1897   foreach $item (@categories_ausgaben) {
1898     $form->{"eur${item}"} =
1899       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1900     $form->{"sumeurb"} += $form->{$item};
1901   }
1902
1903   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
1904
1905   foreach $item (@ergebnisse) {
1906     $form->{$item} =
1907       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
1908   }
1909   $main::lxdebug->leave_sub();
1910 }
1911 1;