From bae8f672cb7a0c7062b0570d3c91e7f9ac756f1f Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Thu, 29 Jun 2017 16:48:26 +0200 Subject: [PATCH] ShippedQty Helper --- SL/Helper/ShippedQty.pm | 693 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 693 insertions(+) create mode 100644 SL/Helper/ShippedQty.pm diff --git a/SL/Helper/ShippedQty.pm b/SL/Helper/ShippedQty.pm new file mode 100644 index 000000000..38a450eb2 --- /dev/null +++ b/SL/Helper/ShippedQty.pm @@ -0,0 +1,693 @@ +package SL::Helper::ShippedQty; + +use strict; +use parent qw(Rose::Object); + +use SL::AM; +use Scalar::Util qw(blessed); +use SL::DBUtils qw(selectall_hashref_query selectall_as_map); +use List::Util qw(min); +use List::MoreUtils qw(any all); +use List::UtilsBy qw(partition_by); + +use Rose::Object::MakeMethods::Generic ( + 'scalar' => [ qw(objects objects_or_ids shipped_qty ) ], + 'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered) ], +); + +my $no_stock_item_links_query = <<''; + 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 + FROM record_links rl + INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems' + INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items' + WHERE oi.trans_id IN (%s) + ORDER BY oi.trans_id, oi.position + +# oi not item linked. takes about 250ms for 100k hits +my $fill_up_oi_query = <<''; + SELECT oi.id, oi.trans_id, oi.position, oi.parts_id, oi.description, oi.reqdate, oi.serialnumber, oi.qty, oi.unit + FROM orderitems oi + WHERE oi.trans_id IN (%s) + ORDER BY oi.trans_id, oi.position + +# doi linked by record, but not by items; 250ms for 100k hits +my $no_stock_fill_up_doi_query = <<''; + SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, doi.qty, doi.unit + FROM delivery_order_items doi + WHERE doi.delivery_order_id IN ( + SELECT to_id + FROM record_links + WHERE from_id IN (%s) + AND from_table = 'oe' + AND to_table = 'delivery_orders' + AND to_id = doi.delivery_order_id) + AND NOT EXISTS ( + SELECT NULL + FROM record_links + WHERE from_table = 'orderitems' + AND to_table = 'delivery_order_items' + AND to_id = doi.id) + +my $stock_item_links_query = <<''; + 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 + FROM record_links rl + INNER JOIN orderitems oi ON oi.id = rl.from_id AND rl.from_table = 'orderitems' + INNER JOIN delivery_order_items doi ON doi.id = rl.to_id AND rl.to_table = 'delivery_order_items' + INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id + INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id + INNER JOIN parts p ON p.id = doi.parts_id + WHERE oi.trans_id IN (%s) + ORDER BY oi.trans_id, oi.position + +my $stock_fill_up_doi_query = <<''; + SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, i.qty, i.unit + FROM delivery_order_items doi + INNER JOIN parts p ON p.id = doi.parts_id + INNER JOIN delivery_order_items_stock dois ON dois.delivery_order_item_id = doi.id + INNER JOIN inventory i ON dois.id = i.delivery_order_items_stock_id + WHERE doi.delivery_order_id IN ( + SELECT to_id + FROM record_links + WHERE from_id IN (%s) + AND from_table = 'oe' + AND to_table = 'delivery_orders' + AND to_id = doi.delivery_order_id) + AND NOT EXISTS ( + SELECT NULL + FROM record_links + WHERE from_table = 'orderitems' + AND to_table = 'delivery_order_items' + AND to_id = doi.id) + +my $oe_do_record_links = <<''; + SELECT from_id, to_id + FROM record_links + WHERE from_id IN (%s) + AND from_table = 'oe' + AND to_table = 'delivery_orders' + +sub calculate { + my ($self, $data) = @_; + + die 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_; + + return if !$data || ('ARRAY' eq ref $data && !@$data); + + $self->normalize_input($data); + + return unless @{ $self->oe_ids }; + + $self->calculate_item_links; + $self->calculate_fill_up if $self->fill_up; +} + +sub calculate_item_links { + my ($self) = @_; + + my @oe_ids = @{ $self->oe_ids }; + + my $item_links_query = $self->require_stock_out ? $stock_item_links_query : $no_stock_item_links_query; + + my $query = sprintf $item_links_query, join (', ', ('?')x @oe_ids); + + my $data = selectall_hashref_query($::form, $self->dbh, $query, @oe_ids); + + for (@$data) { + $self->shipped_qty->{$_->{oi_id}} //= 0; + $self->shipped_qty->{$_->{oi_id}} += $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit}); + $self->oi2oe->{$_->{oi_id}} = $_->{trans_id}; + $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty}; + } +} + +sub _intersect { + my ($a1, $a2) = @_; + my %seen; + grep { $seen{$_}++ } @$a1, @$a2; +} + +sub calculate_fill_up { + my ($self) = @_; + + my @oe_ids = @{ $self->oe_ids }; + + my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query; + + my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids); + my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids); + my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids); + + my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids); + + return unless @$oi; + + my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids); + my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids); + + my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi; + my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi; + my %doi_by_trans_id; + push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} } + for grep { exists $doi_by_id{$_->{to_id}} } @$rl; + + my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi; + + for my $match (sort keys %oi_by_identity) { + next unless exists $doi_by_identity{$match}; + + my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} }; + for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) { + next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id}); + + # sorting should be quite fast here, because there are usually only a handful of matches + next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} }; + + # parallel walk through sorted oi/doi entries + my $oi_i = my $doi_i = 0; + my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]); + while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) { + $oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}}; + $doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty}; + + my $factor = AM->convert_unit($doi->{unit} => $oi->{unit}); + my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor); + + # min_qty should never be 0 now. the first part triggers the first next, + # the second triggers the second next and factor must not be 0 + # but it would lead to an infinite loop, so catch that. + die 'panic! invalid shipping quantity' unless $min_qty; + + $self->shipped_qty->{$oi->{id}} += $min_qty; + $doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding + } + } + } + + $self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi; + $self->oi_qty->{$_->{id}} = $_->{qty} for @$oi; +} + +sub write_to { + my ($self, $objects) = @_; + + die 'expecting array of objects' unless 'ARRAY' eq ref $objects; + + my $shipped_qty = $self->shipped_qty; + + for my $obj (@$objects) { + if ('SL::DB::OrderItem' eq ref $obj) { + $obj->{shipped_qty} = $shipped_qty->{$obj->id}; + $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty; + } elsif ('SL::DB::Order' eq ref $obj) { + if (exists $obj->{orderitems}) { + $self->write_to($obj->{orderitems}); + $obj->{delivered} = all { $_->{delivered} } @{ $obj->{orderitems} }; + } else { + # don't force a load on items. just compute by oe_id directly + $obj->{delivered} = $self->delivered->{$obj->id}; + } + } else { + die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to"; + } + } +} + +sub write_to_objects { + my ($self) = @_; + + die 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids; + + $self->write_to($self->objects); +} + +sub item_identity { + my ($self, $row) = @_; + + join $;, map $row->{$_}, @{ $self->item_identity_fields }; +} + +sub normalize_input { + my ($self, $data) = @_; + + $data = [$data] if 'ARRAY' ne ref $data; + + $self->objects_or_ids(!!blessed($data->[0])); + + if ($self->objects_or_ids) { + die 'unblessed object in data while expecting object' if any { !blessed($_) } @$data; + $self->objects($data); + } else { + die 'object or reference in data while expecting ids' if any { ref($_) } @$data; + $self->oe_ids($data); + } + + $self->shipped_qty({}); +} + +sub init_oe_ids { + my ($self) = @_; + + die 'oe_ids not initialized in id mode' if !$self->objects_or_ids; + die 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects; + + [ map { $_->id } @{ $self->objects } ] +} + +sub init_dbh { SL::DB->client->dbh } + +sub init_oi2oe { {} } +sub init_oi_qty { {} } +sub init_delivered { + my ($self) = @_; + my $d = { }; + for (keys %{ $self->oi_qty }) { + my $oe_id = $self->oi2oe->{$_}; + $d->{$oe_id} //= 1; + $d->{$oe_id} &&= $self->shipped_qty->{$_} == $self->oi_qty->{$_}; + } + $d; +} + +sub init_require_stock_out { 0 } +sub init_item_identity_fields { [ qw(parts_id description reqdate serialnumber) ] } +sub init_fill_up { 1 } + +1; + +__END__ + +=encoding utf-8 + +=head1 NAME + +SL::Helper::ShippedQty - Algorithmic module for calculating shipped qty + +=head1 SYNOPSIS + + use SL::Helper::ShippedQty; + + my $helper = SL::Helper::ShippedQty->new( + fill_up => 0, + require_stock_out => 0, + item_identity_fields => [ qw(parts_id description reqdate serialnumber) ], + set_delivered => 1, + ); + + $helper->calculate($order_object); + $helper->calculate(\@order_objects); + $helper->calculate($oe_id); + $helper->calculate(\@oe_ids); + + # if these are items set elivered and shipped_qty + # if these are orders, iterate through their items and set delivered on order + $helper->write_to($objects); + + # if calculate was called with objects, you can use this shortcut: + $helper->write_to_objects; + + # shipped_qtys by oi_id + my $shipped_qtys_by_oi_id = $helper->shipped_qtys; + + # delivered by oe_id + my $delivered_by_oe_id = $helper->delievered; + +=head1 DESCRIPTION + +This module encapsulates the algorithm needed to compute the shipped qty for +orderitems (hopefully) correctly and efficiently for several use cases. + +While this is used in object accessors, it can not be fast when called in a +loop over and over, so take advantage of batch processing when possible. + +=head1 MOTIVATION AND PROBLEMS + +The concept of shipped qty is sadly not as straight forward as it sounds on +first glance. Any correct implementation must in some way deal with the +following problems. + +=over 4 + +=item * + +When is an order shipped? For users that use the inventory it +will mean when a delivery order is stocked out. For those not using the +inventory it will mean when the delivery order is saved. + +=item * + +How to find the correct matching elements. After the changes +to record item links it's natural to assume that each position is linked, but +for various reasons this might not be the case. Positions that are not linked +in database need to be matched by marching. + +=item * + +Double links need to be accounted for (these can stem from buggy code). + +=item * + +orderitems and oe entries may link to many of their counterparts in +delivery_orders. delivery_orders my be created from multiple orders. The +only constant is that a single entry in delivery_order_items has at most one +link from an orderitem. + +=item * + +For the fill up case the identity of positions is not clear. The naive approach +is just the same part, but description, charge number, reqdate and qty can all +be part of the identity of a position for finding shipped matches. + +=item * + +Certain delivery orders might not be eligable for qty calculations if delivery +orders are used for other purposes. + +=item * + +Units need to be handled correctly + +=item * + +Negative positions must be taken into account. A negative delivery order is +assumed to be a RMA of sorts, but a negative order is not as straight forward. + +=item * + +Must be able to work with plain ids and Rose objects, and absolutely must +include a bulk mode to speed up multiple objects. + +=back + + +=head1 FUNCTIONS + +=over 4 + +=item C + +Creates a new helper object. PARAMS may include: + +=over 4 + +=item * C + +Boolean. If set, delivery orders must be stocked out to be considered +delivered. The default is a client setting. + +=item * C + +Boolean. If set, unlinked delivery order items will be used to fill up +undelivered order items. Not needed in newer installations. The default is a +client setting. + +=item * C + +If set, the fields are used to compute the identity of matching positions. The +default is a client setting. Possible values include: + +=over 4 + +=item * C + +=item * C + +=item * C + +=item * C + +=back + +=back + +=item C + +=item C + +Do the main work. There must be a single argument: Either an id or an +C object, or an arrayref of one of these types. + +Mixing ids and objects will generate an exception. + +No return value. All internal errors will throw an exception. + +=item C + +=item C + +Save the C and C state to the objects. If L +was called with objects, then C will use these. + +=item C + +Valid after L. Returns a hasref with shipped qtys by orderitems id. + +=item C + +Valid after L. Returns a hasref with delivered flag by order id. + +=back + +=head1 REPLACED FUNCTIONALITY + +=head2 delivered mode + +Originally used in mark_orders_if_delivered. Searches for orders associated +with a delivery order and evaluates whether those are delivered or not. No +detailed information is needed. + +This is to be integrated into fast delivered check on the orders. The calling +convention for the delivery_order is not scope of this module. + +=head2 do_mode + +Originally used for printing delivery orders. Resolves for each position for +much was originally ordered, and how much remains undelivered. + +This one is likely to be dropped. The information makes only sense without +combined merge/split deliveries and is very fragile with unaccounted delivery +orders. + +=head2 oe mode + +Same from order perspective. Used for transitions to delivery orders, where +delivered qtys should be removed from positions. Also used each time a record +is rendered to show the shipped qtys. Also used to find orders that are not +fully delivered. + +Acceptable shortcuts would be the concepts fully shipped (for the order) and +providing already loaded objects. + +=head2 Replaces the following functions + +C + +C + +C + +C + +=head1 OLD ALGORITHM + +this is the old get_shipped_qty algorithm by Martin for reference + + in: oe_id, do_id, doctype, delivered flag + + not needed with better signatures + if do_id: + load oe->do links for this id, + set oe_ids from those + fi + if oe_id: + set oe_ids to this + + return if no oe_ids; + + 2 load all orderitems for these oe_ids + for orderitem: + nomalize qty + set undelivered := qty + end + + create tuple: [ position => qty_ordered, qty_not_delivered, orderitem.id ] + + 1 load all oe->do links for these oe_ids + + if no links: + return all tuples so far + fi + + 4 create dictionary for orderitems from [2] by id + + 3 load all delivery_order_items for do_ids from [1], with recorditem_links from orderitems + - optionally with doctype filter (identity filter) + + # first pass for record_item_links + for dois: + normalize qty + if link from orderitem exists and orderitem is in dictionary [4] + reduce qty_notdelivered in orderitem by doi.qty + keep link to do entry in orderitem + end + + # second pass fill up + for dois: + ignroe if from link exists or qty == 0 + + for orderitems from [2]: + next if notdelivered_qty == 0 + if doi.parts_id == orderitem.parts_id: + if oi.notdelivered_qty < 0: + doi :+= -oi.notdelivered_qty, + oi.notdelivered_qty := 0 + else: + fi doi.qty < oi.notdelivered_qty: + doi.qty := 0 + oi.notdelivered_qty :-= doi.qty + else: + doi.qty :-= oi.notdelivered_qty + oi.notdelivered_qty := 0 + fi + keep link to oi in doi + fi + fi + last wenn doi.qty <= 0 + end + end + + # post process for return + + if oe_id: + copy notdelivered from oe to ship{position}{notdelivered} + if !oe_id and do_id and delivered: + ship.{oi.trans_id}.delivered := oi.notdelivered_qty <= 0 + if !oe_id and do_id and !delivered: + for all doi: + ignore if do.id != doi.delivery_order_id + if oi in doi verlinkt und position bekannt: + addiere oi.qty zu doi.ordered_qty + addiere oi.notdelievered_qty zu doi.notdelivered_qty + fi + end + fi + +=head1 NEW ALGORITHM + + in: orders, parameters + + normalize orders to ids + + # handle record_item links + retrieve record_links entries with inner joins on orderitems, delivery_orderitems and stock/inventory if requested + for all record_links: + initialize shipped_qty for this doi to 0 if not yet seen + convert doi.qty to oi.unit + add normalized doi.qty to shipped_qty + end + + # handle fill up + abort if fill up is not requested + + retrieve all orderitems matching the given order ids + retrieve all doi with a link to the given order ids but without item link (and optionally with stock/inventory) + retrieve all record_links between orders and delivery_orders (1) + + abort when no dois were found + + create a partition of the delivery order items by do_id (2) + create empty mapping for delivery order items by order_id (3) + for all record_links from [1]: + add all matching doi from (2) to (3) + end + + create a partition of the orderitems by item identity (4) + create a partition of the delivery order items by item identity (5) + + for each identity in (4): + skip if no matching entries in (5) + + create partition of all orderitems for this identity by order id (6) + for each sorted order id in [6]: + look up matching delivery order items by identity from [5] (7) + look up matching delivery order items by order id from [3] (8) + create stable sorted intersection between [7] and [8] (9) + + sort the orderitems from (6) by position (10) + + parallel walk through [9] and [10]: + missing qty := oi.qty - shipped_qty[oi] + + + next orderitem if missing_qty <= 0 + next delivery order item if doi.qty == 0 + + min_qty := minimum(missing_qty, [doi.qty converted to oi.unit] + + # transfer min_qty from doi.qty to shipped[qty]: + shipped_qty[oi] += min_qty + doi.qty -= [min_qty converted to doi.unit] + end + end + end + +=head1 COMPLEXITY OBSERVATIONS + +Perl ops except sort are expected to be constant (relative to the op overhead). + +=head2 Record item links + +The query itself has indices available for all joins and filters and should +scale with sublinear with number of affected orderitems. + +The rest of the code iterates through the result and call C, +which caches internally and is asymptotically constant. + +=head2 Fill up + +C and C both scale linearly. The first two scale with +input size, but use existing indices. The delivery order items query scales +with the nested loop anti join of the "NOT EXISTS" subquery, which takes most +of the time. For large databases omitting the order id filter may be faster. + +Three partitions after that scale linearly. Building the doi_by_oe_id +multimap is O(n²) worst case, but will be linear for most real life data. + +Iterating through the values of the partitions scales with the number of +elements in the multimap, and does not add additional complexity. + +The sort and parallel walk are O(nlogn) for the length of the subdivisions, +whioch again makes square worst case, but much less than that in the general +case. + +=head3 Space requirements + +In the current form the results of the 4 queries get fetched, and 4 of them are +held in memory at the same time. Three persistent structures are held: +C, C, and C - all hashes with one entry for each +orderitem. C is calculated on demand and is a hash with an entry for +each order id of input. + +Temporary structures are partitions of the orderitems, of which again the fill +up multi map between order id and delivery order items is potentially the +largest with square requierment worst case. + + +=head1 TODO + + * delivery order identity + * test stocked + * rewrite to avoid division + * rewrite to avoid selectall for really large queries (no problem for up to 100k) + * calling mode or return to flag delivery_orders as delivered? + * add localized field white list + * reduce worst case square space requirement to linear + +=head1 BUGS + +None yet, but there are most likely a lot in code this funky. + +=head1 AUTHOR + +Sven Schöling Es.schoeling@linet-services.deE + +=cut -- 2.20.1