/SL/IC.pm - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 2a9ed0d5

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt

ID 2a9ed0d59a6ef4f3cfaf6893e6d534c0528a7eb5
Vorgänger 34dc226c
Nachfolger a731cd26

Umstellung von IC.pm auf die Verwendung von parametrisierten Queries und gequoteter Werte zur Vermeidung von SQL injections.

       # connect to db
       my $dbh = $form->dbconnect($myconfig);
       my $query = qq|SELECT p.*,
                      c1.accno AS inventory_accno,
     		 c2.accno AS income_accno,
     		 c3.accno AS expense_accno,
     		 pg.partsgroup
     	         FROM parts p
     		 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
     		 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
     		 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
     		 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                      WHERE p.id = ? |;
       my @vars = ($form->{id});
       my $sth = $dbh->prepare($query);
       $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
       my $ref = $sth->fetchrow_hashref(NAME_lc);
       my $sth;
       my $query =
         qq|SELECT p.*,
              c1.accno AS inventory_accno,
              c2.accno AS income_accno,
              c3.accno AS expense_accno,
              pg.partsgroup
            FROM parts p
            LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
            LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
            LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
            WHERE p.id = ? |;
       my $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
       # copy to $form variables
       map { $form->{$_} = $ref->{$_} } (keys %{$ref});
       $sth->finish;
       my %oid = ('Pg'     => 'a.oid',
                  'Oracle' => 'a.rowid');
-...
         $form->{item} = 'assembly';
         # retrieve assembly items
         $query = qq|SELECT p.id, p.partnumber, p.description,
                     p.sellprice, p.weight, a.qty, a.bom, p.unit,
     		pg.partsgroup
                     FROM parts p
     		JOIN assembly a ON (a.parts_id = p.id)
     		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		WHERE a.id = ?
     		ORDER BY ?|;
         @vars = ($form->{id}, $oid{$myconfig->{dbdriver}});
         $sth = $dbh->prepare($query);
         $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
         $query =
           qq|SELECT p.id, p.partnumber, p.description,
                p.sellprice, p.weight, a.qty, a.bom, p.unit,
                pg.partsgroup
              FROM parts p
              JOIN assembly a ON (a.parts_id = p.id)
              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
              WHERE (a.id = ?)
              ORDER BY $oid{$myconfig->{dbdriver}}|;
         $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
         $form->{assembly_rows} = 0;
         while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
           $form->{assembly_rows}++;
-...
       $form->{amount}{IC_expense} = $form->{expense_accno};
       $form->{amount}{IC_cogs}    = $form->{expense_accno};
       my @pricegroups          = ();
       my @pricegroups_not_used = ();
       # get prices
       $query =
         qq|SELECT p.parts_id, p.pricegroup_id, p.price, (SELECT pg.pricegroup FROM pricegroup pg WHERE pg.id=p.pricegroup_id) AS pricegroup FROM prices p
                   WHERE parts_id = ?
                   ORDER by pricegroup|;
       @vars = ($form->{id});
       $sth = $dbh->prepare($query);
       $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
       @pricegroups          = ();
       @pricegroups_not_used = ();
         qq|SELECT p.parts_id, p.pricegroup_id, p.price,
              (SELECT pg.pricegroup
               FROM pricegroup pg
               WHERE pg.id = p.pricegroup_id) AS pricegroup
            FROM prices p
            WHERE (parts_id = ?)
            ORDER BY pricegroup|;
       $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
       #for pricegroups
       my $i = 1;
       while (
              ($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
       while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
               $form->{"price_$i"}, $form->{"pricegroup_$i"})
              = $sth->fetchrow_array
         ) {
              = $sth->fetchrow_array()) {
         $form->{"price_$i"} = $form->round_amount($form->{"price_$i"}, 5);
         $form->{"price_$i"} =
           $form->format_amount($myconfig, $form->{"price_$i"}, 5);
         $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
         push @pricegroups, $form->{"pricegroup_id_$i"};
         $i++;
+      }
-...
       $sth->finish;
       # get pricegroups
       $query = qq|SELECT p.id, p.pricegroup FROM pricegroup p|;
       $pkq = $dbh->prepare($query);
       $pkq->execute || $form->dberror($query);
       while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{PRICEGROUPS} }, $pkr;
