Revision 7942a6ac
Von Moritz Bunkus vor mehr als 15 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 997 | 997 |
$main::lxdebug->leave_sub(); |
| 998 | 998 |
} |
| 999 | 999 |
|
| 1000 |
sub update_prices {
|
|
| 1000 |
sub _create_filter_for_priceupdate {
|
|
| 1001 | 1001 |
$main::lxdebug->enter_sub(); |
| 1002 | 1002 |
|
| 1003 |
my ($self, $myconfig, $form) = @_; |
|
| 1003 |
my $self = shift; |
|
| 1004 |
my $myconfig = \%main::myconfig; |
|
| 1005 |
my $form = $main::form; |
|
| 1006 |
|
|
| 1004 | 1007 |
my @where_values; |
| 1005 | 1008 |
my $where = '1 = 1'; |
| 1006 |
my $var; |
|
| 1007 | 1009 |
|
| 1008 |
my $group; |
|
| 1009 |
my $limit; |
|
| 1010 |
foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
|
|
| 1011 |
my $column = $item; |
|
| 1012 |
$column =~ s/.*\.//; |
|
| 1013 |
next unless ($form->{$column});
|
|
| 1010 | 1014 |
|
| 1011 |
if ($item ne 'make') {
|
|
| 1012 |
foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
|
|
| 1013 |
my $column = $item; |
|
| 1014 |
$column =~ s/.*\.//; |
|
| 1015 |
next unless ($form->{$column});
|
|
| 1016 |
$where .= qq| AND $item ILIKE ?|; |
|
| 1017 |
push(@where_values, '%' . $form->{$column} . '%');
|
|
| 1018 |
} |
|
| 1015 |
$where .= qq| AND $item ILIKE ?|; |
|
| 1016 |
push(@where_values, '%' . $form->{$column} . '%');
|
|
| 1019 | 1017 |
} |
| 1020 | 1018 |
|
| 1021 |
# special case for description |
|
| 1022 |
if ($form->{description}
|
|
| 1023 |
&& !( $form->{bought} || $form->{sold} || $form->{onorder}
|
|
| 1024 |
|| $form->{ordered} || $form->{rfq} || $form->{quoted})) {
|
|
| 1025 |
$where .= qq| AND (p.description ILIKE ?)|; |
|
| 1026 |
push(@where_values, '%' . $form->{description} . '%');
|
|
| 1027 |
} |
|
| 1019 |
foreach my $item (qw(description serialnumber)) {
|
|
| 1020 |
next unless ($form->{$item});
|
|
| 1028 | 1021 |
|
| 1029 |
# special case for serialnumber |
|
| 1030 |
if ($form->{l_serialnumber} && $form->{serialnumber}) {
|
|
| 1031 |
$where .= qq| AND serialnumber ILIKE ?|; |
|
| 1032 |
push(@where_values, '%' . $form->{serialnumber} . '%');
|
|
| 1022 |
$where .= qq| AND (${item} ILIKE ?)|;
|
|
| 1023 |
push(@where_values, '%' . $form->{$item} . '%');
|
|
| 1033 | 1024 |
} |
| 1034 | 1025 |
|
| 1035 | 1026 |
|
| 1036 | 1027 |
# items which were never bought, sold or on an order |
| 1037 | 1028 |
if ($form->{itemstatus} eq 'orphaned') {
|
| 1038 |
$form->{onhand} = $form->{short} = 0;
|
|
| 1039 |
$form->{bought} = $form->{sold} = 0;
|
|
| 1040 |
$form->{onorder} = $form->{ordered} = 0;
|
|
| 1041 |
$form->{rfq} = $form->{quoted} = 0;
|
|
| 1042 |
|
|
| 1043 |
$form->{transdatefrom} = $form->{transdateto} = "";
|
|
| 1044 |
|
|
| 1045 | 1029 |
$where .= |
| 1046 | 1030 |
qq| AND (p.onhand = 0) |
| 1047 | 1031 |
AND p.id NOT IN |
| ... | ... | |
| 1052 | 1036 |
UNION |
| 1053 | 1037 |
SELECT DISTINCT parts_id FROM orderitems |
| 1054 | 1038 |
)|; |
| 1055 |
} |
|
| 1056 | 1039 |
|
| 1057 |
if ($form->{itemstatus} eq 'active') {
|
|
| 1040 |
} elsif ($form->{itemstatus} eq 'active') {
|
|
| 1058 | 1041 |
$where .= qq| AND p.obsolete = '0'|; |
| 1059 |
} |
|
| 1060 | 1042 |
|
| 1061 |
if ($form->{itemstatus} eq 'obsolete') {
|
|
| 1043 |
} elsif ($form->{itemstatus} eq 'obsolete') {
|
|
| 1062 | 1044 |
$where .= qq| AND p.obsolete = '1'|; |
| 1063 |
$form->{onhand} = $form->{short} = 0;
|
|
| 1064 |
} |
|
| 1065 | 1045 |
|
| 1066 |
if ($form->{itemstatus} eq 'onhand') {
|
|
| 1046 |
} elsif ($form->{itemstatus} eq 'onhand') {
|
|
| 1067 | 1047 |
$where .= qq| AND p.onhand > 0|; |
| 1068 |
} |
|
| 1069 | 1048 |
|
| 1070 |
if ($form->{itemstatus} eq 'short') {
|
|
| 1049 |
} elsif ($form->{itemstatus} eq 'short') {
|
|
| 1071 | 1050 |
$where .= qq| AND p.onhand < p.rop|; |
| 1051 |
|
|
| 1072 | 1052 |
} |
| 1073 | 1053 |
|
| 1074 | 1054 |
foreach my $column (qw(make model)) {
|
| ... | ... | |
| 1077 | 1057 |
push(@where_values, '%' . $form->{$column} . '%');
|
| 1078 | 1058 |
} |
| 1079 | 1059 |
|
| 1060 |
$main::lxdebug->leave_sub(); |
|
| 1061 |
|
|
| 1062 |
return ($where, @where_values); |
|
| 1063 |
} |
|
| 1064 |
|
|
| 1065 |
sub get_num_matches_for_priceupdate {
|
|
| 1066 |
$main::lxdebug->enter_sub(); |
|
| 1067 |
|
|
| 1068 |
my $self = shift; |
|
| 1069 |
|
|
| 1070 |
my $myconfig = \%main::myconfig; |
|
| 1071 |
my $form = $main::form; |
|
| 1072 |
|
|
| 1073 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 1074 |
|
|
| 1075 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate(); |
|
| 1076 |
|
|
| 1077 |
my $num_updated = 0; |
|
| 1078 |
my $query; |
|
| 1079 |
|
|
| 1080 |
for my $column (qw(sellprice listprice)) {
|
|
| 1081 |
next if ($form->{$column} eq "");
|
|
| 1082 |
|
|
| 1083 |
$query = |
|
| 1084 |
qq|SELECT COUNT(*) |
|
| 1085 |
FROM parts |
|
| 1086 |
WHERE id IN |
|
| 1087 |
(SELECT p.id |
|
| 1088 |
FROM parts p |
|
| 1089 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1090 |
WHERE $where)|; |
|
| 1091 |
my ($result) = selectfirst_array_query($from, $dbh, $query, @where_values); |
|
| 1092 |
$num_updated += $result if (0 <= $result); |
|
| 1093 |
} |
|
| 1094 |
|
|
| 1095 |
$query = |
|
| 1096 |
qq|SELECT COUNT(*) |
|
| 1097 |
FROM prices |
|
| 1098 |
WHERE parts_id IN |
|
| 1099 |
(SELECT p.id |
|
| 1100 |
FROM parts p |
|
| 1101 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1102 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 1103 |
my $sth = prepare_query($form, $dbh, $query); |
|
| 1104 |
|
|
| 1105 |
for my $i (1 .. $form->{price_rows}) {
|
|
| 1106 |
next if ($form->{"price_$i"} eq "");
|
|
| 1107 |
|
|
| 1108 |
my ($result) = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 1109 |
$num_updated += $result if (0 <= $result); |
|
| 1110 |
} |
|
| 1111 |
$sth->finish(); |
|
| 1112 |
|
|
| 1113 |
$main::lxdebug->leave_sub(); |
|
| 1114 |
|
|
| 1115 |
return $num_updated; |
|
| 1116 |
} |
|
| 1117 |
|
|
| 1118 |
sub update_prices {
|
|
| 1119 |
$main::lxdebug->enter_sub(); |
|
| 1120 |
|
|
| 1121 |
my ($self, $myconfig, $form) = @_; |
|
| 1122 |
|
|
| 1123 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate(); |
|
| 1124 |
my $num_updated = 0; |
|
| 1125 |
|
|
| 1080 | 1126 |
# connect to database |
| 1081 | 1127 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 1082 | 1128 |
|
| ... | ... | |
| 1098 | 1144 |
FROM parts p |
| 1099 | 1145 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
| 1100 | 1146 |
WHERE $where)|; |
| 1101 |
do_query($from, $dbh, $query, $value, @where_values); |
|
| 1147 |
my $result = do_query($from, $dbh, $query, $value, @where_values); |
|
| 1148 |
$num_updated += $result if (0 <= $result); |
|
| 1102 | 1149 |
} |
| 1103 | 1150 |
|
| 1104 | 1151 |
my $q_add = |
| ... | ... | |
| 1123 | 1170 |
next if ($form->{"price_$i"} eq "");
|
| 1124 | 1171 |
|
| 1125 | 1172 |
my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
|
| 1173 |
my $result; |
|
| 1126 | 1174 |
|
| 1127 | 1175 |
if ($form->{"pricegroup_type_$i"} eq "percent") {
|
| 1128 |
do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 1176 |
$result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 1129 | 1177 |
} else {
|
| 1130 |
do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 1178 |
$result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 1131 | 1179 |
} |
| 1180 |
|
|
| 1181 |
$num_updated += $result if (0 <= $result); |
|
| 1132 | 1182 |
} |
| 1133 | 1183 |
|
| 1134 | 1184 |
$sth_add->finish(); |
| ... | ... | |
| 1139 | 1189 |
|
| 1140 | 1190 |
$main::lxdebug->leave_sub(); |
| 1141 | 1191 |
|
| 1142 |
return $rc;
|
|
| 1192 |
return $num_updated;
|
|
| 1143 | 1193 |
} |
| 1144 | 1194 |
|
| 1145 | 1195 |
sub create_links {
|
Auch abrufbar als: Unified diff
Bessere Abfragen und (Fehler-)Meldungen bei Preisupdates.
Fix für Bugs 480.