path: root/LedgerSMB/IR.pm

#=====================================================================
# LedgerSMB 
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
# 
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors: Jim Rawlings <jim@your-dba.com>
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Inventory received module
#
#======================================================================

package IR;
use LedgerSMB::Tax;
use LedgerSMB::PriceMatrix;
use LedgerSMB::Sysconfig;


sub post_invoice {
    my ($self, $myconfig, $form) = @_;
  
    my $dbh = $form->{dbh};

    for (1 .. $form->{rowcount}){
        unless ($form->{"deliverydate_$_"}){
            $form->{"deliverydate_$_"} =  $form->{transdate};
        }

    }
    my $query;
    my $sth;
    my $ref;
    my $null;
    my $project_id;
    my $exchangerate = 0;
    my $allocated;
    my $taxrate;
    my $taxamount;
    my $diff = 0;
    my $item;
    my $invoice_id;
    my $keepcleared;
  
    ($null, $form->{employee_id}) = split /--/, $form->{employee};

    unless ($form->{employee_id}) {
        ($form->{employee}, $form->{employee_id}) 
            = $form->get_employee($dbh);
    }

    ($null, $form->{department_id}) = split(/--/, $form->{department});
    $form->{department_id} *= 1;
 
    $query = qq|
        SELECT (SELECT value FROM defaults
                 WHERE setting_key = 'fxgain_accno_id') 
               AS fxgain_accno_id, 
               (SELECT value FROM defaults
                 WHERE setting_key = 'fxloss_accno_id')
               AS fxloss_accno_id|;
    my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
  
    $query = qq|
        SELECT inventory_accno_id, income_accno_id, expense_accno_id
          FROM parts
         WHERE id = ?|;

    my $pth = $dbh->prepare($query) || $form->dberror($query);
  
    my %updparts = ();
  
    if ($form->{id}) {

        my $sth;

        $keepcleared = 1;

        $query = qq|SELECT id FROM ap WHERE id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute($form->{id});
    
         if ($sth->fetchrow_array($query)) {
            $query = qq|
                SELECT p.id, p.inventory_accno_id, 
                       p.income_accno_id
                  FROM invoice i
                  JOIN parts p ON (p.id = i.parts_id)
                 WHERE i.trans_id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute($form->{id}) || $form->dberror($query);
            while ($ref = $sth->fetchrow_hashref) {
                if ($ref->{inventory_accno_id} 
                        && $ref->{income_accno_id}) {

                    $updparts{$ref->{id}} = 1;
                }
            }
            $sth->finish;

            &reverse_invoice($dbh, $form);
        } else { 
            $query = qq|INSERT INTO ap (id) VALUES (?)|;
            $sth = $dbh->prepare($query);
            $sth->execute($form->{id}) || $form->dberror($query);
        } 
    }

    my $uid = localtime;
    $uid .= "$$";

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

        $query = qq|
            INSERT INTO ap (invnumber, employee_id)
            VALUES ('$uid', (SELECT id FROM employee
                              WHERE login = ?))|;    
        $sth = $dbh->prepare($query);
        $sth->execute($form->{login}) || $form->dberror($query);
    
        $query = qq|SELECT id FROM ap WHERE invnumber = '$uid'|;
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

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

    }

    my $amount;
    my $grossamount;
    my $allocated;
    my $invamount = 0;
    my $invnetamount = 0;

    if ($form->{currency} eq $form->{defaultcurrency}) {
        $form->{exchangerate} = 1;
    } else {
        $exchangerate = 
            $form->check_exchangerate(
                $myconfig, $form->{currency}, 
                $form->{transdate}, 'sell');
    }
  
    $form->{exchangerate} = 
        ($exchangerate) 
        ? $exchangerate 
        : $form->parse_amount($myconfig, $form->{exchangerate});

    for my $i (1 .. $form->{rowcount}) {
        $form->{"qty_$i"} = 
            $form->parse_amount($myconfig, $form->{"qty_$i"});
    
        if ($form->{"qty_$i"}) {
      
            $pth->execute($form->{"id_$i"});
            $ref = $pth->fetchrow_hashref(NAME_lc);
            for (keys %$ref) {
                $form->{"${_}_$i"} = $ref->{$_};
            }
            $pth->finish;
      
            # project

            if ($form->{"projectnumber_$i"} ne "") {
                ($null, $project_id) = 
                    split /--/, $form->{"projectnumber_$i"};
            }
 
            # undo discount formatting
            $form->{"discount_$i"} = 
                $form->parse_amount(
                    $myconfig, 
                    $form->{"discount_$i"}) / 100;
      
            # keep entered selling price
            my $fxsellprice = 
                $form->parse_amount(
                    $myconfig, $form->{"sellprice_$i"});

            my ($dec) = ($fxsellprice =~ /\.(\d+)/);
            $dec = length $dec;
            my $decimalplaces = ($dec > 2) ? $dec : 2;
          
            # deduct discount
            $form->{"sellprice_$i"} = 
                $fxsellprice - $form->round_amount(
                    $fxsellprice * $form->{"discount_$i"}, 
                    $decimalplaces);

            # linetotal
            my $fxlinetotal = $form->round_amount(
                $form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);

            $amount = $fxlinetotal * $form->{exchangerate};
            my $linetotal = $form->round_amount($amount, 2);
            $fxdiff += $amount - $linetotal;

            @taxaccounts = Tax::init_taxes(
                $form, $form->{"taxaccounts_$i"});

            $tax = Math::BigFloat->bzero();
            $fxtax = Math::BigFloat->bzero();
      
            if ($form->{taxincluded}) {
                $tax += $amount = Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 1);

                $form->{"sellprice_$i"} 
                    -= $amount / $form{"qty_$i"};
            } else {
                $tax += $amount = Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 0);

                $fxtax += Tax::calculate_taxes(
                    \@taxaccounts, $form, $fxlinetotal, 0);
            }
      
            for (@taxaccounts) {
                $form->{acc_trans}{$form->{id}}{$_->account}{amount} += $_->value;
            }

            $grossamount = $form->round_amount($linetotal, 2);

            if ($form->{taxincluded}) {
                $amount = $form->round_amount($tax, 2);
                $linetotal -= $form->round_amount(
                    $tax - $diff, 2);
                $diff = ($amount - $tax);
            }
      
            $amount = $form->round_amount($linetotal, 2);
            $allocated = 0;

            # adjust and round sellprice
            $form->{"sellprice_$i"} = $form->round_amount(
                $form->{"sellprice_$i"} * $form->{exchangerate},
                $decimalplaces);

            # save detail record in invoice table
            $query = qq|
                INSERT INTO invoice (description)
                     VALUES ('$uid')|;
            $dbh->do($query) || $form->dberror($query);

            $query = qq|
                SELECT id FROM invoice
                 WHERE description = '$uid'|;
            ($invoice_id) = $dbh->selectrow_array($query);

                $form->debug;

            $query = qq|
                UPDATE invoice 
                   SET trans_id = ?,
                       parts_id = ?,
                       description = ?,
                       qty = ? * -1,
                       sellprice = ?,
                       fxsellprice = ?,
                       discount = ?,
                       allocated = ?,
                       unit = ?,
                       deliverydate = ?,
                       project_id = ?,
                       serialnumber = ?,
                       notes = ?
                 WHERE id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{id},  $form->{"id_$i"}, 
                $form->{"description_$i"}, $form->{"qty_$i"},
                $form->{"sellprice_$i"}, $fxsellprice,
                $form->{"discount_$i"}, $allocated, 
                $form->{"unit_$i"}, $form->{"deliverydate_$i"},
                $project_id, $form->{"serialnumber_$i"},
                $form->{"notes_$i"}, $invoice_id)
                     || $form->dberror($query);
      

            if ($form->{"inventory_accno_id_$i"}) {

                # add purchase to inventory
                push @{ $form->{acc_trans}{lineitems} }, 
                    {chart_id => 
                        $form->{"inventory_accno_id_$i"},
                    amount => $amount,
                    fxgrossamount => $fxlinetotal + 
                        $form->round_amount($fxtax, 2),
                    grossamount => $grossamount,
                    project_id => $project_id,
                    invoice_id => $invoice_id };
    
    
                $updparts{$form->{"id_$i"}} = 1;

                # update parts table
                $form->update_balance(
                    $dbh, "parts", "onhand",
                    qq|id = $form->{"id_$i"}|,
                    $form->{"qty_$i"}) 
                        unless $form->{shipped};
                  
    
                # check if we sold the item
                $query = qq|
                      SELECT i.id, i.qty, i.allocated, 
                             i.trans_id, i.project_id,
                             p.inventory_accno_id, 
                             p.expense_accno_id, a.transdate
                        FROM invoice i
                        JOIN parts p ON (p.id = i.parts_id)
                        JOIN ar a ON (a.id = i.trans_id)
                       WHERE i.parts_id = ?
                             AND (i.qty + i.allocated) > 0
                    ORDER BY transdate|;
                $sth = $dbh->prepare($query);
                $sth->execute($form->{"id_$i"}) 
                    || $form->dberror($query);

                my $totalqty = $form->{"qty_$i"};
    
                while (my $ref = 
                    $sth->fetchrow_hashref(NAME_lc)) {
      
                    my $qty = $ref->{qty} 
                        + $ref->{allocated};

                    if (($qty - $totalqty) > 0) {
                        $qty = $totalqty;
                    }

                    $linetotal = $form->round_amount(
                        $form->{"sellprice_$i"} * $qty, 
                        2);

                    $ref->{project_id} ||= 'NULL';

                    if ($linetotal) {
                        $query = qq|
                            INSERT INTO acc_trans 
                                        (trans_id, 
                                        chart_id, 
                                        amount, 
                                        transdate, 
                                        project_id, 
                                        invoice_id)
                                 VALUES (?, ?, ?, ?,
                                        ?, ?)|;

                            $sth = $dbh->prepare($query);
                        $sth->execute(
                            $ref->{trans_id},
                            $ref->{inventory_accno_id},
                            $linetotal, 
                            $ref->{transdate},
                            $ref->{project_id},
                            $invoice_id
                            ) || $form->dberror(
                                $query);

                        # add expense
                        $query = qq|
                            INSERT INTO acc_trans 
                                        (trans_id, 
                                        chart_id, 
                                        amount, 
                                        transdate, 
                                        project_id,
                                        invoice_id)
                                 VALUES (?, ?, ?, ?,
                                        ?, ?)|;
                        $sth = $dbh->prepare($query) ;
                        $sth->execute(
                            $ref->{trans_id}, 
                            $ref->{expense_accno_id},
                            $linetotal * -1, 
                            $ref->{transdate},
                            $ref->{project_id}, 
                            $invoice_id
                            ) || $form->dberror(
                                $query);
                    }
      
                    # update allocated for sold item
                    $form->update_balance(
                        $dbh, "invoice", "allocated",
                        qq|id = $ref->{id}|, $qty * -1);
    
                    $allocated += $qty;

                    last if (($totalqty -= $qty) <= 0);
                }

                $sth->finish;

            } else {
    
                # add purchase to expense
                push @{ $form->{acc_trans}{lineitems} }, {
                    chart_id => 
                        $form->{"expense_accno_id_$i"},
                    amount => $amount,
                    fxgrossamount => $fxlinetotal 
                        + $form->round_amount(
                        $fxtax, 2),
                    grossamount => $grossamount,
                    project_id => $project_id,
                    invoice_id => $invoice_id };
    
            }
        }
    }

    $form->{paid} = 0;
    for $i (1 .. $form->{paidaccounts}) {
        $form->{"paid_$i"} = 
            $form->parse_amount($myconfig, $form->{"paid_$i"});
        $form->{paid} += $form->{"paid_$i"};
        $form->{datepaid} = $form->{"datepaid_$i"} 
            if ($form->{"datepaid_$i"});
    }

    # add lineitems + tax
    $amount = 0;
    $grossamount = 0;
    $fxgrossamount = 0;
    for (@{ $form->{acc_trans}{lineitems} }) {
        $amount += $_->{amount};
        $grossamount += $_->{grossamount};
        $fxgrossamount += $_->{fxgrossamount};
    }
    $invnetamount = $amount;

    $amount = 0;
    for (split / /, $form->{taxaccounts}) {
        $amount += $form->{acc_trans}{$form->{id}}{$_}{amount} 
            = $form->round_amount(
                $form->{acc_trans}{$form->{id}}{$_}{amount}, 2);

        $form->{acc_trans}{$form->{id}}{$_}{amount} *= -1;
    }
    $invamount = $invnetamount + $amount;

    $diff = 0;
    if ($form->{taxincluded}) {
        $diff = $form->round_amount($grossamount - $invamount, 2);
        $invamount += $diff;
    }
    $fxdiff = $form->round_amount($fxdiff,2);
    $invnetamount += $fxdiff;
    $invamount += $fxdiff;

    if ($form->round_amount($form->{paid} - $fxgrossamount,2) == 0) {
        $form->{paid} = $invamount;
    } else {
        $form->{paid} = $form->round_amount(
            $form->{paid} * $form->{exchangerate}, 2);
    }
 
    foreach $ref (sort { $b->{amount} <=> $a->{amount} } 
                    @ { $form->{acc_trans}{lineitems} }) {

        $amount = $ref->{amount} + $diff + $fxdiff;
        $query = qq|
            INSERT INTO acc_trans (trans_id, chart_id, amount,
                        transdate, project_id, invoice_id)
                        VALUES (?, ?, ?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
        $sth->execute(
            $form->{id}, $ref->{chart_id}, $amount * -1, 
            $form->{transdate}, $ref->{project_id}, 
            $ref->{invoice_id}) || $form->dberror($query);
        $diff = 0;
        $fxdiff = 0;
    }

    $form->{payables} = $invamount;
  
    delete $form->{acc_trans}{lineitems};
  
    # update exchangerate
    if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
        $form->update_exchangerate(
            $dbh, $form->{currency}, $form->{transdate}, 0, 
            $form->{exchangerate});
    }
  
    # record payable
    if ($form->{payables}) {
        ($accno) = split /--/, $form->{AP};

        $query = qq|
            INSERT INTO acc_trans (trans_id, chart_id, amount,
                                transdate)
                         VALUES (?, (SELECT id FROM chart WHERE accno = ?),
                                ?, ?)|;
        $sth = $dbh->prepare($query);
        $sth->execute(
            $form->{id}, $accno, $form->{payables}, 
            $form->{transdate}
            ) || $form->dberror($query);
    }

    foreach my $trans_id (keys %{$form->{acc_trans}}) {
        foreach my $accno (keys %{ $form->{acc_trans}{$trans_id} }) {
            $amount = $form->round_amount(
                $form->{acc_trans}{$trans_id}{$accno}{amount}, 
                2);

            if ($amount) {
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, amount,
                                transdate)
                            VALUES (?, (SELECT id FROM chart
                                WHERE accno = ?),
                                ?, ?)|;
                $sth =$dbh->prepare($query);
                $sth->execute(
                    $trans_id, $accno, $amount, 
                    $form->{transdate}
                    ) || $form->dberror($query);
            }
        }
    }

    # if there is no amount but a payment record payable
    if ($invamount == 0) {
        $form->{payables} = 1;
    }

    my $cleared = 0;
  
    # record payments and offsetting AP
    for my $i (1 .. $form->{paidaccounts}) {

        if ($form->{"paid_$i"}) {
            my ($accno) = split /--/, $form->{"AP_paid_$i"};
            $form->{"datepaid_$i"} = $form->{transdate} 
                unless ($form->{"datepaid_$i"});

            $form->{datepaid} = $form->{"datepaid_$i"};

            $exchangerate = 0;

            if ($form->{currency} eq $form->{defaultcurrency}) {
                $form->{"exchangerate_$i"} = 1;
            } else {
                $exchangerate = $form->check_exchangerate(
                    $myconfig, $form->{currency}, 
                    $form->{"datepaid_$i"}, 'sell');

                $form->{"exchangerate_$i"} = 
                    ($exchangerate) 
                    ? $exchangerate 
                    : $form->parse_amount(
                        $myconfig, 
                        $form->{"exchangerate_$i"});
            }
      

            # record AP
            $amount = ($form->round_amount(
                $form->{"paid_$i"} * $form->{exchangerate}, 
                2)) * -1;
      
            if ($form->{payables}) {
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, amount,
                                    transdate)
                        VALUES (?, (SELECT id FROM chart
                                 WHERE accno = ?),
                                     ?, ?)|;

                $sth = $dbh->$dbh->prepare($query);
                $sth->execute(
                    $form->{id}, $form->{AP}, $amount, 
                    $form->{"datepaid_$i"}
                        ) || $form->dberror($query);
            }

            if ($keepcleared) {
                $cleared = ($form->{"cleared_$i"}) ? 1 : 0;
            }
      
            # record payment
            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, amount, 
                            transdate, source, memo, cleared)
                     VALUES (?, (SELECT id FROM chart 
                                  WHERE accno = ?),
                            ?, ?, ?, ?, ?)|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{id}, $accno, $form->{"paid_$i"},
                $form->{"datepaid_$i"}, $form->{"source_$i"},
                $form->{"memo_$i"}, $cleared
                )  || $form->dberror($query);

            # exchangerate difference
            $amount = $form->round_amount(
                $form->{"paid_$i"} 
                    * $form->{"exchangerate_$i"} 
                    - $form->{"paid_$i"}, 
                2);
      
            if ($amount) {
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, amount,
                                transdate, source, 
                                fx_transaction, cleared)
                         VALUES (?, (SELECT id FROM chart
                                      WHERE accno = ?),
                                ?, ?, ?, '1', ?)|;
                $sth = $dbh->prepare($query);
                $sth->execute(
                    $form->{id}, $accno, $amount, 
                    $form->{"datepaid_$i"}, 
                    $form->{"source_$i"}, $cleared
                    ) || $form->dberror($query);

            }
 
            # gain/loss
            $amount = $form->round_amount(
                $form->round_amount(
                    $form->{"paid_$i"} 
                        * $form->{exchangerate},2) 
                - $form->round_amount(
                    $form->{"paid_$i"} 
                        * $form->{"exchangerate_$i"},2)
                , 2);
      
            if ($amount) {
                my $accno_id = 
                    ($amount > 0) 
                    ? $fxgain_accno_id 
                    : $fxloss_accno_id;
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, amount,
                                transdate, fx_transaction, 
                                cleared)
                         VALUES (?, ?, ?, ?, '1', ?)|;

                $sth = $dbh->prepare($query);
                $sth->execute(
                    $form->{id}, $accno_id, $amount, 
                    $form->{"datepaid_$i"}, $cleared
                ) || $form->dberror($query);
            }
      
            # update exchange rate
            if (($form->{currency} ne $form->{defaultcurrency}) 
                            && !$exchangerate) {

                $form->update_exchangerate(
                    $dbh, $form->{currency}, 
                    $form->{"datepaid_$i"}, 0, 
                    $form->{"exchangerate_$i"});
            }
        }
    }

    # set values which could be empty
    $form->{taxincluded} *= 1;

    $form->{invnumber} = 
        $form->update_defaults($myconfig, "vinumber", $dbh) 
            unless $form->{invnumber};

    # save AP record
    $query = qq|
        UPDATE ap 
           SET invnumber = ?,
               ordnumber = ?,
               quonumber = ?,
               transdate = ?,
               vendor_id = ?,
               amount = ?,
               netamount = ?,
               paid = ?,
               datepaid = ?,
               duedate = ?,
               invoice = '1',
               shippingpoint = ?,
               shipvia = ?,
               taxincluded = ?,
               notes = ?,
               intnotes = ?,
               curr = ?,
               department_id = ?,
               employee_id = ?,
               language_code = ?,
               ponumber = ?
         WHERE id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute(
        $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
        $form->{transdate}, $form->{vendor_id}, $invamount, 
        $invnetamount, $form->{paid}, $form->{datepaid}, 
        $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
        $form->{taxincluded}, $form->{notes}, $form->{intnotes},
        $form->{currency}, $form->{department_id}, $form->{employee_id},
        $form->{language_code}, $form->{ponumber}, $form->{id}
        ) || $form->dberror($query);
    # add shipto
    $form->{name} = $form->{vendor};
    $form->{name} =~ s/--$form->{vendor_id}//;
    $form->add_shipto($dbh, $form->{id});
  
    my %audittrail = ( 
        tablename  => 'ap',
        reference  => $form->{invnumber},
        formname   => $form->{type},
        action     => 'posted',
        id         => $form->{id} );
 
    $form->audittrail($dbh, "", \%audittrail);
 
    my $rc = $dbh->commit;

    foreach $item (keys %updparts) {
        $item = $dbh->quote($item);
        $query = qq|
            UPDATE parts 
               SET avgcost = avgcost($item),
                   lastcost = lastcost($item)
             WHERE id = $item|;
        $dbh->prepare($query) || $form->dberror($query);
        $dbh->commit;
    }
  
    $rc;
  
}



