Revision 00b6dc22
Von Sven Schöling vor etwa 8 Jahren hinzugefügt
| SL/DATEV.pm | ||
|---|---|---|
| 32 | 32 |
use SL::DBUtils; |
| 33 | 33 |
use SL::DATEV::KNEFile; |
| 34 | 34 |
use SL::DB; |
| 35 |
use SL::HTML::Util (); |
|
| 35 | 36 |
|
| 36 | 37 |
use Data::Dumper; |
| 37 | 38 |
use DateTime; |
| 38 | 39 |
use Exporter qw(import); |
| 39 | 40 |
use File::Path; |
| 40 |
use List::Util qw(max sum); |
|
| 41 |
use IO::File; |
|
| 42 |
use List::MoreUtils qw(any); |
|
| 43 |
use List::Util qw(min max sum); |
|
| 44 |
use List::UtilsBy qw(partition_by sort_by); |
|
| 45 |
use Text::CSV_XS; |
|
| 41 | 46 |
use Time::HiRes qw(gettimeofday); |
| 42 | 47 |
|
| 43 | 48 |
{
|
| ... | ... | |
| 45 | 50 |
use constant {
|
| 46 | 51 |
DATEV_ET_BUCHUNGEN => $i++, |
| 47 | 52 |
DATEV_ET_STAMM => $i++, |
| 53 |
DATEV_ET_CSV => $i++, |
|
| 48 | 54 |
|
| 49 | 55 |
DATEV_FORMAT_KNE => $i++, |
| 50 | 56 |
DATEV_FORMAT_OBE => $i++, |
| 51 | 57 |
}; |
| 52 | 58 |
} |
| 53 | 59 |
|
| 54 |
my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_FORMAT_KNE DATEV_FORMAT_OBE); |
|
| 60 |
my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE);
|
|
| 55 | 61 |
our @EXPORT_OK = (@export_constants); |
| 56 | 62 |
our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]); |
| 57 | 63 |
|
| ... | ... | |
| 324 | 330 |
$result = $self->kne_buchungsexport; |
| 325 | 331 |
} elsif ($self->exporttype == DATEV_ET_STAMM) {
|
| 326 | 332 |
$result = $self->kne_stammdatenexport; |
| 333 |
} elsif ($self->exporttype == DATEV_ET_CSV) {
|
|
| 334 |
$result = $self->csv_export_for_tax_accountant; |
|
| 327 | 335 |
} else {
|
| 328 | 336 |
die 'unrecognized exporttype'; |
| 329 | 337 |
} |
| ... | ... | |
| 349 | 357 |
|
| 350 | 358 |
sub _get_transactions {
|
| 351 | 359 |
$main::lxdebug->enter_sub(); |
| 352 |
my $self = shift; |
|
| 353 |
my $fromto = shift; |
|
| 354 |
my $progress_callback = shift || sub {};
|
|
| 360 |
|
|
| 361 |
my ($self, %params) = @_; |
|
| 362 |
my $fromto = $params{from_to};
|
|
| 363 |
my $progress_callback = $params{progress_callback} || sub {};
|
|
| 355 | 364 |
|
| 356 | 365 |
my $form = $main::form; |
| 357 | 366 |
|
| ... | ... | |
| 374 | 383 |
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'); |
| 375 | 384 |
|
| 376 | 385 |
my $query = |
| 377 |
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, |
|
| 386 |
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
|
|
| 378 | 387 |
ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, |
| 379 |
ct.name, ct.ustid, |
|
| 380 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, |
|
| 388 |
ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
|
|
| 389 |
c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
|
| 381 | 390 |
ar.invoice, |
| 382 | 391 |
t.rate AS taxrate, |
| 383 | 392 |
'ar' as table |
| 393 |
tc.accno AS tax_accno, tc.description AS tax_accname, |
|
| 394 |
ar.notes |
|
| 384 | 395 |
FROM acc_trans ac |
| 385 | 396 |
LEFT JOIN ar ON (ac.trans_id = ar.id) |
| 386 | 397 |
LEFT JOIN customer ct ON (ar.customer_id = ct.id) |
| 387 | 398 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
| 388 | 399 |
LEFT JOIN tax t ON (ac.tax_id = t.id) |
| 400 |
LEFT JOIN chart tc ON (t.chart_id = tc.id) |
|
| 389 | 401 |
WHERE (ar.id IS NOT NULL) |
| 390 | 402 |
AND $fromto |
| 391 | 403 |
$trans_id_filter |
| ... | ... | |
| 393 | 405 |
|
| 394 | 406 |
UNION ALL |
| 395 | 407 |
|
| 396 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, |
|
| 408 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
|
|
| 397 | 409 |
ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, |
| 398 |
ct.name,ct.ustid,
|
|
| 399 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, |
|
| 410 |
ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
|
|
| 411 |
c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
|
| 400 | 412 |
ap.invoice, |
| 401 | 413 |
t.rate AS taxrate, |
| 402 | 414 |
'ap' as table |
| 415 |
tc.accno AS tax_accno, tc.description AS tax_accname, |
|
| 416 |
ap.notes |
|
| 403 | 417 |
FROM acc_trans ac |
| 404 | 418 |
LEFT JOIN ap ON (ac.trans_id = ap.id) |
| 405 | 419 |
LEFT JOIN vendor ct ON (ap.vendor_id = ct.id) |
| 406 | 420 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
| 407 | 421 |
LEFT JOIN tax t ON (ac.tax_id = t.id) |
| 422 |
LEFT JOIN chart tc ON (t.chart_id = tc.id) |
|
| 408 | 423 |
WHERE (ap.id IS NOT NULL) |
| 409 | 424 |
AND $fromto |
| 410 | 425 |
$trans_id_filter |
| ... | ... | |
| 412 | 427 |
|
| 413 | 428 |
UNION ALL |
| 414 | 429 |
|
| 415 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, |
|
| 430 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
|
|
| 416 | 431 |
gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, |
| 417 |
gl.description AS name, NULL as ustid, |
|
| 418 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, |
|
| 432 |
gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
|
|
| 433 |
c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
|
| 419 | 434 |
FALSE AS invoice, |
| 420 | 435 |
t.rate AS taxrate, |
| 421 | 436 |
'gl' as table |
| 437 |
tc.accno AS tax_accno, tc.description AS tax_accname, |
|
| 438 |
gl.notes |
|
| 422 | 439 |
FROM acc_trans ac |
| 423 | 440 |
LEFT JOIN gl ON (ac.trans_id = gl.id) |
| 424 | 441 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
| 425 | 442 |
LEFT JOIN tax t ON (ac.tax_id = t.id) |
| 443 |
LEFT JOIN chart tc ON (t.chart_id = tc.id) |
|
| 426 | 444 |
WHERE (gl.id IS NOT NULL) |
| 427 | 445 |
AND $fromto |
| 428 | 446 |
$trans_id_filter |
| ... | ... | |
| 812 | 830 |
|
| 813 | 831 |
my $fromto = $self->fromto; |
| 814 | 832 |
|
| 815 |
$self->_get_transactions($fromto); |
|
| 833 |
$self->_get_transactions(from_to => $fromto);
|
|
| 816 | 834 |
|
| 817 | 835 |
return if $self->errors; |
| 818 | 836 |
|
| ... | ... | |
| 1082 | 1100 |
return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
|
| 1083 | 1101 |
} |
| 1084 | 1102 |
|
| 1103 |
sub _format_accno {
|
|
| 1104 |
my ($accno) = @_; |
|
| 1105 |
return $accno . ('0' x (6 - min(length($accno), 6)));
|
|
| 1106 |
} |
|
| 1107 |
|
|
| 1108 |
sub csv_export_for_tax_accountant {
|
|
| 1109 |
my ($self) = @_; |
|
| 1110 |
|
|
| 1111 |
$self->_get_transactions(from_to => $self->fromto); |
|
| 1112 |
|
|
| 1113 |
foreach my $transaction (@{ $self->{DATEV} }) {
|
|
| 1114 |
foreach my $entry (@{ $transaction }) {
|
|
| 1115 |
$entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
|
|
| 1116 |
} |
|
| 1117 |
} |
|
| 1118 |
|
|
| 1119 |
my %transactions = |
|
| 1120 |
partition_by { $_->[0]->{table} }
|
|
| 1121 |
sort_by { $_->[0]->{sortkey} }
|
|
| 1122 |
grep { 2 == scalar(@{ $_ }) }
|
|
| 1123 |
@{ $self->{DATEV} };
|
|
| 1124 |
|
|
| 1125 |
my %column_defs = ( |
|
| 1126 |
acc_trans_id => { 'text' => $::locale->text('ID'), },
|
|
| 1127 |
amount => { 'text' => $::locale->text('Amount'), },
|
|
| 1128 |
credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
|
|
| 1129 |
credit_accno => { 'text' => $::locale->text('Credit Account'), },
|
|
| 1130 |
debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
|
|
| 1131 |
debit_accno => { 'text' => $::locale->text('Debit Account'), },
|
|
| 1132 |
invnumber => { 'text' => $::locale->text('Reference'), },
|
|
| 1133 |
name => { 'text' => $::locale->text('Name'), },
|
|
| 1134 |
notes => { 'text' => $::locale->text('Notes'), },
|
|
| 1135 |
tax => { 'text' => $::locale->text('Tax'), },
|
|
| 1136 |
taxkey => { 'text' => $::locale->text('Taxkey'), },
|
|
| 1137 |
tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
|
|
| 1138 |
tax_accno => { 'text' => $::locale->text('Tax Account'), },
|
|
| 1139 |
transdate => { 'text' => $::locale->text('Invoice Date'), },
|
|
| 1140 |
vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
|
|
| 1141 |
); |
|
| 1142 |
|
|
| 1143 |
my @columns = qw( |
|
| 1144 |
acc_trans_id name vcnumber |
|
| 1145 |
transdate invnumber amount |
|
| 1146 |
debit_accno debit_accname |
|
| 1147 |
credit_accno credit_accname |
|
| 1148 |
tax |
|
| 1149 |
tax_accno tax_accname taxkey |
|
| 1150 |
notes |
|
| 1151 |
); |
|
| 1152 |
|
|
| 1153 |
my %filenames_by_type = ( |
|
| 1154 |
ar => $::locale->text('AR Transactions'),
|
|
| 1155 |
ap => $::locale->text('AP Transactions'),
|
|
| 1156 |
gl => $::locale->text('GL Transactions'),
|
|
| 1157 |
); |
|
| 1158 |
|
|
| 1159 |
my @filenames; |
|
| 1160 |
foreach my $type (qw(ap ar)) {
|
|
| 1161 |
my %csvs = ( |
|
| 1162 |
invoices => {
|
|
| 1163 |
content => '', |
|
| 1164 |
filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
|
|
| 1165 |
csv => Text::CSV_XS->new({
|
|
| 1166 |
binary => 1, |
|
| 1167 |
eol => "\n", |
|
| 1168 |
sep_char => ";", |
|
| 1169 |
}), |
|
| 1170 |
}, |
|
| 1171 |
payments => {
|
|
| 1172 |
content => '', |
|
| 1173 |
filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
|
|
| 1174 |
csv => Text::CSV_XS->new({
|
|
| 1175 |
binary => 1, |
|
| 1176 |
eol => "\n", |
|
| 1177 |
sep_char => ";", |
|
| 1178 |
}), |
|
| 1179 |
}, |
|
| 1180 |
); |
|
| 1181 |
|
|
| 1182 |
foreach my $csv (values %csvs) {
|
|
| 1183 |
$csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
|
|
| 1184 |
$csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
|
|
| 1185 |
|
|
| 1186 |
push @filenames, $csv->{filename};
|
|
| 1187 |
} |
|
| 1188 |
|
|
| 1189 |
foreach my $transaction (@{ $transactions{$type} }) {
|
|
| 1190 |
my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
|
|
| 1191 |
my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
|
|
| 1192 |
|
|
| 1193 |
my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
|
|
| 1194 |
my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
|
|
| 1195 |
my $amount = defined($soll->{net_amount}) ? $soll : $haben;
|
|
| 1196 |
$haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
|
|
| 1197 |
$haben->{notes} //= '';
|
|
| 1198 |
$haben->{notes} = SL::HTML::Util->strip($haben->{notes});
|
|
| 1199 |
$haben->{notes} =~ s{\r}{}g;
|
|
| 1200 |
$haben->{notes} =~ s{\n+}{ }g;
|
|
| 1201 |
|
|
| 1202 |
my %row = ( |
|
| 1203 |
amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
|
|
| 1204 |
debit_accno => _format_accno($soll->{accno}),
|
|
| 1205 |
debit_accname => $soll->{accname},
|
|
| 1206 |
credit_accno => _format_accno($haben->{accno}),
|
|
| 1207 |
credit_accname => $haben->{accname},
|
|
| 1208 |
tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
|
|
| 1209 |
notes => $haben->{notes},
|
|
| 1210 |
(map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
|
|
| 1211 |
(map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
|
|
| 1212 |
); |
|
| 1213 |
|
|
| 1214 |
$csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
|
|
| 1215 |
} |
|
| 1216 |
|
|
| 1217 |
$_->{out}->close for values %csvs;
|
|
| 1218 |
} |
|
| 1219 |
|
|
| 1220 |
$self->add_filenames(@filenames); |
|
| 1221 |
|
|
| 1222 |
return { download_token => $self->download_token, filenames => \@filenames };
|
|
| 1223 |
} |
|
| 1224 |
|
|
| 1085 | 1225 |
sub DESTROY {
|
| 1086 | 1226 |
clean_temporary_directories(); |
| 1087 | 1227 |
} |
Auch abrufbar als: Unified diff
GDPDU: DATEV-ähnlicher Buchungsexport Rohversion