Revision 7679dfa4
Von Sven Schöling vor fast 18 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
|
#=====================================================================
|
||
|
###=====================================================================
|
||
|
# LX-Office ERP
|
||
|
# Copyright (C) 2004
|
||
|
# Based on SQL-Ledger Version 2.1.9
|
||
| ... | ... | |
|
package RP;
|
||
|
|
||
|
use SL::DBUtils;
|
||
|
use Data::Dumper;
|
||
|
|
||
|
sub balance_sheet {
|
||
|
$main::lxdebug->enter_sub();
|
||
| ... | ... | |
|
$dpt_join
|
||
|
WHERE $where
|
||
|
$glwhere
|
||
|
$dpt_from
|
||
|
$dpt_where
|
||
|
$category
|
||
|
AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
|
||
|
$project
|
||
| ... | ... | |
|
if ($fromdate) {
|
||
|
$fromdate = conv_dateq($fromdate);
|
||
|
if ($form->{method} eq 'cash') {
|
||
|
$subwhere .= " AND (transdate >= $fromdate)";
|
||
|
$glwhere = " AND (ac.transdate >= $fromdate)";
|
||
|
$prwhere = " AND (ar.transdate >= $fromdate)";
|
||
|
$subwhere .= " AND (transdate >= $fromdate)";
|
||
|
$glwhere = " AND (ac.transdate >= $fromdate)";
|
||
|
$prwhere = " AND (ar.transdate >= $fromdate)";
|
||
|
} else {
|
||
|
$where .= " AND (ac.transdate >= $fromdate)";
|
||
|
$where .= " AND (ac.transdate >= $fromdate)";
|
||
|
}
|
||
|
}
|
||
|
|
||
|
if ($todate) {
|
||
|
$todate = conv_dateq($todate);
|
||
|
$where .= " AND (ac.transdate <= $todate)";
|
||
|
$subwhere .= " AND (transdate <= $todate)";
|
||
|
$prwhere .= " AND (ar.transdate <= $todate)";
|
||
|
$subwhere .= " AND (transdate <= $todate)";
|
||
|
$where .= " AND (ac.transdate <= $todate)";
|
||
|
$prwhere .= " AND (ar.transdate <= $todate)";
|
||
|
}
|
||
|
|
||
|
if ($department_id) {
|
||
| ... | ... | |
|
|
||
|
if ($form->{method} eq 'cash') {
|
||
|
$query =
|
||
|
qq|SELECT sum(ac.amount) AS amount, c.$category
|
||
|
qq|
|
||
|
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
JOIN ar a ON (a.id = ac.trans_id)
|
||
|
$dpt_join
|
||
|
WHERE $where
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE (link LIKE '%AR_paid%')
|
||
|
$subwhere
|
||
|
)
|
||
|
WHERE $where $dpt_where
|
||
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
GROUP BY c.$category
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT sum(ac.amount) AS amount, c.$category
|
||
|
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
JOIN ap a ON (a.id = ac.trans_id)
|
||
|
$dpt_join
|
||
|
WHERE $where
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE (link LIKE '%AP_paid%')
|
||
|
$subwhere
|
||
|
)
|
||
|
WHERE $where $dpt_where
|
||
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
GROUP BY c.$category
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT sum(ac.amount) AS amount, c.$category
|
||
|
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
JOIN gl a ON (a.id = ac.trans_id)
|
||
|
$dpt_join
|
||
|
WHERE $where
|
||
|
$glwhere
|
||
|
$dpt_from
|
||
|
WHERE $where $dpt_where $glwhere
|
||
|
AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
|
||
|
$project
|
||
|
GROUP BY c.$category |;
|
||
|
|
||
|
$project_union
|
||
|
GROUP BY c.$category
|
||
|
|;
|
||
|
|
||
|
if ($form->{project_id}) {
|
||
|
$query .= qq|
|
||
|
$project_union = qq|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category
|
||
|
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ar a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.income_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
WHERE (c.category = 'I')
|
||
|
$prwhere
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE (link LIKE '%AR_paid%')
|
||
|
$subwhere
|
||
|
)
|
||
|
WHERE (c.category = 'I') $prwhere $dpt_where
|
||
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
|
||
|
$project
|
||
|
GROUP BY c.$category
|
||
|
GROUP BY c.$category
|
||
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice) AS amount, c.$category
|
||
|
SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ap a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
|
JOIN chart c on (p.expense_accno_id = c.id)
|
||
|
$dpt_join
|
||
|
WHERE (c.category = 'E') $prwhere
|
||
|
$dpt_where
|
||
|
AND ac.trans_id IN
|
||
|
(
|
||
|
SELECT trans_id
|
||
|
FROM acc_trans
|
||
|
JOIN chart ON (chart_id = id)
|
||
|
WHERE (link LIKE '%AP_paid%')
|
||
|
$subwhere
|
||
|
)
|
||
|
|
||
|
WHERE (c.category = 'E') $prwhere $dpt_where
|
||
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
|
||
|
$project
|
||
|
GROUP BY c.$category |;
|
||
|
GROUP BY c.$category
|
||
|
|;
|
||
|
}
|
||
|
|
||
|
} else { # if ($form->{method} eq 'cash')
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
$query = qq|
|
||
|
SELECT sum(ac.amount) AS amount, c.$category
|
||
|
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
$dpt_join
|
||
| ... | ... | |
|
$query .= qq|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category
|
||
|
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ar a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
| ... | ... | |
|
|
||
|
UNION
|
||
|
|
||
|
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, c.$category
|
||
|
SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
|
||
|
FROM invoice ac
|
||
|
JOIN ap a ON (a.id = ac.trans_id)
|
||
|
JOIN parts p ON (ac.parts_id = p.id)
|
||
| ... | ... | |
|
my $accno;
|
||
|
my $ref;
|
||
|
|
||
|
#print $query;
|
||
|
my $sth = prepare_execute_query($form, $dbh, $query);
|
||
|
|
||
|
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
if ($ref->{amount} < 0) {
|
||
|
$ref->{amount} *= -1;
|
||
|
}
|
||
|
if ($category eq "pos_bwa") {
|
||
|
if ($last_period) {
|
||
|
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
|
||
| SL/USTVA.pm | ||
|---|---|---|
|
$dpt_join
|
||
|
WHERE 1 = 1
|
||
|
$where
|
||
|
$dpt_from
|
||
|
$dpt_where
|
||
|
$project
|
||
|
GROUP BY tk.pos_ustva
|
||
|
|
||
| ... | ... | |
|
$dpt_join
|
||
|
WHERE 1 = 1
|
||
|
$where
|
||
|
$dpt_from
|
||
|
$dpt_where
|
||
|
$project
|
||
|
GROUP BY tk.pos_ustva
|
||
|
|
||
| sql/Pg-upgrade2/chart_category_to_sgn.sql | ||
|---|---|---|
|
-- @tag: chart_category_to_sgn
|
||
|
-- @description: Fuegt eine Hilfsfunktion ein mit der die interne Reprasentation der Konten (Haben positiv) in die Mehrungsrepraesentation gewandelt werden kann.
|
||
|
-- @depends:
|
||
|
|
||
|
CREATE OR REPLACE FUNCTION chart_category_to_sgn(CHARACTER(1))
|
||
|
RETURNS INTEGER
|
||
|
LANGUAGE SQL
|
||
|
AS 'SELECT 1 WHERE $1 IN (''I'', ''L'', ''Q'')
|
||
|
UNION
|
||
|
SELECT -1 WHERE $1 IN (''E'', ''A'')';
|
||
|
|
||
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.