Revision b8fee3fc
Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 178 | 178 |
|
| 179 | 179 |
my $query; |
| 180 | 180 |
my $dpt_where = ''; |
| 181 |
my $dpt_join = '';
|
|
| 181 |
my $dpt_where_without_arapgl = '';
|
|
| 182 | 182 |
my $project = ''; |
| 183 | 183 |
my $where = "1 = 1"; |
| 184 | 184 |
my $glwhere = ""; |
| ... | ... | |
| 235 | 235 |
} |
| 236 | 236 |
|
| 237 | 237 |
if ($department_id) {
|
| 238 |
$dpt_join = qq| JOIN department t ON (a.department_id = t.id) |; |
|
| 239 |
$dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|)|; |
|
| 238 |
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|; |
|
| 240 | 239 |
} |
| 241 | 240 |
|
| 242 | 241 |
if ($form->{project_id}) {
|
| ... | ... | |
| 251 | 250 |
FROM acc_trans ac |
| 252 | 251 |
JOIN chart c ON (c.id = ac.chart_id) |
| 253 | 252 |
JOIN ar a ON (a.id = ac.trans_id) |
| 254 |
$dpt_join |
|
| 255 | 253 |
WHERE $where |
| 256 | 254 |
$dpt_where |
| 257 | 255 |
$category |
| ... | ... | |
| 272 | 270 |
FROM acc_trans ac |
| 273 | 271 |
JOIN chart c ON (c.id = ac.chart_id) |
| 274 | 272 |
JOIN ap a ON (a.id = ac.trans_id) |
| 275 |
$dpt_join |
|
| 276 | 273 |
WHERE $where |
| 277 | 274 |
$dpt_where |
| 278 | 275 |
$category |
| ... | ... | |
| 293 | 290 |
FROM acc_trans ac |
| 294 | 291 |
JOIN chart c ON (c.id = ac.chart_id) |
| 295 | 292 |
JOIN gl a ON (a.id = ac.trans_id) |
| 296 |
$dpt_join |
|
| 297 | 293 |
WHERE $where |
| 298 | 294 |
$glwhere |
| 299 | 295 |
$dpt_where |
| ... | ... | |
| 313 | 309 |
JOIN ar a ON (a.id = ac.trans_id) |
| 314 | 310 |
JOIN parts p ON (ac.parts_id = p.id) |
| 315 | 311 |
JOIN chart c on (p.income_accno_id = c.id) |
| 316 |
$dpt_join |
|
| 317 | 312 |
-- use transdate from subwhere |
| 318 | 313 |
WHERE (c.category = 'I') |
| 319 | 314 |
$subwhere |
| ... | ... | |
| 336 | 331 |
JOIN ap a ON (a.id = ac.trans_id) |
| 337 | 332 |
JOIN parts p ON (ac.parts_id = p.id) |
| 338 | 333 |
JOIN chart c on (p.expense_accno_id = c.id) |
| 339 |
$dpt_join |
|
| 340 | 334 |
WHERE (c.category = 'E') |
| 341 | 335 |
$subwhere |
| 342 | 336 |
$dpt_where |
| ... | ... | |
| 354 | 348 |
|
| 355 | 349 |
} else { # if ($form->{method} eq 'cash')
|
| 356 | 350 |
if ($department_id) {
|
| 357 |
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |; |
|
| 358 |
$dpt_where = qq| AND t.department_id = | . conv_i($department_id); |
|
| 351 |
$dpt_where = qq| AND a.department_id = | . conv_i($department_id); |
|
| 352 |
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id), |
|
| 353 |
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id), |
|
| 354 |
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id); |
|
| 359 | 355 |
} |
| 360 | 356 |
|
| 361 | 357 |
$query = qq| |
| 362 | 358 |
SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category |
| 363 | 359 |
FROM acc_trans ac |
| 364 | 360 |
JOIN chart c ON (c.id = ac.chart_id) |
| 365 |
$dpt_join |
|
| 366 | 361 |
WHERE $where |
| 367 |
$dpt_where |
|
| 362 |
$dpt_where_without_arapgl
|
|
| 368 | 363 |
$category |
| 369 | 364 |
$project |
| 370 | 365 |
GROUP BY c.accno, c.description, c.category |; |
| ... | ... | |
| 379 | 374 |
JOIN ar a ON (a.id = ac.trans_id) |
| 380 | 375 |
JOIN parts p ON (ac.parts_id = p.id) |
| 381 | 376 |
JOIN chart c on (p.income_accno_id = c.id) |
| 382 |
$dpt_join |
|
| 383 | 377 |
-- use transdate from subwhere |
| 384 | 378 |
WHERE (c.category = 'I') |
| 385 | 379 |
$subwhere |
| ... | ... | |
| 394 | 388 |
JOIN ap a ON (a.id = ac.trans_id) |
| 395 | 389 |
JOIN parts p ON (ac.parts_id = p.id) |
| 396 | 390 |
JOIN chart c on (p.expense_accno_id = c.id) |
| 397 |
$dpt_join |
|
| 398 | 391 |
WHERE (c.category = 'E') |
| 399 | 392 |
$subwhere |
| 400 | 393 |
$dpt_where |
| ... | ... | |
| 462 | 455 |
|
| 463 | 456 |
my $query; |
| 464 | 457 |
my $dpt_where; |
| 465 |
my $dpt_join;
|
|
| 458 |
my $dpt_where_without_arapgl;
|
|
| 466 | 459 |
my $project; |
| 467 | 460 |
my $where = "1 = 1"; |
| 468 | 461 |
my $glwhere = ""; |
| ... | ... | |
| 494 | 487 |
} |
| 495 | 488 |
|
| 496 | 489 |
if ($department_id) {
|
| 497 |
$dpt_join = qq| JOIN department t ON (a.department_id = t.id) |; |
|
| 498 |
$dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 490 |
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 499 | 491 |
} |
| 500 | 492 |
|
| 501 | 493 |
if ($form->{project_id}) {
|
| ... | ... | |
| 522 | 514 |
FROM acc_trans acc |
| 523 | 515 |
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%') |
| 524 | 516 |
WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id) |
| 525 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
|
|
| 526 |
ELSE 0
|
|
| 517 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id) |
|
| 518 |
ELSE 0 |
|
| 527 | 519 |
/* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */ |
| 528 | 520 |
END |
| 529 | 521 |
) AS amount, c.$category |
| ... | ... | |
| 546 | 538 |
FROM acc_trans ac |
| 547 | 539 |
JOIN chart c ON (c.id = ac.chart_id) |
| 548 | 540 |
JOIN ar a ON (a.id = ac.trans_id) |
| 549 |
$dpt_join |
|
| 550 | 541 |
WHERE $where $dpt_where |
| 551 | 542 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere) |
| 552 | 543 |
$project |
| ... | ... | |
| 558 | 549 |
FROM acc_trans ac |
| 559 | 550 |
JOIN chart c ON (c.id = ac.chart_id) |
| 560 | 551 |
JOIN ap a ON (a.id = ac.trans_id) |
| 561 |
$dpt_join |
|
| 562 | 552 |
WHERE $where $dpt_where |
| 563 | 553 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere) |
| 564 | 554 |
$project |
| ... | ... | |
| 570 | 560 |
FROM acc_trans ac |
| 571 | 561 |
JOIN chart c ON (c.id = ac.chart_id) |
| 572 | 562 |
JOIN gl a ON (a.id = ac.trans_id) |
| 573 |
$dpt_join |
|
| 574 | 563 |
WHERE $where $dpt_where $glwhere |
| 575 | 564 |
AND NOT ((c.link = 'AR') OR (c.link = 'AP')) |
| 576 | 565 |
$project |
| ... | ... | |
| 586 | 575 |
JOIN ar a ON (a.id = ac.trans_id) |
| 587 | 576 |
JOIN parts p ON (ac.parts_id = p.id) |
| 588 | 577 |
JOIN chart c on (p.income_accno_id = c.id) |
| 589 |
$dpt_join |
|
| 590 | 578 |
WHERE (c.category = 'I') $prwhere $dpt_where |
| 591 | 579 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere) |
| 592 | 580 |
$project |
| ... | ... | |
| 599 | 587 |
JOIN ap a ON (a.id = ac.trans_id) |
| 600 | 588 |
JOIN parts p ON (ac.parts_id = p.id) |
| 601 | 589 |
JOIN chart c on (p.expense_accno_id = c.id) |
| 602 |
$dpt_join |
|
| 603 | 590 |
WHERE (c.category = 'E') $prwhere $dpt_where |
| 604 | 591 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere) |
| 605 | 592 |
$project |
| ... | ... | |
| 609 | 596 |
|
| 610 | 597 |
} else { # if ($form->{method} eq 'cash')
|
| 611 | 598 |
if ($department_id) {
|
| 612 |
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |; |
|
| 613 |
$dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 599 |
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 600 |
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id), |
|
| 601 |
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id), |
|
| 602 |
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id); |
|
| 614 | 603 |
} |
| 615 | 604 |
|
| 616 | 605 |
$query = qq| |
| 617 | 606 |
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category |
| 618 | 607 |
FROM acc_trans ac |
| 619 | 608 |
JOIN chart c ON (c.id = ac.chart_id) |
| 620 |
$dpt_join |
|
| 621 | 609 |
WHERE $where |
| 622 |
$dpt_where |
|
| 610 |
$dpt_where_without_arapgl
|
|
| 623 | 611 |
$project |
| 624 | 612 |
GROUP BY c.$category |; |
| 625 | 613 |
|
| ... | ... | |
| 632 | 620 |
JOIN ar a ON (a.id = ac.trans_id) |
| 633 | 621 |
JOIN parts p ON (ac.parts_id = p.id) |
| 634 | 622 |
JOIN chart c on (p.income_accno_id = c.id) |
| 635 |
$dpt_join |
|
| 636 | 623 |
WHERE (c.category = 'I') |
| 637 | 624 |
$prwhere |
| 638 | 625 |
$dpt_where |
| ... | ... | |
| 646 | 633 |
JOIN ap a ON (a.id = ac.trans_id) |
| 647 | 634 |
JOIN parts p ON (ac.parts_id = p.id) |
| 648 | 635 |
JOIN chart c on (p.expense_accno_id = c.id) |
| 649 |
$dpt_join |
|
| 650 | 636 |
WHERE (c.category = 'E') |
| 651 | 637 |
$prwhere |
| 652 | 638 |
$dpt_where |
| ... | ... | |
| 687 | 673 |
my ($null, $department_id) = split /--/, $form->{department};
|
| 688 | 674 |
my @headingaccounts = (); |
| 689 | 675 |
my $dpt_where; |
| 690 |
my $dpt_join;
|
|
| 676 |
my $dpt_where_without_arapgl;
|
|
| 691 | 677 |
my $project; |
| 692 | 678 |
|
| 693 | 679 |
my $where = "1 = 1"; |
| 694 | 680 |
my $invwhere = $where; |
| 695 | 681 |
|
| 696 | 682 |
if ($department_id) {
|
| 697 |
$dpt_join = qq| JOIN dpt_trans t ON (ac.trans_id = t.trans_id) |; |
|
| 698 |
$dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 683 |
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |; |
|
| 684 |
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id), |
|
| 685 |
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id), |
|
| 686 |
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id); |
|
| 699 | 687 |
} |
| 700 | 688 |
|
| 701 | 689 |
# project_id only applies to getting transactions |
| ... | ... | |
| 743 | 731 |
my $min_max = $prefix eq 'from' ? 'min' : 'max'; |
| 744 | 732 |
$query = qq|SELECT ${min_max}(transdate)
|
| 745 | 733 |
FROM acc_trans ac |
| 746 |
$dpt_join |
|
| 747 | 734 |
WHERE (1 = 1) |
| 748 |
$dpt_where |
|
| 735 |
$dpt_where_without_arapgl
|
|
| 749 | 736 |
$project|; |
| 750 | 737 |
($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
|
| 751 | 738 |
} |
| ... | ... | |
| 755 | 742 |
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description |
| 756 | 743 |
FROM acc_trans ac |
| 757 | 744 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
| 758 |
$dpt_join |
|
| 759 | 745 |
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
|
| 760 |
$dpt_where |
|
| 746 |
$dpt_where_without_arapgl
|
|
| 761 | 747 |
$project |
| 762 | 748 |
GROUP BY c.accno, c.category, c.description |; |
| 763 | 749 |
|
| ... | ... | |
| 855 | 841 |
SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount |
| 856 | 842 |
FROM acc_trans ac |
| 857 | 843 |
JOIN chart c ON (c.id = ac.chart_id) |
| 858 |
$dpt_join |
|
| 859 | 844 |
WHERE $where |
| 860 |
$dpt_where |
|
| 845 |
$dpt_where_without_arapgl
|
|
| 861 | 846 |
$project |
| 862 | 847 |
GROUP BY c.accno, c.description, c.category |; |
| 863 | 848 |
|
| ... | ... | |
| 872 | 857 |
JOIN ar a ON (ac.trans_id = a.id) |
| 873 | 858 |
JOIN parts p ON (ac.parts_id = p.id) |
| 874 | 859 |
JOIN chart c ON (p.income_accno_id = c.id) |
| 875 |
$dpt_join |
|
| 876 | 860 |
WHERE $invwhere |
| 877 | 861 |
$dpt_where |
| 878 | 862 |
$project |
| ... | ... | |
| 885 | 869 |
JOIN ap a ON (ac.trans_id = a.id) |
| 886 | 870 |
JOIN parts p ON (ac.parts_id = p.id) |
| 887 | 871 |
JOIN chart c ON (p.expense_accno_id = c.id) |
| 888 |
$dpt_join |
|
| 889 | 872 |
WHERE $invwhere |
| 890 | 873 |
$dpt_where |
| 891 | 874 |
$project |
| ... | ... | |
| 914 | 897 |
(SELECT SUM(ac.amount) * -1 |
| 915 | 898 |
FROM acc_trans ac |
| 916 | 899 |
JOIN chart c ON (c.id = ac.chart_id) |
| 917 |
$dpt_join |
|
| 918 | 900 |
WHERE $where |
| 919 |
$dpt_where |
|
| 901 |
$dpt_where_without_arapgl
|
|
| 920 | 902 |
$project |
| 921 | 903 |
AND (ac.amount < 0) |
| 922 | 904 |
AND (c.accno = ?)) AS debit, |
| ... | ... | |
| 924 | 906 |
(SELECT SUM(ac.amount) |
| 925 | 907 |
FROM acc_trans ac |
| 926 | 908 |
JOIN chart c ON (c.id = ac.chart_id) |
| 927 |
$dpt_join |
|
| 928 | 909 |
WHERE $where |
| 929 |
$dpt_where |
|
| 910 |
$dpt_where_without_arapgl
|
|
| 930 | 911 |
$project |
| 931 | 912 |
AND ac.amount > 0 |
| 932 | 913 |
AND c.accno = ?) AS credit, |
| 933 | 914 |
(SELECT SUM(ac.amount) |
| 934 | 915 |
FROM acc_trans ac |
| 935 | 916 |
JOIN chart c ON (ac.chart_id = c.id) |
| 936 |
$dpt_join |
|
| 937 | 917 |
WHERE $saldowhere |
| 938 |
$dpt_where |
|
| 918 |
$dpt_where_without_arapgl
|
|
| 939 | 919 |
$project |
| 940 | 920 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, |
| 941 | 921 |
|
| 942 | 922 |
(SELECT SUM(ac.amount) |
| 943 | 923 |
FROM acc_trans ac |
| 944 | 924 |
JOIN chart c ON (ac.chart_id = c.id) |
| 945 |
$dpt_join |
|
| 946 | 925 |
WHERE $sumwhere |
| 947 |
$dpt_where |
|
| 926 |
$dpt_where_without_arapgl
|
|
| 948 | 927 |
$project |
| 949 | 928 |
AND amount > 0 |
| 950 | 929 |
AND c.accno = ?) AS sum_credit, |
| ... | ... | |
| 952 | 931 |
(SELECT SUM(ac.amount) |
| 953 | 932 |
FROM acc_trans ac |
| 954 | 933 |
JOIN chart c ON (ac.chart_id = c.id) |
| 955 |
$dpt_join |
|
| 956 | 934 |
WHERE $sumwhere |
| 957 |
$dpt_where |
|
| 935 |
$dpt_where_without_arapgl
|
|
| 958 | 936 |
$project |
| 959 | 937 |
AND amount < 0 |
| 960 | 938 |
AND c.accno = ?) AS sum_debit, |
| 961 | 939 |
|
| 962 | 940 |
(SELECT max(ac.transdate) FROM acc_trans ac |
| 963 | 941 |
JOIN chart c ON (ac.chart_id = c.id) |
| 964 |
$dpt_join |
|
| 965 | 942 |
WHERE $where |
| 966 |
$dpt_where |
|
| 943 |
$dpt_where_without_arapgl
|
|
| 967 | 944 |
$project |
| 968 | 945 |
AND c.accno = ?) AS last_transaction |
| 969 | 946 |
|
| ... | ... | |
| 981 | 958 |
JOIN parts p ON (ac.parts_id = p.id) |
| 982 | 959 |
JOIN ap a ON (ac.trans_id = a.id) |
| 983 | 960 |
JOIN chart c ON (p.expense_accno_id = c.id) |
| 984 |
$dpt_join |
|
| 985 | 961 |
WHERE $invwhere |
| 986 | 962 |
$dpt_where |
| 987 | 963 |
$project |
| ... | ... | |
| 992 | 968 |
JOIN parts p ON (ac.parts_id = p.id) |
| 993 | 969 |
JOIN ar a ON (ac.trans_id = a.id) |
| 994 | 970 |
JOIN chart c ON (p.income_accno_id = c.id) |
| 995 |
$dpt_join |
|
| 996 | 971 |
WHERE $invwhere |
| 997 | 972 |
$dpt_where |
| 998 | 973 |
$project |
| ... | ... | |
| 1001 | 976 |
(SELECT SUM(ac.amount) |
| 1002 | 977 |
FROM acc_trans ac |
| 1003 | 978 |
JOIN chart c ON (ac.chart_id = c.id) |
| 1004 |
$dpt_join |
|
| 1005 | 979 |
WHERE $saldowhere |
| 1006 |
$dpt_where |
|
| 980 |
$dpt_where_without_arapgl
|
|
| 1007 | 981 |
$project |
| 1008 | 982 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, |
| 1009 | 983 |
|
| 1010 | 984 |
(SELECT SUM(ac.amount) |
| 1011 | 985 |
FROM acc_trans ac |
| 1012 | 986 |
JOIN chart c ON (ac.chart_id = c.id) |
| 1013 |
$dpt_join |
|
| 1014 | 987 |
WHERE $sumwhere |
| 1015 |
$dpt_where |
|
| 988 |
$dpt_where_without_arapgl
|
|
| 1016 | 989 |
$project |
| 1017 | 990 |
AND amount > 0 |
| 1018 | 991 |
AND c.accno = ?) AS sum_credit, |
| ... | ... | |
| 1020 | 993 |
(SELECT SUM(ac.amount) |
| 1021 | 994 |
FROM acc_trans ac |
| 1022 | 995 |
JOIN chart c ON (ac.chart_id = c.id) |
| 1023 |
$dpt_join |
|
| 1024 | 996 |
WHERE $sumwhere |
| 1025 |
$dpt_where |
|
| 997 |
$dpt_where_without_arapgl
|
|
| 1026 | 998 |
$project |
| 1027 | 999 |
AND amount < 0 |
| 1028 | 1000 |
AND c.accno = ?) AS sum_debit, |
| ... | ... | |
| 1030 | 1002 |
|
| 1031 | 1003 |
(SELECT max(ac.transdate) FROM acc_trans ac |
| 1032 | 1004 |
JOIN chart c ON (ac.chart_id = c.id) |
| 1033 |
$dpt_join |
|
| 1034 | 1005 |
WHERE $where |
| 1035 |
$dpt_where |
|
| 1006 |
$dpt_where_without_arapgl
|
|
| 1036 | 1007 |
$project |
| 1037 | 1008 |
AND c.accno = ?) AS last_transaction |
| 1038 | 1009 |
|; |
| ... | ... | |
| 1503 | 1474 |
} |
| 1504 | 1475 |
|
| 1505 | 1476 |
my ($query, $sth); |
| 1506 |
my $dpt_join; |
|
| 1507 | 1477 |
my $where; |
| 1508 | 1478 |
|
| 1509 | 1479 |
if ($form->{department_id}) {
|
| 1510 |
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |; |
|
| 1511 |
$where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
|
|
| 1480 |
$where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
|
|
| 1512 | 1481 |
} |
| 1513 | 1482 |
|
| 1514 | 1483 |
if ($form->{fromdate}) {
|
| ... | ... | |
| 1526 | 1495 |
if ($form->{reference}) {
|
| 1527 | 1496 |
$reference = $dbh->quote('%' . $form->{reference} . '%');
|
| 1528 | 1497 |
$invnumber = " AND (a.invnumber LIKE $reference)"; |
| 1529 |
$reference = " AND (g.reference LIKE $reference)";
|
|
| 1498 |
$reference = " AND (a.reference LIKE $reference)";
|
|
| 1530 | 1499 |
} |
| 1531 | 1500 |
if ($form->{source}) {
|
| 1532 | 1501 |
$where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
|
| ... | ... | |
| 1543 | 1512 |
'memo' => [ qw(lower_memo) ], |
| 1544 | 1513 |
); |
| 1545 | 1514 |
my %lowered_columns = ( |
| 1546 |
'invnumber' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
|
|
| 1515 |
'invnumber' => { 'gl' => 'a.reference', 'arap' => 'a.invnumber', },
|
|
| 1547 | 1516 |
'memo' => { 'gl' => 'ac.memo', 'arap' => 'ac.memo', },
|
| 1548 | 1517 |
'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
|
| 1549 |
'name' => { 'gl' => 'g.description', 'arap' => 'c.name', },
|
|
| 1518 |
'name' => { 'gl' => 'a.description', 'arap' => 'c.name', },
|
|
| 1550 | 1519 |
); |
| 1551 | 1520 |
|
| 1552 | 1521 |
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
|
| ... | ... | |
| 1573 | 1542 |
FROM acc_trans ac |
| 1574 | 1543 |
JOIN $arap a ON (ac.trans_id = a.id) |
| 1575 | 1544 |
JOIN $table c ON (c.id = a.${table}_id)
|
| 1576 |
$dpt_join |
|
| 1577 | 1545 |
WHERE (ac.chart_id = ?) |
| 1578 | 1546 |
$where |
| 1579 | 1547 |
$invnumber |
| 1580 | 1548 |
|
| 1581 | 1549 |
UNION |
| 1582 | 1550 |
|
| 1583 |
SELECT g.description, g.reference, NULL AS ordnumber,
|
|
| 1551 |
SELECT a.description, a.reference, NULL AS ordnumber,
|
|
| 1584 | 1552 |
ac.transdate, ac.amount * $ml AS paid, ac.source, |
| 1585 |
'0' as invoice, g.id, ac.memo, 'gl' AS module
|
|
| 1553 |
'0' as invoice, a.id, ac.memo, 'gl' AS module
|
|
| 1586 | 1554 |
$columns_for_sorting{gl}
|
| 1587 | 1555 |
FROM acc_trans ac |
| 1588 |
JOIN gl g ON (g.id = ac.trans_id) |
|
| 1589 |
$dpt_join |
|
| 1556 |
JOIN gl a ON (a.id = ac.trans_id) |
|
| 1590 | 1557 |
WHERE (ac.chart_id = ?) |
| 1591 | 1558 |
$where |
| 1592 | 1559 |
$reference |
Auch abrufbar als: Unified diff
Entfernt dpt_trans
Entfernt die Tabelle dpt_trans. Dazu mussten die SQL-queries in der
SL/RP.pm noch angepasst werden. Das Upgrate-Script entfernt zusätzlich
noch alle Trigger/Funktionen, die in die Tabelle dpt_trans noch benutzt
haben.