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