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 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;
132 return $results->[0]{qty};
137 _get_stock_onhand(@_, onhand => 0);
141 _get_stock_onhand(@_, onhand => 1);
147 die SL::X::Inventory::Allocation->new(
148 error => 'allocate needs a part',
149 msg => t8("Method allocate needs the parameter 'part'"),
150 ) unless $params{part};
151 die SL::X::Inventory::Allocation->new(
152 error => 'allocate needs a qty',
153 msg => t8("Method allocate needs the parameter 'qty'"),
154 ) unless $params{qty};
156 my $part = $params{part};
157 my $qty = $params{qty};
159 return () if $qty <= 0;
161 my $results = get_stock(part => $part, by => 'for_allocate');
162 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
163 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
164 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
166 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
167 my @sorted_results = sort {
168 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
169 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
170 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
171 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
176 for my $chunk (@sorted_results) {
177 my $qty = min($chunk->{qty}, $rest_qty);
179 push @allocations, SL::Helper::Inventory::Allocation->new(
180 parts_id => $chunk->{parts_id},
182 comment => $params{comment},
183 bin_id => $chunk->{bin_id},
184 warehouse_id => $chunk->{warehouse_id},
185 chargenumber => $chunk->{chargenumber},
186 bestbefore => $chunk->{bestbefore},
187 for_object_id => undef,
189 $rest_qty -= _round_number($qty, 5);
191 $rest_qty = _round_number($rest_qty, 5);
192 last if $rest_qty == 0;
195 die SL::X::Inventory::Allocation->new(
196 error => 'not enough to allocate',
197 msg => t8("can not allocate #1 units of #2, missing #3 units", _number(\%::myconfig, $qty), $part->displayable_name, _number(\%::myconfig, $rest_qty)),
200 if ($params{constraints}) {
201 check_constraints($params{constraints},\@allocations);
207 sub allocate_for_assembly {
210 my $part = $params{part} or Carp::croak('allocate needs a part');
211 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
213 Carp::croak('not an assembly') unless $part->is_assembly;
215 my %parts_to_allocate;
217 for my $assembly ($part->assemblies) {
218 next if $assembly->part->dispotype eq 'no_stock';
220 my $tmpqty = $assembly->assembly_part->is_recipe ? $assembly->qty * $qty / $assembly->assembly_part->scalebasis
221 : $assembly->part->unit eq 'Stck' ? ceil($assembly->qty * $qty)
222 : $assembly->qty * $qty;
223 $parts_to_allocate{ $assembly->part->id } //= 0;
224 $parts_to_allocate{ $assembly->part->id } += $tmpqty;
229 for my $part_id (keys %parts_to_allocate) {
230 my $part = SL::DB::Part->load_cached($part_id);
231 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
237 sub check_constraints {
238 my ($constraints, $allocations) = @_;
239 if ('CODE' eq ref $constraints) {
240 if (!$constraints->(@$allocations)) {
241 die SL::X::Inventory::Allocation->new(
242 error => 'allocation constraints failure',
243 msg => t8("Allocations didn't pass constraints"),
247 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
249 my %supported_constraints = (
251 warehouse_id => 'warehouse_id',
252 chargenumber => 'chargenumber',
255 for (keys %$constraints ) {
256 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
257 next unless defined $constraints->{$_};
259 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
260 my $accessor = $supported_constraints{$_};
262 if (any { !$whitelist{$_->$accessor} } @$allocations) {
263 my %error_constraints = (
264 bin_id => t8('Bins'),
265 warehouse_id => t8('Warehouses'),
266 chargenumber => t8('Chargenumbers'),
268 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
269 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
270 my $err = t8("Cannot allocate parts.");
271 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
272 SL::DB::Part->load_cached($_->parts_id)->description,
273 SL::DB::Bin->load_cached($_->bin_id)->full_description,
274 _number($_->qty), _number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
275 die SL::X::Inventory::Allocation->new(
276 error => 'allocation constraints failure',
284 sub produce_assembly {
287 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
288 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
290 my $allocations = $params{allocations};
291 if ($params{auto_allocate}) {
292 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
293 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
295 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
296 $allocations = $params{allocations};
299 my $bin = $params{bin} or Carp::croak("need target bin");
300 my $chargenumber = $params{chargenumber};
301 my $bestbefore = $params{bestbefore};
302 my $for_object_id = $params{for_object_id};
303 my $comment = $params{comment} // '';
305 my $production_order_item = $params{production_order_item};
306 my $invoice = $params{invoice};
307 my $project = $params{project};
309 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
311 my $trans_id = $params{trans_id};
312 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
314 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
315 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
317 # check whether allocations are sane
318 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
319 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
320 for my $assembly ($part->assemblies) {
321 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty; # TODO recipe factor
324 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
328 for my $allocation (@$allocations) {
329 my $oe_id = delete $allocation->{for_object_id};
330 push @transfers, SL::DB::Inventory->new(
331 trans_id => $trans_id,
333 qty => -$allocation->qty,
334 trans_type => $trans_type_out,
335 shippingdate => $shippingdate,
336 employee => SL::DB::Manager::Employee->current,
337 oe_id => $allocation->for_object_id,
341 push @transfers, SL::DB::Inventory->new(
342 trans_id => $trans_id,
343 trans_type => $trans_type_in,
347 warehouse => $bin->warehouse_id,
348 chargenumber => $chargenumber,
349 bestbefore => $bestbefore,
350 shippingdate => $shippingdate,
354 prod => $production_order_item,
355 employee => SL::DB::Manager::Employee->current,
356 oe_id => $for_object_id,
359 SL::DB->client->with_transaction(sub {
360 $_->save for @transfers;
363 die SL::DB->client->error;
369 package SL::Helper::Inventory::Allocation {
370 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
371 my %attributes = map { $_ => 1 } @attributes;
373 for my $name (@attributes) {
375 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
379 my ($class, %params) = @_;
381 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
382 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
383 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
384 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
386 bless { %params }, $class;
396 SL::WH - Warehouse and Inventory API
400 # See description for an intro to the concepts used here.
402 use SL::Helper::Inventory qw(:ALL);
404 # stock, get "what's there" for a part with various conditions:
405 my $qty = get_stock(part => $part); # how much is on stock?
406 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
407 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
408 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
409 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
411 # onhand, get "what's available" for a part with various conditions:
412 my $qty = get_onhand(part => $part); # how much is available?
413 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
414 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
415 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
416 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
419 my $data = get_onhand(
420 warehouse => $warehouse,
421 by => [ qw(bin part chargenumber) ],
422 with_objects => [ qw(bin part) ],
426 my @allocations, allocate(
427 part => $part, # part_id works too
428 qty => $qty, # must be positive
429 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
430 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
431 bin => $bin, # optional, may be arrayref. if provided
434 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
435 my @allocations, allocate_for_assembly(
436 part => $assembly, # part_id works too
437 qty => $qty, # must be positive
440 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
441 my $allocation = SL::Helper::Inventory::Allocation->new(
442 part_id => $part->id,
444 bin_id => $bin_obj->id,
445 warehouse_id => $bin_obj->warehouse_id,
446 chargenumber => '1823772365',
448 for_object_id => $order->id,
453 part => $part, # target assembly
455 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
458 bin => $bin, # needed unless a global standard target is configured
459 chargenumber => $chargenumber, # optional
460 bestbefore => $datetime, # optional
461 comment => $comment, # optional
463 # links, all optional
464 production_order_item => $item,
469 New functions for the warehouse and inventory api.
471 The WH api currently has three large shortcomings. It is very hard to just get
472 the current stock for an item, it's extremely complicated to use it to produce
473 assemblies while ensuring that no stock ends up negative, and it's very hard to
474 use it to get an overview over the actual contents of the inventory.
476 The first problem has spawned several dozen small functions in the program that
477 try to implement that, and those usually miss some details. They may ignore
478 reservations, or bestbefore times.
480 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
482 Stock is defined as the actual contents of the inventory, everything that is
483 there. Onhand is what is available, which means things that are stocked
486 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
487 allow simple access with some optional filters for chargenumbers or warehouses.
488 Both of them have a batch mode that can be used to get these information to
489 supllement smiple reports.
491 To address the safe assembly creation a new function has been added.
492 C<allocate> will try to find the requested quantity of a part in the inventory
493 and will return allocations of it which can then be used to create the
494 assembly. Allocation will happen with the C<onhand> semantics defined above,
495 meaning that by default no reservations or expired goods will be used. The
496 caller can supply hints of what shold be used and in those cases chargenumber
497 and reservations will be used up as much as possible first. C<allocate> will
498 always try to fulfil the request even beyond those. Should the required amount
499 not be stocked, allocate will throw an exception.
501 C<produce_assembly> has been rewritten to only accept parameters about the
502 target of the production, and requires allocations to complete the request. The
503 allocations can be supplied manually, or can be generated automatically.
504 C<produce_assembly> will check whether enough allocations are given to create
505 the recipe, but will not check whether the allocations are backed. If the
506 allocations are not sufficient or if the auto-allocation fails an exception
507 is returned. If you need to produce something that is not in the inventory, you
508 can bypass those checks by creating the allocations yourself (see
509 L</"ALLOCATION DATA STRUCTURE">).
511 Note: this is only intended to cover the scenarios described above. For other cases:
517 If you need actual inventory objects because of record links, prod_id links or
518 something like that load them directly. And strongly consider redesigning that,
519 because it's really fragile.
523 You need weight or accounting information you're on your own. The inventory api
524 only concerns itself with the raw quantities.
528 If you need the first stock date of parts, or anything related to a specific
529 transfer type or direction, this is not covered yet.
537 =item * get_stock PARAMS
539 Returns for single parts how much actually exists in the inventory.
547 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
551 If given, will only return stock on these bins. Optional. May be array, May be object or id.
555 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
559 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
563 If given, will only show stock with this chargenumber. Optional. May be array.
567 See L</"STOCK/ONHAND REPORT MODE">
571 See L</"STOCK/ONHAND REPORT MODE">
575 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
576 mode when C<by> is given.
578 =item * get_onhand PARAMS
580 Returns for single parts how much is available in the inventory. That excludes
581 stock with expired bestbefore.
583 It takes all options of L</get_stock> and has some additional ones:
591 =item * allocate PARAMS
603 Bin object. Optional.
607 Warehouse object. Optional.
619 Tries to allocate the required quantity using what is currently onhand. If
620 given any of C<bin>, C<warehouse>, C<chargenumber>
622 =item * allocate_for_assembly PARAMS
624 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
625 compute the required amount for each assembly part and allocate all of them.
627 =item * produce_assembly
632 =head1 STOCK/ONHAND REPORT MODE
634 If the special option C<by> is given with an arrayref, the result will instead
635 be an arrayref of partitioned stocks by those fields. Valid partitions are:
641 If this is given, part is optional in the parameters
653 Note: If you want to use the returned data to create allocations you I<need> to
654 enable all of these. To make this easier a special shortcut exists
656 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
657 C<parts> objects in one go, just like with Rose. They
658 need to be present in C<by> before that though.
660 =head1 ALLOCATION ALGORITHM
662 When calling allocate, the current onhand (== available stock) of the item will
663 be used to decide which bins/chargenumbers/bestbefore can be used.
665 In general allocate will try to make the request happen, and will use the
666 provided charges up first, and then tap everything else. If you need to only
667 I<exactly> use the provided charges, you'll need to craft the allocations
668 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
670 If C<chargenumber> is given, those will be used up next.
672 After that normal quantities will be used.
674 These are tiebreakers and expected to rarely matter in reality. If you need
675 finegrained control over which allocation is used, you may want to get the
676 onhands yourself and select the appropriate ones.
678 Only quantities with C<bestbefore> unset or after the given date will be
679 considered. If more than one charge is eligible, the earlier C<bestbefore>
682 Allocations do NOT have an internal memory and can't react to other allocations
683 of the same part earlier. Never double allocate the same part within a
686 =head1 ALLOCATION DATA STRUCTURE
688 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
689 each of the following attributes to be set at creation time:
705 =item * for_object_id
707 If set the allocations will be marked as allocated for the given object.
708 If these allocations are later used to produce an assembly, the resulting
709 consuming transactions will be marked as belonging to the given object.
710 The object may be an order, productionorder or other objects
714 C<chargenumber>, C<bestbefore> and C<for_object_id> may be C<undef> (but must
715 still be present at creation time). Instances are considered immutable.
720 # whitelist constraints
724 bin_id => \@allowed_bins,
725 chargenumber => \@allowed_chargenumbers,
732 # only allow chargenumbers with specific format
733 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
736 # and must all have a bestbefore date
737 all { $_->bestbefore } @_;
741 C<allocation> is "best effort" in nature. It will take the C<bin>,
742 C<chargenumber> etc hints from the parameters, but will try it's bvest to
743 fulfil the request anyway and only bail out if it is absolutely not possible.
745 Sometimes you need to restrict allocations though. For this you can pass
746 additional constraints to C<allocate>. A constraint serves as a whitelist.
747 Every allocation must fulfil every constraint by having that attribute be one
750 In case even that is not enough, you may supply a custom check by passing a
751 function that will be given the allocation objects.
753 Note that both whitelists and constraints do not influence the order of
754 allocations, which is done purely from the initial parameters. They only serve
755 to reject allocations made in good faith which do fulfil required assertions.
757 =head1 ERROR HANDLING
759 C<allocate> and C<produce_assembly> will throw exceptions if the request can
760 not be completed. The usual reason will be insufficient onhand to allocate, or
761 insufficient allocations to process the request.
765 * define and describe error classes
766 * define wrapper classes for stock/onhand batch mode return values
767 * handle extra arguments in produce: shippingdate, project, oe
768 * clean up allocation helper class
769 * with objects for reservations
779 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>