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);
11 use List::UtilsBy qw(partition_by);
13 use Rose::Object::MakeMethods::Generic (
14 'scalar' => [ qw(objects objects_or_ids shipped_qty ) ],
15 'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered) ],
18 my $no_stock_item_links_query = <<'';
19 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
21 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
22 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
23 WHERE oi.trans_id IN (%s)
24 ORDER BY oi.trans_id, oi.position
26 # oi not item linked. takes about 250ms for 100k hits
27 my $fill_up_oi_query = <<'';
28 SELECT oi.id, oi.trans_id, oi.position, oi.parts_id, oi.description, oi.reqdate, oi.serialnumber, oi.qty, oi.unit
30 WHERE oi.trans_id IN (%s)
31 ORDER BY oi.trans_id, oi.position
33 # doi linked by record, but not by items; 250ms for 100k hits
34 my $no_stock_fill_up_doi_query = <<'';
35 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, doi.qty, doi.unit
36 FROM delivery_order_items doi
37 WHERE doi.delivery_order_id IN (
42 AND to_table = 'delivery_orders'
43 AND to_id = doi.delivery_order_id)
47 WHERE from_table = 'orderitems'
48 AND to_table = 'delivery_order_items'
51 my $stock_item_links_query = <<'';
52 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
54 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
55 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
56 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
57 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
58 INNER JOIN parts p ON p.id = doi.parts_id
59 WHERE oi.trans_id IN (%s)
60 ORDER BY oi.trans_id, oi.position
62 my $stock_fill_up_doi_query = <<'';
63 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, i.qty, i.unit
64 FROM delivery_order_items doi
65 INNER JOIN parts p ON p.id = doi.parts_id
66 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
67 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
68 WHERE doi.delivery_order_id IN (
73 AND to_table = 'delivery_orders'
74 AND to_id = doi.delivery_order_id)
78 WHERE from_table = 'orderitems'
79 AND to_table = 'delivery_order_items'
82 my $oe_do_record_links = <<'';
87 AND to_table = 'delivery_orders'
90 my ($self, $data) = @_;
92 die 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
94 return if !$data || ('ARRAY' eq ref $data && !@$data);
96 $self->normalize_input($data);
98 return unless @{ $self->oe_ids };
100 $self->calculate_item_links;
101 $self->calculate_fill_up if $self->fill_up;
104 sub calculate_item_links {
107 my @oe_ids = @{ $self->oe_ids };
109 my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query;
111 my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids);
113 my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids);
116 $self->shipped_qty->{$_->{oi_id}} //= 0;
117 $self->shipped_qty->{$_->{oi_id}} += $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
118 $self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
119 $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
126 grep { $seen{$_}++ } @$a1, @$a2;
129 sub calculate_fill_up {
132 my @oe_ids = @{ $self->oe_ids };
134 my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query;
136 my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids);
137 my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids);
138 my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids);
140 my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids);
144 my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids);
145 my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids);
147 my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi;
148 my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi;
150 push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} }
151 for grep { exists $doi_by_id{$_->{to_id}} } @$rl;
153 my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi;
155 for my $match (sort keys %oi_by_identity) {
156 next unless exists $doi_by_identity{$match};
158 my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} };
159 for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) {
160 next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id});
162 # sorting should be quite fast here, because there are usually only a handful of matches
163 next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} };
165 # parallel walk through sorted oi/doi entries
166 my $oi_i = my $doi_i = 0;
167 my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]);
168 while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) {
169 $oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}};
170 $doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty};
172 my $factor = AM->convert_unit($doi->{unit} => $oi->{unit});
173 my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor);
175 # min_qty should never be 0 now. the first part triggers the first next,
176 # the second triggers the second next and factor must not be 0
177 # but it would lead to an infinite loop, so catch that.
178 die 'panic! invalid shipping quantity' unless $min_qty;
180 $self->shipped_qty->{$oi->{id}} += $min_qty;
181 $doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding
186 $self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
187 $self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
191 my ($self, $objects) = @_;
193 die 'expecting array of objects' unless 'ARRAY' eq ref $objects;
195 my $shipped_qty = $self->shipped_qty;
197 for my $obj (@$objects) {
198 if ('SL::DB::OrderItem' eq ref $obj) {
199 $obj->{shipped_qty} = $shipped_qty->{$obj->id};
200 $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
201 } elsif ('SL::DB::Order' eq ref $obj) {
202 if (exists $obj->{orderitems}) {
203 $self->write_to($obj->{orderitems});
204 $obj->{delivered} = all { $_->{delivered} } @{ $obj->{orderitems} };
206 # don't force a load on items. just compute by oe_id directly
207 $obj->{delivered} = $self->delivered->{$obj->id};
210 die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to";
215 sub write_to_objects {
218 die 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
220 $self->write_to($self->objects);
224 my ($self, $row) = @_;
226 join $;, map $row->{$_}, @{ $self->item_identity_fields };
229 sub normalize_input {
230 my ($self, $data) = @_;
232 $data = [$data] if 'ARRAY' ne ref $data;
234 $self->objects_or_ids(!!blessed($data->[0]));
236 if ($self->objects_or_ids) {
237 die 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
238 $self->objects($data);
240 die 'object or reference in data while expecting ids' if any { ref($_) } @$data;
241 $self->oe_ids($data);
244 $self->shipped_qty({});
250 die 'oe_ids not initialized in id mode' if !$self->objects_or_ids;
251 die 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects;
253 [ map { $_->id } @{ $self->objects } ]
256 sub init_dbh { SL::DB->client->dbh }
258 sub init_oi2oe { {} }
259 sub init_oi_qty { {} }
263 for (keys %{ $self->oi_qty }) {
264 my $oe_id = $self->oi2oe->{$_};
266 $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_};
271 sub init_require_stock_out { 0 }
272 sub init_item_identity_fields { [ qw(parts_id description reqdate serialnumber) ] }
273 sub init_fill_up { 1 }
283 SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty
287 use SL::Helper::ShippedQty;
289 my $helper = SL::Helper::ShippedQty->new(
291 require_stock_out => 0,
292 item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
296 $helper->calculate($order_object);
297 $helper->calculate(\@order_objects);
298 $helper->calculate($oe_id);
299 $helper->calculate(\@oe_ids);
301 # if these are items set elivered and shipped_qty
302 # if these are orders, iterate through their items and set delivered on order
303 $helper->write_to($objects);
305 # if calculate was called with objects, you can use this shortcut:
306 $helper->write_to_objects;
308 # shipped_qtys by oi_id
309 my $shipped_qtys_by_oi_id = $helper->shipped_qtys;
312 my $delivered_by_oe_id = $helper->delievered;
316 This module encapsulates the algorithm needed to compute the shipped qty for
317 orderitems (hopefully) correctly and efficiently for several use cases.
319 While this is used in object accessors, it can not be fast when called in a
320 loop over and over, so take advantage of batch processing when possible.
322 =head1 MOTIVATION AND PROBLEMS
324 The concept of shipped qty is sadly not as straight forward as it sounds on
325 first glance. Any correct implementation must in some way deal with the
332 When is an order shipped? For users that use the inventory it
333 will mean when a delivery order is stocked out. For those not using the
334 inventory it will mean when the delivery order is saved.
338 How to find the correct matching elements. After the changes
339 to record item links it's natural to assume that each position is linked, but
340 for various reasons this might not be the case. Positions that are not linked
341 in database need to be matched by marching.
345 Double links need to be accounted for (these can stem from buggy code).
349 orderitems and oe entries may link to many of their counterparts in
350 delivery_orders. delivery_orders my be created from multiple orders. The
351 only constant is that a single entry in delivery_order_items has at most one
352 link from an orderitem.
356 For the fill up case the identity of positions is not clear. The naive approach
357 is just the same part, but description, charge number, reqdate and qty can all
358 be part of the identity of a position for finding shipped matches.
362 Certain delivery orders might not be eligable for qty calculations if delivery
363 orders are used for other purposes.
367 Units need to be handled correctly
371 Negative positions must be taken into account. A negative delivery order is
372 assumed to be a RMA of sorts, but a negative order is not as straight forward.
376 Must be able to work with plain ids and Rose objects, and absolutely must
377 include a bulk mode to speed up multiple objects.
388 Creates a new helper object. PARAMS may include:
392 =item * C<require_stock_out>
394 Boolean. If set, delivery orders must be stocked out to be considered
395 delivered. The default is a client setting.
399 Boolean. If set, unlinked delivery order items will be used to fill up
400 undelivered order items. Not needed in newer installations. The default is a
403 =item * C<item_identity_fields ARRAY>
405 If set, the fields are used to compute the identity of matching positions. The
406 default is a client setting. Possible values include:
412 =item * C<description>
416 =item * C<serialnumber>
422 =item C<calculate OBJECTS>
424 =item C<calculate IDS>
426 Do the main work. There must be a single argument: Either an id or an
427 C<SL::DB::Order> object, or an arrayref of one of these types.
429 Mixing ids and objects will generate an exception.
431 No return value. All internal errors will throw an exception.
433 =item C<write_to OBJECTS>
435 =item C<write_to_objects>
437 Save the C<shipped_qty> and C<delivered> state to the objects. If L</calculate>
438 was called with objects, then C<write_to_objects> will use these.
442 Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.
446 Valid after L</calculate>. Returns a hasref with delivered flag by order id.
450 =head1 REPLACED FUNCTIONALITY
452 =head2 delivered mode
454 Originally used in mark_orders_if_delivered. Searches for orders associated
455 with a delivery order and evaluates whether those are delivered or not. No
456 detailed information is needed.
458 This is to be integrated into fast delivered check on the orders. The calling
459 convention for the delivery_order is not scope of this module.
463 Originally used for printing delivery orders. Resolves for each position for
464 much was originally ordered, and how much remains undelivered.
466 This one is likely to be dropped. The information makes only sense without
467 combined merge/split deliveries and is very fragile with unaccounted delivery
472 Same from order perspective. Used for transitions to delivery orders, where
473 delivered qtys should be removed from positions. Also used each time a record
474 is rendered to show the shipped qtys. Also used to find orders that are not
477 Acceptable shortcuts would be the concepts fully shipped (for the order) and
478 providing already loaded objects.
480 =head2 Replaces the following functions
482 C<DO::get_shipped_qty>
484 C<SL::Controller::DeliveryPlan::calc_qtys>
486 C<SL::DB::OrderItem::shipped_qty>
488 C<SL::DB::OrderItem::delivered_qty>
492 this is the old get_shipped_qty algorithm by Martin for reference
494 in: oe_id, do_id, doctype, delivered flag
496 not needed with better signatures
498 load oe->do links for this id,
499 set oe_ids from those
506 2 load all orderitems for these oe_ids
509 set undelivered := qty
512 create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ]
514 1 load all oe->do links for these oe_ids
517 return all tuples so far
520 4 create dictionary for orderitems from [2] by id
522 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems
523 - optionally with doctype filter (identity filter)
525 # first pass for record_item_links
528 if link from orderitem exists and orderitem is in dictionary [4]
529 reduce qty_notdelivered in orderitem by doi.qty
530 keep link to do entry in orderitem
533 # second pass fill up
535 ignroe if from link exists or qty == 0
537 for orderitems from [2]:
538 next if notdelivered_qty == 0
539 if doi.parts_id == orderitem.parts_id:
540 if oi.notdelivered_qty < 0:
541 doi :+= -oi.notdelivered_qty,
542 oi.notdelivered_qty := 0
544 fi doi.qty < oi.notdelivered_qty:
546 oi.notdelivered_qty :-= doi.qty
548 doi.qty :-= oi.notdelivered_qty
549 oi.notdelivered_qty := 0
551 keep link to oi in doi
554 last wenn doi.qty <= 0
558 # post process for return
561 copy notdelivered from oe to ship{position}{notdelivered}
562 if !oe_id and do_id and delivered:
563 ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0
564 if !oe_id and do_id and !delivered:
566 ignore if do.id != doi.delivery_order_id
567 if oi in doi verlinkt und position bekannt:
568 addiere oi.qty zu doi.ordered_qty
569 addiere oi.notdelievered_qty zu doi.notdelivered_qty
576 in: orders, parameters
578 normalize orders to ids
580 # handle record_item links
581 retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested
582 for all record_links:
583 initialize shipped_qty for this doi to 0 if not yet seen
584 convert doi.qty to oi.unit
585 add normalized doi.qty to shipped_qty
589 abort if fill up is not requested
591 retrieve all orderitems matching the given order ids
592 retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory)
593 retrieve all record_links between orders and delivery_orders (1)
595 abort when no dois were found
597 create a partition of the delivery order items by do_id (2)
598 create empty mapping for delivery order items by order_id (3)
599 for all record_links from [1]:
600 add all matching doi from (2) to (3)
603 create a partition of the orderitems by item identity (4)
604 create a partition of the delivery order items by item identity (5)
606 for each identity in (4):
607 skip if no matching entries in (5)
609 create partition of all orderitems for this identity by order id (6)
610 for each sorted order id in [6]:
611 look up matching delivery order items by identity from [5] (7)
612 look up matching delivery order items by order id from [3] (8)
613 create stable sorted intersection between [7] and [8] (9)
615 sort the orderitems from (6) by position (10)
617 parallel walk through [9] and [10]:
618 missing qty := oi.qty - shipped_qty[oi]
621 next orderitem if missing_qty <= 0
622 next delivery order item if doi.qty == 0
624 min_qty := minimum(missing_qty, [doi.qty converted to oi.unit]
626 # transfer min_qty from doi.qty to shipped[qty]:
627 shipped_qty[oi] += min_qty
628 doi.qty -= [min_qty converted to doi.unit]
633 =head1 COMPLEXITY OBSERVATIONS
635 Perl ops except sort are expected to be constant (relative to the op overhead).
637 =head2 Record item links
639 The query itself has indices available for all joins and filters and should
640 scale with sublinear with number of affected orderitems.
642 The rest of the code iterates through the result and call C<AM::convert_unit>,
643 which caches internally and is asymptotically constant.
647 C<partition_by> and C<intersect> both scale linearly. The first two scale with
648 input size, but use existing indices. The delivery order items query scales
649 with the nested loop anti join of the "NOT EXISTS" subquery, which takes most
650 of the time. For large databases omitting the order id filter may be faster.
652 Three partitions after that scale linearly. Building the doi_by_oe_id
653 multimap is O(n²) worst case, but will be linear for most real life data.
655 Iterating through the values of the partitions scales with the number of
656 elements in the multimap, and does not add additional complexity.
658 The sort and parallel walk are O(nlogn) for the length of the subdivisions,
659 whioch again makes square worst case, but much less than that in the general
662 =head3 Space requirements
664 In the current form the results of the 4 queries get fetched, and 4 of them are
665 held in memory at the same time. Three persistent structures are held:
666 C<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
667 orderitem. C<delivered> is calculated on demand and is a hash with an entry for
668 each order id of input.
670 Temporary structures are partitions of the orderitems, of which again the fill
671 up multi map between order id and delivery order items is potentially the
672 largest with square requierment worst case.
677 * delivery order identity
679 * rewrite to avoid division
680 * rewrite to avoid selectall for really large queries (no problem for up to 100k)
681 * calling mode or return to flag delivery_orders as delivered?
682 * add localized field white list
683 * reduce worst case square space requirement to linear
687 None yet, but there are most likely a lot in code this funky.
691 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>