AP: single-dbh
[kivitendo-erp.git] / SL / AP.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 Payables database backend routines
32 #
33 #======================================================================
34
35 package AP;
36
37 use SL::DATEV qw(:CONSTANTS);
38 use SL::DBUtils;
39 use SL::IO;
40 use SL::MoreCommon;
41 use SL::DB::Default;
42 use SL::Util qw(trim);
43 use SL::DB;
44 use Data::Dumper;
45
46 use strict;
47
48 sub post_transaction {
49   my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
50   $main::lxdebug->enter_sub();
51
52   my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form, $provided_dbh, $payments_only);
53
54   $::lxdebug->leave_sub;
55   return $rc;
56 }
57
58 sub _post_transaction {
59   my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
60   my $dbh = $provided_dbh || SL::DB->client->dbh;
61
62   my ($null, $taxrate, $amount);
63   my $exchangerate = 0;
64
65   $form->{defaultcurrency} = $form->get_default_currency($myconfig);
66   $form->{taxincluded} = 0 unless $form->{taxincluded};
67
68   ($null, $form->{department_id}) = split(/--/, $form->{department});
69
70   if ($form->{currency} eq $form->{defaultcurrency}) {
71     $form->{exchangerate} = 1;
72   } else {
73     $exchangerate         = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
74     $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
75   }
76
77   for my $i (1 .. $form->{rowcount}) {
78     $form->{AP_amounts}{"amount_$i"} =
79       (split(/--/, $form->{"AP_amount_$i"}))[0];
80   }
81
82   ($form->{AP_amounts}{payables}) = split(/--/, $form->{APselected});
83   ($form->{AP_payables})          = split(/--/, $form->{APselected});
84
85   # calculate the totals while calculating and reformatting the $amount_$i and $tax_$i
86   ($form->{netamount},$form->{total_tax},$form->{invtotal}) = $form->calculate_arap('buy',$form->{taxincluded}, $form->{exchangerate});
87
88   # adjust paidaccounts if there is no date in the last row
89   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
90
91   $form->{invpaid} = 0;
92
93   # add payments
94   for my $i (1 .. $form->{paidaccounts}) {
95     $form->{"paid_$i"} =
96       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
97                           2);
98
99     $form->{invpaid} += $form->{"paid_$i"};
100     $form->{datepaid} = $form->{"datepaid_$i"};
101
102   }
103
104   $form->{invpaid} =
105     $form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
106
107   # # store invoice total, this goes into ap table
108   # $form->{invtotal} = $form->{netamount} + $form->{total_tax};
109
110   # amount for total AP
111   $form->{payables} = $form->{invtotal};
112
113   # update exchangerate
114   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
115     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0,
116                                $form->{exchangerate});
117   }
118
119   my ($query, $sth, @values);
120
121   if (!$payments_only) {
122     # if we have an id delete old records
123     if ($form->{id}) {
124
125       # delete detail records
126       $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
127       do_query($form, $dbh, $query, $form->{id});
128
129     } else {
130
131       ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
132
133       $query =
134         qq|INSERT INTO ap (id, invnumber, employee_id,currency_id, taxzone_id) | .
135         qq|VALUES (?, ?, (SELECT e.id FROM employee e WHERE e.login = ?),
136                       (SELECT id FROM currencies WHERE name = ?), (SELECT taxzone_id FROM vendor WHERE id = ?) )|;
137       do_query($form, $dbh, $query, $form->{id}, $form->{invnumber}, $::myconfig{login}, $form->{currency}, $form->{vendor_id});
138
139     }
140
141     $query = qq|UPDATE ap SET invnumber = ?,
142                 transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?,
143                 amount = ?, duedate = ?, paid = ?, netamount = ?,
144                 currency_id = (SELECT id FROM currencies WHERE name = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?,
145                 globalproject_id = ?, direct_debit = ?
146                WHERE id = ?|;
147     @values = ($form->{invnumber}, conv_date($form->{transdate}),
148                   $form->{ordnumber}, conv_i($form->{vendor_id}),
149                   $form->{taxincluded} ? 't' : 'f', $form->{invtotal},
150                   conv_date($form->{duedate}), $form->{invpaid},
151                   $form->{netamount},
152                   $form->{currency}, $form->{notes},
153                   conv_i($form->{department_id}), $form->{storno},
154                   $form->{storno_id}, conv_i($form->{globalproject_id}),
155                   $form->{direct_debit} ? 't' : 'f',
156                   $form->{id});
157     do_query($form, $dbh, $query, @values);
158
159     $form->new_lastmtime('ap');
160
161     # add individual transactions
162     for my $i (1 .. $form->{rowcount}) {
163       if ($form->{"amount_$i"} != 0) {
164         my $project_id;
165         $project_id = conv_i($form->{"project_id_$i"});
166
167         # insert detail records in acc_trans
168         $query =
169           qq|INSERT INTO acc_trans | .
170           qq|  (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link)| .
171           qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | .
172           qq|  ?, ?, ?, ?, ?,| .
173           qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|;
174         @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"},
175                    $form->{"amount_$i"}, conv_date($form->{transdate}),
176                    $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}),
177                    $form->{AP_amounts}{"amount_$i"});
178         do_query($form, $dbh, $query, @values);
179
180         if ($form->{"tax_$i"} != 0) {
181           # insert detail records in acc_trans
182           $query =
183             qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | .
184             qq|  project_id, taxkey, tax_id, chart_link) | .
185             qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | .
186             qq|  ?, ?, ?, ?, ?,| .
187             qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|;
188           @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"},
189                      $form->{"tax_$i"}, conv_date($form->{transdate}),
190                      $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}),
191                      $form->{AP_amounts}{"tax_$i"});
192           do_query($form, $dbh, $query, @values);
193         }
194
195       }
196     }
197
198     # add payables
199     $query =
200       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) | .
201       qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | .
202       qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| .
203       qq|        (SELECT tax_id| .
204       qq|         FROM taxkeys| .
205       qq|         WHERE chart_id= (SELECT id | .
206       qq|                          FROM chart| .
207       qq|                          WHERE accno = ?)| .
208       qq|         AND startdate <= ?| .
209       qq|         ORDER BY startdate DESC LIMIT 1),| .
210       qq|        (SELECT c.link FROM chart c WHERE c.accno = ?))|;
211     @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
212                conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate}),
213                $form->{AP_amounts}{payables});
214     do_query($form, $dbh, $query, @values);
215   }
216
217   # if there is no amount but a payment record a payable
218   if ($form->{amount} == 0 && $form->{invtotal} == 0) {
219     $form->{payables} = $form->{invpaid};
220   }
221
222   # add paid transactions
223   for my $i (1 .. $form->{paidaccounts}) {
224
225     if ($form->{"acc_trans_id_$i"} && $payments_only && (SL::DB::Default->get->payments_changeable == 0)) {
226       next;
227     }
228
229     if ($form->{"paid_$i"} != 0) {
230       my $project_id = conv_i($form->{"paid_project_id_$i"});
231
232       $exchangerate = 0;
233       if ($form->{currency} eq $form->{defaultcurrency}) {
234         $form->{"exchangerate_$i"} = 1;
235       } else {
236         $exchangerate              = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell');
237         $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
238       }
239       $form->{"AP_paid_$i"} =~ s/\"//g;
240
241       # get paid account
242
243       ($form->{"AP_paid_account_$i"}) = split(/--/, $form->{"AP_paid_$i"});
244       $form->{"datepaid_$i"} = $form->{transdate}
245         unless ($form->{"datepaid_$i"});
246
247       # if there is no amount and invtotal is zero there is no exchangerate
248       if ($form->{amount} == 0 && $form->{invtotal} == 0) {
249         $form->{exchangerate} = $form->{"exchangerate_$i"};
250       }
251
252       $amount =
253         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1,
254                             2);
255       if ($form->{payables}) {
256         $query =
257           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link) | .
258           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | .
259           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| .
260           qq|        (SELECT tax_id| .
261           qq|         FROM taxkeys| .
262           qq|         WHERE chart_id= (SELECT id | .
263           qq|                          FROM chart| .
264           qq|                          WHERE accno = ?)| .
265           qq|         AND startdate <= ?| .
266           qq|         ORDER BY startdate DESC LIMIT 1),| .
267           qq|        (SELECT c.link FROM chart c WHERE c.accno = ?))|;
268         @values = ($form->{id}, $form->{AP_payables}, $amount,
269                    conv_date($form->{"datepaid_$i"}), $project_id,
270                    $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"}),
271                    $form->{AP_payables});
272         do_query($form, $dbh, $query, @values);
273       }
274       $form->{payables} = $amount;
275
276       # add payment
277       my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
278       $query =
279         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id, chart_link) | .
280         qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | .
281         qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | .
282         qq|        (SELECT tax_id| .
283         qq|         FROM taxkeys| .
284         qq|         WHERE chart_id= (SELECT id | .
285         qq|                          FROM chart| .
286         qq|                          WHERE accno = ?)| .
287         qq|         AND startdate <= ?| .
288         qq|         ORDER BY startdate DESC LIMIT 1),| .
289         qq|        (SELECT c.link FROM chart c WHERE c.accno = ?))|;
290       @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"},
291                  conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"},
292                  $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"},
293                  $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}),
294                  $form->{"AP_paid_account_$i"});
295       do_query($form, $dbh, $query, @values);
296
297       # add exchange rate difference
298       $amount =
299         $form->round_amount($form->{"paid_$i"} *
300                             ($form->{"exchangerate_$i"} - 1), 2);
301       if ($amount != 0) {
302         $query =
303           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link) | .
304           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
305           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | .
306           qq|        (SELECT tax_id| .
307           qq|         FROM taxkeys| .
308           qq|         WHERE chart_id= (SELECT id | .
309           qq|                          FROM chart| .
310           qq|                          WHERE accno = ?)| .
311           qq|         AND startdate <= ?| .
312           qq|         ORDER BY startdate DESC LIMIT 1),| .
313           qq|        (SELECT c.link FROM chart c WHERE c.accno = ?))|;
314         @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount,
315                    conv_date($form->{"datepaid_$i"}), $project_id,
316                    $form->{"AP_paid_account_$i"},
317                    $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}),
318                    $form->{"AP_paid_account_$i"});
319         do_query($form, $dbh, $query, @values);
320       }
321
322       # exchangerate gain/loss
323       $amount =
324         $form->round_amount($form->{"paid_$i"} *
325                             ($form->{exchangerate} -
326                              $form->{"exchangerate_$i"}), 2);
327
328       if ($amount != 0) {
329         # fetch fxgain and fxloss chart info from defaults if charts aren't already filled in form
330         if ( !$form->{fxgain_accno} && $::instance_conf->get_fxgain_accno_id ) {
331           $form->{fxgain_accno} = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxgain_accno_id)->accno;
332         };
333         if ( !$form->{fxloss_accno} && $::instance_conf->get_fxloss_accno_id ) {
334           $form->{fxloss_accno} = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxloss_accno_id)->accno;
335         };
336         die "fxloss_accno missing" if $amount < 0 and not $form->{fxloss_accno};
337         die "fxgain_accno missing" if $amount > 0 and not $form->{fxgain_accno};
338         $query =
339           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link) | .
340           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
341           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| .
342           qq|        (SELECT tax_id| .
343           qq|         FROM taxkeys| .
344           qq|         WHERE chart_id= (SELECT id | .
345           qq|                          FROM chart| .
346           qq|                          WHERE accno = ?)| .
347           qq|         AND startdate <= ?| .
348           qq|         ORDER BY startdate DESC LIMIT 1),| .
349           qq|        (SELECT c.link FROM chart c WHERE c.accno = ?))|;
350         @values = ($form->{id},
351                    ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno},
352                    $amount, conv_date($form->{"datepaid_$i"}), $project_id,
353                    ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno},
354                    ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, conv_date($form->{"datepaid_$i"}),
355                    ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno});
356         do_query($form, $dbh, $query, @values);
357       }
358
359       # update exchange rate record
360       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
361         $form->update_exchangerate($dbh, $form->{currency},
362                                    $form->{"datepaid_$i"},
363                                    0, $form->{"exchangerate_$i"});
364       }
365     }
366   }
367
368   if ($payments_only) {
369     $query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|;
370     do_query($form, $dbh, $query,  $form->{invpaid}, $form->{invpaid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
371     $form->new_lastmtime('ap');
372   }
373
374   IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh);
375
376   # safety check datev export
377   if ($::instance_conf->get_datev_check_on_ap_transaction) {
378     my $transdate = $::form->{transdate} ? DateTime->from_lxoffice($::form->{transdate}) : undef;
379     $transdate  ||= DateTime->today;
380
381     my $datev = SL::DATEV->new(
382       exporttype => DATEV_ET_BUCHUNGEN,
383       format     => DATEV_FORMAT_KNE,
384       dbh        => $dbh,
385       trans_id   => $form->{id},
386     );
387
388     $datev->export;
389
390     if ($datev->errors) {
391       die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
392     }
393   }
394
395   return 1;
396 }
397
398 sub delete_transaction {
399   $main::lxdebug->enter_sub();
400
401   my ($self, $myconfig, $form) = @_;
402
403   SL::DB->client->with_transaction(sub {
404     my $query = qq|DELETE FROM ap WHERE id = ?|;
405     do_query($form, SL::DB->client->dbh, $query, $form->{id});
406   });
407
408   $main::lxdebug->leave_sub();
409
410   return 1;
411 }
412
413 sub ap_transactions {
414   $main::lxdebug->enter_sub();
415
416   my ($self, $myconfig, $form) = @_;
417
418   # connect to database
419   my $dbh = $form->get_standard_dbh($myconfig);
420
421   my $query =
422     qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, a.amount, a.paid, | .
423     qq|  a.ordnumber, v.name, a.invoice, a.netamount, a.datepaid, a.notes, | .
424     qq|  a.globalproject_id, a.storno, a.storno_id, a.direct_debit, | .
425     qq|  pr.projectnumber AS globalprojectnumber, | .
426     qq|  e.name AS employee, | .
427     qq|  v.vendornumber, v.country, v.ustid, | .
428     qq|  tz.description AS taxzone, | .
429     qq|  pt.description AS payment_terms, | .
430     qq{  ( SELECT ch.accno || ' -- ' || ch.description
431            FROM acc_trans at
432            LEFT JOIN chart ch ON ch.id = at.chart_id
433            WHERE ch.link ~ 'AP[[:>:]]'
434             AND at.trans_id = a.id
435             LIMIT 1
436           ) AS charts } .
437     qq|FROM ap a | .
438     qq|JOIN vendor v ON (a.vendor_id = v.id) | .
439     qq|LEFT JOIN contacts cp ON (a.cp_id = cp.cp_id) | .
440     qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
441     qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id) | .
442     qq|LEFT JOIN tax_zones tz ON (tz.id = a.taxzone_id)| .
443     qq|LEFT JOIN payment_terms pt ON (pt.id = a.payment_id)|;
444
445   my $where = '';
446
447   unless ( $::auth->assert('show_ap_transactions', 1) ) {
448     $where .= " AND NOT invoice = 'f' ";  # remove ap transactions from Sales -> Reports -> Invoices
449   };
450
451   my @values;
452
453   if ($form->{vendor_id}) {
454     $where .= " AND a.vendor_id = ?";
455     push(@values, $form->{vendor_id});
456   } elsif ($form->{vendor}) {
457     $where .= " AND v.name ILIKE ?";
458     push(@values, like($form->{vendor}));
459   }
460   if ($form->{"cp_name"}) {
461     $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
462     push(@values, (like($form->{"cp_name"}))x2);
463   }
464   if ($form->{department}) {
465     # Ã¤hnlich wie commit 0bbfb33b6aa8e38bb6c81d1684ab7d08e5b5c5af abteilung
466     # wird so nicht mehr als zeichenkette zusammengebaut
467     # hätte zu ee9f9f9aa4c3b9d5d20ab10a45c12bcaa6aa78d0 auffallen können ;-) jan
468     #my ($null, $department_id) = split /--/, $form->{department};
469     $where .= " AND a.department_id = ?";
470     push(@values, $form->{department});
471   }
472   if ($form->{invnumber}) {
473     $where .= " AND a.invnumber ILIKE ?";
474     push(@values, like($form->{invnumber}));
475   }
476   if ($form->{ordnumber}) {
477     $where .= " AND a.ordnumber ILIKE ?";
478     push(@values, like($form->{ordnumber}));
479   }
480   if ($form->{notes}) {
481     $where .= " AND lower(a.notes) LIKE ?";
482     push(@values, like($form->{notes}));
483   }
484   if ($form->{project_id}) {
485     $where .=
486       qq| AND ((a.globalproject_id = ?) OR EXISTS | .
487       qq|  (SELECT * FROM invoice i | .
488       qq|   WHERE i.project_id = ? AND i.trans_id = a.id) | .
489       qq| OR EXISTS | .
490       qq|  (SELECT * FROM acc_trans at | .
491       qq|   WHERE at.project_id = ? AND at.trans_id = a.id)| .
492       qq|  )|;
493     push(@values, $form->{project_id}, $form->{project_id}, $form->{project_id});
494   }
495
496   if ($form->{transdatefrom}) {
497     $where .= " AND a.transdate >= ?";
498     push(@values, trim($form->{transdatefrom}));
499   }
500   if ($form->{transdateto}) {
501     $where .= " AND a.transdate <= ?";
502     push(@values, trim($form->{transdateto}));
503   }
504   if ($form->{open} || $form->{closed}) {
505     unless ($form->{open} && $form->{closed}) {
506       $where .= " AND a.amount <> a.paid" if ($form->{open});
507       $where .= " AND a.amount = a.paid"  if ($form->{closed});
508     }
509   }
510
511   if ($form->{parts_partnumber}) {
512     $where .= <<SQL;
513  AND EXISTS (
514         SELECT invoice.trans_id
515         FROM invoice
516         LEFT JOIN parts ON (invoice.parts_id = parts.id)
517         WHERE (invoice.trans_id = a.id)
518           AND (parts.partnumber ILIKE ?)
519         LIMIT 1
520       )
521 SQL
522     push @values, like($form->{parts_partnumber});
523   }
524
525   if ($form->{parts_description}) {
526     $where .= <<SQL;
527  AND EXISTS (
528         SELECT invoice.trans_id
529         FROM invoice
530         WHERE (invoice.trans_id = a.id)
531           AND (invoice.description ILIKE ?)
532         LIMIT 1
533       )
534 SQL
535     push @values, like($form->{parts_description});
536   }
537
538   if ($where) {
539     substr($where, 0, 4, " WHERE ");
540     $query .= $where;
541   }
542
543   my @a = qw(transdate invnumber name);
544   push @a, "employee" if $form->{l_employee};
545   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
546   my $sortorder = join(', ', map { "$_ $sortdir" } @a);
547
548   if (grep({ $_ eq $form->{sort} } qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee transaction_description direct_debit))) {
549     $sortorder = $form->{sort} . " $sortdir";
550   }
551
552   $query .= " ORDER BY $sortorder";
553
554   my @result = selectall_hashref_query($form, $dbh, $query, @values);
555
556   $form->{AP} = [ @result ];
557
558   $main::lxdebug->leave_sub();
559 }
560
561 sub get_transdate {
562   $main::lxdebug->enter_sub();
563
564   my ($self, $myconfig, $form) = @_;
565
566   # connect to database
567   my $dbh = $form->dbconnect($myconfig);
568
569   my $query =
570     "SELECT COALESCE(" .
571     "  (SELECT transdate FROM ap WHERE id = " .
572     "    (SELECT MAX(id) FROM ap) LIMIT 1), " .
573     "  current_date)";
574   ($form->{transdate}) = $dbh->selectrow_array($query);
575
576   $dbh->disconnect;
577
578   $main::lxdebug->leave_sub();
579 }
580
581 sub _delete_payments {
582   $main::lxdebug->enter_sub();
583
584   my ($self, $form, $dbh) = @_;
585
586   my @delete_acc_trans_ids;
587
588   # Delete old payment entries from acc_trans.
589   my $query =
590     qq|SELECT acc_trans_id
591        FROM acc_trans
592        WHERE (trans_id = ?) AND fx_transaction
593
594        UNION
595
596        SELECT at.acc_trans_id
597        FROM acc_trans at
598        LEFT JOIN chart c ON (at.chart_id = c.id)
599        WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|;
600   push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
601
602   $query =
603     qq|SELECT at.acc_trans_id
604        FROM acc_trans at
605        LEFT JOIN chart c ON (at.chart_id = c.id)
606        WHERE (trans_id = ?)
607          AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%'))
608        ORDER BY at.acc_trans_id
609        OFFSET 1|;
610   push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
611
612   if (@delete_acc_trans_ids) {
613     $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
614     do_query($form, $dbh, $query);
615   }
616
617   $main::lxdebug->leave_sub();
618 }
619
620 sub post_payment {
621   my ($self, $myconfig, $form, $locale) = @_;
622   $main::lxdebug->enter_sub();
623
624   my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, $myconfig, $form, $locale);
625
626   $::lxdebug->leave_sub;
627   return $rc;
628 }
629
630 sub _post_payment {
631   my ($self, $myconfig, $form, $locale) = @_;
632
633   my $dbh = SL::DB->client->dbh;
634
635   my (%payments, $old_form, $row, $item, $query, %keep_vars);
636
637   $old_form = save_form();
638
639   # Delete all entries in acc_trans from prior payments.
640   if (SL::DB::Default->get->payments_changeable != 0) {
641     $self->_delete_payments($form, $dbh);
642   }
643
644   # Save the new payments the user made before cleaning up $form.
645   my $payments_re = '^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^paid_project_id_\d+$|^AP_paid_\d+$|^paidaccounts$';
646   map { $payments{$_} = $form->{$_} } grep m/$payments_re/, keys %{ $form };
647
648   # Clean up $form so that old content won't tamper the results.
649   %keep_vars = map { $_, 1 } qw(login password id);
650   map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
651
652   # Retrieve the invoice from the database.
653   $form->create_links('AP', $myconfig, 'vendor', $dbh);
654
655   # Restore the payment options from the user input.
656   map { $form->{$_} = $payments{$_} } keys %payments;
657
658   # Set up the content of $form in the way that AR::post_transaction() expects.
659
660   $self->setup_form($form, 1);
661
662   $form->{exchangerate}    = $form->format_amount($myconfig, $form->{exchangerate});
663   $form->{defaultcurrency} = $form->get_default_currency($myconfig);
664
665   # Get the AP accno.
666   $query =
667     qq|SELECT c.accno
668        FROM acc_trans at
669        LEFT JOIN chart c ON (at.chart_id = c.id)
670        WHERE (trans_id = ?)
671          AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%'))
672        ORDER BY at.acc_trans_id
673        LIMIT 1|;
674
675   ($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
676
677   # Post the new payments.
678   $self->post_transaction($myconfig, $form, $dbh, 1);
679
680   restore_form($old_form);
681
682   return 1;
683 }
684
685 sub setup_form {
686   $main::lxdebug->enter_sub();
687
688   my ($self, $form, $for_post_payments) = @_;
689
690   my ($exchangerate, $i, $j, $k, $key, $akey, $ref, $index, $taxamount, $totalamount, $totaltax, $totalwithholding, $withholdingrate,
691       $tax, $diff);
692
693   # forex
694   $form->{forex} = $form->{exchangerate};
695   $exchangerate = ($form->{exchangerate}) ? $form->{exchangerate} : 1;
696
697   foreach $key (keys %{ $form->{AP_links} }) {
698     foreach $ref (@{ $form->{AP_links}{$key} }) {
699       if ($key eq "AP_paid") {
700         $form->{"select$key"} .= "<option value=\"$ref->{accno}\">$ref->{accno}--$ref->{description}</option>\n";
701       } else {
702         $form->{"select$key"} .= "<option value=\"$ref->{accno}--$ref->{tax_id}\">$ref->{accno}--$ref->{description}</option>\n";
703       }
704     }
705
706     $form->{$key} = $form->{"select$key"};
707
708     $j = 0;
709     $k = 0;
710
711     # if there is a value we have an old entry
712     next unless $form->{acc_trans}{$key};
713
714     # do not use old entries for payments. They come from the form
715     # even if they are not changeable (then they are in hiddens)
716     next if $for_post_payments && $key eq "AP_paid";
717
718     for $i (1 .. scalar @{ $form->{acc_trans}{$key} }) {
719
720       if ($key eq "AP_paid") {
721         $j++;
722         $form->{"AP_paid_$j"}         = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}";
723         $form->{"acc_trans_id_$j"}    = $form->{acc_trans}{$key}->[$i - 1]->{acc_trans_id};
724         $form->{"paid_$j"}            = $form->{acc_trans}{$key}->[$i - 1]->{amount};
725         $form->{"datepaid_$j"}        = $form->{acc_trans}{$key}->[$i - 1]->{transdate};
726         $form->{"gldate_$j"}          = $form->{acc_trans}{$key}->[$i - 1]->{gldate};
727         $form->{"source_$j"}          = $form->{acc_trans}{$key}->[$i - 1]->{source};
728         $form->{"memo_$j"}            = $form->{acc_trans}{$key}->[$i - 1]->{memo};
729
730         $form->{"exchangerate_$i"}    = $form->{acc_trans}{$key}->[$i - 1]->{exchangerate};
731         $form->{"forex_$j"}           = $form->{"exchangerate_$i"};
732         $form->{"AP_paid_$j"}         = $form->{acc_trans}{$key}->[$i-1]->{accno};
733         $form->{"paid_project_id_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{project_id};
734         $form->{paidaccounts}++;
735
736       } else {
737         $akey = $key;
738         $akey =~ s/AP_//;
739
740         if (($key eq "AP_tax") || ($key eq "AR_tax")) {
741           $form->{"${key}_$form->{acc_trans}{$key}->[$i-1]->{accno}"}  = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}";
742           $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2);
743
744           if ($form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"} > 0) {
745             $totaltax += $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"};
746           } else {
747             $totalwithholding += $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"};
748             $withholdingrate  += $form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"};
749           }
750
751           $index                 = $form->{acc_trans}{$key}->[$i - 1]->{index};
752           $form->{"tax_$index"}  = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} * -1 / $exchangerate, 2);
753           $totaltax             += $form->{"tax_$index"};
754
755         } else {
756           $k++;
757           $form->{"${akey}_$k"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2);
758
759           if ($akey eq 'amount') {
760             $form->{rowcount}++;
761             $form->{"${akey}_$i"} *= -1;
762             $totalamount          += $form->{"${akey}_$i"};
763             $form->{taxrate}       = $form->{acc_trans}{$key}->[$i - 1]->{rate};
764
765             $form->{"projectnumber_$k"}    = "$form->{acc_trans}{$key}->[$i-1]->{projectnumber}";
766             $form->{"oldprojectnumber_$k"} = $form->{"projectnumber_$k"};
767             $form->{"project_id_$k"}       = "$form->{acc_trans}{$key}->[$i-1]->{project_id}";
768           }
769
770           $form->{"${key}_$k"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}";
771
772           my $q_description    = quotemeta($form->{acc_trans}{$key}->[$i-1]->{description});
773           $form->{"select${key}"} =~
774             m/<option value=\"
775                 ($form->{acc_trans}{$key}->[$i-1]->{accno}--[^\"]*)
776               \">
777               $form->{acc_trans}{$key}->[$i-1]->{accno}
778               --
779               ${q_description}
780               <\/option>\n/x;
781           $form->{"${key}_$k"} = $1;
782
783           if ($akey eq "AP") {
784             $form->{APselected} = $form->{acc_trans}{$key}->[$i-1]->{accno};
785
786           } elsif ($akey eq 'amount') {
787             $form->{"${key}_$k"}   = $form->{acc_trans}{$key}->[$i-1]->{accno} . "--" . $form->{acc_trans}{$key}->[$i-1]->{id};
788             $form->{"taxchart_$k"} = $form->{acc_trans}{$key}->[$i-1]->{id}    . "--" . $form->{acc_trans}{$key}->[$i-1]->{rate};
789           }
790         }
791       }
792     }
793   }
794
795   $form->{paidaccounts} = 1            if not defined $form->{paidaccounts};
796
797   if ($form->{taxincluded} && $form->{taxrate} && $totalamount) {
798     # add tax to amounts and invtotal
799     for $i (1 .. $form->{rowcount}) {
800       $taxamount            = ($totaltax + $totalwithholding) * $form->{"amount_$i"} / $totalamount;
801       $tax                  = $form->round_amount($taxamount, 2);
802       $diff                += ($taxamount - $tax);
803       $form->{"amount_$i"} += $form->{"tax_$i"};
804     }
805
806     $form->{amount_1} += $form->round_amount($diff, 2);
807   }
808
809   $taxamount        = $form->round_amount($taxamount, 2);
810   $form->{invtotal} = $totalamount + $totaltax;
811
812   $main::lxdebug->leave_sub();
813 }
814
815 sub storno {
816   my ($self, $form, $myconfig, $id) = @_;
817   $main::lxdebug->enter_sub();
818
819   my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
820
821   $::lxdebug->leave_sub;
822   return $rc;
823 }
824
825 sub _storno {
826   my ($self, $form, $myconfig, $id) = @_;
827
828   my ($query, $new_id, $storno_row, $acc_trans_rows);
829   my $dbh = SL::DB->client->dbh;
830
831   $query = qq|SELECT nextval('glid')|;
832   ($new_id) = selectrow_query($form, $dbh, $query);
833
834   $query = qq|SELECT * FROM ap WHERE id = ?|;
835   $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
836
837   $storno_row->{id}         = $new_id;
838   $storno_row->{storno_id}  = $id;
839   $storno_row->{storno}     = 't';
840   $storno_row->{invnumber}  = 'Storno-' . $storno_row->{invnumber};
841   $storno_row->{amount}    *= -1;
842   $storno_row->{netamount} *= -1;
843   $storno_row->{paid}       = $storno_row->{amount};
844
845   delete @$storno_row{qw(itime mtime)};
846
847   $query = sprintf 'INSERT INTO ap (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
848   do_query($form, $dbh, $query, (values %$storno_row));
849
850   $query = qq|UPDATE ap SET paid = amount + paid, storno = 't' WHERE id = ?|;
851   do_query($form, $dbh, $query, $id);
852
853   $form->new_lastmtime('ap') if $id == $form->{id};
854
855   # now copy acc_trans entries
856   $query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.acc_trans_id|;
857   my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
858
859   # kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/
860   while ($rowref->[-1]{link} =~ /paid/) {
861     splice(@$rowref, -2);
862   }
863
864   for my $row (@$rowref) {
865     delete @$row{qw(itime mtime link acc_trans_id)};
866     $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
867     $row->{trans_id}   = $new_id;
868     $row->{amount}    *= -1;
869     do_query($form, $dbh, $query, (values %$row));
870   }
871
872   map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id);
873
874   return 1;
875 }
876
877 1;