#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version. For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
# Author: DWS Systems Inc.
# Web: http://www.sql-ledger.org
#
# Contributors: Jim Rawlings <jim@your-dba.com>
#`
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Inventory invoicing module
#
#======================================================================
package IS;
use LedgerSMB::Tax;
use LedgerSMB::PriceMatrix;
use LedgerSMB::Sysconfig;
sub invoice_details {
use LedgerSMB::CP;
my ($self, $myconfig, $form) = @_;
$form->{duedate} = $form->{transdate} unless ($form->{duedate});
# connect to database
my $dbh = $form->{dbh};
my $query = qq|
SELECT ?::date - ?::date
AS terms, value
FROM defaults
WHERE setting_key = 'weightunit'|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{duedate}, $form->{transdate})
|| $form->dberror($query);
($form->{terms}, $form->{weightunit}) = $sth->fetchrow_array;
$sth->finish;
# this is for the template
$form->{invdate} = $form->{transdate};
my $tax = 0;
my $item;
my $i;
my @sortlist = ();
my $projectnumber;
my $projectdescription;
my $projectnumber_id;
my $translation;
my $partsgroup;
my @taxaccounts;
my %taxaccounts;
my $tax;
my $taxrate;
my $taxamount;
my %translations;
$query = qq|
SELECT p.description, t.description
FROM project p
LEFT JOIN translation t
ON (t.trans_id = p.id
AND t.language_code = ?)
WHERE id = ?|;
my $prh = $dbh->prepare($query) || $form->dberror($query);
$query = qq|
SELECT inventory_accno_id, income_accno_id,
expense_accno_id, assembly, weight FROM parts
WHERE id = ?|;
my $pth = $dbh->prepare($query) || $form->dberror($query);
my $sortby;
# sort items by project and partsgroup
for $i (1 .. $form->{rowcount} - 1) {
# account numbers
$pth->execute($form->{"id_$i"});
$ref = $pth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
$pth->finish;
$projectnumber_id = 0;
$projectnumber = "";
$form->{partsgroup} = "";
$form->{projectnumber} = "";
if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
$inventory_accno_id =
($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"})
? "1"
: "";
if ($form->{groupprojectnumber}) {
($projectnumber, $projectnumber_id) =
split /--/, $form->{"projectnumber_$i"};
}
if ($form->{grouppartsgroup}) {
($form->{partsgroup}) =
split /--/, $form->{"partsgroup_$i"};
}
if ($projectnumber_id && $form->{groupprojectnumber}) {
if ($translation{$projectnumber_id}) {
$form->{projectnumber} =
$translation{$projectnumber_id};
} else {
# get project description
$prh->execute($projectnumber_id,
$form->{language_code});
($projectdescription, $translation) =
$prh->fetchrow_array;
$prh->finish;
$form->{projectnumber} =
($translation)
? "$projectnumber, $translation"
: "$projectnumber, ".
"$projectdescription";
$translation{$projectnumber_id} =
$form->{projectnumber};
}
}
if ($form->{grouppartsgroup} && $form->{partsgroup}) {
$form->{projectnumber} .= " / "
if $projectnumber_id;
$form->{projectnumber} .= $form->{partsgroup};
}
$form->format_string(projectnumber);
}
$sortby = qq|$projectnumber$form->{partsgroup}|;
if ($form->{sortby} ne 'runningnumber') {
for (qw(partnumber description bin)) {
$sortby .= $form->{"${_}_$i"}
if $form->{sortby} eq $_;
}
}
push @sortlist, [ $i,
qq|$projectnumber$form->{partsgroup}|.
qq|$inventory_accno_id|,
$form->{projectnumber}, $projectnumber_id,
$form->{partsgroup}, $sortby ];
}
# sort the whole thing by project and group
@sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;
my $runningnumber = 1;
my $sameitem = "";
my $subtotal;
my $k = scalar @sortlist;
my $j = 0;
foreach $item (@sortlist) {
$i = $item->[0];
$j++;
# heading
if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
if ($item->[1] ne $sameitem) {
$sameitem = $item->[1];
$ok = 0;
if ($form->{groupprojectnumber}) {
$ok = $form->{"projectnumber_$i"};
}
if ($form->{grouppartsgroup}) {
$ok = $form->{"partsgroup_$i"}
unless $ok;
}
if ($ok) {
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} }, "");
push(@{ $form->{service} },
NULL);
} else {
push(@{ $form->{part} }, NULL);
push(@{ $form->{service} }, "");
}
push(@{ $form->{description} },
$item->[2]);
for (
qw(taxrates runningnumber number
sku serialnumber bin qty ship
unit deliverydate projectnumber
sellprice listprice netprice
discount discountrate linetotal
weight itemnotes)
) {
push(@{ $form->{$_} }, "");
}
push(@{ $form->{lineitems} },
{ amount => 0, tax => 0 });
}
}
}
$form->{"qty_$i"} =
$form->parse_amount($myconfig, $form->{"qty_$i"});
if ($form->{"qty_$i"}) {
$form->{totalqty} += $form->{"qty_$i"};
$form->{totalship} += $form->{"qty_$i"};
$form->{totalweight} += ($form->{"qty_$i"}
* $form->{"weight_$i"});
$form->{totalweightship} += ($form->{"qty_$i"}
* $form->{"weight_$i"});
# add number, description and qty to $form->{number}...
push(@{ $form->{runningnumber} }, $runningnumber++);
push(@{ $form->{number} }, $form->{"partnumber_$i"});
push(@{ $form->{sku} }, $form->{"sku_$i"});
push(@{ $form->{serialnumber} },
$form->{"serialnumber_$i"});
push(@{ $form->{bin} }, $form->{"bin_$i"});
push(@{ $form->{description} },
$form->{"description_$i"});
push(@{ $form->{itemnotes} }, $form->{"notes_$i"});
push(@{ $form->{qty} },
$form->format_amount(
$myconfig, $form->{"qty_$i"}));
push(@{ $form->{ship} },
$form->format_amount(
$myconfig, $form->{"qty_$i"}));
push(@{ $form->{unit} }, $form->{"unit_$i"});
push(@{ $form->{deliverydate} },
$form->{"deliverydate_$i"});
push(@{ $form->{projectnumber} },
$form->{"projectnumber_$i"});
push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
push(@{ $form->{listprice} }, $form->{"listprice_$i"});
push(@{ $form->{weight} },
$form->format_amount(
$myconfig,
$form->{"weight_$i"}
* $form->{"qty_$i"}));
my $sellprice =
$form->parse_amount(
$myconfig, $form->{"sellprice_$i"});
my ($dec) = ($sellprice =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
my $discount =
$form->round_amount(
$sellprice
* $form->parse_amount(
$myconfig,
$form->{"discount_$i"})
/100,
$decimalplaces);
# keep a netprice as well, (sellprice - discount)
$form->{"netprice_$i"} = $sellprice - $discount;
my $linetotal = $form->round_amount(
$form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
push(@{ $form->{part} }, $form->{"sku_$i"});
push(@{ $form->{service} }, NULL);
$form->{totalparts} += $linetotal;
} else {
push(@{ $form->{service} }, $form->{"sku_$i"});
push(@{ $form->{part} }, NULL);
$form->{totalservices} += $linetotal;
}
push(@{ $form->{netprice} },
($form->{"netprice_$i"})
? $form->format_amount(
$myconfig, $form->{"netprice_$i"},
$decimalplaces)
: " ");
$discount =
($discount)
? $form->format_amount(
$myconfig, $discount * -1,
$decimalplaces)
: " ";
$linetotal = ($linetotal) ? $linetotal : " ";
push(@{ $form->{discount} }, $discount);
push(@{ $form->{discountrate} },
$form->format_amount(
$myconfig, $form->{"discount_$i"}));
$form->{total} += $linetotal;
# this is for the subtotals for grouping
$subtotal += $linetotal;
$form->{"linetotal_$i"} =
$form->format_amount($myconfig, $linetotal, 2);
push(@{ $form->{linetotal} }, $form->{"linetotal_$i"});
@taxaccounts = Tax::init_taxes(
$form, $form->{"taxaccounts_$i"});
my $ml = 1;
my @taxrates = ();
$tax = 0;
if ($form->{taxincluded}) {
$taxamount = Tax::calculate_taxes(
\@taxaccounts, $form, $linetotal, 1);
$taxbase = ($linetotal - $taxamount);
$tax += Tax::extract_taxes(
\@taxaccounts, $form, $linetotal);
} else {
$taxamount = Tax::calculate_taxes(
\@taxaccounts, $form, $linetotal, 0);
$tax += Tax::apply_taxes(
\@taxaccounts, $form, $linetotal);
}
foreach $item (@taxaccounts) {
push @taxrates, 100 * $item->rate;
$taxaccounts{$item->account} += $item->value;
if ($form->{taxincluded}) {
$taxbase{$item->account} += $taxbase;
} else {
$taxbase{$item->account} += $linetotal;
}
}
push(@{ $form->{lineitems} },
{ amount => $linetotal,
tax => $form->round_amount($tax, 2) });
push(@{ $form->{taxrates} },
join' ', sort { $a <=> $b } @taxrates);
if ($form->{"assembly_$i"}) {
$form->{stagger} = -1;
&assembly_details(
$myconfig, $form, $dbh,
$form->{"id_$i"},
$oid{$myconfig->{dbdriver}},
$form->{"qty_$i"});
}
}
# add subtotal
if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
if ($subtotal) {
if ($j < $k) {
# look at next item
if ($sortlist[$j]->[1] ne $sameitem) {
if ($form->{"inventory_accno_id_$j"} || $form->{"assembly_$i"}) {
push(@{ $form->{part} },
"");
push(@{$form->{service}},
NULL);
} else {
push(@{$form->{service}},
"");
push(@{ $form->{part} },
NULL);
}
for (
qw(taxrates
runningnumber number sku
serialnumber bin qty
ship unit deliverydate
projectnumber sellprice
listprice netprice
discount discountrate
weight itemnotes)
) {
push(@{ $form->{$_} },
"")
}
push(@{ $form->{description} },
$form->{groupsubtotaldescription});
push(@{ $form->{lineitems} },
{ amount => 0,
tax => 0 });
if ($form->{groupsubtotaldescription} ne "") {
push(@{ $form->{linetotal} },
$form->format_amount(
$myconfig,
$subtotal,
2));
} else {
push(@{$form->{linetotal}},
"");
}
$subtotal = 0;
}
} else {
# got last item
if ($form->{groupsubtotaldescription}
ne "") {
if ($form->{"inventory_accno_id_$j"} || $form->{"assembly_$i"}) {
push(@{ $form->{part} },
"");
push(@{$form->{service}},
NULL);
} else {
push(@{$form->{service}},
"");
push(@{ $form->{part} },
NULL);
}
for (
qw(taxrates
runningnumber number sku
serialnumber bin qty
ship unit deliverydate
projectnumber sellprice
listprice netprice
discount discountrate
weight itemnotes)
) {
push(@{ $form->{$_} },
"");
}
push(@{ $form->{description} },
$form->{groupsubtotaldescription});
push(@{ $form->{linetotal} },
$form->format_amount(
$myconfig,
$subtotal,
2));
push(@{ $form->{lineitems} },
{ amount => 0,
tax => 0 });
}
}
}
}
}
$tax = 0;
foreach my $item (sort keys %taxaccounts) {
if ($form->round_amount($taxaccounts{$item}, 2)) {
$tax += $taxamount =
$form->round_amount($taxaccounts{$item}, 2);
push(@{ $form->{taxbaseinclusive} },
$form->{"${item}_taxbaseinclusive"}
= $form->format_amount(
$myconfig,
$taxbase{$item} + $tax, 2));
push(@{ $form->{taxbase} },
$form->{"${item}_taxbase"}
= $form->format_amount(
$myconfig, $taxbase{$item}, 2));
push(@{ $form->{tax} },
$form->{"${item}_tax"}
= $form->format_amount(
$myconfig, $taxamount, 2));
push(@{ $form->{taxdescription} },
$form->{"${item}_description"});
$form->{"${item}_taxrate"}
= $form->format_amount(
$myconfig,
$form->{"${item}_rate"} * 100);
push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"});
push(@{ $form->{taxnumber} },
$form->{"${item}_taxnumber"});
}
}
# adjust taxes for lineitems
my $total = 0;
for (@{ $form->{lineitems} }) {
$total += $_->{tax};
}
if ($form->round_amount($total,2) != $form->round_amount($tax,2)) {
# get largest amount
for (reverse sort { $a->{tax} <=> $b->{tax} }
@{ $form->{lineitems} }) {
$_->{tax} -= $total - $tax;
last;
}
}
$i = 1;
for (@{ $form->{lineitems} }) {
push(@{ $form->{linetax} },
$form->format_amount($myconfig, $_->{tax}, 2, ""));
}
for $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"}) {
push(@{ $form->{payment} }, $form->{"paid_$i"});
my ($accno, $description)
= split /--/, $form->{"AR_paid_$i"};
push(@{ $form->{paymentaccount} }, $description);
push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
push(@{ $form->{paymentsource} }, $form->{"source_$i"});
push(@{ $form->{paymentmemo} }, $form->{"memo_$i"});
$form->{paid}
+= $form->parse_amount(
$myconfig, $form->{"paid_$i"});
}
}
for (qw(totalparts totalservices)) {
$form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2);
}
for (qw(totalqty totalship totalweight)) {
$form->{$_} = $form->format_amount($myconfig, $form->{$_});
}
$form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
$form->{invtotal} =
($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
my $c;
if ($form->{language_code} ne "") {
$c = new CP $form->{language_code};
} else {
$c = new CP $myconfig->{countrycode};
}
$c->init;
my $whole;
($whole, $form->{decimal}) = split /\./, $form->{invtotal};
$form->{decimal} .= "00";
$form->{decimal} = substr($form->{decimal}, 0, 2);
$form->{text_decimal} = $c->num2text($form->{decimal} * 1);
$form->{text_amount} = $c->num2text($whole);
$form->{integer_amount} = $form->format_amount($myconfig, $whole);
$form->format_string(qw(text_amount text_decimal));
$form->{total}
= $form->format_amount(
$myconfig, $form->{invtotal} - $form->{paid}, 2);
$form->{invtotal}
= $form->format_amount($myconfig, $form->{invtotal}, 2);
$form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
$dbh->commit;
}
sub assembly_details {
my ($myconfig, $form, $dbh2, $id, $oid, $qty) = @_;
$dbh = $form->{dbh};
my $sm = "";
my $spacer;
$form->{stagger}++;
if ($form->{format} eq 'html') {
$spacer = " " x (3 * ($form->{stagger} - 1))
if $form->{stagger} > 1;
}
if ($form->{format} =~ /(postscript|pdf)/) {
if ($form->{stagger} > 1) {
$spacer = ($form->{stagger} - 1) * 3;
$spacer = '\rule{'.$spacer.'mm}{0mm}';
}
}
# get parts and push them onto the stack
my $sortorder = "";
if ($form->{grouppartsgroup}) {
$sortorder = qq|ORDER BY pg.partsgroup|;
}
my $query = qq|
SELECT p.partnumber, p.description, p.unit, a.qty,
pg.partsgroup, p.partnumber AS sku
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
WHERE a.bom = '1'
AND a.id = ?
$sortorder|;
my $sth = $dbh->prepare($query);
$sth->execute($id) || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
for (qw(partnumber description partsgroup)) {
$form->{"a_$_"} = $ref->{$_};
$form->format_string("a_$_");
}
if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) {
for (
qw(taxrates runningnumber number sku
serialnumber unit qty ship bin deliverydate
projectnumber sellprice listprice netprice
discount discountrate linetotal weight
itemnotes)
) {
push(@{ $form->{$_} }, "");
}
$sm =
($form->{"a_partsgroup"})
? $form->{"a_partsgroup"}
: "--";
push(@{ $form->{description} }, "$spacer$sm");
push(@{ $form->{lineitems} },
{ amount => 0, tax => 0 });
}
if ($form->{stagger}) {
push(@{ $form->{description} },
$form->format_amount(
$myconfig,
$ref->{qty} * $form->{"qty_$i"})
.qq| -- $form->{"a_partnumber"}|
.qq|, $form->{"a_description"}|);
for (
qw(taxrates runningnumber number sku
serialnumber unit qty ship bin deliverydate
projectnumber sellprice listprice netprice
discount discountrate linetotal weight
itemnotes)
) {
push(@{ $form->{$_} }, "");
}
} else {
push(@{ $form->{description} },
qq|$form->{"a_description"}|);
push(@{ $form->{number} }, $form->{"a_partnumber"});
push(@{ $form->{sku} }, $form->{"a_partnumber"});
for (
qw(taxrates runningnumber ship serialnumber
reqdate projectnumber sellprice listprice
netprice discount discountrate linetotal weight
itemnotes)
) {
push(@{ $form->{$_} }, "");
}
}
push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
push(@{ $form->{qty} },
$form->format_amount($myconfig, $ref->{qty} * $qty));
for (qw(unit bin)) {
$form->{"a_$_"} = $ref->{$_};
$form->format_string("a_$_");
push(@{ $form->{$_} }, $form->{"a_$_"});
}
}
$sth->finish;
$form->{stagger}--;
}
sub project_description {
my ($self, $dbh2, $id) = @_;
$dbh = $form->{dbh};
my $query = qq|
SELECT description
FROM project
WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($id);
($_) = $sth->fetchrow_array;
$_;
}
sub customer_details {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
# get rest for the customer
my $query = qq|
SELECT customernumber, name, address1, address2, city,
state, zipcode, country,
contact, phone as customerphone, fax as customerfax,
taxnumber AS customertaxnumber, sic_code AS sic, iban,
bic, startdate, enddate
FROM customer
WHERE id = ?|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{customer_id}) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
$dbh->commit;
}
sub post_invoice {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
my $query;
my $sth;
my $null;
my $project_id;
my $exchangerate = 0;
my $keepcleared = 0;
%$form->{acc_trans} = ();
($null, $form->{employee_id}) = split /--/, $form->{employee};
unless ($form->{employee_id}) {
($form->{employee}, $form->{employee_id})
= $form->get_employee($dbh);
}
($null, $form->{department_id}) = split(/--/, $form->{department});
$form->{department_id} *= 1;
$query = qq|
SELECT (SELECT value FROM defaults
WHERE setting_key = 'fxgain_accno_id')
AS fxgain_accno_id,
(SELECT value FROM defaults
WHERE setting_key = 'fxloss_accno_id')
AS fxloss_accno_id|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
$query = qq|
SELECT p.assembly, p.inventory_accno_id,
p.income_accno_id, p.expense_accno_id, p.project_id
FROM parts p
WHERE p.id = ?|;
my $pth = $dbh->prepare($query) || $form->dberror($query);
if ($form->{id}) {
$keepcleared = 1;
$query = qq|SELECT id FROM ar WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id});
if ($sth->fetchrow_array) {
&reverse_invoice($dbh, $form);
} else {
$query = qq|INSERT INTO ar (id) VALUES (?)|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
}
}
my $uid = localtime;
$uid .= "$$";
if (! $form->{id}) {
$query = qq|
INSERT INTO ar (invnumber, employee_id)
VALUES ('$uid', ?)|;
$sth = $dbh->prepare($query);
$sth->execute($form->{employee_id}) || $form->dberror($query);
$query = qq|SELECT id FROM ar WHERE invnumber = '$uid'|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
($form->{id}) = $sth->fetchrow_array;
$sth->finish;
@queries = $form->run_custom_queries('ar', 'INSERT');
}
if ($form->{currency} eq $form->{defaultcurrency}) {
$form->{exchangerate} = 1;
} else {
$exchangerate =
$form->check_exchangerate(
$myconfig, $form->{currency},
$form->{transdate}, 'buy');
}
$form->{exchangerate} =
($exchangerate)
? $exchangerate
: $form->parse_amount($myconfig, $form->{exchangerate});
my $i;
my $item;
my $allocated = 0;
my $taxrate;
my $tax;
my $fxtax;
my @taxaccounts;
my $amount;
my $grossamount;
my $invamount = 0;
my $invnetamount = 0;
my $diff = 0;
my $ml;
my $invoice_id;
my $ndx;
foreach $i (1 .. $form->{rowcount}) {
$form->{"qty_$i"} =
$form->parse_amount($myconfig, $form->{"qty_$i"});
if ($form->{"qty_$i"}) {
$pth->execute($form->{"id_$i"});
$ref = $pth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
$pth->finish;
# project
if ($form->{"projectnumber_$i"}) {
($null, $project_id)
= split /--/,
$form->{"projectnumber_$i"};
}
$project_id = $form->{"project_id_$i"}
if $form->{"project_id_$i"};
# keep entered selling price
my $fxsellprice =
$form->parse_amount(
$myconfig, $form->{"sellprice_$i"});
my ($dec) = ($fxsellprice =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
# undo discount formatting
$form->{"discount_$i"} =
$form->parse_amount(
$myconfig, $form->{"discount_$i"})/100;
# deduct discount
$form->{"sellprice_$i"} = $fxsellprice
- $form->round_amount(
$fxsellprice * $form->{"discount_$i"},
$decimalplaces);
# linetotal
my $fxlinetotal = $form->round_amount(
$form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
$amount = $fxlinetotal * $form->{exchangerate};
my $linetotal = $form->round_amount($amount, 2);
$fxdiff += $amount - $linetotal;
@taxaccounts = Tax::init_taxes(
$form, $form->{"taxaccounts_$i"});
$ml = 1;
$tax = 0;
$fxtax = 0;
if ($form->{taxincluded}) {
$tax += $amount =
Tax::calculate_taxes(
\@taxaccounts, $form,
$linetotal, 1);
$form->{"sellprice_$i"} -= $amount
/ $form->{"qty_$i"};
$fxtax += Tax::calculate_taxes(
\@taxaccounts, $form, $linetotal, 1);
} else {
$tax += $amount = Tax::calculate_taxes(
\@taxaccounts, $form, $linetotal, 0);
$fxtax += Tax::calculate_taxes(
\@taxaccounts, $form, $linetotal, 0);
}
$grossamount = $form->round_amount($linetotal, 2);
if ($form->{taxincluded}) {
$amount = $form->round_amount($tax, 2);
$linetotal -= $form->round_amount(
$tax - $diff, 2);
$diff = ($amount - $tax);
}
# add linetotal to income
$amount = $form->round_amount($linetotal, 2);
push @{ $form->{acc_trans}{lineitems} }, {
chart_id => $form->{"income_accno_id_$i"},
amount => $amount,
fxgrossamount => $fxlinetotal + $fxtax,
grossamount => $grossamount,
project_id => $project_id };
$ndx = $#{@{$form->{acc_trans}{lineitems}}};
$form->{"sellprice_$i"} =
$form->round_amount(
$form->{"sellprice_$i"}
* $form->{exchangerate},
$decimalplaces);
if ($form->{"inventory_accno_id_$i"}
|| $form->{"assembly_$i"}) {
if ($form->{"assembly_$i"}) {
# If the assembly consists of all
# services, we don't keep inventory,
# so we should not update it
$query = qq|
SELECT sum(
p.inventory_accno_id),
p.assembly
FROM parts p
JOIN assembly a
ON (a.parts_id = p.id)
WHERE a.id = $form->{"id_$i"}
GROUP BY p.assembly|;
$sth = $dbh->prepare($query);
$sth->execute($form->{"id_$i"})
|| $form->dberror($query);
my ($inv, $assembly)
= $sth->fetchrow_array;
$sth->finish;
if ($inv || $assembly) {
$form->update_balance($dbh,
"parts",
"onhand",
qq|id = |.
qq|$form->{"id_$i"}|,
$form->{"qty_$i"} * -1)
unless $form->{shipped};
}
&process_assembly(
$dbh, $form, $form->{"id_$i"},
$form->{"qty_$i"}, $project_id);
} else {
$form->update_balance(
$dbh, "parts", "onhand",
qq|id = $form->{"id_$i"}|,
$form->{"qty_$i"} * -1)
unless $form->{shipped};
$allocated = &cogs(
$dbh, $form, $form->{"id_$i"},
$form->{"qty_$i"}, $project_id);
}
}
# save detail record in invoice table
$query = qq|
INSERT INTO invoice (description)
VALUES ('$uid')|;
$dbh->do($query) || $form->dberror($query);
$query = qq|
SELECT id FROM invoice
WHERE description = '$uid'|;
($invoice_id) = $dbh->selectrow_array($query);
unless ($form->{"deliverydate_$i"}){
undef $form->{"deliverydate_$i"};
}
$query = qq|
UPDATE invoice
SET trans_id = ?,
parts_id = ?,
description = ?,
qty = ?,
sellprice = ?,
fxsellprice = ?,
discount = ?,
allocated = ?,
unit = ?,
deliverydate = ?,
project_id = ?,
serialnumber = ?,
notes = ?
WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $form->{"id_$i"},
$form->{"description_$i"}, $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated,
$form->{"unit_$i"}, $form->{"deliverydate_$i"},
$project_id, $form->{"serialnumber_$i"},
$form->{"notes_$i"}, $invoice_id)
|| $form->dberror($query);
# add invoice_id
$form->{acc_trans}{lineitems}[$ndx]->{invoice_id}
= $invoice_id;
}
}
$form->{paid} = 0;
for $i (1 .. $form->{paidaccounts}) {
$form->{"paid_$i"} =
$form->parse_amount($myconfig, $form->{"paid_$i"});
$form->{paid} += $form->{"paid_$i"};
$form->{datepaid} = $form->{"datepaid_$i"}
if ($form->{"paid_$i"});
}
# add lineitems + tax
$amount = 0;
$grossamount = 0;
$fxgrossamount = 0;
for (@{ $form->{acc_trans}{lineitems} }) {
$amount += $_->{amount};
$grossamount += $_->{grossamount};
$fxgrossamount += $_->{fxgrossamount};
}
$invnetamount = $amount;
$amount = 0;
for (split / /, $form->{taxaccounts}) {
$amount +=
$form->{acc_trans}{$form->{id}}{$_}{amount} =
$form->round_amount(
$form->{acc_trans}{$form->{id}}{$_}{amount},
2);
}
$invamount = $invnetamount + $amount;
$diff = 0;
if ($form->{taxincluded}) {
$diff = $form->round_amount($grossamount - $invamount, 2);
$invamount += $diff;
}
$fxdiff = $form->round_amount($fxdiff,2);
$invnetamount += $fxdiff;
$invamount += $fxdiff;
if ($form->round_amount($form->{paid} - $fxgrossamount,2) == 0) {
$form->{paid} = $invamount;
} else {
$form->{paid} = $form->round_amount(
$form->{paid} * $form->{exchangerate}, 2);
}
foreach $ref (sort { $b->{amount} <=> $a->{amount} }
@ { $form->{acc_trans}{lineitems} }) {
$amount = $ref->{amount} + $diff + $fxdiff;
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate, project_id, invoice_id)
VALUES (?, ?, ?, ?, ?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $ref->{chart_id}, $amount,
$form->{transdate}, $ref->{project_id},
$ref->{invoice_id})
|| $form->dberror($query);
$diff = 0;
$fxdiff = 0;
}
$form->{receivables} = $invamount * -1;
delete $form->{acc_trans}{lineitems};
# update exchangerate
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
$form->update_exchangerate(
$dbh, $form->{currency}, $form->{transdate},
$form->{exchangerate}, 0);
}
# record receivable
if ($form->{receivables}) {
($accno) = split /--/, $form->{AR};
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount, transdate)
VALUES (?, (SELECT id FROM chart WHERE accno = ?),
?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $accno, $form->{receivables},
$form->{transdate})
|| $form->dberror($query);
}
foreach my $trans_id (keys %{$form->{acc_trans}}) {
foreach my $accno (keys %{$form->{acc_trans}{$trans_id}}) {
$amount = $form->round_amount(
$form->{acc_trans}{$trans_id}{$accno}{amount},
2);
if ($amount) {
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate)
VALUES (?, (SELECT id FROM chart
WHERE accno = ?),
?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$trans_id, $accno, $amount,
$form->{transdate})
|| $form->dberror($query);
}
}
}
# if there is no amount but a payment record receivable
if ($invamount == 0) {
$form->{receivables} = 1;
}
my $cleared = 0;
# record payments and offsetting AR
for $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"}) {
my ($accno) = split /--/, $form->{"AR_paid_$i"};
$form->{"datepaid_$i"} = $form->{transdate}
unless ($form->{"datepaid_$i"});
$form->{datepaid} = $form->{"datepaid_$i"};
$exchangerate = 0;
if ($form->{currency} eq $form->{defaultcurrency}) {
$form->{"exchangerate_$i"} = 1;
} else {
$exchangerate =
$form->check_exchangerate(
$myconfig, $form->{currency},
$form->{"datepaid_$i"}, 'buy');
$form->{"exchangerate_$i"} =
($exchangerate)
? $exchangerate
: $form->parse_amount(
$myconfig,
$form->{"exchangerate_$i"});
}
# record AR
$amount = $form->round_amount(
$form->{"paid_$i"} * $form->{exchangerate}, 2);
if ($form->{receivables}) {
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate)
VALUES (?, (SELECT id FROM chart
WHERE accno = ?),
?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $form->{AR}, $amount,
$form->{"datepaid_$i"})
|| $form->dberror($query);
}
# record payment
$amount = $form->{"paid_$i"} * -1;
if ($keepcleared) {
$cleared = ($form->{"cleared_$i"}) ? 1 : 0;
}
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate, source, memo, cleared)
VALUES (?, (SELECT id FROM chart
WHERE accno = ?),
?, ?, ?, ?, ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $accno, $amount,
$form->{"datepaid_$i"}, $form->{"source_$i"},
$form->{"memo_$i"}, $cleared)
|| $form->dberror($query);
# exchangerate difference
$amount = $form->round_amount(
($form->round_amount(
$form->{"paid_$i"}
* $form->{"exchangerate_$i"}
- $form->{"paid_$i"}, 2)) * -1,
2);
if ($amount) {
$query = qq|
INSERT INTO acc_trans
(trans_id, chart_id, amount,
transdate, source,
fx_transaction, cleared)
VALUES (?, (SELECT id FROM chart
WHERE accno = >),
?, ?, ?, '1', ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $accno, $amount,
$form->{"datepaid_$i"},
$form->{"source_$i"}, $cleared )
|| $form->dberror($query);
}
# gain/loss
$amount = $form->round_amount(
($form->round_amount(
$form->{"paid_$i"}
* $form->{exchangerate},2)
- $form->round_amount(
$form->{"paid_$i"}
* $form->{"exchangerate_$i"},2)
) * -1,
2);
if ($amount) {
my $accno_id =
($amount > 0)
? $fxgain_accno_id
: $fxloss_accno_id;
$query = qq|
INSERT INTO acc_trans (
trans_id, chart_id, amount,
transdate, fx_transaction,
cleared)
VALUES (?, ?, ?, ?, '1', ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $accno_id, $amount,
$form->{"datepaid_$i"}, $cleared)
|| $form->dberror($query);
}
# update exchange rate
if (($form->{currency} ne $form->{defaultcurrency})
&& !$exchangerate) {
$form->update_exchangerate(
$dbh, $form->{currency},
$form->{"datepaid_$i"},
$form->{"exchangerate_$i"}, 0);
}
}
}
# set values which could be empty to 0
$form->{terms} *= 1;
$form->{taxincluded} *= 1;
$form->{invnumber} =
$form->update_defaults($myconfig, "sinumber", $dbh)
unless $form->{invnumber};
# save AR record
$query = qq|
UPDATE ar set
invnumber = ?,
ordnumber = ?,
quonumber = ?,
transdate = ?,
customer_id = ?,
amount = ?,
netamount = ?,
paid = ?,
datepaid = ?,
duedate = ?,
invoice = '1',
shippingpoint = ?,
shipvia = ?,
terms = ?,
notes = ?,
intnotes = ?,
taxincluded = ?,
curr = ?,
department_id = ?,
employee_id = ?,
till = ?,
language_code = ?,
ponumber = ?
WHERE id = ?
|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
$form->{transdate}, $form->{customer_id}, $invamount,
$invnetamount, $form->{paid}, $form->{datepaid},
$form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
$form->{terms}, $form->{notes}, $form->{intnotes},
$form->{taxincluded}, $form->{currency}, $form->{department_id},
$form->{employee_id}, $till, $form->{language_code},
$form->{ponumber}, $form->{id})
|| $form->dberror($query);
# add shipto
$form->{name} = $form->{customer};
$form->{name} =~ s/--$form->{customer_id}//;
$form->add_shipto($dbh, $form->{id});
# save printed, emailed and queued
$form->save_status($dbh);
my %audittrail = (
tablename => 'ar',
reference => $form->{invnumber},
formname => $form->{type},
action => 'posted',
id => $form->{id} );
$form->audittrail($dbh, "", \%audittrail);
$form->save_recurring($dbh, $myconfig);
my $rc = $dbh->commit;
$rc;
}
sub process_assembly {
my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
my $dbh = $form->{dbh};
my $query = qq|
SELECT a.parts_id, a.qty, p.assembly,
p.partnumber, p.description, p.unit,
p.inventory_accno_id, p.income_accno_id,
p.expense_accno_id
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
WHERE a.id = ?|;
my $sth = $dbh->prepare($query);
$sth->execute($id) || $form->dberror($query);
my $allocated;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
$allocated = 0;
$ref->{inventory_accno_id} *= 1;
$ref->{expense_accno_id} *= 1;
# multiply by number of assemblies
$ref->{qty} *= $totalqty;
if ($ref->{assembly}) {
&process_assembly(
$dbh, $form, $ref->{parts_id}, $ref->{qty},
$project_id);
next;
} else {
if ($ref->{inventory_accno_id}) {
$allocated = &cogs(
$dbh, $form, $ref->{parts_id},
$ref->{qty}, $project_id);
}
}
$query = qq|
INSERT INTO invoice
(trans_id, description, parts_id, qty,
sellprice, fxsellprice, allocated,
assemblyitem, unit)
VALUES (?, ?, ?, ?, 0, 0, ?, 't', ?)|;
$sth = $dbh->prepare($query);
$sth->execute(
$form->{id}, $ref->{description}, $ref->{parts_id},
$ref->{qty}, $allocated, $ref->{unit})
|| $form->dberror($query);
}
$sth->finish;
}
sub cogs {
my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
my $dbh = $form->{dbh};
my $query = qq|
SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice,
i.fxsellprice, p.inventory_accno_id,
p.expense_accno_id
FROM invoice i, parts p
WHERE i.parts_id = p.id
AND i.parts_id = ?
AND (i.qty + i.allocated) < 0
ORDER BY trans_id|;
my $sth = $dbh->prepare($query);
$sth->execute($id) || $form->dberror($query);
my $allocated = 0;
my $qty;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
if (($qty = (($ref->{qty} * -1) - $ref->{allocated}))
> $totalqty) {
$qty = $totalqty;
}
$form->update_balance(
$dbh, "invoice", "allocated", qq|id = $ref->{id}|,
$qty);
# total expenses and inventory
# sellprice is the cost of the item
my $linetotal = $form->round_amount(
$ref->{sellprice} * $qty, 2);
# add expense
push @{ $form->{acc_trans}{lineitems} }, {
chart_id => $ref->{expense_accno_id},
amount => $linetotal * -1,
project_id => $project_id,
invoice_id => $ref->{id} };
# deduct inventory
push @{ $form->{acc_trans}{lineitems} }, {
chart_id => $ref->{inventory_accno_id},
amount => $linetotal,
project_id => $project_id,
invoice_id => $ref->{id} };
# add allocated
$allocated += -$qty;
last if (($totalqty -= $qty) <= 0);
}
$sth->finish;
$allocated;
$dbh->commit;
}
sub reverse_invoice {
my ($dbh2, $form) = @_;
my $dbh = $form->{dbh};
my $query = qq|
SELECT id FROM ar
WHERE id = ?|;
my $sth;
$sth = $dbh->prepare($query);
$sth->execute($form->{id});
my ($id) = $sth->fetchrow_array;
return unless $id;
# reverse inventory items
my $query = qq|
SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly,
p.inventory_accno_id
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
WHERE i.trans_id = ?|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
if ($ref->{inventory_accno_id} || $ref->{assembly}) {
# if the invoice item is not an assemblyitem
# adjust parts onhand
if (!$ref->{assemblyitem}) {
# adjust onhand in parts table
$form->update_balance(
$dbh, "parts", "onhand",
qq|id = $ref->{parts_id}|, $ref->{qty});
}
# loop if it is an assembly
next if ($ref->{assembly});
# de-allocated purchases
$query = qq|
SELECT id, trans_id, allocated
FROM invoice
WHERE parts_id = ?
AND allocated > 0
ORDER BY trans_id DESC|;
my $sth = $dbh->prepare($query);
$sth->execute($ref->{parts_id})
|| $form->dberror($query);
while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
$qty = $ref->{qty};
if (($ref->{qty} - $inhref->{allocated}) > 0) {
$qty = $inhref->{allocated};
}
# update invoice
$form->update_balance(
$dbh, "invoice", "allocated",
qq|id = $inhref->{id}|, $qty * -1);
last if (($ref->{qty} -= $qty) <= 0);
}
$sth->finish;
}
}
$sth->finish;
# delete acc_trans
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
# delete invoice entries
$query = qq|DELETE FROM invoice WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$dbh->commit;
}
sub delete_invoice {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
my $sth;
&reverse_invoice($dbh, $form);
my %audittrail = (
tablename => 'ar',
reference => $form->{invnumber},
formname => $form->{type},
action => 'deleted',
id => $form->{id} );
$form->audittrail($dbh, "", \%audittrail);
# delete AR record
my $query = qq|DELETE FROM ar WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
# delete spool files
$query = qq|
SELECT spoolfile FROM status
WHERE trans_id = $form->{id} AND spoolfile IS NOT NULL|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
my $spoolfile;
my @spoolfiles = ();
while (($spoolfile) = $sth->fetchrow_array) {
push @spoolfiles, $spoolfile;
}
$sth->finish;
# delete status entries
$query = qq|DELETE FROM status WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
my $rc = $dbh->commit;
if ($rc) {
foreach $spoolfile (@spoolfiles) {
unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
if $spoolfile;
}
}
$dbh->commit;
$rc;
}
sub retrieve_invoice {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
my $query;
if ($form->{id}) {
# get default accounts and last invoice number
$query = qq|
SELECT value AS currencies FROM defaults
WHERE setting_key = 'curr'|;
} else {
$query = qq|
SELECT value AS currencies, current_date AS transdate
FROM defaults
WHERE setting_key = 'curr'|;
}
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
my $ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
if ($form->{id}) {
# retrieve invoice
$query = qq|
SELECT a.invnumber, a.ordnumber, a.quonumber,
a.transdate, a.paid,
a.shippingpoint, a.shipvia, a.terms, a.notes,
a.intnotes,
a.duedate, a.taxincluded, a.curr AS currency,
a.employee_id, e.name AS employee, a.till,
a.customer_id,
a.language_code, a.ponumber
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
# get shipto
$query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute($form->{id}) || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
$sth->finish;
# retrieve individual items
$query = qq|
SELECT i.description, i.qty, i.fxsellprice,
i.sellprice, i.discount, i.parts_id AS id,
i.unit, i.deliverydate, i.project_id,
pr.projectnumber, i.serialnumber, i.notes,
p.partnumber, p.assembly, p.bin,
pg.partsgroup, p.partsgroup_id,
p.partnumber AS sku, p.listprice, p.lastcost,
p.weight, p.onhand, p.inventory_accno_id,
p.income_accno_id, p.expense_accno_id,
t.description AS partsgrouptranslation
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
LEFT JOIN project pr ON (i.project_id = pr.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
LEFT JOIN translation t
ON (t.trans_id = p.partsgroup_id
AND t.language_code
= ?)
WHERE i.trans_id = ?
AND NOT i.assemblyitem = '1'
ORDER BY i.id|;
$sth = $dbh->prepare($query);
$sth->execute($form->{language_code}, $form->{id})
|| $form->dberror($query);
# foreign currency
&exchangerate_defaults($dbh, $form);
# query for price matrix
my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
# taxes
$query = qq|
SELECT c.accno
FROM chart c
JOIN partstax pt ON (pt.chart_id = c.id)
WHERE pt.parts_id = ?|;
my $tth = $dbh->prepare($query) || $form->dberror($query);
my $taxrate;
my $ptref;
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
$tth->execute($ref->{id});
$ref->{taxaccounts} = "";
$taxrate = 0;
while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
$ref->{taxaccounts} .= "$ptref->{accno} ";
$taxrate += $form->{"$ptref->{accno}_rate"};
}
$tth->finish;
chop $ref->{taxaccounts};
# price matrix
$ref->{sellprice} =
($ref->{fxsellprice}
* $form->{$form->{currency}});
PriceMatrix::price_matrix(
$pmh, $ref, $form->{transdate}, $decimalplaces,
$form, $myconfig);
$ref->{sellprice} = $ref->{fxsellprice};
$ref->{partsgroup} = $ref->{partsgrouptranslation}
if $ref->{partsgrouptranslation};
push @{ $form->{invoice_details} }, $ref;
}
$sth->finish;
}
@queries = $form->run_custom_queries('ar', 'SELECT');
my $rc = $dbh->commit;
$rc;
}
sub retrieve_item {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
my $i = $form->{rowcount};
my $null;
my $var;
my $where = "WHERE p.obsolete = '0' AND NOT p.income_accno_id IS NULL";
if ($form->{"partnumber_$i"} ne "") {
$var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
$where .= " AND lower(p.partnumber) LIKE $var";
}
if ($form->{"description_$i"} ne "") {
$var = $dbh->quote($form->like(lc $form->{"description_$i"}));
if ($form->{language_code} ne "") {
$where .= " AND lower(t1.description) LIKE $var";
} else {
$where .= " AND lower(p.description) LIKE $var";
}
}
if ($form->{"partsgroup_$i"} ne "") {
($null, $var) = split /--/, $form->{"partsgroup_$i"};
$var = $dbh->quote($var);
if ($var == 0) {
# search by partsgroup, this is for the POS
$where .= qq| AND pg.partsgroup = |.
$dbh->quote($form->{"partsgroup_$i"});
} else {
$where .= qq| AND p.partsgroup_id = $var|;
}
}
if ($form->{"description_$i"} ne "") {
$where .= " ORDER BY 3";
} else {
$where .= " ORDER BY 2";
}
my $query = qq|
SELECT p.id, p.partnumber, p.description, p.sellprice,
p.listprice, p.lastcost, p.unit, p.assembly, p.bin,
p.onhand, p.notes, p.inventory_accno_id,
p.income_accno_id, p.expense_accno_id, pg.partsgroup,
p.partsgroup_id, p.partnumber AS sku, p.weight,
t1.description AS translation,
t2.description AS grouptranslation
FROM parts p
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
LEFT JOIN translation t1
ON (t1.trans_id = p.id AND t1.language_code = ?)
LEFT JOIN translation t2
ON (t2.trans_id = p.partsgroup_id
AND t2.language_code = ?)
$where|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{language_code}, $form->{language_code})
|| $form->dberror($query);
my $ref;
my $ptref;
# setup exchange rates
&exchangerate_defaults($dbh, $form);
# taxes
$query = qq|
SELECT c.accno
FROM chart c
JOIN partstax pt ON (c.id = pt.chart_id)
WHERE pt.parts_id = ?|;
my $tth = $dbh->prepare($query) || $form->dberror($query);
# price matrix
my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
my $transdate = $form->datetonum($myconfig, $form->{transdate});
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
my ($dec) = ($ref->{sellprice} =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;
# get taxes for part
$tth->execute($ref->{id});
$ref->{taxaccounts} = "";
while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
$ref->{taxaccounts} .= "$ptref->{accno} ";
}
$tth->finish;
chop $ref->{taxaccounts};
# get matrix
PriceMatrix::price_matrix(
$pmh, $ref, $transdate, $decimalplaces, $form,
$myconfig);
$ref->{description} = $ref->{translation}
if $ref->{translation};
$ref->{partsgroup} = $ref->{grouptranslation}
if $ref->{grouptranslation};
push @{ $form->{item_list} }, $ref;
}
$sth->finish;
$dbh->commit;
}
sub exchangerate_defaults {
my ($dbh2, $form) = @_;
$dbh = $form->{dbh};
my $var;
# get default currencies
my $query = qq|
SELECT substr(value,1,3), value FROM defaults
WHERE setting_key = 'curr'|;
my $eth = $dbh->prepare($query) || $form->dberror($query);
$eth->execute;
($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
$eth->finish;
$query = qq|
SELECT buy
FROM exchangerate
WHERE curr = ?
AND transdate = ?|;
my $eth1 = $dbh->prepare($query) || $form->dberror($query);
$query = qq/
SELECT max(transdate || ' ' || buy || ' ' || curr)
FROM exchangerate
WHERE curr = ?/;
my $eth2 = $dbh->prepare($query) || $form->dberror($query);
# get exchange rates for transdate or max
foreach $var (split /:/, substr($form->{currencies},4)) {
$eth1->execute($var, $form->{transdate});
($form->{$var}) = $eth1->fetchrow_array;
if (! $form->{$var} ) {
$eth2->execute($var);
($form->{$var}) = $eth2->fetchrow_array;
($null, $form->{$var}) = split / /, $form->{$var};
$form->{$var} = 1 unless $form->{$var};
$eth2->finish;
}
$eth1->finish;
}
$form->{$form->{currency}} = $form->{exchangerate}
if $form->{exchangerate};
$form->{$form->{currency}} ||= 1;
$form->{$form->{defaultcurrency}} = 1;
}
1;