--- /dev/null
+package SL::Controller::FinancialControllingReport;
+
+use strict;
+use parent qw(SL::Controller::Base);
+
+use List::Util qw(sum);
+
+use SL::DB::Order;
+use SL::Controller::Helper::GetModels;
+use SL::Controller::Helper::Paginated;
+use SL::Controller::Helper::Sorted;
+use SL::Controller::Helper::ParseFilter;
+use SL::Controller::Helper::ReportGenerator;
+use SL::Locale::String;
+
+use Rose::Object::MakeMethods::Generic (
+ scalar => [ qw(db_args flat_filter) ],
+);
+
+__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); });
+
+__PACKAGE__->get_models_url_params('flat_filter');
+__PACKAGE__->make_paginated(
+ MODEL => 'Order',
+ PAGINATE_ARGS => 'db_args',
+ ONLY => [ qw(list) ],
+);
+
+__PACKAGE__->make_sorted(
+ MODEL => 'Order',
+ ONLY => [ qw(list) ],
+
+ DEFAULT_BY => 'transdate',
+ DEFAULT_DIR => 1,
+
+ transdate => t8('Order Date'),
+ ordnumber => t8('Order'),
+ customer => t8('Customer'),
+ transaction_description => t8('Transaction description'),
+ globalprojectnumber => t8('Project'),
+ netamount => t8('Order amount'),
+);
+
+sub action_list {
+ my ($self) = @_;
+
+ $self->db_args($self->setup_db_args_for_list(filter => $::form->{filter}));
+ $self->flat_filter({ map { $_->{key} => $_->{value} } $::form->flatten_variables('filter') });
+ $self->make_filter_summary;
+
+ $self->prepare_report;
+
+ $self->{orders} = $self->get_models(%{ $self->db_args });
+
+ $self->calculate_data;
+
+ $self->list_objects;
+}
+
+# private functions
+
+sub setup_db_args_for_list {
+ my ($self) = @_;
+
+ $self->{filter} = {};
+ my %args = ( parse_filter($::form->{filter}, with_objects => [ 'customer', 'globalproject' ], launder_to => $self->{filter}));
+ $args{query} = [
+ @{ $args{query} || [] },
+ SL::DB::Manager::Order->type_filter('sales_order'),
+ ];
+
+ return \%args;
+}
+
+sub prepare_report {
+ my ($self) = @_;
+
+ my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
+ $self->{report} = $report;
+
+ my @columns = qw(customer globalprojectnumber ordnumber transdate netamount delivered_amount delivered_amount_p billed_amount billed_amount_p paid_amount paid_amount_p
+ billable_amount billable_amount_p other_amount open_amount transaction_description);
+ my @sortable = qw(ordnumber transdate customer netamount globalprojectnumber);
+ $self->{number_columns} = [ qw(netamount billed_amount billed_amount_p delivered_amount delivered_amount_p paid_amount paid_amount_p open_amount other_amount billable_amount billable_amount_p) ];
+
+ my %column_defs = (
+ transdate => { sub => sub { $_[0]->transdate_as_date } },
+ netamount => { },
+ billed_amount => { text => $::locale->text('Billed amount') },
+ billed_amount_p => { text => $::locale->text('%') },
+ delivered_amount => { text => $::locale->text('Delivered amount') },
+ delivered_amount_p => { text => $::locale->text('%') },
+ paid_amount => { text => $::locale->text('Paid amount') },
+ paid_amount_p => { text => $::locale->text('%') },
+ billable_amount => { text => $::locale->text('Billable amount') },
+ billable_amount_p => { text => $::locale->text('%') },
+ open_amount => { text => $::locale->text('Bills receivable') },
+ other_amount => { text => $::locale->text('Billed extra expenses') },
+ transaction_description => { },
+ ordnumber => { obj_link => sub { $self->link_to($_[0]) } },
+ customer => { sub => sub { $_[0]->customer->name },
+ obj_link => sub { $self->link_to($_[0]->customer) } },
+ globalprojectnumber => { sub => sub { $_[0]->globalproject_id ? $_[0]->globalproject->projectnumber : '' } },
+ );
+
+ map { $column_defs{$_}->{text} ||= $::locale->text( $self->get_sort_spec->{$_}->{title} ) } keys %column_defs;
+ map { $column_defs{$_}->{align} = 'right' } @{ $self->{number_columns} };
+
+ $report->set_options(
+ std_column_visibility => 1,
+ controller_class => 'FinancialControlling',
+ output_format => 'HTML',
+ top_info_text => $::locale->text('Financial controlling report for open sales orders'),
+ raw_top_info_text => $self->render('financial_controlling_report/report_top', { no_output => 1, partial => 1 }),
+ raw_bottom_info_text => $self->render('financial_controlling_report/report_bottom', { no_output => 1, partial => 1 }),
+ title => $::locale->text('Financial Controlling Report'),
+ allow_pdf_export => 1,
+ allow_csv_export => 1,
+ );
+ $report->set_columns(%column_defs);
+ $report->set_column_order(@columns);
+ $report->set_export_options(qw(list filter));
+ $report->set_options_from_form;
+ $self->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
+
+ $self->disable_pagination if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
+
+ $self->{report_data} = {
+ column_defs => \%column_defs,
+ columns => \@columns,
+ };
+}
+
+sub calculate_data {
+ my ($self) = @_;
+
+ foreach my $order (@{ $self->{orders} }) {
+ my $delivery_orders = $order->linked_records(direction => 'to', to => 'DeliveryOrder', via => 'Order', query => [ '!customer_id' => undef ]);
+ my $invoices = $order->linked_records(direction => 'to', to => 'Invoice', via => [ 'Order', 'DeliveryOrder' ]);
+
+ $order->{delivered_amount} = sum map { $self->sum_relevant_items(order => $order, other => $_, by_order => 1) } @{ $delivery_orders };
+ $order->{billed_amount} = sum map { $self->sum_relevant_items(order => $order, other => $_) } @{ $invoices };
+ $order->{paid_amount} = sum map { $_->paid } @{ $invoices };
+ my $billed_amount = sum map { $_->netamount } @{ $invoices };
+ $order->{other_amount} = $billed_amount - $order->{billed_amount};
+ $order->{billable_amount} = $order->{delivered_amount} - $order->{billed_amount};
+ $order->{open_amount} = $billed_amount - $order->{paid_amount };
+
+ foreach (qw(delivered billed paid billable)) {
+ $order->{"${_}_amount_p"} = $order->netamount * 1 ? $order->{"${_}_amount"} * 100 / $order->netamount : undef;
+ }
+ }
+}
+
+sub sum_items {
+ my ($self, %params) = @_;
+
+ my %vals;
+
+ foreach my $item (@{ $params{obj}->items }) {
+ $vals{$item->parts_id} ||= { parts_id => $item->parts_id, amount => 0, base_qty => 0 };
+ $vals{$item->parts_id}->{amount} += $item->qty * $item->sellprice * (1 - $item->discount) / (($item->price_factor * 1) || 1);
+ $vals{$item->parts_id}->{base_qty} += $item->qty * $item->unit_obj->base_factor;
+ }
+
+ return \%vals;
+}
+
+sub sum_relevant_items {
+ my ($self, %params) = @_;
+
+ $params{order}->{amounts_by_parts_id} ||= $self->sum_items(obj => $params{order});
+ my $sums = $self->sum_items(obj => $params{other});
+ my $total = 0;
+
+ foreach my $item (grep { $params{order}->{amounts_by_parts_id}->{ $_->{parts_id} } } values %{ $sums }) {
+ my $order_item = $params{order}->{amounts_by_parts_id}->{ $item->{parts_id} };
+ if ($params{by_order} && $order_item->{base_qty}) {
+ $total += $order_item->{amount} * $item->{base_qty} / $order_item->{base_qty};
+ } else {
+ $total += $item->{amount};
+ }
+ }
+
+ if (!$params{by_order} && ($params{order}->ordnumber =~ m/4$/)) {
+ $::lxdebug->dump(0, "obj @ $total", $sums);
+ $::lxdebug->dump(0, "ord", $params{order}->{amounts_by_parts_id});
+ }
+
+ return $total;
+}
+
+sub list_objects {
+ my ($self) = @_;
+ my $column_defs = $self->{report_data}->{column_defs};
+
+ for my $obj (@{ $self->{orders} || [] }) {
+ my %data = map {
+ $_ => {
+ data => $column_defs->{$_}{sub} ? $column_defs->{$_}{sub}->($obj)
+ : $obj->can($_) ? $obj->$_
+ : $obj->{$_},
+ link => $column_defs->{$_}{obj_link} ? $column_defs->{$_}{obj_link}->($obj) : '',
+ },
+ } @{ $self->{report_data}{columns} || {} };
+
+ map { $data{$_}->{data} = defined $data{$_}->{data} ? int($data{$_}->{data}) : '' } grep { m/_p$/ } @{ $self->{number_columns} };
+ map { $data{$_}->{data} = $::form->format_amount(\%::myconfig, $data{$_}->{data}, 2) } grep { !m/_p$/ } @{ $self->{number_columns} };
+
+ $self->{report}->add_data(\%data);
+ }
+
+ return $self->{report}->generate_with_headers;
+}
+
+sub make_filter_summary {
+ my ($self) = @_;
+
+ my $filter = $::form->{filter} || {};
+ my @filter_strings;
+
+ my @filters = (
+ [ $filter->{"ordnumber:substr::ilike"}, $::locale->text('Number') ],
+ [ $filter->{"transdate:date::ge"}, $::locale->text('Order Date') . " " . $::locale->text('From Date') ],
+ [ $filter->{"transdate:date::le"}, $::locale->text('Order Date') . " " . $::locale->text('To Date') ],
+ [ $filter->{customer}{"name:substr::ilike"}, $::locale->text('Customer') ],
+ [ $filter->{customer}{"customernumber:substr::ilike"}, $::locale->text('Customer Number') ],
+ );
+
+ $self->{filter_summary} = join ', ', @filter_strings;
+}
+
+sub link_to {
+ my ($self, $object, %params) = @_;
+
+ return unless $object;
+ my $action = $params{action} || 'edit';
+
+ if ($object->isa('SL::DB::Order')) {
+ my $type = $object->type;
+ my $id = $object->id;
+
+ return "oe.pl?action=$action&type=$type&vc=customer&id=$id";
+ }
+ if ($object->isa('SL::DB::Customer')) {
+ my $id = $object->id;
+ return "ct.pl?action=$action&id=$id&db=customer";
+ }
+}
+
+1;
'#1 of #2 importable objects were imported.' => '#1 von #2 importierbaren Objekten wurden importiert.',
'#1 prices were updated.' => '#1 Preise wurden aktualisiert.',
'(recommended) Insert the used currencies in the system. You can simply change the name of the currencies by editing the textfields above. Do not use a name of a currency that is already in use.' => '(empfohlen) Fügen Sie die verwaisten Währungen in Ihr System ein. Sie können den Namen der Währung einfach ändern, indem Sie die Felder oben bearbeiten. Benutzen Sie keine Namen von Währungen, die Sie bereits benutzen.',
+ '%' => '%',
+ '* there are restrictions for the perpetual method, look at chapter "Bemerkungen zu Bestandsmethode" in' => ' für die Bestandsmethode gibt es Einschränkungen, siehe Kapitel "Bemerkungen zu Bestandsmethode" in',
+ '*) Since version 2.7 these parameters ares set in the client database and not in the configuration file, details in chapter:' => '*) Seit 2.7 werden Gewinnermittlungsart, Versteuerungsart und Warenbuchungsmethode in der Mandanten-DB gesteuert und nicht mehr in der Konfigurationsdatei, Umstellungs-Details:',
'*/' => '*/',
', if set' => ', falls gesetzt',
'---please select---' => '---bitte auswählen---',
'Best Before' => 'Mindesthaltbarkeit',
'Bestandskonto' => 'Bestandskonto',
'Bilanz' => 'Bilanz',
+ 'Billable amount' => 'Abrechenbarer Betrag',
+ 'Billed amount' => 'Abgerechneter Betrag',
+ 'Billed extra expenses' => 'Abgerechnete Nebenkosten',
'Billing Address' => 'Rechnungsadresse',
'Billing/shipping address (city)' => 'Rechnungsadresse (Stadt)',
'Billing/shipping address (country)' => 'Rechnungsadresse (Land)',
'Billing/shipping address (street)' => 'Rechnungsadresse (Straße)',
'Billing/shipping address (zipcode)' => 'Rechnungsadresse (PLZ)',
+ 'Bills receivable' => 'Offene Forderungen',
'Bin' => 'Lagerplatz',
'Bin From' => 'Quelllagerplatz',
'Bin List' => 'Lagerliste',
'Delete transaction' => 'Buchung löschen',
'Deleted' => 'Gelöscht',
'Delivered' => 'Geliefert',
+ 'Delivered amount' => 'Gelieferter Betrag',
'Delivery Date' => 'Lieferdatum',
'Delivery Order' => 'Lieferschein',
'Delivery Order Date' => 'Lieferscheindatum',
'Order Date missing!' => 'Auftragsdatum fehlt!',
'Order Number' => 'Auftragsnummer',
'Order Number missing!' => 'Auftragsnummer fehlt!',
+ 'Order amount' => 'Auftragswert',
'Order deleted!' => 'Auftrag gelöscht!',
'Order/Item row name' => 'Name der Auftrag-/Positions-Zeilen',
'OrderItem' => 'Position',
'Page' => 'Seite',
'Page #1/#2' => 'Seite #1/#2',
'Paid' => 'bezahlt',
+ 'Paid amount' => 'Bezahlter Betrag',
'Part' => 'Ware',
'Part (database ID)' => 'Artikel (Datenbank-ID)',
'Part Description' => 'Artikelbeschreibung',
--- /dev/null
+[%- USE T8 %]
+[%- USE L %]
+[%- USE LxERP %]
+[%- USE HTML %]
+<form action='controller.pl' method='post'>
+<div class='filter_toggle'>
+<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Show Filter' | $T8 %]</a>
+ [% SELF.filter_summary %]
+</div>
+<div class='filter_toggle' style='display:none'>
+<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Hide Filter' | $T8 %]</a>
+ <table id='filter_table'>
+ <tr>
+ <th align="right">[% 'Customer' | $T8 %]</th>
+ <td>[% L.input_tag('filter.customer.name:substr::ilike', filter.customer.name_substr__ilike, size = 20) %]</td>
+ </tr>
+ <tr>
+ <th align="right">[% 'Customer Number' | $T8 %]</th>
+ <td>[% L.input_tag('filter.customer.customernumber:substr::ilike', filter.customer.customernumber_substr__ilike, size = 20) %]</td>
+ </tr>
+ <tr>
+ <th align="right">[% 'Order Number' | $T8 %]</th>
+ <td>[% L.input_tag('filter.ordnumber:substr::ilike', filter.ordnumber_substr__ilike, size = 20) %]</td>
+ </tr>
+ <tr>
+ <th align="right">[% 'Project Number' | $T8 %]</th>
+ <td>[% L.input_tag('filter.globalproject.projectnumber:substr::ilike', filter.globalproject.projectnumber_substr__ilike, size = 20) %]</td>
+ </tr>
+ <tr>
+ <th align="right">[% 'Order Date' | $T8 %] [% 'From Date' | $T8 %]</th>
+ <td>[% L.date_tag('filter.transdate:date::ge', filter.transdate_date__ge, cal_align = 'BR') %]</td>
+ </tr>
+ <tr>
+ <th align="right">[% 'Order Date' | $T8 %] [% 'To Date' | $T8 %]</th>
+ <td>[% L.date_tag('filter.transdate:date::le', filter.transdate_date__le, cal_align = 'BR') %]</td>
+ </tr>
+ </table>
+
+[% L.hidden_tag('action', 'FinancialControllingReport/dispatch') %]
+[% L.hidden_tag('sort_by', FORM.sort_by) %]
+[% L.hidden_tag('sort_dir', FORM.sort_dir) %]
+[% L.hidden_tag('page', FORM.page) %]
+[% L.input_tag('action_list', LxERP.t8('Continue'), type = 'submit', class='submit')%]
+
+
+<a href='#' onClick='javascript:$("#filter_table input").attr("value","");$("#filter_table option").attr("selected","")'>[% 'Reset' | $T8 %]</a>
+
+</div>
+
+</form>
projects / kivitendo-erp.git / commitdiff