Uebernahme der kompletten Version, so wie sie Philip als "Demo-Version" gezeigt hat...
[kivitendo-erp.git] / SL / AR.pm
1 #=====================================================================
2 # LX-Office ERP
3 # Copyright (C) 2004
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
6 #
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 2001
10 #
11 #  Author: Dieter Simader
12 #   Email: dsimader@sql-ledger.org
13 #     Web: http://www.sql-ledger.org
14 #
15 #  Contributors:
16 #
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
21 #
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
30 #
31 # Accounts Receivable module backend routines
32 #
33 #======================================================================
34
35 package AR;
36
37 use Data::Dumper;
38
39 sub post_transaction {
40   $main::lxdebug->enter_sub();
41
42   my ($self, $myconfig, $form) = @_;
43
44   my ($null, $taxrate, $amount, $tax, $diff);
45   my $exchangerate = 0;
46   my $i;
47
48   my $dbh = $form->dbconnect_noauto($myconfig);
49
50   if ($form->{currency} eq $form->{defaultcurrency}) {
51     $form->{exchangerate} = 1;
52   } else {
53     $exchangerate =
54       $form->check_exchangerate($myconfig, $form->{currency},
55                                 $form->{transdate}, 'buy');
56   }
57   for $i (1 .. $form->{rowcount}) {
58     $form->{AR_amounts}{"amount_$i"} =
59       (split(/--/, $form->{"AR_amount_$i"}))[0];
60   }
61   ($form->{AR_amounts}{receivables}) = split(/--/, $form->{ARselected});
62   ($form->{AR}{receivables})         = split(/--/, $form->{ARselected});
63
64   $form->{exchangerate} =
65     ($exchangerate)
66     ? $exchangerate
67     : $form->parse_amount($myconfig, $form->{exchangerate});
68
69   for $i (1 .. $form->{rowcount}) {
70
71     $form->{"amount_$i"} =
72       $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"})
73                             * $form->{exchangerate},
74                           2);
75
76     $form->{netamount} += $form->{"amount_$i"};
77
78     # parse tax_$i for later
79     $form->{"tax_$i"} = $form->parse_amount($myconfig, $form->{"tax_$i"});
80   }
81
82   # this is for ar
83
84   $form->{amount} = $form->{netamount};
85
86   $form->{tax}       = 0;
87   $form->{netamount} = 0;
88   $form->{total_tax} = 0;
89
90   # taxincluded doesn't make sense if there is no amount
91
92   $form->{taxincluded} = 0 if ($form->{amount} == 0);
93   for $i (1 .. $form->{rowcount}) {
94     ($form->{"taxkey_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
95
96     $query =
97       qq| SELECT c.accno, t.rate FROM chart c, tax t where c.id=t.chart_id AND t.taxkey=$form->{"taxkey_$i"}|;
98     $sth = $dbh->prepare($query);
99     $sth->execute || $form->dberror($query);
100     ($form->{AR_amounts}{"tax_$i"}, $form->{"taxrate_$i"}) =
101       $sth->fetchrow_array;
102     $form->{AR_amounts}{"tax_$i"}{taxkey}    = $form->{"taxkey_$i"};
103     $form->{AR_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"};
104
105     $sth->finish;
106     if (!$form->{"korrektur_$i"}) {
107       if ($form->{taxincluded} *= 1) {
108         $tax =
109           $form->{"amount_$i"} -
110           ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1));
111         $amount = $form->{"amount_$i"} - $tax;
112         $form->{"amount_$i"} = $form->round_amount($amount, 2);
113         $diff += $amount - $form->{"amount_$i"};
114         $form->{"tax_$i"} = $form->round_amount($tax, 2);
115         $form->{netamount} += $form->{"amount_$i"};
116       } else {
117         $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"};
118         $form->{"tax_$i"} =
119           $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2);
120         $form->{netamount} += $form->{"amount_$i"};
121       }
122     }
123     $form->{total_tax} += $form->{"tax_$i"};
124   }
125
126   # adjust paidaccounts if there is no date in the last row
127   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
128   $form->{paid} = 0;
129
130   # add payments
131   for $i (1 .. $form->{paidaccounts}) {
132     $form->{"paid_$i"} =
133       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
134                           2);
135
136     $form->{paid} += $form->{"paid_$i"};
137     $form->{datepaid} = $form->{"datepaid_$i"};
138
139   }
140
141   $form->{amount} = $form->{netamount} + $form->{total_tax};
142   $form->{paid}   =
143     $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
144
145   my ($query, $sth, $null);
146
147   ($null, $form->{employee_id}) = split /--/, $form->{employee};
148   unless ($form->{employee_id}) {
149     $form->get_employee($dbh);
150   }
151
152   # if we have an id delete old records
153   if ($form->{id}) {
154
155     # delete detail records
156     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
157     $dbh->do($query) || $form->dberror($query);
158
159   } else {
160     my $uid = rand() . time;
161
162     $uid .= $form->{login};
163
164     $uid = substr($uid, 2, 75);
165
166     $query = qq|INSERT INTO ar (invnumber, employee_id)
167                 VALUES ('$uid', $form->{employee_id})|;
168     $dbh->do($query) || $form->dberror($query);
169
170     $query = qq|SELECT a.id FROM ar a
171                 WHERE a.invnumber = '$uid'|;
172     $sth = $dbh->prepare($query);
173     $sth->execute || $form->dberror($query);
174
175     ($form->{id}) = $sth->fetchrow_array;
176     $sth->finish;
177
178   }
179
180   # update department
181   ($null, $form->{department_id}) = split(/--/, $form->{department});
182   $form->{department_id} *= 1;
183
184   # escape '
185   map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
186
187   # record last payment date in ar table
188   $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
189   my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
190
191   $query = qq|UPDATE ar set
192               invnumber = '$form->{invnumber}',
193               ordnumber = '$form->{ordnumber}',
194               transdate = '$form->{transdate}',
195               customer_id = $form->{customer_id},
196               taxincluded = '$form->{taxincluded}',
197               amount = $form->{amount},
198               duedate = '$form->{duedate}',
199               paid = $form->{paid},
200               datepaid = $datepaid,
201               netamount = $form->{netamount},
202               curr = '$form->{currency}',
203               notes = '$form->{notes}',
204               department_id = $form->{department_id},
205               employee_id = $form->{employee_id}
206               WHERE id = $form->{id}|;
207   $dbh->do($query) || $form->dberror($query);
208
209   # amount for AR account
210   $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
211
212   # update exchangerate
213   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
214     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate},
215                                $form->{exchangerate}, 0);
216   }
217
218   # add individual transactions for AR, amount and taxes
219   for $i (1 .. $form->{rowcount}) {
220     if ($form->{"amount_$i"} != 0) {
221       $project_id = 'NULL';
222       if ("amount_$i" =~ /amount_/) {
223         if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) {
224           $project_id = $form->{"project_id_$i"};
225         }
226       }
227       if ("amount_$i" =~ /amount/) {
228         $taxkey = $form->{AR_amounts}{"amount_$i"}{taxkey};
229       }
230
231       # insert detail records in acc_trans
232       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
233                                          project_id, taxkey)
234                   VALUES ($form->{id}, (SELECT c.id FROM chart c
235                                         WHERE c.accno = '$form->{AR_amounts}{"amount_$i"}'),
236                   $form->{"amount_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
237       $dbh->do($query) || $form->dberror($query);
238       if ($form->{"tax_$i"} != 0) {
239
240         # insert detail records in acc_trans
241         $query =
242           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
243                                           project_id, taxkey)
244                     VALUES ($form->{id}, (SELECT c.id FROM chart c
245                                           WHERE c.accno = '$form->{AR_amounts}{"tax_$i"}'),
246                     $form->{"tax_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
247         $dbh->do($query) || $form->dberror($query);
248       }
249     }
250   }
251
252   # add recievables
253   $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
254                                       project_id)
255               VALUES ($form->{id}, (SELECT c.id FROM chart c
256                                     WHERE c.accno = '$form->{AR_amounts}{receivables}'),
257               $form->{receivables}, '$form->{transdate}', $project_id)|;
258   $dbh->do($query) || $form->dberror($query);
259
260   # add paid transactions
261   for my $i (1 .. $form->{paidaccounts}) {
262     if ($form->{"paid_$i"} != 0) {
263
264       $form->{"AR_paid_$i"} =~ s/\"//g;
265       ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
266       $form->{"datepaid_$i"} = $form->{transdate}
267         unless ($form->{"datepaid_$i"});
268
269       $exchangerate = 0;
270       if ($form->{currency} eq $form->{defaultcurrency}) {
271         $form->{"exchangerate_$i"} = 1;
272       } else {
273         $exchangerate =
274           $form->check_exchangerate($myconfig, $form->{currency},
275                                     $form->{"datepaid_$i"}, 'buy');
276
277         $form->{"exchangerate_$i"} =
278           ($exchangerate)
279           ? $exchangerate
280           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
281       }
282
283       # if there is no amount and invtotal is zero there is no exchangerate
284       if ($form->{amount} == 0 && $form->{netamount} == 0) {
285         $form->{exchangerate} = $form->{"exchangerate_$i"};
286       }
287
288       # receivables amount
289       $amount =
290         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
291
292       if ($form->{receivables} != 0) {
293
294         # add receivable
295         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
296                     transdate)
297                     VALUES ($form->{id},
298                            (SELECT c.id FROM chart c
299                             WHERE c.accno = '$form->{AR}{receivables}'),
300                     $amount, '$form->{"datepaid_$i"}')|;
301         $dbh->do($query) || $form->dberror($query);
302       }
303       $form->{receivables} = $amount;
304
305       $form->{"memo_$i"} =~ s/\'/\'\'/g;
306
307       if ($form->{"paid_$i"} != 0) {
308
309         # add payment
310         $amount = $form->{"paid_$i"} * -1;
311         $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
312                     transdate, source, memo)
313                     VALUES ($form->{id},
314                            (SELECT c.id FROM chart c
315                             WHERE c.accno = '$form->{AR}{"paid_$i"}'),
316                     $amount, '$form->{"datepaid_$i"}',
317                     '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
318         $dbh->do($query) || $form->dberror($query);
319
320         # exchangerate difference for payment
321         $amount =
322           $form->round_amount(
323                     $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1,
324                     2);
325
326         if ($amount != 0) {
327           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
328                       transdate, fx_transaction, cleared)
329                       VALUES ($form->{id},
330                              (SELECT c.id FROM chart c
331                               WHERE c.accno = '$form->{AR}{"paid_$i"}'),
332                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
333           $dbh->do($query) || $form->dberror($query);
334         }
335
336         # exchangerate gain/loss
337         $amount =
338           $form->round_amount(
339                    $form->{"paid_$i"} *
340                      ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1,
341                    2);
342
343         if ($amount != 0) {
344           $accno =
345             ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
346           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
347                       transdate, fx_transaction, cleared)
348                       VALUES ($form->{id}, (SELECT c.id FROM chart c
349                                             WHERE c.accno = '$accno'),
350                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
351           $dbh->do($query) || $form->dberror($query);
352         }
353       }
354
355       # update exchangerate record
356       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
357         $form->update_exchangerate($dbh, $form->{currency},
358                                    $form->{"datepaid_$i"},
359                                    $form->{"exchangerate_$i"}, 0);
360       }
361     }
362   }
363
364   my $rc = $dbh->commit;
365   $dbh->disconnect;
366
367   $main::lxdebug->leave_sub();
368
369   return $rc;
370 }
371
372 sub delete_transaction {
373   $main::lxdebug->enter_sub();
374
375   my ($self, $myconfig, $form) = @_;
376
377   # connect to database, turn AutoCommit off
378   my $dbh = $form->dbconnect_noauto($myconfig);
379
380   my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
381   $dbh->do($query) || $form->dberror($query);
382
383   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
384   $dbh->do($query) || $form->dberror($query);
385
386   # commit
387   my $rc = $dbh->commit;
388   $dbh->disconnect;
389
390   $main::lxdebug->leave_sub();
391
392   return $rc;
393 }
394
395 sub ar_transactions {
396   $main::lxdebug->enter_sub();
397
398   my ($self, $myconfig, $form) = @_;
399
400   # connect to database
401   my $dbh = $form->dbconnect($myconfig);
402
403   my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
404                  a.duedate, a.netamount, a.amount, a.paid, c.name,
405                  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia,
406                  a.shippingpoint,
407                  e.name AS employee
408                  FROM ar a
409               JOIN customer c ON (a.customer_id = c.id)
410               LEFT JOIN employee e ON (a.employee_id = e.id)|;
411
412   my $where = "1 = 1";
413   if ($form->{customer_id}) {
414     $where .= " AND a.customer_id = $form->{customer_id}";
415   } else {
416     if ($form->{customer}) {
417       my $customer = $form->like(lc $form->{customer});
418       $where .= " AND lower(c.name) LIKE '$customer'";
419     }
420   }
421   if ($form->{department}) {
422     my ($null, $department_id) = split /--/, $form->{department};
423     $where .= " AND a.department_id = $department_id";
424   }
425   if ($form->{invnumber}) {
426     my $invnumber = $form->like(lc $form->{invnumber});
427     $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
428   }
429   if ($form->{ordnumber}) {
430     my $ordnumber = $form->like(lc $form->{ordnumber});
431     $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
432   }
433   if ($form->{notes}) {
434     my $notes = $form->like(lc $form->{notes});
435     $where .= " AND lower(a.notes) LIKE '$notes'";
436   }
437
438   $where .= " AND a.transdate >= '$form->{transdatefrom}'"
439     if $form->{transdatefrom};
440   $where .= " AND a.transdate <= '$form->{transdateto}'"
441     if $form->{transdateto};
442   if ($form->{open} || $form->{closed}) {
443     unless ($form->{open} && $form->{closed}) {
444       $where .= " AND a.amount <> a.paid" if ($form->{open});
445       $where .= " AND a.amount = a.paid"  if ($form->{closed});
446     }
447   }
448
449   my @a = (transdate, invnumber, name);
450   push @a, "employee" if $form->{l_employee};
451   my $sortorder = join ', ', $form->sort_columns(@a);
452   $sortorder = $form->{sort} if $form->{sort};
453
454   $query .= "WHERE $where
455              ORDER by $sortorder";
456
457   my $sth = $dbh->prepare($query);
458   $sth->execute || $form->dberror($query);
459
460   while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
461     push @{ $form->{AR} }, $ar;
462   }
463
464   $sth->finish;
465   $dbh->disconnect;
466
467   $main::lxdebug->leave_sub();
468 }
469
470 1;
471