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

summary refs log tree commit diff

path: root/doc/plugins/hnb

diff options

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

0 files changed, 0 insertions, 0 deletions

rs:

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

# This file has undergone whitespace cleanup

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

# Order entry module

# Quotation

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

package OE;

use LedgerSMB::Tax;

use LedgerSMB::Sysconfig;

sub transactions {

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

# connect to database

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

    my $query;

    my $null;

    my $var;

    my $ordnumber = 'ordnumber';

    my $quotation = '0';

    my $department;

    my $rate = ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell';

    ( $form->{transdatefrom}, $form->{transdateto} ) =

      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )

      if $form->{year} && $form->{month};

    if ( $form->{type} =~ /_quotation$/ ) {

        $quotation = '1';

        $ordnumber = 'quonumber';

}

    my $number  = $form->like( lc $form->{$ordnumber} );

    my $name    = $form->like( lc $form->{ $form->{vc} } );

    my @dptargs = ();

    for (qw(department employee)) {

        if ( $form->{$_} ) {

            ( $null, $var ) = split /--/, $form->{$_};

            $department .= " AND o.${_}_id = ?";

            push @dptargs, $var;

}

    if ( $form->{vc} ne 'customer' ) {    # Sanitize $form->{vc}

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

}

    my $query = qq|

        SELECT o.id, o.ordnumber, o.transdate, o.reqdate,

            o.amount, ct.name, o.netamount, o.$form->{vc}_id,

            ex.$rate AS exchangerate, o.closed, o.quonumber, 

            o.shippingpoint, o.shipvia, e.name AS employee, 

            m.name AS manager, o.curr, o.ponumber

FROM oe o

        JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)

        LEFT JOIN employee e ON (o.employee_id = e.id)

        LEFT JOIN employee m ON (e.managerid = m.id)

LEFT JOIN exchangerate ex

            ON (ex.curr = o.curr AND ex.transdate = o.transdate)

WHERE o.quotation = ?

$department|;

    my @queryargs = @dptargs;

    unshift @queryargs, $quotation;

    my %ordinal = (

        id        => 1,

        ordnumber => 2,

        transdate => 3,

        reqdate   => 4,

        name      => 6,

        quonumber => 11,

        shipvia   => 13,

        employee  => 14,

        manager   => 15,

        curr      => 16,

ponumber => 17

);

    my @a = ( transdate, $ordnumber, name );

    push @a, "employee" if $form->{l_employee};

    if ( $form->{type} !~ /(ship|receive)_order/ ) {

        push @a, "manager" if $form->{l_manager};

}

    my $sortorder = $form->sort_order( \@a, \%ordinal );

# build query if type eq (ship|receive)_order

    if ( $form->{type} =~ /(ship|receive)_order/ ) {

        my ( $warehouse, $warehouse_id ) = split /--/, $form->{warehouse};

        $query = qq|

            SELECT DISTINCT o.id, o.ordnumber, o.transdate,

                o.reqdate, o.amount, ct.name, o.netamount, 

                o.$form->{vc}_id, ex.$rate AS exchangerate,

                o.closed, o.quonumber, o.shippingpoint, 

                o.shipvia, e.name AS employee, o.curr, 

o.ponumber

FROM oe o

            JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)

            JOIN orderitems oi ON (oi.trans_id = o.id)

            JOIN parts p ON (p.id = oi.parts_id)|;

        if ( $warehouse_id && $form->{type} eq 'ship_order' ) {

            $query .= qq|

                JOIN inventory i ON (oi.parts_id = i.parts_id)

}

        $query .= qq|

            LEFT JOIN employee e ON (o.employee_id = e.id)

LEFT JOIN exchangerate ex

                ON (ex.curr = o.curr 

                    AND ex.transdate = o.transdate)

            WHERE o.quotation = '0'

            AND (p.inventory_accno_id > 0 OR p.assembly = '1')

            AND oi.qty != oi.ship

$department|;

        @queryargs = @dptargs;    #reset @queryargs

        if ( $warehouse_id && $form->{type} eq 'ship_order' ) {

            $query .= qq| 

AND i.warehouse_id = ?

AND (

                    SELECT SUM(i.qty)

FROM inventory i

                    WHERE oi.parts_id = i.parts_id

AND i.warehouse_id = ?

                ) > 0|;

            push( @queryargs, $warehouse_id, $warehouse_id );

}

    if ( $form->{"$form->{vc}_id"} ) {

        $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;

}

    elsif ( $form->{ $form->{vc} } ne "" ) {

        $query .= " AND lower(ct.name) LIKE ?";

        push @queryargs, $name;

}

    if ( $form->{$ordnumber} ne "" ) {

    $ordnumber = ($ordnumber eq 'ordnumber') ? 'ordnumber' : 'quonumber';

        $query .= " AND lower($ordnumber) LIKE ?";

        push @queryargs, $number;

        $form->{open}   = 1;

        $form->{closed} = 1;

}

    if ( $form->{ponumber} ne "" ) {

        $query .= " AND lower(ponumber) LIKE '%' || lower(?) || '%'";

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

    $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

        $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->{"ship_$i"} = 0 unless $form->{"ship_$i"};

        $form->db_prepare_vars( "orderitems_id_$i", "id_$i", "description_$i",

            "project_id_$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"},

                $form->{taxaccounts} );

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

            $query .= qq|

                trans_id, parts_id, description, qty, sellprice,

                discount, unit, reqdate, project_id, ship, 

