# 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.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Datev export module
use SL::DBUtils;
use SL::DATEV::KNEFile;
-use SL::Taxkeys;
+use SL::DB;
+use SL::HTML::Util ();
+use SL::Locale::String qw(t8);
use Data::Dumper;
use DateTime;
use Exporter qw(import);
use File::Path;
-use List::Util qw(max sum);
+use IO::File;
+use List::MoreUtils qw(any);
+use List::Util qw(min max sum);
+use List::UtilsBy qw(partition_by sort_by);
+use Text::CSV_XS;
use Time::HiRes qw(gettimeofday);
{
use constant {
DATEV_ET_BUCHUNGEN => $i++,
DATEV_ET_STAMM => $i++,
+ DATEV_ET_CSV => $i++,
DATEV_FORMAT_KNE => $i++,
DATEV_FORMAT_OBE => $i++,
};
}
-my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_FORMAT_KNE DATEV_FORMAT_OBE);
+my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE);
our @EXPORT_OK = (@export_constants);
our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
$self->{trans_id} = $_[0];
}
+ die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/;
+
return $self->{trans_id};
}
$self->{provided_dbh} = 1;
}
- $self->{dbh} ||= $::form->get_standard_dbh;
+ $self->{dbh} ||= SL::DB->client->dbh;
}
sub provided_dbh {
sub _fill {
$main::lxdebug->enter_sub();
- my $text = shift;
+ my $text = shift // '';
my $field_len = shift;
my $fill_char = shift;
my $alignment = shift || 'right';
sub save_datev_stamm {
my ($self, $data) = @_;
- do_query($::form, $self->dbh, 'DELETE FROM datev');
-
- my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
+ SL::DB->client->with_transaction(sub {
+ do_query($::form, $self->dbh, 'DELETE FROM datev');
- my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
- do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
+ my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
- $self->dbh->commit unless $self->provided_dbh;
+ my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
+ do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
+ 1;
+ }) or do { die SL::DB->client->error };
}
sub export {
$result = $self->kne_buchungsexport;
} elsif ($self->exporttype == DATEV_ET_STAMM) {
$result = $self->kne_stammdatenexport;
+ } elsif ($self->exporttype == DATEV_ET_CSV) {
+ $result = $self->csv_export_for_tax_accountant;
} else {
die 'unrecognized exporttype';
}
sub _get_transactions {
$main::lxdebug->enter_sub();
- my $self = shift;
- my $fromto = shift;
- my $progress_callback = shift || sub {};
+
+ my ($self, %params) = @_;
+ my $fromto = $params{from_to};
+ my $progress_callback = $params{progress_callback} || sub {};
my $form = $main::form;
my $trans_id_filter = '';
- $trans_id_filter = 'AND ac.trans_id = ' . $self->trans_id if $self->trans_id;
+ if ( $self->{trans_id} ) {
+ # ignore dates when trans_id is passed so that the entire transaction is
+ # checked, not just either the initial bookings or the subsequent payments
+ # (the transdates will likely differ)
+ $fromto = '';
+ $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
+ } else {
+ $fromto =~ s/transdate/ac\.transdate/g;
+ };
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, ar.deliverydate,
- ct.name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
- ar.invoice
+ qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
+ ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
+ ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
+ c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ ar.invoice,
+ t.rate AS taxrate, t.taxdescription,
+ 'ar' as table,
+ tc.accno AS tax_accno, tc.description AS tax_accname,
+ ar.notes
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)
+ LEFT JOIN chart tc ON (t.chart_id = tc.id)
WHERE (ar.id IS NOT NULL)
AND $fromto
$trans_id_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, ap.deliverydate,
- ct.name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
- ap.invoice
+ SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
+ ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
+ ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
+ c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ ap.invoice,
+ t.rate AS taxrate, t.taxdescription,
+ 'ap' as table,
+ tc.accno AS tax_accno, tc.description AS tax_accname,
+ ap.notes
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)
+ LEFT JOIN chart tc ON (t.chart_id = tc.id)
WHERE (ap.id IS NOT NULL)
AND $fromto
$trans_id_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, NULL as deliverydate,
- gl.description AS name,
- c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
- FALSE AS invoice
+ SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
+ gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
+ gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
+ c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ FALSE AS invoice,
+ t.rate AS taxrate, t.taxdescription,
+ 'gl' as table,
+ tc.accno AS tax_accno, tc.description AS tax_accname,
+ gl.notes
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)
+ LEFT JOIN chart tc ON (t.chart_id = tc.id)
WHERE (gl.id IS NOT NULL)
AND $fromto
$trans_id_filter
$self->{DATEV} = [];
my $counter = 0;
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ my $continue = 1; #
+ my $name;
+ while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
+ last unless $ref; # for single transactions
$counter++;
if (($counter % 500) == 0) {
$progress_callback->($counter);
# 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);
+ unless ( $ref2 ) {
+ $continue = 0;
+ last;
+ };
# check if trans_id of current acc_trans line is still the same as the
- # trans_id of the first line in group
+ # trans_id of the first line in group, i.e. we haven't finished a 0-group
+ # before moving on to the next trans_id, error will likely be in the old
+ # trans_id.
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");
+ require SL::DB::Manager::AccTransaction;
+ if ( $trans->[0]->{trans_id} ) {
+ my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
+ $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)",
+ $acc_trans_obj->transaction_name, $ref2->{trans_id})
+ );
+ };
return;
}
}
}
- my %taxid_taxkeys = ();
my $absumsatz = 0;
if (scalar(@{$trans}) <= 2) {
push @{ $self->{DATEV} }, $trans;
# 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') {
+ # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone
+ # manually pays an invoice in GL.
+ if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) {
$notsplitindex = $j; # position in booking with highest amount
$absumsatz = $trans->[$j]->{'amount'};
last;
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},
- 'deliverydate' => $trans->[$j]->{deliverydate});
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;
$absumsatz = $form->round_amount($absumsatz, 2);
if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
- $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)");
-
+ require SL::DB::Manager::AccTransaction;
+ my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
+ $self->add_error(t8("Export error in transaction #1: Rounding error too large #2",
+ $acc_trans_obj->transaction_name, $absumsatz)
+ );
} elsif (abs($absumsatz) >= 0.01) {
$self->add_net_gross_differences($absumsatz);
}
$header .= _fill($stamm->{dfvkz}, 2, '0');
$header .= _fill($stamm->{beraternr}, 7, '0');
$header .= _fill($stamm->{mandantennr}, 5, '0');
- $header .= _fill($stamm->{abrechnungsnr} . $jahr, 6, '0');
+ $header .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
$header .= $self->from ? $self->from->strftime('%d%m%y') : '';
$header .= $self->to ? $self->to->strftime('%d%m%y') : '';
my $fromto = $self->fromto;
- $self->_get_transactions($fromto);
+ $self->_get_transactions(from_to => $fromto);
return if $self->errors;
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]->{'duedate'} ne "") {
+ 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");
}
print(EV $ev_header);
foreach my $file (@ed_versionset) {
- print(EV $ed_versionset[$file]);
+ print(EV $file);
}
close(EV);
###
return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
}
+sub _format_accno {
+ my ($accno) = @_;
+ return $accno . ('0' x (6 - min(length($accno), 6)));
+}
+
+sub csv_export_for_tax_accountant {
+ my ($self) = @_;
+
+ $self->_get_transactions(from_to => $self->fromto);
+
+ foreach my $transaction (@{ $self->{DATEV} }) {
+ foreach my $entry (@{ $transaction }) {
+ $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
+ }
+ }
+
+ my %transactions =
+ partition_by { $_->[0]->{table} }
+ sort_by { $_->[0]->{sortkey} }
+ grep { 2 == scalar(@{ $_ }) }
+ @{ $self->{DATEV} };
+
+ my %column_defs = (
+ acc_trans_id => { 'text' => $::locale->text('ID'), },
+ amount => { 'text' => $::locale->text('Amount'), },
+ credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
+ credit_accno => { 'text' => $::locale->text('Credit Account'), },
+ debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
+ debit_accno => { 'text' => $::locale->text('Debit Account'), },
+ invnumber => { 'text' => $::locale->text('Reference'), },
+ name => { 'text' => $::locale->text('Name'), },
+ notes => { 'text' => $::locale->text('Notes'), },
+ tax => { 'text' => $::locale->text('Tax'), },
+ taxkey => { 'text' => $::locale->text('Taxkey'), },
+ tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
+ tax_accno => { 'text' => $::locale->text('Tax Account'), },
+ transdate => { 'text' => $::locale->text('Invoice Date'), },
+ vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
+ );
+
+ my @columns = qw(
+ acc_trans_id name vcnumber
+ transdate invnumber amount
+ debit_accno debit_accname
+ credit_accno credit_accname
+ tax
+ tax_accno tax_accname taxkey
+ notes
+ );
+
+ my %filenames_by_type = (
+ ar => $::locale->text('AR Transactions'),
+ ap => $::locale->text('AP Transactions'),
+ gl => $::locale->text('GL Transactions'),
+ );
+
+ my @filenames;
+ foreach my $type (qw(ap ar)) {
+ my %csvs = (
+ invoices => {
+ content => '',
+ filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
+ csv => Text::CSV_XS->new({
+ binary => 1,
+ eol => "\n",
+ sep_char => ";",
+ }),
+ },
+ payments => {
+ content => '',
+ filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
+ csv => Text::CSV_XS->new({
+ binary => 1,
+ eol => "\n",
+ sep_char => ";",
+ }),
+ },
+ );
+
+ foreach my $csv (values %csvs) {
+ $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
+ $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
+
+ push @filenames, $csv->{filename};
+ }
+
+ foreach my $transaction (@{ $transactions{$type} }) {
+ my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
+ my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
+
+ my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
+ my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
+ my $amount = defined($soll->{net_amount}) ? $soll : $haben;
+ $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
+ $haben->{notes} //= '';
+ $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
+ $haben->{notes} =~ s{\r}{}g;
+ $haben->{notes} =~ s{\n+}{ }g;
+
+ my %row = (
+ amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
+ debit_accno => _format_accno($soll->{accno}),
+ debit_accname => $soll->{accname},
+ credit_accno => _format_accno($haben->{accno}),
+ credit_accname => $haben->{accname},
+ tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
+ notes => $haben->{notes},
+ (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
+ (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
+ );
+
+ $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
+ }
+
+ $_->{out}->close for values %csvs;
+ }
+
+ $self->add_filenames(@filenames);
+
+ return { download_token => $self->download_token, filenames => \@filenames };
+}
+
sub DESTROY {
clean_temporary_directories();
}
use SL::DATEV qw(:CONSTANTS);
+ my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
+ my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
my $datev = SL::DATEV->new(
exporttype => DATEV_ET_BUCHUNGEN,
format => DATEV_FORMAT_KNE,
to => $enddate,
);
+ # To only export transactions from a specific trans_id: (from and to are ignored)
+ my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
+ my $datev = SL::DATEV->new(
+ exporttype => DATEV_ET_BUCHUNGEN,
+ format => DATEV_FORMAT_KNE,
+ trans_id => $invoice->trans_id,
+ );
+
my $datev = SL::DATEV->new(
exporttype => DATEV_ET_STAMM,
format => DATEV_FORMAT_KNE,
my $hashref = $datev->get_datev_stamm;
$datev->save_datev_stamm($hashref);
- # manually clean up temporary directories
+ # manually clean up temporary directories older than 8 hours
$datev->clean_temporary_directories;
# export
=item new PARAMS
-Generic constructor. See section attributes for information about hat to pass.
+Generic constructor. See section attributes for information about what to pass.
=item get_datev_stamm
=item filenames
-Returns a list of filenames generated by this DATEV object. This only works if th files were generated during it's lifetime, not if the object was created from a download_token.
+Returns a list of filenames generated by this DATEV object. This only works if the files were generated during its lifetime, not if the object was created from a download_token.
=item net_gross_differences
Set a database handle to use in the process. This allows for an export to be
done on a transaction in progress without committing first.
+Note: If you don't want this code to commit, simply providing a dbh is not
+enough enymore. You'll have to wrap the call into a transaction yourself, so
+that the internal transaction does not commit.
+
=item exporttype
See L<CONSTANTS> for possible values. This MUST be set before export is called.
=item to
-Set boundary dates for the export. Currently thse MUST be set for the export to work.
+Set boundary dates for the export. Unless a trans_id is passed these MUST be
+set for the export to work.
+
+=item trans_id
+
+To check only one gl/ar/ap transaction, pass the trans_id. The attributes
+L<from> and L<to> are currently still needed for the query to be assembled
+correctly.
=item accnofrom
=item *
-OBE rxport was called, which is not yet implemented.
+OBE export was called, which is not yet implemented.
=item *
=item *
C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
-transaction could not be reliably sorted out, or had rounding errors over the acceptable threshold.
+transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.
=back
=item *
-Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export.
+Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.
=item *
projects / kivitendo-erp.git / blobdiff