+      }
       $pkq->finish;
       $query = qq|SELECT id, pricegroup FROM pricegroup|;
       $form->{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query);
       #find not used pricegroups
       while ($tmp = pop @{ $form->{PRICEGROUPS} }) {
         my $insert = 0;
         foreach $item (@pricegroups) {
       while ($tmp = pop(@{ $form->{PRICEGROUPS} })) {
         my $in_use = 0;
         foreach my $item (@pricegroups) {
           if ($item eq $tmp->{id}) {
             #drop
             $insert = 1;
             $in_use = 1;
             last;
+          }
+        }
         if ($insert == 0) {
           push @pricegroups_not_used, $tmp;
+        }
         push(@pricegroups_not_used, $tmp) unless ($in_use);
+      }
       # if not used pricegroups are avaible
-...
         foreach $name (@pricegroups_not_used) {
           $form->{"klass_$i"} = "$name->{id}";
           $form->{"price_$i"} = $form->round_amount($form->{sellprice}, 5);
           $form->{"price_$i"} =
             $form->format_amount($myconfig, $form->{"price_$i"}, 5);
           $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
           $form->{"pricegroup_id_$i"} = "$name->{id}";
           $form->{"pricegroup_$i"}    = "$name->{pricegroup}";
           $i++;
-...
         # get makes
         if ($form->{makemodel}) {
           $query = qq|SELECT m.make, m.model FROM makemodel m
                       WHERE m.parts_id = ?|;
           @vars = ($form->{id});
           $query = qq|SELECT m.make, m.model FROM makemodel m | .
                    qq|WHERE m.parts_id = ?|;
           @values = ($form->{id});
           $sth = $dbh->prepare($query);
           $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
           $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
           my $i = 1;
           while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
-...
       # get translations
       $form->{language_values} = "";
       $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
       @vars = ($form->{id});
       $trq = $dbh->prepare($query);
       $trq->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
       my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
       while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
         $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
+      }
       $trq->finish;
       # now get accno for taxes
       $query = qq|SELECT c.accno
                   FROM chart c, partstax pt
     	      WHERE pt.chart_id = c.id
     	      AND pt.parts_id = $form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $query =
         qq|SELECT c.accno
            FROM chart c, partstax pt
            WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|;
       $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
       while (($key) = $sth->fetchrow_array) {
         $form->{amount}{$key} = $key;
+      }
-...
       $sth->finish;
       # is it an orphan
       $query = qq|SELECT i.parts_id
                   FROM invoice i
     	      WHERE i.parts_id = $form->{id}
     	    UNION
     	      SELECT o.parts_id
     	      FROM orderitems o
     	      WHERE o.parts_id = $form->{id}
     	    UNION
     	      SELECT a.parts_id
     	      FROM assembly a
     	      WHERE a.parts_id = $form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       ($form->{orphaned}) = $sth->fetchrow_array;
       $query =
         qq|SELECT i.parts_id
            FROM invoice i
            WHERE (i.parts_id = ?)
            UNION
            SELECT o.parts_id
            FROM orderitems o
            WHERE (o.parts_id = ?)
            UNION
            SELECT a.parts_id
            FROM assembly a
            WHERE (a.parts_id = ?)|;
       @values = (conv_i($form->{id}), conv_i($form->{id}), conv_i($form->{id}));
       ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
       $form->{orphaned} = !$form->{orphaned};
       $sth->finish;
       $form->{"unit_changeable"} = 1;
       foreach my $table (qw(invoice assembly orderitems inventory license)) {
         $query = "SELECT COUNT(*) FROM $table WHERE parts_id = ?";
         my ($count) = $dbh->selectrow_array($query, undef, $form->{"id"});
         $form->dberror($query . " (" . $form->{"id"} . ")") if ($dbh->err);
         $query = qq|SELECT COUNT(*) FROM $table WHERE parts_id = ?|;
         my ($count) = selectrow_query($form, $dbh, $query, conv_i($form->{"id"}));
         if ($count) {
           $form->{"unit_changeable"} = 0;
-...
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my $dbh                  = $form->dbconnect($myconfig);
       my $i                    = 1;
       my @pricegroups_not_used = ();
       # get pricegroups
       my $query = qq|SELECT p.id, p.pricegroup FROM pricegroup p|;
       my $pkq = $dbh->prepare($query);
       $pkq->execute || $form->dberror($query);
       while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{PRICEGROUPS} }, $pkr;
+      }
       $pkq->finish;
       #find not used pricegroups
       while ($tmp = pop @{ $form->{PRICEGROUPS} }) {
         push @pricegroups_not_used, $tmp;
+      }
       my $dbh = $form->dbconnect($myconfig);
       # if not used pricegroups are avaible
       if (@pricegroups_not_used) {
       # get pricegroups
       my $query = qq|SELECT id, pricegroup FROM pricegroup|;
       my $pricegroups = selectall_hashref_query($form, $dbh, $query);
         foreach $name (@pricegroups_not_used) {
           $form->{"klass_$i"} = "$name->{id}";
           $form->{"price_$i"} = $form->round_amount($form->{sellprice}, 5);
           $form->{"price_$i"} =
             $form->format_amount($myconfig, $form->{"price_$i"}, 5);
           $form->{"pricegroup_id_$i"} = "$name->{id}";
           $form->{"pricegroup_$i"}    = "$name->{pricegroup}";
           $i++;
+        }
       my $i = 1;
       foreach $pg (@{ $pricegroups }) {
         $form->{"klass_$i"} = "$pg->{id}";
         $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
         $form->{"pricegroup_id_$i"} = "$pg->{id}";
         $form->{"pricegroup_$i"}    = "$pg->{pricegroup}";
         $i++;
+      }
       #correct rows
-...
       my $dbh = $form->dbconnect($myconfig);
       # get buchungsgruppen
       $query = qq|SELECT id, description
                   FROM buchungsgruppen
                   ORDER BY sortkey|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $form->{BUCHUNGSGRUPPEN} = [];
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push(@{ $form->{BUCHUNGSGRUPPEN} }, $ref);
+      }
       $sth->finish;
       $query = qq|SELECT id, description FROM buchungsgruppen ORDER BY sortkey|;
       $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, $query);
       $main::lxdebug->leave_sub();
+    }
-...
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my @values;
       # connect to database, turn off AutoCommit
       my $dbh = $form->dbconnect_noauto($myconfig);
-...
       # if there is a $form->{id} then replace the old entry
       # delete all makemodel entries and add the new ones
       # escape '
       map { $form->{$_} =~ s/\'/\'\'/g } qw(partnumber description notes unit);
       # undo amount formatting
       map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
         qw(rop weight listprice sellprice gv lastcost stock);
       # set date to NULL if nothing entered
       $form->{priceupdate} =
         ($form->{priceupdate}) ? qq|'$form->{priceupdate}'| : "NULL";
       $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
       my $makemodel = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
       $form->{alternate} = 0;
       $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
       $form->{obsolete} *= 1;
       $form->{shop}     *= 1;
       $form->{onhand}   *= 1;
       $form->{ve}       *= 1;
       $form->{ge}       *= 1;
       $form->{buchungsgruppen_id}       *= 1;
       $form->{not_discountable}       *= 1;
       $form->{payment_id}       *= 1;
       my ($query, $sth);
       if ($form->{id}) {
         # get old price
         $query = qq|SELECT p.sellprice, p.weight
                     FROM parts p
     		WHERE p.id = $form->{id}|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         my ($sellprice, $weight) = $sth->fetchrow_array;
         $sth->finish;
         $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
         my ($sellprice, $weight) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
         # if item is part of an assembly adjust all assemblies
         $query = qq|SELECT a.id, a.qty
                     FROM assembly a
     		WHERE a.parts_id = $form->{id}|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
         $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
         while (my ($id, $qty) = $sth->fetchrow_array) {
           &update_assembly($dbh, $form, $id, $qty, $sellprice * 1, $weight * 1);
+        }
         $sth->finish;
         if ($form->{item} ne 'service') {
           # delete makemodel records
           $query = qq|DELETE FROM makemodel
     		  WHERE parts_id = $form->{id}|;
           $dbh->do($query) || $form->dberror($query);
           do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
+        }
         if ($form->{item} eq 'assembly') {
-...
+          }
           # delete assembly records
           $query = qq|DELETE FROM assembly
     		  WHERE id = $form->{id}|;
           $dbh->do($query) || $form->dberror($query);
           do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
           $form->{onhand} += $form->{stock};
+        }
         # delete tax records
         $query = qq|DELETE FROM partstax
     		WHERE parts_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
         do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id}));
         # delete translations
         $query = qq|DELETE FROM translation
     		WHERE parts_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
         do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
       } else {
         my $uid = rand() . time;
         $uid .= $form->{login};
         $query = qq|SELECT p.id FROM parts p
                     WHERE p.partnumber = '$form->{partnumber}'|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{id}) = $sth->fetchrow_array;
         $sth->finish;
         if ($form->{id} ne "") {
         my ($count) = selectrow_array($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
         if ($count) {
           $main::lxdebug->leave_sub();
           return 3;
+        }
         $query = qq|INSERT INTO parts (partnumber, description)
                     VALUES ('$uid', 'dummy')|;
         $dbh->do($query) || $form->dberror($query);
         $query = qq|SELECT p.id FROM parts p
                     WHERE p.partnumber = '$uid'|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{id}) = $sth->fetchrow_array;
         $sth->finish;
         ($form->{id}) = selectrow_array($form, $dbh, qq|SELECT nextval('id')|);
         do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id});
         $form->{orphaned} = 1;
         $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
