Einige Übersetzungen, einheitliche Bezeichnung
[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->{taxincluded} *= 1) {
107       if (!$form->{"korrektur_$i"}) {
108       $tax =
109         $form->{"amount_$i"} -
110         ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1));
111       } else {
112         $tax = $form->{"tax_$i"};
113       }
114       $amount = $form->{"amount_$i"} - $tax;
115       $form->{"amount_$i"} = $form->round_amount($amount, 2);
116       $diff += $amount - $form->{"amount_$i"};
117       $form->{"tax_$i"} = $form->round_amount($tax, 2);
118       $form->{netamount} += $form->{"amount_$i"};
119     } else {
120       if (!$form->{"korrektur_$i"}) {
121         $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"};
122       }
123       $form->{"tax_$i"} =
124         $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2);
125       $form->{netamount} += $form->{"amount_$i"};
126     }
127     
128     $form->{total_tax} += $form->{"tax_$i"};
129   }
130
131   # adjust paidaccounts if there is no date in the last row
132   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
133   $form->{paid} = 0;
134
135   # add payments
136   for $i (1 .. $form->{paidaccounts}) {
137     $form->{"paid_$i"} =
138       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
139                           2);
140
141     $form->{paid} += $form->{"paid_$i"};
142     $form->{datepaid} = $form->{"datepaid_$i"};
143
144   }
145
146   $form->{amount} = $form->{netamount} + $form->{total_tax};
147   $form->{paid}   =
148     $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
149
150   my ($query, $sth, $null);
151
152   ($null, $form->{employee_id}) = split /--/, $form->{employee};
153   unless ($form->{employee_id}) {
154     $form->get_employee($dbh);
155   }
156
157   # if we have an id delete old records
158   if ($form->{id}) {
159
160     # delete detail records
161     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
162     $dbh->do($query) || $form->dberror($query);
163
164   } else {
165     my $uid = rand() . time;
166
167     $uid .= $form->{login};
168
169     $uid = substr($uid, 2, 75);
170
171     $query = qq|INSERT INTO ar (invnumber, employee_id)
172                 VALUES ('$uid', $form->{employee_id})|;
173     $dbh->do($query) || $form->dberror($query);
174
175     $query = qq|SELECT a.id FROM ar a
176                 WHERE a.invnumber = '$uid'|;
177     $sth = $dbh->prepare($query);
178     $sth->execute || $form->dberror($query);
179
180     ($form->{id}) = $sth->fetchrow_array;
181     $sth->finish;
182
183   }
184
185   # update department
186   ($null, $form->{department_id}) = split(/--/, $form->{department});
187   $form->{department_id} *= 1;
188
189   # escape '
190   map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
191
192   # record last payment date in ar table
193   $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
194   my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
195
196   $query = qq|UPDATE ar set
197               invnumber = '$form->{invnumber}',
198               ordnumber = '$form->{ordnumber}',
199               transdate = '$form->{transdate}',
200               customer_id = $form->{customer_id},
201               taxincluded = '$form->{taxincluded}',
202               amount = $form->{amount},
203               duedate = '$form->{duedate}',
204               paid = $form->{paid},
205               datepaid = $datepaid,
206               netamount = $form->{netamount},
207               curr = '$form->{currency}',
208               notes = '$form->{notes}',
209               department_id = $form->{department_id},
210               employee_id = $form->{employee_id}
211               WHERE id = $form->{id}|;
212   $dbh->do($query) || $form->dberror($query);
213
214   # amount for AR account
215   $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
216
217   # update exchangerate
218   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
219     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate},
220                                $form->{exchangerate}, 0);
221   }
222
223   # add individual transactions for AR, amount and taxes
224   for $i (1 .. $form->{rowcount}) {
225     if ($form->{"amount_$i"} != 0) {
226       $project_id = 'NULL';
227       if ("amount_$i" =~ /amount_/) {
228         if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) {
229           $project_id = $form->{"project_id_$i"};
230         }
231       }
232       if ("amount_$i" =~ /amount/) {
233         $taxkey = $form->{AR_amounts}{"amount_$i"}{taxkey};
234       }
235
236       # insert detail records in acc_trans
237       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
238                                          project_id, taxkey)
239                   VALUES ($form->{id}, (SELECT c.id FROM chart c
240                                         WHERE c.accno = '$form->{AR_amounts}{"amount_$i"}'),
241                   $form->{"amount_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
242       $dbh->do($query) || $form->dberror($query);
243       if ($form->{"tax_$i"} != 0) {
244
245         # insert detail records in acc_trans
246         $query =
247           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
248                                           project_id, taxkey)
249                     VALUES ($form->{id}, (SELECT c.id FROM chart c
250                                           WHERE c.accno = '$form->{AR_amounts}{"tax_$i"}'),
251                     $form->{"tax_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
252         $dbh->do($query) || $form->dberror($query);
253       }
254     }
255   }
256
257   # add recievables
258   $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
259                                       project_id)
260               VALUES ($form->{id}, (SELECT c.id FROM chart c
261                                     WHERE c.accno = '$form->{AR_amounts}{receivables}'),
262               $form->{receivables}, '$form->{transdate}', $project_id)|;
263   $dbh->do($query) || $form->dberror($query);
264
265   # add paid transactions
266   for my $i (1 .. $form->{paidaccounts}) {
267     if ($form->{"paid_$i"} != 0) {
268
269       $form->{"AR_paid_$i"} =~ s/\"//g;
270       ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
271       $form->{"datepaid_$i"} = $form->{transdate}
272         unless ($form->{"datepaid_$i"});
273
274       $exchangerate = 0;
275       if ($form->{currency} eq $form->{defaultcurrency}) {
276         $form->{"exchangerate_$i"} = 1;
277       } else {
278         $exchangerate =
279           $form->check_exchangerate($myconfig, $form->{currency},
280                                     $form->{"datepaid_$i"}, 'buy');
281
282         $form->{"exchangerate_$i"} =
283           ($exchangerate)
284           ? $exchangerate
285           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
286       }
287
288       # if there is no amount and invtotal is zero there is no exchangerate
289       if ($form->{amount} == 0 && $form->{netamount} == 0) {
290         $form->{exchangerate} = $form->{"exchangerate_$i"};
291       }
292
293       # receivables amount
294       $amount =
295         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
296
297       if ($form->{receivables} != 0) {
298
299         # add receivable
300         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
301                     transdate)
302                     VALUES ($form->{id},
303                            (SELECT c.id FROM chart c
304                             WHERE c.accno = '$form->{AR}{receivables}'),
305                     $amount, '$form->{"datepaid_$i"}')|;
306         $dbh->do($query) || $form->dberror($query);
307       }
308       $form->{receivables} = $amount;
309
310       $form->{"memo_$i"} =~ s/\'/\'\'/g;
311
312       if ($form->{"paid_$i"} != 0) {
313
314         # add payment
315         $amount = $form->{"paid_$i"} * -1;
316         $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
317                     transdate, source, memo)
318                     VALUES ($form->{id},
319                            (SELECT c.id FROM chart c
320                             WHERE c.accno = '$form->{AR}{"paid_$i"}'),
321                     $amount, '$form->{"datepaid_$i"}',
322                     '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
323         $dbh->do($query) || $form->dberror($query);
324
325         # exchangerate difference for payment
326         $amount =
327           $form->round_amount(
328                     $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1,
329                     2);
330
331         if ($amount != 0) {
332           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
333                       transdate, fx_transaction, cleared)
334                       VALUES ($form->{id},
335                              (SELECT c.id FROM chart c
336                               WHERE c.accno = '$form->{AR}{"paid_$i"}'),
337                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
338           $dbh->do($query) || $form->dberror($query);
339         }
340
341         # exchangerate gain/loss
342         $amount =
343           $form->round_amount(
344                    $form->{"paid_$i"} *
345                      ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1,
346                    2);
347
348         if ($amount != 0) {
349           $accno =
350             ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
351           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
352                       transdate, fx_transaction, cleared)
353                       VALUES ($form->{id}, (SELECT c.id FROM chart c
354                                             WHERE c.accno = '$accno'),
355                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
356           $dbh->do($query) || $form->dberror($query);
357         }
358       }
359
360       # update exchangerate record
361       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
362         $form->update_exchangerate($dbh, $form->{currency},
363                                    $form->{"datepaid_$i"},
364                                    $form->{"exchangerate_$i"}, 0);
365       }
366     }
367   }
368
369   my $rc = $dbh->commit;
370   $dbh->disconnect;
371
372   $main::lxdebug->leave_sub();
373
374   return $rc;
375 }
376
377 sub delete_transaction {
378   $main::lxdebug->enter_sub();
379
380   my ($self, $myconfig, $form) = @_;
381
382   # connect to database, turn AutoCommit off
383   my $dbh = $form->dbconnect_noauto($myconfig);
384
385   my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
386   $dbh->do($query) || $form->dberror($query);
387
388   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
389   $dbh->do($query) || $form->dberror($query);
390
391   # commit
392   my $rc = $dbh->commit;
393   $dbh->disconnect;
394
395   $main::lxdebug->leave_sub();
396
397   return $rc;
398 }
399
400 sub ar_transactions {
401   $main::lxdebug->enter_sub();
402
403   my ($self, $myconfig, $form) = @_;
404
405   # connect to database
406   my $dbh = $form->dbconnect($myconfig);
407
408   my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
409                  a.duedate, a.netamount, a.amount, a.paid, c.name,
410                  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia,
411                  a.shippingpoint,
412                  e.name AS employee
413                  FROM ar a
414               JOIN customer c ON (a.customer_id = c.id)
415               LEFT JOIN employee e ON (a.employee_id = e.id)|;
416
417   my $where = "1 = 1";
418   if ($form->{customer_id}) {
419     $where .= " AND a.customer_id = $form->{customer_id}";
420   } else {
421     if ($form->{customer}) {
422       my $customer = $form->like(lc $form->{customer});
423       $where .= " AND lower(c.name) LIKE '$customer'";
424     }
425   }
426   if ($form->{department}) {
427     my ($null, $department_id) = split /--/, $form->{department};
428     $where .= " AND a.department_id = $department_id";
429   }
430   if ($form->{invnumber}) {
431     my $invnumber = $form->like(lc $form->{invnumber});
432     $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
433   }
434   if ($form->{ordnumber}) {
435     my $ordnumber = $form->like(lc $form->{ordnumber});
436     $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
437   }
438   if ($form->{notes}) {
439     my $notes = $form->like(lc $form->{notes});
440     $where .= " AND lower(a.notes) LIKE '$notes'";
441   }
442
443   $where .= " AND a.transdate >= '$form->{transdatefrom}'"
444     if $form->{transdatefrom};
445   $where .= " AND a.transdate <= '$form->{transdateto}'"
446     if $form->{transdateto};
447   if ($form->{open} || $form->{closed}) {
448     unless ($form->{open} && $form->{closed}) {
449       $where .= " AND a.amount <> a.paid" if ($form->{open});
450       $where .= " AND a.amount = a.paid"  if ($form->{closed});
451     }
452   }
453
454   my @a = (transdate, invnumber, name);
455   push @a, "employee" if $form->{l_employee};
456   my $sortorder = join ', ', $form->sort_columns(@a);
457   $sortorder = $form->{sort} if $form->{sort};
458
459   $query .= "WHERE $where
460              ORDER by $sortorder";
461
462   my $sth = $dbh->prepare($query);
463   $sth->execute || $form->dberror($query);
464
465   while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
466     push @{ $form->{AR} }, $ar;
467   }
468
469   $sth->finish;
470   $dbh->disconnect;
471
472   $main::lxdebug->leave_sub();
473 }
474
475 1;
476