#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version. For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2001
#
# Author: DWS Systems Inc.
# Web: http://www.sql-ledger.org
#
# Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup
#
#======================================================================
#
# Order entry module
# Quotation
#
#======================================================================
package OE;
use LedgerSMB::Tax;
use LedgerSMB::Sysconfig;
sub transactions {
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->{dbh};
my $query;
my $null;
my $var;
my $ordnumber = 'ordnumber';
my $quotation = '0';
my $department;
my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
($form->{transdatefrom}, $form->{transdateto}) =
$form->from_to($form->{year}, $form->{month}, $form->{interval})
if $form->{year} && $form->{month};
if ($form->{type} =~ /_quotation$/) {
$quotation = '1';
$ordnumber = 'quonumber';
}
my $number = $form->like(lc $form->{$ordnumber});
my $name = $form->like(lc $form->{$form->{vc}});
my @dptargs = ();
for (qw(department employee)) {
if ($form->{$_}) {
($null, $var) = split /--/, $form->{$_};
$department .= " AND o.${_}_id = ?";
push @dptargs, $var;
}
}
if ($form->{vc} ne 'customer'){ # Sanitize $form->{vc}
$form->{vc} = 'vendor';
}
my $query = qq|
SELECT 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,
m.name AS manager, o.curr, o.ponumber
FROM oe o
JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
LEFT JOIN employee e ON (o.employee_id = e.id)
LEFT JOIN employee m ON (e.managerid = m.id)
LEFT JOIN exchangerate ex
ON (ex.curr = o.curr AND ex.transdate = o.transdate)
WHERE o.quotation = ?
$department|;
my @queryargs = @dptargs;
unshift @queryargs, $quotation;
my %ordinal = (
id => 1,
ordnumber => 2,
transdate => 3,
reqdate => 4,
name => 6,
quonumber => 11,
shipvia => 13,
employee => 14,
manager => 15,
curr => 16,
ponumber => 17);
my @a = (transdate, $ordnumber, name);
push @a, "employee" if $form->{l_employee};
if ($form->{type} !~ /(ship|receive)_order/) {
push @a, "manager" if $form->{l_manager};
}
my $sortorder = $form->sort_order(\@a, \%ordinal);
# 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 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, o.curr,
o.ponumber
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|;
@queryargs = @dptargs; #reset @queryargs
if ($warehouse_id && $form->{type} eq 'ship_order') {
$query .= qq|
AND i.warehouse_id = ?
AND (
SELECT SUM(i.qty)
FROM inventory i
WHERE oi.parts_id = i.parts_id
AND i.warehouse_id = ?
) > 0|;
push(@queryargs, $warehouse_id, $warehouse_id);
}
}
if ($form->{"$form->{vc}_id"}) {
$query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
} elsif ($form->{$form->{vc}} ne "") {
$query .= " AND lower(ct.name) LIKE ?";
push @queryargs, $name;
}
if ($form->{$ordnumber} ne "") {
$query .= " AND lower(?) LIKE ?";
push @queryargs, $ordnumber, $number;
$form->{open} = 1;
$form->{closed} = 1;
}
if ($form->{ponumber} ne "") {
$query .= " AND lower(ponumber) LIKE ?";
push @queryargs, $form->{ponumber};
}
if (!$form->{open} && !$form->{closed}) {
$query .= " AND o.id = 0";
} elsif (!($form->{open} && $form->{closed})) {
$query .= ($form->{open}) ?
" AND o.closed = '0'" : " AND o.closed = '1'";
}
if ($form->{shipvia} ne "") {
$var = $form->like(lc $form->{shipvia});
$query .= " AND lower(o.shipvia) LIKE ?";
push @queryargs, $var;
}
if ($form->{description} ne "") {
$var = $form->like(lc $form->{description});
$query .= " AND o.id IN (SELECT DISTINCT trans_id
FROM orderitems
WHERE lower(description) LIKE '$var')";
push @queryargs, $var;
}
if ($form->{transdatefrom}) {
$query .= " AND o.transdate >= ?";
push @queryargs, $form->{transdatefrom};
}
if ($form->{transdateto}) {
$query .= " AND o.transdate <= ?";
push @queryargs, $form->{transdateto};
}
$query .= " ORDER by $sortorder";
my $sth = $dbh->prepare($query);
$sth->execute(@queryargs) || $form->dberror($query);
my %oid = ();
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
$ref->{exchangerate} = 1 unless $ref->{exchangerate};
if ($ref->{id} != $oid{id}{$ref->{id}}) {
push @{ $form->{OE} }, $ref;
$oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
}
$oid{id}{$ref->{id}} = $ref->{id};
}
$sth->finish;
$dbh->commit;
if ($form->{type} =~ /^consolidate_/) {
@a = ();
foreach $ref (@{ $form->{OE} }) {
push @a, $ref if $oid{vc}{$ref->{curr}}
{$ref->{"$form->{vc}_id"}} > 1;
}
@{ $form->{OE} } = @a;
}
}
sub save {
my ($self, $myconfig, $form) = @_;
$form->db_prepare_vars("quonumber", "transdate", "vendor_id",
"customer_id", "reqdate", "taxincluded", "shippingpoint",
"shipvia", "currency", "department_id",
"employee_id", "language_code", "ponumber", "terms");
# connect to database, turn off autocommit
my $dbh = $form->{dbh};
my @queryargs;
my $quotation;
my $ordnumber;
my $numberfld;
$form->{vc} = ($form->{vc} eq 'customer') ? 'customer': 'vendor';
if ($form->{type} =~ /_order$/) {
$quotation = "0";
$ordnumber = "ordnumber";
$numberfld = ($form->{vc} eq 'customer') ? "sonumber" :
"ponumber";
} else {
$quotation = "1";
$ordnumber = "quonumber";
$numberfld = ($form->{vc} eq 'customer') ? "sqnumber" :
"rfqnumber";
}
$form->{"$ordnumber"} = $form->update_defaults(
$myconfig, $numberfld, $dbh)
unless $form->{ordnumber};
my $query;
my $sth;
my $null;
my $exchangerate = 0;
($null, $form->{employee_id}) = split /--/, $form->{employee};
if (! $form->{employee_id}) {
($form->{employee}, $form->{employee_id}) =
$form->get_employee($dbh);
$form->{employee} = "$form->{employee}--$form->{employee_id}";
}
my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
$query = qq|
SELECT p.assembly, p.project_id
FROM parts p WHERE p.id = ?|;
my $pth = $dbh->prepare($query) || $form->dberror($query);
if ($form->{id}) {
$query = qq|SELECT id FROM oe WHERE id = $form->{id}|;
if ($dbh->selectrow_array($query)) {
&adj_onhand($dbh, $form, $ml)
if $form->{type} =~ /_order$/;
$query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
} else { # id is not in the database
delete $form->{id};
}
}
my $did_insert = 0;
if (! $form->{id}) {
$query = qq|SELECT nextval('id')|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
($form->{id}) = $sth->fetchrow_array;
$sth->finish;
my $uid = localtime;
$uid .= "$$";
if (!$form->{reqdate}){
$form->{reqdate} = undef;
}
if (!$form->{transdate}){
$form->{transdate} = "now";
}
if (($form->{closed} ne 't') and ($form->{closed} ne "1")){
$form->{closed} = 'f';
}
# $form->{id} is safe because it is only pulled *from* the db.
$query = qq|
INSERT INTO oe
(id, ordnumber, quonumber, transdate, vendor_id,
customer_id, reqdate, shippingpoint, shipvia,
notes, intnotes, curr, closed, department_id,
employee_id, language_code, ponumber, terms,
quotation)
VALUES
($form->{id}, ?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?, ?,
?, ?, ?, ?, ?)|;
@queryargs = (
$form->{ordnumber}, $form->{quonumber},
$form->{transdate}, $form->{vendor_id},
$form->{customer_id}, $form->{reqdate},
$form->{shippingpoint}, $form->{shipvia},
$form->{notes}, $form->{intnotes}, $form->{currency},
$form->{closed}, $form->{department_id},
$form->{employee_id}, $form->{language_code},
$form->{ponumber}, $form->{terms}, $quotation);
$sth = $dbh->prepare($query);
$sth->execute(@queryargs) || $form->dberror($query);
$sth->finish;
@queries = $form->run_custom_queries('oe', 'INSERT');
}
my $amount;
my $linetotal;
my $discount;
my $project_id;
my $taxrate;
my $taxamount;
my $fxsellprice;
my %taxbase;
my @taxaccounts;
my %taxaccounts;
my $netamount = 0;
my $rowcount = $form->{rowcount};
for my $i (1 .. $rowcount) {
$form->db_prepare_vars("orderitems_id_$i", "id_$i",
"description_$i", "project_id_$i", "ship_$i");
for (qw(qty ship)) {
$form->{"${_}_$i"} = $form->parse_amount(
$myconfig, $form->{"${_}_$i"}
);
}
$form->{"discount_$i"} = $form->parse_amount(
$myconfig, $form->{"discount_$i"}
) / 100;
$form->{"sellprice_$i"} = $form->parse_amount(
$myconfig, $form->{"sellprice_$i"}
);
if ($form->{"qty_$i"}) {
$pth->execute($form->{"id_$i"});
$ref = $pth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
$pth->finish;
$fxsellprice = $form->{"sellprice_$i"};
my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
$discount = $form->round_amount(
$form->{"sellprice_$i"} *
$form->{"discount_$i"},
$decimalplaces
);
$form->{"sellprice_$i"} = $form->round_amount(
$form->{"sellprice_$i"} - $discount,
$decimalplaces
);
$linetotal = $form->round_amount(
$form->{"sellprice_$i"} * $form->{"qty_$i"}, 2
);
@taxaccounts = Tax::init_taxes($form,
$form->{"taxaccounts_$i"});
if ($form->{taxincluded}) {
$taxamount = Tax::calculate_taxes(\@taxaccounts,
$form, $linetotal, 1);
$form->{"sellprice_$i"} = Tax::extract_taxes(\@taxaccounts,
$form, $form->{"sellprice_$i"});
$taxbase = Tax::extract_taxes(\@taxaccounts,
$form, $linetotal);
} else {
$taxamount = Tax::apply_taxes(\@taxaccounts,
$form, $linetotal);
$taxbase = $linetotal;
}
if (@taxaccounts && $form->round_amount($taxamount, 2)
== 0) {
if ($form->{taxincluded}) {
foreach $item (@taxaccounts) {
$taxamount =
$form->round_amount(
$item->value, 2);
$taxaccounts{$item->account} +=
$taxamount;
$taxdiff += $taxamount;
$taxbase{$item->account} +=
$taxbase;
}
$taxaccounts{$taxaccounts[0]->account}
+= $taxdiff;
} else {
foreach $item (@taxaccounts) {
$taxaccounts{$item->account} +=
$item->value;
$taxbase{$item->account} +=
$taxbase;
}
}
} else {
foreach $item (@taxaccounts) {
$taxaccounts{$item->account} +=
$item->value;
$taxbase{$item->account} += $taxbase;
}
}
$netamount += $form->{"sellprice_$i"}
* $form->{"qty_$i"};
if ($form->{"projectnumber_$i"} ne "") {
($null, $project_id)
= split /--/,
$form->{"projectnumber_$i"};
}
$project_id = $form->{"project_id_$i"}
if $form->{"project_id_$i"};
if (!$form->{"reqdate_$i"}){
$form->{"reqdate_$i"} = undef;
}
@queryargs = ();
# save detail record in orderitems table
$query = qq|INSERT INTO orderitems (|;
if ($form->{"orderitems_id_$i"}){
$query .= "id, ";
}
$query .= qq|
trans_id, parts_id, description, qty, sellprice,
discount, unit, reqdate, project_id, ship,
serialnumber, notes)
VALUES (|;
if ($form->{"orderitems_id_$i"}){
$query .= "?, ";
push @queryargs, $form->{"orderitems_id_$i"};
}
$query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
$sth = $dbh->prepare($query);
push (@queryargs,
$form->{id}, $form->{"id_$i"},
$form->{"description_$i"}, $form->{"qty_$i"},
$fxsellprice, $form->{"discount_$i"},
$form->{"unit_$i"}, $form->{"reqdate_$i"},
$project_id, $form->{"ship_$i"},
$form->{"serialnumber_$i"},
$form->{"notes_$i"});
$sth->execute(@queryargs) || $form->dberror($query);
$form->{"sellprice_$i"} = $fxsellprice;
}
$form->{"discount_$i"} *= 100;
}
# set values which could be empty
for (qw(vendor_id customer_id taxincluded closed quotation))
{ $form->{$_} *= 1 }
# add up the tax
my $tax = 0;
for (keys %taxaccounts) { $tax += $taxaccounts{$_} }
$amount = $form->round_amount($netamount + $tax, 2);
$netamount = $form->round_amount($netamount, 2);
if ($form->{currency} eq $form->{defaultcurrency}) {
$form->{exchangerate} = 1;
} else {
$exchangerate = $form->check_exchangerate(
$myconfig, $form->{currency}, $form->{transdate},
($form->{vc} eq 'customer') ? 'buy' : 'sell');
}
$form->{exchangerate} = ($exchangerate) ? $exchangerate :
$form->parse_amount($myconfig, $form->{exchangerate});
($null, $form->{department_id}) = split(/--/, $form->{department});
for (qw(department_id terms)) { $form->{$_} *= 1 }
if ($did_insert){
$query = qq|
UPDATE oe SET
amount = ?,
netamount = ?,
taxincluded = ?
WHERE id = ?|;
@queryargs = ($amount, $netamount, $form->{taxincluded},
$form->{id});
} else {
# save OE record
$query = qq|
UPDATE oe set
ordnumber = ?,
quonumber = ?,
transdate = ?,
vendor_id = ?,
customer_id = ?,
amount = ?,
netamount = ?,
reqdate = ?,
taxincluded = ?,
shippingpoint = ?,
shipvia = ?,
notes = ?,
intnotes = ?,
curr = ?,
closed = ?,
quotation = ?,
department_id = ?,
employee_id = ?,
language_code = ?,
ponumber = ?,
terms = ?
WHERE id = ?|;
if (!$form->{reqdate}){
$form->{reqdate} = undef;
}
@queryargs = ($form->{ordnumber},
$form->{quonumber},
$form->{transdate},
$form->{vendor_id},
$form->{customer_id},
$amount,
$netamount,
$form->{reqdate},
$form->{taxincluded},
$form->{shippingpoint},
$form->{shipvia},
$form->{notes},
$form->{intnotes},
$form->{currency},
$form->{closed},
$quotation,
$form->{department_id},
$form->{employee_id},
$form->{language_code},
$form->{ponumber},
$form->{terms},
$form->{id});
}
$sth = $dbh->prepare($query);
$sth->execute(@queryargs) || $form->dberror($query);
if (!$did_insert){
@queries = $form->run_custom_queries('oe', 'UPDATE');
}
$form->{ordtotal} = $amount;
# add shipto
$form->{name} = $form->{$form->{vc}};
$form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
$form->add_shipto($dbh, $form->{id});
# save printed, emailed, queued
$form->save_status($dbh);
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
if ($form->{vc} eq 'customer') {
$form->update_exchangerate(
$dbh,
$form->{currency},
$form->{transdate},
$form->{exchangerate},
0);
}
if ($form->{vc} eq 'vendor') {
$form->update_exchangerate(
$dbh,
$form->{currency},
$form->{transdate},
0,
$form->{exchangerate});
}
}
if ($form->{type} =~ /_order$/) {
# adjust onhand
&adj_onhand($dbh, $form, $ml * -1);
&adj_inventory($dbh, $myconfig, $form);
}
my %audittrail = (
tablename => 'oe',
reference => ($form->{type} =~ /_order$/)
? $form->{ordnumber} : $form->{quonumber},
formname => $form->{type},
action => 'saved',
id => $form->{id} );
$form->audittrail($dbh, "", \%audittrail);
$form->save_recurring($dbh, $myconfig);
my $rc = $dbh->commit;
$rc;
}
sub delete {
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->{dbh};
# delete spool files
my $query = qq|
SELECT spoolfile FROM status
WHERE trans_id = ?
AND spoolfile IS NOT NULL|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
my $spoolfile;
my @spoolfiles = ();
while (($spoolfile) = $sth->fetchrow_array) {
push @spoolfiles, $spoolfile;
}
$sth->finish;
$query = qq|
SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly
FROM orderitems o
JOIN parts p ON (p.id = o.parts_id)
WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
if ($form->{type} =~ /_order$/) {
$ml = ($form->{type} eq 'purchase_order') ? -1 : 1;
while (my ($id, $ship, $inv, $assembly)
= $sth->fetchrow_array) {
$form->update_balance(
$dbh,
"parts",
"onhand",
"id = $id",
$ship * $ml)
if ($inv || $assembly);
}
}
$sth->finish;
# delete inventory
$query = qq|DELETE FROM inventory WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$sth->finish;
# delete status entries
$query = qq|DELETE FROM status WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$sth->finish;
# delete OE record
$query = qq|DELETE FROM oe WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$sth->finish;
# delete individual entries
$query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
$sth->finish;
$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$sth->finish;
my %audittrail = (
tablename => 'oe',
reference => ($form->{type} =~ /_order$/)
? $form->{ordnumber} : $form->{quonumber},
formname => $form->{type},
action => 'deleted',
id => $form->{id} );
$form->audittrail($dbh, "", \%audittrail);
my $rc = $dbh->commit;
$dbh->disconnect;
if ($rc) {
foreach $spoolfile (@spoolfiles) {
unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
}
}
$rc;
}
sub retrieve {
use LedgerSMB::PriceMatrix;
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->{dbh};
my $query;
my $sth;
my $var;
my $ref;
$query = qq|
SELECT value, current_date FROM defaults
WHERE setting_key = 'curr'|;
($form->{currencies}, $form->{transdate}) =
$dbh->selectrow_array($query);
if ($form->{id}) {
# retrieve order
$query = qq|
SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,
o.taxincluded, o.shippingpoint, o.shipvia,
o.notes, o.intnotes, o.curr AS currency,
e.name AS employee, o.employee_id,
o.$form->{vc}_id, vc.name AS $form->{vc},
o.amount AS invtotal, o.closed, o.reqdate,
o.quonumber, o.department_id,
d.description AS department, o.language_code,
o.ponumber
FROM oe o
JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)
LEFT JOIN employee e ON (o.employee_id = e.id)
LEFT JOIN department d ON (o.department_id = d.id)
WHERE o.id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
$query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
# get printed, emailed and queued
$query = qq|
SELECT s.printed, s.emailed, s.spoolfile, s.formname
FROM status s
WHERE s.trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
$form->{printed} .= "$ref->{formname} "
if $ref->{printed};
$form->{emailed} .= "$ref->{formname} "
if $ref->{emailed};
$form->{queued} .= "$ref->{formname} $ref->{spoolfile} "
if $ref->{spoolfile};
}
$sth->finish;
for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g }
# retrieve individual items
$query = qq|
SELECT o.id AS orderitems_id, p.partnumber, p.assembly,
o.description, o.qty, o.sellprice,
o.parts_id AS id, o.unit, o.discount, p.bin,
o.reqdate, o.project_id, o.ship, o.serialnumber,
o.notes, pr.projectnumber, pg.partsgroup,
p.partsgroup_id, p.partnumber AS sku,
p.listprice, p.lastcost, p.weight, p.onhand,
p.inventory_accno_id, p.income_accno_id,
p.expense_accno_id, t.description
AS partsgrouptranslation
FROM orderitems o
JOIN parts p ON (o.parts_id = p.id)
LEFT JOIN project pr ON (o.project_id = pr.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
LEFT JOIN translation t
ON (t.trans_id = p.partsgroup_id
AND t.language_code = ?)
WHERE o.trans_id = ?
ORDER BY o.id|;
$sth = $dbh->prepare($query);
$sth->execute($form->{language_code}, $form->{id})
|| $form->dberror($query);
# foreign exchange rates
&exchangerate_defaults($dbh, $form);
# query for price matrix
my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
# taxes
$query = qq|
SELECT c.accno FROM chart c
JOIN partstax pt ON (pt.chart_id = c.id)
WHERE pt.parts_id = ?|;
my $tth = $dbh->prepare($query) || $form->dberror($query);
my $taxrate;
my $ptref;
my $sellprice;
my $listprice;
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
($decimalplaces) = ($ref->{sellprice} =~ /\.(\d+)/);
$decimalplaces = length $decimalplaces;
$decimalplaces = ($decimalplaces > 2) ?
$decimalplaces : 2;
$tth->execute($ref->{id});
$ref->{taxaccounts} = "";
$taxrate = 0;
while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
$ref->{taxaccounts} .= "$ptref->{accno} ";
$taxrate += $form->{"$ptref->{accno}_rate"};
}
$tth->finish;
chop $ref->{taxaccounts};
# preserve price
$sellprice = $ref->{sellprice};
# multiply by exchangerate
$ref->{sellprice} = $form->round_amount(
$ref->{sellprice} * $form->{$form->{currency}},
$decimalplaces
);
for (qw(listprice lastcost)) {
$ref->{$_} = $form->round_amount(
$ref->{$_} / $form->{$form->{currency}},
$decimalplaces
);
}
# partnumber and price matrix
PriceMatrix::price_matrix(
$pmh, $ref, $form->{transdate}, $decimalplaces,
$form, $myconfig);
$ref->{sellprice} = $sellprice;
$ref->{partsgroup} = $ref->{partsgrouptranslation}
if $ref->{partsgrouptranslation};
push @{ $form->{form_details} }, $ref;
}
$sth->finish;
# get recurring transaction
$form->get_recurring;
@queries = $form->run_custom_queries('oe', 'SELECT');
$form->{dbh}->commit;
} else {
# get last name used
$form->lastname_used($myconfig, $dbh, $form->{vc})
unless $form->{"$form->{vc}_id"};
delete $form->{notes};
}
$dbh->commit;
}
sub exchangerate_defaults {
my ($dbh2, $form) = @_;
$dbh = $form->{dbh};
my $var;
my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
# get default currencies
my $query = qq|
SELECT substr(value,1,3), value FROM defaults
WHERE setting_key = 'curr'|;
($form->{defaultcurrency}, $form->{currencies})
= $dbh->selectrow_array($query);
$query = qq|
SELECT $buysell
FROM exchangerate
WHERE curr = ?
AND transdate = ?|;
my $eth1 = $dbh->prepare($query) || $form->dberror($query);
$query = qq~
SELECT max(transdate || ' ' || $buysell || ' ' || curr)
FROM exchangerate
WHERE curr = ?~;
my $eth2 = $dbh->prepare($query) || $form->dberror($query);
# get exchange rates for transdate or max
foreach $var (split /:/, substr($form->{currencies},4)) {
$eth1->execute($var, $form->{transdate});
($form->{$var}) = $eth1->fetchrow_array;
if (! $form->{$var} ) {
$eth2->execute($var);
($form->{$var}) = $eth2->fetchrow_array;
($null, $form->{$var}) = split / /, $form->{$var};
$form->{$var} = 1 unless $form->{$var};
$eth2->finish;
}
$eth1->finish;
}
$form->{$form->{currency}} = $form->{exchangerate}
if $form->{exchangerate};
$form->{$form->{currency}} ||= 1;
$form->{$form->{defaultcurrency}} = 1;
}
sub order_details {
use LedgerSMB::CP;
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->dbconnect($myconfig);
my $query;
my $sth;
my $item;
my $i;
my @sortlist = ();
my $projectnumber;
my $projectdescription;
my $projectnumber_id;
my $translation;
my $partsgroup;
my @queryargs;
my @taxaccounts;
my %taxaccounts; # I don't think this works.
my $tax;
my $taxrate;
my $taxamount;
my %translations;
my $language_code = $form->{dbh}->quote($form->{language_code});
$query = qq|
SELECT p.description, t.description
FROM project p
LEFT JOIN translation t ON (t.trans_id = p.id AND
t.language_code = $language_code)
WHERE id = ?|;
my $prh = $dbh->prepare($query) || $form->dberror($query);
$query = qq|
SELECT inventory_accno_id, income_accno_id,
expense_accno_id, assembly FROM parts
WHERE id = ?|;
my $pth = $dbh->prepare($query) || $form->dberror($query);
my $sortby;
# sort items by project and partsgroup
for $i (1 .. $form->{rowcount}) {
if ($form->{"id_$i"}) {
# account numbers
$pth->execute($form->{"id_$i"});
$ref = $pth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
$pth->finish;
$projectnumber_id = 0;
$projectnumber = "";
$form->{partsgroup} = "";
$form->{projectnumber} = "";
if ($form->{groupprojectnumber}
|| $form->{grouppartsgroup}) {
$inventory_accno_id =
($form->{"inventory_accno_id_$i"} ||
$form->{"assembly_$i"}) ? "1" : "";
if ($form->{groupprojectnumber}) {
($projectnumber, $projectnumber_id) =
split /--/,
$form->{"projectnumber_$i"};
}
if ($form->{grouppartsgroup}) {
($form->{partsgroup}) = split /--/,
$form->{"partsgroup_$i"};
}
if ($projectnumber_id &&
$form->{groupprojectnumber}) {
if ($translation{$projectnumber_id}) {
$form->{projectnumber} =
$translation{$projectnumber_id};
} else {
# get project description
$prh->execute(
$projectnumber_id);
($projectdescription,
$translation) =
$prh->fetchrow_array;
$prh->finish;
$form->{projectnumber} =
($translation) ?
"$projectnumber, \n" .
"$translation"
: "$projectnumber, \n" .
"$projectdescription";
$translation{$projectnumber_id}
= $form->{projectnumber};
}
}
if ($form->{grouppartsgroup}
&& $form->{partsgroup}) {
$form->{projectnumber} .= " / "
if $projectnumber_id;
$form->{projectnumber} .=
$form->{partsgroup};
}
$form->format_string(projectnumber);
}
$sortby = qq|$projectnumber$form->{partsgroup}|;
if ($form->{sortby} ne 'runningnumber') {
for (qw(partnumber description bin)) {
$sortby .= $form->{"${_}_$i"}
if $form->{sortby} eq $_;
}
}
push @sortlist, [ $i,
"$projectnumber$form->{partsgroup}".
"$inventory_accno_id",
$form->{projectnumber}, $projectnumber_id,
$form->{partsgroup}, $sortby ];
}
}
delete $form->{projectnumber};
# sort the whole thing by project and group
@sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;
# 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(qty) AS qty FROM inventory
WHERE parts_id = ? AND 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);
}
}
}
my $runningnumber = 1;
my $sameitem = "";
my $subtotal;
my $k = scalar @sortlist;
my $j = 0;
foreach $item (@sortlist) {
$i = $item->[0];
$j++;
if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
if ($item->[1] ne $sameitem) {
$sameitem = $item->[1];
$ok = 0;
if ($form->{groupprojectnumber}) {
$ok = $form->{"projectnumber_$i"};
}
if ($form->{grouppartsgroup}) {
$ok = $form->{"partsgroup_$i"}
unless $ok;
}
if ($ok) {
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} }, "");
push(@{ $form->{service} },
NULL);
} else {
push(@{ $form->{part} }, NULL);
push(@{ $form->{service} }, "");
}
push(@{ $form->{description} },
$item->[2]);
for (
qw(taxrates runningnumber
number sku qty ship unit bin
serialnumber requiredate
projectnumber sellprice
listprice netprice discount
discountrate linetotal weight
itemnotes)
) {
push(@{ $form->{$_} }, "");
}
push(@{ $form->{lineitems} },
{ amount => 0, tax => 0 });
}
}
}
$form->{"qty_$i"} = $form->parse_amount(
$myconfig, $form->{"qty_$i"});
$form->{"ship_$i"} = $form->parse_amount(
$myconfig, $form->{"ship_$i"});
if ($form->{"qty_$i"}) {
$form->{totalqty} += $form->{"qty_$i"};
$form->{totalship} += $form->{"ship_$i"};
$form->{totalweight} += ($form->{"weight_$i"}
* $form->{"qty_$i"});
$form->{totalweightship} += ($form->{"weight_$i"}
* $form->{"ship_$i"});
# add number, description and qty to $form->{number}
push(@{ $form->{runningnumber} }, $runningnumber++);
push(@{ $form->{number} },
qq|$form->{"partnumber_$i"}|);
push(@{ $form->{sku} }, qq|$form->{"sku_$i"}|);
push(@{ $form->{description} },
qq|$form->{"description_$i"}|);
push(@{ $form->{itemnotes} }, $form->{"notes_$i"});
push(@{ $form->{qty} }, $form->format_amount(
$myconfig, $form->{"qty_$i"}));
push(@{ $form->{ship} }, $form->format_amount(
$myconfig, $form->{"ship_$i"}));
push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
push(@{ $form->{bin} }, qq|$form->{"bin_$i"}|);
push(@{ $form->{serialnumber} },
qq|$form->{"serialnumber_$i"}|);
push(@{ $form->{requiredate} },
qq|$form->{"reqdate_$i"}|);
push(@{ $form->{projectnumber} },
qq|$form->{"projectnumber_$i"}|);
push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
push(@{ $form->{listprice} }, $form->{"listprice_$i"});
push(@{ $form->{weight} }, $form->format_amount(
$myconfig,
$form->{"weight_$i"} * $form->{"ship_$i"}));
my $sellprice = $form->parse_amount(
$myconfig, $form->{"sellprice_$i"});
my ($dec) = ($sellprice =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
my $discount = $form->round_amount(
$sellprice * $form->parse_amount(
$myconfig,
$form->{"discount_$i"}) / 100,
$decimalplaces);
# keep a netprice as well, (sellprice - discount)
$form->{"netprice_$i"} = $sellprice - $discount;
my $linetotal = $form->round_amount(
$form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} }, $form->{"sku_$i"});
push(@{ $form->{service} }, NULL);
$form->{totalparts} += $linetotal;
} else {
push(@{ $form->{service} }, $form->{"sku_$i"});
push(@{ $form->{part} }, NULL);
$form->{totalservices} += $linetotal;
}
push(@{ $form->{netprice} },
($form->{"netprice_$i"})
? $form->format_amount(
$myconfig,
$form->{"netprice_$i"},
$decimalplaces)
: " ");
$discount = ($discount)
? $form->format_amount(
$myconfig,
$discount * -1,
$decimalplaces)
: " ";
push(@{ $form->{discount} }, $discount);
push(@{ $form->{discountrate} },
$form->format_amount($myconfig,
$form->{"discount_$i"}));
$form->{ordtotal} += $linetotal;
# this is for the subtotals for grouping
$subtotal += $linetotal;
$form->{"linetotal_$i"} = $form->format_amount(
$myconfig, $linetotal, 2);
push(@{ $form->{linetotal} }, $form->{"linetotal_$i"});
@taxaccounts = Tax::init_taxes($form,
$form->{"taxaccounts_$i"});
my $ml = 1;
my @taxrates = ();
$tax = 0;
$taxamount = Tax::calculate_taxes(\@taxaccounts,
$form, $linetotal, 1);
$taxbase = Tax::extract_taxes(\@taxaccounts,
$form, $linetotal);
foreach $item (@taxaccounts) {
push @taxrates, Math::BigFloat->new(100) *
$item->rate;
if ($form->{taxincluded}) {
$taxaccounts{$item->account} +=
$item->value;
$taxbase{$item->account} += $taxbase;
} else {
Tax::apply_taxes(\@taxaccounts, $form,
$linetotal);
$taxbase{$item->account} += $linetotal;
$taxaccounts{$item->account} +=
$item->value;
}
}
if ($form->{taxincluded}) {
$tax += Tax::calculate_taxes(\@taxaccounts,
$form, $linetotal, 1);
} else {
$tax += Tax::calculate_taxes(\@taxaccounts,
$form, $linetotal, 0);
}
push(@{ $form->{lineitems} },
{ amount => $linetotal,
tax => $form->round_amount($tax, 2) });
push(@{ $form->{taxrates} },
join ' ', sort { $a <=> $b } @taxrates);
if ($form->{"assembly_$i"}) {
$form->{stagger} = -1;
&assembly_details($myconfig,
$form, $dbh, $form->{"id_$i"},
$oid{$myconfig->{dbdriver}},
$form->{"qty_$i"});
}
}
# add subtotal
if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
if ($subtotal) {
if ($j < $k) {
# look at next item
if ($sortlist[$j]->[1] ne $sameitem) {
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} },
"");
push(@{
$form->{service}
}, NULL);
} else {
push(@{
$form->{service}
}, "");
push(@{ $form->{part} },
NULL);
}
for (qw(
taxrates runningnumber
number sku qty ship unit
bin serialnumber
requiredate
projectnumber sellprice
listprice netprice
discount discountrate
weight itemnotes)
) {
push(@{ $form->{$_} },
"");
}
push(@{ $form->{description} },
$form->{groupsubtotaldescription});
push(@{ $form->{lineitems} },
{ amount => 0,
tax => 0 });
if ($form->{groupsubtotaldescription}
ne "") {
push(@{
$form->{linetotal}
},
$form->format_amount($myconfig, $subtotal, 2));
} else {
push(@{
$form->{linetotal}
}, "");
}
$subtotal = 0;
}
} else {
# got last item
if ($form->{groupsubtotaldescription}
ne "") {
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} },
"");
push(@{
$form->{service}
}, NULL);
} else {
push(@{
$form->{service}
}, "");
push(@{ $form->{part} },
NULL);
}
for (qw(
taxrates runningnumber
number sku qty ship unit
bin serialnumber
requiredate
projectnumber sellprice
listprice netprice
discount discountrate
weight itemnotes)
) {
push(@{ $form->{$_} },
"");
}
push(@{ $form->{description} },
$form->{groupsubtotaldescription});
push(@{ $form->{linetotal} },
$form->format_amount(
$myconfig,
$subtotal,
2));
push(@{ $form->{lineitems} },
{ amount => 0,
tax => 0 });
}
}
}
}
}
$tax = 0;
foreach $item (sort keys %taxaccounts) {
if ($form->round_amount($taxaccounts{$item}, 2)) {
$tax += $taxamount = $form->round_amount(
$taxaccounts{$item}, 2);
push(@{ $form->{taxbaseinclusive} },
$form->{"${item}_taxbaseinclusive"}
= $form->round_amount(
$taxbase{$item} + $tax, 2));
push(@{ $form->{taxbase} },
$form->{"${item}_taxbase"}
= $form->format_amount($myconfig,
$taxbase{$item}, 2));
push(@{ $form->{tax} },
$form->{"${item}_tax"}
= $form->format_amount($myconfig,
$taxamount, 2));
push(@{ $form->{taxdescription} },
$form->{"${item}_description"});
$form->{"${item}_taxrate"} =
$form->format_amount($myconfig,
$form->{"${item}_rate"} * 100);
push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"});
push(@{ $form->{taxnumber} },
$form->{"${item}_taxnumber"});
}
}
# adjust taxes for lineitems
my $total = 0;
for (@{ $form->{lineitems} }) {
$total += $_->{tax};
}
if ($form->round_amount($total,2) != $form->round_amount($tax,2)) {
# get largest amount
for (reverse sort { $a->{tax} <=> $b->{tax} }
@{ $form->{lineitems} }) {
$_->{tax} -= $total - $tax;
last;
}
}
$i = 1;
for (@{ $form->{lineitems} }) {
push(@{ $form->{linetax} },
$form->format_amount($myconfig, $_->{tax}, 2, ""));
}
for (qw(totalparts totalservices)) {
$form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2);
}
for (qw(totalqty totalship totalweight)) {
$form->{$_} = $form->format_amount($myconfig, $form->{$_});
}
$form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal},
2);
$form->{ordtotal} = ($form->{taxincluded})
? $form->{ordtotal}
: $form->{ordtotal} + $tax;
my $c;
if ($form->{language_code} ne "") {
$c = new CP $form->{language_code};
} else {
$c = new CP $myconfig->{countrycode};
}
$c->init;
my $whole;
($whole, $form->{decimal}) = split /\./, $form->{ordtotal};
$form->{decimal} .= "00";
$form->{decimal} = substr($form->{decimal}, 0, 2);
$form->{text_decimal} = $c->num2text($form->{decimal} * 1);
$form->{text_amount} = $c->num2text($whole);
$form->{integer_amount} = $form->format_amount($myconfig, $whole);
# format amounts
$form->{quototal} = $form->{ordtotal} =
$form->format_amount($myconfig, $form->{ordtotal}, 2);
$form->format_string(qw(text_amount text_decimal));
$query = qq|
SELECT value FROM defaults
WHERE setting_key = 'weightunit'|;
($form->{weightunit}) = $dbh->selectrow_array($query);
$dbh->commit;
}
sub assembly_details {
my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_;
my $sm = "";
my $spacer;
$form->{stagger}++;
if ($form->{format} eq 'html') {
$spacer = " " x (3 * ($form->{stagger} - 1))
if $form->{stagger} > 1;
}
if ($form->{format} =~ /(postscript|pdf)/) {
if ($form->{stagger} > 1) {
$spacer = ($form->{stagger} - 1) * 3;
$spacer = '\rule{'.$spacer.'mm}{0mm}';
}
}
# get parts and push them onto the stack
my $sortorder = "";
if ($form->{grouppartsgroup}) {
$sortorder = qq|ORDER BY pg.partsgroup, a.id|;
} else {
$sortorder = qq|ORDER BY a.id|;
}
my $where = ($form->{formname} eq 'work_order')
? "1 = 1"
: "a.bom = '1'";
my $query = qq|
SELECT p.partnumber, p.description, p.unit, a.qty,
pg.partsgroup, p.partnumber AS sku, p.assembly, p.id,
p.bin
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
WHERE $where
AND a.id = ?
$sortorder|;
my $sth = $dbh->prepare($query);
$sth->execute($id) || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
for (qw(partnumber description partsgroup)) {
$form->{"a_$_"} = $ref->{$_};
$form->format_string("a_$_");
}
if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) {
for (qw(
taxrates number sku unit qty runningnumber ship
bin serialnumber requiredate projectnumber
sellprice listprice netprice discount
discountrate linetotal weight itemnotes)
) {
push(@{ $form->{$_} }, "");
}
$sm = ($form->{"a_partsgroup"})
? $form->{"a_partsgroup"} : "";
push(@{ $form->{description} }, "$spacer$sm");
push(@{ $form->{lineitems} },
{ amount => 0, tax => 0 });
}
if ($form->{stagger}) {
push(@{ $form->{description} },
qq|$spacer$form->{"a_partnumber"}, |.
qq|$form->{"a_description"}|);
for (qw(
taxrates number sku runningnumber ship
serialnumber requiredate projectnumber
sellprice listprice netprice discount
discountrate linetotal weight itemnotes)
) {
push(@{ $form->{$_} }, "")
}
} else {
push(@{ $form->{description} },
qq|$form->{"a_description"}|);
push(@{ $form->{sku} }, $form->{"a_partnumber"});
push(@{ $form->{number} }, $form->{"a_partnumber"});
for (qw(
taxrates runningnumber ship serialnumber
requiredate projectnumber sellprice listprice
netprice discount discountrate linetotal weight
itemnotes)
) {
push(@{ $form->{$_} }, "")
}
}
push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
push(@{ $form->{qty} }, $form->format_amount(
$myconfig, $ref->{qty} * $qty));
for (qw(unit bin)) {
$form->{"a_$_"} = $ref->{$_};
$form->format_string("a_$_");
push(@{ $form->{$_} }, $form->{"a_$_"});
}
if ($ref->{assembly} && $form->{formname} eq 'work_order') {
&assembly_details(
$myconfig, $form, $dbh, $ref->{id}, $oid,
$ref->{qty} * $qty);
}
}
$sth->finish;
$form->{stagger}--;
}
sub project_description {
my ($self, $dbh, $id) = @_;
my $query = qq|
SELECT description
FROM project
WHERE id = $id|;
($_) = $dbh->selectrow_array($query);
$_;
}
sub get_warehouses {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->dbconnect($myconfig);
# setup warehouses
my $query = qq|
SELECT id, description
FROM warehouse
ORDER BY 2|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
push @{ $form->{all_warehouse} }, $ref;
}
$sth->finish;
$dbh->disconnect;
}
sub save_inventory {
my ($self, $myconfig, $form) = @_;
my ($null, $warehouse_id) = split /--/, $form->{warehouse};
$warehouse_id *= 1;
my $ml = ($form->{type} eq 'ship_order') ? -1 : 1;
my $dbh = $form->dbconnect_noauto($myconfig);
my $sth;
my $wth;
my $serialnumber;
my $ship;
my ($null, $employee_id) = split /--/, $form->{employee};
($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id;
$query = qq|
SELECT serialnumber, ship
FROM orderitems
WHERE trans_id = ?
AND id = ?
FOR UPDATE|;
$sth = $dbh->prepare($query) || $form->dberror($query);
$query = qq|
SELECT sum(qty)
FROM inventory
WHERE parts_id = ?
AND warehouse_id = ?|;
$wth = $dbh->prepare($query) || $form->dberror($query);
for my $i (1 .. $form->{rowcount}) {
$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) {
if (!$form->{shippingdate}){
$form->{shippingdate} = undef;
}
$ship *= $ml;
$query = qq|
INSERT INTO inventory
(parts_id, warehouse_id, qty, trans_id,
orderitems_id, shippingdate,
employee_id)
VALUES
(?, ?, ?, ?, ?, ?, ?)|;
$sth2 = $dbh->prepare($query);
$sth2->execute(
$form->{"id_$i"}, $warehouse_id,
$ship, $form->{"id"},
$form->{"orderitems_id_$i"},
$form->{shippingdate},
$employee_id
) || $form->dberror($query);
$sth2->finish;
# 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,
reqdate = '$form->{shippingdate}'
WHERE trans_id = $form->{id}
AND id = $form->{"orderitems_id_$i"}|;
$sth2 = $dbh->prepare($query);
$sth2->execute(
$serialnumber, $ship,
$form->{shippingdate}, $form->{id},
$form->{"orderitems_id_$i"}
) || $form->dberror($query);
$sth2->finish;
# update order with ship via
$query = qq|
UPDATE oe SET
shippingpoint = ?,
shipvia = ?
WHERE id = ?|;
$sth2 = $dbh->prepare($query);
$sth2->execute(
$form->{shippingpoint}, $form->{shipvia},
$form->{id}
) || $form->dberror($query);
$sth2->finish;
# update onhand for parts
$form->update_balance($dbh,
"parts",
"onhand",
qq|id = $form->{"id_$i"}|,
$form->{"ship_$i"} * $ml);
}
}
my $rc = $dbh->commit;
$rc;
}
sub adj_onhand {
my ($dbh, $form, $ml) = @_;
my $query = qq|
SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly
FROM orderitems oi
JOIN parts p ON (p.id = oi.parts_id)
WHERE oi.trans_id = ?|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $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 $ath = $dbh->prepare($query) || $form->dberror($query);
my $ref;
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
if ($ref->{inventory_accno_id} || $ref->{assembly}) {
# do not update if assembly consists of all services
if ($ref->{assembly}) {
$ath->execute($ref->{parts_id})
|| $form->dberror($query);
my ($inv, $assembly) = $ath->fetchrow_array;
$ath->finish;
next unless ($inv || $assembly);
}
# adjust onhand in parts table
$form->update_balance($dbh,
"parts",
"onhand",
qq|id = $ref->{parts_id}|,
$ref->{ship} * $ml);
}
}
$sth->finish;
}
sub adj_inventory {
my ($dbh, $myconfig, $form) = @_;
# increase/reduce qty in inventory table
my $query = qq|
SELECT oi.id, oi.parts_id, oi.ship
FROM orderitems oi
WHERE oi.trans_id = ?|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
my $id = $dbh->quote($form->{id});
$query = qq|
SELECT qty,
(SELECT SUM(qty) FROM inventory
WHERE trans_id = $id
AND orderitems_id = ?) AS total
FROM inventory
WHERE trans_id = $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);
my $ship = $ref->{ship};
while (my $inv = $ith->fetchrow_hashref(NAME_lc)) {
if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) {
$qty = $inv->{qty} * $ml
if ($qty > ($inv->{qty} * $ml));
$form->update_balance($dbh,
"inventory",
"qty",
qq|$oid{$myconfig->{dbdriver}} |.
qq|= $inv->{oid}|,
$qty * -1 * $ml);
$ship -= $qty;
}
}
$ith->finish;
}
$sth->finish;
# delete inventory entries if qty = 0
$query = qq|
DELETE FROM inventory
WHERE trans_id = ?
AND qty = 0|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
}
sub get_inventory {
my ($self, $myconfig, $form) = @_;
my $where;
my $query;
my $null;
my $fromwarehouse_id;
my $towarehouse_id;
my $var;
my $dbh = $form->{dbh};
if ($form->{partnumber} ne "") {
$var = $dbh->quote($form->like(lc $form->{partnumber}));
$where .= "
AND lower(p.partnumber) LIKE '$var'";
}
if ($form->{description} ne "") {
$var = $dbh->quote($form->like(lc $form->{description}));
$where .= "
AND lower(p.description) LIKE '$var'";
}
if ($form->{partsgroup} ne "") {
($null, $var) = split /--/, $form->{partsgroup};
$var = $dbh->quote($var);
$where .= "
AND pg.id = $var";
}
($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse};
$fromwarehouse_id = $dbh->quote($fromwarehouse_id);
($null, $towarehouse_id) = split /--/, $form->{towarehouse};
$towarehouse_id = $dbh->quote($towarehouse_id);
my %ordinal = (
partnumber => 2,
description => 3,
partsgroup => 5,
warehouse => 6,
);
my @a = (partnumber, warehouse);
my $sortorder = $form->sort_order(\@a, \%ordinal);
if ($fromwarehouse_id) {
if ($towarehouse_id) {
$where .= "
AND NOT i.warehouse_id = $towarehouse_id";
}
$query = qq|
SELECT p.id, p.partnumber, p.description,
sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
pg.partsgroup, w.description AS warehouse,
i.warehouse_id
FROM inventory i
JOIN parts p ON (p.id = i.parts_id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
JOIN warehouse w ON (w.id = i.warehouse_id)
WHERE i.warehouse_id = $fromwarehouse_id
$where
GROUP BY p.id, p.partnumber, p.description,
pg.partsgroup, w.description, i.warehouse_id
ORDER BY $sortorder|;
} else {
if ($towarehouse_id) {
$query = qq|
SELECT p.id, p.partnumber, p.description,
p.onhand,
(SELECT SUM(qty)
FROM inventory i
WHERE i.parts_id = p.id) AS qty,
pg.partsgroup, '' AS warehouse,
0 AS warehouse_id
FROM parts p
LEFT JOIN partsgroup pg
ON (p.partsgroup_id = pg.id)
WHERE p.onhand > 0
$where
UNION|;
}
$query .= qq|
SELECT p.id, p.partnumber, p.description,
sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
pg.partsgroup, w.description AS warehouse,
i.warehouse_id
FROM inventory i
JOIN parts p ON (p.id = i.parts_id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
JOIN warehouse w ON (w.id = i.warehouse_id)
WHERE i.warehouse_id != $towarehouse_id
$where
GROUP BY p.id, p.partnumber, p.description,
pg.partsgroup, w.description, i.warehouse_id
ORDER BY $sortorder|;
}
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
$ref->{qty} = $ref->{onhand} - $ref->{qty};
push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0;
}
$sth->finish;
$dbh->commit;
}
sub transfer {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->dbconnect_noauto($myconfig);
($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
my @a = localtime;
$a[5] += 1900;
$a[4]++;
$a[4] = substr("0$a[4]", -2);
$a[3] = substr("0$a[3]", -2);
$shippingdate = "$a[5]$a[4]$a[3]";
my %total = ();
my $query = qq|
INSERT INTO inventory
(warehouse_id, parts_id, qty, shippingdate, employee_id)
VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|;
$sth = $dbh->prepare($query) || $form->dberror($query);
my $qty;
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 > 0) {
# to warehouse
if ($form->{warehouse_id}) {
$sth->execute($form->{warehouse_id},
$form->{"id_$i"}, $qty,
$shippingdate, $form->{employee_id})
|| $form->dberror;
$sth->finish;
}
# from warehouse
if ($form->{"warehouse_id_$i"}) {
$sth->execute($form->{"warehouse_id_$i"},
$form->{"id_$i"}, $qty * -1)
|| $form->dberror;
$sth->finish;
}
}
}
my $rc = $dbh->commit;
$dbh->commit;
$rc;
}
sub get_soparts {
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->{dbh};
my $id;
my $ref;
# store required items from selected sales orders
my $query = qq|
SELECT p.id, oi.qty - oi.ship AS required, p.assembly
FROM orderitems oi
JOIN parts p ON (p.id = oi.parts_id)
WHERE oi.trans_id = ?|;
my $sth = $dbh->prepare($query) || $form->dberror($query);
for (my $i = 1; $i <= $form->{rowcount}; $i++) {
if ($form->{"ndx_$i"}) {
$sth->execute($form->{"ndx_$i"});
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
&add_items_required(
"", $dbh, $form, $ref->{id},
$ref->{required}, $ref->{assembly});
}
$sth->finish;
}
}
$query = qq|SELECT current_date|;
($form->{transdate}) = $dbh->selectrow_array($query);
# foreign exchange rates
&exchangerate_defaults($dbh, $form);
$dbh->disconnect;
}
sub add_items_required {
my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_;
my $query;
my $sth;
my $ref;
if ($assembly) {
$query = qq|
SELECT p.id, a.qty, p.assembly
FROM assembly a
JOIN parts p ON (p.id = a.parts_id)
WHERE a.id = ?|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
&add_items_required(
"", $dbh, $form, $ref->{id},
$required * $ref->{qty}, $ref->{assembly});
}
$sth->finish;
} else {
$query = qq|
SELECT partnumber, description, lastcost
FROM parts
WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($parts_id) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) {
$form->{orderitems}{$parts_id}{$_} = $ref->{$_};
}
$sth->finish;
$form->{orderitems}{$parts_id}{required} += $required;
$query = qq|
SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr,
pv.vendor_id, v.name
FROM partsvendor pv
JOIN vendor v ON (v.id = pv.vendor_id)
WHERE pv.parts_id = ?|;
$sth = $dbh->prepare($query) || $form->dberror($query);
# get cost and vendor
$sth->execute($parts_id);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
for (keys %$ref) {
$form->{orderitems}{$parts_id}{partsvendor}
{$ref->{vendor_id}}{$_} = $ref->{$_};
}
}
$sth->finish;
}
}
sub generate_orders {
my ($self, $myconfig, $form) = @_;
my $i;
my %a;
my $query;
my $sth;
for ($i = 1; $i <= $form->{rowcount}; $i++) {
for (qw(qty lastcost)) {
$form->{"${_}_$i"} = $form->parse_amount(
$myconfig, $form->{"${_}_$i"});
}
if ($form->{"qty_$i"}) {
($vendor, $vendor_id) =
split /--/, $form->{"vendor_$i"};
if ($vendor_id) {
$a{$vendor_id}{$form->{"id_$i"}}{qty}
+= $form->{"qty_$i"};
for (qw(curr lastcost)) {
$a{$vendor_id}{$form->{"id_$i"}}{$_}
= $form->{"${_}_$i"};
}
}
}
}
# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
# foreign exchange rates
&exchangerate_defaults($dbh, $form);
my $amount;
my $netamount;
my $curr = "";
my %tax;
my $taxincluded = 0;
my $vendor_id;
my $description;
my $unit;
my $sellprice;
foreach $vendor_id (keys %a) {
%tax = ();
$query = qq|
SELECT v.curr, v.taxincluded, t.rate, c.accno
FROM vendor v
LEFT JOIN vendortax vt ON (v.id = vt.vendor_id)
LEFT JOIN tax t ON (t.chart_id = vt.chart_id)
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE v.id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($vendor_id) || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
$curr = $ref->{curr};
$taxincluded = $ref->{taxincluded};
$tax{$ref->{accno}} = $ref->{rate};
}
$sth->finish;
$curr ||= $form->{defaultcurrency};
$taxincluded *= 1;
my $uid = localtime;
$uid .= "$$";
# TODO: Make this function insert as much as possible
$query = qq|
INSERT INTO oe (ordnumber)
VALUES ('$uid')|;
$dbh->do($query) || $form->dberror($query);
$query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
my ($id) = $sth->fetchrow_array;
$sth->finish;
$amount = 0;
$netamount = 0;
foreach my $parts_id (keys %{ $a{$vendor_id} }) {
if (($form->{$curr}
* $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) {
$sellprice = $a{$vendor_id}{$parts_id}{lastcost}
/ $form->{$curr}
* $form->{$a{$vendor_id}{$parts_id}
{curr}};
} else {
$sellprice =
$a{$vendor_id}{$parts_id}{lastcost};
}
$sellprice = $form->round_amount($sellprice, 2);
my $linetotal = $form->round_amount(
$sellprice * $a{$vendor_id}{$parts_id}{qty}, 2);
$query = qq|
SELECT p.description, p.unit, c.accno
FROM parts p
LEFT JOIN partstax pt ON (p.id = pt.parts_id)
LEFT JOIN chart c ON (c.id = pt.chart_id)
WHERE p.id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($parts_id) || $form->dberror($query);
my $rate = 0;
my $taxes = '';
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
$description = $ref->{description};
$unit = $ref->{unit};
$rate += $tax{$ref->{accno}};
$taxes .= "$ref->{accno} ";
}
$sth->finish;
chop $taxes;
my @taxaccounts = Tax::init_taxes($form, $taxes);
$netamount += $linetotal;
if ($taxincluded) {
$amount += $linetotal;
} else {
$amount += $form->round_amount(
Tax::apply_taxes(\@taxaccounts, $form,
$linetotal), 2);
}
$query = qq|
INSERT INTO orderitems
(trans_id, parts_id, description,
qty, ship, sellprice, unit)
VALUES
(?, ?, ?, ?, 0, ?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$id, $parts_id, $description,
$a{vendor_id}{parts_id}{qty}, $sellprice,
$unit
) || $form->dberror($query);
}
my $ordnumber = $form->update_defaults($myconfig, 'ponumber');
my $null;
my $employee_id;
my $department_id;
($null, $employee_id) = $form->get_employee($dbh);
($null, $department_id) = split /--/, $form->{department};
$department_id *= 1;
$query = qq|
UPDATE oe SET
ordnumber = ?,
transdate = current_date,
vendor_id = ?,
customer_id = 0,
amount = ?,
netamount = ?,
taxincluded = ?,
curr = ?,
employee_id = ?,
department_id = ?,
ponumber = ?
WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute(
$ordnumber, $vendor_id, $amount, $netamount,
$taxincluded, $curr, $employee_id,
$department_id, $form->{ponumber}, $id
) || $form->dberror($query);
}
my $rc = $dbh->commit;
$rc;
}
sub consolidate_orders {
my ($self, $myconfig, $form) = @_;
# connect to database
my $dbh = $form->{dbh};
my $i;
my $id;
my $ref;
my %oe = ();
my $query = qq|SELECT * FROM oe WHERE id = ?|;
my $sth = $dbh->prepare($query) || $form->dberror($query);
for ($i = 1; $i <= $form->{rowcount}; $i++) {
# retrieve order
if ($form->{"ndx_$i"}) {
$sth->execute($form->{"ndx_$i"});
$ref = $sth->fetchrow_hashref(NAME_lc);
$ref->{ndx} = $i;
$oe{oe}{$ref->{curr}}{$ref->{id}} = $ref;
$oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
$sth->finish;
}
}
$query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
$sth = $dbh->prepare($query) || $form->dberror($query);
foreach $curr (keys %{ $oe{oe} }) {
foreach $id (sort {
$oe{oe}{$curr}{$a}->{ndx}
<=> $oe{oe}{$curr}{$b}->{ndx} }
keys %{ $oe{oe}{$curr} }) {
# retrieve order
$vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"};
if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) {
push @{ $oe{orders}{$curr}{$vc_id} }, $id;
$sth->execute($id);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
push @{ $oe{orderitems}{$curr}{$id} },
$ref;
}
$sth->finish;
}
}
}
my $ordnumber = $form->{ordnumber};
my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
my ($department, $department_id) = $form->{department};
$department_id *= 1;
my $uid = localtime;
$uid .= "$$";
my @orderitems = ();
foreach $curr (keys %{ $oe{orders} }) {
foreach $vc_id (sort {
$a <=> $b
} keys %{ $oe{orders}{$curr} }) {
# the orders
@orderitems = ();
$form->{customer_id} = $form->{vendor_id} = 0;
$form->{"$form->{vc}_id"} = $vc_id;
$amount = 0;
$netamount = 0;
foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) {
# header
$ref = $oe{oe}{$curr}{$id};
$amount += $ref->{amount};
$netamount += $ref->{netamount};
$id = $dbh->quore($id);
foreach $item
(@{ $oe{orderitems}{$curr}{$id} }) {
push @orderitems, $item;
}
# close order
$query = qq|
UPDATE oe SET
closed = '1'
WHERE id = $id|;
$dbh->do($query) || $form->dberror($query);
# reset shipped
$query = qq|
UPDATE orderitems SET
ship = 0
WHERE trans_id = $id|;
$dbh->do($query) || $form->dberror($query);
}
$ordnumber ||= $form->update_defaults(
$myconfig, $numberfld, $dbh);
#fixme: Change this
$query = qq|
INSERT INTO oe (ordnumber) VALUES ($uid)|;
$dbh->do($query) || $form->dberror($query);
$query = qq|
SELECT id
FROM oe
WHERE ordnumber = '$uid'|;
($id) = $dbh->selectrow_array($query);
$ref->{employee_id} *= 1;
$query = qq|
UPDATE oe SET
ordnumber = |.$dbh->quote($ordnumber).qq|,
transdate = current_date,
vendor_id = $form->{vendor_id},
customer_id = $form->{customer_id},
amount = $amount,
netamount = $netamount,
reqdate = |.$form->dbquote($ref->{reqdate}, SQL_DATE).qq|,
taxincluded = '$ref->{taxincluded}',
shippingpoint = |.$dbh->quote($ref->{shippingpoint}).qq|,
notes = |.$dbh->quote($ref->{notes}).qq|,
curr = '$curr',
employee_id = $ref->{employee_id},
intnotes = |.$dbh->quote($ref->{intnotes}).qq|,
shipvia = |.$dbh->quote($ref->{shipvia}).qq|,
language_code = '$ref->{language_code}',
ponumber = |.$dbh->quote($form->{ponumber}).qq|,
department_id = $department_id
WHERE id = $id|;
$sth = $dbh->prepare($query);
$sth->execute (
$department_id, $id
) || $form->dberror($query);
# add items
foreach $item (@orderitems) {
for (qw(
qty sellprice discount project_id ship)
) {
$item->{$_} *= 1;
}
$query = qq|
INSERT INTO orderitems
(trans_id, parts_id, description,
qty, sellprice, discount, unit, reqdate,
project_id, ship, serialnumber, notes)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$id, $item->{parts_id}, $item->{description},
$item->{qty}, $item->{sellprice},
$item->{discount}, $item->{unit},
$form->{reqdate}, $item->{project_id},
$item->{ship}, $item->{serialnumber},
$item->{notes}
) || $form->dberror($query);
}
}
}
$rc = $dbh->commit;
$rc;
}
1;