X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FIR.pm;h=e5e02f5bed4ce480dd0adc25d67e1f353cc296b1;hb=96b0d77f1ab14caba1eb246b19c6eb38846ae44c;hp=04e071e27c5c4529d2572741ee44adb832be5e27;hpb=9d218dc8b07ff4f9be9589cb3246e50653e2b6e2;p=kivitendo-erp.git diff --git a/SL/IR.pm b/SL/IR.pm index 04e071e27..e5e02f5be 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -57,6 +57,7 @@ sub post_invoice { # connect to database, turn off autocommit my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + my $defaultcurrency = $form->{defaultcurrency}; my $ic_cvar_configs = CVar->get_configs(module => 'IC', dbh => $dbh); @@ -70,18 +71,16 @@ 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) VALUES (?, '', (SELECT id FROM currencies WHERE name=?))|, $form->{id}, $form->{currency}); } } - 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 { @@ -211,19 +210,21 @@ sub post_invoice { # 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 = (abs($form->{"sellprice_$i"} / $basefactor), 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, i.base_qty, - p.inventory_accno_id, p.expense_accno_id, a.transdate - FROM invoice i, ar a, parts p + bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate + FROM invoice i, ar a, parts p, buchungsgruppen bg 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) ORDER BY transdate|; # ORDER BY transdate guarantees FIFO @@ -269,13 +270,37 @@ sub post_invoice { # 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}); + $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) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|; - @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}); + $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); } }; @@ -478,11 +503,26 @@ sub post_invoice { 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); } } @@ -517,11 +557,26 @@ 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); } @@ -529,11 +584,24 @@ sub post_invoice { 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) + 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 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"}, - $gldate, $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; @@ -571,9 +639,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); } } @@ -615,18 +685,19 @@ sub post_invoice { 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); @@ -696,6 +767,7 @@ sub post_invoice { dbh => $dbh, from => $transdate, to => $transdate, + trans_id => $form->{id}, ); $datev->export; @@ -852,8 +924,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); @@ -870,15 +941,13 @@ 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})); map { $form->{$_} = $ref->{$_} } keys %$ref; - # remove any trailing whitespace - $form->{currency} =~ s/\s*$//; - $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell"); # get shipto @@ -902,7 +971,7 @@ 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) @@ -1019,22 +1088,20 @@ 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.curr, + 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; - # remove any trailing whitespace - $form->{curr} =~ s/\s*$//; - - # use vendor currency if not empty - $form->{currency} = $form->{curr} if $form->{curr}; + # use vendor currency + $form->{currency} = $form->{curr}; $params->{creditremaining} = $params->{creditlimit}; @@ -1045,7 +1112,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')|; @@ -1148,7 +1215,9 @@ 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"; } @@ -1159,7 +1228,7 @@ sub retrieve_item { 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, @@ -1244,7 +1313,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; @@ -1319,9 +1387,10 @@ sub vendor_details { # 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, - ct.curr AS currency + 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|; @@ -1338,8 +1407,6 @@ 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',