#=====================================================================
-# Lx-Office ERP
+# kivitendo ERP
# Copyright (c) 2004
#
# Author: Philip Reetz
use SL::DBUtils;
use SL::DATEV::KNEFile;
-use SL::Taxkeys;
use Data::Dumper;
use DateTime;
return $self->{to};
}
+sub trans_id {
+ my $self = shift;
+
+ if (@_) {
+ $self->{trans_id} = $_[0];
+ }
+
+ return $self->{trans_id};
+}
+
sub accnofrom {
my $self = shift;
my $form = $main::form;
+ my $trans_id_filter = '';
+
+ $trans_id_filter = 'AND ac.trans_id = ' . $self->trans_id if $self->trans_id;
+
my ($notsplitindex);
$fromto =~ s/transdate/ac\.transdate/g;
- my $taxkeys = Taxkeys->new();
my $filter = ''; # Useful for debugging purposes
my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set');
my $query =
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
- ar.invnumber, ar.duedate, ar.amount as umsatz,
- ct.name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
- ar.invoice
+ ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
+ ct.name, ct.ustid,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ ar.invoice,
+ t.rate AS taxrate
FROM acc_trans ac
LEFT JOIN ar ON (ac.trans_id = ar.id)
LEFT JOIN customer ct ON (ar.customer_id = ct.id)
LEFT JOIN chart c ON (ac.chart_id = c.id)
+ LEFT JOIN tax t ON (ac.tax_id = t.id)
WHERE (ar.id IS NOT NULL)
AND $fromto
+ $trans_id_filter
$filter
UNION ALL
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
- ap.invnumber, ap.duedate, ap.amount as umsatz,
- ct.name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
- ap.invoice
+ ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
+ ct.name,ct.ustid,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ ap.invoice,
+ t.rate AS taxrate
FROM acc_trans ac
LEFT JOIN ap ON (ac.trans_id = ap.id)
LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
LEFT JOIN chart c ON (ac.chart_id = c.id)
+ LEFT JOIN tax t ON (ac.tax_id = t.id)
WHERE (ap.id IS NOT NULL)
AND $fromto
+ $trans_id_filter
$filter
UNION ALL
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
- gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
- gl.description AS name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
- FALSE AS invoice
+ gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate,
+ gl.description AS name, NULL as ustid,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ FALSE AS invoice,
+ t.rate AS taxrate
FROM acc_trans ac
LEFT JOIN gl ON (ac.trans_id = gl.id)
LEFT JOIN chart c ON (ac.chart_id = c.id)
+ LEFT JOIN tax t ON (ac.tax_id = t.id)
WHERE (gl.id IS NOT NULL)
AND $fromto
+ $trans_id_filter
$filter
ORDER BY trans_id, acc_trans_id|;
my $count = $ref->{amount};
my $firstrun = 1;
+
+ # if the amount of a booking in a group is smaller than 0.02, any tax
+ # amounts will likely be smaller than 1 cent, so go into subcent mode
my $subcent = abs($count) < 0.02;
+ # records from acc_trans are ordered by trans_id and acc_trans_id
+ # first check for unbalanced ledger inside one trans_id
+ # there may be several groups inside a trans_id, e.g. the original booking and the payment
+ # each group individually should be exactly balanced and each group
+ # individually needs its own datev lines
+
+ # keep fetching new acc_trans lines until the end of a balanced group is reached
while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
my $ref2 = $sth->fetchrow_hashref("NAME_lc");
last unless ($ref2);
+ # check if trans_id of current acc_trans line is still the same as the
+ # trans_id of the first line in group
+
if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
$self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count");
return;
}
}
- my %taxid_taxkeys = ();
my $absumsatz = 0;
if (scalar(@{$trans}) <= 2) {
push @{ $self->{DATEV} }, $trans;
next;
}
+ # determine at which array position the reference value (called absumsatz) is
+ # and which amount it has
+
for my $j (0 .. (scalar(@{$trans}) - 1)) {
+
+ # Three cases:
+ # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
+
+ # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
+ # true, instead of absumsatz use link AR/AP (there should only be one
+ # entry)
+
+ # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
+ # instead of absumsatz use link AR/AP (there should only be one, so jump
+ # out of search as soon as you find it )
+
+ # case 1 and 2
# for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
# for ap/ar bookings we can always search for AR/AP in link and use that
if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
$absumsatz = $trans->[$j]->{'amount'};
$notsplitindex = $j;
}
+
+ # case 3
+ # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
+ # for AR and AP transaction exit the loop as soon as an AR or AP account is found
+ # there must be only one AR or AP chart in the booking
+ if ( $trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP') {
+ $notsplitindex = $j; # position in booking with highest amount
+ $absumsatz = $trans->[$j]->{'amount'};
+ last;
+ };
}
my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
my $rounding_error = 0;
my @taxed;
+ # go through each line and determine if it is a tax booking or not
+ # skip all tax lines and notsplitindex line
+ # push all other accounts (e.g. income or expense) with corresponding taxkey
+
for my $j (0 .. (scalar(@{$trans}) - 1)) {
if ( ($j != $notsplitindex)
&& !$trans->[$j]->{is_tax}
push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
} elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
- my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate});
my %new_trans = ();
map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
- my $tax_rate = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{taxrate};
+ my $tax_rate = $trans->[$j]->{'taxrate'};
$new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
$new_trans{'tax_rate'} = 1 + $tax_rate;
my $datevautomatik = 0;
my $taxkey = 0;
my $charttax = 0;
+ my $ustid ="";
my ($haben, $soll);
my $iconv = $::locale->{iconv_utf8};
my %umlaute = ($iconv->convert('ä') => 'ae',
$soll = $i;
}
}
-
# Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
foreach my $umlaut (keys(%umlaute)) {
$transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
if ($transaction->[$haben]->{'name'} ne "") {
$buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
}
+ if ($transaction->[$haben]->{'ustid'} ne "") {
+ $ustid = "\xBA" . $transaction->[$haben]->{'ustid'} . "\x1C";
+ }
if ($transaction->[$haben]->{'duedate'} ne "") {
$belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
}
$kne_file->add_block($datum);
$kne_file->add_block($konto);
$kne_file->add_block($buchungstext);
+ $kne_file->add_block($ustid);
$kne_file->add_block($waehrung . "\x79");
}
=head1 NAME
-SL::DATEV - Lx-Office DATEV Export module
+SL::DATEV - kivitendo DATEV Export module
=head1 SYNOPSIS
projects / kivitendo-erp.git / blobdiff