1 package SL::Controller::DeliveryValueReport;
4 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::Locale::String;
17 use Rose::Object::MakeMethods::Generic (
18 scalar => [ qw(db_args flat_filter) ],
19 'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ],
22 __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); });
25 reqdate => t8('Reqdate'),
26 customer => t8('Customer'),
27 vendor => t8('Vendor'),
28 ordnumber => t8('Order'),
29 partnumber => t8('Part Number'),
30 description => t8('Description'),
31 qty => t8('Qty in Order'),
33 netto_qty => t8('Net value in Order'),
34 not_shipped_qty => t8('not shipped'),
35 netto_not_shipped_qty => t8('Net value without delivery orders'),
36 shipped_qty => t8('Qty in delivery orders'),
37 netto_shipped_qty => t8('Net Value in delivery orders'),
38 delivered_qty => t8('transferred in / out'),
39 netto_delivered_qty => t8('Net value transferred in / out'),
40 do_closed_qty => t8('Qty in closed delivery orders'),
41 netto_do_closed_qty => t8('Net value in closed delivery orders')
53 $self->make_filter_summary;
54 $self->prepare_report;
56 my $orderitems = $self->models->get;
57 $self->calc_qtys_price($orderitems);
58 $self->setup_list_action_bar;
59 $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems);
66 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
67 my $csv_option = $::form->{report_generator_output_format};
68 $self->{report} = $report;
70 my @columns = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty
71 not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty
72 netto_delivered_qty do_closed_qty netto_do_closed_qty);
75 my @sortable = qw(reqdate customer vendor ordnumber partnumber description);
77 # if csv report export no units
78 my $rp_csv_mod = ($csv_option eq 'CSV') ? 1 : '';
81 reqdate => { sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date } },
82 description => { sub => sub { $_[0]->description },
83 obj_link => sub { $self->link_to($_[0]->part) } },
84 partnumber => { sub => sub { $_[0]->part->partnumber },
85 obj_link => sub { $self->link_to($_[0]->part) } },
86 qty => { sub => sub { $_[0]->qty_as_number .
87 ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } },
88 netto_qty => { sub => sub { $::form->format_amount(\%::myconfig,
89 ($_[0]->qty * $_[0]->sellprice * (1 - $_[0]->discount) /
90 ($_[0]->price_factor || 1), 2)) },},
91 unit => { sub => sub { $_[0]->unit },
92 visible => $rp_csv_mod },
93 shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{shipped_qty}, 2) .
94 ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } },
95 netto_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_shipped_qty}, 2) },},
96 not_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->qty - $_[0]{shipped_qty}
97 - $_[0]{delivered_qty} - $_[0]{do_closed_qty}, 2) .
98 ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } },
99 delivered_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{delivered_qty}, 2) .
100 ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } },
101 netto_delivered_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_delivered_qty}, 2) },},
102 netto_not_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig,(($_[0]->qty -
103 $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty})
104 * ($_[0]->sellprice * (1 - $_[0]->discount) /
105 ($_[0]->price_factor || 1)), 2)) },},
106 do_closed_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{do_closed_qty}, 2) .
107 ($rp_csv_mod ? '' : ' ' . $_[0]->unit) },},
108 netto_do_closed_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_do_closed_qty}, 2) },},
109 ordnumber => { sub => sub { $_[0]->order->ordnumber },
110 obj_link => sub { $self->link_to($_[0]->order) } },
111 vendor => { sub => sub { $_[0]->order->vendor->name },
112 visible => $vc eq 'vendor',
113 obj_link => sub { $self->link_to($_[0]->order->vendor) } },
114 customer => { sub => sub { $_[0]->order->customer->name },
115 visible => $vc eq 'customer',
116 obj_link => sub { $self->link_to($_[0]->order->customer) } },
119 $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
121 $report->set_options(
122 std_column_visibility => 1,
123 controller_class => 'DeliveryValueReport',
124 output_format => 'HTML',
125 top_info_text => ($vc eq 'customer') ? t8('Delivery Value Report for currently open sales orders') :
126 t8('Delivery Value Report for currently outstanding purchase orders'),
127 title => $::locale->text('Delivery Value Report'),
128 allow_pdf_export => 1,
129 allow_csv_export => 1,
131 $report->set_columns(%column_defs);
132 $report->set_column_order(@columns);
133 $report->set_export_options(qw(list filter vc));
134 $report->set_options_from_form;
135 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
136 $self->models->finalize; # for filter laundering
137 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
138 $report->set_options(
139 raw_top_info_text => $self->render('delivery_value_report/report_top', { output => 0 }),
140 raw_bottom_info_text => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),
151 sub make_filter_summary {
154 my ($business, $employee);
156 my $filter = $::form->{filter} || {};
159 $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};
160 $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id};
163 [ $filter->{order}{"ordnumber:substr::ilike"}, $::locale->text('Number') ],
164 [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number') ],
165 [ $filter->{part}{"partnumber:substr::ilike"}, $::locale->text('Part Number') ],
166 [ $filter->{"description:substr::ilike"}, $::locale->text('Part Description') ],
167 [ $filter->{"reqdate:date::ge"}, $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],
168 [ $filter->{"reqdate:date::le"}, $::locale->text('Delivery Date') . " " . $::locale->text('To Date') ],
169 [ $filter->{"qty:number"}, $::locale->text('Quantity') ],
170 [ $filter->{order}{vendor}{"name:substr::ilike"}, $::locale->text('Vendor') ],
171 [ $filter->{order}{vendor}{"vendornumber:substr::ilike"}, $::locale->text('Vendor Number') ],
172 [ $filter->{order}{customer}{"name:substr::ilike"}, $::locale->text('Customer') ],
173 [ $filter->{order}{customer}{"customernumber:substr::ilike"}, $::locale->text('Customer Number') ],
174 [ $business, $::locale->text('Customer type') ],
175 [ $employee, $::locale->text('Employee') ],
178 # flags for with_object 'part'
180 part => $::locale->text('Parts'),
181 service => $::locale->text('Services'),
182 assembly => $::locale->text('Assemblies'),
184 my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
187 push @filter_strings, $_ if $_;
190 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
192 $self->{filter_summary} = join ', ', @filter_strings;
203 SL::Controller::Helper::GetModels->new(
205 model => 'OrderItem',
213 # show only open (sales|purchase) orders
214 query => [ 'order.closed' => '0', "order.${vc}_id" => { gt => 0 },
215 'order.quotation' => 0 ],
216 with_objects => [ 'order', "order.$vc", 'part' ],
217 additional_url_params => { vc => $vc},
222 return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryValueReport) has no vc defined";
224 sub init_all_employees {
225 return SL::DB::Manager::Employee->get_all_sorted;
227 sub init_all_businesses {
228 return SL::DB::Manager::Business->get_all_sorted;
233 my ($self, $object, %params) = @_;
235 return unless $object;
236 my $action = $params{action} || 'edit';
238 if ($object->isa('SL::DB::Order')) {
239 my $type = $object->type;
240 my $vc = $object->is_sales ? 'customer' : 'vendor';
241 my $id = $object->id;
243 return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
245 if ($object->isa('SL::DB::Part')) {
246 my $id = $object->id;
247 return "controller.pl?action=Part/$action&part.id=$id";
249 if ($object->isa('SL::DB::Customer')) {
250 my $id = $object->id;
251 return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
256 sub calc_qtys_price {
257 my ($self, $orderitems) = @_;
258 # using $orderitem->shipped_qty 40 times is far too slow. need to do it manually
259 # also for calc net values
261 return unless scalar @$orderitems;
263 my %orderitems_by_id = map { $_->id => $_ } @$orderitems;
266 SELECT oi.id, doi.qty, doi.unit, doe.delivered, doe.closed,
267 oi.sellprice, oi.discount, oi.price_factor
269 INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
270 INNER JOIN orderitems oi ON (oi.id = rl.from_id)
271 INNER JOIN delivery_orders doe ON (doe.id = doi.delivery_order_id)
272 WHERE rl.from_table = 'orderitems'
273 AND rl.to_table = 'delivery_order_items'
274 AND oi.id IN (@{[ join ', ', ("?")x @$orderitems ]})
277 my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $query, map { $_->id } @$orderitems);
279 for my $row (@$result) {
280 my $item = $orderitems_by_id{ $row->{id} };
281 $item->{shipped_qty} ||= 0;
282 $item->{delivered_qty} ||= 0;
283 $item->{do_closed_qty} ||= 0;
284 $item->{shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered} || $row->{closed});
285 $item->{delivered_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{delivered} && !$row->{closed});
286 $item->{do_closed_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{closed});
287 $item->{not_shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered});
289 my $price_factor = $row->{price_factor} || 1;
290 $item->{netto_shipped_qty} = $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
291 $item->{netto_delivered_qty} = $item->{delivered_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
292 $item->{netto_do_closed_qty} = $item->{do_closed_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
297 sub setup_list_action_bar {
298 my ($self, %params) = @_;
300 for my $bar ($::request->layout->get('actionbar')) {
304 submit => [ '#filter_form', { action => 'DeliveryValueReport/list' } ],
305 accesskey => 'enter',
322 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
326 Controller class for Delivery Value Report
328 The goal of the report is to determine which goods and at what costs are already delivered, transfered in
329 relation to open orders, orders in process.
332 Inherited from the base controller class, this controller implements the Delivery Value Report.
333 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
334 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
335 emphasis on expected future cashflow.
336 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
337 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
338 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
339 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
340 are equal. This breaks if the document has the same item on different positions. The next idea was to check
341 for individual item reqdates.
342 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
343 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
344 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
345 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
346 used in the position.
348 The main intelligence is this query (qty_stocked as comments):
350 SELECT oi.id,and more metadata , -- dois.qty as qty_stocked,
352 INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
353 INNER JOIN orderitems oi ON (oi.id = rl.from_id)
354 INNER JOIN delivery_orders doe ON (doe.id = doi.delivery_order_id)
355 --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
356 WHERE rl.from_table = 'orderitems'
357 AND rl.to_table = 'delivery_order_items'
359 Get all entries which were converted from orderitems to delivery_order_items (WHERE).
360 The persistent id are in rl, therefore we can fetch orderitems and delivery_order_items.
361 The join on delivery_orders (doe) is only needed for the current state of the delivery order (closed, delivered).
369 =item C<prepare_report>
371 =item C<make_filter_summary>
373 =item C<calc_qtys_price>
381 =item C<init_all_employees>
383 =item C<init_all_businesses>
389 Currently no foreign currencies and OrderItems with taxincluded are calculated / supported. The report can be easily extended
390 for the real stocked qty. The report is really easy to implement and customise if your model is focussed straight.
391 For long term maintaineance it would be wise to add more testcases for the conversion from orders to delivery_orders.
392 Right now record_links are tested only from document to document and the convert_invoice method (via task server) has a
393 test case with record_links items included. Furhtermore I personally dislike the calcs in the %columns_def, but for a quick report
394 this is ok, though if we redesign this further, the taxincluded / currency cases should be implemented as well.
399 Jan Büren E<lt>jan@kivitendo-premium.deE<gt> (based on DeliveryPlan.pm by Sven)