X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIR.pm;h=5ff85e5730f0171a5b6dce4f9d3c49d2ca442364;hb=357d134d265637fb37ed2cd47da2ce3ef44d015a;hp=690c20d8412364994ba15235dc7b4937294ff429;hpb=077ebb6c7e6b6ba243da85dbede70ff57927db01;p=kivitendo-erp.git diff --git a/SL/IR.pm b/SL/IR.pm index 690c20d84..5ff85e573 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -35,9 +35,19 @@ package IR; use SL::AM; +use SL::ARAP; use SL::Common; +use SL::CVar; +use SL::DATEV qw(:CONSTANTS); use SL::DBUtils; +use SL::DO; +use SL::GenericTranslations; +use SL::IO; use SL::MoreCommon; +use SL::DB::Default; +use List::Util qw(min); + +use strict; sub post_invoice { $main::lxdebug->enter_sub(); @@ -48,21 +58,23 @@ sub post_invoice { my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + my $ic_cvar_configs = CVar->get_configs(module => 'IC', + dbh => $dbh); + 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 ($basefactor, $baseqty, @taxaccounts, $totaltax); my $all_units = AM->retrieve_units($myconfig, $form); if (!$payments_only) { if ($form->{id}) { &reverse_invoice($dbh, $form); - } else { ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|); - do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber) VALUES (?, '')|, $form->{id}); } } @@ -73,34 +85,31 @@ sub post_invoice { if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { - $exchangerate = - $form->check_exchangerate($myconfig, $form->{currency}, - $form->{transdate}, 'sell'); + $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'sell'); } - $form->{exchangerate} = - ($exchangerate) - ? $exchangerate - : $form->parse_amount($myconfig, $form->{exchangerate}); - + $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate}); $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); + $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS'); + my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} }; + my $price_factor; + 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; - } + $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); + $form->{"qty_$i"} *= -1 if $form->{storno}; - if ($main::eur) { - $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"}; - } + if ( $::instance_conf->get_inventory_system eq 'periodic') { + # inventory account number is overwritten with expense account number, so + # never book incoming to inventory account but always to expense account + $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"} + }; # get item baseunit if (!$item_units{$form->{"id_$i"}}) { @@ -111,8 +120,8 @@ sub post_invoice { my $item_unit = $item_units{$form->{"id_$i"}}; if (defined($all_units->{$item_unit}->{factor}) - && ($all_units->{$item_unit}->{factor} ne '') - && ($all_units->{$item_unit}->{factor} * 1 != 0)) { + && ($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; @@ -125,21 +134,40 @@ sub post_invoice { $taxrate = 0; $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); - my $fxsellprice = $form->{"sellprice_$i"}; - - my ($dec) = ($fxsellprice =~ /\.(\d+)/); - $dec = length $dec; + (my $fxsellprice = $form->{"sellprice_$i"}) =~ /\.(\d+)/; + my $dec = length $1; my $decimalplaces = ($dec > 2) ? $dec : 2; map { $taxrate += $form->{"${_}_rate"} } @taxaccounts; + $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1; + ##################################################################### + # das ist aus IS.pm kopiert. schlimm. jb 7.10.2009 + # ich würde mir wünschen, dass diese vier stellen zusammengefasst werden + # ... vier stellen = (einkauf + verkauf) * (maske + backend) + # ansonsten stolpert man immer wieder viermal statt einmal heftig + # und auch das undo discount formatting ist nicht besonders wartungsfreundlich + + # keine ahnung wofür das in IS.pm gemacht wird: + # my ($dec) = ($fxsellprice =~ /\.(\d+)/); + # $dec = length $dec; + # my $decimalplaces = ($dec > 2) ? $dec : 2; + + # undo discount formatting + $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100; + # deduct discount + $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"}); + + ###################################################################### if ($form->{"inventory_accno_$i"}) { - $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2); + $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2); if ($form->{taxincluded}) { + $taxamount = $linetotal * ($taxrate / (1 + $taxrate)); $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate)); + } else { $taxamount = $linetotal * $taxrate; } @@ -165,8 +193,8 @@ sub post_invoice { } # 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}; + $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor; + $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate}; $linetotal = $form->round_amount($linetotal, 2); # this is the difference for the inventory @@ -175,26 +203,21 @@ sub post_invoice { $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); + $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces); $lastinventoryaccno = $form->{"inventory_accno_$i"}; next if $payments_only; - # update parts table + # update parts table by setting lastcost to current price, don't allow negative values by using abs $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|; - @values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"})); + @values = (abs($form->{"sellprice_$i"} / $basefactor), conv_i($form->{"id_$i"})); do_query($form, $dbh, $query, @values); - if (!$form->{shipped}) { - $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $baseqty, $form->{"id_$i"}) - } - # 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, + qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty, p.inventory_accno_id, p.expense_accno_id, a.transdate FROM invoice i, ar a, parts p WHERE (i.parts_id = p.id) @@ -202,64 +225,78 @@ sub post_invoice { 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"})); + # ORDER BY transdate guarantees FIFO - my $totalqty = $base_qty; +# sold two items without having bought them yet, example result of query: +# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate +# ---+-----+-----------+----------+--------------------+------------------+------------ +# 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05 - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { +# base_qty + allocated > 0 if article has already been sold but not bought yet - my $qty = $ref->{base_qty} + $ref->{allocated}; +# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9; +# qty | allocated | base_qty | sellprice +# -----+-----------+----------+------------ +# 2 | 0 | 2 | 1000.00000 - if (($qty - $totalqty) > 0) { - $qty = $totalqty; - } + $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"})); + my $totalqty = $baseqty; + + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + my $qty = min $totalqty, ($ref->{base_qty} + $ref->{allocated}); $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); - } + if ( $::instance_conf->get_inventory_system eq 'perpetual' ) { + # Warenbestandsbuchungen nur bei Bestandsmethode + + 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) { + + # allocated >= 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, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?), + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{inventory_accno_id}, $ref->{transdate}); + do_query($form, $dbh, $query, @values); + +# add expense + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?), + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{expense_accno_id}, $ref->{transdate}); + do_query($form, $dbh, $query, @values); + } + }; # update allocated for sold item $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1); $allocated += $qty; - last if (($totalqty -= $qty) <= 0); + last if ($totalqty -= $qty) <= 0; } $sth->finish(); - } else { + } else { # if ($form->{"inventory_accno_id_$i"}) + # part doesn't have an inventory_accno_id + # lastcost of the part is updated at the end - $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2); + $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2); if ($form->{taxincluded}) { $taxamount = $linetotal * ($taxrate / (1 + $taxrate)); @@ -274,23 +311,19 @@ sub post_invoice { if ($form->round_amount($taxrate, 7) == 0) { if ($form->{taxincluded}) { foreach $item (@taxaccounts) { - $taxamount = - $linetotal * $form->{"${item}_rate"} - / (1 + abs($form->{"${item}_rate"})); + $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}; + $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor; + $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate}; $linetotal = $form->round_amount($linetotal, 2); # this is the difference for expense @@ -308,28 +341,40 @@ sub post_invoice { # update lastcost $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"})); + do_query($form, $dbh, $query, $form->{"sellprice_$i"} / $basefactor, conv_i($form->{"id_$i"})); } next if $payments_only; # save detail record in invoice table + my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); + $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, + qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty, base_qty, + sellprice, fxsellprice, discount, allocated, unit, deliverydate, + project_id, serialnumber, price_factor_id, price_factor, marge_price_factor) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|; + @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), + $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"} * -1, + $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, $form->{"unit_$i"}, conv_date($form->{deliverydate}), - conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}); + conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, + conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"})); do_query($form, $dbh, $query, @values); + + CVar->save_custom_variables(module => 'IC', + sub_module => 'invoice', + trans_id => $invoice_id, + configs => $ic_cvar_configs, + variables => $form, + name_prefix => 'ic_', + name_postfix => "_$i", + dbh => $dbh); } $h_item_unit->finish(); - my $project_id = conv_i($form->{"globalproject_id"}); + $project_id = conv_i($form->{"globalproject_id"}); $form->{datepaid} = $form->{invdate}; @@ -341,7 +386,7 @@ sub post_invoice { 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->{datepaid} = $form->{"datepaid_$i"} if $form->{"datepaid_$i"}; } my ($tax, $paiddiff) = (0, 0); @@ -367,13 +412,40 @@ sub post_invoice { $invoicediff += $paiddiff; $expensediff += $paiddiff; - ######## this only applies to tax included - if ($lastinventoryaccno) { - $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff; - } - if ($lastexpenseaccno) { - $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff; - } +######## this only applies to tax included + + # in the sales invoice case rounding errors only have to be corrected for + # income accounts, it is enough to add the total rounding error to one of + # the income accounts, with the one assigned to the last row being used + # (lastinventoryaccno) + + # in the purchase invoice case rounding errors may be split between + # inventory accounts and expense accounts. After rounding, an error of 1 + # cent is introduced if the total rounding error exceeds 0.005. The total + # error is made up of $invoicediff and $expensediff, however, so if both + # values are below 0.005, but add up to a total >= 0.005, correcting + # lastinventoryaccno and lastexpenseaccno separately has no effect after + # rounding. This caused bug 1579. Therefore when the combined total exceeds + # 0.005, but neither do individually, the account with the larger value + # shall receive the total rounding error, and the next time it is rounded + # the 1 cent correction will be introduced. + + $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff if $lastinventoryaccno; + $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff if $lastexpenseaccno; + + if ( (abs($expensediff)+abs($invoicediff)) >= 0.005 and abs($expensediff) < 0.005 and abs($invoicediff) < 0.005 ) { + + # in total the rounding error adds up to 1 cent effectively, correct the + # larger of the two numbers + + if ( abs($form->{amount}{ $form->{id} }{$lastinventoryaccno}) > abs($form->{amount}{ $form->{id} }{$lastexpenseaccno}) ) { + # $invoicediff has already been deducted, now also deduct expensediff + $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $expensediff; + } else { + # expensediff has already been deducted, now also deduct invoicediff + $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $invoicediff; + }; + }; } else { $amount = $form->round_amount($netamount * $form->{exchangerate}, 2); @@ -392,27 +464,28 @@ sub post_invoice { $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax; - if ($form->{paid} != 0) { - $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2); - } - # update exchangerate - if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate}); - } + $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2) if $form->{paid} != 0; + +# update exchangerate - # record acc_trans transactions + $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate}) + if ($form->{currency} ne $defaultcurrency) && !$exchangerate; + +# record acc_trans transactions foreach my $trans_id (keys %{ $form->{amount} }) { foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2); - next if ($payments_only || !$form->{amount}{$trans_id}{$accno}); - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + next if $payments_only || !$form->{amount}{$trans_id}{$accno}; + + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno}, - conv_date($form->{invdate}), $accno, $project_id); + conv_date($form->{invdate}), $accno, $project_id, $accno, conv_date($form->{invdate})); do_query($form, $dbh, $query, @values); } } @@ -426,13 +499,18 @@ sub post_invoice { } # force AP entry if 0 - if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0) { - $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}; - } + + $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid} if $form->{amount}{$form->{id}}{$form->{AP}} == 0; # record payments and offsetting AP for my $i (1 .. $form->{paidaccounts}) { - next if ($form->{"paid_$i"} == 0); + if ($form->{"acc_trans_id_$i"} + && $payments_only + && (SL::DB::Default->get->payments_changeable == 0)) { + next; + } + + next if $form->{"paid_$i"} == 0; my ($accno) = split /--/, $form->{"AP_paid_$i"}; $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"}); @@ -442,20 +520,25 @@ sub post_invoice { # 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) + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = (conv_i($form->{id}), $form->{AP}, $amount, - $form->{"datepaid_$i"}, $form->{AP}, $project_id); + $form->{"datepaid_$i"}, $form->{AP}, $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } # 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 = ?), ?)|; + my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); + + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, - $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id, $accno, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); $exchangerate = 0; @@ -463,17 +546,12 @@ sub post_invoice { if ($form->{currency} eq $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 = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell'); + $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); } # exchangerate difference - $form->{fx}{$accno}{ $form->{"datepaid_$i"} } += - $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff; + $form->{fx}{$accno}{ $form->{"datepaid_$i"} } += $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff; # gain/loss $amount = @@ -481,7 +559,6 @@ sub post_invoice { ($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; } @@ -489,11 +566,8 @@ sub post_invoice { $paiddiff = 0; # update exchange rate - if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, - $form->{"datepaid_$i"}, - 0, $form->{"exchangerate_$i"}); - } + $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, 0, $form->{"exchangerate_$i"}) + if ($form->{currency} ne $defaultcurrency) && !$exchangerate; } # record exchange rate differences and gains/losses @@ -502,16 +576,19 @@ sub post_invoice { $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); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?, + (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno, $form->{fx}{$accno}{$transdate}); do_query($form, $dbh, $query, @values); } } + IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh); + if ($payments_only) { - $query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); + $query = qq|UPDATE ap SET paid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); if (!$provided_dbh) { $dbh->commit(); @@ -526,49 +603,38 @@ sub post_invoice { # set values which could be empty my $taxzone_id = $form->{taxzone_id} * 1; - $form->{department_id} = (split /--/, $form->{department})[1]; + + # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr + # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen + # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011 + # copy & paste von IS.pm + if (!$form->{department_id}){ + $form->{department_id} = (split /--/, $form->{department})[1]; + } $form->{invnumber} = $form->{id} unless $form->{invnumber}; - $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id)); + $taxzone_id = 0 if (3 < $taxzone_id) || (0 > $taxzone_id); # save AP record $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 = ?, - storno_id = ?, - globalproject_id = ?, - cp_id = ?, - employee_id = ? + invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?, + orddate = ?, quodate = ?, vendor_id = ?, amount = ?, + netamount = ?, paid = ?, duedate = ?, + invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, + intnotes = ?, curr = ?, storno_id = ?, storno = ?, + cp_id = ?, employee_id = ?, department_id = ?, + globalproject_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->{storno_id}), - conv_i($form->{globalproject_id}), conv_i($form->{cp_id}), - conv_i($form->{employee_id}), - conv_i($form->{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}, conv_date($form->{duedate}), + '1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f', + $form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', + conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), + conv_i($form->{globalproject_id}), + conv_i($form->{id}) + ); do_query($form, $dbh, $query, @values); if ($form->{storno}) { @@ -579,7 +645,7 @@ sub post_invoice { 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})); + 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})); @@ -588,16 +654,65 @@ sub post_invoice { # add shipto $form->{name} = $form->{vendor}; - $form->{name} =~ s/--$form->{vendor_id}//; + $form->{name} =~ s/--\Q$form->{vendor_id}\E//; $form->add_shipto($dbh, $form->{id}, "AP"); # delete zero entries do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE amount = 0|); - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); + + # Link this record to the records it was created from. + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => $form->{convert_from_oe_ids}, + 'to_table' => 'ap', + 'to_id' => $form->{id}, + ); + delete $form->{convert_from_oe_ids}; + + my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids}; + if (scalar @convert_from_do_ids) { + DO->close_orders('dbh' => $dbh, + 'ids' => \@convert_from_do_ids); + + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'delivery_orders', + 'from_ids' => \@convert_from_do_ids, + 'to_table' => 'ap', + 'to_id' => $form->{id}, + ); + } + delete $form->{convert_from_do_ids}; + + ARAP->close_orders_if_billed('dbh' => $dbh, + 'arap_id' => $form->{id}, + 'table' => 'ap',); + + # safety check datev export + if ($::instance_conf->get_datev_check_on_purchase_invoice) { + my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef; + $transdate ||= DateTime->today; + + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_KNE, + dbh => $dbh, + from => $transdate, + to => $transdate, + ); + + $datev->export; + + if ($datev->errors) { + $dbh->rollback; + die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors; + } + } my $rc = 1; - if (!$provided_dbh) { $rc = $dbh->commit(); $dbh->disconnect(); @@ -623,14 +738,11 @@ sub reverse_invoice { my $netamount = 0; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2); next unless $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 next if ($ref->{allocated} <= 0); @@ -644,7 +756,7 @@ sub reverse_invoice { ORDER BY transdate DESC|; my $sth2 = prepare_execute_query($form, $dbh, $query, $ref->{parts_id}); - while (my $pthref = $sth2->fetchrow_hashref(NAME_lc)) { + while (my $pthref = $sth2->fetchrow_hashref("NAME_lc")) { my $qty = $ref->{allocated}; if (($ref->{allocated} + $pthref->{allocated}) > 0) { $qty = $pthref->{allocated} * -1; @@ -693,18 +805,18 @@ sub delete_invoice { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; - + my $query; # connect to database my $dbh = $form->dbconnect_noauto($myconfig); &reverse_invoice($dbh, $form); # delete zero entries - my $query = qq|DELETE FROM acc_trans WHERE amount = 0|; + $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 = ?|; + $query = qq|DELETE FROM ap WHERE id = ?|; do_query($form, $dbh, $query, conv_i($form->{id})); my $rc = $dbh->commit; @@ -741,16 +853,15 @@ sub retrieve_invoice { # 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 - $q_invdate - FROM defaults d|; + $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; @@ -762,17 +873,19 @@ sub retrieve_invoice { } # 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 = ?|; + $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; - $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell"); + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + + $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell"); # get shipto $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|; @@ -788,39 +901,20 @@ sub retrieve_invoice { # 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, + 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 + i.id AS invoice_id, + i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber, + i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount, + 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 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) @@ -829,38 +923,34 @@ sub retrieve_invoice { ORDER BY i.id|; $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - 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)); - } + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + # Retrieve custom variables. + my $cvars = CVar->get_custom_variables(dbh => $dbh, + module => 'IC', + sub_module => 'invoice', + trans_id => $ref->{invoice_id}, + ); + map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; + delete $ref->{invoice_id}; + + map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"}; 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"}); + my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|; + @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"}); } } # 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 + 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 = ?) + (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) @@ -869,7 +959,7 @@ sub retrieve_invoice { $ref->{taxaccounts} = ""; my $i = 0; - while ($ptr = $stw->fetchrow_hashref(NAME_lc)) { + while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) { if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) { $i++; $ptr->{accno} = $i; @@ -877,7 +967,7 @@ sub retrieve_invoice { $ref->{taxaccounts} .= "$ptr->{accno} "; - if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) { + if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) { $form->{"$ptr->{accno}_rate"} = $ptr->{rate}; $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription}; $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber}; @@ -892,7 +982,7 @@ sub retrieve_invoice { } $sth->finish(); - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); $dbh->disconnect(); @@ -902,7 +992,9 @@ sub retrieve_invoice { sub get_vendor { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $params) = @_; + + $params = $form unless defined $params && ref $params eq "HASH"; # connect to database my $dbh = $form->dbconnect($myconfig); @@ -910,33 +1002,51 @@ sub get_vendor { my $dateformat = $myconfig->{dateformat}; $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/; - my $vid = conv_i($form->{vendor_id}); + my $vid = conv_i($params->{vendor_id}); + my $vnr = conv_i($params->{vendornumber}); my $duedate = - ($form->{invdate}) - ? "to_date(" . $dbh->quote($form->{invdate}) . ", '$dateformat')" + ($params->{invdate}) + ? "to_date(" . $dbh->quote($params->{invdate}) . ", '$dateformat')" : "current_date"; # get vendor + my @values = (); + my $where = ''; + if ($vid) { + $where .= 'AND v.id = ?'; + push @values, $vid; + } + if ($vnr) { + $where .= 'AND v.vendornumber = ?'; + push @values, $vnr; + } my $query = qq|SELECT - v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes, + v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount, + 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, + v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr, $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; + WHERE 1=1 $where|; + my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); + map { $params->{$_} = $ref->{$_} } keys %$ref; + + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; - $form->{creditremaining} = $form->{creditlimit}; + # use vendor currency if not empty + $form->{currency} = $form->{curr} if $form->{curr}; + + $params->{creditremaining} = $params->{creditlimit}; $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; + $params->{creditremaining} -= $unpaid_invoices; $query = qq|SELECT o.amount, (SELECT e.sell @@ -948,21 +1058,21 @@ sub get_vendor { my $sth = prepare_execute_query($form, $dbh, $query, $vid); while (my ($amount, $exch) = $sth->fetchrow_array()) { $exch = 1 unless $exch; - $form->{creditremaining} -= $amount * $exch; + $params->{creditremaining} -= $amount * $exch; } $sth->finish(); # get shipto if we do not convert an order or invoice - if (!$form->{shipto}) { - delete @{$form}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)}; + if (!$params->{shipto}) { + delete @{$params}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)}; $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; + @{$params}{keys %$ref} = @{$ref}{keys %$ref}; + map { $params->{$_} = $ref->{$_} } keys %$ref; } - if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) { + if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) { # setup last accounts used $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category @@ -978,25 +1088,25 @@ sub get_vendor { for $ref (@$refs) { if ($ref->{category} eq 'E') { $i++; - - if ($form->{initial_transdate}) { + my ($tax_id, $rate); + if ($params->{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}); - $form->{"taxchart_$i"} = "${tax_id}--${rate}"; + ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate}); + $params->{"taxchart_$i"} = "${tax_id}--${rate}"; } - $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id"; + $params->{"AP_amount_$i"} = "$ref->{accno}--$tax_id"; } if ($ref->{category} eq 'L') { - $form->{APselected} = $form->{AP_1} = $ref->{accno}; + $params->{APselected} = $params->{AP_1} = $ref->{accno}; } } - $form->{rowcount} = $i if ($i && !$form->{type}); + $params->{rowcount} = $i if ($i && !$params->{type}); } $dbh->disconnect(); @@ -1024,6 +1134,17 @@ sub retrieve_item { $where .= " AND lower(${table_column}) LIKE lower(?)"; push @values, '%' . $form->{"${field}_${i}"} . '%'; } + #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung + if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) { + $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; + push @values, $form->{"partnumber_$i"}; + } + + # Search for part ID overrides all other criteria. + if ($form->{"id_${i}"}) { + $where = qq|p.id = ?|; + @values = ($form->{"id_${i}"}); + } if ($form->{"description_$i"}) { $where .= " ORDER BY p.description"; @@ -1046,7 +1167,9 @@ sub retrieve_item { 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, + p.inventory_accno_id, p.price_factor_id, + + pfac.factor AS price_factor, c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, @@ -1076,11 +1199,25 @@ sub retrieve_item { FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id) LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) + LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id) WHERE $where|; my $sth = prepare_execute_query($form, $dbh, $query, @values); + my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription + FROM translation tr + WHERE tr.language_id = ? AND tr.parts_id = ?| ], + [ qq|SELECT tr.translation, tr.longdescription + FROM translation tr + WHERE tr.language_id IN + (SELECT id + FROM language + WHERE article_code = (SELECT article_code FROM language WHERE id = ?)) + AND tr.parts_id = ? + LIMIT 1| ] ); + map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries; + $form->{item_list} = []; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das @@ -1091,7 +1228,7 @@ 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}; + 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 @@ -1111,7 +1248,7 @@ sub retrieve_item { $ref->{taxaccounts} = ""; my $i = 0; - while ($ptr = $stw->fetchrow_hashref(NAME_lc)) { + while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) { # if ($customertax{$ref->{accno}}) { if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) { @@ -1121,23 +1258,46 @@ sub retrieve_item { $ref->{taxaccounts} .= "$ptr->{accno} "; - if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) { + if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) { $form->{"$ptr->{accno}_rate"} = $ptr->{rate}; $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription}; $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber}; $form->{taxaccounts} .= "$ptr->{accno} "; } + if ($form->{language_id}) { + for my $spec (@translation_queries) { + do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id})); + my ($translation, $longdescription) = $spec->[1]->fetchrow_array; + next unless $translation; + $ref->{description} = $translation; + $ref->{longdescription} = $longdescription; + last; + } + } } $stw->finish(); chop $ref->{taxaccounts}; + $ref->{onhand} *= 1; + push @{ $form->{item_list} }, $ref; } $sth->finish(); + $_->[1]->finish for @translation_queries; + + foreach my $item (@{ $form->{item_list} }) { + my $custom_variables = CVar->get_custom_variables(module => 'IC', + trans_id => $item->{id}, + dbh => $dbh, + ); + + map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables }; + } + $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1154,7 +1314,7 @@ sub vendor_details { my @values; # get contact id, set it if nessessary - $form->{cp_id} = (split /--/, $form->{contact})[1]; + $form->{cp_id} *= 1; my $contact = ""; if ($form->{cp_id}) { $contact = "AND cp.cp_id = ?"; @@ -1164,7 +1324,8 @@ sub vendor_details { # 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 + qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail, + ct.curr AS currency FROM vendor ct LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) WHERE (ct.id = ?) $contact @@ -1183,6 +1344,17 @@ sub vendor_details { } map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$// if ($form->{currency}); + + my $custom_variables = CVar->get_custom_variables('dbh' => $dbh, + 'module' => 'CT', + 'trans_id' => $form->{vendor_id}); + map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables }; + + $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh, + 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'), + 'allow_fallback' => 1); $dbh->disconnect(); @@ -1204,7 +1376,7 @@ sub item_links { ORDER BY accno|; my $sth = prepare_execute_query($query, $dbh, $query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { foreach my $key (split(/:/, $ref->{link})) { if ($key =~ /IC/) { push @{ $form->{IC_links}{$key} }, @@ -1225,34 +1397,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -1272,10 +1444,12 @@ sub post_payment { $old_form = save_form(); # Delete all entries in acc_trans from prior payments. - $self->_delete_payments($form, $dbh); + if (SL::DB::Default->get->payments_changeable != 0) { + $self->_delete_payments($form, $dbh); + } # Save the new payments the user made before cleaning up $form. - map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AP_paid_\d+$|^paidaccounts$/, keys %{ $form }; + map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AP_paid_\d+$|^paidaccounts$/, keys %{ $form }; # Clean up $form so that old content won't tamper the results. %keep_vars = map { $_, 1 } qw(login password id); @@ -1309,7 +1483,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -1327,4 +1501,30 @@ sub post_payment { return $rc; } +sub get_duedate { + $::lxdebug->enter_sub; + + my ($self, %params) = @_; + + if (!$params{vendor_id} || !$params{invdate}) { + $::lxdebug->leave_sub; + return $params{default}; + } + + my $dbh = $::form->get_standard_dbh; + my $query = qq|SELECT ?::date + pt.terms_netto + FROM vendor v + LEFT JOIN payment_terms pt ON (pt.id = v.payment_id) + WHERE v.id = ?|; + + my ($duedate) = selectfirst_array_query($::form, $dbh, $query, $params{invdate}, $params{vendor_id}); + + $duedate ||= $params{default}; + + $::lxdebug->leave_sub; + + return $duedate; +} + + 1;