Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 0c298dbf

Von Sven Schöling vor fast 11 Jahren hinzugefügt

  • ID 0c298dbf1e299f5e1d2eb7241536540ec045372d
  • Vorgänger 1beca8de
  • Nachfolger 0759dd1c

Finanzcontrollingbericht implementiert

Unterschiede anzeigen:

SL/Controller/FinancialControllingReport.pm
1
package SL::Controller::FinancialControllingReport;
2

  
3
use strict;
4
use parent qw(SL::Controller::Base);
5

  
6
use List::Util qw(sum);
7

  
8
use SL::DB::Order;
9
use SL::Controller::Helper::GetModels;
10
use SL::Controller::Helper::Paginated;
11
use SL::Controller::Helper::Sorted;
12
use SL::Controller::Helper::ParseFilter;
13
use SL::Controller::Helper::ReportGenerator;
14
use SL::Locale::String;
15

  
16
use Rose::Object::MakeMethods::Generic (
17
  scalar => [ qw(db_args flat_filter) ],
18
);
19

  
20
__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); });
21

  
22
__PACKAGE__->get_models_url_params('flat_filter');
23
__PACKAGE__->make_paginated(
24
  MODEL         => 'Order',
25
  PAGINATE_ARGS => 'db_args',
26
  ONLY          => [ qw(list) ],
27
);
28

  
29
__PACKAGE__->make_sorted(
30
  MODEL                   => 'Order',
31
  ONLY                    => [ qw(list) ],
32

  
33
  DEFAULT_BY              => 'transdate',
34
  DEFAULT_DIR             => 1,
35

  
36
  transdate               => t8('Order Date'),
37
  ordnumber               => t8('Order'),
38
  customer                => t8('Customer'),
39
  transaction_description => t8('Transaction description'),
40
  globalprojectnumber     => t8('Project'),
41
  netamount               => t8('Order amount'),
42
);
43

  
44
sub action_list {
45
  my ($self) = @_;
46

  
47
  $self->db_args($self->setup_db_args_for_list(filter => $::form->{filter}));
48
  $self->flat_filter({ map { $_->{key} => $_->{value} } $::form->flatten_variables('filter') });
49
  $self->make_filter_summary;
50

  
51
  $self->prepare_report;
52

  
53
  $self->{orders} = $self->get_models(%{ $self->db_args });
54

  
55
  $self->calculate_data;
56

  
57
  $self->list_objects;
58
}
59

  
60
# private functions
61

  
62
sub setup_db_args_for_list {
63
  my ($self) = @_;
64

  
65
  $self->{filter} = {};
66
  my %args     = ( parse_filter($::form->{filter}, with_objects => [ 'customer', 'globalproject' ], launder_to => $self->{filter}));
67
  $args{query} = [
68
    @{ $args{query} || [] },
69
    SL::DB::Manager::Order->type_filter('sales_order'),
70
  ];
71

  
72
  return \%args;
73
}
74

  
75
sub prepare_report {
76
  my ($self)      = @_;
77

  
78
  my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
79
  $self->{report} = $report;
80

  
81
  my @columns     = qw(customer globalprojectnumber ordnumber transdate netamount delivered_amount delivered_amount_p billed_amount billed_amount_p paid_amount paid_amount_p
82
                       billable_amount billable_amount_p other_amount open_amount transaction_description);
83
  my @sortable    = qw(ordnumber transdate customer netamount globalprojectnumber);
84
  $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) ];
85

  
86
  my %column_defs           = (
87
    transdate               => {      sub => sub { $_[0]->transdate_as_date                                           }  },
88
    netamount               => {                                                                                         },
89
    billed_amount           => { text     => $::locale->text('Billed amount')                                            },
90
    billed_amount_p         => { text     => $::locale->text('%')                                                        },
91
    delivered_amount        => { text     => $::locale->text('Delivered amount')                                         },
92
    delivered_amount_p      => { text     => $::locale->text('%')                                                        },
93
    paid_amount             => { text     => $::locale->text('Paid amount')                                              },
94
    paid_amount_p           => { text     => $::locale->text('%')                                                        },
95
    billable_amount         => { text     => $::locale->text('Billable amount')                                          },
96
    billable_amount_p       => { text     => $::locale->text('%')                                                        },
97
    open_amount             => { text     => $::locale->text('Bills receivable')                                         },
98
    other_amount            => { text     => $::locale->text('Billed extra expenses')                                    },
99
    transaction_description => {                                                                                         },
100
    ordnumber               => { obj_link => sub { $self->link_to($_[0])                                              }  },
101
    customer                => {      sub => sub { $_[0]->customer->name                                              },
102
                                 obj_link => sub { $self->link_to($_[0]->customer)                                    }  },
103
    globalprojectnumber     => {      sub => sub { $_[0]->globalproject_id ? $_[0]->globalproject->projectnumber : '' }  },
104
  );
