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 much is on stock in a specific bin?
392 my $qty = get_stock(part => $part, warehouse => $warehouse); # how much is on stock in a specific warehouse?
393 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how much 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
450 New functions for the warehouse and inventory api.
452 The WH api currently has three large shortcomings: It is very hard to just get
453 the current stock for an item, it's extremely complicated to use it to produce
454 assemblies while ensuring that no stock ends up negative, and it's very hard to
455 use it to get an overview over the actual contents of the inventory.
457 The first problem has spawned several dozen small functions in the program that
458 try to implement that, and those usually miss some details. They may ignore
459 bestbefore times, comments, ignore negative quantities etc.
461 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
465 =item * Stock is defined as the actual contents of the inventory, everything that is
468 =item * Onhand is what is available, which means things that are stocked,
469 not expired and not in any other way reserved for other uses.
473 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
474 allow simple access with some optional filters for chargenumbers or warehouses.
475 Both of them have a batch mode that can be used to get these information to
476 supplement simple reports.
478 To address the safe assembly creation a new function has been added.
479 C<allocate> will try to find the requested quantity of a part in the inventory
480 and will return allocations of it which can then be used to create the
481 assembly. Allocation will happen with the C<onhand> semantics defined above,
482 meaning that by default no expired goods will be used. The caller can supply
483 hints of what shold be used and in those cases chargenumbers will be used up as
484 much as possible first. C<allocate> will always try to fulfil the request even
485 beyond those. Should the required amount not be stocked, allocate will throw an
488 C<produce_assembly> has been rewritten to only accept parameters about the
489 target of the production, and requires allocations to complete the request. The
490 allocations can be supplied manually, or can be generated automatically.
491 C<produce_assembly> will check whether enough allocations are given to create
492 the assembly, but will not check whether the allocations are backed. If the
493 allocations are not sufficient or if the auto-allocation fails an exception
494 is returned. If you need to produce something that is not in the inventory, you
495 can bypass those checks by creating the allocations yourself (see
496 L</"ALLOCATION DATA STRUCTURE">).
498 Note: this is only intended to cover the scenarios described above. For other cases:
504 If you need actual inventory objects because of record links or something like
505 that load them directly. And strongly consider redesigning that, because it's
510 You need weight or accounting information you're on your own. The inventory api
511 only concerns itself with the raw quantities.
515 If you need the first stock date of parts, or anything related to a specific
516 transfer type or direction, this is not covered yet.
524 =item * get_stock PARAMS
526 Returns for single parts how much actually exists in the inventory.
534 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
538 If given, will only return stock on these bins. Optional. May be array, May be object or id.
542 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
546 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
550 If given, will only show stock with this chargenumber. Optional. May be array.
554 See L</"STOCK/ONHAND REPORT MODE">
558 See L</"STOCK/ONHAND REPORT MODE">
562 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
563 mode when C<by> is given.
565 =item * get_onhand PARAMS
567 Returns for single parts how much is available in the inventory. That excludes
568 stock with expired bestbefore.
570 It takes the same options as L</get_stock>.
576 If given, will only return stock with a bestbefore at or after the given date.
577 Optional. Must be L<DateTime> object.
581 =item * allocate PARAMS
593 Bin object. Optional.
597 Warehouse object. Optional.
609 Tries to allocate the required quantity using what is currently onhand. If
610 given any of C<bin>, C<warehouse>, C<chargenumber>
612 =item * allocate_for_assembly PARAMS
614 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
615 compute the required amount for each assembly part and allocate all of them.
617 =item * produce_assembly
622 =head1 STOCK/ONHAND REPORT MODE
624 If the special option C<by> is given with an arrayref, the result will instead
625 be an arrayref of partitioned stocks by those fields. Valid partitions are:
631 If this is given, part is optional in the parameters
643 Note: If you want to use the returned data to create allocations you I<need> to
644 enable all of these. To make this easier a special shortcut exists
646 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
647 C<parts> objects in one go, just like with Rose. They
648 need to be present in C<by> before that though.
650 =head1 ALLOCATION ALGORITHM
652 When calling allocate, the current onhand (== available stock) of the item will
653 be used to decide which bins/chargenumbers/bestbefore can be used.
655 In general allocate will try to make the request happen, and will use the
656 provided charges up first, and then tap everything else. If you need to only
657 I<exactly> use the provided charges, you'll need to craft the allocations
658 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
660 If C<chargenumber> is given, those will be used up next.
662 After that normal quantities will be used.
664 These are tiebreakers and expected to rarely matter in reality. If you need
665 finegrained control over which allocation is used, you may want to get the
666 onhands yourself and select the appropriate ones.
668 Only quantities with C<bestbefore> unset or after the given date will be
669 considered. If more than one charge is eligible, the earlier C<bestbefore>
672 Allocations do NOT have an internal memory and can't react to other allocations
673 of the same part earlier. Never double allocate the same part within a
676 =head1 ALLOCATION DATA STRUCTURE
678 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
679 each of the following attributes to be set at creation time:
695 =item * for_object_id
697 If set the allocations will be marked as allocated for the given object.
698 If these allocations are later used to produce an assembly, the resulting
699 consuming transactions will be marked as belonging to the given object.
700 The object may be an order, productionorder or other objects
704 C<chargenumber>, C<bestbefore> and C<for_object_id> and C<comment> may be
705 C<undef> (but must still be present at creation time). Instances are considered
708 Allocations also provide the method C<transfer_object> which will create a new
709 C<SL::DB::Inventory> bject with all the playload.
713 # whitelist constraints
717 bin_id => \@allowed_bins,
718 chargenumber => \@allowed_chargenumbers,
725 # only allow chargenumbers with specific format
726 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
729 # and must all have a bestbefore date
730 all { $_->bestbefore } @_;
734 C<allocation> is "best effort" in nature. It will take the C<bin>,
735 C<chargenumber> etc hints from the parameters, but will try it's bvest to
736 fulfil the request anyway and only bail out if it is absolutely not possible.
738 Sometimes you need to restrict allocations though. For this you can pass
739 additional constraints to C<allocate>. A constraint serves as a whitelist.
740 Every allocation must fulfil every constraint by having that attribute be one
743 In case even that is not enough, you may supply a custom check by passing a
744 function that will be given the allocation objects.
746 Note that both whitelists and constraints do not influence the order of
747 allocations, which is done purely from the initial parameters. They only serve
748 to reject allocations made in good faith which do fulfil required assertions.
750 =head1 ERROR HANDLING
752 C<allocate> and C<produce_assembly> will throw exceptions if the request can
753 not be completed. The usual reason will be insufficient onhand to allocate, or
754 insufficient allocations to process the request.
756 =head1 KNOWN PROBLEMS
758 * It's not currently possible to identify allocations between requests, for
759 example for presenting the user possible allocations and then actually using
760 them on the next request.
761 * It's not currently possible to give C<allocate> prior constraints.
762 Currently all constraints are treated as hints (and will be preferred) but
763 the internal ordering of the hints is fixed and more complex preferentials
765 * bestbefore handling is untested
766 * interaction with config option "transfer_default_ignore_onhand" is
767 currently undefined (and implicitly ignores it)
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
783 Sven Schöling E<lt>sven.schoeling@googlemail.comE<gt>