Projekt

Allgemein

Profil

Herunterladen (19,2 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/Controller/YearEndTransactions.pm @ 17581190

c1c6288a Martin Helmling
package SL::Controller::YearEndTransactions;
95f601bd Martin Helmling
use strict;

use parent qw(SL::Controller::Base);

f2f0fb50 Geoffrey Richardson
use utf8; # Umlauts in hardcoded German default texts
95f601bd Martin Helmling
use DateTime;
use SL::Locale::String qw(t8);
use SL::Helper::Flash;
use SL::DBUtils;
f2f0fb50 Geoffrey Richardson
use Data::Dumper;
use List::Util qw(sum);
use SL::ClientJS;
81860c0c Andreas Rudin
use SL::DB::Default;
95f601bd Martin Helmling
use SL::DB::Chart;
use SL::DB::GLTransaction;
use SL::DB::AccTransaction;
f2f0fb50 Geoffrey Richardson
use SL::DB::Employee;
use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date get_balance_startdate_method_options);
a97574b8 Sven Schöling
95f601bd Martin Helmling
use Rose::Object::MakeMethods::Generic (
81860c0c Andreas Rudin
'scalar --get_set_init' => [ qw(cb_date cb_startdate ob_date defaults) ],
95f601bd Martin Helmling
);

__PACKAGE__->run_before('check_auth');

f2f0fb50 Geoffrey Richardson
sub action_form {
95f601bd Martin Helmling
my ($self) = @_;

f2f0fb50 Geoffrey Richardson
$self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date)));

81860c0c Andreas Rudin
my $carry_over_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->carry_over_account_chart_id );
my $profit_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->profit_carried_forward_chart_id );
my $loss_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->loss_carried_forward_chart_id );
f2f0fb50 Geoffrey Richardson
$self->render('yearend/form',
title => t8('Year-end closing'),
carry_over_chart => $carry_over_chart,
profit_chart => $profit_chart,
loss_chart => $loss_chart,
balance_startdate_method_options => get_balance_startdate_method_options(),
);
};
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
sub action_year_end_bookings {
95f601bd Martin Helmling
my ($self) = @_;

f2f0fb50 Geoffrey Richardson
$self->_parse_form;

eval {
81860c0c Andreas Rudin
$self->_year_end_bookings( start_date => $self->cb_startdate,
f2f0fb50 Geoffrey Richardson
cb_date => $self->cb_date,
);
1;
} or do {
$self->js->flash('error', t8('Error while applying year-end bookings!') . ' ' . $@);
return $self->js->render;
};
95f601bd Martin Helmling
81860c0c Andreas Rudin
my ($report_data, $profit_loss_sum) = $self->_report(
f2f0fb50 Geoffrey Richardson
cb_date => $self->cb_date,
start_date => $self->cb_startdate,
);
cb0414d6 Moritz Bunkus
f2f0fb50 Geoffrey Richardson
my $html = $self->render('yearend/_charts', { layout => 0 , process => 1, output => 0 },
e592e0bc Geoffrey Richardson
charts => $report_data,
profit_loss_sum => $profit_loss_sum,
);
f2f0fb50 Geoffrey Richardson
return $self->js->flash('info', t8('Year-end bookings were successfully completed!'))
->html('#charts', $html)
->render;
95f601bd Martin Helmling
}

f2f0fb50 Geoffrey Richardson
sub action_get_start_date {
95f601bd Martin Helmling
my ($self) = @_;

f2f0fb50 Geoffrey Richardson
my $cb_date = $self->cb_date; # parse from form via init
unless ( $self->cb_date ) {
return $self->hide('#apply_year_end_bookings_button')
->flash('error', t8('Year-end date missing'))
->render;
1c69cfbc Jan Büren
}
f2f0fb50 Geoffrey Richardson
$self->cb_startdate($::locale->parse_date_to_object($self->get_balance_starting_date($self->cb_date, $::form->{'balance_startdate_method'})));

# $main::lxdebug->message(0, "found start date: ", $self->cb_startdate->to_kivitendo);

return $self->js->val('#cb_startdate', $self->cb_startdate->to_kivitendo)
->show('#apply_year_end_bookings_button')
->show('.startdate')
->render;
95f601bd Martin Helmling
}

