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}) {
66 Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
67 push @where, sprintf "bestbefore >= ?";
68 push @values, $params{bestbefore};
73 part => [ qw(parts_id) ],
74 bin => [ qw(bin_id inventory.warehouse_id)],
75 warehouse => [ qw(inventory.warehouse_id) ],
76 chargenumber => [ qw(chargenumber) ],
77 bestbefore => [ qw(bestbefore) ],
78 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
82 for (listify($params{by})) {
83 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
84 push @selects, @$selects;
85 push @groups, @$selects;
89 my $select = join ',', @selects;
90 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
91 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
94 SELECT $select FROM inventory
95 LEFT JOIN bin ON bin_id = bin.id
96 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
101 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
104 part => 'SL::DB::Manager::Part',
105 bin => 'SL::DB::Manager::Bin',
106 warehouse => 'SL::DB::Manager::Warehouse',
112 warehouse => 'warehouse_id',
115 if ($params{by} && $params{with_objects}) {
116 for my $with_object (listify($params{with_objects})) {
117 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
119 if (my $manager = $with_objects{$with_object}) {
120 my $slot = $slots{$with_object};
121 next if !(my @ids = map { $_->{$slot} } @$results);
122 my $objects = $manager->get_all(query => [ id => \@ids ]);
123 my %objects_by_id = map { $_->id => $_ } @$objects;
125 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
127 # need to fetch all partitions
135 return $results->[0]{qty};
140 _get_stock_onhand(@_, onhand => 0);
144 _get_stock_onhand(@_, onhand => 1);
150 die SL::X::Inventory::Allocation->new(
151 error => 'allocate needs a part',
152 msg => t8("Method allocate needs the parameter 'part'"),
153 ) unless $params{part};
154 die SL::X::Inventory::Allocation->new(
155 error => 'allocate needs a qty',
156 msg => t8("Method allocate needs the parameter 'qty'"),
157 ) unless $params{qty};
159 my $part = $params{part};
160 my $qty = $params{qty};
162 return () if $qty <= 0;
164 my $results = get_stock(part => $part, by => 'for_allocate');
165 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
166 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
167 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
169 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
170 my @sorted_results = sort {
171 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
172 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
173 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
174 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
179 for my $chunk (@sorted_results) {
180 my $qty = min($chunk->{qty}, $rest_qty);
182 push @allocations, SL::Helper::Inventory::Allocation->new(
183 parts_id => $chunk->{parts_id},
185 comment => $params{comment},
186 bin_id => $chunk->{bin_id},
187 warehouse_id => $chunk->{warehouse_id},
188 chargenumber => $chunk->{chargenumber},
189 bestbefore => $chunk->{bestbefore},
190 for_object_id => undef,
192 $rest_qty -= _round_number($qty, 5);
194 $rest_qty = _round_number($rest_qty, 5);
195 last if $rest_qty == 0;
198 die SL::X::Inventory::Allocation->new(
199 error => 'not enough to allocate',
200 msg => t8("can not allocate #1 units of #2, missing #3 units", _number(\%::myconfig, $qty), $part->displayable_name, _number(\%::myconfig, $rest_qty)),
203 if ($params{constraints}) {
204 check_constraints($params{constraints},\@allocations);
210 sub allocate_for_assembly {
213 my $part = $params{part} or Carp::croak('allocate needs a part');
214 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
216 Carp::croak('not an assembly') unless $part->is_assembly;
218 my %parts_to_allocate;
220 for my $assembly ($part->assemblies) {
221 next if $assembly->part->dispotype eq 'no_stock';
223 my $tmpqty = $assembly->assembly_part->is_recipe ? $assembly->qty * $qty / $assembly->assembly_part->scalebasis
224 : $assembly->part->unit eq 'Stck' ? ceil($assembly->qty * $qty)
225 : $assembly->qty * $qty;
226 $parts_to_allocate{ $assembly->part->id } //= 0;
227 $parts_to_allocate{ $assembly->part->id } += $tmpqty;
232 for my $part_id (keys %parts_to_allocate) {
233 my $part = SL::DB::Part->load_cached($part_id);
234 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
240 sub check_constraints {
241 my ($constraints, $allocations) = @_;
242 if ('CODE' eq ref $constraints) {
243 if (!$constraints->(@$allocations)) {
244 die SL::X::Inventory::Allocation->new(
245 error => 'allocation constraints failure',
246 msg => t8("Allocations didn't pass constraints"),
250 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
252 my %supported_constraints = (
254 warehouse_id => 'warehouse_id',
255 chargenumber => 'chargenumber',
258 for (keys %$constraints ) {
259 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
260 next unless defined $constraints->{$_};
262 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
263 my $accessor = $supported_constraints{$_};
265 if (any { !$whitelist{$_->$accessor} } @$allocations) {
266 my %error_constraints = (
267 bin_id => t8('Bins'),
268 warehouse_id => t8('Warehouses'),
269 chargenumber => t8('Chargenumbers'),
271 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
272 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
273 my $err = t8("Cannot allocate parts.");
274 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
275 SL::DB::Part->load_cached($_->parts_id)->description,
276 SL::DB::Bin->load_cached($_->bin_id)->full_description,
277 _number($_->qty), _number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
278 die SL::X::Inventory::Allocation->new(
279 error => 'allocation constraints failure',
287 sub produce_assembly {
290 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
291 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
293 my $allocations = $params{allocations};
294 if ($params{auto_allocate}) {
295 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
296 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
298 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
299 $allocations = $params{allocations};
302 my $bin = $params{bin} or Carp::croak("need target bin");
303 my $chargenumber = $params{chargenumber};
304 my $bestbefore = $params{bestbefore};
305 my $for_object_id = $params{for_object_id};
306 my $comment = $params{comment} // '';
308 my $production_order_item = $params{production_order_item};
309 my $invoice = $params{invoice};
310 my $project = $params{project};
312 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
314 my $trans_id = $params{trans_id};
315 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
317 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
318 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
320 # check whether allocations are sane
321 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
322 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
323 for my $assembly ($part->assemblies) {
324 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
327 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
331 for my $allocation (@$allocations) {
332 my $oe_id = delete $allocation->{for_object_id};
333 push @transfers, SL::DB::Inventory->new(
334 trans_id => $trans_id,
336 qty => -$allocation->qty,
337 trans_type => $trans_type_out,
338 shippingdate => $shippingdate,
339 employee => SL::DB::Manager::Employee->current,
340 oe_id => $allocation->for_object_id,
344 push @transfers, SL::DB::Inventory->new(
345 trans_id => $trans_id,
346 trans_type => $trans_type_in,
350 warehouse => $bin->warehouse_id,
351 chargenumber => $chargenumber,
352 bestbefore => $bestbefore,
353 shippingdate => $shippingdate,
357 prod => $production_order_item,
358 employee => SL::DB::Manager::Employee->current,
359 oe_id => $for_object_id,
362 SL::DB->client->with_transaction(sub {
363 $_->save for @transfers;
366 die SL::DB->client->error;
372 package SL::Helper::Inventory::Allocation {
373 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
374 my %attributes = map { $_ => 1 } @attributes;
376 for my $name (@attributes) {
378 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
382 my ($class, %params) = @_;
384 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
385 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
386 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
387 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
389 bless { %params }, $class;
399 SL::WH - Warehouse and Inventory API
403 # See description for an intro to the concepts used here.
405 use SL::Helper::Inventory qw(:ALL);
407 # stock, get "what's there" for a part with various conditions:
408 my $qty = get_stock(part => $part); # how much is on stock?
409 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
410 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
411 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
412 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
414 # onhand, get "what's available" for a part with various conditions:
415 my $qty = get_onhand(part => $part); # how much is available?
416 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
417 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
418 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
419 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
422 my $data = get_onhand(
423 warehouse => $warehouse,
424 by => [ qw(bin part chargenumber) ],
425 with_objects => [ qw(bin part) ],
429 my @allocations, allocate(
430 part => $part, # part_id works too
431 qty => $qty, # must be positive
432 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
433 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
434 bin => $bin, # optional, may be arrayref. if provided
437 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
438 my @allocations, allocate_for_assembly(
439 part => $assembly, # part_id works too
440 qty => $qty, # must be positive
443 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
444 my $allocation = SL::Helper::Inventory::Allocation->new(
445 part_id => $part->id,
447 bin_id => $bin_obj->id,
448 warehouse_id => $bin_obj->warehouse_id,
449 chargenumber => '1823772365',
451 for_object_id => $order->id,
456 part => $part, # target assembly
458 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
461 bin => $bin, # needed unless a global standard target is configured
462 chargenumber => $chargenumber, # optional
463 bestbefore => $datetime, # optional
464 comment => $comment, # optional
466 # links, all optional
467 production_order_item => $item,
472 New functions for the warehouse and inventory api.
474 The WH api currently has three large shortcomings. It is very hard to just get
475 the current stock for an item, it's extremely complicated to use it to produce
476 assemblies while ensuring that no stock ends up negative, and it's very hard to
477 use it to get an overview over the actual contents of the inventory.
479 The first problem has spawned several dozen small functions in the program that
480 try to implement that, and those usually miss some details. They may ignore
481 reservations, or bestbefore times.
483 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
485 Stock is defined as the actual contents of the inventory, everything that is
486 there. Onhand is what is available, which means things that are stocked
489 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
490 allow simple access with some optional filters for chargenumbers or warehouses.
491 Both of them have a batch mode that can be used to get these information to
492 supllement smiple reports.
494 To address the safe assembly creation a new function has been added.
495 C<allocate> will try to find the requested quantity of a part in the inventory
496 and will return allocations of it which can then be used to create the
497 assembly. Allocation will happen with the C<onhand> semantics defined above,
498 meaning that by default no reservations or expired goods will be used. The
499 caller can supply hints of what shold be used and in those cases chargenumber
500 and reservations will be used up as much as possible first. C<allocate> will
501 always try to fulfil the request even beyond those. Should the required amount
502 not be stocked, allocate will throw an exception.
504 C<produce_assembly> has been rewritten to only accept parameters about the
505 target of the production, and requires allocations to complete the request. The
506 allocations can be supplied manually, or can be generated automatically.
507 C<produce_assembly> will check whether enough allocations are given to create
508 the recipe, but will not check whether the allocations are backed. If the
509 allocations are not sufficient or if the auto-allocation fails an exception
510 is returned. If you need to produce something that is not in the inventory, you
511 can bypass those checks by creating the allocations yourself (see
512 L</"ALLOCATION DATA STRUCTURE">).
514 Note: this is only intended to cover the scenarios described above. For other cases:
520 If you need actual inventory objects because of record links, prod_id links or
521 something like that load them directly. And strongly consider redesigning that,
522 because it's really fragile.
526 You need weight or accounting information you're on your own. The inventory api
527 only concerns itself with the raw quantities.
531 If you need the first stock date of parts, or anything related to a specific
532 transfer type or direction, this is not covered yet.
540 =item * get_stock PARAMS
542 Returns for single parts how much actually exists in the inventory.
550 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
554 If given, will only return stock on these bins. Optional. May be array, May be object or id.
558 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
562 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
566 If given, will only show stock with this chargenumber. Optional. May be array.
570 See L</"STOCK/ONHAND REPORT MODE">
574 See L</"STOCK/ONHAND REPORT MODE">
578 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
579 mode when C<by> is given.
581 =item * get_onhand PARAMS
583 Returns for single parts how much is available in the inventory. That excludes
584 stock with expired bestbefore.
586 It takes all options of L</get_stock> and has some additional ones:
594 =item * allocate PARAMS
606 Bin object. Optional.
610 Warehouse object. Optional.
622 Tries to allocate the required quantity using what is currently onhand. If
623 given any of C<bin>, C<warehouse>, C<chargenumber>
625 =item * allocate_for_assembly PARAMS
627 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
628 compute the required amount for each assembly part and allocate all of them.
630 =item * produce_assembly
635 =head1 STOCK/ONHAND REPORT MODE
637 If the special option C<by> is given with an arrayref, the result will instead
638 be an arrayref of partitioned stocks by those fields. Valid partitions are:
644 If this is given, part is optional in the parameters
656 Note: If you want to use the returned data to create allocations you I<need> to
657 enable all of these. To make this easier a special shortcut exists
659 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
660 C<parts> objects in one go, just like with Rose. They
661 need to be present in C<by> before that though.
663 =head1 ALLOCATION ALGORITHM
665 When calling allocate, the current onhand (== available stock) of the item will
666 be used to decide which bins/chargenumbers/bestbefore can be used.
668 In general allocate will try to make the request happen, and will use the
669 provided charges up first, and then tap everything else. If you need to only
670 I<exactly> use the provided charges, you'll need to craft the allocations
671 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
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 * for_object_id
710 If set the allocations will be marked as allocated for the given object.
711 If these allocations are later used to produce an assembly, the resulting
712 consuming transactions will be marked as belonging to the given object.
713 The object may be an order, productionorder or other objects
717 C<chargenumber>, C<bestbefore> and C<for_object_id> may be C<undef> (but must
718 still be present at creation time). Instances are considered immutable.
723 # whitelist constraints
727 bin_id => \@allowed_bins,
728 chargenumber => \@allowed_chargenumbers,
735 # only allow chargenumbers with specific format
736 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
739 # and must all have a bestbefore date
740 all { $_->bestbefore } @_;
744 C<allocation> is "best effort" in nature. It will take the C<bin>,
745 C<chargenumber> etc hints from the parameters, but will try it's bvest to
746 fulfil the request anyway and only bail out if it is absolutely not possible.
748 Sometimes you need to restrict allocations though. For this you can pass
749 additional constraints to C<allocate>. A constraint serves as a whitelist.
750 Every allocation must fulfil every constraint by having that attribute be one
753 In case even that is not enough, you may supply a custom check by passing a
754 function that will be given the allocation objects.
756 Note that both whitelists and constraints do not influence the order of
757 allocations, which is done purely from the initial parameters. They only serve
758 to reject allocations made in good faith which do fulfil required assertions.
760 =head1 ERROR HANDLING
762 C<allocate> and C<produce_assembly> will throw exceptions if the request can
763 not be completed. The usual reason will be insufficient onhand to allocate, or
764 insufficient allocations to process the request.
768 * define and describe error classes
769 * define wrapper classes for stock/onhand batch mode return values
770 * handle extra arguments in produce: shippingdate, project, oe
771 * clean up allocation helper class
772 * with objects for reservations
782 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>