Revision 00b6dc22
Von Sven Schöling vor etwa 8 Jahren hinzugefügt
| 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; |
Auch abrufbar als: Unified diff
GDPDU: DATEV-ähnlicher Buchungsexport Rohversion