path: root/LedgerSMB/IS.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 invoicing module
#
#======================================================================

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


sub invoice_details {
    use LedgerSMB::CP;
    my ($self, $myconfig, $form) = @_;

    $form->{duedate} = $form->{transdate} unless ($form->{duedate});

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

    my $query = qq|
        SELECT ?::date - ?::date
                       AS terms, value
          FROM defaults
         WHERE setting_key = 'weightunit'/|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{duedate}, $form->{transdate})
        || $form->dberror($query);

    ($form->{terms}, $form->{weightunit}) = $sth->fetchrow_array;
    $sth->finish;

    # this is for the template
    $form->{invdate} = $form->{transdate};
  
    my $tax = 0;
    my $item;
    my $i;
    my @sortlist = ();
    my $projectnumber;
    my $projectdescription;
    my $projectnumber_id;
    my $translation;
    my $partsgroup;
  
  
    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 = ?)
            WHERE id = ?|;
    my $prh = $dbh->prepare($query) || $form->dberror($query);

    $query = qq|
        SELECT inventory_accno_id, income_accno_id,
               expense_accno_id, assembly, weight 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} - 1) {

        # 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, 
                        $form->{language_code});

                    ($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}|.
                qq|$inventory_accno_id|, 
            $form->{projectnumber}, $projectnumber_id, 
            $form->{partsgroup}, $sortby ];

    }

    # sort the whole thing by project and group
    @sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;

    my $runningnumber = 1;
    my $sameitem = "";
    my $subtotal;
    my $k = scalar @sortlist;
    my $j = 0;

    foreach $item (@sortlist) {

        $i = $item->[0];
        $j++;

        # heading
        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 serialnumber bin qty ship 
                        unit deliverydate 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"});
    
        if ($form->{"qty_$i"}) {

            $form->{totalqty} += $form->{"qty_$i"};
            $form->{totalship} += $form->{"qty_$i"};
            $form->{totalweight} += ($form->{"qty_$i"} 
                * $form->{"weight_$i"});

            $form->{totalweightship} += ($form->{"qty_$i"} 
                * $form->{"weight_$i"});

            # add number, description and qty to $form->{number}...
            push(@{ $form->{runningnumber} }, $runningnumber++);
            push(@{ $form->{number} }, $form->{"partnumber_$i"});
            push(@{ $form->{sku} }, $form->{"sku_$i"});
            push(@{ $form->{serialnumber} }, 
                $form->{"serialnumber_$i"});

            push(@{ $form->{bin} }, $form->{"bin_$i"});
            push(@{ $form->{description} }, 
                $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->{"qty_$i"}));

            push(@{ $form->{unit} }, $form->{"unit_$i"});
            push(@{ $form->{deliverydate} }, 
                $form->{"deliverydate_$i"});

            push(@{ $form->{projectnumber} }, 
                $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->{"qty_$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) 
                : " ";
            $linetotal = ($linetotal) ? $linetotal : " ";
      
            push(@{ $form->{discount} }, $discount);
            push(@{ $form->{discountrate} }, 
                $form->format_amount(
                    $myconfig, $form->{"discount_$i"}));

            $form->{total} += $linetotal;

            # this is for the subtotals for grouping
            $subtotal += $linetotal;

            $form->{"linetotal_$i"} = 
                $form->format_amount($myconfig, $linetotal, 2);

            push(@{ $form->{linetotal} }, $form->{"linetotal_$i"});
      
            @taxaccounts = Tax::init_taxes(
                $form, $form->{"taxaccounts_$i"});
      
            my $ml = 1;
            my @taxrates = ();
      
            $tax = 0;
      
            if ($form->{taxincluded}) {
                $taxamount = Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 1);
                $taxbase = ($linetotal - $taxamount);
                $tax += Tax::extract_taxes(
                    \@taxaccounts, $form, $linetotal);
            } else {
                $taxamount = Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 0);
                $tax += Tax::apply_taxes(
                    \@taxaccounts, $form, $linetotal);
            }

            foreach $item (@taxaccounts) {
                push @taxrates, 100 * $item->rate;
                $taxaccounts{$item->account} += $item->value;
                if ($form->{taxincluded}) {
                    $taxbase{$item->account} += $taxbase;
                } else {
                    $taxbase{$item->account} += $linetotal;
                }
            }

            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_$j"} || $form->{"assembly_$i"}) {


                            push(@{ $form->{part} },
                                "");
                            push(@{$form->{service}}, 
                                NULL);
                        } else {
                            push(@{$form->{service}}, 
                                "");

                            push(@{ $form->{part} },
                                NULL);
                        }

                        for (
                            qw(taxrates 
                            runningnumber number sku
                            serialnumber bin qty 
                            ship unit deliverydate 
                            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_$j"} || $form->{"assembly_$i"}) {

                            push(@{ $form->{part} },
                                "");

                            push(@{$form->{service}}, 
                                NULL);
                        } else {
                            push(@{$form->{service}}, 
                                "");

                            push(@{ $form->{part} },
                                NULL);
                        }

                        for (
                            qw(taxrates 
                            runningnumber number sku
                            serialnumber bin qty 
                            ship unit deliverydate 
                            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 my $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->format_amount(
                        $myconfig, 
                        $taxbase{$item} + $tax, 2));

            push(@{ $form->{taxbase} }, 
                $form->{"${item}_taxbase"} 
                    = $form->format_amount(
                        $myconfig, $taxbase{$item}, 2));

            push(@{ $form->{tax} }, 
                $form->{"${item}_tax"} 
                    = $form->format_amount(
                        $myconfig, $taxamount, 2));
      
            push(@{ $form->{taxdescription} }, 
                $form->{"${item}_description"});
      
            $form->{"${item}_taxrate"} 
                = $form->format_amount(
                    $myconfig, 
                    $form->{"${item}_rate"} * 100);
            push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"});
            push(@{ $form->{taxnumber} }, 
                $form->{"${item}_taxnumber"});
        }
    }
    
    # adjust taxes for lineitems
    my $total = 0;
    for (@{ $form->{lineitems} }) {
        $total += $_->{tax};
    }
    if ($form->round_amount($total,2) != $form->round_amount($tax,2)) {
        # get largest amount
        for (reverse sort { $a->{tax} <=> $b->{tax} } 
                        @{ $form->{lineitems} }) {

            $_->{tax} -= $total - $tax;
            last;
        }
    }
    $i = 1;
    for (@{ $form->{lineitems} }) {
        push(@{ $form->{linetax} }, 
            $form->format_amount($myconfig, $_->{tax}, 2, ""));
    }
  
 
    for $i (1 .. $form->{paidaccounts}) {
        if ($form->{"paid_$i"}) {
            push(@{ $form->{payment} }, $form->{"paid_$i"});
            my ($accno, $description) 
                = split /--/, $form->{"AR_paid_$i"};

            push(@{ $form->{paymentaccount} }, $description); 
            push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
            push(@{ $form->{paymentsource} }, $form->{"source_$i"});
            push(@{ $form->{paymentmemo} }, $form->{"memo_$i"});

            $form->{paid} 
                += $form->parse_amount(
                    $myconfig, $form->{"paid_$i"});
        }
    }

    for (qw(totalparts totalservices)) { 
        $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2); 
    }
    for (qw(totalqty totalship totalweight)) { 
        $form->{$_} = $form->format_amount($myconfig, $form->{$_});
    }
    $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
    $form->{invtotal} = 
        ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;

    my $c;
    if ($form->{language_code} ne "") {
        $c = new CP $form->{language_code};
    } else {
        $c = new CP $myconfig->{countrycode};
    }
    $c->init;
    my $whole;
    ($whole, $form->{decimal}) = split /\./, $form->{invtotal};
    $form->{decimal} .= "00";
    $form->{decimal} = substr($form->{decimal}, 0, 2);
    $form->{text_decimal} = $c->num2text($form->{decimal} * 1);
    $form->{text_amount} = $c->num2text($whole);
    $form->{integer_amount} = $form->format_amount($myconfig, $whole);
 
    $form->format_string(qw(text_amount text_decimal));

    $form->{total} 
        = $form->format_amount(
            $myconfig, $form->{invtotal} - $form->{paid}, 2);

    $form->{invtotal} 
        = $form->format_amount($myconfig, $form->{invtotal}, 2);

    $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);

    $dbh->commit;
  
}


