use strict;
use SL::DBUtils;
-use SL::DATEV::KNEFile;
+use SL::DATEV::CSV;
use SL::DB;
use SL::HTML::Util ();
+use SL::Iconv;
use SL::Locale::String qw(t8);
+use SL::VATIDNr;
use Data::Dumper;
use DateTime;
return $self->{trans_id};
}
+sub warnings {
+ my $self = shift;
+
+ if (@_) {
+ $self->{warnings} = [@_];
+ } else {
+ return $self->{warnings};
+ }
+}
+
+sub use_pk {
+ my $self = shift;
+
+ if (@_) {
+ $self->{use_pk} = $_[0];
+ }
+
+ return $self->{use_pk};
+}
+
sub accnofrom {
my $self = shift;
$::lxdebug->leave_sub;
}
-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;
-}
-
sub get_datev_stamm {
return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
}
sub export {
my ($self) = @_;
- 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;
+ return $self->csv_export;
}
-sub kne_export {
+sub csv_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;
+
+ $self->generate_datev_data(from_to => $self->fromto);
+ return if $self->errors;
+
+ my $datev_csv = SL::DATEV::CSV->new(
+ datev_lines => $self->generate_datev_lines,
+ from => $self->from,
+ to => $self->to,
+ locked => $self->locked,
+ );
+
+
+ my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+
+ 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();
+
+ # get encoding from defaults - use cp1252 if DATEV strict export is used
+ my $enc = ($::instance_conf->get_datev_export_format eq 'cp1252') ? 'cp1252' : 'utf-8';
+ my $csv_file = IO::File->new($self->export_path . '/' . $filename, ">:encoding($enc)") or die "Can't open: $!";
+
+ $csv->print($csv_file, $_) for @{ $datev_csv->header };
+ $csv->print($csv_file, $_) for @{ $datev_csv->lines };
+ $csv_file->close;
+ $self->{warnings} = $datev_csv->warnings;
+
+ # convert utf-8 to cp1252//translit if set
+ if ($::instance_conf->get_datev_export_format eq 'cp1252-translit') {
+
+ my $filename_translit = "EXTF_DATEV_kivitendo_translit" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+ open my $fh_in, '<:encoding(UTF-8)', $self->export_path . '/' . $filename or die "could not open $filename for reading: $!";
+ open my $fh_out, '>', $self->export_path . '/' . $filename_translit or die "could not open $filename_translit for writing: $!";
+
+ my $converter = SL::Iconv->new("utf-8", "cp1252//translit");
+
+ print $fh_out $converter->convert($_) while <$fh_in>;
+ close $fh_in;
+ close $fh_out;
+
+ unlink $self->export_path . '/' . $filename or warn "Could not unlink $filename: $!";
+ $filename = $filename_translit;
+ }
+
+ return { download_token => $self->download_token, filenames => $filename };
+
} else {
die 'unrecognized exporttype';
}
return $result;
}
-sub csv_export {
- die 'not yet implemented';
-}
-
-sub obe_export {
- die 'not yet implemented';
-}
-
sub fromto {
my ($self) = @_;
$_[0] <=> 0;
}
+sub locked {
+ my $self = shift;
+
+ if (@_) {
+ $self->{locked} = $_[0];
+ }
+ return $self->{locked};
+}
+sub imported {
+ my $self = shift;
+
+ if (@_) {
+ $self->{imported} = $_[0];
+ }
+ return $self->{imported};
+}
+
sub generate_datev_data {
$main::lxdebug->enter_sub();
$gl_department_id_filter = " AND gl.department_id = ? ";
}
+ my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter);
+ if ( $form->{gldatefrom} ) {
+ $gl_itime_filter = " AND gl.itime >= ? ";
+ $ar_itime_filter = " AND ar.itime >= ? ";
+ $ap_itime_filter = " AND ap.itime >= ? ";
+ } else {
+ $gl_itime_filter = "";
+ $ar_itime_filter = "";
+ $ap_itime_filter = "";
+ }
+
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
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 $ar_accno = "c.accno";
+ my $ap_accno = "c.accno";
+ if ( $self->use_pk ) {
+ $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
+ $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber ELSE c.accno END as accno";
+ }
+ my $gl_imported;
+ if ( !$self->imported ) {
+ $gl_imported = " AND NOT imported";
+ }
+
my $query =
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,
+ ar.invnumber, ar.duedate, ar.amount as umsatz, COALESCE(ar.tax_point, ar.deliverydate) AS 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_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.department_id,
- ar.notes
+ ar.notes,
+ project.projectnumber as projectnumber, project.description as projectdescription,
+ department.description as departmentdescription
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)
+ LEFT JOIN department ON (department.id = ar.department_id)
+ LEFT JOIN project ON (project.id = ar.globalproject_id)
WHERE (ar.id IS NOT NULL)
AND $fromto
$trans_id_filter
+ $ar_itime_filter
$ar_department_id_filter
$filter
UNION ALL
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,
+ ap.invnumber, ap.duedate, ap.amount as umsatz, COALESCE(ap.tax_point, ap.deliverydate) AS 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_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.department_id,
- ap.notes
+ ap.notes,
+ project.projectnumber as projectnumber, project.description as projectdescription,
+ department.description as departmentdescription
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)
+ LEFT JOIN department ON (department.id = ap.department_id)
+ LEFT JOIN project ON (project.id = ap.globalproject_id)
WHERE (ap.id IS NOT NULL)
AND $fromto
$trans_id_filter
+ $ap_itime_filter
$ap_department_id_filter
$filter
UNION ALL
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.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, COALESCE(gl.tax_point, gl.deliverydate) 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,
'gl' as table,
tc.accno AS tax_accno, tc.description AS tax_accname,
gl.department_id,
- gl.notes
+ gl.notes,
+ '' as projectnumber, '' as projectdescription,
+ department.description as departmentdescription
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)
+ LEFT JOIN department ON (department.id = gl.department_id)
WHERE (gl.id IS NOT NULL)
AND $fromto
$trans_id_filter
+ $gl_itime_filter
$gl_department_id_filter
+ $gl_imported
+ AND NOT EXISTS (SELECT gl_id from ap_gl where gl_id = gl.id)
$filter
ORDER BY trans_id, acc_trans_id|;
my @query_args;
- if ( $form->{department_id} ) {
- push(@query_args, ($form->{department_id}) x 3);
+ if ( $form->{gldatefrom} or $form->{department_id} ) {
+
+ for ( 1 .. 3 ) {
+ if ( $form->{gldatefrom} ) {
+ my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom});
+ die "illegal data" unless ref($glfromdate) eq 'DateTime';
+ push(@query_args, $glfromdate);
+ }
+ if ( $form->{department_id} ) {
+ push(@query_args, $form->{department_id});
+ }
+ }
}
my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args);
$::lxdebug->leave_sub;
}
-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 ($day =~ /^0/) {
- $day = substr($day, 1, 1);
- }
- 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 generate_datev_lines {
my ($self) = @_;
}
if ($transaction->[$i]->{'taxkey'}) {
$taxkey = $transaction->[$i]->{'taxkey'};
+ # $taxkey = 0 if $taxkey == 94; # taxbookings are in gl
}
if ($transaction->[$i]->{'charttax'}) {
$charttax = $transaction->[$i]->{'charttax'};
if ($trans_lines >= 2) {
+ # Personenkontenerweiterung: accno has already been replaced if use_pk was set
$datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
$datev_data{'konto'} = $transaction->[$soll]->{'accno'};
if ($transaction->[$haben]->{'invnumber'} ne "") {
}
$datev_data{datum} = $transaction->[$haben]->{'transdate'};
$datev_data{waehrung} = 'EUR';
+ $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'};
+ $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'};
if ($transaction->[$haben]->{'name'} ne "") {
$datev_data{buchungstext} = $transaction->[$haben]->{'name'};
}
if (($transaction->[$haben]->{'ustid'} // '') ne "") {
- $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
+ $datev_data{ustid} = SL::VATIDNr->normalize($transaction->[$haben]->{'ustid'});
}
if (($transaction->[$haben]->{'duedate'} // '') ne "") {
$datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
}
- }
+ # if deliverydate exists, add it to datev export if it is
+ # * an ar/ap booking that is not a payment
+ # * a gl booking
+ if ( ($transaction->[$haben]->{'deliverydate'} // '') ne ''
+ && (
+ ( $transaction->[$haben]->{'table'} =~ /^(ar|ap)$/
+ && $transaction->[$haben]->{'link'} !~ m/_paid/
+ && $transaction->[$soll]->{'link'} !~ m/_paid/
+ )
+ || $transaction->[$haben]->{'table'} eq 'gl'
+ )
+ ) {
+ $datev_data{leistungsdatum} = $transaction->[$haben]->{'deliverydate'};
+ }
+ }
$datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
# Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
# $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
$datev_data{buchungsschluessel} = $taxkey;
}
+ # set lock for each transaction
+ $datev_data{locked} = $self->locked;
- push(@datev_lines, \%datev_data);
+ push(@datev_lines, \%datev_data) if $datev_data{umsatz};
}
# example of modifying export data:
return \@datev_lines;
}
-
-sub kne_buchungsexport {
- $main::lxdebug->enter_sub();
-
+sub check_vcnumbers_are_valid_pk_numbers {
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 };
+ # better use a class variable and set this in sub new (also needed in DATEV::CSV)
+ # calculation is also a bit more sane in sub check_valid_length_of_accounts
+ my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
+ my $pk_length = $length_of_accounts + 1;
+ my $query = <<"SQL";
+ SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
+ UNION
+ SELECT vendornumber AS vcnumber FROM vendor WHERE vendornumber !~ '^[[:digit:]]{$pk_length}\$'
+ LIMIT 1;
+SQL
+ my ($has_non_pk_accounts) = selectrow_query($::form, SL::DB->client->dbh, $query);
+ return defined $has_non_pk_accounts ? 0 : 1;
}
-sub _format_accno {
- my ($accno) = @_;
- return $accno . ('0' x (6 - min(length($accno), 6)));
-}
-sub csv_export_for_tax_accountant {
+sub check_valid_length_of_accounts {
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('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 ]);
- }
+ my $query = <<"SQL";
+ SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
+SQL
- $_->{out}->close for values %csvs;
+ my $accno_length = selectall_hashref_query($::form, SL::DB->client->dbh, $query);
+ if (1 < scalar @$accno_length) {
+ $::form->error(t8("Invalid combination of ledger account number length." .
+ " Mismatch length of #1 with length of #2. Please check your account settings. ",
+ $accno_length->[0]->{char_length}, $accno_length->[1]->{char_length}));
}
-
- $self->add_filenames(@filenames);
-
- return { download_token => $self->download_token, filenames => \@filenames };
+ return 1;
}
sub DESTROY {
=item errors
-Returns a list of errors that occured. If no errors occured, the export was a success.
+Returns a list of errors that occurred. If no errors occurred, the export was a success.
=item export
# ]
# };
+
+=item check_vcnumbers_are_valid_pk_numbers
+
+Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
+
+Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
+(e.g. 5), and checks the database for any customers or vendors whose customer-
+or vendornumber doesn't consist of only numbers with exactly that length. E.g.
+for a chart length of four "10001" would be ok, but not "10001b" or "1000".
+
+All vcnumbers are checked, obsolete customers or vendors aren't exempt.
+
+There is also no check for the typical customer range 10000-69999 and the
+typical vendor range 70000-99999.
+
+=item check_valid_length_of_accounts
+
+Returns 1 if all currently booked accounts have only one common number length domain (e.g. 4 or 6).
+Will throw an error if more than one distinct size is detected.
+The error message gives a short hint with the value of the (at least)
+two mismatching number length domains.
+
=back
=head1 ATTRIBUTES
Set boundary account numbers for the export. Only useful for a stammdaten export.
+=item locked
+
+Boolean if the transactions are locked (read-only in kivitenod) or not.
+Default value is false
+
=back
=head1 CONSTANTS
=head1 SEE ALSO
L<SL::DATEV::KNEFile>
+L<SL::DATEV::CSV>
=head1 AUTHORS
projects / kivitendo-erp.git / blobdiff