f2f0fb50 Geoffrey Richardson
sub action_update_charts {
my ($self) = @_;

$self->_parse_form;

81860c0c Andreas Rudin
my ($report_data, $profit_loss_sum) = $self->_report(
f2f0fb50 Geoffrey Richardson
cb_date => $self->cb_date,
start_date => $self->cb_startdate,
);

$self->render('yearend/_charts', { layout => 0 , process => 1 },
e592e0bc Geoffrey Richardson
charts => $report_data,
profit_loss_sum => $profit_loss_sum,
f2f0fb50 Geoffrey Richardson
);
95f601bd Martin Helmling
}

#
# helpers
#

f2f0fb50 Geoffrey Richardson
sub _parse_form {
95f601bd Martin Helmling
my ($self) = @_;
f2f0fb50 Geoffrey Richardson
# parse dates
$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;
95f601bd Martin Helmling
}

f2f0fb50 Geoffrey Richardson
sub _year_end_bookings {
81860c0c Andreas Rudin
my ($self, %params) = @_;
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $start_date = delete $params{start_date};
my $cb_date = delete $params{cb_date};
95f601bd Martin Helmling
81860c0c Andreas Rudin
my $carry_over_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->carry_over_account_chart_id ) // die t8('No carry-over chart configured!');
my $profit_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->profit_carried_forward_chart_id ) // die t8('No profit carried forward chart configured!');
my $loss_chart;
if ( $self->defaults->yearend_method eq 'simple' ) {
$loss_chart = $profit_chart;
} else {
$loss_chart = SL::DB::Manager::Chart->find_by( id => $self->defaults->loss_carried_forward_chart_id ) // die t8('No profit and loss carried forward chart configured!');
}
95f601bd Martin Helmling
81860c0c Andreas Rudin
my ($report_data, $profit_loss_sum) = $self->_report(
f2f0fb50 Geoffrey Richardson
start_date => $start_date,
cb_date => $cb_date,
);
923c8609 Geoffrey Richardson
e592e0bc Geoffrey Richardson
# 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 ]) };

f2f0fb50 Geoffrey Richardson
my @asset_accounts = grep { $_->{account_type} eq 'asset_account' } @{ $report_data };
my @profit_loss_accounts = grep { $_->{account_type} eq 'profit_loss_account' } @{ $report_data };
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $ob_date = $cb_date->clone->add(days => 1);
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my ($credit_sum, $debit_sum) = (0,0);
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $employee_id = SL::DB::Manager::Employee->current->id;
95f601bd Martin Helmling
ed129ff9 Bernd Bleßmann
# rather than having one gl transaction for each asset account, we group all
f2f0fb50 Geoffrey Richardson
# the debit sums and credit sums for cb and ob bookings, so we will have 4 gl
# transactions:
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
# * cb for credit
# * cb for debit
# * ob for credit
# * ob for debit
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $db = SL::DB->client;
$db->with_transaction(sub {
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
######### asset accounts ########
# need cb and ob transactions
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $debit_balance = 0;
my $credit_balance = 0;
95f601bd Martin Helmling
f2f0fb50 Geoffrey Richardson
my $asset_cb_debit_entry = SL::DB::GLTransaction->new(
employee_id => $employee_id,
transdate => $cb_date,
reference => 'SB ' . $cb_date->year,
description => 'Automatische SB-Buchungen Bestandskonten Soll für ' . $cb_date->year,
ob_transaction => 0,
cb_transaction => 1,
e592e0bc Geoffrey Richardson
taxincluded => 0,
transactions => [],
f2f0fb50 Geoffrey Richardson
);
my $asset_ob_debit_entry = SL::DB::GLTransaction->new(
employee_id => $employee_id,
transdate => $ob_date,
reference => 'EB ' . $ob_date->year,
description => 'Automatische EB-Buchungen Bestandskonten Haben für ' . $ob_date->year,
ob_transaction => 1,
cb_transaction => 0,
e592e0bc Geoffrey Richardson
taxincluded => 0,
transactions => [],
f2f0fb50 Geoffrey Richardson
);
my $asset_cb_credit_entry = SL::DB::GLTransaction->new(
employee_id => $employee_id,
transdate => $cb_date,
reference => 'SB ' . $cb_date->year,
description => 'Automatische SB-Buchungen Bestandskonten Haben für ' . $cb_date->year,
ob_transaction => 0,
cb_transaction => 1,
e592e0bc Geoffrey Richardson
taxincluded => 0,
transactions => [],
f2f0fb50 Geoffrey Richardson
);
my $asset_ob_credit_entry = SL::DB::GLTransaction->new(
employee_id => $employee_id,
transdate => $ob_date,
reference => 'EB ' . $ob_date->year,
description => 'Automatische EB-Buchungen Bestandskonten Soll für ' . $ob_date->year,
ob_transaction => 1,
cb_transaction => 0,
e592e0bc Geoffrey Richardson
taxincluded => 0,
transactions => [],
f2f0fb50 Geoffrey Richardson
);

foreach my $asset_account ( @asset_accounts ) {
next if $asset_account->{amount_with_cb} == 0;
e592e0bc Geoffrey Richardson
my $ass_acc = $charts_by_id{ $asset_account->{chart_id} };
f2f0fb50 Geoffrey Richardson
if ( $asset_account->{amount_with_cb} < 0 ) {
# $main::lxdebug->message(0, sprintf("adding accno %s with balance %s to debit", $asset_account->{accno}, $asset_account->{amount_with_cb}));
e592e0bc Geoffrey Richardson
$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
);
f2f0fb50 Geoffrey Richardson
} 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};
e592e0bc Geoffrey Richardson
$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
);
f2f0fb50 Geoffrey Richardson
};
};

