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();
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 ($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});
$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"}}) {
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;
}
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);
# 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)
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, 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);
$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);
# 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,
+ 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();
$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};
$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)
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);
}
}
# 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"};
# 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;
$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();
# 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);
$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 = ?,
@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}),
# 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,
'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();
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};
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;
$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
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
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"});
$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;
}
$sth->finish();
- Common::webdav_folder($form) if ($main::webdav);
+ Common::webdav_folder($form);
$dbh->disconnect();
my $query =
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.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 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;
+ # remove any trailing whitespace
+ $form->{curr} =~ s/\s*$//;
+
+ # 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 = ?|;
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}";
}
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";
} else {
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
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
$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)) {
$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();
}
$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();
# 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
}
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',
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} },
$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);
}
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;
}
projects / kivitendo-erp.git / blobdiff