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   eval {
  53     _year_end_bookings( start_date => $self->cb_startdate,
  54                         cb_date    => $self->cb_date,
  55                       );
  56     1;
  57   } or do {
  58     $self->js->flash('error', t8('Error while applying year-end bookings!') . ' ' . $@);
  59     return $self->js->render;
  60   };
  61
  62   my ($report_data, $profit_loss_sum) = _report(
  63                                                 cb_date    => $self->cb_date,
  64                                                 start_date => $self->cb_startdate,
  65                                                );
  66
  67   my $html = $self->render('yearend/_charts', { layout  => 0 , process => 1, output => 0 },
  68                            charts          => $report_data,
  69                            profit_loss_sum => $profit_loss_sum,
  70                           );
  71   return $self->js->flash('info', t8('Year-end bookings were successfully completed!'))
  72                ->html('#charts', $html)
  73                ->render;
  74 }
  75
  76 sub action_get_start_date {
  77   my ($self) = @_;
  78
  79   my $cb_date = $self->cb_date; # parse from form via init
  80   unless ( $self->cb_date ) {
  81     return $self->hide('#apply_year_end_bookings_button')
  82                 ->flash('error', t8('Year-end date missing'))
  83                 ->render;
  84   }
  85
  86   $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date, $::form->{'balance_startdate_method'})));
  87
  88   # $main::lxdebug->message(0, "found start date: ", $self->cb_startdate->to_kivitendo);
  89
  90   return $self->js->val('#cb_startdate', $self->cb_startdate->to_kivitendo)
  91               ->show('#apply_year_end_bookings_button')
  92               ->show('.startdate')
  93               ->render;
  94 }
  95
  96 sub action_update_charts {
  97   my ($self) = @_;
  98
  99   $self->_parse_form;
 100
 101   my ($report_data, $profit_loss_sum) = _report(
 102                                                 cb_date   => $self->cb_date,
 103                                                 start_date => $self->cb_startdate,
 104                                                );
 105
 106   $self->render('yearend/_charts', { layout  => 0 , process => 1 },
 107                 charts          => $report_data,
 108                 profit_loss_sum => $profit_loss_sum,
 109                );
 110 }
 111
 112 #
 113 # helpers
 114 #
 115
 116 sub _parse_form {
 117   my ($self) = @_;
 118
 119   # parse dates
 120   $self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));
 121
 122   die "cb_date must come after start_date" unless $self->cb_date > $self->cb_startdate;
 123 }
 124
 125 sub _year_end_bookings {
 126   my (%params) = @_;
 127
 128   my $start_date = delete $params{start_date};
 129   my $cb_date    = delete $params{cb_date};
 130
 131   my $defaults         = SL::DB::Default->get;
 132   my $carry_over_chart = SL::DB::Manager::Chart->find_by( id => $defaults->carry_over_account_chart_id     ) // die t8('No carry-over chart configured!');
 133   my $profit_chart     = SL::DB::Manager::Chart->find_by( id => $defaults->profit_carried_forward_chart_id ) // die t8('No profit carried forward chart configured!');
 134   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!');
 135
 136   my ($report_data, $profit_loss_sum) = _report(
 137                                                 start_date => $start_date,
 138                                                 cb_date    => $cb_date,
 139                                                );
 140
 141   # load all charts from report as objects and store them in a hash
 142   my @report_chart_ids = map { $_->{chart_id} } @{ $report_data };
 143   my %charts_by_id = map { ( $_->id => $_ ) } @{ SL::DB::Manager::Chart->get_all(where => [ id => \@report_chart_ids ]) };
 144
 145   my @asset_accounts       = grep { $_->{account_type} eq 'asset_account' }       @{ $report_data };
 146   my @profit_loss_accounts = grep { $_->{account_type} eq 'profit_loss_account' } @{ $report_data };
 147
 148   my $ob_date = $cb_date->clone->add(days => 1);
 149
 150   my ($credit_sum, $debit_sum) = (0,0);
 151
 152   my $employee_id = SL::DB::Manager::Employee->current->id;
 153
 154   # rather than having one gl transaction for each asset account, we group all
 155   # the debit sums and credit sums for cb and ob bookings, so we will have 4 gl
 156   # transactions:
 157
 158   # * cb for credit
 159   # * cb for debit
 160   # * ob for credit
 161   # * ob for debit
 162
 163   my $db = SL::DB->client;
 164   $db->with_transaction(sub {
 165
 166     ######### asset accounts ########
 167     # need cb and ob transactions
 168
 169     my $debit_balance  = 0;
 170     my $credit_balance = 0;
 171
 172     my $asset_cb_debit_entry = SL::DB::GLTransaction->new(
 173       employee_id    => $employee_id,
 174       transdate      => $cb_date,
 175       reference      => 'SB ' . $cb_date->year,
 176       description    => 'Automatische SB-Buchungen Bestandskonten Soll für ' . $cb_date->year,
 177       ob_transaction => 0,
 178       cb_transaction => 1,
 179       taxincluded    => 0,
 180       transactions   => [],
 181     );
 182     my $asset_ob_debit_entry = SL::DB::GLTransaction->new(
 183       employee_id    => $employee_id,
 184       transdate      => $ob_date,
 185       reference      => 'EB ' . $ob_date->year,
 186       description    => 'Automatische EB-Buchungen Bestandskonten Haben für ' . $ob_date->year,
 187       ob_transaction => 1,
 188       cb_transaction => 0,
 189       taxincluded    => 0,
 190       transactions   => [],
 191     );
 192     my $asset_cb_credit_entry = SL::DB::GLTransaction->new(
 193       employee_id    => $employee_id,
 194       transdate      => $cb_date,
 195       reference      => 'SB ' . $cb_date->year,
 196       description    => 'Automatische SB-Buchungen Bestandskonten Haben für ' . $cb_date->year,
 197       ob_transaction => 0,
 198       cb_transaction => 1,
 199       taxincluded    => 0,
 200       transactions   => [],
 201     );
 202     my $asset_ob_credit_entry = SL::DB::GLTransaction->new(
 203       employee_id    => $employee_id,
 204       transdate      => $ob_date,
 205       reference      => 'EB ' . $ob_date->year,
 206       description    => 'Automatische EB-Buchungen Bestandskonten Soll für ' . $ob_date->year,
 207       ob_transaction => 1,
 208       cb_transaction => 0,
 209       taxincluded    => 0,
 210       transactions   => [],
 211     );
 212
 213     foreach my $asset_account ( @asset_accounts ) {
 214       next if $asset_account->{amount_with_cb} == 0;
 215       my $ass_acc = $charts_by_id{ $asset_account->{chart_id} };
 216
 217       if ( $asset_account->{amount_with_cb} < 0 ) {
 218         # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to debit", $asset_account->{accno}, $asset_account->{amount_with_cb}));
 219         $debit_balance += $asset_account->{amount_with_cb};
 220
 221         $asset_cb_debit_entry->add_chart_booking(
 222           chart  => $ass_acc,
 223           credit => - $asset_account->{amount_with_cb},
 224           tax_id => 0
 225         );
 226         $asset_ob_debit_entry->add_chart_booking(
 227           chart  => $ass_acc,
 228           debit  => - $asset_account->{amount_with_cb},
 229           tax_id => 0
 230         );
 231
 232       } else {
 233         # $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to credit", $asset_account->{accno}, $asset_account->{amount_with_cb}));
 234         $credit_balance += $asset_account->{amount_with_cb};
 235
 236         $asset_cb_credit_entry->add_chart_booking(
 237           chart  => $ass_acc,
 238           debit  => $asset_account->{amount_with_cb},
 239           tax_id => 0
 240         );
 241         $asset_ob_credit_entry->add_chart_booking(
 242           chart  => $ass_acc,
 243           credit  => $asset_account->{amount_with_cb},
 244           tax_id => 0
 245         );
 246       };
 247     };
 248
 249     if ( $debit_balance ) {
 250       $asset_cb_debit_entry->add_chart_booking(
 251         chart  => $carry_over_chart,
 252         debit  => -1 * $debit_balance,
 253         tax_id => 0,
 254       );
 255
 256       $asset_ob_debit_entry->add_chart_booking(
 257         chart  => $carry_over_chart,
 258         credit => -1 * $debit_balance,
 259         tax_id => 0,
 260       );
 261     };
 262
 263     if ( $credit_balance ) {
 264       $asset_cb_credit_entry->add_chart_booking(
 265         chart  => $carry_over_chart,
 266         credit => $credit_balance,
 267         tax_id => 0,
 268       );
 269       $asset_ob_credit_entry->add_chart_booking(
 270         chart  => $carry_over_chart,
 271         debit  => $credit_balance,
 272         tax_id => 0,
 273       );
 274     };
 275
 276     $asset_cb_debit_entry->post  if scalar @{ $asset_cb_debit_entry->transactions  } > 1;
 277     $asset_ob_debit_entry->post  if scalar @{ $asset_ob_debit_entry->transactions  } > 1;
 278     $asset_cb_credit_entry->post if scalar @{ $asset_cb_credit_entry->transactions } > 1;
 279     $asset_ob_credit_entry->post if scalar @{ $asset_ob_credit_entry->transactions } > 1;
 280
 281     #######  profit-loss accounts #######
 282     # these only have a closing balance, the balance is transferred to the profit-loss account
 283
 284     # need to know if profit or loss first!
 285     # use amount_with_cb, so it can be run several times. So sum may be 0 the second time.
 286     my $profit_loss_sum = sum map { $_->{amount_with_cb} }
 287                               grep { $_->{account_type} eq 'profit_loss_account' }
 288                               @{$report_data};
 289     $profit_loss_sum ||= 0;
 290     my $pl_chart;
 291     if ( $profit_loss_sum > 0 ) {
 292       $pl_chart = $profit_chart;
 293     } else {
 294       $pl_chart = $loss_chart;
 295     };
 296
 297     my $pl_debit_balance  = 0;
 298     my $pl_credit_balance = 0;
 299     # soll = debit, haben = credit
 300     my $pl_cb_debit_entry = SL::DB::GLTransaction->new(
 301       employee_id    => $employee_id,
 302       transdate      => $cb_date,
 303       reference      => 'SB ' . $cb_date->year,
 304       description    => 'Automatische SB-Buchungen Erfolgskonten Soll für ' . $cb_date->year,
 305       ob_transaction => 0,
 306       cb_transaction => 1,
 307       taxincluded    => 0,
 308       transactions   => [],
 309     );
 310     my $pl_cb_credit_entry = SL::DB::GLTransaction->new(
 311       employee_id    => $employee_id,
 312       transdate      => $cb_date,
 313       reference      => 'SB ' . $cb_date->year,
 314       description    => 'Automatische SB-Buchungen Erfolgskonten Haben für ' . $cb_date->year,
 315       ob_transaction => 0,
 316       cb_transaction => 1,
 317       taxincluded    => 0,
 318       transactions   => [],
 319     );
 320
 321     foreach my $profit_loss_account ( @profit_loss_accounts ) {
 322       # $main::lxdebug->message(0, sprintf("found chart %s with balance %s", $profit_loss_account->{accno}, $profit_loss_account->{amount_with_cb}));
 323       my $chart = $charts_by_id{ $profit_loss_account->{chart_id} };
 324
 325       next if $profit_loss_account->{amount_with_cb} == 0;
 326
 327       if ( $profit_loss_account->{amount_with_cb} < 0 ) {
 328         $pl_debit_balance -= $profit_loss_account->{amount_with_cb};
 329         $pl_cb_debit_entry->add_chart_booking(
 330           chart  => $chart,
 331           tax_id => 0,
 332           credit => - $profit_loss_account->{amount_with_cb},
 333         );
 334       } else {
 335         $pl_credit_balance += $profit_loss_account->{amount_with_cb};
 336         $pl_cb_credit_entry->add_chart_booking(
 337           chart  => $chart,
 338           tax_id => 0,
 339           debit  => $profit_loss_account->{amount_with_cb},
 340         );
 341       };
 342     };
 343
 344     # $main::lxdebug->message(0, "pl_debit_balance  = $pl_debit_balance");
 345     # $main::lxdebug->message(0, "pl_credit_balance = $pl_credit_balance");
 346
 347     $pl_cb_debit_entry->add_chart_booking(
 348       chart  => $pl_chart,
 349       tax_id => 0,
 350       debit  => $pl_debit_balance,
 351     ) if $pl_debit_balance;
 352
 353     $pl_cb_credit_entry->add_chart_booking(
 354       chart  => $pl_chart,
 355       tax_id => 0,
 356       credit => $pl_credit_balance,
 357     ) if $pl_credit_balance;
 358
 359     # printf("debit : %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $pl_cb_debit_entry->transactions };
 360     # printf("credit: %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $pl_cb_credit_entry->transactions };
 361
 362     $pl_cb_debit_entry->post  if scalar @{ $pl_cb_debit_entry->transactions }  > 1;
 363     $pl_cb_credit_entry->post if scalar @{ $pl_cb_credit_entry->transactions } > 1;
 364
 365     ######### profit-loss transfer #########
 366     # and finally transfer the new balance of the profit-loss account via the carry-over account
 367     # we want to use profit_loss_sum with cb!
 368
 369     if ( $profit_loss_sum != 0 ) {
 370
 371       my $carry_over_cb_entry = SL::DB::GLTransaction->new(
 372         employee_id    => $employee_id,
 373         transdate      => $cb_date,
 374         reference      => 'SB ' . $cb_date->year,
 375         description    => sprintf('Automatische SB-Buchung für %s %s',
 376                                   $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag',
 377                                   $cb_date->year,
 378                                  ),
 379         ob_transaction => 0,
 380         cb_transaction => 1,
 381         taxincluded    => 0,
 382         transactions   => [],
 383       );
 384       my $carry_over_ob_entry = SL::DB::GLTransaction->new(
 385         employee_id    => $employee_id,
 386         transdate      => $ob_date,
 387         reference      => 'EB ' . $ob_date->year,
 388         description    => sprintf('Automatische EB-Buchung für %s %s',
 389                                   $profit_loss_sum >= 0 ? 'Gewinnvortrag' : 'Verlustvortrag',
 390                                   $ob_date->year,
 391                                  ),
 392         ob_transaction => 1,
 393         cb_transaction => 0,
 394         taxincluded    => 0,
 395         transactions   => [],
 396       );
 397
 398       my ($amount1, $amount2);
 399       if ( $profit_loss_sum < 0 ) {
 400         $amount1 = 'debit';
 401         $amount2 = 'credit';
 402       } else {
 403         $amount1 = 'credit';
 404         $amount2 = 'debit';
 405       };
 406
 407       $carry_over_cb_entry->add_chart_booking(
 408         chart    => $carry_over_chart,
 409         tax_id   => 0,
 410         $amount1 => abs($profit_loss_sum),
 411       );
 412       $carry_over_cb_entry->add_chart_booking(
 413         chart    => $pl_chart,
 414         tax_id   => 0,
 415         $amount2 => abs($profit_loss_sum),
 416       );
 417       $carry_over_ob_entry->add_chart_booking(
 418         chart    => $carry_over_chart,
 419         tax_id   => 0,
 420         $amount2 => abs($profit_loss_sum),
 421       );
 422       $carry_over_ob_entry->add_chart_booking(
 423         chart    => $pl_chart,
 424         tax_id   => 0,
 425         $amount1 => abs($profit_loss_sum),
 426       );
 427
 428       # printf("debit : %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $carry_over_ob_entry->transactions };
 429       # printf("credit: %s -> %s\n", $_->chart->displayable_name, $_->amount) foreach @{ $carry_over_ob_entry->transactions };
 430
 431       $carry_over_cb_entry->post if scalar @{ $carry_over_cb_entry->transactions } > 1;
 432       $carry_over_ob_entry->post if scalar @{ $carry_over_ob_entry->transactions } > 1;
 433     };
 434
 435     my $consistency_query = <<SQL;
 436 select sum(amount)
 437   from acc_trans
 438  where     (ob_transaction is true or cb_transaction is true)
 439        and (transdate = ? or transdate = ?)
 440 SQL
 441     my ($sum) = selectrow_query($::form, $db->dbh, $consistency_query,
 442                                 $cb_date,
 443                                 $ob_date
 444                                );
 445      die "acc_trans transactions don't add up to zero" unless $sum == 0;
 446
 447     1;
 448   }) or die $db->error;
 449 }
 450
 451 sub _report {
 452   my (%params) = @_;
 453
 454   my $start_date = delete $params{start_date};
 455   my $cb_date    = delete $params{cb_date};
 456
 457   my $defaults = SL::DB::Default->get;
 458   die "no carry over account defined"
 459     unless defined $defaults->carry_over_account_chart_id
 460            and $defaults->carry_over_account_chart_id > 0;
 461
 462   my $salden_query = <<SQL;
 463 select c.id as chart_id,
 464        c.accno,
 465        c.description,
 466        c.category,
 467        sum(a.amount) filter (where cb_transaction is false and ob_transaction is false) as amount,
 468        sum(a.amount) filter (where ob_transaction is true                             ) as ob_amount,
 469        sum(a.amount) filter (where cb_transaction is false                            ) as amount_without_cb,
 470        sum(a.amount) filter (where cb_transaction is true                             ) as cb_amount,
 471        sum(a.amount)                                                                    as amount_with_cb,
 472        case when c.category = ANY( '{I,E}'     ) then 'profit_loss_account'
 473             when c.category = ANY( '{A,C,L,Q}' ) then 'asset_account'
 474                                                  else null
 475             end                                                                         as account_type
 476   from acc_trans a
 477        inner join chart c on (c.id = a.chart_id)
 478  where     a.transdate >= ?
 479        and a.transdate <= ?
 480        and a.chart_id != ?
 481  group by c.id, c.accno, c.category
 482  order by account_type, c.accno
 483 SQL
 484
 485   my $dbh = SL::DB->client->dbh;
 486   my $report = selectall_hashref_query($::form, $dbh, $salden_query,
 487                                        $start_date,
 488                                        $cb_date,
 489                                        $defaults->carry_over_account_chart_id,
 490                                       );
 491   # profit_loss_sum is the actual profit/loss for the year, without cb, use "amount_without_cb")
 492   my $profit_loss_sum = sum map { $_->{amount_without_cb} }
 493                             grep { $_->{account_type} eq 'profit_loss_account' }
 494                             @{$report};
 495
 496   return ($report, $profit_loss_sum);
 497 }
 498
 499 #
 500 # auth
 501 #
 502
 503 sub check_auth {
 504   $::auth->assert('general_ledger');
 505 }
 506
 507
 508 #
 509 # inits
 510 #
 511
 512 sub init_ob_date        { $::locale->parse_date_to_object($::form->{ob_date})      }
 513 sub init_cb_startdate   { $::locale->parse_date_to_object($::form->{cb_startdate}) }
 514 sub init_cb_date        { $::locale->parse_date_to_object($::form->{cb_date})      }
 515
 516 1;