1 package SL::Controller::CsvImport::ARTransaction;
5 use List::MoreUtils qw(any);
9 use SL::Controller::CsvImport::Helper::Consistency;
11 use SL::DB::AccTransaction;
12 use SL::DB::Department;
19 use parent qw(SL::Controller::CsvImport::BaseMulti);
21 use Rose::Object::MakeMethods::Generic
23 'scalar --get_set_init' => [ qw(settings charts_by taxkeys_by) ],
29 $self->class(['SL::DB::Invoice', 'SL::DB::AccTransaction']);
32 sub set_profile_defaults {
35 $self->controller->profile->_set_defaults(
36 ar_column => $::locale->text('Invoice'),
37 transaction_column => $::locale->text('AccTransaction'),
38 max_amount_diff => 0.02,
46 return { map { ( $_ => $self->controller->profile->get($_) ) } qw(ar_column transaction_column max_amount_diff) };
52 my $profile = $self->SUPER::init_profile;
54 # SUPER::init_profile sets row_ident to the translated class name
55 # overwrite it with the user specified settings
56 # TODO: remove hardcoded row_idents
57 foreach my $p (@{ $profile }) {
58 if ($p->{class} eq 'SL::DB::Invoice') {
59 $p->{row_ident} = $self->_ar_column;
61 if ($p->{class} eq 'SL::DB::AccTransaction') {
62 $p->{row_ident} = $self->_transaction_column;
66 foreach my $p (@{ $profile }) {
67 my $prof = $p->{profile};
68 if ($p->{row_ident} eq $self->_ar_column) {
70 delete @{$prof}{qw(delivery_customer_id delivery_vendor_id )};
72 if ($p->{row_ident} eq $self->_transaction_column) {
74 delete @{$prof}{qw(trans_id)};
82 sub setup_displayable_columns {
85 $self->SUPER::setup_displayable_columns;
87 $self->add_displayable_columns($self->_ar_column,
88 { name => 'datatype', description => $self->_ar_column . ' [1]' },
89 { name => 'currency', description => $::locale->text('Currency') },
90 { name => 'cusordnumber', description => $::locale->text('Customer Order Number') },
91 { name => 'direct_debit', description => $::locale->text('direct debit') },
92 { name => 'donumber', description => $::locale->text('Delivery Order Number') },
93 { name => 'duedate', description => $::locale->text('Due Date') },
94 { name => 'delivery_term_id', description => $::locale->text('Delivery terms (database ID)') },
95 { name => 'delivery_term', description => $::locale->text('Delivery terms (name)') },
96 { name => 'deliverydate', description => $::locale->text('Delivery Date') },
97 { name => 'employee_id', description => $::locale->text('Employee (database ID)') },
98 { name => 'intnotes', description => $::locale->text('Internal Notes') },
99 { name => 'notes', description => $::locale->text('Notes') },
100 { name => 'invnumber', description => $::locale->text('Invoice Number') },
101 { name => 'quonumber', description => $::locale->text('Quotation Number') },
102 { name => 'reqdate', description => $::locale->text('Reqdate') },
103 { name => 'salesman_id', description => $::locale->text('Salesman (database ID)') },
104 { name => 'transaction_description', description => $::locale->text('Transaction description') },
105 { name => 'transdate', description => $::locale->text('Invoice Date') },
106 { name => 'verify_amount', description => $::locale->text('Amount (for verification)') . ' [2]' },
107 { name => 'verify_netamount', description => $::locale->text('Net amount (for verification)') . ' [2]'},
108 { name => 'taxincluded', description => $::locale->text('Tax Included') },
109 { name => 'customer', description => $::locale->text('Customer (name)') },
110 { name => 'customernumber', description => $::locale->text('Customer Number') },
111 { name => 'customer_gln', description => $::locale->text('Customer GLN') },
112 { name => 'customer_id', description => $::locale->text('Customer (database ID)') },
113 { name => 'language_id', description => $::locale->text('Language (database ID)') },
114 { name => 'language', description => $::locale->text('Language (name)') },
115 { name => 'payment_id', description => $::locale->text('Payment terms (database ID)') },
116 { name => 'payment', description => $::locale->text('Payment terms (name)') },
117 { name => 'taxzone_id', description => $::locale->text('Tax zone (database ID)') },
118 { name => 'taxzone', description => $::locale->text('Tax zone (description)') },
119 { name => 'department_id', description => $::locale->text('Department (database ID)') },
120 { name => 'department', description => $::locale->text('Department (description)') },
121 { name => 'globalproject_id', description => $::locale->text('Document Project (database ID)') },
122 { name => 'globalprojectnumber', description => $::locale->text('Document Project (number)') },
123 { name => 'globalproject', description => $::locale->text('Document Project (description)') },
124 { name => 'archart', description => $::locale->text('Receivables account (account number)') },
125 { name => 'orddate', description => $::locale->text('Order Date') },
126 { name => 'ordnumber', description => $::locale->text('Order Number') },
127 { name => 'quonumber', description => $::locale->text('Quotation Number') },
128 { name => 'quodate', description => $::locale->text('Quotation Date') },
131 $self->add_displayable_columns($self->_transaction_column,
132 { name => 'datatype', description => $self->_transaction_column . ' [1]' },
133 { name => 'projectnumber', description => $::locale->text('Project (number)') },
134 { name => 'project', description => $::locale->text('Project (description)') },
135 { name => 'amount', description => $::locale->text('Amount') },
136 { name => 'accno', description => $::locale->text('Account number') },
137 { name => 'taxkey', description => $::locale->text('Taxkey') },
141 sub init_taxkeys_by {
144 my $all_taxes = SL::DB::Manager::Tax->get_all;
145 return { map { $_->taxkey => $_->id } @{ $all_taxes } };
152 my $all_charts = SL::DB::Manager::Chart->get_all;
153 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_charts } } ) } qw(id accno) };
159 $self->controller->track_progress(phase => 'building data', progress => 0);
162 my $num_data = scalar @{ $self->controller->data };
165 foreach my $entry (@{ $self->controller->data }) {
166 $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0;
168 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
169 $self->handle_invoice($entry);
170 $invoice_entry = $entry;
171 } elsif ($entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
172 die "Cannot process transaction row without an invoice row" if !$invoice_entry;
173 $self->handle_transaction($entry, $invoice_entry);
175 die "unknown datatype";
180 } # finished data parsing
182 $self->add_transactions_to_ar(); # go through all data entries again, adding receivable entry to ar lines while calculating amount and netamount
184 foreach my $entry (@{ $self->controller->data }) {
185 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
186 $self->check_verify_amounts($entry->{object});
189 foreach my $entry (@{ $self->controller->data }) {
190 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
191 unless ( $entry->{object}->validate_acc_trans ) {
192 push @{ $entry->{errors} }, $::locale->text('Error: ar transaction doesn\'t validate');
196 # add info columns that aren't directly part of the object to be imported
197 # but are always determined or should always be shown because they are mandatory
198 $self->add_info_columns($self->_ar_column,
199 { header => $::locale->text('Customer/Vendor'), method => 'vc_name' },
200 { header => $::locale->text('Receivables account'), method => 'archart' },
201 { header => $::locale->text('Amount'), method => 'amount' },
202 { header => $::locale->text('Net amount'), method => 'netamount' },
203 { header => $::locale->text('Tax zone'), method => 'taxzone' });
205 # Adding info_header this way only works, if the first invoice $self->controller->data->[0]
207 # Todo: access via ->[0] ok? Better: search first order column and use this
208 $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};
210 $self->add_info_columns($self->_ar_column, { header => $::locale->text('Project Number'), method => 'globalprojectnumber' }) if $self->controller->data->[0]->{info_data}->{globalprojectnumber};
212 $self->add_columns($self->_ar_column,
213 map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw(payment department globalproject taxzone cp currency));
214 $self->add_columns($self->_ar_column, 'globalproject_id') if exists $self->controller->data->[0]->{raw_data}->{globalprojectnumber};
215 $self->add_columns($self->_ar_column, 'notes') if exists $self->controller->data->[0]->{raw_data}->{notes};
217 # Todo: access via ->[1] ok? Better: search first item column and use this
218 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Chart'), method => 'accno' });
219 $self->add_columns($self->_transaction_column, 'amount');
221 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Project Number'), method => 'projectnumber' }) if $self->controller->data->[1]->{info_data}->{projectnumber};
223 # $self->add_columns($self->_transaction_column,
224 # map { "${_}_id" } grep { exists $self->controller->data->[1]->{raw_data}->{$_} } qw(project price_factor pricegroup));
225 # $self->add_columns($self->_transaction_column,
226 # map { "${_}_id" } grep { exists $self->controller->data->[2]->{raw_data}->{$_} } qw(project price_factor pricegroup));
227 # $self->add_columns($self->_transaction_column, 'project_id') if exists $self->controller->data->[1]->{raw_data}->{projectnumber};
228 # $self->add_columns($self->_transaction_column, 'taxkey') if exists $self->controller->data->[1]->{raw_data}->{taxkey};
230 # If invoice has errors, add error for acc_trans items
231 # If acc_trans item has an error, add an error to the invoice item
233 foreach my $entry (@{ $self->controller->data }) {
235 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
237 } elsif ( defined $ar_entry
238 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
239 && scalar @{ $ar_entry->{errors} } > 0 ) {
240 push @{ $entry->{errors} }, $::locale->text('Error: invalid ar row for this transaction');
241 } elsif ( defined $ar_entry
242 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
243 && scalar @{ $entry->{errors} } > 0 ) {
244 push @{ $ar_entry->{errors} }, $::locale->text('Error: invalid acc transactions for this ar row');
251 my ($self, $entry) = @_;
253 my $object = $entry->{object};
255 $object->transactions( [] ); # initialise transactions for ar object so methods work on unsaved transactions
258 if (any { $entry->{raw_data}->{$_} } qw(customer customernumber customer_gln customer_id)) {
259 $self->check_vc($entry, 'customer_id');
260 # check_vc only sets customer_id, but we need vc_obj later for customer defaults
261 $vc_obj = SL::DB::Customer->new(id => $object->customer_id)->load if $object->customer_id;
262 } elsif (any { $entry->{raw_data}->{$_} } qw(vendor vendornumber vendor_gln vendor_id)) {
263 $self->check_vc($entry, 'vendor_id');
264 $vc_obj = SL::DB::Vendor->new(id => $object->vendor_id)->load if $object->vendor_id;
266 push @{ $entry->{errors} }, $::locale->text('Error: Customer/vendor missing');
269 # check for duplicate invnumbers already in database
270 if ( SL::DB::Manager::Invoice->get_all_count( where => [ invnumber => $object->invnumber ] ) ) {
271 push @{ $entry->{errors} }, $::locale->text('Error: invnumber already exists');
274 $self->check_archart($entry); # checks for receivable account
275 # $self->check_amounts($entry); # checks and sets amount and netamount, use verify_amount and verify_netamount instead
276 $self->check_payment($entry); # currency default from customer used below
277 $self->check_department($entry);
278 $self->check_taxincluded($entry);
279 $self->check_project($entry, global => 1);
280 $self->check_taxzone($entry); # taxzone default from customer used below
281 $self->check_currency($entry); # currency default from customer used below
282 $self->handle_salesman($entry);
283 $self->handle_employee($entry);
286 # copy defaults from customer if not specified in import file
287 foreach (qw(payment_id language_id taxzone_id currency_id)) {
288 $object->$_($vc_obj->$_) unless $object->$_;
294 my ($self, $entry, $invoice_entry, $chart) = @_;
296 die "check_taxkey needs chart object as an argument" unless ref($chart) eq 'SL::DB::Chart';
297 # problem: taxkey is not unique in table tax, normally one of those entries is chosen directly from a dropdown
298 # so we check if the chart has an active taxkey, and if it matches the taxkey from the import, use the active taxkey
299 # if the chart doesn't have an active taxkey, use the first entry from Tax that matches the taxkey
301 my $object = $entry->{object};
302 my $invoice_object = $invoice_entry->{object};
304 unless ( defined $entry->{raw_data}->{taxkey} ) {
305 push @{ $entry->{errors} }, $::locale->text('Error: taxkey missing'); # don't just assume 0, force taxkey in import
309 my $tax = $chart->get_active_taxkey($invoice_object->deliverydate // $invoice_object->transdate // DateTime->today_local)->tax;
310 if ( $entry->{raw_data}->{taxkey} != $tax->taxkey ) {
311 # assume there is only one tax entry with that taxkey, can't guess
312 $tax = SL::DB::Manager::Tax->get_first( where => [ taxkey => $entry->{raw_data}->{taxkey} ]);
316 push @{ $entry->{errors} }, $::locale->text('Error: invalid taxkey');
320 $object->taxkey($tax->taxkey);
321 $object->tax_id($tax->id);
326 my ($self, $entry) = @_;
327 # currently not used in favour of verify_amount and verify_netamount
329 my $object = $entry->{object};
331 unless ($entry->{raw_data}->{amount} && $entry->{raw_data}->{netamount}) {
332 push @{ $entry->{errors} }, $::locale->text('Error: need amount and netamount');
335 unless ($entry->{raw_data}->{amount} * 1 && $entry->{raw_data}->{netamount} * 1) {
336 push @{ $entry->{errors} }, $::locale->text('Error: amount and netamount need to be numeric');
340 $object->amount( $entry->{raw_data}->{amount} );
341 $object->netamount( $entry->{raw_data}->{netamount} );
344 sub handle_transaction {
345 my ($self, $entry, $invoice_entry) = @_;
347 # Prepare acc_trans data. amount is dealt with in add_transactions_to_ar
349 my $object = $entry->{object};
351 $self->check_project($entry, global => 0);
352 if ( $self->check_chart($entry) ) {
353 my $chart_obj = SL::DB::Manager::Chart->find_by(id => $object->chart_id);
355 unless ( $chart_obj->link =~ /AR_amount/ ) {
356 push @{ $entry->{errors} }, $::locale->text('Error: chart isn\'t an ar_amount chart');
360 if ( $self->check_taxkey($entry, $invoice_entry, $chart_obj) ) {
361 # do nothing, taxkey was assigned, just continue
363 # missing taxkey, don't do anything
370 # check whether taxkey and automatic taxkey match
371 # 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;
373 die "no taxkey for transaction object" unless $object->taxkey or $object->taxkey == 0;
378 my ($self, $entry) = @_;
380 my $object = $entry->{object};
382 if (any { $entry->{raw_data}->{$_} } qw(accno chart_id)) {
384 # Check whether or not chart ID is valid.
385 if ($object->chart_id && !$self->charts_by->{id}->{ $object->chart_id }) {
386 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart_id');
390 # Map number to ID if given.
391 if (!$object->chart_id && $entry->{raw_data}->{accno}) {
392 my $chart = $self->charts_by->{accno}->{ $entry->{raw_data}->{accno} };
394 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart (accno)');
398 $object->chart_id($chart->id);
401 # Map description to ID if given.
402 if (!$object->chart_id && $entry->{raw_data}->{description}) {
403 my $chart = $self->charts_by->{description}->{ $entry->{raw_data}->{description} };
405 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart');
409 $object->chart_id($chart->id);
412 if ($object->chart_id) {
413 # add account number to preview
414 $entry->{info_data}->{accno} = $self->charts_by->{id}->{ $object->chart_id }->accno;
416 push @{ $entry->{errors} }, $::locale->text('Error: chart not found');
420 push @{ $entry->{errors} }, $::locale->text('Error: chart missing');
428 my ($self, $entry) = @_;
432 if ( $entry->{raw_data}->{archart} ) {
433 my $archart = $entry->{raw_data}->{archart};
434 $chart = SL::DB::Manager::Chart->find_by(accno => $archart);
436 push @{ $entry->{errors} }, $::locale->text("Error: can't find ar chart with accno #1", $archart);
439 } elsif ( $::instance_conf->get_ar_chart_id ) {
440 $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
442 push @{ $entry->{errors} }, $::locale->text("Error: neither archart passed, no default receivables chart configured");
446 unless ($chart->link eq 'AR') {
447 push @{ $entry->{errors} }, $::locale->text('Error: archart isn\'t an AR chart');
451 $entry->{info_data}->{archart} = $chart->accno;
452 $entry->{object}->{archart} = $chart;
456 sub check_taxincluded {
457 my ($self, $entry) = @_;
459 my $object = $entry->{object};
461 if ( $entry->{raw_data}->{taxincluded} ) {
462 if ( $entry->{raw_data}->{taxincluded} eq 'f' or $entry->{raw_data}->{taxincluded} eq '0' ) {
463 $object->taxincluded('0');
464 } elsif ( $entry->{raw_data}->{taxincluded} eq 't' or $entry->{raw_data}->{taxincluded} eq '1' ) {
465 $object->taxincluded('1');
467 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded has to be t or f');
471 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded wasn\'t set');
477 sub check_verify_amounts {
480 # If amounts are given, show calculated amounts as info and given amounts (verify_xxx).
481 # And throw an error if the differences are too big.
482 my @to_verify = ( { column => 'amount',
483 raw_column => 'verify_amount',
484 info_header => 'Calc. Amount',
485 info_method => 'calc_amount',
486 err_msg => $::locale->text('Amounts differ too much'),
488 { column => 'netamount',
489 raw_column => 'verify_netamount',
490 info_header => 'Calc. Net amount',
491 info_method => 'calc_netamount',
492 err_msg => $::locale->text('Net amounts differ too much'),
495 foreach my $tv (@to_verify) {
496 if (exists $self->controller->data->[0]->{raw_data}->{ $tv->{raw_column} }) {
497 $self->add_raw_data_columns($self->_ar_column, $tv->{raw_column});
498 $self->add_info_columns($self->_ar_column,
499 { header => $::locale->text($tv->{info_header}), method => $tv->{info_method} });
503 foreach my $entry (@{ $self->controller->data }) {
504 if ( @{ $entry->{errors} } ) {
505 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
509 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
510 next if !$entry->{raw_data}->{ $tv->{raw_column} };
511 my $parsed_value = $::form->parse_amount(\%::myconfig, $entry->{raw_data}->{ $tv->{raw_column} });
512 # round $abs_diff, otherwise it might trigger for 0.020000000000021
513 my $abs_diff = $::form->round_amount(abs($entry->{object}->${ \$tv->{column} } - $parsed_value),2);
514 if ( $abs_diff > $self->settings->{'max_amount_diff'}) {
515 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
522 sub add_transactions_to_ar {
525 # go through all verified ar and acc_trans rows in import, adding acc_trans objects to ar objects
527 my $ar_entry; # the current ar row
529 foreach my $entry (@{ $self->controller->data }) {
530 # when we reach an ar_column for the first time, don't do anything, just store in $ar_entry
531 # when we reach an ar_column for the second time, save it
532 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
533 if ( $ar_entry && $ar_entry->{object} ) { # won't trigger the first time, finishes the last object
534 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
535 $ar_entry->{object}->recalculate_amounts; # determine and set amount and netamount for ar
536 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
537 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
538 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
540 push @{ $entry->{errors} }, $::locale->text("ar_chart isn't a valid chart");
543 $ar_entry = $entry; # remember as last ar_entry
545 } elsif ( defined $ar_entry && $entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
546 push @{ $entry->{errors} }, $::locale->text('no tax_id in acc_trans') if !defined $entry->{object}->tax_id;
547 next if @{ $entry->{errors} };
549 my $acc_trans_objects = $ar_entry->{object}->add_ar_amount_row(
550 amount => $entry->{object}->amount,
551 chart => SL::DB::Manager::Chart->find_by(id => $entry->{object}->chart_id), # add_ar_amount takes chart obj. as argument
552 tax_id => $entry->{object}->tax_id,
553 project_id => $entry->{object}->project_id,
558 die "This should never happen\n";
562 # finish the final object
563 if ( $ar_entry->{object} ) {
564 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
565 $ar_entry->{object}->recalculate_amounts;
566 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
567 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
569 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
571 push @{ $ar_entry->{errors} }, $::locale->text("The receivables chart isn't a valid chart.");
575 die "There was no final ar_entry object";
580 my ($self, %params) = @_;
582 # save all the Invoice objects
584 foreach my $entry (@{ $self->controller->data }) {
585 # only push the invoice objects that don't have an error
586 next if $entry->{raw_data}->{datatype} ne $self->_ar_column;
587 next if @{ $entry->{errors} };
589 die unless $entry->{object}->validate_acc_trans;
591 push @{ $objects_to_save }, $entry;
594 $self->SUPER::save_objects(data => $objects_to_save);
598 $_[0]->settings->{'ar_column'}
601 sub _transaction_column {
602 $_[0]->settings->{'transaction_column'}