| 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
|
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.