e592e0bc Geoffrey Richardson
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,
);
};
f2f0fb50 Geoffrey Richardson
e592e0bc Geoffrey Richardson
$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;
f2f0fb50 Geoffrey Richardson
####### profit-loss accounts #######
# these only have a closing balance, the balance is transferred to the profit-loss account

# need to know if profit or loss first!
# use amount_with_cb, so it can be run several times. So sum may be 0 the second time.
my $profit_loss_sum = sum map { $_->{amount_with_cb} }
grep { $_->{account_type} eq 'profit_loss_account' }
@{$report_data};
e592e0bc Geoffrey Richardson
$profit_loss_sum ||= 0;
f2f0fb50 Geoffrey Richardson
my $pl_chart;
81860c0c Andreas Rudin
if ( $profit_loss_sum > 0 || $self->defaults->yearend_method eq 'simple' ) {
f2f0fb50 Geoffrey Richardson
$pl_chart = $profit_chart;
} else {
$pl_chart = $loss_chart;
};

my $pl_debit_balance = 0;
my $pl_credit_balance = 0;
# soll = debit, haben = credit
81860c0c Andreas Rudin
if ( $self->defaults->yearend_method ne 'simple' ) {
my $pl_cb_debit_entry = SL::DB::GLTransaction->new(
employee_id => $employee_id,
transdate => $cb_date,
reference => 'SB ' . $cb_date->year,
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,
transdate => $cb_date,
reference => 'SB ' . $cb_date->year,
description => 'Automatische SB-Buchungen Erfolgskonten Haben für ' . $cb_date->year,
ob_transaction => 0,
cb_transaction => 1,
taxincluded => 0,
transactions => [],
);
f2f0fb50 Geoffrey Richardson
81860c0c Andreas Rudin
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;

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_chart_booking(
chart => $chart,
tax_id => 0,
debit => $profit_loss_account->{amount_with_cb},
);
};
f2f0fb50 Geoffrey Richardson
};
e592e0bc Geoffrey Richardson
81860c0c Andreas Rudin
# $main::lxdebug->message(0, "pl_debit_balance = $pl_debit_balance");
# $main::lxdebug->message(0, "pl_credit_balance = $pl_credit_balance");
e592e0bc Geoffrey Richardson
81860c0c Andreas Rudin
$pl_cb_debit_entry->add_chart_booking(
chart => $pl_chart,
tax_id => 0,
debit => $pl_debit_balance,
) if $pl_debit_balance;
f2f0fb50 Geoffrey Richardson
81860c0c Andreas Rudin
$pl_cb_credit_entry->add_chart_booking(
chart => $pl_chart,
tax_id => 0,
credit => $pl_credit_balance,
) if $pl_credit_balance;
e592e0bc Geoffrey Richardson
81860c0c Andreas Rudin
# 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 };
f2f0fb50 Geoffrey Richardson
81860c0c Andreas Rudin
$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;
};
f2f0fb50 Geoffrey Richardson
######### 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!