-...
       my $partsgroup_id = 0;
       if ($form->{partsgroup}) {
         ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
         ($partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
+      }
       my ($subq_inventory, $subq_expense, $subq_income);
       if ($form->{"item"} eq "part") {
         $subq_inventory =
           qq|(SELECT bg.inventory_accno_id | .
           qq| FROM buchungsgruppen bg | .
           qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
           qq|(SELECT bg.inventory_accno_id
               FROM buchungsgruppen bg
               WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
       } else {
         $subq_inventory = "NULL";
+      }
       if ($form->{"item"} ne "assembly") {
         $subq_expense =
           qq|(SELECT bg.expense_accno_id_0 | .
           qq| FROM buchungsgruppen bg | .
           qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
           qq|(SELECT bg.expense_accno_id_0
               FROM buchungsgruppen bg
               WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
       } else {
         $subq_expense = "NULL";
+      }
       $subq_income =
         qq|(SELECT bg.income_accno_id_0 | .
         qq| FROM buchungsgruppen bg | .
         qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
       $query = qq|UPDATE parts SET
     	      partnumber = '$form->{partnumber}',
     	      description = '$form->{description}',
     	      makemodel = '$form->{makemodel}',
     	      alternate = '$form->{alternate}',
     	      assembly = '$form->{assembly}',
     	      listprice = $form->{listprice},
     	      sellprice = $form->{sellprice},
     	      lastcost = $form->{lastcost},
     	      weight = $form->{weight},
     	      priceupdate = $form->{priceupdate},
     	      unit = '$form->{unit}',
     	      notes = '$form->{notes}',
     	      formel = '$form->{formel}',
     	      rop = $form->{rop},
     	      bin = '$form->{bin}',
     	      buchungsgruppen_id = '$form->{buchungsgruppen_id}',
     	      payment_id = '$form->{payment_id}',
     	      inventory_accno_id = $subq_inventory,
     	      income_accno_id = $subq_income,
     	      expense_accno_id = $subq_expense,
                   obsolete = '$form->{obsolete}',
     	      image = '$form->{image}',
     	      drawing = '$form->{drawing}',
     	      shop = '$form->{shop}',
                   ve = '$form->{ve}',
                   gv = '$form->{gv}',
                   ean = '$form->{ean}',
                   not_discountable = '$form->{not_discountable}',
     	      microfiche = '$form->{microfiche}',
     	      partsgroup_id = $partsgroup_id
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       $query =
         qq|UPDATE parts SET
              partnumber = ?,
              description = ?,
              makemodel = ?,
              alternate = 'f',
              assembly = ?,
              listprice = ?,
              sellprice = ?,
              lastcost = ?,
              weight = ?,
              priceupdate = ?,
              unit = ?,
              notes = ?,
              formel = ?,
              rop = ?,
              bin = ?,
              buchungsgruppen_id = ?,
              payment_id = ?,
              inventory_accno_id = $subq_inventory,
              income_accno_id = (SELECT bg.income_accno_id_0 FROM buchungsgruppen bg WHERE bg.id = ?),
              expense_accno_id = $subq_expense,
              obsolete = ?,
              image = ?,
              drawing = ?,
              shop = ?,
              ve = ?,
              gv = ?,
              ean = ?,
              not_discountable = ?,
              microfiche = ?,
              partsgroup_id = ?
            WHERE id = ?|;
       @values = ($form->{partnumber},
                  $form->{description},
                  $makemodel ? 't' : 'f',
                  $form->{assembly} ? 't' : 'f',
                  $form->{listprice},
                  $form->{sellprice},
                  $form->{lastcost},
                  $form->{weight},
                  conv_date($form->{priceupdate}),
                  $form->{unit},
                  $form->{notes},
                  $form->{formel},
                  $form->{rop},
                  $form->{bin},
                  conv_i($form->{buchungsgruppen_id}),
                  conv_i($form->{payment_id}),
                  conv_i($form->{buchungsgruppen_id}),
                  $form->{obsolete} ? 't' : 'f',
                  $form->{image},
                  $form->{drawing},
                  $form->{shop} ? 't' : 'f',
                  conv_i($form->{ve}),
                  conv_i($form->{gv}),
                  $form->{ean},
                  $form->{not_discountable} ? 't' : 'f',
                  $form->{microfiche},
                  conv_i($partsgroup_id),
                  conv_i($form->{id})
       );
       do_query($form, $dbh, $query, @values);
       # delete translation records
       $query = qq|DELETE FROM translation
                   WHERE parts_id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
       if ($form->{language_values} ne "") {
         split /---\+\+\+---/,$form->{language_values};
         foreach $item (@_) {
           my ($language_id, $translation, $longdescription) = split /--\+\+--/, $item;
         foreach $item (split(/---\+\+\+---/, $form->{language_values})) {
           my ($language_id, $translation, $longdescription) = split(/--\+\+--/, $item);
           if ($translation ne "") {
             $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription) VALUES
                         ($form->{id}, $language_id, | . $dbh->quote($translation) . qq|, | . $dbh->quote($longdescription) . qq| )|;
             $dbh->do($query) || $form->dberror($query);
             $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription)
                         VALUES ( ?, ?, ?, ? )|;
             @values = (conv_i($form->{id}), conv_i($language_id), $translation, $longdescription);
             do_query($form, $dbh, $query, @values);
+          }
+        }
+      }
       # delete price records
       $query = qq|DELETE FROM prices
                   WHERE parts_id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id}));
       # insert price records only if different to sellprice
       for my $i (1 .. $form->{price_rows}) {
         if ($form->{"price_$i"} eq "0") {
-...
              || $form->{"pricegroup_id_$i"})
             and $form->{"price_$i"} != $form->{sellprice}
           ) {
           $klass = $form->parse_amount($myconfig, $form->{"klass_$i"});
           #$klass = $form->parse_amount($myconfig, $form->{"klass_$i"});
           $price = $form->parse_amount($myconfig, $form->{"price_$i"});
           $pricegroup_id =
             $form->parse_amount($myconfig, $form->{"pricegroup_id_$i"});
           $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price)
                       VALUES($form->{id},$pricegroup_id,$price)|;
           $dbh->do($query) || $form->dberror($query);
           $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) | .
                    qq|VALUES(?, ?, ?)|;
           @values = (conv_i($form->{id}), conv_i($pricegroup_id), $price);
           do_query($form, $dbh, $query, @values);
+        }
+      }
-...
           if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
             map { $form->{"${_}_$i"} =~ s/\'/\'\'/g } qw(make model);
             $query = qq|INSERT INTO makemodel (parts_id, make, model)
     		    VALUES ($form->{id},
     		    '$form->{"make_$i"}', '$form->{"model_$i"}')|;
             $dbh->do($query) || $form->dberror($query);
             $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
     		             qq|VALUES (?, ?, ?)|;
     		    @values = (conv_i($form->{id}), $form->{"make_$i"}, $form->{"model_$i"});
             do_query($form, $dbh, $query, @values);
+          }
+        }
+      }
       # insert taxes
       foreach $item (split / /, $form->{taxaccounts}) {
       foreach $item (split(/ /, $form->{taxaccounts})) {
         if ($form->{"IC_tax_$item"}) {
           $query = qq|INSERT INTO partstax (parts_id, chart_id)
                       VALUES ($form->{id},
     		          (SELECT c.id
     			   FROM chart c
     			   WHERE c.accno = '$item'))|;
           $dbh->do($query) || $form->dberror($query);
           $query =
             qq|INSERT INTO partstax (parts_id, chart_id)
                VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
     			@values = (conv_i($form->{id}), $item);
           do_query($form, $dbh, $query, @values);
+        }
+      }
-...
           if ($form->{"qty_$i"} != 0) {
             $form->{"bom_$i"} *= 1;
             $query = qq|INSERT INTO assembly (id, parts_id, qty, bom)
     		    VALUES ($form->{id}, $form->{"id_$i"},
     		    $form->{"qty_$i"}, '$form->{"bom_$i"}')|;
             $dbh->do($query) || $form->dberror($query);
             $query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
     		             qq|VALUES (?, ?, ?, ?)|;
     		    @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
             do_query($form, $dbh, $query, @values);
+          }
+        }
-...
         $form->get_employee($dbh);
         # add inventory record
         $query = qq|INSERT INTO inventory (warehouse_id, parts_id, qty,
                     shippingdate, employee_id) VALUES (
 , $form->{id}, $form->{stock}, '$shippingdate',
     		$form->{employee_id})|;
         $dbh->do($query) || $form->dberror($query);
         $query =
           qq|INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id)
              VALUES (0, ?, ?, '$shippingdate', ?)|;
         @values = (conv_i($form->{id}), $form->{stock}, conv_i($form->{employee_id}));
         do_query($form, $dbh, $query, @values);
