1 package SL::Controller::YearEndTransactions;
5 use parent qw(SL::Controller::Base);
7 use utf8; # Umlauts in hardcoded German default texts
9 use SL::Locale::String qw(t8);
10 use SL::Helper::Flash;
13 use List::Util qw(sum);
17 use SL::DB::GLTransaction;
18 use SL::DB::AccTransaction;
20 use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date get_balance_startdate_method_options);
22 use Rose::Object::MakeMethods::Generic (
23 'scalar --get_set_init' => [ qw(cb_date cb_startdate ob_date) ],
26 __PACKAGE__->run_before('check_auth');
31 $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));
33 my $defaults = SL::DB::Default->get;
34 my $carry_over_chart = SL::DB::Manager::Chart->find_by( id => $defaults->carry_over_account_chart_id );
35 my $profit_chart = SL::DB::Manager::Chart->find_by( id => $defaults->profit_carried_forward_chart_id );
36 my $loss_chart = SL::DB::Manager::Chart->find_by( id => $defaults->loss_carried_forward_chart_id );
38 $self->render('yearend/form',
39 title => t8('Year-end closing'),
40 carry_over_chart => $carry_over_chart,
41 profit_chart => $profit_chart,
42 loss_chart => $loss_chart,
43 balance_startdate_method_options => get_balance_startdate_method_options(),
47 sub action_year_end_bookings {
54 _year_end_bookings( start_date => $self->cb_startdate,
55 cb_date => $self->cb_date,
59 $self->js->flash('error', t8('Error while applying year-end bookings!') . ' ' . $@);
60 return $self->js->render;
63 my ($report_data, $profit_loss_sum) = _report(
64 cb_date => $self->cb_date,
65 start_date => $self->cb_startdate,
68 my $html = $self->render('yearend/_charts', { layout => 0 , process => 1, output => 0 },
69 charts => $report_data,
70 profit_loss_sum => $profit_loss_sum,
72 return $self->js->flash('info', t8('Year-end bookings were successfully completed!'))
73 ->html('#charts', $html)
77 sub action_get_start_date {
80 my $cb_date = $self->cb_date; # parse from form via init
81 unless ( $self->cb_date ) {
82 return $self->hide('#apply_year_end_bookings_button')
83 ->flash('error', t8('Year-end date missing'))
87 $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date, $::form->{'balance_startdate_method'})));
89 # $main::lxdebug->message(0, "found start date: ", $self->cb_startdate->to_kivitendo);
91 return $self->js->val('#cb_startdate', $self->cb_startdate->to_kivitendo)
92 ->show('#apply_year_end_bookings_button')
97 sub action_update_charts {
102 my ($report_data, $profit_loss_sum) = _report(
103 cb_date => $self->cb_date,
104 start_date => $self->cb_startdate,
107 $self->render('yearend/_charts', { layout => 0 , process => 1 },
108 charts => $report_data,
109 profit_loss_sum => $profit_loss_sum,
121 $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));
123 die "cb_date must come after start_date" unless $self->cb_date > $self->cb_startdate;
127 sub _year_end_bookings {
130 my $start_date = delete $params{start_date};
131 my $cb_date = delete $params{cb_date};
133 my $defaults = SL::DB::Default->get;
134 my $carry_over_chart = SL::DB::Manager::Chart->find_by( id => $defaults->carry_over_account_chart_id ) // die t8('No carry-over chart configured!');
135 my $profit_chart = SL::DB::Manager::Chart->find_by( id => $defaults->profit_carried_forward_chart_id ) // die t8('No profit carried forward chart configured!');
136 my $loss_chart = SL::DB::Manager::Chart->find_by( id => $defaults->loss_carried_forward_chart_id ) // die t8('No profit and loss carried forward chart configured!');
138 my ($report_data, $profit_loss_sum) = _report(
139 start_date => $start_date,
143 my @asset_accounts = grep { $_->{account_type} eq 'asset_account' } @{ $report_data };
144 my @profit_loss_accounts = grep { $_->{account_type} eq 'profit_loss_account' } @{ $report_data };
146 my $ob_date = $cb_date->clone->add(days => 1);
148 my ($credit_sum, $debit_sum) = (0,0);
150 my $employee_id = SL::DB::Manager::Employee->current->id;
152 # rather than having one gl transaction for each asset account, we group all
153 # the debit sums and credit sums for cb and ob bookings, so we will have 4 gl
161 my $db = SL::DB->client;
162 $db->with_transaction(sub {
164 ######### asset accounts ########
165 # need cb and ob transactions
167 my $debit_balance = 0;
168 my $credit_balance = 0;
170 my $asset_cb_debit_entry = SL::DB::GLTransaction->new(
171 employee_id => $employee_id,
172 transdate => $cb_date,
173 reference => 'SB ' . $cb_date->year,
174 description => 'Automatische SB-Buchungen Bestandskonten Soll für ' . $cb_date->year,
178 my $asset_ob_debit_entry = SL::DB::GLTransaction->new(
179 employee_id => $employee_id,
180 transdate => $ob_date,
181 reference => 'EB ' . $ob_date->year,
182 description => 'Automatische EB-Buchungen Bestandskonten Haben für ' . $ob_date->year,
186 my $asset_cb_credit_entry = SL::DB::GLTransaction->new(
187 employee_id => $employee_id,
188 transdate => $cb_date,
189 reference => 'SB ' . $cb_date->year,
190 description => 'Automatische SB-Buchungen Bestandskonten Haben für ' . $cb_date->year,
194 my $asset_ob_credit_entry = SL::DB::GLTransaction->new(
195 employee_id => $employee_id,
196 transdate => $ob_date,
197 reference => 'EB ' . $ob_date->year,
198 description => 'Automatische EB-Buchungen Bestandskonten Soll für ' . $ob_date->year,
202 $asset_cb_debit_entry->transactions([]);
203 $asset_ob_debit_entry->transactions([]);
204 $asset_cb_credit_entry->transactions([]);
205 $asset_ob_credit_entry->transactions([]);
207 foreach my $asset_account ( @asset_accounts ) {
208 next if $asset_account->{amount_with_cb} == 0;
210 # create cb and ob acc_trans entry here, but decide which gl entry to add it to later
211 my $asset_cb_acc = SL::DB::AccTransaction->new(
212 transdate => $cb_date,
215 chart_id => $asset_account->{chart_id},
216 chart_link => $asset_account->{chart_link},
219 amount => - $asset_account->{amount_with_cb},
221 my $asset_ob_acc = SL::DB::AccTransaction->new(
222 transdate => $ob_date,
225 chart_id => $asset_account->{chart_id},
226 chart_link => $asset_account->{chart_link},
229 amount => $asset_account->{amount_with_cb},
232 if ( $asset_account->{amount_with_cb} < 0 ) {
233 $debit_balance += $asset_account->{amount_with_cb};
234 # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to debit", $asset_account->{accno}, $asset_account->{amount_with_cb}));
236 $asset_cb_debit_entry->add_transactions($asset_cb_acc);
237 $asset_ob_debit_entry->add_transactions($asset_ob_acc);
239 # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to credit", $asset_account->{accno}, $asset_account->{amount_with_cb}));
240 $credit_balance += $asset_account->{amount_with_cb};
241 $asset_cb_credit_entry->add_transactions($asset_cb_acc);
242 $asset_ob_credit_entry->add_transactions($asset_ob_acc);
246 my $debit_cb_acc = SL::DB::AccTransaction->new(
247 transdate => $cb_date,
250 chart_id => $carry_over_chart->id,
251 chart_link => $carry_over_chart->link, # maybe leave chart_link empty?
254 amount => $debit_balance,
256 my $debit_ob_acc = SL::DB::AccTransaction->new(
257 transdate => $ob_date,
260 chart_id => $carry_over_chart->id,
261 chart_link => $carry_over_chart->link,
264 amount => - $debit_balance,
266 my $credit_cb_acc = SL::DB::AccTransaction->new(
267 transdate => $cb_date,
270 chart_id => $carry_over_chart->id,
271 chart_link => $carry_over_chart->link, # maybe leave chart_link empty?
274 amount => $credit_balance,
276 my $credit_ob_acc = SL::DB::AccTransaction->new(
277 transdate => $ob_date,
280 chart_id => $carry_over_chart->id,
281 chart_link => $carry_over_chart->link,
284 amount => - $credit_balance,
286 $asset_cb_debit_entry->add_transactions($debit_cb_acc);
287 $asset_ob_debit_entry->add_transactions($debit_ob_acc);
288 $asset_cb_credit_entry->add_transactions($credit_cb_acc);
289 $asset_ob_credit_entry->add_transactions($credit_ob_acc);
291 $asset_cb_debit_entry->save if scalar @{ $asset_cb_debit_entry->transactions } > 1;
292 $asset_ob_debit_entry->save if scalar @{ $asset_ob_debit_entry->transactions } > 1;
293 $asset_cb_credit_entry->save if scalar @{ $asset_cb_credit_entry->transactions } > 1;
294 $asset_ob_credit_entry->save if scalar @{ $asset_ob_credit_entry->transactions } > 1;
296 ####### profit-loss accounts #######
297 # these only have a closing balance, the balance is transferred to the profit-loss account
299 # need to know if profit or loss first!
300 # use amount_with_cb, so it can be run several times. So sum may be 0 the second time.
301 my $profit_loss_sum = sum map { $_->{amount_with_cb} }
302 grep { $_->{account_type} eq 'profit_loss_account' }
305 if ( $profit_loss_sum > 0 ) {
306 $pl_chart = $profit_chart;
308 $pl_chart = $loss_chart;
311 my $pl_debit_balance = 0;
312 my $pl_credit_balance = 0;
313 # soll = debit, haben = credit
314 my $pl_cb_debit_entry = SL::DB::GLTransaction->new(
315 employee_id => $employee_id,
316 transdate => $cb_date,
317 reference => 'SB ' . $cb_date->year,
318 description => 'Automatische SB-Buchungen Erfolgskonten Soll für ' . $cb_date->year,
322 my $pl_cb_credit_entry = SL::DB::GLTransaction->new(
323 employee_id => $employee_id,
324 transdate => $cb_date,
325 reference => 'SB ' . $cb_date->year,
326 description => 'Automatische SB-Buchungen Erfolgskonten Haben für ' . $cb_date->year,
330 $pl_cb_debit_entry->transactions([]);
331 $pl_cb_credit_entry->transactions([]);
333 foreach my $profit_loss_account ( @profit_loss_accounts ) {
334 # $main::lxdebug->message(0, sprintf("found chart %s with balance %s", $profit_loss_account->{accno}, $profit_loss_account->{amount_with_cb}));
336 next if $profit_loss_account->{amount_with_cb} == 0;
338 my $debit_cb_acc = SL::DB::AccTransaction->new(
339 transdate => $cb_date,
342 chart_id => $profit_loss_account->{chart_id},
343 chart_link => $profit_loss_account->{chart_link},
346 amount => - $profit_loss_account->{amount_with_cb},
348 my $credit_cb_acc = SL::DB::AccTransaction->new(
349 transdate => $cb_date,
352 chart_id => $profit_loss_account->{chart_id},
353 chart_link => $profit_loss_account->{chart_link},
356 amount => $profit_loss_account->{amount_with_cb},
358 if ( { $profit_loss_account->{amount_with_cb} < 0 } ) {
359 $pl_debit_balance += $profit_loss_account->{amount_with_cb};
360 $pl_cb_debit_entry->add_transactions($debit_cb_acc);
362 $pl_credit_balance += $profit_loss_account->{amount_with_cb};
363 $pl_cb_credit_entry->add_transactions($credit_cb_acc);
367 my $debit_cb_acc = SL::DB::AccTransaction->new(
368 transdate => $cb_date,
371 chart_id => $pl_chart->id,
372 chart_link => $pl_chart->link,
375 amount => $pl_debit_balance,
377 my $credit_cb_acc = SL::DB::AccTransaction->new(
378 transdate => $cb_date,
381 chart_id => $pl_chart->id,
382 chart_link => $pl_chart->link,
385 amount => - $pl_credit_balance,
387 $pl_cb_debit_entry->add_transactions($debit_cb_acc);
388 $pl_cb_credit_entry->add_transactions($credit_cb_acc);
390 $pl_cb_debit_entry->save if scalar @{ $pl_cb_debit_entry->transactions } > 1;
391 $pl_cb_credit_entry->save if scalar @{ $pl_cb_credit_entry->transactions } > 1;
393 ######### profit-loss transfer #########
394 # and finally transfer the new balance of the profit-loss account via the carry-over account
395 # we want to use profit_loss_sum with cb!
397 my $carry_over_cb_entry = SL::DB::GLTransaction->new(
398 employee_id => $employee_id,
399 transdate => $cb_date,
400 reference => 'SB ' . $cb_date->year,
401 description => sprintf('Automatische SB-Buchung für %s %s',
402 $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag',
408 my $carry_over_ob_entry = SL::DB::GLTransaction->new(
409 employee_id => $employee_id,
410 transdate => $ob_date,
411 reference => 'EB ' . $ob_date->year,
412 description => sprintf('Automatische EB-Buchung für %s %s',
413 $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag',
419 $carry_over_cb_entry->transactions([]);
420 $carry_over_ob_entry->transactions([]);
422 my $carry_over_cb_acc_co = SL::DB::AccTransaction->new(
423 transdate => $cb_date,
426 chart_id => $carry_over_chart->id,
427 chart_link => $carry_over_chart->link,
430 amount => $profit_loss_sum,
432 my $carry_over_cb_acc_pl = SL::DB::AccTransaction->new(
433 transdate => $cb_date,
436 chart_id => $pl_chart->id,
437 chart_link => $pl_chart->link,
440 amount => - $profit_loss_sum,
443 $carry_over_cb_entry->add_transactions($carry_over_cb_acc_co);
444 $carry_over_cb_entry->add_transactions($carry_over_cb_acc_pl);
445 $carry_over_cb_entry->save if $profit_loss_sum != 0;
447 my $carry_over_ob_acc_co = SL::DB::AccTransaction->new(
448 transdate => $ob_date,
451 chart_id => $pl_chart->id,
452 chart_link => $pl_chart->link,
455 amount => $profit_loss_sum,
457 my $carry_over_ob_acc_pl = SL::DB::AccTransaction->new(
458 transdate => $ob_date,
461 chart_id => $carry_over_chart->id,
462 chart_link => $carry_over_chart->link,
465 amount => - $profit_loss_sum,
468 $carry_over_ob_entry->add_transactions($carry_over_ob_acc_co);
469 $carry_over_ob_entry->add_transactions($carry_over_ob_acc_pl);
470 $carry_over_ob_entry->save if $profit_loss_sum != 0;
472 my $consistency_query = <<SQL;
475 where (ob_transaction is true or cb_transaction is true)
476 and (transdate = ? or transdate = ?)
478 my ($sum) = my ($empty) = selectrow_query($::form, $db->dbh, $consistency_query,
482 die "acc_trans transactions don't add up to zero" unless $sum == 0;
485 }) or die $db->error;
491 my $start_date = delete $params{start_date};
492 my $cb_date = delete $params{cb_date};
494 my $defaults = SL::DB::Default->get;
495 die "no carry over account defined"
496 unless defined $defaults->carry_over_account_chart_id
497 and $defaults->carry_over_account_chart_id > 0;
499 my $salden_query = <<SQL;
500 select c.id as chart_id,
503 c.link as chart_link,
505 sum(a.amount) filter (where cb_transaction is false and ob_transaction is false) as amount,
506 sum(a.amount) filter (where ob_transaction is true ) as ob_amount,
507 sum(a.amount) filter (where cb_transaction is false ) as amount_without_cb,
508 sum(a.amount) filter (where cb_transaction is true ) as cb_amount,
509 sum(a.amount) as amount_with_cb,
510 case when c.category = ANY( '{I,E}' ) then 'profit_loss_account'
511 when c.category = ANY( '{A,C,L,Q}' ) then 'asset_account'
515 inner join chart c on (c.id = a.chart_id)
516 where a.transdate >= ?
519 group by c.id, c.accno, c.category
520 order by account_type, c.accno
523 my $dbh = SL::DB->client->dbh;
524 my $report = selectall_hashref_query($::form, $dbh, $salden_query,
527 $defaults->carry_over_account_chart_id,
529 # profit_loss_sum is the actual profit/loss for the year, without cb, use "amount_without_cb")
530 my $profit_loss_sum = sum map { $_->{amount_without_cb} }
531 grep { $_->{account_type} eq 'profit_loss_account' }
534 return ($report, $profit_loss_sum);
542 $::auth->assert('general_ledger');
550 sub init_ob_date { $::locale->parse_date_to_object($::form->{ob_date}) }
551 sub init_cb_startdate { $::locale->parse_date_to_object($::form->{cb_startdate}) }
552 sub init_cb_date { $::locale->parse_date_to_object($::form->{cb_date}) }