use Carp;
use DateTime;
use Exporter qw(import);
-use List::Util qw(min);
+use List::Util qw(min sum);
use List::UtilsBy qw(sort_by);
use List::MoreUtils qw(any);
+use POSIX qw(ceil);
use SL::Locale::String qw(t8);
use SL::MoreCommon qw(listify);
use SL::DBUtils qw(selectall_hashref_query selectrow_query);
use SL::DB::TransferType;
+use SL::Helper::Number qw(_number _round_number);
use SL::X;
our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
my $onhand_mode = !!$params{onhand};
- my @selects = ('SUM(qty) as qty');
+ my @selects = (
+ 'SUM(qty) AS qty',
+ 'MIN(EXTRACT(epoch FROM inventory.itime)) AS itime',
+ );
my @values;
my @where;
my @groups;
}
if ($params{date}) {
+ Carp::croak("not DateTime ".$params{date}) unless ref($params{date}) eq 'DateTime';
push @where, sprintf "shippingdate <= ?";
push @values, $params{date};
}
- if ($params{bestbefore}) {
- push @where, sprintf "bestbefore >= ?";
- push @values, $params{bestbefore};
- }
-
- # reserve_warehouse
- if ($params{onhand} && !$params{warehouse}) {
- push @where, 'NOT warehouse.forreserve';
- }
-
- # reserve_for
- if ($params{onhand} && !$params{reserve_for}) {
- push @where, 'reserve_for_id IS NULL AND reserve_for_table IS NULL';
+ if (!$params{bestbefore} && $onhand_mode && default_show_bestbefore()) {
+ $params{bestbefore} = DateTime->now_local;
}
- if ($params{reserve_for}) {
- my @objects = listify($params{chargenumber});
- my @tokens;
- push @tokens, ( "(reserve_for_id = ? AND reserve_for_table = ?)") x @objects;
- push @values, map { ($_->id, $_->meta->table) } @objects;
- push @where, '(' . join(' OR ', @tokens) . ')';
+ if ($params{bestbefore}) {
+ Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
+ push @where, sprintf "(bestbefore IS NULL OR bestbefore >= ?)";
+ push @values, $params{bestbefore};
}
# by
my %allowed_by = (
part => [ qw(parts_id) ],
- bin => [ qw(bin_id inventory.warehouse_id warehouse.forreserve)],
- warehouse => [ qw(inventory.warehouse_id warehouse.forreserve) ],
+ bin => [ qw(bin_id inventory.warehouse_id)],
+ warehouse => [ qw(inventory.warehouse_id) ],
chargenumber => [ qw(chargenumber) ],
bestbefore => [ qw(bestbefore) ],
- reserve_for => [ qw(reserve_for_id reserve_for_table) ],
- for_allocate => [ qw(parts_id bin_id inventory.warehouse_id warehouse.forreserve chargenumber bestbefore reserve_for_id reserve_for_table) ],
+ for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
);
if ($params{by}) {
LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
$where
$group_by
- HAVING SUM(qty) > 0
+
+ if ($onhand_mode) {
+ $query .= ' HAVING SUM(qty) > 0';
+ }
my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
part => 'SL::DB::Manager::Part',
bin => 'SL::DB::Manager::Bin',
warehouse => 'SL::DB::Manager::Warehouse',
- reserve_for => undef,
);
my %slots = (
for my $with_object (listify($params{with_objects})) {
Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
- if (my $manager = $with_objects{$with_object}) {
- my $slot = $slots{$with_object};
- next if !(my @ids = map { $_->{$slot} } @$results);
- my $objects = $manager->get_all(query => [ id => \@ids ]);
- my %objects_by_id = map { $_->id => $_ } @$objects;
+ my $manager = $with_objects{$with_object};
+ my $slot = $slots{$with_object};
+ next if !(my @ids = map { $_->{$slot} } @$results);
+ my $objects = $manager->get_all(query => [ id => \@ids ]);
+ my %objects_by_id = map { $_->id => $_ } @$objects;
- $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
- } else {
- # need to fetch all reserve_for_table partitions
- }
+ $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
}
}
sub allocate {
my (%params) = @_;
- my $part = $params{part} or Carp::croak('allocate needs a part');
- my $qty = $params{qty} or Carp::croak('allocate needs a qty');
+ die SL::X::Inventory::Allocation->new(
+ error => 'allocate needs a part',
+ msg => t8("Method allocate needs the parameter 'part'"),
+ ) unless $params{part};
+ die SL::X::Inventory::Allocation->new(
+ error => 'allocate needs a qty',
+ msg => t8("Method allocate needs the parameter 'qty'"),
+ ) unless $params{qty};
+
+ my $part = $params{part};
+ my $qty = $params{qty};
return () if $qty <= 0;
my $results = get_stock(part => $part, by => 'for_allocate');
- my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{bin});
- my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{warehouse});
- my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } listify($params{chargenumber});
- my %reserve_whitelist;
- if ($params{reserve_for}) {
- $reserve_whitelist{ $_->meta->table }{ $_->id } = 1 for listify($params{reserve_for});
- }
-
- # filter the results. we don't want:
- # - negative amounts
- # - bins that are reserve but not in the white-list of warehouses or bins
- # - reservations that are not white-listed
-
- my @filtered_results = grep {
- (!$_->{forreserve} || $bin_whitelist{$_->{bin_id}} || $wh_whitelist{$_->{warehouse_id}})
- && (!$_->{reserve_for_id} || $reserve_whitelist{ $_->{reserve_for_table} }{ $_->{reserve_for_id} })
- } @$results;
+ my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
+ my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
+ my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
- # sort results so that reserve_for is first, then chargenumbers, then wanted bins, then wanted warehouses
+ # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
my @sorted_results = sort {
- (!!$b->{reserve_for_id}) <=> (!!$a->{reserve_for_id}) # sort by existing reserve_for_id first.
- || $chargenumbers{$b->{chargenumber}} <=> $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
- || $bin_whitelist{$b->{bin_id}} <=> $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
- || $wh_whitelist{$b->{warehouse_id}} <=> $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
- } @filtered_results;
+ exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
+ || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
+ || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
+ || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
+ } @$results;
my @allocations;
my $rest_qty = $qty;
for my $chunk (@sorted_results) {
my $qty = min($chunk->{qty}, $rest_qty);
+
+ # since allocate operates on stock, this also ensures that no negative stock results are used
if ($qty > 0) {
push @allocations, SL::Helper::Inventory::Allocation->new(
parts_id => $chunk->{parts_id},
warehouse_id => $chunk->{warehouse_id},
chargenumber => $chunk->{chargenumber},
bestbefore => $chunk->{bestbefore},
- reserve_for_id => $chunk->{reserve_for_id},
- reserve_for_table => $chunk->{reserve_for_table},
- oe_id => undef,
+ for_object_id => undef,
);
- $rest_qty -= $qty;
+ $rest_qty -= _round_number($qty, 5);
}
-
+ $rest_qty = _round_number($rest_qty, 5);
last if $rest_qty == 0;
}
if ($rest_qty > 0) {
die SL::X::Inventory::Allocation->new(
- error => t8('not enough to allocate'),
- msg => t8("can not allocate #1 units of #2, missing #3 units", $qty, $part->displayable_name, $rest_qty),
+ error => 'not enough to allocate',
+ msg => t8("can not allocate #1 units of #2, missing #3 units", _number(\%::myconfig, $qty), $part->displayable_name, _number(\%::myconfig, $rest_qty)),
);
} else {
if ($params{constraints}) {
for my $assembly ($part->assemblies) {
$parts_to_allocate{ $assembly->part->id } //= 0;
- $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty; # TODO recipe factor
+ $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty;
}
my @allocations;
for (keys %$constraints ) {
croak "unsupported constraint '$_'" unless $supported_constraints{$_};
+ next unless defined $constraints->{$_};
my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
my $accessor = $supported_constraints{$_};
if (any { !$whitelist{$_->$accessor} } @$allocations) {
my %error_constraints = (
- bin_id => t8('Bins'),
- warehouse_id => t8('Warehouses'),
- chargenumber => t8('Chargenumbers'),
+ bin_id => t8('Bins'),
+ warehouse_id => t8('Warehouses'),
+ chargenumber => t8('Chargenumbers'),
);
+ my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
+ my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
+ my $err = t8("Cannot allocate parts.");
+ $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
+ SL::DB::Part->load_cached($_->parts_id)->description,
+ SL::DB::Bin->load_cached($_->bin_id)->full_description,
+ _number($_->qty), _number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
die SL::X::Inventory::Allocation->new(
error => 'allocation constraints failure',
- msg => t8("Allocations didn't pass constraints for #1",$error_constraints{$_}),
+ msg => $err,
);
}
}
my $bin = $params{bin} or Carp::croak("need target bin");
my $chargenumber = $params{chargenumber};
my $bestbefore = $params{bestbefore};
- my $oe_id = $params{oe_id};
+ my $for_object_id = $params{for_object_id};
my $comment = $params{comment} // '';
- my $production_order_item = $params{production_order_item};
my $invoice = $params{invoice};
my $project = $params{project};
- my $reserve_for = $params{reserve_for};
-
- my $reserve_for_id = $reserve_for ? $reserve_for->id : undef;
- my $reserve_for_table = $reserve_for ? $reserve_for->meta->table : undef;
my $shippingdate = $params{shippingsdate} // DateTime->now_local;
if (!$params{no_check_allocations} && !$params{auto_allocate}) {
my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
for my $assembly ($part->assemblies) {
- $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
+ $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty;
}
die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
my @transfers;
for my $allocation (@$allocations) {
+ my $oe_id = delete $allocation->{for_object_id};
push @transfers, SL::DB::Inventory->new(
trans_id => $trans_id,
%$allocation,
trans_type => $trans_type_out,
shippingdate => $shippingdate,
employee => SL::DB::Manager::Employee->current,
- oe_id => $allocation->oe_id,
+ oe_id => $allocation->for_object_id,
);
}
warehouse => $bin->warehouse_id,
chargenumber => $chargenumber,
bestbefore => $bestbefore,
- reserve_for_id => $reserve_for_id,
- reserve_for_table => $reserve_for_table,
shippingdate => $shippingdate,
project => $project,
invoice => $invoice,
comment => $comment,
- prod => $production_order_item,
employee => SL::DB::Manager::Employee->current,
- oe_id => $oe_id,
+ oe_id => $for_object_id,
);
SL::DB->client->with_transaction(sub {
@transfers;
}
+sub default_show_bestbefore {
+ $::instance_conf->get_show_bestbefore
+}
+
package SL::Helper::Inventory::Allocation {
- my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment reserve_for_id reserve_for_table oe_id);
+ my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
my %attributes = map { $_ => 1 } @attributes;
for my $name (@attributes) {
# See description for an intro to the concepts used here.
- use SL::Helper::Inventory;
+ use SL::Helper::Inventory qw(:ALL);
# stock, get "what's there" for a part with various conditions:
- my $qty = SL::Helper::Inventory->get_stock(part => $part); # how much is on stock?
- my $qty = SL::Helper::Inventory->get_stock(part => $part, date => $date); # how much was on stock at a specific time?
- my $qty = SL::Helper::Inventory->get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
- my $qty = SL::Helper::Inventory->get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
- my $qty = SL::Helper::Inventory->get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
+ my $qty = get_stock(part => $part); # how much is on stock?
+ my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
+ my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
+ my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
+ my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
# onhand, get "what's available" for a part with various conditions:
- my $qty = SL::Helper::Inventory->get_onhand(part => $part); # how much is available?
- my $qty = SL::Helper::Inventory->get_onhand(part => $part, date => $date); # how much was available at a specific time?
- my $qty = SL::Helper::Inventory->get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
- my $qty = SL::Helper::Inventory->get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
- my $qty = SL::Helper::Inventory->get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
- my $qty = SL::Helper::Inventory->get_onhand(part => $part, reserve_for => $order); # how much is available if you include this reservation?
+ my $qty = get_onhand(part => $part); # how much is available?
+ my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
+ my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
+ my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
+ my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
# onhand batch mode:
- my $data = SL::Helper::Inventory->get_onhand(
+ my $data = get_onhand(
warehouse => $warehouse,
- by => [ qw(bin part chargenumber reserve_for) ],
+ by => [ qw(bin part chargenumber) ],
with_objects => [ qw(bin part) ],
);
# allocate:
- my @allocations, SL::Helper::Inventory->allocate(
+ my @allocations, allocate(
part => $part, # part_id works too
qty => $qty, # must be positive
chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
- reserve_for => $object, # optional, may be arrayref. if provided the qtys reserved for these objects will be used first
bin => $bin, # optional, may be arrayref. if provided
);
# shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
- my @allocations, SL::Helper::Inventory->allocate_for_assembly(
+ my @allocations, allocate_for_assembly(
part => $assembly, # part_id works too
qty => $qty, # must be positive
);
warehouse_id => $bin_obj->warehouse_id,
chargenumber => '1823772365',
bestbefore => undef,
- reserve_for_id => undef,
- reserve_for_table => undef,
- oe_id => $my_document,
+ for_object_id => $order->id,
);
# produce_assembly:
- SL::Helper::Inventory->produce_assembly(
+ produce_assembly(
part => $part, # target assembly
qty => $qty, # qty
allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
comment => $comment, # optional
# links, all optional
- production_order_item => $item,
- reserve_for => $object,
);
=head1 DESCRIPTION
The first problem has spawned several dozen small functions in the program that
try to implement that, and those usually miss some details. They may ignore
-reservations, or reserve warehouses, or bestbefore times.
+bestbefore times, comments, ignore negative quantities etc.
To get this cleaned up a bit this code introduces two concepts: stock and onhand.
-Stock is defined as the actual contents of the inventory, everything that is
-there. Onhand is what is available, which means things that are stocked and not
-reserved and not expired.
+=over 4
+
+=item * Stock is defined as the actual contents of the inventory, everything that is
+there.
+
+=item * Onhand is what is available, which means things that are stocked,
+not expired and not reserved for other uses.
+
+=back
The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
allow simple access with some optional filters for chargenumbers or warehouses.
C<allocate> will try to find the requested quantity of a part in the inventory
and will return allocations of it which can then be used to create the
assembly. Allocation will happen with the C<onhand> semantics defined above,
-meaning that by default no reservations or expired goods will be used. The
-caller can supply hints of what shold be used and in those cases chargenumber
-and reservations will be used up as much as possible first. C<allocate> will
-always try to fulfil the request even beyond those. Should the required amount
-not be stocked, allocate will throw an exception.
+meaning that by default no expired goods will be used. The caller can supply
+hints of what shold be used and in those cases chargenumbers will be used up as
+much as possible first. C<allocate> will always try to fulfil the request even
+beyond those. Should the required amount not be stocked, allocate will throw an
+exception.
C<produce_assembly> has been rewritten to only accept parameters about the
target of the production, and requires allocations to complete the request. The
=item *
-If you need the reserved amount for an order use C<SL::DB::Helper::Reservation>
-instead.
-
-=item *
-
-If you need actual inventory objects because of record links, prod_id links or
-something like that load them directly. And strongly consider redesigning that,
-because it's really fragile.
+If you need actual inventory objects because of record links or something like
+that load them directly. And strongly consider redesigning that, because it's
+really fragile.
=item *
=item * get_onhand PARAMS
-Returns for single parts how much is available in the inventory. That excludes:
-reserved quantities, reserved warehouses and stock with expired bestbefore.
+Returns for single parts how much is available in the inventory. That excludes
+stock with expired bestbefore.
-It takes all options of L</get_stock> but treats some of the differently and has some additional ones:
+It takes the same options as L</get_stock>.
=over 4
-=item * warehouse
-
-Usually C<onhand> will not include results from warehouses with the C<reserve>
-flag. However giving an explicit list of warehouses will include there in the
-search, as well as all others.
-
-=item * reserve_for
-
-=item * reserve_warehouse
-
=item * bestbefore
+If given, will only return stock with a bestbefore at or after the given date.
+Optional. Must be L<DateTime> object.
+
=back
=item * allocate PARAMS
Datetime. Optional.
-=item * reserve_for
-
-Needs to be a rose object, where id and table can be extracted. Optional.
-
=back
Tries to allocate the required quantity using what is currently onhand. If
-given any of C<bin>, C<warehouse>, C<chargenumber>, C<reserve_for>
-
+given any of C<bin>, C<warehouse>, C<chargenumber>
=item * allocate_for_assembly PARAMS
=item * bestbefore
-=item * reserve_for
-
=back
Note: If you want to use the returned data to create allocations you I<need> to
enable all of these. To make this easier a special shortcut exists
In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
-C<parts>, and the C<reserve_for> objects in one go, just like with Rose. They
+C<parts> objects in one go, just like with Rose. They
need to be present in C<by> before that though.
=head1 ALLOCATION ALGORITHM
I<exactly> use the provided charges, you'll need to craft the allocations
yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
-If C<reserve_for> is given, those will be used up first too.
-
-If C<reserved_warehouse> is given, those will be used up second.
-
If C<chargenumber> is given, those will be used up next.
After that normal quantities will be used.
=item * bestbefore
-=item * reserve_for_id
-
-=item * reserve_for_table
+=item * for_object_id
-=item * oe_id
-
-Must be explicit set if the allocation needs also an (other) document.
+If set the allocations will be marked as allocated for the given object.
+If these allocations are later used to produce an assembly, the resulting
+consuming transactions will be marked as belonging to the given object.
+The object may be an order, productionorder or other objects
=back
-C<chargenumber>, C<bestbefore>, C<reserve_for_id>, C<reserve_for_table> and oe_id may
-be C<undef> (but must still be present at creation time). Instances are
-considered immutable.
+C<chargenumber>, C<bestbefore> and C<for_object_id> may be C<undef> (but must
+still be present at creation time). Instances are considered immutable.
=head1 CONSTRAINTS
all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
&&
- # and must be all reservations
- all { $_->reserve_for_id } @_;
+ # and must all have a bestbefore date
+ all { $_->bestbefore } @_;
}
)
not be completed. The usual reason will be insufficient onhand to allocate, or
insufficient allocations to process the request.
+=head1 KNOWN PROBLEMS
+
+ * It's not currently possible to identify allocations between requests, for
+ example for presenting the user possible allocations and then actually using
+ them on the next request.
+ * It's not currently possible to give C<allocate> prior constraints.
+ Currently all constraints are treated as hints (and will be preferred) but
+ the internal ordering of the hints is fixed and more complex preferentials
+ are not supported.
+ * bestbefore handling is untested
+
=head1 TODO
* define and describe error classes
* define wrapper classes for stock/onhand batch mode return values
- * handle extra arguments in produce: shippingdate, project, oe
+ * handle extra arguments in produce: shippingdate, project
* clean up allocation helper class
- * with objects for reservations
* document no_ check
* tests