SL/RP.pm

   1 #=====================================================================
   2 # LX-Office ERP
   3 # Copyright (C) 2004
   4 # Based on SQL-Ledger Version 2.1.9
   5 # Web http://www.lx-office.org
   6 #
   7 #=====================================================================
   8 # SQL-Ledger Accounting
   9 # Copyright (C) 1998-2002
  10 #
  11 #  Author: Dieter Simader
  12 #   Email: dsimader@sql-ledger.org
  13 #     Web: http://www.sql-ledger.org
  14 #
  15 #  Contributors: Benjamin Lee <benjaminlee@consultant.com>
  16 #
  17 # This program is free software; you can redistribute it and/or modify
  18 # it under the terms of the GNU General Public License as published by
  19 # the Free Software Foundation; either version 2 of the License, or
  20 # (at your option) any later version.
  21 #
  22 # This program is distributed in the hope that it will be useful,
  23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 # GNU General Public License for more details.
  26 # You should have received a copy of the GNU General Public License
  27 # along with this program; if not, write to the Free Software
  28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  29 #======================================================================
  30 #
  31 # backend code for reports
  32 #
  33 #======================================================================
  34
  35 package RP;
  36
  37 sub income_statement {
  38   $main::lxdebug->enter_sub();
  39
  40   my ($self, $myconfig, $form) = @_;
  41
  42   # connect to database
  43   my $dbh = $form->dbconnect($myconfig);
  44
  45   my $last_period = 0;
  46   my @categories  = qw(I E);
  47   my $category;
  48
  49   $form->{decimalplaces} *= 1;
  50
  51   &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form,
  52                 \@categories);
  53
  54   # if there are any compare dates
  55   if ($form->{comparefromdate} || $form->{comparetodate}) {
  56     $last_period = 1;
  57
  58     &get_accounts($dbh, $last_period,
  59                   $form->{comparefromdate},
  60                   $form->{comparetodate},
  61                   $form, \@categories);
  62   }
  63
  64   # disconnect
  65   $dbh->disconnect;
  66
  67   # now we got $form->{I}{accno}{ }
  68   # and $form->{E}{accno}{  }
  69
  70   my %account = (
  71                  'I' => { 'label'  => 'income',
  72                           'labels' => 'income',
  73                           'ml'     => 1
  74                  },
  75                  'E' => { 'label'  => 'expense',
  76                           'labels' => 'expenses',
  77                           'ml'     => -1
  78                  });
  79
  80   my $str;
  81
  82   foreach $category (@categories) {
  83
  84     foreach $key (sort keys %{ $form->{$category} }) {
  85
  86       # push description onto array
  87
  88       $str = ($form->{l_heading}) ? $form->{padding} : "";
  89
  90       if ($form->{$category}{$key}{charttype} eq "A") {
  91         $str .=
  92           ($form->{l_accno})
  93           ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
  94           : "$form->{$category}{$key}{description}";
  95       }
  96       if ($form->{$category}{$key}{charttype} eq "H") {
  97         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
  98           $dash = "- ";
  99           push(@{ $form->{"$account{$category}{label}_account"} },
 100                "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
 101           );
 102           push(@{ $form->{"$account{$category}{labels}_this_period"} },
 103                $form->format_amount(
 104                         $myconfig,
 105                         $account{$category}{subthis} * $account{$category}{ml},
 106                         $form->{decimalplaces}, $dash
 107                ));
 108
 109           if ($last_period) {
 110             push(@{ $form->{"$account{$category}{labels}_last_period"} },
 111                  $form->format_amount(
 112                         $myconfig,
 113                         $account{$category}{sublast} * $account{$category}{ml},
 114                         $form->{decimalplaces}, $dash
 115                  ));
 116           }
 117
 118         }
 119
 120         $str =
 121           "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
 122
 123         $account{$category}{subthis}        = $form->{$category}{$key}{this};
 124         $account{$category}{sublast}        = $form->{$category}{$key}{last};
 125         $account{$category}{subdescription} =
 126           $form->{$category}{$key}{description};
 127         $account{$category}{subtotal} = 1;
 128
 129         $form->{$category}{$key}{this} = 0;
 130         $form->{$category}{$key}{last} = 0;
 131
 132         next unless $form->{l_heading};
 133
 134         $dash = " ";
 135       }
 136
 137       push(@{ $form->{"$account{$category}{label}_account"} }, $str);
 138
 139       if ($form->{$category}{$key}{charttype} eq 'A') {
 140         $form->{"total_$account{$category}{labels}_this_period"} +=
 141           $form->{$category}{$key}{this} * $account{$category}{ml};
 142         $dash = "- ";
 143       }
 144
 145       push(@{ $form->{"$account{$category}{labels}_this_period"} },
 146            $form->format_amount(
 147                       $myconfig,
 148                       $form->{$category}{$key}{this} * $account{$category}{ml},
 149                       $form->{decimalplaces}, $dash
 150            ));
 151
 152       # add amount or - for last period
 153       if ($last_period) {
 154         $form->{"total_$account{$category}{labels}_last_period"} +=
 155           $form->{$category}{$key}{last} * $account{$category}{ml};
 156
 157         push(@{ $form->{"$account{$category}{labels}_last_period"} },
 158              $form->format_amount(
 159                       $myconfig,
 160                       $form->{$category}{$key}{last} * $account{$category}{ml},
 161                       $form->{decimalplaces}, $dash
 162              ));
 163       }
 164     }
 165
 166     $str = ($form->{l_heading}) ? $form->{padding} : "";
 167     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
 168       push(@{ $form->{"$account{$category}{label}_account"} },
 169            "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
 170       );
 171       push(@{ $form->{"$account{$category}{labels}_this_period"} },
 172            $form->format_amount(
 173                         $myconfig,
 174                         $account{$category}{subthis} * $account{$category}{ml},
 175                         $form->{decimalplaces}, $dash
 176            ));
 177
 178       if ($last_period) {
 179         push(@{ $form->{"$account{$category}{labels}_last_period"} },
 180              $form->format_amount(
 181                         $myconfig,
 182                         $account{$category}{sublast} * $account{$category}{ml},
 183                         $form->{decimalplaces}, $dash
 184              ));
 185       }
 186     }
 187
 188   }
 189
 190   # totals for income and expenses
 191   $form->{total_income_this_period} =
 192     $form->round_amount($form->{total_income_this_period},
 193                         $form->{decimalplaces});
 194   $form->{total_expenses_this_period} =
 195     $form->round_amount($form->{total_expenses_this_period},
 196                         $form->{decimalplaces});
 197
 198   # total for income/loss
 199   $form->{total_this_period} =
 200     $form->{total_income_this_period} - $form->{total_expenses_this_period};
 201
 202   if ($last_period) {
 203
 204     # total for income/loss
 205     $form->{total_last_period} =
 206       $form->format_amount(
 207        $myconfig,
 208        $form->{total_income_last_period} - $form->{total_expenses_last_period},
 209        $form->{decimalplaces},
 210        "- ");
 211
 212     # totals for income and expenses for last_period
 213     $form->{total_income_last_period} =
 214       $form->format_amount($myconfig,
 215                            $form->{total_income_last_period},
 216                            $form->{decimalplaces}, "- ");
 217     $form->{total_expenses_last_period} =
 218       $form->format_amount($myconfig,
 219                            $form->{total_expenses_last_period},
 220                            $form->{decimalplaces}, "- ");
 221
 222   }
 223
 224   $form->{total_income_this_period} =
 225     $form->format_amount($myconfig,
 226                          $form->{total_income_this_period},
 227                          $form->{decimalplaces}, "- ");
 228   $form->{total_expenses_this_period} =
 229     $form->format_amount($myconfig,
 230                          $form->{total_expenses_this_period},
 231                          $form->{decimalplaces}, "- ");
 232   $form->{total_this_period} =
 233     $form->format_amount($myconfig,
 234                          $form->{total_this_period},
 235                          $form->{decimalplaces}, "- ");
 236
 237   $main::lxdebug->leave_sub();
 238 }
 239
 240 sub balance_sheet {
 241   $main::lxdebug->enter_sub();
 242
 243   my ($self, $myconfig, $form) = @_;
 244
 245   # connect to database
 246   my $dbh = $form->dbconnect($myconfig);
 247
 248   my $last_period = 0;
 249   my @categories  = qw(A C L Q);
 250
 251   # if there are any dates construct a where
 252   if ($form->{asofdate}) {
 253
 254     $form->{this_period} = "$form->{asofdate}";
 255     $form->{period}      = "$form->{asofdate}";
 256
 257   }
 258
 259   $form->{decimalplaces} *= 1;
 260
 261   &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form,
 262                 \@categories);
 263
 264   # if there are any compare dates
 265   if ($form->{compareasofdate}) {
 266
 267     $last_period = 1;
 268     &get_accounts($dbh, $last_period, "", $form->{compareasofdate},
 269                   $form, \@categories);
 270
 271     $form->{last_period} = "$form->{compareasofdate}";
 272
 273   }
 274
 275   # disconnect
 276   $dbh->disconnect;
 277
 278   # now we got $form->{A}{accno}{ }    assets
 279   # and $form->{L}{accno}{ }           liabilities
 280   # and $form->{Q}{accno}{ }           equity
 281   # build asset accounts
 282
 283   my $str;
 284   my $key;
 285
 286   my %account = (
 287                  'A' => { 'label'  => 'asset',
 288                           'labels' => 'assets',
 289                           'ml'     => -1
 290                  },
 291                  'L' => { 'label'  => 'liability',
 292                           'labels' => 'liabilities',
 293                           'ml'     => 1
 294                  },
 295                  'Q' => { 'label'  => 'equity',
 296                           'labels' => 'equity',
 297                           'ml'     => 1
 298                  });
 299
 300   foreach $category (grep { !/C/ } @categories) {
 301
 302     foreach $key (sort keys %{ $form->{$category} }) {
 303
 304       $str = ($form->{l_heading}) ? $form->{padding} : "";
 305
 306       if ($form->{$category}{$key}{charttype} eq "A") {
 307         $str .=
 308           ($form->{l_accno})
 309           ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
 310           : "$form->{$category}{$key}{description}";
 311       }
 312       if ($form->{$category}{$key}{charttype} eq "H") {
 313         if ($account{$category}{subtotal} && $form->{l_subtotal}) {
 314           $dash = "- ";
 315           push(@{ $form->{"$account{$category}{label}_account"} },
 316                "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
 317           );
 318           push(@{ $form->{"$account{$category}{label}_this_period"} },
 319                $form->format_amount(
 320                         $myconfig,
 321                         $account{$category}{subthis} * $account{$category}{ml},
 322                         $form->{decimalplaces}, $dash
 323                ));
 324
 325           if ($last_period) {
 326             push(@{ $form->{"$account{$category}{label}_last_period"} },
 327                  $form->format_amount(
 328                         $myconfig,
 329                         $account{$category}{sublast} * $account{$category}{ml},
 330                         $form->{decimalplaces}, $dash
 331                  ));
 332           }
 333         }
 334
 335         $str =
 336           "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
 337
 338         $account{$category}{subthis}        = $form->{$category}{$key}{this};
 339         $account{$category}{sublast}        = $form->{$category}{$key}{last};
 340         $account{$category}{subdescription} =
 341           $form->{$category}{$key}{description};
 342         $account{$category}{subtotal} = 1;
 343
 344         $form->{$category}{$key}{this} = 0;
 345         $form->{$category}{$key}{last} = 0;
 346
 347         next unless $form->{l_heading};
 348
 349         $dash = " ";
 350       }
 351
 352       # push description onto array
 353       push(@{ $form->{"$account{$category}{label}_account"} }, $str);
 354
 355       if ($form->{$category}{$key}{charttype} eq 'A') {
 356         $form->{"total_$account{$category}{labels}_this_period"} +=
 357           $form->{$category}{$key}{this} * $account{$category}{ml};
 358         $dash = "- ";
 359       }
 360
 361       push(@{ $form->{"$account{$category}{label}_this_period"} },
 362            $form->format_amount(
 363                       $myconfig,
 364                       $form->{$category}{$key}{this} * $account{$category}{ml},
 365                       $form->{decimalplaces}, $dash
 366            ));
 367
 368       if ($last_period) {
 369         $form->{"total_$account{$category}{labels}_last_period"} +=
 370           $form->{$category}{$key}{last} * $account{$category}{ml};
 371
 372         push(@{ $form->{"$account{$category}{label}_last_period"} },
 373              $form->format_amount(
 374                       $myconfig,
 375                       $form->{$category}{$key}{last} * $account{$category}{ml},
 376                       $form->{decimalplaces}, $dash
 377              ));
 378       }
 379     }
 380
 381     $str = ($form->{l_heading}) ? $form->{padding} : "";
 382     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
 383       push(@{ $form->{"$account{$category}{label}_account"} },
 384            "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
 385       );
 386       push(@{ $form->{"$account{$category}{label}_this_period"} },
 387            $form->format_amount(
 388                         $myconfig,
 389                         $account{$category}{subthis} * $account{$category}{ml},
 390                         $form->{decimalplaces}, $dash
 391            ));
 392
 393       if ($last_period) {
 394         push(@{ $form->{"$account{$category}{label}_last_period"} },
 395              $form->format_amount(
 396                         $myconfig,
 397                         $account{$category}{sublast} * $account{$category}{ml},
 398                         $form->{decimalplaces}, $dash
 399              ));
 400       }
 401     }
 402
 403   }
 404
 405   # totals for assets, liabilities
 406   $form->{total_assets_this_period} =
 407     $form->round_amount($form->{total_assets_this_period},
 408                         $form->{decimalplaces});
 409   $form->{total_liabilities_this_period} =
 410     $form->round_amount($form->{total_liabilities_this_period},
 411                         $form->{decimalplaces});
 412   $form->{total_equity_this_period} =
 413     $form->round_amount($form->{total_equity_this_period},
 414                         $form->{decimalplaces});
 415
 416   # calculate earnings
 417   $form->{earnings_this_period} =
 418     $form->{total_assets_this_period} -
 419     $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
 420
 421   push(@{ $form->{equity_this_period} },
 422        $form->format_amount($myconfig,
 423                             $form->{earnings_this_period},
 424                             $form->{decimalplaces}, "- "
 425        ));
 426
 427   $form->{total_equity_this_period} =
 428     $form->round_amount(
 429              $form->{total_equity_this_period} + $form->{earnings_this_period},
 430              $form->{decimalplaces});
 431
 432   # add liability + equity
 433   $form->{total_this_period} =
 434     $form->format_amount(
 435     $myconfig,
 436     $form->{total_liabilities_this_period} + $form->{total_equity_this_period},
 437     $form->{decimalplaces},
 438     "- ");
 439
 440   if ($last_period) {
 441
 442     # totals for assets, liabilities
 443     $form->{total_assets_last_period} =
 444       $form->round_amount($form->{total_assets_last_period},
 445                           $form->{decimalplaces});
 446     $form->{total_liabilities_last_period} =
 447       $form->round_amount($form->{total_liabilities_last_period},
 448                           $form->{decimalplaces});
 449     $form->{total_equity_last_period} =
 450       $form->round_amount($form->{total_equity_last_period},
 451                           $form->{decimalplaces});
 452
 453     # calculate retained earnings
 454     $form->{earnings_last_period} =
 455       $form->{total_assets_last_period} -
 456       $form->{total_liabilities_last_period} -
 457       $form->{total_equity_last_period};
 458
 459     push(@{ $form->{equity_last_period} },
 460          $form->format_amount($myconfig,
 461                               $form->{earnings_last_period},
 462                               $form->{decimalplaces}, "- "
 463          ));
 464
 465     $form->{total_equity_last_period} =
 466       $form->round_amount(
 467              $form->{total_equity_last_period} + $form->{earnings_last_period},
 468              $form->{decimalplaces});
 469
 470     # add liability + equity
 471     $form->{total_last_period} =
 472       $form->format_amount($myconfig,
 473                            $form->{total_liabilities_last_period} +
 474                              $form->{total_equity_last_period},
 475                            $form->{decimalplaces},
 476                            "- ");
 477
 478   }
 479
 480   $form->{total_liabilities_last_period} =
 481     $form->format_amount($myconfig,
 482                          $form->{total_liabilities_last_period},
 483                          $form->{decimalplaces}, "- ")
 484     if ($form->{total_liabilities_last_period} != 0);
 485
 486   $form->{total_equity_last_period} =
 487     $form->format_amount($myconfig,
 488                          $form->{total_equity_last_period},
 489                          $form->{decimalplaces}, "- ")
 490     if ($form->{total_equity_last_period} != 0);
 491
 492   $form->{total_assets_last_period} =
 493     $form->format_amount($myconfig,
 494                          $form->{total_assets_last_period},
 495                          $form->{decimalplaces}, "- ")
 496     if ($form->{total_assets_last_period} != 0);
 497
 498   $form->{total_assets_this_period} =
 499     $form->format_amount($myconfig,
 500                          $form->{total_assets_this_period},
 501                          $form->{decimalplaces}, "- ");
 502
 503   $form->{total_liabilities_this_period} =
 504     $form->format_amount($myconfig,
 505                          $form->{total_liabilities_this_period},
 506                          $form->{decimalplaces}, "- ");
 507
 508   $form->{total_equity_this_period} =
 509     $form->format_amount($myconfig,
 510                          $form->{total_equity_this_period},
 511                          $form->{decimalplaces}, "- ");
 512
 513   $main::lxdebug->leave_sub();
 514 }
 515
 516 sub get_accounts {
 517   $main::lxdebug->enter_sub();
 518
 519   my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
 520
 521   my ($null, $department_id) = split /--/, $form->{department};
 522
 523   my $query;
 524   my $dpt_where;
 525   my $dpt_join;
 526   my $project;
 527   my $where    = "1 = 1";
 528   my $glwhere  = "";
 529   my $subwhere = "";
 530   my $item;
 531
 532   my $category = "AND (";
 533   foreach $item (@{$categories}) {
 534     $category .= qq|c.category = '$item' OR |;
 535   }
 536   $category =~ s/OR $/\)/;
 537
 538   # get headings
 539   $query = qq|SELECT c.accno, c.description, c.category
 540               FROM chart c
 541               WHERE c.charttype = 'H'
 542               $category
 543               ORDER by c.accno|;
 544
 545   if ($form->{accounttype} eq 'gifi') {
 546     $query = qq|SELECT g.accno, g.description, c.category
 547                 FROM gifi g
 548                 JOIN chart c ON (c.gifi_accno = g.accno)
 549                 WHERE c.charttype = 'H'
 550                 $category
 551                 ORDER BY g.accno|;
 552   }
 553
 554   $sth = $dbh->prepare($query);
 555   $sth->execute || $form->dberror($query);
 556
 557   my @headingaccounts = ();
 558   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 559     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
 560       "$ref->{description}";
 561     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
 562     $form->{ $ref->{category} }{ $ref->{accno} }{accno}     = $ref->{accno};
 563
 564     push @headingaccounts, $ref->{accno};
 565   }
 566
 567   $sth->finish;
 568
 569   if ($fromdate) {
 570     if ($form->{method} eq 'cash') {
 571       $subwhere .= " AND transdate >= '$fromdate'";
 572       $glwhere = " AND ac.transdate >= '$fromdate'";
 573     } else {
 574       $where .= " AND ac.transdate >= '$fromdate'";
 575     }
 576   }
 577
 578   if ($todate) {
 579     $where    .= " AND ac.transdate <= '$todate'";
 580     $subwhere .= " AND transdate <= '$todate'";
 581   }
 582
 583   if ($department_id) {
 584     $dpt_join = qq|
 585                JOIN department t ON (a.department_id = t.id)
 586                   |;
 587     $dpt_where = qq|
 588                AND t.id = $department_id
 589                    |;
 590   }
 591
 592   if ($form->{project_id}) {
 593     $project = qq|
 594                  AND ac.project_id = $form->{project_id}
 595                  |;
 596   }
 597
 598   if ($form->{accounttype} eq 'gifi') {
 599
 600     if ($form->{method} eq 'cash') {
 601
 602       $query = qq|
 603
 604                  SELECT g.accno, sum(ac.amount) AS amount,
 605                  g.description, c.category
 606                  FROM acc_trans ac
 607                  JOIN chart c ON (c.id = ac.chart_id)
 608                  JOIN ar a ON (a.id = ac.trans_id)
 609                  JOIN gifi g ON (g.accno = c.gifi_accno)
 610                  $dpt_join
 611                  WHERE $where
 612                  $dpt_where
 613                  $category
 614                  AND ac.trans_id IN
 615                    (
 616                      SELECT trans_id
 617                      FROM acc_trans
 618                      JOIN chart ON (chart_id = id)
 619                      WHERE link LIKE '%AR_paid%'
 620                      $subwhere
 621                    )
 622                  $project
 623                  GROUP BY g.accno, g.description, c.category
 624
 625        UNION ALL
 626
 627                  SELECT '' AS accno, SUM(ac.amount) AS amount,
 628                  '' AS description, c.category
 629                  FROM acc_trans ac
 630                  JOIN chart c ON (c.id = ac.chart_id)
 631                  JOIN ar a ON (a.id = ac.trans_id)
 632                  $dpt_join
 633                  WHERE $where
 634                  $dpt_where
 635                  $category
 636                  AND c.gifi_accno = ''
 637                  AND ac.trans_id IN
 638                    (
 639                      SELECT trans_id
 640                      FROM acc_trans
 641                      JOIN chart ON (chart_id = id)
 642                      WHERE link LIKE '%AR_paid%'
 643                      $subwhere
 644                    )
 645                  $project
 646                  GROUP BY c.category
 647
 648        UNION ALL
 649
 650                  SELECT g.accno, sum(ac.amount) AS amount,
 651                  g.description, c.category
 652                  FROM acc_trans ac
 653                  JOIN chart c ON (c.id = ac.chart_id)
 654                  JOIN ap a ON (a.id = ac.trans_id)
 655                  JOIN gifi g ON (g.accno = c.gifi_accno)
 656                  $dpt_join
 657                  WHERE $where
 658                  $dpt_where
 659                  $category
 660                  AND ac.trans_id IN
 661                    (
 662                      SELECT trans_id
 663                      FROM acc_trans
 664                      JOIN chart ON (chart_id = id)
 665                      WHERE link LIKE '%AP_paid%'
 666                      $subwhere
 667                    )
 668                  $project
 669                  GROUP BY g.accno, g.description, c.category
 670
 671        UNION ALL
 672
 673                  SELECT '' AS accno, SUM(ac.amount) AS amount,
 674                  '' AS description, c.category
 675                  FROM acc_trans ac
 676                  JOIN chart c ON (c.id = ac.chart_id)
 677                  JOIN ap a ON (a.id = ac.trans_id)
 678                  $dpt_join
 679                  WHERE $where
 680                  $dpt_where
 681                  $category
 682                  AND c.gifi_accno = ''
 683                  AND ac.trans_id IN
 684                    (
 685                      SELECT trans_id
 686                      FROM acc_trans
 687                      JOIN chart ON (chart_id = id)
 688                      WHERE link LIKE '%AP_paid%'
 689                      $subwhere
 690                    )
 691                  $project
 692                  GROUP BY c.category
 693
 694        UNION ALL
 695
 696 -- add gl
 697
 698                  SELECT g.accno, sum(ac.amount) AS amount,
 699                  g.description, c.category
 700                  FROM acc_trans ac
 701                  JOIN chart c ON (c.id = ac.chart_id)
 702                  JOIN gifi g ON (g.accno = c.gifi_accno)
 703                  JOIN gl a ON (a.id = ac.trans_id)
 704                  $dpt_join
 705                  WHERE $where
 706                  $glwhere
 707                  $dpt_where
 708                  $category
 709                  AND NOT (c.link = 'AR' OR c.link = 'AP')
 710                  $project
 711                  GROUP BY g.accno, g.description, c.category
 712
 713        UNION ALL
 714
 715                  SELECT '' AS accno, SUM(ac.amount) AS amount,
 716                  '' AS description, c.category
 717                  FROM acc_trans ac
 718                  JOIN chart c ON (c.id = ac.chart_id)
 719                  JOIN gl a ON (a.id = ac.trans_id)
 720                  $dpt_join
 721                  WHERE $where
 722                  $glwhere
 723                  $dpt_where
 724                  $category
 725                  AND c.gifi_accno = ''
 726                  AND NOT (c.link = 'AR' OR c.link = 'AP')
 727                  $project
 728                  GROUP BY c.category
 729                  |;
 730
 731       if ($form->{project_id}) {
 732
 733         $query .= qq|
 734
 735        UNION ALL
 736
 737                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
 738                  g.description AS description, c.category
 739                  FROM invoice ac
 740                  JOIN ar a ON (a.id = ac.trans_id)
 741                  JOIN parts p ON (ac.parts_id = p.id)
 742                  JOIN chart c on (p.income_accno_id = c.id)
 743                  JOIN gifi g ON (g.accno = c.gifi_accno)
 744                  $dpt_join
 745         -- use transdate from subwhere
 746                  WHERE 1 = 1 $subwhere
 747                  AND c.category = 'I'
 748                  $dpt_where
 749                  AND ac.trans_id IN
 750                    (
 751                      SELECT trans_id
 752                      FROM acc_trans
 753                      JOIN chart ON (chart_id = id)
 754                      WHERE link LIKE '%AR_paid%'
 755                      $subwhere
 756                    )
 757                  $project
 758                  GROUP BY g.accno, g.description, c.category
 759
 760        UNION ALL
 761
 762                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
 763                  g.description AS description, c.category
 764                  FROM invoice ac
 765                  JOIN ap a ON (a.id = ac.trans_id)
 766                  JOIN parts p ON (ac.parts_id = p.id)
 767                  JOIN chart c on (p.expense_accno_id = c.id)
 768                  JOIN gifi g ON (g.accno = c.gifi_accno)
 769                  $dpt_join
 770                  WHERE 1 = 1 $subwhere
 771                  AND c.category = 'E'
 772                  $dpt_where
 773                  AND ac.trans_id IN
 774                    (
 775                      SELECT trans_id
 776                      FROM acc_trans
 777                      JOIN chart ON (chart_id = id)
 778                      WHERE link LIKE '%AP_paid%'
 779                      $subwhere
 780                    )
 781                  $project
 782                  GROUP BY g.accno, g.description, c.category
 783                  |;
 784       }
 785
 786     } else {
 787
 788       if ($department_id) {
 789         $dpt_join = qq|
 790               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
 791               |;
 792         $dpt_where = qq|
 793                AND t.department_id = $department_id
 794               |;
 795
 796       }
 797
 798       $query = qq|
 799
 800               SELECT g.accno, SUM(ac.amount) AS amount,
 801               g.description, c.category
 802               FROM acc_trans ac
 803               JOIN chart c ON (c.id = ac.chart_id)
 804               JOIN gifi g ON (c.gifi_accno = g.accno)
 805               $dpt_join
 806               WHERE $where
 807               $dpt_from
 808               $category
 809               $project
 810               GROUP BY g.accno, g.description, c.category
 811
 812            UNION ALL
 813
 814               SELECT '' AS accno, SUM(ac.amount) AS amount,
 815               '' AS description, c.category
 816               FROM acc_trans ac
 817               JOIN chart c ON (c.id = ac.chart_id)
 818               $dpt_join
 819               WHERE $where
 820               $dpt_from
 821               $category
 822               AND c.gifi_accno = ''
 823               $project
 824               GROUP BY c.category
 825               |;
 826
 827       if ($form->{project_id}) {
 828
 829         $query .= qq|
 830
 831          UNION ALL
 832
 833                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
 834                  g.description AS description, c.category
 835                  FROM invoice ac
 836                  JOIN ar a ON (a.id = ac.trans_id)
 837                  JOIN parts p ON (ac.parts_id = p.id)
 838                  JOIN chart c on (p.income_accno_id = c.id)
 839                  JOIN gifi g ON (c.gifi_accno = g.accno)
 840                  $dpt_join
 841         -- use transdate from subwhere
 842                  WHERE 1 = 1 $subwhere
 843                  AND c.category = 'I'
 844                  $dpt_where
 845                  $project
 846                  GROUP BY g.accno, g.description, c.category
 847
 848        UNION ALL
 849
 850                  SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
 851                  g.description AS description, c.category
 852                  FROM invoice ac
 853                  JOIN ap a ON (a.id = ac.trans_id)
 854                  JOIN parts p ON (ac.parts_id = p.id)
 855                  JOIN chart c on (p.expense_accno_id = c.id)
 856                  JOIN gifi g ON (c.gifi_accno = g.accno)
 857                  $dpt_join
 858                  WHERE 1 = 1 $subwhere
 859                  AND c.category = 'E'
 860                  $dpt_where
 861                  $project
 862                  GROUP BY g.accno, g.description, c.category
 863                  |;
 864       }
 865
 866     }
 867
 868   } else {    # standard account
 869
 870     if ($form->{method} eq 'cash') {
 871
 872       $query = qq|
 873
 874                  SELECT c.accno, sum(ac.amount) AS amount,
 875                  c.description, c.category
 876                  FROM acc_trans ac
 877                  JOIN chart c ON (c.id = ac.chart_id)
 878                  JOIN ar a ON (a.id = ac.trans_id)
 879                  $dpt_join
 880                  WHERE $where
 881                  $dpt_where
 882                  $category
 883                  AND ac.trans_id IN
 884                    (
 885                      SELECT trans_id
 886                      FROM acc_trans
 887                      JOIN chart ON (chart_id = id)
 888                      WHERE link LIKE '%AR_paid%'
 889                      $subwhere
 890                    )
 891
 892                  $project
 893                  GROUP BY c.accno, c.description, c.category
 894
 895         UNION ALL
 896
 897                  SELECT c.accno, sum(ac.amount) AS amount,
 898                  c.description, c.category
 899                  FROM acc_trans ac
 900                  JOIN chart c ON (c.id = ac.chart_id)
 901                  JOIN ap a ON (a.id = ac.trans_id)
 902                  $dpt_join
 903                  WHERE $where
 904                  $dpt_where
 905                  $category
 906                  AND ac.trans_id IN
 907                    (
 908                      SELECT trans_id
 909                      FROM acc_trans
 910                      JOIN chart ON (chart_id = id)
 911                      WHERE link LIKE '%AP_paid%'
 912                      $subwhere
 913                    )
 914
 915                  $project
 916                  GROUP BY c.accno, c.description, c.category
 917
 918         UNION ALL
 919
 920                  SELECT c.accno, sum(ac.amount) AS amount,
 921                  c.description, c.category
 922                  FROM acc_trans ac
 923                  JOIN chart c ON (c.id = ac.chart_id)
 924                  JOIN gl a ON (a.id = ac.trans_id)
 925                  $dpt_join
 926                  WHERE $where
 927                  $glwhere
 928                  $dpt_from
 929                  $category
 930                  AND NOT (c.link = 'AR' OR c.link = 'AP')
 931                  $project
 932                  GROUP BY c.accno, c.description, c.category
 933                  |;
 934
 935       if ($form->{project_id}) {
 936
 937         $query .= qq|
 938
 939          UNION ALL
 940
 941                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
 942                  c.description AS description, c.category
 943                  FROM invoice ac
 944                  JOIN ar a ON (a.id = ac.trans_id)
 945                  JOIN parts p ON (ac.parts_id = p.id)
 946                  JOIN chart c on (p.income_accno_id = c.id)
 947                  $dpt_join
 948         -- use transdate from subwhere
 949                  WHERE 1 = 1 $subwhere
 950                  AND c.category = 'I'
 951                  $dpt_where
 952                  AND ac.trans_id IN
 953                    (
 954                      SELECT trans_id
 955                      FROM acc_trans
 956                      JOIN chart ON (chart_id = id)
 957                      WHERE link LIKE '%AR_paid%'
 958                      $subwhere
 959                    )
 960
 961                  $project
 962                  GROUP BY c.accno, c.description, c.category
 963
 964          UNION ALL
 965
 966                  SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
 967                  c.description AS description, c.category
 968                  FROM invoice ac
 969                  JOIN ap a ON (a.id = ac.trans_id)
 970                  JOIN parts p ON (ac.parts_id = p.id)
 971                  JOIN chart c on (p.expense_accno_id = c.id)
 972                  $dpt_join
 973                  WHERE 1 = 1 $subwhere
 974                  AND c.category = 'E'
 975                  $dpt_where
 976                  AND ac.trans_id IN
 977                    (
 978                      SELECT trans_id
 979                      FROM acc_trans
 980                      JOIN chart ON (chart_id = id)
 981                      WHERE link LIKE '%AP_paid%'
 982                      $subwhere
 983                    )
 984
 985                  $project
 986                  GROUP BY c.accno, c.description, c.category
 987                  |;
 988       }
 989
 990     } else {
 991
 992       if ($department_id) {
 993         $dpt_join = qq|
 994               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
 995               |;
 996         $dpt_where = qq|
 997                AND t.department_id = $department_id
 998               |;
 999       }
1000
1001       $query = qq|
1002
1003                  SELECT c.accno, sum(ac.amount) AS amount,
1004                  c.description, c.category
1005                  FROM acc_trans ac
1006                  JOIN chart c ON (c.id = ac.chart_id)
1007                  $dpt_join
1008                  WHERE $where
1009                  $dpt_where
1010                  $category
1011                  $project
1012                  GROUP BY c.accno, c.description, c.category
1013                  |;
1014
1015       if ($form->{project_id}) {
1016
1017         $query .= qq|
1018
1019         UNION ALL
1020
1021                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1022                  c.description AS description, c.category
1023                  FROM invoice ac
1024                  JOIN ar a ON (a.id = ac.trans_id)
1025                  JOIN parts p ON (ac.parts_id = p.id)
1026                  JOIN chart c on (p.income_accno_id = c.id)
1027                  $dpt_join
1028         -- use transdate from subwhere
1029                  WHERE 1 = 1 $subwhere
1030                  AND c.category = 'I'
1031                  $dpt_where
1032                  $project
1033                  GROUP BY c.accno, c.description, c.category
1034
1035         UNION ALL
1036
1037                  SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
1038                  c.description AS description, c.category
1039                  FROM invoice ac
1040                  JOIN ap a ON (a.id = ac.trans_id)
1041                  JOIN parts p ON (ac.parts_id = p.id)
1042                  JOIN chart c on (p.expense_accno_id = c.id)
1043                  $dpt_join
1044                  WHERE 1 = 1 $subwhere
1045                  AND c.category = 'E'
1046                  $dpt_where
1047                  $project
1048                  GROUP BY c.accno, c.description, c.category
1049                  |;
1050
1051       }
1052     }
1053   }
1054
1055   my @accno;
1056   my $accno;
1057   my $ref;
1058
1059   my $sth = $dbh->prepare($query);
1060   $sth->execute || $form->dberror($query);
1061
1062   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1063
1064     if ($ref->{category} eq 'C') {
1065       $ref->{category} = 'A';
1066     }
1067
1068     # get last heading account
1069     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1070     $accno = pop @accno;
1071     if ($accno) {
1072       if ($last_period) {
1073         $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
1074       } else {
1075         $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
1076       }
1077     }
1078
1079     $form->{ $ref->{category} }{ $ref->{accno} }{accno}       = $ref->{accno};
1080     $form->{ $ref->{category} }{ $ref->{accno} }{description} =
1081       $ref->{description};
1082     $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
1083
1084     if ($last_period) {
1085       $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
1086     } else {
1087       $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
1088     }
1089   }
1090   $sth->finish;
1091
1092   # remove accounts with zero balance
1093   foreach $category (@{$categories}) {
1094     foreach $accno (keys %{ $form->{$category} }) {
1095       $form->{$category}{$accno}{last} =
1096         $form->round_amount($form->{$category}{$accno}{last},
1097                             $form->{decimalplaces});
1098       $form->{$category}{$accno}{this} =
1099         $form->round_amount($form->{$category}{$accno}{this},
1100                             $form->{decimalplaces});
1101
1102       delete $form->{$category}{$accno}
1103         if (   $form->{$category}{$accno}{this} == 0
1104             && $form->{$category}{$accno}{last} == 0);
1105     }
1106   }
1107
1108   $main::lxdebug->leave_sub();
1109 }
1110
1111 sub get_accounts_g {
1112   $main::lxdebug->enter_sub();
1113
1114   my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
1115
1116   my ($null, $department_id) = split /--/, $form->{department};
1117
1118   my $query;
1119   my $dpt_where;
1120   my $dpt_join;
1121   my $project;
1122   my $where    = "1 = 1";
1123   my $glwhere  = "";
1124   my $subwhere = "";
1125   my $item;
1126
1127   if ($fromdate) {
1128     if ($form->{method} eq 'cash') {
1129       $subwhere .= " AND transdate >= '$fromdate'";
1130       $glwhere = " AND ac.transdate >= '$fromdate'";
1131     } else {
1132       $where .= " AND ac.transdate >= '$fromdate'";
1133     }
1134   }
1135
1136   if ($todate) {
1137     $where    .= " AND ac.transdate <= '$todate'";
1138     $subwhere .= " AND transdate <= '$todate'";
1139   }
1140
1141   if ($department_id) {
1142     $dpt_join = qq|
1143                JOIN department t ON (a.department_id = t.id)
1144                   |;
1145     $dpt_where = qq|
1146                AND t.id = $department_id
1147                    |;
1148   }
1149
1150   if ($form->{project_id}) {
1151     $project = qq|
1152                  AND ac.project_id = $form->{project_id}
1153                  |;
1154   }
1155
1156   if ($form->{method} eq 'cash') {
1157
1158     $query = qq|
1159
1160                  SELECT sum(ac.amount) AS amount,
1161                  c.$category
1162                  FROM acc_trans ac
1163                  JOIN chart c ON (c.id = ac.chart_id)
1164                  JOIN ar a ON (a.id = ac.trans_id)
1165                  $dpt_join
1166                  WHERE $where
1167                  $dpt_where
1168                  AND ac.trans_id IN
1169                    (
1170                      SELECT trans_id
1171                      FROM acc_trans
1172                      JOIN chart ON (chart_id = id)
1173                      WHERE link LIKE '%AR_paid%'
1174                      $subwhere
1175                    )
1176
1177                  $project
1178                  GROUP BY c.$category
1179
1180         UNION
1181
1182                  SELECT sum(ac.amount) AS amount,
1183                  c.$category
1184                  FROM acc_trans ac
1185                  JOIN chart c ON (c.id = ac.chart_id)
1186                  JOIN ap a ON (a.id = ac.trans_id)
1187                  $dpt_join
1188                  WHERE $where
1189                  $dpt_where
1190                  AND ac.trans_id IN
1191                    (
1192                      SELECT trans_id
1193                      FROM acc_trans
1194                      JOIN chart ON (chart_id = id)
1195                      WHERE link LIKE '%AP_paid%'
1196                      $subwhere
1197                    )
1198
1199                  $project
1200                  GROUP BY c.$category
1201
1202         UNION
1203
1204                  SELECT sum(ac.amount) AS amount,
1205                  c.$category
1206                  FROM acc_trans ac
1207                  JOIN chart c ON (c.id = ac.chart_id)
1208                  JOIN gl a ON (a.id = ac.trans_id)
1209                  $dpt_join
1210                  WHERE $where
1211                  $glwhere
1212                  $dpt_from
1213                  AND NOT (c.link = 'AR' OR c.link = 'AP')
1214                  $project
1215                  GROUP BY c.$category
1216                  |;
1217
1218     if ($form->{project_id}) {
1219
1220       $query .= qq|
1221
1222          UNION
1223
1224                  SELECT SUM(ac.sellprice * ac.qty) AS amount,
1225                  c.$category
1226                  FROM invoice ac
1227                  JOIN ar a ON (a.id = ac.trans_id)
1228                  JOIN parts p ON (ac.parts_id = p.id)
1229                  JOIN chart c on (p.income_accno_id = c.id)
1230                  $dpt_join
1231         -- use transdate from subwhere
1232                  WHERE 1 = 1 $subwhere
1233                  AND c.category = 'I'
1234                  $dpt_where
1235                  AND ac.trans_id IN
1236                    (
1237                      SELECT trans_id
1238                      FROM acc_trans
1239                      JOIN chart ON (chart_id = id)
1240                      WHERE link LIKE '%AR_paid%'
1241                      $subwhere
1242                    )
1243
1244                  $project
1245                  GROUP BY c.$category
1246
1247          UNION
1248
1249                  SELECT SUM(ac.sellprice) AS amount,
1250                  c.$category
1251                  FROM invoice ac
1252                  JOIN ap a ON (a.id = ac.trans_id)
1253                  JOIN parts p ON (ac.parts_id = p.id)
1254                  JOIN chart c on (p.expense_accno_id = c.id)
1255                  $dpt_join
1256                  WHERE 1 = 1 $subwhere
1257                  AND c.category = 'E'
1258                  $dpt_where
1259                  AND ac.trans_id IN
1260                    (
1261                      SELECT trans_id
1262                      FROM acc_trans
1263                      JOIN chart ON (chart_id = id)
1264                      WHERE link LIKE '%AP_paid%'
1265                      $subwhere
1266                    )
1267
1268                  $project
1269                  GROUP BY c.$category
1270                  |;
1271     }
1272
1273   } else {
1274
1275     if ($department_id) {
1276       $dpt_join = qq|
1277               JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1278               |;
1279       $dpt_where = qq|
1280                AND t.department_id = $department_id
1281               |;
1282     }
1283
1284     $query = qq|
1285
1286                  SELECT sum(ac.amount) AS amount,
1287                  c.$category
1288                  FROM acc_trans ac
1289                  JOIN chart c ON (c.id = ac.chart_id)
1290                  $dpt_join
1291                  WHERE $where
1292                  $dpt_where
1293                  $project
1294                  GROUP BY c.$category
1295                  |;
1296
1297     if ($form->{project_id}) {
1298
1299       $query .= qq|
1300
1301         UNION
1302
1303                  SELECT SUM(ac.sellprice * ac.qty) AS amount,
1304                  c.$category
1305                  FROM invoice ac
1306                  JOIN ar a ON (a.id = ac.trans_id)
1307                  JOIN parts p ON (ac.parts_id = p.id)
1308                  JOIN chart c on (p.income_accno_id = c.id)
1309                  $dpt_join
1310         -- use transdate from subwhere
1311                  WHERE 1 = 1 $subwhere
1312                  AND c.category = 'I'
1313                  $dpt_where
1314                  $project
1315                  GROUP BY c.$category
1316
1317         UNION
1318
1319                  SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
1320                  c.$category
1321                  FROM invoice ac
1322                  JOIN ap a ON (a.id = ac.trans_id)
1323                  JOIN parts p ON (ac.parts_id = p.id)
1324                  JOIN chart c on (p.expense_accno_id = c.id)
1325                  $dpt_join
1326                  WHERE 1 = 1 $subwhere
1327                  AND c.category = 'E'
1328                  $dpt_where
1329                  $project
1330                  GROUP BY c.$category
1331                  |;
1332
1333     }
1334   }
1335
1336   my @accno;
1337   my $accno;
1338   my $ref;
1339
1340   my $sth = $dbh->prepare($query);
1341   $sth->execute || $form->dberror($query);
1342
1343   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1344     if ($ref->{amount} < 0) {
1345       $ref->{amount} *= -1;
1346     }
1347     if ($category eq "pos_bwa") {
1348       if ($last_period) {
1349         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
1350       } else {
1351         $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
1352       }
1353     } else {
1354       $form->{ $ref->{$category} } += $ref->{amount};
1355     }
1356   }
1357   $sth->finish;
1358
1359   $main::lxdebug->leave_sub();
1360 }
1361
1362 sub trial_balance {
1363   $main::lxdebug->enter_sub();
1364
1365   my ($self, $myconfig, $form) = @_;
1366
1367   my $dbh = $form->dbconnect($myconfig);
1368
1369   my ($query, $sth, $ref);
1370   my %balance = ();
1371   my %trb     = ();
1372   my ($null, $department_id) = split /--/, $form->{department};
1373   my @headingaccounts = ();
1374   my $dpt_where;
1375   my $dpt_join;
1376   my $project;
1377
1378   my $where    = "1 = 1";
1379   my $invwhere = $where;
1380
1381   if ($department_id) {
1382     $dpt_join = qq|
1383                 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
1384                   |;
1385     $dpt_where = qq|
1386                 AND t.department_id = $department_id
1387                 |;
1388   }
1389
1390   # project_id only applies to getting transactions
1391   # it has nothing to do with a trial balance
1392   # but we use the same function to collect information
1393
1394   if ($form->{project_id}) {
1395     $project = qq|
1396                 AND ac.project_id = $form->{project_id}
1397                 |;
1398   }
1399
1400   # get beginning balances
1401   if ($form->{fromdate}) {
1402
1403     if ($form->{accounttype} eq 'gifi') {
1404
1405       $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
1406                   g.description
1407                   FROM acc_trans ac
1408                   JOIN chart c ON (ac.chart_id = c.id)
1409                   JOIN gifi g ON (c.gifi_accno = g.accno)
1410                   $dpt_join
1411                   WHERE ac.transdate < '$form->{fromdate}'
1412                   $dpt_where
1413                   $project
1414                   GROUP BY g.accno, c.category, g.description
1415                   |;
1416
1417     } else {
1418
1419       $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
1420                   c.description
1421                   FROM acc_trans ac
1422                   JOIN chart c ON (ac.chart_id = c.id)
1423                   $dpt_join
1424                   WHERE ac.transdate < '$form->{fromdate}'
1425                   $dpt_where
1426                   $project
1427                   GROUP BY c.accno, c.category, c.description
1428                   |;
1429
1430     }
1431
1432     $sth = $dbh->prepare($query);
1433     $sth->execute || $form->dberror($query);
1434
1435     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1436       $balance{ $ref->{accno} } = $ref->{amount};
1437
1438       if ($ref->{amount} != 0 && $form->{all_accounts}) {
1439         $trb{ $ref->{accno} }{description} = $ref->{description};
1440         $trb{ $ref->{accno} }{charttype}   = 'A';
1441         $trb{ $ref->{accno} }{category}    = $ref->{category};
1442       }
1443
1444     }
1445     $sth->finish;
1446
1447   }
1448
1449   # get headings
1450   $query = qq|SELECT c.accno, c.description, c.category
1451               FROM chart c
1452               WHERE c.charttype = 'H'
1453               ORDER by c.accno|;
1454
1455   if ($form->{accounttype} eq 'gifi') {
1456     $query = qq|SELECT g.accno, g.description, c.category
1457                 FROM gifi g
1458                 JOIN chart c ON (c.gifi_accno = g.accno)
1459                 WHERE c.charttype = 'H'
1460                 ORDER BY g.accno|;
1461   }
1462
1463   $sth = $dbh->prepare($query);
1464   $sth->execute || $form->dberror($query);
1465
1466   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1467     $trb{ $ref->{accno} }{description} = $ref->{description};
1468     $trb{ $ref->{accno} }{charttype}   = 'H';
1469     $trb{ $ref->{accno} }{category}    = $ref->{category};
1470
1471     push @headingaccounts, $ref->{accno};
1472   }
1473
1474   $sth->finish;
1475
1476   $where = " 1 = 1 ";
1477
1478   if ($form->{fromdate} || $form->{todate}) {
1479     if ($form->{fromdate}) {
1480       $tofrom   .= " AND ac.transdate >= '$form->{fromdate}'";
1481       $subwhere .= " AND transdate >= '$form->{fromdate}'";
1482       $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
1483       $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
1484     }
1485     if ($form->{todate}) {
1486       $tofrom   .= " AND ac.transdate <= '$form->{todate}'";
1487       $invwhere .= " AND a.transdate <= '$form->{todate}'";
1488       $subwhere .= " AND transdate <= '$form->{todate}'";
1489       $glwhere  .= " AND ac.transdate <= '$form->{todate}'";
1490     }
1491   }
1492   if ($form->{eur}) {
1493     $where .= qq| AND ((ac.trans_id in (SELECT id from ar)
1494                   AND ac.trans_id IN
1495                    (
1496                      SELECT trans_id
1497                      FROM acc_trans
1498                      JOIN chart ON (chart_id = id)
1499                      WHERE link LIKE '%AR_paid%'
1500                      $subwhere
1501                    )) OR (ac.trans_id in (SELECT id from ap)
1502                    AND ac.trans_id IN
1503                    (
1504                      SELECT trans_id
1505                      FROM acc_trans
1506                      JOIN chart ON (chart_id = id)
1507                      WHERE link LIKE '%AP_paid%'
1508                      $subwhere
1509                    )) OR (ac.trans_id in (SELECT id from gl)
1510                    $glwhere))|;
1511   } else {
1512     $where .= $tofrom;
1513   }
1514
1515   if ($form->{accounttype} eq 'gifi') {
1516
1517     $query = qq|SELECT g.accno, g.description, c.category,
1518                 SUM(ac.amount) AS amount
1519                 FROM acc_trans ac
1520                 JOIN chart c ON (c.id = ac.chart_id)
1521                 JOIN gifi g ON (c.gifi_accno = g.accno)
1522                 $dpt_join
1523                 WHERE $where
1524                 $dpt_where
1525                 $project
1526                 GROUP BY g.accno, g.description, c.category
1527                 |;
1528
1529     if ($form->{project_id}) {
1530
1531       $query .= qq|
1532
1533         -- add project transactions from invoice
1534
1535         UNION ALL
1536
1537                 SELECT g.accno, g.description, c.category,
1538                 SUM(ac.sellprice * ac.qty) AS amount
1539                 FROM invoice ac
1540                 JOIN ar a ON (ac.trans_id = a.id)
1541                 JOIN parts p ON (ac.parts_id = p.id)
1542                 JOIN chart c ON (p.income_accno_id = c.id)
1543                 JOIN gifi g ON (c.gifi_accno = g.accno)
1544                 $dpt_join
1545                 WHERE $invwhere
1546                 $dpt_where
1547                 $project
1548                 GROUP BY g.accno, g.description, c.category
1549
1550         UNION ALL
1551
1552                 SELECT g.accno, g.description, c.category,
1553                 SUM(ac.sellprice * ac.qty) * -1 AS amount
1554                 FROM invoice ac
1555                 JOIN ap a ON (ac.trans_id = a.id)
1556                 JOIN parts p ON (ac.parts_id = p.id)
1557                 JOIN chart c ON (p.expense_accno_id = c.id)
1558                 JOIN gifi g ON (c.gifi_accno = g.accno)
1559                 $dpt_join
1560                 WHERE $invwhere
1561                 $dpt_where
1562                 $project
1563                 GROUP BY g.accno, g.description, c.category
1564                 |;
1565     }
1566
1567     $query .= qq|
1568                 ORDER BY accno|;
1569
1570   } else {
1571
1572     $query = qq|SELECT c.accno, c.description, c.category,
1573                 SUM(ac.amount) AS amount
1574                 FROM acc_trans ac
1575                 JOIN chart c ON (c.id = ac.chart_id)
1576                 $dpt_join
1577                 WHERE $where
1578                 $dpt_where
1579                 $project
1580                 GROUP BY c.accno, c.description, c.category
1581                 |;
1582
1583     if ($form->{project_id}) {
1584
1585       $query .= qq|
1586
1587         -- add project transactions from invoice
1588
1589         UNION ALL
1590
1591                 SELECT c.accno, c.description, c.category,
1592                 SUM(ac.sellprice * ac.qty) AS amount
1593                 FROM invoice ac
1594                 JOIN ar a ON (ac.trans_id = a.id)
1595                 JOIN parts p ON (ac.parts_id = p.id)
1596                 JOIN chart c ON (p.income_accno_id = c.id)
1597                 $dpt_join
1598                 WHERE $invwhere
1599                 $dpt_where
1600                 $project
1601                 GROUP BY c.accno, c.description, c.category
1602
1603         UNION ALL
1604
1605                 SELECT c.accno, c.description, c.category,
1606                 SUM(ac.sellprice * ac.qty) * -1 AS amount
1607                 FROM invoice ac
1608                 JOIN ap a ON (ac.trans_id = a.id)
1609                 JOIN parts p ON (ac.parts_id = p.id)
1610                 JOIN chart c ON (p.expense_accno_id = c.id)
1611                 $dpt_join
1612                 WHERE $invwhere
1613                 $dpt_where
1614                 $project
1615                 GROUP BY c.accno, c.description, c.category
1616                 |;
1617     }
1618
1619     $query .= qq|
1620                 ORDER BY accno|;
1621
1622   }
1623
1624   $sth = $dbh->prepare($query);
1625   $sth->execute || $form->dberror($query);
1626
1627   # prepare query for each account
1628   $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1629               FROM acc_trans ac
1630               JOIN chart c ON (c.id = ac.chart_id)
1631               $dpt_join
1632               WHERE $where
1633               $dpt_where
1634               $project
1635               AND ac.amount < 0
1636               AND c.accno = ?) AS debit,
1637
1638              (SELECT SUM(ac.amount)
1639               FROM acc_trans ac
1640               JOIN chart c ON (c.id = ac.chart_id)
1641               $dpt_join
1642               WHERE $where
1643               $dpt_where
1644               $project
1645               AND ac.amount > 0
1646               AND c.accno = ?) AS credit
1647               |;
1648
1649   if ($form->{accounttype} eq 'gifi') {
1650
1651     $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1652                 FROM acc_trans ac
1653                 JOIN chart c ON (c.id = ac.chart_id)
1654                 $dpt_join
1655                 WHERE $where
1656                 $dpt_where
1657                 $project
1658                 AND ac.amount < 0
1659                 AND c.gifi_accno = ?) AS debit,
1660
1661                (SELECT SUM(ac.amount)
1662                 FROM acc_trans ac
1663                 JOIN chart c ON (c.id = ac.chart_id)
1664                 $dpt_join
1665                 WHERE $where
1666                 $dpt_where
1667                 $project
1668                 AND ac.amount > 0
1669                 AND c.gifi_accno = ?) AS credit|;
1670
1671   }
1672
1673   $drcr = $dbh->prepare($query);
1674
1675   if ($form->{project_id}) {
1676
1677     # prepare query for each account
1678     $query = qq|SELECT (SELECT SUM(ac.sellprice * ac.qty) * -1
1679               FROM invoice ac
1680               JOIN parts p ON (ac.parts_id = p.id)
1681               JOIN ap a ON (ac.trans_id = a.id)
1682               JOIN chart c ON (p.expense_accno_id = c.id)
1683               $dpt_join
1684               WHERE $invwhere
1685               $dpt_where
1686               $project
1687               AND c.accno = ?) AS debit,
1688
1689              (SELECT SUM(ac.sellprice * ac.qty)
1690               FROM invoice ac
1691               JOIN parts p ON (ac.parts_id = p.id)
1692               JOIN ar a ON (ac.trans_id = a.id)
1693               JOIN chart c ON (p.income_accno_id = c.id)
1694               $dpt_join
1695               WHERE $invwhere
1696               $dpt_where
1697               $project
1698               AND c.accno = ?) AS credit
1699               |;
1700
1701     $project_drcr = $dbh->prepare($query);
1702
1703   }
1704
1705   # calculate the debit and credit in the period
1706   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1707     $trb{ $ref->{accno} }{description} = $ref->{description};
1708     $trb{ $ref->{accno} }{charttype}   = 'A';
1709     $trb{ $ref->{accno} }{category}    = $ref->{category};
1710     $trb{ $ref->{accno} }{amount} += $ref->{amount};
1711   }
1712   $sth->finish;
1713
1714   my ($debit, $credit);
1715
1716   foreach my $accno (sort keys %trb) {
1717     $ref = ();
1718
1719     $ref->{accno} = $accno;
1720     map { $ref->{$_} = $trb{$accno}{$_} }
1721       qw(description category charttype amount);
1722
1723     $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1724
1725     if ($trb{$accno}{charttype} eq 'A') {
1726
1727       # get DR/CR
1728       $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1729
1730       ($debit, $credit) = (0, 0);
1731       while (($debit, $credit) = $drcr->fetchrow_array) {
1732         $ref->{debit}  += $debit;
1733         $ref->{credit} += $credit;
1734       }
1735       $drcr->finish;
1736
1737       if ($form->{project_id}) {
1738
1739         # get DR/CR
1740         $project_drcr->execute($ref->{accno}, $ref->{accno})
1741           || $form->dberror($query);
1742
1743         ($debit, $credit) = (0, 0);
1744         while (($debit, $credit) = $project_drcr->fetchrow_array) {
1745           $ref->{debit}  += $debit;
1746           $ref->{credit} += $credit;
1747         }
1748         $project_drcr->finish;
1749       }
1750
1751       $ref->{debit}  = $form->round_amount($ref->{debit},  2);
1752       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1753
1754     }
1755
1756     # add subtotal
1757     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1758     $accno = pop @accno;
1759     if ($accno) {
1760       $trb{$accno}{debit}  += $ref->{debit};
1761       $trb{$accno}{credit} += $ref->{credit};
1762     }
1763
1764     push @{ $form->{TB} }, $ref;
1765
1766   }
1767
1768   $dbh->disconnect;
1769
1770   # debits and credits for headings
1771   foreach $accno (@headingaccounts) {
1772     foreach $ref (@{ $form->{TB} }) {
1773       if ($accno eq $ref->{accno}) {
1774         $ref->{debit}  = $trb{$accno}{debit};
1775         $ref->{credit} = $trb{$accno}{credit};
1776       }
1777     }
1778   }
1779
1780   $main::lxdebug->leave_sub();
1781 }
1782
1783 sub aging {
1784   $main::lxdebug->enter_sub();
1785
1786   my ($self, $myconfig, $form) = @_;
1787
1788   # connect to database
1789   my $dbh     = $form->dbconnect($myconfig);
1790   my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1791
1792   $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1793
1794   my $where = "1 = 1";
1795   my ($name, $null);
1796
1797   if ($form->{"$form->{ct}_id"}) {
1798     $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1799   } else {
1800     if ($form->{ $form->{ct} }) {
1801       $name = $form->like(lc $form->{ $form->{ct} });
1802       $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{ $form->{ct} };
1803     }
1804   }
1805
1806   my $dpt_join;
1807   if ($form->{department}) {
1808     ($null, $department_id) = split /--/, $form->{department};
1809     $dpt_join = qq|
1810                JOIN department d ON (a.department_id = d.id)
1811                   |;
1812
1813     $where .= qq| AND a.department_id = $department_id|;
1814   }
1815
1816   # select outstanding vendors or customers, depends on $ct
1817   my $query = qq|SELECT DISTINCT ct.id, ct.name
1818                  FROM $form->{ct} ct, $form->{arap} a
1819                  $dpt_join
1820                  WHERE $where
1821                  AND a.$form->{ct}_id = ct.id
1822                  AND a.paid != a.amount
1823                  AND (a.transdate <= '$form->{todate}')
1824                  ORDER BY ct.name|;
1825
1826   my $sth = $dbh->prepare($query);
1827   $sth->execute || $form->dberror;
1828
1829   my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1830
1831   # for each company that has some stuff outstanding
1832   while (my ($id) = $sth->fetchrow_array) {
1833
1834     $query = qq|
1835
1836 -- between 0-30 days
1837
1838         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1839         street, zipcode, city, country, contact, email,
1840         phone as customerphone, fax as customerfax, $form->{ct}number,
1841         "invnumber", "transdate",
1842         (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1843         "duedate", invoice, $form->{arap}.id,
1844           (SELECT $buysell FROM exchangerate
1845            WHERE $form->{arap}.curr = exchangerate.curr
1846            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1847   FROM $form->{arap}, $form->{ct}
1848         WHERE paid != amount
1849         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1850         AND $form->{ct}.id = $id
1851         AND (
1852                 transdate <= (date '$form->{todate}' - interval '0 days')
1853                 AND transdate >= (date '$form->{todate}' - interval '30 days')
1854             )
1855
1856         UNION
1857
1858 -- between 31-60 days
1859
1860         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1861         street, zipcode, city, country, contact, email,
1862         phone as customerphone, fax as customerfax, $form->{ct}number,
1863         "invnumber", "transdate",
1864         0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1865         "duedate", invoice, $form->{arap}.id,
1866           (SELECT $buysell FROM exchangerate
1867            WHERE $form->{arap}.curr = exchangerate.curr
1868            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1869   FROM $form->{arap}, $form->{ct}
1870         WHERE paid != amount
1871         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1872         AND $form->{ct}.id = $id
1873         AND (
1874                 transdate < (date '$form->{todate}' - interval '30 days')
1875                 AND transdate >= (date '$form->{todate}' - interval '60 days')
1876                 )
1877
1878         UNION
1879
1880 -- between 61-90 days
1881
1882         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1883         street, zipcode, city, country, contact, email,
1884         phone as customerphone, fax as customerfax, $form->{ct}number,
1885         "invnumber", "transdate",
1886         0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1887         "duedate", invoice, $form->{arap}.id,
1888           (SELECT $buysell FROM exchangerate
1889            WHERE $form->{arap}.curr = exchangerate.curr
1890            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1891         FROM $form->{arap}, $form->{ct}
1892         WHERE paid != amount
1893         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1894         AND $form->{ct}.id = $id
1895         AND (
1896                 transdate < (date '$form->{todate}' - interval '60 days')
1897                 AND transdate >= (date '$form->{todate}' - interval '90 days')
1898                 )
1899
1900         UNION
1901
1902 -- over 90 days
1903
1904         SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1905         street, zipcode, city, country, contact, email,
1906         phone as customerphone, fax as customerfax, $form->{ct}number,
1907         "invnumber", "transdate",
1908         0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1909         "duedate", invoice, $form->{arap}.id,
1910           (SELECT $buysell FROM exchangerate
1911            WHERE $form->{arap}.curr = exchangerate.curr
1912            AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1913         FROM $form->{arap}, $form->{ct}
1914         WHERE paid != amount
1915         AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1916         AND $form->{ct}.id = $id
1917         AND transdate < (date '$form->{todate}' - interval '90 days')
1918
1919         ORDER BY
1920
1921   ctid, transdate, invnumber
1922
1923                 |;
1924
1925     my $sth = $dbh->prepare($query);
1926     $sth->execute || $form->dberror;
1927
1928     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1929       $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1930       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1931       push @{ $form->{AG} }, $ref;
1932     }
1933
1934     $sth->finish;
1935
1936   }
1937
1938   $sth->finish;
1939
1940   # disconnect
1941   $dbh->disconnect;
1942
1943   $main::lxdebug->leave_sub();
1944 }
1945
1946 sub get_customer {
1947   $main::lxdebug->enter_sub();
1948
1949   my ($self, $myconfig, $form) = @_;
1950
1951   # connect to database
1952   my $dbh = $form->dbconnect($myconfig);
1953
1954   my $query = qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1955                  FROM $form->{ct} ct
1956                  WHERE ct.id = $form->{"$form->{ct}_id"}|;
1957   my $sth = $dbh->prepare($query);
1958   $sth->execute || $form->dberror;
1959
1960   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1961     $sth->fetchrow_array;
1962   $sth->finish;
1963   $dbh->disconnect;
1964
1965   $main::lxdebug->leave_sub();
1966 }
1967
1968 sub get_taxaccounts {
1969   $main::lxdebug->enter_sub();
1970
1971   my ($self, $myconfig, $form) = @_;
1972
1973   # connect to database
1974   my $dbh = $form->dbconnect($myconfig);
1975
1976   # get tax accounts
1977   my $query = qq|SELECT c.accno, c.description, t.rate
1978                  FROM chart c, tax t
1979                  WHERE c.link LIKE '%CT_tax%'
1980                  AND c.id = t.chart_id
1981                  ORDER BY c.accno|;
1982   my $sth = $dbh->prepare($query);
1983   $sth->execute || $form->dberror;
1984
1985   my $ref = ();
1986   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1987     push @{ $form->{taxaccounts} }, $ref;
1988   }
1989   $sth->finish;
1990
1991   # get gifi tax accounts
1992   my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description,
1993                  sum(t.rate) AS rate
1994                  FROM gifi g, chart c, tax t
1995                  WHERE g.accno = c.gifi_accno
1996                  AND c.id = t.chart_id
1997                  AND c.link LIKE '%CT_tax%'
1998                  GROUP BY g.accno, g.description
1999                  ORDER BY accno|;
2000   my $sth = $dbh->prepare($query);
2001   $sth->execute || $form->dberror;
2002
2003   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
2004     push @{ $form->{gifi_taxaccounts} }, $ref;
2005   }
2006   $sth->finish;
2007
2008   $dbh->disconnect;
2009
2010   $main::lxdebug->leave_sub();
2011 }
2012
2013 sub tax_report {
2014   $main::lxdebug->enter_sub();
2015
2016   my ($self, $myconfig, $form) = @_;
2017
2018   # connect to database
2019   my $dbh = $form->dbconnect($myconfig);
2020
2021   my ($null, $department_id) = split /--/, $form->{department};
2022
2023   # build WHERE
2024   my $where = "1 = 1";
2025
2026   if ($department_id) {
2027     $where .= qq|
2028                  AND a.department_id = $department_id
2029                 |;
2030   }
2031
2032   my ($accno, $rate);
2033
2034   if ($form->{accno}) {
2035     if ($form->{accno} =~ /^gifi_/) {
2036       ($null, $accno) = split /_/, $form->{accno};
2037       $rate  = $form->{"$form->{accno}_rate"};
2038       $accno = qq| AND ch.gifi_accno = '$accno'|;
2039     } else {
2040       $accno = $form->{accno};
2041       $rate  = $form->{"$form->{accno}_rate"};
2042       $accno = qq| AND ch.accno = '$accno'|;
2043     }
2044   }
2045   $rate *= 1;
2046
2047   my ($table, $ARAP);
2048
2049   if ($form->{db} eq 'ar') {
2050     $table = "customer";
2051     $ARAP  = "AR";
2052   }
2053   if ($form->{db} eq 'ap') {
2054     $table = "vendor";
2055     $ARAP  = "AP";
2056   }
2057
2058   my $transdate = "a.transdate";
2059
2060   if ($form->{method} eq 'cash') {
2061     $transdate = "a.datepaid";
2062
2063     my $todate =
2064       ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
2065
2066     $where .= qq|
2067                  AND ac.trans_id IN
2068                    (
2069                      SELECT trans_id
2070                      FROM acc_trans
2071                      JOIN chart ON (chart_id = id)
2072                      WHERE link LIKE '%${ARAP}_paid%'
2073                      AND transdate <= '$todate'
2074                    )
2075                   |;
2076   }
2077
2078   # if there are any dates construct a where
2079   if ($form->{fromdate} || $form->{todate}) {
2080     if ($form->{fromdate}) {
2081       $where .= " AND $transdate >= '$form->{fromdate}'";
2082     }
2083     if ($form->{todate}) {
2084       $where .= " AND $transdate <= '$form->{todate}'";
2085     }
2086   }
2087
2088   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
2089
2090   my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
2091   $sortorder = $form->{sort} unless $sortorder;
2092
2093   $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2094               a.invnumber, n.name, a.netamount,
2095               ac.amount * $ml AS tax
2096               FROM acc_trans ac
2097             JOIN $form->{db} a ON (a.id = ac.trans_id)
2098             JOIN chart ch ON (ch.id = ac.chart_id)
2099             JOIN $table n ON (n.id = a.${table}_id)
2100               WHERE $where
2101               $accno
2102               AND a.invoice = '0'
2103             UNION
2104               SELECT a.id, '1' AS invoice, $transdate AS transdate,
2105               a.invnumber, n.name, i.sellprice * i.qty AS netamount,
2106               i.sellprice * i.qty * $rate * $ml AS tax
2107               FROM acc_trans ac
2108             JOIN $form->{db} a ON (a.id = ac.trans_id)
2109             JOIN chart ch ON (ch.id = ac.chart_id)
2110             JOIN $table n ON (n.id = a.${table}_id)
2111             JOIN ${table}tax t ON (t.${table}_id = n.id)
2112             JOIN invoice i ON (i.trans_id = a.id)
2113             JOIN partstax p ON (p.parts_id = i.parts_id)
2114               WHERE $where
2115               $accno
2116               AND a.invoice = '1'
2117               ORDER by $sortorder|;
2118
2119   if ($form->{report} =~ /nontaxable/) {
2120
2121     # only gather up non-taxable transactions
2122     $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2123                 a.invnumber, n.name, a.netamount
2124                 FROM acc_trans ac
2125               JOIN $form->{db} a ON (a.id = ac.trans_id)
2126               JOIN $table n ON (n.id = a.${table}_id)
2127                 WHERE $where
2128                 AND a.invoice = '0'
2129                 AND a.netamount = a.amount
2130               UNION
2131                 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2132                 a.invnumber, n.name, i.sellprice * i.qty AS netamount
2133                 FROM acc_trans ac
2134               JOIN $form->{db} a ON (a.id = ac.trans_id)
2135               JOIN $table n ON (n.id = a.${table}_id)
2136               JOIN invoice i ON (i.trans_id = a.id)
2137                 WHERE $where
2138                 AND a.invoice = '1'
2139                 AND (
2140                   a.${table}_id NOT IN (
2141                         SELECT ${table}_id FROM ${table}tax t (${table}_id)
2142                                        ) OR
2143                   i.parts_id NOT IN (
2144                         SELECT parts_id FROM partstax p (parts_id)
2145                                     )
2146                     )
2147                 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
2148                 ORDER by $sortorder|;
2149   }
2150
2151   my $sth = $dbh->prepare($query);
2152   $sth->execute || $form->dberror($query);
2153
2154   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2155     push @{ $form->{TR} }, $ref;
2156   }
2157
2158   $sth->finish;
2159   $dbh->disconnect;
2160
2161   $main::lxdebug->leave_sub();
2162 }
2163
2164 sub paymentaccounts {
2165   $main::lxdebug->enter_sub();
2166
2167   my ($self, $myconfig, $form) = @_;
2168
2169   # connect to database, turn AutoCommit off
2170   my $dbh = $form->dbconnect_noauto($myconfig);
2171
2172   my $ARAP = uc $form->{db};
2173
2174   # get A(R|P)_paid accounts
2175   my $query = qq|SELECT c.accno, c.description
2176                  FROM chart c
2177                  WHERE c.link LIKE '%${ARAP}_paid%'|;
2178   my $sth = $dbh->prepare($query);
2179   $sth->execute || $form->dberror($query);
2180
2181   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2182     push @{ $form->{PR} }, $ref;
2183   }
2184
2185   $sth->finish;
2186   $dbh->disconnect;
2187
2188   $main::lxdebug->leave_sub();
2189 }
2190
2191 sub payments {
2192   $main::lxdebug->enter_sub();
2193
2194   my ($self, $myconfig, $form) = @_;
2195
2196   # connect to database, turn AutoCommit off
2197   my $dbh = $form->dbconnect_noauto($myconfig);
2198
2199   my $ml = 1;
2200   if ($form->{db} eq 'ar') {
2201     $table = 'customer';
2202     $ml    = -1;
2203   }
2204   if ($form->{db} eq 'ap') {
2205     $table = 'vendor';
2206   }
2207
2208   my ($query, $sth);
2209   my $dpt_join;
2210   my $where;
2211
2212   if ($form->{department_id}) {
2213     $dpt_join = qq|
2214                  JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
2215                  |;
2216
2217     $where = qq|
2218                  AND t.department_id = $form->{department_id}
2219                 |;
2220   }
2221
2222   if ($form->{fromdate}) {
2223     $where .= " AND ac.transdate >= '$form->{fromdate}'";
2224   }
2225   if ($form->{todate}) {
2226     $where .= " AND ac.transdate <= '$form->{todate}'";
2227   }
2228   if (!$form->{fx_transaction}) {
2229     $where .= " AND ac.fx_transaction = '0'";
2230   }
2231
2232   my $invnumber;
2233   my $reference;
2234   if ($form->{reference}) {
2235     $reference = $form->like(lc $form->{reference});
2236     $invnumber = " AND lower(a.invnumber) LIKE '$reference'";
2237     $reference = " AND lower(g.reference) LIKE '$reference'";
2238   }
2239   if ($form->{source}) {
2240     my $source = $form->like(lc $form->{source});
2241     $where .= " AND lower(ac.source) LIKE '$source'";
2242   }
2243   if ($form->{memo}) {
2244     my $memo = $form->like(lc $form->{memo});
2245     $where .= " AND lower(ac.memo) LIKE '$memo'";
2246   }
2247
2248   my $sortorder = join ', ',
2249     $form->sort_columns(qw(name invnumber ordnumber transdate source));
2250
2251   # cycle through each id
2252   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
2253
2254     $query = qq|SELECT c.id, c.accno, c.description
2255                 FROM chart c
2256                 WHERE c.accno = '$accno'|;
2257     $sth = $dbh->prepare($query);
2258     $sth->execute || $form->dberror($query);
2259
2260     my $ref = $sth->fetchrow_hashref(NAME_lc);
2261     push @{ $form->{PR} }, $ref;
2262     $sth->finish;
2263
2264     $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
2265                 ac.transdate, ac.amount * $ml AS paid, ac.source,
2266                 a.invoice, a.id, ac.memo, '$form->{db}' AS module
2267                 FROM acc_trans ac
2268                 JOIN $form->{db} a ON (ac.trans_id = a.id)
2269                 JOIN $table c ON (c.id = a.${table}_id)
2270                 $dpt_join
2271                 WHERE ac.chart_id = $ref->{id}
2272                 $where
2273                 $invnumber
2274
2275         UNION
2276                 SELECT g.description, g.reference, NULL AS ordnumber,
2277                 ac.transdate, ac.amount * $ml AS paid, ac.source,
2278                 '0' as invoice, g.id, ac.memo, 'gl' AS module
2279                 FROM acc_trans ac
2280                 JOIN gl g ON (g.id = ac.trans_id)
2281                 $dpt_join
2282                 WHERE ac.chart_id = $ref->{id}
2283                 $where
2284                 $reference
2285                 AND (ac.amount * $ml) > 0
2286                 ORDER BY $sortorder|;
2287
2288     $sth = $dbh->prepare($query);
2289     $sth->execute || $form->dberror($query);
2290
2291     while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
2292       push @{ $form->{ $ref->{id} } }, $pr;
2293     }
2294     $sth->finish;
2295
2296   }
2297
2298   $dbh->disconnect;
2299
2300   $main::lxdebug->leave_sub();
2301 }
2302
2303 sub bwa {
2304   $main::lxdebug->enter_sub();
2305
2306   my ($self, $myconfig, $form) = @_;
2307
2308   # connect to database
2309   my $dbh = $form->dbconnect($myconfig);
2310
2311   my $last_period = 0;
2312   my $category    = "pos_bwa";
2313   my @categories  =
2314     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);
2315
2316   $form->{decimalplaces} *= 1;
2317
2318   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2319                   $form, $category);
2320
2321   # if there are any compare dates
2322   if ($form->{fromdate} || $form->{todate}) {
2323     $last_period = 1;
2324     if ($form->{fromdate}) {
2325       $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2326       $year = $1;
2327     } else {
2328       $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2329       $year = $1;
2330     }
2331     $kummfromdate = $form->{comparefromdate};
2332     $kummtodate   = $form->{comparetodate};
2333     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form,
2334                     $category);
2335   }
2336
2337   @periods        = qw(jetzt kumm);
2338   @gesamtleistung = qw(1 2 3);
2339   @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
2340   @ergebnisse     =
2341     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
2342
2343   foreach $key (@periods) {
2344     $form->{ "$key" . "gesamtleistung" } = 0;
2345     $form->{ "$key" . "gesamtkosten" }   = 0;
2346     foreach $kosten (@gesamtkosten) {
2347       $form->{$kosten}{$key} *= -1;
2348     }
2349     $form->{4}{$key} *= -1;
2350
2351     foreach $category (@categories) {
2352
2353       if (defined($form->{$category}{$key})) {
2354         $form->{"$key$category"} =
2355           $form->format_amount($myconfig,
2356                                $form->round_amount($form->{$category}{$key}, 2
2357                                ));
2358       }
2359     }
2360     foreach $item (@gesamtleistung) {
2361       $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
2362     }
2363     foreach $item (@gesamtkosten) {
2364       $form->{$item}{$key} *= -1;
2365       $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
2366     }
2367     $form->{ "$key" . "rohertrag" } =
2368       $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
2369     $form->{ "$key" . "betriebrohertrag" } =
2370       $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
2371     $form->{ "$key" . "betriebsergebnis" } =
2372       $form->{ "$key" . "betriebrohertrag" } -
2373       $form->{ "$key" . "gesamtkosten" };
2374     $form->{ "$key" . "neutraleraufwand" } =
2375       $form->{30}{$key} + $form->{31}{$key};
2376     $form->{ "$key" . "neutralertrag" } =
2377       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
2378     $form->{ "$key" . "ergebnisvorsteuern" } =
2379       $form->{ "$key" . "betriebsergebnis" } -
2380       ($form->{ "$key" . "neutraleraufwand" } +
2381        $form->{ "$key" . "neutralertrag" });
2382     $form->{ "$key" . "ergebnis" } =
2383       $form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
2384
2385     if ($form->{ "$key" . "gesamtleistung" } > 0) {
2386       foreach $category (@categories) {
2387         if (defined($form->{$category}{$key})) {
2388           $form->{ "$key" . "gl" . "$category" } =
2389             $form->format_amount(
2390                                $myconfig,
2391                                $form->round_amount(
2392                                  ($form->{$category}{$key} /
2393                                     $form->{ "$key" . "gesamtleistung" } * 100
2394                                  ),
2395                                  2
2396                                ));
2397         }
2398       }
2399       foreach $item (@ergebnisse) {
2400         $form->{ "$key" . "gl" . "$item" } =
2401           $form->format_amount($myconfig,
2402                                $form->round_amount(
2403                                  ( $form->{ "$key" . "$item" } /
2404                                      $form->{ "$key" . "gesamtleistung" } * 100
2405                                  ),
2406                                  2
2407                                ));
2408       }
2409     }
2410
2411     if ($form->{ "$key" . "gesamtkosten" } > 0) {
2412       foreach $category (@categories) {
2413         if (defined($form->{$category}{$key})) {
2414           $form->{ "$key" . "gk" . "$category" } =
2415             $form->format_amount($myconfig,
2416                                  $form->round_amount(
2417                                    ($form->{$category}{$key} /
2418                                       $form->{ "$key" . "gesamtkosten" } * 100
2419                                    ),
2420                                    2
2421                                  ));
2422         }
2423       }
2424       foreach $item (@ergebnisse) {
2425         $form->{ "$key" . "gk" . "$item" } =
2426           $form->format_amount($myconfig,
2427                                $form->round_amount(
2428                                    ($form->{ "$key" . "$item" } /
2429                                       $form->{ "$key" . "gesamtkosten" } * 100
2430                                    ),
2431                                    2
2432                                ));
2433       }
2434     }
2435
2436     if ($form->{10}{$key} > 0) {
2437       foreach $category (@categories) {
2438         if (defined($form->{$category}{$key})) {
2439           $form->{ "$key" . "pk" . "$category" } =
2440             $form->format_amount(
2441                       $myconfig,
2442                       $form->round_amount(
2443                         ($form->{$category}{$key} / $form->{10}{$key} * 100), 2
2444                       ));
2445         }
2446       }
2447       foreach $item (@ergebnisse) {
2448         $form->{ "$key" . "pk" . "$item" } =
2449           $form->format_amount($myconfig,
2450                                $form->round_amount(
2451                                                 ($form->{ "$key" . "$item" } /
2452                                                    $form->{10}{$key} * 100
2453                                                 ),
2454                                                 2
2455                                ));
2456       }
2457     }
2458
2459     if ($form->{4}{$key} > 0) {
2460       foreach $category (@categories) {
2461         if (defined($form->{$category}{$key})) {
2462           $form->{ "$key" . "auf" . "$category" } =
2463             $form->format_amount(
2464                        $myconfig,
2465                        $form->round_amount(
2466                          ($form->{$category}{$key} / $form->{4}{$key} * 100), 2
2467                        ));
2468         }
2469       }
2470       foreach $item (@ergebnisse) {
2471         $form->{ "$key" . "auf" . "$item" } =
2472           $form->format_amount($myconfig,
2473                                $form->round_amount(
2474                                                 ($form->{ "$key" . "$item" } /
2475                                                    $form->{4}{$key} * 100
2476                                                 ),
2477                                                 2
2478                                ));
2479       }
2480     }
2481
2482     foreach $item (@ergebnisse) {
2483       $form->{ "$key" . "$item" } =
2484         $form->format_amount($myconfig,
2485                              $form->round_amount($form->{ "$key" . "$item" }, 2
2486                              ));
2487     }
2488
2489   }
2490   $dbh->disconnect;
2491
2492   $main::lxdebug->leave_sub();
2493 }
2494
2495 sub ustva {
2496   $main::lxdebug->enter_sub();
2497
2498   my ($self, $myconfig, $form) = @_;
2499
2500   # connect to database
2501   my $dbh = $form->dbconnect($myconfig);
2502
2503   my $last_period     = 0;
2504   my $category        = "pos_ustva";
2505   my @categories_cent = qw(51r 86r 97r 93r 96 66 43 45 53 62 65 67);
2506   my @categories_euro = qw(48 51 86 91 97 93 94);
2507   $form->{decimalplaces} *= 1;
2508
2509   foreach $item (@categories_cent) {
2510     $form->{"$item"} = 0;
2511   }
2512   foreach $item (@categories_euro) {
2513     $form->{"$item"} = 0;
2514   }
2515
2516   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2517                   $form, $category);
2518
2519   #   foreach $item (@categories_cent) {
2520   #     if ($form->{$item}{"jetzt"} > 0) {
2521   #             $form->{$item} = $form->{$item}{"jetzt"};
2522   #             delete $form->{$item}{"jetzt"};
2523   #     }
2524   #   }
2525   #   foreach $item (@categories_euro) {
2526   #     if ($form->{$item}{"jetzt"} > 0) {
2527   #             $form->{$item} = $form->{$item}{"jetzt"};
2528   #             delete $form->{$item}{"jetzt"};
2529   #     }  foreach $item (@categories_cent) {
2530   #     if ($form->{$item}{"jetzt"} > 0) {
2531   #             $form->{$item} = $form->{$item}{"jetzt"};
2532   #             delete $form->{$item}{"jetzt"};
2533   #     }
2534   #   }
2535   #   foreach $item (@categories_euro) {
2536   #     if ($form->{$item}{"jetzt"} > 0) {
2537   #             $form->{$item} = $form->{$item}{"jetzt"};
2538   #             delete $form->{$item}{"jetzt"};
2539   #     }
2540   #   }
2541   #
2542   #    }
2543
2544   $form->{"51r"} = $form->{"51"} * 0.16;
2545   $form->{"86r"} = $form->{"86"} * 0.07;
2546   $form->{"97r"} = $form->{"97"} * 0.16;
2547   $form->{"93r"} = $form->{"93"} * 0.07;
2548   $form->{"96"}  = $form->{"94"} * 0.16;
2549   $form->{"43"}  =
2550     $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
2551     $form->{"96"};
2552   $form->{"45"} = $form->{"43"};
2553   $form->{"53"} = $form->{"43"};
2554   $form->{"62"} = $form->{"43"} - $form->{"66"};
2555   $form->{"65"} = $form->{"43"} - $form->{"66"};
2556   $form->{"67"} = $form->{"43"} - $form->{"66"};
2557
2558   foreach $item (@categories_cent) {
2559     $form->{$item} =
2560       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2561   }
2562
2563   foreach $item (@categories_euro) {
2564     $form->{$item} =
2565       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0));
2566   }
2567
2568   $dbh->disconnect;
2569
2570   $main::lxdebug->leave_sub();
2571 }
2572
2573 sub income_statement {
2574   $main::lxdebug->enter_sub();
2575
2576   my ($self, $myconfig, $form) = @_;
2577
2578   # connect to database
2579   my $dbh = $form->dbconnect($myconfig);
2580
2581   my $last_period          = 0;
2582   my $category             = "pos_eur";
2583   my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2584   my @categories_ausgaben  =
2585     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);
2586
2587   my @ergebnisse = qw(sumeura sumeurb guvsumme);
2588
2589   $form->{decimalplaces} *= 1;
2590
2591   foreach $item (@categories_einnahmen) {
2592     $form->{$item} = 0;
2593   }
2594   foreach $item (@categories_ausgaben) {
2595     $form->{$item} = 0;
2596   }
2597
2598   foreach $item (@ergebnisse) {
2599     $form->{$item} = 0;
2600   }
2601
2602   &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2603                   $form, $category);
2604
2605   foreach $item (@categories_einnahmen) {
2606     $form->{"eur${item}"} =
2607       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2608     $form->{"sumeura"} += $form->{$item};
2609   }
2610   foreach $item (@categories_ausgaben) {
2611     $form->{"eur${item}"} =
2612       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2613     $form->{"sumeurb"} += $form->{$item};
2614   }
2615
2616   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2617
2618   foreach $item (@ergebnisse) {
2619     $form->{$item} =
2620       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2621   }
2622   $main::lxdebug->leave_sub();
2623 }
2624 1;