Revision d0a4a74d
Von Kivitendo Admin vor mehr als 7 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 522 | 522 |
ELSE 0 |
| 523 | 523 |
/* 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 */ |
| 524 | 524 |
END |
| 525 |
) AS amount, c.$category |
|
| 525 |
) AS amount, c.$category, c.accno, c.description
|
|
| 526 | 526 |
FROM acc_trans ac |
| 527 | 527 |
LEFT JOIN chart c ON (c.id = ac.chart_id) |
| 528 | 528 |
LEFT JOIN ar ON (ar.id = ac.trans_id) |
| 529 | 529 |
WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere) |
| 530 | 530 |
|
| 531 |
GROUP BY c.$category |
|
| 531 |
GROUP BY c.$category, c.accno, c.description
|
|
| 532 | 532 |
|
| 533 | 533 |
/* |
| 534 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 534 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 535 | 535 |
FROM acc_trans ac |
| 536 | 536 |
JOIN chart c ON (c.id = ac.chart_id) |
| 537 | 537 |
JOIN ar a ON (a.id = ac.trans_id) |
| 538 | 538 |
WHERE $where $dpt_where |
| 539 | 539 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere) |
| 540 | 540 |
$project |
| 541 |
GROUP BY c.$category |
|
| 541 |
GROUP BY c.$category, c.accno, c.description
|
|
| 542 | 542 |
*/ |
| 543 | 543 |
UNION |
| 544 | 544 |
|
| 545 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 545 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 546 | 546 |
FROM acc_trans ac |
| 547 | 547 |
JOIN chart c ON (c.id = ac.chart_id) |
| 548 | 548 |
JOIN ap a ON (a.id = ac.trans_id) |
| 549 | 549 |
WHERE $where $dpt_where |
| 550 | 550 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere) |
| 551 | 551 |
$project |
| 552 |
GROUP BY c.$category |
|
| 552 |
GROUP BY c.$category, c.accno, c.description
|
|
| 553 | 553 |
|
| 554 | 554 |
UNION |
| 555 | 555 |
|
| 556 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 556 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 557 | 557 |
FROM acc_trans ac |
| 558 | 558 |
JOIN chart c ON (c.id = ac.chart_id) |
| 559 | 559 |
JOIN gl a ON (a.id = ac.trans_id) |
| 560 | 560 |
WHERE $where $dpt_where $glwhere |
| 561 | 561 |
AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP')) |
| 562 | 562 |
$project |
| 563 |
GROUP BY c.$category |
|
| 563 |
GROUP BY c.$category, c.accno, c.description
|
|
| 564 | 564 |
|; |
| 565 | 565 |
|
| 566 | 566 |
if ($form->{project_id}) {
|
| 567 | 567 |
$query .= qq| |
| 568 | 568 |
UNION |
| 569 | 569 |
|
| 570 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 570 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 571 | 571 |
FROM invoice ac |
| 572 | 572 |
JOIN ar a ON (a.id = ac.trans_id) |
| 573 | 573 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 575 | 575 |
WHERE (c.category = 'I') $prwhere $dpt_where |
| 576 | 576 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere) |
| 577 | 577 |
$project |
| 578 |
GROUP BY c.$category |
|
| 578 |
GROUP BY c.$category, c.accno, c.description
|
|
| 579 | 579 |
|
| 580 | 580 |
UNION |
| 581 | 581 |
|
| 582 |
SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 582 |
SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 583 | 583 |
FROM invoice ac |
| 584 | 584 |
JOIN ap a ON (a.id = ac.trans_id) |
| 585 | 585 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 587 | 587 |
WHERE (c.category = 'E') $prwhere $dpt_where |
| 588 | 588 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere) |
| 589 | 589 |
$project |
| 590 |
GROUP BY c.$category |
|
| 590 |
GROUP BY c.$category, c.accno, c.description
|
|
| 591 | 591 |
|; |
| 592 | 592 |
} |
| 593 | 593 |
|
| ... | ... | |
| 600 | 600 |
} |
| 601 | 601 |
|
| 602 | 602 |
$query = qq| |
| 603 |
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 603 |
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 604 | 604 |
FROM acc_trans ac |
| 605 | 605 |
JOIN chart c ON (c.id = ac.chart_id) |
| 606 | 606 |
WHERE $where |
| 607 | 607 |
$dpt_where_without_arapgl |
| 608 | 608 |
$project |
| 609 |
GROUP BY c.$category |; |
|
| 609 |
GROUP BY c.$category, c.accno, c.description |;
|
|
| 610 | 610 |
|
| 611 | 611 |
if ($form->{project_id}) {
|
| 612 | 612 |
$query .= qq| |
| 613 | 613 |
UNION |
| 614 | 614 |
|
| 615 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 615 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 616 | 616 |
FROM invoice ac |
| 617 | 617 |
JOIN ar a ON (a.id = ac.trans_id) |
| 618 | 618 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 621 | 621 |
$prwhere |
| 622 | 622 |
$dpt_where |
| 623 | 623 |
$project |
| 624 |
GROUP BY c.$category |
|
| 624 |
GROUP BY c.$category, c.accno, c.description
|
|
| 625 | 625 |
|
| 626 | 626 |
UNION |
| 627 | 627 |
|
| 628 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 628 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
|
|
| 629 | 629 |
FROM invoice ac |
| 630 | 630 |
JOIN ap a ON (a.id = ac.trans_id) |
| 631 | 631 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 634 | 634 |
$prwhere |
| 635 | 635 |
$dpt_where |
| 636 | 636 |
$project |
| 637 |
GROUP BY c.$category |; |
|
| 637 |
GROUP BY c.$category, c.accno, c.description |;
|
|
| 638 | 638 |
} |
| 639 | 639 |
} |
| 640 | 640 |
|
| ... | ... | |
| 642 | 642 |
my $accno; |
| 643 | 643 |
my $ref; |
| 644 | 644 |
|
| 645 |
# store information for chart list in $form->{charts}
|
|
| 645 | 646 |
foreach my $ref (selectall_hashref_query($form, $dbh, $query)) {
|
| 647 |
unless ( defined $form->{charts}->{$ref->{accno}} ) {
|
|
| 648 |
# a chart may appear several times in the resulting hashref, init it the first time |
|
| 649 |
$form->{charts}->{$ref->{accno}} = { amount => 0,
|
|
| 650 |
"$category" => $ref->{"$category"},
|
|
| 651 |
accno => $ref->{accno},
|
|
| 652 |
description => $ref->{description},
|
|
| 653 |
}; |
|
| 654 |
} |
|
| 646 | 655 |
if ($category eq "pos_bwa") {
|
| 647 | 656 |
if ($last_period) {
|
| 648 | 657 |
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
|
| 649 | 658 |
} else {
|
| 650 | 659 |
$form->{ $ref->{$category} }{jetzt} += $ref->{amount};
|
| 660 |
# only increase chart amount for current period, not last_period |
|
| 661 |
$form->{charts}->{$ref->{accno}}->{amount} += $ref->{amount},
|
|
| 651 | 662 |
} |
| 652 | 663 |
} else {
|
| 653 | 664 |
$form->{ $ref->{$category} } += $ref->{amount};
|
| 665 |
$form->{charts}->{$ref->{accno}}->{amount} += $ref->{amount}; # no last_period for eur
|
|
| 654 | 666 |
} |
| 655 | 667 |
} |
| 656 | 668 |
|
Auch abrufbar als: Unified diff
RP.pm get_accounts_g zusätzlich nach Konto gruppieren
Dadurch erhält man die Salden der Einzelkonten in der Abfrage, und kann
diese in der EÜR und BWA als Kontenliste/Kontennachweis mit ausgeben.
Das Ergebnis aus der Abfrage für die Konten wird hierfür zusätzlich in
$form->{charts} gespeichert.
Für die Kontenliste wird an dieser Stelle auch die Kontenbeschreibung
mit abgefragt.