From 3f924c0fc876c2133e5ba22f25a45a484885ee0b Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Mon, 29 Feb 2016 11:56:49 +0100 Subject: [PATCH] Debitorenbuchungen als CSV importieren MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Ähnlich wie der Auftragsimport wird hier gemultiplexed, d.h. es gibt separate Zeilen für die Debitorenbuchung (ar) und die Buchungszeilen (acc_trans). Es handelt sich allerdings nicht exakt um acc_trans-Zeilen, die direkt als acc_trans Objekte importiert werden, sondern es können die gleichen Informationen wie bei der Debitorenbuchung übergeben werden, also Konto, Betrag, Steuerschlüssel und Projekt, und daraus werden dann die acc_trans-Zeilen generiert, inklusive Steuerautomatik. Das Forderungskonto muß in der Rechnungszeile übergeben werden, der Betrag wird dann anhand der Buchungszeilen bestimmt. Beispiel für Import-Format (siehe auch mehr Beispiele in t/controllers/csvimport/artransactions.t) datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart datatype,accno,amount,taxkey "Rechnung",960,4,1,"invoice",f,1400 "AccTransaction",8400,159.48,3 --- SL/Controller/CsvImport.pm | 7 +- SL/Controller/CsvImport/ARTransaction.pm | 602 +++++++++++++++++ SL/Controller/CsvImport/Base.pm | 3 +- SL/Controller/CsvImport/BaseMulti.pm | 14 +- doc/changelog | 7 + locale/de/all | 32 +- menus/user/00-erp.yaml | 7 + t/controllers/csvimport/artransactions.t | 611 ++++++++++++++++++ .../csv_import/_form_artransactions.html | 15 + templates/webpages/csv_import/form.html | 4 +- 10 files changed, 1296 insertions(+), 6 deletions(-) create mode 100644 SL/Controller/CsvImport/ARTransaction.pm create mode 100644 t/controllers/csvimport/artransactions.t create mode 100644 templates/webpages/csv_import/_form_artransactions.html diff --git a/SL/Controller/CsvImport.pm b/SL/Controller/CsvImport.pm index 8e46dafbe..5183cf623 100644 --- a/SL/Controller/CsvImport.pm +++ b/SL/Controller/CsvImport.pm @@ -17,6 +17,7 @@ use SL::Controller::CsvImport::Inventory; use SL::Controller::CsvImport::Shipto; use SL::Controller::CsvImport::Project; use SL::Controller::CsvImport::Order; +use SL::Controller::CsvImport::ARTransaction; use SL::JSON; use SL::Controller::CsvImport::BankTransaction; use SL::BackgroundJob::CsvImport; @@ -224,7 +225,7 @@ sub check_auth { sub check_type { my ($self) = @_; - die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts inventories customers_vendors addresses contacts projects orders bank_transactions); + die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts inventories customers_vendors addresses contacts projects orders bank_transactions ar_transactions); $self->type($::form->{profile}->{type}); } @@ -270,9 +271,10 @@ sub render_inputs { : $self->type eq 'projects' ? $::locale->text('CSV import: projects') : $self->type eq 'orders' ? $::locale->text('CSV import: orders') : $self->type eq 'bank_transactions' ? $::locale->text('CSV import: bank transactions') + : $self->type eq 'ar_transactions' ? $::locale->text('CSV import: ar transactions') : die; - if ($self->{type} eq 'customers_vendors' or $self->{type} eq 'orders' ) { + if ($self->{type} eq 'customers_vendors' or $self->{type} eq 'orders' or $self->{type} eq 'ar_transactions' ) { $self->all_taxzones(SL::DB::Manager::TaxZone->get_all_sorted(query => [ obsolete => 0 ])); }; @@ -626,6 +628,7 @@ sub init_worker { : $self->{type} eq 'projects' ? SL::Controller::CsvImport::Project->new(@args) : $self->{type} eq 'orders' ? SL::Controller::CsvImport::Order->new(@args) : $self->{type} eq 'bank_transactions' ? SL::Controller::CsvImport::BankTransaction->new(@args) + : $self->{type} eq 'ar_transactions' ? SL::Controller::CsvImport::ARTransaction->new(@args) : die "Program logic error"; } diff --git a/SL/Controller/CsvImport/ARTransaction.pm b/SL/Controller/CsvImport/ARTransaction.pm new file mode 100644 index 000000000..1d4179b75 --- /dev/null +++ b/SL/Controller/CsvImport/ARTransaction.pm @@ -0,0 +1,602 @@ +package SL::Controller::CsvImport::ARTransaction; + +use strict; + +use List::MoreUtils qw(any); + +use Data::Dumper; +use SL::Helper::Csv; +use SL::Controller::CsvImport::Helper::Consistency; +use SL::DB::Invoice; +use SL::DB::AccTransaction; +use SL::DB::Department; +use SL::DB::Project; +use SL::DB::TaxZone; +use SL::DB::Chart; +use SL::TransNumber; +use DateTime; + +use parent qw(SL::Controller::CsvImport::BaseMulti); + +use Rose::Object::MakeMethods::Generic +( + 'scalar --get_set_init' => [ qw(settings charts_by taxkeys_by) ], +); + + +sub init_class { + my ($self) = @_; + $self->class(['SL::DB::Invoice', 'SL::DB::AccTransaction']); +} + +sub set_profile_defaults { + my ($self) = @_; + + $self->controller->profile->_set_defaults( + ar_column => $::locale->text('Invoice'), + transaction_column => $::locale->text('AccTransaction'), + max_amount_diff => 0.02, + ); +}; + + +sub init_settings { + my ($self) = @_; + + return { map { ( $_ => $self->controller->profile->get($_) ) } qw(ar_column transaction_column max_amount_diff) }; +} + +sub init_profile { + my ($self) = @_; + + my $profile = $self->SUPER::init_profile; + + # SUPER::init_profile sets row_ident to the translated class name + # overwrite it with the user specified settings +# TODO: remove hardcoded row_idents + foreach my $p (@{ $profile }) { + if ($p->{class} eq 'SL::DB::Invoice') { + $p->{row_ident} = $self->_ar_column; + } + if ($p->{class} eq 'SL::DB::AccTransaction') { + $p->{row_ident} = $self->_transaction_column; + } + } + + foreach my $p (@{ $profile }) { + my $prof = $p->{profile}; + if ($p->{row_ident} eq $self->_ar_column) { + # no need to handle + delete @{$prof}{qw(delivery_customer_id delivery_vendor_id )}; + } + if ($p->{row_ident} eq $self->_transaction_column) { + # no need to handle + delete @{$prof}{qw(trans_id)}; + } + } + + return $profile; +} + + +sub setup_displayable_columns { + my ($self) = @_; + + $self->SUPER::setup_displayable_columns; + + $self->add_displayable_columns($self->_ar_column, + { name => 'datatype', description => $self->_ar_column . ' [1]' }, + { name => 'currency', description => $::locale->text('Currency') }, + { name => 'cusordnumber', description => $::locale->text('Customer Order Number') }, + { name => 'direct_debit', description => $::locale->text('direct debit') }, + { name => 'donumber', description => $::locale->text('Delivery Order Number') }, + { name => 'duedate', description => $::locale->text('Due Date') }, + { name => 'delivery_term_id', description => $::locale->text('Delivery terms (database ID)') }, + { name => 'delivery_term', description => $::locale->text('Delivery terms (name)') }, + { name => 'deliverydate', description => $::locale->text('Delivery Date') }, + { name => 'employee_id', description => $::locale->text('Employee (database ID)') }, + { name => 'intnotes', description => $::locale->text('Internal Notes') }, + { name => 'notes', description => $::locale->text('Notes') }, + { name => 'invnumber', description => $::locale->text('Invoice Number') }, + { name => 'quonumber', description => $::locale->text('Quotation Number') }, + { name => 'reqdate', description => $::locale->text('Reqdate') }, + { name => 'salesman_id', description => $::locale->text('Salesman (database ID)') }, + { name => 'transaction_description', description => $::locale->text('Transaction description') }, + { name => 'transdate', description => $::locale->text('Invoice Date') }, + { name => 'verify_amount', description => $::locale->text('Amount (for verification)') . ' [2]' }, + { name => 'verify_netamount', description => $::locale->text('Net amount (for verification)') . ' [2]'}, + { name => 'taxincluded', description => $::locale->text('Tax Included') }, + { name => 'customer', description => $::locale->text('Customer (name)') }, + { name => 'customernumber', description => $::locale->text('Customer Number') }, + { name => 'customer_id', description => $::locale->text('Customer (database ID)') }, + { name => 'language_id', description => $::locale->text('Language (database ID)') }, + { name => 'language', description => $::locale->text('Language (name)') }, + { name => 'payment_id', description => $::locale->text('Payment terms (database ID)') }, + { name => 'payment', description => $::locale->text('Payment terms (name)') }, + { name => 'taxzone_id', description => $::locale->text('Tax zone (database ID)') }, + { name => 'taxzone', description => $::locale->text('Tax zone (description)') }, + { name => 'department_id', description => $::locale->text('Department (database ID)') }, + { name => 'department', description => $::locale->text('Department (description)') }, + { name => 'globalproject_id', description => $::locale->text('Document Project (database ID)') }, + { name => 'globalprojectnumber', description => $::locale->text('Document Project (number)') }, + { name => 'globalproject', description => $::locale->text('Document Project (description)') }, + { name => 'archart', description => $::locale->text('Receivables account (account number)') }, + { name => 'orddate', description => $::locale->text('Order Date') }, + { name => 'ordnumber', description => $::locale->text('Order Number') }, + { name => 'quonumber', description => $::locale->text('Quotation Number') }, + { name => 'quodate', description => $::locale->text('Quotation Date') }, + ); + + $self->add_displayable_columns($self->_transaction_column, + { name => 'datatype', description => $self->_transaction_column . ' [1]' }, + { name => 'projectnumber', description => $::locale->text('Project (number)') }, + { name => 'project', description => $::locale->text('Project (description)') }, + { name => 'amount', description => $::locale->text('Amount') }, + { name => 'chart', description => $::locale->text('Account number') }, + { name => 'taxkey', description => $::locale->text('Taxkey') }, + ); +} + +sub init_taxkeys_by { + my ($self) = @_; + + my $all_taxes = SL::DB::Manager::Tax->get_all; + return { map { $_->taxkey => $_->id } @{ $all_taxes } }; +} + + +sub init_charts_by { + my ($self) = @_; + + my $all_charts = SL::DB::Manager::Chart->get_all; + return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_charts } } ) } qw(id accno) }; +} + +sub check_objects { + my ($self) = @_; + + $self->controller->track_progress(phase => 'building data', progress => 0); + + my $i = 0; + my $num_data = scalar @{ $self->controller->data }; + + foreach my $entry (@{ $self->controller->data }) { + $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0; + + if ($entry->{raw_data}->{datatype} eq $self->_ar_column) { + $self->handle_invoice($entry); + } elsif ($entry->{raw_data}->{datatype} eq $self->_transaction_column ) { + $self->handle_transaction($entry); + } else { + die "unknown datatype"; + }; + + } continue { + $i++; + } # finished data parsing + + $self->add_transactions_to_ar(); # go through all data entries again, adding receivable entry to ar lines while calculating amount and netamount + + foreach my $entry (@{ $self->controller->data }) { + next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column); + $self->check_verify_amounts($entry->{object}); + }; + + foreach my $entry (@{ $self->controller->data }) { + next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column); + unless ( $entry->{object}->validate_acc_trans ) { + push @{ $entry->{errors} }, $::locale->text('Error: ar transaction doesn\'t validate'); + }; + }; + + # add info columns that aren't directly part of the object to be imported + # but are always determined or should always be shown because they are mandatory + $self->add_info_columns($self->_ar_column, + { header => $::locale->text('Customer/Vendor'), method => 'vc_name' }, + { header => $::locale->text('Receivables account'), method => 'archart' }, + { header => $::locale->text('Amount'), method => 'amount' }, + { header => $::locale->text('Net amount'), method => 'netamount' }, + { header => $::locale->text('Tax zone'), method => 'taxzone' }); + + # Adding info_header this way only works, if the first invoice $self->controller->data->[0] + + # Todo: access via ->[0] ok? Better: search first order column and use this + $self->add_info_columns($self->_ar_column, { header => $::locale->text('Department'), method => 'department' }) if $self->controller->data->[0]->{info_data}->{department} or $self->controller->data->[0]->{raw_data}->{department}; + + $self->add_info_columns($self->_ar_column, { header => $::locale->text('Project Number'), method => 'globalprojectnumber' }) if $self->controller->data->[0]->{info_data}->{globalprojectnumber}; + + $self->add_columns($self->_ar_column, + map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw(payment department globalproject taxzone cp currency)); + $self->add_columns($self->_ar_column, 'globalproject_id') if exists $self->controller->data->[0]->{raw_data}->{globalprojectnumber}; + $self->add_columns($self->_ar_column, 'notes') if exists $self->controller->data->[0]->{raw_data}->{notes}; + + # Todo: access via ->[1] ok? Better: search first item column and use this + $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Chart'), method => 'accno' }); + $self->add_columns($self->_transaction_column, 'amount'); + + $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Project Number'), method => 'projectnumber' }) if $self->controller->data->[1]->{info_data}->{projectnumber}; + + # $self->add_columns($self->_transaction_column, + # map { "${_}_id" } grep { exists $self->controller->data->[1]->{raw_data}->{$_} } qw(project price_factor pricegroup)); + # $self->add_columns($self->_transaction_column, + # map { "${_}_id" } grep { exists $self->controller->data->[2]->{raw_data}->{$_} } qw(project price_factor pricegroup)); + # $self->add_columns($self->_transaction_column, 'project_id') if exists $self->controller->data->[1]->{raw_data}->{projectnumber}; + # $self->add_columns($self->_transaction_column, 'taxkey') if exists $self->controller->data->[1]->{raw_data}->{taxkey}; + + # If invoice has errors, add error for acc_trans items + # If acc_trans item has an error, add an error to the invoice item + my $ar_entry; + foreach my $entry (@{ $self->controller->data }) { + # Search first order + if ($entry->{raw_data}->{datatype} eq $self->_ar_column) { + $ar_entry = $entry; + } elsif ( defined $ar_entry + && $entry->{raw_data}->{datatype} eq $self->_transaction_column + && scalar @{ $ar_entry->{errors} } > 0 ) { + push @{ $entry->{errors} }, $::locale->text('Error: invalid ar row for this transaction'); + } elsif ( defined $ar_entry + && $entry->{raw_data}->{datatype} eq $self->_transaction_column + && scalar @{ $entry->{errors} } > 0 ) { + push @{ $ar_entry->{errors} }, $::locale->text('Error: invalid acc transactions for this ar row'); + } + } +} + +sub handle_invoice { + + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + $object->transactions( [] ); # initialise transactions for ar object so methods work on unsaved transactions + + my $vc_obj; + if (any { $entry->{raw_data}->{$_} } qw(customer customernumber customer_id)) { + $self->check_vc($entry, 'customer_id'); + # check_vc only sets customer_id, but we need vc_obj later for customer defaults + $vc_obj = SL::DB::Customer->new(id => $object->customer_id)->load if $object->customer_id; + } elsif (any { $entry->{raw_data}->{$_} } qw(vendor vendornumber vendor_id)) { + $self->check_vc($entry, 'vendor_id'); + $vc_obj = SL::DB::Vendor->new(id => $object->vendor_id)->load if $object->vendor_id; + } else { + push @{ $entry->{errors} }, $::locale->text('Error: Customer/vendor missing'); + } + + # check for duplicate invnumbers already in database + if ( SL::DB::Manager::Invoice->get_all_count( where => [ invnumber => $object->invnumber ] ) ) { + push @{ $entry->{errors} }, $::locale->text('Error: invnumber already exists'); + } + + $self->check_archart($entry); # checks for receivable account + # $self->check_amounts($entry); # checks and sets amount and netamount, use verify_amount and verify_netamount instead + $self->check_payment($entry); # currency default from customer used below + $self->check_department($entry); + $self->check_taxincluded($entry); + $self->check_project($entry, global => 1); + $self->check_taxzone($entry); # taxzone default from customer used below + $self->check_currency($entry); # currency default from customer used below + $self->handle_salesman($entry); + $self->handle_employee($entry); + + if ($vc_obj ) { + # copy defaults from customer if not specified in import file + foreach (qw(payment_id language_id taxzone_id currency_id)) { + $object->$_($vc_obj->$_) unless $object->$_; + } + } +} + +sub check_taxkey { + my ($self, $entry, $chart) = @_; + + die "check_taxkey needs chart object as an argument" unless ref($chart) eq 'SL::DB::Chart'; + # problem: taxkey is not unique in table tax, normally one of those entries is chosen directly from a dropdown + # so we check if the chart has an active taxkey, and if it matches the taxkey from the import, use the active taxkey + # if the chart doesn't have an active taxkey, use the first entry from Tax that matches the taxkey + + my $object = $entry->{object}; + unless ( defined $entry->{raw_data}->{taxkey} ) { + push @{ $entry->{errors} }, $::locale->text('Error: taxkey missing'); # don't just assume 0, force taxkey in import + return 0; + }; + + my $tax; + + if ( $entry->{raw_data}->{taxkey} == $chart->get_active_taxkey->tax->taxkey ) { + $tax = $chart->get_active_taxkey->tax; + } else { + # assume there is only one tax entry with that taxkey, can't guess + $tax = SL::DB::Manager::Tax->get_first( where => [ taxkey => $entry->{raw_data}->{taxkey} ]); + }; + + unless ( $tax ) { + push @{ $entry->{errors} }, $::locale->text('Error: invalid taxkey'); + return 0; + }; + + $object->taxkey($tax->taxkey); + $object->tax_id($tax->id); + return 1; +}; + +sub check_amounts { + my ($self, $entry) = @_; + # currently not used in favour of verify_amount and verify_netamount + + my $object = $entry->{object}; + + unless ($entry->{raw_data}->{amount} && $entry->{raw_data}->{netamount}) { + push @{ $entry->{errors} }, $::locale->text('Error: need amount and netamount'); + return 0; + }; + unless ($entry->{raw_data}->{amount} * 1 && $entry->{raw_data}->{netamount} * 1) { + push @{ $entry->{errors} }, $::locale->text('Error: amount and netamount need to be numeric'); + return 0; + }; + + $object->amount( $entry->{raw_data}->{amount} ); + $object->netamount( $entry->{raw_data}->{netamount} ); +}; + +sub handle_transaction { + my ($self, $entry) = @_; + + # Prepare acc_trans data. amount is dealt with in add_transactions_to_ar + + my $object = $entry->{object}; + + $self->check_project($entry, global => 0); + if ( $self->check_chart($entry) ) { + my $chart_obj = SL::DB::Manager::Chart->find_by(id => $object->chart_id); + + unless ( $chart_obj->link =~ /AR_amount/ ) { + push @{ $entry->{errors} }, $::locale->text('Error: chart isn\'t an ar_amount chart'); + return 0; + }; + + if ( $self->check_taxkey($entry, $chart_obj) ) { + # do nothing, taxkey was assigned, just continue + } else { + # missing taxkey, don't do anything + return 0; + }; + } else { + return 0; + }; + + # check whether taxkey and automatic taxkey match + # die sprintf("taxkeys don't match: %s not equal default taxkey for chart %s: %s", $object->taxkey, $chart_obj->accno, $active_tax_for_chart->tax->taxkey) unless $object->taxkey == $active_tax_for_chart->tax->taxkey; + + die "no taxkey for transaction object" unless $object->taxkey or $object->taxkey == 0; + +} + +sub check_chart { + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + if (any { $entry->{raw_data}->{$_} } qw(accno chart_id)) { + + # Check whether or not chart ID is valid. + if ($object->chart_id && !$self->charts_by->{id}->{ $object->chart_id }) { + push @{ $entry->{errors} }, $::locale->text('Error: invalid chart_id'); + return 0; + } + + # Map number to ID if given. + if (!$object->chart_id && $entry->{raw_data}->{accno}) { + my $chart = $self->charts_by->{accno}->{ $entry->{raw_data}->{accno} }; + if (!$chart) { + push @{ $entry->{errors} }, $::locale->text('Error: invalid chart (accno)'); + return 0; + } + + $object->chart_id($chart->id); + } + + # Map description to ID if given. + if (!$object->chart_id && $entry->{raw_data}->{description}) { + my $chart = $self->charts_by->{description}->{ $entry->{raw_data}->{description} }; + if (!$chart) { + push @{ $entry->{errors} }, $::locale->text('Error: invalid chart'); + return 0; + } + + $object->chart_id($chart->id); + } + + if ($object->chart_id) { + # add account number to preview + $entry->{info_data}->{accno} = $self->charts_by->{id}->{ $object->chart_id }->accno; + } else { + push @{ $entry->{errors} }, $::locale->text('Error: chart not found'); + return 0; + } + } else { + push @{ $entry->{errors} }, $::locale->text('Error: chart missing'); + return 0; + } + + return 1; +} + +sub check_archart { + my ($self, $entry) = @_; + + my $chart; + + if ( $entry->{raw_data}->{archart} ) { + my $archart = $entry->{raw_data}->{archart}; + $chart = SL::DB::Manager::Chart->find_by(accno => $archart); + unless ($chart) { + push @{ $entry->{errors} }, $::locale->text("Error: can't find ar chart with accno #1", $archart); + return 0; + }; + } elsif ( $::instance_conf->get_ar_chart_id ) { + $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id); + } else { + push @{ $entry->{errors} }, $::locale->text("Error: neither archart passed, no default receivables chart configured"); + return 0; + }; + + unless ($chart->link eq 'AR') { + push @{ $entry->{errors} }, $::locale->text('Error: archart isn\'t an AR chart'); + return 0; + }; + + $entry->{info_data}->{archart} = $chart->accno; + $entry->{object}->{archart} = $chart; + return 1; +}; + +sub check_taxincluded { + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + if ( $entry->{raw_data}->{taxincluded} ) { + if ( $entry->{raw_data}->{taxincluded} eq 'f' or $entry->{raw_data}->{taxincluded} eq '0' ) { + $object->taxincluded('0'); + } elsif ( $entry->{raw_data}->{taxincluded} eq 't' or $entry->{raw_data}->{taxincluded} eq '1' ) { + $object->taxincluded('1'); + } else { + push @{ $entry->{errors} }, $::locale->text('Error: taxincluded has to be t or f'); + return 0; + }; + } else { + push @{ $entry->{errors} }, $::locale->text('Error: taxincluded wasn\'t set'); + return 0; + }; + return 1; +}; + +sub check_verify_amounts { + my ($self) = @_; + + # If amounts are given, show calculated amounts as info and given amounts (verify_xxx). + # And throw an error if the differences are too big. + my @to_verify = ( { column => 'amount', + raw_column => 'verify_amount', + info_header => 'Calc. Amount', + info_method => 'calc_amount', + err_msg => $::locale->text('Amounts differ too much'), + }, + { column => 'netamount', + raw_column => 'verify_netamount', + info_header => 'Calc. Net amount', + info_method => 'calc_netamount', + err_msg => $::locale->text('Net amounts differ too much'), + } ); + + foreach my $tv (@to_verify) { + if (exists $self->controller->data->[0]->{raw_data}->{ $tv->{raw_column} }) { + $self->add_raw_data_columns($self->_ar_column, $tv->{raw_column}); + $self->add_info_columns($self->_ar_column, + { header => $::locale->text($tv->{info_header}), method => $tv->{info_method} }); + } + + # check differences + foreach my $entry (@{ $self->controller->data }) { + if ( @{ $entry->{errors} } ) { + push @{ $entry->{errors} }, $::locale->text($tv->{err_msg}); + return 0; + }; + + if ($entry->{raw_data}->{datatype} eq $self->_ar_column) { + next if !$entry->{raw_data}->{ $tv->{raw_column} }; + my $parsed_value = $::form->parse_amount(\%::myconfig, $entry->{raw_data}->{ $tv->{raw_column} }); + # round $abs_diff, otherwise it might trigger for 0.020000000000021 + my $abs_diff = $::form->round_amount(abs($entry->{object}->${ \$tv->{column} } - $parsed_value),2); + if ( $abs_diff > $self->settings->{'max_amount_diff'}) { + push @{ $entry->{errors} }, $::locale->text($tv->{err_msg}); + } + } + } + } +}; + +sub add_transactions_to_ar { + my ($self) = @_; + + # go through all verified ar and acc_trans rows in import, adding acc_trans objects to ar objects + + my $ar_entry; # the current ar row + + foreach my $entry (@{ $self->controller->data }) { + # when we reach an ar_column for the first time, don't do anything, just store in $ar_entry + # when we reach an ar_column for the second time, save it + if ($entry->{raw_data}->{datatype} eq $self->_ar_column) { + if ( $ar_entry && $ar_entry->{object} ) { # won't trigger the first time, finishes the last object + if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) { + $ar_entry->{object}->recalculate_amounts; # determine and set amount and netamount for ar + $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart}); + $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount; + $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount; + } else { + push @{ $entry->{errors} }, $::locale->text("ar_chart isn't a valid chart"); + }; + }; + $ar_entry = $entry; # remember as last ar_entry + + } elsif ( defined $ar_entry && $entry->{raw_data}->{datatype} eq $self->_transaction_column ) { + push @{ $entry->{errors} }, $::locale->text('no tax_id in acc_trans') unless $entry->{object}->tax_id || $entry->{object}->tax_id == 0; + next if @{ $entry->{errors} }; + + my $acc_trans_objects = $ar_entry->{object}->add_ar_amount_row( + amount => $entry->{object}->amount, + chart => SL::DB::Manager::Chart->find_by(id => $entry->{object}->chart_id), # add_ar_amount takes chart obj. as argument + tax_id => $entry->{object}->tax_id, + project_id => $entry->{object}->project_id, + debug => 0, + ); + + } else { + die "This should never happen\n"; + }; + } + + # finish the final object + if ( $ar_entry->{object} ) { + if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) { + $ar_entry->{object}->recalculate_amounts; + $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount; + $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount; + + $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart}); + } else { + push @{ $ar_entry->{errors} }, $::locale->text("The receivables chart isn't a valid chart."); + return 0; + }; + } else { + die "There was no final ar_entry object"; + }; +} + +sub save_objects { + my ($self, %params) = @_; + + # save all the Invoice objects + my $objects_to_save; + foreach my $entry (@{ $self->controller->data }) { + # only push the invoice objects that don't have an error + next if $entry->{raw_data}->{datatype} ne $self->_ar_column; + next if @{ $entry->{errors} }; + + die unless $entry->{object}->validate_acc_trans; + + push @{ $objects_to_save }, $entry; + } + + $self->SUPER::save_objects(data => $objects_to_save); +} + +sub _ar_column { + $_[0]->settings->{'ar_column'} +} + +sub _transaction_column { + $_[0]->settings->{'transaction_column'} +} + +1; diff --git a/SL/Controller/CsvImport/Base.pm b/SL/Controller/CsvImport/Base.pm index 3090182c1..c8456cafa 100644 --- a/SL/Controller/CsvImport/Base.pm +++ b/SL/Controller/CsvImport/Base.pm @@ -522,11 +522,12 @@ sub clean_fields { sub _save_history { my ($self, $object) = @_; - if (any { $_ eq $self->controller->{type} } qw(parts customers_vendors orders)) { + if (any { $_ eq $self->controller->{type} } qw(parts customers_vendors orders ar_transactions)) { my $snumbers = $self->controller->{type} eq 'parts' ? 'partnumber_' . $object->partnumber : $self->controller->{type} eq 'customers_vendors' ? ($self->table eq 'customer' ? 'customernumber_' . $object->customernumber : 'vendornumber_' . $object->vendornumber) : $self->controller->{type} eq 'orders' ? 'ordnumber_' . $object->ordnumber + : $self->controller->{type} eq 'ar_transactions' ? 'invnumber_' . $object->invnumber : ''; my $what_done = $self->controller->{type} eq 'orders' ? 'sales_order' diff --git a/SL/Controller/CsvImport/BaseMulti.pm b/SL/Controller/CsvImport/BaseMulti.pm index 973a4ae8e..5f5e337bf 100644 --- a/SL/Controller/CsvImport/BaseMulti.pm +++ b/SL/Controller/CsvImport/BaseMulti.pm @@ -187,6 +187,18 @@ sub init_profile { eval "require " . $class; my %unwanted = map { ( $_ => 1 ) } (qw(itime mtime), map { $_->name } @{ $class->meta->primary_key_columns }); + + # TODO: execeptions for AccTransaction and Invoice wh + if ( $class =~ m/^SL::DB::AccTransaction/ ) { + my %unwanted_acc_trans = map { ( $_ => 1 ) } (qw(acc_trans_id trans_id cleared fx_transaction ob_transaction cb_transaction itime mtime chart_link tax_id description gldate memo source transdate), map { $_->name } @{ $class->meta->primary_key_columns }); + @unwanted{keys %unwanted_acc_trans} = values %unwanted_acc_trans; + }; + if ( $class =~ m/^SL::DB::Invoice/ ) { + # remove fields that aren't needed / shouldn't be set for ar transaction + my %unwanted_ar = map { ( $_ => 1 ) } (qw(closed currency currency_id datepaid dunning_config_id gldate invnumber_for_credit_note invoice marge_percent marge_total amount netamount paid shippingpoint shipto_id shipvia storno storno_id type cp_id), map { $_->name } @{ $class->meta->primary_key_columns }); + @unwanted{keys %unwanted_ar} = values %unwanted_ar; + }; + my %prof; $prof{datatype} = ''; for my $col ($class->meta->columns) { @@ -268,7 +280,7 @@ sub fix_field_lengths { my %field_lengths_by_ri = $self->field_lengths; foreach my $entry (@{ $self->controller->data }) { - next unless @{ $entry->{errors} }; + next unless defined $entry->{errors} && @{ $entry->{errors} }; my %field_lengths = %{ $field_lengths_by_ri{ $entry->{raw_data}->{datatype} } }; map { $entry->{object}->$_(substr($entry->{object}->$_, 0, $field_lengths{$_})) if $entry->{object}->$_ } keys %field_lengths; } diff --git a/doc/changelog b/doc/changelog index 5433e3e59..2a517a99f 100644 --- a/doc/changelog +++ b/doc/changelog @@ -40,6 +40,13 @@ Größere neue Features: zu Lieferscheinposition mitverfolgt. Ferner wird der Nettowarenwert für den Fall Hauptwährung und Netto-Auftrag berechnet. +Debitorenbuchungsimport + + Neuer Menüpunkt im CSV Importer. Anwendungsbeispiele: + * bei einer Migration zu kivitendo die offenen Posten übernehmen + * wenn kivitendo für die Buchhaltung benutzt wird, die Rechnungen aber mit + einem externen Programm erstellt werden + Kleinere neue Features und Detailverbesserungen: - Neues Feld GLN bei Kunden/Lieferanten und Lieferadressen. diff --git a/locale/de/all b/locale/de/all index fea9c4b25..eaa205de3 100755 --- a/locale/de/all +++ b/locale/de/all @@ -74,6 +74,7 @@ $self->{texts} = { 'AR Aging' => 'Offene Forderungen', 'AR Transaction' => 'Debitorenbuchung', 'AR Transaction (abbreviation)' => 'D', + 'AR Transaction/AccTrans Item row names' => 'Namen der Rechnungs/Buchungszeilen', 'AR Transactions' => 'Debitorenbuchungen', 'AR transactions changeable' => 'Änderbarkeit von Debitorenbuchungen', 'ASSETS' => 'AKTIVA', @@ -245,6 +246,7 @@ $self->{texts} = { 'Amount payable' => 'Noch zu bezahlender Betrag', 'Amount payable less discount' => 'Noch zu bezahlender Betrag abzüglich Skonto', 'An error occured. Letter could not be deleted.' => 'Es ist ein Fehler aufgetreten. Der Brief konnte nicht gelöscht werden.', + 'Amounts differ too much' => 'Beträge weichen zu sehr voneinander ab.', 'An exception occurred during execution.' => 'Während der Ausführung trat eine Ausnahme auf.', 'An invalid character was used (invalid characters: #1).' => 'Ein ungültiges Zeichen wurde benutzt (ungültige Zeichen: #1).', 'An invalid character was used (valid characters: #1).' => 'Ein ungültiges Zeichen wurde benutzt (gültige Zeichen: #1).', @@ -444,6 +446,7 @@ $self->{texts} = { 'CSS style for pictures' => 'CSS Style für Bilder', 'CSV' => 'CSV', 'CSV export -- options' => 'CSV-Export -- Optionen', + 'CSV import: ar transactions' => 'CSV Import: Debitorenbuchungen', 'CSV import: bank transactions' => 'CSV Import: Bankbewegungen', 'CSV import: contacts' => 'CSV-Import: Ansprechpersonen', 'CSV import: customers and vendors' => 'CSV-Import: Kunden und Lieferanten', @@ -516,6 +519,7 @@ $self->{texts} = { 'Charge number' => 'Chargennummer', 'Charset' => 'Zeichensatz', 'Chart' => 'Buchungskonto', + 'Chart (database ID)' => 'Konto (Datenbank ID)', 'Chart Type' => 'Kontentyp', 'Chart balance' => 'Kontensaldo', 'Chart of Accounts' => 'Kontenübersicht', @@ -1130,8 +1134,10 @@ $self->{texts} = { 'Error: Customer/vendor not found' => 'Fehler: Kunde/Lieferant nicht gefunden', 'Error: Found local bank account number but local bank code doesn\'t match' => 'Fehler: Kontonummer wurde gefunden aber gespeicherte Bankleitzahl stimmt nicht überein', 'Error: Gender (cp_gender) missing or invalid' => 'Fehler: Geschlecht (cp_gender) fehlt oder ungültig', + 'Error: Invalid ar transaction for this order item' => 'Fehler: ungültige Debitorenrechnung für diese Buchungszeile', 'Error: Invalid bin' => 'Fehler: Ungültiger Lagerplatz', 'Error: Invalid business' => 'Fehler: Kunden-/Lieferantentyp ungültig', + 'Error: Invalid chart' => 'Fehler: ungültiges Konto', 'Error: Invalid contact' => 'Fehler: Ansprechperson ungültig', 'Error: Invalid currency' => 'Fehler: ungültige Währung', 'Error: Invalid delivery terms' => 'Fehler: Lieferbedingungen ungültig', @@ -1156,8 +1162,27 @@ $self->{texts} = { 'Error: Transfer would result in a negative target quantity.' => 'Fehler: Lagerbewegung würde zu einer negativen Zielmenge führen.', 'Error: Unit missing or invalid' => 'Fehler: Einheit fehlt oder ungültig', 'Error: Warehouse not found' => 'Fehler: Lager nicht gefunden', + 'Error: amount and netamount need to be numeric' => 'Fehler: amount und netamount müssen numerisch sein', + 'Error: ar transaction doesn\'t validate' => 'Fehler: die Debitorenbuchung ist nicht korrekt', + 'Error: archart isn\'t an AR chart' => 'Fehler: das Forderungskonto ist nicht als Forderungskonto definiert (link = AR)', + 'Error: can\'t find ar chart with accno #1' => 'Fehler: kein Forderungskonto mit Kontonummer #1', + 'Error: chart isn\'t an ar_amount chart' => 'Fehler: Konto ist kein Erlöskonto', + 'Error: chart missing' => 'Fehler: Konto fehlt', + 'Error: chart not found' => 'Fehler: Konto nicht gefunden', + 'Error: invalid acc transactions for this ar row' => 'Fehler: ungültige Buchungszeilen für diese Rechnungzeile', + 'Error: invalid ar row for this transaction' => 'Ungültige Rechnungszeile für diese Buchungszeile', + 'Error: invalid chart' => 'Fehler: ungültiges Konto', + 'Error: invalid chart (accno)' => 'Fehler: ungültiges Konto (accno)', + 'Error: invalid chart_id' => 'Fehler: ungültige Konto ID (chart_id)', + 'Error: invalid taxkey' => 'Fehler: ungültiger Steuerschlüssel', + 'Error: invnumber already exists' => 'Fehler: Rechnungsnummer existiert schon', 'Error: local bank account id doesn\'t match local bank account number' => 'Fehler: Bankkonto-ID stimmt nicht mit Kontonummer überein', 'Error: local bank account id doesn\'t match local bank code' => 'Fehler: Bankkonto-ID stimmt nicht mit BLZ überein', + 'Error: need amount and netamount' => 'Fehler: amount und netamount werden benötigt', + 'Error: neither archart passed, no default receivables chart configured' => 'Fehler: Forderungskonto (archart) fehlt, kein Standardforderungskonto definiert', + 'Error: taxincluded has to be t or f' => 'Fehler: Steuer im Preis inbegriffen muß t oder f sein', + 'Error: taxincluded wasn\'t set' => 'Fehler: Steuer im Preis inbegriffen nicht gesetzt (taxincluded)', + 'Error: taxkey missing' => 'Fehler: Steuerschlüssel fehlt', 'Error: this feature requires that articles with a time-based unit (e.g. \'h\' or \'min\') exist.' => 'Fehler: dieses Feature setzt voraus, dass Artikel mit einer Zeit-basierenden Einheit (z.B. "Std") existieren.', 'Error: unknown local bank account' => 'Fehler: unbekannte Kontnummer', 'Error: unknown local bank account id' => 'Fehler: unbekannte Bankkonto-ID', @@ -1680,6 +1705,7 @@ $self->{texts} = { 'Net Value in delivery orders' => 'Netto mit Lieferschein', 'Net amount' => 'Nettobetrag', 'Net amount (for verification)' => 'Nettobetrag (zur Überprüfung)', + 'Net amounts differ too much' => 'Nettobeträge weichen zu sehr ab.', 'Net value in Order' => 'Netto Auftrag', 'Net value transferred in / out' => 'Netto ein- /ausgelagert', 'Net value without delivery orders' => 'Netto ohne Lieferschein', @@ -2173,7 +2199,8 @@ $self->{texts} = { 'Receipts' => 'Zahlungseingänge', 'Receivable account' => 'Forderungskonto', 'Receivables' => 'Forderungen', - 'Recipients' => 'EmpfängerInnen', + 'Receivables account' => 'Forderungskonto', + 'Receivables account (account number)' => 'Forderungskonto (Kontonummer)', 'Reconcile' => 'Abgleichen', 'Reconciliation' => 'Kontenabgleich', 'Reconciliation with bank' => 'Kontenabgleich mit Bank', @@ -2708,6 +2735,7 @@ $self->{texts} = { 'The client has been deleted.' => 'Der Mandant wurde gelöscht.', 'The client has been saved.' => 'Der Mandant wurde gespeichert.', 'The clipboard does not contain anything that can be pasted here.' => 'Die Zwischenablage enthält momentan keine Objekte, die hier eingefügt werden können.', + 'The column "datatype" must be present and must be at the same position / column in each data set. The values must be the row names (see settings) for invoice and transaction data respectively.' => 'Die Spalte "datatype" muss vorhanden sein und sie muss in jedem Datensatz an der gleichen Stelle / Spalte sein. Die Werte in dieser Spalte müssen die Namen der Rechnungs- und Buchungszeilen (siehe Einstellungen) sein.', 'The column "datatype" must be present and must be at the same position / column in each data set. The values must be the row names (see settings) for order and item data respectively.' => 'Die Spalte "datatype" muss vorhanden sein und sie muss in jedem Datensatz an der gleichen Stelle / Spalte sein. Die Werte in dieser Spalte müssen die Namen der Auftrag-/Positions-Zeilen (siehe Einstellungen) sein.', 'The column "make_X" can contain either a vendor\'s database ID, a vendor number or a vendor\'s name.' => 'Die Spalte "make_X" can entweder die Datenbank-ID des Lieferanten, eine Lieferantennummer oder einen Lieferantennamen enthalten.', 'The column triplets can occur multiple times with different numbers "X" each time (e.g. "make_1", "model_1", "lastcost_1", "make_2", "model_2", "lastcost_2", "make_3", "model_3", "lastcost_3" etc).' => 'Die Spalten-Dreiergruppen können mehrfach auftreten, sofern sie unterschiedliche Nummern "X" verwenden (z.B. "make_1", "model_1", "lastcost_1", "make_2", "model_2", "lastcost_2", "make_3", "model_3", "lastcost_3" etc).', @@ -2858,6 +2886,7 @@ $self->{texts} = { 'The project type has been deleted.' => 'Der Projekttyp wurde gelöscht.', 'The project type has been saved.' => 'Der Projekttyp wurde gespeichert.', 'The project type is in use and cannot be deleted.' => 'Der Projekttyp wird verwendet und kann nicht gelöscht werden.', + 'The receivables chart isn\'t a valid chart.' => 'Das Forderungskonto ist kein gültiges Konto', 'The recipient, subject or body is missing.' => 'Der Empfäger, der Betreff oder der Text ist leer.', 'The required information consists of the IBAN and the BIC.' => 'Die benötigten Informationen bestehen aus der IBAN und der BIC. Zusätzlich wird die SEPA-Kreditoren-Identifikation aus der Mandantenkonfiguration benötigt.', 'The required information consists of the IBAN, the BIC, the mandator ID and the mandate\'s date of signature.' => 'Die benötigten Informationen bestehen aus IBAN, BIC, Mandanten-ID und dem Unterschriftsdatum des Mandates. Zusätzlich wird die SEPA-Kreditoren-Identifikation aus der Mandantenkonfiguration benötigt.', @@ -3323,6 +3352,7 @@ $self->{texts} = { 'and' => 'und', 'ap_aging_list' => 'liste_offene_verbindlichkeiten', 'ar_aging_list' => 'liste_offene_forderungen', + 'ar_chart isn\'t a valid chart' => 'Das Forderungskonto ist kein gültiges Konto.', 'as at' => 'zum Stand', 'assembly' => 'Erzeugnis', 'assembly_list' => 'erzeugnisliste', diff --git a/menus/user/00-erp.yaml b/menus/user/00-erp.yaml index 052853a77..4f9a69cd9 100644 --- a/menus/user/00-erp.yaml +++ b/menus/user/00-erp.yaml @@ -1257,6 +1257,13 @@ params: action: CsvImport/new profile.type: orders +- parent: system_import_csv + id: system_import_csv_ar_transactions + name: AR Transactions + order: 800 + params: + action: CsvImport/new + profile.type: ar_transactions - parent: system id: system_templates name: Templates diff --git a/t/controllers/csvimport/artransactions.t b/t/controllers/csvimport/artransactions.t new file mode 100644 index 000000000..dd0a8f1e3 --- /dev/null +++ b/t/controllers/csvimport/artransactions.t @@ -0,0 +1,611 @@ +use Test::More tests => 70; + +use strict; + +use lib 't'; + +use Carp; +use Data::Dumper; +use Support::TestSetup; +use Test::Exception; + +use List::MoreUtils qw(pairwise); +use SL::Controller::CsvImport; + +my $DEBUG = 0; + +use_ok 'SL::Controller::CsvImport::ARTransaction'; + +use SL::DB::Buchungsgruppe; +use SL::DB::Currency; +use SL::DB::Customer; +use SL::DB::Employee; +use SL::DB::Invoice; +use SL::DB::TaxZone; +use SL::DB::Chart; +use SL::DB::AccTransaction; + +my ($customer, $currency_id, $employee, $taxzone, $project, $department); + +sub reset_state { + # Create test data + my %params = @_; + + $params{$_} ||= {} for qw(buchungsgruppe customer tax); + + clear_up(); + $employee = SL::DB::Manager::Employee->current || croak "No employee"; + $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || croak "No taxzone"; + $currency_id = $::instance_conf->get_currency_id; + + $customer = SL::DB::Customer->new( + name => 'Test Customer', + currency_id => $currency_id, + taxzone_id => $taxzone->id, + %{ $params{customer} } + )->save; + + $project = SL::DB::Project->new( + projectnumber => 'P1', + description => 'Project X', + project_type => SL::DB::Manager::ProjectType->find_by(description => 'Standard'), + project_status => SL::DB::Manager::ProjectStatus->find_by(name => 'running'), + )->save; + + $department = SL::DB::Department->new( + description => 'Department 1', + )->save; +} + +Support::TestSetup::login(); + +reset_state(customer => {id => 960, customernumber => 2}); + +##### +sub test_import { + my $file = shift; + + my $controller = SL::Controller::CsvImport->new(); + + my $csv_artransactions_import = SL::Controller::CsvImport::ARTransaction->new( + settings => {'ar_column' => 'Rechnung', + 'transaction_column' => 'AccTransaction', + 'max_amount_diff' => 0.02 + }, + controller => $controller, + file => $file, + ); + + # $csv_artransactions_import->init_vc_by; + $csv_artransactions_import->test_run(0); + $csv_artransactions_import->csv(SL::Helper::Csv->new(file => $csv_artransactions_import->file, + profile => $csv_artransactions_import->profile, + encoding => 'utf-8', + ignore_unknown_columns => 1, + strict_profile => 1, + case_insensitive_header => 1, + sep_char => ',', + quote_char => '"', + ignore_unknown_columns => 1, + )); + + $csv_artransactions_import->csv->parse; + + $csv_artransactions_import->controller->errors([ $csv_artransactions_import->csv->errors ]) if $csv_artransactions_import->csv->errors; + + return if ( !$csv_artransactions_import->csv->header || $csv_artransactions_import->csv->errors ); + + my $headers; + my $i = 0; + foreach my $header (@{ $csv_artransactions_import->csv->header }) { + + my $profile = $csv_artransactions_import->csv->profile->[$i]->{profile}; + my $row_ident = $csv_artransactions_import->csv->profile->[$i]->{row_ident}; + + my $h = { headers => [ grep { $profile->{$_} } @{ $header } ] }; + $h->{methods} = [ map { $profile->{$_} } @{ $h->{headers} } ]; + $h->{used} = { map { ($_ => 1) } @{ $h->{headers} } }; + + $headers->{$row_ident} = $h; + $i++; + } + + $csv_artransactions_import->controller->headers($headers); + + my $raw_data_headers; + my $info_headers; + foreach my $p (@{ $csv_artransactions_import->csv->profile }) { + my $ident = $p->{row_ident}; + $raw_data_headers->{$ident} = { used => { }, headers => [ ] }; + $info_headers->{$ident} = { used => { }, headers => [ ] }; + } + $csv_artransactions_import->controller->raw_data_headers($raw_data_headers); + $csv_artransactions_import->controller->info_headers($info_headers); + + my $objects = $csv_artransactions_import->csv->get_objects; + my @raw_data = @{ $csv_artransactions_import->csv->get_data }; + + $csv_artransactions_import->controller->data([ pairwise { no warnings 'once'; { object => $a, raw_data => $b, errors => [], information => [], info_data => {} } } @$objects, @raw_data ]); + $csv_artransactions_import->check_objects; + + # don't try and save objects that have errors + $csv_artransactions_import->save_objects unless scalar @{$csv_artransactions_import->controller->data->[0]->{errors}}; + + return $csv_artransactions_import->controller->data; +} + +##### manually create an ar transaction from scratch, testing the methods +$::myconfig{numberformat} = '1000.00'; +my $old_locale = $::locale; +# set locale to en so we can match errors +$::locale = Locale->new('en'); + +my $amount = 10; + +my $ar = SL::DB::Invoice->new( + invoice => 0, + invnumber => 'manual invoice', + taxzone_id => $taxzone->id, + currency_id => $currency_id, + taxincluded => 'f', + customer_id => $customer->id, + transdate => DateTime->today, + employee_id => SL::DB::Manager::Employee->current->id, + transactions => [], +); + +my $tax3 = SL::DB::Manager::Tax->find_by(rate => 0.19, taxkey => 3) || die "can't find tax with taxkey 3"; +my $income_chart = SL::DB::Manager::Chart->find_by(accno => '8400') || die "can't find income chart"; + +$ar->add_ar_amount_row( + amount => $amount, + chart => $income_chart, + tax_id => $tax3->id, +); + +$ar->recalculate_amounts; # set amount and netamount from transactions +is $ar->amount, '10', 'amount of manual invoice is 10'; +is $ar->netamount, '8.4', 'netamount of manual invoice is 10'; + +$ar->create_ar_row( chart => SL::DB::Manager::Chart->find_by(accno => '1400', link => 'AR') ); +my $result = $ar->validate_acc_trans(debug => 0); +is $result, 1, 'manual $ar validates'; + +$ar->save; +is ${ $ar->transactions }[0]->chart->accno, '8400', 'assigned income chart after save ok'; +is ${ $ar->transactions }[2]->chart->accno, '1400', 'assigned receivable chart after save ok'; +is scalar @{$ar->transactions}, 3, 'manual invoice has 3 acc_trans entries'; + +$ar->pay_invoice( chart_id => SL::DB::Manager::Chart->find_by(accno => '1200')->id, # bank + amount => $ar->open_amount, + transdate => DateTime->now->to_kivitendo, + payment_type => 'without_skonto', # default if not specified + ); +$result = $ar->validate_acc_trans(debug => 0); +is $result, 1, 'manual invoice validates after payment'; + +reset_state(customer => {id => 960, customernumber => 2}); + +my ($entries, $entry, $file); + +# starting test of csv imports +# to debug errors in certain tests, run after test_import: +# die Dumper($entry->{errors}); +##### basic test +$file = \<[0]; +$entry->{object}->validate_acc_trans; + +is $entry->{object}->invnumber, 'invoice 1', 'simple invnumber ok (customer)'; +is $entry->{object}->customer_id, '960', 'simple customer_id ok (customer)'; +is scalar @{$entry->{object}->transactions}, 3, 'invoice 1 has 3 acc_trans entries'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), 159.48, 'invoice 1 ar amount is 159.48'; +is $entry->{object}->direct_debit, '0', 'no direct debit'; +is $entry->{object}->taxincluded, '0', 'taxincluded is false'; +is $entry->{object}->amount, '189.78', 'ar amount tax not included is 189.78'; +is $entry->{object}->netamount, '159.48', 'ar netamount tax not included is 159.48'; + +##### test for duplicate invnumber +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{errors}->[0], 'Error: invnumber already exists', 'detects verify_amount differences'; + +##### test for no invnumber given +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->invnumber =~ /^\d+$/, 1, 'invnumber assigned automatically'; + +##### basic test without amounts in Rechnung, only specified in AccTransaction +$file = \<[0]; +$entry->{object}->validate_acc_trans; + +is $entry->{object}->invnumber, 'invoice 1 no amounts', 'simple invnumber ok (customer)'; +is $entry->{object}->customer_id, '960', 'simple customer_id ok (customer)'; +is scalar @{$entry->{object}->transactions}, 3, 'invoice 1 has 3 acc_trans entries'; +is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'not taxincluded ar amount'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '159.48', 'not taxincluded acc_trans netamount'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), 159.48, 'invoice 1 ar amount is 159.48'; + +##### basic test: credit_note +$file = \<[0]; +$entry->{object}->validate_acc_trans; + +is $entry->{object}->invnumber, 'credit note', 'simple credit note ok'; +is scalar @{$entry->{object}->transactions}, 3, 'credit note has 3 acc_trans entries'; +is $::form->round_amount($entry->{object}->amount, 2), '-189.78', 'taxincluded ar amount'; +is $::form->round_amount($entry->{object}->netamount, 2), '-159.48', 'taxincluded ar net amount'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), -159.48, 'credit note ar amount is -159.48'; +is $entry->{object}->amount, '-189.78', 'credit note amount tax not included is 189.78'; +is $entry->{object}->netamount, '-159.48', 'credit note netamount tax not included is 159.48'; + +#### verify_amount differs: max_amount_diff = 0.02, 189.80 is ok, 189.81 is not +$file = \<[0]; +is $entry->{errors}->[0], 'Amounts differ too much', 'detects verify_amount differences'; + +##### direct debit +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->direct_debit, '1', 'direct debit'; + +#### tax included +$file = \<[0]; +$entry->{object}->validate_acc_trans(debug => 0); +is $entry->{object}->taxincluded, '1', 'taxincluded is true'; +is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'taxincluded ar amount'; +is $::form->round_amount($entry->{object}->netamount, 2), '159.48', 'taxincluded ar net amount'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '159.48', 'taxincluded acc_trans netamount'; + +#### multiple tax included +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'taxincluded ar amount'; +is $::form->round_amount($entry->{object}->netamount, 2), '159.48', 'taxincluded ar netamount'; +is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '79.74', 'taxincluded amount'; +is $::form->round_amount($entry->{object}->transactions->[1]->amount, 2), '15.15', 'taxincluded tax'; + +# different receivables chart +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->transactions->[2]->chart->accno, '1448', 'archart set to 1448'; + +# missing customer +$file = \<[0]; +is $entry->{errors}->[0], 'Error: Customer/vendor missing', 'detects missing customer or vendor'; + + +##### customer by name +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->customer->name, "Test Customer", 'detects customer by name'; + +##### detect missing chart +$file = \<[1]; +is $entry->{errors}->[0], 'Error: chart missing', 'detects missing chart (chart_id or accno)'; + +##### detect illegal chart by accno +$file = \<[1]; +is $entry->{errors}->[0], 'Error: invalid chart (accno)', 'detects invalid chart (chart_id or accno)'; + +# ##### detect illegal archart +$file = \<[0]; +is $entry->{errors}->[0], "Error: can't find ar chart with accno 11400", 'detects illegal receivables chart (archart)'; + +##### detect chart by id +$file = \<[1]; # acc_trans entry is at entry array pos 1 +$entries->[0]->{object}->validate_acc_trans; +is $entry->{object}->chart->id, "184", 'detects chart by id'; + +##### detect chart by accno +$file = \<[1]; +$entries->[0]->{object}->validate_acc_trans; +is $entry->{object}->chart->accno, "8400", 'detects chart by accno'; + +##### detect chart isn't an ar_chart +$file = \<[1]; +$entries->[0]->{object}->validate_acc_trans; +is $entry->{errors}->[0], 'Error: chart isn\'t an ar_amount chart', 'detects valid chart that is not an ar_amount chart'; + +# missing taxkey +$file = \<[1]; +is $entry->{errors}->[0], 'Error: taxkey missing', 'detects missing taxkey (DATEV Steuerschlüssel)'; + +# illegal taxkey +$file = \<[1]; +is $entry->{errors}->[0], 'Error: invalid taxkey', 'detects invalid taxkey (DATEV Steuerschlüssel)'; + +# taxkey +$file = \<[1]; +is $entry->{object}->taxkey, 3, 'detects taxkey'; + +# acc_trans project +$file = \<[1]; +# die Dumper($entries->[0]->{errors}) if scalar @{$entries->[0]->{errors}}; +is $entry->{object}->project->projectnumber, 'P1', 'detects acc_trans project'; + +##### various tests +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->duedate->to_kivitendo, '30.04.2016', 'duedate'; +is $entry->{object}->transdate->to_kivitendo, '21.04.2016', 'transdate'; +is $entry->{object}->globalproject->description, 'Project X', 'project'; +is $entry->{object}->department->description, 'Department 1', 'department'; +# 8300 is third entry after 8400 and tax for 8400 +is $::form->round_amount($entry->{object}->transactions->[2]->amount), '100', '8300 net amount: 100'; +is $::form->round_amount($entry->{object}->transactions->[2]->taxkey), '2', '8300 has taxkey 2'; +is $::form->round_amount($entry->{object}->transactions->[2]->project_id), $project->id, 'AccTrans project'; + +##### ar amount test +$file = \<[0]; +$entry->{object}->validate_acc_trans; +is $entry->{object}->duedate->to_kivitendo, '30.04.2016', 'duedate'; +is $entry->{info_data}->{amount}, '326', "First invoice amount displayed in info data"; +is $entries->[4]->{info_data}->{amount}, '326', "Second invoice amount displayed in info data"; + +# multiple entries, taxincluded = f +$file = \<{object}->isa('SL::DB::Invoice'); + $i++; + is scalar @{$entry->{object}->transactions}, 9, "invoice $i: 'invoice 4 acc_trans' has 9 acc_trans entries"; + $entry->{object}->validate_acc_trans; +}; + +##### missing acc_trans +$file = \<[0]; +is $entry->{errors}->[0], "Error: ar transaction doesn't validate", 'detects invalid ar, maybe acc_trans entry missing'; + +my $number_of_imported_invoices = SL::DB::Manager::Invoice->get_all_count; +is $number_of_imported_invoices, 19, 'All invoices saved'; + +#### taxkey differs from active_taxkey +$file = \<[0]; +$entry->{object}->validate_acc_trans(debug => 0); + +clear_up(); # remove all data at end of tests +# end of tests + + +sub clear_up { + SL::DB::Manager::AccTransaction->delete_all(all => 1); + SL::DB::Manager::Invoice->delete_all (all => 1); + SL::DB::Manager::Customer->delete_all (all => 1); + SL::DB::Manager::Project->delete_all (all => 1); + SL::DB::Manager::Department->delete_all (all => 1); +}; + + +1; + +##### +# vim: ft=perl +# set emacs to perl mode +# Local Variables: +# mode: perl +# End: diff --git a/templates/webpages/csv_import/_form_artransactions.html b/templates/webpages/csv_import/_form_artransactions.html new file mode 100644 index 000000000..eeb792241 --- /dev/null +++ b/templates/webpages/csv_import/_form_artransactions.html @@ -0,0 +1,15 @@ +[% USE LxERP %] +[% USE L %] + + [%- LxERP.t8('AR Transaction/AccTrans Item row names') %]: + + [% L.input_tag('settings.ar_column', SELF.profile.get('ar_column'), size => "10") %] + [% L.input_tag('settings.transaction_column', SELF.profile.get('transaction_column'), size => "20") %] + + + + [%- LxERP.t8('Maximal amount difference') %]: + + [% L.input_tag('settings.max_amount_diff', LxERP.format_amount(SELF.profile.get('max_amount_diff')), size => "5") %] + + diff --git a/templates/webpages/csv_import/form.html b/templates/webpages/csv_import/form.html index 12162f6a7..792aa2086 100644 --- a/templates/webpages/csv_import/form.html +++ b/templates/webpages/csv_import/form.html @@ -135,7 +135,7 @@ [%- LxERP.t8('One of the columns "qty" or "target_qty" must be given. If "target_qty" is given, the quantity to transfer for each transfer will be calculate, so that the quantity for this part, warehouse and bin will result in the given "target_qty" after each transfer.') %]

-[%- ELSIF SELF.type == 'orders' %] +[%- ELSIF SELF.type == 'orders' OR SELF.type == 'ar_transactions' %]

[1]: [% LxERP.t8('The column "datatype" must be present and must be at the same position / column in each data set. The values must be the row names (see settings) for order and item data respectively.') %] @@ -260,6 +260,8 @@ [%- INCLUDE 'csv_import/_form_inventories.html' %] [%- ELSIF SELF.type == 'orders' %] [%- INCLUDE 'csv_import/_form_orders.html' %] +[%- ELSIF SELF.type == 'ar_transactions' %] + [%- INCLUDE 'csv_import/_form_artransactions.html' %] [%- ELSIF SELF.type == 'bank_transactions' %] [%- INCLUDE 'csv_import/_form_banktransactions.html' %] [%- END %] -- 2.20.1