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,
54 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty AS doi_qty, p.unit AS doi_unit
56 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
57 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
58 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
59 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
60 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
61 INNER JOIN parts p ON p.id = doi.parts_id
62 WHERE oi.trans_id IN (%s)
63 ORDER BY oi.trans_id, oi.position
65 my $stock_fill_up_doi_query = <<'';
66 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber,
67 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty, p.unit
68 FROM delivery_order_items doi
69 INNER JOIN parts p ON p.id = doi.parts_id
70 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
71 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
72 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
73 WHERE doi.delivery_order_id IN (
78 AND to_table = 'delivery_orders'
79 AND to_id = doi.delivery_order_id)
83 WHERE from_table = 'orderitems'
84 AND to_table = 'delivery_order_items'
87 my $oe_do_record_links = <<'';
92 AND to_table = 'delivery_orders'
94 my @known_item_identity_fields = qw(parts_id description reqdate serialnumber);
95 my %item_identity_fields = (
96 parts_id => t8('Part'),
97 description => t8('Description'),
98 reqdate => t8('Reqdate'),
99 serialnumber => t8('Serial Number'),
103 my ($self, $data) = @_;
105 die 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
107 return if !$data || ('ARRAY' eq ref $data && !@$data);
109 $self->normalize_input($data);
111 return unless @{ $self->oe_ids };
113 $self->calculate_item_links;
114 $self->calculate_fill_up if $self->fill_up;
119 sub calculate_item_links {
122 my @oe_ids = @{ $self->oe_ids };
124 my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query;
126 my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids);
128 my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids);
131 $self->shipped_qty->{$_->{oi_id}} //= 0;
132 $self->shipped_qty->{$_->{oi_id}} += $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
133 $self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
134 $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
141 grep { $seen{$_}++ } @$a1, @$a2;
144 sub calculate_fill_up {
147 my @oe_ids = @{ $self->oe_ids };
149 my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query;
151 my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids);
152 my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids);
153 my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids);
155 my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids);
159 my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids);
160 my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids);
162 my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi;
163 my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi;
165 push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} }
166 for grep { exists $doi_by_id{$_->{to_id}} } @$rl;
168 my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi;
170 for my $match (sort keys %oi_by_identity) {
171 next unless exists $doi_by_identity{$match};
173 my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} };
174 for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) {
175 next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id});
177 # sorting should be quite fast here, because there are usually only a handful of matches
178 next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} };
180 # parallel walk through sorted oi/doi entries
181 my $oi_i = my $doi_i = 0;
182 my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]);
183 while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) {
184 $oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}};
185 $doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty};
187 my $factor = AM->convert_unit($doi->{unit} => $oi->{unit});
188 my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor);
190 # min_qty should never be 0 now. the first part triggers the first next,
191 # the second triggers the second next and factor must not be 0
192 # but it would lead to an infinite loop, so catch that.
193 die 'panic! invalid shipping quantity' unless $min_qty;
195 $self->shipped_qty->{$oi->{id}} += $min_qty;
196 $doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding
201 $self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
202 $self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
206 my ($self, $objects) = @_;
208 die 'expecting array of objects' unless 'ARRAY' eq ref $objects;
210 my $shipped_qty = $self->shipped_qty;
212 for my $obj (@$objects) {
213 if ('SL::DB::OrderItem' eq ref $obj) {
214 $obj->{shipped_qty} = $shipped_qty->{$obj->id} //= 0;
215 $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
216 } elsif ('SL::DB::Order' eq ref $obj) {
217 if (exists $obj->{orderitems}) {
218 $self->write_to($obj->{orderitems});
219 $obj->{delivered} = all { $_->{delivered} } @{ $obj->{orderitems} };
221 # don't force a load on items. just compute by oe_id directly
222 $obj->{delivered} = $self->delivered->{$obj->id};
225 die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to";
231 sub write_to_objects {
234 die 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
236 $self->write_to($self->objects);
240 my ($self, $row) = @_;
242 join $;, map $row->{$_}, @{ $self->item_identity_fields };
245 sub normalize_input {
246 my ($self, $data) = @_;
248 $data = [$data] if 'ARRAY' ne ref $data;
250 $self->objects_or_ids(!!blessed($data->[0]));
252 if ($self->objects_or_ids) {
253 die 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
254 $self->objects($data);
256 die 'object or reference in data while expecting ids' if any { ref($_) } @$data;
257 $self->oe_ids($data);
260 $self->shipped_qty({});
263 sub available_item_identity_fields {
264 map { [ $_ => $item_identity_fields{$_} ] } @known_item_identity_fields;
270 die 'oe_ids not initialized in id mode' if !$self->objects_or_ids;
271 die 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects;
272 die 'objects need to be Order or OrderItem' if any { ref($_) !~ /^SL::DB::Order(?:Item)?$/ } @{ $self->objects };
274 [ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ]
277 sub init_dbh { SL::DB->client->dbh }
279 sub init_oi2oe { {} }
280 sub init_oi_qty { {} }
284 for (keys %{ $self->oi_qty }) {
285 my $oe_id = $self->oi2oe->{$_};
287 $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_};
292 sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
293 sub init_item_identity_fields { [ grep $item_identity_fields{$_}, @{ $::instance_conf->get_shipped_qty_item_identity_fields } ] }
294 sub init_fill_up { $::instance_conf->get_shipped_qty_fill_up }
304 SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty
308 use SL::Helper::ShippedQty;
310 my $helper = SL::Helper::ShippedQty->new(
312 require_stock_out => 0,
313 item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
316 $helper->calculate($order_object);
317 $helper->calculate(\@order_objects);
318 $helper->calculate($orderitem_object);
319 $helper->calculate(\@orderitem_objects);
320 $helper->calculate($oe_id);
321 $helper->calculate(\@oe_ids);
323 # if these are items set elivered and shipped_qty
324 # if these are orders, iterate through their items and set delivered on order
325 $helper->write_to($objects);
327 # if calculate was called with objects, you can use this shortcut:
328 $helper->write_to_objects;
330 # shipped_qtys by oi_id
331 my $shipped_qty = $helper->shipped_qty->{$oi->id};
334 my $delivered = $helper->delievered->{$oi->id};
336 # calculate and write_to can be chained:
337 my $helper = SL::Helper::ShippedQty->new->calculate($orders)->write_to_objects;
341 This module encapsulates the algorithm needed to compute the shipped qty for
342 orderitems (hopefully) correctly and efficiently for several use cases.
344 While this is used in object accessors, it can not be fast when called in a
345 loop over and over, so take advantage of batch processing when possible.
347 =head1 MOTIVATION AND PROBLEMS
349 The concept of shipped qty is sadly not as straight forward as it sounds at
350 first glance. Any correct implementation must in some way deal with the
357 When is an order shipped? For users that use the inventory it
358 will mean when a delivery order is stocked out. For those not using the
359 inventory it will mean when the delivery order is saved.
363 How to find the correct matching elements. After the changes
364 to record item links it's natural to assume that each position is linked, but
365 for various reasons this might not be the case. Positions that are not linked
366 in the database need to be matched by marching.
370 Double links need to be accounted for (these can stem from buggy code).
374 orderitems and oe entries may link to many of their counterparts in
375 delivery_orders. delivery_orders my be created from multiple orders. The
376 only constant is that a single entry in delivery_order_items has at most one
377 link from an orderitem.
381 For the fill up case the identity of positions is not clear. The naive approach
382 is just the same part, but description, charge number, reqdate and qty can all
383 be part of the identity of a position for finding shipped matches.
387 Certain delivery orders might not be eligible for qty calculations if delivery
388 orders are used for other purposes.
392 Units need to be handled correctly
396 Negative positions must be taken into account. A negative delivery order is
397 assumed to be a RMA of sorts, but a negative order is not as straight forward.
401 Must be able to work with plain ids and Rose objects, and absolutely must
402 include a bulk mode to speed up multiple objects.
413 Creates a new helper object. PARAMS may include:
417 =item * C<require_stock_out>
419 Boolean. If set, delivery orders must be stocked out to be considered
420 delivered. The default is a client setting.
424 Boolean. If set, unlinked delivery order items will be used to fill up
425 undelivered order items. Not needed in newer installations. The default is a
428 =item * C<item_identity_fields ARRAY>
430 If set, the fields are used to compute the identity of matching positions. The
431 default is a client setting. Possible values include:
437 =item * C<description>
441 =item * C<serialnumber>
447 =item C<calculate OBJECTS>
449 =item C<calculate IDS>
451 Do the main work. There must be a single argument: Either an id or an
452 C<SL::DB::Order> object, or an arrayref of one of these types.
454 Mixing ids and objects will generate an exception.
456 No return value. All internal errors will throw an exception.
458 =item C<write_to OBJECTS>
460 =item C<write_to_objects>
462 Save the C<shipped_qty> and C<delivered> state to the given objects. If
463 L</calculate> was called with objects, then C<write_to_objects> will use these.
465 C<shipped_qty> and C<delivered> will be directly infused into the objects
466 without calling the accessor for delivered. If you want to save afterwards,
467 you'll have to do that yourself.
469 C<shipped_qty> is guaranteed to be coerced to a number. If no delivery_order
470 was found it will be set to zero.
472 C<delivered> is guaranteed only to be the correct boolean value, but not
477 Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.
479 Unlike the result of C</write_to>, entries in C<shipped_qty> may be C<undef> if
480 linked elements were found.
484 Valid after L</calculate>. Returns a hashref with a delivered flag by order id.
488 =head1 REPLACED FUNCTIONALITY
490 =head2 delivered mode
492 Originally used in mark_orders_if_delivered. Searches for orders associated
493 with a delivery order and evaluates whether those are delivered or not. No
494 detailed information is needed.
496 This is to be integrated into fast delivered check on the orders. The calling
497 convention for the delivery_order is not part of the scope of this module.
501 Originally used for printing delivery orders. Resolves for each position for
502 how much was originally ordered, and how much remains undelivered.
504 This one is likely to be dropped. The information only makes sense without
505 combined merge/split deliveries and is very fragile with unaccounted delivery
510 Same from the order perspective. Used for transitions to delivery orders, where
511 delivered qtys should be removed from positions. Also used each time a record
512 is rendered to show the shipped qtys. Also used to find orders that are not
515 Acceptable shortcuts would be the concepts fully shipped (for the order) and
516 providing already loaded objects.
518 =head2 Replaces the following functions
520 C<DO::get_shipped_qty>
522 C<SL::Controller::DeliveryPlan::calc_qtys>
524 C<SL::DB::OrderItem::shipped_qty>
526 C<SL::DB::OrderItem::delivered_qty>
530 this is the old get_shipped_qty algorithm by Martin for reference
532 in: oe_id, do_id, doctype, delivered flag
534 not needed with better signatures
536 load oe->do links for this id,
537 set oe_ids from those
544 2 load all orderitems for these oe_ids
547 set undelivered := qty
550 create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ]
552 1 load all oe->do links for these oe_ids
555 return all tuples so far
558 4 create dictionary for orderitems from [2] by id
560 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems
561 - optionally with doctype filter (identity filter)
563 # first pass for record_item_links
566 if link from orderitem exists and orderitem is in dictionary [4]
567 reduce qty_notdelivered in orderitem by doi.qty
568 keep link to do entry in orderitem
571 # second pass fill up
573 ignroe if from link exists or qty == 0
575 for orderitems from [2]:
576 next if notdelivered_qty == 0
577 if doi.parts_id == orderitem.parts_id:
578 if oi.notdelivered_qty < 0:
579 doi :+= -oi.notdelivered_qty,
580 oi.notdelivered_qty := 0
582 fi doi.qty < oi.notdelivered_qty:
584 oi.notdelivered_qty :-= doi.qty
586 doi.qty :-= oi.notdelivered_qty
587 oi.notdelivered_qty := 0
589 keep link to oi in doi
592 last wenn doi.qty <= 0
596 # post process for return
599 copy notdelivered from oe to ship{position}{notdelivered}
600 if !oe_id and do_id and delivered:
601 ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0
602 if !oe_id and do_id and !delivered:
604 ignore if do.id != doi.delivery_order_id
605 if oi in doi verlinkt und position bekannt:
606 addiere oi.qty zu doi.ordered_qty
607 addiere oi.notdelievered_qty zu doi.notdelivered_qty
614 in: orders, parameters
616 normalize orders to ids
618 # handle record_item links
619 retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested
620 for all record_links:
621 initialize shipped_qty for this doi to 0 if not yet seen
622 convert doi.qty to oi.unit
623 add normalized doi.qty to shipped_qty
627 abort if fill up is not requested
629 retrieve all orderitems matching the given order ids
630 retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory)
631 retrieve all record_links between orders and delivery_orders (1)
633 abort when no dois were found
635 create a partition of the delivery order items by do_id (2)
636 create empty mapping for delivery order items by order_id (3)
637 for all record_links from [1]:
638 add all matching doi from (2) to (3)
641 create a partition of the orderitems by item identity (4)
642 create a partition of the delivery order items by item identity (5)
644 for each identity in (4):
645 skip if no matching entries in (5)
647 create partition of all orderitems for this identity by order id (6)
648 for each sorted order id in [6]:
649 look up matching delivery order items by identity from [5] (7)
650 look up matching delivery order items by order id from [3] (8)
651 create stable sorted intersection between [7] and [8] (9)
653 sort the orderitems from (6) by position (10)
655 parallel walk through [9] and [10]:
656 missing qty := oi.qty - shipped_qty[oi]
659 next orderitem if missing_qty <= 0
660 next delivery order item if doi.qty == 0
662 min_qty := minimum(missing_qty, [doi.qty converted to oi.unit]
664 # transfer min_qty from doi.qty to shipped[qty]:
665 shipped_qty[oi] += min_qty
666 doi.qty -= [min_qty converted to doi.unit]
671 =head1 COMPLEXITY OBSERVATIONS
673 Perl ops except for sort are expected to be constant (relative to the op overhead).
675 =head2 Record item links
677 The query itself has indices available for all joins and filters and should
678 scale with sublinear with the number of affected orderitems.
680 The rest of the code iterates through the result and calls C<AM::convert_unit>,
681 which caches internally and is asymptotically constant.
685 C<partition_by> and C<intersect> both scale linearly. The first two scale with
686 input size, but use existing indices. The delivery order items query scales
687 with the nested loop anti join of the "NOT EXISTS" subquery, which takes most
688 of the time. For large databases omitting the order id filter may be faster.
690 Three partitions after that scale linearly. Building the doi_by_oe_id
691 multimap is O(n²) worst case, but will be linear for most real life data.
693 Iterating through the values of the partitions scales with the number of
694 elements in the multimap, and does not add additional complexity.
696 The sort and parallel walk are O(nlogn) for the length of the subdivisions,
697 which again makes square worst case, but much less than that in the general
700 =head3 Space requirements
702 In the current form the results of the 4 queries get fetched, and 4 of them are
703 held in memory at the same time. Three persistent structures are held:
704 C<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
705 orderitem. C<delivered> is calculated on demand and is a hash with an entry for
706 each order id of input.
708 Temporary structures are partitions of the orderitems, of which again the fill
709 up multi map between order id and delivery order items is potentially the
710 largest with square requierment worst case.
715 * delivery order identity
717 * rewrite to avoid division
718 * rewrite to avoid selectall for really large queries (no problem for up to 100k)
719 * calling mode or return to flag delivery_orders as delivered?
720 * add localized field white list
721 * reduce worst case square space requirement to linear
725 None yet, but there are most likely a lot in code this funky.
729 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>