sub assembly_details {
    my ($myconfig, $form, $dbh2, $id, $oid, $qty) = @_;
    $dbh  = $form->{dbh};
    my $sm = "";
    my $spacer;
  
    $form->{stagger}++;
    if ($form->{format} eq 'html') {
        $spacer = "&nbsp;" x (3 * ($form->{stagger} - 1)) 
            if $form->{stagger} > 1;
    }
    if ($form->{format} =~ /(postscript|pdf)/) {
        if ($form->{stagger} > 1) {
            $spacer = ($form->{stagger} - 1) * 3;
            $spacer = '\rule{'.$spacer.'mm}{0mm}';
        }
    }
  
  # get parts and push them onto the stack
    my $sortorder = "";

    if ($form->{grouppartsgroup}) {
        $sortorder = qq|ORDER BY pg.partsgroup|;
    }
  
    my $query = qq|
           SELECT p.partnumber, p.description, p.unit, a.qty,
                  pg.partsgroup, p.partnumber AS sku
             FROM assembly a
             JOIN parts p ON (a.parts_id = p.id)
        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
            WHERE a.bom = '1'
              AND a.id = ?
        $sortorder|;
    my $sth = $dbh->prepare($query);
    $sth->execute($id) || $form->dberror($query);

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

        for (qw(partnumber description partsgroup)) {
            $form->{"a_$_"} = $ref->{$_};
            $form->format_string("a_$_");
        }

        if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) {
            for (
                qw(taxrates runningnumber number sku 
                serialnumber unit qty ship bin deliverydate 
                projectnumber sellprice listprice netprice 
                discount discountrate linetotal weight 
                itemnotes)
            ) { 

                push(@{ $form->{$_} }, ""); 
            }
            $sm = 
                ($form->{"a_partsgroup"}) 
                ? $form->{"a_partsgroup"} 
                : "--";

            push(@{ $form->{description} }, "$spacer$sm");
            push(@{ $form->{lineitems} }, 
                { amount => 0, tax => 0 });
        }
    
        if ($form->{stagger}) {
      
            push(@{ $form->{description} }, 
                $form->format_amount(
                    $myconfig, 
                    $ref->{qty} * $form->{"qty_$i"}) 
                    .qq| -- $form->{"a_partnumber"}|
                    .qq|, $form->{"a_description"}|);

            for (
                qw(taxrates runningnumber number sku 
                serialnumber unit qty ship bin deliverydate 
                projectnumber sellprice listprice netprice 
                discount discountrate linetotal weight 
                itemnotes)
            ) { 
                push(@{ $form->{$_} }, ""); 
            }
      
        } else {
      
            push(@{ $form->{description} }, 
                qq|$form->{"a_description"}|);

            push(@{ $form->{number} }, $form->{"a_partnumber"});
            push(@{ $form->{sku} }, $form->{"a_partnumber"});

            for (
                qw(taxrates runningnumber ship serialnumber 
                reqdate projectnumber sellprice listprice 
                netprice discount discountrate linetotal weight 
                itemnotes)
            ) { 

                push(@{ $form->{$_} }, "");
            }
      
        }

        push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });

        push(@{ $form->{qty} }, 
            $form->format_amount($myconfig, $ref->{qty} * $qty));
    
        for (qw(unit bin)) {
            $form->{"a_$_"} = $ref->{$_};
            $form->format_string("a_$_");
            push(@{ $form->{$_} }, $form->{"a_$_"});
        }

    }
    $sth->finish;

    $form->{stagger}--;
  
}


