Revision 5e2cb6ed
Von Sven Schöling vor mehr als 10 Jahren hinzugefügt
SL/RP.pm | ||
---|---|---|
748 | 748 |
my @headingaccounts = (); |
749 | 749 |
my $dpt_where; |
750 | 750 |
my $dpt_where_without_arapgl; |
751 |
my ($customer_where, $customer_join, $customer_no_union); |
|
751 | 752 |
my $project; |
752 | 753 |
|
753 | 754 |
my $where = "1 = 1"; |
... | ... | |
759 | 760 |
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id), |
760 | 761 |
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id); |
761 | 762 |
} |
763 |
if ($form->{customer_id}) { |
|
764 |
$customer_join = qq| JOIN ar a ON (ac.trans_id = a.id) |; |
|
765 |
$customer_where = qq| AND (a.customer_id = | . conv_i($form->{customer_id}, 'NULL') . qq|) |; |
|
766 |
$customer_no_union = qq| AND 1=0 |; |
|
767 |
} |
|
762 | 768 |
|
763 | 769 |
# project_id only applies to getting transactions |
764 | 770 |
# it has nothing to do with a trial balance |
... | ... | |
805 | 811 |
my $min_max = $prefix eq 'from' ? 'min' : 'max'; |
806 | 812 |
$query = qq|SELECT ${min_max}(transdate) |
807 | 813 |
FROM acc_trans ac |
814 |
$customer_join |
|
808 | 815 |
WHERE (1 = 1) |
809 | 816 |
$dpt_where_without_arapgl |
817 |
$dpt_where |
|
818 |
$customer_where |
|
810 | 819 |
$project|; |
811 | 820 |
($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query); |
812 | 821 |
} |
... | ... | |
816 | 825 |
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description |
817 | 826 |
FROM acc_trans ac |
818 | 827 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
828 |
$customer_join |
|
819 | 829 |
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction |
820 | 830 |
$dpt_where_without_arapgl |
831 |
$dpt_where |
|
832 |
$customer_where |
|
821 | 833 |
$project |
822 | 834 |
GROUP BY c.accno, c.category, c.description |; |
823 | 835 |
|
... | ... | |
915 | 927 |
SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount |
916 | 928 |
FROM acc_trans ac |
917 | 929 |
JOIN chart c ON (c.id = ac.chart_id) |
930 |
$customer_join |
|
918 | 931 |
WHERE $where |
919 | 932 |
$dpt_where_without_arapgl |
920 | 933 |
$project |
... | ... | |
933 | 946 |
JOIN chart c ON (p.income_accno_id = c.id) |
934 | 947 |
WHERE $invwhere |
935 | 948 |
$dpt_where |
949 |
$customer_where |
|
936 | 950 |
$project |
937 | 951 |
GROUP BY c.accno, c.description, c.category |
938 | 952 |
|
... | ... | |
945 | 959 |
JOIN chart c ON (p.expense_accno_id = c.id) |
946 | 960 |
WHERE $invwhere |
947 | 961 |
$dpt_where |
962 |
$customer_no_union |
|
948 | 963 |
$project |
949 | 964 |
GROUP BY c.accno, c.description, c.category |
950 | 965 |
|; |
... | ... | |
971 | 986 |
(SELECT SUM(ac.amount) * -1 |
972 | 987 |
FROM acc_trans ac |
973 | 988 |
JOIN chart c ON (c.id = ac.chart_id) |
989 |
$customer_join |
|
974 | 990 |
WHERE $where |
975 | 991 |
$dpt_where_without_arapgl |
992 |
$dpt_where |
|
993 |
$customer_where |
|
976 | 994 |
$project |
977 | 995 |
AND (ac.amount < 0) |
978 | 996 |
AND (c.accno = ?)) AS debit, |
... | ... | |
980 | 998 |
(SELECT SUM(ac.amount) |
981 | 999 |
FROM acc_trans ac |
982 | 1000 |
JOIN chart c ON (c.id = ac.chart_id) |
1001 |
$customer_join |
|
983 | 1002 |
WHERE $where |
984 | 1003 |
$dpt_where_without_arapgl |
1004 |
$dpt_where |
|
1005 |
$customer_where |
|
985 | 1006 |
$project |
986 | 1007 |
AND ac.amount > 0 |
987 | 1008 |
AND c.accno = ?) AS credit, |
988 | 1009 |
(SELECT SUM(ac.amount) |
989 | 1010 |
FROM acc_trans ac |
990 | 1011 |
JOIN chart c ON (ac.chart_id = c.id) |
1012 |
$customer_join |
|
991 | 1013 |
WHERE $saldowhere |
992 | 1014 |
$dpt_where_without_arapgl |
1015 |
$dpt_where |
|
1016 |
$customer_where |
|
993 | 1017 |
$project |
994 | 1018 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, |
995 | 1019 |
|
996 | 1020 |
(SELECT SUM(ac.amount) |
997 | 1021 |
FROM acc_trans ac |
998 | 1022 |
JOIN chart c ON (ac.chart_id = c.id) |
1023 |
$customer_join |
|
999 | 1024 |
WHERE $sumwhere |
1000 | 1025 |
$dpt_where_without_arapgl |
1026 |
$dpt_where |
|
1027 |
$customer_where |
|
1001 | 1028 |
$project |
1002 |
AND amount > 0 |
|
1029 |
AND ac.amount > 0
|
|
1003 | 1030 |
AND c.accno = ?) AS sum_credit, |
1004 | 1031 |
|
1005 | 1032 |
(SELECT SUM(ac.amount) |
1006 | 1033 |
FROM acc_trans ac |
1007 | 1034 |
JOIN chart c ON (ac.chart_id = c.id) |
1035 |
$customer_join |
|
1008 | 1036 |
WHERE $sumwhere |
1009 | 1037 |
$dpt_where_without_arapgl |
1038 |
$dpt_where |
|
1039 |
$customer_where |
|
1010 | 1040 |
$project |
1011 |
AND amount < 0 |
|
1041 |
AND ac.amount < 0
|
|
1012 | 1042 |
AND c.accno = ?) AS sum_debit, |
1013 | 1043 |
|
1014 | 1044 |
(SELECT max(ac.transdate) FROM acc_trans ac |
1015 | 1045 |
JOIN chart c ON (ac.chart_id = c.id) |
1046 |
$customer_join |
|
1016 | 1047 |
WHERE $where |
1017 | 1048 |
$dpt_where_without_arapgl |
1049 |
$dpt_where |
|
1050 |
$customer_where |
|
1018 | 1051 |
$project |
1019 | 1052 |
AND c.accno = ?) AS last_transaction |
1020 | 1053 |
|
... | ... | |
1034 | 1067 |
JOIN chart c ON (p.expense_accno_id = c.id) |
1035 | 1068 |
WHERE $invwhere |
1036 | 1069 |
$dpt_where |
1070 |
$customer_no_union |
|
1037 | 1071 |
$project |
1038 | 1072 |
AND c.accno = ?) AS debit, |
1039 | 1073 |
|
... | ... | |
1044 | 1078 |
JOIN chart c ON (p.income_accno_id = c.id) |
1045 | 1079 |
WHERE $invwhere |
1046 | 1080 |
$dpt_where |
1081 |
$customer_where |
|
1047 | 1082 |
$project |
1048 | 1083 |
AND c.accno = ?) AS credit, |
1049 | 1084 |
|
1050 | 1085 |
(SELECT SUM(ac.amount) |
1051 | 1086 |
FROM acc_trans ac |
1052 | 1087 |
JOIN chart c ON (ac.chart_id = c.id) |
1088 |
$customer_join |
|
1053 | 1089 |
WHERE $saldowhere |
1054 | 1090 |
$dpt_where_without_arapgl |
1091 |
$dpt_where |
|
1092 |
$customer_where |
|
1055 | 1093 |
$project |
1056 | 1094 |
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, |
1057 | 1095 |
|
1058 | 1096 |
(SELECT SUM(ac.amount) |
1059 | 1097 |
FROM acc_trans ac |
1060 | 1098 |
JOIN chart c ON (ac.chart_id = c.id) |
1099 |
$customer_join |
|
1061 | 1100 |
WHERE $sumwhere |
1062 | 1101 |
$dpt_where_without_arapgl |
1102 |
$dpt_where |
|
1103 |
$customer_where |
|
1063 | 1104 |
$project |
1064 |
AND amount > 0 |
|
1105 |
AND ac.amount > 0
|
|
1065 | 1106 |
AND c.accno = ?) AS sum_credit, |
1066 | 1107 |
|
1067 | 1108 |
(SELECT SUM(ac.amount) |
1068 | 1109 |
FROM acc_trans ac |
1069 | 1110 |
JOIN chart c ON (ac.chart_id = c.id) |
1111 |
$customer_join |
|
1070 | 1112 |
WHERE $sumwhere |
1113 |
$dpt_where |
|
1071 | 1114 |
$dpt_where_without_arapgl |
1115 |
$customer_where |
|
1072 | 1116 |
$project |
1073 |
AND amount < 0 |
|
1117 |
AND ac.amount < 0
|
|
1074 | 1118 |
AND c.accno = ?) AS sum_debit, |
1075 | 1119 |
|
1076 | 1120 |
|
1077 | 1121 |
(SELECT max(ac.transdate) FROM acc_trans ac |
1078 | 1122 |
JOIN chart c ON (ac.chart_id = c.id) |
1123 |
$customer_join |
|
1079 | 1124 |
WHERE $where |
1080 | 1125 |
$dpt_where_without_arapgl |
1126 |
$dpt_where |
|
1127 |
$customer_where |
|
1081 | 1128 |
$project |
1082 | 1129 |
AND c.accno = ?) AS last_transaction |
1083 | 1130 |
|; |
Auch abrufbar als: Unified diff
Kundenauswahl bei Summen-/Saldenliste