+      }
-...
       # get tax rates and description
       $accno_id =
         ($form->{vc} eq "customer") ? $form->{income_accno} : $vendor_accno;
       $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
     	      FROM chart c, tax t
     	      WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
     	      ORDER BY c.accno|;
       $stw = $dbh->prepare($query);
       $stw->execute || $form->dberror($query);
       $query =
         qq|SELECT c.accno, c.description, t.rate, t.taxnumber
            FROM chart c, tax t
            WHERE (c.id = t.chart_id) AND (t.taxkey IN (SELECT taxkey_id FROM chart where accno = ?))
            ORDER BY c.accno|;
       $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
       $form->{taxaccount} = "";
       while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
         #    if ($customertax{$ref->{accno}}) {
         $form->{taxaccount} .= "$ptr->{accno} ";
         if (!($form->{taxaccount2} =~ /$ptr->{accno}/)) {
           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
-...
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
           $form->{taxaccount2} .= " $ptr->{accno} ";
+        }
+      }
       # commit
-...
       my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
       my $query = qq|SELECT a.id, a.qty
                      FROM assembly a
     		 WHERE a.parts_id = $id|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
       my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
       while (my ($pid, $aqty) = $sth->fetchrow_array) {
         &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
+      }
       $sth->finish;
       $query = qq|UPDATE parts
                   SET sellprice = sellprice +
     	          $qty * ($form->{sellprice} - $sellprice),
                       weight = weight +
     		  $qty * ($form->{weight} - $weight)
     	      WHERE id = $id|;
       $dbh->do($query) || $form->dberror($query);
       $query =
         qq|UPDATE parts SET sellprice = sellprice + ?, weight = weight + ?
            WHERE id = ?|;
       @values = ($qty * ($form->{sellprice} - $sellprice),
                  $qty * ($form->{weight} - $weight), conv_i($id));
       do_query($form, $dbh, $query, @values);
       $main::lxdebug->leave_sub();