105

  
106
  map { $column_defs{$_}->{text} ||= $::locale->text( $self->get_sort_spec->{$_}->{title} ) } keys %column_defs;
107
  map { $column_defs{$_}->{align} = 'right' } @{ $self->{number_columns} };
108

  
109
  $report->set_options(
110
    std_column_visibility => 1,
111
    controller_class      => 'FinancialControlling',
112
    output_format         => 'HTML',
113
    top_info_text         => $::locale->text('Financial controlling report for open sales orders'),
114
    raw_top_info_text     => $self->render('financial_controlling_report/report_top',    { no_output => 1, partial => 1 }),
115
    raw_bottom_info_text  => $self->render('financial_controlling_report/report_bottom', { no_output => 1, partial => 1 }),
116
    title                 => $::locale->text('Financial Controlling Report'),
117
    allow_pdf_export      => 1,
118
    allow_csv_export      => 1,
119
  );
120
  $report->set_columns(%column_defs);
121
  $report->set_column_order(@columns);
122
  $report->set_export_options(qw(list filter));
123
  $report->set_options_from_form;
124
  $self->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
125

  
126
  $self->disable_pagination if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
127

  
128
  $self->{report_data} = {
129
    column_defs        => \%column_defs,
130
    columns            => \@columns,
131
  };
132
}
133

  
134
sub calculate_data {
135
  my ($self) = @_;
136

  
137
  foreach my $order (@{ $self->{orders} }) {
138
    my $delivery_orders = $order->linked_records(direction => 'to', to => 'DeliveryOrder', via => 'Order', query => [ '!customer_id' => undef ]);
139
    my $invoices        = $order->linked_records(direction => 'to', to => 'Invoice',       via => [ 'Order', 'DeliveryOrder' ]);
140

  
141
    $order->{delivered_amount}  = sum map { $self->sum_relevant_items(order => $order, other => $_, by_order => 1) } @{ $delivery_orders };
142
    $order->{billed_amount}     = sum map { $self->sum_relevant_items(order => $order, other => $_)                } @{ $invoices        };
143
    $order->{paid_amount}       = sum map { $_->paid                                                               } @{ $invoices        };
144
    my $billed_amount           = sum map { $_->netamount                                                          } @{ $invoices        };
145
    $order->{other_amount}      = $billed_amount             - $order->{billed_amount};
146
    $order->{billable_amount}   = $order->{delivered_amount} - $order->{billed_amount};
147
    $order->{open_amount}       = $billed_amount             - $order->{paid_amount  };
148

  
149
    foreach (qw(delivered billed paid billable)) {
150
      $order->{"${_}_amount_p"} = $order->netamount * 1 ? $order->{"${_}_amount"} * 100 / $order->netamount : undef;
151
    }
152
  }
153
}
154

  
155
sub sum_items {
156
  my ($self, %params) = @_;
157

  
158
  my %vals;
159

  
160
  foreach my $item (@{ $params{obj}->items }) {
161
    $vals{$item->parts_id}            ||= { parts_id => $item->parts_id, amount => 0, base_qty => 0 };
162
    $vals{$item->parts_id}->{amount}   += $item->qty * $item->sellprice * (1 - $item->discount) / (($item->price_factor * 1) || 1);
163
    $vals{$item->parts_id}->{base_qty} += $item->qty * $item->unit_obj->base_factor;
164
  }
165

  
166
  return \%vals;
167
}
168

  
169
sub sum_relevant_items {
170
  my ($self, %params) = @_;
171

  
172
  $params{order}->{amounts_by_parts_id} ||= $self->sum_items(obj => $params{order});
173
  my $sums                                = $self->sum_items(obj => $params{other});
174
  my $total                               = 0;
175

  
176
  foreach my $item (grep { $params{order}->{amounts_by_parts_id}->{ $_->{parts_id} } } values %{ $sums }) {
177
    my $order_item = $params{order}->{amounts_by_parts_id}->{ $item->{parts_id} };
178
    if ($params{by_order} && $order_item->{base_qty}) {
179
      $total += $order_item->{amount} * $item->{base_qty} / $order_item->{base_qty};
180
    } else {
181
      $total += $item->{amount};
182
    }
183
  }
184

  
185
  if (!$params{by_order} && ($params{order}->ordnumber =~ m/4$/)) {
186
    $::lxdebug->dump(0, "obj @ $total", $sums);
187
    $::lxdebug->dump(0, "ord", $params{order}->{amounts_by_parts_id});
188
  }
189

  
190
  return $total;
191
}
192

  
193
sub list_objects {
194
  my ($self)      = @_;
195
  my $column_defs = $self->{report_data}->{column_defs};
196

  
197
  for my $obj (@{ $self->{orders} || [] }) {
198
    my %data = map {
199
      $_ => {
200
        data => $column_defs->{$_}{sub} ? $column_defs->{$_}{sub}->($obj)
201
              : $obj->can($_)           ? $obj->$_
202
              :                           $obj->{$_},
203
        link => $column_defs->{$_}{obj_link} ? $column_defs->{$_}{obj_link}->($obj) : '',
204
      },
205
    } @{ $self->{report_data}{columns} || {} };
206

  
207
    map { $data{$_}->{data} = defined $data{$_}->{data} ? int($data{$_}->{data}) : ''    } grep {  m/_p$/ } @{ $self->{number_columns} };
208
    map { $data{$_}->{data} = $::form->format_amount(\%::myconfig, $data{$_}->{data}, 2) } grep { !m/_p$/ } @{ $self->{number_columns} };
209

  
210
    $self->{report}->add_data(\%data);
211
  }
212

  
213
  return $self->{report}->generate_with_headers;
214
}
215

  
216
sub make_filter_summary {
217
  my ($self) = @_;
218

  
219
  my $filter = $::form->{filter} || {};
220
  my @filter_strings;
221

  
222
  my @filters = (
223
    [ $filter->{"ordnumber:substr::ilike"},                $::locale->text('Number')                                          ],
224
    [ $filter->{"transdate:date::ge"},                     $::locale->text('Order Date') . " " . $::locale->text('From Date') ],
225
    [ $filter->{"transdate:date::le"},                     $::locale->text('Order Date') . " " . $::locale->text('To Date')   ],
226
    [ $filter->{customer}{"name:substr::ilike"},           $::locale->text('Customer')                                        ],
227
    [ $filter->{customer}{"customernumber:substr::ilike"}, $::locale->text('Customer Number')                                 ],
228
  );
229

  
230
  $self->{filter_summary} = join ', ', @filter_strings;
231
}
232

  
233
sub link_to {
234
  my ($self, $object, %params) = @_;
235

  
236
  return unless $object;
237
  my $action = $params{action} || 'edit';
238

  
239
  if ($object->isa('SL::DB::Order')) {
240
    my $type = $object->type;
241
    my $id   = $object->id;
242

  
243
    return "oe.pl?action=$action&type=$type&vc=customer&id=$id";
244
  }
245
  if ($object->isa('SL::DB::Customer')) {
246
    my $id     = $object->id;
247
    return "ct.pl?action=$action&id=$id&db=customer";
248
  }
249
}
250

  
251
1;
locale/de/all
19 19
  '#1 of #2 importable objects were imported.' => '#1 von #2 importierbaren Objekten wurden importiert.',
