Revision 7a0bca7f
Von Moritz Bunkus vor mehr als 15 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 939 | 939 |
|
| 940 | 940 |
map { $_->{onhand} *= 1 } @{ $form->{parts} };
|
| 941 | 941 |
|
| 942 |
## my $where = qq|1 = 1|; |
|
| 943 |
## my (@values, $var, $flds, $group, $limit); |
|
| 944 |
## |
|
| 945 |
## foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
|
|
| 946 |
## my $column = $item; |
|
| 947 |
## $column =~ s/.*\.//; # get rid of table prefixes |
|
| 948 |
## if ($form->{$column}) {
|
|
| 949 |
## $where .= qq| AND ($item ILIKE ?)|; |
|
| 950 |
## push(@values, "%$form->{$column}%");
|
|
| 951 |
## } |
|
| 952 |
## } |
|
| 953 |
## |
|
| 954 |
## # special case for description |
|
| 955 |
## if ($form->{description}
|
|
| 956 |
## && !( $form->{bought} || $form->{sold} || $form->{onorder}
|
|
| 957 |
## || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
|
|
| 958 |
## $where .= qq| AND (p.description ILIKE ?)|; |
|
| 959 |
## push(@values, "%$form->{description}%");
|
|
| 960 |
## } |
|
| 961 |
## |
|
| 962 |
## # special case for serialnumber |
|
| 963 |
## if ($form->{l_serialnumber} && $form->{serialnumber}) {
|
|
| 964 |
## $where .= qq| AND (serialnumber ILIKE ?)|; |
|
| 965 |
## push(@values, "%$form->{serialnumber}%");
|
|
| 966 |
## } |
|
| 967 |
## |
|
| 968 |
## if ($form->{searchitems} eq 'part') {
|
|
| 969 |
## $where .= qq| AND (p.inventory_accno_id > 0) |; |
|
| 970 |
## } |
|
| 971 |
## |
|
| 972 |
## if ($form->{searchitems} eq 'assembly') {
|
|
| 973 |
## $form->{bought} = "";
|
|
| 974 |
## $where .= qq| AND p.assembly|; |
|
| 975 |
## } |
|
| 976 |
## |
|
| 977 |
## if ($form->{searchitems} eq 'service') {
|
|
| 978 |
## $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|; |
|
| 979 |
## # irrelevant for services |
|
| 980 |
## map { $form->{$_} = '' } qw(make model);
|
|
| 981 |
## } |
|
| 982 |
## |
|
| 983 |
## # items which were never bought, sold or on an order |
|
| 984 |
## if ($form->{itemstatus} eq 'orphaned') {
|
|
| 985 |
## map { $form->{$_} = 0 } qw(onhand short bought sold onorder ordered rfq quoted);
|
|
| 986 |
## map { $form->{$_} = '' } qw(transdatefrom transdateto);
|
|
| 987 |
## |
|
| 988 |
## $where .= |
|
| 989 |
## qq| AND (p.onhand = 0) |
|
| 990 |
## AND p.id NOT IN |
|
| 991 |
## ( |
|
| 992 |
## SELECT DISTINCT parts_id FROM invoice |
|
| 993 |
## UNION |
|
| 994 |
## SELECT DISTINCT parts_id FROM assembly |
|
| 995 |
## UNION |
|
| 996 |
## SELECT DISTINCT parts_id FROM orderitems |
|
| 997 |
## )|; |
|
| 998 |
## } |
|
| 999 |
## |
|
| 1000 |
## my %status2condition = ( |
|
| 1001 |
## active => " AND (p.obsolete = '0')", |
|
| 1002 |
## obsolete => " AND (p.obsolete = '1')", |
|
| 1003 |
## onhand => " AND (p.onhand > 0)", |
|
| 1004 |
## short => " AND (p.onhand < p.rop)", |
|
| 1005 |
## ); |
|
| 1006 |
## $where .= $status2condition{$form->{itemstatus}};
|
|
| 1007 |
## |
|
| 1008 |
## $form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');
|
|
| 1009 |
## |
|
| 1010 |
## my @subcolumns; |
|
| 1011 |
## foreach my $column (qw(make model)) {
|
|
| 1012 |
## push @subcolumns, $column if $form->{$column};
|
|
| 1013 |
## } |
|
| 1014 |
## if (@subcolumns) {
|
|
| 1015 |
## $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
|
|
| 1016 |
## push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
|
|
| 1017 |
## } |
|
| 1018 |
## |
|
| 1019 |
## if ($form->{l_soldtotal}) {
|
|
| 1020 |
## $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|; |
|
| 1021 |
## $group = qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|; |
|
| 1022 |
## } |
|
| 1023 |
## |
|
| 1024 |
## $limit = qq| LIMIT 100| if ($form->{top100});
|
|
| 1025 |
## |
|
| 1026 |
## # connect to database |
|
| 1027 |
## my $dbh = $form->dbconnect($myconfig); |
|
| 1028 |
## |
|
| 1029 |
## my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand |
|
| 1030 |
## invnumber ordnumber quonumber name drawing microfiche |
|
| 1031 |
## serialnumber soldtotal deliverydate); |
|
| 1032 |
## |
|
| 1033 |
## my $sortorder = "partnumber"; |
|
| 1034 |
## $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
|
|
| 1035 |
## $sortorder .= " DESC" if ($form->{revers});
|
|
| 1036 |
## |
|
| 1037 |
## my $query = ""; |
|
| 1038 |
## |
|
| 1039 |
## if ($form->{l_soldtotal}) {
|
|
| 1040 |
## $form->{soldtotal} = 'soldtotal';
|
|
| 1041 |
## $query = |
|
| 1042 |
## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 1043 |
## p.bin, p.sellprice, p.listprice, p.lastcost, |
|
| 1044 |
## p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal |
|
| 1045 |
## FROM parts p |
|
| 1046 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i |
|
| 1047 |
## WHERE $where |
|
| 1048 |
## $group |
|
| 1049 |
## ORDER BY $sortorder $limit|; |
|
| 1050 |
## } else {
|
|
| 1051 |
## $query = |
|
| 1052 |
## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 1053 |
## p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1054 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1055 |
## pg.partsgroup |
|
| 1056 |
## FROM parts p |
|
| 1057 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1058 |
## WHERE $where |
|
| 1059 |
## $group |
|
| 1060 |
## ORDER BY $sortorder $limit|; |
|
| 1061 |
## } |
|
| 1062 |
## |
|
| 1063 |
## my @all_values = @values; |
|
| 1064 |
## |
|
| 1065 |
## # rebuild query for bought and sold items |
|
| 1066 |
## if ( $form->{bought}
|
|
| 1067 |
## || $form->{sold}
|
|
| 1068 |
## || $form->{onorder}
|
|
| 1069 |
## || $form->{ordered}
|
|
| 1070 |
## || $form->{rfq}
|
|
| 1071 |
## || $form->{quoted}) {
|
|
| 1072 |
## my $union = ""; |
|
| 1073 |
## $query = ""; |
|
| 1074 |
## @all_values = (); |
|
| 1075 |
## |
|
| 1076 |
## if ($form->{bought} || $form->{sold}) {
|
|
| 1077 |
## |
|
| 1078 |
## my @invvalues = @values; |
|
| 1079 |
## my $invwhere = "$where"; |
|
| 1080 |
# $invwhere .= qq| AND i.assemblyitem = '0'|; |
|
| 1081 |
## |
|
| 1082 |
## if ($form->{transdatefrom}) {
|
|
| 1083 |
## $invwhere .= qq| AND a.transdate >= ?|; |
|
| 1084 |
## push(@invvalues, $form->{transdatefrom});
|
|
| 1085 |
## } |
|
| 1086 |
## |
|
| 1087 |
## if ($form->{transdateto}) {
|
|
| 1088 |
## $invwhere .= qq| AND a.transdate <= ?|; |
|
| 1089 |
## push(@invvalues, $form->{transdateto});
|
|
| 1090 |
## } |
|
| 1091 |
## |
|
| 1092 |
## if ($form->{description}) {
|
|
| 1093 |
## $invwhere .= qq| AND i.description ILIKE ?|; |
|
| 1094 |
## push(@invvalues, '%' . $form->{description} . '%');
|
|
| 1095 |
## } |
|
| 1096 |
## |
|
| 1097 |
## $flds = |
|
| 1098 |
## qq|p.id, p.partnumber, i.description, i.serialnumber, |
|
| 1099 |
# i.qty AS onhand, i.unit, p.bin, i.sellprice, |
|
| 1100 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1101 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1102 |
## pg.partsgroup, |
|
| 1103 |
## a.invnumber, a.ordnumber, a.quonumber, i.trans_id, |
|
| 1104 |
## ct.name, i.deliverydate|; |
|
| 1105 |
## |
|
| 1106 |
## if ($form->{bought}) {
|
|
| 1107 |
## $query = |
|
| 1108 |
## qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate |
|
| 1109 |
## FROM invoice i |
|
| 1110 |
## JOIN parts p ON (p.id = i.parts_id) |
|
| 1111 |
## JOIN ap a ON (a.id = i.trans_id) |
|
| 1112 |
## JOIN vendor ct ON (a.vendor_id = ct.id) |
|
| 1113 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1114 |
## WHERE $invwhere|; |
|
| 1115 |
## |
|
| 1116 |
## $union = qq| UNION |; |
|
| 1117 |
## |
|
| 1118 |
## push(@all_values, @invvalues); |
|
| 1119 |
## } |
|
| 1120 |
## |
|
| 1121 |
## if ($form->{sold}) {
|
|
| 1122 |
## $query .= |
|
| 1123 |
## qq|$union |
|
| 1124 |
## |
|
| 1125 |
## SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate |
|
| 1126 |
## FROM invoice i |
|
| 1127 |
## JOIN parts p ON (p.id = i.parts_id) |
|
| 1128 |
## JOIN ar a ON (a.id = i.trans_id) |
|
| 1129 |
## JOIN customer ct ON (a.customer_id = ct.id) |
|
| 1130 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1131 |
## WHERE $invwhere|; |
|
| 1132 |
## $union = qq| UNION |; |
|
| 1133 |
## |
|
| 1134 |
## push(@all_values, @invvalues); |
|
| 1135 |
## } |
|
| 1136 |
## } |
|
| 1137 |
## |
|
| 1138 |
## if ($form->{onorder} || $form->{ordered}) {
|
|
| 1139 |
## my @ordvalues = @values; |
|
| 1140 |
## my $ordwhere = $where . qq| AND o.quotation = '0'|; |
|
| 1141 |
## |
|
| 1142 |
## if ($form->{transdatefrom}) {
|
|
| 1143 |
## $ordwhere .= qq| AND o.transdate >= ?|; |
|
| 1144 |
## push(@ordvalues, $form->{transdatefrom});
|
|
| 1145 |
## } |
|
| 1146 |
## |
|
| 1147 |
## if ($form->{transdateto}) {
|
|
| 1148 |
## $ordwhere .= qq| AND o.transdate <= ?|; |
|
| 1149 |
## push(@ordvalues, $form->{transdateto});
|
|
| 1150 |
## } |
|
| 1151 |
## |
|
| 1152 |
## if ($form->{description}) {
|
|
| 1153 |
## $ordwhere .= qq| AND oi.description ILIKE ?|; |
|
| 1154 |
## push(@ordvalues, '%' . $form->{description} . '%');
|
|
| 1155 |
## } |
|
| 1156 |
## |
|
| 1157 |
## if ($form->{ordered}) {
|
|
| 1158 |
## $query .= |
|
| 1159 |
## qq|$union |
|
| 1160 |
## |
|
| 1161 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1162 |
## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1163 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1164 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1165 |
## pg.partsgroup, |
|
| 1166 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1167 |
## ct.name, NULL AS deliverydate, |
|
| 1168 |
## 'oe' AS module, 'sales_order' AS type, |
|
| 1169 |
## (SELECT buy FROM exchangerate ex |
|
| 1170 |
## WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate |
|
| 1171 |
## FROM orderitems oi |
|
| 1172 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1173 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1174 |
## JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1175 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1176 |
## WHERE $ordwhere AND (o.customer_id > 0)|; |
|
| 1177 |
## $union = qq| UNION |; |
|
| 1178 |
## |
|
| 1179 |
## push(@all_values, @ordvalues); |
|
| 1180 |
## } |
|
| 1181 |
## |
|
| 1182 |
## if ($form->{onorder}) {
|
|
| 1183 |
## $query .= |
|
| 1184 |
## qq|$union |
|
| 1185 |
## |
|
| 1186 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1187 |
## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1188 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1189 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1190 |
## pg.partsgroup, |
|
| 1191 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1192 |
## ct.name, NULL AS deliverydate, |
|
| 1193 |
## 'oe' AS module, 'purchase_order' AS type, |
|
| 1194 |
## (SELECT sell FROM exchangerate ex |
|
| 1195 |
## WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1196 |
## FROM orderitems oi |
|
| 1197 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1198 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1199 |
## JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1200 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1201 |
## WHERE $ordwhere AND (o.vendor_id > 0)|; |
|
| 1202 |
## $union = qq| UNION |; |
|
| 1203 |
## |
|
| 1204 |
## push(@all_values, @ordvalues); |
|
| 1205 |
## } |
|
| 1206 |
## |
|
| 1207 |
## } |
|
| 1208 |
## |
|
| 1209 |
## if ($form->{rfq} || $form->{quoted}) {
|
|
| 1210 |
## my $quowhere = $where . qq| AND o.quotation = '1'|; |
|
| 1211 |
## my @quovalues = @values; |
|
| 1212 |
## |
|
| 1213 |
## if ($form->{transdatefrom}) {
|
|
| 1214 |
## $quowhere .= qq| AND o.transdate >= ?|; |
|
| 1215 |
## push(@quovalues, $form->{transdatefrom});
|
|
| 1216 |
## } |
|
| 1217 |
## |
|
| 1218 |
## if ($form->{transdateto}) {
|
|
| 1219 |
## $quowhere .= qq| AND o.transdate <= ?|; |
|
| 1220 |
## push(@quovalues, $form->{transdateto});
|
|
| 1221 |
## } |
|
| 1222 |
## |
|
| 1223 |
## if ($form->{description}) {
|
|
| 1224 |
## $quowhere .= qq| AND oi.description ILIKE ?|; |
|
| 1225 |
## push(@quovalues, '%' . $form->{description} . '%');
|
|
| 1226 |
## } |
|
| 1227 |
## |
|
| 1228 |
## if ($form->{quoted}) {
|
|
| 1229 |
## $query .= |
|
| 1230 |
## qq|$union |
|
| 1231 |
## |
|
| 1232 |
## SELECT |
|
| 1233 |
## p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1234 |
## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1235 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1236 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1237 |
## pg.partsgroup, |
|
| 1238 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1239 |
## ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type, |
|
| 1240 |
## (SELECT buy FROM exchangerate ex |
|
| 1241 |
## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1242 |
## FROM orderitems oi |
|
| 1243 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1244 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1245 |
## JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1246 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1247 |
## WHERE $quowhere |
|
| 1248 |
## AND o.customer_id > 0|; |
|
| 1249 |
## $union = qq| UNION |; |
|
| 1250 |
## |
|
| 1251 |
## push(@all_values, @quovalues); |
|
| 1252 |
## } |
|
| 1253 |
## |
|
| 1254 |
## if ($form->{rfq}) {
|
|
| 1255 |
## $query .= |
|
| 1256 |
## qq|$union |
|
| 1257 |
## |
|
| 1258 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1259 |
## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1260 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1261 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1262 |
## pg.partsgroup, |
|
| 1263 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1264 |
## ct.name, NULL AS deliverydate, |
|
| 1265 |
## 'oe' AS module, 'request_quotation' AS type, |
|
| 1266 |
## (SELECT sell FROM exchangerate ex |
|
| 1267 |
## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1268 |
## FROM orderitems oi |
|
| 1269 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1270 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1271 |
## JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1272 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1273 |
## WHERE $quowhere |
|
| 1274 |
## AND o.vendor_id > 0|; |
|
| 1275 |
## |
|
| 1276 |
## push(@all_values, @quovalues); |
|
| 1277 |
## } |
|
| 1278 |
## |
|
| 1279 |
## } |
|
| 1280 |
## $query .= qq| ORDER BY | . $sortorder; |
|
| 1281 |
## |
|
| 1282 |
## } |
|
| 1283 |
## |
|
| 1284 |
## $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
|
|
| 1285 |
# |
|
| 1286 | 942 |
my @assemblies; |
| 1287 | 943 |
# include individual items for assemblies |
| 1288 | 944 |
if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
|
Auch abrufbar als: Unified diff
Alten, auskommentierten Code für die Artikelliste entfernt.