my ($null, $department_id) = split /--/, $form->{department};
my $department = " AND o.department_id = $department_id" if $department_id;
+ my @values;
+
+ if ($form->{"project_id"}) {
+ $department .=
+ qq|AND ((globalproject_id = ?) OR EXISTS | .
+ qq| (SELECT * FROM orderitems oi | .
+ qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
+ push(@values, $form->{"project_id"}, $form->{"project_id"});
+ }
my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
o.amount, ct.name, o.netamount, o.$form->{vc}_id,
+ o.globalproject_id, pr.projectnumber AS globalprojectnumber,
ex.$rate AS exchangerate,
o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia,
e.name AS employee
LEFT JOIN employee e ON (o.employee_id = e.id)
LEFT JOIN exchangerate ex ON (ex.curr = o.curr
AND ex.transdate = o.transdate)
+ LEFT JOIN project pr ON o.globalproject_id = pr.id
WHERE o.quotation = '$quotation'
$department|;
- # build query if type eq (ship|receive)_order
- if ($form->{type} =~ /(ship|receive)_order/) {
- my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse};
-
- $query = qq|SELECT DISTINCT ON (o.id) o.id, o.ordnumber, o.transdate,
- o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id,
- ex.$rate AS exchangerate,
- o.closed, o.quonumber, o.shippingpoint, o.shipvia,
- e.name AS employee
- FROM oe o
- JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
- JOIN orderitems oi ON (oi.trans_id = o.id)
- JOIN parts p ON (p.id = oi.parts_id)|;
-
- if ($warehouse_id && $form->{type} eq 'ship_order') {
- $query .= qq|
- JOIN inventory i ON (oi.parts_id = i.parts_id)
- |;
- }
-
- $query .= qq|
- LEFT JOIN employee e ON (o.employee_id = e.id)
- LEFT JOIN exchangerate ex ON (ex.curr = o.curr
- AND ex.transdate = o.transdate)
- WHERE o.quotation = '0'
- AND (p.inventory_accno_id > 0 OR p.assembly = '1')
- AND oi.qty <> oi.ship
- $department|;
-
- if ($warehouse_id && $form->{type} eq 'ship_order') {
- $query .= qq|
- AND i.warehouse_id = $warehouse_id
- AND i.qty >= (oi.qty - oi.ship)
- |;
- }
-
- }
-
if ($form->{"$form->{vc}_id"}) {
$query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
} else {
$query .= " ORDER by $sortorder";
my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute(@values) ||
+ $form->dberror($query . " (" . join(", ", @values) . ")");
+
+ dump_query(0, "laqje", $query, @values);
my %id = ();
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
$netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
- $project_id = 'NULL';
- if ($form->{"projectnumber_$i"}) {
- $project_id = $form->{"projectnumber_$i"};
- }
$reqdate =
($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL";
$query .= qq|$form->{id}, $form->{"id_$i"},
'$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"}, $baseqty,
$fxsellprice, $form->{"discount_$i"},
- '$form->{"unit_$i"}', $reqdate, (SELECT id from project where projectnumber = '$project_id'),
+ '$form->{"unit_$i"}', $reqdate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
'$form->{"serialnumber_$i"}', $form->{"ship_$i"}, '$pricegroup_id',
'$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', '$subtotal')|;
$dbh->do($query) || $form->dberror($query);
payment_id = $form->{payment_id},
delivery_vendor_id = $form->{delivery_vendor_id},
delivery_customer_id = $form->{delivery_customer_id},
+ globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
employee_id = $form->{employee_id},
cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
WHERE id = $form->{id}|;
# adjust onhand
&adj_onhand($dbh, $form, $ml * -1);
- &adj_inventory($dbh, $myconfig, $form);
}
my $rc = $dbh->commit;
o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
+ o.globalproject_id,
o.delivered
FROM oe o
JOIN $form->{vc} cv ON (o.$form->{vc}_id = cv.id)
my %oid = ('Pg' => 'oid',
'Oracle' => 'rowid');
+ my (@project_ids, %projectnumbers);
+
+ push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
+
# sort items by partsgroup
for $i (1 .. $form->{rowcount}) {
$partsgroup = "";
$partsgroup = $form->{"partsgroup_$i"};
}
push @partsgroup, [$i, $partsgroup];
+ push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
}
- # if there is a warehouse limit picking
- if ($form->{warehouse_id} && $form->{formname} =~ /(pick|packing)_list/) {
-
- # run query to check for inventory
- $query = qq|SELECT sum(i.qty) AS qty
- FROM inventory i
- WHERE i.parts_id = ?
- AND i.warehouse_id = ?|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for $i (1 .. $form->{rowcount}) {
- $sth->execute($form->{"id_$i"}, $form->{warehouse_id}) || $form->dberror;
-
- ($qty) = $sth->fetchrow_array;
- $sth->finish;
-
- $form->{"qty_$i"} = 0 if $qty == 0;
-
- if ($form->parse_amount($myconfig, $form->{"ship_$i"}) > $qty) {
- $form->{"ship_$i"} = $form->format_amount($myconfig, $qty);
- }
+ if (@project_ids) {
+ $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
+ join(", ", map({ "?" } @project_ids)) . ")";
+ $sth = $dbh->prepare($query);
+ $sth->execute(@project_ids) ||
+ $form->dberror($query . " (" . join(", ", @project_ids) . ")");
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $projectnumbers{$ref->{id}} = $ref->{projectnumber};
}
+ $sth->finish();
}
+ $form->{"globalprojectnumber"} =
+ $projectnumbers{$form->{"globalproject_id"}};
+
+ my @arrays =
+ qw(runningnumber number description longdescription qty ship unit bin
+ partnotes serialnumber reqdate sellprice listprice netprice
+ discount p_discount discount_sub nodiscount_sub
+ linetotal nodiscount_linetotal tax_rate projectnumber);
+
my $sameitem = "";
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];
push(@{ $form->{description} }, qq|$item->[1]|);
$sameitem = $item->[1];
- map { push(@{ $form->{$_} }, "") }
- qw(runningnumber number qty ship unit bin partnotes
- serialnumber reqdate sellprice listprice netprice
- discount p_discount linetotal);
+ map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
push(@{ $form->{p_discount} }, $form->{"discount_$i"});
$form->{ordtotal} += $linetotal;
- $discount_subtotal += $linetotal;
+ $discount_subtotal += $linetotal;
$form->{nodiscount_total} += $nodiscount_linetotal;
$nodiscount_subtotal += $nodiscount_linetotal;
$form->{discount_total} += $form->parse_amount($myconfig, $discount);
push(@{ $form->{nodiscount_linetotal} },
$form->format_amount($myconfig, $nodiscount_linetotal, 2));
+ push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
+
my ($taxamount, $taxbase);
my $taxrate = 0;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
- map { push(@{ $form->{$_} }, "") }
- qw(runningnumber ship bin serialnumber number unit bin qty
- reqdate sellprice listprice netprice discount p_discount
- linetotal nodiscount_linetotal);
+ map({ push(@{ $form->{$_} }, "") }
+ grep({ $_ ne "description" } @arrays));
$sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
push(@{ $form->{description} }, $sameitem);
}
)
. qq|, $ref->{partnumber}, $ref->{description}|);
- map { push(@{ $form->{$_} }, "") }
- qw(number unit qty runningnumber ship bin serialnumber reqdate
- sellprice listprice netprice discount p_discount linetotal
- nodiscount_linetotal);
-
+ map({ push(@{ $form->{$_} }, "") }
+ grep({ $_ ne "description" } @arrays));
}
$sth->finish;
}
my $tax = 0;
foreach $item (sort keys %taxaccounts) {
- push(@{ $form->{taxbase} },
- $form->format_amount($myconfig, $taxbase{$item}, 2));
+ push(@{ $form->{taxbase} },
+ $form->format_amount($myconfig, $taxbase{$item}, 2));
- $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
+ $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
- push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
- push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
- push(@{ $form->{taxrate} },
- $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
- push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
+ push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
+ push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
+ push(@{ $form->{taxrate} },
+ $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
+ push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
}
$form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
$yesdiscount = $form->{nodiscount_total} - $nodiscount;
$form->set_payment_options($myconfig, $form->{orddate});
}
- # myconfig variables
- map { $form->{$_} = $myconfig->{$_} }
- (qw(company address tel fax signature businessnumber));
$form->{username} = $myconfig->{name};
$dbh->disconnect;
return $_;
}
-sub get_warehouses {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->dbconnect($myconfig);
-
- # setup warehouses
- 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;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_inventory {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my ($null, $warehouse_id) = split /--/, $form->{warehouse};
- $warehouse_id *= 1;
-
- my $employee_id;
- ($null, $employee_id) = split /--/, $form->{employee};
-
- my $ml = ($form->{type} eq 'ship_order') ? -1 : 1;
-
- my $dbh = $form->dbconnect_noauto($myconfig);
- my $sth;
- my $wth;
- my $serialnumber;
- my $ship;
-
- $query = qq|SELECT o.serialnumber, o.ship
- FROM orderitems o
- WHERE o.trans_id = ?
- AND o.id = ?
- FOR UPDATE|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- $query = qq|SELECT sum(i.qty)
- FROM inventory i
- WHERE i.parts_id = ?
- AND i.warehouse_id = ?|;
- $wth = $dbh->prepare($query) || $form->dberror($query);
-
- for my $i (1 .. $form->{rowcount} - 1) {
-
- $ship =
- (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"}))
- ? $form->{"qty_$i"}
- : $form->{"ship_$i"};
-
- if ($warehouse_id && $form->{type} eq 'ship_order') {
-
- $wth->execute($form->{"id_$i"}, $warehouse_id) || $form->dberror;
-
- ($qty) = $wth->fetchrow_array;
- $wth->finish;
-
- if ($ship > $qty) {
- $ship = $qty;
- }
- }
-
- if ($ship != 0) {
-
- $ship *= $ml;
- $query = qq|INSERT INTO inventory (parts_id, warehouse_id,
- qty, oe_id, orderitems_id, shippingdate, employee_id)
- VALUES ($form->{"id_$i"}, $warehouse_id,
- $ship, $form->{"id"},
- $form->{"orderitems_id_$i"}, '$form->{shippingdate}',
- $employee_id)|;
- $dbh->do($query) || $form->dberror($query);
-
- # add serialnumber, ship to orderitems
- $sth->execute($form->{id}, $form->{"orderitems_id_$i"})
- || $form->dberror;
- ($serialnumber, $ship) = $sth->fetchrow_array;
- $sth->finish;
-
- $serialnumber .= " " if $serialnumber;
- $serialnumber .= qq|$form->{"serialnumber_$i"}|;
- $ship += $form->{"ship_$i"};
-
- $query = qq|UPDATE orderitems SET
- serialnumber = '$serialnumber',
- ship = $ship
- WHERE trans_id = $form->{id}
- AND id = $form->{"orderitems_id_$i"}|;
- $dbh->do($query) || $form->dberror($query);
-
- # update order with ship via
- $query = qq|UPDATE oe SET
- shippingpoint = '$form->{shippingpoint}',
- shipvia = '$form->{shipvia}'
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- # update onhand for parts
- $form->update_balance($dbh, "parts", "onhand",
- qq|id = $form->{"id_$i"}|,
- $form->{"ship_$i"} * $ml);
-
- }
- }
-
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
sub adj_onhand {
$main::lxdebug->enter_sub();
$main::lxdebug->leave_sub();
}
-sub adj_inventory {
- $main::lxdebug->enter_sub();
-
- my ($dbh, $myconfig, $form) = @_;
-
- my %oid = ('Pg' => 'oid',
- 'Oracle' => 'rowid');
-
- # increase/reduce qty in inventory table
- my $query = qq|SELECT oi.id, oi.parts_id, oi.ship
- FROM orderitems oi
- WHERE oi.trans_id = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $query = qq|SELECT $oid{$myconfig->{dbdriver}} AS oid, qty,
- (SELECT SUM(qty) FROM inventory
- WHERE oe_id = $form->{id}
- AND orderitems_id = ?) AS total
- FROM inventory
- WHERE oe_id = $form->{id}
- AND orderitems_id = ?|;
- my $ith = $dbh->prepare($query) || $form->dberror($query);
-
- my $qty;
- my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1;
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- $ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query);
-
- while (my $inv = $ith->fetchrow_hashref(NAME_lc)) {
-
- if (($qty = (($inv->{total} * $ml) - $ref->{ship})) >= 0) {
- $qty = $inv->{qty} if ($qty > ($inv->{qty} * $ml));
-
- $form->update_balance($dbh, "inventory", "qty",
- qq|$oid{$myconfig->{dbdriver}} = $inv->{oid}|,
- $qty * -1 * $ml);
- }
- }
- $ith->finish;
-
- }
- $sth->finish;
-
- # delete inventory entries if qty = 0
- $query = qq|DELETE FROM inventory
- WHERE oe_id = $form->{id}
- AND qty = 0|;
- $dbh->do($query) || $form->dberror($query);
-
- $main::lxdebug->leave_sub();
-}
-
-sub get_inventory {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my ($null, $warehouse_id) = split /--/, $form->{warehouse};
- $warehouse_id *= 1;
-
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand,
- pg.partsgroup
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE p.onhand > 0|;
-
- if ($form->{partnumber}) {
- $var = $form->like(lc $form->{partnumber});
- $query .= "
- AND lower(p.partnumber) LIKE '$var'";
- }
- if ($form->{description}) {
- $var = $form->like(lc $form->{description});
- $query .= "
- AND lower(p.description) LIKE '$var'";
- }
- if ($form->{partsgroup}) {
- $var = $form->like(lc $form->{partsgroup});
- $query .= "
- AND lower(pg.partsgroup) LIKE '$var'";
- }
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $query = qq|SELECT sum(i.qty), w.description, w.id
- FROM inventory i
- LEFT JOIN warehouse w ON (w.id = i.warehouse_id)
- WHERE i.parts_id = ?
- AND NOT i.warehouse_id = $warehouse_id
- GROUP BY w.description, w.id|;
- $wth = $dbh->prepare($query) || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- $wth->execute($ref->{id}) || $form->dberror;
-
- while (($qty, $warehouse, $warehouse_id) = $wth->fetchrow_array) {
- push @{ $form->{all_inventory} },
- { 'id' => $ref->{id},
- 'partnumber' => $ref->{partnumber},
- 'description' => $ref->{description},
- 'partsgroup' => $ref->{partsgroup},
- 'qty' => $qty,
- 'warehouse_id' => $warehouse_id,
- 'warehouse' => $warehouse }
- if $qty > 0;
- }
- $wth->finish;
- }
- $sth->finish;
-
- $dbh->disconnect;
-
- # sort inventory
- @{ $form->{all_inventory} } =
- sort { $a->{ $form->{sort} } cmp $b->{ $form->{sort} } }
- @{ $form->{all_inventory} };
-
- $main::lxdebug->leave_sub();
-
- return @{ $form->{all_inventory} };
-}
-
-sub transfer {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- my $query = qq|INSERT INTO inventory
- (warehouse_id, parts_id, qty, shippingdate, employee_id)
- VALUES (?, ?, ?, ?, ?)|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- $form->get_employee($dbh);
-
- my @a = localtime;
- $a[5] += 1900;
- $a[4]++;
- $shippingdate = "$a[5]-$a[4]-$a[3]";
-
- for my $i (1 .. $form->{rowcount}) {
- $qty = $form->parse_amount($myconfig, $form->{"transfer_$i"});
-
- $qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"});
-
- if ($qty) {
-
- # to warehouse
- $sth->execute($form->{warehouse_id}, $form->{"id_$i"}, $qty,
- $shippingdate, $form->{employee_id})
- || $form->dberror;
-
- $sth->finish;
-
- # from warehouse
- $sth->execute($form->{"warehouse_id_$i"},
- $form->{"id_$i"}, $qty * -1, $shippingdate,
- $form->{employee_id})
- || $form->dberror;
-
- $sth->finish;
- }
- }
-
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
1;