From e592e0bc4aaf7ce59e4d5eec9834cb430ab6a33f Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Wed, 9 Oct 2019 10:12:08 +0200 Subject: [PATCH] =?utf8?q?Jahresabschlu=C3=9F=20-=20GLTransaction->post=20?= =?utf8?q?und=20Tests?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Der YearEnd Controller nutzt nun GLTransaction->post, damit muß man die acc_trans-Einträge nicht mehr von Hand zusammenbauen, und die Buchungen passieren automatisch als Transaktion, die Buchungen werden validiert und es wird ein Historieneintrag erstellt. --- SL/Controller/YearEndTransactions.pm | 384 ++++++++-------- t/year_end/year_end.t | 642 +++++++++++++++++++++++++++ 2 files changed, 815 insertions(+), 211 deletions(-) create mode 100644 t/year_end/year_end.t diff --git a/SL/Controller/YearEndTransactions.pm b/SL/Controller/YearEndTransactions.pm index 00dea4b67..43b4fee56 100644 --- a/SL/Controller/YearEndTransactions.pm +++ b/SL/Controller/YearEndTransactions.pm @@ -49,7 +49,6 @@ sub action_year_end_bookings { $self->_parse_form; - eval { _year_end_bookings( start_date => $self->cb_startdate, cb_date => $self->cb_date, @@ -66,9 +65,9 @@ sub action_year_end_bookings { ); my $html = $self->render('yearend/_charts', { layout => 0 , process => 1, output => 0 }, - charts => $report_data, - profit_loss_sum => $profit_loss_sum, - ); + charts => $report_data, + profit_loss_sum => $profit_loss_sum, + ); return $self->js->flash('info', t8('Year-end bookings were successfully completed!')) ->html('#charts', $html) ->render; @@ -105,8 +104,8 @@ sub action_update_charts { ); $self->render('yearend/_charts', { layout => 0 , process => 1 }, - charts => $report_data, - profit_loss_sum => $profit_loss_sum, + charts => $report_data, + profit_loss_sum => $profit_loss_sum, ); } @@ -121,7 +120,6 @@ sub _parse_form { $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date))); die "cb_date must come after start_date" unless $self->cb_date > $self->cb_startdate; - } sub _year_end_bookings { @@ -140,6 +138,10 @@ sub _year_end_bookings { cb_date => $cb_date, ); + # load all charts from report as objects and store them in a hash + my @report_chart_ids = map { $_->{chart_id} } @{ $report_data }; + my %charts_by_id = map { ( $_->id => $_ ) } @{ SL::DB::Manager::Chart->get_all(where => [ id => \@report_chart_ids ]) }; + my @asset_accounts = grep { $_->{account_type} eq 'asset_account' } @{ $report_data }; my @profit_loss_accounts = grep { $_->{account_type} eq 'profit_loss_account' } @{ $report_data }; @@ -174,6 +176,8 @@ sub _year_end_bookings { description => 'Automatische SB-Buchungen Bestandskonten Soll für ' . $cb_date->year, ob_transaction => 0, cb_transaction => 1, + taxincluded => 0, + transactions => [], ); my $asset_ob_debit_entry = SL::DB::GLTransaction->new( employee_id => $employee_id, @@ -182,6 +186,8 @@ sub _year_end_bookings { description => 'Automatische EB-Buchungen Bestandskonten Haben für ' . $ob_date->year, ob_transaction => 1, cb_transaction => 0, + taxincluded => 0, + transactions => [], ); my $asset_cb_credit_entry = SL::DB::GLTransaction->new( employee_id => $employee_id, @@ -190,6 +196,8 @@ sub _year_end_bookings { description => 'Automatische SB-Buchungen Bestandskonten Haben für ' . $cb_date->year, ob_transaction => 0, cb_transaction => 1, + taxincluded => 0, + transactions => [], ); my $asset_ob_credit_entry = SL::DB::GLTransaction->new( employee_id => $employee_id, @@ -198,100 +206,77 @@ sub _year_end_bookings { description => 'Automatische EB-Buchungen Bestandskonten Soll für ' . $ob_date->year, ob_transaction => 1, cb_transaction => 0, + taxincluded => 0, + transactions => [], ); - $asset_cb_debit_entry->transactions([]); - $asset_ob_debit_entry->transactions([]); - $asset_cb_credit_entry->transactions([]); - $asset_ob_credit_entry->transactions([]); foreach my $asset_account ( @asset_accounts ) { next if $asset_account->{amount_with_cb} == 0; - - # create cb and ob acc_trans entry here, but decide which gl entry to add it to later - my $asset_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $asset_account->{chart_id}, - chart_link => $asset_account->{chart_link}, - tax_id => 0, - taxkey => 0, - amount => - $asset_account->{amount_with_cb}, - ); - my $asset_ob_acc = SL::DB::AccTransaction->new( - transdate => $ob_date, - ob_transaction => 1, - cb_transaction => 0, - chart_id => $asset_account->{chart_id}, - chart_link => $asset_account->{chart_link}, - tax_id => 0, - taxkey => 0, - amount => $asset_account->{amount_with_cb}, - ); + my $ass_acc = $charts_by_id{ $asset_account->{chart_id} }; if ( $asset_account->{amount_with_cb} < 0 ) { - $debit_balance += $asset_account->{amount_with_cb}; # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to debit", $asset_account->{accno}, $asset_account->{amount_with_cb})); + $debit_balance += $asset_account->{amount_with_cb}; + + $asset_cb_debit_entry->add_chart_booking( + chart => $ass_acc, + credit => - $asset_account->{amount_with_cb}, + tax_id => 0 + ); + $asset_ob_debit_entry->add_chart_booking( + chart => $ass_acc, + debit => - $asset_account->{amount_with_cb}, + tax_id => 0 + ); - $asset_cb_debit_entry->add_transactions($asset_cb_acc); - $asset_ob_debit_entry->add_transactions($asset_ob_acc); } else { # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to credit", $asset_account->{accno}, $asset_account->{amount_with_cb})); $credit_balance += $asset_account->{amount_with_cb}; - $asset_cb_credit_entry->add_transactions($asset_cb_acc); - $asset_ob_credit_entry->add_transactions($asset_ob_acc); + + $asset_cb_credit_entry->add_chart_booking( + chart => $ass_acc, + debit => $asset_account->{amount_with_cb}, + tax_id => 0 + ); + $asset_ob_credit_entry->add_chart_booking( + chart => $ass_acc, + credit => $asset_account->{amount_with_cb}, + tax_id => 0 + ); }; }; - my $debit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, # maybe leave chart_link empty? - tax_id => 0, - taxkey => 0, - amount => $debit_balance, - ); - my $debit_ob_acc = SL::DB::AccTransaction->new( - transdate => $ob_date, - ob_transaction => 1, - cb_transaction => 0, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, - tax_id => 0, - taxkey => 0, - amount => - $debit_balance, - ); - my $credit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, # maybe leave chart_link empty? - tax_id => 0, - taxkey => 0, - amount => $credit_balance, - ); - my $credit_ob_acc = SL::DB::AccTransaction->new( - transdate => $ob_date, - ob_transaction => 1, - cb_transaction => 0, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, - tax_id => 0, - taxkey => 0, - amount => - $credit_balance, - ); - $asset_cb_debit_entry->add_transactions($debit_cb_acc); - $asset_ob_debit_entry->add_transactions($debit_ob_acc); - $asset_cb_credit_entry->add_transactions($credit_cb_acc); - $asset_ob_credit_entry->add_transactions($credit_ob_acc); + if ( $debit_balance ) { + $asset_cb_debit_entry->add_chart_booking( + chart => $carry_over_chart, + debit => -1 * $debit_balance, + tax_id => 0, + ); + + $asset_ob_debit_entry->add_chart_booking( + chart => $carry_over_chart, + credit => -1 * $debit_balance, + tax_id => 0, + ); + }; + + if ( $credit_balance ) { + $asset_cb_credit_entry->add_chart_booking( + chart => $carry_over_chart, + credit => $credit_balance, + tax_id => 0, + ); + $asset_ob_credit_entry->add_chart_booking( + chart => $carry_over_chart, + debit => $credit_balance, + tax_id => 0, + ); + }; - $asset_cb_debit_entry->save if scalar @{ $asset_cb_debit_entry->transactions } > 1; - $asset_ob_debit_entry->save if scalar @{ $asset_ob_debit_entry->transactions } > 1; - $asset_cb_credit_entry->save if scalar @{ $asset_cb_credit_entry->transactions } > 1; - $asset_ob_credit_entry->save if scalar @{ $asset_ob_credit_entry->transactions } > 1; + $asset_cb_debit_entry->post if scalar @{ $asset_cb_debit_entry->transactions } > 1; + $asset_ob_debit_entry->post if scalar @{ $asset_ob_debit_entry->transactions } > 1; + $asset_cb_credit_entry->post if scalar @{ $asset_cb_credit_entry->transactions } > 1; + $asset_ob_credit_entry->post if scalar @{ $asset_ob_credit_entry->transactions } > 1; ####### profit-loss accounts ####### # these only have a closing balance, the balance is transferred to the profit-loss account @@ -301,6 +286,7 @@ sub _year_end_bookings { my $profit_loss_sum = sum map { $_->{amount_with_cb} } grep { $_->{account_type} eq 'profit_loss_account' } @{$report_data}; + $profit_loss_sum ||= 0; my $pl_chart; if ( $profit_loss_sum > 0 ) { $pl_chart = $profit_chart; @@ -318,6 +304,8 @@ sub _year_end_bookings { description => 'Automatische SB-Buchungen Erfolgskonten Soll für ' . $cb_date->year, ob_transaction => 0, cb_transaction => 1, + taxincluded => 0, + transactions => [], ); my $pl_cb_credit_entry = SL::DB::GLTransaction->new( employee_id => $employee_id, @@ -326,148 +314,123 @@ sub _year_end_bookings { description => 'Automatische SB-Buchungen Erfolgskonten Haben für ' . $cb_date->year, ob_transaction => 0, cb_transaction => 1, + taxincluded => 0, + transactions => [], ); - $pl_cb_debit_entry->transactions([]); - $pl_cb_credit_entry->transactions([]); foreach my $profit_loss_account ( @profit_loss_accounts ) { # $main::lxdebug->message(0, sprintf("found chart %s with balance %s", $profit_loss_account->{accno}, $profit_loss_account->{amount_with_cb})); + my $chart = $charts_by_id{ $profit_loss_account->{chart_id} }; next if $profit_loss_account->{amount_with_cb} == 0; - my $debit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $profit_loss_account->{chart_id}, - chart_link => $profit_loss_account->{chart_link}, - tax_id => 0, - taxkey => 0, - amount => - $profit_loss_account->{amount_with_cb}, - ); - my $credit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $profit_loss_account->{chart_id}, - chart_link => $profit_loss_account->{chart_link}, - tax_id => 0, - taxkey => 0, - amount => $profit_loss_account->{amount_with_cb}, - ); - if ( { $profit_loss_account->{amount_with_cb} < 0 } ) { - $pl_debit_balance += $profit_loss_account->{amount_with_cb}; - $pl_cb_debit_entry->add_transactions($debit_cb_acc); + if ( $profit_loss_account->{amount_with_cb} < 0 ) { + $pl_debit_balance -= $profit_loss_account->{amount_with_cb}; + $pl_cb_debit_entry->add_chart_booking( + chart => $chart, + tax_id => 0, + credit => - $profit_loss_account->{amount_with_cb}, + ); } else { $pl_credit_balance += $profit_loss_account->{amount_with_cb}; - $pl_cb_credit_entry->add_transactions($credit_cb_acc); + $pl_cb_credit_entry->add_chart_booking( + chart => $chart, + tax_id => 0, + debit => $profit_loss_account->{amount_with_cb}, + ); }; }; - $debit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $pl_chart->id, - chart_link => $pl_chart->link, - tax_id => 0, - taxkey => 0, - amount => $pl_debit_balance, - ); - $credit_cb_acc = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $pl_chart->id, - chart_link => $pl_chart->link, - tax_id => 0, - taxkey => 0, - amount => - $pl_credit_balance, - ); - $pl_cb_debit_entry->add_transactions($debit_cb_acc); - $pl_cb_credit_entry->add_transactions($credit_cb_acc); + # $main::lxdebug->message(0, "pl_debit_balance = $pl_debit_balance"); + # $main::lxdebug->message(0, "pl_credit_balance = $pl_credit_balance"); + + $pl_cb_debit_entry->add_chart_booking( + chart => $pl_chart, + tax_id => 0, + debit => $pl_debit_balance, + ) if $pl_debit_balance; + + $pl_cb_credit_entry->add_chart_booking( + chart => $pl_chart, + tax_id => 0, + credit => $pl_credit_balance, + ) if $pl_credit_balance; - $pl_cb_debit_entry->save if scalar @{ $pl_cb_debit_entry->transactions } > 1; - $pl_cb_credit_entry->save if scalar @{ $pl_cb_credit_entry->transactions } > 1; + # printf("debit : %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $pl_cb_debit_entry->transactions }; + # printf("credit: %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $pl_cb_credit_entry->transactions }; + + $pl_cb_debit_entry->post if scalar @{ $pl_cb_debit_entry->transactions } > 1; + $pl_cb_credit_entry->post if scalar @{ $pl_cb_credit_entry->transactions } > 1; ######### profit-loss transfer ######### # and finally transfer the new balance of the profit-loss account via the carry-over account # we want to use profit_loss_sum with cb! - my $carry_over_cb_entry = SL::DB::GLTransaction->new( - employee_id => $employee_id, - transdate => $cb_date, - reference => 'SB ' . $cb_date->year, - description => sprintf('Automatische SB-Buchung für %s %s', - $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag', - $cb_date->year, - ), - ob_transaction => 0, - cb_transaction => 1, - ); - my $carry_over_ob_entry = SL::DB::GLTransaction->new( - employee_id => $employee_id, - transdate => $ob_date, - reference => 'EB ' . $ob_date->year, - description => sprintf('Automatische EB-Buchung für %s %s', - $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag', - $ob_date->year, - ), - ob_transaction => 1, - cb_transaction => 0, - ); - $carry_over_cb_entry->transactions([]); - $carry_over_ob_entry->transactions([]); + if ( $profit_loss_sum != 0 ) { - my $carry_over_cb_acc_co = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, - tax_id => 0, - taxkey => 0, - amount => $profit_loss_sum, - ); - my $carry_over_cb_acc_pl = SL::DB::AccTransaction->new( - transdate => $cb_date, - ob_transaction => 0, - cb_transaction => 1, - chart_id => $pl_chart->id, - chart_link => $pl_chart->link, - tax_id => 0, - taxkey => 0, - amount => - $profit_loss_sum, - ); + my $carry_over_cb_entry = SL::DB::GLTransaction->new( + employee_id => $employee_id, + transdate => $cb_date, + reference => 'SB ' . $cb_date->year, + description => sprintf('Automatische SB-Buchung für %s %s', + $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag', + $cb_date->year, + ), + ob_transaction => 0, + cb_transaction => 1, + taxincluded => 0, + transactions => [], + ); + my $carry_over_ob_entry = SL::DB::GLTransaction->new( + employee_id => $employee_id, + transdate => $ob_date, + reference => 'EB ' . $ob_date->year, + description => sprintf('Automatische EB-Buchung für %s %s', + $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag', + $ob_date->year, + ), + ob_transaction => 1, + cb_transaction => 0, + taxincluded => 0, + transactions => [], + ); - $carry_over_cb_entry->add_transactions($carry_over_cb_acc_co); - $carry_over_cb_entry->add_transactions($carry_over_cb_acc_pl); - $carry_over_cb_entry->save if $profit_loss_sum != 0; + my ($amount1, $amount2); + if ( $profit_loss_sum < 0 ) { + $amount1 = 'debit'; + $amount2 = 'credit'; + } else { + $amount1 = 'credit'; + $amount2 = 'debit'; + }; - my $carry_over_ob_acc_co = SL::DB::AccTransaction->new( - transdate => $ob_date, - ob_transaction => 1, - cb_transaction => 0, - chart_id => $pl_chart->id, - chart_link => $pl_chart->link, - tax_id => 0, - taxkey => 0, - amount => $profit_loss_sum, - ); - my $carry_over_ob_acc_pl = SL::DB::AccTransaction->new( - transdate => $ob_date, - ob_transaction => 1, - cb_transaction => 0, - chart_id => $carry_over_chart->id, - chart_link => $carry_over_chart->link, - tax_id => 0, - taxkey => 0, - amount => - $profit_loss_sum, - ); + $carry_over_cb_entry->add_chart_booking( + chart => $carry_over_chart, + tax_id => 0, + $amount1 => abs($profit_loss_sum), + ); + $carry_over_cb_entry->add_chart_booking( + chart => $pl_chart, + tax_id => 0, + $amount2 => abs($profit_loss_sum), + ); + $carry_over_ob_entry->add_chart_booking( + chart => $carry_over_chart, + tax_id => 0, + $amount2 => abs($profit_loss_sum), + ); + $carry_over_ob_entry->add_chart_booking( + chart => $pl_chart, + tax_id => 0, + $amount1 => abs($profit_loss_sum), + ); + + # printf("debit : %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $carry_over_ob_entry->transactions }; + # printf("credit: %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $carry_over_ob_entry->transactions }; - $carry_over_ob_entry->add_transactions($carry_over_ob_acc_co); - $carry_over_ob_entry->add_transactions($carry_over_ob_acc_pl); - $carry_over_ob_entry->save if $profit_loss_sum != 0; + $carry_over_cb_entry->post if scalar @{ $carry_over_cb_entry->transactions } > 1; + $carry_over_ob_entry->post if scalar @{ $carry_over_ob_entry->transactions } > 1; + }; my $consistency_query = <dbh, $consistency_query, - $cb_date, - $ob_date - ); + my ($sum) = selectrow_query($::form, $db->dbh, $consistency_query, + $cb_date, + $ob_date + ); die "acc_trans transactions don't add up to zero" unless $sum == 0; 1; @@ -500,7 +463,6 @@ sub _report { select c.id as chart_id, c.accno, c.description, - c.link as chart_link, c.category, sum(a.amount) filter (where cb_transaction is false and ob_transaction is false) as amount, sum(a.amount) filter (where ob_transaction is true ) as ob_amount, diff --git a/t/year_end/year_end.t b/t/year_end/year_end.t new file mode 100644 index 000000000..c6a2b77d0 --- /dev/null +++ b/t/year_end/year_end.t @@ -0,0 +1,642 @@ +use strict; +use warnings; + +use Test::More tests => 18; +use lib 't'; +use utf8; + +use Carp; +use Data::Dumper; +use Support::TestSetup; +use Test::Exception; +use SL::DBUtils qw(selectall_hashref_query); + +use SL::DB::BankAccount; +use SL::DB::Chart; +use SL::DB::Invoice; +use SL::DB::PurchaseInvoice; + +use SL::Dev::Record qw(create_ar_transaction create_ap_transaction create_gl_transaction); + +use SL::Controller::YearEndTransactions; + +Support::TestSetup::login(); + +clear_up(); + +# comments: + +# * in the default test client the tax accounts are configured as I/E rather than A/L +# * also the default test client has the accounting method "cash" rather than "accrual" +# (Ist-versteuerung, rather than Soll-versteuerung) + +my $year = 2019; + +note('configuring accounts'); +my $bank_account = SL::DB::BankAccount->new( + account_number => '123', + bank_code => '123', + iban => '123', + bic => '123', + bank => '123', + chart_id => SL::DB::Manager::Chart->find_by(description => 'Bank')->id, + name => SL::DB::Manager::Chart->find_by(description => 'Bank')->description, +)->save; + +my $profit_account = SL::DB::Manager::Chart->find_by(accno => '0890') // + SL::DB::Chart->new( + accno => '0890', + description => 'Gewinnvortrag vor Verwendung', + charttype => 'A', + category => 'Q', + link => '', + taxkey_id => '0', + datevautomatik => 'f', + )->save; + +my $loss_account = SL::DB::Manager::Chart->find_by(accno => '0868') // + SL::DB::Chart->new( + accno => '0868', + description => 'Verlustvortrag vor Verwendung', + charttype => 'A', + category => 'Q', + link => '', + taxkey_id => '0', + datevautomatik => 'f', + )->save; + +my $carry_over_chart = SL::DB::Manager::Chart->find_by(accno => 9000); +my $income_chart = SL::DB::Manager::Chart->find_by(accno => '8400'); # income 19%, taxkey 3 +my $bank = SL::DB::Manager::Chart->find_by(description => 'Bank'); +my $cash = SL::DB::Manager::Chart->find_by(description => 'Kasse'); +my $privateinlagen = SL::DB::Manager::Chart->find_by(description => 'Privateinlagen'); +my $betriebsbedarf = SL::DB::Manager::Chart->find_by(description => 'Betriebsbedarf'); + +my $dbh = SL::DB->client->dbh; +$dbh->do('UPDATE defaults SET carry_over_account_chart_id = ' . $carry_over_chart->id); +$dbh->do('UPDATE defaults SET profit_carried_forward_chart_id = ' . $profit_account->id); +$dbh->do('UPDATE defaults SET loss_carried_forward_chart_id = ' . $loss_account->id); + + +note('creating transactions'); +my $ar_transaction = create_ar_transaction( + taxincluded => 0, + bookings => [ + { + chart => $income_chart, # income 19%, taxkey 3 + amount => 140, + } + ], +); + +$ar_transaction->pay_invoice( + chart_id => $bank_account->chart_id, + transdate => DateTime->today_local->to_kivitendo, + amount => $ar_transaction->amount, + payment_type => 'without_skonto', + ); + +my $ar_transaction2 = create_ar_transaction( + taxincluded => 1, + bookings => [ + { + chart => $income_chart, # income 19%, taxkey 3 + amount => 166.60, + } + ], +); + +my $ap_transaction = create_ap_transaction( + taxincluded => 0, + bookings => [ + { + chart => SL::DB::Manager::Chart->find_by( accno => '3400' ), # Wareneingang 19%, taxkey 9 + amount => 100, + } + ], +); + + +gl_booking(40, "01.01.$year", 'foo', 'bar', $bank, $privateinlagen, 1, 0); + +is(SL::DB::Manager::AccTransaction->get_all_count( ), 13, 'acc_trans transactions created ok'); +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 2, 'acc_trans ob_transactions created ok'); +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 0, 'no cb_transactions created ok'); + +is_deeply( &get_account_balances, + [ + { + 'accno' => '1200', + 'account_type' => 'asset_account', + 'sum' => '-206.60000' + }, + { + 'accno' => '1400', + 'account_type' => 'asset_account', + 'sum' => '-166.60000' + }, + { + 'accno' => '1600', + 'account_type' => 'asset_account', + 'sum' => '119.00000' + }, + { + 'accno' => '1890', + 'account_type' => 'asset_account', + 'sum' => '40.00000' + }, + { + 'accno' => '1576', + 'account_type' => 'profit_loss_account', + 'sum' => '-19.00000' + }, + { + 'accno' => '1776', + 'account_type' => 'profit_loss_account', + 'sum' => '53.20000' + }, + { + 'accno' => '3400', + 'account_type' => 'profit_loss_account', + 'sum' => '-100.00000' + }, + { + 'accno' => '8400', + 'account_type' => 'profit_loss_account', + 'sum' => '280.00000' + } + ], + 'account balances before year_end bookings ok', +); + +# accno | account_type | sum +# -------+---------------------+------------ +# 1200 | asset_account | -206.60000 +# 1400 | asset_account | -166.60000 +# 1600 | asset_account | 119.00000 +# 1890 | asset_account | 40.00000 +# 1576 | profit_loss_account | -19.00000 +# 1776 | profit_loss_account | 53.20000 +# 3400 | profit_loss_account | -100.00000 +# 8400 | profit_loss_account | 280.00000 + + +note('running year-end transactions'); +my $start_date = DateTime->new(year => $year, month => 1, day => 1); +my $cb_date = DateTime->new(year => $year, month => 12, day => 31); +my $ob_date = $cb_date->clone->add(days => 1); + +SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date, + cb_date => $cb_date, + ); + +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok'); +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 5, 'GL cb_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 4, 'GL ob_transactions created ok'); + +my $final_account_balances = [ + { + 'accno' => '0890', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'Q', + 'cb_amount' => '0.00000', + 'ob_amount' => undef, + 'ob_next_year' => '214.20000', + 'type' => 'asset', + 'year_end_amount' => undef + }, + { + 'accno' => '1200', + 'amount' => '-166.60000', + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '-206.60000', + 'ob_amount' => '-40.00000', + 'ob_next_year' => '-206.60000', + 'type' => 'asset', + 'year_end_amount' => '-206.60000' + }, + { + 'accno' => '1400', + 'amount' => '-166.60000', + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '-166.60000', + 'ob_amount' => undef, + 'ob_next_year' => '-166.60000', + 'type' => 'asset', + 'year_end_amount' => '-166.60000' + }, + { + 'accno' => '1600', + 'amount' => '119.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'L', + 'cb_amount' => '119.00000', + 'ob_amount' => undef, + 'ob_next_year' => '119.00000', + 'type' => 'asset', + 'year_end_amount' => '119.00000' + }, + { + 'accno' => '1890', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'Q', + 'cb_amount' => '40.00000', + 'ob_amount' => '40.00000', + 'ob_next_year' => '40.00000', + 'type' => 'asset', + 'year_end_amount' => '40.00000' + }, + { + 'accno' => '9000', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '0.00000', + 'ob_amount' => undef, + 'ob_next_year' => '0.00000', + 'type' => 'asset', + 'year_end_amount' => undef + }, + { + 'accno' => '1576', + 'amount' => '-19.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'E', + 'cb_amount' => '-19.00000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '-19.00000' + }, + { + 'accno' => '1776', + 'amount' => '53.20000', + 'amount_with_cb' => '0.00000', + 'cat' => 'I', + 'cb_amount' => '53.20000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '53.20000' + }, + { + 'accno' => '3400', + 'amount' => '-100.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'E', + 'cb_amount' => '-100.00000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '-100.00000' + }, + { + 'accno' => '8400', + 'amount' => '280.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'I', + 'cb_amount' => '280.00000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '280.00000' + } + ]; + +# running _year_end_bookings several times shouldn't change the anything, the +# second and third run should be no-ops, at least while no further bookings where +# made + +SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date, + cb_date => $cb_date, + ); + +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok'); +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 5, 'GL cb_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 4, 'GL ob_transactions created ok'); + + +# all asset accounts should be the same, except 0890, which should be the sum of p/l-accounts +# all p/l account should be 0 + +# accno | account_type | sum +# -------+---------------------+------------ +# 0890 | asset_account | 214.20000 +# 1200 | asset_account | -206.60000 +# 1400 | asset_account | -166.60000 +# 1600 | asset_account | 119.00000 +# 1890 | asset_account | 40.00000 +# 9000 | asset_account | 0.00000 +# 1576 | profit_loss_account | 0.00000 +# 1776 | profit_loss_account | 0.00000 +# 3400 | profit_loss_account | 0.00000 +# 8400 | profit_loss_account | 0.00000 +# (10 rows) + +is_deeply( &get_final_balances, + $final_account_balances, + 'balances after second year_end ok (nothing changed)'); + + +# select c.accno, +# c.description, +# c.category as cat, +# sum(a.amount ) filter (where ob_transaction is true and a.transdate < '2020-01-01') as ob_amount, +# sum(a.amount ) filter (where cb_transaction is false and ob_transaction is false and a.transdate < '2020-01-01') as amount, +# sum(a.amount ) filter (where cb_transaction is false and a.transdate < '2020-01-01') as year_end_amount, +# sum(a.amount ) filter (where a.transdate < '2020-01-01') as amount_with_cb, +# sum(a.amount * -1) filter (where cb_transaction is true and a.transdate < '2020-01-01') as cb_amount, +# sum(a.amount ) filter (where ob_transaction is true and a.transdate >= '2020-01-01') as ob_next_year, +# case when c.category = ANY( '{I,E}' ) then 'pl' +# when c.category = ANY( '{A,C,L,Q}' ) then 'asset' +# else null +# end as type +# from acc_trans a +# inner join chart c on (c.id = a.chart_id) +# where a.transdate >= '2019-01-01' +# and a.transdate <= '2020-01-01' +# group by c.id, c.accno, c.category +# order by type, c.accno; +# accno | description | cat | ob_amount | amount | year_end_amount | amount_with_cb | cb_amount | ob_next_year | type +# -------+-------------------------------------+-----+-----------+------------+-----------------+----------------+------------+--------------+------- +# 0890 | Gewinnvortrag vor Verwendung | Q | | | | 0.00000 | 0.00000 | 214.20000 | asset +# 1200 | Bank | A | -40.00000 | -166.60000 | -206.60000 | 0.00000 | -206.60000 | -206.60000 | asset +# 1400 | Ford. a.Lieferungen und Leistungen | A | | -166.60000 | -166.60000 | 0.00000 | -166.60000 | -166.60000 | asset +# 1600 | Verbindlichkeiten aus Lief.u.Leist. | L | | 119.00000 | 119.00000 | 0.00000 | 119.00000 | 119.00000 | asset +# 1890 | Privateinlagen | Q | 40.00000 | | 40.00000 | 0.00000 | 40.00000 | 40.00000 | asset +# 9000 | Saldenvorträge,Sachkonten | A | | | | 0.00000 | 0.00000 | 0.00000 | asset +# 1576 | Abziehbare Vorsteuer 19 % | E | | -19.00000 | -19.00000 | 0.00000 | -19.00000 | | pl +# 1776 | Umsatzsteuer 19 % | I | | 53.20000 | 53.20000 | 0.00000 | 53.20000 | | pl +# 3400 | Wareneingang 16%/19% Vorsteuer | E | | -100.00000 | -100.00000 | 0.00000 | -100.00000 | | pl +# 8400 | Erlöse 16%/19% USt. | I | | 280.00000 | 280.00000 | 0.00000 | 280.00000 | | pl +# (10 rows) + +# ob_amount + amount = year_end_amount +# amount_with_cb should be 0 after year-end transactions +# year_end_amount and cb_amount should be the same (will be true with amount_with_cb = 0) +# cb_amount should match ob_next_year for asset accounts, except for profit-carried-forward +# ob_next_year should be empty for profit-loss-accounts + +# Oops, we forgot some bookings, lets quickly add them and run +#_year_end_bookings again. + +# Just these new bookings by themselves will lead to a loss, so the loss account +# will be booked rather than the profit account. +# It would probably be better to check the total profit/loss so far, and +# adjust that profit-loss-carry-over # chart, rather than creating a new entry +# for the loss. + +gl_booking(10, "22.12.$year", 'foo', 'bar', $cash, $bank, 0, 0); +gl_booking(5, "22.12.$year", 'foo', 'bar', $betriebsbedarf, $cash, 0, 0); + +SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date, + cb_date => $cb_date, + ); + +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 23, 'acc_trans cb_transactions created ok'); +is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 16, 'acc_trans ob_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]), 9, 'GL cb_transactions created ok'); +is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]), 7, 'GL ob_transactions created ok'); + +is_deeply( &get_final_balances, + [ + { + 'accno' => '0868', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'Q', + 'cb_amount' => '0.00000', + 'ob_amount' => undef, + 'ob_next_year' => '-5.00000', + 'type' => 'asset', + 'year_end_amount' => undef + }, + { + 'accno' => '0890', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'Q', + 'cb_amount' => '0.00000', + 'ob_amount' => undef, + 'ob_next_year' => '214.20000', + 'type' => 'asset', + 'year_end_amount' => undef + }, + { + 'accno' => '1000', + 'amount' => '-5.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '-5.00000', + 'ob_amount' => undef, + 'ob_next_year' => '-5.00000', + 'type' => 'asset', + 'year_end_amount' => '-5.00000' + }, + { + 'accno' => '1200', + 'amount' => '-156.60000', + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '-196.60000', + 'ob_amount' => '-40.00000', + 'ob_next_year' => '-196.60000', + 'type' => 'asset', + 'year_end_amount' => '-196.60000' + }, + { + 'accno' => '1400', + 'amount' => '-166.60000', + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '-166.60000', + 'ob_amount' => undef, + 'ob_next_year' => '-166.60000', + 'type' => 'asset', + 'year_end_amount' => '-166.60000' + }, + { + 'accno' => '1600', + 'amount' => '119.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'L', + 'cb_amount' => '119.00000', + 'ob_amount' => undef, + 'ob_next_year' => '119.00000', + 'type' => 'asset', + 'year_end_amount' => '119.00000' + }, + { + 'accno' => '1890', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'Q', + 'cb_amount' => '40.00000', + 'ob_amount' => '40.00000', + 'ob_next_year' => '40.00000', + 'type' => 'asset', + 'year_end_amount' => '40.00000' + }, + { + 'accno' => '9000', + 'amount' => undef, + 'amount_with_cb' => '0.00000', + 'cat' => 'A', + 'cb_amount' => '0.00000', + 'ob_amount' => undef, + 'ob_next_year' => '0.00000', + 'type' => 'asset', + 'year_end_amount' => undef + }, + { + 'accno' => '1576', + 'amount' => '-19.80000', + 'amount_with_cb' => '0.00000', + 'cat' => 'E', + 'cb_amount' => '-19.80000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '-19.80000' + }, + { + 'accno' => '1776', + 'amount' => '53.20000', + 'amount_with_cb' => '0.00000', + 'cat' => 'I', + 'cb_amount' => '53.20000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '53.20000' + }, + { + 'accno' => '3400', + 'amount' => '-100.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'E', + 'cb_amount' => '-100.00000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '-100.00000' + }, + { + 'accno' => '4980', + 'amount' => '-4.20000', + 'amount_with_cb' => '0.00000', + 'cat' => 'E', + 'cb_amount' => '-4.20000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '-4.20000' + }, + { + 'accno' => '8400', + 'amount' => '280.00000', + 'amount_with_cb' => '0.00000', + 'cat' => 'I', + 'cb_amount' => '280.00000', + 'ob_amount' => undef, + 'ob_next_year' => undef, + 'type' => 'pl', + 'year_end_amount' => '280.00000' + }, + ], + 'balances after third year_end ok'); + +clear_up(); +done_testing; + +1; + +sub clear_up { + foreach (qw(BankAccount + GLTransaction + AccTransaction + InvoiceItem + Invoice + PurchaseInvoice + Part + Customer + ) + ) { + "SL::DB::Manager::${_}"->delete_all(all => 1); + } +}; + +sub get_account_balances { + my $query = <= ? + and a.transdate <= ? + group by c.id, c.accno, c.category + order by type, c.accno +SQL + + my $result = selectall_hashref_query($::form, $dbh, $query, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $start_date, $ob_date); + return $result; +} + +sub gl_booking { + # wrapper around SL::Dev::Record::create_gl_transaction for quickly creating transactions + my ($amount, $date, $reference, $description, $gegenkonto, $konto, $ob, $cb) = @_; + + my $transdate = $::locale->parse_date_to_object($date); + + return create_gl_transaction( + ob_transaction => $ob, + cb_transaction => $cb, + transdate => $transdate, + reference => $reference, + description => $description, + bookings => [ + { + chart => $konto, + credit => $amount, + }, + { + chart => $gegenkonto, + debit => $amount, + }, + ], + ); +}; -- 2.20.1