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);
11 use SL::Locale::String qw(t8);
12 use SL::MoreCommon qw(listify);
13 use SL::DBUtils qw(selectall_hashref_query selectrow_query);
14 use SL::DB::TransferType;
15 use SL::Helper::Number qw(_round_qty _qty);
18 our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
19 our %EXPORT_TAGS = (ALL => \@EXPORT_OK);
21 sub _get_stock_onhand {
24 my $onhand_mode = !!$params{onhand};
26 my @selects = ('SUM(qty) as qty');
32 my @ids = map { ref $_ ? $_->id : $_ } listify($params{part});
33 push @where, sprintf "parts_id IN (%s)", join ', ', ("?") x @ids;
38 my @ids = map { ref $_ ? $_->id : $_ } listify($params{bin});
39 push @where, sprintf "bin_id IN (%s)", join ', ', ("?") x @ids;
43 if ($params{warehouse}) {
44 my @ids = map { ref $_ ? $_->id : $_ } listify($params{warehouse});
45 push @where, sprintf "warehouse.id IN (%s)", join ', ', ("?") x @ids;
49 if ($params{chargenumber}) {
50 my @ids = listify($params{chargenumber});
51 push @where, sprintf "chargenumber IN (%s)", join ', ', ("?") x @ids;
56 push @where, sprintf "shippingdate <= ?";
57 push @values, $params{date};
60 if ($params{bestbefore}) {
61 push @where, sprintf "bestbefore >= ?";
62 push @values, $params{bestbefore};
66 if ($params{onhand} && !$params{warehouse}) {
67 push @where, 'NOT warehouse.forreserve';
71 if ($params{onhand} && !$params{reserve_for}) {
72 push @where, 'reserve_for_id IS NULL AND reserve_for_table IS NULL';
75 if ($params{reserve_for}) {
76 my @objects = listify($params{chargenumber});
78 push @tokens, ( "(reserve_for_id = ? AND reserve_for_table = ?)") x @objects;
79 push @values, map { ($_->id, $_->meta->table) } @objects;
80 push @where, '(' . join(' OR ', @tokens) . ')';
85 part => [ qw(parts_id) ],
86 bin => [ qw(bin_id inventory.warehouse_id warehouse.forreserve)],
87 warehouse => [ qw(inventory.warehouse_id warehouse.forreserve) ],
88 chargenumber => [ qw(chargenumber) ],
89 bestbefore => [ qw(bestbefore) ],
90 reserve_for => [ qw(reserve_for_id reserve_for_table) ],
91 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id warehouse.forreserve chargenumber bestbefore reserve_for_id reserve_for_table) ],
95 for (listify($params{by})) {
96 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
97 push @selects, @$selects;
98 push @groups, @$selects;
102 my $select = join ',', @selects;
103 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
104 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
107 SELECT $select FROM inventory
108 LEFT JOIN bin ON bin_id = bin.id
109 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
114 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
117 part => 'SL::DB::Manager::Part',
118 bin => 'SL::DB::Manager::Bin',
119 warehouse => 'SL::DB::Manager::Warehouse',
120 reserve_for => undef,
126 warehouse => 'warehouse_id',
129 if ($params{by} && $params{with_objects}) {
130 for my $with_object (listify($params{with_objects})) {
131 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
133 if (my $manager = $with_objects{$with_object}) {
134 my $slot = $slots{$with_object};
135 next if !(my @ids = map { $_->{$slot} } @$results);
136 my $objects = $manager->get_all(query => [ id => \@ids ]);
137 my %objects_by_id = map { $_->id => $_ } @$objects;
139 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
141 # need to fetch all reserve_for_table partitions
149 return $results->[0]{qty};
154 _get_stock_onhand(@_, onhand => 0);
158 _get_stock_onhand(@_, onhand => 1);
164 my $part = $params{part} or Carp::croak('allocate needs a part');
165 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
167 return () if $qty <= 0;
169 my $results = get_stock(part => $part, by => 'for_allocate');
170 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{bin});
171 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{warehouse});
172 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } listify($params{chargenumber});
173 my %reserve_whitelist;
174 if ($params{reserve_for}) {
175 $reserve_whitelist{ $_->meta->table }{ $_->id } = 1 for listify($params{reserve_for});
178 # filter the results. we don't want:
180 # - bins that are reserve but not in the white-list of warehouses or bins
181 # - reservations that are not white-listed
183 my @filtered_results = grep {
184 (!$_->{forreserve} || $bin_whitelist{$_->{bin_id}} || $wh_whitelist{$_->{warehouse_id}})
185 && (!$_->{reserve_for_id} || $reserve_whitelist{ $_->{reserve_for_table} }{ $_->{reserve_for_id} })
188 # sort results so that reserve_for is first, then chargenumbers, then wanted bins, then wanted warehouses
189 my @sorted_results = sort {
190 (!!$b->{reserve_for_id}) <=> (!!$a->{reserve_for_id}) # sort by existing reserve_for_id first.
191 || exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
192 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
193 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
198 for my $chunk (@sorted_results) {
199 my $qty = min($chunk->{qty}, $rest_qty);
201 push @allocations, SL::Helper::Inventory::Allocation->new(
202 parts_id => $chunk->{parts_id},
204 comment => $params{comment},
205 bin_id => $chunk->{bin_id},
206 warehouse_id => $chunk->{warehouse_id},
207 chargenumber => $chunk->{chargenumber},
208 bestbefore => $chunk->{bestbefore},
209 reserve_for_id => $chunk->{reserve_for_id},
210 reserve_for_table => $chunk->{reserve_for_table},
216 last if $rest_qty == 0;
219 die SL::X::Inventory::Allocation->new(
220 error => 'not enough to allocate',
221 msg => t8("can not allocate #1 units of #2, missing #3 units", $qty, $part->displayable_name, $rest_qty),
224 if ($params{constraints}) {
225 check_constraints($params{constraints},\@allocations);
231 sub allocate_for_assembly {
234 my $part = $params{part} or Carp::croak('allocate needs a part');
235 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
237 Carp::croak('not an assembly') unless $part->is_assembly;
239 my %parts_to_allocate;
241 for my $assembly ($part->assemblies) {
242 $parts_to_allocate{ $assembly->part->id } //= 0;
243 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty; # TODO recipe factor
248 for my $part_id (keys %parts_to_allocate) {
249 my $part = SL::DB::Part->load_cached($part_id);
250 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
256 sub check_constraints {
257 my ($constraints, $allocations) = @_;
258 if ('CODE' eq ref $constraints) {
259 if (!$constraints->(@$allocations)) {
260 die SL::X::Inventory::Allocation->new(
261 error => 'allocation constraints failure',
262 msg => t8("Allocations didn't pass constraints"),
266 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
268 my %supported_constraints = (
270 warehouse_id => 'warehouse_id',
271 chargenumber => 'chargenumber',
274 for (keys %$constraints ) {
275 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
277 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
278 my $accessor = $supported_constraints{$_};
280 if (any { !$whitelist{$_->$accessor} } @$allocations) {
281 my %error_constraints = (
282 bin_id => t8('Bins'),
283 warehouse_id => t8('Warehouses'),
284 chargenumber => t8('Chargenumbers'),
286 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
287 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
288 my $err = t8("Cannot allocate parts.");
289 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
290 SL::DB::Part->load_cached($_->parts_id)->description,
291 SL::DB::Bin->load_cached($_->bin_id)->full_description,
292 _qty($_->qty), _qty($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
293 die SL::X::Inventory::Allocation->new(
294 error => 'allocation constraints failure',
302 sub produce_assembly {
305 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
306 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
308 my $allocations = $params{allocations};
309 if ($params{auto_allocate}) {
310 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
311 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
313 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
314 $allocations = $params{allocations};
317 my $bin = $params{bin} or Carp::croak("need target bin");
318 my $chargenumber = $params{chargenumber};
319 my $bestbefore = $params{bestbefore};
320 my $oe_id = $params{oe_id};
321 my $comment = $params{comment} // '';
323 my $production_order_item = $params{production_order_item};
324 my $invoice = $params{invoice};
325 my $project = $params{project};
326 my $reserve_for = $params{reserve_for};
328 my $reserve_for_id = $reserve_for ? $reserve_for->id : undef;
329 my $reserve_for_table = $reserve_for ? $reserve_for->meta->table : undef;
331 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
333 my $trans_id = $params{trans_id};
334 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
336 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
337 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
339 # check whether allocations are sane
340 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
341 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
342 for my $assembly ($part->assemblies) {
343 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
346 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
350 for my $allocation (@$allocations) {
351 push @transfers, SL::DB::Inventory->new(
352 trans_id => $trans_id,
354 qty => -$allocation->qty,
355 trans_type => $trans_type_out,
356 shippingdate => $shippingdate,
357 employee => SL::DB::Manager::Employee->current,
358 oe_id => $allocation->oe_id,
362 push @transfers, SL::DB::Inventory->new(
363 trans_id => $trans_id,
364 trans_type => $trans_type_in,
368 warehouse => $bin->warehouse_id,
369 chargenumber => $chargenumber,
370 bestbefore => $bestbefore,
371 reserve_for_id => $reserve_for_id,
372 reserve_for_table => $reserve_for_table,
373 shippingdate => $shippingdate,
377 prod => $production_order_item,
378 employee => SL::DB::Manager::Employee->current,
382 SL::DB->client->with_transaction(sub {
383 $_->save for @transfers;
386 die SL::DB->client->error;
392 package SL::Helper::Inventory::Allocation {
393 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment reserve_for_id reserve_for_table oe_id);
394 my %attributes = map { $_ => 1 } @attributes;
396 for my $name (@attributes) {
398 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
402 my ($class, %params) = @_;
404 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
405 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
406 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
407 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
409 bless { %params }, $class;
419 SL::WH - Warehouse and Inventory API
423 # See description for an intro to the concepts used here.
425 use SL::Helper::Inventory;
427 # stock, get "what's there" for a part with various conditions:
428 my $qty = SL::Helper::Inventory->get_stock(part => $part); # how much is on stock?
429 my $qty = SL::Helper::Inventory->get_stock(part => $part, date => $date); # how much was on stock at a specific time?
430 my $qty = SL::Helper::Inventory->get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
431 my $qty = SL::Helper::Inventory->get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
432 my $qty = SL::Helper::Inventory->get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
434 # onhand, get "what's available" for a part with various conditions:
435 my $qty = SL::Helper::Inventory->get_onhand(part => $part); # how much is available?
436 my $qty = SL::Helper::Inventory->get_onhand(part => $part, date => $date); # how much was available at a specific time?
437 my $qty = SL::Helper::Inventory->get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
438 my $qty = SL::Helper::Inventory->get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
439 my $qty = SL::Helper::Inventory->get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
440 my $qty = SL::Helper::Inventory->get_onhand(part => $part, reserve_for => $order); # how much is available if you include this reservation?
443 my $data = SL::Helper::Inventory->get_onhand(
444 warehouse => $warehouse,
445 by => [ qw(bin part chargenumber reserve_for) ],
446 with_objects => [ qw(bin part) ],
450 my @allocations, SL::Helper::Inventory->allocate(
451 part => $part, # part_id works too
452 qty => $qty, # must be positive
453 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
454 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
455 reserve_for => $object, # optional, may be arrayref. if provided the qtys reserved for these objects will be used first
456 bin => $bin, # optional, may be arrayref. if provided
459 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
460 my @allocations, SL::Helper::Inventory->allocate_for_assembly(
461 part => $assembly, # part_id works too
462 qty => $qty, # must be positive
465 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
466 my $allocation = SL::Helper::Inventory::Allocation->new(
467 part_id => $part->id,
469 bin_id => $bin_obj->id,
470 warehouse_id => $bin_obj->warehouse_id,
471 chargenumber => '1823772365',
473 reserve_for_id => undef,
474 reserve_for_table => undef,
475 oe_id => $my_document,
479 SL::Helper::Inventory->produce_assembly(
480 part => $part, # target assembly
482 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
485 bin => $bin, # needed unless a global standard target is configured
486 chargenumber => $chargenumber, # optional
487 bestbefore => $datetime, # optional
488 comment => $comment, # optional
490 # links, all optional
491 production_order_item => $item,
492 reserve_for => $object,
497 New functions for the warehouse and inventory api.
499 The WH api currently has three large shortcomings. It is very hard to just get
500 the current stock for an item, it's extremely complicated to use it to produce
501 assemblies while ensuring that no stock ends up negative, and it's very hard to
502 use it to get an overview over the actual contents of the inventory.
504 The first problem has spawned several dozen small functions in the program that
505 try to implement that, and those usually miss some details. They may ignore
506 reservations, or reserve warehouses, or bestbefore times.
508 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
510 Stock is defined as the actual contents of the inventory, everything that is
511 there. Onhand is what is available, which means things that are stocked and not
512 reserved and not expired.
514 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
515 allow simple access with some optional filters for chargenumbers or warehouses.
516 Both of them have a batch mode that can be used to get these information to
517 supllement smiple reports.
519 To address the safe assembly creation a new function has been added.
520 C<allocate> will try to find the requested quantity of a part in the inventory
521 and will return allocations of it which can then be used to create the
522 assembly. Allocation will happen with the C<onhand> semantics defined above,
523 meaning that by default no reservations or expired goods will be used. The
524 caller can supply hints of what shold be used and in those cases chargenumber
525 and reservations will be used up as much as possible first. C<allocate> will
526 always try to fulfil the request even beyond those. Should the required amount
527 not be stocked, allocate will throw an exception.
529 C<produce_assembly> has been rewritten to only accept parameters about the
530 target of the production, and requires allocations to complete the request. The
531 allocations can be supplied manually, or can be generated automatically.
532 C<produce_assembly> will check whether enough allocations are given to create
533 the recipe, but will not check whether the allocations are backed. If the
534 allocations are not sufficient or if the auto-allocation fails an exception
535 is returned. If you need to produce something that is not in the inventory, you
536 can bypass those checks by creating the allocations yourself (see
537 L</"ALLOCATION DATA STRUCTURE">).
539 Note: this is only intended to cover the scenarios described above. For other cases:
545 If you need the reserved amount for an order use C<SL::DB::Helper::Reservation>
550 If you need actual inventory objects because of record links, prod_id links or
551 something like that load them directly. And strongly consider redesigning that,
552 because it's really fragile.
556 You need weight or accounting information you're on your own. The inventory api
557 only concerns itself with the raw quantities.
561 If you need the first stock date of parts, or anything related to a specific
562 transfer type or direction, this is not covered yet.
570 =item * get_stock PARAMS
572 Returns for single parts how much actually exists in the inventory.
580 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
584 If given, will only return stock on these bins. Optional. May be array, May be object or id.
588 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
592 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
596 If given, will only show stock with this chargenumber. Optional. May be array.
600 See L</"STOCK/ONHAND REPORT MODE">
604 See L</"STOCK/ONHAND REPORT MODE">
608 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
609 mode when C<by> is given.
611 =item * get_onhand PARAMS
613 Returns for single parts how much is available in the inventory. That excludes:
614 reserved quantities, reserved warehouses and stock with expired bestbefore.
616 It takes all options of L</get_stock> but treats some of the differently and has some additional ones:
622 Usually C<onhand> will not include results from warehouses with the C<reserve>
623 flag. However giving an explicit list of warehouses will include there in the
624 search, as well as all others.
628 =item * reserve_warehouse
634 =item * allocate PARAMS
646 Bin object. Optional.
650 Warehouse object. Optional.
662 Needs to be a rose object, where id and table can be extracted. Optional.
666 Tries to allocate the required quantity using what is currently onhand. If
667 given any of C<bin>, C<warehouse>, C<chargenumber>, C<reserve_for>
670 =item * allocate_for_assembly PARAMS
672 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
673 compute the required amount for each assembly part and allocate all of them.
675 =item * produce_assembly
680 =head1 STOCK/ONHAND REPORT MODE
682 If the special option C<by> is given with an arrayref, the result will instead
683 be an arrayref of partitioned stocks by those fields. Valid partitions are:
689 If this is given, part is optional in the parameters
703 Note: If you want to use the returned data to create allocations you I<need> to
704 enable all of these. To make this easier a special shortcut exists
706 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
707 C<parts>, and the C<reserve_for> objects in one go, just like with Rose. They
708 need to be present in C<by> before that though.
710 =head1 ALLOCATION ALGORITHM
712 When calling allocate, the current onhand (== available stock) of the item will
713 be used to decide which bins/chargenumbers/bestbefore can be used.
715 In general allocate will try to make the request happen, and will use the
716 provided charges up first, and then tap everything else. If you need to only
717 I<exactly> use the provided charges, you'll need to craft the allocations
718 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
720 If C<reserve_for> is given, those will be used up first too.
722 If C<reserved_warehouse> is given, those will be used up second.
724 If C<chargenumber> is given, those will be used up next.
726 After that normal quantities will be used.
728 These are tiebreakers and expected to rarely matter in reality. If you need
729 finegrained control over which allocation is used, you may want to get the
730 onhands yourself and select the appropriate ones.
732 Only quantities with C<bestbefore> unset or after the given date will be
733 considered. If more than one charge is eligible, the earlier C<bestbefore>
736 Allocations do NOT have an internal memory and can't react to other allocations
737 of the same part earlier. Never double allocate the same part within a
740 =head1 ALLOCATION DATA STRUCTURE
742 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
743 each of the following attributes to be set at creation time:
759 =item * reserve_for_id
761 =item * reserve_for_table
765 Must be explicit set if the allocation needs also an (other) document.
769 C<chargenumber>, C<bestbefore>, C<reserve_for_id>, C<reserve_for_table> and oe_id may
770 be C<undef> (but must still be present at creation time). Instances are
771 considered immutable.
776 # whitelist constraints
780 bin_id => \@allowed_bins,
781 chargenumber => \@allowed_chargenumbers,
788 # only allow chargenumbers with specific format
789 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
792 # and must be all reservations
793 all { $_->reserve_for_id } @_;
797 C<allocation> is "best effort" in nature. It will take the C<bin>,
798 C<chargenumber> etc hints from the parameters, but will try it's bvest to
799 fulfil the request anyway and only bail out if it is absolutely not possible.
801 Sometimes you need to restrict allocations though. For this you can pass
802 additional constraints to C<allocate>. A constraint serves as a whitelist.
803 Every allocation must fulfil every constraint by having that attribute be one
806 In case even that is not enough, you may supply a custom check by passing a
807 function that will be given the allocation objects.
809 Note that both whitelists and constraints do not influence the order of
810 allocations, which is done purely from the initial parameters. They only serve
811 to reject allocations made in good faith which do fulfil required assertions.
813 =head1 ERROR HANDLING
815 C<allocate> and C<produce_assembly> will throw exceptions if the request can
816 not be completed. The usual reason will be insufficient onhand to allocate, or
817 insufficient allocations to process the request.
821 * define and describe error classes
822 * define wrapper classes for stock/onhand batch mode return values
823 * handle extra arguments in produce: shippingdate, project, oe
824 * clean up allocation helper class
825 * with objects for reservations
835 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>