SL/Controller/YearEndTransactions.pm

   1 package SL::Controller::YearEndTransactions;
   2
   3 use strict;
   4
   5 use parent qw(SL::Controller::Base);
   6
   7 use utf8; # Umlauts in hardcoded German default texts
   8 use DateTime;
   9 use SL::Locale::String qw(t8);
  10 use SL::Helper::Flash;
  11 use SL::DBUtils;
  12 use Data::Dumper;
  13 use List::Util qw(sum);
  14 use SL::ClientJS;
  15
  16 use SL::DB::Chart;
  17 use SL::DB::GLTransaction;
  18 use SL::DB::AccTransaction;
  19 use SL::DB::Employee;
  20 use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date get_balance_startdate_method_options);
  21
  22 use Rose::Object::MakeMethods::Generic (
  23   'scalar --get_set_init' => [ qw(cb_date cb_startdate ob_date) ],
  24 );
  25
  26 __PACKAGE__->run_before('check_auth');
  27
  28 sub action_form {
  29   my ($self) = @_;
  30
  31   $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));
  32
  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   );
  37
  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(),
  44                );
  45 };
  46
  47 sub action_year_end_bookings {
  48   my ($self) = @_;
  49
  50   $self->_parse_form;
  51
  52
  53   eval {
  54     _year_end_bookings( start_date => $self->cb_startdate,
  55                         cb_date    => $self->cb_date,
  56                       );
  57     1;
  58   } or do {
  59     $self->js->flash('error', t8('Error while applying year-end bookings!') . ' ' . $@);
  60     return $self->js->render;
  61   };
  62
  63   my ($report_data, $profit_loss_sum) = _report(
  64                                                 cb_date    => $self->cb_date,
  65                                                 start_date => $self->cb_startdate,
  66                                                );
  67
  68   my $html = $self->render('yearend/_charts', { layout  => 0 , process => 1, output => 0 },
  69                  charts          => $report_data,
  70                  profit_loss_sum => $profit_loss_sum,
  71                );
  72   return $self->js->flash('info', t8('Year-end bookings were successfully completed!'))
  73                ->html('#charts', $html)
  74                ->render;
  75 }
  76
  77 sub action_get_start_date {
  78   my ($self) = @_;
  79
  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'))
  84                 ->render;
  85   }
  86
  87   $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date, $::form->{'balance_startdate_method'})));
  88
  89   # $main::lxdebug->message(0, "found start date: ", $self->cb_startdate->to_kivitendo);
  90
  91   return $self->js->val('#cb_startdate', $self->cb_startdate->to_kivitendo)
  92               ->show('#apply_year_end_bookings_button')
  93               ->show('.startdate')
  94               ->render;
  95 }
  96
  97 sub action_update_charts {
  98   my ($self) = @_;
  99
 100   $self->_parse_form;
 101
 102   my ($report_data, $profit_loss_sum) = _report(
 103                                                 cb_date   => $self->cb_date,
 104                                                 start_date => $self->cb_startdate,
 105                                                );
 106
 107   $self->render('yearend/_charts', { layout  => 0 , process => 1 },
 108                  charts          => $report_data,
 109                  profit_loss_sum => $profit_loss_sum,
 110                );
 111 }
 112
 113 #
 114 # helpers
 115 #
 116
 117 sub _parse_form {
 118   my ($self) = @_;
 119
 120   # parse dates
 121   $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));
 122
 123   die "cb_date must come after start_date" unless $self->cb_date > $self->cb_startdate;
 124
 125 }
 126
 127 sub _year_end_bookings {
 128   my (%params) = @_;
 129
 130   my $start_date = delete $params{start_date};
 131   my $cb_date    = delete $params{cb_date};
 132
 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!');
 137
 138   my ($report_data, $profit_loss_sum) = _report(
 139                                                 start_date => $start_date,
 140                                                 cb_date    => $cb_date,
 141                                                );
 142
 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 };
 145
 146   my $ob_date = $cb_date->clone->add(days => 1);
 147
 148   my ($credit_sum, $debit_sum) = (0,0);
 149
 150   my $employee_id = SL::DB::Manager::Employee->current->id;
 151
 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
 154   # transactions:
 155
 156   # * cb for credit
 157   # * cb for debit
 158   # * ob for credit
 159   # * ob for debit
 160
 161   my $db = SL::DB->client;
 162   $db->with_transaction(sub {
 163
 164     ######### asset accounts ########
 165     # need cb and ob transactions
 166
 167     my $debit_balance  = 0;
 168     my $credit_balance = 0;
 169
 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,
 175       ob_transaction => 0,
 176       cb_transaction => 1,
 177     );
 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,
 183       ob_transaction => 1,
 184       cb_transaction => 0,
 185     );
 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,
 191       ob_transaction => 0,
 192       cb_transaction => 1,
 193     );
 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,
 199       ob_transaction => 1,
 200       cb_transaction => 0,
 201     );
 202     $asset_cb_debit_entry->transactions([]);
 203     $asset_ob_debit_entry->transactions([]);
 204     $asset_cb_credit_entry->transactions([]);
 205     $asset_ob_credit_entry->transactions([]);
 206
 207     foreach my $asset_account ( @asset_accounts ) {
 208       next if $asset_account->{amount_with_cb} == 0;
 209
 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,
 213         ob_transaction => 0,
 214         cb_transaction => 1,
 215         chart_id       => $asset_account->{chart_id},
 216         chart_link     => $asset_account->{chart_link},
 217         tax_id         => 0,
 218         taxkey         => 0,
 219         amount         => - $asset_account->{amount_with_cb},
 220       );
 221       my $asset_ob_acc = SL::DB::AccTransaction->new(
 222         transdate      => $ob_date,
 223         ob_transaction => 1,
 224         cb_transaction => 0,
 225         chart_id       => $asset_account->{chart_id},
 226         chart_link     => $asset_account->{chart_link},
 227         tax_id         => 0,
 228         taxkey         => 0,
 229         amount         => $asset_account->{amount_with_cb},
 230       );
 231
 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}));
 235
 236         $asset_cb_debit_entry->add_transactions($asset_cb_acc);
 237         $asset_ob_debit_entry->add_transactions($asset_ob_acc);
 238       } else {
 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);
 243       };
 244     };
 245
 246     my $debit_cb_acc = SL::DB::AccTransaction->new(
 247       transdate      => $cb_date,
 248       ob_transaction => 0,
 249       cb_transaction => 1,
 250       chart_id       => $carry_over_chart->id,
 251       chart_link     => $carry_over_chart->link, # maybe leave chart_link empty?
 252       tax_id         => 0,
 253       taxkey         => 0,
 254       amount         => $debit_balance,
 255     );
 256     my $debit_ob_acc = SL::DB::AccTransaction->new(
 257       transdate      => $ob_date,
 258       ob_transaction => 1,
 259       cb_transaction => 0,
 260       chart_id       => $carry_over_chart->id,
 261       chart_link     => $carry_over_chart->link,
 262       tax_id         => 0,
 263       taxkey         => 0,
 264       amount         => - $debit_balance,
 265     );
 266     my $credit_cb_acc = SL::DB::AccTransaction->new(
 267       transdate      => $cb_date,
 268       ob_transaction => 0,
 269       cb_transaction => 1,
 270       chart_id       => $carry_over_chart->id,
 271       chart_link     => $carry_over_chart->link, # maybe leave chart_link empty?
 272       tax_id         => 0,
 273       taxkey         => 0,
 274       amount         => $credit_balance,
 275     );
 276     my $credit_ob_acc = SL::DB::AccTransaction->new(
 277       transdate      => $ob_date,
 278       ob_transaction => 1,
 279       cb_transaction => 0,
 280       chart_id       => $carry_over_chart->id,
 281       chart_link     => $carry_over_chart->link,
 282       tax_id         => 0,
 283       taxkey         => 0,
 284       amount         => - $credit_balance,
 285     );
 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);
 290
 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;
 295
 296     #######  profit-loss accounts #######
 297     # these only have a closing balance, the balance is transferred to the profit-loss account
 298
 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' }
 303                               @{$report_data};
 304     my $pl_chart;
 305     if ( $profit_loss_sum > 0 ) {
 306       $pl_chart = $profit_chart;
 307     } else {
 308       $pl_chart = $loss_chart;
 309     };
 310
 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,
 319       ob_transaction => 0,
 320       cb_transaction => 1,
 321     );
 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,
 327       ob_transaction => 0,
 328       cb_transaction => 1,
 329     );
 330     $pl_cb_debit_entry->transactions([]);
 331     $pl_cb_credit_entry->transactions([]);
 332
 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}));
 335
 336       next if $profit_loss_account->{amount_with_cb} == 0;
 337
 338       my $debit_cb_acc = SL::DB::AccTransaction->new(
 339         transdate      => $cb_date,
 340         ob_transaction => 0,
 341         cb_transaction => 1,
 342         chart_id       => $profit_loss_account->{chart_id},
 343         chart_link     => $profit_loss_account->{chart_link},
 344         tax_id         => 0,
 345         taxkey         => 0,
 346         amount         => - $profit_loss_account->{amount_with_cb},
 347       );
 348       my $credit_cb_acc = SL::DB::AccTransaction->new(
 349         transdate      => $cb_date,
 350         ob_transaction => 0,
 351         cb_transaction => 1,
 352         chart_id       => $profit_loss_account->{chart_id},
 353         chart_link     => $profit_loss_account->{chart_link},
 354         tax_id         => 0,
 355         taxkey         => 0,
 356         amount         => $profit_loss_account->{amount_with_cb},
 357       );
 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);
 361       } else {
 362         $pl_credit_balance += $profit_loss_account->{amount_with_cb};
 363          $pl_cb_credit_entry->add_transactions($credit_cb_acc);
 364       };
 365     };
 366
 367     my $debit_cb_acc = SL::DB::AccTransaction->new(
 368       transdate      => $cb_date,
 369       ob_transaction => 0,
 370       cb_transaction => 1,
 371       chart_id       => $pl_chart->id,
 372       chart_link     => $pl_chart->link,
 373       tax_id         => 0,
 374       taxkey         => 0,
 375       amount         => $pl_debit_balance,
 376     );
 377     my $credit_cb_acc = SL::DB::AccTransaction->new(
 378       transdate      => $cb_date,
 379       ob_transaction => 0,
 380       cb_transaction => 1,
 381       chart_id       => $pl_chart->id,
 382       chart_link     => $pl_chart->link,
 383       tax_id         => 0,
 384       taxkey         => 0,
 385       amount         => - $pl_credit_balance,
 386     );
 387     $pl_cb_debit_entry->add_transactions($debit_cb_acc);
 388     $pl_cb_credit_entry->add_transactions($credit_cb_acc);
 389
 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;
 392
 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!
 396
 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',
 403                                 $cb_date->year,
 404                                ),
 405       ob_transaction => 0,
 406       cb_transaction => 1,
 407     );
 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',
 414                                 $ob_date->year,
 415                                ),
 416       ob_transaction => 1,
 417       cb_transaction => 0,
 418     );
 419     $carry_over_cb_entry->transactions([]);
 420     $carry_over_ob_entry->transactions([]);
 421
 422     my $carry_over_cb_acc_co = SL::DB::AccTransaction->new(
 423       transdate      => $cb_date,
 424       ob_transaction => 0,
 425       cb_transaction => 1,
 426       chart_id       => $carry_over_chart->id,
 427       chart_link     => $carry_over_chart->link,
 428       tax_id         => 0,
 429       taxkey         => 0,
 430       amount         => $profit_loss_sum,
 431     );
 432     my $carry_over_cb_acc_pl = SL::DB::AccTransaction->new(
 433       transdate      => $cb_date,
 434       ob_transaction => 0,
 435       cb_transaction => 1,
 436       chart_id       => $pl_chart->id,
 437       chart_link     => $pl_chart->link,
 438       tax_id         => 0,
 439       taxkey         => 0,
 440       amount         => - $profit_loss_sum,
 441     );
 442
 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;
 446
 447     my $carry_over_ob_acc_co = SL::DB::AccTransaction->new(
 448       transdate      => $ob_date,
 449       ob_transaction => 1,
 450       cb_transaction => 0,
 451       chart_id       => $pl_chart->id,
 452       chart_link     => $pl_chart->link,
 453       tax_id         => 0,
 454       taxkey         => 0,
 455       amount         => $profit_loss_sum,
 456     );
 457     my $carry_over_ob_acc_pl = SL::DB::AccTransaction->new(
 458       transdate      => $ob_date,
 459       ob_transaction => 1,
 460       cb_transaction => 0,
 461       chart_id       => $carry_over_chart->id,
 462       chart_link     => $carry_over_chart->link,
 463       tax_id         => 0,
 464       taxkey         => 0,
 465       amount         => - $profit_loss_sum,
 466     );
 467
 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;
 471
 472     my $consistency_query = <<SQL;
 473 select sum(amount)
 474   from acc_trans
 475  where     (ob_transaction is true or cb_transaction is true)
 476        and (transdate = ? or transdate = ?)
 477 SQL
 478      my ($sum) = my ($empty) = selectrow_query($::form, $db->dbh, $consistency_query,
 479                                                $cb_date,
 480                                                $ob_date
 481                                               );
 482      die "acc_trans transactions don't add up to zero" unless $sum == 0;
 483
 484     1;
 485   }) or die $db->error;
 486 }
 487
 488 sub _report {
 489   my (%params) = @_;
 490
 491   my $start_date = delete $params{start_date};
 492   my $cb_date    = delete $params{cb_date};
 493
 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;
 498
 499   my $salden_query = <<SQL;
 500 select c.id as chart_id,
 501        c.accno,
 502        c.description,
 503        c.link as chart_link,
 504        c.category,
 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'
 512                                                  else null
 513             end                                                                         as account_type
 514   from acc_trans a
 515        inner join chart c on (c.id = a.chart_id)
 516  where     a.transdate >= ?
 517        and a.transdate <= ?
 518        and a.chart_id != ?
 519  group by c.id, c.accno, c.category
 520  order by account_type, c.accno
 521 SQL
 522
 523   my $dbh = SL::DB->client->dbh;
 524   my $report = selectall_hashref_query($::form, $dbh, $salden_query,
 525                                        $start_date,
 526                                        $cb_date,
 527                                        $defaults->carry_over_account_chart_id,
 528                                       );
 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' }
 532                             @{$report};
 533
 534   return ($report, $profit_loss_sum);
 535 }
 536
 537 #
 538 # auth
 539 #
 540
 541 sub check_auth {
 542   $::auth->assert('general_ledger');
 543 }
 544
 545
 546 #
 547 # inits
 548 #
 549
 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})      }
 553
 554 1;