serialnumber, notes)

VALUES (|;

            $query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;

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

            push( @queryargs,

                $form->{id},                $form->{"id_$i"},

                $form->{"description_$i"},  $form->{"qty_$i"},

                $fxsellprice,               $form->{"discount_$i"},

                $form->{"unit_$i"},         $form->{"reqdate_$i"},

                $project_id,                $form->{"ship_$i"},

                $form->{"serialnumber_$i"}, $form->{"notes_$i"} );

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

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

}

        $form->{"discount_$i"} *= 100;

}

# set values which could be empty

    for (qw(vendor_id customer_id taxincluded closed quotation)) {

        $form->{$_} *= 1;

}

# add up the tax

    my $tax = 0;

    for ( keys %taxaccounts ) { $tax += $taxaccounts{$_} }

    $amount = $form->round_amount( $netamount + $tax, 2 );

    $netamount = $form->round_amount( $netamount, 2 );

    if ( $form->{currency} eq $form->{defaultcurrency} ) {

        $form->{exchangerate} = 1;

}

else {

$exchangerate =

          $form->check_exchangerate( $myconfig, $form->{currency},

            $form->{transdate},

            ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell' );

}

    $form->{exchangerate} =

      ($exchangerate)

? $exchangerate

      : $form->parse_amount( $myconfig, $form->{exchangerate} );

    ( $null, $form->{department_id} ) = split( /--/, $form->{department} );

    for (qw(department_id terms)) { $form->{$_} *= 1 }

    if ($did_insert) {

        $query = qq|

UPDATE oe SET

amount = ?,

netamount = ?,

taxincluded = ?

WHERE id = ?|;

        @queryargs = ( $amount, $netamount, $form->{taxincluded}, $form->{id} );

}

else {

# save OE record

        $query = qq|

UPDATE oe set

ordnumber = ?,

quonumber = ?,

transdate = ?,

vendor_id = ?,

customer_id = ?,

amount = ?,

netamount = ?,

reqdate = ?,

taxincluded = ?,

shippingpoint = ?,

shipvia = ?,

notes = ?,

intnotes = ?,

curr = ?,

closed = ?,

quotation = ?,

department_id = ?,

employee_id = ?,

language_code = ?,

ponumber = ?,

terms = ?

WHERE id = ?|;

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

            $form->{reqdate} = undef;

}

@queryargs = (

            $form->{ordnumber},     $form->{quonumber},

            $form->{transdate},     $form->{vendor_id},

            $form->{customer_id},   $amount,

            $netamount,             $form->{reqdate},

            $form->{taxincluded},   $form->{shippingpoint},

            $form->{shipvia},       $form->{notes},

            $form->{intnotes},      $form->{currency},

            $form->{closed},        $quotation,

            $form->{department_id}, $form->{employee_id},

            $form->{language_code}, $form->{ponumber},

            $form->{terms},         $form->{id}

);

}

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

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

    if ( !$did_insert ) {

        @queries = $form->run_custom_queries( 'oe', 'UPDATE' );

}

    $form->{ordtotal} = $amount;

# add shipto

    $form->{name} = $form->{ $form->{vc} };

    $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;

    $form->add_shipto( $dbh, $form->{id} );

# save printed, emailed, queued

    $form->save_status($dbh);

    if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) {

        if ( $form->{vc} eq 'customer' ) {

            $form->update_exchangerate( $dbh, $form->{currency},

                $form->{transdate}, $form->{exchangerate}, 0 );

}

        if ( $form->{vc} eq 'vendor' ) {

            $form->update_exchangerate( $dbh, $form->{currency},

                $form->{transdate}, 0, $form->{exchangerate} );

}

    if ( $form->{type} =~ /_order$/ ) {

# adjust onhand

        &adj_onhand( $dbh, $form, $ml * -1 );

        &adj_inventory( $dbh, $myconfig, $form );

}

    my %audittrail = (

        tablename => 'oe',

        reference => ( $form->{type} =~ /_order$/ )

        ? $form->{ordnumber}

        : $form->{quonumber},

        formname => $form->{type},

        action   => 'saved',

        id       => $form->{id}

);

    $form->audittrail( $dbh, "", \%audittrail );

    $form->save_recurring( $dbh, $myconfig );

    my $rc = $dbh->commit;

$rc;

}

