Revision e840d786
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/IR.pm | ||
|---|---|---|
|
# connect to database, turn off autocommit
|
||
|
my $dbh = $form->dbconnect_noauto($myconfig);
|
||
|
|
||
|
my ($query, $sth, $null, $project_id);
|
||
|
my ($query, $sth, @values, $project_id);
|
||
|
my ($allocated, $taxrate, $taxamount, $taxdiff, $item);
|
||
|
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
|
||
|
my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
|
||
|
my $exchangerate = 0;
|
||
|
my $allocated;
|
||
|
my $taxrate;
|
||
|
my $taxamount;
|
||
|
my $taxdiff;
|
||
|
my $item;
|
||
|
|
||
|
my $all_units = AM->retrieve_units($myconfig, $form);
|
||
|
|
||
| ... | ... | |
|
&reverse_invoice($dbh, $form);
|
||
|
|
||
|
} else {
|
||
|
my $uid = rand() . time;
|
||
|
|
||
|
$uid .= $form->{login};
|
||
|
|
||
|
$uid = substr($uid, 2, 75);
|
||
|
|
||
|
$query = qq|INSERT INTO ap (invnumber, employee_id)
|
||
|
VALUES ('$uid', '$form->{employee_id}')|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
|
||
|
$query = qq|SELECT a.id FROM ap a
|
||
|
WHERE a.invnumber = '$uid'|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
|
||
|
|
||
|
($form->{id}) = $sth->fetchrow_array;
|
||
|
$sth->finish;
|
||
|
do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber) VALUES (?, '')|, $form->{id});
|
||
|
}
|
||
|
|
||
|
map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
|
||
|
|
||
|
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
|
||
|
my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
|
||
|
|
||
|
if ($form->{currency} eq $form->{defaultcurrency}) {
|
||
|
$form->{exchangerate} = 1;
|
||
|
} else {
|
||
| ... | ... | |
|
|
||
|
$form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
|
||
|
|
||
|
my %item_units;
|
||
|
my $q_item_unit = qq|SELECT unit FROM parts WHERE id = ?|;
|
||
|
my $h_item_unit = prepare_query($form, $dbh, $q_item_unit);
|
||
|
|
||
|
for my $i (1 .. $form->{rowcount}) {
|
||
|
next unless $form->{"id_$i"};
|
||
|
|
||
|
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
|
||
|
|
||
|
|
||
|
if ($form->{storno}) {
|
||
|
$form->{"qty_$i"} *= -1;
|
||
|
}
|
||
| ... | ... | |
|
if ($main::eur) {
|
||
|
$form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
|
||
|
}
|
||
|
|
||
|
if ($form->{"id_$i"}) {
|
||
|
|
||
|
# get item baseunit
|
||
|
$query = qq|SELECT p.unit
|
||
|
FROM parts p
|
||
|
WHERE p.id = $form->{"id_$i"}|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
|
||
|
my ($item_unit) = $sth->fetchrow_array();
|
||
|
$sth->finish;
|
||
|
|
||
|
if (defined($all_units->{$item_unit}->{factor})
|
||
|
&& ($all_units->{$item_unit}->{factor} ne '')
|
||
|
&& ($all_units->{$item_unit}->{factor} * 1 != 0)) {
|
||
|
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
||
|
} else {
|
||
|
$basefactor = 1;
|
||
|
}
|
||
|
$baseqty = $form->{"qty_$i"} * $basefactor;
|
||
|
|
||
|
map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
|
||
|
qw(partnumber description unit);
|
||
|
# get item baseunit
|
||
|
if (!$item_units{$form->{"id_$i"}}) {
|
||
|
do_statement($form, $h_item_unit, $q_item_unit, $form->{"id_$i"});
|
||
|
($item_units{$form->{"id_$i"}}) = $h_item_unit->fetchrow_array();
|
||
|
}
|
||
|
|
||
|
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
||
|
$taxdiff = 0;
|
||
|
$allocated = 0;
|
||
|
$taxrate = 0;
|
||
|
my $item_unit = $item_units{$form->{"id_$i"}};
|
||
|
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->parse_amount($myconfig, $form->{"sellprice_$i"});
|
||
|
my $fxsellprice = $form->{"sellprice_$i"};
|
||
|
if (defined($all_units->{$item_unit}->{factor})
|
||
|
&& ($all_units->{$item_unit}->{factor} ne '')
|
||
|
&& ($all_units->{$item_unit}->{factor} * 1 != 0)) {
|
||
|
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
||
|
} else {
|
||
|
$basefactor = 1;
|
||
|
}
|
||
|
$baseqty = $form->{"qty_$i"} * $basefactor;
|
||
|
|
||
|
my ($dec) = ($fxsellprice =~ /\.(\d+)/);
|
||
|
$dec = length $dec;
|
||
|
my $decimalplaces = ($dec > 2) ? $dec : 2;
|
||
|
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
||
|
$taxdiff = 0;
|
||
|
$allocated = 0;
|
||
|
$taxrate = 0;
|
||
|
|
||
|
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
|
||
|
$form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
|
||
|
my $fxsellprice = $form->{"sellprice_$i"};
|
||
|
|
||
|
if ($form->{"inventory_accno_$i"}) {
|
||
|
my ($dec) = ($fxsellprice =~ /\.(\d+)/);
|
||
|
$dec = length $dec;
|
||
|
my $decimalplaces = ($dec > 2) ? $dec : 2;
|
||
|
|
||
|
$linetotal =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
||
|
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
|
||
|
|
||
|
if ($form->{taxincluded}) {
|
||
|
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
||
|
} else {
|
||
|
$taxamount = $linetotal * $taxrate;
|
||
|
}
|
||
|
if ($form->{"inventory_accno_$i"}) {
|
||
|
|
||
|
$netamount += $linetotal;
|
||
|
|
||
|
if ($form->round_amount($taxrate, 7) == 0) {
|
||
|
if ($form->{taxincluded}) {
|
||
|
foreach $item (@taxaccounts) {
|
||
|
$taxamount =
|
||
|
$form->round_amount($linetotal * $form->{"${item}_rate"} /
|
||
|
(1 + abs($form->{"${item}_rate"})),
|
||
|
2);
|
||
|
$taxdiff += $taxamount;
|
||
|
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
||
|
}
|
||
|
$form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
|
||
|
} else {
|
||
|
map {
|
||
|
$form->{amount}{ $form->{id} }{$_} -=
|
||
|
$linetotal * $form->{"${_}_rate"}
|
||
|
} @taxaccounts;
|
||
|
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
||
|
|
||
|
if ($form->{taxincluded}) {
|
||
|
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
|
||
|
$form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
||
|
} else {
|
||
|
$taxamount = $linetotal * $taxrate;
|
||
|
}
|
||
|
|
||
|
$netamount += $linetotal;
|
||
|
|
||
|
if ($form->round_amount($taxrate, 7) == 0) {
|
||
|
if ($form->{taxincluded}) {
|
||
|
foreach $item (@taxaccounts) {
|
||
|
$taxamount =
|
||
|
$form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
|
||
|
$taxdiff += $taxamount;
|
||
|
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
||
|
}
|
||
|
$form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
|
||
|
|
||
|
} else {
|
||
|
map {
|
||
|
$form->{amount}{ $form->{id} }{$_} -=
|
||
|
$taxamount * $form->{"${_}_rate"} / $taxrate
|
||
|
} @taxaccounts;
|
||
|
map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
|
||
|
}
|
||
|
|
||
|
# add purchase to inventory, this one is without the tax!
|
||
|
$amount =
|
||
|
$form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
||
|
$linetotal =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
|
||
|
$form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($linetotal, 2);
|
||
|
|
||
|
# this is the difference for the inventory
|
||
|
$invoicediff += ($amount - $linetotal);
|
||
|
|
||
|
$form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
|
||
|
$linetotal;
|
||
|
|
||
|
# adjust and round sellprice
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
|
||
|
$decimalplaces);
|
||
|
|
||
|
# update parts table
|
||
|
$query = qq|UPDATE parts SET
|
||
|
lastcost = $form->{"sellprice_$i"}
|
||
|
WHERE id = $form->{"id_$i"}|;
|
||
|
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
|
||
|
$form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
|
||
|
$baseqty, $form->{"id_$i"})
|
||
|
unless $form->{shipped};
|
||
|
|
||
|
# check if we sold the item already and
|
||
|
# make an entry for the expense and inventory
|
||
|
$query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
|
||
|
p.inventory_accno_id, p.expense_accno_id, a.transdate
|
||
|
FROM invoice i, ar a, parts p
|
||
|
WHERE i.parts_id = p.id
|
||
|
AND i.parts_id = $form->{"id_$i"}
|
||
|
AND (i.base_qty + i.allocated) > 0
|
||
|
AND i.trans_id = a.id
|
||
|
ORDER BY transdate|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
|
||
|
my $totalqty = $base_qty;
|
||
|
|
||
|
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
|
||
|
my $qty = $ref->{base_qty} + $ref->{allocated};
|
||
|
|
||
|
if (($qty - $totalqty) > 0) {
|
||
|
$qty = $totalqty;
|
||
|
}
|
||
|
} else {
|
||
|
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
|
||
|
}
|
||
|
|
||
|
$linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
|
||
|
|
||
|
if ($ref->{allocated} < 0) {
|
||
|
|
||
|
# we have an entry for it already, adjust amount
|
||
|
$form->update_balance(
|
||
|
$dbh,
|
||
|
"acc_trans",
|
||
|
"amount",
|
||
|
qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
|
||
|
$linetotal);
|
||
|
|
||
|
$form->update_balance(
|
||
|
$dbh,
|
||
|
"acc_trans",
|
||
|
"amount",
|
||
|
qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
|
||
|
$linetotal * -1);
|
||
|
|
||
|
} else {
|
||
|
|
||
|
# add entry for inventory, this one is for the sold item
|
||
|
if ($linetotal != 0) {
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
|
||
|
transdate, taxkey)
|
||
|
VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
|
||
|
$linetotal, '$ref->{transdate}', (SELECT taxkey_id FROM chart WHERE id = $ref->{inventory_accno_id}))|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
|
||
|
# add expense
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
|
||
|
transdate, taxkey)
|
||
|
VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
|
||
|
| . ($linetotal * -1) . qq|, '$ref->{transdate}',
|
||
|
(SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
}
|
||
|
}
|
||
|
# add purchase to inventory, this one is without the tax!
|
||
|
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($linetotal, 2);
|
||
|
|
||
|
# update allocated for sold item
|
||
|
$form->update_balance($dbh, "invoice", "allocated",
|
||
|
qq|id = $ref->{id}|,
|
||
|
$qty * -1);
|
||
|
# this is the difference for the inventory
|
||
|
$invoicediff += ($amount - $linetotal);
|
||
|
|
||
|
$allocated += $qty;
|
||
|
$form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -= $linetotal;
|
||
|
|
||
|
last if (($totalqty -= $qty) <= 0);
|
||
|
}
|
||
|
# adjust and round sellprice
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
|
||
|
|
||
|
$sth->finish;
|
||
|
# update parts table
|
||
|
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
|
||
|
@values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
$lastinventoryaccno = $form->{"inventory_accno_$i"};
|
||
|
if (!$form->{shipped}) {
|
||
|
$form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $baseqty, $form->{"id_$i"})
|
||
|
}
|
||
|
|
||
|
} else {
|
||
|
# check if we sold the item already and
|
||
|
# make an entry for the expense and inventory
|
||
|
$query =
|
||
|
qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
|
||
|
p.inventory_accno_id, p.expense_accno_id, a.transdate
|
||
|
FROM invoice i, ar a, parts p
|
||
|
WHERE (i.parts_id = p.id)
|
||
|
AND (i.parts_id = ?)
|
||
|
AND ((i.base_qty + i.allocated) > 0)
|
||
|
AND (i.trans_id = a.id)
|
||
|
ORDER BY transdate|;
|
||
|
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
|
||
|
|
||
|
$linetotal =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
||
|
my $totalqty = $base_qty;
|
||
|
|
||
|
if ($form->{taxincluded}) {
|
||
|
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
|
||
|
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
||
|
} else {
|
||
|
$taxamount = $linetotal * $taxrate;
|
||
|
my $qty = $ref->{base_qty} + $ref->{allocated};
|
||
|
|
||
|
if (($qty - $totalqty) > 0) {
|
||
|
$qty = $totalqty;
|
||
|
}
|
||
|
|
||
|
$netamount += $linetotal;
|
||
|
|
||
|
if ($form->round_amount($taxrate, 7) == 0) {
|
||
|
if ($form->{taxincluded}) {
|
||
|
foreach $item (@taxaccounts) {
|
||
|
$taxamount =
|
||
|
$linetotal * $form->{"${item}_rate"} /
|
||
|
(1 + abs($form->{"${item}_rate"}));
|
||
|
$totaltax += $taxamount;
|
||
|
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
||
|
}
|
||
|
} else {
|
||
|
map {
|
||
|
$form->{amount}{ $form->{id} }{$_} -=
|
||
|
$linetotal * $form->{"${_}_rate"}
|
||
|
} @taxaccounts;
|
||
|
}
|
||
|
} else {
|
||
|
map {
|
||
|
$form->{amount}{ $form->{id} }{$_} -=
|
||
|
$taxamount * $form->{"${_}_rate"} / $taxrate
|
||
|
} @taxaccounts;
|
||
|
$linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
|
||
|
|
||
|
if ($ref->{allocated} < 0) {
|
||
|
|
||
|
# we have an entry for it already, adjust amount
|
||
|
$form->update_balance($dbh, "acc_trans", "amount",
|
||
|
qq| (trans_id = $ref->{trans_id})
|
||
|
AND (chart_id = $ref->{inventory_accno_id})
|
||
|
AND (transdate = '$ref->{transdate}')|,
|
||
|
$linetotal);
|
||
|
|
||
|
$form->update_balance($dbh, "acc_trans", "amount",
|
||
|
qq| (trans_id = $ref->{trans_id})
|
||
|
AND (chart_id = $ref->{expense_accno_id})
|
||
|
AND (transdate = '$ref->{transdate}')|,
|
||
|
$linetotal * -1);
|
||
|
|
||
|
} elsif ($linetotal != 0) {
|
||
|
# add entry for inventory, this one is for the sold item
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey)
|
||
|
VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?))|;
|
||
|
@values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal,
|
||
|
$ref->{transdate}, $ref->{inventory_accno_id});
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
# add expense
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey)
|
||
|
VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|;
|
||
|
@values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1),
|
||
|
$ref->{transdate}, $ref->{expense_accno_id});
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
|
||
|
$amount =
|
||
|
$form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
||
|
$linetotal =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
|
||
|
$form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($linetotal, 2);
|
||
|
# update allocated for sold item
|
||
|
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1);
|
||
|
|
||
|
$allocated += $qty;
|
||
|
|
||
|
# this is the difference for expense
|
||
|
$expensediff += ($amount - $linetotal);
|
||
|
last if (($totalqty -= $qty) <= 0);
|
||
|
}
|
||
|
|
||
|
# add amount to expense
|
||
|
$form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
|
||
|
$linetotal;
|
||
|
$sth->finish();
|
||
|
|
||
|
$lastexpenseaccno = $form->{"expense_accno_$i"};
|
||
|
$lastinventoryaccno = $form->{"inventory_accno_$i"};
|
||
|
|
||
|
# adjust and round sellprice
|
||
|
$form->{"sellprice_$i"} =
|
||
|
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
|
||
|
$decimalplaces);
|
||
|
} else {
|
||
|
|
||
|
# update lastcost
|
||
|
$query = qq|UPDATE parts SET
|
||
|
lastcost = $form->{"sellprice_$i"}
|
||
|
WHERE id = $form->{"id_$i"}|;
|
||
|
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
||
|
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
if ($form->{taxincluded}) {
|
||
|
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
|
||
|
$form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
||
|
|
||
|
} else {
|
||
|
$taxamount = $linetotal * $taxrate;
|
||
|
}
|
||
|
|
||
|
$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, base_qty,
|
||
|
sellprice, fxsellprice, allocated, unit, deliverydate,
|
||
|
project_id, serialnumber)
|
||
|
VALUES ($form->{id}, $form->{"id_$i"},
|
||
|
'$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
|
||
|
$form->{"sellprice_$i"}, $fxsellprice, $allocated,
|
||
|
'$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
|
||
|
'$form->{"serialnumber_$i"}')|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$netamount += $linetotal;
|
||
|
|
||
|
if ($form->round_amount($taxrate, 7) == 0) {
|
||
|
if ($form->{taxincluded}) {
|
||
|
foreach $item (@taxaccounts) {
|
||
|
$taxamount =
|
||
|
$linetotal * $form->{"${item}_rate"}
|
||
|
/ (1 + abs($form->{"${item}_rate"}));
|
||
|
$totaltax += $taxamount;
|
||
|
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
||
|
}
|
||
|
|
||
|
} else {
|
||
|
map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
|
||
|
}
|
||
|
|
||
|
} else {
|
||
|
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
|
||
|
}
|
||
|
|
||
|
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
|
||
|
$linetotal = $form->round_amount($linetotal, 2);
|
||
|
|
||
|
# this is the difference for expense
|
||
|
$expensediff += ($amount - $linetotal);
|
||
|
|
||
|
# add amount to expense
|
||
|
$form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -= $linetotal;
|
||
|
|
||
|
$lastexpenseaccno = $form->{"expense_accno_$i"};
|
||
|
|
||
|
# adjust and round sellprice
|
||
|
$form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
|
||
|
|
||
|
# update lastcost
|
||
|
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
|
||
|
do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
|
||
|
}
|
||
|
|
||
|
# save detail record in invoice table
|
||
|
$query =
|
||
|
qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
|
||
|
sellprice, fxsellprice, allocated, unit, deliverydate,
|
||
|
project_id, serialnumber)
|
||
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
|
||
|
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
||
|
$form->{"description_$i"}, $form->{"qty_$i"} * -1,
|
||
|
$baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated,
|
||
|
$form->{"unit_$i"}, conv_date($form->{deliverydate}),
|
||
|
conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"});
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
|
||
|
$project_id = conv_i($form->{"globalproject_id"});
|
||
|
$h_item_unit->finish();
|
||
|
|
||
|
my $project_id = conv_i($form->{"globalproject_id"});
|
||
|
|
||
|
$form->{datepaid} = $form->{invdate};
|
||
|
|
||
| ... | ... | |
|
|
||
|
# total payments
|
||
|
for my $i (1 .. $form->{paidaccounts}) {
|
||
|
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
|
||
|
$form->{paid} += $form->{"paid_$i"};
|
||
|
$form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
|
||
|
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
|
||
|
$form->{paid} += $form->{"paid_$i"};
|
||
|
$form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
|
||
|
}
|
||
|
|
||
|
my ($tax, $paiddiff) = (0, 0);
|
||
| ... | ... | |
|
$netamount = $amount;
|
||
|
|
||
|
foreach $item (split / /, $form->{taxaccounts}) {
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
|
||
|
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
||
|
$tax += $amount;
|
||
|
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
||
|
$tax += $amount;
|
||
|
$netamount -= $amount;
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
|
||
|
$paiddiff = $amount - $netamount * $form->{exchangerate};
|
||
|
$netamount = $amount;
|
||
|
|
||
|
foreach my $item (split / /, $form->{taxaccounts}) {
|
||
|
$form->{amount}{ $form->{id} }{$item} =
|
||
|
$form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
|
||
|
$amount =
|
||
|
$form->round_amount(
|
||
|
$form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
|
||
|
2);
|
||
|
$paiddiff +=
|
||
|
$amount - $form->{amount}{ $form->{id} }{$item} *
|
||
|
$form->{exchangerate} * -1;
|
||
|
$form->{amount}{ $form->{id} }{$item} =
|
||
|
$form->round_amount($amount * -1, 2);
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
||
|
$tax += $amount;
|
||
|
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
|
||
|
$amount = $form->round_amount( $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1, 2);
|
||
|
$paiddiff += $amount - $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1;
|
||
|
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount * -1, 2);
|
||
|
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
||
|
$tax += $amount;
|
||
|
}
|
||
|
}
|
||
|
|
||
|
$form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
|
||
|
|
||
|
if ($form->{paid} != 0) {
|
||
|
$form->{paid} =
|
||
|
$form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
|
||
|
2);
|
||
|
$form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2);
|
||
|
}
|
||
|
|
||
|
# update exchangerate
|
||
|
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
||
|
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
|
||
|
$form->{exchangerate});
|
||
|
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate});
|
||
|
}
|
||
|
|
||
|
# record acc_trans transactions
|
||
|
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, taxkey, project_id)
|
||
|
VALUES ($trans_id, (SELECT c.id FROM chart c
|
||
|
WHERE c.accno = '$accno'),
|
||
|
$form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|;
|
||
|
do_query($form, $dbh, $query, $project_id);
|
||
|
}
|
||
|
$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
|
||
|
next unless $form->{amount}{$trans_id}{$accno};
|
||
|
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
|
||
|
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
|
||
|
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
|
||
|
conv_date($form->{invdate}), $accno, $project_id);
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
}
|
||
|
|
||
|
# deduct payment differences from paiddiff
|
||
|
for my $i (1 .. $form->{paidaccounts}) {
|
||
|
if ($form->{"paid_$i"} != 0) {
|
||
|
$amount =
|
||
|
$form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
|
||
|
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
|
||
|
$paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
|
||
|
}
|
||
|
}
|
||
|
|
||
|
# force AP entry if 0
|
||
|
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
|
||
|
if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
|
||
|
if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0) {
|
||
|
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid};
|
||
|
}
|
||
|
|
||
|
# record payments and offsetting AP
|
||
|
for my $i (1 .. $form->{paidaccounts}) {
|
||
|
next if ($form->{"paid_$i"} == 0);
|
||
|
|
||
|
my ($accno) = split /--/, $form->{"AP_paid_$i"};
|
||
|
$form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
|
||
|
$form->{datepaid} = $form->{"datepaid_$i"};
|
||
|
|
||
|
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2) * -1;
|
||
|
|
||
|
# record AP
|
||
|
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
|
||
|
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
|
||
|
@values = (conv_i($form->{id}), $form->{AP}, $amount,
|
||
|
$form->{"datepaid_$i"}, $form->{AP}, $project_id);
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
|
||
|
if ($form->{"paid_$i"} != 0) {
|
||
|
my ($accno) = split /--/, $form->{"AP_paid_$i"};
|
||
|
$form->{"datepaid_$i"} = $form->{invdate}
|
||
|
unless ($form->{"datepaid_$i"});
|
||
|
$form->{datepaid} = $form->{"datepaid_$i"};
|
||
|
|
||
|
$amount = (
|
||
|
$form->round_amount(
|
||
|
$form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
|
||
|
)
|
||
|
) * -1;
|
||
|
|
||
|
# record AP
|
||
|
|
||
|
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
|
||
|
transdate, taxkey, project_id)
|
||
|
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
||
|
WHERE c.accno = ?),
|
||
|
$amount, '$form->{"datepaid_$i"}',
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
|
||
|
do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
|
||
|
}
|
||
|
|
||
|
# record payment
|
||
|
# record payment
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
|
||
|
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
|
||
|
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
||
|
$form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
$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"});
|
||
|
}
|
||
|
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
|
||
|
source, memo, taxkey, project_id)
|
||
|
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
||
|
WHERE c.accno = ?),
|
||
|
$form->{"paid_$i"}, '$form->{"datepaid_$i"}',
|
||
|
'$form->{"source_$i"}', '$form->{"memo_$i"}',
|
||
|
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
|
||
|
do_query($form, $dbh, $query, $accno, $accno, $project_id);
|
||
|
# exchangerate difference
|
||
|
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
||
|
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
|
||
|
|
||
|
$exchangerate = 0;
|
||
|
# gain/loss
|
||
|
$amount =
|
||
|
($form->{"paid_$i"} * $form->{exchangerate}) -
|
||
|
($form->{"paid_$i"} * $form->{"exchangerate_$i"});
|
||
|
if ($amount > 0) {
|
||
|
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
|
||
|
|
||
|
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"});
|
||
|
}
|
||
|
|
||
|
# exchangerate difference
|
||
|
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
||
|
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
|
||
|
|
||
|
# gain/loss
|
||
|
$amount =
|
||
|
($form->{"paid_$i"} * $form->{exchangerate}) -
|
||
|
($form->{"paid_$i"} * $form->{"exchangerate_$i"});
|
||
|
if ($amount > 0) {
|
||
|
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
|
||
|
$amount;
|
||
|
} else {
|
||
|
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
|
||
|
$amount;
|
||
|
}
|
||
|
} else {
|
||
|
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
|
||
|
}
|
||
|
|
||
|
$paiddiff = 0;
|
||
|
$paiddiff = 0;
|
||
|
|
||
|
# update exchange rate
|
||
|
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
||
|
$form->update_exchangerate($dbh, $form->{currency},
|
||
|
$form->{"datepaid_$i"},
|
||
|
0, $form->{"exchangerate_$i"});
|
||
|
}
|
||
|
# update exchange rate
|
||
|
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
||
|
$form->update_exchangerate($dbh, $form->{currency},
|
||
|
$form->{"datepaid_$i"},
|
||
|
0, $form->{"exchangerate_$i"});
|
||
|
}
|
||
|
}
|
||
|
|
||
|
# record exchange rate differences and gains/losses
|
||
|
foreach my $accno (keys %{ $form->{fx} }) {
|
||
|
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
|
||
|
if (
|
||
|
($form->{fx}{$accno}{$transdate} =
|
||
|
$form->round_amount($form->{fx}{$accno}{$transdate}, 2)
|
||
|
) != 0
|
||
|
) {
|
||
|
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
|
||
|
transdate, cleared, fx_transaction, taxkey, project_id)
|
||
|
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
||
|
WHERE c.accno = '$accno'),
|
||
|
$form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', 0, ?)|;
|
||
|
do_query($form, $dbh, $query, $project_id);
|
||
|
}
|
||
|
$form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
|
||
|
next if ($form->{fx}{$accno}{$transdate} == 0);
|
||
|
|
||
|
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
|
||
|
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?)|;
|
||
|
@values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id);
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
}
|
||
|
|
||
|
$amount = $netamount + $tax;
|
||
|
|
||
|
# set values which could be empty
|
||
|
$form->{taxincluded} *= 1;
|
||
|
my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
|
||
|
my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
|
||
|
|
||
|
($null, $form->{department_id}) = split(/--/, $form->{department});
|
||
|
$form->{department_id} *= 1;
|
||
|
$form->{payment_id} *= 1;
|
||
|
$form->{language_id} *= 1;
|
||
|
$form->{taxzone_id} *= 1;
|
||
|
$form->{storno} *= 1;
|
||
|
my $taxzone_id = $form->{taxzone_id} * 1;
|
||
|
$form->{department_id} = (split /--/, $form->{department})[1];
|
||
|
$form->{invnumber} = $form->{id} unless $form->{invnumber};
|
||
|
|
||
|
|
||
|
$form->{invnumber} = $form->{id} unless $form->{invnumber};
|
||
|
$taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
|
||
|
|
||
|
# save AP record
|
||
|
$query = qq|UPDATE ap set
|
||
|
invnumber = '$form->{invnumber}',
|
||
|
ordnumber = '$form->{ordnumber}',
|
||
|
quonumber = '$form->{quonumber}',
|
||
|
transdate = '$form->{invdate}',
|
||
|
orddate = | . conv_dateq($form->{"orddate"}) . qq|,
|
||
|
quodate = | . conv_dateq($form->{"quodate"}) . qq|,
|
||
|
vendor_id = $form->{vendor_id},
|
||
|
amount = $amount,
|
||
|
netamount = $netamount,
|
||
|
paid = $form->{paid},
|
||
|
datepaid = $datepaid,
|
||
|
duedate = $duedate,
|
||
|
invoice = '1',
|
||
|
taxzone_id = '$form->{taxzone_id}',
|
||
|
taxincluded = '$form->{taxincluded}',
|
||
|
notes = '$form->{notes}',
|
||
|
intnotes = '$form->{intnotes}',
|
||
|
curr = '$form->{currency}',
|
||
|
department_id = $form->{department_id},
|
||
|
storno = '$form->{storno}',
|
||
|
globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
|
||
|
cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
|
||
|
WHERE id = $form->{id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq|UPDATE ap SET
|
||
|
invnumber = ?,
|
||
|
ordnumber = ?,
|
||
|
quonumber = ?,
|
||
|
transdate = ?,
|
||
|
orddate = ?,
|
||
|
quodate = ?,
|
||
|
vendor_id = ?,
|
||
|
amount = ?,
|
||
|
netamount = ?,
|
||
|
paid = ?,
|
||
|
datepaid = ?,
|
||
|
duedate = ?,
|
||
|
invoice = '1',
|
||
|
taxzone_id = ?,
|
||
|
taxincluded = ?,
|
||
|
notes = ?,
|
||
|
intnotes = ?,
|
||
|
curr = ?,
|
||
|
department_id = ?,
|
||
|
storno = ?,
|
||
|
globalproject_id = ?,
|
||
|
cp_id = ?,
|
||
|
employee_id = ?
|
||
|
WHERE id = ?|;
|
||
|
@values = ($form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
|
||
|
conv_date($form->{invdate}), conv_date($form->{orddate}), conv_date($form->{quodate}),
|
||
|
conv_i($form->{vendor_id}), $amount, $netamount, $form->{paid},
|
||
|
$form->{paid} ? conv_date($form->{datepaid}) : undef,
|
||
|
conv_date($form->{duedate}), $taxzone_id,
|
||
|
$form->{taxincluded} ? 't' : 'f',
|
||
|
$form->{notes}, $form->{intnotes}, $form->{currency}, conv_i($form->{department_id}),
|
||
|
$form->{storno} ? 't' : 'f',
|
||
|
conv_i($form->{globalproject_id}), conv_i($form->{cp_id}),
|
||
|
conv_i($form->{employee_id}),
|
||
|
conv_i($form->{id}));
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
if ($form->{storno}) {
|
||
|
$query = qq| UPDATE ap SET paid = paid+amount WHERE id = $form->{storno_id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq| UPDATE ap SET storno = '$form->{storno}' WHERE id = $form->{storno_id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq? UPDATE ap SET intnotes = 'Rechnung storniert am $form->{invdate} ' || intnotes WHERE id = $form->{storno_id}?;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
|
||
|
$query = qq| UPDATE ap SET paid = amount WHERE id = $form->{id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq|UPDATE ap SET paid = paid + amount WHERE id = ?|;
|
||
|
do_query($form, $dbh, $query, conv_i($form->{storno_id}));
|
||
|
|
||
|
$query = qq|UPDATE ap SET storno = 't' WHERE id = ?|;
|
||
|
do_query($form, $dbh, $query, conv_i($form->{storno_id}));
|
||
|
|
||
|
$query = qq!UPDATE ap SET intnotes = ? || intnotes WHERE id = ?!;
|
||
|
do_query($form, $dbh, $query, 'Rechnung storniert am $form->{invdate} ', conv_i($form->{storno_id}));
|
||
|
|
||
|
$query = qq|UPDATE ap SET paid = amount WHERE id = ?|;
|
||
|
do_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
}
|
||
|
|
||
|
|
||
| ... | ... | |
|
$form->add_shipto($dbh, $form->{id}, "AP");
|
||
|
|
||
|
# delete zero entries
|
||
|
$query = qq|DELETE FROM acc_trans
|
||
|
WHERE amount = 0|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE amount = 0|);
|
||
|
|
||
|
Common::webdav_folder($form) if ($main::webdav);
|
||
|
|
||
| ... | ... | |
|
my ($dbh, $form) = @_;
|
||
|
|
||
|
# reverse inventory items
|
||
|
my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
|
||
|
i.qty, i.allocated, i.sellprice
|
||
|
FROM invoice i, parts p
|
||
|
WHERE i.parts_id = p.id
|
||
|
AND i.trans_id = $form->{id}|;
|
||
|
my $sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
my $query =
|
||
|
qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id, i.qty, i.allocated, i.sellprice
|
||
|
FROM invoice i, parts p
|
||
|
WHERE (i.parts_id = p.id)
|
||
|
AND (i.trans_id = ?)|;
|
||
|
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
|
||
|
my $netamount = 0;
|
||
|
|
||
|
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
$netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
|
||
|
|
||
|
if ($ref->{inventory_accno_id}) {
|
||
|
|
||
|
# update onhand
|
||
|
$form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
|
||
|
$ref->{qty});
|
||
|
|
||
|
# if $ref->{allocated} > 0 than we sold that many items
|
||
|
if ($ref->{allocated} > 0) {
|
||
|
|
||
|
# get references for sold items
|
||
|
$query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
|
||
|
FROM invoice i, ar a
|
||
|
WHERE i.parts_id = $ref->{parts_id}
|
||
|
AND i.allocated < 0
|
||
|
AND i.trans_id = a.id
|
||
|
ORDER BY transdate DESC|;
|
||
|
my $sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
|
||
|
while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
my $qty = $ref->{allocated};
|
||
|
if (($ref->{allocated} + $pthref->{allocated}) > 0) {
|
||
|
$qty = $pthref->{allocated} * -1;
|
||
|
}
|
||
|
next unless $ref->{inventory_accno_id};
|
||
|
|
||
|
my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
|
||
|
# update onhand
|
||
|
$form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
|
||
|
|
||
|
#adjust allocated
|
||
|
$form->update_balance($dbh, "invoice", "allocated",
|
||
|
qq|id = $pthref->{id}|, $qty);
|
||
|
# if $ref->{allocated} > 0 than we sold that many items
|
||
|
next if ($ref->{allocated} <= 0);
|
||
|
|
||
|
$form->update_balance(
|
||
|
$dbh,
|
||
|
"acc_trans",
|
||
|
"amount",
|
||
|
qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
|
||
|
$amount);
|
||
|
# get references for sold items
|
||
|
$query =
|
||
|
qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
|
||
|
FROM invoice i, ar a
|
||
|
WHERE (i.parts_id = ?)
|
||
|
AND (i.allocated < 0)
|
||
|
AND (i.trans_id = a.id)
|
||
|
ORDER BY transdate DESC|;
|
||
|
my $sth2 = prepare_execute_query($form, $dbh, $query, $ref->{parts_id});
|
||
|
|
||
|
$form->update_balance(
|
||
|
$dbh,
|
||
|
"acc_trans",
|
||
|
"amount",
|
||
|
qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
|
||
|
$amount * -1);
|
||
|
|
||
|
last if (($ref->{allocated} -= $qty) <= 0);
|
||
|
while (my $pthref = $sth2->fetchrow_hashref(NAME_lc)) {
|
||
|
my $qty = $ref->{allocated};
|
||
|
if (($ref->{allocated} + $pthref->{allocated}) > 0) {
|
||
|
$qty = $pthref->{allocated} * -1;
|
||
|
}
|
||
|
$sth->finish;
|
||
|
|
||
|
my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
|
||
|
|
||
|
#adjust allocated
|
||
|
$form->update_balance($dbh, "invoice", "allocated", qq|id = $pthref->{id}|, $qty);
|
||
|
|
||
|
$form->update_balance($dbh, "acc_trans", "amount",
|
||
|
qq| (trans_id = $pthref->{trans_id})
|
||
|
AND (chart_id = $ref->{expense_accno_id})
|
||
|
AND (transdate = '$pthref->{transdate}')|,
|
||
|
$amount);
|
||
|
|
||
|
$form->update_balance($dbh, "acc_trans", "amount",
|
||
|
qq| (trans_id = $pthref->{trans_id})
|
||
|
AND (chart_id = $ref->{inventory_accno_id})
|
||
|
AND (transdate = '$pthref->{transdate}')|,
|
||
|
$amount * -1);
|
||
|
|
||
|
last if (($ref->{allocated} -= $qty) <= 0);
|
||
|
}
|
||
|
}
|
||
|
$sth2->finish();
|
||
|
}
|
||
|
$sth->finish;
|
||
|
$sth->finish();
|
||
|
|
||
|
my $id = conv_i($form->{id});
|
||
|
|
||
|
# delete acc_trans
|
||
|
$query = qq|DELETE FROM acc_trans
|
||
|
WHERE trans_id = $form->{id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
|
||
|
do_query($form, $dbh, $query, $id);
|
||
|
|
||
|
# delete invoice entries
|
||
|
$query = qq|DELETE FROM invoice
|
||
|
WHERE trans_id = $form->{id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq|DELETE FROM invoice WHERE trans_id = ?|;
|
||
|
do_query($form, $dbh, $query, $id);
|
||
|
|
||
|
$query = qq|DELETE FROM shipto
|
||
|
WHERE trans_id = $form->{id} AND module = 'AP'|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
$query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
|
||
|
do_query($form, $dbh, $query, $id);
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
}
|
||
| ... | ... | |
|
&reverse_invoice($dbh, $form);
|
||
|
|
||
|
# delete zero entries
|
||
|
my $query = qq|DELETE FROM acc_trans
|
||
|
WHERE amount = 0|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
my $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
|
||
|
do_query($form, $dbh, $query);
|
||
|
|
||
|
# delete AP record
|
||
|
my $query = qq|DELETE FROM ap
|
||
|
WHERE id = $form->{id}|;
|
||
|
$dbh->do($query) || $form->dberror($query);
|
||
|
my $query = qq|DELETE FROM ap WHERE id = $form->{id}|;
|
||
|
do_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
|
||
|
my $rc = $dbh->commit;
|
||
|
$dbh->disconnect;
|
||
| ... | ... | |
|
my ($self, $myconfig, $form) = @_;
|
||
|
|
||
|
# connect to database
|
||
|
my $dbh = $form->dbconnect_noauto($myconfig);
|
||
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
|
||
|
my $query;
|
||
|
my ($query, $sth, $ref, $q_invdate);
|
||
|
|
||
|
if ($form->{id}) {
|
||
|
$q_invdate = ", current_date AS invdate" unless $form->{id};
|
||
|
|
||
|
# get default accounts and last invoice number
|
||
|
$query = qq|SELECT (SELECT c.accno FROM chart c
|
||
|
WHERE d.inventory_accno_id = c.id) AS inventory_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.income_accno_id = c.id) AS income_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.expense_accno_id = c.id) AS expense_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
|
||
|
d.curr AS currencies
|
||
|
FROM defaults d|;
|
||
|
} else {
|
||
|
$query = qq|SELECT (SELECT c.accno FROM chart c
|
||
|
WHERE d.inventory_accno_id = c.id) AS inventory_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.income_accno_id = c.id) AS income_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.expense_accno_id = c.id) AS expense_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
|
||
|
(SELECT c.accno FROM chart c
|
||
|
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
|
||
|
d.curr AS currencies,
|
||
|
current_date AS invdate
|
||
|
FROM defaults d|;
|
||
|
}
|
||
|
my $sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
# get default accounts and last invoice number
|
||
|
|
||
|
my $ref = $sth->fetchrow_hashref(NAME_lc);
|
||
|
$query=
|
||
|
qq|SELECT
|
||
|
(SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
|
||
|
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
|
||
|
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
|
||
|
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
|
||
|
(SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
|
||
|
d.curr AS currencies
|
||
|
$q_invdate
|
||
|
FROM defaults d|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query);
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
$sth->finish;
|
||
|
|
||
|
if ($form->{id}) {
|
||
|
if (!$form->{id}) {
|
||
|
$dbh->disconnect();
|
||
|
$main::lxdebug->leave_sub();
|
||
|
|
||
|
# retrieve invoice
|
||
|
$query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
|
||
|
a.orddate, a.quodate, a.globalproject_id,
|
||
|
a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
|
||
|
a.intnotes, a.curr AS currency
|
||
|
FROM ap a
|
||
|
WHERE a.id = $form->{id}|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
|
||
|
$ref = $sth->fetchrow_hashref(NAME_lc);
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
$sth->finish;
|
||
|
return;
|
||
|
}
|
||
|
|
||
|
$form->{exchangerate} =
|
||
|
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
|
||
|
"sell");
|
||
|
# retrieve invoice
|
||
|
$query =
|
||
|
qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
|
||
|
orddate, quodate, globalproject_id,
|
||
|
ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
|
||
|
intnotes, curr AS currency
|
||
|
FROM ap
|
||
|
WHERE id = ?|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
|
||
|
# get shipto
|
||
|
$query = qq|SELECT s.* FROM shipto s
|
||
|
WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
|
||
|
|
||
|
$ref = $sth->fetchrow_hashref(NAME_lc);
|
||
|
delete($ref->{id});
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
$sth->finish;
|
||
|
# get shipto
|
||
|
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
delete $ref->{id};
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
|
||
|
my $transdate =
|
||
|
$form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
|
||
|
my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
|
||
|
my $taxzone_id = $form->{taxzone_id} * 1;
|
||
|
|
||
|
$taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
|
||
|
|
||
|
# retrieve individual items
|
||
|
$query =
|
||
|
qq|SELECT
|
||
|
c1.accno AS inventory_accno,
|
||
|
c1.new_chart_id AS inventory_new_chart,
|
||
|
date($transdate) - c1.valid_from AS inventory_valid,
|
||
|
|
||
|
c2.accno AS income_accno,
|
||
|
c2.new_chart_id AS income_new_chart,
|
||
|
date($transdate) - c2.valid_from AS income_valid,
|
||
|
|
||
|
c3.accno AS expense_accno,
|
||
|
c3.new_chart_id AS expense_new_chart,
|
||
|
date($transdate) - c3.valid_from AS expense_valid,
|
||
|
|
||
|
i.description, i.qty, i.fxsellprice AS sellprice,
|
||
|
i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
|
||
|
|
||
|
p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin,
|
||
|
pr.projectnumber,
|
||
|
pg.partsgroup
|
||
|
|
||
|
FROM invoice i
|
||
|
JOIN parts p ON (i.parts_id = p.id)
|
||
|
LEFT JOIN chart c1 ON
|
||
|
((SELECT inventory_accno_id
|
||
|
FROM buchungsgruppen
|
||
|
WHERE id = p.buchungsgruppen_id) = c1.id)
|
||
|
LEFT JOIN chart c2 ON
|
||
|
((SELECT income_accno_id_${taxzone_id}
|
||
|
FROM buchungsgruppen
|
||
|
WHERE id = p.buchungsgruppen_id) = c2.id)
|
||
|
LEFT JOIN chart c3 ON
|
||
|
((SELECT expense_accno_id_${taxzone_id}
|
||
|
FROM buchungsgruppen
|
||
|
WHERE id = p.buchungsgruppen_id) = c3.id)
|
||
|
LEFT JOIN project pr ON (i.project_id = pr.id)
|
||
|
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
|
||
|
|
||
|
WHERE i.trans_id = ?
|
||
|
|
||
|
ORDER BY i.id|;
|
||
|
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
|
||
|
|
||
|
if(!$form->{taxzone_id}) {
|
||
|
$form->{taxzone_id} = 0;
|
||
|
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
if (!$ref->{"part_inventory_accno_id"}) {
|
||
|
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
|
||
|
}
|
||
|
# retrieve individual items
|
||
|
$query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
|
||
|
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
|
||
|
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
|
||
|
p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
|
||
|
i.parts_id AS id, i.unit, p.bin, i.deliverydate,
|
||
|
pr.projectnumber,
|
||
|
i.project_id, i.serialnumber,
|
||
|
pg.partsgroup
|
||
|
FROM invoice i
|
||
|
JOIN parts p ON (i.parts_id = p.id)
|
||
|
LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
|
||
|
LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
|
||
|
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
|
||
|
LEFT JOIN project pr ON (i.project_id = pr.id)
|
||
|
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
|
||
|
WHERE i.trans_id = $form->{id}
|
||
|
ORDER BY i.id|;
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute || $form->dberror($query);
|
||
|
|
||
|
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
||
|
if (!$ref->{"part_inventory_accno_id"}) {
|
||
|
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
|
||
|
}
|
||
|
delete($ref->{"part_inventory_accno_id"});
|
||
|
|
||
|
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
|
||
|
my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
|
||
|
my $stw = $dbh->prepare($query);
|
||
|
$stw->execute || $form->dberror($query);
|
||
|
($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
|
||
|
$stw->finish;
|
||
|
delete($ref->{"part_inventory_accno_id"});
|
||
|
|
||
|
foreach my $type (qw(inventory income expense)) {
|
||
|
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
|
||
|
my $query =
|
||
|
qq|SELECT accno, new_chart_id, date($transdate) - valid_from
|
||
|
FROM chart
|
||
|
WHERE id = ?|;
|
||
|
($ref->{"${type}_accno"},
|
||
|
$ref->{"${type}_new_chart"},
|
||
|
$ref->{"${type}_valid"})
|
||
|
= selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
|
||
|
}
|
||
|
|
||
|
while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
|
||
|
my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
|
||
|
my $stw = $dbh->prepare($query);
|
||
|
$stw->execute || $form->dberror($query);
|
||
|
($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
|
||
|
$stw->finish;
|
||
|
}
|
||
|
|
||
|
while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
|
||
|
my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
|
||
|
my $stw = $dbh->prepare($query);
|
||
|
$stw->execute || $form->dberror($query);
|
||
|
($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
|
||
|
$stw->finish;
|
||
|
}
|
||
|
|
||
|
# get tax rates and description
|
||
|
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
||
|
$query =
|
||
|
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
|
||
|
FROM tax t
|
||
|
LEFT JOIN chart c ON (c.id = t.chart_id)
|
||
|
WHERE t.id in
|
||
|
(SELECT tk.tax_id
|
||
|
FROM taxkeys tk
|
||
|
WHERE tk.chart_id =
|
||
|
(SELECT id
|
||
|
FROM chart
|
||
|
WHERE accno = ?)
|
||
|
AND (startdate <= $transdate)
|
||
|
ORDER BY startdate DESC
|
||
|
LIMIT 1)
|
||
|
ORDER BY c.accno|;
|
||
|
my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
|
||
|
$ref->{taxaccounts} = "";
|
||
|
|
||
|
my $i = 0;
|
||
|
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
||
|
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
||
|
$i++;
|
||
|
$ptr->{accno} = $i;
|
||
|
}
|
||
|
|
||
|
# get tax rates and description
|
||
|
$accno_id =
|
||
|
($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
||
|
$query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
|
||
|
FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
|
||
|
WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
|
||
|
ORDER BY c.accno|;
|
||
|
$stw = $dbh->prepare($query);
|
||
|
$stw->execute || $form->dberror($query);
|
||
|
$ref->{taxaccounts} = "";
|
||
|
my $i = 0;
|
||
|
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
||
|
|
||
|
# if ($customertax{$ref->{accno}}) {
|
||
|
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
||
|
$i++;
|
||
|
$ptr->{accno} = $i;
|
||
|
}
|
||
|
$ref->{taxaccounts} .= "$ptr->{accno} ";
|
||
|
if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
|
||
|
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
|
||
|
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
|
||
|
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
|
||
|
$form->{taxaccounts} .= "$ptr->{accno} ";
|
||
|
}
|
||
|
$ref->{taxaccounts} .= "$ptr->{accno} ";
|
||
|
|
||
|
if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
|
||
|
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
|
||
|
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
|
||
|
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
|
||
|
$form->{taxaccounts} .= "$ptr->{accno} ";
|
||
|
}
|
||
|
|
||
|
chop $ref->{taxaccounts};
|
||
|
push @{ $form->{invoice_details} }, $ref;
|
||
|
$stw->finish;
|
||
|
}
|
||
|
$sth->finish;
|
||
|
|
||
|
Common::webdav_folder($form) if ($main::webdav);
|
||
|
chop $ref->{taxaccounts};
|
||
|
push @{ $form->{invoice_details} }, $ref;
|
||
|
$stw->finish();
|
||
|
}
|
||
|
$sth->finish();
|
||
|
|
||
|
my $rc = $dbh->commit;
|
||
|
$dbh->disconnect;
|
||
|
Common::webdav_folder($form) if ($main::webdav);
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
$dbh->disconnect();
|
||
|
|
||
|
return $rc;
|
||
|
$main::lxdebug->leave_sub();
|
||
|
}
|
||
|
|
||
|
sub get_vendor {
|
||
| ... | ... | |
|
my $dateformat = $myconfig->{dateformat};
|
||
|
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
|
||
|
|
||
|
my $vid = conv_i($form->{vendor_id});
|
||
|
|
||
|
my $duedate =
|
||
|
($form->{invdate})
|
||
|
? "to_date('$form->{invdate}', '$dateformat')"
|
||
|
? "to_date(" . $dbh->quote($form->{invdate}) . ", '$dateformat')"
|
||
|
: "current_date";
|
||
|
|
||
|
$form->{vendor_id} *= 1;
|
||
|
|
||
|
# get vendor
|
||
|
my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes,
|
||
|
v.email, v.cc, v.bcc, v.language_id, v.payment_id,
|
||
|
v.street, v.zipcode, v.city, v.country, v.taxzone_id,
|
||
|
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
|
||
|
b.description AS business
|
||
|
FROM vendor v
|
||
|
LEFT JOIN business b ON b.id = v.business_id
|
||
|
LEFT JOIN payment_terms pt ON v.payment_id = pt.id
|
||
|
WHERE v.id = ?|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
||
|
my $query =
|
||
|
qq|SELECT
|
||
|
v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes,
|
||
|
v.email, v.cc, v.bcc, v.language_id, v.payment_id,
|
||
|
v.street, v.zipcode, v.city, v.country, v.taxzone_id,
|
||
|
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
|
||
|
b.description AS business
|
||
|
FROM vendor v
|
||
|
LEFT JOIN business b ON (b.id = v.business_id)
|
||
|
LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
|
||
|
WHERE v.id = ?|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
|
||
|
$form->{creditremaining} = $form->{creditlimit};
|
||
|
$query = qq|SELECT SUM(a.amount - a.paid) FROM ap a WHERE a.vendor_id = ?|;
|
||
|
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $form->{vendor_id});
|
||
|
|
||
|
$query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|;
|
||
|
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $vid);
|
||
|
$form->{creditremaining} -= $unpaid_invoices;
|
||
|
|
||
|
$query = qq|SELECT o.amount,
|
||
|
(SELECT e.sell FROM exchangerate e
|
||
|
WHERE e.curr = o.curr AND e.transdate = o.transdate) AS exch
|
||
|
FROM oe o WHERE o.vendor_id = ?
|
||
|
AND o.quotation = '0' AND o.closed = '0'|;
|
||
|
my $sth = prepare_execute_query($form, $dbh, $query, $form->{vendor_id});
|
||
|
(SELECT e.sell
|
||
|
FROM exchangerate e
|
||
|
WHERE (e.curr = o.curr)
|
||
|
AND (e.transdate = o.transdate)) AS exch
|
||
|
FROM oe o
|
||
|
WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;
|
||
|
my $sth = prepare_execute_query($form, $dbh, $query, $vid);
|
||
|
while (my ($amount, $exch) = $sth->fetchrow_array()) {
|
||
|
$exch = 1 unless $exch;
|
||
|
$form->{creditremaining} -= $amount * $exch;
|
||
| ... | ... | |
|
|
||
|
# get shipto if we do not convert an order or invoice
|
||
|
if (!$form->{shipto}) {
|
||
|
map { delete $form->{$_} }
|
||
|
qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
|
||
|
delete @{$form}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
|
||
|
|
||
|
$query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module= 'CT'|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
||
|
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module= 'CT')|;
|
||
|
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
|
||
|
@{$form}{keys %$ref} = @{$ref}{keys %$ref};
|
||
|
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
||
|
}
|
||
|
|
||
|
# get taxes for vendor
|
||
|
$query = qq|SELECT c.accno FROM chart c
|
||
|
JOIN vendortax v ON (v.chart_id = c.id)
|
||
|
WHERE v.vendor_id = ?|;
|
||
|
my $vendortax = ();
|
||
|
$ref = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
||
|
map { $vendortax{ $_->{accno} } = 1 } @$ref;
|
||
Auch abrufbar als: Unified diff
Umstellung der IR.pm auf die Verwendung von parametrisierten und richtig gequoteten Queries zur Vermeidung von SQL injections. Zusätzlich Kosmetik wie Leerzeichen am Zeilenende oder Umformatierungen anderer Codeteile.