1 package SL::Helper::Inventory;
6 use Exporter qw(import);
7 use List::Util qw(min);
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;
17 our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
18 our %EXPORT_TAGS = (ALL => \@EXPORT_OK);
20 sub _get_stock_onhand {
23 my $onhand_mode = !!$params{onhand};
25 my @selects = ('SUM(qty) as qty');
31 my @ids = map { ref $_ ? $_->id : $_ } listify($params{part});
32 push @where, sprintf "parts_id IN (%s)", join ', ', ("?") x @ids;
37 my @ids = map { ref $_ ? $_->id : $_ } listify($params{bin});
38 push @where, sprintf "bin_id IN (%s)", join ', ', ("?") x @ids;
42 if ($params{warehouse}) {
43 my @ids = map { ref $_ ? $_->id : $_ } listify($params{warehouse});
44 push @where, sprintf "warehouse.id IN (%s)", join ', ', ("?") x @ids;
48 if ($params{chargenumber}) {
49 my @ids = listify($params{chargenumber});
50 push @where, sprintf "chargenumber IN (%s)", join ', ', ("?") x @ids;
55 push @where, sprintf "shippingdate <= ?";
56 push @values, $params{date};
59 if ($params{bestbefore}) {
60 push @where, sprintf "bestbefore >= ?";
61 push @values, $params{bestbefore};
65 if ($params{onhand} && !$params{warehouse}) {
66 push @where, 'NOT warehouse.forreserve';
70 if ($params{onhand} && !$params{reserve_for}) {
71 push @where, 'reserve_for_id IS NULL AND reserve_for_table IS NULL';
74 if ($params{reserve_for}) {
75 my @objects = listify($params{chargenumber});
77 push @tokens, ( "(reserve_for_id = ? AND reserve_for_table = ?)") x @objects;
78 push @values, map { ($_->id, $_->meta->table) } @objects;
79 push @where, '(' . join(' OR ', @tokens) . ')';
84 part => [ qw(parts_id) ],
85 bin => [ qw(bin_id inventory.warehouse_id warehouse.forreserve)],
86 warehouse => [ qw(inventory.warehouse_id warehouse.forreserve) ],
87 chargenumber => [ qw(chargenumber) ],
88 bestbefore => [ qw(bestbefore) ],
89 reserve_for => [ qw(reserve_for_id reserve_for_table) ],
90 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id warehouse.forreserve chargenumber bestbefore reserve_for_id reserve_for_table) ],
94 for (listify($params{by})) {
95 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
96 push @selects, @$selects;
97 push @groups, @$selects;
101 my $select = join ',', @selects;
102 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
103 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
106 SELECT $select FROM inventory
107 LEFT JOIN bin ON bin_id = bin.id
108 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
113 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
116 part => 'SL::DB::Manager::Part',
117 bin => 'SL::DB::Manager::Bin',
118 warehouse => 'SL::DB::Manager::Warehouse',
119 reserve_for => undef,
125 warehouse => 'warehouse_id',
128 if ($params{by} && $params{with_objects}) {
129 for my $with_object (listify($params{with_objects})) {
130 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
132 if (my $manager = $with_objects{$with_object}) {
133 my $slot = $slots{$with_object};
134 next if !(my @ids = map { $_->{$slot} } @$results);
135 my $objects = $manager->get_all(query => [ id => \@ids ]);
136 my %objects_by_id = map { $_->id => $_ } @$objects;
138 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
140 # need to fetch all reserve_for_table partitions
148 return $results->[0]{qty};
153 _get_stock_onhand(@_, onhand => 0);
157 _get_stock_onhand(@_, onhand => 1);
163 my $part = $params{part} or Carp::croak('allocate needs a part');
164 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
166 return () if $qty <= 0;
168 my $results = get_stock(part => $part, by => 'for_allocate');
169 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{bin});
170 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($params{warehouse});
171 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } listify($params{chargenumber});
172 my %reserve_whitelist;
173 if ($params{reserve_for}) {
174 $reserve_whitelist{ $_->meta->table }{ $_->id } = 1 for listify($params{reserve_for});
177 # filter the results. we don't want:
179 # - bins that are reserve but not in the white-list of warehouses or bins
180 # - reservations that are not white-listed
182 my @filtered_results = grep {
183 (!$_->{forreserve} || $bin_whitelist{$_->{bin_id}} || $wh_whitelist{$_->{warehouse_id}})
184 && (!$_->{reserve_for_id} || $reserve_whitelist{ $_->{reserve_for_table} }{ $_->{reserve_for_id} })
187 # sort results so that reserve_for is first, then chargenumbers, then wanted bins, then wanted warehouses
188 my @sorted_results = sort {
189 (!!$b->{reserve_for_id}) <=> (!!$a->{reserve_for_id}) # sort by existing reserve_for_id first.
190 || exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
191 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
192 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
197 for my $chunk (@sorted_results) {
198 my $qty = min($chunk->{qty}, $rest_qty);
200 push @allocations, SL::Helper::Inventory::Allocation->new(
201 parts_id => $chunk->{parts_id},
203 comment => $params{comment},
204 bin_id => $chunk->{bin_id},
205 warehouse_id => $chunk->{warehouse_id},
206 chargenumber => $chunk->{chargenumber},
207 bestbefore => $chunk->{bestbefore},
208 reserve_for_id => $chunk->{reserve_for_id},
209 reserve_for_table => $chunk->{reserve_for_table},
215 last if $rest_qty == 0;
218 die SL::X::Inventory::Allocation->new(
219 error => t8('not enough to allocate'),
220 msg => t8("can not allocate #1 units of #2, missing #3 units", $qty, $part->displayable_name, $rest_qty),
223 if ($params{constraints}) {
224 check_constraints($params{constraints},\@allocations);
230 sub allocate_for_assembly {
233 my $part = $params{part} or Carp::croak('allocate needs a part');
234 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
236 Carp::croak('not an assembly') unless $part->is_assembly;
238 my %parts_to_allocate;
240 for my $assembly ($part->assemblies) {
241 $parts_to_allocate{ $assembly->part->id } //= 0;
242 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty; # TODO recipe factor
247 for my $part_id (keys %parts_to_allocate) {
248 my $part = SL::DB::Part->load_cached($part_id);
249 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
255 sub check_constraints {
256 my ($constraints, $allocations) = @_;
257 if ('CODE' eq ref $constraints) {
258 if (!$constraints->(@$allocations)) {
259 die SL::X::Inventory::Allocation->new(
260 error => 'allocation constraints failure',
261 msg => t8("Allocations didn't pass constraints"),
265 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
267 my %supported_constraints = (
269 warehouse_id => 'warehouse_id',
270 chargenumber => 'chargenumber',
273 for (keys %$constraints ) {
274 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
276 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
277 my $accessor = $supported_constraints{$_};
279 if (any { !$whitelist{$_->$accessor} } @$allocations) {
280 my %error_constraints = (
281 bin_id => t8('Bins'),
282 warehouse_id => t8('Warehouses'),
283 chargenumber => t8('Chargenumbers'),
285 my @allocs = grep { !$whitelist{$_->$accessor} } @$allocations;
286 die SL::X::Inventory::Allocation->new(
287 accessor => $accessor,
288 allocations => \@allocs,
289 error => 'allocation constraints failure',
290 msg => t8("Allocations didn't pass constraints for #1",$error_constraints{$_}),
297 sub produce_assembly {
300 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
301 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
303 my $allocations = $params{allocations};
304 if ($params{auto_allocate}) {
305 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
306 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
308 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
309 $allocations = $params{allocations};
312 my $bin = $params{bin} or Carp::croak("need target bin");
313 my $chargenumber = $params{chargenumber};
314 my $bestbefore = $params{bestbefore};
315 my $oe_id = $params{oe_id};
316 my $comment = $params{comment} // '';
318 my $production_order_item = $params{production_order_item};
319 my $invoice = $params{invoice};
320 my $project = $params{project};
321 my $reserve_for = $params{reserve_for};
323 my $reserve_for_id = $reserve_for ? $reserve_for->id : undef;
324 my $reserve_for_table = $reserve_for ? $reserve_for->meta->table : undef;
326 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
328 my $trans_id = $params{trans_id};
329 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
331 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
332 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
334 # check whether allocations are sane
335 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
336 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
337 for my $assembly ($part->assemblies) {
338 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
341 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
345 for my $allocation (@$allocations) {
346 push @transfers, SL::DB::Inventory->new(
347 trans_id => $trans_id,
349 qty => -$allocation->qty,
350 trans_type => $trans_type_out,
351 shippingdate => $shippingdate,
352 employee => SL::DB::Manager::Employee->current,
353 oe_id => $allocation->oe_id,
357 push @transfers, SL::DB::Inventory->new(
358 trans_id => $trans_id,
359 trans_type => $trans_type_in,
363 warehouse => $bin->warehouse_id,
364 chargenumber => $chargenumber,
365 bestbefore => $bestbefore,
366 reserve_for_id => $reserve_for_id,
367 reserve_for_table => $reserve_for_table,
368 shippingdate => $shippingdate,
372 prod => $production_order_item,
373 employee => SL::DB::Manager::Employee->current,
377 SL::DB->client->with_transaction(sub {
378 $_->save for @transfers;
381 die SL::DB->client->error;
387 package SL::Helper::Inventory::Allocation {
388 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment reserve_for_id reserve_for_table oe_id);
389 my %attributes = map { $_ => 1 } @attributes;
391 for my $name (@attributes) {
393 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
397 my ($class, %params) = @_;
399 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
400 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
401 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
402 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
404 bless { %params }, $class;
414 SL::WH - Warehouse and Inventory API
418 # See description for an intro to the concepts used here.
420 use SL::Helper::Inventory;
422 # stock, get "what's there" for a part with various conditions:
423 my $qty = SL::Helper::Inventory->get_stock(part => $part); # how much is on stock?
424 my $qty = SL::Helper::Inventory->get_stock(part => $part, date => $date); # how much was on stock at a specific time?
425 my $qty = SL::Helper::Inventory->get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
426 my $qty = SL::Helper::Inventory->get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
427 my $qty = SL::Helper::Inventory->get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
429 # onhand, get "what's available" for a part with various conditions:
430 my $qty = SL::Helper::Inventory->get_onhand(part => $part); # how much is available?
431 my $qty = SL::Helper::Inventory->get_onhand(part => $part, date => $date); # how much was available at a specific time?
432 my $qty = SL::Helper::Inventory->get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
433 my $qty = SL::Helper::Inventory->get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
434 my $qty = SL::Helper::Inventory->get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
435 my $qty = SL::Helper::Inventory->get_onhand(part => $part, reserve_for => $order); # how much is available if you include this reservation?
438 my $data = SL::Helper::Inventory->get_onhand(
439 warehouse => $warehouse,
440 by => [ qw(bin part chargenumber reserve_for) ],
441 with_objects => [ qw(bin part) ],
445 my @allocations, SL::Helper::Inventory->allocate(
446 part => $part, # part_id works too
447 qty => $qty, # must be positive
448 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
449 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
450 reserve_for => $object, # optional, may be arrayref. if provided the qtys reserved for these objects will be used first
451 bin => $bin, # optional, may be arrayref. if provided
454 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
455 my @allocations, SL::Helper::Inventory->allocate_for_assembly(
456 part => $assembly, # part_id works too
457 qty => $qty, # must be positive
460 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
461 my $allocation = SL::Helper::Inventory::Allocation->new(
462 part_id => $part->id,
464 bin_id => $bin_obj->id,
465 warehouse_id => $bin_obj->warehouse_id,
466 chargenumber => '1823772365',
468 reserve_for_id => undef,
469 reserve_for_table => undef,
470 oe_id => $my_document,
474 SL::Helper::Inventory->produce_assembly(
475 part => $part, # target assembly
477 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
480 bin => $bin, # needed unless a global standard target is configured
481 chargenumber => $chargenumber, # optional
482 bestbefore => $datetime, # optional
483 comment => $comment, # optional
485 # links, all optional
486 production_order_item => $item,
487 reserve_for => $object,
492 New functions for the warehouse and inventory api.
494 The WH api currently has three large shortcomings. It is very hard to just get
495 the current stock for an item, it's extremely complicated to use it to produce
496 assemblies while ensuring that no stock ends up negative, and it's very hard to
497 use it to get an overview over the actual contents of the inventory.
499 The first problem has spawned several dozen small functions in the program that
500 try to implement that, and those usually miss some details. They may ignore
501 reservations, or reserve warehouses, or bestbefore times.
503 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
505 Stock is defined as the actual contents of the inventory, everything that is
506 there. Onhand is what is available, which means things that are stocked and not
507 reserved and not expired.
509 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
510 allow simple access with some optional filters for chargenumbers or warehouses.
511 Both of them have a batch mode that can be used to get these information to
512 supllement smiple reports.
514 To address the safe assembly creation a new function has been added.
515 C<allocate> will try to find the requested quantity of a part in the inventory
516 and will return allocations of it which can then be used to create the
517 assembly. Allocation will happen with the C<onhand> semantics defined above,
518 meaning that by default no reservations or expired goods will be used. The
519 caller can supply hints of what shold be used and in those cases chargenumber
520 and reservations will be used up as much as possible first. C<allocate> will
521 always try to fulfil the request even beyond those. Should the required amount
522 not be stocked, allocate will throw an exception.
524 C<produce_assembly> has been rewritten to only accept parameters about the
525 target of the production, and requires allocations to complete the request. The
526 allocations can be supplied manually, or can be generated automatically.
527 C<produce_assembly> will check whether enough allocations are given to create
528 the recipe, but will not check whether the allocations are backed. If the
529 allocations are not sufficient or if the auto-allocation fails an exception
530 is returned. If you need to produce something that is not in the inventory, you
531 can bypass those checks by creating the allocations yourself (see
532 L</"ALLOCATION DATA STRUCTURE">).
534 Note: this is only intended to cover the scenarios described above. For other cases:
540 If you need the reserved amount for an order use C<SL::DB::Helper::Reservation>
545 If you need actual inventory objects because of record links, prod_id links or
546 something like that load them directly. And strongly consider redesigning that,
547 because it's really fragile.
551 You need weight or accounting information you're on your own. The inventory api
552 only concerns itself with the raw quantities.
556 If you need the first stock date of parts, or anything related to a specific
557 transfer type or direction, this is not covered yet.
565 =item * get_stock PARAMS
567 Returns for single parts how much actually exists in the inventory.
575 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
579 If given, will only return stock on these bins. Optional. May be array, May be object or id.
583 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
587 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
591 If given, will only show stock with this chargenumber. Optional. May be array.
595 See L</"STOCK/ONHAND REPORT MODE">
599 See L</"STOCK/ONHAND REPORT MODE">
603 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
604 mode when C<by> is given.
606 =item * get_onhand PARAMS
608 Returns for single parts how much is available in the inventory. That excludes:
609 reserved quantities, reserved warehouses and stock with expired bestbefore.
611 It takes all options of L</get_stock> but treats some of the differently and has some additional ones:
617 Usually C<onhand> will not include results from warehouses with the C<reserve>
618 flag. However giving an explicit list of warehouses will include there in the
619 search, as well as all others.
623 =item * reserve_warehouse
629 =item * allocate PARAMS
641 Bin object. Optional.
645 Warehouse object. Optional.
657 Needs to be a rose object, where id and table can be extracted. Optional.
661 Tries to allocate the required quantity using what is currently onhand. If
662 given any of C<bin>, C<warehouse>, C<chargenumber>, C<reserve_for>
665 =item * allocate_for_assembly PARAMS
667 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
668 compute the required amount for each assembly part and allocate all of them.
670 =item * produce_assembly
675 =head1 STOCK/ONHAND REPORT MODE
677 If the special option C<by> is given with an arrayref, the result will instead
678 be an arrayref of partitioned stocks by those fields. Valid partitions are:
684 If this is given, part is optional in the parameters
698 Note: If you want to use the returned data to create allocations you I<need> to
699 enable all of these. To make this easier a special shortcut exists
701 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
702 C<parts>, and the C<reserve_for> objects in one go, just like with Rose. They
703 need to be present in C<by> before that though.
705 =head1 ALLOCATION ALGORITHM
707 When calling allocate, the current onhand (== available stock) of the item will
708 be used to decide which bins/chargenumbers/bestbefore can be used.
710 In general allocate will try to make the request happen, and will use the
711 provided charges up first, and then tap everything else. If you need to only
712 I<exactly> use the provided charges, you'll need to craft the allocations
713 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
715 If C<reserve_for> is given, those will be used up first too.
717 If C<reserved_warehouse> is given, those will be used up second.
719 If C<chargenumber> is given, those will be used up next.
721 After that normal quantities will be used.
723 These are tiebreakers and expected to rarely matter in reality. If you need
724 finegrained control over which allocation is used, you may want to get the
725 onhands yourself and select the appropriate ones.
727 Only quantities with C<bestbefore> unset or after the given date will be
728 considered. If more than one charge is eligible, the earlier C<bestbefore>
731 Allocations do NOT have an internal memory and can't react to other allocations
732 of the same part earlier. Never double allocate the same part within a
735 =head1 ALLOCATION DATA STRUCTURE
737 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
738 each of the following attributes to be set at creation time:
754 =item * reserve_for_id
756 =item * reserve_for_table
760 Must be explicit set if the allocation needs also an (other) document.
764 C<chargenumber>, C<bestbefore>, C<reserve_for_id>, C<reserve_for_table> and oe_id may
765 be C<undef> (but must still be present at creation time). Instances are
766 considered immutable.
771 # whitelist constraints
775 bin_id => \@allowed_bins,
776 chargenumber => \@allowed_chargenumbers,
783 # only allow chargenumbers with specific format
784 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
787 # and must be all reservations
788 all { $_->reserve_for_id } @_;
792 C<allocation> is "best effort" in nature. It will take the C<bin>,
793 C<chargenumber> etc hints from the parameters, but will try it's bvest to
794 fulfil the request anyway and only bail out if it is absolutely not possible.
796 Sometimes you need to restrict allocations though. For this you can pass
797 additional constraints to C<allocate>. A constraint serves as a whitelist.
798 Every allocation must fulfil every constraint by having that attribute be one
801 In case even that is not enough, you may supply a custom check by passing a
802 function that will be given the allocation objects.
804 Note that both whitelists and constraints do not influence the order of
805 allocations, which is done purely from the initial parameters. They only serve
806 to reject allocations made in good faith which do fulfil required assertions.
808 =head1 ERROR HANDLING
810 C<allocate> and C<produce_assembly> will throw exceptions if the request can
811 not be completed. The usual reason will be insufficient onhand to allocate, or
812 insufficient allocations to process the request.
816 * define and describe error classes
817 * define wrapper classes for stock/onhand batch mode return values
818 * handle extra arguments in produce: shippingdate, project, oe
819 * clean up allocation helper class
820 * with objects for reservations
830 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>