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->part->unit eq 'Stck' ? ceil($assembly->qty * $qty)
221 : $assembly->qty * $qty;
222 $parts_to_allocate{ $assembly->part->id } //= 0;
223 $parts_to_allocate{ $assembly->part->id } += $tmpqty;
228 for my $part_id (keys %parts_to_allocate) {
229 my $part = SL::DB::Part->load_cached($part_id);
230 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
236 sub check_constraints {
237 my ($constraints, $allocations) = @_;
238 if ('CODE' eq ref $constraints) {
239 if (!$constraints->(@$allocations)) {
240 die SL::X::Inventory::Allocation->new(
241 error => 'allocation constraints failure',
242 msg => t8("Allocations didn't pass constraints"),
246 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
248 my %supported_constraints = (
250 warehouse_id => 'warehouse_id',
251 chargenumber => 'chargenumber',
254 for (keys %$constraints ) {
255 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
256 next unless defined $constraints->{$_};
258 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
259 my $accessor = $supported_constraints{$_};
261 if (any { !$whitelist{$_->$accessor} } @$allocations) {
262 my %error_constraints = (
263 bin_id => t8('Bins'),
264 warehouse_id => t8('Warehouses'),
265 chargenumber => t8('Chargenumbers'),
267 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
268 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
269 my $err = t8("Cannot allocate parts.");
270 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
271 SL::DB::Part->load_cached($_->parts_id)->description,
272 SL::DB::Bin->load_cached($_->bin_id)->full_description,
273 _number($_->qty), _number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
274 die SL::X::Inventory::Allocation->new(
275 error => 'allocation constraints failure',
283 sub produce_assembly {
286 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
287 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
289 my $allocations = $params{allocations};
290 if ($params{auto_allocate}) {
291 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
292 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
294 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
295 $allocations = $params{allocations};
298 my $bin = $params{bin} or Carp::croak("need target bin");
299 my $chargenumber = $params{chargenumber};
300 my $bestbefore = $params{bestbefore};
301 my $for_object_id = $params{for_object_id};
302 my $comment = $params{comment} // '';
304 my $invoice = $params{invoice};
305 my $project = $params{project};
307 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
309 my $trans_id = $params{trans_id};
310 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
312 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
313 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
315 # check whether allocations are sane
316 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
317 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
318 for my $assembly ($part->assemblies) {
319 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty;
322 die "allocations are insufficient for production" if any { $_ < 0 } values %allocations_by_part;
326 for my $allocation (@$allocations) {
327 my $oe_id = delete $allocation->{for_object_id};
328 push @transfers, SL::DB::Inventory->new(
329 trans_id => $trans_id,
331 qty => -$allocation->qty,
332 trans_type => $trans_type_out,
333 shippingdate => $shippingdate,
334 employee => SL::DB::Manager::Employee->current,
335 oe_id => $allocation->for_object_id,
339 push @transfers, SL::DB::Inventory->new(
340 trans_id => $trans_id,
341 trans_type => $trans_type_in,
345 warehouse => $bin->warehouse_id,
346 chargenumber => $chargenumber,
347 bestbefore => $bestbefore,
348 shippingdate => $shippingdate,
352 employee => SL::DB::Manager::Employee->current,
353 oe_id => $for_object_id,
356 SL::DB->client->with_transaction(sub {
357 $_->save for @transfers;
360 die SL::DB->client->error;
366 package SL::Helper::Inventory::Allocation {
367 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
368 my %attributes = map { $_ => 1 } @attributes;
370 for my $name (@attributes) {
372 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
376 my ($class, %params) = @_;
378 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
379 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
380 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
381 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
383 bless { %params }, $class;
393 SL::WH - Warehouse and Inventory API
397 # See description for an intro to the concepts used here.
399 use SL::Helper::Inventory qw(:ALL);
401 # stock, get "what's there" for a part with various conditions:
402 my $qty = get_stock(part => $part); # how much is on stock?
403 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
404 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
405 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
406 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
408 # onhand, get "what's available" for a part with various conditions:
409 my $qty = get_onhand(part => $part); # how much is available?
410 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
411 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
412 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
413 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
416 my $data = get_onhand(
417 warehouse => $warehouse,
418 by => [ qw(bin part chargenumber) ],
419 with_objects => [ qw(bin part) ],
423 my @allocations, allocate(
424 part => $part, # part_id works too
425 qty => $qty, # must be positive
426 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
427 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
428 bin => $bin, # optional, may be arrayref. if provided
431 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
432 my @allocations, allocate_for_assembly(
433 part => $assembly, # part_id works too
434 qty => $qty, # must be positive
437 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
438 my $allocation = SL::Helper::Inventory::Allocation->new(
439 part_id => $part->id,
441 bin_id => $bin_obj->id,
442 warehouse_id => $bin_obj->warehouse_id,
443 chargenumber => '1823772365',
445 for_object_id => $order->id,
450 part => $part, # target assembly
452 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
455 bin => $bin, # needed unless a global standard target is configured
456 chargenumber => $chargenumber, # optional
457 bestbefore => $datetime, # optional
458 comment => $comment, # optional
460 # links, all optional
465 New functions for the warehouse and inventory api.
467 The WH api currently has three large shortcomings. It is very hard to just get
468 the current stock for an item, it's extremely complicated to use it to produce
469 assemblies while ensuring that no stock ends up negative, and it's very hard to
470 use it to get an overview over the actual contents of the inventory.
472 The first problem has spawned several dozen small functions in the program that
473 try to implement that, and those usually miss some details. They may ignore
474 reservations, or bestbefore times.
476 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
478 Stock is defined as the actual contents of the inventory, everything that is
479 there. Onhand is what is available, which means things that are stocked
482 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
483 allow simple access with some optional filters for chargenumbers or warehouses.
484 Both of them have a batch mode that can be used to get these information to
485 supllement smiple reports.
487 To address the safe assembly creation a new function has been added.
488 C<allocate> will try to find the requested quantity of a part in the inventory
489 and will return allocations of it which can then be used to create the
490 assembly. Allocation will happen with the C<onhand> semantics defined above,
491 meaning that by default no reservations or expired goods will be used. The
492 caller can supply hints of what shold be used and in those cases chargenumber
493 and reservations will be used up as much as possible first. C<allocate> will
494 always try to fulfil the request even beyond those. Should the required amount
495 not be stocked, allocate will throw an exception.
497 C<produce_assembly> has been rewritten to only accept parameters about the
498 target of the production, and requires allocations to complete the request. The
499 allocations can be supplied manually, or can be generated automatically.
500 C<produce_assembly> will check whether enough allocations are given to create
501 the recipe, but will not check whether the allocations are backed. If the
502 allocations are not sufficient or if the auto-allocation fails an exception
503 is returned. If you need to produce something that is not in the inventory, you
504 can bypass those checks by creating the allocations yourself (see
505 L</"ALLOCATION DATA STRUCTURE">).
507 Note: this is only intended to cover the scenarios described above. For other cases:
513 If you need actual inventory objects because of record links, prod_id links or
514 something like that load them directly. And strongly consider redesigning that,
515 because it's really fragile.
519 You need weight or accounting information you're on your own. The inventory api
520 only concerns itself with the raw quantities.
524 If you need the first stock date of parts, or anything related to a specific
525 transfer type or direction, this is not covered yet.
533 =item * get_stock PARAMS
535 Returns for single parts how much actually exists in the inventory.
543 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
547 If given, will only return stock on these bins. Optional. May be array, May be object or id.
551 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
555 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
559 If given, will only show stock with this chargenumber. Optional. May be array.
563 See L</"STOCK/ONHAND REPORT MODE">
567 See L</"STOCK/ONHAND REPORT MODE">
571 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
572 mode when C<by> is given.
574 =item * get_onhand PARAMS
576 Returns for single parts how much is available in the inventory. That excludes
577 stock with expired bestbefore.
579 It takes all options of L</get_stock> and has some additional ones:
587 =item * allocate PARAMS
599 Bin object. Optional.
603 Warehouse object. Optional.
615 Tries to allocate the required quantity using what is currently onhand. If
616 given any of C<bin>, C<warehouse>, C<chargenumber>
618 =item * allocate_for_assembly PARAMS
620 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
621 compute the required amount for each assembly part and allocate all of them.
623 =item * produce_assembly
628 =head1 STOCK/ONHAND REPORT MODE
630 If the special option C<by> is given with an arrayref, the result will instead
631 be an arrayref of partitioned stocks by those fields. Valid partitions are:
637 If this is given, part is optional in the parameters
649 Note: If you want to use the returned data to create allocations you I<need> to
650 enable all of these. To make this easier a special shortcut exists
652 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
653 C<parts> objects in one go, just like with Rose. They
654 need to be present in C<by> before that though.
656 =head1 ALLOCATION ALGORITHM
658 When calling allocate, the current onhand (== available stock) of the item will
659 be used to decide which bins/chargenumbers/bestbefore can be used.
661 In general allocate will try to make the request happen, and will use the
662 provided charges up first, and then tap everything else. If you need to only
663 I<exactly> use the provided charges, you'll need to craft the allocations
664 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
666 If C<chargenumber> is given, those will be used up next.
668 After that normal quantities will be used.
670 These are tiebreakers and expected to rarely matter in reality. If you need
671 finegrained control over which allocation is used, you may want to get the
672 onhands yourself and select the appropriate ones.
674 Only quantities with C<bestbefore> unset or after the given date will be
675 considered. If more than one charge is eligible, the earlier C<bestbefore>
678 Allocations do NOT have an internal memory and can't react to other allocations
679 of the same part earlier. Never double allocate the same part within a
682 =head1 ALLOCATION DATA STRUCTURE
684 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
685 each of the following attributes to be set at creation time:
701 =item * for_object_id
703 If set the allocations will be marked as allocated for the given object.
704 If these allocations are later used to produce an assembly, the resulting
705 consuming transactions will be marked as belonging to the given object.
706 The object may be an order, productionorder or other objects
710 C<chargenumber>, C<bestbefore> and C<for_object_id> may be C<undef> (but must
711 still be present at creation time). Instances are considered immutable.
716 # whitelist constraints
720 bin_id => \@allowed_bins,
721 chargenumber => \@allowed_chargenumbers,
728 # only allow chargenumbers with specific format
729 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
732 # and must all have a bestbefore date
733 all { $_->bestbefore } @_;
737 C<allocation> is "best effort" in nature. It will take the C<bin>,
738 C<chargenumber> etc hints from the parameters, but will try it's bvest to
739 fulfil the request anyway and only bail out if it is absolutely not possible.
741 Sometimes you need to restrict allocations though. For this you can pass
742 additional constraints to C<allocate>. A constraint serves as a whitelist.
743 Every allocation must fulfil every constraint by having that attribute be one
746 In case even that is not enough, you may supply a custom check by passing a
747 function that will be given the allocation objects.
749 Note that both whitelists and constraints do not influence the order of
750 allocations, which is done purely from the initial parameters. They only serve
751 to reject allocations made in good faith which do fulfil required assertions.
753 =head1 ERROR HANDLING
755 C<allocate> and C<produce_assembly> will throw exceptions if the request can
756 not be completed. The usual reason will be insufficient onhand to allocate, or
757 insufficient allocations to process the request.
761 * define and describe error classes
762 * define wrapper classes for stock/onhand batch mode return values
763 * handle extra arguments in produce: shippingdate, project, oe
764 * clean up allocation helper class
765 * with objects for reservations
775 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>