summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rwxr-xr-xLedgerSMB/Form.pm2
-rwxr-xr-xLedgerSMB/IC.pm1386
2 files changed, 745 insertions, 643 deletions
diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index 22dbf06f..23733bbf 100755
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -1491,6 +1491,8 @@ sub dbquote {
sub update_balance {
+ # This is a dangerous private function. All apps calling it must
+ # be careful to avoid SQL injection issues
my ($self, $dbh, $table, $field, $where, $value) = @_;
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index f96b1294..dbaed0ce 100755
--- a/LedgerSMB/IC.pm
+++ b/LedgerSMB/IC.pm
@@ -35,801 +35,901 @@ package IC;
sub get_part {
- my ($self, $myconfig, $form) = @_;
-
- # connect to db
- my $dbh = $form->{dbh};
- my $i;
-
- my $query = qq|SELECT p.*,
- c1.accno AS inventory_accno, c1.description AS inventory_description,
- c2.accno AS income_accno, c2.description AS income_description,
- c3.accno AS expense_accno, c3.description AS expense_description,
- 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 = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- my $ref = $sth->fetchrow_hashref(NAME_lc);
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to db
+ my $dbh = $form->{dbh};
+ my $i;
+
+ my $query = qq|
+ SELECT p.*, c1.accno AS inventory_accno,
+ c1.description AS inventory_description,
+ c2.accno AS income_accno,
+ c2.description AS income_description,
+ c3.accno AS expense_accno,
+ c3.description AS expense_description, 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 $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+ my $ref = $sth->fetchrow_hashref(NAME_lc);
- # copy to $form variables
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
- $sth->finish;
+ # copy to $form variables
+ for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ $sth->finish;
- my %oid = ('Pg' => 'TRUE',
- 'Oracle' => 'a.rowid',
- 'DB2' => '1=1'
- );
-
- # part, service item or labor
- $form->{item} = ($form->{inventory_accno_id}) ? 'part' : 'service';
- $form->{item} = 'labor' if ! $form->{income_accno_id};
+ # part, service item or labor
+ $form->{item} = ($form->{inventory_accno_id}) ? 'part' : 'service';
+ $form->{item} = 'labor' if ! $form->{income_accno_id};
- if ($form->{assembly}) {
- $form->{item} = 'assembly';
-
- # retrieve assembly items
- $query = qq|SELECT p.id, p.partnumber, p.description,
- p.sellprice, p.weight, a.qty, a.bom, a.adj, p.unit,
- p.lastcost, p.listprice,
- pg.partsgroup, p.assembly, p.partsgroup_id
- 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 = ?
- |;
-
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ if ($form->{assembly}) {
+ $form->{item} = 'assembly';
+
+ # retrieve assembly items
+ $query = qq|
+ SELECT p.id, p.partnumber, p.description,
+ p.sellprice, p.weight, a.qty, a.bom, a.adj,
+ p.unit, p.lastcost, p.listprice,
+ pg.partsgroup, p.assembly, p.partsgroup_id
+ 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 = ?|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- $form->{assembly_rows} = 0;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{assembly_rows}++;
- foreach my $key ( keys %{ $ref } ) {
- $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
- }
- }
- $sth->finish;
-
- }
-
- # setup accno hash for <option checked> {amount} is used in create_links
- for (qw(inventory income expense)) { $form->{amount}{"IC_$_"} = { accno => $form->{"${_}_accno"}, description => $form->{"${_}_description"} } }
-
+ $form->{assembly_rows} = 0;
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $form->{assembly_rows}++;
+ foreach my $key ( keys %{ $ref } ) {
+ $form->{"${key}_$form->{assembly_rows}"}
+ = $ref->{$key};
+ }
+ }
+ $sth->finish;
- if ($form->{item} =~ /(part|assembly)/) {
- # get makes
- if ($form->{makemodel} ne "") {
- $query = qq|SELECT make, model
- FROM makemodel
- WHERE parts_id = ?|;
+ }
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ # setup accno hash for <option checked>
+ # {amount} is used in create_links
+ for (qw(inventory income expense)) {
+ $form->{amount}{"IC_$_"}
+ = {
+ accno => $form->{"${_}_accno"},
+ description => $form->{"${_}_description"}
+ };
+ };
+
+
+ if ($form->{item} =~ /(part|assembly)/) {
+
+ if ($form->{makemodel} ne "") {
+ $query = qq|
+ SELECT make, model
+ FROM makemodel
+ WHERE parts_id = ?|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{makemodels} }, $ref;
- }
- $sth->finish;
- }
- }
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{makemodels} }, $ref;
+ }
+ $sth->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 = ?|;
+ # 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 = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- while (($key) = $sth->fetchrow_array) {
- $form->{amount}{$key} = $key;
- }
+ while (($key) = $sth->fetchrow_array) {
+ $form->{amount}{$key} = $key;
+ }
- $sth->finish;
+ $sth->finish;
- my $id = $dbh->quote($form->{id});
- # is it an orphan
- $query = qq|SELECT parts_id
- FROM invoice
- WHERE parts_id = $id
- UNION
- SELECT parts_id
- FROM orderitems
- WHERE parts_id = $id
- UNION
- SELECT parts_id
- FROM assembly
- WHERE parts_id = $id
- UNION
- SELECT parts_id
- FROM jcitems
- WHERE parts_id = $id|;
- ($form->{orphaned}) = $dbh->selectrow_array($query);
- $form->{orphaned} = !$form->{orphaned};
-
- $form->{orphaned} = 0 if $form->{project_id};
-
- if ($form->{item} eq 'assembly') {
- if ($form->{orphaned}) {
- $form->{orphaned} = !$form->{onhand};
- }
- }
+ my $id = $dbh->quote($form->{id});
+ # is it an orphan
+ $query = qq|
+ SELECT parts_id FROM invoice WHERE parts_id = $id
+ UNION
+ SELECT parts_id FROM orderitems WHERE parts_id = $id
+ UNION
+ SELECT parts_id FROM assembly WHERE parts_id = $id
+ UNION
+ SELECT parts_id FROM jcitems WHERE parts_id = $id|;
+ ($form->{orphaned}) = $dbh->selectrow_array($query);
+ $form->{orphaned} = !$form->{orphaned};
+
+ $form->{orphaned} = 0 if $form->{project_id};
+
+ if ($form->{item} eq 'assembly') {
+ if ($form->{orphaned}) {
+ $form->{orphaned} = !$form->{onhand};
+ }
+ }
- if ($form->{item} =~ /(part|service)/) {
- # get vendors
- $query = qq|SELECT v.id, v.name, pv.partnumber,
- pv.lastcost, pv.leadtime, pv.curr AS vendorcurr
- FROM partsvendor pv
- JOIN vendor v ON (v.id = pv.vendor_id)
- WHERE pv.parts_id = ?
- ORDER BY 2|;
+ if ($form->{item} =~ /(part|service)/) {
+ # get vendors
+ $query = qq|
+ SELECT v.id, v.name, pv.partnumber,
+ pv.lastcost, pv.leadtime,
+ pv.curr AS vendorcurr
+ FROM partsvendor pv
+ JOIN vendor v ON (v.id = pv.vendor_id)
+ WHERE pv.parts_id = ?
+ ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{vendormatrix} }, $ref;
- }
- $sth->finish;
- }
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{vendormatrix} }, $ref;
+ }
+ $sth->finish;
+ }
- # get matrix
- if ($form->{item} ne 'labor') {
- $query = qq|SELECT pc.pricebreak, pc.sellprice AS customerprice,
- pc.curr AS customercurr,
- pc.validfrom, pc.validto,
- c.name, c.id AS cid, g.pricegroup, g.id AS gid
- FROM partscustomer pc
- LEFT JOIN customer c ON (c.id = pc.customer_id)
- LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
- WHERE pc.parts_id = ?
- ORDER BY c.name, g.pricegroup, pc.pricebreak|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{customermatrix} }, $ref;
- }
- $sth->finish;
- }
+ # get matrix
+ if ($form->{item} ne 'labor') {
+ $query = qq|
+ SELECT pc.pricebreak, pc.sellprice AS customerprice,
+ pc.curr AS customercurr, pc.validfrom,
+ pc.validto, c.name, c.id AS cid,
+ g.pricegroup, g.id AS gid
+ FROM partscustomer pc
+ LEFT JOIN customer c ON (c.id = pc.customer_id)
+ LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
+ WHERE pc.parts_id = ?
+ ORDER BY c.name, g.pricegroup, pc.pricebreak|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{customermatrix} }, $ref;
+ }
+ $sth->finish;
+ }
- $form->run_custom_queries('parts', 'SELECT');
+ $form->run_custom_queries('parts', 'SELECT');
}
sub save {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- ($form->{inventory_accno}) = split(/--/, $form->{IC_inventory});
- ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
- ($form->{income_accno}) = split(/--/, $form->{IC_income});
+ ($form->{inventory_accno}) = split(/--/, $form->{IC_inventory});
+ ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
+ ($form->{income_accno}) = split(/--/, $form->{IC_income});
- # connect to database, turn off AutoCommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->{dbh};
- # save the part
- # make up a unique handle and store in partnumber field
- # then retrieve the record based on the unique handle to get the id
- # replace the partnumber field with the actual variable
- # add records for makemodel
-
- # if there is a $form->{id} then replace the old entry
- # delete all makemodel entries and add the new ones
-
- # undo amount formatting
- for (qw(rop weight listprice sellprice lastcost stock)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
+ # undo amount formatting
+ for (qw(rop weight listprice sellprice lastcost stock)) {
+ $form->{$_} = $form->parse_amount($myconfig, $form->{$_});
+ }
- $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
+ $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
- $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
- for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 }
+ $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
+ for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 }
- my $query;
- my $sth;
- my $i;
- my $null;
- my $vendor_id;
- my $customer_id;
+ my $query;
+ my $sth;
+ my $i;
+ my $null;
+ my $vendor_id;
+ my $customer_id;
- if ($form->{id}) {
-
- # get old price
- $query = qq|SELECT id, listprice, sellprice, lastcost, weight, project_id
- FROM parts
- WHERE id = $form->{id}|;
- my ($id, $listprice, $sellprice, $lastcost, $weight, $project_id) = $dbh->selectrow_array($query);
-
- if ($id) {
+ if ($form->{id}) {
+
+ # get old price
+ $query = qq|
+ SELECT id, listprice, sellprice, lastcost, weight,
+ project_id
+ FROM parts
+ WHERE id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($form->{id});
+ my ($id, $listprice, $sellprice, $lastcost, $weight,
+ $project_id)
+ = $dbh->fetchrow_array();
+
+ if ($id) {
- if (!$project_id) {
- # if item is part of an assembly adjust all assemblies
- $query = qq|SELECT id, qty, adj
- FROM assembly
- WHERE parts_id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- while (my ($id, $qty, $adj) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $id, $qty, $adj, $listprice * 1, $sellprice * 1, $lastcost * 1, $weight * 1);
- }
- $sth->finish;
- }
-
- if ($form->{item} =~ /(part|service)/) {
- # delete partsvendor records
- $query = qq|DELETE FROM partsvendor
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
+ if (!$project_id) {
+ # if item is part of an assembly
+ # adjust all assemblies
+ $query = qq|
+ SELECT id, qty, adj
+ FROM assembly
+ WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) ||
+ $form->dberror($query);
+ while (my ($id, $qty, $adj)
+ = $sth->fetchrow_array) {
+
+ &update_assembly(
+ $dbh, $form, $id, $qty, $adj,
+ $listprice * 1, $sellprice * 1,
+ $lastcost * 1, $weight * 1);
+ }
+ $sth->finish;
+ }
+
+ if ($form->{item} =~ /(part|service)/) {
+ # delete partsvendor records
+ $query = qq|
+ DELETE FROM partsvendor
+ WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})
+ || $form->dberror($query);
+ }
- if ($form->{item} !~ /(service|labor)/) {
- # delete makemodel records
- $query = qq|DELETE FROM makemodel
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
-
- if ($form->{item} eq 'assembly') {
-
- if ($form->{onhand}) {
- &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
- }
+ if ($form->{item} !~ /(service|labor)/) {
+ # delete makemodel records
+ $query = qq|
+ DELETE FROM makemodel
+ WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})
+ || $form->dberror($query);
+ }
+
+ if ($form->{item} eq 'assembly') {
+
+ if ($form->{onhand}) {
+ &adjust_inventory(
+ $dbh, $form, $form->{id},
+ $form->{onhand} * -1);
+ }
- if ($form->{orphaned}) {
- # delete assembly records
- $query = qq|DELETE FROM assembly
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- } else {
-
- for $i (1 .. $form->{assembly_rows} - 1) {
- # update BOM, A only
- for (qw(bom adj)) { $form->{"${_}_$i"} *= 1 }
-
- $query = qq|UPDATE assembly SET
- bom = '$form->{"bom_$i"}',
- adj = '$form->{"adj_$i"}'
- WHERE id = $form->{id}
- AND parts_id = $form->{"id_$i"}|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
-
- $form->{onhand} += $form->{stock};
-
- }
-
- # delete tax records
- $query = qq|DELETE FROM partstax
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- # delete matrix
- $query = qq|DELETE FROM partscustomer
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ if ($form->{orphaned}) {
+ # delete assembly records
+ $query = qq|
+ DELETE FROM assembly
+ WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})
+ || $form->dberror($query);
+ } else {
+
+ for $i (1 ..
+ $form->{assembly_rows} - 1) {
+
+ # update BOM, A only
+ for (qw(bom adj)) {
+ $form->{"${_}_$i"}
+ *= 1;
+ }
+
+ $query = qq|
+ UPDATE assembly
+ SET bom = ?,
+ adj = ?
+ WHERE id = ?
+ AND parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $form->{"bom_$i"},
+ $form->{"adj_$i"},
+ $form->{id},
+ $form->{"id_$i"}
+ )|| $form->dberror(
+ $query);
+ }
+ }
+
+ $form->{onhand} += $form->{stock};
+
+ }
+
+ # delete tax records
+ $query = qq|DELETE FROM partstax WHERE parts_id = ?|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})|| $form->dberror($query);
+
+ # delete matrix
+ $query = qq|
+ DELETE FROM partscustomer
+ WHERE parts_id = ?|;
- } else {
- $query = qq|INSERT INTO parts (id)
- VALUES ($form->{id})|;
- $dbh->do($query) || $form->dberror($query);
- }
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})|| $form->dberror($query);
+ } else {
+ $query = qq|INSERT INTO parts (id) VALUES (?)|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id})|| $form->dberror($query);
+ }
- }
+ }
- if (!$form->{id}) {
- my $uid = localtime;
- $uid .= "$$";
-
- $query = qq|INSERT INTO parts (partnumber)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
+ if (!$form->{id}) {
+ my $uid = localtime;
+ $uid .= "$$";
+
+ $query = qq|INSERT INTO parts (partnumber) VALUES ('$uid')|;
+ $dbh->do($query) || $form->dberror($query);
+
+ $query = qq|SELECT id FROM parts WHERE partnumber = '$uid'|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+ ($form->{id}) = $sth->fetchrow_array;
+ $sth->finish;
+
+ $form->{orphaned} = 1;
+ $form->{onhand} = ($form->{stock} * 1)
+ if $form->{item} eq 'assembly';
+ }
- $query = qq|SELECT id FROM parts
- WHERE partnumber = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
+ my $partsgroup_id;
+ ($null, $partsgroup_id) = split /--/, $form->{partsgroup};
+ $partsgroup_id *= 1;
- $form->{orphaned} = 1;
- $form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly';
-
- }
+ $form->{partnumber} = $form->update_defaults(
+ $myconfig, "partnumber", $dbh) if ! $form->{partnumber};
- my $partsgroup_id;
- ($null, $partsgroup_id) = split /--/, $form->{partsgroup};
- $partsgroup_id *= 1;
-
- $form->{partnumber} = $form->update_defaults($myconfig, "partnumber", $dbh) if ! $form->{partnumber};
-
- $query = qq|UPDATE parts SET
- partnumber = |.$dbh->quote($form->{partnumber}).qq|,
- description = |.$dbh->quote($form->{description}).qq|,
- makemodel = '$form->{makemodel}',
- alternate = '$form->{alternate}',
- assembly = '$form->{assembly}',
- listprice = $form->{listprice},
- sellprice = $form->{sellprice},
- lastcost = $form->{lastcost},
- weight = $form->{weight},
- priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|,
- unit = |.$dbh->quote($form->{unit}).qq|,
- notes = |.$dbh->quote($form->{notes}).qq|,
- rop = $form->{rop},
- bin = |.$dbh->quote($form->{bin}).qq|,
- inventory_accno_id = (SELECT id FROM chart
- WHERE accno = '$form->{inventory_accno}'),
- income_accno_id = (SELECT id FROM chart
- WHERE accno = '$form->{income_accno}'),
- expense_accno_id = (SELECT id FROM chart
- WHERE accno = '$form->{expense_accno}'),
- obsolete = '$form->{obsolete}',
- image = '$form->{image}',
- drawing = '$form->{drawing}',
- 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 = ?,
+ assembly = ?,
+ listprice = ?,
+ sellprice = ?,
+ lastcost = ?,
+ weight = ?,
+ priceupdate = ?,
+ unit = ?,
+ notes = ?,
+ rop = ?,
+ bin = ?,
+ inventory_accno_id = (SELECT id FROM chart
+ WHERE accno = ?),
+ income_accno_id = (SELECT id FROM chart
+ WHERE accno = ?),
+ expense_accno_id = (SELECT id FROM chart
+ WHERE accno = ?),
+ obsolete = ?,
+ image = ?,
+ drawing = ?,
+ microfiche = ?,
+ partsgroup_id = ?
+ WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $form->{partnumber}, $form->{description}, $form->{makemodel},
+ $form->{alternate}, $form->{assembly}, $form->{listprice},
+ $form->{sellprice}, $form->{lastcost}, $form->{weight},
+ $form->{priceupdate}, $form->{unit}, $form->{notes},
+ $form->{rop}, $form->{bin}, $form->{inventory_accno},
+ $form->{income_accno}, $form->{expense_accno},
+ $form->{obsolete}, $form->{image}, $form->{drawing},
+ $form->{microfiche}, $partsgroup_id, $form->{id}
+ ) || $form->dberror($query);
- # insert makemodel records
- if ($form->{item} =~ /(part|assembly)/) {
- for $i (1 .. $form->{makemodel_rows}) {
- if (($form->{"make_$i"} ne "") || ($form->{"model_$i"} ne "")) {
- $query = qq|INSERT INTO makemodel (parts_id, make, model)
- VALUES ($form->{id},|
- .$dbh->quote($form->{"make_$i"}).qq|, |
- .$dbh->quote($form->{"model_$i"}).qq|)|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
- }
+ # insert makemodel records
+ if ($form->{item} =~ /(part|assembly)/) {
+ $query = qq|
+ INSERT INTO makemodel (parts_id, make, model)
+ VALUES (?, ?, ?)|;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
+ for $i (1 .. $form->{makemodel_rows}) {
+ if (($form->{"make_$i"} ne "")
+ || ($form->{"model_$i"} ne "")) {
+ $sth->execute(
+ $form->{id}, $form->{"make_$i"},
+ $form->{"model_$i"}
+ ) || $form->dberror($query);
+ }
+ }
+ }
- # insert taxes
- for (split / /, $form->{taxaccounts}) {
- if ($form->{"IC_tax_$_"}) {
- $query = qq|INSERT INTO partstax (parts_id, chart_id)
- VALUES ($form->{id},
- (SELECT id
- FROM chart
- WHERE accno = '$_'))|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
+ # insert taxes
+ $query = qq|
+ INSERT INTO partstax (parts_id, chart_id)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
+ $sth = $dbh->prepare($query);
+ for (split / /, $form->{taxaccounts}) {
+ if ($form->{"IC_tax_$_"}) {
+ $sth->execute($form->{id}, $_)
+ || $form->dberror($query);
+ }
+ }
- @a = localtime;
- $a[5] += 1900;
- $a[4]++;
- $a[4] = substr("0$a[4]", -2);
- $a[3] = substr("0$a[3]", -2);
- my $shippingdate = "$a[5]$a[4]$a[3]";
+ @a = localtime;
+ $a[5] += 1900;
+ $a[4]++;
+ $a[4] = substr("0$a[4]", -2);
+ $a[3] = substr("0$a[3]", -2);
+ my $shippingdate = "$a[5]$a[4]$a[3]";
- ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
+ ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
- # add assembly records
- if ($form->{item} eq 'assembly' && !$project_id) {
+ # add assembly records
+ if ($form->{item} eq 'assembly' && !$project_id) {
- if ($form->{orphaned}) {
- for $i (1 .. $form->{assembly_rows}) {
- $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
-
- if ($form->{"qty_$i"}) {
- for (qw(bom adj)) { $form->{"${_}_$i"} *= 1 }
- $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj)
- VALUES ($form->{id}, $form->{"id_$i"},
- $form->{"qty_$i"}, '$form->{"bom_$i"}',
- '$form->{"adj_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ if ($form->{orphaned}) {
+ $query = qq|
+ INSERT INTO assembly
+ (id, parts_id, qty, bom, adj)
+ VALUES (?, ?, ?, ?, ?)|;
+ $sth = $dbh->prepare($query);
+ for $i (1 .. $form->{assembly_rows}) {
+ $form->{"qty_$i"} = $form->parse_amount(
+ $myconfig, $form->{"qty_$i"});
+ $sth->execute(
+ $form->{id}, $form->{"id_$i"},
+ $form->{"qty_$i"}, $form->{"bom_$i"},
+ $form->{"adj_$i"}
+ ) || $form->dberror($query);
+ }
+ }
+ # adjust onhand for the parts
+ if ($form->{onhand}) {
+ &adjust_inventory(
+ $dbh, $form, $form->{id}, $form->{onhand});
+ }
}
- }
- }
-
- # adjust onhand for the parts
- if ($form->{onhand}) {
- &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
- }
- }
- # add vendors
- if ($form->{item} ne 'assembly') {
- $updparts{$form->{id}} = 1;
+ # add vendors
+ if ($form->{item} ne 'assembly') {
+ $updparts{$form->{id}} = 1;
- for $i (1 .. $form->{vendor_rows}) {
- if (($form->{"vendor_$i"} ne "") && $form->{"lastcost_$i"}) {
+ for $i (1 .. $form->{vendor_rows}) {
+ if (($form->{"vendor_$i"} ne "")
+ && $form->{"lastcost_$i"}) {
- ($null, $vendor_id) = split /--/, $form->{"vendor_$i"};
+ ($null, $vendor_id)
+ = split /--/, $form->{"vendor_$i"};
- for (qw(lastcost leadtime)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})}
+ for (qw(lastcost leadtime)) {
+ $form->{"${_}_$i"}
+ = $form->parse_amount(
+ $myconfig,
+ $form->{"${_}_$i"});
+ }
- $query = qq|INSERT INTO partsvendor (vendor_id, parts_id, partnumber,
- lastcost, leadtime, curr)
- VALUES ($vendor_id, $form->{id},|
- .$dbh->quote($form->{"partnumber_$i"}).qq|,
- $form->{"lastcost_$i"},
- $form->{"leadtime_$i"}, '$form->{"vendorcurr_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
- }
+ $query = qq|
+ INSERT INTO partsvendor
+ (vendor_id, parts_id,
+ partnumber, lastcost,
+ leadtime, curr)
+ VALUES (?, ?, ?, ?, ?, ?)|;
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $vendor_id, $form->{id},
+ $form->{"partnumber_$i"},
+ $form->{"lastcost_$i"},
+ $form->{"leadtime_$i"},
+ $form->{"vendorcurr_$i"}
+ )|| $form->dberror($query);
+ }
+ }
+ }
- # add pricematrix
- for $i (1 .. $form->{customer_rows}) {
+ # add pricematrix
+ for $i (1 .. $form->{customer_rows}) {
- for (qw(pricebreak customerprice)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})}
+ for (qw(pricebreak customerprice)) {
+ $form->{"${_}_$i"} = $form->parse_amount(
+ $myconfig, $form->{"${_}_$i"});
+ }
- if ($form->{"customerprice_$i"}) {
+ if ($form->{"customerprice_$i"}) {
- ($null, $customer_id) = split /--/, $form->{"customer_$i"};
- $customer_id *= 1;
+ ($null, $customer_id)
+ = split /--/, $form->{"customer_$i"};
+ $customer_id *= 1;
- ($null, $pricegroup_id) = split /--/, $form->{"pricegroup_$i"};
- $pricegroup_id *= 1;
+ ($null, $pricegroup_id)
+ = split /--/, $form->{"pricegroup_$i"};
- $query = qq|INSERT INTO partscustomer (parts_id, customer_id,
- pricegroup_id, pricebreak, sellprice, curr,
- validfrom, validto)
- VALUES ($form->{id}, $customer_id,
- $pricegroup_id, $form->{"pricebreak_$i"},
- $form->{"customerprice_$i"}, '$form->{"customercurr_$i"}',|
- .$form->dbquote($form->{"validfrom_$i"}, SQL_DATE).qq|, |
- .$form->dbquote($form->{"validto_$i"}, SQL_DATE).qq|)|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
+ $query = qq|
+ INSERT INTO partscustomer
+ (parts_id, customer_id,
+ pricegroup_id, pricebreak,
+ sellprice, curr,
+ validfrom, validto)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?)|;
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $form->{id}, $customer_id, $pricegroup_id,
+ $form->{"pricebreak_$i"},
+ $form->{"customerprice_$i"},
+ $form->{"customercurr_$i"},
+ $form->{"validfrom_$i"}, $form->{"validto_$i"}
+ )|| $form->dberror($query);
+ }
+ }
- # commit
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ my $rc = $dbh->commit;
- $form->run_custom_queries('parts', 'UPDATE');
- $rc;
+ $form->run_custom_queries('parts', 'UPDATE');
+ $rc;
}
sub update_assembly {
- my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost, $weight) = @_;
+ my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost,
+ $weight) = @_;
- my $formlistprice = $form->{listprice};
- my $formsellprice = $form->{sellprice};
+ my $formlistprice = $form->{listprice};
+ my $formsellprice = $form->{sellprice};
- if (!$adj) {
- $formlistprice = $listprice;
- $formsellprice = $sellprice;
- }
+ if (!$adj) {
+ $formlistprice = $listprice;
+ $formsellprice = $sellprice;
+ }
- my $query = qq|SELECT id, qty, adj
- FROM assembly
- WHERE parts_id = $id|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $query = qq|SELECT id, qty, adj FROM assembly WHERE parts_id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($id) || $form->dberror($query);
- $form->{$id} = 1;
+ $form->{$id} = 1; # Not sure what this is for...
+ # In fact, we don't seem to use it... Chris T
- while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj, $listprice, $sellprice, $lastcost, $weight) if !$form->{$pid};
- }
- $sth->finish;
+ while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) {
+ &update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj,
+ $listprice, $sellprice, $lastcost, $weight)
+ if !$form->{$pid};
+ }
+ $sth->finish;
+ $qty = $dbh->quote($qty);
+ $formlistprice = $dbh->quote($formlistprice );
+ $listprice = $dbh->quote($listprice );
+ $formsellprice = $dbh->quote($formsellprice );
+ $formlastcost = $dbh->quote($form->{lastcost});
+ $lastcost = $dbh->quote($lastcost);
+ $weight = $dbh->quote($weight);
+ $id = $dbh->quote($id);
+
+ $query = qq|
+ UPDATE parts
+ SET listprice = listprice +
+ $qty * ($formlistprice - $listprice),
+ sellprice = sellprice +
+ $qty * ($formsellprice - $sellprice),
+ lastcost = lastcost +
+ $qty * ($form->{lastcost} - $lastcost),
+ weight = weight +
+ $qty * ($form->{weight} - $weight)
+ WHERE id = $id|;
+ $dbh->do($query) || $form->dberror($query);
- $query = qq|UPDATE parts
- SET listprice = listprice +
- $qty * ($formlistprice - $listprice),
- sellprice = sellprice +
- $qty * ($formsellprice - $sellprice),
- lastcost = lastcost +
- $qty * ($form->{lastcost} - $lastcost),
- weight = weight +
- $qty * ($form->{weight} - $weight)
- WHERE id = $id|;
- $dbh->do($query) || $form->dberror($query);
-
- delete $form->{$id};
+ delete $form->{$id};
}
sub retrieve_assemblies {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ # connect to database
+ my $dbh = $form->{dbh};
- my $where = '1 = 1';
+ my $where = '1 = 1';
- if ($form->{partnumber} ne "") {
- my $partnumber = $form->like(lc $form->{partnumber});
- $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
- }
+ if ($form->{partnumber} ne "") {
+ my $partnumber = $dbh->quote($form->like(
+ lc $form->{partnumber}));
+ $where .= " AND lower(p.partnumber) LIKE $partnumber";
+ }
- if ($form->{description} ne "") {
- my $description = $form->like(lc $form->{description});
- $where .= " AND lower(p.description) LIKE '$description'";
- }
- $where .= qq| AND p.obsolete = '0'
- AND p.project_id IS NULL|;
+ if ($form->{description} ne "") {
+ my $description = $dbh->($form->like(lc $form->{description}));
+ $where .= " AND lower(p.description) LIKE $description";
+ }
+ $where .= qq| AND p.obsolete = '0'
+ AND p.project_id IS NULL|;
- my %ordinal = ( 'partnumber' => 2,
- 'description' => 3,
- 'bin' => 4
- );
+ my %ordinal = (
+ 'partnumber' => 2,
+ 'description' => 3,
+ 'bin' => 4
+ );
- my @a = qw(partnumber description bin);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
+ my @a = qw(partnumber description bin);
+ my $sortorder = $form->sort_order(\@a, \%ordinal);
- # retrieve assembly items
- my $query = qq|SELECT p.id, p.partnumber, p.description,
- p.bin, p.onhand, p.rop
- FROM parts p
- WHERE $where
- AND p.assembly = '1'
+ # retrieve assembly items
+ my $query = qq|
+ SELECT p.id, p.partnumber, p.description, p.bin, p.onhand,
+ p.rop
+ FROM parts p
+ WHERE $where
+ AND p.assembly = '1'
ORDER BY $sortorder|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- $query = qq|SELECT sum(p.inventory_accno_id), p.assembly
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- WHERE a.id = ?
- GROUP BY p.assembly|;
- my $svh = $dbh->prepare($query) || $form->dberror($query);
+ $query = qq|
+ SELECT sum(p.inventory_accno_id), p.assembly
+ FROM parts p
+ JOIN assembly a ON (a.parts_id = p.id)
+ WHERE a.id = ?
+ GROUP BY p.assembly|;
+ my $svh = $dbh->prepare($query) || $form->dberror($query);
- my $inh;
- if ($form->{checkinventory}) {
- $query = qq|SELECT p.id, p.onhand, a.qty
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- WHERE (p.inventory_accno_id > 0 OR p.assembly)
- AND p.income_accno_id > 0
- AND a.id = ?|;
- $inh = $dbh->prepare($query) || $form->dberror($query);
- }
+ my $inh;
+ if ($form->{checkinventory}) {
+ $query = qq|
+ SELECT p.id, p.onhand, a.qty
+ FROM parts p
+ JOIN assembly a ON (a.parts_id = p.id)
+ WHERE (p.inventory_accno_id > 0 OR p.assembly)
+ AND p.income_accno_id > 0 AND a.id = ?|;
+ $inh = $dbh->prepare($query) || $form->dberror($query);
+ }
- my %available = ();
- my %required;
- my $ref;
- my $aref;
- my $stock;
- my $howmany;
- my $ok;
+ my %available = ();
+ my %required;
+ my $ref;
+ my $aref;
+ my $stock;
+ my $howmany;
+ my $ok;
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $svh->execute($ref->{id});
- ($ref->{inventory}, $ref->{assembly}) = $svh->fetchrow_array;
- $svh->finish;
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $svh->execute($ref->{id});
+ ($ref->{inventory}, $ref->{assembly}) = $svh->fetchrow_array;
+ $svh->finish;
- if ($ref->{inventory} || $ref->{assembly}) {
- $ok = 1;
- if ($form->{checkinventory}) {
- $inh->execute($ref->{id}) || $form->dberror($query);;
- $ok = 0;
- %required = ();
+ if ($ref->{inventory} || $ref->{assembly}) {
+ $ok = 1;
+ if ($form->{checkinventory}) {
+ $inh->execute($ref->{id})
+ || $form->dberror($query);;
+ $ok = 0;
+ %required = ();
- while ($aref = $inh->fetchrow_hashref(NAME_lc)) {
- $available{$aref->{id}} = (exists $available{$aref->{id}}) ? $available{$aref->{id}} : $aref->{onhand};
- $required{$aref->{id}} = $aref->{qty};
+ while ($aref
+ = $inh->fetchrow_hashref(NAME_lc)) {
+
+ $available{$aref->{id}} =
+ (exists $available{$aref->{id}})
+ ? $available{$aref->{id}}
+ : $aref->{onhand};
+ $required{$aref->{id}} = $aref->{qty};
- if ($available{$aref->{id}} >= $aref->{qty}) {
+ if ($available{$aref->{id}}
+ >= $aref->{qty}) {
- $howmany = ($aref->{qty}) ? int $available{$aref->{id}}/$aref->{qty} : 1;
- if ($stock) {
- $stock = ($stock > $howmany) ? $howmany : $stock;
- } else {
- $stock = $howmany;
- }
- $ok = 1;
-
- $available{$aref->{id}} -= $aref->{qty} * $stock;
-
- } else {
- $ok = 0;
- for (keys %required) { $available{$_} += $required{$_} * $stock }
- $stock = 0;
- last;
- }
- }
- $inh->finish;
- $ref->{stock} = $stock;
+ $howmany =
+ ($aref->{qty})
+ ? int $available{
+ $aref->{id}}
+ /$aref->{qty}
+ : 1;
+ if ($stock) {
+ $stock =
+ ($stock
+ > $howmany)
+ ? $howmany
+ : $stock;
+ } else {
+ $stock = $howmany;
+ }
+ $ok = 1;
+
+ $available{$aref->{id}}
+ -= $aref->{qty}
+ * $stock;
+
+ } else {
+ $ok = 0;
+ for (keys %required) {
+ $available{$_} +=
+ $required{$_}
+ * $stock;
+ }
+ $stock = 0;
+ last;
+ }
+ }
+ $inh->finish;
+ $ref->{stock} = $stock;
- }
- push @{ $form->{assembly_items} }, $ref if $ok;
- }
- }
- $sth->finish;
+ }
+ push @{ $form->{assembly_items} }, $ref if $ok;
+ }
+ }
+ $sth->finish;
- $dbh->disconnect;
+ $dbh->commit;
}
sub restock_assemblies {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
+ # connect to database
+ my $dbh = $form->dbconnect_noauto($myconfig);
- for my $i (1 .. $form->{rowcount}) {
-
- $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
-
- if ($form->{"qty_$i"}) {
- &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
- }
+ for my $i (1 .. $form->{rowcount}) {
+ $form->{"qty_$i"} = $form->parse_amount(
+ $myconfig, $form->{"qty_$i"});
+
+ if ($form->{"qty_$i"}) {
+ &adjust_inventory(
+ $dbh, $form, $form->{"id_$i"},
+ $form->{"qty_$i"});
+ }
- }
+ }
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ my $rc = $dbh->commit;
- $rc;
+ $rc;
}
sub adjust_inventory {
- my ($dbh, $form, $id, $qty) = @_;
-
- my $query = qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- WHERE a.id = $id|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- # is it a service item then loop
- if (! $ref->{inventory_accno_id}) {
- next if ! $ref->{assembly}; # assembly
- }
+ # Private method. Do not commit transaction at end of function...
+ my ($dbh, $form, $id, $qty) = @_;
+
+ my $query = qq|
+ SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
+ FROM parts p
+ JOIN assembly a ON (a.parts_id = p.id)
+ WHERE a.id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($id) || $form->dberror($query);
+
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+ # is it a service item then loop
+ if (! $ref->{inventory_accno_id}) {
+ next if ! $ref->{assembly};
+ }
- # adjust parts onhand
- $form->update_balance($dbh,
- "parts",
- "onhand",
- qq|id = $ref->{id}|,
- $qty * $ref->{qty} * -1);
- }
+ # adjust parts onhand
+ $form->update_balance(
+ $dbh, "parts", "onhand", qq|id = $ref->{id}|,
+ $qty * $ref->{qty} * -1);
+ }
- $sth->finish;
+ $sth->finish;
- # update assembly
- $form->update_balance($dbh,
- "parts",
- "onhand",
- qq|id = $id|,
- $qty);
+ # update assembly
+ $form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $qty);
}
sub delete {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database, turn off AutoCommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->{dbh};
- my $query;
+ my $query;
- $query = qq|DELETE FROM parts
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM parts WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- $query = qq|DELETE FROM partstax
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM partstax WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- if ($form->{item} ne 'assembly') {
- $query = qq|DELETE FROM partsvendor
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
+ if ($form->{item} ne 'assembly') {
+ $query = qq|DELETE FROM partsvendor WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $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);
- }
+ # check if it is a part, assembly or service
+ if ($form->{item} ne 'service') {
+ $query = qq|DELETE FROM makemodel WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+ }
- if ($form->{item} eq 'assembly') {
- $query = qq|DELETE FROM assembly
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
+ if ($form->{item} eq 'assembly') {
+ $query = qq|DELETE FROM assembly WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+ }
- if ($form->{item} eq 'alternate') {
- $query = qq|DELETE FROM alternate
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
- $query = qq|DELETE FROM inventory
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM inventory WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- $query = qq|DELETE FROM partscustomer
- WHERE parts_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM partscustomer WHERE parts_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- $query = qq|DELETE FROM translation
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM translation WHERE trans_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- # commit
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ # commit
+ my $rc = $dbh->commit;
- $rc;
+ $rc;
}
sub assembly_item {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
+ my $dbh = $form->{dbh};
- my $i = $form->{assembly_rows};
- my $var;
- my $null;
- my $where = "p.obsolete = '0'";
+ my $i = $form->{assembly_rows};
+ my $var;
+ my $null;
+ my $where = "p.obsolete = '0'";
- if ($form->{"partnumber_$i"} ne "") {
- $var = $form->like(lc $form->{"partnumber_$i"});
- $where .= " AND lower(p.partnumber) LIKE '$var'";
- }
- if ($form->{"description_$i"} ne "") {
- $var = $form->like(lc $form->{"description_$i"});
- $where .= " AND lower(p.description) LIKE '$var'";
- }
- if ($form->{"partsgroup_$i"} ne "") {
- ($null, $var) = split /--/, $form->{"partsgroup_$i"};
- $where .= qq| AND p.partsgroup_id = $var|;
- }
+ if ($form->{"partnumber_$i"} ne "") {
+ $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
+ $where .= " AND lower(p.partnumber) LIKE $var";
+ }
+ if ($form->{"description_$i"} ne "") {
+ $var = $dbh->quote($form->like(lc $form->{"description_$i"}));
+ $where .= " AND lower(p.description) LIKE $var";
+ }
+ if ($form->{"partsgroup_$i"} ne "") {
+ ($null, $var) = split /--/, $form->{"partsgroup_$i"};
+ $var = $dbh->quote($var);
+ $where .= qq| AND p.partsgroup_id = $var|;
+ }
- if ($form->{id}) {
- $where .= " AND p.id != $form->{id}";
- }
+ if ($form->{id}) {
+ $where .= " AND p.id != ".$dbh->quote($form->{id});
+ }
- if ($form->{"description_$i"} ne "") {
- $where .= " ORDER BY p.description";
- } else {
- $where .= " ORDER BY p.partnumber";
- }
+ if ($form->{"description_$i"} ne "") {
+ $where .= " ORDER BY p.description";
+ } else {
+ $where .= " ORDER BY p.partnumber";
+ }
- # 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, p.lastcost,
- pg.partsgroup, p.partsgroup_id
- 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);
+ my $query = qq|
+ SELECT p.id, p.partnumber, p.description, p.sellprice,
+ p.weight, p.onhand, p.unit, p.lastcost,
+ pg.partsgroup, p.partsgroup_id
+ 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;
- }
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{item_list} }, $ref;
+ }
- $sth->finish;
- $dbh->disconnect;
+ $sth->finish;
+ $dbh->disconnect;
}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-28 06:06:02 +0000