e592e0bc Geoffrey Richardson
if ( $profit_loss_sum != 0 ) {
81860c0c Andreas Rudin
my $carry_over_cb_entry;
if ($self->defaults->yearend_method ne 'simple') {
$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 => [],
);
};
e592e0bc Geoffrey Richardson
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 => [],
);
cb0414d6 Moritz Bunkus
e592e0bc Geoffrey Richardson
my ($amount1, $amount2);
if ( $profit_loss_sum < 0 ) {
$amount1 = 'debit';
$amount2 = 'credit';
} else {
$amount1 = 'credit';
$amount2 = 'debit';
};
f2f0fb50 Geoffrey Richardson
81860c0c Andreas Rudin
if ($self->defaults->yearend_method ne 'simple') {
$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),
);
};
e592e0bc Geoffrey Richardson
$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 };
f2f0fb50 Geoffrey Richardson
81860c0c Andreas Rudin
if ($self->defaults->yearend_method ne 'simple') {
$carry_over_cb_entry->post if scalar @{ $carry_over_cb_entry->transactions } > 1;
};
e592e0bc Geoffrey Richardson
$carry_over_ob_entry->post if scalar @{ $carry_over_ob_entry->transactions } > 1;
};
f2f0fb50 Geoffrey Richardson
my $consistency_query = <<SQL;
select sum(amount)
from acc_trans
where (ob_transaction is true or cb_transaction is true)
and (transdate = ? or transdate = ?)
SQL
e592e0bc Geoffrey Richardson
my ($sum) = selectrow_query($::form, $db->dbh, $consistency_query,
$cb_date,
$ob_date
);
f2f0fb50 Geoffrey Richardson
die "acc_trans transactions don't add up to zero" unless $sum == 0;

1;
}) or die $db->error;
cb0414d6 Moritz Bunkus
}

f2f0fb50 Geoffrey Richardson
sub _report {
81860c0c Andreas Rudin
my ($self, %params) = @_;
f2f0fb50 Geoffrey Richardson
my $start_date = delete $params{start_date};
my $cb_date = delete $params{cb_date};

die "no carry over account defined"
81860c0c Andreas Rudin
unless defined $self->defaults->carry_over_account_chart_id
and $self->defaults->carry_over_account_chart_id > 0;
f2f0fb50 Geoffrey Richardson
my $salden_query = <<SQL;
select c.id as chart_id,
c.accno,
c.description,
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,
sum(a.amount) filter (where cb_transaction is false ) as amount_without_cb,
sum(a.amount) filter (where cb_transaction is true ) as cb_amount,
sum(a.amount) as amount_with_cb,
case when c.category = ANY( '{I,E}' ) then 'profit_loss_account'
when c.category = ANY( '{A,C,L,Q}' ) then 'asset_account'
else null
end as account_type
from acc_trans a
inner join chart c on (c.id = a.chart_id)
where a.transdate >= ?
and a.transdate <= ?
and a.chart_id != ?
group by c.id, c.accno, c.category
order by account_type, c.accno
SQL

my $dbh = SL::DB->client->dbh;
my $report = selectall_hashref_query($::form, $dbh, $salden_query,
$start_date,
$cb_date,
81860c0c Andreas Rudin
$self->defaults->carry_over_account_chart_id,
f2f0fb50 Geoffrey Richardson
);
# profit_loss_sum is the actual profit/loss for the year, without cb, use "amount_without_cb")
my $profit_loss_sum = sum map { $_->{amount_without_cb} }
grep { $_->{account_type} eq 'profit_loss_account' }
@{$report};

return ($report, $profit_loss_sum);
}
cb0414d6 Moritz Bunkus
f2f0fb50 Geoffrey Richardson
#
# auth
#

sub check_auth {
$::auth->assert('general_ledger');
cb0414d6 Moritz Bunkus
}

f2f0fb50 Geoffrey Richardson
#
# inits
#

sub init_ob_date { $::locale->parse_date_to_object($::form->{ob_date}) }
sub init_cb_startdate { $::locale->parse_date_to_object($::form->{cb_startdate}) }
sub init_cb_date { $::locale->parse_date_to_object($::form->{cb_date}) }
81860c0c Andreas Rudin
sub init_defaults { SL::DB::Default->get }
95f601bd Martin Helmling
1;