+sub _format_accno {
+ my ($accno) = @_;
+ return $accno . ('0' x (6 - min(length($accno), 6)));
+}
+
+sub csv_export_for_tax_accountant {
+ 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('Transdate'), },
+ 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 check_vcnumbers_are_valid_pk_numbers {
+ my ($self) = @_;
+
+ # 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 check_valid_length_of_accounts {
+ my ($self) = @_;
+
+ my $query = <<"SQL";
+ SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
+SQL
+
+ 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}));
+ }
+ return 1;
+}
+
projects / mfinanz.git / blobdiff