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
62 WHERE link LIKE '%$form->{arap}_paid%'
64 my $sth = $dbh->prepare($query);
65 $sth->execute || $form->dberror($query);
67 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
68 push @{ $form->{PR} }, $ref;
72 # get currencies and closedto
73 $query = qq|SELECT curr, closedto
75 $sth = $dbh->prepare($query);
76 $sth->execute || $form->dberror($query);
78 ($form->{currencies}, $form->{closedto}) = $sth->fetchrow_array;
87 my ($self, $myconfig, $form) = @_;
89 my $dbh = $form->dbconnect($myconfig);
91 my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
93 my $query = qq|SELECT count(*)
94 FROM $form->{vc} ct, $arap a
95 WHERE a.$form->{vc}_id = ct.id
96 AND a.amount != a.paid|;
97 my $sth = $dbh->prepare($query);
98 $sth->execute || $form->dberror($query);
99 my ($count) = $sth->fetchrow_array;
102 # build selection list
103 if ($count < $myconfig->{vclimit}) {
104 $query = qq|SELECT DISTINCT ct.id, ct.name
105 FROM $form->{vc} ct, $arap a
106 WHERE a.$form->{vc}_id = ct.id
107 AND a.amount != a.paid
109 $sth = $dbh->prepare($query);
110 $sth->execute || $form->dberror($query);
112 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
113 push @{ $form->{"all_$form->{vc}"} }, $ref;
125 sub get_openinvoices {
126 my ($self, $myconfig, $form) = @_;
128 return unless $form->{"$form->{vc}_id"};
130 # connect to database
131 my $dbh = $form->dbconnect($myconfig);
133 my $where = qq|WHERE $form->{vc}_id = $form->{"$form->{vc}_id"}
134 AND curr = '$form->{currency}'
135 AND NOT amount = paid|;
137 if ($form->{transdatefrom}) {
138 $where .= " AND transdate >= '$form->{transdatefrom}'";
140 if ($form->{transdateto}) {
141 $where .= " AND transdate <= '$form->{transdateto}'";
144 my ($arap, $buysell);
145 if ($form->{vc} eq 'customer') {
153 my $query = qq|SELECT id, invnumber, transdate, amount, paid, curr
157 my $sth = $dbh->prepare($query);
158 $sth->execute || $form->dberror($query);
160 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
161 # if this is a foreign currency transaction get exchangerate
162 $ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});
163 push @{ $form->{PR} }, $ref;
173 sub process_payment {
174 my ($self, $myconfig, $form) = @_;
176 # connect to database, turn AutoCommit off
177 my $dbh = $form->dbconnect_noauto($myconfig);
179 my ($paymentaccno) = split /--/, $form->{account};
181 # if currency ne defaultcurrency update exchangerate
182 if ($form->{currency} ne $form->{defaultcurrency}) {
183 $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
185 if ($form->{vc} eq 'customer') {
186 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
188 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
191 $form->{exchangerate} = 1;
194 my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
196 my $sth = $dbh->prepare($query);
197 $sth->execute || $form->dberror($query);
199 my ($fxgain_accno_id, $fxloss_accno_id) = $sth->fetchrow_array;
202 my ($ARAP, $arap, $buysell);
204 if ($form->{vc} eq 'customer') {
214 # go through line by line
215 for my $i (1 .. $form->{rowcount}) {
217 if ($form->{"paid_$i"}) {
219 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
221 # get exchangerate for original
222 $query = qq|SELECT $buysell FROM exchangerate e, $arap a
223 WHERE e.curr = '$form->{currency}'
224 AND a.transdate = e.transdate
225 AND a.id = $form->{"id_$i"}|;
226 $sth = $dbh->prepare($query);
227 $sth->execute || $form->dberror($query);
229 my ($exchangerate) = $sth->fetchrow_array;
232 $exchangerate = 1 unless $exchangerate;
234 $query = qq|SELECT c.id FROM chart c, acc_trans a
235 WHERE a.chart_id = c.id
237 AND a.trans_id = $form->{"id_$i"}|;
238 $sth = $dbh->prepare($query);
239 $sth->execute || $form->dberror($query);
241 my ($id) = $sth->fetchrow_array;
244 my $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate * -1, 2);
245 $ml = ($ARAP eq 'AR') ? -1 : 1;
247 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount)
248 VALUES ($form->{"id_$i"}, $id,
249 '$form->{datepaid}', $amount * $ml)|;
250 $dbh->do($query) || $form->dberror($query);
253 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount,
255 VALUES ($form->{"id_$i"},
256 (SELECT id FROM chart
257 WHERE accno = '$paymentaccno'),
258 '$form->{datepaid}', $form->{"paid_$i"} * $ml,
260 $dbh->do($query) || $form->dberror($query);
262 # add exchangerate difference if currency ne defaultcurrency
263 $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);
266 # exchangerate difference
267 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
268 amount, cleared, fx_transaction)
269 VALUES ($form->{"id_$i"},
270 (SELECT id FROM chart
271 WHERE accno = '$paymentaccno'),
272 '$form->{datepaid}', $amount * $ml, '0', '1')|;
273 $dbh->do($query) || $form->dberror($query);
277 $amount = $form->round_amount($form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}) * $ml, 2);
279 my $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;
280 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
281 amount, cleared, fx_transaction)
282 VALUES ($form->{"id_$i"}, $accno_id,
283 '$form->{datepaid}', $amount, '0', '1')|;
284 $dbh->do($query) || $form->dberror($query);
288 $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
290 # update AR/AP transaction
291 $query = qq|UPDATE $arap set
292 paid = paid + $form->{"paid_$i"},
293 datepaid = '$form->{datepaid}'
294 WHERE id = $form->{"id_$i"}|;
295 $dbh->do($query) || $form->dberror($query);
299 my $rc = $dbh->commit;