sub reverse_invoice {
    my ($dbh, $form) = @_;
  
    my $query = qq|SELECT id FROM ap
                 WHERE id = $form->{id}|;
    my ($id) = $dbh->selectrow_array($query);

    return unless $id;
  
    # reverse inventory items
    $query = qq|
        SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
               i.qty, i.allocated, i.sellprice, i.project_id
          FROM invoice i, parts p
         WHERE i.parts_id = p.id
               AND i.trans_id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    my $netamount = 0;
  
    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
        $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);

        if ($ref->{inventory_accno_id}) {
            # update onhand
            $form->update_balance($dbh,
                            "parts",
                "onhand",
                qq|id = $ref->{parts_id}|,
                $ref->{qty});
      
            # if $ref->{allocated} > 0 than we sold that many items
            if ($ref->{allocated} > 0) {

                # get references for sold items
                $query = qq|
                      SELECT i.id, i.trans_id, i.allocated, 
                             a.transdate
                        FROM invoice i, ar a
                       WHERE i.parts_id = ?
                             AND i.allocated < 0
                             AND i.trans_id = a.id
                    ORDER BY transdate DESC|;
                my $sth = $dbh->prepare($query);
                $sth->execute($ref->{parts_id}) 
                    || $form->dberror($query);

                while (my $pthref = 
                    $sth->fetchrow_hashref(NAME_lc)) {

                    my $qty = $ref->{allocated};
      
                    if (($ref->{allocated} + 
                            $pthref->{allocated}) 
                                    > 0) {
                        $qty = $pthref->{allocated} 
                            * -1;
                    }

                    my $amount = $form->round_amount(
                        $ref->{sellprice} * $qty, 2);

                    #adjust allocated
                    $form->update_balance(
                        $dbh, "invoice", "allocated",
                        qq|id = $pthref->{id}|, $qty);

                    # add reversal for sale
                    $ref->{project_id} *= 1;
                    $query = qq|
                        INSERT INTO acc_trans 
                                    (trans_id, 
                                chart_id, amount, 
                                    transdate, 
                                    project_id)
                             VALUES (?, ?, ?, ?, ?)|;
                    $sth = $dbh->prepare($query);
                    $sth->execute(
                        $pthref->{trans_id}, 
                        $ref->{expense_accno_id},
                        $amount, $form->{transdate}, 
                        $ref->{project_id}
                        ) || $form->dberror($query);
      
                    $query = qq|
                        INSERT INTO acc_trans 
                                    (trans_id, chart_id,
                                    amount, transdate, 
                                    project_id)
                             VALUES (?, ?, ?, ?, ?)|;
                    $sth = $dbh->do($query);
                    $sth->execute(
                        $pthref->{trans_id}, 
                        $ref->{inventory_accno_id},
                        $amount * -1,
                        $form->{transdate}, 
                        $ref->{project_id}
                        ) || $form->dberror($query);
                    last if (($ref->{allocated} -= $qty) 
                        <= 0);
                }
                $sth->finish;
            }
        }
    }
    $sth->finish;
  
    # delete acc_trans
    $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
    $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    # delete invoice entries
    $query = qq|DELETE FROM invoice WHERE trans_id = ?|;
    $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    $query = qq|DELETE FROM shipto WHERE trans_id = ?|;
    $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    $dbh->commit;

} 



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

    # connect to database
    my $dbh = $form->{dbh};
  
    my %audittrail = ( 
        tablename  => 'ap',
        reference  => $form->{invnumber},
        formname   => $form->{type},
        action     => 'deleted',
        id         => $form->{id} );
 
    $form->audittrail($dbh, "", \%audittrail);

    my $query = qq|SELECT parts_id FROM invoice WHERE trans_id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    my $item;
    my %updparts = ();
    while (($item) = $sth->fetchrow_array) {
        $updparts{$item} = 1;
    }
    $sth->finish;
 
    &reverse_invoice($dbh, $form);
  
    # delete AP record
    $query = qq|DELETE FROM ap WHERE id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    # delete spool files
    $query = qq|
        SELECT spoolfile FROM status 
         WHERE trans_id = ?
               AND spoolfile IS NOT NULL|;
    my $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;
  
    # delete status entries
    $query = qq|DELETE FROM status WHERE trans_id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);
  

    if ($rc) {
        foreach $item (keys %updparts) {
            $item = $dbh->quote($item);
            $query = qq|
                UPDATE parts 
                   SET avgcost = avgcost($item),
                       lastcost = lastcost($item)
                 WHERE id = $item|;
            $dbh->do($query) || $form->dberror($query);
        }

        foreach $spoolfile (@spoolfiles) {
            unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" 
                if $spoolfile;
        }
    }
  
    my $rc = $dbh->commit;

    $rc;
  
}