sub delete {

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

# connect to database

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

# delete spool files

    my $query = qq|

SELECT spoolfile FROM status

WHERE trans_id = ?

AND spoolfile IS NOT NULL|;

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

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

    my $spoolfile;

    my @spoolfiles = ();

    while ( ($spoolfile) = $sth->fetchrow_array ) {

        push @spoolfiles, $spoolfile;

}

    $sth->finish;

    $query = qq|

        SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly

FROM orderitems o

        JOIN parts p ON (p.id = o.parts_id)

WHERE trans_id = ?|;

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

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

    if ( $form->{type} =~ /_order$/ ) {

        $ml = ( $form->{type} eq 'purchase_order' ) ? -1 : 1;

        while ( my ( $id, $ship, $inv, $assembly ) = $sth->fetchrow_array ) {

            $form->update_balance( $dbh, "parts", "onhand", "id = $id",

                $ship * $ml )

              if ( $inv || $assembly );

}

    $sth->finish;

# delete inventory

    $query = qq|DELETE FROM inventory WHERE trans_id = ?|;

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

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

    $sth->finish;

# delete status entries

    $query = qq|DELETE FROM status WHERE trans_id = ?|;

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

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

    $sth->finish;

# delete OE record

    $query = qq|DELETE FROM oe WHERE id = ?|;

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

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

    $sth->finish;

# delete individual entries

    $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;

    $sth->finish;

    $query = qq|DELETE FROM shipto WHERE trans_id = ?|;

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

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

    $sth->finish;

    my %audittrail = (

        tablename => 'oe',

        reference => ( $form->{type} =~ /_order$/ )

        ? $form->{ordnumber}

        : $form->{quonumber},

        formname => $form->{type},

        action   => 'deleted',

        id       => $form->{id}

);

    $form->audittrail( $dbh, "", \%audittrail );

    my $rc = $dbh->commit;

    if ($rc) {

        foreach $spoolfile (@spoolfiles) {

            unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;

}

$rc;

}

sub retrieve {

    use LedgerSMB::PriceMatrix;

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

# connect to database

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

    my $query;

    my $sth;

    my $var;

    my $ref;

    $query = qq|

SELECT value, current_date FROM defaults

         WHERE setting_key = 'curr'|;

    ( $form->{currencies}, $form->{transdate} ) = $dbh->selectrow_array($query);

    if ( $form->{id} ) {

# retrieve order

        $query = qq|

            SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,

                        o.taxincluded, o.shippingpoint, o.shipvia, 

                o.notes, o.intnotes, o.curr AS currency, 

                e.name AS employee, o.employee_id,

                o.$form->{vc}_id, vc.name AS $form->{vc}, 

                o.amount AS invtotal, o.closed, o.reqdate, 

                o.quonumber, o.department_id, 

                d.description AS department, o.language_code, 

o.ponumber

FROM oe o

            JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)

            LEFT JOIN employee e ON (o.employee_id = e.id)

            LEFT JOIN department d ON (o.department_id = d.id)

WHERE o.id = ?|;

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

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

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

        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

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

            $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

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

}

else {

# get last name used

        $form->lastname_used( $myconfig, $dbh, $form->{vc} )

          unless $form->{"$form->{vc}_id"};

        delete $form->{notes};

}

    $dbh->commit;

}

sub exchangerate_defaults {

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

    $dbh = $form->{dbh};

    my $var;

    my $buysell = ( $form->{vc} eq "customer" ) ? "buy" : "sell";

# get default currencies

    my $query = qq|

        SELECT substr(value,1,3), value FROM defaults

         WHERE setting_key = 'curr'|;

    ( $form->{defaultcurrency}, $form->{currencies} ) =

      $dbh->selectrow_array($query);

    $query = qq|

SELECT $buysell

FROM exchangerate

WHERE curr = ?

AND transdate = ?|;

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

    $query = qq~

        SELECT max(transdate || ' ' || $buysell || ' ' || curr)

FROM exchangerate

WHERE curr = ?~;

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

# get exchange rates for transdate or max

    foreach $var ( split /:/, substr( $form->{currencies}, 4 ) ) {

        $eth1->execute( $var, $form->{transdate} );

        my @exchangelist;

        @exchangelist = $eth1->fetchrow_array;

        $form->db_parse_numeric(sth=>$eth1, arrayref=>\@exchangelist);

        $form->{$var} = shift @array;

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

            $eth2->execute($var);

            @exchangelist = $eth2->fetchrow_array;

            $form->db_parse_numeric(sth=>$eth2, arrayref=>\@exchangelist);

            ( $form->{$var} ) = @exchangelist; 

            ( $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->{dbh};

    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;

            my @qtylist = $sth->fetchrow_array;

            $form->db_parse_numeric(sth=>$sth, arrayref=>\@qtylist);

            ($qty) = @qtylist; $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"} );


context:
space:
mode: