1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
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.
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 #======================================================================
25 # Check and receipt printing payment module backend routines
26 # Number to text conversion routines are in
27 # locale/{countrycode}/Num2text
29 #======================================================================
35 my ($type, $countrycode) = @_;
40 if (-f "locale/$countrycode/Num2text") {
41 require "locale/$countrycode/Num2text";
55 my ($self, $myconfig, $form) = @_;
58 my $dbh = $form->dbconnect($myconfig);
60 my $query = qq|SELECT accno, description, link
62 WHERE link LIKE '%$form->{ARAP}%'
64 my $sth = $dbh->prepare($query);
65 $sth->execute || $form->dberror($query);
67 $form->{PR}{$form->{ARAP}} = ();
68 $form->{PR}{"$form->{ARAP}_paid"} = ();
70 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
71 foreach my $item (split /:/, $ref->{link}) {
72 if ($item eq $form->{ARAP}) {
73 push @{ $form->{PR}{$form->{ARAP}} }, $ref;
75 if ($item eq "$form->{ARAP}_paid") {
76 push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
82 # get currencies and closedto
83 $query = qq|SELECT curr, closedto, current_date
85 ($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);
93 my ($self, $myconfig, $form) = @_;
95 my $dbh = $form->dbconnect($myconfig);
97 my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
98 my $query = qq|SELECT count(*)
99 FROM $form->{vc} ct, $arap a
100 WHERE a.$form->{vc}_id = ct.id
101 AND a.amount != a.paid|;
102 my ($count) = $dbh->selectrow_array($query);
107 # build selection list
108 if ($count < $myconfig->{vclimit}) {
109 $query = qq|SELECT DISTINCT ct.id, ct.name
110 FROM $form->{vc} ct, $arap a
111 WHERE a.$form->{vc}_id = ct.id
112 AND a.amount != a.paid
114 $sth = $dbh->prepare($query);
115 $sth->execute || $form->dberror($query);
117 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
118 push @{ $form->{"all_$form->{vc}"} }, $ref;
125 if ($form->{ARAP} eq 'AR') {
126 $query = qq|SELECT id, description
131 $query = qq|SELECT id, description
135 $sth = $dbh->prepare($query);
136 $sth->execute || $form->dberror($query);
138 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
139 push @{ $form->{all_departments} }, $ref;
147 $sth = $dbh->prepare($query);
148 $sth->execute || $self->dberror($query);
150 $form->{all_languages} = ();
151 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
152 push @{ $form->{all_languages} }, $ref;
156 # get currency for first name
157 if ($form->{"all_$form->{vc}"}) {
158 $query = qq|SELECT curr FROM $form->{vc}
159 WHERE id = $form->{"all_$form->{vc}"}->[0]->{id}|;
160 ($form->{currency}) = $dbh->selectrow_array($query);
168 sub get_openinvoices {
169 my ($self, $myconfig, $form) = @_;
174 # connect to database
175 my $dbh = $form->dbconnect($myconfig);
177 my $where = qq|WHERE $form->{vc}_id = $form->{"$form->{vc}_id"}
178 AND curr = '$form->{currency}'
182 if ($form->{vc} eq 'customer') {
188 ($null, $department_id) = split /--/, $form->{department};
189 if ($department_id) {
191 AND department_id = $department_id|;
194 my $query = qq|SELECT id, invnumber, transdate, amount, paid, curr
197 ORDER BY transdate, invnumber|;
198 my $sth = $dbh->prepare($query);
199 $sth->execute || $form->dberror($query);
201 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
202 # if this is a foreign currency transaction get exchangerate
203 $ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});
204 push @{ $form->{PR} }, $ref;
214 sub process_payment {
215 my ($self, $myconfig, $form) = @_;
217 # connect to database, turn AutoCommit off
218 my $dbh = $form->dbconnect_noauto($myconfig);
222 my ($paymentaccno) = split /--/, $form->{account};
224 # if currency ne defaultcurrency update exchangerate
225 if ($form->{currency} ne $form->{defaultcurrency}) {
226 $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
228 if ($form->{vc} eq 'customer') {
229 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
231 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
234 $form->{exchangerate} = 1;
237 my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
239 my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
243 if ($form->{vc} eq 'customer') {
252 if ($form->{ARAP} eq 'AR') {
256 OR c.link LIKE 'AR:%')
262 OR c.link LIKE '%:AP'
263 OR c.link LIKE '%:AP:%')
267 my $paymentamount = $form->parse_amount($myconfig, $form->{amount});
270 ($null, $form->{department_id}) = split /--/, $form->{department};
271 $form->{department_id} *= 1;
274 # query to retrieve paid amount
275 $query = qq|SELECT paid FROM $form->{arap}
278 my $pth = $dbh->prepare($query) || $form->dberror($query);
282 # go through line by line
283 for my $i (1 .. $form->{rowcount}) {
285 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
286 $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});
288 if ($form->{"checked_$i"} && $form->{"paid_$i"}) {
290 $paymentamount -= $form->{"paid_$i"};
292 # get exchangerate for original
293 $query = qq|SELECT $buysell
295 JOIN $form->{arap} a ON (a.transdate = e.transdate)
296 WHERE e.curr = '$form->{currency}'
297 AND a.id = $form->{"id_$i"}|;
298 my ($exchangerate) = $dbh->selectrow_array($query);
300 $exchangerate = 1 unless $exchangerate;
302 $query = qq|SELECT c.id
304 JOIN acc_trans a ON (a.chart_id = c.id)
306 AND a.trans_id = $form->{"id_$i"}|;
307 my ($id) = $dbh->selectrow_array($query);
309 $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
312 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
314 VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
316 $dbh->do($query) || $form->dberror($query);
319 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
320 amount, source, memo)
321 VALUES ($form->{"id_$i"},
322 (SELECT id FROM chart
323 WHERE accno = '$paymentaccno'),
324 '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
325 .$dbh->quote($form->{source}).qq|, |
326 .$dbh->quote($form->{memo}).qq|)|;
327 $dbh->do($query) || $form->dberror($query);
329 # add exchangerate difference if currency ne defaultcurrency
330 $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);
333 # exchangerate difference
334 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
335 amount, cleared, fx_transaction)
336 VALUES ($form->{"id_$i"},
337 (SELECT id FROM chart
338 WHERE accno = '$paymentaccno'),
339 '$form->{datepaid}', $amount * $ml * -1, '0', '1')|;
340 $dbh->do($query) || $form->dberror($query);
343 $amount = $form->round_amount($form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}) * $ml * -1, 2);
345 my $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;
346 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
347 amount, cleared, fx_transaction)
348 VALUES ($form->{"id_$i"}, $accno_id,
349 '$form->{datepaid}', $amount, '0', '1')|;
350 $dbh->do($query) || $form->dberror($query);
354 $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
356 $pth->execute($form->{"id_$i"}) || $form->dberror;
357 ($amount) = $pth->fetchrow_array;
360 $amount += $form->{"paid_$i"};
362 # update AR/AP transaction
363 $query = qq|UPDATE $form->{arap} set
365 datepaid = '$form->{datepaid}'
366 WHERE id = $form->{"id_$i"}|;
367 $dbh->do($query) || $form->dberror($query);
369 %audittrail = ( tablename => $form->{arap},
370 reference => $form->{source},
371 formname => $form->{formname},
373 id => $form->{"id_$i"} );
375 $form->audittrail($dbh, "", \%audittrail);
381 # record a AR/AP with a payment
382 if ($form->round_amount($paymentamount, 2) != 0) {
383 $form->{invnumber} = "";
384 OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
387 my $rc = $dbh->commit;