1 package SL::Helper::Inventory;
6 use Exporter qw(import);
7 use List::Util qw(min sum);
8 use List::UtilsBy qw(sort_by);
9 use List::MoreUtils qw(any);
12 use SL::Locale::String qw(t8);
13 use SL::MoreCommon qw(listify);
14 use SL::DBUtils qw(selectall_hashref_query selectrow_query);
15 use SL::DB::TransferType;
16 use SL::Helper::Number qw(_format_number _round_number);
17 use SL::Helper::Inventory::Allocation;
20 our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
21 our %EXPORT_TAGS = (ALL => \@EXPORT_OK);
23 sub _get_stock_onhand {
26 my $onhand_mode = !!$params{onhand};
30 'MIN(EXTRACT(epoch FROM inventory.itime)) AS itime',
37 my @ids = map { ref $_ ? $_->id : $_ } listify($params{part});
38 push @where, sprintf "parts_id IN (%s)", join ', ', ("?") x @ids;
43 my @ids = map { ref $_ ? $_->id : $_ } listify($params{bin});
44 push @where, sprintf "bin_id IN (%s)", join ', ', ("?") x @ids;
48 if ($params{warehouse}) {
49 my @ids = map { ref $_ ? $_->id : $_ } listify($params{warehouse});
50 push @where, sprintf "warehouse.id IN (%s)", join ', ', ("?") x @ids;
54 if ($params{chargenumber}) {
55 my @ids = listify($params{chargenumber});
56 push @where, sprintf "chargenumber IN (%s)", join ', ', ("?") x @ids;
61 Carp::croak("not DateTime ".$params{date}) unless ref($params{date}) eq 'DateTime';
62 push @where, sprintf "shippingdate <= ?";
63 push @values, $params{date};
66 if (!$params{bestbefore} && $onhand_mode && default_show_bestbefore()) {
67 $params{bestbefore} = DateTime->now_local;
70 if ($params{bestbefore}) {
71 Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
72 push @where, sprintf "(bestbefore IS NULL OR bestbefore >= ?)";
73 push @values, $params{bestbefore};
78 part => [ qw(parts_id) ],
79 bin => [ qw(bin_id inventory.warehouse_id)],
80 warehouse => [ qw(inventory.warehouse_id) ],
81 chargenumber => [ qw(chargenumber) ],
82 bestbefore => [ qw(bestbefore) ],
83 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
87 for (listify($params{by})) {
88 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
89 push @selects, @$selects;
90 push @groups, @$selects;
94 my $select = join ',', @selects;
95 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
96 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
99 SELECT $select FROM inventory
100 LEFT JOIN bin ON bin_id = bin.id
101 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
106 $query .= ' HAVING SUM(qty) > 0';
109 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
112 part => 'SL::DB::Manager::Part',
113 bin => 'SL::DB::Manager::Bin',
114 warehouse => 'SL::DB::Manager::Warehouse',
120 warehouse => 'warehouse_id',
123 if ($params{by} && $params{with_objects}) {
124 for my $with_object (listify($params{with_objects})) {
125 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
127 my $manager = $with_objects{$with_object};
128 my $slot = $slots{$with_object};
129 next if !(my @ids = map { $_->{$slot} } @$results);
130 my $objects = $manager->get_all(query => [ id => \@ids ]);
131 my %objects_by_id = map { $_->id => $_ } @$objects;
133 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
140 return $results->[0]{qty};
145 _get_stock_onhand(@_, onhand => 0);
149 _get_stock_onhand(@_, onhand => 1);
155 die SL::X::Inventory::Allocation->new(
156 error => 'allocate needs a part',
157 msg => t8("Method allocate needs the parameter 'part'"),
158 ) unless $params{part};
159 die SL::X::Inventory::Allocation->new(
160 error => 'allocate needs a qty',
161 msg => t8("Method allocate needs the parameter 'qty'"),
162 ) unless $params{qty};
164 my $part = $params{part};
165 my $qty = $params{qty};
167 return () if $qty <= 0;
169 my $results = get_stock(part => $part, by => 'for_allocate');
170 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
171 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
172 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
174 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
175 my @sorted_results = sort {
176 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
177 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
178 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
179 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
184 for my $chunk (@sorted_results) {
185 my $qty = min($chunk->{qty}, $rest_qty);
187 # since allocate operates on stock, this also ensures that no negative stock results are used
189 push @allocations, SL::Helper::Inventory::Allocation->new(
190 parts_id => $chunk->{parts_id},
192 comment => $params{comment},
193 bin_id => $chunk->{bin_id},
194 warehouse_id => $chunk->{warehouse_id},
195 chargenumber => $chunk->{chargenumber},
196 bestbefore => $chunk->{bestbefore},
197 for_object_id => undef,
199 $rest_qty -= _round_number($qty, 5);
201 $rest_qty = _round_number($rest_qty, 5);
202 last if $rest_qty == 0;
205 die SL::X::Inventory::Allocation->new(
206 error => 'not enough to allocate',
207 msg => t8("can not allocate #1 units of #2, missing #3 units", _format_number($qty), $part->displayable_name, _format_number($rest_qty)),
210 if ($params{constraints}) {
211 check_constraints($params{constraints},\@allocations);
217 sub allocate_for_assembly {
220 my $part = $params{part} or Carp::croak('allocate needs a part');
221 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
223 Carp::croak('not an assembly') unless $part->is_assembly;
225 my %parts_to_allocate;
227 for my $assembly ($part->assemblies) {
228 $parts_to_allocate{ $assembly->part->id } //= 0;
229 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty;
234 for my $part_id (keys %parts_to_allocate) {
235 my $part = SL::DB::Part->load_cached($part_id);
236 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
242 sub check_constraints {
243 my ($constraints, $allocations) = @_;
244 if ('CODE' eq ref $constraints) {
245 if (!$constraints->(@$allocations)) {
246 die SL::X::Inventory::Allocation->new(
247 error => 'allocation constraints failure',
248 msg => t8("Allocations didn't pass constraints"),
252 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
254 my %supported_constraints = (
256 warehouse_id => 'warehouse_id',
257 chargenumber => 'chargenumber',
260 for (keys %$constraints ) {
261 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
262 next unless defined $constraints->{$_};
264 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
265 my $accessor = $supported_constraints{$_};
267 if (any { !$whitelist{$_->$accessor} } @$allocations) {
268 my %error_constraints = (
269 bin_id => t8('Bins'),
270 warehouse_id => t8('Warehouses'),
271 chargenumber => t8('Chargenumbers'),
273 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
274 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
275 my $err = t8("Cannot allocate parts.");
276 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
277 SL::DB::Part->load_cached($_->parts_id)->description,
278 SL::DB::Bin->load_cached($_->bin_id)->full_description,
279 _format_number($_->qty), _format_number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
280 die SL::X::Inventory::Allocation->new(
281 error => 'allocation constraints failure',
289 sub produce_assembly {
292 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
293 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
295 my $allocations = $params{allocations};
296 if ($params{auto_allocate}) {
297 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
298 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
300 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
301 $allocations = $params{allocations};
304 my $bin = $params{bin} or Carp::croak("need target bin");
305 my $chargenumber = $params{chargenumber};
306 my $bestbefore = $params{bestbefore};
307 my $for_object_id = $params{for_object_id};
308 my $comment = $params{comment} // '';
310 my $invoice = $params{invoice};
311 my $project = $params{project};
313 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
315 my $trans_id = $params{trans_id};
316 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
318 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
319 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
321 # check whether allocations are sane
322 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
323 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
324 for my $assembly ($part->assemblies) {
325 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty;
328 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
332 for my $allocation (@$allocations) {
333 my $oe_id = delete $allocation->{for_object_id};
334 push @transfers, $allocation->transfer_object(
335 trans_id => $trans_id,
336 qty => -$allocation->qty,
337 trans_type => $trans_type_out,
338 shippingdate => $shippingdate,
339 employee => SL::DB::Manager::Employee->current,
343 push @transfers, SL::DB::Inventory->new(
344 trans_id => $trans_id,
345 trans_type => $trans_type_in,
349 warehouse => $bin->warehouse_id,
350 chargenumber => $chargenumber,
351 bestbefore => $bestbefore,
352 shippingdate => $shippingdate,
356 employee => SL::DB::Manager::Employee->current,
357 oe_id => $for_object_id,
360 SL::DB->client->with_transaction(sub {
361 $_->save for @transfers;
364 die SL::DB->client->error;
370 sub default_show_bestbefore {
371 $::instance_conf->get_show_bestbefore
380 SL::WH - Warehouse and Inventory API
384 # See description for an intro to the concepts used here.
386 use SL::Helper::Inventory qw(:ALL);
388 # stock, get "what's there" for a part with various conditions:
389 my $qty = get_stock(part => $part); # how much is on stock?
390 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
391 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
392 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
393 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
395 # onhand, get "what's available" for a part with various conditions:
396 my $qty = get_onhand(part => $part); # how much is available?
397 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
398 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
399 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
400 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
403 my $data = get_onhand(
404 warehouse => $warehouse,
405 by => [ qw(bin part chargenumber) ],
406 with_objects => [ qw(bin part) ],
410 my @allocations, allocate(
411 part => $part, # part_id works too
412 qty => $qty, # must be positive
413 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
414 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
415 bin => $bin, # optional, may be arrayref. if provided
418 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
419 my @allocations, allocate_for_assembly(
420 part => $assembly, # part_id works too
421 qty => $qty, # must be positive
424 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
425 my $allocation = SL::Helper::Inventory::Allocation->new(
426 part_id => $part->id,
428 bin_id => $bin_obj->id,
429 warehouse_id => $bin_obj->warehouse_id,
430 chargenumber => '1823772365',
432 for_object_id => $order->id,
437 part => $part, # target assembly
439 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
442 bin => $bin, # needed unless a global standard target is configured
443 chargenumber => $chargenumber, # optional
444 bestbefore => $datetime, # optional
445 comment => $comment, # optional
447 # links, all optional
452 New functions for the warehouse and inventory api.
454 The WH api currently has three large shortcomings. It is very hard to just get
455 the current stock for an item, it's extremely complicated to use it to produce
456 assemblies while ensuring that no stock ends up negative, and it's very hard to
457 use it to get an overview over the actual contents of the inventory.
459 The first problem has spawned several dozen small functions in the program that
460 try to implement that, and those usually miss some details. They may ignore
461 bestbefore times, comments, ignore negative quantities etc.
463 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
467 =item * Stock is defined as the actual contents of the inventory, everything that is
470 =item * Onhand is what is available, which means things that are stocked,
471 not expired and not reserved for other uses.
475 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
476 allow simple access with some optional filters for chargenumbers or warehouses.
477 Both of them have a batch mode that can be used to get these information to
478 supplement simple reports.
480 To address the safe assembly creation a new function has been added.
481 C<allocate> will try to find the requested quantity of a part in the inventory
482 and will return allocations of it which can then be used to create the
483 assembly. Allocation will happen with the C<onhand> semantics defined above,
484 meaning that by default no expired goods will be used. The caller can supply
485 hints of what shold be used and in those cases chargenumbers will be used up as
486 much as possible first. C<allocate> will always try to fulfil the request even
487 beyond those. Should the required amount not be stocked, allocate will throw an
490 C<produce_assembly> has been rewritten to only accept parameters about the
491 target of the production, and requires allocations to complete the request. The
492 allocations can be supplied manually, or can be generated automatically.
493 C<produce_assembly> will check whether enough allocations are given to create
494 the recipe, but will not check whether the allocations are backed. If the
495 allocations are not sufficient or if the auto-allocation fails an exception
496 is returned. If you need to produce something that is not in the inventory, you
497 can bypass those checks by creating the allocations yourself (see
498 L</"ALLOCATION DATA STRUCTURE">).
500 Note: this is only intended to cover the scenarios described above. For other cases:
506 If you need actual inventory objects because of record links or something like
507 that load them directly. And strongly consider redesigning that, because it's
512 You need weight or accounting information you're on your own. The inventory api
513 only concerns itself with the raw quantities.
517 If you need the first stock date of parts, or anything related to a specific
518 transfer type or direction, this is not covered yet.
526 =item * get_stock PARAMS
528 Returns for single parts how much actually exists in the inventory.
536 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
540 If given, will only return stock on these bins. Optional. May be array, May be object or id.
544 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
548 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
552 If given, will only show stock with this chargenumber. Optional. May be array.
556 See L</"STOCK/ONHAND REPORT MODE">
560 See L</"STOCK/ONHAND REPORT MODE">
564 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
565 mode when C<by> is given.
567 =item * get_onhand PARAMS
569 Returns for single parts how much is available in the inventory. That excludes
570 stock with expired bestbefore.
572 It takes the same options as L</get_stock>.
578 If given, will only return stock with a bestbefore at or after the given date.
579 Optional. Must be L<DateTime> object.
583 =item * allocate PARAMS
595 Bin object. Optional.
599 Warehouse object. Optional.
611 Tries to allocate the required quantity using what is currently onhand. If
612 given any of C<bin>, C<warehouse>, C<chargenumber>
614 =item * allocate_for_assembly PARAMS
616 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
617 compute the required amount for each assembly part and allocate all of them.
619 =item * produce_assembly
624 =head1 STOCK/ONHAND REPORT MODE
626 If the special option C<by> is given with an arrayref, the result will instead
627 be an arrayref of partitioned stocks by those fields. Valid partitions are:
633 If this is given, part is optional in the parameters
645 Note: If you want to use the returned data to create allocations you I<need> to
646 enable all of these. To make this easier a special shortcut exists
648 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
649 C<parts> objects in one go, just like with Rose. They
650 need to be present in C<by> before that though.
652 =head1 ALLOCATION ALGORITHM
654 When calling allocate, the current onhand (== available stock) of the item will
655 be used to decide which bins/chargenumbers/bestbefore can be used.
657 In general allocate will try to make the request happen, and will use the
658 provided charges up first, and then tap everything else. If you need to only
659 I<exactly> use the provided charges, you'll need to craft the allocations
660 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
662 If C<chargenumber> is given, those will be used up next.
664 After that normal quantities will be used.
666 These are tiebreakers and expected to rarely matter in reality. If you need
667 finegrained control over which allocation is used, you may want to get the
668 onhands yourself and select the appropriate ones.
670 Only quantities with C<bestbefore> unset or after the given date will be
671 considered. If more than one charge is eligible, the earlier C<bestbefore>
674 Allocations do NOT have an internal memory and can't react to other allocations
675 of the same part earlier. Never double allocate the same part within a
678 =head1 ALLOCATION DATA STRUCTURE
680 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
681 each of the following attributes to be set at creation time:
697 =item * for_object_id
699 If set the allocations will be marked as allocated for the given object.
700 If these allocations are later used to produce an assembly, the resulting
701 consuming transactions will be marked as belonging to the given object.
702 The object may be an order, productionorder or other objects
706 C<chargenumber>, C<bestbefore> and C<for_object_id> and C<comment> may be
707 C<undef> (but must still be present at creation time). Instances are considered
710 Allocations also provide the method C<transfer_object> which will create a new
711 C<SL::DB::Inventory> bject with all the playload.
715 # whitelist constraints
719 bin_id => \@allowed_bins,
720 chargenumber => \@allowed_chargenumbers,
727 # only allow chargenumbers with specific format
728 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
731 # and must all have a bestbefore date
732 all { $_->bestbefore } @_;
736 C<allocation> is "best effort" in nature. It will take the C<bin>,
737 C<chargenumber> etc hints from the parameters, but will try it's bvest to
738 fulfil the request anyway and only bail out if it is absolutely not possible.
740 Sometimes you need to restrict allocations though. For this you can pass
741 additional constraints to C<allocate>. A constraint serves as a whitelist.
742 Every allocation must fulfil every constraint by having that attribute be one
745 In case even that is not enough, you may supply a custom check by passing a
746 function that will be given the allocation objects.
748 Note that both whitelists and constraints do not influence the order of
749 allocations, which is done purely from the initial parameters. They only serve
750 to reject allocations made in good faith which do fulfil required assertions.
752 =head1 ERROR HANDLING
754 C<allocate> and C<produce_assembly> will throw exceptions if the request can
755 not be completed. The usual reason will be insufficient onhand to allocate, or
756 insufficient allocations to process the request.
758 =head1 KNOWN PROBLEMS
760 * It's not currently possible to identify allocations between requests, for
761 example for presenting the user possible allocations and then actually using
762 them on the next request.
763 * It's not currently possible to give C<allocate> prior constraints.
764 Currently all constraints are treated as hints (and will be preferred) but
765 the internal ordering of the hints is fixed and more complex preferentials
767 * bestbefore handling is untested
771 * define and describe error classes
772 * define wrapper classes for stock/onhand batch mode return values
773 * handle extra arguments in produce: shippingdate, project
774 * clean up allocation helper class
784 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>