sub retrieve_invoice {
    my ($self, $myconfig, $form) = @_;
  
    # connect to database
    my $dbh = $form->{dbh};

    my $query;

    if ($form->{id}) {
        # get default accounts and last invoice number
        $query = qq|
            SELECT (select c.accno FROM chart c 
                     WHERE c.id = (SELECT value FROM defaults 
                                    WHERE setting_key = 
                                          'inventory_accno_id'))
                   AS inventory_accno,

                   (SELECT c.accno FROM chart c
                 WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'income_accno_id'))
                   AS income_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'expense_accno_id'))
                   AS expense_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'fxgain_accno_id'))
                   AS fxgain_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'fxloss_accno_id'))
                   AS fxloss_accno, 
                   (SELECT value FROM defaults
                     WHERE setting_key = 'curr') AS currencies|;
    } else {
        $query = qq|
            SELECT (select c.accno FROM chart c 
                     WHERE c.id = (SELECT value FROM defaults 
                                    WHERE setting_key = 
                                          'inventory_accno_id'))
                   AS inventory_accno,

                   (SELECT c.accno FROM chart c
                 WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'income_accno_id'))
                   AS income_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'expense_accno_id'))
                   AS expense_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'fxgain_accno_id'))
                   AS fxgain_accno,

                   (SELECT c.accno FROM chart c
                     WHERE c.id = (SELECT value FROM defaults
                                    WHERE setting_key =
                                          'fxloss_accno_id'))
                   AS fxloss_accno, 
                   (SELECT value FROM defaults
                     WHERE setting_key = 'curr') AS currencies,
                   current_date AS transdate|;
    }
    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    my $ref = $sth->fetchrow_hashref(NAME_lc);
    for (keys %$ref) {
        $form->{$_} = $ref->{$_};
    }
    $sth->finish;


    if ($form->{id}) {
    
        $query = qq|
            SELECT a.invnumber, a.transdate, a.duedate,
                   a.ordnumber, a.quonumber, a.paid, a.taxincluded,
                   a.notes, a.intnotes, a.curr AS currency, 
                   a.vendor_id, a.language_code, a.ponumber
              FROM ap a
             WHERE 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 shipto
        $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;
    
        # retrieve individual items
        $query = qq|
               SELECT p.partnumber, i.description, i.qty, 
                      i.fxsellprice, i.sellprice,
                      i.parts_id AS id, i.unit, p.bin, 
                      i.deliverydate,
                      pr.projectnumber, i.project_id, 
                      i.serialnumber, 
                      i.discount, i.notes, pg.partsgroup, 
                      p.partsgroup_id, p.partnumber AS sku, 
                      p.weight, p.onhand, p.inventory_accno_id, 
                      p.income_accno_id, p.expense_accno_id,
                      t.description AS partsgrouptranslation
                 FROM invoice i
                 JOIN parts p ON (i.parts_id = p.id)
            LEFT JOIN project pr ON (i.project_id = pr.id)
            LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
            LEFT JOIN translation t 
                      ON (t.trans_id = p.partsgroup_id 
                      AND t.language_code = ?)
                WHERE i.trans_id = ?
                 ORDER BY i.id|;
        $sth = $dbh->prepare($query);
        $sth->execute($form->{language_code}, $form->{id}) 
            || $form->dberror($query);

        # exchangerate defaults
        &exchangerate_defaults($dbh, $form);

        # price matrix and vendor partnumber
        my $pmh = PriceMatrix::PriceMatrixQuery($dbh, $form);

        # tax rates for part
        $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);

        my $ptref;

        while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

            my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);
            $dec = length $dec;
            my $decimalplaces = ($dec > 2) ? $dec : 2;

            $tth->execute($ref->{id});
            $ref->{taxaccounts} = "";
            my $taxrate = 0;
      
            while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
                $ref->{taxaccounts} .= "$ptref->{accno} ";
                $taxrate += $form->{"$ptref->{accno}_rate"};
            }
      
            $tth->finish;
            chop $ref->{taxaccounts};

            # price matrix
            $ref->{sellprice} = $form->round_amount(
                $ref->{fxsellprice} 
                    * $form->{$form->{currency}}, 
                $decimalplaces);
            PriceMatrix::price_matrix(
                $pmh, $ref, $decimalplaces, $form, $myconfig);

            $ref->{sellprice} = $ref->{fxsellprice};
            $ref->{qty} *= -1;

            $ref->{partsgroup} = $ref->{partsgrouptranslation} 
                if $ref->{partsgrouptranslation};
      
            push @{ $form->{invoice_details} }, $ref;
      
        }
    
        $sth->finish;
    
    }
  
  
    my $rc = $dbh->commit;
  
    $rc;
  
}


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

    $dbh = $form->{dbh};
    my $i = $form->{rowcount};
    my $null;
    my $var;
  
    # don't include assemblies or obsolete parts
    my $where = "WHERE p.assembly = '0' AND p.obsolete = '0'";
  
    if ($form->{"partnumber_$i"} ne "") {
        $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
        $where .= " AND lower(p.partnumber) LIKE $var";
    }
  
    if ($form->{"description_$i"} ne "") {
        $var = $dbh->quote($form->like(lc $form->{"description_$i"}));
        if ($form->{language_code} ne "") {
            $where .= " AND lower(t1.description) LIKE $var";
        } else {
            $where .= " AND lower(p.description) LIKE $var";
        }
    }

    if ($form->{"partsgroup_$i"} ne "") {
        ($null, $var) = split /--/, $form->{"partsgroup_$i"};
        $var = $dbh->quote($var);
        $where .= qq| AND p.partsgroup_id = $var|;
    }
  
    if ($form->{"description_$i"} ne "") {
        $where .= " ORDER BY 3";
    } else {
        $where .= " ORDER BY 2";
    }


    my $query = qq|
           SELECT p.id, p.partnumber, p.description,
                  pg.partsgroup, p.partsgroup_id,
                  p.lastcost AS sellprice, p.unit, p.bin, p.onhand, 
                  p.notes, p.inventory_accno_id, p.income_accno_id, 
                  p.expense_accno_id, p.partnumber AS sku, p.weight,
                  t1.description AS translation, 
                  t2.description AS grouptranslation
             FROM parts p
        LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
        LEFT JOIN translation t1 
                  ON (t1.trans_id = p.id AND t1.language_code = ?)
        LEFT JOIN translation t2 
                  ON (t2.trans_id = p.partsgroup_id 
                  AND t2.language_code = ?)
             $where|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{language_code}, $form->{language_code}) 
        || $form->dberror($query);
  
    # foreign currency
    &exchangerate_defaults($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);

    # price matrix
    my $pmh = PriceMatrix::price_matrix_query($dbh, $form);

    my $ref;
    my $ptref;
  
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

        my ($dec) = ($ref->{sellprice} =~ /\.(\d+)/);
        $dec = length $dec;
        my $decimalplaces = ($dec > 2) ? $dec : 2;

        # get taxes for part
        $tth->execute($ref->{id});

        $ref->{taxaccounts} = "";
        while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
            $ref->{taxaccounts} .= "$ptref->{accno} ";
        }
        $tth->finish;
        chop $ref->{taxaccounts};

        # get vendor price and partnumber
        PriceMatrix::price_matrix(
            $pmh, $ref, $decimalplaces, $form, $myconfig);

        $ref->{description} = $ref->{translation} 
            if $ref->{translation};
        $ref->{partsgroup} = $ref->{grouptranslation} 
            if $ref->{grouptranslation};
    
        push @{ $form->{item_list} }, $ref;
    
    }
  
    $sth->finish;
    $dbh->commit;
  
}


