Merge branch 'b-3.6.1' of ../kivitendo-erp_20220811

[mfinanz.git] / SL / Controller / FinancialOverview.pm

diff --git a/SL/Controller/FinancialOverview.pm b/SL/Controller/FinancialOverview.pm
index fec091572a9851e0b172acff0044670b033dc896..c0c7d183ce806aad5e35508a45707c3f341e9f6e 100644 (file)
--- a/SL/Controller/FinancialOverview.pm
+++ b/SL/Controller/FinancialOverview.pm
@@ -4,17 +4,20 @@ use strict;
 use parent qw(SL::Controller::Base);
 
 use List::MoreUtils qw(none);
 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::Employee;
 use SL::DB::Invoice;
 use SL::DB::Order;

+use SL::DB::PeriodicInvoicesConfig;

 use SL::DB::PurchaseInvoice;
 use SL::DB::PurchaseInvoice;

+use SL::DBUtils;

 use SL::Controller::Helper::ReportGenerator;
 use SL::Locale::String;
 
 use Rose::Object::MakeMethods::Generic (
 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 salesman_id) ],
-  'scalar --get_set_init' => [ qw(employees) ],
+  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'); });
 );
 
 __PACKAGE__->run_before(sub { $::auth->assert('report'); });


@@ -25,7 +28,9 @@ sub action_list {
   $self->$_($::form->{$_}) for qw(subtotals_per_quarter salesman_id);
 
   $self->get_objects;
   $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;
 }
   $self->prepare_report;
   $self->list_data;
 }


@@ -37,7 +42,7 @@ sub prepare_report {
 
   $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 
 
   $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 ]);
 
 
   $self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
 


@@ -46,20 +51,22 @@ sub prepare_report {
     year                   => { text => t8('Year')                   },
     quarter                => { text => t8('Quarter')                },
     sales_quotations       => { text => t8('Sales Quotations')       },
     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')      },
     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',
 
   $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,
     title                 => t8('Financial overview for #1', $self->year),
     allow_pdf_export      => 1,
     allow_csv_export      => 1,


@@ -84,18 +91,30 @@ sub get_objects {
 
   $self->objects({
     sales_quotations       => SL::DB::Manager::Order->get_all(          where => [ and => [ @f_date, @f_salesman, SL::DB::Manager::Order->type_filter('sales_quotation')   ]]),
 
   $self->objects({
     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')       ]]),
+    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 ]),
     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 ($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                   ],
 
   my %data  = (
     year    => [ ($self->year) x 12                   ],


@@ -110,18 +129,98 @@ sub calculate_data {
     } @{ $self->types },
   );
 
     } @{ $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 $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;
     }
   }
 
       $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} };
+}

 
 

-  $self->data(\%data);
+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;
+}
+
+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};
+  }

 }
 
 sub list_data {
 }
 
 sub list_data {