4 use Test::More tests => 18;
10 use Support::TestSetup;
12 use SL::DBUtils qw(selectall_hashref_query);
14 use SL::DB::BankAccount;
17 use SL::DB::PurchaseInvoice;
19 use SL::Dev::Record qw(create_ar_transaction create_ap_transaction create_gl_transaction);
21 use SL::Controller::YearEndTransactions;
23 Support::TestSetup::login();
29 # * in the default test client the tax accounts are configured as I/E rather than A/L
30 # * also the default test client has the accounting method "cash" rather than "accrual"
31 # (Ist-versteuerung, rather than Soll-versteuerung)
33 my $year = DateTime->today->year;
35 note('configuring accounts');
36 my $bank_account = SL::DB::BankAccount->new(
37 account_number => '123',
42 chart_id => SL::DB::Manager::Chart->find_by(description => 'Bank')->id,
43 name => SL::DB::Manager::Chart->find_by(description => 'Bank')->description,
46 my $profit_account = SL::DB::Manager::Chart->find_by(accno => '0890') //
49 description => 'Gewinnvortrag vor Verwendung',
54 datevautomatik => 'f',
57 my $loss_account = SL::DB::Manager::Chart->find_by(accno => '0868') //
60 description => 'Verlustvortrag vor Verwendung',
65 datevautomatik => 'f',
68 my $carry_over_chart = SL::DB::Manager::Chart->find_by(accno => 9000);
69 my $income_chart = SL::DB::Manager::Chart->find_by(accno => '8400'); # income 19%, taxkey 3
70 my $bank = SL::DB::Manager::Chart->find_by(description => 'Bank');
71 my $cash = SL::DB::Manager::Chart->find_by(description => 'Kasse');
72 my $privateinlagen = SL::DB::Manager::Chart->find_by(description => 'Privateinlagen');
73 my $betriebsbedarf = SL::DB::Manager::Chart->find_by(description => 'Betriebsbedarf');
75 my $dbh = SL::DB->client->dbh;
76 $dbh->do('UPDATE defaults SET carry_over_account_chart_id = ' . $carry_over_chart->id);
77 $dbh->do('UPDATE defaults SET profit_carried_forward_chart_id = ' . $profit_account->id);
78 $dbh->do('UPDATE defaults SET loss_carried_forward_chart_id = ' . $loss_account->id);
81 note('creating transactions');
82 my $ar_transaction = create_ar_transaction(
86 chart => $income_chart, # income 19%, taxkey 3
92 $ar_transaction->pay_invoice(
93 chart_id => $bank_account->chart_id,
94 transdate => DateTime->today_local->to_kivitendo,
95 amount => $ar_transaction->amount,
96 payment_type => 'without_skonto',
99 my $ar_transaction2 = create_ar_transaction(
103 chart => $income_chart, # income 19%, taxkey 3
109 my $ap_transaction = create_ap_transaction(
113 chart => SL::DB::Manager::Chart->find_by( accno => '3400' ), # Wareneingang 19%, taxkey 9
120 gl_booking(40, "01.01.$year", 'foo', 'bar', $bank, $privateinlagen, 1, 0);
122 is(SL::DB::Manager::AccTransaction->get_all_count( ), 13, 'acc_trans transactions created ok');
123 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 2, 'acc_trans ob_transactions created ok');
124 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 0, 'no cb_transactions created ok');
126 is_deeply( &get_account_balances,
130 'account_type' => 'asset_account',
131 'sum' => '-206.60000'
135 'account_type' => 'asset_account',
136 'sum' => '-166.60000'
140 'account_type' => 'asset_account',
145 'account_type' => 'asset_account',
150 'account_type' => 'profit_loss_account',
155 'account_type' => 'profit_loss_account',
160 'account_type' => 'profit_loss_account',
161 'sum' => '-100.00000'
165 'account_type' => 'profit_loss_account',
169 'account balances before year_end bookings ok',
172 # accno | account_type | sum
173 # -------+---------------------+------------
174 # 1200 | asset_account | -206.60000
175 # 1400 | asset_account | -166.60000
176 # 1600 | asset_account | 119.00000
177 # 1890 | asset_account | 40.00000
178 # 1576 | profit_loss_account | -19.00000
179 # 1776 | profit_loss_account | 53.20000
180 # 3400 | profit_loss_account | -100.00000
181 # 8400 | profit_loss_account | 280.00000
184 note('running year-end transactions');
185 my $start_date = DateTime->new(year => $year, month => 1, day => 1);
186 my $cb_date = DateTime->new(year => $year, month => 12, day => 31);
187 my $ob_date = $cb_date->clone->add(days => 1);
189 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
193 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok');
194 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok');
195 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 5, 'GL cb_transactions created ok');
196 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 4, 'GL ob_transactions created ok');
198 my $final_account_balances = [
202 'amount_with_cb' => '0.00000',
204 'cb_amount' => '0.00000',
205 'ob_amount' => undef,
206 'ob_next_year' => '214.20000',
208 'year_end_amount' => undef
212 'amount' => '-166.60000',
213 'amount_with_cb' => '0.00000',
215 'cb_amount' => '-206.60000',
216 'ob_amount' => '-40.00000',
217 'ob_next_year' => '-206.60000',
219 'year_end_amount' => '-206.60000'
223 'amount' => '-166.60000',
224 'amount_with_cb' => '0.00000',
226 'cb_amount' => '-166.60000',
227 'ob_amount' => undef,
228 'ob_next_year' => '-166.60000',
230 'year_end_amount' => '-166.60000'
234 'amount' => '119.00000',
235 'amount_with_cb' => '0.00000',
237 'cb_amount' => '119.00000',
238 'ob_amount' => undef,
239 'ob_next_year' => '119.00000',
241 'year_end_amount' => '119.00000'
246 'amount_with_cb' => '0.00000',
248 'cb_amount' => '40.00000',
249 'ob_amount' => '40.00000',
250 'ob_next_year' => '40.00000',
252 'year_end_amount' => '40.00000'
257 'amount_with_cb' => '0.00000',
259 'cb_amount' => '0.00000',
260 'ob_amount' => undef,
261 'ob_next_year' => '0.00000',
263 'year_end_amount' => undef
267 'amount' => '-19.00000',
268 'amount_with_cb' => '0.00000',
270 'cb_amount' => '-19.00000',
271 'ob_amount' => undef,
272 'ob_next_year' => undef,
274 'year_end_amount' => '-19.00000'
278 'amount' => '53.20000',
279 'amount_with_cb' => '0.00000',
281 'cb_amount' => '53.20000',
282 'ob_amount' => undef,
283 'ob_next_year' => undef,
285 'year_end_amount' => '53.20000'
289 'amount' => '-100.00000',
290 'amount_with_cb' => '0.00000',
292 'cb_amount' => '-100.00000',
293 'ob_amount' => undef,
294 'ob_next_year' => undef,
296 'year_end_amount' => '-100.00000'
300 'amount' => '280.00000',
301 'amount_with_cb' => '0.00000',
303 'cb_amount' => '280.00000',
304 'ob_amount' => undef,
305 'ob_next_year' => undef,
307 'year_end_amount' => '280.00000'
311 # running _year_end_bookings several times shouldn't change the anything, the
312 # second and third run should be no-ops, at least while no further bookings where
315 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
319 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok');
320 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok');
321 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 5, 'GL cb_transactions created ok');
322 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 4, 'GL ob_transactions created ok');
325 # all asset accounts should be the same, except 0890, which should be the sum of p/l-accounts
326 # all p/l account should be 0
328 # accno | account_type | sum
329 # -------+---------------------+------------
330 # 0890 | asset_account | 214.20000
331 # 1200 | asset_account | -206.60000
332 # 1400 | asset_account | -166.60000
333 # 1600 | asset_account | 119.00000
334 # 1890 | asset_account | 40.00000
335 # 9000 | asset_account | 0.00000
336 # 1576 | profit_loss_account | 0.00000
337 # 1776 | profit_loss_account | 0.00000
338 # 3400 | profit_loss_account | 0.00000
339 # 8400 | profit_loss_account | 0.00000
342 is_deeply( &get_final_balances,
343 $final_account_balances,
344 'balances after second year_end ok (nothing changed)');
350 # sum(a.amount ) filter (where ob_transaction is true and a.transdate < '2020-01-01') as ob_amount,
351 # sum(a.amount ) filter (where cb_transaction is false and ob_transaction is false and a.transdate < '2020-01-01') as amount,
352 # sum(a.amount ) filter (where cb_transaction is false and a.transdate < '2020-01-01') as year_end_amount,
353 # sum(a.amount ) filter (where a.transdate < '2020-01-01') as amount_with_cb,
354 # sum(a.amount * -1) filter (where cb_transaction is true and a.transdate < '2020-01-01') as cb_amount,
355 # sum(a.amount ) filter (where ob_transaction is true and a.transdate >= '2020-01-01') as ob_next_year,
356 # case when c.category = ANY( '{I,E}' ) then 'pl'
357 # when c.category = ANY( '{A,C,L,Q}' ) then 'asset'
361 # inner join chart c on (c.id = a.chart_id)
362 # where a.transdate >= '2019-01-01'
363 # and a.transdate <= '2020-01-01'
364 # group by c.id, c.accno, c.category
365 # order by type, c.accno;
366 # accno | description | cat | ob_amount | amount | year_end_amount | amount_with_cb | cb_amount | ob_next_year | type
367 # -------+-------------------------------------+-----+-----------+------------+-----------------+----------------+------------+--------------+-------
368 # 0890 | Gewinnvortrag vor Verwendung | Q | | | | 0.00000 | 0.00000 | 214.20000 | asset
369 # 1200 | Bank | A | -40.00000 | -166.60000 | -206.60000 | 0.00000 | -206.60000 | -206.60000 | asset
370 # 1400 | Ford. a.Lieferungen und Leistungen | A | | -166.60000 | -166.60000 | 0.00000 | -166.60000 | -166.60000 | asset
371 # 1600 | Verbindlichkeiten aus Lief.u.Leist. | L | | 119.00000 | 119.00000 | 0.00000 | 119.00000 | 119.00000 | asset
372 # 1890 | Privateinlagen | Q | 40.00000 | | 40.00000 | 0.00000 | 40.00000 | 40.00000 | asset
373 # 9000 | Saldenvorträge,Sachkonten | A | | | | 0.00000 | 0.00000 | 0.00000 | asset
374 # 1576 | Abziehbare Vorsteuer 19 % | E | | -19.00000 | -19.00000 | 0.00000 | -19.00000 | | pl
375 # 1776 | Umsatzsteuer 19 % | I | | 53.20000 | 53.20000 | 0.00000 | 53.20000 | | pl
376 # 3400 | Wareneingang 16%/19% Vorsteuer | E | | -100.00000 | -100.00000 | 0.00000 | -100.00000 | | pl
377 # 8400 | Erlöse 16%/19% USt. | I | | 280.00000 | 280.00000 | 0.00000 | 280.00000 | | pl
380 # ob_amount + amount = year_end_amount
381 # amount_with_cb should be 0 after year-end transactions
382 # year_end_amount and cb_amount should be the same (will be true with amount_with_cb = 0)
383 # cb_amount should match ob_next_year for asset accounts, except for profit-carried-forward
384 # ob_next_year should be empty for profit-loss-accounts
386 # Oops, we forgot some bookings, lets quickly add them and run
387 #_year_end_bookings again.
389 # Just these new bookings by themselves will lead to a loss, so the loss account
390 # will be booked rather than the profit account.
391 # It would probably be better to check the total profit/loss so far, and
392 # adjust that profit-loss-carry-over # chart, rather than creating a new entry
395 gl_booking(10, "22.12.$year", 'foo', 'bar', $cash, $bank, 0, 0);
396 gl_booking(5, "22.12.$year", 'foo', 'bar', $betriebsbedarf, $cash, 0, 0);
398 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
402 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 23, 'acc_trans cb_transactions created ok');
403 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 16, 'acc_trans ob_transactions created ok');
404 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 9, 'GL cb_transactions created ok');
405 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 7, 'GL ob_transactions created ok');
407 is_deeply( &get_final_balances,
412 'amount_with_cb' => '0.00000',
414 'cb_amount' => '0.00000',
415 'ob_amount' => undef,
416 'ob_next_year' => '-5.00000',
418 'year_end_amount' => undef
423 'amount_with_cb' => '0.00000',
425 'cb_amount' => '0.00000',
426 'ob_amount' => undef,
427 'ob_next_year' => '214.20000',
429 'year_end_amount' => undef
433 'amount' => '-5.00000',
434 'amount_with_cb' => '0.00000',
436 'cb_amount' => '-5.00000',
437 'ob_amount' => undef,
438 'ob_next_year' => '-5.00000',
440 'year_end_amount' => '-5.00000'
444 'amount' => '-156.60000',
445 'amount_with_cb' => '0.00000',
447 'cb_amount' => '-196.60000',
448 'ob_amount' => '-40.00000',
449 'ob_next_year' => '-196.60000',
451 'year_end_amount' => '-196.60000'
455 'amount' => '-166.60000',
456 'amount_with_cb' => '0.00000',
458 'cb_amount' => '-166.60000',
459 'ob_amount' => undef,
460 'ob_next_year' => '-166.60000',
462 'year_end_amount' => '-166.60000'
466 'amount' => '119.00000',
467 'amount_with_cb' => '0.00000',
469 'cb_amount' => '119.00000',
470 'ob_amount' => undef,
471 'ob_next_year' => '119.00000',
473 'year_end_amount' => '119.00000'
478 'amount_with_cb' => '0.00000',
480 'cb_amount' => '40.00000',
481 'ob_amount' => '40.00000',
482 'ob_next_year' => '40.00000',
484 'year_end_amount' => '40.00000'
489 'amount_with_cb' => '0.00000',
491 'cb_amount' => '0.00000',
492 'ob_amount' => undef,
493 'ob_next_year' => '0.00000',
495 'year_end_amount' => undef
499 'amount' => '-19.80000',
500 'amount_with_cb' => '0.00000',
502 'cb_amount' => '-19.80000',
503 'ob_amount' => undef,
504 'ob_next_year' => undef,
506 'year_end_amount' => '-19.80000'
510 'amount' => '53.20000',
511 'amount_with_cb' => '0.00000',
513 'cb_amount' => '53.20000',
514 'ob_amount' => undef,
515 'ob_next_year' => undef,
517 'year_end_amount' => '53.20000'
521 'amount' => '-100.00000',
522 'amount_with_cb' => '0.00000',
524 'cb_amount' => '-100.00000',
525 'ob_amount' => undef,
526 'ob_next_year' => undef,
528 'year_end_amount' => '-100.00000'
532 'amount' => '-4.20000',
533 'amount_with_cb' => '0.00000',
535 'cb_amount' => '-4.20000',
536 'ob_amount' => undef,
537 'ob_next_year' => undef,
539 'year_end_amount' => '-4.20000'
543 'amount' => '280.00000',
544 'amount_with_cb' => '0.00000',
546 'cb_amount' => '280.00000',
547 'ob_amount' => undef,
548 'ob_next_year' => undef,
550 'year_end_amount' => '280.00000'
553 'balances after third year_end ok');
561 foreach (qw(BankAccount
571 "SL::DB::Manager::${_}"->delete_all(all => 1);
575 sub get_account_balances {
578 case when c.category = ANY( '{I,E}' ) then 'profit_loss_account'
579 when c.category = ANY( '{A,C,L,Q}' ) then 'asset_account'
584 left join chart c on (c.id = a.chart_id)
585 group by c.accno, account_type
586 order by account_type, c.accno;
589 my $result = selectall_hashref_query($::form, $dbh, $query);
593 sub get_final_balances {
597 sum(a.amount ) filter (where ob_transaction is true and a.transdate < ?) as ob_amount,
598 sum(a.amount ) filter (where cb_transaction is false and ob_transaction is false and a.transdate < ?) as amount,
599 sum(a.amount ) filter (where cb_transaction is false and a.transdate < ?) as year_end_amount,
600 sum(a.amount ) filter (where a.transdate < ?) as amount_with_cb,
601 sum(a.amount * -1) filter (where cb_transaction is true and a.transdate < ?) as cb_amount,
602 sum(a.amount ) filter (where ob_transaction is true and a.transdate = ?) as ob_next_year,
603 case when c.category = ANY( '{I,E}' ) then 'pl'
604 when c.category = ANY( '{A,C,L,Q}' ) then 'asset'
608 inner join chart c on (c.id = a.chart_id)
609 where a.transdate >= ?
611 group by c.id, c.accno, c.category
612 order by type, c.accno
615 my $result = selectall_hashref_query($::form, $dbh, $query, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $start_date, $ob_date);
620 # wrapper around SL::Dev::Record::create_gl_transaction for quickly creating transactions
621 my ($amount, $date, $reference, $description, $gegenkonto, $konto, $ob, $cb) = @_;
623 my $transdate = $::locale->parse_date_to_object($date);
625 return create_gl_transaction(
626 ob_transaction => $ob,
627 cb_transaction => $cb,
628 transdate => $transdate,
629 reference => $reference,
630 description => $description,
637 chart => $gegenkonto,