#=====================================================================
# SQL-Ledger Accounting
-# Copyright (C) 1998-2002
+# Copyright (C) 2000
#
# Author: Dieter Simader
# Email: dsimader@sql-ledger.org
# Web: http://www.sql-ledger.org
#
-# Contributors:
+# Contributors: Jim Rawlings <jim@your-dba.com>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
sub invoice_details {
my ($self, $myconfig, $form) = @_;
- $form->{duedate} = $form->{invdate} unless ($form->{duedate});
+ $form->{duedate} = $form->{transdate} unless ($form->{duedate});
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my $query = qq|SELECT date '$form->{duedate}' - date '$form->{invdate}'
+ my $query = qq|SELECT date '$form->{duedate}' - date '$form->{transdate}'
AS terms
FROM defaults|;
my $sth = $dbh->prepare($query);
($form->{terms}) = $sth->fetchrow_array;
$sth->finish;
+ # this is for the template
+ $form->{invdate} = $form->{transdate};
+
my $tax = 0;
my $item;
my $i;
- my @partsgroup = ();
+ my @sortlist = ();
+ my $projectnumber;
+ my $projectnumber_id;
+ my $translation;
my $partsgroup;
- my %oid = ( 'Pg' => 'oid',
- 'Oracle' => 'rowid' );
+
+ my %oid = ( 'Pg' => 'oid',
+ 'PgPP' => 'oid',
+ 'Oracle' => 'rowid',
+ 'DB2' => '1=1'
+ );
# sort items by partsgroup
for $i (1 .. $form->{rowcount}) {
+ $projectnumber = "";
$partsgroup = "";
- if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
- $form->format_string("partsgroup_$i");
- $partsgroup = $form->{"partsgroup_$i"};
+ $projectnumber_id = 0;
+ if ($form->{"projectnumber_$i"} && $form->{groupprojectnumber}) {
+ ($projectnumber, $projectnumber_id) = split /--/, $form->{"projectnumber_$i"};
}
- push @partsgroup, [ $i, $partsgroup ];
+ if ($form->{"partsgroup_$i"} && $form->{grouppartsgroup}) {
+ ($partsgroup) = split /--/, $form->{"partsgroup_$i"};
+ }
+ push @sortlist, [ $i, "$projectnumber$partsgroup", $projectnumber, $projectnumber_id, $partsgroup ];
+
+
+ # sort the whole thing by project and group
+ @sortlist = sort { $a->[1] cmp $b->[1] } @sortlist;
+
}
+ my @taxaccounts;
+ my %taxaccounts;
+ my $taxrate;
+ my $taxamount;
+ my $taxbase;
+ my $taxdiff;
+
+ $query = qq|SELECT p.description, t.description
+ FROM project p
+ LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
+ WHERE id = ?|;
+ my $prh = $dbh->prepare($query) || $form->dberror($query);
+
+ my $runningnumber = 1;
my $sameitem = "";
- foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
+ my $subtotal;
+ my $k = scalar @sortlist;
+ my $j = 0;
+
+ foreach $item (@sortlist) {
$i = $item->[0];
+ $j++;
+
+ if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
+ if ($item->[1] ne $sameitem) {
+
+ $projectnumber = "";
+ if ($form->{groupprojectnumber} && $item->[2]) {
+ # get project description
+ $prh->execute($item->[3]) || $form->dberror($query);
+
+ ($projectnumber, $translation) = $prh->fetchrow_array;
+ $prh->finish;
+
+ $projectnumber = ($translation) ? "$item->[2], $translation" : "$item->[2], $projectnumber";
+ }
+
+ if ($form->{grouppartsgroup} && $item->[4]) {
+ $projectnumber .= " / " if $projectnumber;
+ $projectnumber .= $item->[4];
+ }
- if ($item->[1] ne $sameitem) {
- push(@{ $form->{description} }, qq|$item->[1]|);
- $sameitem = $item->[1];
+ $form->{projectnumber} = $projectnumber;
+ $form->format_string(projectnumber);
- map { push(@{ $form->{$_} }, "") } qw(runningnumber number bin qty unit deliverydate sellprice listprice netprice discount linetotal);
+ push(@{ $form->{description} }, qq|$form->{projectnumber}|);
+ $sameitem = $item->[1];
+
+ map { push(@{ $form->{$_} }, "") } qw(runningnumber number sku serialnumber bin qty unit deliverydate projectnumber sellprice listprice netprice discount discountrate linetotal weight);
+ }
}
-
+
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
if ($form->{"qty_$i"} != 0) {
+ $form->{totalqty} += $form->{"qty_$i"};
+ $form->{totalship} += $form->{"ship_$i"};
+ $form->{totalweight} += ($form->{"qty_$i"} * $form->{"weight_$i"});
+
# add number, description and qty to $form->{number}, ....
- push(@{ $form->{runningnumber} }, $i);
+ push(@{ $form->{runningnumber} }, $runningnumber++);
push(@{ $form->{number} }, qq|$form->{"partnumber_$i"}|);
+ push(@{ $form->{sku} }, qq|$form->{"sku_$i"}|);
+ push(@{ $form->{serialnumber} }, qq|$form->{"serialnumber_$i"}|);
push(@{ $form->{bin} }, qq|$form->{"bin_$i"}|);
push(@{ $form->{description} }, qq|$form->{"description_$i"}|);
push(@{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}));
push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
push(@{ $form->{deliverydate} }, qq|$form->{"deliverydate_$i"}|);
+ push(@{ $form->{projectnumber} }, qq|$form->{"projectnumber_$i"}|);
push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
# listprice
push(@{ $form->{listprice} }, $form->{"listprice_$i"});
+
+ push(@{ $form->{weight} }, $form->{"weight_$i"});
my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
my ($dec) = ($sellprice =~ /\.(\d+)/);
$linetotal = ($linetotal != 0) ? $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;
+
push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2));
- my $taxrate = 0;
- my ($taxamount, $taxbase);
+ @taxaccounts = split / /, $form->{"taxaccounts_$i"};
+ $taxrate = 0;
+ $taxdiff = 0;
- map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
+ map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
if ($form->{taxincluded}) {
# calculate tax
- $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
+ $taxamount = $linetotal * $taxrate / (1 + $taxrate);
$taxbase = $linetotal - $taxamount;
} else {
$taxamount = $linetotal * $taxrate;
$taxbase = $linetotal;
}
-
- if ($taxamount != 0) {
- foreach my $item (split / /, $form->{"taxaccounts_$i"}) {
+
+ if (@taxaccounts && $form->round_amount($taxamount, 2) == 0) {
+ if ($form->{taxincluded}) {
+ foreach $item (@taxaccounts) {
+ $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
+
+ $taxaccounts{$item} += $taxamount;
+ $taxdiff += $taxamount;
+
+ $taxbase{$item} += $taxbase;
+ }
+ $taxaccounts{$taxaccounts[0]} += $taxdiff;
+ } else {
+ foreach $item (@taxaccounts) {
+ $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
+ $taxbase{$item} += $taxbase;
+ }
+ }
+ } else {
+ foreach $item (@taxaccounts) {
$taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
$taxbase{$item} += $taxbase;
}
}
+
if ($form->{"assembly_$i"}) {
- $sameitem = "";
+ my $sm = "";
# get parts and push them onto the stack
my $sortorder = "";
- if ($form->{groupitems}) {
+ if ($form->{groupitems}) {
$sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
} else {
$sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
}
$query = qq|SELECT p.partnumber, p.description, p.unit, a.qty,
- pg.partsgroup
+ 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)
$sth->execute || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
- map { push(@{ $form->{$_} }, "") } qw(runningnumber number unit qty bin sellprice listprice netprice discount linetotal);
- $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
- push(@{ $form->{description} }, $sameitem);
+ if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sameitem) {
+ map { push(@{ $form->{$_} }, "") } qw(runningnumber number sku serialnumber unit qty bin deliverydate projectnumber sellprice listprice netprice discount discountrate linetotal weight);
+ $sm = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
+ push(@{ $form->{description} }, $sm);
}
- push(@{ $form->{number} }, qq|$ref->{partnumber}|);
- push(@{ $form->{description} }, qq|$ref->{description}|);
- push(@{ $form->{unit} }, qq|$ref->{unit}|);
- push(@{ $form->{qty} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}));
-
- map { push(@{ $form->{$_} }, "") } qw(runningnumber bin sellprice listprice netprice discount linetotal);
+ map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
+ $form->format_string("a_partnumber", "a_description");
+
+ push(@{ $form->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
+ map { push(@{ $form->{$_} }, "") } qw(runningnumber number sku serialnumber unit qty bin deliverydate projectnumber sellprice listprice netprice discount discountrate linetotal weight);
}
$sth->finish;
}
-
}
+
+ # add subtotal
+ if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
+ if ($subtotal) {
+ if ($j < $k) {
+ # look at next item
+ if ($sortlist[$j]->[1] ne $sameitem) {
+ map { push(@{ $form->{$_} }, "") } qw(runningnumber number sku serialnumber bin qty unit deliverydate projectnumber sellprice listprice netprice discount discountrate weight);
+ push(@{ $form->{description} }, $form->{groupsubtotaldescription});
+ if (exists $form->{groupsubtotaldescription}) {
+ push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $subtotal, 2));
+ } else {
+ push(@{ $form->{linetotal} }, "");
+ }
+ $subtotal = 0;
+ }
+ } else {
+
+ # got last item
+ if (exists $form->{groupsubtotaldescription}) {
+ map { push(@{ $form->{$_} }, "") } qw(runningnumber number sku serialnumber bin qty unit deliverydate projectnumber sellprice listprice netprice discount discountrate weight);
+ push(@{ $form->{description} }, $form->{groupsubtotaldescription});
+ push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $subtotal, 2));
+ }
+ }
+ }
+ }
+
}
foreach my $item (sort keys %taxaccounts) {
if ($form->round_amount($taxaccounts{$item}, 2) != 0) {
push(@{ $form->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
-
+
$tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
-
+
push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount));
push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
push(@{ $form->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
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"});
}
}
+ map { $form->{$_} = $form->format_amount($myconfig, $form->{$_}) } qw(totalqty totalship totalweight);
$form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
$form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
+
+ use SL::CP;
+ my $c;
+ if ($form->{language_code}) {
+ $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_amount} = $c->num2text($whole);
+
$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);
- # myconfig variables
- map { $form->{$_} = $myconfig->{$_} } (qw(company address tel fax signature businessnumber));
- $form->{username} = $myconfig->{name};
-
$dbh->disconnect;
}
my $dbh = $form->dbconnect($myconfig);
# get rest for the customer
- my $query = qq|SELECT customernumber, name, addr1, addr2, addr3, addr4,
- phone as customerphone, fax as customerfax, contact
+ my $query = qq|SELECT customernumber, name, address1, address2, city,
+ state, zipcode, country,
+ phone as customerphone, fax as customerfax, contact,
+ taxnumber, sic_code AS sic, iban, bic
FROM customer
WHERE id = $form->{customer_id}|;
my $sth = $dbh->prepare($query);
# connect to database, turn off autocommit
my $dbh = $form->dbconnect_noauto($myconfig);
- my ($query, $sth, $null, $project_id, $deliverydate);
+ my $query;
+ my $sth;
+ my $null;
+ my $project_id;
my $exchangerate = 0;
+
+ ($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;
if ($form->{id}) {
$uid .= $form->{login};
$query = qq|INSERT INTO ar (invnumber, employee_id)
- VALUES ('$uid', (SELECT id FROM employee
- WHERE login = '$form->{login}') )|;
+ VALUES ('$uid', $form->{employee_id})|;
$dbh->do($query) || $form->dberror($query);
$query = qq|SELECT id FROM ar
$sth->finish;
}
-
- map { $form->{$_} =~ s/'/''/g } (qw(invnumber shippingpoint notes message));
-
my ($netamount, $invoicediff) = (0, 0);
my ($amount, $linetotal, $lastincomeaccno);
if ($form->{"qty_$i"} != 0) {
- map { $form->{"${_}_$i"} =~ s/'/''/g } (qw(partnumber description unit));
-
+ # project
+ $project_id = 'NULL';
+ if ($form->{"projectnumber_$i"}) {
+ ($null, $project_id) = split /--/, $form->{"projectnumber_$i"};
+ }
+
# undo discount formatting
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
$netamount += $linetotal;
- if ($taxamount != 0) {
+ if ($form->round_amount($taxamount, 2) != 0) {
map { $form->{amount}{$form->{id}}{$_} += $taxamount * $form->{"${_}_rate"} / $taxrate } split / /, $form->{"taxaccounts_$i"};
}
# this is the difference from the inventory
$invoicediff += ($amount - $linetotal);
-
+
$form->{amount}{$form->{id}}{$form->{"income_accno_$i"}} += $linetotal;
$lastincomeaccno = $form->{"income_accno_$i"};
-
# adjust and round sellprice
$form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
if ($form->{"assembly_$i"}) {
# do not update if assembly consists of all services
$query = qq|SELECT sum(p.inventory_accno_id)
- FROM parts p, assembly a
- WHERE a.parts_id = p.id
- AND a.id = $form->{"id_$i"}|;
+ FROM parts p
+ JOIN assembly a ON (a.parts_id = p.id)
+ WHERE a.id = $form->{"id_$i"}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
"parts",
"onhand",
qq|id = $form->{"id_$i"}|,
- $form->{"qty_$i"} * -1);
+ $form->{"qty_$i"} * -1) unless $form->{shipped};
}
$sth->finish;
"parts",
"onhand",
qq|id = $form->{"id_$i"}|,
- $form->{"qty_$i"} * -1);
+ $form->{"qty_$i"} * -1) unless $form->{shipped};
$allocated = &cogs($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
}
}
- $project_id = 'NULL';
- if ($form->{"project_id_$i"}) {
- $project_id = $form->{"project_id_$i"};
- }
- $deliverydate = ($form->{"deliverydate_$i"}) ? qq|'$form->{"deliverydate_$i"}'| : "NULL";
-
+
# save detail record in invoice table
$query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty,
sellprice, fxsellprice, discount, allocated, assemblyitem,
- unit, deliverydate, project_id)
- VALUES ($form->{id}, $form->{"id_$i"},
- '$form->{"description_$i"}', $form->{"qty_$i"},
- $form->{"sellprice_$i"}, $fxsellprice,
- $form->{"discount_$i"}, $allocated, 'f',
- '$form->{"unit_$i"}', $deliverydate, $project_id)|;
+ unit, deliverydate, project_id, serialnumber)
+ VALUES ($form->{id}, $form->{"id_$i"}, |
+ .$dbh->quote($form->{"description_$i"}).qq|,
+ $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice,
+ $form->{"discount_$i"}, $allocated, 'f', |
+ .$dbh->quote($form->{"unit_$i"}).qq|, |
+ .$form->dbquote($form->{"deliverydate_$i"}, SQL_DATE).qq|,
+ $project_id, |
+ .$dbh->quote($form->{"serialnumber_$i"}).qq|)|;
$dbh->do($query) || $form->dberror($query);
}
}
- $form->{datepaid} = $form->{invdate};
+ $form->{datepaid} = $form->{transdate};
# total payments, don't move we need it here
+ $form->{paid} = 0;
for my $i (1 .. $form->{paidaccounts}) {
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
$form->{paid} += $form->{"paid_$i"};
}
}
-
- $form->{amount}{$form->{id}}{$form->{AR}} = $netamount + $tax;
+ $diff = 0 if $form->{paidaccounts} < 2;
- if ($form->{paid} != 0) {
- $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
- }
+ $form->{amount}{$form->{id}}{$form->{AR}} = $netamount + $tax;
+ $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
# reverse AR
$form->{amount}{$form->{id}}{$form->{AR}} *= -1;
# update exchangerate
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, $form->{exchangerate}, 0);
+ $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
}
foreach my $trans_id (keys %{$form->{amount}}) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
if (($form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)) != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate)
VALUES ($trans_id, (SELECT id FROM chart
- WHERE accno = '$accno'),
- $form->{amount}{$trans_id}{$accno}, '$form->{invdate}')|;
+ WHERE accno = '$accno'),
+ $form->{amount}{$trans_id}{$accno},
+ '$form->{transdate}')|;
$dbh->do($query) || $form->dberror($query);
}
}
}
}
+
# force AR entry if 0
- $form->{amount}{$form->{id}}{$form->{AR}} = 1 if ($form->{amount}{$form->{id}}{$form->{AR}} == 0);
+ $form->{amount}{$form->{id}}{$form->{AR}} = $form->{paid} if ($form->{amount}{$form->{id}}{$form->{AR}} == 0);
# record payments and offsetting AR
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"} != 0) {
my ($accno) = split /--/, $form->{"AR_paid_$i"};
- $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$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} + $diff, 2);
$form->{"paid_$i"} *= -1;
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source)
+ source, memo)
VALUES ($form->{id}, (SELECT id FROM chart
WHERE accno = '$accno'),
- $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
- '$form->{"source_$i"}')|;
+ $form->{"paid_$i"}, '$form->{"datepaid_$i"}', |
+ .$dbh->quote($form->{"source_$i"}).qq|, |
+ .$dbh->quote($form->{"memo_$i"}).qq|)|;
$dbh->do($query) || $form->dberror($query);
-
- $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"});
- }
-
-
+
# exchangerate difference
$form->{fx}{$accno}{$form->{"datepaid_$i"}} += $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
-
# gain/loss
- $amount = $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * $form->{"exchangerate_$i"};
+ $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate},2) - $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate_$i"},2);
if ($amount > 0) {
$form->{fx}{$form->{fxgain_accno}}{$form->{"datepaid_$i"}} += $amount;
} else {
# set values which could be empty to 0
$form->{terms} *= 1;
$form->{taxincluded} *= 1;
- my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
- my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
-
- # fill in subject if there is none
- $form->{subject} = qq|$form->{label} $form->{invnumber}| unless $form->{subject};
- # if there is a message stuff it into the notes
- my $cc = "Cc: $form->{cc}\\r\n" if $form->{cc};
- my $bcc = "Bcc: $form->{bcc}\\r\n" if $form->{bcc};
- $form->{notes} .= qq|\r
-\r
-[email]\r
-To: $form->{email}\r
-$cc${bcc}Subject: $form->{subject}\r
-\r
-Message: $form->{message}\r| if $form->{message};
+ # if this is from a till
+ my $till = ($form->{till}) ? qq|'$form->{till}'| : "NULL";
+
# save AR record
$query = qq|UPDATE ar set
- invnumber = '$form->{invnumber}',
- ordnumber = '$form->{ordnumber}',
- transdate = '$form->{invdate}',
+ invnumber = |.$dbh->quote($form->{invnumber}).qq|,
+ ordnumber = |.$dbh->quote($form->{ordnumber}).qq|,
+ quonumber = |.$dbh->quote($form->{quonumber}).qq|,
+ transdate = '$form->{transdate}',
customer_id = $form->{customer_id},
amount = $amount,
netamount = $netamount,
paid = $form->{paid},
- datepaid = $datepaid,
- duedate = $duedate,
+ datepaid = |.$form->dbquote($form->{datepaid}, SQL_DATE).qq|,
+ duedate = |.$form->dbquote($form->{duedate}, SQL_DATE).qq|,
invoice = '1',
- shippingpoint = '$form->{shippingpoint}',
+ shippingpoint = |.$dbh->quote($form->{shippingpoint}).qq|,
+ shipvia = |.$dbh->quote($form->{shipvia}).qq|,
terms = $form->{terms},
- notes = '$form->{notes}',
+ notes = |.$dbh->quote($form->{notes}).qq|,
+ intnotes = |.$dbh->quote($form->{intnotes}).qq|,
taxincluded = '$form->{taxincluded}',
- curr = '$form->{currency}'
+ curr = '$form->{currency}',
+ department_id = $form->{department_id},
+ employee_id = $form->{employee_id},
+ till = $till,
+ language_code = '$form->{language_code}'
WHERE id = $form->{id}
|;
$dbh->do($query) || $form->dberror($query);
$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);
+
my $rc = $dbh->commit;
$dbh->disconnect;
p.partnumber, p.description, p.unit,
p.inventory_accno_id, p.income_accno_id,
p.expense_accno_id
- FROM assembly a, parts p
- WHERE a.parts_id = p.id
- AND a.id = $id|;
+ FROM assembly a
+ JOIN parts p ON (a.parts_id = p.id)
+ WHERE a.id = $id|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$ref->{inventory_accno_id} *= 1;
$ref->{expense_accno_id} *= 1;
- map { $ref->{$_} =~ s/'/''/g } (qw(partnumber description unit));
-
# multiply by number of assemblies
$ref->{qty} *= $totalqty;
$query = qq|INSERT INTO invoice (trans_id, description, parts_id, qty,
sellprice, fxsellprice, allocated, assemblyitem, unit)
VALUES
- ($form->{id}, '$ref->{description}',
- $ref->{parts_id}, $ref->{qty}, 0, 0, $allocated, 't',
- '$ref->{unit}')|;
+ ($form->{id}, |
+ .$dbh->quote($ref->{description}).qq|,
+ $ref->{parts_id}, $ref->{qty}, 0, 0, $allocated, 't', |
+ .$dbh->quote($ref->{unit}).qq|)|;
$dbh->do($query) || $form->dberror($query);
}
sub cogs {
my ($dbh, $form, $id, $totalqty) = @_;
-
+
my $query = qq|SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice,
- (SELECT c.accno FROM chart c
- WHERE p.inventory_accno_id = c.id)
- AS inventory_accno,
- (SELECT c.accno FROM chart c
- WHERE p.expense_accno_id = c.id)
- AS expense_accno
+ (SELECT c.accno FROM chart c
+ WHERE p.inventory_accno_id = c.id) AS inventory_accno,
+ (SELECT c.accno FROM chart c
+ WHERE p.expense_accno_id = c.id) AS expense_accno
FROM invoice i, parts p
WHERE i.parts_id = p.id
AND i.parts_id = $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, parts p
- WHERE i.parts_id = p.id
- AND i.trans_id = $form->{id}|;
+ FROM invoice i
+ JOIN parts p ON (i.parts_id = p.id)
+ WHERE i.trans_id = $form->{id}|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
if ($ref->{inventory_accno_id} || $ref->{assembly}) {
# if the invoice item is not an assemblyitem adjust parts onhand
- unless ($ref->{assemblyitem}) {
+ if (!$ref->{assemblyitem}) {
# adjust onhand in parts table
$form->update_balance($dbh,
"parts",
sub delete_invoice {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $spool) = @_;
# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
- # check for other foreign currency transactions
- $form->delete_exchangerate($dbh) if ($form->{currency} ne $form->{defaultcurrency});
-
&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 = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
+ # delete spool files
+ $query = qq|SELECT spoolfile FROM status
+ WHERE trans_id = $form->{id}
+ AND spoolfile IS NOT NULL|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ my $spoolfile;
+ my @spoolfiles = ();
+
+ while (($spoolfile) = $sth->fetchrow_array) {
+ push @spoolfiles, $spoolfile;
+ }
+ $sth->finish;
+
+ # delete status entries
+ $query = qq|DELETE FROM status
+ WHERE trans_id = $form->{id}|;
+ $dbh->do($query) || $form->dberror($query);
+
my $rc = $dbh->commit;
$dbh->disconnect;
+
+ if ($rc) {
+ foreach $spoolfile (@spoolfiles) {
+ unlink "$spool/$spoolfile" if $spoolfile;
+ }
+ }
$rc;
my $dbh = $form->dbconnect_noauto($myconfig);
my $query;
-
+
if ($form->{id}) {
# get default accounts and last invoice number
$query = qq|SELECT (SELECT c.accno FROM chart c
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
(SELECT c.accno FROM chart c
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
- d.invnumber, d.curr AS currencies, current_date AS invdate
+ d.curr AS currencies, current_date AS transdate
FROM defaults d|;
}
my $sth = $dbh->prepare($query);
if ($form->{id}) {
# retrieve invoice
- $query = qq|SELECT a.invnumber, a.ordnumber, a.transdate AS invdate, a.paid,
- a.shippingpoint, a.terms, a.notes, a.duedate, a.taxincluded,
- a.curr AS currency, (SELECT e.name FROM employee e
- WHERE e.id = a.employee_id) AS employee
+ $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
FROM ar a
+ LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = $form->{id}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
map { $form->{$_} = $ref->{$_} } keys %$ref;
$sth->finish;
- $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
-
# get shipto
$query = qq|SELECT * FROM shipto
WHERE trans_id = $form->{id}|;
$ref = $sth->fetchrow_hashref(NAME_lc);
map { $form->{$_} = $ref->{$_} } keys %$ref;
$sth->finish;
-
+
# retrieve individual items
- $query = qq|SELECT c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- i.description, i.qty, i.fxsellprice AS sellprice,
+ $query = qq|SELECT (SELECT c.accno FROM chart c
+ WHERE p.inventory_accno_id = c.id)
+ AS inventory_accno,
+ (SELECT c.accno FROM chart c
+ WHERE p.income_accno_id = c.id)
+ AS income_accno,
+ (SELECT c.accno FROM chart c
+ WHERE p.expense_accno_id = c.id)
+ AS expense_accno,
+ i.description, i.qty, i.fxsellprice, i.sellprice,
i.discount, i.parts_id AS id, i.unit, i.deliverydate,
- pr.projectnumber,
- i.project_id,
+ i.project_id, pr.projectnumber, i.serialnumber,
p.partnumber, p.assembly, p.bin,
- pg.partsgroup
+ pg.partsgroup, p.partsgroup_id, p.partnumber AS sku,
+ p.listprice, p.lastcost, p.weight,
+ t.description AS partsgrouptranslation
FROM invoice i
- JOIN parts p ON (i.parts_id = p.id)
- LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
- LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
- LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
- LEFT JOIN project pr ON (i.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ 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 = '$form->{language_code}')
WHERE i.trans_id = $form->{id}
AND NOT i.assemblyitem = '1'
ORDER BY i.id|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
+ # foreign currency
+ &exchangerate_defaults($dbh, $form);
+
+ # query for price matrix
+ my $pmh = &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)) {
- # get taxes
- $query = qq|SELECT c.accno
- FROM chart c, partstax pt
- WHERE pt.chart_id = c.id
- AND pt.parts_id = $ref->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+
+ ($decimalplaces) = ($ref->{fxsellprice} =~ /\.(\d+)/);
+ $decimalplaces = length $decimalplaces;
+ $decimalplaces = 2 unless $decimalplaces;
+
+ $tth->execute($ref->{id});
$ref->{taxaccounts} = "";
- my $taxrate = 0;
+ $taxrate = 0;
- while (my $ptref = $sth->fetchrow_hashref(NAME_lc)) {
+ while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
$ref->{taxaccounts} .= "$ptref->{accno} ";
$taxrate += $form->{"$ptref->{accno}_rate"};
}
- $sth->finish;
+ $tth->finish;
chop $ref->{taxaccounts};
+ # price matrix
+ $ref->{sellprice} = ($ref->{fxsellprice} * $form->{$form->{currency}});
+ &price_matrix($pmh, $ref, $form->{transdate}, $decimalplaces, $form, $myconfig, 1);
+ $ref->{sellprice} = $ref->{fxsellprice};
+
+ $ref->{partsgroup} = $ref->{partsgrouptranslation} if $ref->{partsgrouptranslation};
+
push @{ $form->{invoice_details} }, $ref;
}
$sth->finish;
- } else {
-
- $form->{shippingpoint} = $myconfig->{shippingpoint} unless $form->{shippingpoint};
-
- # up invoice number by 1
- $form->{invnumber}++;
-
- # save the new number
- $query = qq|UPDATE defaults
- SET invnumber = '$form->{invnumber}'|;
- $dbh->do($query) || $form->dberror($query);
-
- $form->get_employee($dbh);
-
}
-
my $rc = $dbh->commit;
$dbh->disconnect;
# connect to database
my $dbh = $form->dbconnect($myconfig);
-
+
my $dateformat = $myconfig->{dateformat};
- $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
-
- my $duedate = ($form->{invdate}) ? "to_date('$form->{invdate}', '$dateformat')" : "current_date";
+ if ($myconfig->{dateformat} !~ /^y/) {
+ my @a = split /\W/, $form->{transdate};
+ $dateformat .= "yy" if (length $a[2] > 2);
+ }
+
+ if ($form->{transdate} !~ /\W/) {
+ $dateformat = 'yyyymmdd';
+ }
+
+ my $duedate;
+
+ if ($myconfig->{dbdriver} eq 'DB2') {
+ $duedate = ($form->{transdate}) ? "date('$form->{transdate}') + c.terms DAYS" : "current_date + c.terms DAYS";
+ } else {
+ $duedate = ($form->{transdate}) ? "to_date('$form->{transdate}', '$dateformat') + c.terms" : "current_date + c.terms";
+ }
$form->{customer_id} *= 1;
# get customer
my $query = qq|SELECT c.name AS customer, c.discount, c.creditlimit, c.terms,
c.email, c.cc, c.bcc, c.taxincluded,
- c.addr1, c.addr2, c.addr3, c.addr4,
- $duedate + c.terms AS duedate
+ c.address1, c.address2, c.city, c.state,
+ c.zipcode, c.country, c.curr AS currency, c.language_code,
+ $duedate AS duedate, c.notes AS intnotes,
+ b.discount AS tradediscount, b.description AS business,
+ e.name AS employee, e.id AS employee_id
FROM customer c
+ LEFT JOIN business b ON (b.id = c.business_id)
+ LEFT JOIN employee e ON (e.id = c.employee_id)
WHERE c.id = $form->{customer_id}|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$ref = $sth->fetchrow_hashref(NAME_lc);
+ if ($form->{id}) {
+ map { delete $ref->{$_} } qw(currency taxincluded employee employee_id intnotes);
+ }
+
map { $form->{$_} = $ref->{$_} } keys %$ref;
$sth->finish;
+
+ # if no currency use defaultcurrency
+ $form->{currency} = ($form->{currency}) ? $form->{currency} : $form->{defaultcurrency};
+ $form->{exchangerate} = 0 if $form->{currency} eq $form->{defaultcurrency};
+ if ($form->{transdate} && ($form->{currency} ne $form->{defaultcurrency})) {
+ $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, "buy");
+ }
+ $form->{forex} = $form->{exchangerate};
+
+ # if no employee, default to login
+ ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh) unless $form->{employee_id};
$form->{creditremaining} = $form->{creditlimit};
$query = qq|SELECT SUM(amount - paid)
AND e.transdate = o.transdate)
FROM oe o
WHERE o.customer_id = $form->{customer_id}
+ AND o.quotation = '0'
AND o.closed = '0'|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
# get shipto if we did not converted an order or invoice
if (!$form->{shipto}) {
- map { delete $form->{$_} } qw(shiptoname shiptoaddr1 shiptoaddr2 shiptoaddr3 shiptoaddr4 shiptocontact shiptophone shiptofax shiptoemail);
+ map { delete $form->{$_} } qw(shiptoname shiptoaddress1 shiptoaddress2 shiptocity shiptostate shiptozipcode shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
$query = qq|SELECT * FROM shipto
WHERE trans_id = $form->{customer_id}|;
# get taxes we charge for this customer
$query = qq|SELECT c.accno
- FROM chart c, customertax ct
- WHERE ct.chart_id = c.id
- AND ct.customer_id = $form->{customer_id}|;
+ FROM chart c
+ JOIN customertax ct ON (ct.chart_id = c.id)
+ WHERE ct.customer_id = $form->{customer_id}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
# get tax rates and description
$query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
- FROM chart c, tax t
- WHERE c.id = t.chart_id
- AND c.link LIKE '%CT_tax%'
+ FROM chart c
+ JOIN tax t ON (c.id = t.chart_id)
+ WHERE c.link LIKE '%CT_tax%'
ORDER BY accno|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
chop $form->{taxaccounts};
# setup last accounts used for this customer
- if (!$form->{id} && $form->{type} !~ /_order/) {
- $query = qq|SELECT c.accno, c.description, c.link, c.category
+ if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
+ $query = qq|SELECT c.accno, c.description, c.link, c.category,
+ ac.project_id, p.projectnumber, a.department_id,
+ d.description AS department
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
JOIN ar a ON (a.id = ac.trans_id)
+ LEFT JOIN project p ON (ac.project_id = p.id)
+ LEFT JOIN department d ON (d.id = a.department_id)
WHERE a.customer_id = $form->{customer_id}
- AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
AND a.id IN (SELECT max(id) FROM ar
WHERE customer_id = $form->{customer_id})|;
$sth = $dbh->prepare($query);
my $i = 0;
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- if ($ref->{category} eq 'I') {
+ $form->{department} = $ref->{department};
+ $form->{department_id} = $ref->{department_id};
+
+ if ($ref->{link} =~ /_amount/) {
$i++;
$form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
+ $form->{"projectnumber_$i"} = "$ref->{projectnumber}--$ref->{project_id}";
}
if ($ref->{category} eq 'A') {
$form->{AR} = $form->{AR_1} = "$ref->{accno}--$ref->{description}";
}
$dbh->disconnect;
-
+
}
sub retrieve_item {
my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
my $i = $form->{rowcount};
+ my $null;
my $var;
- my $where = "NOT obsolete = '1'";
+
+ my $where = "WHERE p.obsolete = '0' AND NOT p.income_accno_id IS NULL";
if ($form->{"partnumber_$i"}) {
$var = $form->like(lc $form->{"partnumber_$i"});
}
if ($form->{"description_$i"}) {
$var = $form->like(lc $form->{"description_$i"});
- $where .= " AND lower(p.description) LIKE '$var'";
+ if ($form->{language_code}) {
+ $where .= " AND lower(t1.description) LIKE '$var'";
+ } else {
+ $where .= " AND lower(p.description) LIKE '$var'";
+ }
}
if ($form->{"partsgroup_$i"}) {
- $var = $form->like(lc $form->{"partsgroup_$i"});
- $where .= " AND lower(pg.partsgroup) LIKE '$var'";
+ ($null, $var) = split /--/, $form->{"partsgroup_$i"};
+ $var *= 1;
+ if ($var == 0) {
+ # search by partsgroup, this is for the POS
+ $where .= qq| AND pg.partsgroup = '$form->{"partsgroup_$i"}'|;
+ } else {
+ $where .= qq| AND p.partsgroup_id = $var|;
+ }
}
if ($form->{"description_$i"}) {
- $where .= " ORDER BY description";
+ $where .= " ORDER BY 3";
} else {
- $where .= " ORDER BY partnumber";
+ $where .= " ORDER BY 2";
}
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
- p.listprice,
- c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- p.unit, p.assembly, p.bin, p.onhand, p.makemodel,
- pg.partsgroup
+ p.listprice, p.lastcost,
+ c1.accno AS inventory_accno,
+ c2.accno AS income_accno,
+ c3.accno AS expense_accno,
+ p.unit, p.assembly, p.bin, p.onhand,
+ pg.partsgroup, p.partsgroup_id, p.partnumber AS sku,
+ p.weight,
+ t1.description AS translation,
+ t2.description AS grouptranslation
FROM parts p
LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE $where|;
+ LEFT JOIN translation t1 ON (t1.trans_id = p.id AND t1.language_code = '$form->{language_code}')
+ LEFT JOIN translation t2 ON (t2.trans_id = p.partsgroup_id AND t2.language_code = '$form->{language_code}')
+ $where|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ 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 = &price_matrix_query($dbh, $form);
+
+ my $transdate = $form->datetonum($form->{transdate}, $myconfig);
+ my $decimalplaces;
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+ ($decimalplaces) = ($ref->{sellprice} =~ /\.(\d+)/);
+ $decimalplaces = length $decimalplaces;
+ $decimalplaces = 2 unless $decimalplaces;
+
# get taxes for part
- $query = qq|SELECT c.accno
- FROM chart c
- JOIN partstax pt ON (c.id = pt.chart_id)
- WHERE pt.parts_id = $ref->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $tth->execute($ref->{id});
$ref->{taxaccounts} = "";
- while (my $ptref = $sth->fetchrow_hashref(NAME_lc)) {
+ while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
$ref->{taxaccounts} .= "$ptref->{accno} ";
}
- $sth->finish;
+ $tth->finish;
chop $ref->{taxaccounts};
- # get makemodel
- if ($ref->{makemodel}) {
- $query = qq|SELECT name
- FROM makemodel
- WHERE parts_id = $ref->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref->{makemodel} = "";
- while (my $ptref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{makemodel} .= "$ptref->{name}:";
- }
- $sth->finish;
- chop $ref->{makemodel};
- }
+ # get matrix
+ &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;
}
}
+sub price_matrix_query {
+ my ($dbh, $form) = @_;
+
+ my $query = qq|SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ WHERE p.parts_id = ?
+ AND p.customer_id = $form->{customer_id}
+
+ UNION
+
+ SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ JOIN customer c ON (c.pricegroup_id = g.id)
+ WHERE p.parts_id = ?
+ AND c.id = $form->{customer_id}
+
+ UNION
+
+ SELECT p.*, '' AS pricegroup
+ FROM partscustomer p
+ WHERE p.customer_id = 0
+ AND p.pricegroup_id = 0
+ AND p.parts_id = ?
+
+ ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak
+
+ |;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ $sth;
+
+}
+
+
+sub price_matrix {
+ my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig, $init) = @_;
+
+ $pmh->execute($ref->{id}, $ref->{id}, $ref->{id});
+
+ $ref->{pricematrix} = "";
+ my $customerprice;
+ my $pricegroup;
+ my $sellprice;
+ my $mref;
+
+ while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {
+
+ $customerprice = 0;
+ $pricegroup = 0;
+
+ # check date
+ if ($mref->{validfrom}) {
+ next if $transdate < $form->datetonum($mref->{validfrom}, $myconfig);
+ }
+ if ($mref->{validto}) {
+ next if $transdate > $form->datetonum($mref->{validto}, $myconfig);
+ }
+
+ # convert price
+ $sellprice = $form->round_amount($mref->{sellprice} * $form->{$mref->{curr}}, $decimalplaces);
+
+ if ($mref->{customer_id}) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ $customerprice = 1;
+ }
+
+ if ($mref->{pricegroup_id}) {
+ if (! $customerprice) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ $pricegroup = 1;
+ }
+ }
+
+ if (! $customerprice && ! $pricegroup) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ }
+
+ if ($form->{tradediscount}) {
+ $ref->{sellprice} = $form->round_amount($ref->{sellprice} / (1 - $form->{tradediscount}), $decimalplaces);
+ }
+
+ }
+ $pmh->finish;
+
+ if ($ref->{pricematrix} !~ /^0:/) {
+ if ($init) {
+ $sellprice = $form->round_amount($ref->{sellprice}, $decimalplaces);
+ } else {
+ $sellprice = $form->round_amount($ref->{sellprice} * (1 - $form->{tradediscount}), $decimalplaces);
+ }
+ $ref->{pricematrix} = "0:$sellprice ".$ref->{pricematrix};
+ }
+ chop $ref->{pricematrix};
+
+}
+
+
+sub exchangerate_defaults {
+ my ($dbh, $form) = @_;
+
+ my $var;
+
+ # get default currencies
+ my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ 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->{defaultcurrency}} = 1;
+
+}
+
+
1;
projects / freeside.git / blobdiff