Revision 00b6dc22
Von Sven Schöling vor mehr als 8 Jahren hinzugefügt
| SL/Controller/Gdpdu.pm | ||
|---|---|---|
|
use SL::Helper::Flash;
|
||
|
|
||
|
use Rose::Object::MakeMethods::Generic (
|
||
|
'scalar --get_set_init' => [ qw(from to tables) ],
|
||
|
'scalar --get_set_init' => [ qw(from to) ],
|
||
|
);
|
||
|
|
||
|
__PACKAGE__->run_before('check_auth');
|
||
| ... | ... | |
|
location => $::instance_conf->get_address,
|
||
|
from => $self->from,
|
||
|
to => $self->to,
|
||
|
tables => $self->tables,
|
||
|
all_tables => !@{ $self->tables } && $::form->{all_tables},
|
||
|
all_tables => $::form->{all_tables},
|
||
|
);
|
||
|
|
||
|
my $filename = $gdpdu->generate_export;
|
||
| ... | ... | |
|
|
||
|
my $error = 0;
|
||
|
|
||
|
if ($::form->{tables}) {
|
||
|
$self->tables([ keys %{ $::form->{tables} } ]);
|
||
|
# theese three get inferred
|
||
|
push @{ $self->tables }, 'invoice' if $::form->{tables}{ar} || $::form->{tables}{ap};
|
||
|
push @{ $self->tables }, 'orderitems' if $::form->{tables}{oe};
|
||
|
push @{ $self->tables }, 'delivery_order_items' if $::form->{tables}{delivery_orders};
|
||
|
}
|
||
|
|
||
|
if (!@{ $self->tables } && !$::form->{all_tables}) {
|
||
|
flash('error', t8('No, I really do need checked tables to export.'));
|
||
|
$error = 1;
|
||
|
}
|
||
|
|
||
|
if (!$::form->{from}) {
|
||
|
my $epoch = DateTime->new(day => 1, month => 1, year => 1900);
|
||
|
flash('info', t8('No start date given, setting to #1', $epoch->to_kivitendo));
|
||
| ... | ... | |
|
|
||
|
sub init_from { DateTime->from_kivitendo($::form->{from}) }
|
||
|
sub init_to { DateTime->from_kivitendo($::form->{to}) }
|
||
|
sub init_tables { [ ] }
|
||
|
|
||
|
1;
|
||
| SL/DATEV.pm | ||
|---|---|---|
|
use SL::DBUtils;
|
||
|
use SL::DATEV::KNEFile;
|
||
|
use SL::DB;
|
||
|
use SL::HTML::Util ();
|
||
|
|
||
|
use Data::Dumper;
|
||
|
use DateTime;
|
||
|
use Exporter qw(import);
|
||
|
use File::Path;
|
||
|
use List::Util qw(max sum);
|
||
|
use IO::File;
|
||
|
use List::MoreUtils qw(any);
|
||
|
use List::Util qw(min max sum);
|
||
|
use List::UtilsBy qw(partition_by sort_by);
|
||
|
use Text::CSV_XS;
|
||
|
use Time::HiRes qw(gettimeofday);
|
||
|
|
||
|
{
|
||
| ... | ... | |
|
use constant {
|
||
|
DATEV_ET_BUCHUNGEN => $i++,
|
||
|
DATEV_ET_STAMM => $i++,
|
||
|
DATEV_ET_CSV => $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);
|
||
|
my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE);
|
||
|
our @EXPORT_OK = (@export_constants);
|
||
|
our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
|
||
|
|
||
| ... | ... | |
|
$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;
|
||
|
} else {
|
||
|
die 'unrecognized exporttype';
|
||
|
}
|
||
| ... | ... | |
|
|
||
|
sub _get_transactions {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
my $self = shift;
|
||
|
my $fromto = shift;
|
||
|
my $progress_callback = shift || sub {};
|
||
|
|
||
|
my ($self, %params) = @_;
|
||
|
my $fromto = $params{from_to};
|
||
|
my $progress_callback = $params{progress_callback} || sub {};
|
||
|
|
||
|
my $form = $main::form;
|
||
|
|
||
| ... | ... | |
|
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 $query =
|
||
|
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
|
||
|
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
|
||
|
ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
|
||
|
ct.name, ct.ustid,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
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.invoice,
|
||
|
t.rate AS taxrate,
|
||
|
'ar' as table
|
||
|
tc.accno AS tax_accno, tc.description AS tax_accname,
|
||
|
ar.notes
|
||
|
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)
|
||
|
WHERE (ar.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
|
||
|
UNION ALL
|
||
|
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
|
||
|
ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
|
||
|
ct.name,ct.ustid,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
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.invoice,
|
||
|
t.rate AS taxrate,
|
||
|
'ap' as table
|
||
|
tc.accno AS tax_accno, tc.description AS tax_accname,
|
||
|
ap.notes
|
||
|
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)
|
||
|
WHERE (ap.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
|
||
|
UNION ALL
|
||
|
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
|
||
|
SELECT ac.acc_trans_id, ac.transdate, 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.description AS name, NULL as ustid,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
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,
|
||
|
t.rate AS taxrate,
|
||
|
'gl' as table
|
||
|
tc.accno AS tax_accno, tc.description AS tax_accname,
|
||
|
gl.notes
|
||
|
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)
|
||
|
WHERE (gl.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
|
||
|
my $fromto = $self->fromto;
|
||
|
|
||
|
$self->_get_transactions($fromto);
|
||
|
$self->_get_transactions(from_to => $fromto);
|
||
|
|
||
|
return if $self->errors;
|
||
|
|
||
| ... | ... | |
|
return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
|
||
|
}
|
||
|
|
||
|
sub _format_accno {
|
||
|
my ($accno) = @_;
|
||
|
return $accno . ('0' x (6 - min(length($accno), 6)));
|
||
|
}
|
||
|
|
||
|
sub csv_export_for_tax_accountant {
|
||
|
my ($self) = @_;
|
||
|
|
||
|
$self->_get_transactions(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 ]);
|
||
|
}
|
||
|
|
||
|
$_->{out}->close for values %csvs;
|
||
|
}
|
||
|
|
||
|
$self->add_filenames(@filenames);
|
||
|
|
||
|
return { download_token => $self->download_token, filenames => \@filenames };
|
||
|
}
|
||
|
|
||
|
sub DESTROY {
|
||
|
clean_temporary_directories();
|
||
|
}
|
||
| SL/GDPDU.pm | ||
|---|---|---|
|
use Archive::Zip;
|
||
|
use File::Temp ();
|
||
|
use File::Spec ();
|
||
|
use List::UtilsBy qw(partition_by);
|
||
|
use List::MoreUtils qw(any);
|
||
|
use List::UtilsBy qw(partition_by sort_by);
|
||
|
|
||
|
use SL::DB::Helper::ALL; # since we work on meta data, we need everything
|
||
|
use SL::DB::Helper::Mappings;
|
||
|
use SL::Locale::String qw(t8);
|
||
|
|
||
|
use Rose::Object::MakeMethods::Generic (
|
||
|
scalar => [ qw(from to tables writer company location) ],
|
||
|
'scalar --get_set_init' => [ qw(files tempfiles export_ids) ],
|
||
|
scalar => [ qw(from to writer company location) ],
|
||
|
'scalar --get_set_init' => [ qw(files tempfiles export_ids tables) ],
|
||
|
);
|
||
|
|
||
|
# in this we find:
|
||
| ... | ... | |
|
# keep: arrayref of columns that should be saved for further referencing
|
||
|
# tables: arrayref with one column and one or many table.column references that were kept earlier
|
||
|
my %known_tables = (
|
||
|
ar => { name => t8('Invoice'), description => t8('Sales Invoices and Accounts Receivables'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
||
|
ap => { name => t8('Purchase Invoice'), description => t8('Purchase Invoices and Accounts Payables'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
||
|
oe => { name => t8('Orders'), description => t8('Orders and Quotations, Sales and Purchase'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
||
|
delivery_orders => { name => t8('Delivery Orders'), description => t8('Delivery Orders'), keep => [ qw(id customer_id vendor_id) ], transdate => 'transdate', },
|
||
|
gl => { name => t8('General Ledger'), description => t8('General Ledger Entries'), keep => [ qw(id) ], transdate => 'transdate', },
|
||
|
invoice => { name => t8('Invoice Positions'), description => t8('Positions for all Invoices'), keep => [ qw(parts_id) ], tables => [ trans_id => "ar.id", "ap.id" ] },
|
||
|
orderitems => { name => t8('OrderItems'), description => t8('Positions for all Orders'), keep => [ qw(parts_id) ], tables => [ trans_id => "oe.id" ] },
|
||
|
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" ] },
|
||
|
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" ] },
|
||
|
chart => { name => t8('Charts'), description => t8('Chart of Accounts'), tables => [ id => "acc_trans.chart_id" ] },
|
||
|
customer => { name => t8('Customers'), description => t8('Customer Master Data'), tables => [ id => "ar.customer_id", "ap.customer_id", "oe.customer_id", "delivery_orders.customer_id" ] },
|
||
|
vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), tables => [ id => "ar.vendor_id", "ap.vendor_id", "oe.vendor_id", "delivery_orders.vendor_id" ] },
|
||
|
parts => { name => t8('Parts'), description => t8('Parts, Services, and Assemblies'), tables => [ id => "invoice.parts_id", "orderitems.parts_id", "delivery_order_items.parts_id" ] },
|
||
|
chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno' },
|
||
|
customer => { name => t8('Customers'), description => t8('Customer Master Data'), },
|
||
|
vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), },
|
||
|
);
|
||
|
|
||
|
my %datev_column_defs = (
|
||
|
acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), primary_key => 1 },
|
||
|
amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
|
||
|
credit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account Name'), },
|
||
|
credit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account'), },
|
||
|
debit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account Name'), },
|
||
|
debit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account'), },
|
||
|
invnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Reference'), },
|
||
|
name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), },
|
||
|
notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), },
|
||
|
tax => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax'), },
|
||
|
taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
|
||
|
tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
|
||
|
tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
|
||
|
transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), },
|
||
|
vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
|
||
|
customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer ID'), },
|
||
|
vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor ID'), },
|
||
|
);
|
||
|
|
||
|
my @datev_columns = qw(
|
||
|
acc_trans_id
|
||
|
customer_id vendor_id
|
||
|
name vcnumber
|
||
|
transdate invnumber amount
|
||
|
debit_accno debit_accname
|
||
|
credit_accno credit_accname
|
||
|
tax
|
||
|
tax_accno tax_accname taxkey
|
||
|
notes
|
||
|
);
|
||
|
|
||
|
# rows in this listing are tiers.
|
||
| ... | ... | |
|
$self->do_csv_export($_);
|
||
|
}
|
||
|
|
||
|
$self->do_datev_csv_export;
|
||
|
|
||
|
# write xml file
|
||
|
$self->do_xml_file;
|
||
|
|
||
| ... | ... | |
|
for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
|
||
|
->table($_)
|
||
|
}
|
||
|
$self->do_datev_xml_table;
|
||
|
})
|
||
|
});
|
||
|
close($fh);
|
||
| ... | ... | |
|
my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
|
||
|
|
||
|
# PrimaryKeys must come before regular columns, so partition first
|
||
|
partition_by { 1 * $_->is_primary_key_member } $package->meta->columns;
|
||
|
partition_by {
|
||
|
$known_tables{$table}{primary_key}
|
||
|
? 1 * ($_ eq $known_tables{$table}{primary_key})
|
||
|
: 1 * $_->is_primary_key_member
|
||
|
} $package->meta->columns;
|
||
|
}
|
||
|
|
||
|
sub columns {
|
||
| ... | ... | |
|
}
|
||
|
}
|
||
|
|
||
|
sub do_datev_xml_table {
|
||
|
my ($self) = @_;
|
||
|
my $writer = $self->writer;
|
||
|
|
||
|
$self->tag('Table', sub { $self
|
||
|
->tag('URL', "transaction.csv")
|
||
|
->tag('Name', t8('Transactions'))
|
||
|
->tag('Description', t8('Transactions'))
|
||
|
->tag('Validity', sub { $self
|
||
|
->tag('Range', sub { $self
|
||
|
->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
|
||
|
->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
|
||
|
})
|
||
|
->tag('Format', $date_format)
|
||
|
})
|
||
|
->tag('UTF8')
|
||
|
->tag('DecimalSymbol', '.')
|
||
|
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
|
||
|
->tag('VariableLength', sub { $self
|
||
|
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
|
||
|
->tag('TextEncapsulator', '"')
|
||
|
->datev_columns
|
||
|
->datev_foreign_keys
|
||
|
})
|
||
|
});
|
||
|
}
|
||
|
|
||
|
sub datev_columns {
|
||
|
my ($self, $table) = @_;
|
||
|
|
||
|
my %cols_by_primary_key = partition_by { $datev_column_defs{$_}{primary_key} } @datev_columns;
|
||
|
$::lxdebug->dump(0, "cols", \%cols_by_primary_key);
|
||
|
|
||
|
for my $column (@{ $cols_by_primary_key{1} }) {
|
||
|
my $type = $column_types{ $datev_column_defs{$column}{type} };
|
||
|
|
||
|
die "unknown col type @{[ $column ]}" unless $type;
|
||
|
|
||
|
$self->tag('VariablePrimaryKey', sub { $self
|
||
|
->tag('Name', $column);
|
||
|
$type->($self);
|
||
|
})
|
||
|
}
|
||
|
|
||
|
for my $column (@{ $cols_by_primary_key{''} }) {
|
||
|
my $type = $column_types{ $datev_column_defs{$column}{type} };
|
||
|
|
||
|
die "unknown col type @{[ ref $column]}" unless $type;
|
||
|
|
||
|
$self->tag('VariableColumn', sub { $self
|
||
|
->tag('Name', $column);
|
||
|
$type->($self);
|
||
|
})
|
||
|
}
|
||
|
|
||
|
$self;
|
||
|
}
|
||
|
|
||
|
sub datev_foreign_keys {
|
||
|
my ($self) = @_;
|
||
|
# hard code weeee
|
||
|
$self->tag('ForeignKey', sub { $_[0]
|
||
|
->tag('Name', 'customer_id')
|
||
|
->tag('References', 'customer')
|
||
|
});
|
||
|
$self->tag('ForeignKey', sub { $_[0]
|
||
|
->tag('Name', 'vendor_id')
|
||
|
->tag('References', 'vendor')
|
||
|
});
|
||
|
$self->tag('ForeignKey', sub { $_[0]
|
||
|
->tag('Name', $_)
|
||
|
->tag('References', 'chart')
|
||
|
}) for qw(debit_accno credit_accno tax_accno);
|
||
|
}
|
||
|
|
||
|
sub do_datev_csv_export {
|
||
|
my ($self) = @_;
|
||
|
|
||
|
my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
|
||
|
|
||
|
$datev->_get_transactions(from_to => $datev->fromto);
|
||
|
|
||
|
for my $transaction (@{ $datev->{DATEV} }) {
|
||
|
for my $entry (@{ $transaction }) {
|
||
|
$entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
|
||
|
}
|
||
|
}
|
||
|
|
||
|
my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
|
||
|
|
||
|
my $csv = Text::CSV_XS->new({
|
||
|
binary => 1,
|
||
|
eol => "\n",
|
||
|
sep_char => ";",
|
||
|
});
|
||
|
|
||
|
my ($fh, $filename) = File::Temp::tempfile();
|
||
|
binmode($fh, ':utf8');
|
||
|
|
||
|
$self->files->{"transactions.csv"} = $filename;
|
||
|
push @{ $self->tempfiles }, $filename;
|
||
|
|
||
|
for my $transaction (@transactions) {
|
||
|
my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
|
||
|
|
||
|
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 $haben->{memo} || $soll->{memo};
|
||
|
$haben->{notes} //= '';
|
||
|
$haben->{notes} = SL::HTML::Util->strip($haben->{notes});
|
||
|
$haben->{notes} =~ s{\r}{}g;
|
||
|
$haben->{notes} =~ s{\n+}{ }g;
|
||
|
|
||
|
my %row = (
|
||
|
customer_id => $soll->{customer_id} || $haben->{customer_id},
|
||
|
vendor_id => $soll->{vendor_id} || $haben->{vendor_id},
|
||
|
amount => abs($amount->{amount}),
|
||
|
debit_accno => $soll->{accno},
|
||
|
debit_accname => $soll->{accname},
|
||
|
credit_accno => $haben->{accno},
|
||
|
credit_accname => $haben->{accname},
|
||
|
tax => abs($amount->{amount}) - abs($amount->{net_amount}),
|
||
|
notes => $haben->{notes},
|
||
|
(map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
|
||
|
(map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
|
||
|
);
|
||
|
|
||
|
$csv->print($fh, [ map { $row{$_} } @datev_columns ]);
|
||
|
}
|
||
|
|
||
|
# and build xml spec for it
|
||
|
}
|
||
|
|
||
|
sub do_csv_export {
|
||
|
my ($self, $table) = @_;
|
||
|
|
||
| ... | ... | |
|
sub init_files { +{} }
|
||
|
sub init_export_ids { +{} }
|
||
|
sub init_tempfiles { [] }
|
||
|
sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
|
||
|
|
||
|
sub API_VERSION {
|
||
|
DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
|
||
| locale/de/all | ||
|---|---|---|
|
'Credit' => 'Haben',
|
||
|
'Credit (one letter abbreviation)' => 'H',
|
||
|
'Credit Account' => 'Habenkonto',
|
||
|
'Credit Account Name' => 'Haben-Kontoname',
|
||
|
'Credit Limit' => 'Kreditlimit',
|
||
|
'Credit Limit exceeded!!!' => 'Kreditlimit überschritten!',
|
||
|
'Credit Note' => 'Gutschrift',
|
||
| ... | ... | |
|
'Debit' => 'Soll',
|
||
|
'Debit (one letter abbreviation)' => 'S',
|
||
|
'Debit Account' => 'Sollkonto',
|
||
|
'Debit Account Name' => 'Soll-Kontoname',
|
||
|
'Debit Starting Balance' => 'EB Passiva',
|
||
|
'Debit Tax' => 'Vorsteuer',
|
||
|
'Debit Tax Account' => 'Vorsteuerkonto',
|
||
| ... | ... | |
|
'Export date' => 'Exportdatum',
|
||
|
'Export date from' => 'Exportdatum von',
|
||
|
'Export date to' => 'Exportdatum bis',
|
||
|
'Export for tax accountant' => 'Export für Steuerberater',
|
||
|
'Extend automatically by n months' => 'Automatische Verlängerung um x Monate',
|
||
|
'Extended' => 'Gesamt',
|
||
|
'Extended status' => 'Erweiterter Status',
|
||
| ... | ... | |
|
'Task server control' => 'Task-Server-Steuerung',
|
||
|
'Task server status' => 'Task-Server-Status',
|
||
|
'Tax' => 'Steuer',
|
||
|
'Tax Account' => 'Steuerkonto',
|
||
|
'Tax Account Name' => 'Steuerkontoname',
|
||
|
'Tax Consultant' => 'Steuerberater/-in',
|
||
|
'Tax ID number' => 'UStID-Nummer',
|
||
|
'Tax Included' => 'Steuer im Preis inbegriffen',
|
||
| templates/webpages/gdpdu/filter.html | ||
|---|---|---|
|
<td>[% 'To Date' | $T8 %]</td>
|
||
|
<td>[% L.date_tag('to', SELF.to) %]</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td>[% 'Include in Report' | $T8 %]</td>
|
||
|
<td>
|
||
|
[% L.checkbox_tag('tables.ar', label=LxERP.t8('Invoices'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.ap', label=LxERP.t8('Purchase Invoices'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.gl', label=LxERP.t8('GL Transactions'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.delivery_orders', label=LxERP.t8('Delivery Orders'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.oe', label=LxERP.t8('Quotations and orders'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.customer', label=LxERP.t8('Customers'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.vendor', label=LxERP.t8('Vendors'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.parts', label=LxERP.t8('Parts'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.acc_trans', label=LxERP.t8('Transactions'), checked=1) %]
|
||
|
[% L.checkbox_tag('tables.chart', label=LxERP.t8('Charts'), checked=1) %]
|
||
|
</td>
|
||
|
</tr>
|
||
|
</table>
|
||
|
|
||
|
[% L.hidden_tag('action', 'Gdpdu/dispatch') %]
|
||
Auch abrufbar als: Unified diff
GDPDU: DATEV-ähnlicher Buchungsexport Rohversion