kivitendo/SL/Controller/FinancialOverview.pm @ 4b1666b7
| 8165bc52 | Moritz Bunkus | package SL::Controller::FinancialOverview;
|
||
use strict;
|
||||
use parent qw(SL::Controller::Base);
|
||||
use List::MoreUtils qw(none);
|
||||
| 56d540df | Moritz Bunkus | use List::Util qw(min);
|
||
| 8165bc52 | Moritz Bunkus | |||
| a222e324 | Moritz Bunkus | use SL::DB::Employee;
|
||
| 8165bc52 | Moritz Bunkus | use SL::DB::Invoice;
|
||
use SL::DB::Order;
|
||||
| 1734e1ad | Moritz Bunkus | use SL::DB::PeriodicInvoicesConfig;
|
||
| 8165bc52 | Moritz Bunkus | use SL::DB::PurchaseInvoice;
|
||
| a2d4ad4c | Moritz Bunkus | use SL::DBUtils;
|
||
| 8165bc52 | Moritz Bunkus | use SL::Controller::Helper::ReportGenerator;
|
||
use SL::Locale::String;
|
||||
use Rose::Object::MakeMethods::Generic (
|
||||
| 58f2f2f4 | Moritz Bunkus | scalar => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ],
|
||
| a2d4ad4c | Moritz Bunkus | 'scalar --get_set_init' => [ qw(employees types data show_costs) ],
|
||
| 8165bc52 | Moritz Bunkus | );
|
||
__PACKAGE__->run_before(sub { $::auth->assert('report'); });
|
||||
sub action_list {
|
||||
my ($self) = @_;
|
||||
| a222e324 | Moritz Bunkus | $self->$_($::form->{$_}) for qw(subtotals_per_quarter salesman_id);
|
||
| 8165bc52 | Moritz Bunkus | |||
$self->get_objects;
|
||||
| 1734e1ad | Moritz Bunkus | $self->calculate_one_time_data;
|
||
$self->calculate_periodic_invoices;
|
||||
| a2d4ad4c | Moritz Bunkus | $self->calculate_costs if $self->show_costs;
|
||
| 8165bc52 | Moritz Bunkus | $self->prepare_report;
|
||
$self->list_data;
|
||||
}
|
||||
# private functions
|
||||
sub prepare_report {
|
||||
my ($self) = @_;
|
||||
$self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
|
||||
| 56d540df | Moritz Bunkus | my @columns = (qw(year quarter month), @{ $self->types });
|
||
| 8165bc52 | Moritz Bunkus | |||
$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
|
||||
my %column_defs = (
|
||||
month => { text => t8('Month') },
|
||||
year => { text => t8('Year') },
|
||||
quarter => { text => t8('Quarter') },
|
||||
sales_quotations => { text => t8('Sales Quotations') },
|
||||
| 56d540df | Moritz Bunkus | sales_orders => { text => t8('Sales Orders Advance') },
|
||
sales_orders_per_inv => { text => t8('Total Sales Orders Value') },
|
||||
| 8165bc52 | Moritz Bunkus | sales_invoices => { text => t8('Invoices') },
|
||
requests_for_quotation => { text => t8('Requests for Quotation') },
|
||||
purchase_orders => { text => t8('Purchase Orders') },
|
||||
purchase_invoices => { text => t8('Purchase Invoices') },
|
||||
| a2d4ad4c | Moritz Bunkus | costs => { text => t8('Costs') },
|
||
| 8165bc52 | Moritz Bunkus | );
|
||
| 33dfc3d8 | Moritz Bunkus | $column_defs{$_}->{align} = 'right' for @columns;
|
||
| 8165bc52 | Moritz Bunkus | |||
$self->report->set_options(
|
||||
std_column_visibility => 1,
|
||||
controller_class => 'FinancialOverview',
|
||||
output_format => 'HTML',
|
||||
| ab98131d | Moritz Bunkus | raw_top_info_text => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]),
|
||
| 8165bc52 | Moritz Bunkus | title => t8('Financial overview for #1', $self->year),
|
||
allow_pdf_export => 1,
|
||||
allow_csv_export => 1,
|
||||
);
|
||||
$self->report->set_columns(%column_defs);
|
||||
$self->report->set_column_order(@columns);
|
||||
| a222e324 | Moritz Bunkus | $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id));
|
||
| 8165bc52 | Moritz Bunkus | $self->report->set_options_from_form;
|
||
}
|
||||
sub get_objects {
|
||||
my ($self) = @_;
|
||||
$self->current_year(DateTime->today->year);
|
||||
$self->year($::form->{year} || DateTime->today->year);
|
||||
my $start = DateTime->new(year => $self->year, month => 1, day => 1);
|
||||
my $end = DateTime->new(year => $self->year, month => 12, day => 31);
|
||||
| a222e324 | Moritz Bunkus | my @f_date = (transdate => { ge => $start }, transdate => { le => $end });
|
||
my @f_salesman = $self->salesman_id ? (salesman_id => $self->salesman_id) : ();
|
||||
| 8165bc52 | Moritz Bunkus | |||
$self->objects({
|
||||
| a222e324 | Moritz Bunkus | sales_quotations => SL::DB::Manager::Order->get_all( where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('sales_quotation') ]]),
|
||
| 1734e1ad | Moritz Bunkus | sales_orders => SL::DB::Manager::Order->get_all( where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('sales_order') ]], with_objects => [ qw(periodic_invoices_config) ]),
|
||
| 56d540df | Moritz Bunkus | sales_orders_per_inv => [],
|
||
| a222e324 | Moritz Bunkus | requests_for_quotation => SL::DB::Manager::Order->get_all( where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('request_quotation') ]]),
|
||
purchase_orders => SL::DB::Manager::Order->get_all( where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('purchase_order') ]]),
|
||||
sales_invoices => SL::DB::Manager::Invoice->get_all( where => [ and => [ @f_date, @f_salesman, ]]),
|
||||
purchase_invoices => SL::DB::Manager::PurchaseInvoice->get_all(where => [ and => \@f_date ]),
|
||||
| d1d37394 | Moritz Bunkus | periodic_invoices_cfg => SL::DB::Manager::PeriodicInvoicesConfig->get_all(where => [ active => 1, $self->salesman_id ? ('order.salesman_id' => $self->salesman_id) : () ], with_objects => [ qw(order) ]),
|
||
| 8165bc52 | Moritz Bunkus | });
|
||
| 1734e1ad | Moritz Bunkus | |||
$self->objects->{sales_orders} = [ grep { !$_->periodic_invoices_config || !$_->periodic_invoices_config->active } @{ $self->objects->{sales_orders} } ];
|
||||
| 8165bc52 | Moritz Bunkus | }
|
||
| a2d4ad4c | Moritz Bunkus | sub init_show_costs { $::instance_conf->get_profit_determination eq 'balance' }
|
||
sub init_types {
|
||||
my ($self) = @_;
|
||||
my @types = qw(sales_quotations sales_orders sales_orders_per_inv sales_invoices requests_for_quotation purchase_orders purchase_invoices);
|
||||
push @types, 'costs' if $self->show_costs;
|
||||
return \@types;
|
||||
}
|
||||
| 8165bc52 | Moritz Bunkus | |||
| 1734e1ad | Moritz Bunkus | sub init_data {
|
||
my ($self) = @_;
|
||||
| 8165bc52 | Moritz Bunkus | |||
my %data = (
|
||||
year => [ ($self->year) x 12 ],
|
||||
month => [ (1..12) ],
|
||||
quarter => [ (1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4) ],
|
||||
map {
|
||||
$_ => {
|
||||
months => [ (0) x 12 ],
|
||||
quarters => [ (0) x 4 ],
|
||||
year => 0,
|
||||
}
|
||||
} @{ $self->types },
|
||||
);
|
||||
| 1734e1ad | Moritz Bunkus | return \%data;
|
||
}
|
||||
sub calculate_one_time_data {
|
||||
my ($self) = @_;
|
||||
foreach my $type (@{ $self->types }) {
|
||||
| 56d540df | Moritz Bunkus | my $src_object_type = $type eq 'sales_orders_per_inv' ? 'sales_orders' : $type;
|
||
| a2d4ad4c | Moritz Bunkus | foreach my $object (@{ $self->objects->{ $src_object_type } || [] }) {
|
||
| 8165bc52 | Moritz Bunkus | my $month = $object->transdate->month - 1;
|
||
| 1734e1ad | Moritz Bunkus | my $tdata = $self->data->{$type};
|
||
| 8165bc52 | Moritz Bunkus | |||
$tdata->{months}->[$month] += $object->netamount;
|
||||
$tdata->{quarters}->[int($month / 3)] += $object->netamount;
|
||||
$tdata->{year} += $object->netamount;
|
||||
}
|
||||
}
|
||||
| 1734e1ad | Moritz Bunkus | }
|
||
sub calculate_periodic_invoices {
|
||||
my ($self) = @_;
|
||||
my $start_date = DateTime->new(year => $self->year, month => 1, day => 1, time_zone => $::locale->get_local_time_zone);
|
||||
my $end_date = DateTime->new(year => $self->year, month => 12, day => 31, time_zone => $::locale->get_local_time_zone);
|
||||
$self->calculate_one_periodic_invoice(config => $_, start_date => $start_date, end_date => $end_date) for @{ $self->objects->{periodic_invoices_cfg} };
|
||||
}
|
||||
sub calculate_one_periodic_invoice {
|
||||
my ($self, %params) = @_;
|
||||
| 56d540df | Moritz Bunkus | # Calculate sales order advance
|
||
my $net = $params{config}->order->netamount * $params{config}->get_billing_period_length / $params{config}->get_order_value_period_length;
|
||||
my $sord = $self->data->{sales_orders};
|
||||
| 1734e1ad | Moritz Bunkus | |||
| 56d540df | Moritz Bunkus | foreach my $date ($params{config}->calculate_invoice_dates(start_date => $params{start_date}, end_date => $params{end_date}, past_dates => 1)) {
|
||
$sord->{months }->[ $date->month - 1 ] += $net;
|
||||
$sord->{quarters}->[ $date->quarter - 1 ] += $net;
|
||||
$sord->{year} += $net;
|
||||
}
|
||||
# Calculate total sales order value
|
||||
my $date = $params{config}->order->transdate;
|
||||
return if $date->year != $params{start_date}->year;
|
||||
| 8165bc52 | Moritz Bunkus | |||
| 56d540df | Moritz Bunkus | $net = $params{config}->order->netamount;
|
||
$sord = $self->data->{sales_orders_per_inv};
|
||||
$sord->{months }->[ $date->month - 1 ] += $net;
|
||||
$sord->{quarters}->[ $date->quarter - 1 ] += $net;
|
||||
$sord->{year} += $net;
|
||||
| 8165bc52 | Moritz Bunkus | }
|
||
| a2d4ad4c | Moritz Bunkus | sub calculate_costs {
|
||
my ($self) = @_;
|
||||
# Relevante BWA-Positionen für Kosten:
|
||||
# 4 – Mat./Wareneinkauf
|
||||
# 10 – Personalkosten
|
||||
# 11 – Raumkosten
|
||||
# 12 – Betriebl.Steuern
|
||||
# 13 – Versicherungsbeiträge
|
||||
# 14 – KFZ-Kosten ohne Steuern
|
||||
# 15 – Werbe-/Reisekosten
|
||||
# 16 – Kosten Warenabgabe
|
||||
# 17 – Abschreibungen
|
||||
# 18 – Reparatur/Instandhaltung
|
||||
# 20 – Sonstige Kosten
|
||||
my $query = <<SQL;
|
||||
SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount,
|
||||
EXTRACT(month FROM ac.transdate) AS month
|
||||
FROM acc_trans ac
|
||||
LEFT JOIN chart c ON (c.id = ac.chart_id)
|
||||
WHERE (c.pos_bwa IN (4, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20))
|
||||
AND (ac.transdate >= ?)
|
||||
AND (ac.transdate < ?)
|
||||
GROUP BY month
|
||||
SQL
|
||||
my @args = (
|
||||
DateTime->new_local(day => 1, month => 1, year => $self->year)->to_kivitendo,
|
||||
DateTime->new_local(day => 1, month => 1, year => $self->year + 1)->to_kivitendo,
|
||||
);
|
||||
my @results = selectall_hashref_query($::form, SL::DB::AccTransaction->new->db->dbh, $query, @args);
|
||||
foreach my $row (@results) {
|
||||
my $month = $row->{month} - 1;
|
||||
my $tdata = $self->data->{costs};
|
||||
$tdata->{months}->[$month] += $row->{amount};
|
||||
$tdata->{quarters}->[int($month / 3)] += $row->{amount};
|
||||
$tdata->{year} += $row->{amount};
|
||||
}
|
||||
}
|
||||
| 8165bc52 | Moritz Bunkus | sub list_data {
|
||
my ($self) = @_;
|
||||
my @visible_columns = $self->report->get_visible_columns;
|
||||
my @type_columns = @{ $self->types };
|
||||
my @non_type_columns = grep { my $c = $_; none { $c eq $_ } @type_columns } @visible_columns;
|
||||
for my $month (1..12) {
|
||||
my %data = (
|
||||
| ed0f3caf | Moritz Bunkus | map({ ($_ => { data => $self->data->{$_}->[$month - 1] }) } @non_type_columns),
|
||
map({ ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{months}->[$month - 1], 2) }) } @type_columns ),
|
||||
| 8165bc52 | Moritz Bunkus | );
|
||
$self->report->add_data(\%data);
|
||||
if ($self->subtotals_per_quarter && (($month % 3) == 0)) {
|
||||
my %subtotal = (
|
||||
year => { data => $self->year },
|
||||
month => { data => $::locale->text('Total') },
|
||||
| ed0f3caf | Moritz Bunkus | map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{quarters}->[int(($month - 1) / 3)], 2) }) } @type_columns,
|
||
| 8165bc52 | Moritz Bunkus | );
|
||
$subtotal{$_}->{class} = 'listsubtotal' for @visible_columns;
|
||||
$self->report->add_data(\%subtotal);
|
||||
}
|
||||
}
|
||||
my %data = (
|
||||
year => { data => $self->year },
|
||||
quarter => { data => $::locale->text('Total') },
|
||||
| ed0f3caf | Moritz Bunkus | map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{year}, 2) }) } @type_columns,
|
||
| 8165bc52 | Moritz Bunkus | );
|
||
$data{$_}->{class} = 'listtotal' for @visible_columns;
|
||||
$self->report->add_data(\%data);
|
||||
return $self->report->generate_with_headers;
|
||||
}
|
||||
| a222e324 | Moritz Bunkus | sub init_employees { SL::DB::Manager::Employee->get_all_sorted }
|
||
| 8165bc52 | Moritz Bunkus | 1;
|