Projekt

Allgemein

Profil

Herunterladen (24,5 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/Helper/ShippedQty.pm @ 59626ee2

bae8f672 Sven Schöling
package SL::Helper::ShippedQty;

use strict;
use parent qw(Rose::Object);

4720b32a Sven Schöling
use Carp;
bae8f672 Sven Schöling
use Scalar::Util qw(blessed);
use List::Util qw(min);
0f743db7 Sven Schöling
use List::MoreUtils qw(any all uniq);
bae8f672 Sven Schöling
use List::UtilsBy qw(partition_by);
4720b32a Sven Schöling
use SL::AM;
use SL::DBUtils qw(selectall_hashref_query selectall_as_map);
f559550f Sven Schöling
use SL::Locale::String qw(t8);
bae8f672 Sven Schöling
use Rose::Object::MakeMethods::Generic (
0d6c87b0 Sven Schöling
'scalar' => [ qw(objects objects_or_ids shipped_qty keep_matches) ],
'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered matches) ],
bae8f672 Sven Schöling
);

my $no_stock_item_links_query = <<'';
0d6c87b0 Sven Schöling
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
bae8f672 Sven Schöling
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 = <<'';
0d6c87b0 Sven Schöling
SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id,
5f4bdff3 Sven Schöling
(CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty AS doi_qty, p.unit AS doi_unit
bae8f672 Sven Schöling
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'
5f4bdff3 Sven Schöling
INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
bae8f672 Sven Schöling
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 = <<'';
5f4bdff3 Sven Schöling
SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber,
(CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty, p.unit
bae8f672 Sven Schöling
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
5f4bdff3 Sven Schöling
INNER JOIN delivery_orders doe ON doe.id = doi.delivery_order_id
bae8f672 Sven Schöling
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'

f559550f Sven Schöling
my @known_item_identity_fields = qw(parts_id description reqdate serialnumber);
my %item_identity_fields = (
parts_id => t8('Part'),
description => t8('Description'),
reqdate => t8('Reqdate'),
serialnumber => t8('Serial Number'),
);

bae8f672 Sven Schöling
sub calculate {
my ($self, $data) = @_;

4720b32a Sven Schöling
croak 'Need exactly one argument, either id, object or arrayref of ids or objects.' unless 2 == @_;
bae8f672 Sven Schöling
$self->normalize_input($data);

66ac2e99 Sven Schöling
return $self unless @{ $self->oe_ids };
bae8f672 Sven Schöling
$self->calculate_item_links;
$self->calculate_fill_up if $self->fill_up;
e05e7574 Sven Schöling
$self;
bae8f672 Sven Schöling
}

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) {
0d6c87b0 Sven Schöling
my $qty = $_->{doi_qty} * AM->convert_unit($_->{doi_unit} => $_->{oi_unit});
bae8f672 Sven Schöling
$self->shipped_qty->{$_->{oi_id}} //= 0;
0d6c87b0 Sven Schöling
$self->shipped_qty->{$_->{oi_id}} += $qty;
bae8f672 Sven Schöling
$self->oi2oe->{$_->{oi_id}} = $_->{trans_id};
$self->oi_qty->{$_->{oi_id}} = $_->{oi_qty};
0d6c87b0 Sven Schöling
push @{ $self->matches }, [ $_->{oi_id}, $_->{doi_id}, $qty, 1 ] if $self->keep_matches;
bae8f672 Sven Schöling
}
}

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
0d6c87b0 Sven Schöling
push @{ $self->matches }, [ $oi->{id}, $doi->{id}, $min_qty, 0 ] if $self->keep_matches;
bae8f672 Sven Schöling
}
}
}

$self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
$self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
}

