projekt kivitendo

use SL::DATEV::KNEFile;

sub _fill {

  $main::lxdebug->enter_sub();

  my $text      = shift // '';

  my $field_len = shift;

  my $fill_char = shift;

  my $alignment = shift || 'right';

  my $text_len  = length $text;

  if ($field_len < $text_len) {

    $text = substr $text, 0, $field_len;

  } elsif ($field_len > $text_len) {

    my $filler = ($fill_char) x ($field_len - $text_len);

    $text      = $alignment eq 'right' ? $filler . $text : $text . $filler;

  }

  $main::lxdebug->leave_sub();

  return $text;

}

  my $result;

  die 'no format set!' unless $self->has_format;

  if ($self->format == DATEV_FORMAT_CSV) {

    $result = $self->csv_export;

  } elsif ($self->format == DATEV_FORMAT_KNE) {

    $result = $self->kne_export;

  } elsif ($self->format == DATEV_FORMAT_OBE) {

    $result = $self->obe_export;

  } else {

    die 'unrecognized export format';

  }

  return $result;

}

sub kne_export {

  my ($self) = @_;

  my $result;

  die 'no exporttype set!' unless $self->has_exporttype;

  if ($self->exporttype == DATEV_ET_BUCHUNGEN) {

    $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';

  }

  return $result;

  } elsif ($self->exporttype == DATEV_ET_STAMM) {

    die 'will never be implemented';

    # 'Background: Export should only contain non

    #  DATEV-Charts and DATEV import will only

    #  import new Charts.'

  } elsif ($self->exporttype == DATEV_ET_CSV) {

    $result = $self->csv_export_for_tax_accountant;

sub obe_export {

  die 'not yet implemented';

}

sub make_kne_data_header {

  $main::lxdebug->enter_sub();

  my ($self, $form) = @_;

  my ($primanota);

  my $stamm = $self->get_datev_stamm;

  my $jahr = $self->from ? $self->from->year : DateTime->today->year;

  #Header

  my $header  = "\x1D\x181";

  $header    .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');

  $header    .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer

  $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 .= $self->from ? $self->from->strftime('%d%m%y') : '';

  $header .= $self->to   ? $self->to->strftime('%d%m%y')   : '';

  if ($self->fromto) {

    $primanota = "001";

    $header .= $primanota;

  }

  $header .= _fill($stamm->{passwort}, 4, '0');

  $header .= " " x 16;       # Anwendungsinfo

  $header .= " " x 16;       # Inputinfo

  $header .= "\x79";

  #Versionssatz

  my $versionssatz  = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";

  my $query         = qq|SELECT accno FROM chart LIMIT 1|;

  my $ref           = selectfirst_hashref_query($form, $self->dbh, $query);

  $versionssatz    .= length $ref->{accno};

  $versionssatz    .= ",";

  $versionssatz    .= length $ref->{accno};

  $versionssatz    .= ",SELF" . "\x1C\x79";

  $header          .= $versionssatz;

  $main::lxdebug->leave_sub();

  return $header;

}

sub datetofour {

  $main::lxdebug->enter_sub();

  my ($date, $six) = @_;

  my ($day, $month, $year) = split(/\./, $date);

  if (length($month) < 2) {

    $month = "0" . $month;

  }

  if (length($year) > 2) {

    $year = substr($year, -2, 2);

  }

  if ($six) {

    $date = $day . $month . $year;

  } else {

    $date = $day . $month;

  }

  $main::lxdebug->leave_sub();

  return $date;

}

sub trim_leading_zeroes {

  my $str = shift;

  $str =~ s/^0+//g;

  return $str;

}

sub make_ed_versionset {

  $main::lxdebug->enter_sub();

  my ($self, $header, $filename, $blockcount) = @_;

  my $versionset  = "V" . substr($filename, 2, 5);

  $versionset    .= substr($header, 6, 22);

  if ($self->fromto) {

    $versionset .= "0000" . substr($header, 28, 19);

  } else {

    my $datum = " " x 16;

    $versionset .= $datum . "001" . substr($header, 28, 4);

  }

  $versionset .= _fill($blockcount, 5, '0');

  $versionset .= "001";

  $versionset .= " 1";

  $versionset .= substr($header, -12, 10) . "    ";

  $versionset .= " " x 53;

  $main::lxdebug->leave_sub();

  return $versionset;

}

sub make_ev_header {

  $main::lxdebug->enter_sub();

  my ($self, $form, $fileno) = @_;

  my $stamm = $self->get_datev_stamm;

  my $ev_header  = _fill($stamm->{datentraegernr}, 3, ' ', 'left');

  $ev_header    .= "   ";

  $ev_header    .= _fill($stamm->{beraternr}, 7, ' ', 'left');

  $ev_header    .= _fill($stamm->{beratername}, 9, ' ', 'left');

  $ev_header    .= " ";

  $ev_header    .= (_fill($fileno, 5, '0')) x 2;

  $ev_header    .= " " x 95;

  $main::lxdebug->leave_sub();

  return $ev_header;

}

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" . SL::VATIDNr->normalize($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 };

}

