sql-ledger/old/sql-ledger/SL/AP.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 Payables database backend routines
  26 #
  27 #======================================================================
  28
  29
  30 package AP;
  31
  32
  33 sub post_transaction {
  34   my ($self, $myconfig, $form) = @_;
  35
  36   # connect to database
  37   my $dbh = $form->dbconnect_noauto($myconfig);
  38
  39   my ($null, $taxrate, $amount);
  40   my $exchangerate = 0;
  41
  42   # split and store id numbers in link accounts
  43   ($form->{AP}{payables}) = split(/--/, $form->{AP});
  44   map { ($form->{AP}{"amount_$_"}) = split(/--/, $form->{"AP_amount_$_"}) } (1 .. $form->{rowcount});
  45
  46   if ($form->{currency} eq $form->{defaultcurrency}) {
  47     $form->{exchangerate} = 1;
  48   } else {
  49     $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
  50
  51     $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  52   }
  53
  54   # reverse and parse amounts
  55   for my $i (1 .. $form->{rowcount}) {
  56     $form->{"amount_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"}) * $form->{exchangerate} * -1, 2);
  57     $amount += ($form->{"amount_$i"} * -1);
  58   }
  59
  60   # this is for ap
  61   $form->{amount} = $amount;
  62
  63   # taxincluded doesn't make sense if there is no amount
  64   $form->{taxincluded} = 0 if ($form->{amount} == 0);
  65
  66   for my $item (split / /, $form->{taxaccounts}) {
  67     $form->{AP}{"tax_$item"} = $item;
  68
  69     $amount = $form->round_amount($form->parse_amount($myconfig, $form->{"tax_$item"}), 2);
  70
  71     $form->{"tax_$item"} = $form->round_amount($amount * $form->{exchangerate}, 2) * -1;
  72     $form->{total_tax} += ($form->{"tax_$item"} * -1);
  73   }
  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->{invpaid} = 0;
  80   # add payments
  81   for my $i (1 .. $form->{paidaccounts}) {
  82     $form->{"paid_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}), 2);
  83
  84     $form->{invpaid} += $form->{"paid_$i"};
  85     $form->{datepaid} = $form->{"datepaid_$i"};
  86
  87   }
  88
  89   $form->{invpaid} = $form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
  90
  91   if ($form->{taxincluded} *= 1) {
  92     for $i (1 .. $form->{rowcount}) {
  93       $tax = $form->{total_tax} * $form->{"amount_$i"} / $form->{amount};
  94       $amount = $form->{"amount_$i"} - $tax;
  95       $form->{"amount_$i"} = $form->round_amount($amount, 2);
  96       $diff += $amount - $form->{"amount_$i"};
  97     }
  98
  99     # deduct taxes from amount
 100     $form->{amount} -= $form->{total_tax};
 101     # deduct difference from amount_1
 102     $form->{amount_1} += $form->round_amount($diff, 2);
 103   }
 104
 105   $form->{netamount} = $form->{amount};
 106
 107   # store invoice total, this goes into ap table
 108   $form->{invtotal} = $form->{amount} + $form->{total_tax};
 109
 110   # amount for total AP
 111   $form->{payables} = $form->{invtotal};
 112
 113
 114   my ($query, $sth);
 115
 116   # if we have an id delete old records
 117   if ($form->{id}) {
 118
 119     # delete detail records
 120     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 121
 122     $dbh->do($query) || $form->dberror($query);
 123
 124   } else {
 125     my $uid = time;
 126     $uid .= $form->{login};
 127
 128     $query = qq|INSERT INTO ap (invnumber, employee_id)
 129                 VALUES ('$uid', (SELECT id FROM employee
 130                                  WHERE login = '$form->{login}') )|;
 131     $dbh->do($query) || $form->dberror($query);
 132
 133     $query = qq|SELECT id FROM ap
 134                 WHERE invnumber = '$uid'|;
 135     $sth = $dbh->prepare($query);
 136     $sth->execute || $form->dberror($query);
 137
 138     ($form->{id}) = $sth->fetchrow_array;
 139     $sth->finish;
 140
 141   }
 142
 143   # escape '
 144   $form->{notes} =~ s/'/''/g;
 145
 146   $form->{datepaid} = $form->{transdate} unless ($form->{datepaid});
 147   my $datepaid = ($form->{invpaid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
 148
 149   $query = qq|UPDATE ap SET
 150               invnumber = '$form->{invnumber}',
 151               transdate = '$form->{transdate}',
 152               ordnumber = '$form->{ordnumber}',
 153               vendor_id = $form->{vendor_id},
 154               taxincluded = '$form->{taxincluded}',
 155               amount = $form->{invtotal},
 156               duedate = '$form->{duedate}',
 157               paid = $form->{invpaid},
 158               datepaid = $datepaid,
 159               netamount = $form->{netamount},
 160               curr = '$form->{currency}',
 161               notes = '$form->{notes}'
 162               WHERE id = $form->{id}
 163              |;
 164   $dbh->do($query) || $form->dberror($query);
 165
 166
 167   # update exchangerate
 168   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 169     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
 170   }
 171
 172   # add individual transactions
 173   foreach my $item (keys %{ $form->{AP} }) {
 174     if ($form->{$item} != 0) {
 175       $project_id = 'NULL';
 176       if ($item =~ /amount_/) {
 177         if ($form->{"project_id_$'"} && $form->{"projectnumber_$'"}) {
 178           $project_id = $form->{"project_id_$'"};
 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->{AP}{$item}'),
 187                   $form->{$item}, '$form->{transdate}', $project_id)|;
 188       $dbh->do($query) || $form->dberror($query);
 189     }
 190   }
 191
 192   # if there is no amount but a payment record a payable
 193   if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 194     $form->{payables} = $form->{invpaid};
 195   }
 196
 197   # add paid transactions
 198   for my $i (1 .. $form->{paidaccounts}) {
 199     if ($form->{"paid_$i"} != 0) {
 200
 201       $exchangerate = 0;
 202       if ($form->{currency} eq $form->{defaultcurrency}) {
 203         $form->{"exchangerate_$i"} = 1;
 204       } else {
 205         $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell');
 206
 207         $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
 208       }
 209
 210
 211       # get paid account
 212       ($form->{AP}{"paid_$i"}) = split(/--/, $form->{"AP_paid_$i"});
 213       $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"});
 214
 215       # if there is no amount and invtotal is zero there is no exchangerate
 216       if ($form->{amount} == 0 && $form->{invtotal} == 0) {
 217         $form->{exchangerate} = $form->{"exchangerate_$i"};
 218       }
 219
 220       $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1, 2);
 221       if ($form->{payables}) {
 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->{AP}{payables}'),
 227                     $amount, '$form->{"datepaid_$i"}')|;
 228         $dbh->do($query) || $form->dberror($query);
 229       }
 230       $form->{payables} = $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->{AP}{"paid_$i"}'),
 238                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
 239                   '$form->{"source_$i"}')|;
 240       $dbh->do($query) || $form->dberror($query);
 241
 242       # add exchange rate difference
 243       $amount = $form->round_amount($form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1), 2);
 244       if ($amount != 0) {
 245         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 246                     transdate, fx_transaction, cleared)
 247                     VALUES ($form->{id},
 248                            (SELECT id FROM chart
 249                             WHERE accno = '$form->{AP}{"paid_$i"}'),
 250                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 251
 252         $dbh->do($query) || $form->dberror($query);
 253       }
 254
 255       # exchangerate gain/loss
 256       $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - $form->{"exchangerate_$i"}), 2);
 257
 258       if ($amount != 0) {
 259         $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
 260         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
 261                     transdate, fx_transaction, cleared)
 262                     VALUES ($form->{id}, (SELECT id FROM chart
 263                                           WHERE accno = '$accno'),
 264                     $amount, '$form->{"datepaid_$i"}', '1', '0')|;
 265         $dbh->do($query) || $form->dberror($query);
 266       }
 267
 268       # update exchange rate record
 269       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
 270         $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, 0, $form->{"exchangerate_$i"});
 271       }
 272     }
 273   }
 274
 275   my $rc = $dbh->commit;
 276   $dbh->disconnect;
 277
 278   $rc;
 279
 280 }
 281
 282
 283
 284
 285 sub delete_transaction {
 286   my ($self, $myconfig, $form) = @_;
 287
 288   # connect to database
 289   my $dbh = $form->dbconnect_noauto($myconfig);
 290
 291   # check for other foreign currency transactions
 292   $form->delete_exchangerate($dbh) if ($form->{currency} ne $form->{defaultcurrency});
 293
 294   my $query = qq|DELETE FROM ap WHERE id = $form->{id}|;
 295   $dbh->do($query) || $form->dberror($query);
 296
 297   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
 298   $dbh->do($query) || $form->dberror($query);
 299
 300   # commit and redirect
 301   my $rc = $dbh->commit;
 302   $dbh->disconnect;
 303
 304   $rc;
 305
 306 }
 307
 308
 309
 310
 311 sub ap_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.transdate, a.duedate,
 322                  a.amount, a.paid, a.ordnumber, v.name, a.invoice,
 323                  a.netamount, a.datepaid, a.notes
 324
 325                  $incemp
 326
 327                  FROM ap a, vendor v
 328                  WHERE a.vendor_id = v.id|;
 329
 330   if ($form->{vendor_id}) {
 331     $query .= " AND a.vendor_id = $form->{vendor_id}";
 332   } else {
 333     if ($form->{vendor}) {
 334       my $vendor = $form->like(lc $form->{vendor});
 335       $query .= " AND lower(v.name) LIKE '$vendor'";
 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 $self->{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 $ap = $sth->fetchrow_hashref(NAME_lc)) {
 371     push @{ $form->{AP} }, $ap;
 372   }
 373
 374   $sth->finish;
 375   $dbh->disconnect;
 376
 377 }
 378
 379
 380 1;
 381