1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
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.
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 #======================================================================
31 # Check and receipt printing payment module backend routines
32 # Number to text conversion routines are in
33 # locale/{countrycode}/Num2text
35 #======================================================================
41 $main::lxdebug->enter_sub();
43 my ($type, $countrycode) = @_;
48 if (-f "locale/$countrycode/Num2text") {
49 require "locale/$countrycode/Num2text";
57 $main::lxdebug->leave_sub();
64 $main::lxdebug->enter_sub();
66 my ($self, $myconfig, $form) = @_;
69 my $dbh = $form->dbconnect($myconfig);
71 my $query = qq|SELECT c.accno, c.description, c.link
73 WHERE c.link LIKE '%$form->{ARAP}%'
75 my $sth = $dbh->prepare($query);
76 $sth->execute || $form->dberror($query);
78 $form->{PR}{$form->{ARAP}} = ();
79 $form->{PR}{"$form->{ARAP}_paid"} = ();
81 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
82 foreach my $item (split /:/, $ref->{link}) {
83 if ($item eq $form->{ARAP}) {
84 push @{ $form->{PR}{$form->{ARAP}} }, $ref;
86 if ($item eq "$form->{ARAP}_paid") {
87 push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
93 # get currencies and closedto
94 $query = qq|SELECT curr, closedto
96 $sth = $dbh->prepare($query);
97 $sth->execute || $form->dberror($query);
99 ($form->{currencies}, $form->{closedto}) = $sth->fetchrow_array;
104 $main::lxdebug->leave_sub();
109 $main::lxdebug->enter_sub();
111 my ($self, $myconfig, $form) = @_;
113 my $dbh = $form->dbconnect($myconfig);
115 my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
116 my $query = qq|SELECT count(*)
117 FROM $form->{vc} ct, $arap a
118 WHERE a.$form->{vc}_id = ct.id
119 AND a.amount != a.paid|;
120 my $sth = $dbh->prepare($query);
121 $sth->execute || $form->dberror($query);
122 my ($count) = $sth->fetchrow_array;
127 # build selection list
128 if ($count < $myconfig->{vclimit}) {
129 $query = qq|SELECT DISTINCT ct.id, ct.name
130 FROM $form->{vc} ct, $arap a
131 WHERE a.$form->{vc}_id = ct.id
132 AND a.amount != a.paid
134 $sth = $dbh->prepare($query);
135 $sth->execute || $form->dberror($query);
137 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
138 push @{ $form->{"all_$form->{vc}"} }, $ref;
145 if ($form->{ARAP} eq 'AR') {
146 $query = qq|SELECT d.id, d.description
151 $query = qq|SELECT d.id, d.description
155 $sth = $dbh->prepare($query);
156 $sth->execute || $form->dberror($query);
158 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
159 push @{ $form->{all_departments} }, $ref;
165 $main::lxdebug->leave_sub();
169 sub get_openinvoices {
170 $main::lxdebug->enter_sub();
172 my ($self, $myconfig, $form) = @_;
174 # connect to database
175 my $dbh = $form->dbconnect($myconfig);
177 my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"}
178 AND a.curr = '$form->{currency}'
179 AND NOT a.amount = paid|;
182 if ($form->{vc} eq 'customer') {
188 my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, a.curr
192 my $sth = $dbh->prepare($query);
193 $sth->execute || $form->dberror($query);
195 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
196 # if this is a foreign currency transaction get exchangerate
197 $ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});
198 push @{ $form->{PR} }, $ref;
204 $main::lxdebug->leave_sub();
209 sub process_payment {
210 $main::lxdebug->enter_sub();
212 my ($self, $myconfig, $form) = @_;
214 # connect to database, turn AutoCommit off
215 my $dbh = $form->dbconnect_noauto($myconfig);
217 my ($paymentaccno) = split /--/, $form->{account};
219 # if currency ne defaultcurrency update exchangerate
220 if ($form->{currency} ne $form->{defaultcurrency}) {
221 $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
223 if ($form->{vc} eq 'customer') {
224 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
226 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
229 $form->{exchangerate} = 1;
232 my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
234 my $sth = $dbh->prepare($query);
235 $sth->execute || $form->dberror($query);
237 my ($fxgain_accno_id, $fxloss_accno_id) = $sth->fetchrow_array;
242 if ($form->{vc} eq 'customer') {
251 if ($form->{ARAP} eq 'AR') {
255 OR c.link LIKE 'AR:%')
261 OR c.link LIKE '%:AP'
262 OR c.link LIKE '%:AP:%')
266 $paymentamount = $form->{amount};
268 # $paymentamount = $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 a.paid FROM ar a
278 my $pth = $dbh->prepare($query) || $form->dberror($query);
280 # go through line by line
281 for my $i (1 .. $form->{rowcount}) {
283 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
284 $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});
286 if ($form->{"checked_$i"} && $form->{"paid_$i"}) {
287 $paymentamount = (($paymentamount * 1000) - ($form->{"paid_$i"} * 1000)) / 1000;
289 # get exchangerate for original
290 $query = qq|SELECT $buysell
292 JOIN $form->{arap} a ON (a.transdate = e.transdate)
293 WHERE e.curr = '$form->{currency}'
294 AND a.id = $form->{"id_$i"}|;
295 $sth = $dbh->prepare($query);
296 $sth->execute || $form->dberror($query);
298 my ($exchangerate) = $sth->fetchrow_array;
301 $exchangerate = 1 unless $exchangerate;
303 $query = qq|SELECT c.id
305 JOIN acc_trans a ON (a.chart_id = c.id)
307 AND a.trans_id = $form->{"id_$i"}|;
308 $sth = $dbh->prepare($query);
309 $sth->execute || $form->dberror($query);
311 my ($id) = $sth->fetchrow_array;
315 $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
318 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
320 VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
322 $dbh->do($query) || $form->dberror($query);
324 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
325 amount, source, memo)
326 VALUES ($form->{"id_$i"},
327 (SELECT c.id FROM chart c
328 WHERE c.accno = '$paymentaccno'),
329 '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1,
330 '$form->{source}', '$form->{memo}')|;
331 $dbh->do($query) || $form->dberror($query);
332 # add exchangerate difference if currency ne defaultcurrency
333 $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);
335 # exchangerate difference
336 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
337 amount, cleared, fx_transaction)
338 VALUES ($form->{"id_$i"},
339 (SELECT c.id FROM chart c
340 WHERE c.accno = '$paymentaccno'),
341 '$form->{datepaid}', $amount * $ml * -1, '0', '1')|;
342 $dbh->do($query) || $form->dberror($query);
346 $amount = $form->round_amount($form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}), 2);
348 my $accno_id = ($amount < 0) ? $fxgain_accno_id : $fxloss_accno_id;
349 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
350 amount, cleared, fx_transaction)
351 VALUES ($form->{"id_$i"}, $accno_id,
352 '$form->{datepaid}', $amount * $ml * -1, '0', '1')|;
353 $dbh->do($query) || $form->dberror($query);
357 $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
359 $pth->execute($form->{"id_$i"}) || $form->dberror;
360 ($amount) = $pth->fetchrow_array;
363 $amount += $form->{"paid_$i"};
365 # update AR/AP transaction
366 $query = qq|UPDATE $form->{arap} set
368 datepaid = '$form->{datepaid}'
369 WHERE id = $form->{"id_$i"}|;
370 $dbh->do($query) || $form->dberror($query);
375 # record a AR/AP with a payment
376 if ($form->round_amount($paymentamount, 2) > 0) {
377 $form->{invnumber} = "";
378 OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
381 if ($form->round_amount($paymentamount, 2) < 0) {
385 if ($form->round_amount($paymentamount, 2) == 0) {
391 $main::lxdebug->leave_sub();