Projekt

Allgemein

Profil

Herunterladen (62,3 KB) Statistiken
| Zweig: | Markierung: | Revision:
#=====================================================================
# 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 !=