Revision 546480e0
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
SL/CP.pm | ||
---|---|---|
35 | 35 |
#====================================================================== |
36 | 36 |
|
37 | 37 |
package CP; |
38 |
use SL::DBUtils; |
|
38 | 39 |
|
39 | 40 |
sub new { |
40 | 41 |
$main::lxdebug->enter_sub(); |
... | ... | |
66 | 67 |
# connect to database |
67 | 68 |
my $dbh = $form->dbconnect($myconfig); |
68 | 69 |
|
69 |
my $query = qq|SELECT c.accno, c.description, c.link |
|
70 |
FROM chart c |
|
71 |
WHERE c.link LIKE '%$form->{ARAP}%' |
|
72 |
ORDER BY c.accno|; |
|
73 |
my $sth = $dbh->prepare($query); |
|
74 |
$sth->execute || $form->dberror($query); |
|
70 |
my $ARAP = $form->{ARAP} eq "AR" ? "AR" : "AP"; |
|
71 |
|
|
72 |
my $query = |
|
73 |
qq|SELECT accno, description, link | . |
|
74 |
qq|FROM chart | . |
|
75 |
qq|WHERE link LIKE ? |. |
|
76 |
qq|ORDER BY accno|; |
|
77 |
my $sth = prepare_execute_query($form, $dbh, $query, |
|
78 |
$form->{ARAP} eq "AR" ? "AR" : "AP" ); |
|
75 | 79 |
|
76 | 80 |
$form->{PR}{ $form->{ARAP} } = (); |
77 | 81 |
$form->{PR}{"$form->{ARAP}_paid"} = (); |
78 | 82 |
|
79 | 83 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { |
80 |
foreach my $item (split /:/, $ref->{link}) {
|
|
84 |
foreach my $item (split(/:/, $ref->{link})) {
|
|
81 | 85 |
if ($item eq $form->{ARAP}) { |
82 |
push @{ $form->{PR}{ $form->{ARAP} } }, $ref;
|
|
86 |
push(@{ $form->{PR}{ $form->{ARAP} } }, $ref);
|
|
83 | 87 |
} |
84 | 88 |
if ($item eq "$form->{ARAP}_paid") { |
85 |
push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
|
|
89 |
push(@{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref);
|
|
86 | 90 |
} |
87 | 91 |
} |
88 | 92 |
} |
89 | 93 |
$sth->finish; |
90 | 94 |
|
91 | 95 |
# get currencies and closedto |
92 |
$query = qq|SELECT curr, closedto |
|
93 |
FROM defaults|; |
|
94 |
$sth = $dbh->prepare($query); |
|
95 |
$sth->execute || $form->dberror($query); |
|
96 |
|
|
97 |
($form->{currencies}, $form->{closedto}) = $sth->fetchrow_array; |
|
98 |
$sth->finish; |
|
96 |
$query = qq|SELECT curr, closedto FROM defaults|; |
|
97 |
($form->{currencies}, $form->{closedto}) = |
|
98 |
selectrow_query($form, $dbh, $query); |
|
99 | 99 |
|
100 | 100 |
$dbh->disconnect; |
101 | 101 |
|
... | ... | |
109 | 109 |
|
110 | 110 |
my $dbh = $form->dbconnect($myconfig); |
111 | 111 |
|
112 |
my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; |
|
113 |
my $query = qq|SELECT count(*) |
|
114 |
FROM $form->{vc} ct, $arap a |
|
115 |
WHERE a.$form->{vc}_id = ct.id |
|
116 |
AND a.amount != a.paid|; |
|
117 |
my $sth = $dbh->prepare($query); |
|
118 |
$sth->execute || $form->dberror($query); |
|
119 |
my ($count) = $sth->fetchrow_array; |
|
120 |
$sth->finish; |
|
121 |
|
|
122 |
my $ref; |
|
112 |
my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; |
|
113 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; |
|
114 |
my $query = |
|
115 |
qq|SELECT count(*) | . |
|
116 |
qq|FROM $vc ct, $arap a | . |
|
117 |
qq|WHERE (a.${vc}_id = ct.id) AND (a.amount != a.paid)|; |
|
118 |
my ($count) = selectrow_query($form, $dbh, $query); |
|
123 | 119 |
|
124 | 120 |
# build selection list |
125 | 121 |
if ($count < $myconfig->{vclimit}) { |
126 |
$query = qq|SELECT DISTINCT ct.id, ct.name |
|
127 |
FROM $form->{vc} ct, $arap a |
|
128 |
WHERE a.$form->{vc}_id = ct.id |
|
129 |
AND a.amount != a.paid |
|
130 |
ORDER BY ct.name|; |
|
131 |
$sth = $dbh->prepare($query); |
|
132 |
$sth->execute || $form->dberror($query); |
|
133 |
|
|
134 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) { |
|
135 |
push @{ $form->{"all_$form->{vc}"} }, $ref; |
|
136 |
} |
|
137 |
|
|
138 |
$sth->finish; |
|
139 |
|
|
122 |
$query = |
|
123 |
qq|SELECT DISTINCT ct.id, ct.name | . |
|
124 |
qq|FROM $vc ct, $arap a | . |
|
125 |
qq|WHERE (a.${vc}_id = ct.id) AND (a.amount != a.paid) | . |
|
126 |
qq|ORDER BY ct.name|; |
|
127 |
$form->{"all_$form->{vc}"} = selectall_hashref_query($form, $dbh, $query); |
|
140 | 128 |
} |
141 | 129 |
|
142 | 130 |
if ($form->{ARAP} eq 'AR') { |
143 |
$query = qq|SELECT d.id, d.description |
|
144 |
FROM department d |
|
145 |
WHERE d.role = 'P' |
|
146 |
ORDER BY 2|; |
|
131 |
$query = |
|
132 |
qq|SELECT d.id, d.description | . |
|
133 |
qq|FROM department d | . |
|
134 |
qq|WHERE d.role = 'P' | . |
|
135 |
qq|ORDER BY 2|; |
|
147 | 136 |
} else { |
148 |
$query = qq|SELECT d.id, d.description |
|
149 |
FROM department d |
|
150 |
ORDER BY 2|; |
|
151 |
} |
|
152 |
$sth = $dbh->prepare($query); |
|
153 |
$sth->execute || $form->dberror($query); |
|
154 |
|
|
155 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) { |
|
156 |
push @{ $form->{all_departments} }, $ref; |
|
137 |
$query = |
|
138 |
qq|SELECT d.id, d.description | . |
|
139 |
qq|FROM department d | . |
|
140 |
qq|ORDER BY 2|; |
|
157 | 141 |
} |
158 |
$sth->finish;
|
|
142 |
$form->{all_departments} = selectall_hashref_query($form, $dbh, $query);
|
|
159 | 143 |
|
160 | 144 |
$dbh->disconnect; |
161 | 145 |
|
... | ... | |
170 | 154 |
# connect to database |
171 | 155 |
my $dbh = $form->dbconnect($myconfig); |
172 | 156 |
|
173 |
my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"} |
|
174 |
AND a.curr = '$form->{currency}' |
|
175 |
AND NOT a.amount = paid|; |
|
157 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; |
|
176 | 158 |
|
177 |
my ($buysell); |
|
178 |
if ($form->{vc} eq 'customer') { |
|
179 |
$buysell = "buy"; |
|
180 |
} else { |
|
181 |
$buysell = "sell"; |
|
182 |
} |
|
159 |
my $buysell = $form->{vc} eq 'customer' ? "buy" : "sell"; |
|
160 |
my $arap = $form->{arap} eq "ar" ? "ar" : "ap"; |
|
183 | 161 |
|
184 | 162 |
my $query = |
185 |
qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, a.curr |
|
186 |
FROM $form->{arap} a |
|
187 |
$where |
|
188 |
ORDER BY a.id|; |
|
189 |
my $sth = $dbh->prepare($query); |
|
190 |
$sth->execute || $form->dberror($query); |
|
191 |
|
|
163 |
qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, a.curr | . |
|
164 |
qq|FROM $arap a | . |
|
165 |
qq|WHERE (a.${vc}_id = ?) AND (a.curr = ?) AND NOT (a.amount = paid)|; |
|
166 |
qq|ORDER BY a.id|; |
|
167 |
my $sth = prepare_execute_query($form, $dbh, $query, |
|
168 |
conv_i($form->{"${vc}_id"}), |
|
169 |
$form->{currency}); |
|
170 |
|
|
171 |
$form->{PR} = []; |
|
192 | 172 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) { |
193 | 173 |
|
194 | 174 |
# if this is a foreign currency transaction get exchangerate |
... | ... | |
230 | 210 |
$form->{exchangerate} = 1; |
231 | 211 |
} |
232 | 212 |
|
233 |
my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id |
|
234 |
FROM defaults|; |
|
235 |
my $sth = $dbh->prepare($query); |
|
236 |
$sth->execute || $form->dberror($query); |
|
237 |
|
|
238 |
my ($fxgain_accno_id, $fxloss_accno_id) = $sth->fetchrow_array; |
|
239 |
$sth->finish; |
|
240 |
|
|
241 |
my ($buysell); |
|
213 |
my $query = |
|
214 |
qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults|; |
|
215 |
my ($fxgain_accno_id, $fxloss_accno_id) = |
|
216 |
selectrow_query($form, $dbh, $query); |
|
242 | 217 |
|
243 |
if ($form->{vc} eq 'customer') { |
|
244 |
$buysell = "buy"; |
|
245 |
} else { |
|
246 |
$buysell = "sell"; |
|
247 |
} |
|
218 |
my $buysell = $form->{vc} eq "customer" ? "buy" : "sell"; |
|
219 |
my $arap = $form->{arap} eq "ar" ? "ar" : "ap"; |
|
248 | 220 |
|
249 | 221 |
my $ml; |
250 | 222 |
my $where; |
251 | 223 |
|
252 | 224 |
if ($form->{ARAP} eq 'AR') { |
253 | 225 |
$ml = 1; |
254 |
$where = qq| |
|
255 |
(c.link = 'AR' |
|
256 |
OR c.link LIKE 'AR:%') |
|
257 |
|; |
|
226 |
$where = qq| ((c.link = 'AR') OR (c.link LIKE 'AR:%')) |; |
|
258 | 227 |
} else { |
259 | 228 |
$ml = -1; |
260 |
$where = qq| |
|
261 |
(c.link = 'AP' |
|
262 |
OR c.link LIKE '%:AP' |
|
263 |
OR c.link LIKE '%:AP:%') |
|
264 |
|; |
|
229 |
$where = |
|
230 |
qq| ((c.link = 'AP') OR | . |
|
231 |
qq| (c.link LIKE '%:AP') OR | . |
|
232 |
qq| (c.link LIKE '%:AP:%')) |; |
|
265 | 233 |
} |
266 | 234 |
|
267 | 235 |
$paymentamount = $form->{amount}; |
268 | 236 |
|
269 | 237 |
# $paymentamount = $form->{amount}; |
270 | 238 |
my $null; |
271 |
($null, $form->{department_id}) = split /--/, $form->{department};
|
|
239 |
($null, $form->{department_id}) = split(/--/, $form->{department});
|
|
272 | 240 |
$form->{department_id} *= 1; |
273 | 241 |
|
274 | 242 |
# query to retrieve paid amount |
275 |
$query = qq|SELECT a.paid FROM ar a |
|
276 |
WHERE a.id = ? |
|
277 |
FOR UPDATE|; |
|
278 |
my $pth = $dbh->prepare($query) || $form->dberror($query); |
|
243 |
$query = |
|
244 |
qq|SELECT a.paid FROM ar a | . |
|
245 |
qq|WHERE a.id = ? | . |
|
246 |
qq|FOR UPDATE|; |
|
247 |
my $pth = prepare_query($form, $dbh, $query); |
|
279 | 248 |
|
280 | 249 |
# go through line by line |
281 | 250 |
for my $i (1 .. $form->{rowcount}) { |
... | ... | |
288 | 257 |
(($paymentamount * 1000) - ($form->{"paid_$i"} * 1000)) / 1000; |
289 | 258 |
|
290 | 259 |
# get exchangerate for original |
291 |
$query = qq|SELECT $buysell |
|
292 |
FROM exchangerate e |
|
293 |
JOIN $form->{arap} a ON (a.transdate = e.transdate) |
|
294 |
WHERE e.curr = '$form->{currency}' |
|
295 |
AND a.id = $form->{"id_$i"}|; |
|
296 |
$sth = $dbh->prepare($query); |
|
297 |
$sth->execute || $form->dberror($query); |
|
298 |
|
|
299 |
my ($exchangerate) = $sth->fetchrow_array; |
|
300 |
$sth->finish; |
|
260 |
$query = |
|
261 |
qq|SELECT $buysell | . |
|
262 |
qq|FROM exchangerate e | . |
|
263 |
qq|JOIN ${arap} a ON (a.transdate = e.transdate) | . |
|
264 |
qq|WHERE (e.curr = ?) AND (a.id = ?)|; |
|
265 |
my ($exchangerate) = |
|
266 |
selectrow_query($form, $dbh, $query, |
|
267 |
$form->{currency}, $form->{"id_$i"}); |
|
301 | 268 |
|
302 | 269 |
$exchangerate = 1 unless $exchangerate; |
303 | 270 |
|
304 |
$query = qq|SELECT c.id |
|
305 |
FROM chart c |
|
306 |
JOIN acc_trans a ON (a.chart_id = c.id) |
|
307 |
WHERE $where |
|
308 |
AND a.trans_id = $form->{"id_$i"}|; |
|
309 |
$sth = $dbh->prepare($query); |
|
310 |
$sth->execute || $form->dberror($query); |
|
311 |
|
|
312 |
my ($id) = $sth->fetchrow_array; |
|
313 |
$sth->finish; |
|
271 |
$query = |
|
272 |
qq|SELECT c.id | . |
|
273 |
qq|FROM chart c | . |
|
274 |
qq|JOIN acc_trans a ON (a.chart_id = c.id) | . |
|
275 |
qq|WHERE $where | . |
|
276 |
qq|AND (a.trans_id = ?)|; |
|
277 |
my ($id) = selectrow_query($form, $dbh, $query, $form->{"id_$i"}); |
|
314 | 278 |
|
315 | 279 |
$amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2); |
316 | 280 |
|
317 | 281 |
# add AR/AP |
318 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
|
|
319 |
amount)
|
|
320 |
VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
|
|
321 |
$amount * $ml)|;
|
|
322 |
$dbh->do($query) || $form->dberror($query);
|
|
282 |
$query = |
|
283 |
qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount) | .
|
|
284 |
qq|VALUES (?, ?, ?, ?)|;
|
|
285 |
do_query($form, $dbh, $query, $form->{"id_$i"}, $id,
|
|
286 |
conv_date($form->{datepaid}), $amount * $ml);
|
|
323 | 287 |
|
324 | 288 |
# add payment |
325 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, |
|
326 |
amount, source, memo) |
|
327 |
VALUES ($form->{"id_$i"}, |
|
328 |
(SELECT c.id FROM chart c |
|
329 |
WHERE c.accno = '$paymentaccno'), |
|
330 |
'$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
|
331 |
'$form->{source}', '$form->{memo}')|; |
|
332 |
$dbh->do($query) || $form->dberror($query); |
|
289 |
$query = |
|
290 |
qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, | . |
|
291 |
qq| source, memo) | . |
|
292 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; |
|
293 |
my @values = (conv_i($form->{"id_$i"}), $paymentaccno, |
|
294 |
conv_date($form->{datepaid}), |
|
295 |
$form->{"paid_$i"} * $ml * -1, $form->{source}, |
|
296 |
$form->{memo}); |
|
297 |
do_query($form, $dbh, $query, @values); |
|
333 | 298 |
|
334 | 299 |
# add exchangerate difference if currency ne defaultcurrency |
335 | 300 |
$amount = |
... | ... | |
338 | 303 |
if ($amount != 0) { |
339 | 304 |
|
340 | 305 |
# exchangerate difference |
341 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, |
|
342 |
amount, cleared, fx_transaction) |
|
343 |
VALUES ($form->{"id_$i"}, |
|
344 |
(SELECT c.id FROM chart c |
|
345 |
WHERE c.accno = '$paymentaccno'), |
|
346 |
'$form->{datepaid}', $amount * $ml * -1, '0', '1')|; |
|
347 |
$dbh->do($query) || $form->dberror($query); |
|
306 |
$query = |
|
307 |
qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, | . |
|
308 |
qq| cleared, fx_transaction) | . |
|
309 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; |
|
310 |
@values = (conv_i($form->{"id_$i"}), $paymentaccno, |
|
311 |
conv_date($form->{datepaid}), ($amount * $ml * -1), '0', |
|
312 |
'1'); |
|
313 |
do_query($form, $dbh, $query, @values); |
|
348 | 314 |
|
349 | 315 |
# gain/loss |
350 | 316 |
|
351 | 317 |
$amount = |
352 |
$form->round_amount( |
|
353 |
$form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}), |
|
354 |
2); |
|
318 |
$form->round_amount($form->{"paid_$i"} * |
|
319 |
($exchangerate - $form->{exchangerate}), 2); |
|
355 | 320 |
if ($amount != 0) { |
356 | 321 |
my $accno_id = ($amount < 0) ? $fxgain_accno_id : $fxloss_accno_id; |
357 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, |
|
358 |
amount, cleared, fx_transaction) |
|
359 |
VALUES ($form->{"id_$i"}, $accno_id, |
|
360 |
'$form->{datepaid}', $amount * $ml * -1, '0', '1')|; |
|
361 |
$dbh->do($query) || $form->dberror($query); |
|
322 |
$query = |
|
323 |
qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, | . |
|
324 |
qq| amount, cleared, fx_transaction) | . |
|
325 |
qq|VALUES (?, ?, ?, ?, ?, ?)|; |
|
326 |
@values = (conv_i($form->{"id_$i"}), $accno_id, |
|
327 |
conv_date($form->{datepaid}), $amount * $ml * -1, '0', |
|
328 |
'1'); |
|
329 |
do_query($form, $dbh, $query, @values); |
|
362 | 330 |
} |
363 | 331 |
} |
364 | 332 |
|
... | ... | |
370 | 338 |
$pth->finish; |
371 | 339 |
|
372 | 340 |
$amount += $form->{"paid_$i"}; |
373 |
|
|
341 |
|
|
374 | 342 |
# BUG 324 |
375 | 343 |
if ($form->{arap} eq 'ap') { |
376 | 344 |
$paid = "paid = paid + $amount"; |
... | ... | |
379 | 347 |
} |
380 | 348 |
|
381 | 349 |
# update AR/AP transaction |
382 |
$query = qq|UPDATE $form->{arap} set |
|
383 |
$paid, |
|
384 |
datepaid = '$form->{datepaid}' |
|
385 |
WHERE id = $form->{"id_$i"}|; |
|
386 |
$dbh->do($query) || $form->dberror($query); |
|
350 |
$query = qq|UPDATE $arap SET $paid, datepaid = ? WHERE id = ?|; |
|
351 |
@values = (conv_date($form->{datepaid}), conv_i($form->{"id_$i"})); |
|
352 |
do_query($form, $dbh, $query, @values); |
|
387 | 353 |
} |
388 | 354 |
} |
389 | 355 |
|
Auch abrufbar als: Unified diff
CP.pm auf die Verwendung von parametrisierten Queries zur Vermeidung von SQL injections umgestellt.