20 20
  '#1 prices were updated.'     => '#1 Preise wurden aktualisiert.',
21 21
  '(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.',
22
  '%'                           => '%',
23
  '* 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',
24
  '*) 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:',
22 25
  '*/'                          => '*/',
23 26
  ', if set'                    => ', falls gesetzt',
24 27
  '---please select---'         => '---bitte auswählen---',
......
298 301
  'Best Before'                 => 'Mindesthaltbarkeit',
299 302
  'Bestandskonto'               => 'Bestandskonto',
300 303
  'Bilanz'                      => 'Bilanz',
304
  'Billable amount'             => 'Abrechenbarer Betrag',
305
  'Billed amount'               => 'Abgerechneter Betrag',
306
  'Billed extra expenses'       => 'Abgerechnete Nebenkosten',
301 307
  'Billing Address'             => 'Rechnungsadresse',
302 308
  'Billing/shipping address (city)' => 'Rechnungsadresse (Stadt)',
303 309
  'Billing/shipping address (country)' => 'Rechnungsadresse (Land)',
304 310
  'Billing/shipping address (street)' => 'Rechnungsadresse (Straße)',
305 311
  'Billing/shipping address (zipcode)' => 'Rechnungsadresse (PLZ)',
312
  'Bills receivable'            => 'Offene Forderungen',
306 313
  'Bin'                         => 'Lagerplatz',
307 314
  'Bin From'                    => 'Quelllagerplatz',
308 315
  'Bin List'                    => 'Lagerliste',
......
678 685
  'Delete transaction'          => 'Buchung löschen',
679 686
  'Deleted'                     => 'Gelöscht',
680 687
  'Delivered'                   => 'Geliefert',
688
  'Delivered amount'            => 'Gelieferter Betrag',
681 689
  'Delivery Date'               => 'Lieferdatum',
682 690
  'Delivery Order'              => 'Lieferschein',
683 691
  'Delivery Order Date'         => 'Lieferscheindatum',
......
1461 1469
  'Order Date missing!'         => 'Auftragsdatum fehlt!',
1462 1470
  'Order Number'                => 'Auftragsnummer',
1463 1471
  'Order Number missing!'       => 'Auftragsnummer fehlt!',
1472
  'Order amount'                => 'Auftragswert',
1464 1473
  'Order deleted!'              => 'Auftrag gelöscht!',
1465 1474
  'Order/Item row name'         => 'Name der Auftrag-/Positions-Zeilen',
1466 1475
  'OrderItem'                   => 'Position',
......
1494 1503
  'Page'                        => 'Seite',
1495 1504
  'Page #1/#2'                  => 'Seite #1/#2',
1496 1505
  'Paid'                        => 'bezahlt',
1506
  'Paid amount'                 => 'Bezahlter Betrag',
1497 1507
  'Part'                        => 'Ware',
1498 1508
  'Part (database ID)'          => 'Artikel (Datenbank-ID)',
1499 1509
  'Part Description'            => 'Artikelbeschreibung',
menus/erp.ini
169 169
module=controller.pl
170 170
action=DeliveryPlan/list
171 171

  
172
[AR--Reports--Financial Controlling]
173
ACCESS=sales_order_edit
174
module=controller.pl
175
action=FinancialControllingReport/list
176

  
172 177
[AP]
173 178

  
174 179
[AP--Add RFQ]
templates/webpages/financial_controlling_report/_filter.html
1
[%- USE T8 %]
2
[%- USE L %]
3
[%- USE LxERP %]
4
[%- USE HTML %]
5
<form action='controller.pl' method='post'>
6
<div class='filter_toggle'>
7
<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Show Filter' | $T8 %]</a>
8
  [% SELF.filter_summary %]
9
</div>
10
<div class='filter_toggle' style='display:none'>
11
<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Hide Filter' | $T8 %]</a>
12
 <table id='filter_table'>
13
  <tr>
14
   <th align="right">[% 'Customer' | $T8 %]</th>
15
   <td>[% L.input_tag('filter.customer.name:substr::ilike', filter.customer.name_substr__ilike, size = 20) %]</td>
16
  </tr>
17
  <tr>
18
   <th align="right">[% 'Customer Number' | $T8 %]</th>
19
   <td>[% L.input_tag('filter.customer.customernumber:substr::ilike', filter.customer.customernumber_substr__ilike, size = 20) %]</td>
20
  </tr>
21
  <tr>
22
   <th align="right">[% 'Order Number' | $T8 %]</th>
23
   <td>[% L.input_tag('filter.ordnumber:substr::ilike', filter.ordnumber_substr__ilike, size = 20) %]</td>
24
  </tr>
25
  <tr>
26
   <th align="right">[% 'Project Number' | $T8 %]</th>
27
   <td>[% L.input_tag('filter.globalproject.projectnumber:substr::ilike', filter.globalproject.projectnumber_substr__ilike, size = 20) %]</td>
28
  </tr>
29
  <tr>
30
   <th align="right">[% 'Order Date' | $T8 %] [% 'From Date' | $T8 %]</th>
31
   <td>[% L.date_tag('filter.transdate:date::ge', filter.transdate_date__ge, cal_align = 'BR') %]</td>
32
  </tr>
33
  <tr>
34
   <th align="right">[% 'Order Date' | $T8 %] [% 'To Date' | $T8 %]</th>
35
   <td>[% L.date_tag('filter.transdate:date::le', filter.transdate_date__le, cal_align = 'BR') %]</td>
36
  </tr>
37
 </table>
38

  
39
[% L.hidden_tag('action', 'FinancialControllingReport/dispatch') %]
40
[% L.hidden_tag('sort_by', FORM.sort_by) %]
41
[% L.hidden_tag('sort_dir', FORM.sort_dir) %]
42
[% L.hidden_tag('page', FORM.page) %]
43
[% L.input_tag('action_list', LxERP.t8('Continue'), type = 'submit', class='submit')%]
44

  
45

  
46
<a href='#' onClick='javascript:$("#filter_table input").attr("value","");$("#filter_table option").attr("selected","")'>[% 'Reset' | $T8 %]</a>
47

  
48
</div>
49

  
50
</form>
templates/webpages/financial_controlling_report/report_bottom.html
1
[% USE L %]
2
[%- L.paginate_controls %]
templates/webpages/financial_controlling_report/report_top.html
1
[%- USE L %]
2
[%- PROCESS 'financial_controlling_report/_filter.html' filter=SELF.filter %]
3
 <hr>

Auch abrufbar als: Unified diff