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 oi2oe oi_qty delivered matches services_deliverable) ],
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 my $stock_item_links_query = <<'';
29 SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id,
30 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty AS doi_qty, p.unit AS doi_unit
32 INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems'
33 INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items'
34 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
35 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
36 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
37 INNER JOIN parts p ON p.id = doi.parts_id
38 WHERE oi.trans_id IN (%s)
39 ORDER BY oi.trans_id, oi.position
41 my $stock_fill_up_doi_query = <<'';
42 SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber,
43 (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty, p.unit
44 FROM delivery_order_items doi
45 INNER JOIN parts p ON p.id = doi.parts_id
46 INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id
47 INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
48 INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id
49 WHERE doi.delivery_order_id IN (
54 AND to_table = 'delivery_orders'
55 AND to_id = doi.delivery_order_id)
59 WHERE from_table = 'orderitems'
60 AND to_table = 'delivery_order_items'
64 my ($self, $data) = @_;
66 croak 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
68 $self->normalize_input($data);
70 return $self unless @{ $self->oe_ids };
72 $self->calculate_item_links;
77 sub calculate_item_links {
80 my @oe_ids = @{ $self->oe_ids };
82 my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query;
84 my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids);
86 my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids);
89 my $qty = $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
90 $self->shipped_qty->{$_->{oi_id}} //= 0;
91 $self->shipped_qty->{$_->{oi_id}} += $qty;
92 $self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
93 $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
95 push @{ $self->matches }, [ $_->{oi_id}, $_->{doi_id}, $qty, 1 ] if $self->keep_matches;
100 my ($self, $objects) = @_;
102 croak 'expecting array of objects' unless 'ARRAY' eq ref $objects;
104 my $shipped_qty = $self->shipped_qty;
106 for my $obj (@$objects) {
107 if ('SL::DB::OrderItem' eq ref $obj) {
108 $obj->{shipped_qty} = $shipped_qty->{$obj->id} //= 0;
109 $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
110 } elsif ('SL::DB::Order' eq ref $obj) {
111 # load all orderitems unless not already loaded
112 $obj->orderitems unless (defined $obj->{orderitems});
113 $self->write_to($obj->{orderitems});
114 if ($self->services_deliverable) {
115 $obj->{delivered} = all { $_->{delivered} } grep { !$_->{optional} } @{ $obj->{orderitems} };
117 $obj->{delivered} = all { $_->{delivered} } grep { !$_->{optional} && !$_->part->is_service } @{ $obj->{orderitems} };
120 die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to";
126 sub write_to_objects {
129 return unless @{ $self->oe_ids };
131 croak 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
133 $self->write_to($self->objects);
136 sub normalize_input {
137 my ($self, $data) = @_;
139 $data = [$data] if 'ARRAY' ne ref $data;
141 $self->objects_or_ids(!!blessed($data->[0]));
143 if ($self->objects_or_ids) {
144 croak 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
145 $self->objects($data);
147 croak 'object or reference in data while expecting ids' if any { ref($_) } @$data;
148 croak 'ids need to be numbers' if any { ! ($_ * 1) } @$data;
149 $self->oe_ids($data);
152 $self->shipped_qty({});
159 croak 'oe_ids not initialized in id mode' if !$self->objects_or_ids;
160 croak 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects;
161 croak 'objects need to be Order or OrderItem' if any { ref($_) !~ /^SL::DB::Order(?:Item)?$/ } @{ $self->objects };
163 [ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ]
166 sub init_dbh { SL::DB->client->dbh }
168 sub init_oi2oe { {} }
169 sub init_oi_qty { {} }
170 sub init_matches { [] }
175 for (keys %{ $self->oi_qty }) {
176 my $oe_id = $self->oi2oe->{$_};
178 $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_};
183 sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
185 sub init_services_deliverable {
187 if ($::form->{type} =~ m/^sales_/) {
188 $::instance_conf->get_sales_delivery_order_check_service;
189 } elsif ($::form->{type} =~ m/^purchase_/) {
190 $::instance_conf->get_purchase_delivery_order_check_service;
192 croak "wrong call, no customer or vendor object referenced";
204 SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty
208 use SL::Helper::ShippedQty;
210 my $helper = SL::Helper::ShippedQty->new(
211 require_stock_out => 0,
212 item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
215 $helper->calculate($order_object);
216 $helper->calculate(\@order_objects);
217 $helper->calculate($orderitem_object);
218 $helper->calculate(\@orderitem_objects);
219 $helper->calculate($oe_id);
220 $helper->calculate(\@oe_ids);
222 # if these are items set delivered and shipped_qty
223 # if these are orders, iterate through their items and set delivered on order
224 $helper->write_to($objects);
226 # if calculate was called with objects, you can use this shortcut:
227 $helper->write_to_objects;
229 # shipped_qtys by oi_id
230 my $shipped_qty = $helper->shipped_qty->{$oi->id};
233 my $delivered = $helper->delievered->{$oi->id};
235 # calculate and write_to can be chained:
236 my $helper = SL::Helper::ShippedQty->new->calculate($orders)->write_to_objects;
240 This module encapsulates the algorithm needed to compute the shipped qty for
241 orderitems (hopefully) correctly and efficiently for several use cases.
243 While this is used in object accessors, it can not be fast when called in a
244 loop over and over, so take advantage of batch processing when possible.
246 =head1 MOTIVATION AND PROBLEMS
248 The concept of shipped qty is sadly not as straight forward as it sounds at
249 first glance. Any correct implementation must in some way deal with the
256 When is an order shipped? For users that use the inventory it
257 will mean when a delivery order is stocked out. For those not using the
258 inventory it will mean when the delivery order is saved.
262 orderitems and oe entries may link to many of their counterparts in
263 delivery_orders. delivery_orders may be created from multiple orders. The
264 only constant is that a single entry in delivery_order_items has at most one
265 link from an orderitem.
269 Certain delivery orders might not be eligible for qty calculations if delivery
270 orders are used for other purposes.
274 Units need to be handled correctly
278 Negative positions must be taken into account. A negative delivery order is
279 assumed to be a RMA of sorts, but a negative order is not as straight forward.
283 Must be able to work with plain ids and Rose objects, and absolutely must
284 include a bulk mode to speed up multiple objects.
295 Creates a new helper object, $::form->{type} is mandatory.
301 =item * C<require_stock_out>
303 Boolean. If set, delivery orders must be stocked out to be considered
304 delivered. The default is a client setting.
307 =item * C<keep_matches>
309 Boolean. If set to true the internal matchings of OrderItems and
310 DeliveryOrderItems will be kept for later postprocessing, in case you need more
311 than this modules provides.
313 See C<matches> for the returned format.
318 =item C<calculate OBJECTS>
320 =item C<calculate IDS>
322 Do the main work. There must be a single argument: Either an id or an
323 C<SL::DB::Order> object, or an arrayref of one of these types.
325 Mixing ids and objects will generate an exception.
327 No return value. All internal errors will throw an exception.
329 =item C<write_to OBJECTS>
331 =item C<write_to_objects>
333 Save the C<shipped_qty> and C<delivered> state to the given objects. If
334 L</calculate> was called with objects, then C<write_to_objects> will use these.
336 C<shipped_qty> and C<delivered> will be directly infused into the objects
337 without calling the accessor for delivered. If you want to save afterwards,
338 you'll have to do that yourself.
340 C<shipped_qty> is guaranteed to be coerced to a number. If no delivery_order
341 was found it will be set to zero.
343 C<delivered> is guaranteed only to be the correct boolean value, but not
346 Note: C<write_to> will avoid loading unnecessary objects. This means if it is
347 called with an Order object that has not loaded its orderitems yet, only
348 C<delivered> will be set in the Order object. A subsequent C<<
349 $order->orderitems->[0]->{delivered} >> will return C<undef>, and C<<
350 $order->orderitems->[0]->shipped_qty >> will invoke another implicit
355 Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.
357 Unlike the result of C</write_to>, entries in C<shipped_qty> may be C<undef> if
358 linked elements were found.
362 Valid after L</calculate>. Returns a hashref with a delivered flag by order id.
366 Valid after L</calculate> with C<with_matches> set. Returns an arrayref of
367 individual matches. Each match is an arrayref with these fields:
373 The id of the OrderItem.
377 The id of the DeliveryOrderItem.
381 The qty that was matched between the two converted to the unit of the OrderItem.
385 A boolean flag indicating if this match was found with record_item links. If
386 false, the match was made in the fill up stage.
392 =head1 REPLACED FUNCTIONALITY
394 =head2 delivered mode
396 Originally used in mark_orders_if_delivered. Searches for orders associated
397 with a delivery order and evaluates whether those are delivered or not. No
398 detailed information is needed.
400 This is to be integrated into fast delivered check on the orders. The calling
401 convention for the delivery_order is not part of the scope of this module.
405 Originally used for printing delivery orders. Resolves for each position for
406 how much was originally ordered, and how much remains undelivered.
408 This one is likely to be dropped. The information only makes sense without
409 combined merge/split deliveries and is very fragile with unaccounted delivery
414 Same from the order perspective. Used for transitions to delivery orders, where
415 delivered qtys should be removed from positions. Also used each time a record
416 is rendered to show the shipped qtys. Also used to find orders that are not
419 Acceptable shortcuts would be the concepts fully shipped (for the order) and
420 providing already loaded objects.
422 =head2 Replaces the following functions
424 C<DO::get_shipped_qty>
426 C<SL::Controller::DeliveryPlan::calc_qtys>
428 C<SL::DB::OrderItem::shipped_qty>
430 C<SL::DB::OrderItem::delivered_qty>
434 this is the old get_shipped_qty algorithm by Martin for reference
436 in: oe_id, do_id, doctype, delivered flag
438 not needed with better signatures
440 load oe->do links for this id,
441 set oe_ids from those
448 2 load all orderitems for these oe_ids
451 set undelivered := qty
454 create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ]
456 1 load all oe->do links for these oe_ids
459 return all tuples so far
462 4 create dictionary for orderitems from [2] by id
464 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems
465 - optionally with doctype filter (identity filter)
467 # first pass for record_item_links
470 if link from orderitem exists and orderitem is in dictionary [4]
471 reduce qty_notdelivered in orderitem by doi.qty
472 keep link to do entry in orderitem
475 # second pass fill up
477 ignroe if from link exists or qty == 0
479 for orderitems from [2]:
480 next if notdelivered_qty == 0
481 if doi.parts_id == orderitem.parts_id:
482 if oi.notdelivered_qty < 0:
483 doi :+= -oi.notdelivered_qty,
484 oi.notdelivered_qty := 0
486 fi doi.qty < oi.notdelivered_qty:
488 oi.notdelivered_qty :-= doi.qty
490 doi.qty :-= oi.notdelivered_qty
491 oi.notdelivered_qty := 0
493 keep link to oi in doi
496 last wenn doi.qty <= 0
500 # post process for return
503 copy notdelivered from oe to ship{position}{notdelivered}
504 if !oe_id and do_id and delivered:
505 ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0
506 if !oe_id and do_id and !delivered:
508 ignore if do.id != doi.delivery_order_id
509 if oi in doi verlinkt und position bekannt:
510 addiere oi.qty zu doi.ordered_qty
511 addiere oi.notdelievered_qty zu doi.notdelivered_qty
518 in: orders, parameters
520 normalize orders to ids
522 # handle record_item links
523 retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested
524 for all record_links:
525 initialize shipped_qty for this doi to 0 if not yet seen
526 convert doi.qty to oi.unit
527 add normalized doi.qty to shipped_qty
531 abort if fill up is not requested
533 retrieve all orderitems matching the given order ids
534 retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory)
535 retrieve all record_links between orders and delivery_orders (1)
537 abort when no dois were found
539 create a partition of the delivery order items by do_id (2)
540 create empty mapping for delivery order items by order_id (3)
541 for all record_links from [1]:
542 add all matching doi from (2) to (3)
545 create a partition of the orderitems by item identity (4)
546 create a partition of the delivery order items by item identity (5)
548 for each identity in (4):
549 skip if no matching entries in (5)
551 create partition of all orderitems for this identity by order id (6)
552 for each sorted order id in [6]:
553 look up matching delivery order items by identity from [5] (7)
554 look up matching delivery order items by order id from [3] (8)
555 create stable sorted intersection between [7] and [8] (9)
557 sort the orderitems from (6) by position (10)
559 parallel walk through [9] and [10]:
560 missing qty := oi.qty - shipped_qty[oi]
563 next orderitem if missing_qty <= 0
564 next delivery order item if doi.qty == 0
566 min_qty := minimum(missing_qty, [doi.qty converted to oi.unit]
568 # transfer min_qty from doi.qty to shipped[qty]:
569 shipped_qty[oi] += min_qty
570 doi.qty -= [min_qty converted to doi.unit]
575 =head1 COMPLEXITY OBSERVATIONS
577 Perl ops except for sort are expected to be constant (relative to the op overhead).
579 =head2 Record item links
581 The query itself has indices available for all joins and filters and should
582 scale with sublinear with the number of affected orderitems.
584 The rest of the code iterates through the result and calls C<AM::convert_unit>,
585 which caches internally and is asymptotically constant.
589 C<partition_by> and C<intersect> both scale linearly. The first two scale with
590 input size, but use existing indices. The delivery order items query scales
591 with the nested loop anti join of the "NOT EXISTS" subquery, which takes most
592 of the time. For large databases omitting the order id filter may be faster.
594 Three partitions after that scale linearly. Building the doi_by_oe_id
595 multimap is O(n²) worst case, but will be linear for most real life data.
597 Iterating through the values of the partitions scales with the number of
598 elements in the multimap, and does not add additional complexity.
600 The sort and parallel walk are O(nlogn) for the length of the subdivisions,
601 which again makes square worst case, but much less than that in the general
604 =head3 Space requirements
606 In the current form the results of the 4 queries get fetched, and 4 of them are
607 held in memory at the same time. Three persistent structures are held:
608 C<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
609 orderitem. C<delivered> is calculated on demand and is a hash with an entry for
610 each order id of input.
612 Temporary structures are partitions of the orderitems, of which again the fill
613 up multi map between order id and delivery order items is potentially the
614 largest with square requierment worst case.
619 * delivery order identity
621 * rewrite to avoid division
622 * rewrite to avoid selectall for really large queries (no problem for up to 100k)
623 * calling mode or return to flag delivery_orders as delivered?
624 * add localized field white list
625 * reduce worst case square space requirement to linear
629 None yet, but there are most likely a lot in code this funky.
633 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>