package RP;
-
sub balance_sheet {
$main::lxdebug->enter_sub();
$category
ORDER by c.accno|;
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT g.accno, g.description, c.category
- FROM gifi g
- JOIN chart c ON (c.gifi_accno = g.accno)
- WHERE c.charttype = 'H'
- $category
- ORDER BY g.accno|;
- }
-
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
|;
}
- if ($form->{accounttype} eq 'gifi') {
-
- if ($form->{method} eq 'cash') {
-
- $query = qq|
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- 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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, 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
- $category
- AND c.gifi_accno = ''
- 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
-
- UNION ALL
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- 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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, 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
- $category
- AND c.gifi_accno = ''
- 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
-
- UNION ALL
-
--- add gl
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- JOIN gl a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $glwhere
- $dpt_where
- $category
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, 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_where
- $category
- AND c.gifi_accno = ''
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY c.category
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
- AND c.category = 'I'
- $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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE 1 = 1 $subwhere
- AND c.category = 'E'
- $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 g.accno, g.description, c.category
- |;
- }
-
- } else {
-
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
-
- }
-
- $query = qq|
-
- SELECT g.accno, SUM(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $where
- $dpt_from
- $category
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_from
- $category
- AND c.gifi_accno = ''
- $project
- GROUP BY c.category
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
- AND c.category = 'I'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE 1 = 1 $subwhere
- AND c.category = 'E'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
- }
-
- }
-
- } else { # standard account
+ { # standard account
if ($form->{method} eq 'cash') {
my $project;
my $where = "1 = 1";
my $glwhere = "";
+ my $prwhere = "";
my $subwhere = "";
my $item;
if ($form->{method} eq 'cash') {
$subwhere .= " AND transdate >= '$fromdate'";
$glwhere = " AND ac.transdate >= '$fromdate'";
+ $prwhere = " AND ar.transdate >= '$fromdate'";
} else {
$where .= " AND ac.transdate >= '$fromdate'";
}
if ($todate) {
$where .= " AND ac.transdate <= '$todate'";
$subwhere .= " AND transdate <= '$todate'";
+ $prwhere .= " AND ar.transdate <= '$todate'";
}
if ($department_id) {
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
+ WHERE 1 = 1 $prwhere
AND c.category = 'I'
$dpt_where
AND ac.trans_id IN
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
- WHERE 1 = 1 $subwhere
+ WHERE 1 = 1 $prwhere
AND c.category = 'E'
$dpt_where
AND ac.trans_id IN
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
+ WHERE 1 = 1 $prwhere
AND c.category = 'I'
$dpt_where
$project
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
- WHERE 1 = 1 $subwhere
+ WHERE 1 = 1 $prwhere
AND c.category = 'E'
$dpt_where
$project
my @accno;
my $accno;
my $ref;
+
#print $query;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
# get beginning balances
if ($form->{fromdate}) {
- if ($form->{accounttype} eq 'gifi') {
-
- $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
- g.description
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE ac.transdate < '$form->{fromdate}'
- $dpt_where
- $project
- GROUP BY g.accno, c.category, g.description
- |;
-
- } else {
-
- $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
+ $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
c.description
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
GROUP BY c.accno, c.category, c.description
|;
- }
-
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
WHERE c.charttype = 'H'
ORDER by c.accno|;
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT g.accno, g.description, c.category
- FROM gifi g
- JOIN chart c ON (c.gifi_accno = g.accno)
- WHERE c.charttype = 'H'
- ORDER BY g.accno|;
- }
-
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$where .= $tofrom;
}
- if ($form->{accounttype} eq 'gifi') {
-
- $query = qq|SELECT g.accno, g.description, c.category,
- SUM(ac.amount) AS amount
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- -- add project transactions from invoice
-
- UNION ALL
-
- SELECT g.accno, g.description, c.category,
- SUM(ac.sellprice * ac.qty) AS amount
- FROM invoice ac
- JOIN ar a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.income_accno_id = c.id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $invwhere
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT g.accno, g.description, c.category,
- SUM(ac.sellprice * ac.qty) * -1 AS amount
- FROM invoice ac
- JOIN ap a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $invwhere
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
- }
-
- $query .= qq|
- ORDER BY accno|;
-
- } else {
+ {
$query = qq|SELECT c.accno, c.description, c.category,
SUM(ac.amount) AS amount
AND c.accno = ?) AS credit
|;
- if ($form->{accounttype} eq 'gifi') {
-
- $query = qq|SELECT (SELECT SUM(ac.amount) * -1
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount < 0
- AND c.gifi_accno = ?) AS debit,
-
- (SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount > 0
- AND c.gifi_accno = ?) AS credit|;
-
- }
-
$drcr = $dbh->prepare($query);
if ($form->{project_id}) {
}
$sth->finish;
- # get gifi tax accounts
- $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description,
- sum(t.rate) AS rate
- FROM gifi g, chart c, tax t
- WHERE g.accno = c.gifi_accno
- AND c.id = t.chart_id
- AND c.link LIKE '%CT_tax%'
- GROUP BY g.accno, g.description
- ORDER BY accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{gifi_taxaccounts} }, $ref;
- }
- $sth->finish;
-
$dbh->disconnect;
$main::lxdebug->leave_sub();
my ($accno, $rate);
if ($form->{accno}) {
- if ($form->{accno} =~ /^gifi_/) {
- ($null, $accno) = split /_/, $form->{accno};
- $rate = $form->{"$form->{accno}_rate"};
- $accno = qq| AND ch.gifi_accno = '$accno'|;
- } else {
- $accno = $form->{accno};
- $rate = $form->{"$form->{accno}_rate"};
- $accno = qq| AND ch.accno = '$accno'|;
- }
+ $accno = $form->{accno};
+ $rate = $form->{"$form->{accno}_rate"};
+ $accno = qq| AND ch.accno = '$accno'|;
}
$rate *= 1;
my $sortorder = join ', ',
$form->sort_columns(qw(name invnumber ordnumber transdate source));
- $sortorder = $form->{sort} if $form->{sort};
-
+ $sortorder = $form->{sort} if $form->{sort};
+
# cycle through each id
foreach my $accno (split(/ /, $form->{paymentaccounts})) {
$form->{ "$key" . "gesamtleistung" } = 0;
$form->{ "$key" . "gesamtkosten" } = 0;
-
foreach $category (@categories) {
if (defined($form->{$category}{$key})) {
$form->{"$key$category"} =
$form->format_amount($myconfig,
$form->round_amount($form->{$category}{$key}, 2
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@gesamtleistung) {
$form->{ "$key" . "ergebnisvorsteuern" } =
$form->{ "$key" . "betriebsergebnis" } -
$form->{ "$key" . "neutraleraufwand" } +
- $form->{ "$key" . "neutralertrag" };
+ $form->{ "$key" . "neutralertrag" };
$form->{ "$key" . "ergebnis" } =
$form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
$form->{ "$key" . "gesamtleistung" } * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@ergebnisse) {
$form->{ "$key" . "gesamtleistung" } * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
$form->{ "$key" . "gesamtkosten" } * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@ergebnisse) {
$form->{ "$key" . "gesamtkosten" } * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
if (defined($form->{$category}{$key})) {
$form->{ "$key" . "pk" . "$category" } =
$form->format_amount(
- $myconfig,
- $form->round_amount(
- ($form->{$category}{$key} / $form->{10}{$key} * 100),
- $form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ $myconfig,
+ $form->round_amount(
+ ($form->{$category}{$key} / $form->{10}{$key} * 100),
+ $form->{decimalplaces}
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@ergebnisse) {
$form->{10}{$key} * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
if (defined($form->{$category}{$key})) {
$form->{ "$key" . "auf" . "$category" } =
$form->format_amount(
- $myconfig,
- $form->round_amount(
- ($form->{$category}{$key} / $form->{4}{$key} * 100),
- $form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ $myconfig,
+ $form->round_amount(
+ ($form->{$category}{$key} / $form->{4}{$key} * 100),
+ $form->{decimalplaces}
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@ergebnisse) {
$form->{4}{$key} * 100
),
$form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
foreach $item (@ergebnisse) {
$form->{ "$key" . "$item" } =
$form->format_amount($myconfig,
- $form->round_amount($form->{ "$key" . "$item" },
- $form->{decimalplaces}
- ), $form->{decimalplaces}, '0');
+ $form->round_amount($form->{ "$key" . "$item" },
+ $form->{decimalplaces}
+ ),
+ $form->{decimalplaces},
+ '0');
}
}
my $last_period = 0;
my $category = "pos_ustva";
my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
- 96 66 43 45 53 62 65 67);
+ 96 66 43 45 53 62 65 67);
my @categories_euro = qw(48 51 86 91 97 93 94);
$form->{decimalplaces} *= 1;
# }
#
# }
-
+
#
# Berechnung der USTVA Formularfelder
#
$form->{"86r"} = $form->{"861"};
$form->{"97r"} = $form->{"971"};
$form->{"93r"} = $form->{"931"};
+
#$form->{"96"} = $form->{"94"} * 0.16;
- $form->{"43"} =
+ $form->{"43"} =
$form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
$form->{"96"};
$form->{"45"} = $form->{"43"};
foreach $item (@categories_cent) {
$form->{$item} =
- $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), 2, '0');
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
+ 2, '0');
}
foreach $item (@categories_euro) {
$form->{$item} =
- $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), 0, '0');
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
+ 0, '0');
}
$dbh->disconnect;
projects / kivitendo-erp.git / blobdiff