summaryrefslogtreecommitdiff
path: root/sql-ledger/SL/IC.pm
diff options
context:
space:
mode:
Diffstat (limited to 'sql-ledger/SL/IC.pm')
-rw-r--r--sql-ledger/SL/IC.pm1037
1 files changed, 807 insertions, 230 deletions
diff --git a/sql-ledger/SL/IC.pm b/sql-ledger/SL/IC.pm
index f4a2f75ff..cf70b06ca 100644
--- a/sql-ledger/SL/IC.pm
+++ b/sql-ledger/SL/IC.pm
@@ -1,6 +1,6 @@
#=====================================================================
# SQL-Ledger Accounting
-# Copyright (C) 2001
+# Copyright (C) 2000
#
# Author: Dieter Simader
# Email: dsimader@sql-ledger.org
@@ -34,11 +34,12 @@ sub get_part {
# connect to db
my $dbh = $form->dbconnect($myconfig);
+ my $i;
my $query = qq|SELECT p.*,
c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_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)
@@ -56,19 +57,23 @@ sub get_part {
$sth->finish;
my %oid = ('Pg' => 'a.oid',
- 'Oracle' => 'a.rowid'
+ 'PgPP' => 'a.oid',
+ 'Oracle' => 'a.rowid',
+ 'DB2' => '1=1'
);
-
-
- # part or service item
+
+ # part, service item or labor
$form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
+ $form->{item} = 'labor' if ! $form->{income_accno};
+
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, p.unit,
- pg.partsgroup
+ 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)
@@ -97,22 +102,20 @@ sub get_part {
$form->{amount}{IC_cogs} = $form->{expense_accno};
- if ($form->{item} ne 'service') {
+ if ($form->{item} =~ /(part|assembly)/) {
# get makes
if ($form->{makemodel}) {
- $query = qq|SELECT name FROM makemodel
+ $query = qq|SELECT make, model
+ FROM makemodel
WHERE parts_id = $form->{id}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- my $i = 1;
- while (($form->{"make_$i"}, $form->{"model_$i"}) = split(/:/, $sth->fetchrow_array)) {
- $i++;
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{makemodels} }, $ref;
}
$sth->finish;
- $form->{makemodel_rows} = $i - 1;
-
}
}
@@ -135,11 +138,11 @@ sub get_part {
$query = qq|SELECT parts_id
FROM invoice
WHERE parts_id = $form->{id}
- UNION
+ UNION
SELECT parts_id
FROM orderitems
WHERE parts_id = $form->{id}
- UNION
+ UNION
SELECT parts_id
FROM assembly
WHERE parts_id = $form->{id}|;
@@ -149,13 +152,51 @@ sub get_part {
($form->{orphaned}) = $sth->fetchrow_array;
$form->{orphaned} = !$form->{orphaned};
$sth->finish;
-
+
+
+ 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 = $form->{id}
+ ORDER BY 2|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ 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 = $form->{id}
+ ORDER BY c.name, g.pricegroup, pc.pricebreak|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{customermatrix} }, $ref;
+ }
+ $sth->finish;
+ }
+
$dbh->disconnect;
}
-
sub save {
my ($self, $myconfig, $form) = @_;
@@ -175,50 +216,55 @@ sub save {
# 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 bin);
-
# undo amount formatting
map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } qw(rop weight listprice sellprice lastcost stock);
- # set date to NULL if nothing entered
- $form->{priceupdate} = ($form->{priceupdate}) ? qq|'$form->{priceupdate}'| : "NULL";
+ $form->{lastcost} = $form->{sellprice} if $form->{item} eq 'labor';
$form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
- $form->{alternate} = 0;
$form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
- $form->{obsolete} *= 1;
- $form->{onhand} *= 1;
-
- my ($query, $sth);
+ map { $form->{$_} *= 1 } qw(alternate obsolete onhand);
+
+ my $query;
+ my $sth;
+ my $i;
+ my $null;
+ my $vendor_id;
+ my $customer_id;
if ($form->{id}) {
# get old price
- $query = qq|SELECT sellprice, weight
+ $query = qq|SELECT listprice, sellprice, lastcost, weight
FROM parts
WHERE id = $form->{id}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- my ($sellprice, $weight) = $sth->fetchrow_array;
+ my ($listprice, $sellprice, $lastcost, $weight) = $sth->fetchrow_array;
$sth->finish;
# if item is part of an assembly adjust all assemblies
- $query = qq|SELECT id, qty
+ $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) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $id, $qty * 1, $sellprice * 1, $weight * 1);
+ 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} ne 'service') {
+ 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 ($form->{item} !~ /(service|labor)/) {
# delete makemodel records
$query = qq|DELETE FROM makemodel
WHERE parts_id = $form->{id}|;
@@ -236,20 +282,21 @@ sub save {
WHERE id = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
} else {
- # update BOM only
+ # update BOM, A only
$query = qq|UPDATE assembly
- SET bom = ?
+ SET bom = ?, adj = ?
WHERE id = ?
AND parts_id = ?|;
- $sth = $dbh->prepare($query);
-
+ $sth = $dbh->prepare($query);
+
for $i (1 .. $form->{assembly_rows} - 1) {
- $sth->execute(($form->{"bom_$i"}) ? '1' : '0', $form->{id}, $form->{"id_$i"}) || $form->dberror($query);
+ $sth->execute(($form->{"bom_$i"}) ? '1' : '0', ($form->{"adj_$i"}) ? '1' : '0', $form->{id}, $form->{"id_$i"});
+ $sth->finish;
}
- $sth->finish;
}
-
+
$form->{onhand} += $form->{stock};
+
}
# delete tax records
@@ -257,6 +304,11 @@ sub save {
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);
+
} else {
my $uid = time;
$uid .= $form->{login};
@@ -277,37 +329,16 @@ sub save {
$form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly';
}
-
- my $partsgroup_id = 0;
- if ($form->{partsgroup}) {
- my $partsgroup = lc $form->{partsgroup};
- $query = qq|SELECT DISTINCT id FROM partsgroup
- WHERE lower(partsgroup) = '$partsgroup'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- ($partsgroup_id) = $sth->fetchrow_array;
- $sth->finish;
-
- if (!$partsgroup_id) {
- $query = qq|INSERT INTO partsgroup (partsgroup)
- VALUES ('$form->{partsgroup}')|;
- $dbh->do($query) || $form->dberror($query);
+ my $partsgroup_id;
+ ($null, $partsgroup_id) = split /--/, $form->{partsgroup};
+ $partsgroup_id *= 1;
- $query = qq|SELECT id FROM partsgroup
- WHERE partsgroup = '$form->{partsgroup}'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $form->{partnumber} = $form->update_defaults($myconfig, "partnumber", $dbh) if ! $form->{partnumber};
- ($partsgroup_id) = $sth->fetchrow_array;
- $sth->finish;
- }
- }
-
-
- $query = qq|UPDATE parts SET
- partnumber = '$form->{partnumber}',
- description = '$form->{description}',
+ $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}',
@@ -315,11 +346,11 @@ sub save {
sellprice = $form->{sellprice},
lastcost = $form->{lastcost},
weight = $form->{weight},
- priceupdate = $form->{priceupdate},
- unit = '$form->{unit}',
- notes = '$form->{notes}',
+ priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|,
+ unit = |.$dbh->quote($form->{unit}).qq|,
+ notes = |.$dbh->quote($form->{notes}).qq|,
rop = $form->{rop},
- bin = '$form->{bin}',
+ 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
@@ -336,15 +367,13 @@ sub save {
# insert makemodel records
- unless ($form->{item} eq 'service') {
- for my $i (1 .. $form->{makemodel_rows}) {
- # put make and model together
+ if ($form->{item} =~ /(part|assembly)/) {
+ for $i (1 .. $form->{makemodel_rows}) {
if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
- map { $form->{"${_}_$i"} =~ s/'/''/g } qw(make model);
-
- $query = qq|INSERT INTO makemodel (parts_id, name)
- VALUES ($form->{id},
- '$form->{"make_$i"}:$form->{"model_$i"}')|;
+ $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);
}
}
@@ -367,27 +396,88 @@ sub save {
if ($form->{item} eq 'assembly') {
if ($form->{orphaned}) {
- for my $i (1 .. $form->{assembly_rows}) {
+ for $i (1 .. $form->{assembly_rows}) {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
if ($form->{"qty_$i"} != 0) {
- $form->{"bom_$i"} *= 1;
- $query = qq|INSERT INTO assembly (id, parts_id, qty, bom)
+ map { $form->{"${_}_$i"} *= 1 } qw(bom adj);
+ $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj)
VALUES ($form->{id}, $form->{"id_$i"},
- $form->{"qty_$i"}, '$form->{"bom_$i"}')|;
+ $form->{"qty_$i"}, '$form->{"bom_$i"}',
+ '$form->{"adj_$i"}')|;
$dbh->do($query) || $form->dberror($query);
}
}
}
- # adjust onhand for the assembly
+ # adjust onhand for the parts
if ($form->{onhand} != 0) {
&adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
}
+
+ @a = localtime; $a[5] += 1900; $a[4]++;
+ my $shippingdate = "$a[5]-$a[4]-$a[3]";
+ ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
+
+ # add inventory record
+ if ($form->{stock} != 0) {
+ $query = qq|INSERT INTO inventory (warehouse_id, parts_id, qty,
+ shippingdate, employee_id) VALUES (
+ 0, $form->{id}, $form->{stock}, '$shippingdate',
+ $form->{employee_id})|;
+ $dbh->do($query) || $form->dberror($query);
+ }
+
+ }
+
+
+ # add vendors
+ if ($form->{item} ne 'assembly') {
+ for $i (1 .. $form->{vendor_rows}) {
+ if ($form->{"vendor_$i"} && $form->{"lastcost_$i"}) {
+
+ ($null, $vendor_id) = split /--/, $form->{"vendor_$i"};
+
+ map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})} qw(lastcost leadtime);
+
+ $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);
+ }
+ }
+ }
+
+
+ # add pricematrix
+ for $i (1 .. $form->{customer_rows}) {
+
+ map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})} qw(pricebreak customerprice);
+
+ if ($form->{"customerprice_$i"}) {
+
+ ($null, $customer_id) = split /--/, $form->{"customer_$i"};
+ $customer_id *= 1;
+
+ ($null, $pricegroup_id) = split /--/, $form->{"pricegroup_$i"};
+ $pricegroup_id *= 1;
+
+ $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);
+ }
}
-
# commit
my $rc = $dbh->commit;
$dbh->disconnect;
@@ -399,27 +489,43 @@ sub save {
sub update_assembly {
- my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
+ my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost, $weight) = @_;
- my $query = qq|SELECT id, qty
+ my $formlistprice = $form->{listprice};
+ my $formsellprice = $form->{sellprice};
+
+ if (!$adj) {
+ $formlistprice = $listprice;
+ $formsellprice = $sellprice;
+ }
+
+ my $query = qq|SELECT id, qty, adj
FROM assembly
- WHERE parts_id = $id|;
+ WHERE parts_id = $id|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my ($pid, $aqty) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
+ $form->{$id} = 1;
+
+ 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;
$query = qq|UPDATE parts
- SET sellprice = sellprice +
- $qty * ($form->{sellprice} - $sellprice),
+ 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};
+
}
@@ -443,6 +549,15 @@ sub retrieve_assemblies {
}
$where .= " AND NOT p.obsolete = '1'";
+ my %ordinal = ( 'partnumber' => 2,
+ 'description' => 3,
+ 'bin' => 4
+ );
+
+ 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,
@@ -452,13 +567,58 @@ sub retrieve_assemblies {
AND a.id = p.id) AS inventory
FROM parts p
WHERE $where
- AND assembly = '1'|;
+ AND assembly = '1'
+ ORDER BY $sortorder|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{assembly_items} }, $ref if $ref->{inventory};
+ 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 a.id = ?|;
+ $inh = $dbh->prepare($query) || $form->dberror($query);
+ }
+
+ my $onhand = ();
+ my $ref;
+ my $aref;
+ my $stock;
+ my $howmany;
+ my $ok;
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ if ($ref->{inventory}) {
+ $ok = 1;
+ if ($form->{checkinventory}) {
+ $inh->execute($ref->{id}) || $form->dberror($query);;
+ $ok = 0;
+ while ($aref = $inh->fetchrow_hashref(NAME_lc)) {
+ $onhand{$aref->{id}} = (exists $onhand{$aref->{id}}) ? $onhand{$aref->{id}} : $aref->{onhand};
+
+ if ($aref->{onhand} >= $aref->{qty}) {
+
+ $howmany = ($aref->{qty}) ? $aref->{onhand}/$aref->{qty} : 1;
+ if ($stock) {
+ $stock = ($stock > $howmany) ? $howmany : $stock;
+ } else {
+ $stock = $howmany;
+ }
+ $ok = 1;
+
+ $onhand{$aref->{id}} -= ($aref->{qty} * $stock);
+
+ } else {
+ $ok = 0;
+ last;
+ }
+ }
+ $inh->finish;
+ $ref->{stock} = (($ref->{rop} - $ref->{qty}) > $stock) ? int $stock : $ref->{rop};
+ }
+ push @{ $form->{assembly_items} }, $ref if $ok;
+ }
}
$sth->finish;
@@ -472,6 +632,11 @@ sub restock_assemblies {
# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
+
+ @a = localtime; $a[5] += 1900; $a[4]++;
+ my $shippingdate = "$a[5]-$a[4]-$a[3]";
+
+ ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
for my $i (1 .. $form->{rowcount}) {
@@ -480,9 +645,18 @@ sub restock_assemblies {
if ($form->{"qty_$i"} != 0) {
&adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
}
+
+ # add inventory record
+ if ($form->{"qty_$i"} != 0) {
+ $query = qq|INSERT INTO inventory (warehouse_id, parts_id, qty,
+ shippingdate, employee_id) VALUES (
+ 0, $form->{"id_$i"}, $form->{"qty_$i"}, '$shippingdate',
+ $form->{employee_id})|;
+ $dbh->do($query) || $form->dberror($query);
+ }
}
-
+
my $rc = $dbh->commit;
$dbh->disconnect;
@@ -495,9 +669,9 @@ 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|;
+ 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);
@@ -505,9 +679,10 @@ sub adjust_inventory {
my $allocate = $qty * $ref->{qty};
- # is it a service item, then loop
- $ref->{inventory_accno_id} *= 1;
- next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
+ # is it a service item then loop
+ if (($ref->{inventory_accno_id} *= 1) == 0) {
+ next unless $ref->{assembly}; # assembly
+ }
# adjust parts onhand
$form->update_balance($dbh,
@@ -525,7 +700,7 @@ sub adjust_inventory {
"onhand",
qq|id = $id|,
$qty);
-
+
}
@@ -534,11 +709,6 @@ sub delete {
# connect to database, turn off AutoCommit
my $dbh = $form->dbconnect_noauto($myconfig);
-
- if ($form->{item} eq 'assembly' && $form->{onhand} != 0) {
- # adjust onhand for the assembly
- &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
- }
my $query = qq|DELETE FROM parts
WHERE id = $form->{id}|;
@@ -548,14 +718,26 @@ sub delete {
WHERE parts_id = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
+
+ if ($form->{item} ne 'assembly') {
+ $query = qq|DELETE FROM partsvendor
+ WHERE parts_id = $form->{id}|;
+ $dbh->do($query) || $form->dberror($query);
+ }
+
# check if it is a part, assembly or service
- if ($form->{item} eq 'part') {
+ 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);
+
$query = qq|DELETE FROM assembly
WHERE id = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
@@ -566,6 +748,14 @@ sub delete {
WHERE id = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
}
+
+ $query = qq|DELETE FROM partscustomer
+ WHERE parts_id = $form->{id}|;
+ $dbh->do($query) || $form->dberror($query);
+
+ $query = qq|DELETE FROM translation
+ WHERE trans_id = $form->{id}|;
+ $dbh->do($query) || $form->dberror($query);
# commit
my $rc = $dbh->commit;
@@ -581,7 +771,8 @@ sub assembly_item {
my $i = $form->{assembly_rows};
my $var;
- my $where = "1 = 1";
+ my $null;
+ my $where = "p.obsolete = '0'";
if ($form->{"partnumber_$i"}) {
$var = $form->like(lc $form->{"partnumber_$i"});
@@ -592,12 +783,12 @@ sub assembly_item {
$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'";
+ ($null, $var) = split /--/, $form->{"partsgroup_$i"};
+ $where .= qq| AND p.partsgroup_id = $var|;
}
-
+
if ($form->{id}) {
- $where .= " AND NOT p.id = $form->{id}";
+ $where .= " AND p.id != $form->{id}";
}
if ($partnumber) {
@@ -610,8 +801,8 @@ sub assembly_item {
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
+ 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|;
@@ -632,9 +823,12 @@ sub all_parts {
my ($self, $myconfig, $form) = @_;
my $where = '1 = 1';
+ my $null;
my $var;
+ my $ref;
+ my $item;
- foreach my $item (qw(partnumber drawing microfiche)) {
+ foreach $item (qw(partnumber drawing microfiche)) {
if ($form->{$item}) {
$var = $form->like(lc $form->{$item});
$where .= " AND lower(p.$item) LIKE '$var'";
@@ -642,32 +836,46 @@ sub all_parts {
}
# special case for description
if ($form->{description}) {
- unless ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
+ 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'";
}
}
+
+ # assembly components
+ my $assemblyflds;
+ if ($form->{searchitems} eq 'component') {
+ $assemblyflds = qq|, p1.partnumber AS assemblypartnumber, a.id AS assembly_id|;
+ }
+
+ # special case for serialnumber
+ if ($form->{l_serialnumber}) {
+ if ($form->{serialnumber}) {
+ $var = $form->like(lc $form->{serialnumber});
+ $where .= " AND lower(i.serialnumber) LIKE '$var'";
+ }
+ }
+ if ($form->{warehouse} || $form->{l_warehouse}) {
+ $form->{l_warehouse} = 1;
+ }
+
if ($form->{searchitems} eq 'part') {
- $where .= " AND p.inventory_accno_id > 0";
+ $where .= " AND p.inventory_accno_id > 0 AND p.assembly = '0' AND p.income_accno_id > 0";
}
if ($form->{searchitems} eq 'assembly') {
$form->{bought} = "";
$where .= " AND p.assembly = '1'";
}
if ($form->{searchitems} eq 'service') {
- $where .= " AND p.inventory_accno_id IS NULL AND NOT p.assembly = '1'";
- # irrelevant for services
- $form->{make} = $form->{model} = "";
+ $where .= " AND p.inventory_accno_id IS NULL AND p.assembly = '0'";
+ }
+ if ($form->{searchitems} eq 'labor') {
+ $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL";
}
# items which were never bought, sold or on an order
if ($form->{itemstatus} eq 'orphaned') {
- $form->{onhand} = $form->{short} = 0;
- $form->{bought} = $form->{sold} = 0;
- $form->{onorder} = $form->{ordered} = 0;
- $form->{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)
@@ -682,80 +890,174 @@ sub all_parts {
}
if ($form->{itemstatus} eq 'obsolete') {
$where .= " AND p.obsolete = '1'";
- $form->{onhand} = $form->{short} = 0;
}
if ($form->{itemstatus} eq 'onhand') {
$where .= " AND p.onhand > 0";
}
if ($form->{itemstatus} eq 'short') {
- $where .= " AND p.onhand < 0";
+ $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.name) LIKE '$var')";
- }
- if ($form->{model}) {
- $var = "%:".$form->like($form->{model});
- $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
- FROM makemodel m WHERE lower(m.name) LIKE '$var')";
+ my $makemodelflds = qq|, '', ''|;;
+ my $makemodeljoin;
+
+ if ($form->{make} || $form->{l_make} || $form->{model} || $form->{l_model}) {
+ $makemodelflds = qq|, m.make, m.model|;
+ $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
+
+ if ($form->{make}) {
+ $var = $form->like(lc $form->{make});
+ $where .= " AND lower(m.make) LIKE '$var'";
+ }
+ if ($form->{model}) {
+ $var = $form->like(lc $form->{model});
+ $where .= " AND lower(m.model) LIKE '$var'";
+ }
}
if ($form->{partsgroup}) {
- $var = $form->like(lc $form->{partsgroup});
- $where .= " AND lower(pg.partsgroup) LIKE '$var'";
-
+ ($null, $var) = split /--/, $form->{partsgroup};
+ $where .= qq| AND p.partsgroup_id = $var|;
}
# connect to database
my $dbh = $form->dbconnect($myconfig);
+ my %ordinal = ( 'partnumber' => 2,
+ 'description' => 3,
+ 'bin' => 6,
+ 'priceupdate' => 12,
+ 'drawing' => 14,
+ 'microfiche' => 15,
+ 'partsgroup' => 17,
+ 'make' => 19,
+ 'model' => 20,
+ 'assemblypartnumber' => 21
+ );
- my $sortorder = join ', ', $form->sort_columns(qw(partnumber description bin priceupdate partsgroup));
- $sortorder = $form->{sort} unless $sortorder;
+ my @a = qw(partnumber description);
+ my $sortorder = $form->sort_order(\@a, \%ordinal);
+
+ my $query = qq|SELECT curr FROM defaults|;
+ my ($curr) = $dbh->selectrow_array($query);
+ $curr =~ s/:.*//;
+
+ my $flds = qq|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,
+ p.assembly, pg.partsgroup, '$curr' AS curr
+ $makemodelflds $assemblyflds
+ |;
+
+ $query = qq|SELECT $flds
+ FROM parts p
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ $makemodeljoin
+ WHERE $where
+ ORDER BY $sortorder|;
+
+ # redo query for components report
+ if ($form->{searchitems} eq 'component') {
+
+ $query = qq|SELECT $flds
+ FROM assembly a
+ JOIN parts p ON (a.parts_id = p.id)
+ JOIN parts p1 ON (a.id = p1.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ $makemodeljoin
+ WHERE $where
+ ORDER BY $sortorder|;
+
+ }
- my $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
- ORDER BY $sortorder|;
# rebuild query for bought and sold items
- if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
+ if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered} || $form->{rfq} || $form->{quoted}) {
+
+ $form->sort_order();
+ my @a = qw(partnumber description employee);
+
+ 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});
+
+ %ordinal = ( 'partnumber' => 2,
+ 'description' => 3,
+ 'serialnumber' => 4,
+ 'bin' => 7,
+ 'priceupdate' => 13,
+ 'partsgroup' => 18,
+ 'invnumber' => 19,
+ 'ordnumber' => 20,
+ 'quonumber' => 21,
+ 'name' => 23,
+ 'employee' => 24,
+ 'make' => 27,
+ 'model' => 28
+ );
+ $sortorder = $form->sort_order(\@a, \%ordinal);
+
my $union = "";
$query = "";
if ($form->{bought} || $form->{sold}) {
my $invwhere = "$where";
+ my $transdate = ($form->{method} eq 'accrual') ? "transdate" : "datepaid";
+
$invwhere .= " AND i.assemblyitem = '0'";
- $invwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
- $invwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+ $invwhere .= " AND a.$transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+ $invwhere .= " AND a.$transdate <= '$form->{transdateto}'" if $form->{transdateto};
if ($form->{description}) {
$var = $form->like(lc $form->{description});
$invwhere .= " AND lower(i.description) LIKE '$var'";
}
- my $flds = qq|p.id, p.partnumber, i.description,
+ if ($form->{open} || $form->{closed}) {
+ if ($form->{open} && $form->{closed}) {
+ if ($form->{method} eq 'cash') {
+ $invwhere .= " AND a.amount = a.paid";
+ }
+ } else {
+ if ($form->{open}) {
+ if ($form->{method} eq 'cash') {
+ $invwhere .= " AND a.id = 0";
+ } else {
+ $invwhere .= " AND NOT a.amount = a.paid";
+ }
+ } else {
+ $invwhere .= " AND a.amount = a.paid";
+ }
+ }
+ } else {
+ $invwhere .= " AND a.id = 0";
+ }
+
+ 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, i.trans_id|;
+ p.assembly,
+ pg.partsgroup, a.invnumber, a.ordnumber, a.quonumber,
+ i.trans_id, ct.name, e.name AS employee, a.curr, a.till
+ $makemodelfld|;
+
if ($form->{bought}) {
$query = qq|
SELECT $flds, 'ir' AS module, '' AS type,
- 1 AS exchangerate
- FROM parts p
- JOIN invoice i ON (i.parts_id = p.id)
- JOIN ap a ON (i.trans_id = a.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ (SELECT sell FROM exchangerate ex
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.$transdate) AS exchangerate,
+ i.discount
+ 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)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
WHERE $invwhere|;
$union = "
UNION";
@@ -764,11 +1066,17 @@ sub all_parts {
if ($form->{sold}) {
$query .= qq|$union
SELECT $flds, 'is' AS module, '' AS type,
- 1 As exchangerate
- FROM parts p
- JOIN invoice i ON (i.parts_id = p.id)
- JOIN ar a ON (i.trans_id = a.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ (SELECT buy FROM exchangerate ex
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.$transdate) AS exchangerate,
+ i.discount
+ 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)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
WHERE $invwhere|;
$union = "
UNION";
@@ -776,59 +1084,158 @@ sub all_parts {
}
if ($form->{onorder} || $form->{ordered}) {
- my $ordwhere = "$where";
- $ordwhere .= " AND o.closed = '0'" unless $form->{closed};
-
- $ordwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
- $ordwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+ my $ordwhere = "$where
+ AND a.quotation = '0'";
+ $ordwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+ $ordwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
if ($form->{description}) {
$var = $form->like(lc $form->{description});
- $ordwhere .= " AND lower(oi.description) LIKE '$var'";
+ $ordwhere .= " AND lower(i.description) LIKE '$var'";
+ }
+
+ if ($form->{open} || $form->{closed}) {
+ unless ($form->{open} && $form->{closed}) {
+ $ordwhere .= " AND a.closed = '0'" if $form->{open};
+ $ordwhere .= " AND a.closed = '1'" if $form->{closed};
+ }
+ } else {
+ $ordwhere .= " AND a.id = 0";
}
- $flds = qq|p.id, p.partnumber, oi.description,
- oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
+ $flds = qq|p.id, p.partnumber, i.description, '' AS 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,
- '' AS invnumber, o.ordnumber, oi.trans_id|;
+ p.assembly,
+ pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
+ i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till
+ $makemodelfld|;
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 parts p
- JOIN orderitems oi ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.transdate) AS exchangerate,
+ i.discount
+ FROM orderitems i
+ JOIN parts p ON (i.parts_id = p.id)
+ JOIN oe a ON (i.trans_id = a.id)
+ JOIN customer ct ON (a.customer_id = ct.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
WHERE $ordwhere
- AND o.customer_id > 0|;
+ AND a.customer_id > 0|;
$union = "
UNION";
}
if ($form->{onorder}) {
- $flds = qq|p.id, p.partnumber, oi.description,
- oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
+ $flds = qq|p.id, p.partnumber, i.description, '' AS 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,
- '' AS invnumber, o.ordnumber, oi.trans_id|;
+ p.assembly,
+ pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
+ i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till
+ $makemodelfld|;
$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 parts p
- JOIN orderitems oi ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.transdate) AS exchangerate,
+ i.discount
+ FROM orderitems i
+ JOIN parts p ON (i.parts_id = p.id)
+ JOIN oe a ON (i.trans_id = a.id)
+ JOIN vendor ct ON (a.vendor_id = ct.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
WHERE $ordwhere
- AND o.vendor_id > 0|;
+ AND a.vendor_id > 0|;
+ }
+
+ }
+
+ if ($form->{rfq} || $form->{quoted}) {
+ my $quowhere = "$where
+ AND a.quotation = '1'";
+ $quowhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+ $quowhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+
+ if ($form->{description}) {
+ $var = $form->like(lc $form->{description});
+ $quowhere .= " AND lower(i.description) LIKE '$var'";
+ }
+
+ if ($form->{open} || $form->{closed}) {
+ unless ($form->{open} && $form->{closed}) {
+ $ordwhere .= " AND a.closed = '0'" if $form->{open};
+ $ordwhere .= " AND a.closed = '1'" if $form->{closed};
+ }
+ } else {
+ $ordwhere .= " AND a.id = 0";
+ }
+
+
+ $flds = qq|p.id, p.partnumber, i.description, '' AS 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,
+ p.assembly,
+ pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
+ i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till
+ $makemodelfld|;
+
+ if ($form->{quoted}) {
+ $query .= qq|$union
+ SELECT $flds, 'oe' AS module, 'sales_quotation' AS type,
+ (SELECT buy FROM exchangerate ex
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.transdate) AS exchangerate,
+ i.discount
+ FROM orderitems i
+ JOIN parts p ON (i.parts_id = p.id)
+ JOIN oe a ON (i.trans_id = a.id)
+ JOIN customer ct ON (a.customer_id = ct.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
+ WHERE $quowhere
+ AND a.customer_id > 0|;
+ $union = "
+ UNION";
+ }
+
+ if ($form->{rfq}) {
+ $flds = qq|p.id, p.partnumber, i.description, '' AS 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,
+ p.assembly,
+ pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
+ i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till
+ $makemodelfld|;
+
+ $query .= qq|$union
+ SELECT $flds, 'oe' AS module, 'request_quotation' AS type,
+ (SELECT sell FROM exchangerate ex
+ WHERE ex.curr = a.curr
+ AND ex.transdate = a.transdate) AS exchangerate,
+ i.discount
+ FROM orderitems i
+ JOIN parts p ON (i.parts_id = p.id)
+ JOIN oe a ON (i.trans_id = a.id)
+ JOIN vendor ct ON (a.vendor_id = ct.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ LEFT JOIN employee e ON (a.employee_id = e.id)
+ $makemodeljoin
+ WHERE $quowhere
+ AND a.vendor_id > 0|;
}
}
@@ -838,45 +1245,99 @@ sub all_parts {
}
+
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
push @{ $form->{parts} }, $ref;
}
-
$sth->finish;
-
- # include individual items for assemblies
+ my @a = ();
+
+ # include individual items for assembly
if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
+
+ if ($form->{sold} || $form->{ordered} || $form->{quoted}) {
+ $flds = qq|p.id, p.partnumber, p.description, a.qty AS onhand, p.unit,
+ p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
+ p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
+ p.assembly, pg.partsgroup
+ $makemodelflds $assemblyflds
+ |;
+ } else {
+ # replace p.onhand with a.qty AS onhand
+ $flds =~ s/p.onhand/a.qty AS onhand/;
+ }
+
+ while ($item = shift @{ $form->{parts} }) {
+ push @a, $item;
+ $flds =~ s/a\.qty.*AS onhand/a\.qty * $item->{onhand} AS onhand/;
+ push @a, &include_assembly($dbh, $form, $item->{id}, $flds, $makemodeljoin);
+ push @a, {id => $item->{id}};
+ }
+
+ # copy assemblies to $form->{parts}
+ @{ $form->{parts} } = @a;
+
+ }
+
+
+ @a = ();
+ if ($form->{l_warehouse} || $form->{l_warehouse}) {
+
+ if ($form->{warehouse}) {
+ ($null, $var) = split /--/, $form->{warehouse};
+ $var *= 1;
+ $query = qq|SELECT SUM(qty) AS onhand, '$null' AS description
+ FROM inventory
+ WHERE warehouse_id = $var
+ AND parts_id = ?|;
+ } else {
+
+ $query = qq|SELECT SUM(i.qty) AS onhand, w.description AS warehouse
+ FROM inventory i
+ JOIN warehouse w ON (w.id = i.warehouse_id)
+ WHERE i.parts_id = ?
+ GROUP BY w.description|;
+ }
+
+ $sth = $dbh->prepare($query) || $form->dberror($query);
+
foreach $item (@{ $form->{parts} }) {
- push @assemblies, $item;
- $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
- p.unit, p.bin,
- p.sellprice, p.listprice, p.lastcost,
- p.rop, p.weight, p.priceupdate,
- p.image, p.drawing, p.microfiche
- FROM parts p
- JOIN assembly a ON (p.id = a.parts_id)
- WHERE a.id = $item->{id}|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{assemblyitem} = 1;
- push @assemblies, $ref;
+ if ($item->{onhand} <= 0 && ! $form->{warehouse}) {
+ push @a, $item;
+ next;
}
- $sth->finish;
- push @assemblies, {id => $item->{id}};
+ $sth->execute($item->{id}) || $form->dberror($query);
+
+ if ($form->{warehouse}) {
+
+ $ref = $sth->fetchrow_hashref(NAME_lc);
+ if ($ref->{onhand} > 0) {
+ $item->{onhand} = $ref->{onhand};
+ push @a, $item;
+ }
+ } else {
+
+ push @a, $item;
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ if ($ref->{onhand} > 0) {
+ push @a, $ref;
+ }
+ }
+ }
+
+ $sth->finish;
}
- # copy assemblies to $form->{parts}
- @{ $form->{parts} } = @assemblies;
-
+ @{ $form->{parts} } = @a;
+
}
$dbh->disconnect;
@@ -884,11 +1345,54 @@ sub all_parts {
}
+sub include_assembly {
+ my ($dbh, $form, $id, $flds, $makemodeljoin) = @_;
+
+ $form->{stagger}++;
+ if ($form->{stagger} > $form->{pncol}) {
+ $form->{pncol} = $form->{stagger};
+ }
+
+ $form->{$id} = 1;
+
+ my @a = ();
+ my $query = qq|SELECT $flds
+ FROM parts p
+ JOIN assembly a ON (a.parts_id = p.id)
+ LEFT JOIN partsgroup pg ON (pg.id = p.id)
+ $makemodeljoin
+ WHERE a.id = $id|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $ref->{assemblyitem} = 1;
+ $ref->{stagger} = $form->{stagger};
+ push @a, $ref;
+ if ($ref->{assembly} && !$form->{$ref->{id}}) {
+ push @a, &include_assembly($dbh, $form, $ref->{id}, $flds, $makemodeljoin);
+ if ($form->{stagger} > $form->{pncol}) {
+ $form->{pncol} = $form->{stagger};
+ }
+ }
+ }
+ $sth->finish;
+
+ $form->{$id} = 0;
+ $form->{stagger}--;
+
+ @a;
+
+}
+
+
sub create_links {
my ($self, $module, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->dbconnect($myconfig);
+
+ my $ref;
my $query = qq|SELECT accno, description, link
FROM chart
@@ -897,33 +1401,86 @@ sub create_links {
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- foreach my $key (split(/:/, $ref->{link})) {
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ foreach my $key (split /:/, $ref->{link}) {
if ($key =~ /$module/) {
push @{ $form->{"${module}_links"}{$key} }, { accno => $ref->{accno},
description => $ref->{description} };
}
}
}
+ $sth->finish;
+
+ if ($form->{item} ne 'assembly') {
+ $query = qq|SELECT count(*) FROM vendor|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+ my ($count) = $sth->fetchrow_array;
+ $sth->finish;
+
+ if ($count < $myconfig->{vclimit}) {
+ $query = qq|SELECT id, name
+ FROM vendor
+ ORDER BY name|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_vendor} }, $ref;
+ }
+ $sth->finish;
+ }
+ }
+
+ # pricegroups, customers
+ $query = qq|SELECT count(*) FROM customer|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+ my ($count) = $sth->fetchrow_array;
$sth->finish;
+ if ($count < $myconfig->{vclimit}) {
+ $query = qq|SELECT id, name
+ FROM customer
+ ORDER BY name|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_customer} }, $ref;
+ }
+ $sth->finish;
+ }
+
+ $query = qq|SELECT id, pricegroup
+ FROM pricegroup
+ ORDER BY pricegroup|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_pricegroup} }, $ref;
+ }
+ $sth->finish;
+
+
if ($form->{id}) {
- $query = qq|SELECT weightunit
+ $query = qq|SELECT weightunit, curr AS currencies
FROM defaults|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- ($form->{weightunit}) = $sth->fetchrow_array;
+ ($form->{weightunit}, $form->{currencies}) = $sth->fetchrow_array;
$sth->finish;
} else {
- $query = qq|SELECT weightunit, current_date
+ $query = qq|SELECT weightunit, current_date, curr AS currencies
FROM defaults|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- ($form->{weightunit}, $form->{priceupdate}) = $sth->fetchrow_array;
+ ($form->{weightunit}, $form->{priceupdate}, $form->{currencies}) = $sth->fetchrow_array;
$sth->finish;
}
@@ -932,5 +1489,25 @@ sub create_links {
}
+sub get_warehouses {
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT id, description
+ FROM warehouse|;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_warehouses} }, $ref;
+ }
+ $sth->finish;
+
+ $dbh->disconnect;
+
+}
+
1;
generated by cgit v1.2.1 (git 2.18.0) at 2025-01-11 22:00:18 +0000