sql-ledger/old/sql-ledger/SL/AR.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:
  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, $taxrate, $amount, $tax, $diff);
  36   my $exchangerate = 0;
  37   my $i;
  38
  39   # split and store id numbers in link accounts
  40   map { ($form->{AR}{"amount_$_"}) = split(/--/, $form->{"AR_amount_$_"}) } (1 .. $form->{rowcount});
  41   ($form->{AR}{receivables}) = split(/--/, $form->{AR});
  42
  43   if ($form->{currency} eq $form->{defaultcurrency}) {
  44     $form->{exchangerate} = 1;
  45   } else {
  46     $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
  47   }
  48
  49   $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  50
  51   for $i (1 .. $form->{rowcount}) {
  52     $form->{"amount_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"}) * $form->{exchangerate}, 2);
  53     $amount += $form->{"amount_$i"};
  54   }
  55
  56   # this is for ar
  57   $form->{amount} = $amount;
  58
  59   # taxincluded doesn't make sense if there is no amount
  60   $form->{taxincluded} = 0 if ($form->{amount} == 0);
  61
  62   foreach my $item (split / /, $form->{taxaccounts}) {
  63     $form->{AR}{"tax_$item"} = $item;
  64
  65     $amount = $form->round_amount($form->parse_amount($myconfig, $form->{"tax_$item"}), 2);
  66
  67     $form->{"tax_$item"} = $form->round_amount($amount * $form->{exchangerate}, 2);
  68     $form->{total_tax} += $form->{"tax_$item"};
  69
  70   }
  71
  72   # adjust paidaccounts if there is no date in the last row
  73   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
  74
  75   $form->{invpaid} = 0;
  76   # add payments
  77   for $i (1 .. $form->{paidaccounts}) {
  78     $form->{"paid_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}), 2);
  79
  80     $form->{invpaid} += $form->{"paid_$i"};
  81     $form->{datepaid} = $form->{"datepaid_$i"};
  82
  83     # reverse payment
  84     $form->{"paid_$i"} *= -1;
  85
  86   }
  87
  88   $form->{invpaid} = $form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
  89
  90   if ($form->{taxincluded} *= 1) {
  91     for $i (1 .. $form->{rowcount}) {
  92       $tax = $form->{total_tax} * $form->{"amount_$i"} / $form->{amount};
  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->{amount} -= $form->{total_tax};
  99     # deduct difference from amount_1
 100     $form->{amount_1} += $form->round_amount($diff, 2);
 101   }
 102
 103   # store invoice total, this goes into ar table
 104   $form->{invtotal} = $form->{amount} + $form->{total_tax};
 105
 106   # connect to database
 107   my $dbh = $form->dbconnect_noauto($myconfig);
 108
 109   my ($query, $sth);
 110
 111   # if we have an id delete old records
 112   if ($form->{id}) {
 113
 114     # delete detail records
 115     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 116     $dbh->do($query) || $form->dberror($query);
 117
 118   } else {
 119     my $uid = time;
 120     $uid .= $form->{login};
 121
 122     $query = qq|INSERT INTO ar (invnumber, employee_id)
 123                 VALUES ('$uid', (SELECT id FROM employee
 124                                  WHERE login = '$form->{login}') )|;
 125     $dbh->do($query) || $form->dberror($query);
 126
 127     $query = qq|SELECT id FROM ar
 128                 WHERE invnumber = '$uid'|;
 129     $sth = $dbh->prepare($query);
 130     $sth->execute || $form->dberror($query);
 131
 132     ($form->{id}) = $sth->fetchrow_array;
 133     $sth->finish;
 134
 135   }
 136
 137   # escape '
 138   $form->{notes} =~ s/'/''/g;
 139
 140   # record last payment date in ar table
 141   $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
 142   my $datepaid = ($form->{invpaid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
 143
 144   $query = qq|UPDATE ar set
 145               invnumber = '$form->{invnumber}',
 146               ordnumber = '$form->{ordnumber}',
 147               transdate = '$form->{transdate}',
 148               customer_id = $form->{customer_id},
 149               taxincluded = '$form->{taxincluded}',
 150               amount = $form->{invtotal},
 151               duedate = '$form->{duedate}',
 152               paid = $form->{invpaid},
 153               datepaid = $datepaid,
 154               netamount = $form->{amount},
 155               curr = '$form->{currency}',
 156               notes = '$form->{notes}'
 157               WHERE id = $form->{id}|;
 158   $dbh->do($query) || $form->dberror($query);
 159
 160
 161   # amount for AR account
 162   $form->{receivables} = $form->round_amount($form->{invtotal} * -1, 2);
 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} }) {
 172     if ($form->{$item} != 0) {
 173       $project_id = 'NULL';
 174       if ($item =~ /amount_/) {
 175         if ($form->{"project_id_$'"} && $form->{"projectnumber_$'"}) {
 176           $project_id = $form->{"project_id_$'"};
 177         }
 178       }
 179
 180       # insert detail records in acc_trans
 181       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
 182                                          project_id)
 183                   VALUES ($form->{id}, (SELECT id FROM chart
 184                                         WHERE accno = '$form->{AR}{$item}'),
 185                   $form->{$item}, '$form->{transdate}', $project_id)|;
 186       $dbh->do($query) || $form->dberror($query);
 187     }
 188   }
 189
 190   # if there is no amount but a payment record a receivables
 191   if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 192     $form->{receivables} = $form->{invpaid} * -1;
 193   }
 194
 195   # add paid transactions
 196   for my $i (1 .. $form->{paidaccounts}) {
 197     if ($form->{"paid_$i"} != 0) {
 198
 199        ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
 200       $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"});
 201
 202       $exchangerate = 0;
 203       if ($form->{currency} eq $form->{defaultcurrency}) {
 204         $form->{"exchangerate_$i"} = 1;
 205       } else {
 206         $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
 207
 208         $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 209       }
 210
 211
 212       # if there is no amount and invtotal is zero there is no exchangerate
 213       if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 214         $form->{exchangerate} = $form->{"exchangerate_$i"};
 215       }
 216
 217       # receivables amount
 218       $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1, 2);
 219
 220       if ($form->{receivables} != 0) {
 221         # add receivable
 222         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 223                     transdate)
 224                     VALUES ($form->{id},
 225                            (SELECT id FROM chart
 226                             WHERE accno = '$form->{AR}{receivables}'),
 227                     $amount, '$form->{"datepaid_$i"}')|;
 228         $dbh->do($query) || $form->dberror($query);
 229       }
 230       $form->{receivables} = $amount;
 231
 232       # add payment
 233       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 234                   transdate, source)
 235                   VALUES ($form->{id},
 236                          (SELECT id FROM chart
 237                           WHERE accno = '$form->{AR}{"paid_$i"}'),
 238                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 239                   '$form->{"source_$i"}')|;
 240       $dbh->do($query) || $form->dberror($query);
 241
 242
 243       # exchangerate difference for payment
 244       $amount = $form->round_amount($form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1), 2);
 245
 246       if ($amount != 0) {
 247         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 248                     transdate, fx_transaction, cleared)
 249                     VALUES ($form->{id},
 250                            (SELECT id FROM chart
 251                             WHERE accno = '$form->{AR}{"paid_$i"}'),
 252                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 253         $dbh->do($query) || $form->dberror($query);
 254       }
 255
 256       # exchangerate gain/loss
 257       $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - $form->{"exchangerate_$i"}), 2);
 258
 259       if ($amount != 0) {
 260         $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
 261         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 262                     transdate, fx_transaction, cleared)
 263                     VALUES ($form->{id}, (SELECT id FROM chart
 264                                           WHERE accno = '$accno'),
 265                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 266         $dbh->do($query) || $form->dberror($query);
 267       }
 268
 269       # update exchangerate record
 270       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 271         $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0);
 272       }
 273     }
 274   }
 275
 276
 277   my $rc = $dbh->commit;
 278   $dbh->disconnect;
 279
 280   $rc;
 281
 282 }
 283
 284
 285
 286 sub delete_transaction {
 287   my ($self, $myconfig, $form) = @_;
 288
 289   # connect to database, turn AutoCommit off
 290   my $dbh = $form->dbconnect_noauto($myconfig);
 291
 292   # check for other foreign currency transactions
 293   $form->delete_exchangerate($dbh) if ($form->{currency} ne $form->{defaultcurrency});
 294
 295   my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
 296   $dbh->do($query) || $form->dberror($query);
 297
 298   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 299   $dbh->do($query) || $form->dberror($query);
 300
 301   # commit
 302   my $rc = $dbh->commit;
 303   $dbh->disconnect;
 304
 305   $rc;
 306
 307 }
 308
 309
 310
 311 sub ar_transactions {
 312   my ($self, $myconfig, $form) = @_;
 313
 314   # connect to database
 315   my $dbh = $form->dbconnect($myconfig);
 316
 317   my $incemp = qq|, (SELECT e.name FROM employee e
 318                    WHERE a.employee_id = e.id) AS employee
 319                    | if ($form->{l_employee});
 320
 321   my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
 322                  a.duedate, a.netamount, a.amount, a.paid, c.name,
 323                  a.invoice, a.datepaid, a.terms, a.notes, a.shippingpoint
 324
 325                  $incemp
 326
 327                  FROM ar a, customer c
 328                  WHERE a.customer_id = c.id|;
 329
 330   if ($form->{customer_id}) {
 331     $query .= " AND a.customer_id = $form->{customer_id}";
 332   } else {
 333     if ($form->{customer}) {
 334       my $customer = $form->like(lc $form->{customer});
 335       $query .= " AND lower(c.name) LIKE '$customer'";
 336     }
 337   }
 338   if ($form->{invnumber}) {
 339     my $invnumber = $form->like(lc $form->{invnumber});
 340     $query .= " AND lower(a.invnumber) LIKE '$invnumber'";
 341   }
 342   if ($form->{ordnumber}) {
 343     my $ordnumber = $form->like(lc $form->{ordnumber});
 344     $query .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
 345   }
 346   if ($form->{notes}) {
 347     my $notes = $form->like(lc $form->{notes});
 348     $query .= " AND lower(a.notes) LIKE '$notes'";
 349   }
 350
 351   $query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
 352   $query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
 353   if ($form->{open} || $form->{closed}) {
 354     unless ($form->{open} && $form->{closed}) {
 355     $query .= " AND a.amount <> a.paid" if ($form->{open});
 356     $query .= " AND a.amount = a.paid" if ($form->{closed});
 357     }
 358   }
 359
 360   my @a = (transdate, invnumber, name);
 361   push @a, "employee" if $form->{l_employee};
 362   my $sortorder = join ', ', $form->sort_columns(@a);
 363   $sortorder = $form->{sort} unless $sortorder;
 364
 365   $query .= " ORDER by $sortorder";
 366
 367   my $sth = $dbh->prepare($query);
 368   $sth->execute || $form->dberror($query);
 369
 370   while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
 371     push @{ $form->{AR} }, $ar;
 372   }
 373
 374   $sth->finish;
 375   $dbh->disconnect;
 376
 377 }
 378
 379
 380 1;
 381