$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"}}) {
$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));
# 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 = $baseqty;
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);
$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);
# 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
# shall receive the total rounding error, and the next time it is rounded
# the 1 cent correction will be introduced.
- my $total_rounding_diff = $invoicediff+$expensediff;
-
$form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff if $lastinventoryaccno;
$form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff if $lastexpenseaccno;
- if ( ($expensediff+$invoicediff) >= 0.005 and $expensediff < 0.005 and $invoicediff < 0.005 ) {
+ 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
push @values, $form->{"partnumber_$i"};
}
+ if ($form->{"id_${i}"}) {
+ $where .= qq| AND p.id = ?|;
+ push @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")) {
$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',
projects / kivitendo-erp.git / blobdiff