sql-ledger/sql-ledger/SL/RP.pm

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