sql-ledger/sql-ledger/SL/AR.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 # Accounts Receivable module backend routines
  26 #
  27 #======================================================================
  28
  29 package AR;
  30
  31
  32 sub post_transaction {
  33   my ($self, $myconfig, $form) = @_;
  34
  35   my $null;
  36   my $taxrate;
  37   my $amount;
  38   my $tax;
  39   my $diff;
  40   my $exchangerate = 0;
  41   my $i;
  42
  43   # split and store id numbers in link accounts
  44   map { ($form->{AR_amounts}{"amount_$_"}) = split(/--/, $form->{"AR_amount_$_"}) } (1 .. $form->{rowcount});
  45   ($form->{AR_amounts}{receivables}) = split(/--/, $form->{AR});
  46
  47   ($null, $form->{department_id}) = split(/--/, $form->{department});
  48   $form->{department_id} *= 1;
  49
  50   if ($form->{currency} eq $form->{defaultcurrency}) {
  51     $form->{exchangerate} = 1;
  52   } else {
  53     $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
  54
  55     $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  56   }
  57
  58   for $i (1 .. $form->{rowcount}) {
  59     $form->{"amount_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"}) * $form->{exchangerate}, 2);
  60
  61     $form->{netamount} += $form->{"amount_$i"};
  62
  63   }
  64
  65
  66   # taxincluded doesn't make sense if there is no amount
  67   $form->{taxincluded} = 0 if ($form->{netamount} == 0);
  68
  69   foreach my $item (split / /, $form->{taxaccounts}) {
  70     $form->{AR_amounts}{"tax_$item"} = $item;
  71
  72     $form->{"tax_$item"} = $form->round_amount($form->parse_amount($myconfig, $form->{"tax_$item"}) * $form->{exchangerate}, 2);
  73     $form->{tax} += $form->{"tax_$item"};
  74   }
  75
  76   # adjust paidaccounts if there is no date in the last row
  77   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
  78
  79   $form->{paid} = 0;
  80   # add payments
  81   for $i (1 .. $form->{paidaccounts}) {
  82     $form->{"paid_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}), 2);
  83
  84     $form->{paid} += $form->{"paid_$i"};
  85     $form->{datepaid} = $form->{"datepaid_$i"};
  86
  87   }
  88
  89
  90   if ($form->{taxincluded} *= 1) {
  91     for $i (1 .. $form->{rowcount}) {
  92       $tax = ($form->{netamount}) ? $form->{tax} * $form->{"amount_$i"} / $form->{netamount} : 0;
  93       $amount = $form->{"amount_$i"} - $tax;
  94       $form->{"amount_$i"} = $form->round_amount($amount, 2);
  95       $diff += $amount - $form->{"amount_$i"};
  96     }
  97
  98     $form->{netamount} -= $form->{tax};
  99     # deduct difference from amount_1
 100     $form->{amount_1} += $form->round_amount($diff, 2);
 101   }
 102
 103   $form->{amount} = $form->{netamount} + $form->{tax};
 104   $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
 105
 106   # connect to database
 107   my $dbh = $form->dbconnect_noauto($myconfig);
 108
 109   my $query;
 110   my $sth;
 111
 112   ($null, $form->{employee_id}) = split /--/, $form->{employee};
 113   unless ($form->{employee_id}) {
 114     ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
 115   }
 116
 117   # if we have an id delete old records
 118   if ($form->{id}) {
 119
 120     # delete detail records
 121     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 122     $dbh->do($query) || $form->dberror($query);
 123
 124   } else {
 125     my $uid = time;
 126     $uid .= $form->{login};
 127
 128     $query = qq|INSERT INTO ar (invnumber)
 129                 VALUES ('$uid')|;
 130     $dbh->do($query) || $form->dberror($query);
 131
 132     $query = qq|SELECT id FROM ar
 133                 WHERE invnumber = '$uid'|;
 134     ($form->{id}) = $dbh->selectrow_array($query);
 135   }
 136
 137
 138   # record last payment date in ar table
 139   $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
 140   my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
 141
 142   $query = qq|UPDATE ar set
 143               invnumber = |.$dbh->quote($form->{invnumber}).qq|,
 144               ordnumber = |.$dbh->quote($form->{ordnumber}).qq|,
 145               transdate = '$form->{transdate}',
 146               customer_id = $form->{customer_id},
 147               taxincluded = '$form->{taxincluded}',
 148               amount = $form->{amount},
 149               duedate = '$form->{duedate}',
 150               paid = $form->{paid},
 151               datepaid = $datepaid,
 152               netamount = $form->{netamount},
 153               curr = '$form->{currency}',
 154               notes = |.$dbh->quote($form->{notes}).qq|,
 155               department_id = $form->{department_id},
 156               employee_id = $form->{employee_id}
 157               WHERE id = $form->{id}|;
 158   $dbh->do($query) || $form->dberror($query);
 159
 160
 161   # amount for AR account
 162   $form->{receivables} = $form->{amount} * -1;
 163
 164
 165   # update exchangerate
 166   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 167     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
 168   }
 169
 170   # add individual transactions for AR, amount and taxes
 171   foreach my $item (keys %{ $form->{AR_amounts} }) {
 172
 173     if ($form->{$item} != 0) {
 174
 175       $project_id = 'NULL';
 176       if ($item =~ /amount_/) {
 177         if ($form->{"projectnumber_$'"}) {
 178           ($null, $project_id) = split /--/, $form->{"projectnumber_$'"};
 179         }
 180       }
 181
 182       # insert detail records in acc_trans
 183       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 184                                          project_id)
 185                   VALUES ($form->{id}, (SELECT id FROM chart
 186                                         WHERE accno = '$form->{AR_amounts}{$item}'),
 187                   $form->{$item}, '$form->{transdate}', $project_id)|;
 188       $dbh->do($query) || $form->dberror($query);
 189     }
 190   }
 191
 192   if ($form->{amount} == 0) {
 193     $form->{receivables} = $form->{paid};
 194     $form->{receivables} -= $form->{paid_1} if $form->{amount_1} != 0;
 195   }
 196
 197   # add paid transactions
 198   for my $i (1 .. $form->{paidaccounts}) {
 199     if ($form->{"paid_$i"} != 0) {
 200
 201        ($form->{AR_amounts}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
 202       $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"});
 203
 204       $exchangerate = 0;
 205       if ($form->{currency} eq $form->{defaultcurrency}) {
 206         $form->{"exchangerate_$i"} = 1;
 207       } else {
 208         $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
 209
 210         $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 211       }
 212
 213
 214       # if there is no amount
 215       if ($form->{amount} == 0 && $form->{netamount} == 0) {
 216         $form->{exchangerate} = $form->{"exchangerate_$i"};
 217       }
 218
 219       # receivables amount
 220       $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
 221
 222       if ($form->{receivables} != 0) {
 223         # add receivable
 224         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 225                     transdate)
 226                     VALUES ($form->{id},
 227                            (SELECT id FROM chart
 228                             WHERE accno = '$form->{AR_amounts}{receivables}'),
 229                     $amount, '$form->{"datepaid_$i"}')|;
 230         $dbh->do($query) || $form->dberror($query);
 231       }
 232       $form->{receivables} = $amount;
 233
 234       if ($form->{"paid_$i"} != 0) {
 235         # add payment
 236         $amount = $form->{"paid_$i"} * -1;
 237         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 238                     transdate, source, memo)
 239                     VALUES ($form->{id},
 240                            (SELECT id FROM chart
 241                             WHERE accno = '$form->{AR_amounts}{"paid_$i"}'),
 242                     $amount, '$form->{"datepaid_$i"}', |
 243                     .$dbh->quote($form->{"source_$i"}).qq|, |
 244                     .$dbh->quote($form->{"memo_$i"}).qq|)|;
 245         $dbh->do($query) || $form->dberror($query);
 246
 247
 248         # exchangerate difference for payment
 249         $amount = $form->round_amount($form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1, 2);
 250
 251         if ($amount != 0) {
 252           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 253                       transdate, fx_transaction, cleared)
 254                       VALUES ($form->{id},
 255                              (SELECT id FROM chart
 256                               WHERE accno = '$form->{AR_amounts}{"paid_$i"}'),
 257                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 258           $dbh->do($query) || $form->dberror($query);
 259         }
 260
 261         # exchangerate gain/loss
 262         $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1, 2);
 263
 264         if ($amount != 0) {
 265           $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
 266           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 267                       transdate, fx_transaction, cleared)
 268                       VALUES ($form->{id}, (SELECT id FROM chart
 269                                             WHERE accno = '$accno'),
 270                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 271           $dbh->do($query) || $form->dberror($query);
 272         }
 273       }
 274
 275       # update exchangerate record
 276       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 277         $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0);
 278       }
 279     }
 280   }
 281
 282   # save printed and queued
 283   $form->save_status($dbh);
 284
 285   my %audittrail = ( tablename  => 'ar',
 286                      reference  => $form->{invnumber},
 287                      formname   => 'transaction',
 288                      action     => 'posted',
 289                      id         => $form->{id} );
 290
 291   $form->audittrail($dbh, "", \%audittrail);
 292
 293   my $rc = $dbh->commit;
 294   $dbh->disconnect;
 295
 296   $rc;
 297
 298 }
 299
 300
 301
 302 sub delete_transaction {
 303   my ($self, $myconfig, $form) = @_;
 304
 305   # connect to database, turn AutoCommit off
 306   my $dbh = $form->dbconnect_noauto($myconfig);
 307
 308   my %audittrail = ( tablename  => 'ar',
 309                      reference  => $form->{invnumber},
 310                      formname   => 'transaction',
 311                      action     => 'deleted',
 312                      id         => $form->{id} );
 313
 314   $form->audittrail($dbh, "", \%audittrail);
 315
 316   my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
 317   $dbh->do($query) || $form->dberror($query);
 318
 319   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 320   $dbh->do($query) || $form->dberror($query);
 321
 322   # delete spool files
 323   $query = qq|SELECT spoolfile FROM status
 324               WHERE trans_id = $form->{id}
 325               AND spoolfile IS NOT NULL|;
 326   my $sth = $dbh->prepare($query);
 327   $sth->execute || $form->dberror($query);
 328
 329   my $spoolfile;
 330   my @spoolfiles = ();
 331
 332   while (($spoolfile) = $sth->fetchrow_array) {
 333     push @spoolfiles, $spoolfile;
 334   }
 335   $sth->finish;
 336
 337   $query = qq|DELETE FROM status WHERE trans_id = $form->{id}|;
 338   $dbh->do($query) || $form->dberror($query);
 339
 340   # commit
 341   my $rc = $dbh->commit;
 342   $dbh->disconnect;
 343
 344   if ($rc) {
 345     foreach $spoolfile (@spoolfiles) {
 346       unlink "$spool/$spoolfile" if $spoolfile;
 347     }
 348   }
 349
 350   $rc;
 351
 352 }
 353
 354
 355
 356 sub ar_transactions {
 357   my ($self, $myconfig, $form) = @_;
 358
 359   # connect to database
 360   my $dbh = $form->dbconnect($myconfig);
 361   my $var;
 362
 363   my $paid = "a.paid";
 364
 365   ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 366
 367   if ($form->{outstanding}) {
 368     $paid = qq|SELECT SUM(ac.amount) * -1
 369                FROM acc_trans ac
 370                JOIN chart c ON (c.id = ac.chart_id)
 371                WHERE ac.trans_id = a.id
 372                AND (c.link LIKE '%AR_paid%' OR c.link = '')|;
 373     $paid .= qq|
 374                AND ac.transdate <= '$form->{transdateto}'| if $form->{transdateto};
 375   }
 376
 377   my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
 378                  a.duedate, a.netamount, a.amount, ($paid) AS paid,
 379                  a.invoice, a.datepaid, a.terms, a.notes,
 380                  a.shipvia, a.shippingpoint, e.name AS employee, c.name,
 381                  a.customer_id, a.till, m.name AS manager, a.curr,
 382                  ex.buy AS exchangerate
 383                  FROM ar a
 384               JOIN customer c ON (a.customer_id = c.id)
 385               LEFT JOIN employee e ON (a.employee_id = e.id)
 386               LEFT JOIN employee m ON (e.managerid = m.id)
 387               LEFT JOIN exchangerate ex ON (ex.curr = a.curr
 388                                             AND ex.transdate = a.transdate)
 389               |;
 390
 391   my %ordinal = ( 'id' => 1,
 392                   'invnumber' => 2,
 393                   'ordnumber' => 3,
 394                   'transdate' => 4,
 395                   'duedate' => 5,
 396                   'datepaid' => 10,
 397                   'shipvia' => 13,
 398                   'shippingpoint' => 14,
 399                   'employee' => 15,
 400                   'name' => 16,
 401                   'manager' => 19,
 402                   'curr' => 20
 403                 );
 404
 405
 406   my @a = (transdate, invnumber, name);
 407   push @a, "employee" if $form->{l_employee};
 408   push @a, "manager" if $form->{l_manager};
 409   my $sortorder = $form->sort_order(\@a, \%ordinal);
 410
 411   my $where = "1 = 1";
 412   if ($form->{customer_id}) {
 413     $where .= " AND a.customer_id = $form->{customer_id}";
 414   } else {
 415     if ($form->{customer}) {
 416       $var = $form->like(lc $form->{customer});
 417       $where .= " AND lower(c.name) LIKE '$var'";
 418     }
 419   }
 420   if ($form->{department}) {
 421     my ($null, $department_id) = split /--/, $form->{department};
 422     $where .= " AND a.department_id = $department_id";
 423   }
 424   if ($form->{invnumber}) {
 425     $var = $form->like(lc $form->{invnumber});
 426     $where .= " AND lower(a.invnumber) LIKE '$var'";
 427     $form->{open} = $form->{closed} = 0;
 428   }
 429   if ($form->{ordnumber}) {
 430     $var = $form->like(lc $form->{ordnumber});
 431     $where .= " AND lower(a.ordnumber) LIKE '$var'";
 432     $form->{open} = $form->{closed} = 0;
 433   }
 434   if ($form->{shipvia}) {
 435     $var = $form->like(lc $form->{shipvia});
 436     $where .= " AND lower(a.shipvia) LIKE '$var'";
 437   }
 438   if ($form->{notes}) {
 439     $var = $form->like(lc $form->{notes});
 440     $where .= " AND lower(a.notes) LIKE '$var'";
 441   }
 442
 443   $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
 444   $where .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
 445   if ($form->{open} || $form->{closed}) {
 446     unless ($form->{open} && $form->{closed}) {
 447       $where .= " AND a.amount != a.paid" if ($form->{open});
 448       $where .= " AND a.amount = a.paid" if ($form->{closed});
 449     }
 450   }
 451
 452   if ($form->{till}) {
 453     $where .= " AND a.invoice = '1'
 454                 AND NOT a.till IS NULL";
 455     if ($myconfig->{role} eq 'user') {
 456       $where .= " AND e.login = '$form->{login}'";
 457     }
 458   }
 459
 460   if ($form->{AR}) {
 461     my ($accno) = split /--/, $form->{AR};
 462     $where .= qq|
 463                 AND a.id IN (SELECT ac.trans_id
 464                              FROM acc_trans ac
 465                              JOIN chart c ON (c.id = ac.chart_id)
 466                              WHERE a.id = ac.trans_id
 467                              AND c.accno = '$accno')
 468                 |;
 469   }
 470
 471   $query .= "WHERE $where
 472              ORDER by $sortorder";
 473
 474   my $sth = $dbh->prepare($query);
 475   $sth->execute || $form->dberror($query);
 476
 477   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 478     $ref->{exchangerate} = 1 unless $ref->{exchangerate};
 479     if ($form->{outstanding}) {
 480       next if $form->round_amount($ref->{amount}, 2) == $form->round_amount($ref->{paid}, 2);
 481     }
 482     push @{ $form->{transactions} }, $ref;
 483   }
 484
 485   $sth->finish;
 486   $dbh->disconnect;
 487
 488 }
 489
 490
 491 1;
 492