summaryrefslogtreecommitdiff
path: root/LedgerSMB/IR.pm
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB/IR.pm')
-rw-r--r--LedgerSMB/IR.pm1984
1 files changed, 973 insertions, 1011 deletions
diff --git a/LedgerSMB/IR.pm b/LedgerSMB/IR.pm
index 87719d4d..1dab91d7 100644
--- a/LedgerSMB/IR.pm
+++ b/LedgerSMB/IR.pm
@@ -1,8 +1,8 @@
#=====================================================================
-# LedgerSMB
+# 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.
@@ -37,241 +37,237 @@ use LedgerSMB::PriceMatrix;
use LedgerSMB::Sysconfig;
use Math::BigFloat;
-
sub post_invoice {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- for (1 .. $form->{rowcount}){
- unless ($form->{"deliverydate_$_"}){
- $form->{"deliverydate_$_"} = $form->{transdate};
- }
-
- }
- my $query;
- my $sth;
- my $ref;
- my $null;
- my $project_id;
- my $exchangerate = 0;
- my $allocated;
- my $taxrate;
- my $taxamount;
- my $diff = 0;
- my $item;
- my $invoice_id;
- my $keepcleared;
-
- ($null, $form->{employee_id}) = split /--/, $form->{employee};
-
- unless ($form->{employee_id}) {
- ($form->{employee}, $form->{employee_id})
- = $form->get_employee($dbh);
- }
-
- ($null, $form->{department_id}) = split(/--/, $form->{department});
- $form->{department_id} *= 1;
-
- $query = qq|
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
+
+ for ( 1 .. $form->{rowcount} ) {
+ unless ( $form->{"deliverydate_$_"} ) {
+ $form->{"deliverydate_$_"} = $form->{transdate};
+ }
+
+ }
+ my $query;
+ my $sth;
+ my $ref;
+ my $null;
+ my $project_id;
+ my $exchangerate = 0;
+ my $allocated;
+ my $taxrate;
+ my $taxamount;
+ my $diff = 0;
+ my $item;
+ my $invoice_id;
+ my $keepcleared;
+
+ ( $null, $form->{employee_id} ) = split /--/, $form->{employee};
+
+ unless ( $form->{employee_id} ) {
+ ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
+ }
+
+ ( $null, $form->{department_id} ) = split( /--/, $form->{department} );
+ $form->{department_id} *= 1;
+
+ $query = qq|
SELECT (SELECT value FROM defaults
WHERE setting_key = 'fxgain_accno_id')
AS fxgain_accno_id,
(SELECT value FROM defaults
WHERE setting_key = 'fxloss_accno_id')
AS fxloss_accno_id|;
- my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
-
- $query = qq|
+ 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 $pth = $dbh->prepare($query) || $form->dberror($query);
+
+ my %updparts = ();
+
+ if ( $form->{id} ) {
+
+ my $sth;
- my $sth;
+ $keepcleared = 1;
- $keepcleared = 1;
+ $query = qq|SELECT id FROM ap WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} );
- $query = qq|SELECT id FROM ap WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id});
-
- if ($sth->fetchrow_array) {
- $query = qq|
+ 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|
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+ while ( $ref = $sth->fetchrow_hashref ) {
+ if ( $ref->{inventory_accno_id}
+ && $ref->{income_accno_id} )
+ {
+
+ $updparts{ $ref->{id} } = 1;
+ }
+ }
+ $sth->finish;
+
+ &reverse_invoice( $dbh, $form );
+ }
+ else {
+ $query = qq|INSERT INTO ap (id) VALUES (?)|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+ }
+ }
+
+ my $uid = localtime;
+ $uid .= "$$";
+
+ if ( !$form->{id} ) {
+
+ $query = qq|
INSERT INTO ap (invnumber, employee_id)
VALUES ('$uid', (SELECT id FROM employee
- WHERE login = ?))|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{login}) || $form->dberror($query);
-
- $query = qq|SELECT id FROM ap WHERE invnumber = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
-
- }
-
- my $amount;
- my $grossamount;
- my $allocated;
- my $invamount = 0;
- my $invnetamount = 0;
-
- if ($form->{currency} eq $form->{defaultcurrency}) {
- $form->{exchangerate} = 1;
- } else {
- $exchangerate =
- $form->check_exchangerate(
- $myconfig, $form->{currency},
- $form->{transdate}, 'sell');
- }
-
- $form->{exchangerate} =
- ($exchangerate)
- ? $exchangerate
- : $form->parse_amount($myconfig, $form->{exchangerate});
-
- for my $i (1 .. $form->{rowcount}) {
- $form->{"qty_$i"} =
- $form->parse_amount($myconfig, $form->{"qty_$i"});
-
- if ($form->{"qty_$i"}) {
-
- $pth->execute($form->{"id_$i"});
- $ref = $pth->fetchrow_hashref(NAME_lc);
- for (keys %$ref) {
- $form->{"${_}_$i"} = $ref->{$_};
- }
- $pth->finish;
-
- # project
-
- if ($form->{"projectnumber_$i"} ne "") {
- ($null, $project_id) =
- split /--/, $form->{"projectnumber_$i"};
- }
-
- # undo discount formatting
- $form->{"discount_$i"} =
- $form->parse_amount(
- $myconfig,
- $form->{"discount_$i"}) / 100;
-
- # keep entered selling price
- my $fxsellprice =
- $form->parse_amount(
- $myconfig, $form->{"sellprice_$i"});
-
- my ($dec) = ($fxsellprice =~ /\.(\d+)/);
- $dec = length $dec;
- my $decimalplaces = ($dec > 2) ? $dec : 2;
-
- # deduct discount
- $form->{"sellprice_$i"} =
- $fxsellprice - $form->round_amount(
- $fxsellprice * $form->{"discount_$i"},
- $decimalplaces);
-
- # linetotal
- my $fxlinetotal = $form->round_amount(
- $form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
-
- $amount = $fxlinetotal * $form->{exchangerate};
- my $linetotal = $form->round_amount($amount, 2);
- $fxdiff += $amount - $linetotal;
-
- @taxaccounts = Tax::init_taxes(
- $form, $form->{"taxaccounts_$i"});
-
- $tax = Math::BigFloat->bzero();
- $fxtax = Math::BigFloat->bzero();
-
- if ($form->{taxincluded}) {
- $tax += $amount = Tax::calculate_taxes(
- \@taxaccounts, $form, $linetotal, 1);
-
- $form->{"sellprice_$i"}
- -= $amount / $form->{"qty_$i"};
- } else {
- $tax += $amount = Tax::calculate_taxes(
- \@taxaccounts, $form, $linetotal, 0);
-
- $fxtax += Tax::calculate_taxes(
- \@taxaccounts, $form, $fxlinetotal, 0);
- }
-
- for (@taxaccounts) {
- $form->{acc_trans}{$form->{id}}{$_->account}{amount} += $_->value;
- }
-
- $grossamount = $form->round_amount($linetotal, 2);
-
- if ($form->{taxincluded}) {
- $amount = $form->round_amount($tax, 2);
- $linetotal -= $form->round_amount(
- $tax - $diff, 2);
- $diff = ($amount - $tax);
- }
-
- $amount = $form->round_amount($linetotal, 2);
- $allocated = 0;
-
- # adjust and round sellprice
- $form->{"sellprice_$i"} = $form->round_amount(
- $form->{"sellprice_$i"} * $form->{exchangerate},
- $decimalplaces);
-
- # save detail record in invoice table
- $query = qq|
+ WHERE login = ?))|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{login} ) || $form->dberror($query);
+
+ $query = qq|SELECT id FROM ap WHERE invnumber = '$uid'|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ ( $form->{id} ) = $sth->fetchrow_array;
+ $sth->finish;
+
+ }
+
+ my $amount;
+ my $grossamount;
+ my $allocated;
+ my $invamount = 0;
+ my $invnetamount = 0;
+
+ if ( $form->{currency} eq $form->{defaultcurrency} ) {
+ $form->{exchangerate} = 1;
+ }
+ else {
+ $exchangerate =
+ $form->check_exchangerate( $myconfig, $form->{currency},
+ $form->{transdate}, 'sell' );
+ }
+
+ $form->{exchangerate} =
+ ($exchangerate)
+ ? $exchangerate
+ : $form->parse_amount( $myconfig, $form->{exchangerate} );
+
+ for my $i ( 1 .. $form->{rowcount} ) {
+ $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} );
+
+ if ( $form->{"qty_$i"} ) {
+
+ $pth->execute( $form->{"id_$i"} );
+ $ref = $pth->fetchrow_hashref(NAME_lc);
+ for ( keys %$ref ) {
+ $form->{"${_}_$i"} = $ref->{$_};
+ }
+ $pth->finish;
+
+ # project
+
+ if ( $form->{"projectnumber_$i"} ne "" ) {
+ ( $null, $project_id ) =
+ split /--/, $form->{"projectnumber_$i"};
+ }
+
+ # undo discount formatting
+ $form->{"discount_$i"} =
+ $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / 100;
+
+ # keep entered selling price
+ my $fxsellprice =
+ $form->parse_amount( $myconfig, $form->{"sellprice_$i"} );
+
+ my ($dec) = ( $fxsellprice =~ /\.(\d+)/ );
+ $dec = length $dec;
+ my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
+
+ # deduct discount
+ $form->{"sellprice_$i"} = $fxsellprice -
+ $form->round_amount( $fxsellprice * $form->{"discount_$i"},
+ $decimalplaces );
+
+ # linetotal
+ my $fxlinetotal =
+ $form->round_amount( $form->{"sellprice_$i"} * $form->{"qty_$i"},
+ 2 );
+
+ $amount = $fxlinetotal * $form->{exchangerate};
+ my $linetotal = $form->round_amount( $amount, 2 );
+ $fxdiff += $amount - $linetotal;
+
+ @taxaccounts = Tax::init_taxes( $form, $form->{"taxaccounts_$i"} );
+
+ $tax = Math::BigFloat->bzero();
+ $fxtax = Math::BigFloat->bzero();
+
+ if ( $form->{taxincluded} ) {
+ $tax += $amount =
+ Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
+
+ $form->{"sellprice_$i"} -= $amount / $form->{"qty_$i"};
+ }
+ else {
+ $tax += $amount =
+ Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 0 );
+
+ $fxtax +=
+ Tax::calculate_taxes( \@taxaccounts, $form, $fxlinetotal, 0 );
+ }
+
+ for (@taxaccounts) {
+ $form->{acc_trans}{ $form->{id} }{ $_->account }{amount} +=
+ $_->value;
+ }
+
+ $grossamount = $form->round_amount( $linetotal, 2 );
+
+ if ( $form->{taxincluded} ) {
+ $amount = $form->round_amount( $tax, 2 );
+ $linetotal -= $form->round_amount( $tax - $diff, 2 );
+ $diff = ( $amount - $tax );
+ }
+
+ $amount = $form->round_amount( $linetotal, 2 );
+ $allocated = 0;
+
+ # adjust and round sellprice
+ $form->{"sellprice_$i"} =
+ $form->round_amount(
+ $form->{"sellprice_$i"} * $form->{exchangerate},
+ $decimalplaces );
+
+ # save detail record in invoice table
+ $query = qq|
INSERT INTO invoice (description)
VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
+ $dbh->do($query) || $form->dberror($query);
- $query = qq|
+ $query = qq|
SELECT id FROM invoice
WHERE description = '$uid'|;
- ($invoice_id) = $dbh->selectrow_array($query);
+ ($invoice_id) = $dbh->selectrow_array($query);
- $form->debug;
+ $form->debug;
- $query = qq|
+ $query = qq|
UPDATE invoice
SET trans_id = ?,
parts_id = ?,
@@ -287,44 +283,41 @@ sub post_invoice {
serialnumber = ?,
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->{"notes_$i"}, $invoice_id)
- || $form->dberror($query);
-
-
- if ($form->{"inventory_accno_id_$i"}) {
-
- # add purchase to inventory
- push @{ $form->{acc_trans}{lineitems} },
- {chart_id =>
- $form->{"inventory_accno_id_$i"},
- amount => $amount,
- fxgrossamount => $fxlinetotal +
- $form->round_amount($fxtax, 2),
- grossamount => $grossamount,
- project_id => $project_id,
- invoice_id => $invoice_id };
-
-
- $updparts{$form->{"id_$i"}} = 1;
-
- # update parts table
- $form->update_balance(
- $dbh, "parts", "onhand",
- qq|id = $form->{"id_$i"}|,
- $form->{"qty_$i"})
- unless $form->{shipped};
-
-
- # check if we sold the item
- $query = qq|
+ $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->{"notes_$i"}, $invoice_id
+ ) || $form->dberror($query);
+
+ if ( $form->{"inventory_accno_id_$i"} ) {
+
+ # add purchase to inventory
+ push @{ $form->{acc_trans}{lineitems} },
+ {
+ chart_id => $form->{"inventory_accno_id_$i"},
+ amount => $amount,
+ fxgrossamount => $fxlinetotal +
+ $form->round_amount( $fxtax, 2 ),
+ grossamount => $grossamount,
+ project_id => $project_id,
+ invoice_id => $invoice_id
+ };
+
+ $updparts{ $form->{"id_$i"} } = 1;
+
+ # update parts table
+ $form->update_balance( $dbh, "parts", "onhand",
+ qq|id = $form->{"id_$i"}|,
+ $form->{"qty_$i"} )
+ unless $form->{shipped};
+
+ # check if we sold the item
+ $query = qq|
SELECT i.id, i.qty, i.allocated,
i.trans_id, i.project_id,
p.inventory_accno_id,
@@ -335,28 +328,25 @@ sub post_invoice {
WHERE i.parts_id = ?
AND (i.qty + i.allocated) > 0
ORDER BY transdate|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{"id_$i"})
- || $form->dberror($query);
-
- my $totalqty = $form->{"qty_$i"};
-
- while (my $ref =
- $sth->fetchrow_hashref(NAME_lc)) {
-
- my $qty = $ref->{qty}
- + $ref->{allocated};
-
- if (($qty - $totalqty) > 0) {
- $qty = $totalqty;
- }
-
- $linetotal = $form->round_amount(
- $form->{"sellprice_$i"} * $qty,
- 2);
-
- if ($linetotal) {
- $query = qq|
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{"id_$i"} )
+ || $form->dberror($query);
+
+ my $totalqty = $form->{"qty_$i"};
+
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+
+ my $qty = $ref->{qty} + $ref->{allocated};
+
+ if ( ( $qty - $totalqty ) > 0 ) {
+ $qty = $totalqty;
+ }
+
+ $linetotal =
+ $form->round_amount( $form->{"sellprice_$i"} * $qty, 2 );
+
+ if ($linetotal) {
+ $query = qq|
INSERT INTO acc_trans
(trans_id,
chart_id,
@@ -367,19 +357,15 @@ sub post_invoice {
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|
+ $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,
@@ -389,203 +375,199 @@ sub post_invoice {
invoice_id)
VALUES (?, ?, ?, ?,
?, ?)|;
- $sth = $dbh->prepare($query) ;
- $sth->execute(
- $ref->{trans_id},
- $ref->{expense_accno_id},
- $linetotal * -1,
- $ref->{transdate},
- $ref->{project_id},
- $invoice_id
- ) || $form->dberror(
- $query);
- }
-
- # update allocated for sold item
- $form->update_balance(
- $dbh, "invoice", "allocated",
- qq|id = $ref->{id}|, $qty * -1);
-
- $allocated += $qty;
-
- last if (($totalqty -= $qty) <= 0);
- }
-
- $sth->finish;
-
- } else {
-
- # add purchase to expense
- push @{ $form->{acc_trans}{lineitems} }, {
- chart_id =>
- $form->{"expense_accno_id_$i"},
- amount => $amount,
- fxgrossamount => $fxlinetotal
- + $form->round_amount(
- $fxtax, 2),
- grossamount => $grossamount,
- project_id => $project_id,
- invoice_id => $invoice_id };
-
- }
- }
- }
-
- $form->{paid} = 0;
- for $i (1 .. $form->{paidaccounts}) {
- $form->{"paid_$i"} =
- $form->parse_amount($myconfig, $form->{"paid_$i"});
- $form->{paid} += $form->{"paid_$i"};
- $form->{datepaid} = $form->{"datepaid_$i"}
- if ($form->{"datepaid_$i"});
- }
-
- # add lineitems + tax
- $amount = 0;
- $grossamount = 0;
- $fxgrossamount = 0;
- for (@{ $form->{acc_trans}{lineitems} }) {
- $amount += $_->{amount};
- $grossamount += $_->{grossamount};
- $fxgrossamount += $_->{fxgrossamount};
- }
- $invnetamount = $amount;
-
- $amount = 0;
- for (split / /, $form->{taxaccounts}) {
- $amount += $form->{acc_trans}{$form->{id}}{$_}{amount}
- = $form->round_amount(
- $form->{acc_trans}{$form->{id}}{$_}{amount}, 2);
-
- $form->{acc_trans}{$form->{id}}{$_}{amount} *= -1;
- }
- $invamount = $invnetamount + $amount;
-
- $diff = 0;
- if ($form->{taxincluded}) {
- $diff = $form->round_amount($grossamount - $invamount, 2);
- $invamount += $diff;
- }
- $fxdiff = $form->round_amount($fxdiff,2);
- $invnetamount += $fxdiff;
- $invamount += $fxdiff;
-
- if ($form->round_amount($form->{paid} - $fxgrossamount,2) == 0) {
- $form->{paid} = $invamount;
- } else {
- $form->{paid} = $form->round_amount(
- $form->{paid} * $form->{exchangerate}, 2);
- }
-
- foreach $ref (sort { $b->{amount} <=> $a->{amount} }
- @ { $form->{acc_trans}{lineitems} }) {
-
- $amount = $ref->{amount} + $diff + $fxdiff;
- $query = qq|
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $ref->{trans_id}, $ref->{expense_accno_id},
+ $linetotal * -1, $ref->{transdate},
+ $ref->{project_id}, $invoice_id
+ ) || $form->dberror($query);
+ }
+
+ # update allocated for sold item
+ $form->update_balance( $dbh, "invoice", "allocated",
+ qq|id = $ref->{id}|,
+ $qty * -1 );
+
+ $allocated += $qty;
+
+ last if ( ( $totalqty -= $qty ) <= 0 );
+ }
+
+ $sth->finish;
+
+ }
+ else {
+
+ # add purchase to expense
+ push @{ $form->{acc_trans}{lineitems} },
+ {
+ chart_id => $form->{"expense_accno_id_$i"},
+ amount => $amount,
+ fxgrossamount => $fxlinetotal +
+ $form->round_amount( $fxtax, 2 ),
+ grossamount => $grossamount,
+ project_id => $project_id,
+ invoice_id => $invoice_id
+ };
+
+ }
+ }
+ }
+
+ $form->{paid} = 0;
+ for $i ( 1 .. $form->{paidaccounts} ) {
+ $form->{"paid_$i"} =
+ $form->parse_amount( $myconfig, $form->{"paid_$i"} );
+ $form->{paid} += $form->{"paid_$i"};
+ $form->{datepaid} = $form->{"datepaid_$i"}
+ if ( $form->{"datepaid_$i"} );
+ }
+
+ # add lineitems + tax
+ $amount = 0;
+ $grossamount = 0;
+ $fxgrossamount = 0;
+ for ( @{ $form->{acc_trans}{lineitems} } ) {
+ $amount += $_->{amount};
+ $grossamount += $_->{grossamount};
+ $fxgrossamount += $_->{fxgrossamount};
+ }
+ $invnetamount = $amount;
+
+ $amount = 0;
+ for ( split / /, $form->{taxaccounts} ) {
+ $amount += $form->{acc_trans}{ $form->{id} }{$_}{amount} =
+ $form->round_amount( $form->{acc_trans}{ $form->{id} }{$_}{amount},
+ 2 );
+
+ $form->{acc_trans}{ $form->{id} }{$_}{amount} *= -1;
+ }
+ $invamount = $invnetamount + $amount;
+
+ $diff = 0;
+ if ( $form->{taxincluded} ) {
+ $diff = $form->round_amount( $grossamount - $invamount, 2 );
+ $invamount += $diff;
+ }
+ $fxdiff = $form->round_amount( $fxdiff, 2 );
+ $invnetamount += $fxdiff;
+ $invamount += $fxdiff;
+
+ if ( $form->round_amount( $form->{paid} - $fxgrossamount, 2 ) == 0 ) {
+ $form->{paid} = $invamount;
+ }
+ else {
+ $form->{paid} =
+ $form->round_amount( $form->{paid} * $form->{exchangerate}, 2 );
+ }
+
+ foreach $ref ( sort { $b->{amount} <=> $a->{amount} }
+ @{ $form->{acc_trans}{lineitems} } )
+ {
+
+ $amount = $ref->{amount} + $diff + $fxdiff;
+ $query = qq|
INSERT INTO acc_trans (trans_id, chart_id, amount,
transdate, project_id, invoice_id)
VALUES (?, ?, ?, ?, ?, ?)|;
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{id}, $ref->{chart_id}, $amount * -1,
- $form->{transdate}, $ref->{project_id},
- $ref->{invoice_id}) || $form->dberror($query);
- $diff = 0;
- $fxdiff = 0;
- }
-
- $form->{payables} = $invamount;
-
- delete $form->{acc_trans}{lineitems};
-
- # update exchangerate
- if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate(
- $dbh, $form->{currency}, $form->{transdate}, 0,
- $form->{exchangerate});
- }
-
- # record payable
- if ($form->{payables}) {
- ($accno) = split /--/, $form->{AP};
-
- $query = qq|
+ $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|
+ $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|
+ $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)
@@ -593,19 +575,18 @@ sub post_invoice {
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|
+ $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)
@@ -613,22 +594,21 @@ sub post_invoice {
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|
+ $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,
@@ -636,65 +616,61 @@ sub post_invoice {
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|
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id}, $accno, $amount,
+ $form->{"datepaid_$i"},
+ $form->{"source_$i"}, $cleared )
+ || $form->dberror($query);
+
+ }
+
+ # gain/loss
+ $amount = $form->round_amount(
+ $form->round_amount( $form->{"paid_$i"} * $form->{exchangerate},
+ 2 ) - $form->round_amount(
+ $form->{"paid_$i"} * $form->{"exchangerate_$i"}, 2
+ ),
+ 2
+ );
+
+ if ($amount) {
+ my $accno_id =
+ ( $amount > 0 )
+ ? $fxgain_accno_id
+ : $fxloss_accno_id;
+ $query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate, fx_transaction,
cleared)
VALUES (?, ?, ?, ?, '1', ?)|;
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{id}, $accno_id, $amount,
- $form->{"datepaid_$i"}, $cleared
- ) || $form->dberror($query);
- }
-
- # update exchange rate
- if (($form->{currency} ne $form->{defaultcurrency})
- && !$exchangerate) {
-
- $form->update_exchangerate(
- $dbh, $form->{currency},
- $form->{"datepaid_$i"}, 0,
- $form->{"exchangerate_$i"});
- }
- }
- }
-
- # set values which could be empty
- $form->{taxincluded} *= 1;
-
- $form->{invnumber} =
- $form->update_defaults($myconfig, "vinumber", $dbh)
- unless $form->{invnumber};
-
- # save AP record
- $query = qq|
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id}, $accno_id, $amount,
+ $form->{"datepaid_$i"}, $cleared )
+ || $form->dberror($query);
+ }
+
+ # update exchange rate
+ if ( ( $form->{currency} ne $form->{defaultcurrency} )
+ && !$exchangerate )
+ {
+
+ $form->update_exchangerate( $dbh, $form->{currency},
+ $form->{"datepaid_$i"},
+ 0, $form->{"exchangerate_$i"} );
+ }
+ }
+ }
+
+ # set values which could be empty
+ $form->{taxincluded} *= 1;
+
+ $form->{invnumber} = $form->update_defaults( $myconfig, "vinumber", $dbh )
+ unless $form->{invnumber};
+
+ # save AP record
+ $query = qq|
UPDATE ap
SET invnumber = ?,
ordnumber = ?,
@@ -719,86 +695,86 @@ sub post_invoice {
ponumber = ?
WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
- $form->{transdate}, $form->{vendor_id}, $invamount,
- $invnetamount, $form->{paid}, $form->{datepaid},
- $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
- $form->{taxincluded}, $form->{notes}, $form->{intnotes},
- $form->{currency}, $form->{department_id}, $form->{employee_id},
- $form->{language_code}, $form->{ponumber}, $form->{id}
- ) || $form->dberror($query);
- # add shipto
- $form->{name} = $form->{vendor};
- $form->{name} =~ s/--$form->{vendor_id}//;
- $form->add_shipto($dbh, $form->{id});
-
- my %audittrail = (
- tablename => 'ap',
- reference => $form->{invnumber},
- formname => $form->{type},
- action => 'posted',
- id => $form->{id} );
-
- $form->audittrail($dbh, "", \%audittrail);
-
- my $rc = $dbh->commit;
-
- foreach $item (keys %updparts) {
- $item = $dbh->quote($item);
- $query = qq|
+ $sth = $dbh->prepare($query);
+ $sth->execute(
+ $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
+ $form->{transdate}, $form->{vendor_id}, $invamount,
+ $invnetamount, $form->{paid}, $form->{datepaid},
+ $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
+ $form->{taxincluded}, $form->{notes}, $form->{intnotes},
+ $form->{currency}, $form->{department_id}, $form->{employee_id},
+ $form->{language_code}, $form->{ponumber}, $form->{id}
+ ) || $form->dberror($query);
+
+ # add shipto
+ $form->{name} = $form->{vendor};
+ $form->{name} =~ s/--$form->{vendor_id}//;
+ $form->add_shipto( $dbh, $form->{id} );
+
+ my %audittrail = (
+ tablename => 'ap',
+ reference => $form->{invnumber},
+ formname => $form->{type},
+ action => 'posted',
+ id => $form->{id}
+ );
+
+ $form->audittrail( $dbh, "", \%audittrail );
+
+ my $rc = $dbh->commit;
+
+ foreach $item ( keys %updparts ) {
+ $item = $dbh->quote($item);
+ $query = qq|
UPDATE parts
SET avgcost = avgcost($item),
lastcost = lastcost($item)
WHERE id = $item|;
- $dbh->prepare($query) || $form->dberror($query);
- $dbh->commit;
- }
-
- $rc;
-
-}
+ $dbh->prepare($query) || $form->dberror($query);
+ $dbh->commit;
+ }
+ $rc;
+}
sub reverse_invoice {
- my ($dbh, $form) = @_;
-
- my $query = qq|SELECT id FROM ap
+ my ( $dbh, $form ) = @_;
+
+ 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;
-
- # reverse inventory items
- $query = qq|
+ 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|
+ 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
@@ -806,181 +782,169 @@ sub reverse_invoice {
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|
+ 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|
+ $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;
-
-}
-
+ $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|
+ 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|
+ 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;
-
-}
+ $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 ( $self, $myconfig, $form ) = @_;
- my $query;
+ # connect to database
+ my $dbh = $form->{dbh};
- if ($form->{id}) {
- # get default accounts and last invoice number
- $query = qq|
+ 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 =
@@ -1012,8 +976,9 @@ sub retrieve_invoice {
AS fxloss_accno,
(SELECT value FROM defaults
WHERE setting_key = 'curr') AS currencies|;
- } else {
- $query = qq|
+ }
+ else {
+ $query = qq|
SELECT (select c.accno FROM chart c
WHERE c.id = (SELECT value FROM defaults
WHERE setting_key =
@@ -1046,48 +1011,47 @@ sub retrieve_invoice {
(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 $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}) {
-
- $query = qq|
+ $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|
+ $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,
@@ -1108,113 +1072,112 @@ sub retrieve_invoice {
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);
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{language_code}, $form->{id} )
+ || $form->dberror($query);
- # exchangerate defaults
- &exchangerate_defaults($dbh, $form);
+ # exchangerate defaults
+ &exchangerate_defaults( $dbh, $form );
- # price matrix and vendor partnumber
- my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
+ # price matrix and vendor partnumber
+ my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
- # tax rates for part
- $query = qq|
+ # tax rates for part
+ $query = qq|
SELECT c.accno
FROM chart c
JOIN partstax pt ON (pt.chart_id = c.id)
WHERE pt.parts_id = ?|;
- my $tth = $dbh->prepare($query);
-
- my $ptref;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);
- $dec = length $dec;
- my $decimalplaces = ($dec > 2) ? $dec : 2;
-
- $tth->execute($ref->{id});
- $ref->{taxaccounts} = "";
- my $taxrate = 0;
-
- while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
- $ref->{taxaccounts} .= "$ptref->{accno} ";
- $taxrate += $form->{"$ptref->{accno}_rate"};
- }
-
- $tth->finish;
- chop $ref->{taxaccounts};
-
- # price matrix
- $ref->{sellprice} = $form->round_amount(
- $ref->{fxsellprice}
- * $form->{$form->{currency}},
- $decimalplaces);
- PriceMatrix::price_matrix(
- $pmh, $ref, $decimalplaces, $form, $myconfig);
-
- $ref->{sellprice} = $ref->{fxsellprice};
- $ref->{qty} *= -1;
-
- $ref->{partsgroup} = $ref->{partsgrouptranslation}
- if $ref->{partsgrouptranslation};
-
- push @{ $form->{invoice_details} }, $ref;
-
- }
-
- $sth->finish;
-
- }
-
-
- my $rc = $dbh->commit;
-
- $rc;
-
-}
+ my $tth = $dbh->prepare($query);
+
+ my $ptref;
+
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+
+ my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ );
+ $dec = length $dec;
+ my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
+
+ $tth->execute( $ref->{id} );
+ $ref->{taxaccounts} = "";
+ my $taxrate = 0;
+
+ while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
+ $ref->{taxaccounts} .= "$ptref->{accno} ";
+ $taxrate += $form->{"$ptref->{accno}_rate"};
+ }
+
+ $tth->finish;
+ chop $ref->{taxaccounts};
+
+ # price matrix
+ $ref->{sellprice} =
+ $form->round_amount(
+ $ref->{fxsellprice} * $form->{ $form->{currency} },
+ $decimalplaces );
+ PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form,
+ $myconfig );
+
+ $ref->{sellprice} = $ref->{fxsellprice};
+ $ref->{qty} *= -1;
+
+ $ref->{partsgroup} = $ref->{partsgrouptranslation}
+ if $ref->{partsgrouptranslation};
+
+ push @{ $form->{invoice_details} }, $ref;
+
+ }
+
+ $sth->finish;
+
+ }
+
+ my $rc = $dbh->commit;
+ $rc;
+
+}
sub retrieve_item {
- my ($self, $myconfig, $form) = @_;
-
- $dbh = $form->{dbh};
- my $i = $form->{rowcount};
- my $null;
- my $var;
-
- # don't include assemblies or obsolete parts
- my $where = "WHERE p.assembly = '0' AND p.obsolete = '0'";
-
- if ($form->{"partnumber_$i"} ne "") {
- $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
- $where .= " AND lower(p.partnumber) LIKE $var";
- }
-
- if ($form->{"description_$i"} ne "") {
- $var = $dbh->quote($form->like(lc $form->{"description_$i"}));
- if ($form->{language_code} ne "") {
- $where .= " AND lower(t1.description) LIKE $var";
- } else {
- $where .= " AND lower(p.description) LIKE $var";
- }
- }
-
- if ($form->{"partsgroup_$i"} ne "") {
- ($null, $var) = split /--/, $form->{"partsgroup_$i"};
- $var = $dbh->quote($var);
- $where .= qq| AND p.partsgroup_id = $var|;
- }
-
- if ($form->{"description_$i"} ne "") {
- $where .= " ORDER BY 3";
- } else {
- $where .= " ORDER BY 2";
- }
-
-
- my $query = qq|
+ 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,
@@ -1230,120 +1193,118 @@ sub retrieve_item {
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|
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $form->{language_code}, $form->{language_code} )
+ || $form->dberror($query);
+
+ # foreign currency
+ &exchangerate_defaults( $dbh, $form );
+
+ # taxes
+ $query = qq|
SELECT c.accno
FROM chart c
JOIN partstax pt ON (pt.chart_id = c.id)
WHERE pt.parts_id = ?|;
- my $tth = $dbh->prepare($query) || $form->dberror($query);
-
- # price matrix
- my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
-
- my $ref;
- my $ptref;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- my ($dec) = ($ref->{sellprice} =~ /\.(\d+)/);
- $dec = length $dec;
- my $decimalplaces = ($dec > 2) ? $dec : 2;
-
- # get taxes for part
- $tth->execute($ref->{id});
-
- $ref->{taxaccounts} = "";
- while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
- $ref->{taxaccounts} .= "$ptref->{accno} ";
- }
- $tth->finish;
- chop $ref->{taxaccounts};
-
- # get vendor price and partnumber
- PriceMatrix::price_matrix(
- $pmh, $ref, $decimalplaces, $form, $myconfig);
-
- $ref->{description} = $ref->{translation}
- if $ref->{translation};
- $ref->{partsgroup} = $ref->{grouptranslation}
- if $ref->{grouptranslation};
-
- push @{ $form->{item_list} }, $ref;
-
- }
-
- $sth->finish;
- $dbh->commit;
-
-}
+ my $tth = $dbh->prepare($query) || $form->dberror($query);
+
+ # price matrix
+ my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
+
+ my $ref;
+ my $ptref;
+
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+
+ my ($dec) = ( $ref->{sellprice} =~ /\.(\d+)/ );
+ $dec = length $dec;
+ my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
+
+ # get taxes for part
+ $tth->execute( $ref->{id} );
+
+ $ref->{taxaccounts} = "";
+ while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
+ $ref->{taxaccounts} .= "$ptref->{accno} ";
+ }
+ $tth->finish;
+ chop $ref->{taxaccounts};
+ # get vendor price and partnumber
+ PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form,
+ $myconfig );
+
+ $ref->{description} = $ref->{translation}
+ if $ref->{translation};
+ $ref->{partsgroup} = $ref->{grouptranslation}
+ if $ref->{grouptranslation};
+
+ push @{ $form->{item_list} }, $ref;
+
+ }
+
+ $sth->finish;
+ $dbh->commit;
+
+}
sub exchangerate_defaults {
- my ($dbh, $form) = @_;
+ my ( $dbh, $form ) = @_;
- my $var;
-
- # get default currencies
- my $query = qq|
+ 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;
+ my $eth = $dbh->prepare($query) || $form->dberror($query);
+ $eth->execute;
+ ( $form->{defaultcurrency}, $form->{currencies} ) = $eth->fetchrow_array;
+ $eth->finish;
- $query = qq|
+ $query = qq|
SELECT sell
FROM exchangerate
WHERE curr = ?
AND transdate = ?|;
- my $eth1 = $dbh->prepare($query) || $form->dberror($query);
+ my $eth1 = $dbh->prepare($query) || $form->dberror($query);
- $query = qq/
+ $query = qq/
SELECT max(transdate || ' ' || sell || ' ' || curr)
FROM exchangerate
WHERE curr = ?/;
- my $eth2 = $dbh->prepare($query) || $form->dberror($query);
-
- # get exchange rates for transdate or max
- foreach $var (split /:/, substr($form->{currencies},4)) {
- $eth1->execute($var, $form->{transdate});
- ($form->{$var}) = $eth1->fetchrow_array;
- if (! $form->{$var} ) {
- $eth2->execute($var);
-
- ($form->{$var}) = $eth2->fetchrow_array;
- ($null, $form->{$var}) = split / /, $form->{$var};
- $form->{$var} = 1 unless $form->{$var};
- $eth2->finish;
- }
- $eth1->finish;
- }
-
- $form->{$form->{currency}} = $form->{exchangerate}
- if $form->{exchangerate};
- $form->{$form->{currency}} ||= 1;
- $form->{$form->{defaultcurrency}} = 1;
-
-}
+ my $eth2 = $dbh->prepare($query) || $form->dberror($query);
+
+ # get exchange rates for transdate or max
+ foreach $var ( split /:/, substr( $form->{currencies}, 4 ) ) {
+ $eth1->execute( $var, $form->{transdate} );
+ ( $form->{$var} ) = $eth1->fetchrow_array;
+ if ( !$form->{$var} ) {
+ $eth2->execute($var);
+
+ ( $form->{$var} ) = $eth2->fetchrow_array;
+ ( $null, $form->{$var} ) = split / /, $form->{$var};
+ $form->{$var} = 1 unless $form->{$var};
+ $eth2->finish;
+ }
+ $eth1->finish;
+ }
+
+ $form->{ $form->{currency} } = $form->{exchangerate}
+ if $form->{exchangerate};
+ $form->{ $form->{currency} } ||= 1;
+ $form->{ $form->{defaultcurrency} } = 1;
+}
sub vendor_details {
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
+ my ( $self, $myconfig, $form ) = @_;
- # get rest for the vendor
- my $query = qq|
+ # 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,
@@ -1351,45 +1312,46 @@ sub vendor_details {
gifi_accno AS gifi, startdate, enddate
FROM vendor
WHERE id = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{vendor_id}) || $form->dberror($query);
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $form->{vendor_id} ) || $form->dberror($query);
- $ref = $sth->fetchrow_hashref(NAME_lc);
- for (keys %$ref) {
- $form->{$_} = $ref->{$_};
- }
+ $ref = $sth->fetchrow_hashref(NAME_lc);
+ for ( keys %$ref ) {
+ $form->{$_} = $ref->{$_};
+ }
- $sth->finish;
- $dbh->commit;
+ $sth->finish;
+ $dbh->commit;
}
-
sub item_links {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- # connect to database
- my $dbh = $form->{dbh};
+ # connect to database
+ my $dbh = $form->{dbh};
- my $query = qq|
+ 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;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ foreach my $key ( split( /:/, $ref->{link} ) ) {
+ if ( $key =~ /IC/ ) {
+ push @{ $form->{IC_links}{$key} },
+ {
+ accno => $ref->{accno},
+ description => $ref->{description}
+ };
+ }
+ }
+ }
+
+ $sth->finish;
}
1;
generated by cgit v1.2.3 (git 2.46.0) at 2025-04-01 09:08:47 +0000