|
#=====================================================================
|
|
# LX-Office ERP
|
|
# Copyright (C) 2004
|
|
# Based on SQL-Ledger Version 2.1.9
|
|
# Web http://www.lx-office.org
|
|
#
|
|
#=====================================================================
|
|
# SQL-Ledger Accounting
|
|
# Copyright (C) 2001
|
|
#
|
|
# Author: Dieter Simader
|
|
# Email: dsimader@sql-ledger.org
|
|
# Web: http://www.sql-ledger.org
|
|
#
|
|
# Contributors:
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; either version 2 of the License, or
|
|
# (at your option) any later version.
|
|
#
|
|
# This program is distributed in the hope that it will be useful,
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
# GNU General Public License for more details.
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
|
|
#======================================================================
|
|
#
|
|
# Inventory received module
|
|
#
|
|
#======================================================================
|
|
|
|
package IR;
|
|
|
|
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;
|
|
|
|
sub post_invoice {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
|
|
|
|
# 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);
|
|
|
|
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);
|
|
|
|
#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, currency_id, taxzone_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?), ?)|, $form->{id}, $form->{currency}, $form->{taxzone_id});
|
|
}
|
|
}
|
|
|
|
if ($form->{currency} eq $defaultcurrency) {
|
|
$form->{exchangerate} = 1;
|
|
} else {
|
|
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'sell');
|
|
}
|
|
|
|
$form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
|
|
$form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
|
|
|
|
my %item_units;
|
|
my $q_item_unit = qq|SELECT unit FROM parts WHERE id = ?|;
|
|
my $h_item_unit = prepare_query($form, $dbh, $q_item_unit);
|
|
|
|
$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
|
|
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
|
|
my $price_factor;
|
|
|
|
my @processed_invoice_ids;
|
|
for my $i (1 .. $form->{rowcount}) {
|
|
next unless $form->{"id_$i"};
|
|
|
|
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
|
|
$form->{"qty_$i"} *= -1 if $form->{storno};
|
|
|
|
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"}}) {
|
|
do_statement($form, $h_item_unit, $q_item_unit, $form->{"id_$i"});
|
|
($item_units{$form->{"id_$i"}}) = $h_item_unit->fetchrow_array();
|
|
}
|
|
|
|
my $item_unit = $item_units{$form->{"id_$i"}};
|
|
|
|
if (defined($all_units->{$item_unit}->{factor})
|
|
&& ($all_units->{$item_unit}->{factor} ne '')
|
|
&& ($all_units->{$item_unit}->{factor} * 1 != 0)) {
|
|
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
|
} else {
|
|
$basefactor = 1;
|
|
}
|
|
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
|
|
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
|
$taxdiff = 0;
|
|
$allocated = 0;
|
|
$taxrate = 0;
|
|
|
|
$form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
|
|
(my $fxsellprice = $form->{"sellprice_$i"}) =~ /\.(\d+)/;
|
|
my $dec = length $1;
|
|
my $decimalplaces = ($dec > 2) ? $dec : 2;
|
|
|
|
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
|
|
|
|
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
|
|
# copied from IS.pm, with some changes (no decimalplaces corrections here etc)
|
|
# TODO maybe use PriceTaxCalculation or something like this for backends (IR.pm / IS.pm)
|
|
|
|
# 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;
|
|
}
|
|
|
|
$netamount += $linetotal;
|
|
|
|
if ($form->round_amount($taxrate, 7) == 0) {
|
|
if ($form->{taxincluded}) {
|
|
foreach $item (@taxaccounts) {
|
|
$taxamount =
|
|
$form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
|
|
$taxdiff += $taxamount;
|
|
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
|
}
|
|
$form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
|
|
|
|
} else {
|
|
map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
|
|
}
|
|
|
|
} else {
|
|
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
|
|
}
|
|
|
|
# add purchase to inventory, this one is without the tax!
|
|
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
|
|
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
|
|
$linetotal = $form->round_amount($linetotal, 2);
|
|
|
|
# this is the difference for the inventory
|
|
$invoicediff += ($amount - $linetotal);
|
|
|
|
$form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -= $linetotal;
|
|
|
|
# adjust and round sellprice
|
|
$form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
|
|
|
|
$lastinventoryaccno = $form->{"inventory_accno_$i"};
|
|
|
|
next if $payments_only;
|
|
|
|
# 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($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,
|
|
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;
|
|
|
|
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 ( $::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 != |