sub project_description {
    my ($self, $dbh2, $id) = @_;
    $dbh = $form->{dbh};
    my $query = qq|
        SELECT description
          FROM project
         WHERE id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute($id);
    ($_) = $sth->fetchrow_array($query);

    $_;

}


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

    my $dbh = $form->{dbh};
  
    # get rest for the customer
    my $query = qq|
        SELECT customernumber, name, address1, address2, city,
               state, zipcode, country,
               contact, phone as customerphone, fax as customerfax,
               taxnumber AS customertaxnumber, sic_code AS sic, iban, 
               bic, startdate, enddate
          FROM customer
         WHERE id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{customer_id}) || $form->dberror($query);

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

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

}


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

    my $query;
    my $sth;
    my $null;
    my $project_id;
    my $exchangerate = 0;
    my $keepcleared = 0;
  
    %$form->{acc_trans} = ();

    ($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 p.assembly, p.inventory_accno_id,
               p.income_accno_id, p.expense_accno_id, p.project_id
          FROM parts p
         WHERE p.id = ?|;
    my $pth = $dbh->prepare($query) || $form->dberror($query);
  
    if ($form->{id}) {
        $keepcleared = 1;
        $query = qq|SELECT id FROM ar WHERE id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute($form->{id});

        if ($dbh->selectrow_array($query)) {
            &reverse_invoice($dbh, $form);
        } else {
            $query = qq|INSERT INTO ar (id) VALUES (?)|;
            $sth = $dbh->prepare($query); 
            $sth->execute($form->{id}) || $form->dberror($query);
        }
    
    }
  
    my $uid = localtime;
    $uid .= "$$";
 
    if (! $form->{id}) {
   
        $query = qq|
            INSERT INTO ar (invnumber, employee_id) 
                 VALUES ('$uid', ?)|;
        $sth = $dbh->prepare($query);
        $sth->execute($form->{employee_id}) || $form->dberror($query);

        $query = qq|SELECT id FROM ar WHERE invnumber = '$uid'|;
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

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

        @queries = $form->run_custom_queries('ar', 'INSERT');
    }


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

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

    my $i;
    my $item;
    my $allocated = 0;
    my $taxrate;
    my $tax;
    my $fxtax;
    my @taxaccounts;
    my $amount;
    my $grossamount;
    my $invamount = 0;
    my $invnetamount = 0;
    my $diff = 0;
    my $ml;
    my $invoice_id;
    my $ndx;

    foreach $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"}) {
                ($null, $project_id) 
                    = split /--/, 
                        $form->{"projectnumber_$i"};
            }
            $project_id = $form->{"project_id_$i"} 
                if $form->{"project_id_$i"};

            # 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;
      
            # undo discount formatting
            $form->{"discount_$i"} = 
                $form->parse_amount(
                    $myconfig, $form->{"discount_$i"})/100;
     
            # 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"});
            $ml = 1;
            $tax = 0;
            $fxtax = 0;
      
            if ($form->{taxincluded}) {
                $tax += $amount = 
                    Tax::calculate_taxes(
                        \@taxaccounts, $form,
                        $linetotal, 1);
                $form->{"sellprice_$i"} -= $amount 
                    / $form->{"qty_$i"};

                $fxtax += Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 1);
            } else {
                $tax += $amount = Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 0);

                $fxtax += Tax::calculate_taxes(
                    \@taxaccounts, $form, $linetotal, 0);
            }

            $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);
            }
      
            # add linetotal to income
            $amount = $form->round_amount($linetotal, 2);

            push @{ $form->{acc_trans}{lineitems} }, {
                chart_id => $form->{"income_accno_id_$i"},
                amount => $amount,
                fxgrossamount => $fxlinetotal + $fxtax,
                grossamount => $grossamount,
                project_id => $project_id };

            $ndx = $#{@{$form->{acc_trans}{lineitems}}};

            $form->{"sellprice_$i"} = 
                $form->round_amount(
                    $form->{"sellprice_$i"} 
                        * $form->{exchangerate}, 
                    $decimalplaces);
  
            if ($form->{"inventory_accno_id_$i"} 
                        || $form->{"assembly_$i"}) {
    
                if ($form->{"assembly_$i"}) {
                    # If the assembly consists of all 
                    # services, we don't keep inventory,
                    # so we should not update it
                    $query = qq|
                        SELECT sum(
                               p.inventory_accno_id), 
                               p.assembly
                          FROM parts p
                          JOIN assembly a 
                               ON (a.parts_id = p.id)
                         WHERE a.id = $form->{"id_$i"}
                         GROUP BY p.assembly|;
                    $sth = $dbh->prepare($query);
                    $sth->execute($form->{"id_$i"}) 
                        || $form->dberror($query);
                    my ($inv, $assembly) 
                        = $sth->fetchrow_array;
                    $sth->finish;
              
                    if ($inv || $assembly) {
                        $form->update_balance($dbh,
                            "parts",
                            "onhand",
                            qq|id = |.
                            qq|$form->{"id_$i"}|,
                            $form->{"qty_$i"} * -1) 
                                unless $form->{shipped};
                    }

                    &process_assembly(
                        $dbh, $form, $form->{"id_$i"}, 
                        $form->{"qty_$i"}, $project_id);
                } else {
                    $form->update_balance(
                        $dbh, "parts", "onhand",
                        qq|id = $form->{"id_$i"}|,
                        $form->{"qty_$i"} * -1) 
                            unless $form->{shipped};
                
                    $allocated = &cogs(
                        $dbh, $form, $form->{"id_$i"}, 
                        $form->{"qty_$i"}, $project_id);
                }
            }

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

            unless ($form->{"deliverydate_$i"}){
                undef $form->{"deliverydate_$i"};   
            }   
            $query = qq|
                UPDATE invoice 
                   SET trans_id = ?,
                       parts_id = ?,
                       description = ?,
                       qty = ?,
                       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);

            # add invoice_id
            $form->{acc_trans}{lineitems}[$ndx]->{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->{"paid_$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); 
    }
    $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, 
            $form->{transdate}, $ref->{project_id}, 
            $ref->{invoice_id}) 
                || $form->dberror($query);
        $diff = 0;
        $fxdiff = 0;
    }
  
    $form->{receivables} = $invamount * -1;
 
    delete $form->{acc_trans}{lineitems};
  
    # update exchangerate
    if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
        $form->update_exchangerate(
            $dbh, $form->{currency}, $form->{transdate}, 
            $form->{exchangerate}, 0);
    }

    # record receivable
    if ($form->{receivables}) {
        ($accno) = split /--/, $form->{AR};

        $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->{receivables}, 
            $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 receivable
    if ($invamount == 0) {
        $form->{receivables} = 1;
    }
  
    my $cleared = 0;
  
    # record payments and offsetting AR
    for $i (1 .. $form->{paidaccounts}) {
    
        if ($form->{"paid_$i"}) {
            my ($accno) = split /--/, $form->{"AR_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"}, 'buy');
    
                $form->{"exchangerate_$i"} = 
                    ($exchangerate) 
                    ? $exchangerate 
                    : $form->parse_amount(
                        $myconfig, 
                        $form->{"exchangerate_$i"});
            }
      
 
            # record AR
            $amount = $form->round_amount(
                $form->{"paid_$i"} * $form->{exchangerate}, 2);

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

            # record payment
            $amount = $form->{"paid_$i"} * -1;
            if ($keepcleared) {
                $cleared = ($form->{"cleared_$i"}) ? 1 : 0;
            }
      
            $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->prepare(
                $form->{id}, $accno, $amount, 
                $form->{"datepaid_$i"}, $form->{"source_$i"},
                $form->{"memo_$i"}, $cleared) 
                    || $form->dberror($query);

            # exchangerate difference
            $amount = $form->round_amount(
                ($form->round_amount(
                    $form->{"paid_$i"} 
                        * $form->{"exchangerate_$i"} 
                        - $form->{"paid_$i"}, 2)) * -1, 
                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)
                ) * -1, 
                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"}, 
                    $form->{"exchangerate_$i"}, 0);
            }
        }
    }

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


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

    # save AR record
    $query = qq|
        UPDATE ar set
               invnumber = ?,
               ordnumber = ?,
               quonumber = ?,
               transdate = ?,
               customer_id = ?,
               amount = ?,
               netamount = ?,
               paid = ?,
               datepaid = ?,
               duedate = ?,
               invoice = '1',
               shippingpoint = ?,
               shipvia = ?,
               terms = ?,
               notes = ?,
               intnotes = ?,
               taxincluded = ?,
               curr = ?,
               department_id = ?,
               employee_id = ?,
               till = ?,
               language_code = ?,
               ponumber = ?
         WHERE id = ?
             |;

    $sth = $dbh->prepare($query);
    $sth->execute(
        $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
        $form->{transdate}, $form->{customer_id}, $invamount,
        $invnetamount, $form->{paid}, $form->{datepaid}, 
        $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
        $form->{terms}, $form->{notes}, $form->{intnotes}, 
        $form->{taxincluded}, $form->{currency}, $form->{department_id},
        $form->{employee_id}, $till, $form->{language_code}, 
        $form->{ponumber}, $form->{id})
             || $form->dberror($query);

    # add shipto
    $form->{name} = $form->{customer};
    $form->{name} =~ s/--$form->{customer_id}//;
    $form->add_shipto($dbh, $form->{id});

    # save printed, emailed and queued
    $form->save_status($dbh);
  
    my %audittrail = ( 
        tablename  => 'ar',
        reference  => $form->{invnumber},
        formname   => $form->{type},
        action     => 'posted',
        id         => $form->{id} );
 
    $form->audittrail($dbh, "", \%audittrail);

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

    my $rc = $dbh->commit;


    $rc;
  
}


