1 use Test::More tests => 38;
2 use Test::Deep qw(cmp_deeply);
9 use Support::TestSetup;
15 use SL::DB::GLTransaction;
17 use SL::DB::PaymentTerm;
18 use SL::DBUtils qw(selectall_hashref_query);
19 use SL::Dev::Record qw(:ALL);
20 use SL::Dev::CustomerVendor qw(new_customer new_vendor);
21 use SL::Dev::Part qw(new_part);
22 use SL::Dev::Payment qw(create_payment_terms);
25 Support::TestSetup::login();
26 my $dbh = SL::DB->client->dbh;
28 my $test_kontenrahmen = 'skr03';
32 # TODOs: Storno muß noch korrekt funktionieren
33 # neue Konten für 5% anlegen
34 # Leistungszeitraum vs. Datum Zuord. Steuerperiodest
36 note('checking if all tax entries exist for Konjunkturprogramm');
38 # create dates to test on
39 my $date_2006 = DateTime->new(year => 2006, month => 6, day => 15);
40 my $date_2020_1 = DateTime->new(year => 2020, month => 6, day => 15);
41 my $date_2020_2 = DateTime->new(year => 2020, month => 7, day => 15);
42 my $date_2021 = DateTime->new(year => 2021, month => 1, day => 15);
44 # The only way to discern the pre-2007 16% tax from the 2020 16% tax is by
45 # their configured automatic tax charts, so look them up here:
47 my ($chart_vst_19, $chart_vst_16, $chart_vst_5, $chart_vst_7);
48 my ($chart_ust_19, $chart_ust_16, $chart_ust_5, $chart_ust_7);
49 my ($income_19_accno, $income_7_accno);
50 my ($ar_accno, $ap_accno);
51 my ($chart_reisekosten_accno, $chart_cash_accno, $chart_bank_accno);
54 if ( $test_kontenrahmen eq 'skr03' ) {
56 is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR03EU', "coa SKR03 ok");
58 $chart_vst_19 = '1776';
59 $chart_vst_16 = '1775';
60 $chart_vst_5 = '1773';
61 $chart_vst_7 = '1771';
63 $chart_ust_19 = '1576';
64 $chart_ust_16 = '1575';
65 $chart_ust_5 = '1568';
66 $chart_ust_7 = '1571';
68 $income_19_accno = '8400';
69 $income_7_accno = '8300';
71 $chart_reisekosten_accno = 4660;
72 $chart_cash_accno = 1000;
77 } elsif ( $test_kontenrahmen eq 'skr04') { # skr04 - test can be ran manually by running t/000setup_database.t with coa for SKR04
78 is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR04EU', "coa SKR04 ok");
79 $chart_ust_19 = '1406';
80 $chart_ust_16 = '1405';
81 $chart_ust_5 = '1403';
82 $chart_ust_7 = '1401';
84 $chart_vst_19 = '3806';
85 $chart_vst_16 = '3805';
86 $chart_vst_5 = '3803';
87 $chart_vst_7 = '3801';
89 $income_19_accno = '4400';
90 $income_7_accno = '4300';
92 $chart_reisekosten_accno = 6650;
93 $chart_cash_accno = 1600;
99 my $tax_vst_19 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_19) or die; # 19%
100 my $tax_vst_16 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_16) or die; # 16%
101 my $tax_vst_5 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_5 ) or die; # 5%
102 my $tax_vst_7 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_7 ) or die; # 7%
104 my $tax_ust_19 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_19) or die; # 19%
105 my $tax_ust_16 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_16) or die; # 16%
106 my $tax_ust_5 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_5) or die; # 5%
107 my $tax_ust_7 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_7) or die; # 7%
109 my $chart_income_19 = SL::DB::Manager::Chart->find_by(accno => $income_19_accno) or die;
110 my $chart_income_7 = SL::DB::Manager::Chart->find_by(accno => $income_7_accno) or die;
112 my $chart_reisekosten = SL::DB::Manager::Chart->find_by(accno => $chart_reisekosten_accno) or die;
113 my $chart_cash = SL::DB::Manager::Chart->find_by(accno => $chart_cash_accno) or die;
115 is(defined SL::DB::Manager::Tax->find_by(taxkey => 2, rate => 0.05), 1, "tax for taxkey 2 with 5% was created ok");
116 is(defined SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.16, chart_id => $tax_vst_16->id), 1, "new sales tax for taxkey 3 with 16% exists ok");
117 is(defined SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.19, chart_id => $tax_vst_19->id), 1, "old sales tax for taxkey 3 with 19% exists ok");
118 is(defined SL::DB::Manager::Tax->find_by(taxkey => 5, rate => 0.16, chart_id => $tax_vst_16->id), 1, "new sales tax for taxkey 5 with 16% exists ok");
120 is(defined SL::DB::Manager::Tax->find_by(taxkey => 7, rate => 0.16, chart_id => $tax_ust_16->id), 1, "old purchase tax for taxkey 7 with 16% exists ok");
121 # is(defined SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07, chart_id => $tax_ust_16->id), 1, "old purchase tax for taxkey 7 with 16% exists ok");
122 is(defined SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, chart_id => $tax_ust_19->id), 1, "old purchase tax for taxkey 9 with 19% exists ok");
123 is(defined SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.16, chart_id => $tax_ust_16->id), 1, "new purchase tax for taxkey 9 with 16% exists ok");
125 my $vendor = new_vendor( name => 'Testvendor')->save;
126 my $customer = new_customer(name => 'Testcustomer')->save;
128 # cmp_ok($chart_income_7->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.07, "get_active_taxkey rate for 8300 in 2020_1 ok");
129 # cmp_ok($chart_income_7->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.05, "get_active_taxkey rate for 8300 in 2020_2 ok");
130 # cmp_ok($chart_income_7->get_active_taxkey($date_2021 )->tax->rate, '==', 0.07, "get_active_taxkey rate for 8300 in 2021 ok");
131 cmp_ok($chart_income_7->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2020_1 ok");
132 cmp_ok($chart_income_7->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.05, "get_active_taxkey rate for $income_7_accno in 2020_2 ok");
133 cmp_ok($chart_income_7->get_active_taxkey($date_2021 )->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2021 ok");
134 cmp_ok($chart_income_7->get_active_taxkey($date_2006 )->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2016 ok");
136 cmp_ok($chart_income_19->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.19, "get_active_taxkey rate for $income_19_accno in 2020_1 ok");
137 cmp_ok($chart_income_19->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.16, "get_active_taxkey rate for $income_19_accno in 2020_2 ok");
138 cmp_ok($chart_income_19->get_active_taxkey($date_2021 )->tax->rate, '==', 0.19, "get_active_taxkey rate for $income_19_accno in 2021 ok");
139 cmp_ok($chart_income_19->get_active_taxkey($date_2006 )->tax->rate, '==', 0.16, "get_active_taxkey rate for $income_19_accno in 2016 ok");
141 my $bugru19 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 19%') or die "Can't find bugru19";
142 my $bugru7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%' ) or die "Can't find bugru7";
144 my $part1 = new_part(partnumber => '1', description => 'part19', buchungsgruppen_id => $bugru19->id)->save;
145 my $part2 = new_part(partnumber => '2', description => 'part7', buchungsgruppen_id => $bugru7->id )->save;
147 note('sales invoices');
148 my $sales_invoice_2006 = create_invoice_for_date('2006', $date_2006);
149 my $sales_invoice_2020_1 = create_invoice_for_date('2020_1', $date_2020_1);
150 my $sales_invoice_2020_2 = create_invoice_for_date('2020_2', $date_2020_2);
151 my $sales_invoice_2021 = create_invoice_for_date('2021', $date_2021);
153 is($sales_invoice_2006->amount, 223, '2006 sales invoice has 16% and 7% tax ok' ); # 116 + 7
154 is($sales_invoice_2020_1->amount, 226, '2020_01 sales invoice has 19% and 7% tax ok'); # 119 + 7
155 is($sales_invoice_2020_2->amount, 221, '2020_02 sales invoice has 16% and 5% tax ok'); # 116 + 5
156 is($sales_invoice_2021->amount, 226, '2021 sales invoice has 19% and 7% tax ok' ); # 119 + 7
158 &datev_test($sales_invoice_2020_2,
161 'belegfeld1' => 'test is 2020_2',
162 'buchungstext' => 'Testcustomer',
163 'datum' => '15.07.2020',
164 'leistungsdatum' => '15.07.2020', # should leistungsdatum be empty if it doesn't exist?
165 'gegenkonto' => $income_7_accno,
166 'konto' => $ar_accno,
174 'belegfeld1' => 'test is 2020_2',
175 'buchungstext' => 'Testcustomer',
176 'datum' => '15.07.2020',
177 'leistungsdatum' => '15.07.2020',
178 'gegenkonto' => $income_19_accno,
179 'konto' => $ar_accno,
187 "datev check for 16/5 ok, no taxkey"
190 note('sales invoice with differing delivery dates');
191 my $sales_invoice_2020_1_with_delivery_date_2020_2 = create_invoice_for_date('deliverydate 2020_1', $date_2020_1, $date_2020_2);
192 is($sales_invoice_2020_1_with_delivery_date_2020_2->amount, 221, "sales_invoice from 2020_1 with future delivery_date 2020_2 tax ok");
194 my $sales_invoice_2020_2_with_delivery_date_2020_1 = create_invoice_for_date('deliverydate 2020_2', $date_2020_2, $date_2020_1);
195 is($sales_invoice_2020_2_with_delivery_date_2020_1->amount, 226, "sales_invoice from 2020_2 with past delivery_date 2020_1 tax ok");
197 &datev_test($sales_invoice_2020_2_with_delivery_date_2020_1,
200 'belegfeld1' => 'test is deliverydate 2020_2',
201 'buchungstext' => 'Testcustomer',
202 'datum' => '15.07.2020',
203 'gegenkonto' => $income_7_accno,
204 'konto' => $ar_accno,
207 'leistungsdatum' => '15.06.2020',
213 'belegfeld1' => 'test is deliverydate 2020_2',
214 'buchungstext' => 'Testcustomer',
215 'datum' => '15.07.2020',
216 'gegenkonto' => $income_19_accno,
217 'konto' => $ar_accno,
220 'leistungsdatum' => '15.06.2020',
226 "datev check for datev export with delivery_date 19/7 ok, no taxkey"
229 my $sales_invoice_2021_with_delivery_date_2020_2 = create_invoice_for_date('deliverydate 2020_2', $date_2021, $date_2020_2);
230 is($sales_invoice_2021_with_delivery_date_2020_2->amount, 221, "sales_invoice from 2021 with past delivery_date 2020_2 tax ok");
232 my $sales_invoice_2020_2_with_delivery_date_2021 = create_invoice_for_date('deliverydate 2021', $date_2020_2, $date_2021);
233 is($sales_invoice_2020_2_with_delivery_date_2021->amount, 226, "sales_invoice from 2020_2 with future delivery_date 2021 tax ok");
236 note('ap transactions');
237 # in the test we want to test for Reisekosten with 19% and 7%. Normally the user
238 # would select the entries from the dropdown, as they may differ from the
239 # default, so we have to pass the tax we want to create_ap_transaction
241 my $tax_9_16_old = SL::DB::Manager::Tax->find_by(taxkey => 7, rate => 0.16, chart_id => $tax_ust_16->id);
242 my $tax_9_19 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, chart_id => $tax_ust_19->id);
243 my $tax_9_16 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.16, chart_id => $tax_ust_16->id);
244 my $tax_8_7 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07, chart_id => $tax_ust_7->id);
245 my $tax_8_5 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.05, chart_id => $tax_ust_5->id);
247 # simulate user selecting the "correct" taxes in dropdown:
248 my $ap_transaction_2006 = create_ap_transaction_for_date('2006', $date_2006, undef, $tax_9_16_old, $tax_8_7);
249 my $ap_transaction_2020_1 = create_ap_transaction_for_date('2020_1', $date_2020_1, undef, $tax_9_19, $tax_8_7);
250 my $ap_transaction_2020_2 = create_ap_transaction_for_date('2020_2', $date_2020_2, undef, $tax_9_16, $tax_8_5);
251 my $ap_transaction_2021 = create_ap_transaction_for_date('2021', $date_2021, undef, $tax_9_19, $tax_8_7);
254 is($ap_transaction_2006->amount, 223, '2006 ap transaction has 16% and 7% tax ok'); # 116 + 7
255 is($ap_transaction_2020_1->amount, 226, '2020_01 ap transaction has 19% and 7% tax ok'); # 119 + 7
256 is($ap_transaction_2020_2->amount, 221, '2020_02 ap transaction has 16% and 5% tax ok'); # 116 + 5
257 is($ap_transaction_2021->amount, 226, '2021 ap transaction has 19% and 7% tax ok'); # 119 + 7
259 # ap transaction in july, but use old tax
260 my $ap_transaction_2020_2_with_delivery_date_2020_1 = create_ap_transaction_for_date('2020_2 with delivery date 2020_1', $date_2020_2, $date_2020_1, $tax_9_19, $tax_8_7);
261 is($ap_transaction_2020_2_with_delivery_date_2020_1->amount, 226, 'ap transaction 2020_2 with delivery date 2020_1, 19% and 7% tax ok'); # 119 + 7
262 &datev_test($ap_transaction_2020_2_with_delivery_date_2020_1,
265 'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1',
266 'buchungsschluessel' => 8,
267 'buchungstext' => 'Testvendor',
268 'datum' => '15.07.2020',
269 'gegenkonto' => $ap_accno,
270 'konto' => $chart_reisekosten_accno,
273 'leistungsdatum' => '15.06.2020',
279 'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1',
280 'buchungsschluessel' => 9,
281 'buchungstext' => 'Testvendor',
282 'datum' => '15.07.2020',
283 'gegenkonto' => $ap_accno,
284 'konto' => $chart_reisekosten_accno,
287 'leistungsdatum' => '15.06.2020',
293 "datev check for ap transaction 2020_2 with delivery date 2020_1, 19% and 7% tax ok"
296 note('ar transactions');
298 my $ar_transaction_2006 = create_ar_transaction_for_date('2006', $date_2006);
299 my $ar_transaction_2020_1 = create_ar_transaction_for_date('2020_1', $date_2020_1);
300 my $ar_transaction_2020_2 = create_ar_transaction_for_date('2020_2', $date_2020_2);
301 my $ar_transaction_2021 = create_ar_transaction_for_date('2021', $date_2021);
303 is($ar_transaction_2006->amount, 223, '2006 ar transaction has 16% and 7% tax ok'); # 116 + 7
304 is($ar_transaction_2020_1->amount, 226, '2020_01 ar transaction has 19% and 7% tax ok'); # 119 + 7
305 is($ar_transaction_2020_2->amount, 221, '2020_02 ar transaction has 16% and 5% tax ok'); # 116 + 5
306 is($ar_transaction_2021->amount, 226, '2021 ar transaction has 19% and 7% tax ok'); # 119 + 7
308 note('gl transactions');
310 my $gl_2006 = create_gl_transaction_for_date('glincome 2006', $date_2006, 223);
311 my $gl_2020_1 = create_gl_transaction_for_date('glincome 2020_1', $date_2020_1, 226);
312 my $gl_2020_2 = create_gl_transaction_for_date('glincome 2020_2', $date_2020_2, 221);
313 my $gl_2021 = create_gl_transaction_for_date('glincome 2021', $date_2021, 226);
315 is(SL::DB::Manager::GLTransaction->get_all_count(), 4, "4 gltransactions created correctly");
317 my $result = &get_account_balances;
318 # print Dumper($result);
319 is_deeply( &get_account_balances,
323 # # 'description' => 'Kasse',
324 # 'sum' => '-896.00000'
328 # # 'description' => 'Ford. a.Lieferungen und Leistungen',
329 # 'sum' => '-2686.00000'
333 # 'description' => 'Abziehbare Vorsteuer 7%',
338 # 'description' => 'Abziehbare Vorsteuer 7%',
343 # 'description' => 'Abziehbare Vorsteuer 16%',
348 # 'description' => 'Abziehbare Vorsteuer 19 %',
353 # # 'description' => 'Verbindlichkeiten aus Lief.u.Leist.',
354 # 'sum' => '896.00000'
358 # 'description' => 'Umsatzsteuer 7%',
363 # 'description' => 'Umsatzsteuer 5 %',
368 # 'description' => 'Umsatzsteuer 16%',
373 # 'description' => 'Umsatzsteuer 19 %',
378 # # 'description' => 'Reisekosten Arbeitnehmer',
379 # 'sum' => '-800.00000'
382 # 'accno' => $income_7_accno,
383 # # 'description' => "Erl\x{f6}se 7%USt",
384 # 'sum' => '1600.00000'
387 # 'accno' => $income_19_accno,
388 # # 'description' => "Erl\x{f6}se 16%/19% USt.",
389 # 'sum' => '1600.00000'
392 'account balances after invoices'
399 ###### functions for setting up data
401 sub create_invoice_for_date {
402 my ($invnumber, $transdate, $deliverydate) = @_;
404 $deliverydate = $transdate unless defined $deliverydate;
406 my $sales_invoice = create_sales_invoice(
407 invnumber => 'test is ' . $invnumber,
408 transdate => $transdate,
409 customer => $customer,
410 deliverydate => $deliverydate,
412 invoiceitems => [ create_invoice_item(part => $part1, qty => 10, sellprice => 10),
413 create_invoice_item(part => $part2, qty => 10, sellprice => 10),
416 return $sales_invoice;
419 sub create_ar_transaction_for_date {
420 my ($invnumber, $transdate) = @_;
422 my $ar_transaction = create_ar_transaction(
423 customer => $customer,
424 invnumber => 'test ar' . $invnumber,
426 transdate => $transdate,
427 ar_chart => SL::DB::Manager::Chart->find_by(accno => $ar_accno), # pass ar_chart, as it is hardcoded for SKR03 in SL::Dev::Record
430 chart => $chart_income_19,
434 chart => $chart_income_7,
439 return $ar_transaction;
442 sub create_ap_transaction_for_date {
443 my ($invnumber, $transdate, $deliverydate, $tax_high, $tax_low) = @_;
445 # printf("invnumber = %s tax_high = %s tax_low = %s\n", $invnumber, $tax_high->accno , $tax_low->accno);
446 my $taxkey_ = $chart_reisekosten->get_active_taxkey($transdate);
448 my $ap_transaction = create_ap_transaction(
450 invnumber => 'test ap_transaction ' . $invnumber,
452 transdate => $transdate,
453 deliverydate => $deliverydate,
454 ap_chart => SL::DB::Manager::Chart->find_by(accno => $ap_accno), # pass ap_chart, as it is hardcoded for SKR03 in SL::Dev::Record
457 chart => $chart_reisekosten,
459 tax_id => $tax_high->id,
462 chart => $chart_reisekosten,
464 tax_id => $tax_low->id,
468 return $ap_transaction;
471 sub create_gl_transaction_for_date {
472 my ($reference, $transdate, $debitamount) = @_;
474 my $gl_transaction = create_gl_transaction(
475 reference => $reference,
477 transdate => $transdate,
480 chart => $chart_income_19,
486 chart => $chart_income_7,
492 chart => $chart_cash,
493 debit => $debitamount,
499 return $gl_transaction;
502 sub get_account_balances {
504 select c.accno, sum(a.amount)
506 left join chart c on (c.id = a.chart_id)
507 where c.accno ~ '^17' or c.accno ~ '^15'
508 group by c.accno, c.description
512 my $result = selectall_hashref_query($::form, $dbh, $query);
517 my ($invoice, $expected_data, $msg) = @_;
519 my $datev = SL::DATEV->new(
520 dbh => $invoice->db->dbh,
521 trans_id => $invoice->id,
524 $datev->generate_datev_data;
525 my @data_datev = sort { $a->{umsatz} <=> $b->{umsatz} } @{ $datev->generate_datev_lines() };
527 # print Dumper(\@data_datev);
529 cmp_deeply(\@data_datev, $expected_data, $msg);
533 SL::DB::Manager::OrderItem->delete_all(all => 1);
534 SL::DB::Manager::Order->delete_all(all => 1);
535 SL::DB::Manager::InvoiceItem->delete_all(all => 1);
536 SL::DB::Manager::Invoice->delete_all(all => 1);
537 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
538 SL::DB::Manager::GLTransaction->delete_all(all => 1);
539 SL::DB::Manager::Part->delete_all(all => 1);
540 SL::DB::Manager::Customer->delete_all(all => 1);
541 SL::DB::Manager::Vendor->delete_all(all => 1);