+ $form->{parts} = \@assemblies;
+ }
+
+ $dbh->disconnect;
+ $main::lxdebug->leave_sub();
+}
+
+sub update_prices {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+ my @where_values;
+ my $where = '1 = 1';
+ my $var;
+
+ my $group;
+ my $limit;
+
+ my @where_values;
+
+ if ($item ne 'make') {
+ foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
+ my $column = $item;
+ $column =~ s/.*\.//;
+ next unless ($form->{$column});
+ $where .= qq| AND $item ILIKE ?|;
+ push(@where_values, '%' . $form->{$column} . '%');
+ }
+ }
+
+ # special case for description
+ if ($form->{description}
+ && !( $form->{bought} || $form->{sold} || $form->{onorder}
+ || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
+ $where .= qq| AND (p.description ILIKE ?)|;
+ push(@where_values, '%' . $form->{description} . '%');
+ }
+
+ # special case for serialnumber
+ if ($form->{l_serialnumber} && $form->{serialnumber}) {
+ $where .= qq| AND serialnumber ILIKE ?|;
+ push(@where_values, '%' . $form->{serialnumber} . '%');
+ }
+
+
+ # items which were never bought, sold or on an order
+ if ($form->{itemstatus} eq 'orphaned') {
+ $form->{onhand} = $form->{short} = 0;
+ $form->{bought} = $form->{sold} = 0;
+ $form->{onorder} = $form->{ordered} = 0;
+ $form->{rfq} = $form->{quoted} = 0;
+
+ $form->{transdatefrom} = $form->{transdateto} = "";
+
+ $where .=
+ qq| AND (p.onhand = 0)
+ AND p.id NOT IN
+ (
+ SELECT DISTINCT parts_id FROM invoice
+ UNION
+ SELECT DISTINCT parts_id FROM assembly
+ UNION
+ SELECT DISTINCT parts_id FROM orderitems
+ )|;
+ }
+
+ if ($form->{itemstatus} eq 'active') {
+ $where .= qq| AND p.obsolete = '0'|;
+ }
+
+ if ($form->{itemstatus} eq 'obsolete') {
+ $where .= qq| AND p.obsolete = '1'|;
+ $form->{onhand} = $form->{short} = 0;
+ }
+
+ if ($form->{itemstatus} eq 'onhand') {
+ $where .= qq| AND p.onhand > 0|;
+ }
+
+ if ($form->{itemstatus} eq 'short') {
+ $where .= qq| AND p.onhand < p.rop|;
+ }
+
+ foreach my $column (qw(make model)) {
+ next unless ($form->{$colum});
+ $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
+ push(@where_values, '%' . $form->{$column} . '%');
+ }
+
+ # connect to database
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ for my $column (qw(sellprice listprice)) {
+ next if ($form->{$column} eq "");
+
+ my $value = $form->parse_amount($myconfig, $form->{$column});
+ my $operator = '+';
+
+ if ($form->{"${column}_type"} eq "percent") {
+ $value = ($value / 100) + 1;
+ $operator = '*';
+ }
+
+ $query =
+ qq|UPDATE parts SET $column = $column $operator ?
+ WHERE id IN
+ (SELECT p.id
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE $where)|;
+ do_query($from, $dbh, $query, $value, @where_values);
+ }
+
+ my $q_add =
+ qq|UPDATE prices SET price = price + ?
+ WHERE parts_id IN
+ (SELECT p.id
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE $where) AND (pricegroup_id = ?)|;
+ my $sth_add = prepare_query($form, $dbh, $q_add);
+
+ my $q_multiply =
+ qq|UPDATE prices SET price = price * ?
+ WHERE parts_id IN
+ (SELECT p.id
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE $where) AND (pricegroup_id = ?)|;
+ my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
+
+ for my $i (1 .. $form->{price_rows}) {
+ next if ($form->{"price_$i"} eq "");
+
+ my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
+
+ if ($form->{"pricegroup_type_$i"} eq "percent") {
+ do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
+ } else {
+ do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
+ }