Revision d333f237
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 339 | 339 |
$category |
| 340 | 340 |
ORDER by c.accno|; |
| 341 | 341 |
|
| 342 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 343 |
$query = qq|SELECT g.accno, g.description, c.category |
|
| 344 |
FROM gifi g |
|
| 345 |
JOIN chart c ON (c.gifi_accno = g.accno) |
|
| 346 |
WHERE c.charttype = 'H' |
|
| 347 |
$category |
|
| 348 |
ORDER BY g.accno|; |
|
| 349 |
} |
|
| 350 |
|
|
| 351 | 342 |
$sth = $dbh->prepare($query); |
| 352 | 343 |
$sth->execute || $form->dberror($query); |
| 353 | 344 |
|
| ... | ... | |
| 392 | 383 |
|; |
| 393 | 384 |
} |
| 394 | 385 |
|
| 395 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 396 |
|
|
| 397 |
if ($form->{method} eq 'cash') {
|
|
| 398 |
|
|
| 399 |
$query = qq| |
|
| 400 |
|
|
| 401 |
SELECT g.accno, sum(ac.amount) AS amount, |
|
| 402 |
g.description, c.category |
|
| 403 |
FROM acc_trans ac |
|
| 404 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 405 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 406 |
JOIN gifi g ON (g.accno = c.gifi_accno) |
|
| 407 |
$dpt_join |
|
| 408 |
WHERE $where |
|
| 409 |
$dpt_where |
|
| 410 |
$category |
|
| 411 |
AND ac.trans_id IN |
|
| 412 |
( |
|
| 413 |
SELECT trans_id |
|
| 414 |
FROM acc_trans |
|
| 415 |
JOIN chart ON (chart_id = id) |
|
| 416 |
WHERE link LIKE '%AR_paid%' |
|
| 417 |
$subwhere |
|
| 418 |
) |
|
| 419 |
$project |
|
| 420 |
GROUP BY g.accno, g.description, c.category |
|
| 421 |
|
|
| 422 |
UNION ALL |
|
| 423 |
|
|
| 424 |
SELECT '' AS accno, SUM(ac.amount) AS amount, |
|
| 425 |
'' AS description, c.category |
|
| 426 |
FROM acc_trans ac |
|
| 427 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 428 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 429 |
$dpt_join |
|
| 430 |
WHERE $where |
|
| 431 |
$dpt_where |
|
| 432 |
$category |
|
| 433 |
AND c.gifi_accno = '' |
|
| 434 |
AND ac.trans_id IN |
|
| 435 |
( |
|
| 436 |
SELECT trans_id |
|
| 437 |
FROM acc_trans |
|
| 438 |
JOIN chart ON (chart_id = id) |
|
| 439 |
WHERE link LIKE '%AR_paid%' |
|
| 440 |
$subwhere |
|
| 441 |
) |
|
| 442 |
$project |
|
| 443 |
GROUP BY c.category |
|
| 444 |
|
|
| 445 |
UNION ALL |
|
| 446 |
|
|
| 447 |
SELECT g.accno, sum(ac.amount) AS amount, |
|
| 448 |
g.description, c.category |
|
| 449 |
FROM acc_trans ac |
|
| 450 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 451 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 452 |
JOIN gifi g ON (g.accno = c.gifi_accno) |
|
| 453 |
$dpt_join |
|
| 454 |
WHERE $where |
|
| 455 |
$dpt_where |
|
| 456 |
$category |
|
| 457 |
AND ac.trans_id IN |
|
| 458 |
( |
|
| 459 |
SELECT trans_id |
|
| 460 |
FROM acc_trans |
|
| 461 |
JOIN chart ON (chart_id = id) |
|
| 462 |
WHERE link LIKE '%AP_paid%' |
|
| 463 |
$subwhere |
|
| 464 |
) |
|
| 465 |
$project |
|
| 466 |
GROUP BY g.accno, g.description, c.category |
|
| 467 |
|
|
| 468 |
UNION ALL |
|
| 469 |
|
|
| 470 |
SELECT '' AS accno, SUM(ac.amount) AS amount, |
|
| 471 |
'' AS description, c.category |
|
| 472 |
FROM acc_trans ac |
|
| 473 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 474 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 475 |
$dpt_join |
|
| 476 |
WHERE $where |
|
| 477 |
$dpt_where |
|
| 478 |
$category |
|
| 479 |
AND c.gifi_accno = '' |
|
| 480 |
AND ac.trans_id IN |
|
| 481 |
( |
|
| 482 |
SELECT trans_id |
|
| 483 |
FROM acc_trans |
|
| 484 |
JOIN chart ON (chart_id = id) |
|
| 485 |
WHERE link LIKE '%AP_paid%' |
|
| 486 |
$subwhere |
|
| 487 |
) |
|
| 488 |
$project |
|
| 489 |
GROUP BY c.category |
|
| 490 |
|
|
| 491 |
UNION ALL |
|
| 492 |
|
|
| 493 |
-- add gl |
|
| 494 |
|
|
| 495 |
SELECT g.accno, sum(ac.amount) AS amount, |
|
| 496 |
g.description, c.category |
|
| 497 |
FROM acc_trans ac |
|
| 498 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 499 |
JOIN gifi g ON (g.accno = c.gifi_accno) |
|
| 500 |
JOIN gl a ON (a.id = ac.trans_id) |
|
| 501 |
$dpt_join |
|
| 502 |
WHERE $where |
|
| 503 |
$glwhere |
|
| 504 |
$dpt_where |
|
| 505 |
$category |
|
| 506 |
AND NOT (c.link = 'AR' OR c.link = 'AP') |
|
| 507 |
$project |
|
| 508 |
GROUP BY g.accno, g.description, c.category |
|
| 509 |
|
|
| 510 |
UNION ALL |
|
| 511 |
|
|
| 512 |
SELECT '' AS accno, SUM(ac.amount) AS amount, |
|
| 513 |
'' AS description, c.category |
|
| 514 |
FROM acc_trans ac |
|
| 515 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 516 |
JOIN gl a ON (a.id = ac.trans_id) |
|
| 517 |
$dpt_join |
|
| 518 |
WHERE $where |
|
| 519 |
$glwhere |
|
| 520 |
$dpt_where |
|
| 521 |
$category |
|
| 522 |
AND c.gifi_accno = '' |
|
| 523 |
AND NOT (c.link = 'AR' OR c.link = 'AP') |
|
| 524 |
$project |
|
| 525 |
GROUP BY c.category |
|
| 526 |
|; |
|
| 527 |
|
|
| 528 |
if ($form->{project_id}) {
|
|
| 529 |
|
|
| 530 |
$query .= qq| |
|
| 531 |
|
|
| 532 |
UNION ALL |
|
| 533 |
|
|
| 534 |
SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, |
|
| 535 |
g.description AS description, c.category |
|
| 536 |
FROM invoice ac |
|
| 537 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 538 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 539 |
JOIN chart c on (p.income_accno_id = c.id) |
|
| 540 |
JOIN gifi g ON (g.accno = c.gifi_accno) |
|
| 541 |
$dpt_join |
|
| 542 |
-- use transdate from subwhere |
|
| 543 |
WHERE 1 = 1 $subwhere |
|
| 544 |
AND c.category = 'I' |
|
| 545 |
$dpt_where |
|
| 546 |
AND ac.trans_id IN |
|
| 547 |
( |
|
| 548 |
SELECT trans_id |
|
| 549 |
FROM acc_trans |
|
| 550 |
JOIN chart ON (chart_id = id) |
|
| 551 |
WHERE link LIKE '%AR_paid%' |
|
| 552 |
$subwhere |
|
| 553 |
) |
|
| 554 |
$project |
|
| 555 |
GROUP BY g.accno, g.description, c.category |
|
| 556 |
|
|
| 557 |
UNION ALL |
|
| 558 |
|
|
| 559 |
SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, |
|
| 560 |
g.description AS description, c.category |
|
| 561 |
FROM invoice ac |
|
| 562 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 563 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 564 |
JOIN chart c on (p.expense_accno_id = c.id) |
|
| 565 |
JOIN gifi g ON (g.accno = c.gifi_accno) |
|
| 566 |
$dpt_join |
|
| 567 |
WHERE 1 = 1 $subwhere |
|
| 568 |
AND c.category = 'E' |
|
| 569 |
$dpt_where |
|
| 570 |
AND ac.trans_id IN |
|
| 571 |
( |
|
| 572 |
SELECT trans_id |
|
| 573 |
FROM acc_trans |
|
| 574 |
JOIN chart ON (chart_id = id) |
|
| 575 |
WHERE link LIKE '%AP_paid%' |
|
| 576 |
$subwhere |
|
| 577 |
) |
|
| 578 |
$project |
|
| 579 |
GROUP BY g.accno, g.description, c.category |
|
| 580 |
|; |
|
| 581 |
} |
|
| 582 |
|
|
| 583 |
} else {
|
|
| 584 |
|
|
| 585 |
if ($department_id) {
|
|
| 586 |
$dpt_join = qq| |
|
| 587 |
JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |
|
| 588 |
|; |
|
| 589 |
$dpt_where = qq| |
|
| 590 |
AND t.department_id = $department_id |
|
| 591 |
|; |
|
| 592 |
|
|
| 593 |
} |
|
| 594 |
|
|
| 595 |
$query = qq| |
|
| 596 |
|
|
| 597 |
SELECT g.accno, SUM(ac.amount) AS amount, |
|
| 598 |
g.description, c.category |
|
| 599 |
FROM acc_trans ac |
|
| 600 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 601 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 602 |
$dpt_join |
|
| 603 |
WHERE $where |
|
| 604 |
$dpt_from |
|
| 605 |
$category |
|
| 606 |
$project |
|
| 607 |
GROUP BY g.accno, g.description, c.category |
|
| 608 |
|
|
| 609 |
UNION ALL |
|
| 610 |
|
|
| 611 |
SELECT '' AS accno, SUM(ac.amount) AS amount, |
|
| 612 |
'' AS description, c.category |
|
| 613 |
FROM acc_trans ac |
|
| 614 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 615 |
$dpt_join |
|
| 616 |
WHERE $where |
|
| 617 |
$dpt_from |
|
| 618 |
$category |
|
| 619 |
AND c.gifi_accno = '' |
|
| 620 |
$project |
|
| 621 |
GROUP BY c.category |
|
| 622 |
|; |
|
| 623 |
|
|
| 624 |
if ($form->{project_id}) {
|
|
| 625 |
|
|
| 626 |
$query .= qq| |
|
| 627 |
|
|
| 628 |
UNION ALL |
|
| 629 |
|
|
| 630 |
SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, |
|
| 631 |
g.description AS description, c.category |
|
| 632 |
FROM invoice ac |
|
| 633 |
JOIN ar a ON (a.id = ac.trans_id) |
|
| 634 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 635 |
JOIN chart c on (p.income_accno_id = c.id) |
|
| 636 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 637 |
$dpt_join |
|
| 638 |
-- use transdate from subwhere |
|
| 639 |
WHERE 1 = 1 $subwhere |
|
| 640 |
AND c.category = 'I' |
|
| 641 |
$dpt_where |
|
| 642 |
$project |
|
| 643 |
GROUP BY g.accno, g.description, c.category |
|
| 644 |
|
|
| 645 |
UNION ALL |
|
| 646 |
|
|
| 647 |
SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, |
|
| 648 |
g.description AS description, c.category |
|
| 649 |
FROM invoice ac |
|
| 650 |
JOIN ap a ON (a.id = ac.trans_id) |
|
| 651 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 652 |
JOIN chart c on (p.expense_accno_id = c.id) |
|
| 653 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 654 |
$dpt_join |
|
| 655 |
WHERE 1 = 1 $subwhere |
|
| 656 |
AND c.category = 'E' |
|
| 657 |
$dpt_where |
|
| 658 |
$project |
|
| 659 |
GROUP BY g.accno, g.description, c.category |
|
| 660 |
|; |
|
| 661 |
} |
|
| 662 |
|
|
| 663 |
} |
|
| 664 |
|
|
| 665 |
} else { # standard account
|
|
| 386 |
{ # standard account
|
|
| 666 | 387 |
|
| 667 | 388 |
if ($form->{method} eq 'cash') {
|
| 668 | 389 |
|
| ... | ... | |
| 1199 | 920 |
# get beginning balances |
| 1200 | 921 |
if ($form->{fromdate}) {
|
| 1201 | 922 |
|
| 1202 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 1203 |
|
|
| 1204 |
$query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount, |
|
| 1205 |
g.description |
|
| 1206 |
FROM acc_trans ac |
|
| 1207 |
JOIN chart c ON (ac.chart_id = c.id) |
|
| 1208 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 1209 |
$dpt_join |
|
| 1210 |
WHERE ac.transdate < '$form->{fromdate}'
|
|
| 1211 |
$dpt_where |
|
| 1212 |
$project |
|
| 1213 |
GROUP BY g.accno, c.category, g.description |
|
| 1214 |
|; |
|
| 1215 |
|
|
| 1216 |
} else {
|
|
| 1217 |
|
|
| 1218 |
$query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, |
|
| 923 |
$query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, |
|
| 1219 | 924 |
c.description |
| 1220 | 925 |
FROM acc_trans ac |
| 1221 | 926 |
JOIN chart c ON (ac.chart_id = c.id) |
| ... | ... | |
| 1226 | 931 |
GROUP BY c.accno, c.category, c.description |
| 1227 | 932 |
|; |
| 1228 | 933 |
|
| 1229 |
} |
|
| 1230 |
|
|
| 1231 | 934 |
$sth = $dbh->prepare($query); |
| 1232 | 935 |
$sth->execute || $form->dberror($query); |
| 1233 | 936 |
|
| ... | ... | |
| 1251 | 954 |
WHERE c.charttype = 'H' |
| 1252 | 955 |
ORDER by c.accno|; |
| 1253 | 956 |
|
| 1254 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 1255 |
$query = qq|SELECT g.accno, g.description, c.category |
|
| 1256 |
FROM gifi g |
|
| 1257 |
JOIN chart c ON (c.gifi_accno = g.accno) |
|
| 1258 |
WHERE c.charttype = 'H' |
|
| 1259 |
ORDER BY g.accno|; |
|
| 1260 |
} |
|
| 1261 |
|
|
| 1262 | 957 |
$sth = $dbh->prepare($query); |
| 1263 | 958 |
$sth->execute || $form->dberror($query); |
| 1264 | 959 |
|
| ... | ... | |
| 1311 | 1006 |
$where .= $tofrom; |
| 1312 | 1007 |
} |
| 1313 | 1008 |
|
| 1314 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 1315 |
|
|
| 1316 |
$query = qq|SELECT g.accno, g.description, c.category, |
|
| 1317 |
SUM(ac.amount) AS amount |
|
| 1318 |
FROM acc_trans ac |
|
| 1319 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 1320 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 1321 |
$dpt_join |
|
| 1322 |
WHERE $where |
|
| 1323 |
$dpt_where |
|
| 1324 |
$project |
|
| 1325 |
GROUP BY g.accno, g.description, c.category |
|
| 1326 |
|; |
|
| 1327 |
|
|
| 1328 |
if ($form->{project_id}) {
|
|
| 1329 |
|
|
| 1330 |
$query .= qq| |
|
| 1331 |
|
|
| 1332 |
-- add project transactions from invoice |
|
| 1333 |
|
|
| 1334 |
UNION ALL |
|
| 1335 |
|
|
| 1336 |
SELECT g.accno, g.description, c.category, |
|
| 1337 |
SUM(ac.sellprice * ac.qty) AS amount |
|
| 1338 |
FROM invoice ac |
|
| 1339 |
JOIN ar a ON (ac.trans_id = a.id) |
|
| 1340 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 1341 |
JOIN chart c ON (p.income_accno_id = c.id) |
|
| 1342 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 1343 |
$dpt_join |
|
| 1344 |
WHERE $invwhere |
|
| 1345 |
$dpt_where |
|
| 1346 |
$project |
|
| 1347 |
GROUP BY g.accno, g.description, c.category |
|
| 1348 |
|
|
| 1349 |
UNION ALL |
|
| 1350 |
|
|
| 1351 |
SELECT g.accno, g.description, c.category, |
|
| 1352 |
SUM(ac.sellprice * ac.qty) * -1 AS amount |
|
| 1353 |
FROM invoice ac |
|
| 1354 |
JOIN ap a ON (ac.trans_id = a.id) |
|
| 1355 |
JOIN parts p ON (ac.parts_id = p.id) |
|
| 1356 |
JOIN chart c ON (p.expense_accno_id = c.id) |
|
| 1357 |
JOIN gifi g ON (c.gifi_accno = g.accno) |
|
| 1358 |
$dpt_join |
|
| 1359 |
WHERE $invwhere |
|
| 1360 |
$dpt_where |
|
| 1361 |
$project |
|
| 1362 |
GROUP BY g.accno, g.description, c.category |
|
| 1363 |
|; |
|
| 1364 |
} |
|
| 1365 |
|
|
| 1366 |
$query .= qq| |
|
| 1367 |
ORDER BY accno|; |
|
| 1368 |
|
|
| 1369 |
} else {
|
|
| 1009 |
{
|
|
| 1370 | 1010 |
|
| 1371 | 1011 |
$query = qq|SELECT c.accno, c.description, c.category, |
| 1372 | 1012 |
SUM(ac.amount) AS amount |
| ... | ... | |
| 1445 | 1085 |
AND c.accno = ?) AS credit |
| 1446 | 1086 |
|; |
| 1447 | 1087 |
|
| 1448 |
if ($form->{accounttype} eq 'gifi') {
|
|
| 1449 |
|
|
| 1450 |
$query = qq|SELECT (SELECT SUM(ac.amount) * -1 |
|
| 1451 |
FROM acc_trans ac |
|
| 1452 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 1453 |
$dpt_join |
|
| 1454 |
WHERE $where |
|
| 1455 |
$dpt_where |
|
| 1456 |
$project |
|
| 1457 |
AND ac.amount < 0 |
|
| 1458 |
AND c.gifi_accno = ?) AS debit, |
|
| 1459 |
|
|
| 1460 |
(SELECT SUM(ac.amount) |
|
| 1461 |
FROM acc_trans ac |
|
| 1462 |
JOIN chart c ON (c.id = ac.chart_id) |
|
| 1463 |
$dpt_join |
|
| 1464 |
WHERE $where |
|
| 1465 |
$dpt_where |
|
| 1466 |
$project |
|
| 1467 |
AND ac.amount > 0 |
|
| 1468 |
AND c.gifi_accno = ?) AS credit|; |
|
| 1469 |
|
|
| 1470 |
} |
|
| 1471 |
|
|
| 1472 | 1088 |
$drcr = $dbh->prepare($query); |
| 1473 | 1089 |
|
| 1474 | 1090 |
if ($form->{project_id}) {
|
| ... | ... | |
| 1787 | 1403 |
} |
| 1788 | 1404 |
$sth->finish; |
| 1789 | 1405 |
|
| 1790 |
# get gifi tax accounts |
|
| 1791 |
$query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description, |
|
| 1792 |
sum(t.rate) AS rate |
|
| 1793 |
FROM gifi g, chart c, tax t |
|
| 1794 |
WHERE g.accno = c.gifi_accno |
|
| 1795 |
AND c.id = t.chart_id |
|
| 1796 |
AND c.link LIKE '%CT_tax%' |
|
| 1797 |
GROUP BY g.accno, g.description |
|
| 1798 |
ORDER BY accno|; |
|
| 1799 |
$sth = $dbh->prepare($query); |
|
| 1800 |
$sth->execute || $form->dberror; |
|
| 1801 |
|
|
| 1802 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 1803 |
push @{ $form->{gifi_taxaccounts} }, $ref;
|
|
| 1804 |
} |
|
| 1805 |
$sth->finish; |
|
| 1806 |
|
|
| 1807 | 1406 |
$dbh->disconnect; |
| 1808 | 1407 |
|
| 1809 | 1408 |
$main::lxdebug->leave_sub(); |
| ... | ... | |
| 1831 | 1430 |
my ($accno, $rate); |
| 1832 | 1431 |
|
| 1833 | 1432 |
if ($form->{accno}) {
|
| 1834 |
if ($form->{accno} =~ /^gifi_/) {
|
|
| 1835 |
($null, $accno) = split /_/, $form->{accno};
|
|
| 1836 |
$rate = $form->{"$form->{accno}_rate"};
|
|
| 1837 |
$accno = qq| AND ch.gifi_accno = '$accno'|; |
|
| 1838 |
} else {
|
|
| 1839 |
$accno = $form->{accno};
|
|
| 1840 |
$rate = $form->{"$form->{accno}_rate"};
|
|
| 1841 |
$accno = qq| AND ch.accno = '$accno'|; |
|
| 1842 |
} |
|
| 1433 |
$accno = $form->{accno};
|
|
| 1434 |
$rate = $form->{"$form->{accno}_rate"};
|
|
| 1435 |
$accno = qq| AND ch.accno = '$accno'|; |
|
| 1843 | 1436 |
} |
| 1844 | 1437 |
$rate *= 1; |
| 1845 | 1438 |
|
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.