sub process_assembly {
    my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
    my $dbh = $form->{dbh};
    my $query = qq|
        SELECT a.parts_id, a.qty, p.assembly,
               p.partnumber, p.description, p.unit,
               p.inventory_accno_id, p.income_accno_id,
               p.expense_accno_id
          FROM assembly a
          JOIN parts p ON (a.parts_id = p.id)
         WHERE a.id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($id) || $form->dberror($query);

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

        $allocated = 0;
    
        $ref->{inventory_accno_id} *= 1;
        $ref->{expense_accno_id} *= 1;

        # multiply by number of assemblies
        $ref->{qty} *= $totalqty;
    
        if ($ref->{assembly}) {
            &process_assembly(
                $dbh, $form, $ref->{parts_id}, $ref->{qty}, 
                $project_id);
            next;
        } else {
            if ($ref->{inventory_accno_id}) {
            $allocated = &cogs(
                $dbh, $form, $ref->{parts_id}, 
                $ref->{qty}, $project_id);
            }
        }

        $query = qq|
            INSERT INTO invoice 
                        (trans_id, description, parts_id, qty,
                        sellprice, fxsellprice, allocated, 
                        assemblyitem, unit)
                 VALUES (?, ?, ?, ?, 0, 0, ?, 't', ?)|;

        $sth = $dbh->prepare($query);
        $sth->execute(
            $form->{id}, $ref->{description}, $ref->{parts_id}, 
            $ref->{qty}, $allocated, $ref->{unit})
                || $form->dberror($query);
     
    }

    $sth->finish;

}