sub write_to {
my ($self, $objects) = @_;

4720b32a Sven Schöling
croak 'expecting array of objects' unless 'ARRAY' eq ref $objects;
bae8f672 Sven Schöling
my $shipped_qty = $self->shipped_qty;

for my $obj (@$objects) {
if ('SL::DB::OrderItem' eq ref $obj) {
4d2ba312 Sven Schöling
$obj->{shipped_qty} = $shipped_qty->{$obj->id} //= 0;
bae8f672 Sven Schöling
$obj->{delivered} = $shipped_qty->{$obj->id} == $obj->qty;
} elsif ('SL::DB::Order' eq ref $obj) {
6446d674 Sven Schöling
if (defined $obj->{orderitems}) {
bae8f672 Sven Schöling
$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";
}
}
f56f6f7a Sven Schöling
$self;
bae8f672 Sven Schöling
}

sub write_to_objects {
my ($self) = @_;

96ddb932 Sven Schöling
return unless @{ $self->oe_ids };

4720b32a Sven Schöling
croak 'Can only use write_to_objects, when calculate was called with objects. Use write_to instead.' unless $self->objects_or_ids;
bae8f672 Sven Schöling
$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) {
4720b32a Sven Schöling
croak 'unblessed object in data while expecting object' if any { !blessed($_) } @$data;
bae8f672 Sven Schöling
$self->objects($data);
} else {
4720b32a Sven Schöling
croak 'object or reference in data while expecting ids' if any { ref($_) } @$data;
croak 'ids need to be numbers' if any { ! ($_ * 1) } @$data;
bae8f672 Sven Schöling
$self->oe_ids($data);
}

$self->shipped_qty({});
}

f559550f Sven Schöling
sub available_item_identity_fields {
map { [ $_ => $item_identity_fields{$_} ] } @known_item_identity_fields;
}

bae8f672 Sven Schöling
sub init_oe_ids {
my ($self) = @_;

4720b32a Sven Schöling
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 };
bae8f672 Sven Schöling
0f743db7 Sven Schöling
[ uniq map { ref($_) =~ /Item/ ? $_->trans_id : $_->id } @{ $self->objects } ]
bae8f672 Sven Schöling
}

sub init_dbh { SL::DB->client->dbh }

sub init_oi2oe { {} }
sub init_oi_qty { {} }
0d6c87b0 Sven Schöling
sub init_matches { [] }
bae8f672 Sven Schöling
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;
}

f559550f Sven Schöling
sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
sub init_item_identity_fields { [ grep $item_identity_fields{$_}, @{ $::instance_conf->get_shipped_qty_item_identity_fields } ] }
sub init_fill_up { $::instance_conf->get_shipped_qty_fill_up }
bae8f672 Sven Schöling
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) ],
);

$helper->calculate($order_object);
$helper->calculate(\@order_objects);
0f743db7 Sven Schöling
$helper->calculate($orderitem_object);
$helper->calculate(\@orderitem_objects);
bae8f672 Sven Schöling
$helper->calculate($oe_id);
$helper->calculate(\@oe_ids);

42442d1f Bernd Bleßmann
# if these are items set delivered and shipped_qty
bae8f672 Sven Schöling
# 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
66d68994 Sven Schöling
my $shipped_qty = $helper->shipped_qty->{$oi->id};
bae8f672 Sven Schöling
# delivered by oe_id
66d68994 Sven Schöling
my $delivered = $helper->delievered->{$oi->id};
bae8f672 Sven Schöling
f56f6f7a Sven Schöling
# calculate and write_to can be chained:
my $helper = SL::Helper::ShippedQty->new->calculate($orders)->write_to_objects;

bae8f672 Sven Schöling
=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

577277a3 Geoffrey Richardson
The concept of shipped qty is sadly not as straight forward as it sounds at
bae8f672 Sven Schöling
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
577277a3 Geoffrey Richardson
in the database need to be matched by marching.
bae8f672 Sven Schöling
=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 *

577277a3 Geoffrey Richardson
Certain delivery orders might not be eligible for qty calculations if delivery
bae8f672 Sven Schöling
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<new PARAMS>

Creates a new helper object. PARAMS may include:

=over 4

=item * C<require_stock_out>

Boolean. If set, delivery orders must be stocked out to be considered
delivered. The default is a client setting.

