1 use Test::More tests => 70;
9 use Support::TestSetup;
12 use List::MoreUtils qw(pairwise);
13 use SL::Controller::CsvImport;
17 use_ok 'SL::Controller::CsvImport::ARTransaction';
19 use SL::DB::Buchungsgruppe;
26 use SL::DB::AccTransaction;
28 my ($customer, $currency_id, $employee, $taxzone, $project, $department);
29 my ($transdate, $transdate_string);
35 $transdate = DateTime->today_local;
36 $transdate->set_year(2019) if $transdate->year == 2020; # hardcode for 2019 in 2020, because of tax rate change in Germany
37 $transdate_string = $transdate->to_kivitendo;
39 $params{$_} ||= {} for qw(buchungsgruppe customer tax);
42 $employee = SL::DB::Manager::Employee->current || croak "No employee";
43 $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || croak "No taxzone";
44 $currency_id = $::instance_conf->get_currency_id;
46 $customer = SL::DB::Customer->new(
47 name => 'Test Customer',
48 currency_id => $currency_id,
49 taxzone_id => $taxzone->id,
50 %{ $params{customer} }
53 $project = SL::DB::Project->new(
54 projectnumber => 'P1',
55 description => 'Project X',
56 project_type => SL::DB::Manager::ProjectType->find_by(description => 'Standard'),
57 project_status => SL::DB::Manager::ProjectStatus->find_by(name => 'running'),
60 $department = SL::DB::Department->new(
61 description => 'Department 1',
65 Support::TestSetup::login();
67 reset_state(customer => {id => 960, customernumber => 2});
73 my $controller = SL::Controller::CsvImport->new(
74 type => 'ar_transactions'
76 $controller->load_default_profile;
77 $controller->profile->set(
81 numberformat => $::myconfig{numberformat},
84 my $csv_artransactions_import = SL::Controller::CsvImport::ARTransaction->new(
85 settings => {'ar_column' => 'Rechnung',
86 'transaction_column' => 'AccTransaction',
87 'max_amount_diff' => 0.02
89 controller => $controller,
93 # $csv_artransactions_import->init_vc_by;
94 $csv_artransactions_import->run(test => 0);
96 # don't try and save objects that have errors
97 $csv_artransactions_import->save_objects unless scalar @{$csv_artransactions_import->controller->data->[0]->{errors}};
99 return $csv_artransactions_import->controller->data;
102 ##### manually create an ar transaction from scratch, testing the methods
103 $::myconfig{numberformat} = '1000.00';
104 $::myconfig{dateformat} = 'dd.mm.yyyy';
105 my $old_locale = $::locale;
106 # set locale to en so we can match errors
107 $::locale = Locale->new('en');
111 my $ar = SL::DB::Invoice->new(
113 invnumber => 'manual invoice',
114 taxzone_id => $taxzone->id,
115 currency_id => $currency_id,
117 customer_id => $customer->id,
118 transdate => $transdate,
119 employee_id => SL::DB::Manager::Employee->current->id,
123 my $tax3 = SL::DB::Manager::Tax->find_by(rate => 0.19, taxkey => 3) || die "can't find tax with taxkey 3";
124 my $income_chart = SL::DB::Manager::Chart->find_by(accno => '8400') || die "can't find income chart";
126 $ar->add_ar_amount_row(
128 chart => $income_chart,
132 $ar->recalculate_amounts; # set amount and netamount from transactions
133 is $ar->amount, '10', 'amount of manual invoice is 10';
134 is $ar->netamount, '8.4', 'netamount of manual invoice is 10';
136 $ar->create_ar_row( chart => SL::DB::Manager::Chart->find_by(accno => '1400', link => 'AR') );
137 my $result = $ar->validate_acc_trans(debug => 0);
138 is $result, 1, 'manual $ar validates';
141 is ${ $ar->transactions }[0]->chart->accno, '8400', 'assigned income chart after save ok';
142 is ${ $ar->transactions }[2]->chart->accno, '1400', 'assigned receivable chart after save ok';
143 is scalar @{$ar->transactions}, 3, 'manual invoice has 3 acc_trans entries';
145 $ar->pay_invoice( chart_id => SL::DB::Manager::Chart->find_by(accno => '1200')->id, # bank
146 amount => $ar->open_amount,
147 transdate => $transdate,
148 payment_type => 'without_skonto', # default if not specified
150 $result = $ar->validate_acc_trans(debug => 0);
151 is $result, 1, 'manual invoice validates after payment';
153 reset_state(customer => {id => 960, customernumber => 2});
155 my ($entries, $entry, $file);
157 # starting test of csv imports
158 # to debug errors in certain tests, run after test_import:
159 # die Dumper($entry->{errors});
162 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
163 datatype,accno,amount,taxkey
164 "Rechnung",960,4,1,"invoice 1",f,1400,"$transdate_string"
165 "AccTransaction",8400,159.48,3
167 $entries = test_import($file);
168 $entry = $entries->[0];
169 $entry->{object}->validate_acc_trans;
171 is $entry->{object}->invnumber, 'invoice 1', 'simple invnumber ok (customer)';
172 is $entry->{object}->customer_id, '960', 'simple customer_id ok (customer)';
173 is scalar @{$entry->{object}->transactions}, 3, 'invoice 1 has 3 acc_trans entries';
174 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), 159.48, 'invoice 1 ar amount is 159.48';
175 is $entry->{object}->direct_debit, '0', 'no direct debit';
176 is $entry->{object}->taxincluded, '0', 'taxincluded is false';
177 is $entry->{object}->amount, '189.78', 'ar amount tax not included is 189.78';
178 is $entry->{object}->netamount, '159.48', 'ar netamount tax not included is 159.48';
180 ##### test for duplicate invnumber
182 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
183 datatype,accno,amount,taxkey
184 "Rechnung",960,4,1,"invoice 1",f,1400,"$transdate_string"
185 "AccTransaction",8400,159.48,3
187 $entries = test_import($file);
188 $entry = $entries->[0];
189 $entry->{object}->validate_acc_trans;
190 is $entry->{errors}->[0], 'Error: invnumber already exists', 'detects verify_amount differences';
192 ##### test for no invnumber given
194 datatype,customer_id,taxzone_id,currency_id,taxincluded,archart,transdate
195 datatype,accno,amount,taxkey
196 "Rechnung",960,4,1,f,1400,"$transdate_string"
197 "AccTransaction",8400,159.48,3
199 $entries = test_import($file);
200 $entry = $entries->[0];
201 $entry->{object}->validate_acc_trans;
202 is $entry->{object}->invnumber =~ /^\d+$/, 1, 'invnumber assigned automatically';
204 ##### basic test without amounts in Rechnung, only specified in AccTransaction
206 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
207 datatype,accno,amount,taxkey
208 "Rechnung",960,4,1,"invoice 1 no amounts",f,1400,"$transdate_string"
209 "AccTransaction",8400,159.48,3
211 $entries = test_import($file);
212 $entry = $entries->[0];
213 $entry->{object}->validate_acc_trans;
215 is $entry->{object}->invnumber, 'invoice 1 no amounts', 'simple invnumber ok (customer)';
216 is $entry->{object}->customer_id, '960', 'simple customer_id ok (customer)';
217 is scalar @{$entry->{object}->transactions}, 3, 'invoice 1 has 3 acc_trans entries';
218 is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'not taxincluded ar amount';
219 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '159.48', 'not taxincluded acc_trans netamount';
220 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), 159.48, 'invoice 1 ar amount is 159.48';
222 ##### basic test: credit_note
224 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
225 datatype,accno,amount,taxkey
226 "Rechnung",960,4,1,"credit note",f,1400,"$transdate_string"
227 "AccTransaction",8400,-159.48,3
229 $entries = test_import($file);
230 $entry = $entries->[0];
231 $entry->{object}->validate_acc_trans;
233 is $entry->{object}->invnumber, 'credit note', 'simple credit note ok';
234 is scalar @{$entry->{object}->transactions}, 3, 'credit note has 3 acc_trans entries';
235 is $::form->round_amount($entry->{object}->amount, 2), '-189.78', 'taxincluded ar amount';
236 is $::form->round_amount($entry->{object}->netamount, 2), '-159.48', 'taxincluded ar net amount';
237 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), -159.48, 'credit note ar amount is -159.48';
238 is $entry->{object}->amount, '-189.78', 'credit note amount tax not included is 189.78';
239 is $entry->{object}->netamount, '-159.48', 'credit note netamount tax not included is 159.48';
241 #### verify_amount differs: max_amount_diff = 0.02, 189.80 is ok, 189.81 is not
243 datatype,customer_id,verify_amount,verify_netamount,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
244 datatype,accno,amount,taxkey
245 "Rechnung",960,189.81,159.48,4,1,"invoice amounts differing",f,1400,"$transdate_string"
246 "AccTransaction",8400,159.48,3
248 $entries = test_import($file);
249 $entry = $entries->[0];
250 is $entry->{errors}->[0], 'Amounts differ too much', 'detects verify_amount differences';
254 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,direct_debit,archart,transdate
255 datatype,accno,amount,taxkey
256 "Rechnung",960,4,1,"invoice with direct debit",f,t,1400,"$transdate_string"
257 "AccTransaction",8400,159.48,3
260 $entries = test_import($file);
261 $entry = $entries->[0];
262 $entry->{object}->validate_acc_trans;
263 is $entry->{object}->direct_debit, '1', 'direct debit';
267 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
268 datatype,accno,amount,taxkey
269 "Rechnung",960,4,1,"invoice 1 tax included no amounts",t,1400,"$transdate_string"
270 "AccTransaction",8400,189.78,3
273 $entries = test_import($file);
274 $entry = $entries->[0];
275 $entry->{object}->validate_acc_trans(debug => 0);
276 is $entry->{object}->taxincluded, '1', 'taxincluded is true';
277 is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'taxincluded ar amount';
278 is $::form->round_amount($entry->{object}->netamount, 2), '159.48', 'taxincluded ar net amount';
279 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '159.48', 'taxincluded acc_trans netamount';
281 #### multiple tax included
283 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate
284 datatype,accno,amount,taxkey
285 "Rechnung",960,4,1,"invoice multiple tax included",t,1400,"$transdate_string"
286 "AccTransaction",8400,94.89,3
287 "AccTransaction",8400,94.89,3
290 $entries = test_import($file);
291 $entry = $entries->[0];
292 $entry->{object}->validate_acc_trans;
293 is $::form->round_amount($entry->{object}->amount, 2), '189.78', 'taxincluded ar amount';
294 is $::form->round_amount($entry->{object}->netamount, 2), '159.48', 'taxincluded ar netamount';
295 is $::form->round_amount($entry->{object}->transactions->[0]->amount, 2), '79.74', 'taxincluded amount';
296 is $::form->round_amount($entry->{object}->transactions->[1]->amount, 2), '15.15', 'taxincluded tax';
298 # different receivables chart
300 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart
301 datatype,accno,amount,taxkey
302 "Rechnung",960,4,1,"invoice mit archart 1448",f,1448
303 "AccTransaction",8400,159.48,3
305 $entries = test_import($file);
306 $entry = $entries->[0];
307 $entry->{object}->validate_acc_trans;
308 is $entry->{object}->transactions->[2]->chart->accno, '1448', 'archart set to 1448';
312 datatype,taxzone_id,currency_id,invnumber,taxincluded,archart
313 datatype,accno,amount,taxkey
314 "Rechnung",4,1,"invoice missing customer",f,1400
315 "AccTransaction",8400,159.48,3
317 $entries = test_import($file);
318 $entry = $entries->[0];
319 is $entry->{errors}->[0], 'Error: Customer/vendor missing', 'detects missing customer or vendor';
322 ##### customer by name
324 datatype,customer,taxzone_id,currency_id,invnumber,taxincluded,archart
325 datatype,accno,amount,taxkey
326 "Rechnung","Test Customer",4,1,"invoice customer name",f,1400
327 "AccTransaction",8400,159.48,3
329 $entries = test_import($file);
330 $entry = $entries->[0];
331 $entry->{object}->validate_acc_trans;
332 is $entry->{object}->customer->name, "Test Customer", 'detects customer by name';
334 ##### detect missing chart
336 datatype,taxzone_id,currency_id,invnumber,customer,archart
337 datatype,amount,taxkey
338 "Rechnung",4,1,"invoice missing chart","Test Customer",1400
341 $entries = test_import($file);
342 $entry = $entries->[1];
343 is $entry->{errors}->[0], 'Error: chart missing', 'detects missing chart (chart_id or accno)';
345 ##### detect illegal chart by accno
347 datatype,taxzone_id,currency_id,invnumber,customer,archart
348 datatype,accno,amount,taxkey
349 "Rechnung",4,1,"invoice illegal chart accno","Test Customer",1400
350 "AccTransaction",9999,4,3
352 $entries = test_import($file);
353 $entry = $entries->[1];
354 is $entry->{errors}->[0], 'Error: invalid chart (accno)', 'detects invalid chart (chart_id or accno)';
356 # ##### detect illegal archart
358 datatype,taxzone_id,currency_id,invnumber,customer,taxincluded,archart
359 datatype,accno,amount,taxkey
360 "Rechnung",4,1,"invoice illegal archart","Test Customer",f,11400
361 "AccTransaction",8400,159.48,3
363 $entries = test_import($file);
364 $entry = $entries->[0];
365 is $entry->{errors}->[0], "Error: can't find ar chart with accno 11400", 'detects illegal receivables chart (archart)';
367 ##### detect chart by id
369 datatype,taxzone_id,currency_id,invnumber,customer,taxincluded,archart
370 datatype,amount,chart_id,taxkey
371 "Rechnung",4,1,"invoice chart_id","Test Customer",f,1400
372 "AccTransaction",159.48,184,3
374 $entries = test_import($file);
375 $entry = $entries->[1]; # acc_trans entry is at entry array pos 1
376 $entries->[0]->{object}->validate_acc_trans;
377 is $entry->{object}->chart->id, "184", 'detects chart by id';
379 ##### detect chart by accno
381 datatype,taxzone_id,currency_id,invnumber,customer,taxincluded,archart
382 datatype,amount,accno,taxkey
383 "Rechnung",4,1,"invoice by chart accno","Test Customer",f,1400
384 "AccTransaction",159.48,8400,3
386 $entries = test_import($file);
387 $entry = $entries->[1];
388 $entries->[0]->{object}->validate_acc_trans;
389 is $entry->{object}->chart->accno, "8400", 'detects chart by accno';
391 ##### detect chart isn't an ar_chart
393 datatype,taxzone_id,currency_id,invnumber,customer,taxincluded,archart
394 datatype,amount,accno,taxkey
395 "Rechnung",4,1,"invoice by chart accno","Test Customer",f,1400
396 "AccTransaction",159.48,1400,3
398 $entries = test_import($file);
399 $entry = $entries->[1];
400 $entries->[0]->{object}->validate_acc_trans;
401 is $entry->{errors}->[0], 'Error: chart isn\'t an ar_amount chart', 'detects valid chart that is not an ar_amount chart';
405 datatype,taxzone_id,currency_id,invnumber,customer,archart
406 datatype,amount,accno
407 "Rechnung",4,1,"invoice missing taxkey chart accno","Test Customer",1400
408 "AccTransaction",159.48,8400
410 $entries = test_import($file);
411 $entry = $entries->[1];
412 is $entry->{errors}->[0], 'Error: taxkey missing', 'detects missing taxkey (DATEV Steuerschlüssel)';
416 datatype,taxzone_id,currency_id,invnumber,customer,archart
417 datatype,amount,accno,taxkey
418 "Rechnung",4,1,"invoice illegal taxkey","Test Customer",1400
419 "AccTransaction",4,8400,123
421 $entries = test_import($file);
422 $entry = $entries->[1];
423 is $entry->{errors}->[0], 'Error: invalid taxkey', 'detects invalid taxkey (DATEV Steuerschlüssel)';
427 datatype,customer_id,taxzone_id,currency_id,invnumber,archart
428 datatype,accno,amount,taxkey
429 "Rechnung",960,4,1,"invoice by taxkey",1400
430 "AccTransaction",8400,4,3
433 $entries = test_import($file);
434 $entry = $entries->[1];
435 is $entry->{object}->taxkey, 3, 'detects taxkey';
439 datatype,customer_id,taxzone_id,currency_id,invnumber,archart,taxincluded
440 datatype,accno,amount,taxkey,projectnumber
441 "Rechnung",960,4,1,"invoice with acc_trans project",1400,f
442 "AccTransaction",8400,159.48,3,P1
445 $entries = test_import($file);
446 $entry = $entries->[1];
447 # die Dumper($entries->[0]->{errors}) if scalar @{$entries->[0]->{errors}};
448 is $entry->{object}->project->projectnumber, 'P1', 'detects acc_trans project';
452 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate,duedate,globalprojectnumber,department
453 datatype,accno,amount,taxkey,projectnumber
454 "Rechnung",960,4,1,"invoice various",t,1400,21.04.2016,30.04.2016,P1,Department 1
455 "AccTransaction",8400,119,3,P1
456 "AccTransaction",8300,107,2,P1
457 "AccTransaction",8200,100,0,P1
460 $entries = test_import($file);
461 $entry = $entries->[0];
462 $entry->{object}->validate_acc_trans;
463 is $entry->{object}->duedate->to_kivitendo, '30.04.2016', 'duedate';
464 is $entry->{object}->transdate->to_kivitendo, '21.04.2016', 'transdate';
465 is $entry->{object}->globalproject->description, 'Project X', 'project';
466 is $entry->{object}->department->description, 'Department 1', 'department';
467 # 8300 is third entry after 8400 and tax for 8400
468 is $::form->round_amount($entry->{object}->transactions->[2]->amount), '100', '8300 net amount: 100';
469 is $::form->round_amount($entry->{object}->transactions->[2]->taxkey), '2', '8300 has taxkey 2';
470 is $::form->round_amount($entry->{object}->transactions->[2]->project_id), $project->id, 'AccTrans project';
474 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate,duedate,globalprojectnumber,department
475 datatype,accno,amount,taxkey,projectnumber
476 "Rechnung",960,4,1,"invoice various 1",t,1400,21.04.2016,30.04.2016,P1,Department 1
477 "AccTransaction",8400,119,3,P1
478 "AccTransaction",8300,107,2,P1
479 "AccTransaction",8200,100,0,P1
480 "Rechnung",960,4,1,"invoice various 2",t,1400,21.04.2016,30.04.2016,P1,Department 1
481 "AccTransaction",8400,119,3,P1
482 "AccTransaction",8300,107,2,P1
483 "AccTransaction",8200,100,0,P1
486 $entries = test_import($file);
487 $entry = $entries->[0];
488 $entry->{object}->validate_acc_trans;
489 is $entry->{object}->duedate->to_kivitendo, '30.04.2016', 'duedate';
490 is $entry->{info_data}->{amount}, '326', "First invoice amount displayed in info data";
491 is $entries->[4]->{info_data}->{amount}, '326', "Second invoice amount displayed in info data";
493 # multiple entries, taxincluded = f
495 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart
496 datatype,accno,amount,taxkey
497 "Rechnung",960,4,1,"invoice 4 acc_trans",f,1400
498 "AccTransaction",8400,39.87,3
499 "AccTransaction",8400,39.87,3
500 "AccTransaction",8400,39.87,3
501 "AccTransaction",8400,39.87,3
502 "Rechnung",960,4,1,"invoice 4 acc_trans 2",f,1400
503 "AccTransaction",8400,39.87,3
504 "AccTransaction",8400,39.87,3
505 "AccTransaction",8400,39.87,3
506 "AccTransaction",8400,39.87,3
507 "Rechnung",960,4,1,"invoice 4 acc_trans 3",f,1400
508 "AccTransaction",8400,39.87,3
509 "AccTransaction",8400,39.87,3
510 "AccTransaction",8400,39.87,3
511 "AccTransaction",8400,39.87,3
512 "Rechnung",960,4,1,"invoice 4 acc_trans 4",f,1448
513 "AccTransaction",8400,39.87,3
514 "AccTransaction",8400,39.87,3
515 "AccTransaction",8400,39.87,3
516 "AccTransaction",8400,39.87,3
518 $entries = test_import($file);
521 foreach my $entry ( @$entries ) {
522 next unless $entry->{object}->isa('SL::DB::Invoice');
524 is scalar @{$entry->{object}->transactions}, 9, "invoice $i: 'invoice 4 acc_trans' has 9 acc_trans entries";
525 $entry->{object}->validate_acc_trans;
528 ##### missing acc_trans
530 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart,transdate,duedate,globalprojectnumber,department
531 datatype,accno,amount,taxkey,projectnumber
532 "Rechnung",960,4,1,"invoice acc_trans missing",t,1400,21.04.2016,30.04.2016,P1,Department 1
533 "Rechnung",960,4,1,"invoice various a",t,1400,21.04.2016,30.04.2016,P1,Department 1
534 "AccTransaction",8400,119,3,P1
535 "AccTransaction",8300,107,2,P1
538 $entries = test_import($file);
539 $entry = $entries->[0];
540 is $entry->{errors}->[0], "Error: ar transaction doesn't validate", 'detects invalid ar, maybe acc_trans entry missing';
542 my $number_of_imported_invoices = SL::DB::Manager::Invoice->get_all_count;
543 is $number_of_imported_invoices, 19, 'All invoices saved';
545 #### taxkey differs from active_taxkey
547 datatype,customer_id,taxzone_id,currency_id,invnumber,taxincluded,archart
548 datatype,accno,amount,taxkey
549 "Rechnung",960,4,1,"invoice 1 tax included no amounts",t,1400
550 "AccTransaction",8400,189.78,2
553 $entries = test_import($file);
554 $entry = $entries->[0];
555 $entry->{object}->validate_acc_trans(debug => 0);
557 clear_up(); # remove all data at end of tests
562 SL::DB::Manager::AccTransaction->delete_all(all => 1);
563 SL::DB::Manager::Invoice->delete_all (all => 1);
564 SL::DB::Manager::Customer->delete_all (all => 1);
565 SL::DB::Manager::Project->delete_all (all => 1);
566 SL::DB::Manager::Department->delete_all (all => 1);
574 # set emacs to perl mode