From e840d786480e62252218d3f3467c116ba98e3a8b Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Wed, 9 May 2007 14:12:10 +0000 Subject: [PATCH] =?utf8?q?Umstellung=20der=20IR.pm=20auf=20die=20Verwendun?= =?utf8?q?g=20von=20parametrisierten=20und=20richtig=20gequoteten=20Querie?= =?utf8?q?s=20zur=20Vermeidung=20von=20SQL=20injections.=20Zus=C3=A4tzlich?= =?utf8?q?=20Kosmetik=20wie=20Leerzeichen=20am=20Zeilenende=20oder=20Umfor?= =?utf8?q?matierungen=20anderer=20Codeteile.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/IR.pm | 1646 ++++++++++++++++++++++++++---------------------------- 1 file changed, 784 insertions(+), 862 deletions(-) diff --git a/SL/IR.pm b/SL/IR.pm index 8bc228b64..b6840a54c 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -46,13 +46,11 @@ sub post_invoice { # 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); @@ -61,30 +59,11 @@ sub post_invoice { &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 { @@ -100,9 +79,15 @@ sub post_invoice { $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; } @@ -110,278 +95,229 @@ sub post_invoice { 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}; @@ -391,9 +327,9 @@ sub post_invoice { # 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); @@ -408,10 +344,11 @@ sub post_invoice { $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; } @@ -430,222 +367,195 @@ sub post_invoice { $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})); } @@ -655,9 +565,7 @@ sub post_invoice { $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); @@ -675,85 +583,77 @@ sub reverse_invoice { 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(); } @@ -769,14 +669,12 @@ sub delete_invoice { &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; @@ -792,178 +690,170 @@ sub retrieve_invoice { 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 { @@ -977,37 +867,42 @@ 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; @@ -1016,34 +911,25 @@ sub get_vendor { # 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; - if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) { - # setup last accounts used - $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category - FROM chart c - JOIN acc_trans ac ON (ac.chart_id = c.id) - JOIN ap a ON (a.id = ac.trans_id) - WHERE a.vendor_id = ? - AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%') - AND a.id IN (SELECT max(a2.id) FROM ap a2 - WHERE a2.vendor_id = ?)|; - $refs = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id}, $form->{vendor_id}); + $query = + qq|SELECT c.id, c.accno, c.description, c.link, c.category + FROM chart c + JOIN acc_trans ac ON (ac.chart_id = c.id) + JOIN ap a ON (a.id = ac.trans_id) + WHERE (a.vendor_id = ?) + AND (NOT ((c.link LIKE '%_tax%') OR (c.link LIKE '%_paid%'))) + AND (a.id IN (SELECT max(a2.id) FROM ap a2 WHERE a2.vendor_id = ?))|; + my $refs = selectall_hashref_query($form, $dbh, $query, $vid, $vid); my $i = 0; for $ref (@$refs) { @@ -1052,16 +938,17 @@ sub get_vendor { if ($form->{initial_transdate}) { my $tax_query = qq|SELECT tk.tax_id, t.rate FROM taxkeys tk - LEFT JOIN tax t ON tk.tax_id = t.id - WHERE tk.chart_id = ? AND startdate <= ? - ORDER BY tk.startdate DESC LIMIT 1|; - my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, - $form->{initial_transdate}); + LEFT JOIN tax t ON (tk.tax_id = t.id) + WHERE (tk.chart_id = ?) AND (startdate <= ?) + ORDER BY tk.startdate DESC + LIMIT 1|; + my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $form->{initial_transdate}); $form->{"taxchart_$i"} = "${tax_id}--${rate}"; } $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id"; } + if ($ref->{category} eq 'L') { $form->{APselected} = $form->{AP_1} = $ref->{accno}; } @@ -1069,7 +956,7 @@ sub get_vendor { $form->{rowcount} = $i if ($i && !$form->{type}); } - $dbh->disconnect; + $dbh->disconnect(); $main::lxdebug->leave_sub(); } @@ -1086,20 +973,13 @@ sub retrieve_item { # don't include assemblies or obsolete parts my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'"; + my @values; - if ($form->{"partnumber_$i"}) { - my $partnumber = $form->like(lc $form->{"partnumber_$i"}); - $where .= " AND lower(p.partnumber) LIKE '$partnumber'"; - } - - if ($form->{"description_$i"}) { - my $description = $form->like(lc $form->{"description_$i"}); - $where .= " AND lower(p.description) LIKE '$description'"; - } - - if ($form->{"partsgroup_$i"}) { - my $partsgroup = $form->like(lc $form->{"partsgroup_$i"}); - $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'"; + foreach my $table_column (qw(p.partnumber p.description pg.partsgroup)) { + my $field = (split m{\.}, $table_column)[1]; + next unless $form->{"${field}_${i}"}; + $where .= " AND lower(${table_column}) LIKE ?"; + push @values, '%' . $form->{"${field}_${i}"} . '%'; } if ($form->{"description_$i"}) { @@ -1110,29 +990,53 @@ sub retrieve_item { my $transdate = ""; if ($form->{type} eq "invoice") { - $transdate = - $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date"; + $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date"; } else { - $transdate = - $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; + $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; } - my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS sellprice, - p.listprice, p.inventory_accno_id, - 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.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable, - pg.partsgroup, p.formel - FROM parts p - 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 partsgroup pg ON (pg.id = p.partsgroup_id) - WHERE $where|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $taxzone_id = $form->{taxzone_id} * 1; + $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id)); + my $query = + qq|SELECT + p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice, + p.unit, p.assembly, p.bin, p.onhand, p.formel, + p.notes AS partnotes, p.notes AS longdescription, p.not_discountable, + p.inventory_accno_id, + + 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, + + pg.partsgroup + + FROM parts p + 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 partsgroup pg ON (pg.id = p.partsgroup_id) + WHERE $where|; + my $sth = prepare_execute_query($form, $dbh, $query, @values); + + $form->{item_list} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn @@ -1144,14 +1048,23 @@ sub retrieve_item { delete($ref->{inventory_accno_id}); # 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); + $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; @@ -1162,25 +1075,27 @@ sub retrieve_item { $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} "; + $form->{"$ptr->{accno}_rate"} = $ptr->{rate}; + $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription}; + $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber}; + $form->{taxaccounts} .= "$ptr->{accno} "; } } - $stw->finish; + $stw->finish(); chop $ref->{taxaccounts}; push @{ $form->{item_list} }, $ref; } - $sth->finish; - $dbh->disconnect; + $sth->finish(); + $dbh->disconnect(); $main::lxdebug->leave_sub(); } @@ -1193,25 +1108,26 @@ sub vendor_details { # connect to database my $dbh = $form->dbconnect($myconfig); - # get contact id, set it if nessessary - ($null, $form->{cp_id}) = split /--/, $form->{contact}; + my @values; - $contact = ""; + # get contact id, set it if nessessary + $form->{cp_id} = (split /--/, $form->{contact})[1]; + my $contact = ""; if ($form->{cp_id}) { - $contact = "and cp.cp_id = $form->{cp_id}"; + $contact = "AND cp.cp_id = ?"; + push @values, $form->{cp_id}; } # get rest for the vendor # fax and phone and email as vendor* my $query = qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail - FROM vendor ct - LEFT JOIN contacts cp on ct.id = cp.cp_cv_id - WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); + FROM vendor ct + LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) + WHERE (ct.id = ?) $contact + ORDER BY cp.cp_id + LIMIT 1|; + my $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id}, @values); # remove id and taxincluded before copy back delete @$ref{qw(id taxincluded)}; @@ -1225,8 +1141,7 @@ sub vendor_details { map { $form->{$_} = $ref->{$_} } keys %$ref; - $sth->finish; - $dbh->disconnect; + $dbh->disconnect(); $main::lxdebug->leave_sub(); } @@ -1239,12 +1154,12 @@ sub item_links { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT c.accno, c.description, c.link - FROM chart c - WHERE c.link LIKE '%IC%' - ORDER BY c.accno|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $query = + qq|SELECT accno, description, link + FROM chart + WHERE link LIKE '%IC%' + ORDER BY accno|; + my $sth = prepare_execute_query($query, $dbh, $query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { foreach my $key (split(/:/, $ref->{link})) { @@ -1256,7 +1171,9 @@ sub item_links { } } - $sth->finish; + $sth->finish(); + $dbh->disconnect(); + $main::lxdebug->leave_sub(); } @@ -1272,131 +1189,136 @@ sub post_payment { # total payments, don't move we need it here 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"}); } $form->{exchangerate} = - $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, - "buy"); + $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); my $project_id = conv_i($form->{"globalproject_id"}); # record payments and offsetting AP for my $i (1 .. $form->{paidaccounts}) { + next if $form->{"paid_$i"} == 0; - 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"}; - - $exchangerate = 0; - if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) { - $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 AP - $amount = - $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, - 2) * -1; - - - $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|; - $dbh->do($query) || $form->dberror($query); - - $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); - - + my ($accno) = split /--/, $form->{"AP_paid_$i"}; + $form->{"datepaid_$i"} = $form->{invdate} unless $form->{"datepaid_$i"}; + $form->{datepaid} = $form->{"datepaid_$i"}; - $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c - WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|; - $dbh->do($query) || $form->dberror($query); + $exchangerate = 0; + if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) { + $form->{"exchangerate_$i"} = 1; - $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); + } else { + $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy'); + $form->{"exchangerate_$i"} = + ($exchangerate) + ? $exchangerate + : $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); + } - # 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; - } + # record AP + $amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2) * -1; + + $query = + qq|DELETE FROM acc_trans + WHERE (trans_id = ?) + AND (chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)) + AND (amount = ?) + AND (transdate = ?)|; + @values = (conv_i($form->{id}), $form->{AP}, $amount, conv_date($form->{"datepaid_$i"})); + do_query($form, $dbh, $query, @values); + + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, + (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + @values = (conv_i($form->{id}), $form->{AP}, $amount, + conv_date($form->{"datepaid_$i"}), $form->{AP}, $project_id); + do_query($form, $dbh, $query, @values); + + $query = + qq|DELETE FROM acc_trans + WHERE (trans_id = ?) + AND (chart_id=(SELECT c.id FROM chart c WHERE c.accno = ?)) + AND (amount = ?) + AND (transdate = ?) + AND (source = ?) + AND (memo = ?)|; + @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, + conv_date($form->{"datepaid_$i"}), $form->{"source_$i"}, + $form->{"memo_$i"}); + do_query($form, $dbh, $query, @values); + + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id) + VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, + (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, + conv_date($form->{"datepaid_$i"}), $form->{"source_$i"}, + $form->{"memo_$i"}, $accno, $project_id); + do_query($form, $dbh, $query, @values); + + # 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; + } - $diff = 0; + $diff = 0; - # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, - $form->{"datepaid_$i"}, - $form->{"exchangerate_$i"}, 0); - } + # update exchange rate + if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + $form->update_exchangerate($dbh, $form->{currency}, + $form->{"datepaid_$i"}, + $form->{"exchangerate_$i"}, 0); } } # 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|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c - WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|; - $dbh->do($query) || $form->dberror($query); - $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 = ?), - $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - do_query($form, $dbh, $query, $accno, $accno, $project_id); - } + $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2); + next if $form->{fx}{$accno}{$transdate} == 0; + + $query = + qq|DELETE FROM acc_trans + WHERE (trans_id = ?) + AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) + AND (amount = ?) + AND (transdate = ?) + AND (cleared = '0') + AND (fx_transaction = '1')|; + @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, $transdate); + do_query($form, $dbh, $query, @values); + + $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', + (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, + $transdate, $accno, $project_id); + do_query($form, $dbh, $query, @values); } } - my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL"; - # save AP record - my $query = qq|UPDATE ap set - paid = $form->{paid}, - datepaid = $datepaid - WHERE id=$form->{id}|; - - $dbh->do($query) || $form->dberror($query); + my $datepaid = $form->{paid} ? qq|'$form->{datepaid}'| : "NULL"; - my $rc = $dbh->commit; - $dbh->disconnect; + # save AP record + my $query = qq|UPDATE ap + SET paid = ?, datepaid = ? + WHERE id = ?|; + @values = ($form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); + do_query($form, $dbh, $query, @values); + + my $rc = $dbh->commit(); + $dbh->disconnect(); $main::lxdebug->leave_sub(); -- 2.20.1