X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIR.pm;h=1dde56f74cd29d79e23776a489dea94d650998ae;hb=fee5532a132c44dcfc1743393cba00c8e3397176;hp=83921779abe595da25d65f06a8fa04c208b2e834;hpb=b3caa17585a69e0c5092483f709dd1227b9acaaf;p=kivitendo-erp.git diff --git a/SL/IR.pm b/SL/IR.pm index 83921779a..1dde56f74 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -38,11 +38,15 @@ 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::HTML::Restrict; use SL::IO; use SL::MoreCommon; +use SL::DB::Default; +use SL::DB::TaxZone; use List::Util qw(min); use strict; @@ -54,7 +58,10 @@ sub post_invoice { # connect to database, turn off autocommit my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); + my $restricter = SL::HTML::Restrict->create; + $form->{defaultcurrency} = $form->get_default_currency($myconfig); + my $defaultcurrency = $form->{defaultcurrency}; my $ic_cvar_configs = CVar->get_configs(module => 'IC', dbh => $dbh); @@ -68,22 +75,20 @@ sub post_invoice { my $all_units = AM->retrieve_units($myconfig, $form); +#markierung 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}); + do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, currency_id, taxzone_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?), ?)|, $form->{id}, $form->{currency}, $form->{taxzone_id}); } } - my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|); - my $defaultcurrency = (split m/:/, $currencies)[0]; - 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 || $form->parse_amount($myconfig, $form->{exchangerate}); @@ -103,7 +108,11 @@ sub post_invoice { $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); $form->{"qty_$i"} *= -1 if $form->{storno}; - $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"} if $::lx_office_conf{system}->{eur}; + 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"}}) { @@ -158,8 +167,10 @@ sub post_invoice { $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; } @@ -201,22 +212,40 @@ sub post_invoice { 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($fxsellprice * $form->{exchangerate} / $basefactor), conv_i($form->{"id_$i"})); do_query($form, $dbh, $query, @values); # check if we sold the item already and # make an entry for the expense and inventory + my $taxzone = $form->{taxzone_id} * 1; $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 + qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty, + bg.inventory_accno_id, tc.expense_accno_id AS expense_accno_id, a.transdate + FROM invoice i, ar a, parts p, buchungsgruppen bg, taxzone_charts tc WHERE (i.parts_id = p.id) AND (i.parts_id = ?) AND ((i.base_qty + i.allocated) > 0) AND (i.trans_id = a.id) + AND (p.buchungsgruppen_id = bg.id) + AND (tc.buchungsgruppen_id = p.buchungsgruppen_id) + AND (tc.taxzone_id = ${taxzone}) ORDER BY transdate|; + # ORDER BY transdate guarantees FIFO + +# 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 + +# base_qty + allocated > 0 if article has already been sold but not bought yet + +# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9; +# qty | allocated | base_qty | sellprice +# -----+-----------+----------+------------ +# 2 | 0 | 2 | 1000.00000 + $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"})); my $totalqty = $baseqty; @@ -225,32 +254,62 @@ sub post_invoice { 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, chart_link) VALUES (?, ?, ?, ?, + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= ? + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT tax_id + FROM taxkeys + WHERE chart_id= ? + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT link FROM chart WHERE id = ?))|; + @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $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, tax_id, chart_link) VALUES (?, ?, ?, ?, + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= ? + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT tax_id + FROM taxkeys + WHERE chart_id= ? + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT link FROM chart WHERE id = ?))|; + @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, + $ref->{expense_accno_id}); + do_query($form, $dbh, $query, @values); + } + }; # update allocated for sold item $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1); @@ -263,6 +322,8 @@ sub post_invoice { $sth->finish(); } 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"} / $price_factor, 2); @@ -309,7 +370,7 @@ 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; @@ -323,7 +384,7 @@ sub post_invoice { 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, + $form->{"description_$i"}, $restricter->process($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"}, @@ -380,11 +441,41 @@ sub post_invoice { $invoicediff += $paiddiff; $expensediff += $paiddiff; - ######## this only applies to tax included +######## 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); $paiddiff = $amount - $netamount * $form->{exchangerate}; @@ -405,23 +496,39 @@ sub post_invoice { $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2) if $form->{paid} != 0; - # update exchangerate +# update exchangerate $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate}) if ($form->{currency} ne $defaultcurrency) && !$exchangerate; - # record acc_trans transactions +# 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) + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT link FROM chart WHERE accno = ?))|; @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno}, - conv_date($form->{invdate}), $accno, $project_id); + conv_date($form->{invdate}), $accno, conv_date($form->{invdate}), $project_id, $accno, conv_date($form->{invdate}), $accno); do_query($form, $dbh, $query, @values); } } @@ -440,6 +547,12 @@ sub post_invoice { # record payments and offsetting AP for my $i (1 .. $form->{paidaccounts}) { + 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"}; @@ -450,20 +563,51 @@ 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, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT link FROM chart WHERE accno = ?))|; @values = (conv_i($form->{id}), $form->{AP}, $amount, - $form->{"datepaid_$i"}, $form->{AP}, $project_id); + $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP}); 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, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT link 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); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($form->{"datepaid_$i"}), $project_id, $accno, conv_date($form->{"datepaid_$i"}), $accno); do_query($form, $dbh, $query, @values); $exchangerate = 0; @@ -501,9 +645,11 @@ 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, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?, + (SELECT id FROM tax WHERE taxkey=0 LIMIT 1), + (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno); do_query($form, $dbh, $query, @values); } } @@ -527,6 +673,7 @@ sub post_invoice { # set values which could be empty my $taxzone_id = $form->{taxzone_id} * 1; + $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id); # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen @@ -537,26 +684,25 @@ sub post_invoice { } $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 = ?, 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 = ? + intnotes = ?, storno_id = ?, storno = ?, + cp_id = ?, employee_id = ?, department_id = ?, delivery_term_id = ?, + globalproject_id = ?, direct_debit = ? 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}, 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}), + $form->{intnotes}, 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->{delivery_term_id}), conv_i($form->{globalproject_id}), + $form->{direct_debit} ? 't' : 'f', conv_i($form->{id}) ); do_query($form, $dbh, $query, @values); @@ -576,9 +722,10 @@ sub post_invoice { } - # add shipto $form->{name} = $form->{vendor}; $form->{name} =~ s/--\Q$form->{vendor_id}\E//; + + # add shipto $form->add_shipto($dbh, $form->{id}, "AP"); # delete zero entries @@ -615,6 +762,28 @@ sub post_invoice { '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, + trans_id => $form->{id}, + ); + + $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(); @@ -761,8 +930,7 @@ sub retrieve_invoice { (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 + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno $q_invdate FROM defaults d|; $ref = selectfirst_hashref_query($form, $dbh, $query); @@ -779,7 +947,8 @@ sub 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 + intnotes, (SELECT cu.name FROM currencies cu WHERE cu.id=ap.currency_id) AS currency, direct_debit, + delivery_term_id FROM ap WHERE id = ?|; $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id})); @@ -794,9 +963,9 @@ sub retrieve_invoice { map { $form->{$_} = $ref->{$_} } keys %$ref; 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)); + my $taxzone_id = $form->{taxzone_id} * 1; + $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id); # retrieve individual items $query = @@ -808,13 +977,13 @@ sub retrieve_invoice { 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 + p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, 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 c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) + LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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) @@ -925,17 +1094,21 @@ sub get_vendor { qq|SELECT 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.email, v.cc, v.bcc, v.language_id, v.payment_id, v.delivery_term_id, + v.street, v.zipcode, v.city, v.country, v.taxzone_id, cu.name AS curr, v.direct_debit, $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) + LEFT JOIN currencies cu ON (v.currency_id = cu.id) WHERE 1=1 $where|; my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); map { $params->{$_} = $ref->{$_} } keys %$ref; + # use vendor currency + $form->{currency} = $form->{curr}; + $params->{creditremaining} = $params->{creditlimit}; $query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|; @@ -945,7 +1118,7 @@ sub get_vendor { $query = qq|SELECT o.amount, (SELECT e.sell FROM exchangerate e - WHERE (e.curr = o.curr) + WHERE (e.currency_id = o.currency_id) AND (e.transdate = o.transdate)) AS exch FROM oe o WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|; @@ -956,16 +1129,6 @@ sub get_vendor { } $sth->finish(); - # get shipto if we do not convert an order or invoice - 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); - @{$params}{keys %$ref} = @{$ref}{keys %$ref}; - map { $params->{$_} = $ref->{$_} } keys %$ref; - } - if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) { # setup last accounts used $query = @@ -1028,11 +1191,33 @@ 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 + + my (%mm_by_id); if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) { - $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; - push @values, $form->{"partnumber_$i"}; - } + $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; + push @values, $form->{"partnumber_$i"}; + + # also search hits in makemodels, but only cache the results by id and merge later + my $mm_query = qq| + SELECT parts_id, model FROM makemodel + LEFT JOIN parts ON parts.id = parts_id + WHERE NOT parts.obsolete AND model ILIKE ? AND (make IS NULL OR make = ?); + |; + my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%', $::form->{vendor_id}); + my @mm_ids = map { $_->{parts_id} } @$mm_results; + push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results; + + if (@mm_ids) { + $where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|; + push @values, @mm_ids; + } + } + + # 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"; @@ -1042,20 +1227,23 @@ sub retrieve_item { my $transdate = ""; if ($form->{type} eq "invoice") { - $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date"; + $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) + : $form->{invdate} ? $dbh->quote($form->{invdate}) + : "current_date"; } else { $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; } my $taxzone_id = $form->{taxzone_id} * 1; - $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id)); + $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $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.unit, p.assembly, p.onhand, p.formel, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable, p.inventory_accno_id, p.price_factor_id, + p.ean, pfac.factor AS price_factor, @@ -1079,21 +1267,43 @@ sub retrieve_item { 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) + ((SELECT tc.income_accno_id + FROM taxzone_charts tc + WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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) + ((SELECT tc.expense_accno_id + FROM taxzone_charts tc + WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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")) { + if ($mm_by_id{$ref->{id}}) { + $ref->{makemodels} = $mm_by_id{$ref->{id}}; + push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} }; + } + + if ($ref->{ean} eq $::form->{"partnumber_$i"}) { + push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean}; + } + # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das # Buchungskonto also aus dem Ergebnis rausgenommen werden. @@ -1125,7 +1335,6 @@ sub retrieve_item { my $i = 0; while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) { - # if ($customertax{$ref->{accno}}) { if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) { $i++; $ptr->{accno} = $i; @@ -1140,6 +1349,16 @@ sub retrieve_item { $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(); @@ -1152,6 +1371,7 @@ sub retrieve_item { } $sth->finish(); + $_->[1]->finish for @translation_queries; foreach my $item (@{ $form->{item_list} }) { my $custom_variables = CVar->get_custom_variables(module => 'IC', @@ -1188,9 +1408,11 @@ 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, + cu.name AS currency FROM vendor ct LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) + LEFT JOIN currencies cu ON (ct.currency_id = cu.id) WHERE (ct.id = ?) $contact ORDER BY cp.cp_id LIMIT 1|; @@ -1305,10 +1527,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); @@ -1361,38 +1585,26 @@ sub post_payment { } sub get_duedate { - $main::lxdebug->enter_sub(); + $::lxdebug->enter_sub; - my $self = shift; - my %params = @_; + my ($self, %params) = @_; if (!$params{vendor_id} || !$params{invdate}) { - $main::lxdebug->leave_sub(); + $::lxdebug->leave_sub; return $params{default}; } - my $myconfig = \%main::myconfig; - my $form = $main::form; - - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - + 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 ($sth, $duedate); - - if (($sth = $dbh->prepare($query)) && $sth->execute($params{invdate}, conv_i($params{vendor_id}))) { - ($duedate) = $sth->fetchrow_array(); - $sth->finish(); - } else { - $dbh->rollback(); - } + my ($duedate) = selectfirst_array_query($::form, $dbh, $query, $params{invdate}, $params{vendor_id}); $duedate ||= $params{default}; - $main::lxdebug->leave_sub(); + $::lxdebug->leave_sub; return $duedate; }