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 objects subtotals_per_quarter salesman_id) ],
- 'scalar --get_set_init' => [ qw(employees types data) ],
+ 'scalar --get_set_init' => [ qw(employees types data show_costs) ],
);
__PACKAGE__->run_before(sub { $::auth->assert('report'); });
$self->get_objects;
$self->calculate_one_time_data;
$self->calculate_periodic_invoices;
+ $self->calculate_costs if $self->show_costs;
$self->prepare_report;
$self->list_data;
}
$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 ]);
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,
$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') ]], 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, ]]),
$self->objects->{sales_orders} = [ grep { !$_->periodic_invoices_config || !$_->periodic_invoices_config->active } @{ $self->objects->{sales_orders} } ];
}
-sub init_types { [ qw(sales_quotations sales_orders sales_invoices requests_for_quotation purchase_orders purchase_invoices) ] }
+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;
+}
sub init_data {
my ($self) = @_;
my ($self) = @_;
foreach my $type (@{ $self->types }) {
- foreach my $object (@{ $self->objects->{ $type } }) {
+ 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 = $self->data->{$type};
sub calculate_one_periodic_invoice {
my ($self, %params) = @_;
- return if $params{config}->start_date > $params{end_date};
+ # 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;
- my $first_date = $params{config}->start_date->clone->set_year($self->year);
- my $net = $params{config}->order->netamount * (12 / $params{config}->get_billing_period_length);
- my $sord = $self->data->{sales_orders};
+ $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) = @_;
- $sord->{months }->[ $first_date->month - 1 ] += $net;
- $sord->{quarters}->[ $first_date->quarter - 1 ] += $net;
- $sord->{year} += $net;
+ # 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 {
projects / kivitendo-erp.git / blobdiff