Revision d333f237
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/CA.pm | ||
|---|---|---|
| 60 | 60 |
} |
| 61 | 61 |
$sth->finish; |
| 62 | 62 |
|
| 63 |
$query = qq|SELECT accno, description |
|
| 64 |
FROM gifi|; |
|
| 65 |
$sth = $dbh->prepare($query); |
|
| 66 |
$sth->execute || $form->dberror($query); |
|
| 67 |
|
|
| 68 |
my %gifi; |
|
| 69 |
while (my ($accno, $description) = $sth->fetchrow_array) {
|
|
| 70 |
$gifi{$accno} = $description;
|
|
| 71 |
} |
|
| 72 |
$sth->finish; |
|
| 73 |
|
|
| 74 | 63 |
$query = qq{
|
| 75 | 64 |
SELECT |
| 76 | 65 |
c.accno, |
| ... | ... | |
| 96 | 85 |
FROM chart c |
| 97 | 86 |
LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) |
| 98 | 87 |
LEFT JOIN tax tx ON (tk.tax_id = tx.id) |
| 99 |
GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,
|
|
| 88 |
GROUP BY c.accno, c.id, c.description, c.charttype, |
|
| 100 | 89 |
c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, |
| 101 | 90 |
c.datevautomatik |
| 102 | 91 |
ORDER BY c.accno |
| ... | ... | |
| 107 | 96 |
|
| 108 | 97 |
while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
|
| 109 | 98 |
$ca->{amount} = $amount{ $ca->{accno} };
|
| 110 |
$ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
|
|
| 111 | 99 |
if ($ca->{amount} < 0) {
|
| 112 | 100 |
$ca->{debit} = $ca->{amount} * -1;
|
| 113 | 101 |
} else {
|
| ... | ... | |
| 133 | 121 |
# get chart_id |
| 134 | 122 |
my $query = qq|SELECT c.id FROM chart c |
| 135 | 123 |
WHERE c.accno = '$form->{accno}'|;
|
| 136 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 137 |
$query = qq|SELECT c.id FROM chart c |
|
| 138 |
WHERE c.gifi_accno = '$form->{gifi_accno}'|;
|
|
| 139 |
} |
|
| 140 | 124 |
my $sth = $dbh->prepare($query); |
| 141 | 125 |
$sth->execute || $form->dberror($query); |
| 142 | 126 |
|
| ... | ... | |
| 205 | 189 |
|; |
| 206 | 190 |
} |
| 207 | 191 |
|
| 208 |
if ($form->{accno} || $form->{gifi_accno}) {
|
|
| 192 |
if ($form->{accno}) {
|
|
| 209 | 193 |
|
| 210 | 194 |
# get category for account |
| 211 | 195 |
$query = qq|SELECT c.category |
| 212 | 196 |
FROM chart c |
| 213 | 197 |
WHERE c.accno = '$form->{accno}'|;
|
| 214 | 198 |
|
| 215 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 216 |
$query = qq|SELECT c.category |
|
| 217 |
FROM chart c |
|
| 218 |
WHERE c.gifi_accno = '$form->{gifi_accno}'
|
|
| 219 |
AND c.charttype = 'A'|; |
|
| 220 |
} |
|
| 221 |
|
|
| 222 | 199 |
$sth = $dbh->prepare($query); |
| 223 | 200 |
|
| 224 | 201 |
$sth->execute || $form->dberror($query); |
| ... | ... | |
| 273 | 250 |
|
| 274 | 251 |
} |
| 275 | 252 |
|
| 276 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 277 |
$query = qq|SELECT SUM(ac.amount) |
|
| 278 |
FROM acc_trans ac |
|
| 279 |
JOIN chart c ON (ac.chart_id = c.id) |
|
| 280 |
$dpt_join |
|
| 281 |
WHERE c.gifi_accno = '$form->{gifi_accno}'
|
|
| 282 |
AND ac.transdate < '$form->{fromdate}'
|
|
| 283 |
$dpt_where |
|
| 284 |
$project |
|
| 285 |
|; |
|
| 286 |
|
|
| 287 |
if ($form->{project_id}) {
|
|
| 288 |
|
|
| 289 |
$query .= qq| |
|
| 290 |
|
|
| 291 |
UNION |
|
| 292 |
|
|
| 293 |
SELECT SUM(ac.qty * ac.sellprice) |
|
| 294 |
FROM invoice ac |
|
| 295 |
JOIN ar a ON (ac.trans_id = a.id) |
|
| 296 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 297 |
JOIN chart c ON (p.income_accno_id = c.id) |
|
| 298 |
$dpt_join |
|
| 299 |
WHERE c.gifi_accno = '$form->{gifi_accno}'
|
|
| 300 |
AND a.transdate < '$form->{fromdate}'
|
|
| 301 |
AND c.category = 'I' |
|
| 302 |
$dpt_where |
|
| 303 |
$project |
|
| 304 |
|
|
| 305 |
UNION |
|
| 306 |
|
|
| 307 |
SELECT SUM(ac.qty * ac.sellprice) |
|
| 308 |
FROM invoice ac |
|
| 309 |
JOIN ap a ON (ac.trans_id = a.id) |
|
| 310 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 311 |
JOIN chart c ON (p.expense_accno_id = c.id) |
|
| 312 |
$dpt_join |
|
| 313 |
WHERE c.gifi_accno = '$form->{gifi_accno}'
|
|
| 314 |
AND a.transdate < '$form->{fromdate}'
|
|
| 315 |
AND c.category = 'E' |
|
| 316 |
$dpt_where |
|
| 317 |
$project |
|
| 318 |
|; |
|
| 319 |
|
|
| 320 |
} |
|
| 321 |
} |
|
| 322 |
|
|
| 323 | 253 |
$sth = $dbh->prepare($query); |
| 324 | 254 |
|
| 325 | 255 |
$sth->execute || $form->dberror($query); |
Auch abrufbar als: Unified diff
Weitere Vorkommen von GIFI entfernt. GIFI müsste bis auf Spalte chart.gifi_accno und Tabelle chart komplett entfernt sein.