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_id', description => $::locale->text('Customer (database ID)') },
112 { name => 'language_id', description => $::locale->text('Language (database ID)') },
113 { name => 'language', description => $::locale->text('Language (name)') },
114 { name => 'payment_id', description => $::locale->text('Payment terms (database ID)') },
115 { name => 'payment', description => $::locale->text('Payment terms (name)') },
116 { name => 'taxzone_id', description => $::locale->text('Tax zone (database ID)') },
117 { name => 'taxzone', description => $::locale->text('Tax zone (description)') },
118 { name => 'department_id', description => $::locale->text('Department (database ID)') },
119 { name => 'department', description => $::locale->text('Department (description)') },
120 { name => 'globalproject_id', description => $::locale->text('Document Project (database ID)') },
121 { name => 'globalprojectnumber', description => $::locale->text('Document Project (number)') },
122 { name => 'globalproject', description => $::locale->text('Document Project (description)') },
123 { name => 'archart', description => $::locale->text('Receivables account (account number)') },
124 { name => 'orddate', description => $::locale->text('Order Date') },
125 { name => 'ordnumber', description => $::locale->text('Order Number') },
126 { name => 'quonumber', description => $::locale->text('Quotation Number') },
127 { name => 'quodate', description => $::locale->text('Quotation Date') },
130 $self->add_displayable_columns($self->_transaction_column,
131 { name => 'datatype', description => $self->_transaction_column . ' [1]' },
132 { name => 'projectnumber', description => $::locale->text('Project (number)') },
133 { name => 'project', description => $::locale->text('Project (description)') },
134 { name => 'amount', description => $::locale->text('Amount') },
135 { name => 'chart', description => $::locale->text('Account number') },
136 { name => 'taxkey', description => $::locale->text('Taxkey') },
140 sub init_taxkeys_by {
143 my $all_taxes = SL::DB::Manager::Tax->get_all;
144 return { map { $_->taxkey => $_->id } @{ $all_taxes } };
151 my $all_charts = SL::DB::Manager::Chart->get_all;
152 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_charts } } ) } qw(id accno) };
158 $self->controller->track_progress(phase => 'building data', progress => 0);
161 my $num_data = scalar @{ $self->controller->data };
163 foreach my $entry (@{ $self->controller->data }) {
164 $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0;
166 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
167 $self->handle_invoice($entry);
168 } elsif ($entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
169 $self->handle_transaction($entry);
171 die "unknown datatype";
176 } # finished data parsing
178 $self->add_transactions_to_ar(); # go through all data entries again, adding receivable entry to ar lines while calculating amount and netamount
180 foreach my $entry (@{ $self->controller->data }) {
181 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
182 $self->check_verify_amounts($entry->{object});
185 foreach my $entry (@{ $self->controller->data }) {
186 next unless ($entry->{raw_data}->{datatype} eq $self->_ar_column);
187 unless ( $entry->{object}->validate_acc_trans ) {
188 push @{ $entry->{errors} }, $::locale->text('Error: ar transaction doesn\'t validate');
192 # add info columns that aren't directly part of the object to be imported
193 # but are always determined or should always be shown because they are mandatory
194 $self->add_info_columns($self->_ar_column,
195 { header => $::locale->text('Customer/Vendor'), method => 'vc_name' },
196 { header => $::locale->text('Receivables account'), method => 'archart' },
197 { header => $::locale->text('Amount'), method => 'amount' },
198 { header => $::locale->text('Net amount'), method => 'netamount' },
199 { header => $::locale->text('Tax zone'), method => 'taxzone' });
201 # Adding info_header this way only works, if the first invoice $self->controller->data->[0]
203 # Todo: access via ->[0] ok? Better: search first order column and use this
204 $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};
206 $self->add_info_columns($self->_ar_column, { header => $::locale->text('Project Number'), method => 'globalprojectnumber' }) if $self->controller->data->[0]->{info_data}->{globalprojectnumber};
208 $self->add_columns($self->_ar_column,
209 map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw(payment department globalproject taxzone cp currency));
210 $self->add_columns($self->_ar_column, 'globalproject_id') if exists $self->controller->data->[0]->{raw_data}->{globalprojectnumber};
211 $self->add_columns($self->_ar_column, 'notes') if exists $self->controller->data->[0]->{raw_data}->{notes};
213 # Todo: access via ->[1] ok? Better: search first item column and use this
214 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Chart'), method => 'accno' });
215 $self->add_columns($self->_transaction_column, 'amount');
217 $self->add_info_columns($self->_transaction_column, { header => $::locale->text('Project Number'), method => 'projectnumber' }) if $self->controller->data->[1]->{info_data}->{projectnumber};
219 # $self->add_columns($self->_transaction_column,
220 # map { "${_}_id" } grep { exists $self->controller->data->[1]->{raw_data}->{$_} } qw(project price_factor pricegroup));
221 # $self->add_columns($self->_transaction_column,
222 # map { "${_}_id" } grep { exists $self->controller->data->[2]->{raw_data}->{$_} } qw(project price_factor pricegroup));
223 # $self->add_columns($self->_transaction_column, 'project_id') if exists $self->controller->data->[1]->{raw_data}->{projectnumber};
224 # $self->add_columns($self->_transaction_column, 'taxkey') if exists $self->controller->data->[1]->{raw_data}->{taxkey};
226 # If invoice has errors, add error for acc_trans items
227 # If acc_trans item has an error, add an error to the invoice item
229 foreach my $entry (@{ $self->controller->data }) {
231 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
233 } elsif ( defined $ar_entry
234 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
235 && scalar @{ $ar_entry->{errors} } > 0 ) {
236 push @{ $entry->{errors} }, $::locale->text('Error: invalid ar row for this transaction');
237 } elsif ( defined $ar_entry
238 && $entry->{raw_data}->{datatype} eq $self->_transaction_column
239 && scalar @{ $entry->{errors} } > 0 ) {
240 push @{ $ar_entry->{errors} }, $::locale->text('Error: invalid acc transactions for this ar row');
247 my ($self, $entry) = @_;
249 my $object = $entry->{object};
251 $object->transactions( [] ); # initialise transactions for ar object so methods work on unsaved transactions
254 if (any { $entry->{raw_data}->{$_} } qw(customer customernumber customer_id)) {
255 $self->check_vc($entry, 'customer_id');
256 # check_vc only sets customer_id, but we need vc_obj later for customer defaults
257 $vc_obj = SL::DB::Customer->new(id => $object->customer_id)->load if $object->customer_id;
258 } elsif (any { $entry->{raw_data}->{$_} } qw(vendor vendornumber vendor_id)) {
259 $self->check_vc($entry, 'vendor_id');
260 $vc_obj = SL::DB::Vendor->new(id => $object->vendor_id)->load if $object->vendor_id;
262 push @{ $entry->{errors} }, $::locale->text('Error: Customer/vendor missing');
265 # check for duplicate invnumbers already in database
266 if ( SL::DB::Manager::Invoice->get_all_count( where => [ invnumber => $object->invnumber ] ) ) {
267 push @{ $entry->{errors} }, $::locale->text('Error: invnumber already exists');
270 $self->check_archart($entry); # checks for receivable account
271 # $self->check_amounts($entry); # checks and sets amount and netamount, use verify_amount and verify_netamount instead
272 $self->check_payment($entry); # currency default from customer used below
273 $self->check_department($entry);
274 $self->check_taxincluded($entry);
275 $self->check_project($entry, global => 1);
276 $self->check_taxzone($entry); # taxzone default from customer used below
277 $self->check_currency($entry); # currency default from customer used below
278 $self->handle_salesman($entry);
279 $self->handle_employee($entry);
282 # copy defaults from customer if not specified in import file
283 foreach (qw(payment_id language_id taxzone_id currency_id)) {
284 $object->$_($vc_obj->$_) unless $object->$_;
290 my ($self, $entry, $chart) = @_;
292 die "check_taxkey needs chart object as an argument" unless ref($chart) eq 'SL::DB::Chart';
293 # problem: taxkey is not unique in table tax, normally one of those entries is chosen directly from a dropdown
294 # so we check if the chart has an active taxkey, and if it matches the taxkey from the import, use the active taxkey
295 # if the chart doesn't have an active taxkey, use the first entry from Tax that matches the taxkey
297 my $object = $entry->{object};
298 unless ( defined $entry->{raw_data}->{taxkey} ) {
299 push @{ $entry->{errors} }, $::locale->text('Error: taxkey missing'); # don't just assume 0, force taxkey in import
305 if ( $entry->{raw_data}->{taxkey} == $chart->get_active_taxkey->tax->taxkey ) {
306 $tax = $chart->get_active_taxkey->tax;
308 # assume there is only one tax entry with that taxkey, can't guess
309 $tax = SL::DB::Manager::Tax->get_first( where => [ taxkey => $entry->{raw_data}->{taxkey} ]);
313 push @{ $entry->{errors} }, $::locale->text('Error: invalid taxkey');
317 $object->taxkey($tax->taxkey);
318 $object->tax_id($tax->id);
323 my ($self, $entry) = @_;
324 # currently not used in favour of verify_amount and verify_netamount
326 my $object = $entry->{object};
328 unless ($entry->{raw_data}->{amount} && $entry->{raw_data}->{netamount}) {
329 push @{ $entry->{errors} }, $::locale->text('Error: need amount and netamount');
332 unless ($entry->{raw_data}->{amount} * 1 && $entry->{raw_data}->{netamount} * 1) {
333 push @{ $entry->{errors} }, $::locale->text('Error: amount and netamount need to be numeric');
337 $object->amount( $entry->{raw_data}->{amount} );
338 $object->netamount( $entry->{raw_data}->{netamount} );
341 sub handle_transaction {
342 my ($self, $entry) = @_;
344 # Prepare acc_trans data. amount is dealt with in add_transactions_to_ar
346 my $object = $entry->{object};
348 $self->check_project($entry, global => 0);
349 if ( $self->check_chart($entry) ) {
350 my $chart_obj = SL::DB::Manager::Chart->find_by(id => $object->chart_id);
352 unless ( $chart_obj->link =~ /AR_amount/ ) {
353 push @{ $entry->{errors} }, $::locale->text('Error: chart isn\'t an ar_amount chart');
357 if ( $self->check_taxkey($entry, $chart_obj) ) {
358 # do nothing, taxkey was assigned, just continue
360 # missing taxkey, don't do anything
367 # check whether taxkey and automatic taxkey match
368 # 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;
370 die "no taxkey for transaction object" unless $object->taxkey or $object->taxkey == 0;
375 my ($self, $entry) = @_;
377 my $object = $entry->{object};
379 if (any { $entry->{raw_data}->{$_} } qw(accno chart_id)) {
381 # Check whether or not chart ID is valid.
382 if ($object->chart_id && !$self->charts_by->{id}->{ $object->chart_id }) {
383 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart_id');
387 # Map number to ID if given.
388 if (!$object->chart_id && $entry->{raw_data}->{accno}) {
389 my $chart = $self->charts_by->{accno}->{ $entry->{raw_data}->{accno} };
391 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart (accno)');
395 $object->chart_id($chart->id);
398 # Map description to ID if given.
399 if (!$object->chart_id && $entry->{raw_data}->{description}) {
400 my $chart = $self->charts_by->{description}->{ $entry->{raw_data}->{description} };
402 push @{ $entry->{errors} }, $::locale->text('Error: invalid chart');
406 $object->chart_id($chart->id);
409 if ($object->chart_id) {
410 # add account number to preview
411 $entry->{info_data}->{accno} = $self->charts_by->{id}->{ $object->chart_id }->accno;
413 push @{ $entry->{errors} }, $::locale->text('Error: chart not found');
417 push @{ $entry->{errors} }, $::locale->text('Error: chart missing');
425 my ($self, $entry) = @_;
429 if ( $entry->{raw_data}->{archart} ) {
430 my $archart = $entry->{raw_data}->{archart};
431 $chart = SL::DB::Manager::Chart->find_by(accno => $archart);
433 push @{ $entry->{errors} }, $::locale->text("Error: can't find ar chart with accno #1", $archart);
436 } elsif ( $::instance_conf->get_ar_chart_id ) {
437 $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
439 push @{ $entry->{errors} }, $::locale->text("Error: neither archart passed, no default receivables chart configured");
443 unless ($chart->link eq 'AR') {
444 push @{ $entry->{errors} }, $::locale->text('Error: archart isn\'t an AR chart');
448 $entry->{info_data}->{archart} = $chart->accno;
449 $entry->{object}->{archart} = $chart;
453 sub check_taxincluded {
454 my ($self, $entry) = @_;
456 my $object = $entry->{object};
458 if ( $entry->{raw_data}->{taxincluded} ) {
459 if ( $entry->{raw_data}->{taxincluded} eq 'f' or $entry->{raw_data}->{taxincluded} eq '0' ) {
460 $object->taxincluded('0');
461 } elsif ( $entry->{raw_data}->{taxincluded} eq 't' or $entry->{raw_data}->{taxincluded} eq '1' ) {
462 $object->taxincluded('1');
464 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded has to be t or f');
468 push @{ $entry->{errors} }, $::locale->text('Error: taxincluded wasn\'t set');
474 sub check_verify_amounts {
477 # If amounts are given, show calculated amounts as info and given amounts (verify_xxx).
478 # And throw an error if the differences are too big.
479 my @to_verify = ( { column => 'amount',
480 raw_column => 'verify_amount',
481 info_header => 'Calc. Amount',
482 info_method => 'calc_amount',
483 err_msg => $::locale->text('Amounts differ too much'),
485 { column => 'netamount',
486 raw_column => 'verify_netamount',
487 info_header => 'Calc. Net amount',
488 info_method => 'calc_netamount',
489 err_msg => $::locale->text('Net amounts differ too much'),
492 foreach my $tv (@to_verify) {
493 if (exists $self->controller->data->[0]->{raw_data}->{ $tv->{raw_column} }) {
494 $self->add_raw_data_columns($self->_ar_column, $tv->{raw_column});
495 $self->add_info_columns($self->_ar_column,
496 { header => $::locale->text($tv->{info_header}), method => $tv->{info_method} });
500 foreach my $entry (@{ $self->controller->data }) {
501 if ( @{ $entry->{errors} } ) {
502 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
506 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
507 next if !$entry->{raw_data}->{ $tv->{raw_column} };
508 my $parsed_value = $::form->parse_amount(\%::myconfig, $entry->{raw_data}->{ $tv->{raw_column} });
509 # round $abs_diff, otherwise it might trigger for 0.020000000000021
510 my $abs_diff = $::form->round_amount(abs($entry->{object}->${ \$tv->{column} } - $parsed_value),2);
511 if ( $abs_diff > $self->settings->{'max_amount_diff'}) {
512 push @{ $entry->{errors} }, $::locale->text($tv->{err_msg});
519 sub add_transactions_to_ar {
522 # go through all verified ar and acc_trans rows in import, adding acc_trans objects to ar objects
524 my $ar_entry; # the current ar row
526 foreach my $entry (@{ $self->controller->data }) {
527 # when we reach an ar_column for the first time, don't do anything, just store in $ar_entry
528 # when we reach an ar_column for the second time, save it
529 if ($entry->{raw_data}->{datatype} eq $self->_ar_column) {
530 if ( $ar_entry && $ar_entry->{object} ) { # won't trigger the first time, finishes the last object
531 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
532 $ar_entry->{object}->recalculate_amounts; # determine and set amount and netamount for ar
533 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
534 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
535 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
537 push @{ $entry->{errors} }, $::locale->text("ar_chart isn't a valid chart");
540 $ar_entry = $entry; # remember as last ar_entry
542 } elsif ( defined $ar_entry && $entry->{raw_data}->{datatype} eq $self->_transaction_column ) {
543 push @{ $entry->{errors} }, $::locale->text('no tax_id in acc_trans') if !defined $entry->{object}->tax_id;
544 next if @{ $entry->{errors} };
546 my $acc_trans_objects = $ar_entry->{object}->add_ar_amount_row(
547 amount => $entry->{object}->amount,
548 chart => SL::DB::Manager::Chart->find_by(id => $entry->{object}->chart_id), # add_ar_amount takes chart obj. as argument
549 tax_id => $entry->{object}->tax_id,
550 project_id => $entry->{object}->project_id,
555 die "This should never happen\n";
559 # finish the final object
560 if ( $ar_entry->{object} ) {
561 if ( $ar_entry->{object}->{archart} && $ar_entry->{object}->{archart}->isa('SL::DB::Chart') ) {
562 $ar_entry->{object}->recalculate_amounts;
563 $ar_entry->{info_data}->{amount} = $ar_entry->{object}->amount;
564 $ar_entry->{info_data}->{netamount} = $ar_entry->{object}->netamount;
566 $ar_entry->{object}->create_ar_row(chart => $ar_entry->{object}->{archart});
568 push @{ $ar_entry->{errors} }, $::locale->text("The receivables chart isn't a valid chart.");
572 die "There was no final ar_entry object";
577 my ($self, %params) = @_;
579 # save all the Invoice objects
581 foreach my $entry (@{ $self->controller->data }) {
582 # only push the invoice objects that don't have an error
583 next if $entry->{raw_data}->{datatype} ne $self->_ar_column;
584 next if @{ $entry->{errors} };
586 die unless $entry->{object}->validate_acc_trans;
588 push @{ $objects_to_save }, $entry;
591 $self->SUPER::save_objects(data => $objects_to_save);
595 $_[0]->settings->{'ar_column'}
598 sub _transaction_column {
599 $_[0]->settings->{'transaction_column'}