#=====================================================================
# 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;
use Math::BigFloat;

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

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

    for ( 1 .. $form->{rowcount} ) {
        $form->{"qty_$_"} *= -1 if $form->{reverse};
        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 = 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"},
                $form->{'taxaccounts'}
            );

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

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

                $query = qq| UPDATE ap SET approved = ? WHERE id = ?|;
                $dbh->prepare($query)->execute($form->{approved}, $form->{id}) 
                     || $form->dberror($query);
                if (!$form->{approved}){
                   if (not defined $form->{batch_id}){
                       $form->error($locale->text('Batch ID Missing'));
                   }
                   $query = qq| 
			INSERT INTO voucher (batch_id, trans_id) VALUES (?, ?)|;
                   $sth = $dbh->prepare($query);
                   $sth->execute($form->{batch_id}, $form->{id}) ||
                        $form->dberror($query);
               }
            }

            if ( $form->{"inventory_accno_id_$i"} ) {
		if($form->{"qty_$i"}<0) {
                    # check for unallocated entries at the same price to match our entry
                    $query = qq|
				  SELECT i.id, i.qty, i.allocated, a.transdate
			    	    FROM invoice i
			            JOIN parts p ON (p.id = i.parts_id)
				    JOIN ap a ON (a.id = i.trans_id)
				   WHERE i.parts_id = ? AND (i.qty + i.allocated) < 0 AND i.sellprice = ?
			    	ORDER BY transdate
				    |;
            	    $sth = $dbh->prepare($query);
                    $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
                    my $totalqty = $form->{"qty_$i"};
            	    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
                        $form->db_parse_numeric(sth=>$sth, hashref => $ref);
                    	my $qty = $ref->{qty} + $ref->{allocated};
                        if ( ( $qty - $totalqty ) < 0 ) { $qty = $totalqty; }
                    	# update allocated for sold item
                        $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
                        $allocated += $qty;
                        last if ( ( $totalqty -= $qty ) >= 0 );
            	    }
		}

                # 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) ) {
                    $form->db_parse_numeric(sth=>$sth, hashref => $ref);

                    my $qty = $ref->{qty} + $ref->{allocated};

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

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

                    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 );
                    $form->update_balance( $dbh, "invoice", "allocated",
				qq|id =$invoice_id|,$qty);

                    $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_$i"} *= -1 if $form->{reverse};
        $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->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;


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

    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);
    }
    my $rc = $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,
			       a.on_hold, a.reverse
			  FROM ap a
			 WHERE id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

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

        # 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::price_matrix_query( $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) ) {
            $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
            $ref->{qty} *= -1 if $form->{reverse};
            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) ) {
                $form->db_parse_numeric(sth => $tth, hashref => $ptref);
                $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) ) {
        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

        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;

}

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} );
        @array = $eth1->fetchrow_array;
	$form->db_parse_numeric(sth=> $eth1, arrayref=>\@array);
        $form->{$var} = shift @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;

}

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

sub toggle_on_hold {
    
    my $self = shift @_;
    my $form = shift @_;
    
    if ($form->{id}) { # it's an existing (.. probably) invoice.
        
        my $dbh = $form->{dbh};
        my $sth = $dbh->prepare("SELECT on_hold from ar where ar.id = ?");
        $sth->execute($form->{id});
        my $state = $sth->fetchrow_array;
        my $sth;
        my $n_s; # new state
        if ($state[0] == 't') {
            
            # Turn it off
            $n_s = 'f';
            
        } else {
            $n_s = 't';
        }
        
        my $sth = $dbh->prepare("update ar set on_hold = ?::boolean where ar.id = ?");
        my $code = $dbh->execute($ns, $form->{id});
        
        return 1;
        
    } else { # This shouldn't even be possible, but check for it anyway.
        
        # Definitely, DEFINITELY check it.        
        # happily return 0. Find out about proper error states.
        return 0;
    }
}

1;