1 package SL::Helper::ShippedQty;
4 use parent qw(Rose::Object);
7 use Scalar::Util qw(blessed);
8 use List::Util qw(min);
9 use List::MoreUtils qw(any all uniq);
10 use List::UtilsBy qw(partition_by);
12 use SL::DBUtils qw(selectall_hashref_query selectall_as_map);
13 use SL::Locale::String qw(t8);
15 use Rose::Object::MakeMethods::Generic (
16 'scalar' => [ qw(objects objects_or_ids shipped_qty keep_matches) ],
17 'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered matches) ],
20 my $no_stock_item_links_query = <<'';
21 SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id, doi.qty AS doi_qty, doi.unit AS doi_unit
23 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
24 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
25 WHERE oi.trans_id IN (%s)
26 ORDER BY oi.trans_id, oi.position
28 # oi not item linked. takes about 250ms for 100k hits
29 # obsolete since 3.5.6
30 my $fill_up_oi_query = <<'';
31 SELECT oi.id, oi.trans_id, oi.position, oi.parts_id, oi.description, oi.reqdate, oi.serialnumber, oi.qty, oi.unit
33 WHERE oi.trans_id IN (%s)
34 ORDER BY oi.trans_id, oi.position
36 # doi linked by record, but not by items; 250ms for 100k hits
37 # obsolete since 3.5.6
38 my $no_stock_fill_up_doi_query = <<'';
39 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, doi.qty, doi.unit
40 FROM delivery_order_items doi
41 WHERE doi.delivery_order_id IN (
46 AND to_table = 'delivery_orders'
47 AND to_id = doi.delivery_order_id)
51 WHERE from_table = 'orderitems'
52 AND to_table = 'delivery_order_items'
55 my $stock_item_links_query = <<'';
56 SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id,
57 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty AS doi_qty, p.unit AS doi_unit
59 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
60 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
61 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
62 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
63 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
64 INNER JOIN parts p ON p.id = doi.parts_id
65 WHERE oi.trans_id IN (%s)
66 ORDER BY oi.trans_id, oi.position
68 my $stock_fill_up_doi_query = <<'';
69 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber,
70 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty, p.unit
71 FROM delivery_order_items doi
72 INNER JOIN parts p ON p.id = doi.parts_id
73 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
74 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
75 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
76 WHERE doi.delivery_order_id IN (
81 AND to_table = 'delivery_orders'
82 AND to_id = doi.delivery_order_id)
86 WHERE from_table = 'orderitems'
87 AND to_table = 'delivery_order_items'
90 my $oe_do_record_links = <<'';
95 AND to_table = 'delivery_orders'
97 my @known_item_identity_fields = qw(parts_id description reqdate serialnumber);
98 my %item_identity_fields = (
99 parts_id => t8('Part'),
100 description => t8('Description'),
101 reqdate => t8('Reqdate'),
102 serialnumber => t8('Serial Number'),
106 my ($self, $data) = @_;
108 croak 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
110 $self->normalize_input($data);
112 return $self unless @{ $self->oe_ids };
114 $self->calculate_item_links;
115 $self->calculate_fill_up if $self->fill_up;
120 sub calculate_item_links {
123 my @oe_ids = @{ $self->oe_ids };
125 my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query;
127 my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids);
129 my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids);
132 my $qty = $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
133 $self->shipped_qty->{$_->{oi_id}} //= 0;
134 $self->shipped_qty->{$_->{oi_id}} += $qty;
135 $self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
136 $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
138 push @{ $self->matches }, [ $_->{oi_id}, $_->{doi_id}, $qty, 1 ] if $self->keep_matches;
145 grep { $seen{$_}++ } @$a1, @$a2;
148 sub calculate_fill_up {
151 my @oe_ids = @{ $self->oe_ids };
153 my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query;
155 my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids);
156 my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids);
157 my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids);
159 my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids);
163 my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids);
164 my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids);
166 my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi;
167 my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi;
169 push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} }
170 for grep { exists $doi_by_id{$_->{to_id}} } @$rl;
172 my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi;
174 for my $match (sort keys %oi_by_identity) {
175 next unless exists $doi_by_identity{$match};
177 my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} };
178 for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) {
179 next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id});
181 # sorting should be quite fast here, because there are usually only a handful of matches
182 next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} };
184 # parallel walk through sorted oi/doi entries
185 my $oi_i = my $doi_i = 0;
186 my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]);
187 while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) {
188 $oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}};
189 $doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty};
191 my $factor = AM->convert_unit($doi->{unit} => $oi->{unit});
192 my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor);
194 # min_qty should never be 0 now. the first part triggers the first next,
195 # the second triggers the second next and factor must not be 0
196 # but it would lead to an infinite loop, so catch that.
197 die 'panic! invalid shipping quantity' unless $min_qty;
199 $self->shipped_qty->{$oi->{id}} += $min_qty;
200 $doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding
201 push @{ $self->matches }, [ $oi->{id}, $doi->{id}, $min_qty, 0 ] if $self->keep_matches;
206 $self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
207 $self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
211 my ($self, $objects) = @_;
213 croak 'expecting array of objects' unless 'ARRAY' eq ref $objects;
215 my $shipped_qty = $self->shipped_qty;
217 for my $obj (@$objects) {
218 if ('SL::DB::OrderItem' eq ref $obj) {
219 $obj->{shipped_qty} = $shipped_qty->{$obj->id} //= 0;
220 $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
221 } elsif ('SL::DB::Order' eq ref $obj) {
222 if (defined $obj->{orderitems}) {
223 $self->write_to($obj->{orderitems});
224 $obj->{delivered} = all { $_->{delivered} } grep { !$_->{optional} } @{ $obj->{orderitems} };
226 # don't force a load on items. just compute by oe_id directly
227 $obj->{delivered} = $self->delivered->{$obj->id};
230 die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to";
236 sub write_to_objects {
239 return unless @{ $self->oe_ids };
241 croak 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
243 $self->write_to($self->objects);
247 my ($self, $row) = @_;
249 join $;, map $row->{$_}, @{ $self->item_identity_fields };
252 sub normalize_input {
253 my ($self, $data) = @_;
255 $data = [$data] if 'ARRAY' ne ref $data;
257 $self->objects_or_ids(!!blessed($data->[0]));
259 if ($self->objects_or_ids) {
260 croak 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
261 $self->objects($data);
263 croak 'object or reference in data while expecting ids' if any { ref($_) } @$data;
264 croak 'ids need to be numbers' if any { ! ($_ * 1) } @$data;
265 $self->oe_ids($data);
268 $self->shipped_qty({});
271 sub available_item_identity_fields {
272 map { [ $_ => $item_identity_fields{$_} ] } @known_item_identity_fields;
278 croak 'oe_ids not initialized in id mode' if !$self->objects_or_ids;
279 croak 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects;
280 croak 'objects need to be Order or OrderItem' if any { ref($_) !~ /^SL::DB::Order(?:Item)?$/ } @{ $self->objects };
282 [ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ]
285 sub init_dbh { SL::DB->client->dbh }
287 sub init_oi2oe { {} }
288 sub init_oi_qty { {} }
289 sub init_matches { [] }
293 for (keys %{ $self->oi_qty }) {
294 my $oe_id = $self->oi2oe->{$_};
296 $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_};
301 sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
302 sub init_item_identity_fields { [ grep $item_identity_fields{$_}, @{ $::instance_conf->get_shipped_qty_item_identity_fields } ] }
303 sub init_fill_up { $::instance_conf->get_shipped_qty_fill_up }
313 SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty
317 use SL::Helper::ShippedQty;
319 my $helper = SL::Helper::ShippedQty->new(
321 require_stock_out => 0,
322 item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
325 $helper->calculate($order_object);
326 $helper->calculate(\@order_objects);
327 $helper->calculate($orderitem_object);
328 $helper->calculate(\@orderitem_objects);
329 $helper->calculate($oe_id);
330 $helper->calculate(\@oe_ids);
332 # if these are items set delivered and shipped_qty
333 # if these are orders, iterate through their items and set delivered on order
334 $helper->write_to($objects);
336 # if calculate was called with objects, you can use this shortcut:
337 $helper->write_to_objects;
339 # shipped_qtys by oi_id
340 my $shipped_qty = $helper->shipped_qty->{$oi->id};
343 my $delivered = $helper->delievered->{$oi->id};
345 # calculate and write_to can be chained:
346 my $helper = SL::Helper::ShippedQty->new->calculate($orders)->write_to_objects;
350 This module encapsulates the algorithm needed to compute the shipped qty for
351 orderitems (hopefully) correctly and efficiently for several use cases.
353 While this is used in object accessors, it can not be fast when called in a
354 loop over and over, so take advantage of batch processing when possible.
356 =head1 MOTIVATION AND PROBLEMS
358 The concept of shipped qty is sadly not as straight forward as it sounds at
359 first glance. Any correct implementation must in some way deal with the
366 When is an order shipped? For users that use the inventory it
367 will mean when a delivery order is stocked out. For those not using the
368 inventory it will mean when the delivery order is saved.
372 How to find the correct matching elements. After the changes
373 to record item links it's natural to assume that each position is linked, but
374 for various reasons this might not be the case. Positions that are not linked
375 in the database need to be matched by marching.
379 Double links need to be accounted for (these can stem from buggy code).
383 orderitems and oe entries may link to many of their counterparts in
384 delivery_orders. delivery_orders my be created from multiple orders. The
385 only constant is that a single entry in delivery_order_items has at most one
386 link from an orderitem.
390 For the fill up case the identity of positions is not clear. The naive approach
391 is just the same part, but description, charge number, reqdate and qty can all
392 be part of the identity of a position for finding shipped matches.
396 Certain delivery orders might not be eligible for qty calculations if delivery
397 orders are used for other purposes.
401 Units need to be handled correctly
405 Negative positions must be taken into account. A negative delivery order is
406 assumed to be a RMA of sorts, but a negative order is not as straight forward.
410 Must be able to work with plain ids and Rose objects, and absolutely must
411 include a bulk mode to speed up multiple objects.
422 Creates a new helper object. PARAMS may include:
426 =item * C<require_stock_out>
428 Boolean. If set, delivery orders must be stocked out to be considered
429 delivered. The default is a client setting.
433 Boolean. If set, unlinked delivery order items will be used to fill up
434 undelivered order items. Not needed in newer installations. The default is a
437 =item * C<item_identity_fields ARRAY>
439 If set, the fields are used to compute the identity of matching positions. The
440 default is a client setting. Possible values include:
446 =item * C<description>
450 =item * C<serialnumber>
454 =item * C<keep_matches>
456 Boolean. If set to true the internal matchings of OrderItems and
457 DeliveryOrderItems will be kept for later postprocessing, in case you need more
458 than this modules provides.
460 See C<matches> for the returned format.
464 =item C<calculate OBJECTS>
466 =item C<calculate IDS>
468 Do the main work. There must be a single argument: Either an id or an
469 C<SL::DB::Order> object, or an arrayref of one of these types.
471 Mixing ids and objects will generate an exception.
473 No return value. All internal errors will throw an exception.
475 =item C<write_to OBJECTS>
477 =item C<write_to_objects>
479 Save the C<shipped_qty> and C<delivered> state to the given objects. If
480 L</calculate> was called with objects, then C<write_to_objects> will use these.
482 C<shipped_qty> and C<delivered> will be directly infused into the objects
483 without calling the accessor for delivered. If you want to save afterwards,
484 you'll have to do that yourself.
486 C<shipped_qty> is guaranteed to be coerced to a number. If no delivery_order
487 was found it will be set to zero.
489 C<delivered> is guaranteed only to be the correct boolean value, but not
492 Note: C<write_to> will avoid loading unnecessary objects. This means if it is
493 called with an Order object that has not loaded its orderitems yet, only
494 C<delivered> will be set in the Order object. A subsequent C<<
495 $order->orderitems->[0]->{delivered} >> will return C<undef>, and C<<
496 $order->orderitems->[0]->shipped_qty >> will invoke another implicit
501 Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.
503 Unlike the result of C</write_to>, entries in C<shipped_qty> may be C<undef> if
504 linked elements were found.
508 Valid after L</calculate>. Returns a hashref with a delivered flag by order id.
512 Valid after L</calculate> with C<with_matches> set. Returns an arrayref of
513 individual matches. Each match is an arrayref with these fields:
519 The id of the OrderItem.
523 The id of the DeliveryOrderItem.
527 The qty that was matched between the two converted to the unit of the OrderItem.
531 A boolean flag indicating if this match was found with record_item links. If
532 false, the match was made in the fill up stage.
538 =head1 REPLACED FUNCTIONALITY
540 =head2 delivered mode
542 Originally used in mark_orders_if_delivered. Searches for orders associated
543 with a delivery order and evaluates whether those are delivered or not. No
544 detailed information is needed.
546 This is to be integrated into fast delivered check on the orders. The calling
547 convention for the delivery_order is not part of the scope of this module.
551 Originally used for printing delivery orders. Resolves for each position for
552 how much was originally ordered, and how much remains undelivered.
554 This one is likely to be dropped. The information only makes sense without
555 combined merge/split deliveries and is very fragile with unaccounted delivery
560 Same from the order perspective. Used for transitions to delivery orders, where
561 delivered qtys should be removed from positions. Also used each time a record
562 is rendered to show the shipped qtys. Also used to find orders that are not
565 Acceptable shortcuts would be the concepts fully shipped (for the order) and
566 providing already loaded objects.
568 =head2 Replaces the following functions
570 C<DO::get_shipped_qty>
572 C<SL::Controller::DeliveryPlan::calc_qtys>
574 C<SL::DB::OrderItem::shipped_qty>
576 C<SL::DB::OrderItem::delivered_qty>
580 this is the old get_shipped_qty algorithm by Martin for reference
582 in: oe_id, do_id, doctype, delivered flag
584 not needed with better signatures
586 load oe->do links for this id,
587 set oe_ids from those
594 2 load all orderitems for these oe_ids
597 set undelivered := qty
600 create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ]
602 1 load all oe->do links for these oe_ids
605 return all tuples so far
608 4 create dictionary for orderitems from [2] by id
610 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems
611 - optionally with doctype filter (identity filter)
613 # first pass for record_item_links
616 if link from orderitem exists and orderitem is in dictionary [4]
617 reduce qty_notdelivered in orderitem by doi.qty
618 keep link to do entry in orderitem
621 # second pass fill up
623 ignroe if from link exists or qty == 0
625 for orderitems from [2]:
626 next if notdelivered_qty == 0
627 if doi.parts_id == orderitem.parts_id:
628 if oi.notdelivered_qty < 0:
629 doi :+= -oi.notdelivered_qty,
630 oi.notdelivered_qty := 0
632 fi doi.qty < oi.notdelivered_qty:
634 oi.notdelivered_qty :-= doi.qty
636 doi.qty :-= oi.notdelivered_qty
637 oi.notdelivered_qty := 0
639 keep link to oi in doi
642 last wenn doi.qty <= 0
646 # post process for return
649 copy notdelivered from oe to ship{position}{notdelivered}
650 if !oe_id and do_id and delivered:
651 ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0
652 if !oe_id and do_id and !delivered:
654 ignore if do.id != doi.delivery_order_id
655 if oi in doi verlinkt und position bekannt:
656 addiere oi.qty zu doi.ordered_qty
657 addiere oi.notdelievered_qty zu doi.notdelivered_qty
664 in: orders, parameters
666 normalize orders to ids
668 # handle record_item links
669 retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested
670 for all record_links:
671 initialize shipped_qty for this doi to 0 if not yet seen
672 convert doi.qty to oi.unit
673 add normalized doi.qty to shipped_qty
677 abort if fill up is not requested
679 retrieve all orderitems matching the given order ids
680 retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory)
681 retrieve all record_links between orders and delivery_orders (1)
683 abort when no dois were found
685 create a partition of the delivery order items by do_id (2)
686 create empty mapping for delivery order items by order_id (3)
687 for all record_links from [1]:
688 add all matching doi from (2) to (3)
691 create a partition of the orderitems by item identity (4)
692 create a partition of the delivery order items by item identity (5)
694 for each identity in (4):
695 skip if no matching entries in (5)
697 create partition of all orderitems for this identity by order id (6)
698 for each sorted order id in [6]:
699 look up matching delivery order items by identity from [5] (7)
700 look up matching delivery order items by order id from [3] (8)
701 create stable sorted intersection between [7] and [8] (9)
703 sort the orderitems from (6) by position (10)
705 parallel walk through [9] and [10]:
706 missing qty := oi.qty - shipped_qty[oi]
709 next orderitem if missing_qty <= 0
710 next delivery order item if doi.qty == 0
712 min_qty := minimum(missing_qty, [doi.qty converted to oi.unit]
714 # transfer min_qty from doi.qty to shipped[qty]:
715 shipped_qty[oi] += min_qty
716 doi.qty -= [min_qty converted to doi.unit]
721 =head1 COMPLEXITY OBSERVATIONS
723 Perl ops except for sort are expected to be constant (relative to the op overhead).
725 =head2 Record item links
727 The query itself has indices available for all joins and filters and should
728 scale with sublinear with the number of affected orderitems.
730 The rest of the code iterates through the result and calls C<AM::convert_unit>,
731 which caches internally and is asymptotically constant.
735 C<partition_by> and C<intersect> both scale linearly. The first two scale with
736 input size, but use existing indices. The delivery order items query scales
737 with the nested loop anti join of the "NOT EXISTS" subquery, which takes most
738 of the time. For large databases omitting the order id filter may be faster.
740 Three partitions after that scale linearly. Building the doi_by_oe_id
741 multimap is O(n²) worst case, but will be linear for most real life data.
743 Iterating through the values of the partitions scales with the number of
744 elements in the multimap, and does not add additional complexity.
746 The sort and parallel walk are O(nlogn) for the length of the subdivisions,
747 which again makes square worst case, but much less than that in the general
750 =head3 Space requirements
752 In the current form the results of the 4 queries get fetched, and 4 of them are
753 held in memory at the same time. Three persistent structures are held:
754 C<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
755 orderitem. C<delivered> is calculated on demand and is a hash with an entry for
756 each order id of input.
758 Temporary structures are partitions of the orderitems, of which again the fill
759 up multi map between order id and delivery order items is potentially the
760 largest with square requierment worst case.
765 * delivery order identity
767 * rewrite to avoid division
768 * rewrite to avoid selectall for really large queries (no problem for up to 100k)
769 * calling mode or return to flag delivery_orders as delivered?
770 * add localized field white list
771 * reduce worst case square space requirement to linear
775 None yet, but there are most likely a lot in code this funky.
779 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>