ikiwiki-upstream - Unnamed repository; edit this file to name it for gitweb.

summary refs log tree commit diff

path: root/doc/bugs/cgi_does_not_use_templatedir_overlay.mdwn

diff options

Diffstat (limited to 'doc/bugs/cgi_does_not_use_templatedir_overlay.mdwn')

0 files changed, 0 insertions, 0 deletions

hl">#

#======================================================================

# This file has undergone PARTIAL (30%) whitespace cleanup To line 638

#======================================================================

# Order entry module

# Quotation

#======================================================================

package OE;

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} == 'customer'){ # Sanitize $form->{vc}

        $form->{vc} = 'customer';

    } else {

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

# connect to database, turn off autocommit

    my $dbh = $form->{dbh};

    my $quotation;

    my $ordnumber;

    my $numberfld;

    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 {

            $query = qq|INSERT INTO oe (id) VALUES (?)|;

            $sth = $dbh->prepare($query);

            $sth->execute($form->{id}) || $form->dberror($query);

}

    my $did_insert = 0;

    if (! $form->{id}) {

        my $uid = localtime;

        $uid .= "$$";

        $query = qq|

            INSERT INTO oe (ordnumber, employee_id)

            VALUES ('$uid', $form->{employee_id})|;

        $dbh->do($query) || $form->dberror($query);

        $query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|;

        $sth = $dbh->prepare($query);

        $sth->execute || $form->dberror($query);

        ($form->{id}) = $sth->fetchrow_array;

        $sth->finish;

        @queries = $form->get_custom_queries('oe', 'INSERT');

        for (@queries){

            $query = shift (@{$_});

            $sth = $dbh->prepare($query) 

                || $form->db_error($query);

            $sth->execute(@{$_}, $form->{id})

                || $form->dberror($query);;

            $sth->finish;

            $did_insert = 1;

}

    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} - 1;

    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 = split / /, $form->{"taxaccounts_$i"};

            $taxrate = 0;

            $taxdiff = 0;

            for (@taxaccounts) { $taxrate += $form->{"${_}_rate"} }

            if ($form->{taxincluded}) {

                $taxamount = $linetotal * $taxrate 

                    / (1 + $taxrate);

                $taxbase = $linetotal - $taxamount;

# we are not keeping a natural price,

# do not round

                $form->{"sellprice_$i"} = 

                    $form->{"sellprice_$i"} 

                        * (1 / (1 + $taxrate));

            } else {

                $taxamount = $linetotal * $taxrate;

                $taxbase = $linetotal;

}

            if (@taxaccounts && $form->round_amount($taxamount, 2) 

                    == 0) {

                if ($form->{taxincluded}) {

                    foreach $item (@taxaccounts) {

$taxamount =

                            $form->round_amount(

$linetotal

                            * $form->{"${item}_rate"} 

                            / (1 + abs(

                                $form->{"${item}_rate"}

)), 2

);

                        $taxaccounts{$item} += 

$taxamount;

                        $taxdiff += $taxamount; 

                        $taxbase{$item} += $taxbase;

}

                    $taxaccounts{$taxaccounts[0]} 

                        += $taxdiff;

                } else {

                    foreach $item (@taxaccounts) {

                        $taxaccounts{$item} += 

$linetotal *

                            $form->{"${item}_rate"};

                        $taxbase{$item} += $taxbase;

}

            } else {

                foreach $item (@taxaccounts) {

                    $taxaccounts{$item} += 

$taxamount *

                        $form->{"${item}_rate"} / 

$taxrate;

                    $taxbase{$item} += $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 }

# 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 = ?|;

    $form->db_prepare_vars("quonumber", "transdate", "vendor_id",

        "customer_id", "reqdate", "taxincluded", "shippingpoint",

        "shipvia", "currency", "closed", "department_id",

        "employee_id", "language_code", "ponumber", "terms");

    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->get_custom_queries('oe', 'UPDATE');

        for (@queries){

            my $query = shift @{$_};

            $sth = $dbh->prepare($query);

            $sth->execute (@{$_}, $form->{id});

            $sth->finish;

}

    $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 {

  my ($self, $myconfig, $form) = @_;

# connect to database

  my $dbh = $form->dbconnect($myconfig);

  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 = $form->{id}|;

    $sth = $dbh->prepare($query);

    $sth->execute || $form->dberror($query);

    $ref = $sth->fetchrow_hashref(NAME_lc);

    for (keys %$ref) { $form->{$_} = $ref->{$_} }

    $sth->finish;

    $query = qq|SELECT * FROM shipto

                WHERE trans_id = $form->{id}|;

    $sth = $dbh->prepare($query);

    $sth->execute || $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 = $form->{id}|;

    $sth = $dbh->prepare($query);

    $sth->execute || $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 = '$form->{language_code}')

        WHERE o.trans_id = $form->{id}

ORDER BY o.id|;

    $sth = $dbh->prepare($query);

    $sth->execute || $form->dberror($query);

# foreign exchange rates

    &exchangerate_defaults($dbh, $form);

# query for price matrix

    my $pmh = &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

      &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($dbh);

    @queries = $form->get_custom_queries('oe', 'SELECT');

    for (@queries){

    $query = shift @{$_};

    $sth = $form->{dbh}->prepare($query);

    $sth->execute($form->{id});

    $ref = $sth->fetchrow_hashref(NAME_lc);

    for (keys %{$ref}){

        $form->{$_} = $ref->{$_};

}

    $form->{dbh}->commit;

  } else {

# get last name used

    $form->lastname_used($myconfig, $dbh, $form->{vc}) unless $form->{"$form->{vc}_id"};

    delete $form->{notes};

}

  $dbh->disconnect;

}

sub price_matrix_query {

  my ($dbh, $form) = @_;

  my $query;

  my $sth;

  if ($form->{customer_id}) {

    $query = qq|SELECT p.id AS parts_id, 0 AS customer_id, 0 AS pricegroup_id,

             0 AS pricebreak, p.sellprice, NULL AS validfrom, NULL AS validto,

         '$form->{defaultcurrency}' AS curr, '' AS pricegroup

FROM parts p

WHERE p.id = ?

UNION

SELECT p.*, g.pricegroup

FROM partscustomer p

         LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)

WHERE p.parts_id = ?

         AND p.customer_id = $form->{customer_id}

UNION

SELECT p.*, g.pricegroup

FROM partscustomer p

         LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)

         JOIN customer c ON (c.pricegroup_id = g.id)

WHERE p.parts_id = ?

         AND c.id = $form->{customer_id}

UNION

SELECT p.*, '' AS pricegroup

FROM partscustomer p

         WHERE p.customer_id = 0

         AND p.pricegroup_id = 0

AND p.parts_id = ?

ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak

    $sth = $dbh->prepare($query) || $form->dberror($query);

}

  if ($form->{vendor_id}) {

# price matrix and vendor's partnumber

    $query = qq|SELECT partnumber

FROM partsvendor

WHERE parts_id = ?

        AND vendor_id = $form->{vendor_id}|;

    $sth = $dbh->prepare($query) || $form->dberror($query);

}

$sth;

}

