getfield: query field (meta-data) values - ikiwiki-upstream - Unnamed repository; edit this file to name it for gitweb.

diff options

author	http://kerravonsen.dreamwidth.org/ <http://kerravonsen.dreamwidth.org/@web>	2010-01-13 02:27:59 +0000
committer	Joey Hess <joey@finch.kitenet.net>	2010-01-13 02:27:59 +0000
commit	ccc8e8868269bf2751596e04f792c388acb85c12 (patch)
tree	198ee598cc1f06dd329662fbb2698501d15d102e /doc/plugins/htmlscrubber
parent	9ccd1ba41bff43c1a5d06197b454c8748224e30f (diff)

getfield: query field (meta-data) values

Diffstat (limited to 'doc/plugins/htmlscrubber')

0 files changed, 0 insertions, 0 deletions

class="hl">package OE;

use LedgerSMB::Tax;

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}, 

                    $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, $spool) = @_;

# 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 "$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 curr, current_date FROM defaults|;

    ($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(curr,1,3), curr FROM defaults|;

    ($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 weightunit FROM defaults|;

    ($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 = "&nbsp;" 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 FROM defaults|;

    ($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;

}


context:
space:
mode: