projekt kivitendo

package SL::DATEV;

use DateTime;

use Exporter qw(import);

use List::Util qw(max sum);

{

  my $i = 0;

  use constant {

    DATEV_ET_BUCHUNGEN => $i++,

    DATEV_ET_STAMM     => $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);

our @EXPORT_OK = (@export_constants);

our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);

sub new {

  my $class = shift;

  my %data  = @_;

  my $obj = bless {}, $class;

  $obj->$_($data{$_}) for keys %data;

  $obj;

}

sub exporttype {

  my $self = shift;

  $self->{exporttype} = $_[0] if @_;

  return $self->{exporttype};

}

sub has_exporttype {

  defined $_[0]->{exporttype};

}

sub format {

  my $self = shift;

  $self->{format} = $_[0] if @_;

  return $self->{format};

}

sub has_format {

  defined $_[0]->{format};

}

  my $path    = _get_path_for_download_token("${a}-${b}-${$}");

sub _get_path_for_download_token {

  my $token = shift || '';

    $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/";

sub _get_download_token_for_path {

sub download_token {

  my $self = shift;

  $self->{download_token} = $_[0] if @_;

  return $self->{download_token} ||= _get_download_token_for_path($self->export_path);

}

sub export_path {

  my ($self) = @_;

  return  $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path();

}

sub add_filenames {

  my $self = shift;

  push @{ $self->{filenames} ||= [] }, @_;

}

sub filenames {

  return @{ $_[0]{filenames} || [] };

}

sub add_error {

  my $self = shift;

  push @{ $self->{errors} ||= [] }, @_;

}

sub errors {

  return @{ $_[0]{errors} || [] };

}

sub add_net_gross_differences {

  my $self = shift;

  push @{ $self->{net_gross_differences} ||= [] }, @_;

}

sub net_gross_differences {

  return @{ $_[0]{net_gross_differences} || [] };

}

sub sum_net_gross_differences {

  return sum $_[0]->net_gross_differences;

}

sub from {

 my $self = shift;

 if (@_) {

   $self->{from} = $_[0];

 }

 return $self->{from};

}

sub to {

 my $self = shift;

 if (@_) {

   $self->{to} = $_[0];

 }

 return $self->{to};

}

sub accnofrom {

 my $self = shift;

 if (@_) {

   $self->{accnofrom} = $_[0];

 }

 return $self->{accnofrom};

}

sub accnoto {

 my $self = shift;

 if (@_) {

   $self->{accnoto} = $_[0];

 }

 return $self->{accnoto};

}

sub dbh {

  my $self = shift;

  if (@_) {

    $self->{dbh} = $_[0];

    $self->{provided_dbh} = 1;

  }

  $self->{dbh} ||= $::form->get_standard_dbh;

}

sub provided_dbh {

  $_[0]{provided_dbh};

}

  $::lxdebug->enter_sub;

    next unless -d $path;

  $::lxdebug->leave_sub;

  return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');

}

sub save_datev_stamm {

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

  do_query($::form, $self->dbh, 'DELETE FROM datev');

  my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);

  my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";

  do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);

  $self->dbh->commit unless $self->provided_dbh;

sub export {

  my ($self) = @_;

  my $result;

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

  if ($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;

  my ($self) = @_;

  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;

    die 'unrecognized exporttype';

  die 'not yet implemented';

sub fromto {

  my ($self) = @_;

  return unless $self->from && $self->to;

  return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'";

  $_[0] <=> 0;

  my $self     = shift;

  my $progress_callback = shift || sub {};

  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 $sth = prepare_execute_query($form, $self->dbh, $query);

  $self->{DATEV} = [];

      $progress_callback->($counter);

        $self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count");

        return;

      push @{ $self->{DATEV} }, $trans;

        push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];

        push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];

        push @taxed, $self->{DATEV}->[-1];

      $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)");

      $self->add_net_gross_differences($absumsatz);

  $::lxdebug->leave_sub;

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

  my $stamm = $self->get_datev_stamm;

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

  $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) {

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

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

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

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

  if ($self->fromto) {

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

  my $stamm = $self->get_datev_stamm;

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

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

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

  my ($self) = @_;

  my $form = $::form;

  my $fromto = $self->fromto;

  $self->_get_transactions($fromto);

  return if $self->errors;

  while (scalar(@{ $self->{DATEV} || [] })) {

    my $ed_filename = $self->export_path . $filename;

    my $header = $self->make_kne_data_header($form);

    while (scalar(@{ $self->{DATEV} }) > 0) {

      my $transaction = shift @{ $self->{DATEV} };

    $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());

  my $ev_header = $self->make_ev_header($form, $fileno);

  my $ev_filename = $self->export_path . $evfile;

  $self->add_filenames(@filenames);

  return { 'download_token' => $self->download_token, 'filenames' => \@filenames };

  my ($self) = @_;

  my $form = $::form;

  $self->get_datev_stamm->{abrechnungsnr} = "99";

  my $ed_filename = $self->export_path . $filename;

  my $header = $self->make_kne_data_header($form);

  if ($self->accnofrom) {

    push @values, $self->accnofrom;

  if ($self->accnoto) {

    push @values, $self->accnoto;

  my $sth = $self->dbh->prepare($query);

    $self->make_ed_versionset($header, $filename, $blockcount);

  my $ev_header = $self->make_ev_header($form, $fileno);

  my $ev_filename = $self->export_path . $evfile;

  $self->add_filenames(@filenames);

  return { 'download_token' => $self->download_token, 'filenames' => \@filenames };

}

sub DESTROY {

  clean_temporary_directories();

__END__

=encoding utf-8

=head1 NAME

SL::DATEV - Lx-Office DATEV Export module

=head1 SYNOPSIS

  use SL::DATEV qw(:CONSTANTS);

  my $datev = SL::DATEV->new(

    exporttype => DATEV_ET_BUCHUNGEN,

    format     => DATEV_FORMAT_KNE,

    from       => $startdate,

    to         => $enddate,

  );

  my $datev = SL::DATEV->new(

    exporttype => DATEV_ET_STAMM,

    format     => DATEV_FORMAT_KNE,

    accnofrom  => $start_account_number,

    accnoto    => $end_account_number,

  );

  # get or set datev stamm

  my $hashref = $datev->get_datev_stamm;

  $datev->save_datev_stamm($hashref);

  # manually clean up temporary directories

  $datev->clean_temporary_directories;

  # export

  $datev->export;

  if ($datev->errors) {

    die join "\n", $datev->error;

  }

  # get relevant data for saving the export:

  my $dl_token = $datev->download_token;

  my $path     = $datev->export_path;

  my @files    = $datev->filenames;

  # retrieving an export at a later time

  my $datev = SL::DATEV->new(

    download_token => $dl_token_from_user,

  );

  my $path     = $datev->export_path;

  my @files    = glob("$path/*");

=head1 DESCRIPTION

This module implements the DATEV export standard. For usage see above.

=head1 FUNCTIONS

=over 4

=item new PARAMS

Generic constructor. See section attributes for information about hat to pass.

=item get_datev_stamm

Loads DATEV Stammdaten and returns as hashref.

=item save_datev_stamm HASHREF

Saves DATEV Stammdaten from provided hashref.

=item exporttype

See L<CONSTANTS> for possible values

=item has_exporttype

Returns true if an exporttype has been set. Without exporttype most report functions won't work.

=item format

Specifies the designated format of the export. Currently only KNE export is implemented.

See L<CONSTANTS> for possible values

=item has_format

Returns true if a format has been set. Without format most report functions won't work.

=item download_token

Returns a download token for this DATEV object.

Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.

=item export_path

Returns an export_path for this DATEV object.

Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.

=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.

=item net_gross_differences

If there were any net gross differences during calculation they will be collected here.

=item sum_net_gross_differences

Sum of all differences.

=item clean_temporary_directories

Forces a garbage collection on previous exports which will delete all exports that are older than 8 hours. It will be automatically called on destruction of the object, but is advised to be called manually before delivering results of an export to the user.

=item errors

Returns a list of errors that occured. If no errors occured, the export was a success.

=item export

Exports data. You have to have set L<exporttype> and L<format> or an error will

occur. OBE exports are currently not implemented.

=back

=head1 ATTRIBUTES

This is a list of attributes set in either the C<new> or a method of the same name.

=over 4

=item dbh

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.

=item exporttype

See L<CONSTANTS> for possible values. This MUST be set before export is called.

=item format

See L<CONSTANTS> for possible values. This MUST be set before export is called.

=item download_token

Can be set on creation to retrieve a prior export for download.

=item from

=item to

Set boundary dates for the export. Currently thse MUST be set for the export to work.

=item accnofrom

=item accnoto

Set boundary account numbers for the export. Only useful for a stammdaten export.

=back

=head1 CONSTANTS

=head2 Supplied to L<exporttype>

=over 4

=item DATEV_ET_BUCHUNGEN

=item DATEV_ET_STAMM

=back

=head2 Supplied to L<format>.

=over 4

=item DATEV_FORMAT_KNE

=item DATEV_FORMAT_OBE

=back

=head1 ERROR HANDLING

This module will die in the following cases:

=over 4

=item *

No or unrecognized exporttype or format was provided for an export

=item *

OBE rxport was called, which is not yet implemented.

=item *

general I/O errors

=back

Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:

=over 4

=item *

C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.

=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.

=back

=head1 BUGS AND CAVEATS

=over 4

=item *

Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export.

=item *

OBE export is currently not implemented.

=back

=head1 TODO

- handling of export_path and download token is a bit dodgy, clean that up.

=head1 SEE ALSO

L<SL::DATEV::KNEFile>

=head1 AUTHORS

Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,

Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,

Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,

Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,

Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,

Stephan Köhler

=cut

use SL::DATEV qw(:CONSTANTS);

  my $stamm = SL::DATEV->new->get_datev_stamm;

  print $::form->parse_html_template('datev/export', $stamm);

  $::lxdebug->enter_sub;

  $::auth->assert('datev_export');

  my %data = (

    exporttype => $::form->{exporttype} ? DATEV_ET_STAMM : DATEV_ET_BUCHUNGEN,

    format     => $::form->{kne}        ? DATEV_FORMAT_KNE : DATEV_FORMAT_OBE,

  );

  if ($::form->{exporttype} == DATEV_ET_STAMM) {

    $data{accnofrom}  = $::form->{accnofrom},

    $data{accnoto}    = $::form->{accnoto},

  } elsif ($::form->{exporttype} == DATEV_ET_BUCHUNGEN) {

    @data{qw(from to)} = _get_dates(

      $::form->{zeitraum}, $::form->{monat}, $::form->{quartal},

      $::form->{transdatefrom}, $::form->{transdateto},

    );

  } else {

    die 'invalid exporttype';

  }

  my $datev = SL::DATEV->new(%data);

  $datev->clean_temporary_directories;

  $datev->save_datev_stamm($::form);

  $datev->export;

  if (!$datev->errors) {

    $::form->header;

    print $::form->parse_html_template('datev/export3', { datev => $datev });

    $::form->error("Export schlug fehl.\n" . join "\n", $datev->errors);

  $::lxdebug->leave_sub;

  $::auth->assert('datev_export');

  my $datev = SL::DATEV->new(download_token => $form->{download_token});

  my $path = $datev->export_path;

sub _get_dates {

  $::lxdebug->enter_sub;

  my ($mode, $month, $quarter, $transdatefrom, $transdateto) = @_;

  my ($fromdate, $todate);

  if ($mode eq "monat") {

    $fromdate = DateTime->new(day => 1, month => $month, year => DateTime->today->year);

    $todate   = $fromdate->clone->add(months => 1)->add(days => -1);

  } elsif ($mode eq "quartal") {

    die 'quarter out of of bounds' if $quarter < 1 || $quarter > 4;

    $fromdate = DateTime->new(day => 1, month => (3 * $quarter - 2), year => DateTime->today->year);

    $todate   = $fromdate->clone->add(months => 3)->add(days => -1);

  } elsif ($mode eq "zeit") {

    $fromdate = DateTime->from_lxoffice($transdatefrom);

    $todate   = DateTime->from_lxoffice($transdateto);

    die 'need from and to time' unless $fromdate && $todate;

  } else {

    die 'undefined interval mode';

  }

  $::lxdebug->leave_sub;

  return ($fromdate, $todate);

}

use strict;

use Test::More;

use Test::Deep ();

use lib 't';

use_ok 'Support::TestSetup';

use SL::DATEV qw(:CONSTANTS);

use DateTime;

Support::TestSetup::login();

# first, test all the accessors

my $d = new_ok('SL::DATEV' => [], 'new without params');

# export_type

ok(!$d->has_exporttype, 'no exporttype');

is($d->exporttype(DATEV_ET_BUCHUNGEN), DATEV_ET_BUCHUNGEN, 'set exporttype');

is($d->exporttype, DATEV_ET_BUCHUNGEN, 'get exporttype');

ok($d->has_exporttype, 'now exporttype');

# export_path/download_token

ok(!$d->has_format, 'no format');

is($d->format(DATEV_FORMAT_KNE), DATEV_FORMAT_KNE, 'set format');

is($d->format, DATEV_FORMAT_KNE, 'get format');

ok($d->has_format, 'now format');

# check if autogenerated download token work work

ok($d->export_path, 'auto generated export path');

ok($d->download_token, 'auto generated download token');

my $export_path = $d->export_path;

my $download_token = $d->download_token;

# see if that's roundtrip safe

$d = new_ok('SL::DATEV' => [ download_token => $download_token ], 'new with dl token');

is($d->download_token, $download_token, 'previously set download token');

is($d->export_path, $export_path, 'export path from download token');

# array attributes

Test::Deep::cmp_deeply([$d->filenames], [], 'init filenames');

is($d->add_filenames(qw(a b c)), 3, 'add filenames');

Test::Deep::cmp_deeply([$d->filenames], [qw(a b c)], 'get filenames');

Test::Deep::cmp_deeply([$d->errors], [], 'init errors');

is($d->add_error(qw(a b c)), 3, 'add error');

Test::Deep::cmp_deeply([$d->errors], [qw(a b c)], 'get errors');

Test::Deep::cmp_deeply([$d->net_gross_differences], [], 'init net_gross_differences');

is($d->add_net_gross_differences(qw(1 2 3)), 3, 'add net_gross_differences');

Test::Deep::cmp_deeply([$d->net_gross_differences], [qw(1 2 3)], 'get net_gross_differences');

is($d->sum_net_gross_differences, 6, 'sum net_gross_differences');

#get/set attributes

is($d->from, undef, 'init from');

is($d->from(DateTime->today), DateTime->today, 'set from');

is($d->from, DateTime->today, 'get from');

is($d->to, undef, 'init to');

is($d->to(DateTime->today), DateTime->today, 'set to');

is($d->to, DateTime->today, 'get to');

is($d->accnofrom, undef, 'init accnofrom');

is($d->accnofrom('3400'), '3400', 'set accnofrom');

is($d->accnofrom, '3400', 'get accnofrom');

is($d->accnoto, undef, 'init accnoto');

is($d->accnoto('3400'), '3400', 'set accnoto');

is($d->accnoto, '3400', 'get accnoto');

# dbh handling

is($d->dbh, $::form->get_standard_dbh, 'init dbh');

ok(!$d->provided_dbh, 'no dbh provided');

my $other_dbh = $::form->get_standard_dbh->clone;

$d = new_ok('SL::DATEV' => [ dbh => $other_dbh ], 'new with dbh');

isnt($d->dbh, $::form->get_standard_dbh, 'now a different dbh');

ok($d->provided_dbh, 'now got some provided dbh');

$d = new_ok('SL::DATEV' => [], 'another clean one');

is($d->fromto, undef, 'if no from or to -> fromto undef');

# TODO:error handling. test that stuff dies if it has to

done_testing();

1;

[%- USE T8 %]

[%- USE HTML %]

<html>

<body>

  Export in Bearbeitung<br>

  <br>

  Done.

  <br>

  <br><b>[% 'KNE-Export erfolgreich!' | $T8 %]</b>

  <br>

  <br>

  <a href="datev.pl?action=download&download_token=[% datev.download_token | html %]">Download</a>

[% IF datev.net_gross_differences.size %]

[% INCLUDE 'datev/net_gross_difference'

  net_gross_differences     = datev.net_gross_differences,

  sum_net_gross_differences = datev.sum_net_gross_differences

%]

[% END %]

</body>

</html>