X-Git-Url: http://wagnertech.de/git?p=kivitendo-erp.git;a=blobdiff_plain;f=SL%2FHelper%2FShippedQty.pm;fp=SL%2FHelper%2FShippedQty.pm;h=6b878a972d54f43a75fe2b936c58c70728798a80;hp=0000000000000000000000000000000000000000;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44 diff --git a/SL/Helper/ShippedQty.pm b/SL/Helper/ShippedQty.pm new file mode 100644 index 000000000..6b878a972 --- /dev/null +++ b/SL/Helper/ShippedQty.pm @@ -0,0 +1,613 @@ +package SL::Helper::ShippedQty; + +use strict; +use parent qw(Rose::Object); + +use Carp; +use Scalar::Util qw(blessed); +use List::Util qw(min); +use List::MoreUtils qw(any all uniq); +use List::UtilsBy qw(partition_by); +use SL::AM; +use SL::DBUtils qw(selectall_hashref_query selectall_as_map); +use SL::Locale::String qw(t8); + +use Rose::Object::MakeMethods::Generic ( + 'scalar' => [ qw(objects objects_or_ids shipped_qty keep_matches) ], + 'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out oi2oe oi_qty delivered matches services_deliverable) ], +); + +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.id AS doi_id, 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 + +my $stock_item_links_query = <<''; + SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id, + (CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * 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_orders doe ON doe.id = doi.delivery_order_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 + INNER JOIN parts p ON p.id = doi.parts_id + WHERE oi.trans_id IN (%s) + ORDER BY oi.trans_id, oi.position + +sub calculate { + my ($self, $data) = @_; + + croak 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_; + + $self->normalize_input($data); + + return $self unless @{ $self->oe_ids }; + + $self->calculate_item_links; + + $self; +} + +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) { + my $qty = $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit}); + $self->shipped_qty->{$_->{oi_id}} //= 0; + $self->shipped_qty->{$_->{oi_id}} += $qty; + $self->oi2oe->{$_->{oi_id}} = $_->{trans_id}; + $self->oi_qty->{$_->{oi_id}} = $_->{oi_qty}; + + push @{ $self->matches }, [ $_->{oi_id}, $_->{doi_id}, $qty, 1 ] if $self->keep_matches; + } +} + +sub write_to { + my ($self, $objects) = @_; + + croak '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} //= 0; + $obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty; + } elsif ('SL::DB::Order' eq ref $obj) { + # load all orderitems unless not already loaded + $obj->orderitems unless (defined $obj->{orderitems}); + $self->write_to($obj->{orderitems}); + if ($self->services_deliverable) { + $obj->{delivered} = all { $_->{delivered} } grep { !$_->{optional} } @{ $obj->{orderitems} }; + } else { + $obj->{delivered} = all { $_->{delivered} } grep { !$_->{optional} && !$_->part->is_service } @{ $obj->{orderitems} }; + } + } else { + die "unknown reference '@{[ ref $obj ]}' for @{[ __PACKAGE__ ]}::write_to"; + } + } + $self; +} + +sub write_to_objects { + my ($self) = @_; + + return unless @{ $self->oe_ids }; + + croak '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 normalize_input { + my ($self, $data) = @_; + + $data = [$data] if 'ARRAY' ne ref $data; + + $self->objects_or_ids(!!blessed($data->[0])); + + if ($self->objects_or_ids) { + croak 'unblessed object in data while expecting object' if any { !blessed($_) } @$data; + $self->objects($data); + } else { + croak 'object or reference in data while expecting ids' if any { ref($_) } @$data; + croak 'ids need to be numbers' if any { ! ($_ * 1) } @$data; + $self->oe_ids($data); + } + + $self->shipped_qty({}); +} + + +sub init_oe_ids { + my ($self) = @_; + + croak 'oe_ids not initialized in id mode' if !$self->objects_or_ids; + croak 'objects not initialized before accessing ids' if $self->objects_or_ids && !defined $self->objects; + croak 'objects need to be Order or OrderItem' if any { ref($_) !~ /^SL::DB::Order(?:Item)?$/ } @{ $self->objects }; + + [ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ] +} + +sub init_dbh { SL::DB->client->dbh } + +sub init_oi2oe { {} } +sub init_oi_qty { {} } +sub init_matches { [] } +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 { $::instance_conf->get_shipped_qty_require_stock_out } + +sub init_services_deliverable { + my ($self) = @_; + if (($::form->{type}//'') =~ m/^sales_/ || $self->{objects}->[0]->{customer_id}) { + $::instance_conf->get_sales_delivery_order_check_service; + } elsif (($::form->{type}//'') =~ m/^purchase_/ || $self->{objects}->[0]->{vendor_id}) { + $::instance_conf->get_purchase_delivery_order_check_service; + } else { + croak "wrong call, no customer or vendor object referenced"; + } +} + +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( + require_stock_out => 0, + item_identity_fields => [ qw(parts_id description reqdate serialnumber) ], + ); + + $helper->calculate($order_object); + $helper->calculate(\@order_objects); + $helper->calculate($orderitem_object); + $helper->calculate(\@orderitem_objects); + $helper->calculate($oe_id); + $helper->calculate(\@oe_ids); + + # if these are items set delivered 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_qty = $helper->shipped_qty->{$oi->id}; + + # delivered by oe_id + my $delivered = $helper->delievered->{$oi->id}; + + # calculate and write_to can be chained: + my $helper = SL::Helper::ShippedQty->new->calculate($orders)->write_to_objects; + +=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 at +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 * + +orderitems and oe entries may link to many of their counterparts in +delivery_orders. delivery_orders may 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 * + +Certain delivery orders might not be eligible 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, $::form->{type} is mandatory. + +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 to true the internal matchings of OrderItems and +DeliveryOrderItems will be kept for later postprocessing, in case you need more +than this modules provides. + +See C for the returned format. + + +=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 given objects. If +L was called with objects, then C will use these. + +C and C will be directly infused into the objects +without calling the accessor for delivered. If you want to save afterwards, +you'll have to do that yourself. + +C is guaranteed to be coerced to a number. If no delivery_order +was found it will be set to zero. + +C is guaranteed only to be the correct boolean value, but not +any specific value. + +Note: C will avoid loading unnecessary objects. This means if it is +called with an Order object that has not loaded its orderitems yet, only +C will be set in the Order object. A subsequent C<< +$order->orderitems->[0]->{delivered} >> will return C, and C<< +$order->orderitems->[0]->shipped_qty >> will invoke another implicit +calculation. + +=item C + +Valid after L. Returns a hasref with shipped qtys by orderitems id. + +Unlike the result of C, entries in C may be C if +linked elements were found. + +=item C + +Valid after L. Returns a hashref with a delivered flag by order id. + +=item C + +Valid after L with C set. Returns an arrayref of +individual matches. Each match is an arrayref with these fields: + +=over 4 + +=item * + +The id of the OrderItem. + +=item * + +The id of the DeliveryOrderItem. + +=item * + +The qty that was matched between the two converted to the unit of the OrderItem. + +=item * + +A boolean flag indicating if this match was found with record_item links. If +false, the match was made in the fill up stage. + +=back + +=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 part of the scope of this module. + +=head2 do_mode + +Originally used for printing delivery orders. Resolves for each position for +how much was originally ordered, and how much remains undelivered. + +This one is likely to be dropped. The information only makes sense without +combined merge/split deliveries and is very fragile with unaccounted delivery +orders. + +=head2 oe mode + +Same from the 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 for 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 the number of affected orderitems. + +The rest of the code iterates through the result and calls 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, +which 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