X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FController%2FFinancialOverview.pm;h=c0c7d183ce806aad5e35508a45707c3f341e9f6e;hb=5474d397bbc42ffaea0b51ebb4bb9a4c7afaa881;hp=7779a003817323ffdc78d1741faa9636bc1b7c6f;hpb=8165bc52de684ec8cd95a78515bb82a23d0dab5d;p=kivitendo-erp.git diff --git a/SL/Controller/FinancialOverview.pm b/SL/Controller/FinancialOverview.pm index 7779a0038..c0c7d183c 100644 --- a/SL/Controller/FinancialOverview.pm +++ b/SL/Controller/FinancialOverview.pm @@ -4,15 +4,20 @@ use strict; use parent qw(SL::Controller::Base); use List::MoreUtils qw(none); +use List::Util qw(min); +use SL::DB::Employee; use SL::DB::Invoice; use SL::DB::Order; +use SL::DB::PeriodicInvoicesConfig; use SL::DB::PurchaseInvoice; +use SL::DBUtils; use SL::Controller::Helper::ReportGenerator; use SL::Locale::String; use Rose::Object::MakeMethods::Generic ( - scalar => [ qw(report number_columns year current_year types objects data subtotals_per_quarter) ], + scalar => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ], + 'scalar --get_set_init' => [ qw(employees types data show_costs) ], ); __PACKAGE__->run_before(sub { $::auth->assert('report'); }); @@ -20,10 +25,12 @@ __PACKAGE__->run_before(sub { $::auth->assert('report'); }); sub action_list { my ($self) = @_; - $self->subtotals_per_quarter($::form->{subtotals_per_quarter}); + $self->$_($::form->{$_}) for qw(subtotals_per_quarter salesman_id); $self->get_objects; - $self->calculate_data; + $self->calculate_one_time_data; + $self->calculate_periodic_invoices; + $self->calculate_costs if $self->show_costs; $self->prepare_report; $self->list_data; } @@ -35,7 +42,7 @@ sub prepare_report { $self->report(SL::ReportGenerator->new(\%::myconfig, $::form)); - my @columns = qw(year quarter month sales_quotations sales_orders sales_invoices requests_for_quotation purchase_orders purchase_invoices); + my @columns = (qw(year quarter month), @{ $self->types }); $self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]); @@ -44,27 +51,29 @@ sub prepare_report { year => { text => t8('Year') }, quarter => { text => t8('Quarter') }, sales_quotations => { text => t8('Sales Quotations') }, - sales_orders => { text => t8('Sales Orders') }, + sales_orders => { text => t8('Sales Orders Advance') }, + sales_orders_per_inv => { text => t8('Total Sales Orders Value') }, 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') }, + costs => { text => t8('Costs') }, ); - map { $column_defs{$_}->{align} = 'right' } @columns; + $column_defs{$_}->{align} = 'right' for @columns; $self->report->set_options( std_column_visibility => 1, controller_class => 'FinancialOverview', output_format => 'HTML', - raw_top_info_text => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(2000..$self->current_year) ]), + raw_top_info_text => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]), 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); - $self->report->set_export_options(qw(list year subtotals_per_quarter)); + $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id)); $self->report->set_options_from_form; } @@ -77,22 +86,35 @@ sub get_objects { my $start = DateTime->new(year => $self->year, month => 1, day => 1); my $end = DateTime->new(year => $self->year, month => 12, day => 31); - my @date_filter = (and => [ transdate => { ge => $start }, transdate => { le => $end } ]); + my @f_date = (transdate => { ge => $start }, transdate => { le => $end }); + my @f_salesman = $self->salesman_id ? (salesman_id => $self->salesman_id) : (); $self->objects({ - sales_quotations => SL::DB::Manager::Order->get_all( where => [ and => [ @date_filter, SL::DB::Manager::Order->type_filter('sales_quotation') ]]), - sales_orders => SL::DB::Manager::Order->get_all( where => [ and => [ @date_filter, SL::DB::Manager::Order->type_filter('sales_order') ]]), - requests_for_quotation => SL::DB::Manager::Order->get_all( where => [ and => [ @date_filter, SL::DB::Manager::Order->type_filter('request_quotation') ]]), - purchase_orders => SL::DB::Manager::Order->get_all( where => [ and => [ @date_filter, SL::DB::Manager::Order->type_filter('purchase_order') ]]), - sales_invoices => SL::DB::Manager::Invoice->get_all( where => \@date_filter), - purchase_invoices => SL::DB::Manager::PurchaseInvoice->get_all(where => \@date_filter), + sales_quotations => SL::DB::Manager::Order->get_all( where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('sales_quotation') ]]), + 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) ]), + sales_orders_per_inv => [], + 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 ]), + 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) ]), }); + + $self->objects->{sales_orders} = [ grep { !$_->periodic_invoices_config || !$_->periodic_invoices_config->active } @{ $self->objects->{sales_orders} } ]; } -sub calculate_data { +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; - $self->types([ qw(sales_quotations sales_orders sales_invoices requests_for_quotation purchase_orders purchase_invoices) ]); + return \@types; +} + +sub init_data { + my ($self) = @_; my %data = ( year => [ ($self->year) x 12 ], @@ -107,18 +129,98 @@ sub calculate_data { } @{ $self->types }, ); - foreach my $type (keys %{ $self->objects }) { - foreach my $object (@{ $self->objects->{ $type } }) { + return \%data; +} + +sub calculate_one_time_data { + my ($self) = @_; + + foreach my $type (@{ $self->types }) { + my $src_object_type = $type eq 'sales_orders_per_inv' ? 'sales_orders' : $type; + foreach my $object (@{ $self->objects->{ $src_object_type } || [] }) { my $month = $object->transdate->month - 1; - my $tdata = $data{$type}; + my $tdata = $self->data->{$type}; $tdata->{months}->[$month] += $object->netamount; $tdata->{quarters}->[int($month / 3)] += $object->netamount; $tdata->{year} += $object->netamount; } } +} + +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) = @_; + + # 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}; + + 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; + + $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; +} - $self->data(\%data); +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 = <= ?) + 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}; + } } sub list_data { @@ -130,8 +232,8 @@ sub list_data { for my $month (1..12) { my %data = ( - map({ ($_ => { data => $self->data->{$_}->[$month - 1] }) } @non_type_columns), - map({ ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{months}->[$month - 1]) }) } @type_columns ), + map({ ($_ => { data => $self->data->{$_}->[$month - 1] }) } @non_type_columns), + map({ ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{months}->[$month - 1], 2) }) } @type_columns ), ); $self->report->add_data(\%data); @@ -140,7 +242,7 @@ sub list_data { my %subtotal = ( year => { data => $self->year }, month => { data => $::locale->text('Total') }, - map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{quarters}->[int(($month - 1) / 3)]) }) } @type_columns, + map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{quarters}->[int(($month - 1) / 3)], 2) }) } @type_columns, ); $subtotal{$_}->{class} = 'listsubtotal' for @visible_columns; @@ -152,7 +254,7 @@ sub list_data { my %data = ( year => { data => $self->year }, quarter => { data => $::locale->text('Total') }, - map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{year}) }) } @type_columns, + map { ($_ => { data => $::form->format_amount(\%::myconfig, $self->data->{$_}->{year}, 2) }) } @type_columns, ); $data{$_}->{class} = 'listtotal' for @visible_columns; @@ -162,4 +264,6 @@ sub list_data { return $self->report->generate_with_headers; } +sub init_employees { SL::DB::Manager::Employee->get_all_sorted } + 1;