sub exchangerate_defaults {
    my ($dbh, $form) = @_;

    my $var;
  
    # get default currencies
    my $query = qq|
        SELECT substr(value,1,3), value FROM defaults
         WHERE setting_key = 'curr'|;
    my $eth = $dbh->prepare($query) || $form->dberror($query);
    $eth->execute;
    ($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
    $eth->finish;

    $query = qq|
        SELECT sell
          FROM exchangerate
         WHERE curr = ?
               AND transdate = ?|;
    my $eth1 = $dbh->prepare($query) || $form->dberror($query);

    $query = qq/
        SELECT max(transdate || ' ' || sell || ' ' || 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 vendor_details {
    my ($self, $myconfig, $form) = @_;
      
    # connect to database
    my $dbh = $form->{dbh};

    # get rest for the vendor
    my $query = qq|
        SELECT vendornumber, name, address1, address2, city, state,
               zipcode, country, contact, phone as vendorphone, 
               fax as vendorfax, vendornumber, 
               taxnumber AS vendortaxnumber, sic_code AS sic, iban, bic,
               gifi_accno AS gifi, startdate, enddate
          FROM vendor
         WHERE id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{vendor_id}) || $form->dberror($query);

    $ref = $sth->fetchrow_hashref(NAME_lc);
    for (keys %$ref) {
        $form->{$_} = $ref->{$_};
    }

    $sth->finish;
    $dbh->commit;

}


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

    # connect to database
    my $dbh = $form->{dbh};

    my $query = qq|
           SELECT accno, description, link
             FROM chart
                WHERE link LIKE '%IC%'
         ORDER BY accno|;
    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
        foreach my $key (split(/:/, $ref->{link})) {
            if ($key =~ /IC/) {
                push @{ $form->{IC_links}{$key} }, 
                    { accno => $ref->{accno},
                    description => $ref->{description} };
            }
        }
    }

    $sth->finish;
}

1;