sub price_matrix {

  my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig) = @_;

  $ref->{pricematrix} = "";

  my $customerprice;

  my $pricegroupprice;

  my $sellprice;

  my $mref;

  my %p = ();

# depends if this is a customer or vendor

  if ($form->{customer_id}) {

    $pmh->execute($ref->{id}, $ref->{id}, $ref->{id}, $ref->{id});

    while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {

# check date

      if ($mref->{validfrom}) {

    next if $transdate < $form->datetonum($myconfig, $mref->{validfrom});

}

      if ($mref->{validto}) {

    next if $transdate > $form->datetonum($myconfig, $mref->{validto});

}

# convert price

      $sellprice = $form->round_amount($mref->{sellprice} * $form->{$mref->{curr}}, $decimalplaces);

      if ($mref->{customer_id}) {

    $ref->{sellprice} = $sellprice if !$mref->{pricebreak};

    $p{$mref->{pricebreak}} = $sellprice;

    $customerprice = 1;

}

      if ($mref->{pricegroup_id}) {

    if (! $customerprice) {

      $ref->{sellprice} = $sellprice if !$mref->{pricebreak};

      $p{$mref->{pricebreak}} = $sellprice;

}

    $pricegroupprice = 1;

}

      if (!$customerprice && !$pricegroupprice) {

    $p{$mref->{pricebreak}} = $sellprice;

}

    $pmh->finish;

    if (%p) {

      if ($ref->{sellprice}) {

    $p{0} = $ref->{sellprice};

}

      for (sort { $a <=> $b } keys %p) { $ref->{pricematrix} .= "${_}:$p{$_} " }

    } else {

      if ($init) {

    $ref->{sellprice} = $form->round_amount($ref->{sellprice}, $decimalplaces);

      } else {

    $ref->{sellprice} = $form->round_amount($ref->{sellprice} * (1 - $form->{tradediscount}), $decimalplaces);

}

      $ref->{pricematrix} = "0:$ref->{sellprice} " if $ref->{sellprice};

}

    chop $ref->{pricematrix};

}

  if ($form->{vendor_id}) {

    $pmh->execute($ref->{id});

    $mref = $pmh->fetchrow_hashref(NAME_lc);

    if ($mref->{partnumber} ne "") {

      $ref->{partnumber} = $mref->{partnumber};

}

    if ($mref->{lastcost}) {

# do a conversion

      $ref->{sellprice} = $form->round_amount($mref->{lastcost} * $form->{$mref->{curr}}, $decimalplaces);

}

    $pmh->finish;

    $ref->{sellprice} *= 1;

# add 0:price to matrix

    $ref->{pricematrix} = "0:$ref->{sellprice}";

}

sub exchangerate_defaults {

  my ($dbh, $form) = @_;

  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 {

  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 %oid = ( 'Pg'  => 'TRUE',

              'PgPP'    => 'TRUE',

              'Oracle'  => 'rowid',

          'DB2' => '1=1'

);

  my @taxaccounts;

  my %taxaccounts;

  my $tax;

  my $taxrate;

  my $taxamount;

  my %translations;

  $query = qq|SELECT p.description, t.description

FROM project p

           LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{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, $translation" : "$projectnumber, $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, qq|$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 = split / /, $form->{"taxaccounts_$i"};

      my $ml = 1;

      my @taxrates = ();

      $tax = 0;

      for (0 .. 1) {

    $taxrate = 0;

    for (@taxaccounts) { $taxrate += $form->{"${_}_rate"} if ($form->{"${_}_rate"} * $ml) > 0 }

    $taxrate *= $ml;

    $taxamount = $linetotal * $taxrate / (1 + $taxrate);

    $taxbase = ($linetotal - $taxamount);

    foreach $item (@taxaccounts) {

      if (($form->{"${item}_rate"} * $ml) > 0) {

        push @taxrates, $form->{"${item}_rate"} * 100;

        if ($form->{taxincluded}) {

          $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"} / (1 + $taxrate);

          $taxbase{$item} += $taxbase;

        } else {

          $taxbase{$item} += $linetotal;

          $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};

}

    if ($form->{taxincluded}) {

      $tax += $linetotal * ($taxrate / (1 + ($taxrate * $ml)));

    } else {

      $tax += $linetotal * $taxrate;

}

    $ml *= -1;

}

      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));


context:
space:
mode: