1 package SL::Helper::ShippedQty;
4 use parent qw(Rose::Object);
7 use Scalar::Util qw(blessed);
8 use SL::DBUtils qw(selectall_hashref_query selectall_as_map);
9 use List::Util qw(min);
10 use List::MoreUtils qw(any all uniq);
11 use List::UtilsBy qw(partition_by);
12 use SL::Locale::String qw(t8);
14 use Rose::Object::MakeMethods::Generic (
15 'scalar' => [ qw(objects objects_or_ids shipped_qty ) ],
16 'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered) ],
19 my $no_stock_item_links_query = <<'';
20 SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.qty AS doi_qty, doi.unit AS doi_unit
22 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
23 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
24 WHERE oi.trans_id IN (%s)
25 ORDER BY oi.trans_id, oi.position
27 # oi not item linked. takes about 250ms for 100k hits
28 my $fill_up_oi_query = <<'';
29 SELECT oi.id, oi.trans_id, oi.position, oi.parts_id, oi.description, oi.reqdate, oi.serialnumber, oi.qty, oi.unit
31 WHERE oi.trans_id IN (%s)
32 ORDER BY oi.trans_id, oi.position
34 # doi linked by record, but not by items; 250ms for 100k hits
35 my $no_stock_fill_up_doi_query = <<'';
36 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, doi.qty, doi.unit
37 FROM delivery_order_items doi
38 WHERE doi.delivery_order_id IN (
43 AND to_table = 'delivery_orders'
44 AND to_id = doi.delivery_order_id)
48 WHERE from_table = 'orderitems'
49 AND to_table = 'delivery_order_items'
52 my $stock_item_links_query = <<'';
53 SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, i.qty AS doi_qty, p.unit AS doi_unit
55 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
56 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
57 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
58 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
59 INNER JOIN parts p ON p.id = doi.parts_id
60 WHERE oi.trans_id IN (%s)
61 ORDER BY oi.trans_id, oi.position
63 my $stock_fill_up_doi_query = <<'';
64 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, i.qty, i.unit
65 FROM delivery_order_items doi
66 INNER JOIN parts p ON p.id = doi.parts_id
67 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
68 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
69 WHERE doi.delivery_order_id IN (
74 AND to_table = 'delivery_orders'
75 AND to_id = doi.delivery_order_id)
79 WHERE from_table = 'orderitems'
80 AND to_table = 'delivery_order_items'
83 my $oe_do_record_links = <<'';
88 AND to_table = 'delivery_orders'
90 my @known_item_identity_fields = qw(parts_id description reqdate serialnumber);
91 my %item_identity_fields = (
92 parts_id => t8('Part'),
93 description => t8('Description'),
94 reqdate => t8('Reqdate'),
95 serialnumber => t8('Serial Number'),
99 my ($self, $data) = @_;
101 die 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
103 return if !$data || ('ARRAY' eq ref $data && !@$data);
105 $self->normalize_input($data);
107 return unless @{ $self->oe_ids };
109 $self->calculate_item_links;
110 $self->calculate_fill_up if $self->fill_up;
115 sub calculate_item_links {
118 my @oe_ids = @{ $self->oe_ids };
120 my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query;
122 my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids);
124 my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids);
127 $self->shipped_qty->{$_->{oi_id}} //= 0;
128 $self->shipped_qty->{$_->{oi_id}} += $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
129 $self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
130 $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
137 grep { $seen{$_}++ } @$a1, @$a2;
140 sub calculate_fill_up {
143 my @oe_ids = @{ $self->oe_ids };
145 my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query;
147 my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids);
148 my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids);
149 my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids);
151 my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids);
155 my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids);
156 my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids);
158 my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi;
159 my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi;
161 push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} }
162 for grep { exists $doi_by_id{$_->{to_id}} } @$rl;
164 my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi;
166 for my $match (sort keys %oi_by_identity) {
167 next unless exists $doi_by_identity{$match};
169 my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} };
170 for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) {
171 next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id});
173 # sorting should be quite fast here, because there are usually only a handful of matches
174 next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} };
176 # parallel walk through sorted oi/doi entries
177 my $oi_i = my $doi_i = 0;
178 my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]);
179 while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) {
180 $oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}};
181 $doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty};
183 my $factor = AM->convert_unit($doi->{unit} => $oi->{unit});
184 my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor);
186 # min_qty should never be 0 now. the first part triggers the first next,
187 # the second triggers the second next and factor must not be 0
188 # but it would lead to an infinite loop, so catch that.
189 die 'panic! invalid shipping quantity' unless $min_qty;
191 $self->shipped_qty->{$oi->{id}} += $min_qty;
192 $doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding
197 $self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
198 $self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
202 my ($self, $objects) = @_;
204 die 'expecting array of objects' unless 'ARRAY' eq ref $objects;
206 my $shipped_qty = $self->shipped_qty;
208 for my $obj (@$objects) {
209 if ('SL::DB::OrderItem' eq ref $obj) {
210 $obj->{shipped_qty} = $shipped_qty->{$obj->id};
211 $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
212 } elsif ('SL::DB::Order' eq ref $obj) {
213 if (exists $obj->{orderitems}) {
214 $self->write_to($obj->{orderitems});
215 $obj->{delivered} = all { $_->{delivered} } @{ $obj->{orderitems} };
217 # don't force a load on items. just compute by oe_id directly
218 $obj->{delivered} = $self->delivered->{$obj->id};
221 die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to";
226 sub write_to_objects {
229 die 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
231 $self->write_to($self->objects);
235 my ($self, $row) = @_;
237 join $;, map $row->{$_}, @{ $self->item_identity_fields };
240 sub normalize_input {
241 my ($self, $data) = @_;
243 $data = [$data] if 'ARRAY' ne ref $data;
245 $self->objects_or_ids(!!blessed($data->[0]));
247 if ($self->objects_or_ids) {
248 die 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
249 $self->objects($data);
251 die 'object or reference in data while expecting ids' if any { ref($_) } @$data;
252 $self->oe_ids($data);
255 $self->shipped_qty({});
258 sub available_item_identity_fields {
259 map { [ $_ => $item_identity_fields{$_} ] } @known_item_identity_fields;
265 die 'oe_ids not initialized in id mode' if !$self->objects_or_ids;
266 die 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects;
267 die 'objects need to be Order or OrderItem' if any { ref($_) !~ /^SL::DB::Order(?:Item)?$/ } @{ $self->objects };
269 [ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ]
272 sub init_dbh { SL::DB->client->dbh }
274 sub init_oi2oe { {} }
275 sub init_oi_qty { {} }
279 for (keys %{ $self->oi_qty }) {
280 my $oe_id = $self->oi2oe->{$_};
282 $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_};
287 sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
288 sub init_item_identity_fields { [ grep $item_identity_fields{$_}, @{ $::instance_conf->get_shipped_qty_item_identity_fields } ] }
289 sub init_fill_up { $::instance_conf->get_shipped_qty_fill_up }
299 SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty
303 use SL::Helper::ShippedQty;
305 my $helper = SL::Helper::ShippedQty->new(
307 require_stock_out => 0,
308 item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
312 $helper->calculate($order_object);
313 $helper->calculate(\@order_objects);
314 $helper->calculate($orderitem_object);
315 $helper->calculate(\@orderitem_objects);
316 $helper->calculate($oe_id);
317 $helper->calculate(\@oe_ids);
319 # if these are items set elivered and shipped_qty
320 # if these are orders, iterate through their items and set delivered on order
321 $helper->write_to($objects);
323 # if calculate was called with objects, you can use this shortcut:
324 $helper->write_to_objects;
326 # shipped_qtys by oi_id
327 my $shipped_qtys_by_oi_id = $helper->shipped_qtys;
330 my $delivered_by_oe_id = $helper->delievered;
334 This module encapsulates the algorithm needed to compute the shipped qty for
335 orderitems (hopefully) correctly and efficiently for several use cases.
337 While this is used in object accessors, it can not be fast when called in a
338 loop over and over, so take advantage of batch processing when possible.
340 =head1 MOTIVATION AND PROBLEMS
342 The concept of shipped qty is sadly not as straight forward as it sounds on
343 first glance. Any correct implementation must in some way deal with the
350 When is an order shipped? For users that use the inventory it
351 will mean when a delivery order is stocked out. For those not using the
352 inventory it will mean when the delivery order is saved.
356 How to find the correct matching elements. After the changes
357 to record item links it's natural to assume that each position is linked, but
358 for various reasons this might not be the case. Positions that are not linked
359 in database need to be matched by marching.
363 Double links need to be accounted for (these can stem from buggy code).
367 orderitems and oe entries may link to many of their counterparts in
368 delivery_orders. delivery_orders my be created from multiple orders. The
369 only constant is that a single entry in delivery_order_items has at most one
370 link from an orderitem.
374 For the fill up case the identity of positions is not clear. The naive approach
375 is just the same part, but description, charge number, reqdate and qty can all
376 be part of the identity of a position for finding shipped matches.
380 Certain delivery orders might not be eligable for qty calculations if delivery
381 orders are used for other purposes.
385 Units need to be handled correctly
389 Negative positions must be taken into account. A negative delivery order is
390 assumed to be a RMA of sorts, but a negative order is not as straight forward.
394 Must be able to work with plain ids and Rose objects, and absolutely must
395 include a bulk mode to speed up multiple objects.
406 Creates a new helper object. PARAMS may include:
410 =item * C<require_stock_out>
412 Boolean. If set, delivery orders must be stocked out to be considered
413 delivered. The default is a client setting.
417 Boolean. If set, unlinked delivery order items will be used to fill up
418 undelivered order items. Not needed in newer installations. The default is a
421 =item * C<item_identity_fields ARRAY>
423 If set, the fields are used to compute the identity of matching positions. The
424 default is a client setting. Possible values include:
430 =item * C<description>
434 =item * C<serialnumber>
440 =item C<calculate OBJECTS>
442 =item C<calculate IDS>
444 Do the main work. There must be a single argument: Either an id or an
445 C<SL::DB::Order> object, or an arrayref of one of these types.
447 Mixing ids and objects will generate an exception.
449 No return value. All internal errors will throw an exception.
451 =item C<write_to OBJECTS>
453 =item C<write_to_objects>
455 Save the C<shipped_qty> and C<delivered> state to the objects. If L</calculate>
456 was called with objects, then C<write_to_objects> will use these.
460 Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.
464 Valid after L</calculate>. Returns a hasref with delivered flag by order id.
468 =head1 REPLACED FUNCTIONALITY
470 =head2 delivered mode
472 Originally used in mark_orders_if_delivered. Searches for orders associated
473 with a delivery order and evaluates whether those are delivered or not. No
474 detailed information is needed.
476 This is to be integrated into fast delivered check on the orders. The calling
477 convention for the delivery_order is not scope of this module.
481 Originally used for printing delivery orders. Resolves for each position for
482 much was originally ordered, and how much remains undelivered.
484 This one is likely to be dropped. The information makes only sense without
485 combined merge/split deliveries and is very fragile with unaccounted delivery
490 Same from order perspective. Used for transitions to delivery orders, where
491 delivered qtys should be removed from positions. Also used each time a record
492 is rendered to show the shipped qtys. Also used to find orders that are not
495 Acceptable shortcuts would be the concepts fully shipped (for the order) and
496 providing already loaded objects.
498 =head2 Replaces the following functions
500 C<DO::get_shipped_qty>
502 C<SL::Controller::DeliveryPlan::calc_qtys>
504 C<SL::DB::OrderItem::shipped_qty>
506 C<SL::DB::OrderItem::delivered_qty>
510 this is the old get_shipped_qty algorithm by Martin for reference
512 in: oe_id, do_id, doctype, delivered flag
514 not needed with better signatures
516 load oe->do links for this id,
517 set oe_ids from those
524 2 load all orderitems for these oe_ids
527 set undelivered := qty
530 create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ]
532 1 load all oe->do links for these oe_ids
535 return all tuples so far
538 4 create dictionary for orderitems from [2] by id
540 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems
541 - optionally with doctype filter (identity filter)
543 # first pass for record_item_links
546 if link from orderitem exists and orderitem is in dictionary [4]
547 reduce qty_notdelivered in orderitem by doi.qty
548 keep link to do entry in orderitem
551 # second pass fill up
553 ignroe if from link exists or qty == 0
555 for orderitems from [2]:
556 next if notdelivered_qty == 0
557 if doi.parts_id == orderitem.parts_id:
558 if oi.notdelivered_qty < 0:
559 doi :+= -oi.notdelivered_qty,
560 oi.notdelivered_qty := 0
562 fi doi.qty < oi.notdelivered_qty:
564 oi.notdelivered_qty :-= doi.qty
566 doi.qty :-= oi.notdelivered_qty
567 oi.notdelivered_qty := 0
569 keep link to oi in doi
572 last wenn doi.qty <= 0
576 # post process for return
579 copy notdelivered from oe to ship{position}{notdelivered}
580 if !oe_id and do_id and delivered:
581 ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0
582 if !oe_id and do_id and !delivered:
584 ignore if do.id != doi.delivery_order_id
585 if oi in doi verlinkt und position bekannt:
586 addiere oi.qty zu doi.ordered_qty
587 addiere oi.notdelievered_qty zu doi.notdelivered_qty
594 in: orders, parameters
596 normalize orders to ids
598 # handle record_item links
599 retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested
600 for all record_links:
601 initialize shipped_qty for this doi to 0 if not yet seen
602 convert doi.qty to oi.unit
603 add normalized doi.qty to shipped_qty
607 abort if fill up is not requested
609 retrieve all orderitems matching the given order ids
610 retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory)
611 retrieve all record_links between orders and delivery_orders (1)
613 abort when no dois were found
615 create a partition of the delivery order items by do_id (2)
616 create empty mapping for delivery order items by order_id (3)
617 for all record_links from [1]:
618 add all matching doi from (2) to (3)
621 create a partition of the orderitems by item identity (4)
622 create a partition of the delivery order items by item identity (5)
624 for each identity in (4):
625 skip if no matching entries in (5)
627 create partition of all orderitems for this identity by order id (6)
628 for each sorted order id in [6]:
629 look up matching delivery order items by identity from [5] (7)
630 look up matching delivery order items by order id from [3] (8)
631 create stable sorted intersection between [7] and [8] (9)
633 sort the orderitems from (6) by position (10)
635 parallel walk through [9] and [10]:
636 missing qty := oi.qty - shipped_qty[oi]
639 next orderitem if missing_qty <= 0
640 next delivery order item if doi.qty == 0
642 min_qty := minimum(missing_qty, [doi.qty converted to oi.unit]
644 # transfer min_qty from doi.qty to shipped[qty]:
645 shipped_qty[oi] += min_qty
646 doi.qty -= [min_qty converted to doi.unit]
651 =head1 COMPLEXITY OBSERVATIONS
653 Perl ops except sort are expected to be constant (relative to the op overhead).
655 =head2 Record item links
657 The query itself has indices available for all joins and filters and should
658 scale with sublinear with number of affected orderitems.
660 The rest of the code iterates through the result and call C<AM::convert_unit>,
661 which caches internally and is asymptotically constant.
665 C<partition_by> and C<intersect> both scale linearly. The first two scale with
666 input size, but use existing indices. The delivery order items query scales
667 with the nested loop anti join of the "NOT EXISTS" subquery, which takes most
668 of the time. For large databases omitting the order id filter may be faster.
670 Three partitions after that scale linearly. Building the doi_by_oe_id
671 multimap is O(n²) worst case, but will be linear for most real life data.
673 Iterating through the values of the partitions scales with the number of
674 elements in the multimap, and does not add additional complexity.
676 The sort and parallel walk are O(nlogn) for the length of the subdivisions,
677 whioch again makes square worst case, but much less than that in the general
680 =head3 Space requirements
682 In the current form the results of the 4 queries get fetched, and 4 of them are
683 held in memory at the same time. Three persistent structures are held:
684 C<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
685 orderitem. C<delivered> is calculated on demand and is a hash with an entry for
686 each order id of input.
688 Temporary structures are partitions of the orderitems, of which again the fill
689 up multi map between order id and delivery order items is potentially the
690 largest with square requierment worst case.
695 * delivery order identity
697 * rewrite to avoid division
698 * rewrite to avoid selectall for really large queries (no problem for up to 100k)
699 * calling mode or return to flag delivery_orders as delivered?
700 * add localized field white list
701 * reduce worst case square space requirement to linear
705 None yet, but there are most likely a lot in code this funky.
709 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>