+    }
-...
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $where = '1 = 1';
       my $where = qq|NOT p.obsolete|;
       my @values;
       if ($form->{partnumber}) {
         my $partnumber = $form->like(lc $form->{partnumber});
         $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
         $where .= qq| AND (p.partnumber ILIKE ?)|;
         push(@values, '%' . $form->{partnumber} . '%');
+      }
       if ($form->{description}) {
         my $description = $form->like(lc $form->{description});
         $where .= " AND lower(p.description) LIKE '$description'";
         $where .= qq| AND (p.description ILIKE ?)|;
         push(@values, '%' . $form->{description} . '%');
+      }
       $where .= " AND NOT p.obsolete = '1'";
       # retrieve assembly items
       my $query = qq|SELECT p.id, p.partnumber, p.description,
                      p.bin, p.onhand, p.rop,
     		   (SELECT sum(p2.inventory_accno_id)
     		    FROM parts p2, assembly a
     		    WHERE p2.id = a.parts_id
     		    AND a.id = p.id) AS inventory
                      FROM parts p
      		 WHERE $where
     		 AND assembly = '1'|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $query =
         qq|SELECT p.id, p.partnumber, p.description,
              p.bin, p.onhand, p.rop,
              (SELECT sum(p2.inventory_accno_id)
               FROM parts p2, assembly a
               WHERE (p2.id = a.parts_id) AND (a.id = p.id)) AS inventory
            FROM parts p
            WHERE NOT p.obsolete AND p.assembly $where|;
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{assembly_items} }, $ref if $ref->{inventory};
+      }
       $sth->finish;
       $form->{assembly_items} = selectall_hashref_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my ($dbh, $form, $id, $qty) = @_;
       my $query = qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
     		 FROM parts p, assembly a
     		 WHERE a.parts_id = p.id
     		 AND a.id = $id|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $query =
         qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
            FROM parts p, assembly a
            WHERE (a.parts_id = p.id) AND (a.id = ?)|;
       my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-...
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my @values = (conv_i($form->{id}));
       # connect to database, turn off AutoCommit
       my $dbh = $form->dbconnect_noauto($myconfig);
       # first delete prices of pricegroup
       my $query = qq|DELETE FROM prices
                WHERE parts_id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       my $query = qq|DELETE FROM parts
      	         WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       $query = qq|DELETE FROM partstax
     	      WHERE parts_id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       # check if it is a part, assembly or service
       if ($form->{item} ne 'service') {
         $query = qq|DELETE FROM makemodel
     		WHERE parts_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
+      }
       if ($form->{item} eq 'assembly') {
         # delete inventory
         $query = qq|DELETE FROM inventory
                     WHERE parts_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
       my %columns = ( "assembly" => "id", "alternate" => "id", "parts" => "id" );
         $query = qq|DELETE FROM assembly
     		WHERE id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
+      }
       if ($form->{item} eq 'alternate') {
         $query = qq|DELETE FROM alternate
     		WHERE id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
       for my $table (qw(prices partstax makemodel inventory assembly parts)) {
         my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
         do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
+      }
       # commit
-...
       my $i = $form->{assembly_rows};
       my $var;
       my $where = "1 = 1";
       my $where = qq|1 = 1|;
       my @values;
       if ($form->{"partnumber_$i"}) {
         $var = $form->like(lc $form->{"partnumber_$i"});
         $where .= " AND lower(p.partnumber) LIKE '$var'";
+      }
       if ($form->{"description_$i"}) {
         $var = $form->like(lc $form->{"description_$i"});
         $where .= " AND lower(p.description) LIKE '$var'";
+      }
       if ($form->{"partsgroup_$i"}) {
         $var = $form->like(lc $form->{"partsgroup_$i"});
         $where .= " AND lower(pg.partsgroup) LIKE '$var'";
       my %columns = ("partnumber" => "p", "description" => "p", "partsgroup" => "pg");
       while (my ($column, $table) = each(%columns)) {
         next unless ($form->{"${column}_$i"});
         $where .= qq| AND ${table}.${column} ILIKE ?|;
         push(@values, '%' . $form->{"${column}_$i"} . '%');
+      }
       if ($form->{id}) {
         $where .= " AND NOT p.id = $form->{id}";
         $where .= qq| AND NOT (p.id = ?)|;
         push(@values, conv_i($form->{id}));
+      }
       if ($partnumber) {
         $where .= " ORDER BY p.partnumber";
         $where .= qq| ORDER BY p.partnumber|;
       } else {
         $where .= " ORDER BY p.description";
         $where .= qq| ORDER BY p.description|;
+      }
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
                      p.weight, p.onhand, p.unit,
     		 pg.partsgroup
     		 FROM parts p
     		 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		 WHERE $where|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{item_list} }, $ref;
+      }
       my $query =
         qq|SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, p.onhand, p.unit, pg.partsgroup
            FROM parts p
            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
            WHERE $where|;
       $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
       $sth->finish;
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
-...
       my ($self, $myconfig, $form) = @_;
       my $where = '1 = 1';
       my $var;
       my $group;
       my $limit;
       my $where = qq|1 = 1|;
       my (@values, $var, $flds, $group, $limit);
       foreach my $item (qw(partnumber drawing microfiche)) {
         if ($form->{$item}) {
           $var = $form->like(lc $form->{$item});
           $where .= " AND lower(p.$item) LIKE '$var'";
       foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
         my $column = $item;
         $column =~ s/.*\.//;
         if ($form->{$column}) {
           $where .= qq| AND (${item} ILIKE ?)|;
           push(@values, '%' . $form->{$column} . '%');
+        }
+      }
       # 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'";
+        }
       if ($form->{description}
           && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
                || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
         $where .= qq| AND (p.description ILIKE ?)|;
         push(@values, '%' . $form->{description} . '%');
+      }
       # special case for serialnumber
       if ($form->{l_serialnumber}) {
         if ($form->{serialnumber}) {
           $var = $form->like(lc $form->{serialnumber});
           $where .= " AND lower(serialnumber) LIKE '$var'";
+        }
       if ($form->{l_serialnumber} && $form->{serialnumber}) {
         $where .= qq| AND (p.serialnumber ILIKE ?)|;
         push(@values, '%' . $form->{serialnumber} . '%');
+      }
       if ($form->{ean}) {
         $var = $form->like(lc $form->{ean});
         $where .= " AND lower(ean) LIKE '$var'";
+      }
       if ($form->{searchitems} eq 'part') {
         $where .= " AND p.inventory_accno_id > 0";
         $where .= qq| AND (p.inventory_accno_id > 0) |;
+      }
       if ($form->{searchitems} eq 'assembly') {
         $form->{bought} = "";
         $where .= " AND p.assembly = '1'";
         $where .= qq| AND p.assembly|;
+      }
       if ($form->{searchitems} eq 'service') {
         $where .= " AND p.inventory_accno_id IS NULL AND NOT p.assembly = '1'";
         $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|;
         # irrelevant for services
         $form->{make} = $form->{model} = "";
-...
         $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)";
         $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 .= " AND p.obsolete = '0'";
+      }
       if ($form->{itemstatus} eq 'obsolete') {
         $where .= " AND p.obsolete = '1'";
         $where .= qq| AND (p.obsolete = '0')|;
       } elsif ($form->{itemstatus} eq 'obsolete') {
         $where .= qq| AND (p.obsolete = '1')|;
         $form->{onhand} = $form->{short} = 0;
       } elsif ($form->{itemstatus} eq 'onhand') {
         $where .= qq| AND (p.onhand > 0)|;
       } elsif ($form->{itemstatus} eq 'short') {
         $where .= qq| AND (p.onhand < p.rop)|;
+      }
       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'";
       foreach my $column (qw(make model)) {
         next unless ($form->{$column});
         $where .= qq| AND p.id IN (SELECT DISTINCT m.parts_id FROM makemodel WHERE $column ILIKE ?)|;
         push(@values, '%' . $form->{$column} . '%');
+      }
       if ($form->{l_soldtotal}) {
         $where .= " AND p.id=i.parts_id AND  i.qty >= 0";
         $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|;
         $group =
           " GROUP BY  p.id,p.partnumber,p.description,p.onhand,p.unit,p.bin, p.sellprice,p.listprice,p.lastcost,p.priceupdate,pg.partsgroup";
+      }
       if ($form->{top100}) {
         $limit = " LIMIT 100";
           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|;
+      }
       # tables revers?
       if ($form->{revers} == 1) {
         $form->{desc} = " DESC";
       } else {
         $form->{desc} = "";
+      }
       $limit = qq| LIMIT 100| if ($form->{top100});
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $sortorder = $form->{sort};
       $sortorder .= $form->{desc};
       $sortorder = $form->{sort} if $form->{sort};
       my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand
                          invnumber ordnumber quonumber name drawing microfiche
                          serialnumber soldtotal deliverydate);
       my $sortorder = "partnumber";
       $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
       $sortorder .= " DESC" if ($form->{revers});
       my $query = "";
       if ($form->{l_soldtotal}) {
         $form->{soldtotal} = 'soldtotal';
         $query =
           qq|SELECT p.id,p.partnumber,p.description,p.onhand,p.unit,p.bin,p.sellprice,p.listprice,
     		p.lastcost,p.priceupdate,pg.partsgroup,sum(i.qty) as soldtotal FROM parts
     		p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
     		WHERE $where
     		$group
     		ORDER BY $sortorder
     		$limit|;
           qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice,
                p.lastcost, p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal FROM parts
                p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
                WHERE $where
                $group
                ORDER BY $sortorder
                $limit|;
       } else {
         $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
                      p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
     		 p.priceupdate, p.image, p.drawing, p.microfiche,
     		 pg.partsgroup
                      FROM parts p
     		 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
       	         WHERE $where
     		 $group
     	         ORDER BY $sortorder|;
         $query =
           qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
                p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
                p.priceupdate, p.image, p.drawing, p.microfiche,
                pg.partsgroup
              FROM parts p
              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
              WHERE $where
              $group
              ORDER BY $sortorder
              $limit|;
+      }
       my @all_values = @values;
       # rebuild query for bought and sold items
       if (   $form->{bought}
           || $form->{sold}
-...
           || $form->{ordered}
           || $form->{rfq}
           || $form->{quoted}) {
         my @a = qw(partnumber description bin priceupdate name);
         push @a, qw(invnumber serialnumber) if ($form->{bought} || $form->{sold});
         push @a, "ordnumber" if ($form->{onorder} || $form->{ordered});
         push @a, "quonumber" if ($form->{rfq}     || $form->{quoted});
         my $union = "";
         $query = "";
         @all_values = ();
         if ($form->{bought} || $form->{sold}) {
           my @invvalues = @values;
           my $invwhere = "$where";
           $invwhere .= " AND i.assemblyitem = '0'";
           $invwhere .= " AND a.transdate >= '$form->{transdatefrom}'"
             if $form->{transdatefrom};
           $invwhere .= " AND a.transdate <= '$form->{transdateto}'"
             if $form->{transdateto};
           $invwhere .= qq| AND i.assemblyitem = '0'|;
           if ($form->{transdatefrom}) {
             $invwhere .= qq| AND a.transdate >= ?|;
             push(@invvalues, $form->{transdatefrom});
+          }
           if ($form->{transdateto}) {
             $invwhere .= qq| AND a.transdate <= ?|;
             push(@invvalues, $form->{transdateto});
+          }
           if ($form->{description}) {
             $var = $form->like(lc $form->{description});
             $invwhere .= " AND lower(i.description) LIKE '$var'";
             $invwhere .= qq| AND i.description ILIKE ?|;
             push(@invvalues, '%' . $form->{description} . '%');
+          }
           my $flds = qq|p.id, p.partnumber, i.description, i.serialnumber,
                         i.qty AS onhand, i.unit, p.bin, i.sellprice,
     		    p.listprice, p.lastcost, p.rop, p.weight,
     		    p.priceupdate, p.image, p.drawing, p.microfiche,
     		    pg.partsgroup,
     		    a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
     		    ct.name, i.deliverydate|;
           $flds =
             qq|p.id, p.partnumber, i.description, i.serialnumber,
                i.qty AS onhand, i.unit, p.bin, i.sellprice,
                p.listprice, p.lastcost, p.rop, p.weight,
                p.priceupdate, p.image, p.drawing, p.microfiche,
                pg.partsgroup,
                a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
                ct.name, i.deliverydate|;
           if ($form->{bought}) {
             $query = qq|
     	            SELECT $flds, 'ir' AS module, '' AS type,
 AS exchangerate
     		    FROM invoice i
     		    JOIN parts p ON (p.id = i.parts_id)
     		    JOIN ap a ON (a.id = i.trans_id)
     		    JOIN vendor ct ON (a.vendor_id = ct.id)
     		    LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		    WHERE $invwhere|;
             $union = "
     	          UNION";
             $query =
               qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate
                  FROM invoice i
                  JOIN parts p ON (p.id = i.parts_id)
                  JOIN ap a ON (a.id = i.trans_id)
                  JOIN vendor ct ON (a.vendor_id = ct.id)
                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                  WHERE $invwhere|;
             $union = qq| UNION |;
             push(@all_values, @invvalues);
+          }
           if ($form->{sold}) {
             $query .= qq|$union
                          SELECT $flds, 'is' AS module, '' AS type,
 As exchangerate
     		     FROM invoice i
     		     JOIN parts p ON (p.id = i.parts_id)
     		     JOIN ar a ON (a.id = i.trans_id)
     		     JOIN customer ct ON (a.customer_id = ct.id)
     		     LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		     WHERE $invwhere|;
             $union = "
     	          UNION";
             $query .=
               qq|$union
                  SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate
                  FROM invoice i
                  JOIN parts p ON (p.id = i.parts_id)
                  JOIN ar a ON (a.id = i.trans_id)
                  JOIN customer ct ON (a.customer_id = ct.id)
                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                  WHERE $invwhere|;
             $union = qq| UNION |;
             push(@all_values, @invvalues);
+          }
+        }
         if ($form->{onorder} || $form->{ordered}) {
           my $ordwhere = "$where
     		     AND o.quotation = '0'";
           $ordwhere .= " AND o.transdate >= '$form->{transdatefrom}'"
             if $form->{transdatefrom};
           $ordwhere .= " AND o.transdate <= '$form->{transdateto}'"
             if $form->{transdateto};
           my @ordvalues = @values;
           my $ordwhere = $where . qq| AND o.quotation = '0'|;
           if ($form->{description}) {
             $var = $form->like(lc $form->{description});
             $ordwhere .= " AND lower(oi.description) LIKE '$var'";
           if ($form->{transdatefrom}) {
             $ordwhere .= qq| AND o.transdate >= ?|;
             push(@ordvalues, $form->{transdatefrom});
+          }
           $flds =
             qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                      oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
     	         p.listprice, p.lastcost, p.rop, p.weight,
     		 p.priceupdate, p.image, p.drawing, p.microfiche,
     		 pg.partsgroup,
     		 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
     		 ct.name, NULL AS deliverydate|;
           if ($form->{transdateto}) {
             $ordwhere .= qq| AND o.transdate <= ?|;
             push(@ordvalues, $form->{transdateto});
+          }
           if ($form->{description}) {
             $ordwhere .= qq| AND oi.description ILIKE ?|;
             push(@ordvalues, '%' . $form->{description} . '%');
+          }
           if ($form->{ordered}) {
             $query .= qq|$union
                          SELECT $flds, 'oe' AS module, 'sales_order' AS type,
     		    (SELECT buy FROM exchangerate ex
     		     WHERE ex.curr = o.curr
     		     AND ex.transdate = o.transdate) AS exchangerate
     		     FROM orderitems oi
     		     JOIN parts p ON (oi.parts_id = p.id)
     		     JOIN oe o ON (oi.trans_id = o.id)
     		     JOIN customer ct ON (o.customer_id = ct.id)
     		     LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		     WHERE $ordwhere
     		     AND o.customer_id > 0|;
             $union = "
     	          UNION";
             $query .=
               qq|$union
                  SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                    oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
                    p.listprice, p.lastcost, p.rop, p.weight,
                    p.priceupdate, p.image, p.drawing, p.microfiche,
                    pg.partsgroup,
                    '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
                    ct.name, NULL AS deliverydate,
                    'oe' AS module, 'sales_order' AS type,
                    (SELECT buy FROM exchangerate ex
                     WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate
                  FROM orderitems oi
                  JOIN parts p ON (oi.parts_id = p.id)
                  JOIN oe o ON (oi.trans_id = o.id)
                  JOIN customer ct ON (o.customer_id = ct.id)
                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                  WHERE $ordwhere AND (o.customer_id > 0)|;
             $union = qq| UNION |;
             push(@all_values, @ordvalues);
+          }
           if ($form->{onorder}) {
             $flds =
               qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                        oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
     		   p.listprice, p.lastcost, p.rop, p.weight,
     		   p.priceupdate, p.image, p.drawing, p.microfiche,
     		   pg.partsgroup,
     		   '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
     		   ct.name, NULL AS deliverydate|;
             $query .= qq|$union
     	            SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
     		    (SELECT sell FROM exchangerate ex
     		     WHERE ex.curr = o.curr
     		     AND ex.transdate = o.transdate) AS exchangerate
     		    FROM orderitems oi
     		    JOIN parts p ON (oi.parts_id = p.id)
     		    JOIN oe o ON (oi.trans_id = o.id)
     		    JOIN vendor ct ON (o.vendor_id = ct.id)
     		    LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		    WHERE $ordwhere
     		    AND o.vendor_id > 0|;
             $query .=
               qq|$union
                  SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                    oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
                    p.listprice, p.lastcost, p.rop, p.weight,
                    p.priceupdate, p.image, p.drawing, p.microfiche,
                    pg.partsgroup,
                    '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
                    ct.name, NULL AS deliverydate,
                    'oe' AS module, 'purchase_order' AS type,
                    (SELECT sell FROM exchangerate ex
                    WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate
                  FROM orderitems oi
                  JOIN parts p ON (oi.parts_id = p.id)
                  JOIN oe o ON (oi.trans_id = o.id)
                  JOIN vendor ct ON (o.vendor_id = ct.id)
                  LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                  WHERE $ordwhere AND (o.vendor_id > 0)|;
             $union = qq| UNION |;
             push(@all_values, @ordvalues);
+          }
+        }
         if ($form->{rfq} || $form->{quoted}) {
           my $quowhere = "$where
     		     AND o.quotation = '1'";
           $quowhere .= " AND o.transdate >= '$form->{transdatefrom}'"
             if $form->{transdatefrom};
           $quowhere .= " AND o.transdate <= '$form->{transdateto}'"
             if $form->{transdateto};
           my $quowhere = $where . qq| AND o.quotation = '1'|;
           my @quovalues = @values;
           if ($form->{description}) {
             $var = $form->like(lc $form->{description});
             $quowhere .= " AND lower(oi.description) LIKE '$var'";
           if ($form->{transdatefrom}) {
             $quowhere .= qq| AND o.transdate >= ?|;
             push(@quovalues, $form->{transdatefrom});
+          }
           $flds =
             qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                      oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
     	         p.listprice, p.lastcost, p.rop, p.weight,
     		 p.priceupdate, p.image, p.drawing, p.microfiche,
     		 pg.partsgroup,
     		 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
     		 ct.name, NULL AS deliverydate|;
           if ($form->{transdateto}) {
             $quowhere .= qq| AND o.transdate <= ?|;
             push(@quovalues, $form->{transdateto});
+          }
           if ($form->{description}) {
             $quowhere .= qq| AND oi.description ILIKE ?|;
             push(@quovalues, '%' . $form->{description} . '%');
+          }
           if ($form->{quoted}) {
             $query .= qq|$union
                          SELECT $flds, 'oe' AS module, 'sales_quotation' AS type,
     		    (SELECT buy FROM exchangerate ex
     		     WHERE ex.curr = o.curr
     		     AND ex.transdate = o.transdate) AS exchangerate
     		     FROM orderitems oi
     		     JOIN parts p ON (oi.parts_id = p.id)
     		     JOIN oe o ON (oi.trans_id = o.id)
     		     JOIN customer ct ON (o.customer_id = ct.id)
     		     LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
     		     WHERE $quowhere
     		     AND o.customer_id > 0|;
             $union = "
     	          UNION";
             $query .=
               qq|$union
                  SELECT
                    p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
                    oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
                    p.listprice, p.lastcost, p.rop, p.weight,
                    p.priceupdate, p.image, p.drawing, p.microfiche,
                    pg.partsgroup,
                    '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
                    ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type,
                    (SELECT buy FROM exchangerate ex
                     WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
                  FROM orderitems oi

... Dieser Diff wurde abgeschnitten, weil er die maximale Anzahl anzuzeigender Zeilen überschreitet.

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 2a9ed0d5

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt