-
-sub kne_buchungsexport {
- $main::lxdebug->enter_sub();
-
- my ($self) = @_;
-
- my $form = $::form;
-
- my @filenames;
-
- my $filename = "ED00001";
- my $evfile = "EV01";
- my @ed_versionset;
- my $fileno = 1;
- my $ed_filename = $self->export_path . $filename;
-
- my $fromto = $self->fromto;
-
- $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
- return if $self->errors;
-
- my @datev_lines = @{ $self->generate_datev_lines };
-
-
- my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
-
- # prepare kne file, everything gets stored in ED00001
- my $header = $self->make_kne_data_header($form);
- my $kne_file = SL::DATEV::KNEFile->new();
- $kne_file->add_block($header);
-
- my $iconv = $::locale->{iconv_utf8};
- my %umlaute = ($iconv->convert('ä') => 'ae',
- $iconv->convert('ö') => 'oe',
- $iconv->convert('ü') => 'ue',
- $iconv->convert('Ä') => 'Ae',
- $iconv->convert('Ö') => 'Oe',
- $iconv->convert('Ü') => 'Ue',
- $iconv->convert('ß') => 'sz');
-
- # add the data from @datev_lines to the kne_file, formatting as needed
- foreach my $kne ( @datev_lines ) {
- $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
-
- # only add buchungsschluessel if it was previously defined
- $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
-
- # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
- $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
-
- if ( $kne->{belegfeld1} ) {
- my $invnumber = $kne->{belegfeld1};
- foreach my $umlaut (keys(%umlaute)) {
- $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
- }
- $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
- $invnumber = substr($invnumber, 0, 12);
- $invnumber =~ s/\ *$//;
- $kne_file->add_block("\xBD" . $invnumber . "\x1C");
- }
-
- $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
-
- $kne_file->add_block("d" . &datetofour($kne->{datum},0));
-
- # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
- $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
-
- my $name = $kne->{buchungstext};
- foreach my $umlaut (keys(%umlaute)) {
- $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
- }
- $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
- $name = substr($name, 0, 30);
- $name =~ s/\ *$//;
- $kne_file->add_block("\x1E" . $name . "\x1C");
-
- $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'};
-
- $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
- };
-
- $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0);
- my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
-
- $kne_file->add_block($mandantenendsumme);
- $kne_file->flush();
-
- open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
- print(ED $kne_file->get_data());
- close(ED);
-
- $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
-
- #Make EV Verwaltungsdatei
- my $ev_header = $self->make_ev_header($form, $fileno);
- my $ev_filename = $self->export_path . $evfile;
- push(@filenames, $evfile);
- open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
- print(EV $ev_header);
-
- foreach my $file (@ed_versionset) {
- print(EV $file);
- }
- close(EV);
- ###
-
- $self->add_filenames(@filenames);
-
- $main::lxdebug->leave_sub();
-
- return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
-}
-
-sub kne_stammdatenexport {
- $main::lxdebug->enter_sub();
-
- my ($self) = @_;
- my $form = $::form;
-
- $self->get_datev_stamm->{abrechnungsnr} = "99";
-
- my @filenames;
-
- my $filename = "ED00000";
- my $evfile = "EV01";
- my @ed_versionset;
- my $fileno = 1;
- my $i = 0;
- my $blockcount = 1;
- my $remaining_bytes = 256;
- my $total_bytes = 256;
- my $buchungssatz = "";
- $filename++;
- my $ed_filename = $self->export_path . $filename;
- push(@filenames, $filename);
- open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
- my $header = $self->make_kne_data_header($form);
- $remaining_bytes -= length($header);
-
- my $fuellzeichen;
-
- my (@where, @values) = ((), ());
- if ($self->accnofrom) {
- push @where, 'c.accno >= ?';
- push @values, $self->accnofrom;
- }
- if ($self->accnoto) {
- push @where, 'c.accno <= ?';
- push @values, $self->accnoto;
- }
-
- my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
-
- my $query = qq|SELECT c.accno, c.description
- FROM chart c
- $where_str
- ORDER BY c.accno|;
-
- my $sth = $self->dbh->prepare($query);
- $sth->execute(@values) || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
- $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
- $buchungssatz .= "\x00" x $fuellzeichen;
- $blockcount++;
- $total_bytes = ($blockcount) * 256;
- }
- $buchungssatz .= "t" . $ref->{'accno'};
- $remaining_bytes = $total_bytes - length($buchungssatz . $header);
- $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
- $ref->{'description'} = substr($ref->{'description'}, 0, 40);
- $ref->{'description'} =~ s/\ *$//;
-
- if (
- ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
- ) <= 6
- ) {
- $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
- $buchungssatz .= "\x00" x $fuellzeichen;
- $blockcount++;
- $total_bytes = ($blockcount) * 256;
- }
- $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
- $remaining_bytes = $total_bytes - length($buchungssatz . $header);
- }
-
- $sth->finish;
- print(ED $header);
- print(ED $buchungssatz);
- $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
- my $dateiende = "\x00" x $fuellzeichen;
- print(ED "z");
- print(ED $dateiende);
- close(ED);
-
- #Make EV Verwaltungsdatei
- $ed_versionset[0] =
- $self->make_ed_versionset($header, $filename, $blockcount);
-
- my $ev_header = $self->make_ev_header($form, $fileno);
- my $ev_filename = $self->export_path . $evfile;
- push(@filenames, $evfile);
- open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
- print(EV $ev_header);
-
- foreach my $file (@ed_versionset) {
- print(EV $ed_versionset[$file]);
- }
- close(EV);
-
- $self->add_filenames(@filenames);
-
- $main::lxdebug->leave_sub();
-
- 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->generate_datev_data(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('Transdate'), },
- 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 csv_buchungsexport {
- my $self = shift;
- my %params = @_;
-
- $self->generate_datev_data(from_to => $self->fromto);
- return if $self->errors;
-
- my @datev_lines = @{ $self->generate_datev_lines };
-
- my @csv_columns = SL::DATEV::CSV->kivitendo_to_datev();
- my @csv_headers = SL::DATEV::CSV->generate_csv_header(
- from => $self->from->ymd(''),
- to => $self->to->ymd(''),
- first_day_of_fiscal_year => $self->to->year . '0101',
- locked => 0
- );
-
- my @array_of_datev;
-
- # 2 Headers
- push @array_of_datev, \@csv_headers;
- push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ];
-
- my @warnings;
- foreach my $row ( @datev_lines ) {
- my @current_datev_row;
-
- # shorten strings
- if ($row->{belegfeld1}) {
- $row->{buchungsbes} = $row->{belegfeld1} if $row->{belegfeld1};
- $row->{belegfeld1} = substr($row->{belegfeld1}, 0, 12);
- $row->{buchungsbes} = substr($row->{buchungsbes}, 0, 60);
- }
-
- $row->{datum} = datetofour($row->{datum}, 0);
- $row->{kost1} = substr($row->{kost1}, 0, 8) if $row->{kost1};
- $row->{kost2} = substr($row->{kost2}, 0, 8) if $row->{kost2};
-
- # , as decimal point and trim for UstID
- $row->{umsatz} =~ s/\./,/;
- $row->{ustid} =~ s/\s//g if $row->{ustid}; # trim whitespace
-
- foreach my $column (@csv_columns) {
- if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') {
- # check max length
- die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length};
- }
- if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') {
- # more checks, listed as user warnings
- push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
- " with amount '#3'",$row->{ $column->{kivi_datev_name} },
- $column->{kivi_datev_name},$row->{umsatz})
- unless ($column->{valid_check}->($row->{ $column->{kivi_datev_name} }));
- }
- push @current_datev_row, $row->{ $column->{kivi_datev_name} };
- }
- push @array_of_datev, \@current_datev_row;
- }
- $self->warnings(@warnings) if @warnings;
- return \@array_of_datev;
-}
-
-sub _csv_buchungsexport_to_file {
- my $self = shift;
- my %params = @_;
-
- # we can definitely deny shorter data structures
- croak ("Need at least 2 rows for header info") unless scalar @{ $params{data} } > 1;
-
- my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
- my @data = \$params{data};
-
- my $csv = Text::CSV_XS->new({
- binary => 1,
- sep_char => ";",
- always_quote => 1,
- eol => "\r\n",
- }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
-
- if ($csv->version >= 1.18) {
- # get rid of stupid datev warnings in "Validity program"
- $csv->quote_empty(1);
- }
-
- my $csv_file = IO::File->new($self->export_path . '/' . $filename, '>:encoding(cp1252)') or die "Can't open: $!";
- $csv->print($csv_file, $_) for @{ $params{data} };
- $csv_file->close;
-
- return { download_token => $self->download_token, filenames => $params{filename} };
-}
-