sql-ledger/sql-ledger/SL/GL.pm

   1 #=====================================================================
   2 # SQL-Ledger Accounting
   3 # Copyright (C) 2000
   4 #
   5 #  Author: Dieter Simader
   6 #   Email: dsimader@sql-ledger.org
   7 #     Web: http://www.sql-ledger.org
   8 #
   9 #  Contributors:
  10 #
  11 # This program is free software; you can redistribute it and/or modify
  12 # it under the terms of the GNU General Public License as published by
  13 # the Free Software Foundation; either version 2 of the License, or
  14 # (at your option) any later version.
  15 #
  16 # This program is distributed in the hope that it will be useful,
  17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19 # GNU General Public License for more details.
  20 # You should have received a copy of the GNU General Public License
  21 # along with this program; if not, write to the Free Software
  22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  23 #======================================================================
  24 #
  25 # General ledger backend code
  26 #
  27 #======================================================================
  28
  29 package GL;
  30
  31
  32 sub delete_transaction {
  33   my ($self, $myconfig, $form) = @_;
  34
  35   # connect to database
  36   my $dbh = $form->dbconnect_noauto($myconfig);
  37
  38   my %audittrail = ( tablename  => 'gl',
  39                      reference  => $form->{reference},
  40                      formname   => 'transaction',
  41                      action     => 'deleted',
  42                      id         => $form->{id} );
  43
  44   $form->audittrail($dbh, "", \%audittrail);
  45
  46   my $query = qq|DELETE FROM gl WHERE id = $form->{id}|;
  47   $dbh->do($query) || $form->dberror($query);
  48
  49   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
  50   $dbh->do($query) || $form->dberror($query);
  51
  52   # commit and redirect
  53   my $rc = $dbh->commit;
  54   $dbh->disconnect;
  55
  56   $rc;
  57
  58 }
  59
  60
  61 sub post_transaction {
  62   my ($self, $myconfig, $form) = @_;
  63
  64   my $null;
  65   my $project_id;
  66   my $department_id;
  67   my $i;
  68
  69   # connect to database, turn off AutoCommit
  70   my $dbh = $form->dbconnect_noauto($myconfig);
  71
  72   # post the transaction
  73   # make up a unique handle and store in reference field
  74   # then retrieve the record based on the unique handle to get the id
  75   # replace the reference field with the actual variable
  76   # add records to acc_trans
  77
  78   # if there is a $form->{id} replace the old transaction
  79   # delete all acc_trans entries and add the new ones
  80
  81   my $query;
  82   my $sth;
  83
  84   if ($form->{id}) {
  85     # delete individual transactions
  86     $query = qq|DELETE FROM acc_trans
  87                 WHERE trans_id = $form->{id}|;
  88     $dbh->do($query) || $form->dberror($query);
  89
  90   } else {
  91     my $uid = time;
  92     $uid .= $form->{login};
  93
  94     $query = qq|INSERT INTO gl (reference, employee_id)
  95                 VALUES ('$uid', (SELECT id FROM employee
  96                                  WHERE login = '$form->{login}'))|;
  97     $dbh->do($query) || $form->dberror($query);
  98
  99     $query = qq|SELECT id FROM gl
 100                 WHERE reference = '$uid'|;
 101     $sth = $dbh->prepare($query);
 102     $sth->execute || $form->dberror($query);
 103
 104     ($form->{id}) = $sth->fetchrow_array;
 105     $sth->finish;
 106   }
 107
 108   ($null, $department_id) = split /--/, $form->{department};
 109   $department_id *= 1;
 110
 111   $query = qq|UPDATE gl SET
 112               reference = |.$dbh->quote($form->{reference}).qq|,
 113               description = |.$dbh->quote($form->{description}).qq|,
 114               notes = |.$dbh->quote($form->{notes}).qq|,
 115               transdate = '$form->{transdate}',
 116               department_id = $department_id
 117               WHERE id = $form->{id}|;
 118
 119   $dbh->do($query) || $form->dberror($query);
 120
 121
 122   my $amount = 0;
 123   my $posted = 0;
 124   # insert acc_trans transactions
 125   for $i (1 .. $form->{rowcount}) {
 126
 127     $form->{"debit_$i"} = $form->parse_amount($myconfig, $form->{"debit_$i"});
 128     $form->{"credit_$i"} = $form->parse_amount($myconfig, $form->{"credit_$i"});
 129
 130     # extract accno
 131     ($accno) = split(/--/, $form->{"accno_$i"});
 132     $amount = 0;
 133
 134     if ($form->{"credit_$i"} != 0) {
 135       $amount = $form->{"credit_$i"};
 136       $posted = 0;
 137     }
 138     if ($form->{"debit_$i"} != 0) {
 139       $amount = $form->{"debit_$i"} * -1;
 140       $posted = 0;
 141     }
 142
 143
 144     # add the record
 145     if (! $posted) {
 146
 147       ($null, $project_id) = split /--/, $form->{"projectnumber_$i"};
 148       $project_id *= 1;
 149       $form->{"fx_transaction_$i"} *= 1;
 150
 151       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 152                   source, project_id, fx_transaction)
 153                   VALUES
 154                   ($form->{id}, (SELECT id
 155                                  FROM chart
 156                                  WHERE accno = '$accno'),
 157                    $amount, '$form->{transdate}', |
 158                    .$dbh->quote($form->{reference}).qq|,
 159                   $project_id, '$form->{"fx_transaction_$i"}')|;
 160
 161       $dbh->do($query) || $form->dberror($query);
 162
 163       $posted = 1;
 164     }
 165
 166   }
 167
 168   my %audittrail = ( tablename  => 'gl',
 169                      reference  => $form->{reference},
 170                      formname   => 'transaction',
 171                      action     => 'posted',
 172                      id         => $form->{id} );
 173
 174   $form->audittrail($dbh, "", \%audittrail);
 175
 176   # commit and redirect
 177   my $rc = $dbh->commit;
 178   $dbh->disconnect;
 179
 180   $rc;
 181
 182 }
 183
 184
 185
 186 sub all_transactions {
 187   my ($self, $myconfig, $form) = @_;
 188
 189   # connect to database
 190   my $dbh = $form->dbconnect($myconfig);
 191   my $query;
 192   my $sth;
 193   my $var;
 194   my $null;
 195
 196   my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
 197
 198   if ($form->{reference}) {
 199     $var = $form->like(lc $form->{reference});
 200     $glwhere .= " AND lower(g.reference) LIKE '$var'";
 201     $arwhere .= " AND lower(a.invnumber) LIKE '$var'";
 202     $apwhere .= " AND lower(a.invnumber) LIKE '$var'";
 203   }
 204   if ($form->{department}) {
 205     ($null, $var) = split /--/, $form->{department};
 206     $glwhere .= " AND g.department_id = $var";
 207     $arwhere .= " AND a.department_id = $var";
 208     $apwhere .= " AND a.department_id = $var";
 209   }
 210
 211   if ($form->{source}) {
 212     $var = $form->like(lc $form->{source});
 213     $glwhere .= " AND lower(ac.source) LIKE '$var'";
 214     $arwhere .= " AND lower(ac.source) LIKE '$var'";
 215     $apwhere .= " AND lower(ac.source) LIKE '$var'";
 216   }
 217
 218   ($form->{datefrom}, $form->{dateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 219
 220   if ($form->{datefrom}) {
 221     $glwhere .= " AND ac.transdate >= '$form->{datefrom}'";
 222     $arwhere .= " AND ac.transdate >= '$form->{datefrom}'";
 223     $apwhere .= " AND ac.transdate >= '$form->{datefrom}'";
 224   }
 225   if ($form->{dateto}) {
 226     $glwhere .= " AND ac.transdate <= '$form->{dateto}'";
 227     $arwhere .= " AND ac.transdate <= '$form->{dateto}'";
 228     $apwhere .= " AND ac.transdate <= '$form->{dateto}'";
 229   }
 230   if ($form->{amountfrom}) {
 231     $glwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
 232     $arwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
 233     $apwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
 234   }
 235   if ($form->{amountto}) {
 236     $glwhere .= " AND abs(ac.amount) <= $form->{amountto}";
 237     $arwhere .= " AND abs(ac.amount) <= $form->{amountto}";
 238     $apwhere .= " AND abs(ac.amount) <= $form->{amountto}";
 239   }
 240   if ($form->{description}) {
 241     $var = $form->like(lc $form->{description});
 242     $glwhere .= " AND lower(g.description) LIKE '$var'";
 243     $arwhere .= " AND lower(ct.name) LIKE '$var'";
 244     $apwhere .= " AND lower(ct.name) LIKE '$var'";
 245   }
 246   if ($form->{notes}) {
 247     $var = $form->like(lc $form->{notes});
 248     $glwhere .= " AND lower(g.notes) LIKE '$var'";
 249     $arwhere .= " AND lower(a.notes) LIKE '$var'";
 250     $apwhere .= " AND lower(a.notes) LIKE '$var'";
 251   }
 252   if ($form->{accno}) {
 253     $glwhere .= " AND c.accno = '$form->{accno}'";
 254     $arwhere .= " AND c.accno = '$form->{accno}'";
 255     $apwhere .= " AND c.accno = '$form->{accno}'";
 256   }
 257   if ($form->{gifi_accno}) {
 258     $glwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
 259     $arwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
 260     $apwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
 261   }
 262   if ($form->{category} ne 'X') {
 263     $glwhere .= " AND c.category = '$form->{category}'";
 264     $arwhere .= " AND c.category = '$form->{category}'";
 265     $apwhere .= " AND c.category = '$form->{category}'";
 266   }
 267
 268   if ($form->{accno}) {
 269     # get category for account
 270     $query = qq|SELECT category, link
 271                 FROM chart
 272                 WHERE accno = '$form->{accno}'|;
 273     ($form->{ml}, $form->{link}) = $dbh->selectrow_array($query);
 274
 275     if ($form->{datefrom}) {
 276       $query = qq|SELECT SUM(ac.amount)
 277                   FROM acc_trans ac
 278                   JOIN chart c ON (ac.chart_id = c.id)
 279                   WHERE c.accno = '$form->{accno}'
 280                   AND ac.transdate < date '$form->{datefrom}'
 281                   |;
 282       ($form->{balance}) = $dbh->selectrow_array($query);
 283     }
 284   }
 285
 286   if ($form->{gifi_accno}) {
 287     # get category for account
 288     $query = qq|SELECT category, link
 289                 FROM chart
 290                 WHERE gifi_accno = '$form->{gifi_accno}'|;
 291     ($form->{ml}, $form->{link}) = $dbh->selectrow_array($query);
 292
 293     if ($form->{datefrom}) {
 294       $query = qq|SELECT SUM(ac.amount)
 295                   FROM acc_trans ac
 296                   JOIN chart c ON (ac.chart_id = c.id)
 297                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 298                   AND ac.transdate < date '$form->{datefrom}'
 299                   |;
 300       ($form->{balance}) = $dbh->selectrow_array($query);
 301     }
 302   }
 303
 304   my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
 305
 306   my %ordinal = ( id => 1,
 307                   accno => 9,
 308                   transdate => 6,
 309                   reference => 4,
 310                   source => 7,
 311                   description => 5 );
 312
 313   my @a = (id, transdate, reference, source, description, accno);
 314   my $sortorder = $form->sort_order(\@a, \%ordinal);
 315
 316   my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
 317                  g.description, ac.transdate, ac.source,
 318                  ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
 319                  '' AS till, ac.cleared
 320                  FROM gl g, acc_trans ac, chart c
 321                  WHERE $glwhere
 322                  AND ac.chart_id = c.id
 323                  AND g.id = ac.trans_id
 324         UNION ALL
 325                  SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
 326                  ct.name, ac.transdate, ac.source,
 327                  ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
 328                  a.till, ac.cleared
 329                  FROM ar a, acc_trans ac, chart c, customer ct
 330                  WHERE $arwhere
 331                  AND ac.chart_id = c.id
 332                  AND a.customer_id = ct.id
 333                  AND a.id = ac.trans_id
 334         UNION ALL
 335                  SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
 336                  ct.name, ac.transdate, ac.source,
 337                  ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
 338                  a.till, ac.cleared
 339                  FROM ap a, acc_trans ac, chart c, vendor ct
 340                  WHERE $apwhere
 341                  AND ac.chart_id = c.id
 342                  AND a.vendor_id = ct.id
 343                  AND a.id = ac.trans_id
 344                  ORDER BY $sortorder|;
 345   my $sth = $dbh->prepare($query);
 346   $sth->execute || $form->dberror($query);
 347
 348
 349   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 350
 351     # gl
 352     if ($ref->{type} eq "gl") {
 353       $ref->{module} = "gl";
 354     }
 355
 356     # ap
 357     if ($ref->{type} eq "ap") {
 358       if ($ref->{invoice}) {
 359         $ref->{module} = "ir";
 360       } else {
 361         $ref->{module} = "ap";
 362       }
 363     }
 364
 365     # ar
 366     if ($ref->{type} eq "ar") {
 367       if ($ref->{invoice}) {
 368         $ref->{module} = ($ref->{till}) ? "ps" : "is";
 369       } else {
 370         $ref->{module} = "ar";
 371       }
 372     }
 373
 374     if ($ref->{amount} < 0) {
 375       $ref->{debit} = $ref->{amount} * -1;
 376       $ref->{credit} = 0;
 377     } else {
 378       $ref->{credit} = $ref->{amount};
 379       $ref->{debit} = 0;
 380     }
 381
 382     push @{ $form->{GL} }, $ref;
 383
 384   }
 385
 386
 387   $sth->finish;
 388
 389   if ($form->{accno}) {
 390     $query = qq|SELECT description FROM chart WHERE accno = '$form->{accno}'|;
 391     $sth = $dbh->prepare($query);
 392     $sth->execute || $form->dberror($query);
 393
 394     ($form->{account_description}) = $sth->fetchrow_array;
 395     $sth->finish;
 396   }
 397   if ($form->{gifi_accno}) {
 398     $query = qq|SELECT description FROM gifi WHERE accno = '$form->{gifi_accno}'|;
 399     $sth = $dbh->prepare($query);
 400     $sth->execute || $form->dberror($query);
 401
 402     ($form->{gifi_account_description}) = $sth->fetchrow_array;
 403     $sth->finish;
 404   }
 405
 406   $dbh->disconnect;
 407
 408 }
 409
 410
 411 sub transaction {
 412   my ($self, $myconfig, $form) = @_;
 413
 414   my ($query, $sth, $ref);
 415
 416   # connect to database
 417   my $dbh = $form->dbconnect($myconfig);
 418
 419   if ($form->{id}) {
 420     $query = "SELECT closedto, revtrans
 421               FROM defaults";
 422     $sth = $dbh->prepare($query);
 423     $sth->execute || $form->dberror($query);
 424
 425     ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
 426     $sth->finish;
 427
 428     $query = qq|SELECT g.*,
 429                 d.description AS department
 430                 FROM gl g
 431                 LEFT JOIN department d ON (d.id = g.department_id)
 432                 WHERE g.id = $form->{id}|;
 433     $sth = $dbh->prepare($query);
 434     $sth->execute || $form->dberror($query);
 435
 436     $ref = $sth->fetchrow_hashref(NAME_lc);
 437     map { $form->{$_} = $ref->{$_} } keys %$ref;
 438     $sth->finish;
 439
 440     # retrieve individual rows
 441     $query = qq|SELECT c.accno, c.description, ac.amount, ac.project_id,
 442                 p.projectnumber, ac.fx_transaction
 443                 FROM acc_trans ac
 444                 JOIN chart c ON (ac.chart_id = c.id)
 445                 LEFT JOIN project p ON (p.id = ac.project_id)
 446                 WHERE ac.trans_id = $form->{id}
 447                 ORDER BY accno|;
 448     $sth = $dbh->prepare($query);
 449     $sth->execute || $form->dberror($query);
 450
 451     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 452       if ($ref->{fx_transaction}) {
 453         $form->{transfer} = 1;
 454       }
 455       push @{ $form->{GL} }, $ref;
 456     }
 457   } else {
 458     $query = "SELECT current_date AS transdate, closedto, revtrans
 459               FROM defaults";
 460     $sth = $dbh->prepare($query);
 461     $sth->execute || $form->dberror($query);
 462
 463     ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
 464   }
 465
 466   $sth->finish;
 467
 468   my $paid;
 469   if ($form->{transfer}) {
 470     $paid = "AND link LIKE '%_paid%'
 471              AND NOT (category = 'I'
 472                   OR category = 'E')
 473
 474           UNION
 475
 476              SELECT accno,description
 477              FROM chart
 478              WHERE id IN (SELECT fxgain_accno_id FROM defaults)
 479              OR id IN (SELECT fxloss_accno_id FROM defaults)";
 480   }
 481
 482   # get chart of accounts
 483   $query = qq|SELECT accno,description
 484               FROM chart
 485               WHERE charttype = 'A'
 486               $paid
 487               ORDER by accno|;
 488   $sth = $dbh->prepare($query);
 489   $sth->execute || $form->dberror($query);
 490
 491   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 492     push @{ $form->{all_accno} }, $ref;
 493   }
 494   $sth->finish;
 495
 496   # get projects
 497   $query = qq|SELECT *
 498               FROM project
 499               ORDER BY projectnumber|;
 500   $sth = $dbh->prepare($query);
 501   $sth->execute || $form->dberror($query);
 502
 503   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 504     push @{ $form->{all_projects} }, $ref;
 505   }
 506   $sth->finish;
 507
 508   $dbh->disconnect;
 509
 510 }
 511
 512
 513 1;
 514