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(_number _round_number);
19 our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
20 our %EXPORT_TAGS = (ALL => \@EXPORT_OK);
22 sub _get_stock_onhand {
25 my $onhand_mode = !!$params{onhand};
29 'MIN(EXTRACT(epoch FROM inventory.itime)) AS itime',
36 my @ids = map { ref $_ ? $_->id : $_ } listify($params{part});
37 push @where, sprintf "parts_id IN (%s)", join ', ', ("?") x @ids;
42 my @ids = map { ref $_ ? $_->id : $_ } listify($params{bin});
43 push @where, sprintf "bin_id IN (%s)", join ', ', ("?") x @ids;
47 if ($params{warehouse}) {
48 my @ids = map { ref $_ ? $_->id : $_ } listify($params{warehouse});
49 push @where, sprintf "warehouse.id IN (%s)", join ', ', ("?") x @ids;
53 if ($params{chargenumber}) {
54 my @ids = listify($params{chargenumber});
55 push @where, sprintf "chargenumber IN (%s)", join ', ', ("?") x @ids;
60 Carp::croak("not DateTime ".$params{date}) unless ref($params{date}) eq 'DateTime';
61 push @where, sprintf "shippingdate <= ?";
62 push @values, $params{date};
65 if (!$params{bestbefore} && $onhand_mode && default_show_bestbefore()) {
66 $params{bestbefore} = DateTime->now_local;
69 if ($params{bestbefore}) {
70 Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
71 push @where, sprintf "(bestbefore IS NULL OR bestbefore >= ?)";
72 push @values, $params{bestbefore};
77 part => [ qw(parts_id) ],
78 bin => [ qw(bin_id inventory.warehouse_id)],
79 warehouse => [ qw(inventory.warehouse_id) ],
80 chargenumber => [ qw(chargenumber) ],
81 bestbefore => [ qw(bestbefore) ],
82 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
86 for (listify($params{by})) {
87 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
88 push @selects, @$selects;
89 push @groups, @$selects;
93 my $select = join ',', @selects;
94 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
95 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
98 SELECT $select FROM inventory
99 LEFT JOIN bin ON bin_id = bin.id
100 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
105 $query .= ' HAVING SUM(qty) > 0';
108 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
111 part => 'SL::DB::Manager::Part',
112 bin => 'SL::DB::Manager::Bin',
113 warehouse => 'SL::DB::Manager::Warehouse',
119 warehouse => 'warehouse_id',
122 if ($params{by} && $params{with_objects}) {
123 for my $with_object (listify($params{with_objects})) {
124 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
126 my $manager = $with_objects{$with_object};
127 my $slot = $slots{$with_object};
128 next if !(my @ids = map { $_->{$slot} } @$results);
129 my $objects = $manager->get_all(query => [ id => \@ids ]);
130 my %objects_by_id = map { $_->id => $_ } @$objects;
132 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
139 return $results->[0]{qty};
144 _get_stock_onhand(@_, onhand => 0);
148 _get_stock_onhand(@_, onhand => 1);
154 die SL::X::Inventory::Allocation->new(
155 error => 'allocate needs a part',
156 msg => t8("Method allocate needs the parameter 'part'"),
157 ) unless $params{part};
158 die SL::X::Inventory::Allocation->new(
159 error => 'allocate needs a qty',
160 msg => t8("Method allocate needs the parameter 'qty'"),
161 ) unless $params{qty};
163 my $part = $params{part};
164 my $qty = $params{qty};
166 return () if $qty <= 0;
168 my $results = get_stock(part => $part, by => 'for_allocate');
169 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
170 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
171 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
173 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
174 my @sorted_results = sort {
175 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
176 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
177 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
178 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
183 for my $chunk (@sorted_results) {
184 my $qty = min($chunk->{qty}, $rest_qty);
186 # since allocate operates on stock, this also ensures that no negative stock results are used
188 push @allocations, SL::Helper::Inventory::Allocation->new(
189 parts_id => $chunk->{parts_id},
191 comment => $params{comment},
192 bin_id => $chunk->{bin_id},
193 warehouse_id => $chunk->{warehouse_id},
194 chargenumber => $chunk->{chargenumber},
195 bestbefore => $chunk->{bestbefore},
196 for_object_id => undef,
198 $rest_qty -= _round_number($qty, 5);
200 $rest_qty = _round_number($rest_qty, 5);
201 last if $rest_qty == 0;
204 die SL::X::Inventory::Allocation->new(
205 error => 'not enough to allocate',
206 msg => t8("can not allocate #1 units of #2, missing #3 units", _number(\%::myconfig, $qty), $part->displayable_name, _number(\%::myconfig, $rest_qty)),
209 if ($params{constraints}) {
210 check_constraints($params{constraints},\@allocations);
216 sub allocate_for_assembly {
219 my $part = $params{part} or Carp::croak('allocate needs a part');
220 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
222 Carp::croak('not an assembly') unless $part->is_assembly;
224 my %parts_to_allocate;
226 for my $assembly ($part->assemblies) {
227 next if $assembly->part->dispotype eq 'no_stock';
229 my $tmpqty = $assembly->part->unit eq 'Stck' ? ceil($assembly->qty * $qty)
230 : $assembly->qty * $qty;
231 $parts_to_allocate{ $assembly->part->id } //= 0;
232 $parts_to_allocate{ $assembly->part->id } += $tmpqty;
237 for my $part_id (keys %parts_to_allocate) {
238 my $part = SL::DB::Part->load_cached($part_id);
239 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
245 sub check_constraints {
246 my ($constraints, $allocations) = @_;
247 if ('CODE' eq ref $constraints) {
248 if (!$constraints->(@$allocations)) {
249 die SL::X::Inventory::Allocation->new(
250 error => 'allocation constraints failure',
251 msg => t8("Allocations didn't pass constraints"),
255 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
257 my %supported_constraints = (
259 warehouse_id => 'warehouse_id',
260 chargenumber => 'chargenumber',
263 for (keys %$constraints ) {
264 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
265 next unless defined $constraints->{$_};
267 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
268 my $accessor = $supported_constraints{$_};
270 if (any { !$whitelist{$_->$accessor} } @$allocations) {
271 my %error_constraints = (
272 bin_id => t8('Bins'),
273 warehouse_id => t8('Warehouses'),
274 chargenumber => t8('Chargenumbers'),
276 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
277 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
278 my $err = t8("Cannot allocate parts.");
279 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
280 SL::DB::Part->load_cached($_->parts_id)->description,
281 SL::DB::Bin->load_cached($_->bin_id)->full_description,
282 _number($_->qty), _number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
283 die SL::X::Inventory::Allocation->new(
284 error => 'allocation constraints failure',
292 sub produce_assembly {
295 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
296 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
298 my $allocations = $params{allocations};
299 if ($params{auto_allocate}) {
300 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
301 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
303 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
304 $allocations = $params{allocations};
307 my $bin = $params{bin} or Carp::croak("need target bin");
308 my $chargenumber = $params{chargenumber};
309 my $bestbefore = $params{bestbefore};
310 my $for_object_id = $params{for_object_id};
311 my $comment = $params{comment} // '';
313 my $invoice = $params{invoice};
314 my $project = $params{project};
316 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
318 my $trans_id = $params{trans_id};
319 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
321 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
322 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
324 # check whether allocations are sane
325 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
326 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
327 for my $assembly ($part->assemblies) {
328 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty;
331 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
335 for my $allocation (@$allocations) {
336 my $oe_id = delete $allocation->{for_object_id};
337 push @transfers, SL::DB::Inventory->new(
338 trans_id => $trans_id,
340 qty => -$allocation->qty,
341 trans_type => $trans_type_out,
342 shippingdate => $shippingdate,
343 employee => SL::DB::Manager::Employee->current,
344 oe_id => $allocation->for_object_id,
348 push @transfers, SL::DB::Inventory->new(
349 trans_id => $trans_id,
350 trans_type => $trans_type_in,
354 warehouse => $bin->warehouse_id,
355 chargenumber => $chargenumber,
356 bestbefore => $bestbefore,
357 shippingdate => $shippingdate,
361 employee => SL::DB::Manager::Employee->current,
362 oe_id => $for_object_id,
365 SL::DB->client->with_transaction(sub {
366 $_->save for @transfers;
369 die SL::DB->client->error;
375 sub default_show_bestbefore {
376 $::instance_conf->get_show_bestbefore
379 package SL::Helper::Inventory::Allocation {
380 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
381 my %attributes = map { $_ => 1 } @attributes;
383 for my $name (@attributes) {
385 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
389 my ($class, %params) = @_;
391 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
392 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
393 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
394 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
396 bless { %params }, $class;
406 SL::WH - Warehouse and Inventory API
410 # See description for an intro to the concepts used here.
412 use SL::Helper::Inventory qw(:ALL);
414 # stock, get "what's there" for a part with various conditions:
415 my $qty = get_stock(part => $part); # how much is on stock?
416 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
417 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
418 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
419 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
421 # onhand, get "what's available" for a part with various conditions:
422 my $qty = get_onhand(part => $part); # how much is available?
423 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
424 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
425 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
426 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
429 my $data = get_onhand(
430 warehouse => $warehouse,
431 by => [ qw(bin part chargenumber) ],
432 with_objects => [ qw(bin part) ],
436 my @allocations, allocate(
437 part => $part, # part_id works too
438 qty => $qty, # must be positive
439 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
440 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
441 bin => $bin, # optional, may be arrayref. if provided
444 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
445 my @allocations, allocate_for_assembly(
446 part => $assembly, # part_id works too
447 qty => $qty, # must be positive
450 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
451 my $allocation = SL::Helper::Inventory::Allocation->new(
452 part_id => $part->id,
454 bin_id => $bin_obj->id,
455 warehouse_id => $bin_obj->warehouse_id,
456 chargenumber => '1823772365',
458 for_object_id => $order->id,
463 part => $part, # target assembly
465 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
468 bin => $bin, # needed unless a global standard target is configured
469 chargenumber => $chargenumber, # optional
470 bestbefore => $datetime, # optional
471 comment => $comment, # optional
473 # links, all optional
478 New functions for the warehouse and inventory api.
480 The WH api currently has three large shortcomings. It is very hard to just get
481 the current stock for an item, it's extremely complicated to use it to produce
482 assemblies while ensuring that no stock ends up negative, and it's very hard to
483 use it to get an overview over the actual contents of the inventory.
485 The first problem has spawned several dozen small functions in the program that
486 try to implement that, and those usually miss some details. They may ignore
487 bestbefore times, comments, ignore negative quantities etc.
489 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
493 =item * Stock is defined as the actual contents of the inventory, everything that is
496 =item * Onhand is what is available, which means things that are stocked,
497 not expired and not reserved for other uses.
501 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
502 allow simple access with some optional filters for chargenumbers or warehouses.
503 Both of them have a batch mode that can be used to get these information to
504 supllement smiple reports.
506 To address the safe assembly creation a new function has been added.
507 C<allocate> will try to find the requested quantity of a part in the inventory
508 and will return allocations of it which can then be used to create the
509 assembly. Allocation will happen with the C<onhand> semantics defined above,
510 meaning that by default no expired goods will be used. The caller can supply
511 hints of what shold be used and in those cases chargenumbers will be used up as
512 much as possible first. C<allocate> will always try to fulfil the request even
513 beyond those. Should the required amount not be stocked, allocate will throw an
516 C<produce_assembly> has been rewritten to only accept parameters about the
517 target of the production, and requires allocations to complete the request. The
518 allocations can be supplied manually, or can be generated automatically.
519 C<produce_assembly> will check whether enough allocations are given to create
520 the recipe, but will not check whether the allocations are backed. If the
521 allocations are not sufficient or if the auto-allocation fails an exception
522 is returned. If you need to produce something that is not in the inventory, you
523 can bypass those checks by creating the allocations yourself (see
524 L</"ALLOCATION DATA STRUCTURE">).
526 Note: this is only intended to cover the scenarios described above. For other cases:
532 If you need actual inventory objects because of record links or something like
533 that load them directly. And strongly consider redesigning that, because it's
538 You need weight or accounting information you're on your own. The inventory api
539 only concerns itself with the raw quantities.
543 If you need the first stock date of parts, or anything related to a specific
544 transfer type or direction, this is not covered yet.
552 =item * get_stock PARAMS
554 Returns for single parts how much actually exists in the inventory.
562 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
566 If given, will only return stock on these bins. Optional. May be array, May be object or id.
570 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
574 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
578 If given, will only show stock with this chargenumber. Optional. May be array.
582 See L</"STOCK/ONHAND REPORT MODE">
586 See L</"STOCK/ONHAND REPORT MODE">
590 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
591 mode when C<by> is given.
593 =item * get_onhand PARAMS
595 Returns for single parts how much is available in the inventory. That excludes
596 stock with expired bestbefore.
598 It takes the same options as L</get_stock>.
604 If given, will only return stock with a bestbefore at or after the given date.
605 Optional. Must be L<DateTime> object.
609 =item * allocate PARAMS
621 Bin object. Optional.
625 Warehouse object. Optional.
637 Tries to allocate the required quantity using what is currently onhand. If
638 given any of C<bin>, C<warehouse>, C<chargenumber>
640 =item * allocate_for_assembly PARAMS
642 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
643 compute the required amount for each assembly part and allocate all of them.
645 =item * produce_assembly
650 =head1 STOCK/ONHAND REPORT MODE
652 If the special option C<by> is given with an arrayref, the result will instead
653 be an arrayref of partitioned stocks by those fields. Valid partitions are:
659 If this is given, part is optional in the parameters
671 Note: If you want to use the returned data to create allocations you I<need> to
672 enable all of these. To make this easier a special shortcut exists
674 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
675 C<parts> objects in one go, just like with Rose. They
676 need to be present in C<by> before that though.
678 =head1 ALLOCATION ALGORITHM
680 When calling allocate, the current onhand (== available stock) of the item will
681 be used to decide which bins/chargenumbers/bestbefore can be used.
683 In general allocate will try to make the request happen, and will use the
684 provided charges up first, and then tap everything else. If you need to only
685 I<exactly> use the provided charges, you'll need to craft the allocations
686 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
688 If C<chargenumber> is given, those will be used up next.
690 After that normal quantities will be used.
692 These are tiebreakers and expected to rarely matter in reality. If you need
693 finegrained control over which allocation is used, you may want to get the
694 onhands yourself and select the appropriate ones.
696 Only quantities with C<bestbefore> unset or after the given date will be
697 considered. If more than one charge is eligible, the earlier C<bestbefore>
700 Allocations do NOT have an internal memory and can't react to other allocations
701 of the same part earlier. Never double allocate the same part within a
704 =head1 ALLOCATION DATA STRUCTURE
706 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
707 each of the following attributes to be set at creation time:
723 =item * for_object_id
725 If set the allocations will be marked as allocated for the given object.
726 If these allocations are later used to produce an assembly, the resulting
727 consuming transactions will be marked as belonging to the given object.
728 The object may be an order, productionorder or other objects
732 C<chargenumber>, C<bestbefore> and C<for_object_id> may be C<undef> (but must
733 still be present at creation time). Instances are considered immutable.
738 # whitelist constraints
742 bin_id => \@allowed_bins,
743 chargenumber => \@allowed_chargenumbers,
750 # only allow chargenumbers with specific format
751 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
754 # and must all have a bestbefore date
755 all { $_->bestbefore } @_;
759 C<allocation> is "best effort" in nature. It will take the C<bin>,
760 C<chargenumber> etc hints from the parameters, but will try it's bvest to
761 fulfil the request anyway and only bail out if it is absolutely not possible.
763 Sometimes you need to restrict allocations though. For this you can pass
764 additional constraints to C<allocate>. A constraint serves as a whitelist.
765 Every allocation must fulfil every constraint by having that attribute be one
768 In case even that is not enough, you may supply a custom check by passing a
769 function that will be given the allocation objects.
771 Note that both whitelists and constraints do not influence the order of
772 allocations, which is done purely from the initial parameters. They only serve
773 to reject allocations made in good faith which do fulfil required assertions.
775 =head1 ERROR HANDLING
777 C<allocate> and C<produce_assembly> will throw exceptions if the request can
778 not be completed. The usual reason will be insufficient onhand to allocate, or
779 insufficient allocations to process the request.
781 =head1 KNOWN PROBLEMS
783 * It's not currently possible to identify allocations between requests, for
784 example for presenting the user possible allocations and then actually using
785 them on the next request.
786 * It's not currently possible to give C<allocate> prior constraints.
787 Currently all constraints are treated as hints (and will be preferred) but
788 the internal ordering of the hints is fixed and more complex preferentials
790 * bestbefore handling is untested
794 * define and describe error classes
795 * define wrapper classes for stock/onhand batch mode return values
796 * handle extra arguments in produce: shippingdate, project
797 * clean up allocation helper class
807 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>