Revision c9a7e79e
Von Philip Reetz vor mehr als 16 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 1274 | 1274 |
# connect to database |
| 1275 | 1275 |
my $dbh = $form->dbconnect($myconfig); |
| 1276 | 1276 |
|
| 1277 |
my ($invoice, $arap, $buysell, $ct, $ct_id); |
|
| 1277 |
my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
|
|
| 1278 | 1278 |
|
| 1279 | 1279 |
if ($form->{ct} eq "customer") {
|
| 1280 | 1280 |
$invoice = "is"; |
| 1281 | 1281 |
$arap = "ar"; |
| 1282 | 1282 |
$buysell = "buy"; |
| 1283 | 1283 |
$ct = "customer"; |
| 1284 |
$ml = -1; |
|
| 1284 | 1285 |
} else {
|
| 1285 | 1286 |
$invoice = "ir"; |
| 1286 | 1287 |
$arap = "ap"; |
| 1287 | 1288 |
$buysell = "sell"; |
| 1288 | 1289 |
$ct = "vendor"; |
| 1290 |
$ml = 1; |
|
| 1289 | 1291 |
} |
| 1290 | 1292 |
$ct_id = "${ct}_id";
|
| 1291 | 1293 |
|
| ... | ... | |
| 1315 | 1317 |
street, zipcode, city, country, contact, email, |
| 1316 | 1318 |
phone as customerphone, fax as customerfax, ${ct}number,
|
| 1317 | 1319 |
"invnumber", "transdate", |
| 1318 |
(amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90", |
|
| 1319 |
"duedate", invoice, ${arap}.id,
|
|
| 1320 |
(SELECT $buysell |
|
| 1321 |
FROM exchangerate |
|
| 1322 |
WHERE (${arap}.curr = exchangerate.curr)
|
|
| 1323 |
AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
|
|
| 1324 |
FROM ${arap}, ${ct}
|
|
| 1325 |
WHERE (paid != amount) |
|
| 1326 |
AND (${arap}.storno IS FALSE)
|
|
| 1327 |
AND (${arap}.${ct}_id = ${ct}.id)
|
|
| 1328 |
AND (${ct}.id = ?)
|
|
| 1329 |
AND (transdate <= (date $todate - interval '0 days')) |
|
| 1330 |
AND (transdate >= (date $todate - interval '30 days')) |
|
| 1331 |
|
|
| 1332 |
UNION |
|
| 1333 |
|
|
| 1334 |
-- between 31-60 days |
|
| 1335 |
|
|
| 1336 |
SELECT ${ct}.id AS ctid, ${ct}.name,
|
|
| 1337 |
street, zipcode, city, country, contact, email, |
|
| 1338 |
phone as customerphone, fax as customerfax, ${ct}number,
|
|
| 1339 |
"invnumber", "transdate", |
|
| 1340 |
0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90", |
|
| 1341 |
"duedate", invoice, ${arap}.id,
|
|
| 1342 |
(SELECT $buysell |
|
| 1343 |
FROM exchangerate |
|
| 1344 |
WHERE (${arap}.curr = exchangerate.curr)
|
|
| 1345 |
AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
|
|
| 1346 |
FROM ${arap}, ${ct}
|
|
| 1347 |
WHERE (paid != amount) |
|
| 1348 |
AND (${arap}.storno IS FALSE)
|
|
| 1349 |
AND (${arap}.${ct}_id = ${ct}.id)
|
|
| 1350 |
AND (${ct}.id = ?)
|
|
| 1351 |
AND (transdate < (date $todate - interval '30 days')) |
|
| 1352 |
AND (transdate >= (date $todate - interval '60 days')) |
|
| 1353 |
|
|
| 1354 |
UNION |
|
| 1355 |
|
|
| 1356 |
-- between 61-90 days |
|
| 1357 |
|
|
| 1358 |
SELECT ${ct}.id AS ctid, ${ct}.name,
|
|
| 1359 |
street, zipcode, city, country, contact, email, |
|
| 1360 |
phone as customerphone, fax as customerfax, ${ct}number,
|
|
| 1361 |
"invnumber", "transdate", |
|
| 1362 |
0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90", |
|
| 1320 |
(amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
|
|
| 1363 | 1321 |
"duedate", invoice, ${arap}.id,
|
| 1364 | 1322 |
(SELECT $buysell |
| 1365 | 1323 |
FROM exchangerate |
| 1366 | 1324 |
WHERE (${arap}.curr = exchangerate.curr)
|
| 1367 | 1325 |
AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
|
| 1368 | 1326 |
FROM ${arap}, ${ct}
|
| 1369 |
WHERE (paid != amount) |
|
| 1370 |
AND (${arap}.storno IS FALSE)
|
|
| 1371 |
AND (${arap}.${ct}_id = ${ct}.id)
|
|
| 1372 |
AND (${ct}.id = ?)
|
|
| 1373 |
AND (transdate < (date $todate - interval '60 days')) |
|
| 1374 |
AND (transdate >= (date $todate - interval '90 days')) |
|
| 1375 |
|
|
| 1376 |
UNION |
|
| 1377 |
|
|
| 1378 |
-- over 90 days |
|
| 1379 |
|
|
| 1380 |
SELECT ${ct}.id AS ctid, ${ct}.name,
|
|
| 1381 |
street, zipcode, city, country, contact, email, |
|
| 1382 |
phone as customerphone, fax as customerfax, ${ct}number,
|
|
| 1383 |
"invnumber", "transdate", |
|
| 1384 |
0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90", |
|
| 1385 |
"duedate", invoice, ${arap}.id,
|
|
| 1386 |
(SELECT $buysell |
|
| 1387 |
FROM exchangerate |
|
| 1388 |
WHERE (${arap}.curr = exchangerate.curr)
|
|
| 1389 |
AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
|
|
| 1390 |
FROM ${arap}, ${ct}
|
|
| 1391 |
WHERE (paid != amount) |
|
| 1327 |
WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null)) |
|
| 1392 | 1328 |
AND (${arap}.storno IS FALSE)
|
| 1393 | 1329 |
AND (${arap}.${ct}_id = ${ct}.id)
|
| 1394 | 1330 |
AND (${ct}.id = ?)
|
| 1395 |
AND (transdate < (date $todate - interval '90 days'))
|
|
| 1331 |
AND (transdate <= (date $todate))
|
|
| 1396 | 1332 |
|
| 1397 | 1333 |
ORDER BY ctid, transdate, invnumber |; |
| 1398 | 1334 |
|
| ... | ... | |
| 1414 | 1350 |
$form->{AG} = [];
|
| 1415 | 1351 |
# for each company that has some stuff outstanding |
| 1416 | 1352 |
while (my ($id) = $sth->fetchrow_array) {
|
| 1417 |
do_statement($form, $sth_details, $q_details, $id, $id, $id, $id);
|
|
| 1353 |
do_statement($form, $sth_details, $q_details, $id); |
|
| 1418 | 1354 |
|
| 1419 | 1355 |
while (my $ref = $sth_details->fetchrow_hashref(NAME_lc)) {
|
| 1420 | 1356 |
$ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
|
Auch abrufbar als: Unified diff
Stichtagsbezogene Auswertungen von offenen Forderungen und Verbindlichkeiten