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);
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 || $chargenumbers{$b->{chargenumber}} <=> $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
191 || $bin_whitelist{$b->{bin_id}} <=> $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
192 || $wh_whitelist{$b->{warehouse_id}} <=> $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},
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),
227 sub allocate_for_assembly {
230 my $part = $params{part} or Carp::croak('allocate needs a part');
231 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
233 Carp::croak('not an assembly') unless $part->is_assembly;
235 my %parts_to_allocate;
237 for my $assembly ($part->assemblies) {
238 $parts_to_allocate{ $assembly->part->id } //= 0;
239 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty; # TODO recipe factor
244 for my $part_id (keys %parts_to_allocate) {
245 my $part = SL::DB::Part->load_cached($part_id);
246 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
252 sub produce_assembly {
255 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
256 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
258 my $allocations = $params{allocations};
259 if ($params{auto_allocate}) {
260 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
261 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
263 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
264 $allocations = $params{allocations};
267 my $bin = $params{bin} or Carp::croak("need target bin");
268 my $chargenumber = $params{chargenumber};
269 my $bestbefore = $params{bestbefore};
270 my $oe_id = $params{oe_id};
271 my $comment = $params{comment} // '';
273 my $production_order_item = $params{production_order_item};
274 my $invoice = $params{invoice};
275 my $project = $params{project};
276 my $reserve_for = $params{reserve_for};
278 my $reserve_for_id = $reserve_for ? $reserve_for->id : undef;
279 my $reserve_for_table = $reserve_for ? $reserve_for->meta->table : undef;
281 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
283 my $trans_id = $params{trans_id};
284 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
286 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
287 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
289 # check whether allocations are sane
290 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
291 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
292 for my $assembly ($part->assemblies) {
293 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
296 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
300 for my $allocation (@$allocations) {
301 push @transfers, SL::DB::Inventory->new(
302 trans_id => $trans_id,
304 qty => -$allocation->qty,
305 trans_type => $trans_type_out,
306 shippingdate => $shippingdate,
307 employee => SL::DB::Manager::Employee->current,
312 push @transfers, SL::DB::Inventory->new(
313 trans_id => $trans_id,
314 trans_type => $trans_type_in,
318 warehouse => $bin->warehouse_id,
319 chargenumber => $chargenumber,
320 bestbefore => $bestbefore,
321 reserve_for_id => $reserve_for_id,
322 reserve_for_table => $reserve_for_table,
323 shippingdate => $shippingdate,
327 prod => $production_order_item,
328 employee => SL::DB::Manager::Employee->current,
332 SL::DB->client->with_transaction(sub {
333 $_->save for @transfers;
336 die SL::DB->client->error;
342 package SL::Helper::Inventory::Allocation {
343 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment reserve_for_id reserve_for_table);
344 my %attributes = map { $_ => 1 } @attributes;
346 for my $name (@attributes) {
348 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
352 my ($class, %params) = @_;
354 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
355 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
356 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
357 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
359 bless { %params }, $class;
369 SL::WH - Warehouse and Inventory API
373 # See description for an intro to the concepts used here.
375 use SL::Helper::Inventory;
377 # stock, get "what's there" for a part with various conditions:
378 my $qty = SL::Helper::Inventory->get_stock(part => $part); # how much is on stock?
379 my $qty = SL::Helper::Inventory->get_stock(part => $part, date => $date); # how much was on stock at a specific time?
380 my $qty = SL::Helper::Inventory->get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
381 my $qty = SL::Helper::Inventory->get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
382 my $qty = SL::Helper::Inventory->get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
384 # onhand, get "what's available" for a part with various conditions:
385 my $qty = SL::Helper::Inventory->get_onhand(part => $part); # how much is available?
386 my $qty = SL::Helper::Inventory->get_onhand(part => $part, date => $date); # how much was available at a specific time?
387 my $qty = SL::Helper::Inventory->get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
388 my $qty = SL::Helper::Inventory->get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
389 my $qty = SL::Helper::Inventory->get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
390 my $qty = SL::Helper::Inventory->get_onhand(part => $part, reserve_for => $order); # how much is available if you include this reservation?
393 my $data = SL::Helper::Inventory->get_onhand(
394 warehouse => $warehouse,
395 by => [ qw(bin part chargenumber reserve_for) ],
396 with_objects => [ qw(bin part) ],
400 my @allocations, SL::Helper::Inventory->allocate(
401 part => $part, # part_id works too
402 qty => $qty, # must be positive
403 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
404 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
405 reserve_for => $object, # optional, may be arrayref. if provided the qtys reserved for these objects will be used first
406 bin => $bin, # optional, may be arrayref. if provided
409 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
410 my @allocations, SL::Helper::Inventory->allocate_for_assembly(
411 part => $assembly, # part_id works too
412 qty => $qty, # must be positive
415 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
416 my $allocation = SL::Helper::Inventory::Allocation->new(
417 part_id => $part->id,
419 bin_id => $bin_obj->id,
420 warehouse_id => $bin_obj->warehouse_id,
421 chargenumber => '1823772365',
423 reserve_for_id => undef,
424 reserve_for_table => undef,
428 SL::Helper::Inventory->produce_assembly(
429 part => $part, # target assembly
431 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
434 bin => $bin, # needed unless a global standard target is configured
435 chargenumber => $chargenumber, # optional
436 bestbefore => $datetime, # optional
437 comment => $comment, # optional
439 # links, all optional
440 production_order_item => $item,
441 reserve_for => $object,
446 New functions for the warehouse and inventory api.
448 The WH api currently has three large shortcomings. It is very hard to just get
449 the current stock for an item, it's extremely complicated to use it to produce
450 assemblies while ensuring that no stock ends up negative, and it's very hard to
451 use it to get an overview over the actual contents of the inventory.
453 The first problem has spawned several dozen small functions in the program that
454 try to implement that, and those usually miss some details. They may ignore
455 reservations, or reserve warehouses, or bestbefore times.
457 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
459 Stock is defined as the actual contents of the inventory, everything that is
460 there. Onhand is what is available, which means things that are stocked and not
461 reserved and not expired.
463 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
464 allow simple access with some optional filters for chargenumbers or warehouses.
465 Both of them have a batch mode that can be used to get these information to
466 supllement smiple reports.
468 To address the safe assembly creation a new function has been added.
469 C<allocate> will try to find the requested quantity of a part in the inventory
470 and will return allocations of it which can then be used to create the
471 assembly. Allocation will happen with the C<onhand> semantics defined above,
472 meaning that by default no reservations or expired goods will be used. The
473 caller can supply hints of what shold be used and in those cases chargenumber
474 and reservations will be used up as much as possible first. C<allocate> will
475 always try to fulfil the request even beyond those. Should the required amount
476 not be stocked, allocate will throw an exception.
478 C<produce_assembly> has been rewritten to only accept parameters about the
479 target of the production, and requires allocations to complete the request. The
480 allocations can be supplied manually, or can be generated automatically.
481 C<produce_assembly> will check whether enough allocations are given to create
482 the recipe, but will not check whether the allocations are backed. If the
483 allocations are not sufficient or if the auto-allocation fails an exception
484 is returned. If you need to produce something that is not in the inventory, you
485 can bypass those checks by creating the allocations yourself (see
486 L</"ALLOCATION DATA STRUCTURE">).
488 Note: this is only intended to cover the scenarios described above. For other cases:
494 If you need the reserved amount for an order use C<SL::DB::Helper::Reservation>
499 If you need actual inventory objects because of record links, prod_id links or
500 something like that load them directly. And strongly consider redesigning that,
501 because it's really fragile.
505 You need weight or accounting information you're on your own. The inventory api
506 only concerns itself with the raw quantities.
510 If you need the first stock date of parts, or anything related to a specific
511 transfer type or direction, this is not covered yet.
519 =item * get_stock PARAMS
521 Returns for single parts how much actually exists in the inventory.
529 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
533 If given, will only return stock on these bins. Optional. May be array, May be object or id.
537 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
541 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
545 If given, will only show stock with this chargenumber. Optional. May be array.
549 See L</"STOCK/ONHAND REPORT MODE">
553 See L</"STOCK/ONHAND REPORT MODE">
557 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
558 mode when C<by> is given.
560 =item * get_onhand PARAMS
562 Returns for single parts how much is available in the inventory. That excludes:
563 reserved quantities, reserved warehouses and stock with expired bestbefore.
565 It takes all options of L</get_stock> but treats some of the differently and has some additional ones:
571 Usually C<onhand> will not include results from warehouses with the C<reserve>
572 flag. However giving an explicit list of warehouses will include there in the
573 search, as well as all others.
577 =item * reserve_warehouse
583 =item * allocate PARAMS
595 Bin object. Optional.
599 Warehouse object. Optional.
611 Needs to be a rose object, where id and table can be extracted. Optional.
615 Tries to allocate the required quantity using what is currently onhand. If
616 given any of C<bin>, C<warehouse>, C<chargenumber>, C<reserve_for>
619 =item * allocate_for_assembly PARAMS
621 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
622 compute the required amount for each assembly part and allocate all of them.
624 =item * produce_assembly
629 =head1 STOCK/ONHAND REPORT MODE
631 If the special option C<by> is given with an arrayref, the result will instead
632 be an arrayref of partitioned stocks by those fields. Valid partitions are:
638 If this is given, part is optional in the parameters
652 Note: If you want to use the returned data to create allocations you I<need> to
653 enable all of these. To make this easier a special shortcut exists
655 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
656 C<parts>, and the C<reserve_for> objects in one go, just like with Rose. They
657 need to be present in C<by> before that though.
659 =head1 ALLOCATION ALGORITHM
661 When calling allocate, the current onhand (== available stock) of the item will
662 be used to decide which bins/chargenumbers/bestbefore can be used.
664 In general allocate will try to make the request happen, and will use the
665 provided charges up first, and then tap everything else. If you need to only
666 I<exactly> use the provided charges, you'll need to craft the allocations
667 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
669 If C<reserve_for> is given, those will be used up first too.
671 If C<reserved_warehouse> is given, those will be used up second.
673 If C<chargenumber> is given, those will be used up next.
675 After that normal quantities will be used.
677 These are tiebreakers and expected to rarely matter in reality. If you need
678 finegrained control over which allocation is used, you may want to get the
679 onhands yourself and select the appropriate ones.
681 Only quantities with C<bestbefore> unset or after the given date will be
682 considered. If more than one charge is eligible, the earlier C<bestbefore>
685 Allocations do NOT have an internal memory and can't react to other allocations
686 of the same part earlier. Never double allocate the same part within a
689 =head1 ALLOCATION DATA STRUCTURE
691 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
692 each of the following attributes to be set at creation time:
708 =item * reserve_for_id
710 =item * reserve_for_table
714 C<chargenumber>, C<bestbefore>, C<reserve_for_id> and C<reserve_for_table> may
715 be C<undef> (but must still be present at creation time). Instances are
716 considered immutable.
718 =head1 ERROR HANDLING
720 C<allocate> and C<produce_assembly> will throw exceptions if the request can
721 not be completed. The usual reason will be insufficient onhand to allocate, or
722 insufficient allocations to process the request.
726 * define and describe error classes
727 * define wrapper classes for stock/onhand batch mode return values
728 * handle extra arguments in produce: shippingdate, project, oe
729 * clean up allocation helper class
730 * with objects for reservations
740 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>