X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FIR.pm;h=8536aad4fabeea997402546a9204b8b31aacd02a;hb=b56c0cd76508a8d39e080f316285db1c52c17255;hp=72a36ae5710732a6f660e2cb13f029f119da68f9;hpb=2868feee8fb33457e7562f02778186b5b5c1a2b6;p=kivitendo-erp.git diff --git a/SL/IR.pm b/SL/IR.pm index 72a36ae57..8536aad4f 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -37,11 +37,16 @@ package IR; use SL::AM; use SL::ARAP; use SL::Common; +use SL::CVar; use SL::DBUtils; use SL::DO; +use SL::GenericTranslations; +use SL::IO; use SL::MoreCommon; use List::Util qw(min); +use strict; + sub post_invoice { $main::lxdebug->enter_sub(); @@ -51,11 +56,15 @@ 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); @@ -94,7 +103,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 $main::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"}}) { @@ -126,14 +139,33 @@ sub post_invoice { 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"} / $price_factor, 2); if ($form->{taxincluded}) { + $taxamount = $linetotal * ($taxrate / (1 + $taxrate)); $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate)); + } else { $taxamount = $linetotal * $taxrate; } @@ -183,7 +215,7 @@ sub post_invoice { # 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) @@ -191,40 +223,60 @@ sub post_invoice { AND ((i.base_qty + i.allocated) > 0) AND (i.trans_id = a.id) 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 = $base_qty; + my $totalqty = $baseqty; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + 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) 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); + } + }; # update allocated for sold item $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1); @@ -237,6 +289,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); @@ -289,18 +343,29 @@ sub post_invoice { 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, + 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 = (conv_i($form->{id}), conv_i($form->{"id_$i"}), - $form->{"description_$i"}, $form->{"qty_$i"} * -1, - $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated, + 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->{"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(); @@ -343,11 +408,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}; @@ -365,24 +460,25 @@ sub post_invoice { $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax; - + $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) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_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); @@ -398,11 +494,17 @@ sub post_invoice { } # force AP entry if 0 - + $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}) { + if ($form->{"acc_trans_id_$i"} + && $payments_only + && ($::lx_office_conf{features}->{payments_changeable} == 0)) { + next; + } + next if $form->{"paid_$i"} == 0; my ($accno) = split /--/, $form->{"AP_paid_$i"}; @@ -422,11 +524,14 @@ sub post_invoice { } # 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 = ?), ?, ?, ?, ?, + 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) + 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); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); do_query($form, $dbh, $query, @values); $exchangerate = 0; @@ -471,9 +576,11 @@ sub post_invoice { } } + 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(); @@ -488,7 +595,14 @@ 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); @@ -497,19 +611,19 @@ sub post_invoice { $query = qq|UPDATE ap SET invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?, orddate = ?, quodate = ?, vendor_id = ?, amount = ?, - netamount = ?, paid = ?, duedate = ?, datepaid = ?, + netamount = ?, paid = ?, duedate = ?, invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, intnotes = ?, curr = ?, storno_id = ?, storno = ?, - cp_id = ?, employee_id = ?, department_id = ?, + 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}, conv_date($form->{duedate}), $form->{paid} ? conv_date($form->{datepaid}) : undef, + $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->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{globalproject_id}), conv_i($form->{id}) ); @@ -523,7 +637,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})); @@ -538,7 +652,7 @@ sub post_invoice { # 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, @@ -595,7 +709,7 @@ 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}; @@ -613,7 +727,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; @@ -759,8 +873,9 @@ sub retrieve_invoice { 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, - i.price_factor_id, i.price_factor, i.marge_price_factor, + 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 @@ -776,7 +891,16 @@ 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)) { + 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"}); @@ -803,7 +927,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; @@ -826,7 +950,7 @@ sub retrieve_invoice { } $sth->finish(); - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); $dbh->disconnect(); @@ -855,7 +979,8 @@ sub get_vendor { : "current_date"; # get vendor - @values = (); + my @values = (); + my $where = ''; if ($vid) { $where .= 'AND v.id = ?'; push @values, $vid; @@ -866,7 +991,8 @@ sub get_vendor { } my $query = qq|SELECT - v.id AS vendor_id, 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, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, @@ -875,7 +1001,7 @@ sub get_vendor { LEFT JOIN business b ON (b.id = v.business_id) LEFT JOIN payment_terms pt ON (v.payment_id = pt.id) WHERE 1=1 $where|; - $ref = selectfirst_hashref_query($form, $dbh, $query, @values); + my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); map { $params->{$_} = $ref->{$_} } keys %$ref; $params->{creditremaining} = $params->{creditlimit}; @@ -924,14 +1050,14 @@ sub get_vendor { for $ref (@$refs) { if ($ref->{category} eq 'E') { $i++; - + 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}, $params->{initial_transdate}); + ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate}); $params->{"taxchart_$i"} = "${tax_id}--${rate}"; } @@ -970,6 +1096,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"; @@ -1028,8 +1165,21 @@ sub retrieve_item { 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 @@ -1040,7 +1190,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 @@ -1060,7 +1210,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)) { @@ -1077,6 +1227,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(); @@ -1089,6 +1249,17 @@ 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', + trans_id => $item->{id}, + dbh => $dbh, + ); + + map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables }; + } + $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1140,6 +1311,10 @@ sub vendor_details { '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(); $main::lxdebug->leave_sub(); @@ -1160,7 +1335,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} }, @@ -1181,34 +1356,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); } @@ -1228,10 +1403,12 @@ sub post_payment { $old_form = save_form(); # Delete all entries in acc_trans from prior payments. - $self->_delete_payments($form, $dbh); + if ($::lx_office_conf{features}->{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); @@ -1265,7 +1442,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}));