package SL::DATEV::KNEFile;

use strict;

sub new {

  my $type = shift;

  my $self = {};

  bless $self, $type;

  $self->_init(@_);

  return $self;

}

sub _init {

  my $self   = shift;

  my %params = @_;

  map { $self->{$_} = $params{$_} } keys %params;

  $self->{remaining_bytes} = 250;

  $self->{block_count}     =   0;

  $self->{data}            = '';

}

sub get_data {

  my $self = shift;

  return $self->{data} || '';

}

sub get_block_count {

  my $self = shift;

  return $self->{block_count};

}

sub add_block {

  my $self      = shift;

  my $block     = shift;

  my $block_len = length $block;

  $self->flush() if ($block_len > $self->{remaining_bytes});

  $self->{data}            .= $block;

  $self->{remaining_bytes} -= $block_len;

  return $self;

}

sub flush {

  my $self = shift;

  if (250 == $self->{remaining_bytes}) {

    return $self;

  }

  my $num_zeros             = 6 + $self->{remaining_bytes};

  $self->{data}            .= "\x00" x $num_zeros;

  $self->{remaining_bytes}  = 250;

  $self->{block_count}++;

  return $self;

}

sub format_amount {

  my $self   = shift;

  my $amount = shift;

  my $width  = shift;

  $amount =~ s/-//;

  my ($places, $decimal_places) = split m/\./, "$amount";

  $places          *= 1;

  $decimal_places ||= 0;

  if (0 < $width) {

    $width  -= 2;

    $places  = sprintf("\%0${width}d", $places);

  }

  $decimal_places .= '0' if (2 > length $decimal_places);

  $amount          = $places . substr($decimal_places, 0, 2);

  $amount         *= 1 if (!$width);

  return $amount;

}

1;

        <tr>

          <td><input name=exporttype type=radio class=radio value=0 checked> [% 'Export Buchungsdaten' | $T8 %]</td>

          <td></td>

          <td><input name=exporttype type=radio class=radio value=1> [% 'Export Stammdaten' | $T8 %]</td>

          <td></td>

        </tr>

        <tr>

          <td><input name=exportformat type=radio class=radio value=csv checked> [% 'CSV' | $T8 %]</td>

          <td></td>

          <td><input name=exportformat type=radio class=radio value=kne> [% 'KNE' | $T8 %]</td>

          <td></td>

        </tr>

  <tr>

    <td><hr size=3 noshade></td>

  </tr>

[% IF exportformat == 'kne' %]

  [% 'KNE Export successful!' | $T8 %]

[% END %]

[% IF exportformat == 'csv' %]

[% END %]

[%- USE T8 %]

<h1>[% 'DATEX - Export Assistent' | $T8 %]</h1>

<form method="post" action="[% script %]" id="form">

<table width=100%>

  <tr>

    <th align=left>[% 'Konten' | $T8 %]</th>

  </tr>

  <tr height="5"></tr>

  <tr valign=top>

    <td>

      <table>

        <tr>

          <td align=left>[% 'Von Konto: ' | $T8 %]</td>

          <td align=left><input name=accnofrom size=8 maxlength=8></td>

        </tr>

        <tr>

          <td align=left>[% 'Bis Konto: ' | $T8 %]</td>

          <td align=left><input name=accnoto size=8 maxlength=8></td>

        </tr>

      </table>

    </td>

  </tr>

  <tr>

    <td><hr size=3 noshade></td>

  </tr>

</table>

<input type=hidden name=beraternr value="[% beraternr %]">

<input type=hidden name=dfvkz value="[% dfvkz %]">

<input type=hidden name=beratername value="[% beratername %]">

<input type=hidden name=passwort value="[% passwort %]">

<input type=hidden name=mandantennr value="[% mandantennr %]">

<input type=hidden name=datentraegernr value="[% datentraegernr %]">

<input type=hidden name=kne value="[% kne %]">

<input type=hidden name=abrechnungsnr value="[% abrechnungsnr %]">

<input type=hidden name=exporttype value="[% exporttype %]">

</form>