Revision 7679dfa4
Von Sven Schöling vor mehr als 17 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 1 |
#===================================================================== |
|
| 1 |
###=====================================================================
|
|
| 2 | 2 |
# LX-Office ERP |
| 3 | 3 |
# Copyright (C) 2004 |
| 4 | 4 |
# Based on SQL-Ledger Version 2.1.9 |
| ... | ... | |
| 35 | 35 |
package RP; |
| 36 | 36 |
|
| 37 | 37 |
use SL::DBUtils; |
| 38 |
use Data::Dumper; |
|
| 38 | 39 |
|
| 39 | 40 |
sub balance_sheet {
|
| 40 | 41 |
$main::lxdebug->enter_sub(); |
| ... | ... | |
| 426 | 427 |
$dpt_join |
| 427 | 428 |
WHERE $where |
| 428 | 429 |
$glwhere |
| 429 |
$dpt_from
|
|
| 430 |
$dpt_where
|
|
| 430 | 431 |
$category |
| 431 | 432 |
AND NOT ((c.link = 'AR') OR (c.link = 'AP')) |
| 432 | 433 |
$project |
| ... | ... | |
| 606 | 607 |
if ($fromdate) {
|
| 607 | 608 |
$fromdate = conv_dateq($fromdate); |
| 608 | 609 |
if ($form->{method} eq 'cash') {
|
| 609 |
$subwhere .= " AND (transdate >= $fromdate)"; |
|
| 610 |
$glwhere = " AND (ac.transdate >= $fromdate)"; |
|
| 611 |
$prwhere = " AND (ar.transdate >= $fromdate)"; |
|
| 610 |
$subwhere .= " AND (transdate >= $fromdate)";
|
|
| 611 |
$glwhere = " AND (ac.transdate >= $fromdate)";
|
|
| 612 |
$prwhere = " AND (ar.transdate >= $fromdate)";
|
|
| 612 | 613 |
} else {
|
| 613 |
$where .= " AND (ac.transdate >= $fromdate)"; |
|
| 614 |
$where .= " AND (ac.transdate >= $fromdate)";
|
|
| 614 | 615 |
} |
| 615 | 616 |
} |
| 616 | 617 |
|
| 617 | 618 |
if ($todate) {
|
| 618 | 619 |
$todate = conv_dateq($todate); |
| 619 |
$where .= " AND (ac.transdate <= $todate)";
|
|
| 620 |
$subwhere .= " AND (transdate <= $todate)";
|
|
| 621 |
$prwhere .= " AND (ar.transdate <= $todate)"; |
|
| 620 |
$subwhere .= " AND (transdate <= $todate)";
|
|
| 621 |
$where .= " AND (ac.transdate <= $todate)";
|
|
| 622 |
$prwhere .= " AND (ar.transdate <= $todate)";
|
|
| 622 | 623 |
} |
| 623 | 624 |
|
| 624 | 625 |
if ($department_id) {
|
| ... | ... | |
| 632 | 633 |
|
| 633 | 634 |
if ($form->{method} eq 'cash') {
|
| 634 | 635 |
$query = |
| 635 |
qq|SELECT sum(ac.amount) AS amount, c.$category |
|
| 636 |
qq| |
|
| 637 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
|
| 636 | 638 |
FROM acc_trans ac |
| 637 | 639 |
JOIN chart c ON (c.id = ac.chart_id) |
| 638 | 640 |
JOIN ar a ON (a.id = ac.trans_id) |
| 639 | 641 |
$dpt_join |
| 640 |
WHERE $where |
|
| 641 |
$dpt_where |
|
| 642 |
AND ac.trans_id IN |
|
| 643 |
( |
|
| 644 |
SELECT trans_id |
|
| 645 |
FROM acc_trans |
|
| 646 |
JOIN chart ON (chart_id = id) |
|
| 647 |
WHERE (link LIKE '%AR_paid%') |
|
| 648 |
$subwhere |
|
| 649 |
) |
|
| 642 |
WHERE $where $dpt_where |
|
| 643 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere) |
|
| 650 | 644 |
$project |
| 651 |
GROUP BY c.$category |
|
| 645 |
GROUP BY c.$category
|
|
| 652 | 646 |
|
| 653 | 647 |
UNION |
| 654 | 648 |
|
| 655 |
SELECT sum(ac.amount) AS amount, c.$category
|
|
| 649 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 656 | 650 |
FROM acc_trans ac |
| 657 | 651 |
JOIN chart c ON (c.id = ac.chart_id) |
| 658 | 652 |
JOIN ap a ON (a.id = ac.trans_id) |
| 659 | 653 |
$dpt_join |
| 660 |
WHERE $where |
|
| 661 |
$dpt_where |
|
| 662 |
AND ac.trans_id IN |
|
| 663 |
( |
|
| 664 |
SELECT trans_id |
|
| 665 |
FROM acc_trans |
|
| 666 |
JOIN chart ON (chart_id = id) |
|
| 667 |
WHERE (link LIKE '%AP_paid%') |
|
| 668 |
$subwhere |
|
| 669 |
) |
|
| 654 |
WHERE $where $dpt_where |
|
| 655 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere) |
|
| 670 | 656 |
$project |
| 671 |
GROUP BY c.$category |
|
| 657 |
GROUP BY c.$category
|
|
| 672 | 658 |
|
| 673 | 659 |
UNION |
| 674 | 660 |
|
| 675 |
SELECT sum(ac.amount) AS amount, c.$category
|
|
| 661 |
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 676 | 662 |
FROM acc_trans ac |
| 677 | 663 |
JOIN chart c ON (c.id = ac.chart_id) |
| 678 | 664 |
JOIN gl a ON (a.id = ac.trans_id) |
| 679 | 665 |
$dpt_join |
| 680 |
WHERE $where |
|
| 681 |
$glwhere |
|
| 682 |
$dpt_from |
|
| 666 |
WHERE $where $dpt_where $glwhere |
|
| 683 | 667 |
AND NOT ((c.link = 'AR') OR (c.link = 'AP')) |
| 684 | 668 |
$project |
| 685 |
GROUP BY c.$category |; |
|
| 669 |
|
|
| 670 |
$project_union |
|
| 671 |
GROUP BY c.$category |
|
| 672 |
|; |
|
| 686 | 673 |
|
| 687 | 674 |
if ($form->{project_id}) {
|
| 688 |
$query .= qq|
|
|
| 675 |
$project_union = qq|
|
|
| 689 | 676 |
UNION |
| 690 | 677 |
|
| 691 |
SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category |
|
| 678 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 692 | 679 |
FROM invoice ac |
| 693 | 680 |
JOIN ar a ON (a.id = ac.trans_id) |
| 694 | 681 |
JOIN parts p ON (ac.parts_id = p.id) |
| 695 | 682 |
JOIN chart c on (p.income_accno_id = c.id) |
| 696 | 683 |
$dpt_join |
| 697 |
WHERE (c.category = 'I') |
|
| 698 |
$prwhere |
|
| 699 |
$dpt_where |
|
| 700 |
AND ac.trans_id IN |
|
| 701 |
( |
|
| 702 |
SELECT trans_id |
|
| 703 |
FROM acc_trans |
|
| 704 |
JOIN chart ON (chart_id = id) |
|
| 705 |
WHERE (link LIKE '%AR_paid%') |
|
| 706 |
$subwhere |
|
| 707 |
) |
|
| 684 |
WHERE (c.category = 'I') $prwhere $dpt_where |
|
| 685 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere) |
|
| 708 | 686 |
$project |
| 709 |
GROUP BY c.$category |
|
| 687 |
GROUP BY c.$category
|
|
| 710 | 688 |
|
| 711 | 689 |
UNION |
| 712 | 690 |
|
| 713 |
SELECT SUM(ac.sellprice) AS amount, c.$category |
|
| 691 |
SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 714 | 692 |
FROM invoice ac |
| 715 | 693 |
JOIN ap a ON (a.id = ac.trans_id) |
| 716 | 694 |
JOIN parts p ON (ac.parts_id = p.id) |
| 717 | 695 |
JOIN chart c on (p.expense_accno_id = c.id) |
| 718 | 696 |
$dpt_join |
| 719 |
WHERE (c.category = 'E') $prwhere |
|
| 720 |
$dpt_where |
|
| 721 |
AND ac.trans_id IN |
|
| 722 |
( |
|
| 723 |
SELECT trans_id |
|
| 724 |
FROM acc_trans |
|
| 725 |
JOIN chart ON (chart_id = id) |
|
| 726 |
WHERE (link LIKE '%AP_paid%') |
|
| 727 |
$subwhere |
|
| 728 |
) |
|
| 729 |
|
|
| 697 |
WHERE (c.category = 'E') $prwhere $dpt_where |
|
| 698 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere) |
|
| 730 | 699 |
$project |
| 731 |
GROUP BY c.$category |; |
|
| 700 |
GROUP BY c.$category |
|
| 701 |
|; |
|
| 732 | 702 |
} |
| 733 | 703 |
|
| 734 | 704 |
} else { # if ($form->{method} eq 'cash')
|
| ... | ... | |
| 738 | 708 |
} |
| 739 | 709 |
|
| 740 | 710 |
$query = qq| |
| 741 |
SELECT sum(ac.amount) AS amount, c.$category |
|
| 711 |
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 742 | 712 |
FROM acc_trans ac |
| 743 | 713 |
JOIN chart c ON (c.id = ac.chart_id) |
| 744 | 714 |
$dpt_join |
| ... | ... | |
| 751 | 721 |
$query .= qq| |
| 752 | 722 |
UNION |
| 753 | 723 |
|
| 754 |
SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category |
|
| 724 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 755 | 725 |
FROM invoice ac |
| 756 | 726 |
JOIN ar a ON (a.id = ac.trans_id) |
| 757 | 727 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 765 | 735 |
|
| 766 | 736 |
UNION |
| 767 | 737 |
|
| 768 |
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, c.$category
|
|
| 738 |
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
|
| 769 | 739 |
FROM invoice ac |
| 770 | 740 |
JOIN ap a ON (a.id = ac.trans_id) |
| 771 | 741 |
JOIN parts p ON (ac.parts_id = p.id) |
| ... | ... | |
| 783 | 753 |
my $accno; |
| 784 | 754 |
my $ref; |
| 785 | 755 |
|
| 786 |
#print $query; |
|
| 787 | 756 |
my $sth = prepare_execute_query($form, $dbh, $query); |
| 788 | 757 |
|
| 789 | 758 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 790 |
if ($ref->{amount} < 0) {
|
|
| 791 |
$ref->{amount} *= -1;
|
|
| 792 |
} |
|
| 793 | 759 |
if ($category eq "pos_bwa") {
|
| 794 | 760 |
if ($last_period) {
|
| 795 | 761 |
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
|
Auch abrufbar als: Unified diff
Bugfix 624 und 93.
G+V sollte jetzt keine Betraege mehr anzeigen sondern die richtigen Werte. Sind sie negativ sollte der Buchhalter sich Gedanken machen.