sub cogs {
    my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
    my $dbh = $form->{dbh};
    my $query = qq|
           SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice,
                  i.fxsellprice, p.inventory_accno_id, 
                  p.expense_accno_id
             FROM invoice i, parts p
            WHERE i.parts_id = p.id
              AND i.parts_id = ?
              AND (i.qty + i.allocated) < 0
         ORDER BY trans_id|;
    my $sth = $dbh->prepare($query);
    $sth->execute($id) || $form->dberror($query);

    my $allocated = 0;
    my $qty;
  
    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
        if (($qty = (($ref->{qty} * -1) - $ref->{allocated})) 
                                > $totalqty) {
            $qty = $totalqty;
        }
    
        $form->update_balance(
            $dbh, "invoice", "allocated", qq|id = $ref->{id}|, 
            $qty);

        # total expenses and inventory
        # sellprice is the cost of the item
        my $linetotal = $form->round_amount(
            $ref->{sellprice} * $qty, 2);

        # add expense
        push @{ $form->{acc_trans}{lineitems} }, {
            chart_id => $ref->{expense_accno_id},
            amount => $linetotal * -1,
            project_id => $project_id,
            invoice_id => $ref->{id} };

        # deduct inventory
        push @{ $form->{acc_trans}{lineitems} }, {
            chart_id => $ref->{inventory_accno_id},
            amount => $linetotal,
            project_id => $project_id,
            invoice_id => $ref->{id} };

        # add allocated
        $allocated += -$qty;
    
        last if (($totalqty -= $qty) <= 0);
    }

    $sth->finish;

    $allocated;
    $dbh->commit;
}



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

    my $sth;
    $sth = $dbh->prepare($query);
    $sth->execute($form->{id});
    my ($id) = $sth->fetchrow_array($query);

    return unless $id;

    # reverse inventory items
    my $query = qq|
        SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly,
               p.inventory_accno_id
          FROM invoice i
          JOIN parts p ON (i.parts_id = p.id)
         WHERE i.trans_id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

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

        if ($ref->{inventory_accno_id} || $ref->{assembly}) {

            # if the invoice item is not an assemblyitem 
            # adjust parts onhand
            if (!$ref->{assemblyitem}) {
                # adjust onhand in parts table
                $form->update_balance(
                    $dbh, "parts", "onhand",
                    qq|id = $ref->{parts_id}|, $ref->{qty});
            }

            # loop if it is an assembly
            next if ($ref->{assembly});
      
            # de-allocated purchases
            $query = qq|
                  SELECT id, trans_id, allocated
                    FROM invoice
                   WHERE parts_id = ?
                         AND allocated > 0
                ORDER BY trans_id DESC|;
            my $sth = $dbh->prepare($query);
            $sth->execute($ref->{parts_id}) 
                || $form->dberror($query);

            while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
                $qty = $ref->{qty};
                if (($ref->{qty} - $inhref->{allocated}) > 0) {
                    $qty = $inhref->{allocated};
                }
    
                # update invoice
                $form->update_balance(
                    $dbh, "invoice", "allocated",
                    qq|id = $inhref->{id}|, $qty * -1);

                last if (($ref->{qty} -= $qty) <= 0);
            }
            $sth->finish;
        }
    }
  
    $sth->finish;
  
    # delete acc_trans
    $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);
 
    # delete invoice entries
    $query = qq|DELETE FROM invoice 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);

    $dbh->commit;

}



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

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

    &reverse_invoice($dbh, $form);
  
    my %audittrail = ( 
        tablename  => 'ar',
        reference  => $form->{invnumber},
        formname   => $form->{type},
        action     => 'deleted',
        id         => $form->{id} );
 
    $form->audittrail($dbh, "", \%audittrail);
     
    # delete AR record
    my $query = qq|DELETE FROM ar WHERE id = ?|;
    $sth = $dbh->prepare($query);
    $sth->execute($form->{id}) || $form->dberror($query);

    # delete spool files
    $query = qq|
        SELECT spoolfile FROM status
         WHERE trans_id = $form->{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;  

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

    my $rc = $dbh->commit;

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



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

    my $query;

    if ($form->{id}) {
        # get default accounts and last invoice number
        $query = qq|
            SELECT value AS currencies FROM defaults
             WHERE setting_key = 'curr'|;
    } else {
        $query = qq|
            SELECT value AS currencies, current_date AS transdate
              FROM defaults
             WHERE setting_key = 'curr'|;
    }
    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}) {
    
        # retrieve invoice
        $query = qq|
               SELECT a.invnumber, a.ordnumber, a.quonumber,
                      a.transdate, a.paid,
                      a.shippingpoint, a.shipvia, a.terms, a.notes, 
                      a.intnotes,
                      a.duedate, a.taxincluded, a.curr AS currency,
                      a.employee_id, e.name AS employee, a.till, 
                      a.customer_id,
                      a.language_code, a.ponumber
                 FROM ar a
            LEFT JOIN employee e ON (e.id = a.employee_id)
                WHERE a.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 i.description, i.qty, i.fxsellprice, 
                      i.sellprice, i.discount, i.parts_id AS id, 
                      i.unit, i.deliverydate, i.project_id, 
                      pr.projectnumber, i.serialnumber, i.notes,
                      p.partnumber, p.assembly, p.bin,
                      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 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 (p.partsgroup_id = pg.id)
            LEFT JOIN translation t 
                      ON (t.trans_id = p.partsgroup_id 
                      AND t.language_code 
                      = ?)
                WHERE i.trans_id = ?
                      AND NOT i.assemblyitem = '1'
             ORDER BY i.id|;
        $sth = $dbh->prepare($query);
        $sth->execute($form->{language_code}, $form->{id}) 
            || $form->dberror($query);

        # foreign currency
        &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;
    
        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} = "";
            $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} = 
                ($ref->{fxsellprice} 
                    * $form->{$form->{currency}});
            PriceMatrix::price_matrix(
                $pmh, $ref, $form->{transdate}, $decimalplaces, 
                $form, $myconfig);
            $ref->{sellprice} = $ref->{fxsellprice};

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

    }

  
    @queries = $form->run_custom_queries('ar', 'SELECT');
    my $rc = $dbh->commit;
    $rc;

}


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

    my $i = $form->{rowcount};
    my $null;
    my $var;

    my $where = "WHERE p.obsolete = '0' AND NOT p.income_accno_id IS NULL";

    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);
        if ($var == 0) {
            # search by partsgroup, this is for the POS
            $where .= qq| AND pg.partsgroup = |.
                $dbh->quote($form->{"partsgroup_$i"});
        } else {
            $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, p.sellprice,
              p.listprice, p.lastcost, p.unit, p.assembly, p.bin, 
                  p.onhand, p.notes, p.inventory_accno_id, 
                  p.income_accno_id, p.expense_accno_id, pg.partsgroup, 
                  p.partsgroup_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);

    my $ref;
    my $ptref;

    # setup exchange rates
    &exchangerate_defaults($dbh, $form);
  
    # taxes
    $query = qq|
        SELECT c.accno
          FROM chart c
          JOIN partstax pt ON (c.id = pt.chart_id)
         WHERE pt.parts_id = ?|;
    my $tth = $dbh->prepare($query) || $form->dberror($query);


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

    my $transdate = $form->datetonum($myconfig, $form->{transdate});
  
    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 matrix
        PriceMatrix::price_matrix(
            $pmh, $ref, $transdate, $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 ($dbh2, $form) = @_;
    $dbh = $form->{dbh};


    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 buy
          FROM exchangerate
         WHERE curr = ?
               AND transdate = ?|;
    my $eth1 = $dbh->prepare($query) || $form->dberror($query);

    $query = qq/
        SELECT max(transdate || ' ' || buy || ' ' || 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;

}


1;