From ff0a9bb6f28f1b15459582354f2c4b69268914bb Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 30 Oct 2006 03:04:59 +0000 Subject: Mostly done with IR.pm git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@379 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/IR.pm | 1671 +++++++++++++++++++++++++++++++------------------------ 1 file changed, 953 insertions(+), 718 deletions(-) diff --git a/LedgerSMB/IR.pm b/LedgerSMB/IR.pm index 5ccb0b15..27e9aa22 100755 --- a/LedgerSMB/IR.pm +++ b/LedgerSMB/IR.pm @@ -38,863 +38,1098 @@ use LedgerSMB::Sysconfig; sub post_invoice { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database, turn off autocommit - my $dbh = $form->dbconnect_noauto($myconfig); - - 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; + my $dbh = $form->{dbh}; + + 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->{employee_id}) = split /--/, $form->{employee}; - ($null, $form->{department_id}) = split(/--/, $form->{department}); - $form->{department_id} *= 1; + 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 fxgain_accno_id, fxloss_accno_id - FROM defaults d|; - my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query); + $query = qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults d|; + 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); + $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 = (); + my %updparts = (); - if ($form->{id}) { - $keepcleared = 1; - $query = qq|SELECT id FROM ap - WHERE id = $form->{id}|; + if ($form->{id}) { + + my $sth; + + $keepcleared = 1; + + $query = qq|SELECT id FROM ap WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}); - if ($dbh->selectrow_array($query)) { - $query = qq|SELECT p.id, p.inventory_accno_id, p.income_accno_id - FROM invoice i - JOIN parts p ON (p.id = i.parts_id) - WHERE i.trans_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref) { - if ($ref->{inventory_accno_id} && $ref->{income_accno_id}) { - $updparts{$ref->{id}} = 1; + if ($sth->fetchrow_array($query)) { + $query = qq| + SELECT p.id, p.inventory_accno_id, + p.income_accno_id + FROM invoice i + JOIN parts p ON (p.id = i.parts_id) + WHERE i.trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + while ($ref = $sth->fetchrow_hashref) { + if ($ref->{inventory_accno_id} + && $ref->{income_accno_id}) { + + $updparts{$ref->{id}} = 1; + } + } + $sth->finish; + + &reverse_invoice($dbh, $form); + } else { + $query = qq|INSERT INTO ap (id) VALUES (?)|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + } } - } - $sth->finish; - - &reverse_invoice($dbh, $form); - } else { - $query = qq|INSERT INTO ap (id) - VALUES ($form->{id})|; - $dbh->do($query) || $form->dberror($query); - } - } - my $uid = localtime; - $uid .= "$$"; + my $uid = localtime; + $uid .= "$$"; - if (! $form->{id}) { + if (! $form->{id}) { - $query = qq|INSERT INTO ap (invnumber, employee_id) - VALUES ('$uid', (SELECT id FROM employee - WHERE login = '$form->{login}'))|; - $dbh->do($query) || $form->dberror($query); + $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); + $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; - - } + ($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'); - } + 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"}); + $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"}) { + if ($form->{"qty_$i"}) { - $pth->execute($form->{"id_$i"}); - $ref = $pth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { - $form->{"${_}_$i"} = $ref->{$_}; - } - $pth->finish; + $pth->execute($form->{"id_$i"}); + $ref = $pth->fetchrow_hashref(NAME_lc); + for (keys %$ref) { + $form->{"${_}_$i"} = $ref->{$_}; + } + $pth->finish; - # project - $project_id = 'NULL'; - if ($form->{"projectnumber_$i"} ne "") { - ($null, $project_id) = split /--/, $form->{"projectnumber_$i"}; - } + # project + $project_id = 'NULL'; + + 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; + # 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; + # 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); + # 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); + # 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; + $amount = $fxlinetotal * $form->{exchangerate}; + my $linetotal = $form->round_amount($amount, 2); + $fxdiff += $amount - $linetotal; - @taxaccounts = Tax::init_taxes($form, $form->{"taxaccounts_$i"}); + @taxaccounts = Tax::init_taxes( + $form, $form->{"taxaccounts_$i"}); - $tax = Math::BigFloat->bzero(); - $fxtax = Math::BigFloat->bzero(); + $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); - } + 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); - } + 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 = $form->{id}, - parts_id = $form->{"id_$i"}, - description = |.$dbh->quote($form->{"description_$i"}).qq|, - qty = $form->{"qty_$i"} * -1, - sellprice = $form->{"sellprice_$i"}, - fxsellprice = $fxsellprice, - discount = $form->{"discount_$i"}, - allocated = $allocated, - unit = |.$dbh->quote($form->{"unit_$i"}).qq|, - deliverydate = |.$form->dbquote($form->{"deliverydate_$i"}, SQL_DATE).qq|, - project_id = $project_id, - serialnumber = |.$dbh->quote($form->{"serialnumber_$i"}).qq|, - notes = |.$dbh->quote($form->{"notes_$i"}).qq| - WHERE id = $invoice_id|; - $dbh->do($query) || $form->dberror($query); + $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 = ? * -1, + sellprice = ?, + fxsellprice = ?, + discount = ?, + allocated = ?, + unit = ?, + deliverydate = ?, + project_id = ?, + serialnumber = ?, + notes = ? + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{id}, $form->{"id_$i"}, + $form->{"description_$i"}, $form->{"qty_$i"}, + $form->{"sellprice_$i"}, $fxsellprice, + $form->{"discount_$i"}, $allocated, + $form->{"unit_$i"}, $form->{"deliverydate_$i"}, + $project_id, $form->{"serialnumber_$i"}, + $form->{"notes_$i"}, $invoice_id) + || $form->dberror($query); - if ($form->{"inventory_accno_id_$i"}) { - - # add purchase to inventory - push @{ $form->{acc_trans}{lineitems} }, { - chart_id => $form->{"inventory_accno_id_$i"}, - amount => $amount, - fxgrossamount => $fxlinetotal + $form->round_amount($fxtax, 2), - grossamount => $grossamount, - project_id => $project_id, - invoice_id => $invoice_id }; + if ($form->{"inventory_accno_id_$i"}) { + + # add purchase to inventory + push @{ $form->{acc_trans}{lineitems} }, + {chart_id => + $form->{"inventory_accno_id_$i"}, + amount => $amount, + fxgrossamount => $fxlinetotal + + $form->round_amount($fxtax, 2), + grossamount => $grossamount, + project_id => $project_id, + invoice_id => $invoice_id }; - $updparts{$form->{"id_$i"}} = 1; - - # update parts table - $form->update_balance($dbh, - "parts", - "onhand", - qq|id = $form->{"id_$i"}|, - $form->{"qty_$i"}) unless $form->{shipped}; + $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 = $form->{"id_$i"} - AND (i.qty + i.allocated) > 0 - ORDER BY transdate|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $totalqty = $form->{"qty_$i"}; + # 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)) { + while (my $ref = + $sth->fetchrow_hashref(NAME_lc)) { - my $qty = $ref->{qty} + $ref->{allocated}; - - if (($qty - $totalqty) > 0) { - $qty = $totalqty; - } - - $linetotal = $form->round_amount($form->{"sellprice_$i"} * $qty, 2); - $ref->{project_id} ||= 'NULL'; - - # add entry for inventory, this one is for the sold item - if ($linetotal) { - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, project_id, invoice_id) - VALUES ($ref->{trans_id}, $ref->{inventory_accno_id}, - $linetotal, '$ref->{transdate}', $ref->{project_id}, - $invoice_id)|; - $dbh->do($query) || $form->dberror($query); - - # add expense - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, project_id, invoice_id) - VALUES ($ref->{trans_id}, $ref->{expense_accno_id}, - |. ($linetotal * -1) .qq|, '$ref->{transdate}', - $ref->{project_id}, $invoice_id)|; - $dbh->do($query) || $form->dberror($query); - } + my $qty = $ref->{qty} + + $ref->{allocated}; + + if (($qty - $totalqty) > 0) { + $qty = $totalqty; + } + + $linetotal = $form->round_amount( + $form->{"sellprice_$i"} * $qty, + 2); + + $ref->{project_id} ||= 'NULL'; + + if ($linetotal) { + $query = qq| + INSERT INTO acc_trans + (trans_id, + chart_id, + amount, + transdate, + project_id, + invoice_id) + VALUES (?, ?, ?, ?, + ?, ?)|; + + $sth = $dbh->prepare($query); + $sth->execute( + $ref->{trans_id}, + $ref->{inventory_accno_id}, + $linetotal, + $ref->{transdate}, + $ref->{project_id}, + $invoice_id + ) || $form->dberror( + $query); + + # add expense + $query = qq| + INSERT INTO acc_trans + (trans_id, + chart_id, + amount, + transdate, + project_id, + invoice_id) + VALUES (?, ?, ?, ?, + ?, ?)|; + $sth = $dbh->prepare($query) ; + $sth->execute( + $ref->{trans_id}, + $ref->{expense_accno_id}, + $linetotal * -1, + $ref->{transdate}, + $ref->{project_id}, + $invoice_id + ) || $form->dberror( + $query); + } - # update allocated for sold item - $form->update_balance($dbh, - "invoice", - "allocated", - qq|id = $ref->{id}|, - $qty * -1); + # update allocated for sold item + $form->update_balance( + $dbh, "invoice", "allocated", + qq|id = $ref->{id}|, $qty * -1); - $allocated += $qty; + $allocated += $qty; - last if (($totalqty -= $qty) <= 0); - } + last if (($totalqty -= $qty) <= 0); + } - $sth->finish; + $sth->finish; - } else { + } 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 }; + # add purchase to expense + push @{ $form->{acc_trans}{lineitems} }, { + chart_id => + $form->{"expense_accno_id_$i"}, + amount => $amount, + fxgrossamount => $fxlinetotal + + $form->round_amount( + $fxtax, 2), + grossamount => $grossamount, + project_id => $project_id, + invoice_id => $invoice_id }; - } - } - } + } + } + } - $form->{paid} = 0; - for $i (1 .. $form->{paidaccounts}) { - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{paid} += $form->{"paid_$i"}; - $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"}); - } + $form->{paid} = 0; + for $i (1 .. $form->{paidaccounts}) { + $form->{"paid_$i"} = + $form->parse_amount($myconfig, $form->{"paid_$i"}); + $form->{paid} += $form->{"paid_$i"}; + $form->{datepaid} = $form->{"datepaid_$i"} + if ($form->{"datepaid_$i"}); + } - # add lineitems + tax - $amount = 0; - $grossamount = 0; - $fxgrossamount = 0; - for (@{ $form->{acc_trans}{lineitems} }) { - $amount += $_->{amount}; - $grossamount += $_->{grossamount}; - $fxgrossamount += $_->{fxgrossamount}; - } - $invnetamount = $amount; + # 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; + $amount = 0; + for (split / /, $form->{taxaccounts}) { + $amount += $form->{acc_trans}{$form->{id}}{$_}{amount} + = $form->round_amount( + $form->{acc_trans}{$form->{id}}{$_}{amount}, 2); - $diff = 0; - if ($form->{taxincluded}) { - $diff = $form->round_amount($grossamount - $invamount, 2); - $invamount += $diff; - } - $fxdiff = $form->round_amount($fxdiff,2); - $invnetamount += $fxdiff; - $invamount += $fxdiff; + $form->{acc_trans}{$form->{id}}{$_}{amount} *= -1; + } + $invamount = $invnetamount + $amount; - if ($form->round_amount($form->{paid} - $fxgrossamount,2) == 0) { - $form->{paid} = $invamount; - } else { - $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate}, 2); - } + $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 ($form->{id}, $ref->{chart_id}, $amount * -1, - '$form->{transdate}', $ref->{project_id}, $ref->{invoice_id})|; - $dbh->do($query) || $form->dberror($query); - $diff = 0; - $fxdiff = 0; - } + 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 (?, ?, ? * -1, ?, ?, ?)|; + $sth = $dbh->do($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->{payables} = $invamount; + $form->{payables} = $invamount; - delete $form->{acc_trans}{lineitems}; + 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}); - } + # 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 ($form->{id}, - (SELECT id FROM chart - WHERE accno = '$accno'), - $form->{payables}, '$form->{transdate}')|; - $dbh->do($query) || $form->dberror($query); - } + # 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 ($trans_id, (SELECT id FROM chart - WHERE accno = '$accno'), - $amount, '$form->{transdate}')|; - $dbh->do($query) || $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; - } + # if there is no amount but a payment record payable + if ($invamount == 0) { + $form->{payables} = 1; + } - my $cleared = 0; + 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 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; + # 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 ($form->{id}, (SELECT id FROM chart - WHERE accno = '$form->{AP}'), - $amount, '$form->{"datepaid_$i"}')|; - $dbh->do($query) || $form->dberror($query); - } - - if ($keepcleared) { - $cleared = ($form->{"cleared_$i"}) ? 1 : 0; - } + if ($form->{payables}) { + $query = qq| + INSERT INTO acc_trans + (trans_id, chart_id, amount, + transdate) + VALUES (?, (SELECT id FROM chart + WHERE accno = ?), + ?, ?)|; + + $sth = $dbh->$dbh->prepare($query); + $sth->execute( + $form->{id}, $form->{AP}, $amount, + $form->{"datepaid_$i"} + ) || $form->dberror($query); + } + + if ($keepcleared) { + $cleared = ($form->{"cleared_$i"}) ? 1 : 0; + } - # record payment - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, memo, cleared) - VALUES ($form->{id}, (SELECT id FROM chart - WHERE accno = '$accno'), - $form->{"paid_$i"}, '$form->{"datepaid_$i"}', | - .$dbh->quote($form->{"source_$i"}).qq|, | - .$dbh->quote($form->{"memo_$i"}).qq|, '$cleared')|; - $dbh->do($query) || $form->dberror($query); - - # exchangerate difference - $amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate_$i"} - $form->{"paid_$i"}, 2); + # 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 ($form->{id}, (SELECT id FROM chart - WHERE accno = '$accno'), - $amount, '$form->{"datepaid_$i"}', | - .$dbh->quote($form->{"source_$i"}).qq|, '1', '$cleared')|; - $dbh->do($query) || $form->dberror($query); - } + 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); + # 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 ($form->{id}, $accno_id, - $amount, '$form->{"datepaid_$i"}', '1', '$cleared')|; - $dbh->do($query) || $form->dberror($query); - } + 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"}); - } - } - } + # update exchange rate + if (($form->{currency} ne $form->{defaultcurrency}) + && !$exchangerate) { + + $form->update_exchangerate( + $dbh, $form->{currency}, + $form->{"datepaid_$i"}, 0, + $form->{"exchangerate_$i"}); + } + } + } - # set values which could be empty - $form->{taxincluded} *= 1; - - $form->{invnumber} = $form->update_defaults($myconfig, "vinumber", $dbh) unless $form->{invnumber}; - - # save AP record - $query = qq|UPDATE ap set - invnumber = |.$dbh->quote($form->{invnumber}).qq|, - ordnumber = |.$dbh->quote($form->{ordnumber}).qq|, - quonumber = |.$dbh->quote($form->{quonumber}).qq|, - transdate = '$form->{transdate}', - vendor_id = $form->{vendor_id}, - amount = $invamount, - netamount = $invnetamount, - paid = $form->{paid}, - datepaid = |.$form->dbquote($form->{datepaid}, SQL_DATE).qq|, - duedate = |.$form->dbquote($form->{duedate}, SQL_DATE).qq|, - invoice = '1', - shippingpoint = |.$dbh->quote($form->{shippingpoint}).qq|, - shipvia = |.$dbh->quote($form->{shipvia}).qq|, - taxincluded = '$form->{taxincluded}', - notes = |.$dbh->quote($form->{notes}).qq|, - intnotes = |.$dbh->quote($form->{intnotes}).qq|, - curr = '$form->{currency}', - department_id = $form->{department_id}, - employee_id = $form->{employee_id}, - language_code = '$form->{language_code}', - ponumber = |.$dbh->quote($form->{ponumber}).qq| - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - # add shipto - $form->{name} = $form->{vendor}; - $form->{name} =~ s/--$form->{vendor_id}//; - $form->add_shipto($dbh, $form->{id}); + # set values which could be empty + $form->{taxincluded} *= 1; + + $form->{invnumber} = + $form->update_defaults($myconfig, "vinumber", $dbh) + unless $form->{invnumber}; + + # save AP record + $query = qq| + UPDATE ap + SET invnumber = ?, + ordnumber = ?, + quonumber = ?, + transdate = ?, + vendor_id = ?, + amount = ?, + netamount = ?, + paid = ?, + datepaid = ?, + duedate = ?, + invoice = '1', + shippingpoint = ?, + shipvia = ?, + taxincluded = ?, + notes = ?, + intnotes = ?, + curr = ?, + department_id = ?, + employee_id = ?, + language_code = ?, + ponumber = ? + WHERE id = ?|; + + $sth = $dbh->prepare($query); + $sth->execute( + $form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, + $form->{transdate}, $form->{vendor_id}, $invamount, + $invnetamount, $form->{paid}, $form->{datepaid}, + $form->{duedate}, $form->{shippingpoint}, $form->{shipvia}, + $form->{taxincluded}, $form->{notes}, $form->{intnotes}, + $form->{currency}, $form->{department_id}, $form->{employee_id}, + $form->{language_code}, $form->{ponumber}, $form->{id} + ) || $form->dberror($query); + # add shipto + $form->{name} = $form->{vendor}; + $form->{name} =~ s/--$form->{vendor_id}//; + $form->add_shipto($dbh, $form->{id}); - my %audittrail = ( tablename => 'ap', - reference => $form->{invnumber}, - formname => $form->{type}, - action => 'posted', - id => $form->{id} ); + my %audittrail = ( + tablename => 'ap', + reference => $form->{invnumber}, + formname => $form->{type}, + action => 'posted', + id => $form->{id} ); - $form->audittrail($dbh, "", \%audittrail); + $form->audittrail($dbh, "", \%audittrail); - my $rc = $dbh->commit; - - foreach $item (keys %updparts) { - $query = qq|UPDATE parts SET - avgcost = avgcost($item), - lastcost = lastcost($item) - WHERE id = $item|; - $dbh->do($query) || $form->dberror($query); - $dbh->commit; - } + my $rc = $dbh->commit; + + foreach $item (keys %updparts) { + $item = $dbh->quote($item); + $query = qq| + UPDATE parts + SET avgcost = avgcost(?), + lastcost = lastcost(?) + WHERE id = ?|; + $dbh->do($query) || $form->dberror($query); + $dbh->commit; + } - $dbh->disconnect; - $rc; + $rc; } sub reverse_invoice { - my ($dbh, $form) = @_; + my ($dbh, $form) = @_; - my $query = qq|SELECT id FROM ap + my $query = qq|SELECT id FROM ap WHERE id = $form->{id}|; - my ($id) = $dbh->selectrow_array($query); + my ($id) = $dbh->selectrow_array($query); - return unless $id; + 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 = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + # 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; + my $netamount = 0; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2); + 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, + 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 = $ref->{parts_id} - AND i.allocated < 0 - AND i.trans_id = a.id - ORDER BY transdate DESC|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) { - my $qty = $ref->{allocated}; + # 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 ($pthref->{trans_id}, $ref->{expense_accno_id}, - $amount, '$form->{transdate}', $ref->{project_id})|; - $dbh->do($query) || $form->dberror($query); + 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 ($pthref->{trans_id}, $ref->{inventory_accno_id}, - $amount * -1, '$form->{transdate}', $ref->{project_id})|; - $dbh->do($query) || $form->dberror($query); - - last if (($ref->{allocated} -= $qty) <= 0); + $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; - } - } - } - $sth->finish; - # delete acc_trans - $query = qq|DELETE FROM acc_trans - WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + # 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 = $form->{id}|; - $dbh->do($query) || $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 = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM shipto WHERE trans_id = ?|; + $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - $dbh->commit; + $dbh->commit; } sub delete_invoice { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + # connect to database + my $dbh = $form->{dbh}; - my %audittrail = ( tablename => 'ap', - reference => $form->{invnumber}, - formname => $form->{type}, - action => 'deleted', - id => $form->{id} ); + my %audittrail = ( + tablename => 'ap', + reference => $form->{invnumber}, + formname => $form->{type}, + action => 'deleted', + id => $form->{id} ); - $form->audittrail($dbh, "", \%audittrail); + $form->audittrail($dbh, "", \%audittrail); - my $query = qq|SELECT parts_id FROM invoice - WHERE trans_id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + 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; + my $item; + my %updparts = (); + while (($item) = $sth->fetchrow_array) { + $updparts{$item} = 1; + } + $sth->finish; - &reverse_invoice($dbh, $form); + &reverse_invoice($dbh, $form); - # delete AP record - $query = qq|DELETE FROM ap - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - # delete spool files - $query = qq|SELECT spoolfile FROM status - WHERE trans_id = $form->{id} - AND spoolfile IS NOT NULL|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + # delete AP record + $query = qq|DELETE FROM ap WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $spoolfile; - my @spoolfiles = (); + # 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); - while (($spoolfile) = $sth->fetchrow_array) { - push @spoolfiles, $spoolfile; - } - $sth->finish; + 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 = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + # delete status entries + $query = qq|DELETE FROM status WHERE trans_id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $rc = $dbh->commit; - - if ($rc) { - foreach $item (keys %updparts) { - $query = qq|UPDATE parts SET - avgcost = avgcost($item), - lastcost = lastcost($item) - WHERE id = $item|; - $dbh->do($query) || $form->dberror($query); - $dbh->commit; - } - foreach $spoolfile (@spoolfiles) { - unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; - } - } + 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; + } + } - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub retrieve_invoice { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - my $query; - - if ($form->{id}) { - # get default accounts and last invoice number - $query = qq|SELECT (SELECT c.accno FROM chart c - WHERE d.inventory_accno_id = c.id) AS inventory_accno, - (SELECT c.accno FROM chart c - WHERE d.income_accno_id = c.id) AS income_accno, - (SELECT c.accno FROM chart c - WHERE d.expense_accno_id = c.id) AS expense_accno, - (SELECT c.accno FROM chart c - WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c - WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, - d.curr AS currencies - FROM defaults d|; - } else { - $query = qq|SELECT (SELECT c.accno FROM chart c - WHERE d.inventory_accno_id = c.id) AS inventory_accno, - (SELECT c.accno FROM chart c - WHERE d.income_accno_id = c.id) AS income_accno, - (SELECT c.accno FROM chart c - WHERE d.expense_accno_id = c.id) AS expense_accno, - (SELECT c.accno FROM chart c - WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c - WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, - d.curr AS currencies, - current_date AS transdate - FROM defaults d|; - } - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query; + + if ($form->{id}) { + # get default accounts and last invoice number + $query = qq| + SELECT (SELECT c.accno FROM chart c + WHERE d.inventory_accno_id = c.id) + AS inventory_accno, + (SELECT c.accno FROM chart c + WHERE d.income_accno_id = c.id) + AS income_accno, + (SELECT c.accno FROM chart c + WHERE d.expense_accno_id = c.id) + AS expense_accno, + (SELECT c.accno FROM chart c + WHERE d.fxgain_accno_id = c.id) + AS fxgain_accno, + (SELECT c.accno FROM chart c + WHERE d.fxloss_accno_id = c.id) + AS fxloss_accno, d.curr AS currencies + FROM defaults d|; + } else { + $query = qq| + SELECT (SELECT c.accno FROM chart c + WHERE d.inventory_accno_id = c.id) + AS inventory_accno, + (SELECT c.accno FROM chart c + WHERE d.income_accno_id = c.id) + AS income_accno, + (SELECT c.accno FROM chart c + WHERE d.expense_accno_id = c.id) + AS expense_accno, + (SELECT c.accno FROM chart c + WHERE d.fxgain_accno_id = c.id) + AS fxgain_accno, + (SELECT c.accno FROM chart c + WHERE d.fxloss_accno_id = c.id) + AS fxloss_accno, d.curr AS currencies, + current_date AS transdate + FROM defaults d|; + } + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { - $form->{$_} = $ref->{$_}; - } - $sth->finish; + my $ref = $sth->fetchrow_hashref(NAME_lc); + for (keys %$ref) { + $form->{$_} = $ref->{$_}; + } + $sth->finish; - if ($form->{id}) { + if ($form->{id}) { - # retrieve invoice - $query = qq|SELECT a.invnumber, a.transdate, a.duedate, - a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, - a.intnotes, a.curr AS currency, a.vendor_id, a.language_code, - a.ponumber - FROM ap a - WHERE id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { - $form->{$_} = $ref->{$_}; - } - $sth->finish; - - # get shipto - $query = qq|SELECT * FROM shipto - WHERE trans_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { - $form->{$_} = $ref->{$_}; - } - $sth->finish; + $query = qq| + SELECT a.invnumber, a.transdate, a.duedate, + a.ordnumber, a.quonumber, a.paid, a.taxincluded, + a.notes, a.intnotes, a.curr AS currency, + a.vendor_id, a.language_code, a.ponumber + FROM ap a + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + for (keys %$ref) { + $form->{$_} = $ref->{$_}; + } + $sth->finish; + + # get shipto + $query = qq|SELECT * FROM shipto WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + for (keys %$ref) { + $form->{$_} = $ref->{$_}; + } + $sth->finish; - # retrieve individual items - $query = qq|SELECT - p.partnumber, i.description, i.qty, i.fxsellprice, i.sellprice, - i.parts_id AS id, i.unit, p.bin, i.deliverydate, - pr.projectnumber, - i.project_id, i.serialnumber, i.discount, i.notes, - pg.partsgroup, p.partsgroup_id, p.partnumber AS sku, - p.weight, p.onhand, - p.inventory_accno_id, p.income_accno_id, p.expense_accno_id, - t.description AS partsgrouptranslation - FROM invoice i - JOIN parts p ON (i.parts_id = p.id) - LEFT JOIN project pr ON (i.project_id = pr.id) - LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) - LEFT JOIN translation t ON (t.trans_id = p.partsgroup_id AND t.language_code = '$form->{language_code}') - WHERE i.trans_id = $form->{id} - ORDER BY i.id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - # exchangerate defaults - &exchangerate_defaults($dbh, $form); - - # price matrix and vendor partnumber - my $pmh = PriceMatrix::PriceMatrixQuery($dbh, $form); - - # tax rates for part - $query = qq|SELECT c.accno - FROM chart c - JOIN partstax pt ON (pt.chart_id = c.id) - WHERE pt.parts_id = ?|; - my $tth = $dbh->prepare($query); - - my $ptref; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - - my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/); - $dec = length $dec; - my $decimalplaces = ($dec > 2) ? $dec : 2; - - $tth->execute($ref->{id}); - $ref->{taxaccounts} = ""; - my $taxrate = 0; + # retrieve individual items + $query = qq| + SELECT p.partnumber, i.description, i.qty, + i.fxsellprice, i.sellprice, + i.parts_id AS id, i.unit, p.bin, + i.deliverydate, + pr.projectnumber, i.project_id, + i.serialnumber, + i.discount, i.notes, pg.partsgroup, + p.partsgroup_id, p.partnumber AS sku, + p.weight, p.onhand, p.inventory_accno_id, + p.income_accno_id, p.expense_accno_id, + t.description AS partsgrouptranslation + FROM invoice i + JOIN parts p ON (i.parts_id = p.id) + LEFT JOIN project pr ON (i.project_id = pr.id) + LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) + LEFT JOIN translation t + ON (t.trans_id = p.partsgroup_id + AND t.language_code = ?) + WHERE i.trans_id = ? + ORDER BY i.id|; + $sth = $dbh->prepare($query); + $sth->execute($form->{language_code}, $form->{id}) + || $form->dberror($query); + + # exchangerate defaults + &exchangerate_defaults($dbh, $form); + + # price matrix and vendor partnumber + my $pmh = PriceMatrix::PriceMatrixQuery($dbh, $form); + + # tax rates for part + $query = qq| + SELECT c.accno + FROM chart c + JOIN partstax pt ON (pt.chart_id = c.id) + WHERE pt.parts_id = ?|; + my $tth = $dbh->prepare($query); + + my $ptref; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + + my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/); + $dec = length $dec; + my $decimalplaces = ($dec > 2) ? $dec : 2; + + $tth->execute($ref->{id}); + $ref->{taxaccounts} = ""; + my $taxrate = 0; - while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { - $ref->{taxaccounts} .= "$ptref->{accno} "; - $taxrate += $form->{"$ptref->{accno}_rate"}; - } + while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { + $ref->{taxaccounts} .= "$ptref->{accno} "; + $taxrate += $form->{"$ptref->{accno}_rate"}; + } - $tth->finish; - chop $ref->{taxaccounts}; - - # price matrix - $ref->{sellprice} = $form->round_amount($ref->{fxsellprice} * $form->{$form->{currency}}, $decimalplaces); - PriceMatrix::price_matrix($pmh, $ref, $decimalplaces, $form, $myconfig); - - $ref->{sellprice} = $ref->{fxsellprice}; - $ref->{qty} *= -1; - - $ref->{partsgroup} = $ref->{partsgrouptranslation} if $ref->{partsgrouptranslation}; + $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; + push @{ $form->{invoice_details} }, $ref; - } + } - $sth->finish; + $sth->finish; - } + } - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } -- cgit v1.2.3