Revision 00b6dc22
Von Sven Schöling vor etwa 8 Jahren hinzugefügt
| SL/Controller/Gdpdu.pm | ||
|---|---|---|
| 12 | 12 |
use SL::Helper::Flash; |
| 13 | 13 |
|
| 14 | 14 |
use Rose::Object::MakeMethods::Generic ( |
| 15 |
'scalar --get_set_init' => [ qw(from to tables) ],
|
|
| 15 |
'scalar --get_set_init' => [ qw(from to) ], |
|
| 16 | 16 |
); |
| 17 | 17 |
|
| 18 | 18 |
__PACKAGE__->run_before('check_auth');
|
| ... | ... | |
| 39 | 39 |
location => $::instance_conf->get_address, |
| 40 | 40 |
from => $self->from, |
| 41 | 41 |
to => $self->to, |
| 42 |
tables => $self->tables, |
|
| 43 |
all_tables => !@{ $self->tables } && $::form->{all_tables},
|
|
| 42 |
all_tables => $::form->{all_tables},
|
|
| 44 | 43 |
); |
| 45 | 44 |
|
| 46 | 45 |
my $filename = $gdpdu->generate_export; |
| ... | ... | |
| 57 | 56 |
|
| 58 | 57 |
my $error = 0; |
| 59 | 58 |
|
| 60 |
if ($::form->{tables}) {
|
|
| 61 |
$self->tables([ keys %{ $::form->{tables} } ]);
|
|
| 62 |
# theese three get inferred |
|
| 63 |
push @{ $self->tables }, 'invoice' if $::form->{tables}{ar} || $::form->{tables}{ap};
|
|
| 64 |
push @{ $self->tables }, 'orderitems' if $::form->{tables}{oe};
|
|
| 65 |
push @{ $self->tables }, 'delivery_order_items' if $::form->{tables}{delivery_orders};
|
|
| 66 |
} |
|
| 67 |
|
|
| 68 |
if (!@{ $self->tables } && !$::form->{all_tables}) {
|
|
| 69 |
flash('error', t8('No, I really do need checked tables to export.'));
|
|
| 70 |
$error = 1; |
|
| 71 |
} |
|
| 72 |
|
|
| 73 | 59 |
if (!$::form->{from}) {
|
| 74 | 60 |
my $epoch = DateTime->new(day => 1, month => 1, year => 1900); |
| 75 | 61 |
flash('info', t8('No start date given, setting to #1', $epoch->to_kivitendo));
|
| ... | ... | |
| 86 | 72 |
|
| 87 | 73 |
sub init_from { DateTime->from_kivitendo($::form->{from}) }
|
| 88 | 74 |
sub init_to { DateTime->from_kivitendo($::form->{to}) }
|
| 89 |
sub init_tables { [ ] }
|
|
| 90 | 75 |
|
| 91 | 76 |
1; |
| 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 |
} |
| SL/GDPDU.pm | ||
|---|---|---|
| 13 | 13 |
use Archive::Zip; |
| 14 | 14 |
use File::Temp (); |
| 15 | 15 |
use File::Spec (); |
| 16 |
use List::UtilsBy qw(partition_by); |
|
| 16 |
use List::MoreUtils qw(any); |
|
| 17 |
use List::UtilsBy qw(partition_by sort_by); |
|
| 17 | 18 |
|
| 18 | 19 |
use SL::DB::Helper::ALL; # since we work on meta data, we need everything |
| 19 | 20 |
use SL::DB::Helper::Mappings; |
| 20 | 21 |
use SL::Locale::String qw(t8); |
| 21 | 22 |
|
| 22 | 23 |
use Rose::Object::MakeMethods::Generic ( |
| 23 |
scalar => [ qw(from to tables writer company location) ],
|
|
| 24 |
'scalar --get_set_init' => [ qw(files tempfiles export_ids) ], |
|
| 24 |
scalar => [ qw(from to writer company location) ], |
|
| 25 |
'scalar --get_set_init' => [ qw(files tempfiles export_ids tables) ],
|
|
| 25 | 26 |
); |
| 26 | 27 |
|
| 27 | 28 |
# in this we find: |
| ... | ... | |
| 32 | 33 |
# keep: arrayref of columns that should be saved for further referencing |
| 33 | 34 |
# tables: arrayref with one column and one or many table.column references that were kept earlier |
| 34 | 35 |
my %known_tables = ( |
| 35 |
ar => { name => t8('Invoice'), description => t8('Sales Invoices and Accounts Receivables'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
|
| 36 |
ap => { name => t8('Purchase Invoice'), description => t8('Purchase Invoices and Accounts Payables'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
|
| 37 |
oe => { name => t8('Orders'), description => t8('Orders and Quotations, Sales and Purchase'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
|
| 38 |
delivery_orders => { name => t8('Delivery Orders'), description => t8('Delivery Orders'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
|
| 39 |
gl => { name => t8('General Ledger'), description => t8('General Ledger Entries'), keep => [ qw(id) ], transdate => 'transdate', },
|
|
| 40 |
invoice => { name => t8('Invoice Positions'), description => t8('Positions for all Invoices'), keep => [ qw(parts_id) ], tables => [ trans_id => "ar.id", "ap.id" ] },
|
|
| 41 |
orderitems => { name => t8('OrderItems'), description => t8('Positions for all Orders'), keep => [ qw(parts_id) ], tables => [ trans_id => "oe.id" ] },
|
|
| 42 |
delivery_order_items => { name => t8('Delivery Order Items'), description => t8('Positions for all Delivery Orders'), keep => [ qw(parts_id) ], tables => [ delivery_order_id => "delivery_orders.id" ] },
|
|
| 43 |
acc_trans => { name => t8('Transactions'), description => t8('All general ledger entries'), keep => [ qw(chart_id) ], tables => [ trans_id => "ar.id", "ap.id", "oe.id", "delivery_orders.id", "gl.id" ] },
|
|
| 44 |
chart => { name => t8('Charts'), description => t8('Chart of Accounts'), tables => [ id => "acc_trans.chart_id" ] },
|
|
| 45 |
customer => { name => t8('Customers'), description => t8('Customer Master Data'), tables => [ id => "ar.customer_id", "ap.customer_id", "oe.customer_id", "delivery_orders.customer_id" ] },
|
|
| 46 |
vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), tables => [ id => "ar.vendor_id", "ap.vendor_id", "oe.vendor_id", "delivery_orders.vendor_id" ] },
|
|
| 47 |
parts => { name => t8('Parts'), description => t8('Parts, Services, and Assemblies'), tables => [ id => "invoice.parts_id", "orderitems.parts_id", "delivery_order_items.parts_id" ] },
|
|
| 36 |
chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno' },
|
|
| 37 |
customer => { name => t8('Customers'), description => t8('Customer Master Data'), },
|
|
| 38 |
vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), },
|
|
| 39 |
); |
|
| 40 |
|
|
| 41 |
my %datev_column_defs = ( |
|
| 42 |
acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), primary_key => 1 },
|
|
| 43 |
amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
|
|
| 44 |
credit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account Name'), },
|
|
| 45 |
credit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account'), },
|
|
| 46 |
debit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account Name'), },
|
|
| 47 |
debit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account'), },
|
|
| 48 |
invnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Reference'), },
|
|
| 49 |
name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), },
|
|
| 50 |
notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), },
|
|
| 51 |
tax => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax'), },
|
|
| 52 |
taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
|
|
| 53 |
tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
|
|
| 54 |
tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
|
|
| 55 |
transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), },
|
|
| 56 |
vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
|
|
| 57 |
customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer ID'), },
|
|
| 58 |
vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor ID'), },
|
|
| 59 |
); |
|
| 60 |
|
|
| 61 |
my @datev_columns = qw( |
|
| 62 |
acc_trans_id |
|
| 63 |
customer_id vendor_id |
|
| 64 |
name vcnumber |
|
| 65 |
transdate invnumber amount |
|
| 66 |
debit_accno debit_accname |
|
| 67 |
credit_accno credit_accname |
|
| 68 |
tax |
|
| 69 |
tax_accno tax_accname taxkey |
|
| 70 |
notes |
|
| 48 | 71 |
); |
| 49 | 72 |
|
| 50 | 73 |
# rows in this listing are tiers. |
| ... | ... | |
| 108 | 131 |
$self->do_csv_export($_); |
| 109 | 132 |
} |
| 110 | 133 |
|
| 134 |
$self->do_datev_csv_export; |
|
| 135 |
|
|
| 111 | 136 |
# write xml file |
| 112 | 137 |
$self->do_xml_file; |
| 113 | 138 |
|
| ... | ... | |
| 157 | 182 |
for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
|
| 158 | 183 |
->table($_) |
| 159 | 184 |
} |
| 185 |
$self->do_datev_xml_table; |
|
| 160 | 186 |
}) |
| 161 | 187 |
}); |
| 162 | 188 |
close($fh); |
| ... | ... | |
| 194 | 220 |
my $package = SL::DB::Helper::Mappings::get_package_for_table($table); |
| 195 | 221 |
|
| 196 | 222 |
# PrimaryKeys must come before regular columns, so partition first |
| 197 |
partition_by { 1 * $_->is_primary_key_member } $package->meta->columns;
|
|
| 223 |
partition_by {
|
|
| 224 |
$known_tables{$table}{primary_key}
|
|
| 225 |
? 1 * ($_ eq $known_tables{$table}{primary_key})
|
|
| 226 |
: 1 * $_->is_primary_key_member |
|
| 227 |
} $package->meta->columns; |
|
| 198 | 228 |
} |
| 199 | 229 |
|
| 200 | 230 |
sub columns {
|
| ... | ... | |
| 255 | 285 |
} |
| 256 | 286 |
} |
| 257 | 287 |
|
| 288 |
sub do_datev_xml_table {
|
|
| 289 |
my ($self) = @_; |
|
| 290 |
my $writer = $self->writer; |
|
| 291 |
|
|
| 292 |
$self->tag('Table', sub { $self
|
|
| 293 |
->tag('URL', "transaction.csv")
|
|
| 294 |
->tag('Name', t8('Transactions'))
|
|
| 295 |
->tag('Description', t8('Transactions'))
|
|
| 296 |
->tag('Validity', sub { $self
|
|
| 297 |
->tag('Range', sub { $self
|
|
| 298 |
->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
|
|
| 299 |
->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
|
|
| 300 |
}) |
|
| 301 |
->tag('Format', $date_format)
|
|
| 302 |
}) |
|
| 303 |
->tag('UTF8')
|
|
| 304 |
->tag('DecimalSymbol', '.')
|
|
| 305 |
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
|
|
| 306 |
->tag('VariableLength', sub { $self
|
|
| 307 |
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
|
|
| 308 |
->tag('TextEncapsulator', '"')
|
|
| 309 |
->datev_columns |
|
| 310 |
->datev_foreign_keys |
|
| 311 |
}) |
|
| 312 |
}); |
|
| 313 |
} |
|
| 314 |
|
|
| 315 |
sub datev_columns {
|
|
| 316 |
my ($self, $table) = @_; |
|
| 317 |
|
|
| 318 |
my %cols_by_primary_key = partition_by { $datev_column_defs{$_}{primary_key} } @datev_columns;
|
|
| 319 |
$::lxdebug->dump(0, "cols", \%cols_by_primary_key); |
|
| 320 |
|
|
| 321 |
for my $column (@{ $cols_by_primary_key{1} }) {
|
|
| 322 |
my $type = $column_types{ $datev_column_defs{$column}{type} };
|
|
| 323 |
|
|
| 324 |
die "unknown col type @{[ $column ]}" unless $type;
|
|
| 325 |
|
|
| 326 |
$self->tag('VariablePrimaryKey', sub { $self
|
|
| 327 |
->tag('Name', $column);
|
|
| 328 |
$type->($self); |
|
| 329 |
}) |
|
| 330 |
} |
|
| 331 |
|
|
| 332 |
for my $column (@{ $cols_by_primary_key{''} }) {
|
|
| 333 |
my $type = $column_types{ $datev_column_defs{$column}{type} };
|
|
| 334 |
|
|
| 335 |
die "unknown col type @{[ ref $column]}" unless $type;
|
|
| 336 |
|
|
| 337 |
$self->tag('VariableColumn', sub { $self
|
|
| 338 |
->tag('Name', $column);
|
|
| 339 |
$type->($self); |
|
| 340 |
}) |
|
| 341 |
} |
|
| 342 |
|
|
| 343 |
$self; |
|
| 344 |
} |
|
| 345 |
|
|
| 346 |
sub datev_foreign_keys {
|
|
| 347 |
my ($self) = @_; |
|
| 348 |
# hard code weeee |
|
| 349 |
$self->tag('ForeignKey', sub { $_[0]
|
|
| 350 |
->tag('Name', 'customer_id')
|
|
| 351 |
->tag('References', 'customer')
|
|
| 352 |
}); |
|
| 353 |
$self->tag('ForeignKey', sub { $_[0]
|
|
| 354 |
->tag('Name', 'vendor_id')
|
|
| 355 |
->tag('References', 'vendor')
|
|
| 356 |
}); |
|
| 357 |
$self->tag('ForeignKey', sub { $_[0]
|
|
| 358 |
->tag('Name', $_)
|
|
| 359 |
->tag('References', 'chart')
|
|
| 360 |
}) for qw(debit_accno credit_accno tax_accno); |
|
| 361 |
} |
|
| 362 |
|
|
| 363 |
sub do_datev_csv_export {
|
|
| 364 |
my ($self) = @_; |
|
| 365 |
|
|
| 366 |
my $datev = SL::DATEV->new(from => $self->from, to => $self->to); |
|
| 367 |
|
|
| 368 |
$datev->_get_transactions(from_to => $datev->fromto); |
|
| 369 |
|
|
| 370 |
for my $transaction (@{ $datev->{DATEV} }) {
|
|
| 371 |
for my $entry (@{ $transaction }) {
|
|
| 372 |
$entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
|
|
| 373 |
} |
|
| 374 |
} |
|
| 375 |
|
|
| 376 |
my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
|
|
| 377 |
|
|
| 378 |
my $csv = Text::CSV_XS->new({
|
|
| 379 |
binary => 1, |
|
| 380 |
eol => "\n", |
|
| 381 |
sep_char => ";", |
|
| 382 |
}); |
|
| 383 |
|
|
| 384 |
my ($fh, $filename) = File::Temp::tempfile(); |
|
| 385 |
binmode($fh, ':utf8'); |
|
| 386 |
|
|
| 387 |
$self->files->{"transactions.csv"} = $filename;
|
|
| 388 |
push @{ $self->tempfiles }, $filename;
|
|
| 389 |
|
|
| 390 |
for my $transaction (@transactions) {
|
|
| 391 |
my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
|
|
| 392 |
|
|
| 393 |
my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
|
|
| 394 |
my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
|
|
| 395 |
my $amount = defined($soll->{net_amount}) ? $soll : $haben;
|
|
| 396 |
$haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
|
|
| 397 |
$haben->{notes} //= '';
|
|
| 398 |
$haben->{notes} = SL::HTML::Util->strip($haben->{notes});
|
|
| 399 |
$haben->{notes} =~ s{\r}{}g;
|
|
| 400 |
$haben->{notes} =~ s{\n+}{ }g;
|
|
| 401 |
|
|
| 402 |
my %row = ( |
|
| 403 |
customer_id => $soll->{customer_id} || $haben->{customer_id},
|
|
| 404 |
vendor_id => $soll->{vendor_id} || $haben->{vendor_id},
|
|
| 405 |
amount => abs($amount->{amount}),
|
|
| 406 |
debit_accno => $soll->{accno},
|
|
| 407 |
debit_accname => $soll->{accname},
|
|
| 408 |
credit_accno => $haben->{accno},
|
|
| 409 |
credit_accname => $haben->{accname},
|
|
| 410 |
tax => abs($amount->{amount}) - abs($amount->{net_amount}),
|
|
| 411 |
notes => $haben->{notes},
|
|
| 412 |
(map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
|
|
| 413 |
(map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
|
|
| 414 |
); |
|
| 415 |
|
|
| 416 |
$csv->print($fh, [ map { $row{$_} } @datev_columns ]);
|
|
| 417 |
} |
|
| 418 |
|
|
| 419 |
# and build xml spec for it |
|
| 420 |
} |
|
| 421 |
|
|
| 258 | 422 |
sub do_csv_export {
|
| 259 | 423 |
my ($self, $table) = @_; |
| 260 | 424 |
|
| ... | ... | |
| 382 | 546 |
sub init_files { +{} }
|
| 383 | 547 |
sub init_export_ids { +{} }
|
| 384 | 548 |
sub init_tempfiles { [] }
|
| 549 |
sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
|
|
| 385 | 550 |
|
| 386 | 551 |
sub API_VERSION {
|
| 387 | 552 |
DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo; |
| locale/de/all | ||
|---|---|---|
| 717 | 717 |
'Credit' => 'Haben', |
| 718 | 718 |
'Credit (one letter abbreviation)' => 'H', |
| 719 | 719 |
'Credit Account' => 'Habenkonto', |
| 720 |
'Credit Account Name' => 'Haben-Kontoname', |
|
| 720 | 721 |
'Credit Limit' => 'Kreditlimit', |
| 721 | 722 |
'Credit Limit exceeded!!!' => 'Kreditlimit überschritten!', |
| 722 | 723 |
'Credit Note' => 'Gutschrift', |
| ... | ... | |
| 832 | 833 |
'Debit' => 'Soll', |
| 833 | 834 |
'Debit (one letter abbreviation)' => 'S', |
| 834 | 835 |
'Debit Account' => 'Sollkonto', |
| 836 |
'Debit Account Name' => 'Soll-Kontoname', |
|
| 835 | 837 |
'Debit Starting Balance' => 'EB Passiva', |
| 836 | 838 |
'Debit Tax' => 'Vorsteuer', |
| 837 | 839 |
'Debit Tax Account' => 'Vorsteuerkonto', |
| ... | ... | |
| 1270 | 1272 |
'Export date' => 'Exportdatum', |
| 1271 | 1273 |
'Export date from' => 'Exportdatum von', |
| 1272 | 1274 |
'Export date to' => 'Exportdatum bis', |
| 1275 |
'Export for tax accountant' => 'Export für Steuerberater', |
|
| 1273 | 1276 |
'Extend automatically by n months' => 'Automatische Verlängerung um x Monate', |
| 1274 | 1277 |
'Extended' => 'Gesamt', |
| 1275 | 1278 |
'Extended status' => 'Erweiterter Status', |
| ... | ... | |
| 2716 | 2719 |
'Task server control' => 'Task-Server-Steuerung', |
| 2717 | 2720 |
'Task server status' => 'Task-Server-Status', |
| 2718 | 2721 |
'Tax' => 'Steuer', |
| 2722 |
'Tax Account' => 'Steuerkonto', |
|
| 2723 |
'Tax Account Name' => 'Steuerkontoname', |
|
| 2719 | 2724 |
'Tax Consultant' => 'Steuerberater/-in', |
| 2720 | 2725 |
'Tax ID number' => 'UStID-Nummer', |
| 2721 | 2726 |
'Tax Included' => 'Steuer im Preis inbegriffen', |
| templates/webpages/gdpdu/filter.html | ||
|---|---|---|
| 20 | 20 |
<td>[% 'To Date' | $T8 %]</td> |
| 21 | 21 |
<td>[% L.date_tag('to', SELF.to) %]</td>
|
| 22 | 22 |
</tr> |
| 23 |
<tr> |
|
| 24 |
<td>[% 'Include in Report' | $T8 %]</td> |
|
| 25 |
<td> |
|
| 26 |
[% L.checkbox_tag('tables.ar', label=LxERP.t8('Invoices'), checked=1) %]
|
|
| 27 |
[% L.checkbox_tag('tables.ap', label=LxERP.t8('Purchase Invoices'), checked=1) %]
|
|
| 28 |
[% L.checkbox_tag('tables.gl', label=LxERP.t8('GL Transactions'), checked=1) %]
|
|
| 29 |
[% L.checkbox_tag('tables.delivery_orders', label=LxERP.t8('Delivery Orders'), checked=1) %]
|
|
| 30 |
[% L.checkbox_tag('tables.oe', label=LxERP.t8('Quotations and orders'), checked=1) %]
|
|
| 31 |
[% L.checkbox_tag('tables.customer', label=LxERP.t8('Customers'), checked=1) %]
|
|
| 32 |
[% L.checkbox_tag('tables.vendor', label=LxERP.t8('Vendors'), checked=1) %]
|
|
| 33 |
[% L.checkbox_tag('tables.parts', label=LxERP.t8('Parts'), checked=1) %]
|
|
| 34 |
[% L.checkbox_tag('tables.acc_trans', label=LxERP.t8('Transactions'), checked=1) %]
|
|
| 35 |
[% L.checkbox_tag('tables.chart', label=LxERP.t8('Charts'), checked=1) %]
|
|
| 36 |
</td> |
|
| 37 |
</tr> |
|
| 38 | 23 |
</table> |
| 39 | 24 |
|
| 40 | 25 |
[% L.hidden_tag('action', 'Gdpdu/dispatch') %]
|
Auch abrufbar als: Unified diff
GDPDU: DATEV-ähnlicher Buchungsexport Rohversion