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 => 'chart', 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 };
164 foreach my $entry (@{ $self->controller->data }) {
165 $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0;
167 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
168 $self->handle_invoice($entry);
169 } elsif ($entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
170 $self->handle_transaction($entry);
172 die "unknown datatype";
177 } # finished data parsing
179 $self->add_transactions_to_ar(); # go through all data entries again, adding receivable entry to ar lines while calculating amount and netamount
181 foreach my $entry (@{ $self->controller->data }) {
182 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
183 $self->check_verify_amounts($entry->{object});
186 foreach my $entry (@{ $self->controller->data }) {
187 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
188 unless ( $entry->{object}->validate_acc_trans ) {
189 push @{ $entry->{errors} }, $::locale->text('Error: ar transaction doesn\'t validate');
193 # add info columns that aren't directly part of the object to be imported
194 # but are always determined or should always be shown because they are mandatory
195 $self->add_info_columns($self->_ar_column,
196 { header => $::locale->text('Customer/Vendor'), method => 'vc_name' },
197 { header => $::locale->text('Receivables account'), method => 'archart' },
198 { header => $::locale->text('Amount'), method => 'amount' },
199 { header => $::locale->text('Net amount'), method => 'netamount' },
200 { header => $::locale->text('Tax zone'), method => 'taxzone' });
202 # Adding info_header this way only works, if the first invoice $self->controller->data->[0]
204 # Todo: access via ->[0] ok? Better: search first order column and use this
205 $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};
207 $self->add_info_columns($self->_ar_column, { header => $::locale->text('Project Number'), method => 'globalprojectnumber' }) if $self->controller->data->[0]->{info_data}->{globalprojectnumber};
209 $self->add_columns($self->_ar_column,
210 map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw(payment department globalproject taxzone cp currency));
211 $self->add_columns($self->_ar_column, 'globalproject_id') if exists $self->controller->data->[0]->{raw_data}->{globalprojectnumber};
212 $self->add_columns($self->_ar_column, 'notes') if exists $self->controller->data->[0]->{raw_data}->{notes};
214 # Todo: access via ->[1] ok? Better: search first item column and use this
215 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Chart'), method => 'accno' });
216 $self->add_columns($self->_transaction_column, 'amount');
218 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Project Number'), method => 'projectnumber' }) if $self->controller->data->[1]->{info_data}->{projectnumber};
220 # $self->add_columns($self->_transaction_column,
221 # map { "${_}_id" } grep { exists $self->controller->data->[1]->{raw_data}->{$_} } qw(project price_factor pricegroup));
222 # $self->add_columns($self->_transaction_column,
223 # map { "${_}_id" } grep { exists $self->controller->data->[2]->{raw_data}->{$_} } qw(project price_factor pricegroup));
224 # $self->add_columns($self->_transaction_column, 'project_id') if exists $self->controller->data->[1]->{raw_data}->{projectnumber};
225 # $self->add_columns($self->_transaction_column, 'taxkey') if exists $self->controller->data->[1]->{raw_data}->{taxkey};
227 # If invoice has errors, add error for acc_trans items
228 # If acc_trans item has an error, add an error to the invoice item
230 foreach my $entry (@{ $self->controller->data }) {
232 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
234 } elsif ( defined $ar_entry
235 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
236 && scalar @{ $ar_entry->{errors} } > 0 ) {
237 push @{ $entry->{errors} }, $::locale->text('Error: invalid ar row for this transaction');
238 } elsif ( defined $ar_entry
239 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
240 && scalar @{ $entry->{errors} } > 0 ) {
241 push @{ $ar_entry->{errors} }, $::locale->text('Error: invalid acc transactions for this ar row');
248 my ($self, $entry) = @_;
250 my $object = $entry->{object};
252 $object->transactions( [] ); # initialise transactions for ar object so methods work on unsaved transactions
255 if (any { $entry->{raw_data}->{$_} } qw(customer customernumber customer_gln customer_id)) {
256 $self->check_vc($entry, 'customer_id');
257 # check_vc only sets customer_id, but we need vc_obj later for customer defaults
258 $vc_obj = SL::DB::Customer->new(id => $object->customer_id)->load if $object->customer_id;
259 } elsif (any { $entry->{raw_data}->{$_} } qw(vendor vendornumber vendor_gln vendor_id)) {
260 $self->check_vc($entry, 'vendor_id');
261 $vc_obj = SL::DB::Vendor->new(id => $object->vendor_id)->load if $object->vendor_id;
263 push @{ $entry->{errors} }, $::locale->text('Error: Customer/vendor missing');
266 # check for duplicate invnumbers already in database
267 if ( SL::DB::Manager::Invoice->get_all_count( where => [ invnumber => $object->invnumber ] ) ) {
268 push @{ $entry->{errors} }, $::locale->text('Error: invnumber already exists');
271 $self->check_archart($entry); # checks for receivable account
272 # $self->check_amounts($entry); # checks and sets amount and netamount, use verify_amount and verify_netamount instead
273 $self->check_payment($entry); # currency default from customer used below
274 $self->check_department($entry);
275 $self->check_taxincluded($entry);
276 $self->check_project($entry, global => 1);
277 $self->check_taxzone($entry); # taxzone default from customer used below
278 $self->check_currency($entry); # currency default from customer used below
279 $self->handle_salesman($entry);
280 $self->handle_employee($entry);
283 # copy defaults from customer if not specified in import file
284 foreach (qw(payment_id language_id taxzone_id currency_id)) {
285 $object->$_($vc_obj->$_) unless $object->$_;
291 my ($self, $entry, $chart) = @_;
293 die "check_taxkey needs chart object as an argument" unless ref($chart) eq 'SL::DB::Chart';
294 # problem: taxkey is not unique in table tax, normally one of those entries is chosen directly from a dropdown
295 # so we check if the chart has an active taxkey, and if it matches the taxkey from the import, use the active taxkey
296 # if the chart doesn't have an active taxkey, use the first entry from Tax that matches the taxkey
298 my $object = $entry->{object};
299 unless ( defined $entry->{raw_data}->{taxkey} ) {
300 push @{ $entry->{errors} }, $::locale->text('Error: taxkey missing'); # don't just assume 0, force taxkey in import
306 if ( $entry->{raw_data}->{taxkey} == $chart->get_active_taxkey->tax->taxkey ) {
307 $tax = $chart->get_active_taxkey->tax;
309 # assume there is only one tax entry with that taxkey, can't guess
310 $tax = SL::DB::Manager::Tax->get_first( where => [ taxkey => $entry->{raw_data}->{taxkey} ]);
314 push @{ $entry->{errors} }, $::locale->text('Error: invalid taxkey');
318 $object->taxkey($tax->taxkey);
319 $object->tax_id($tax->id);
324 my ($self, $entry) = @_;
325 # currently not used in favour of verify_amount and verify_netamount
327 my $object = $entry->{object};
329 unless ($entry->{raw_data}->{amount} && $entry->{raw_data}->{netamount}) {
330 push @{ $entry->{errors} }, $::locale->text('Error: need amount and netamount');
333 unless ($entry->{raw_data}->{amount} * 1 && $entry->{raw_data}->{netamount} * 1) {
334 push @{ $entry->{errors} }, $::locale->text('Error: amount and netamount need to be numeric');
338 $object->amount( $entry->{raw_data}->{amount} );
339 $object->netamount( $entry->{raw_data}->{netamount} );
342 sub handle_transaction {
343 my ($self, $entry) = @_;
345 # Prepare acc_trans data. amount is dealt with in add_transactions_to_ar
347 my $object = $entry->{object};
349 $self->check_project($entry, global => 0);
350 if ( $self->check_chart($entry) ) {
351 my $chart_obj = SL::DB::Manager::Chart->find_by(id => $object->chart_id);
353 unless ( $chart_obj->link =~ /AR_amount/ ) {
354 push @{ $entry->{errors} }, $::locale->text('Error: chart isn\'t an ar_amount chart');
358 if ( $self->check_taxkey($entry, $chart_obj) ) {
359 # do nothing, taxkey was assigned, just continue
361 # missing taxkey, don't do anything
368 # check whether taxkey and automatic taxkey match
369 # 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;
371 die "no taxkey for transaction object" unless $object->taxkey or $object->taxkey == 0;
376 my ($self, $entry) = @_;
378 my $object = $entry->{object};
380 if (any { $entry->{raw_data}->{$_} } qw(accno chart_id)) {
382 # Check whether or not chart ID is valid.
383 if ($object->chart_id && !$self->charts_by->{id}->{ $object->chart_id }) {
384 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart_id');
388 # Map number to ID if given.
389 if (!$object->chart_id && $entry->{raw_data}->{accno}) {
390 my $chart = $self->charts_by->{accno}->{ $entry->{raw_data}->{accno} };
392 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart (accno)');
396 $object->chart_id($chart->id);
399 # Map description to ID if given.
400 if (!$object->chart_id && $entry->{raw_data}->{description}) {
401 my $chart = $self->charts_by->{description}->{ $entry->{raw_data}->{description} };
403 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart');
407 $object->chart_id($chart->id);
410 if ($object->chart_id) {
411 # add account number to preview
412 $entry->{info_data}->{accno} = $self->charts_by->{id}->{ $object->chart_id }->accno;
414 push @{ $entry->{errors} }, $::locale->text('Error: chart not found');
418 push @{ $entry->{errors} }, $::locale->text('Error: chart missing');
426 my ($self, $entry) = @_;
430 if ( $entry->{raw_data}->{archart} ) {
431 my $archart = $entry->{raw_data}->{archart};
432 $chart = SL::DB::Manager::Chart->find_by(accno => $archart);
434 push @{ $entry->{errors} }, $::locale->text("Error: can't find ar chart with accno #1", $archart);
437 } elsif ( $::instance_conf->get_ar_chart_id ) {
438 $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
440 push @{ $entry->{errors} }, $::locale->text("Error: neither archart passed, no default receivables chart configured");
444 unless ($chart->link eq 'AR') {
445 push @{ $entry->{errors} }, $::locale->text('Error: archart isn\'t an AR chart');
449 $entry->{info_data}->{archart} = $chart->accno;
450 $entry->{object}->{archart} = $chart;
454 sub check_taxincluded {
455 my ($self, $entry) = @_;
457 my $object = $entry->{object};
459 if ( $entry->{raw_data}->{taxincluded} ) {
460 if ( $entry->{raw_data}->{taxincluded} eq 'f' or $entry->{raw_data}->{taxincluded} eq '0' ) {
461 $object->taxincluded('0');
462 } elsif ( $entry->{raw_data}->{taxincluded} eq 't' or $entry->{raw_data}->{taxincluded} eq '1' ) {
463 $object->taxincluded('1');
465 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded has to be t or f');
469 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded wasn\'t set');
475 sub check_verify_amounts {
478 # If amounts are given, show calculated amounts as info and given amounts (verify_xxx).
479 # And throw an error if the differences are too big.
480 my @to_verify = ( { column => 'amount',
481 raw_column => 'verify_amount',
482 info_header => 'Calc. Amount',
483 info_method => 'calc_amount',
484 err_msg => $::locale->text('Amounts differ too much'),
486 { column => 'netamount',
487 raw_column => 'verify_netamount',
488 info_header => 'Calc. Net amount',
489 info_method => 'calc_netamount',
490 err_msg => $::locale->text('Net amounts differ too much'),
493 foreach my $tv (@to_verify) {
494 if (exists $self->controller->data->[0]->{raw_data}->{ $tv->{raw_column} }) {
495 $self->add_raw_data_columns($self->_ar_column, $tv->{raw_column});
496 $self->add_info_columns($self->_ar_column,
497 { header => $::locale->text($tv->{info_header}), method => $tv->{info_method} });
501 foreach my $entry (@{ $self->controller->data }) {
502 if ( @{ $entry->{errors} } ) {
503 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
507 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
508 next if !$entry->{raw_data}->{ $tv->{raw_column} };
509 my $parsed_value = $::form->parse_amount(\%::myconfig, $entry->{raw_data}->{ $tv->{raw_column} });
510 # round $abs_diff, otherwise it might trigger for 0.020000000000021
511 my $abs_diff = $::form->round_amount(abs($entry->{object}->${ \$tv->{column} } - $parsed_value),2);
512 if ( $abs_diff > $self->settings->{'max_amount_diff'}) {
513 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
520 sub add_transactions_to_ar {
523 # go through all verified ar and acc_trans rows in import, adding acc_trans objects to ar objects
525 my $ar_entry; # the current ar row
527 foreach my $entry (@{ $self->controller->data }) {
528 # when we reach an ar_column for the first time, don't do anything, just store in $ar_entry
529 # when we reach an ar_column for the second time, save it
530 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
531 if ( $ar_entry && $ar_entry->{object} ) { # won't trigger the first time, finishes the last object
532 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
533 $ar_entry->{object}->recalculate_amounts; # determine and set amount and netamount for ar
534 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
535 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
536 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
538 push @{ $entry->{errors} }, $::locale->text("ar_chart isn't a valid chart");
541 $ar_entry = $entry; # remember as last ar_entry
543 } elsif ( defined $ar_entry && $entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
544 push @{ $entry->{errors} }, $::locale->text('no tax_id in acc_trans') if !defined $entry->{object}->tax_id;
545 next if @{ $entry->{errors} };
547 my $acc_trans_objects = $ar_entry->{object}->add_ar_amount_row(
548 amount => $entry->{object}->amount,
549 chart => SL::DB::Manager::Chart->find_by(id => $entry->{object}->chart_id), # add_ar_amount takes chart obj. as argument
550 tax_id => $entry->{object}->tax_id,
551 project_id => $entry->{object}->project_id,
556 die "This should never happen\n";
560 # finish the final object
561 if ( $ar_entry->{object} ) {
562 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
563 $ar_entry->{object}->recalculate_amounts;
564 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
565 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
567 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
569 push @{ $ar_entry->{errors} }, $::locale->text("The receivables chart isn't a valid chart.");
573 die "There was no final ar_entry object";
578 my ($self, %params) = @_;
580 # save all the Invoice objects
582 foreach my $entry (@{ $self->controller->data }) {
583 # only push the invoice objects that don't have an error
584 next if $entry->{raw_data}->{datatype} ne $self->_ar_column;
585 next if @{ $entry->{errors} };
587 die unless $entry->{object}->validate_acc_trans;
589 push @{ $objects_to_save }, $entry;
592 $self->SUPER::save_objects(data => $objects_to_save);
596 $_[0]->settings->{'ar_column'}
599 sub _transaction_column {
600 $_[0]->settings->{'transaction_column'}