if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT g.accno, sum(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
)
$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
)
$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
UNION ALL
-- add gl
-
+
SELECT g.accno, sum(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
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
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
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
}
$query = qq|
-
+
SELECT g.accno, SUM(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
$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
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
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
if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
WHERE link LIKE '%AR_paid%'
$subwhere
)
-
+
$project
GROUP BY c.accno, c.description, c.category
-
+
UNION ALL
-
+
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
WHERE link LIKE '%AP_paid%'
$subwhere
)
-
+
$project
GROUP BY c.accno, c.description, c.category
-
+
UNION ALL
SELECT c.accno, sum(ac.amount) AS amount,
if ($form->{project_id}) {
$query .= qq|
-
+
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
c.description AS description, c.category
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
c.description AS description, c.category
FROM invoice ac
}
$query = qq|
-
+
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
if ($form->{project_id}) {
$query .= qq|
-
+
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
c.description AS description, c.category
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
c.description AS description, c.category
FROM invoice ac
if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
WHERE link LIKE '%AR_paid%'
$subwhere
)
-
+
$project
GROUP BY c.$category
-
+
UNION
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
WHERE link LIKE '%AP_paid%'
$subwhere
)
-
+
$project
GROUP BY c.$category
-
+
UNION
SELECT sum(ac.amount) AS amount,
if ($form->{project_id}) {
$query .= qq|
-
+
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) AS amount,
c.$category
FROM invoice ac
GROUP BY c.$category
UNION
-
+
SELECT SUM(ac.sellprice) AS amount,
c.$category
FROM invoice ac
}
$query = qq|
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
if ($form->{project_id}) {
$query .= qq|
-
+
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) AS amount,
c.$category
FROM invoice ac
GROUP BY c.$category
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
c.$category
FROM invoice ac
my @accno;
my $accno;
my $ref;
-
+ #print $query;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$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
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
$query .= qq|
-- add project transactions from invoice
-
+
UNION ALL
-
+
SELECT c.accno, c.description, c.category,
SUM(ac.sellprice * ac.qty) AS amount
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno, c.description, c.category,
SUM(ac.sellprice * ac.qty) * -1 AS amount
FROM invoice ac
$project
AND ac.amount < 0
AND c.accno = ?) AS debit,
-
+
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$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_where
$project
AND c.accno = ?) AS debit,
-
+
(SELECT SUM(ac.sellprice * ac.qty)
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate <= (date '$form->{todate}' - interval '0 days')
+ transdate <= (date '$form->{todate}' - interval '0 days')
AND transdate >= (date '$form->{todate}' - interval '30 days')
)
-
+
UNION
-- between 31-60 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ WHERE paid != amount
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate < (date '$form->{todate}' - interval '30 days')
+ transdate < (date '$form->{todate}' - interval '30 days')
AND transdate >= (date '$form->{todate}' - interval '60 days')
)
UNION
-
+
-- between 61-90 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate < (date '$form->{todate}' - interval '60 days')
+ transdate < (date '$form->{todate}' - interval '60 days')
AND transdate >= (date '$form->{todate}' - interval '90 days')
)
UNION
-
+
-- over 90 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
- AND transdate < (date '$form->{todate}' - interval '90 days')
+ AND transdate < (date '$form->{todate}' - interval '90 days')
ORDER BY
-
+
ctid, transdate, invnumber
-
+
|;
my $sth = $dbh->prepare($query);
my $ml = ($form->{db} eq 'ar') ? 1 : -1;
my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
- $sortorder = $form->{sort} unless $sortorder;
+ $sortorder = $form->{sort} if $form->{sort};
$query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
a.invnumber, n.name, a.netamount,
my $sortorder = join ', ',
$form->sort_columns(qw(name invnumber ordnumber transdate source));
-
+ $sortorder = $form->{sort} if $form->{sort};
+
# cycle through each id
foreach my $accno (split(/ /, $form->{paymentaccounts})) {
WHERE ac.chart_id = $ref->{id}
$where
$invnumber
-
+
UNION
SELECT g.description, g.reference, NULL AS ordnumber,
ac.transdate, ac.amount * $ml AS paid, ac.source,
$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');
}
}
foreach $item (@gesamtleistung) {
$form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
$form->{ "$key" . "ergebnisvorsteuern" } =
$form->{ "$key" . "betriebsergebnis" } -
- ($form->{ "$key" . "neutraleraufwand" } +
- $form->{ "$key" . "neutralertrag" });
+ $form->{ "$key" . "neutraleraufwand" } +
+ $form->{ "$key" . "neutralertrag" };
$form->{ "$key" . "ergebnis" } =
$form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
($form->{$category}{$key} /
$form->{ "$key" . "gesamtleistung" } * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
foreach $item (@ergebnisse) {
( $form->{ "$key" . "$item" } /
$form->{ "$key" . "gesamtleistung" } * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
($form->{$category}{$key} /
$form->{ "$key" . "gesamtkosten" } * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
foreach $item (@ergebnisse) {
($form->{ "$key" . "$item" } /
$form->{ "$key" . "gesamtkosten" } * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
$form->format_amount(
$myconfig,
$form->round_amount(
- ($form->{$category}{$key} / $form->{10}{$key} * 100), 2
- ));
+ ($form->{$category}{$key} / $form->{10}{$key} * 100),
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
foreach $item (@ergebnisse) {
($form->{ "$key" . "$item" } /
$form->{10}{$key} * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
$form->format_amount(
$myconfig,
$form->round_amount(
- ($form->{$category}{$key} / $form->{4}{$key} * 100), 2
- ));
+ ($form->{$category}{$key} / $form->{4}{$key} * 100),
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
foreach $item (@ergebnisse) {
($form->{ "$key" . "$item" } /
$form->{4}{$key} * 100
),
- 2
- ));
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
foreach $item (@ergebnisse) {
$form->{ "$key" . "$item" } =
$form->format_amount($myconfig,
- $form->round_amount($form->{ "$key" . "$item" }, 2
- ));
+ $form->round_amount($form->{ "$key" . "$item" },
+ $form->{decimalplaces}
+ ), $form->{decimalplaces}, '0');
}
}
my $last_period = 0;
my $category = "pos_ustva";
- my @categories_cent = qw(51r 86r 97r 93r 96 66 43 45 53 62 65 67);
+ my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
+ 96 66 43 45 53 62 65 67);
my @categories_euro = qw(48 51 86 91 97 93 94);
$form->{decimalplaces} *= 1;
# }
#
# }
-
- $form->{"51r"} = $form->{"51"} * 0.16;
- $form->{"86r"} = $form->{"86"} * 0.07;
- $form->{"97r"} = $form->{"97"} * 0.16;
- $form->{"93r"} = $form->{"93"} * 0.07;
- $form->{"96"} = $form->{"94"} * 0.16;
+
+ #
+ # Berechnung der USTVA Formularfelder
+ #
+ $form->{"51r"} = $form->{"511"};
+ $form->{"86r"} = $form->{"861"};
+ $form->{"97r"} = $form->{"971"};
+ $form->{"93r"} = $form->{"931"};
+ #$form->{"96"} = $form->{"94"} * 0.16;
$form->{"43"} =
$form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
$form->{"96"};
foreach $item (@categories_cent) {
$form->{$item} =
- $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
+ $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));
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), 0, '0');
}
$dbh->disconnect;
$main::lxdebug->leave_sub();
}
1;
-
projects / kivitendo-erp.git / blobdiff