=item * C<fill_up>

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<item_identity_fields ARRAY>

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<parts_id>

=item * C<description>

=item * C<reqdate>

=item * C<serialnumber>

=back

0d6c87b0 Sven Schöling
=item * C<keep_matches>

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<matches> for the returned format.

bae8f672 Sven Schöling
=back

=item C<calculate OBJECTS>

=item C<calculate IDS>

Do the main work. There must be a single argument: Either an id or an
C<SL::DB::Order> 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<write_to OBJECTS>

=item C<write_to_objects>

4d2ba312 Sven Schöling
Save the C<shipped_qty> and C<delivered> state to the given objects. If
L</calculate> was called with objects, then C<write_to_objects> will use these.

C<shipped_qty> and C<delivered> 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<shipped_qty> is guaranteed to be coerced to a number. If no delivery_order
was found it will be set to zero.

C<delivered> is guaranteed only to be the correct boolean value, but not
any specific value.
bae8f672 Sven Schöling
e8dce44b Sven Schöling
Note: C<write_to> will avoid loading unnecessary objects. This means if it is
called with an Order object that has not loaded its orderitems yet, only
C<delivered> will be set in the Order object. A subsequent C<<
$order->orderitems->[0]->{delivered} >> will return C<undef>, and C<<
$order->orderitems->[0]->shipped_qty >> will invoke another implicit
calculation.

bae8f672 Sven Schöling
=item C<shipped_qty>

Valid after L</calculate>. Returns a hasref with shipped qtys by orderitems id.

4d2ba312 Sven Schöling
Unlike the result of C</write_to>, entries in C<shipped_qty> may be C<undef> if
linked elements were found.

bae8f672 Sven Schöling
=item C<delivered>

577277a3 Geoffrey Richardson
Valid after L</calculate>. Returns a hashref with a delivered flag by order id.
bae8f672 Sven Schöling
0d6c87b0 Sven Schöling
=item C<matches>

Valid after L</calculate> with C<with_matches> 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.

bae8f672 Sven Schöling
=back

f88b9da2 Sven Schöling
=back

bae8f672 Sven Schöling
=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
577277a3 Geoffrey Richardson
convention for the delivery_order is not part of the scope of this module.
bae8f672 Sven Schöling
=head2 do_mode

Originally used for printing delivery orders. Resolves for each position for
577277a3 Geoffrey Richardson
how much was originally ordered, and how much remains undelivered.
bae8f672 Sven Schöling
577277a3 Geoffrey Richardson
This one is likely to be dropped. The information only makes sense without
bae8f672 Sven Schöling
combined merge/split deliveries and is very fragile with unaccounted delivery
orders.

=head2 oe mode

577277a3 Geoffrey Richardson
Same from the order perspective. Used for transitions to delivery orders, where
bae8f672 Sven Schöling
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<DO::get_shipped_qty>

C<SL::Controller::DeliveryPlan::calc_qtys>

C<SL::DB::OrderItem::shipped_qty>

C<SL::DB::OrderItem::delivered_qty>

=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

577277a3 Geoffrey Richardson
Perl ops except for sort are expected to be constant (relative to the op overhead).
bae8f672 Sven Schöling
=head2 Record item links

The query itself has indices available for all joins and filters and should
577277a3 Geoffrey Richardson
scale with sublinear with the number of affected orderitems.
bae8f672 Sven Schöling
577277a3 Geoffrey Richardson
The rest of the code iterates through the result and calls C<AM::convert_unit>,
bae8f672 Sven Schöling
which caches internally and is asymptotically constant.

=head2 Fill up

C<partition_by> and C<intersect> 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,
577277a3 Geoffrey Richardson
which again makes square worst case, but much less than that in the general
bae8f672 Sven Schöling
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<shipped_qty>, C<oi2oe>, and C<oi_qty> - all hashes with one entry for each
orderitem. C<delivered> 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 E<lt>s.schoeling@linet-services.deE<gt>

=cut