Revision 9f07753b
Von Moritz Bunkus vor mehr als 11 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 522 | 522 |
FROM acc_trans acc |
| 523 | 523 |
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%') |
| 524 | 524 |
WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id) |
| 525 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
|
|
| 526 |
ELSE 0
|
|
| 525 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id) |
|
| 526 |
ELSE 0 |
|
| 527 | 527 |
/* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */ |
| 528 | 528 |
END |
| 529 | 529 |
) AS amount, c.$category |
| ... | ... | |
| 1392 | 1392 |
my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name)); |
| 1393 | 1393 |
$sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
|
| 1394 | 1394 |
|
| 1395 |
my $query = ''; |
|
| 1396 |
if ($form->{report} !~ /nontaxable/) {
|
|
| 1397 |
$query = |
|
| 1395 |
my $query = |
|
| 1398 | 1396 |
qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount, |
| 1399 | 1397 |
ac.amount * $ml AS tax |
| 1400 | 1398 |
FROM acc_trans ac |
| ... | ... | |
| 1416 | 1414 |
JOIN $table n ON (n.id = a.${table}_id)
|
| 1417 | 1415 |
JOIN ${table}tax t ON (t.${table}_id = n.id)
|
| 1418 | 1416 |
JOIN invoice i ON (i.trans_id = a.id) |
| 1419 |
JOIN partstax p ON (p.parts_id = i.parts_id) |
|
| 1420 | 1417 |
WHERE |
| 1421 | 1418 |
$where |
| 1422 | 1419 |
$accno |
| 1423 | 1420 |
AND (a.invoice = '1') |
| 1424 | 1421 |
ORDER BY $sortorder|; |
| 1425 |
} else {
|
|
| 1426 |
# only gather up non-taxable transactions |
|
| 1427 |
$query = |
|
| 1428 |
qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount |
|
| 1429 |
FROM acc_trans ac |
|
| 1430 |
JOIN ${arap} a ON (a.id = ac.trans_id)
|
|
| 1431 |
JOIN $table n ON (n.id = a.${table}_id)
|
|
| 1432 |
WHERE |
|
| 1433 |
$where |
|
| 1434 |
AND (a.invoice = '0') |
|
| 1435 |
AND (a.netamount = a.amount) |
|
| 1436 |
|
|
| 1437 |
UNION |
|
| 1438 |
|
|
| 1439 |
SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount |
|
| 1440 |
FROM acc_trans ac |
|
| 1441 |
JOIN ${arap} a ON (a.id = ac.trans_id)
|
|
| 1442 |
JOIN $table n ON (n.id = a.${table}_id)
|
|
| 1443 |
JOIN invoice i ON (i.trans_id = a.id) |
|
| 1444 |
WHERE |
|
| 1445 |
$where |
|
| 1446 |
AND (a.invoice = '1') |
|
| 1447 |
AND ( |
|
| 1448 |
a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
|
|
| 1449 |
OR |
|
| 1450 |
i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id)) |
|
| 1451 |
) |
|
| 1452 |
GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty |
|
| 1453 |
ORDER by $sortorder|; |
|
| 1454 |
} |
|
| 1455 | 1422 |
|
| 1456 | 1423 |
$form->{TR} = selectall_hashref_query($form, $dbh, $query);
|
| 1457 | 1424 |
|
Auch abrufbar als: Unified diff
Überflüssige Tabellen customertax, vendortax, partstax entfernt
Fixt #2186.