+sub update_prices {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my $where = '1 = 1';
+ my $var;
+
+ my $group;
+ my $limit;
+
+ foreach my $item (qw(partnumber drawing microfiche make model)) {
+ if ($form->{$item}) {
+ $var = $form->like(lc $form->{$item});
+
+ # make will build later Bugfix 145
+ if ($item ne 'make') {
+ $where .= " AND lower(p.$item) LIKE '$var'";
+ }
+ }
+ }
+
+ # special case for description
+ if ($form->{description}) {
+ unless ( $form->{bought}
+ || $form->{sold}
+ || $form->{onorder}
+ || $form->{ordered}
+ || $form->{rfq}
+ || $form->{quoted}) {
+ $var = $form->like(lc $form->{description});
+ $where .= " AND lower(p.description) LIKE '$var'";
+ }
+ }
+
+ # special case for serialnumber
+ if ($form->{l_serialnumber}) {
+ if ($form->{serialnumber}) {
+ $var = $form->like(lc $form->{serialnumber});
+ $where .= " AND lower(serialnumber) LIKE '$var'";
+ }
+ }
+
+
+ # 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 .= " AND p.onhand = 0
+ AND p.id NOT IN (SELECT p.id FROM parts p, invoice i
+ WHERE p.id = i.parts_id)
+ AND p.id NOT IN (SELECT p.id FROM parts p, assembly a
+ WHERE p.id = a.parts_id)
+ AND p.id NOT IN (SELECT p.id FROM parts p, orderitems o
+ WHERE p.id = o.parts_id)";
+ }
+
+ if ($form->{itemstatus} eq 'active') {
+ $where .= " AND p.obsolete = '0'";
+ }
+ if ($form->{itemstatus} eq 'obsolete') {
+ $where .= " AND p.obsolete = '1'";
+ $form->{onhand} = $form->{short} = 0;
+ }
+ if ($form->{itemstatus} eq 'onhand') {
+ $where .= " AND p.onhand > 0";
+ }
+ if ($form->{itemstatus} eq 'short') {
+ $where .= " AND p.onhand < p.rop";
+ }
+ if ($form->{make}) {
+ $var = $form->like(lc $form->{make});
+ $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
+ FROM makemodel m WHERE lower(m.make) LIKE '$var')";
+ }
+ if ($form->{model}) {
+ $var = $form->like(lc $form->{model});
+ $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
+ FROM makemodel m WHERE lower(m.model) LIKE '$var')";
+ }
+ if ($form->{partsgroup}) {
+ $var = $form->like(lc $form->{partsgroup});
+ $where .= " AND lower(pg.partsgroup) LIKE '$var'";
+ }
+
+
+ # connect to database
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ if ($form->{"sellprice"} ne "") {
+ my $update = "";
+ my $faktor = $form->parse_amount($myconfig,$form->{"sellprice"});
+ if ($form->{"sellprice_type"} eq "percent") {
+ my $faktor = $form->parse_amount($myconfig,$form->{"sellprice"})/100 +1;
+ $update = "sellprice* $faktor";
+ } else {
+ $update = "sellprice+$faktor";
+ }
+
+ $query = qq|UPDATE parts set sellprice=$update WHERE id IN (SELECT p.id
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE $where)|;
+ $dbh->do($query);
+ }
+
+ if ($form->{"listprice"} ne "") {
+ my $update = "";
+ my $faktor = $form->parse_amount($myconfig,$form->{"listprice"});
+ if ($form->{"listprice_type"} eq "percent") {
+ my $faktor = $form->parse_amount($myconfig,$form->{"sellprice"})/100 +1;
+ $update = "listprice* $faktor";
+ } else {
+ $update = "listprice+$faktor";
+ }
+
+ $query = qq|UPDATE parts set listprice=$update WHERE id IN (SELECT p.id
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE $where)|;
+
+ $dbh->do($query);
+ }
+
+
+
+
+ for my $i (1 .. $form->{price_rows}) {
+
+ my $query = "";
+
+
+ if ($form->{"price_$i"} ne "") {
+ my $update = "";
+ my $faktor = $form->parse_amount($myconfig,$form->{"price_$i"});
+ if ($form->{"pricegroup_type_$i"} eq "percent") {
+ my $faktor = $form->parse_amount($myconfig,$form->{"sellprice"})/100 +1;
+ $update = "price* $faktor";
+ } else {
+ $update = "price+$faktor";
+ }
+
+ $query = qq|UPDATE prices set price=$update 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=$form->{"pricegroup_id_$i"}|;
+
+ $dbh->do($query);
+ }
+ }
+
+
+
+ my $rc= $dbh->commit;
+ $dbh->disconnect;
+ $main::lxdebug->leave_sub();
+
+ return $rc;
+}
+