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(_format_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} && $onhand_mode && default_show_bestbefore()) {
66 $params{bestbefore} = DateTime->now_local;
69 if ($params{bestbefore}) {
70 Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
71 push @where, sprintf "(bestbefore IS NULL OR bestbefore >= ?)";
72 push @values, $params{bestbefore};
77 part => [ qw(parts_id) ],
78 bin => [ qw(bin_id inventory.warehouse_id)],
79 warehouse => [ qw(inventory.warehouse_id) ],
80 chargenumber => [ qw(chargenumber) ],
81 bestbefore => [ qw(bestbefore) ],
82 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
86 for (listify($params{by})) {
87 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
88 push @selects, @$selects;
89 push @groups, @$selects;
93 my $select = join ',', @selects;
94 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
95 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
98 SELECT $select FROM inventory
99 LEFT JOIN bin ON bin_id = bin.id
100 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
105 $query .= ' HAVING SUM(qty) > 0';
108 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
111 part => 'SL::DB::Manager::Part',
112 bin => 'SL::DB::Manager::Bin',
113 warehouse => 'SL::DB::Manager::Warehouse',
119 warehouse => 'warehouse_id',
122 if ($params{by} && $params{with_objects}) {
123 for my $with_object (listify($params{with_objects})) {
124 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
126 my $manager = $with_objects{$with_object};
127 my $slot = $slots{$with_object};
128 next if !(my @ids = map { $_->{$slot} } @$results);
129 my $objects = $manager->get_all(query => [ id => \@ids ]);
130 my %objects_by_id = map { $_->id => $_ } @$objects;
132 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
139 return $results->[0]{qty};
144 _get_stock_onhand(@_, onhand => 0);
148 _get_stock_onhand(@_, onhand => 1);
154 die SL::X::Inventory::Allocation->new(
155 error => 'allocate needs a part',
156 msg => t8("Method allocate needs the parameter 'part'"),
157 ) unless $params{part};
158 die SL::X::Inventory::Allocation->new(
159 error => 'allocate needs a qty',
160 msg => t8("Method allocate needs the parameter 'qty'"),
161 ) unless $params{qty};
163 my $part = $params{part};
164 my $qty = $params{qty};
166 return () if $qty <= 0;
168 my $results = get_stock(part => $part, by => 'for_allocate');
169 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
170 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
171 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
173 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
174 my @sorted_results = sort {
175 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
176 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
177 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
178 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
183 for my $chunk (@sorted_results) {
184 my $qty = min($chunk->{qty}, $rest_qty);
186 # since allocate operates on stock, this also ensures that no negative stock results are used
188 push @allocations, SL::Helper::Inventory::Allocation->new(
189 parts_id => $chunk->{parts_id},
191 comment => $params{comment},
192 bin_id => $chunk->{bin_id},
193 warehouse_id => $chunk->{warehouse_id},
194 chargenumber => $chunk->{chargenumber},
195 bestbefore => $chunk->{bestbefore},
196 for_object_id => undef,
198 $rest_qty -= _round_number($qty, 5);
200 $rest_qty = _round_number($rest_qty, 5);
201 last if $rest_qty == 0;
204 die SL::X::Inventory::Allocation->new(
205 error => 'not enough to allocate',
206 msg => t8("can not allocate #1 units of #2, missing #3 units", _format_number($qty), $part->displayable_name, _format_number($rest_qty)),
209 if ($params{constraints}) {
210 check_constraints($params{constraints},\@allocations);
216 sub allocate_for_assembly {
219 my $part = $params{part} or Carp::croak('allocate needs a part');
220 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
222 Carp::croak('not an assembly') unless $part->is_assembly;
224 my %parts_to_allocate;
226 for my $assembly ($part->assemblies) {
227 $parts_to_allocate{ $assembly->part->id } //= 0;
228 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty;
233 for my $part_id (keys %parts_to_allocate) {
234 my $part = SL::DB::Part->load_cached($part_id);
235 push @allocations, allocate(%params, part => $part, qty => $parts_to_allocate{$part_id});
241 sub check_constraints {
242 my ($constraints, $allocations) = @_;
243 if ('CODE' eq ref $constraints) {
244 if (!$constraints->(@$allocations)) {
245 die SL::X::Inventory::Allocation->new(
246 error => 'allocation constraints failure',
247 msg => t8("Allocations didn't pass constraints"),
251 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
253 my %supported_constraints = (
255 warehouse_id => 'warehouse_id',
256 chargenumber => 'chargenumber',
259 for (keys %$constraints ) {
260 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
261 next unless defined $constraints->{$_};
263 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
264 my $accessor = $supported_constraints{$_};
266 if (any { !$whitelist{$_->$accessor} } @$allocations) {
267 my %error_constraints = (
268 bin_id => t8('Bins'),
269 warehouse_id => t8('Warehouses'),
270 chargenumber => t8('Chargenumbers'),
272 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
273 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
274 my $err = t8("Cannot allocate parts.");
275 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
276 SL::DB::Part->load_cached($_->parts_id)->description,
277 SL::DB::Bin->load_cached($_->bin_id)->full_description,
278 _format_number($_->qty), _format_number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
279 die SL::X::Inventory::Allocation->new(
280 error => 'allocation constraints failure',
288 sub produce_assembly {
291 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
292 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
294 my $allocations = $params{allocations};
295 if ($params{auto_allocate}) {
296 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
297 $allocations = [ allocate_for_assembly(part => $part, qty => $qty) ];
299 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
300 $allocations = $params{allocations};
303 my $bin = $params{bin} or Carp::croak("need target bin");
304 my $chargenumber = $params{chargenumber};
305 my $bestbefore = $params{bestbefore};
306 my $for_object_id = $params{for_object_id};
307 my $comment = $params{comment} // '';
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;
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, $allocation->transfer_object(
334 trans_id => $trans_id,
335 qty => -$allocation->qty,
336 trans_type => $trans_type_out,
337 shippingdate => $shippingdate,
338 employee => SL::DB::Manager::Employee->current,
342 push @transfers, SL::DB::Inventory->new(
343 trans_id => $trans_id,
344 trans_type => $trans_type_in,
348 warehouse => $bin->warehouse_id,
349 chargenumber => $chargenumber,
350 bestbefore => $bestbefore,
351 shippingdate => $shippingdate,
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 sub default_show_bestbefore {
370 $::instance_conf->get_show_bestbefore
373 package SL::Helper::Inventory::Allocation {
374 my @attributes = qw(parts_id qty bin_id warehouse_id chargenumber bestbefore comment for_object_id);
375 my %attributes = map { $_ => 1 } @attributes;
376 my %mapped_attributes = (
377 for_object_id => 'oe_id',
380 for my $name (@attributes) {
382 *{"SL::Helper::Inventory::Allocation::$name"} = sub { $_[0]{$name} };
386 my ($class, %params) = @_;
388 Carp::croak("missing attribute $_") for grep { !exists $params{$_} } @attributes;
389 Carp::croak("unknown attribute $_") for grep { !exists $attributes{$_} } keys %params;
390 Carp::croak("$_ must be set") for grep { !$params{$_} } qw(parts_id qty bin_id);
391 Carp::croak("$_ must be positive") for grep { !($params{$_} > 0) } qw(parts_id qty bin_id);
393 bless { %params }, $class;
396 sub transfer_object {
397 my ($self, %params) = @_;
399 SL::DB::Inventory->new(
401 my $attr = $mapped_attributes{$_} // $_;
402 $attr => $self->{$attr}
415 SL::WH - Warehouse and Inventory API
419 # See description for an intro to the concepts used here.
421 use SL::Helper::Inventory qw(:ALL);
423 # stock, get "what's there" for a part with various conditions:
424 my $qty = get_stock(part => $part); # how much is on stock?
425 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
426 my $qty = get_stock(part => $part, bin => $bin); # how is on stock in a specific bin?
427 my $qty = get_stock(part => $part, warehouse => $warehouse); # how is on stock in a specific warehouse?
428 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how is on stock of a specific chargenumber?
430 # onhand, get "what's available" for a part with various conditions:
431 my $qty = get_onhand(part => $part); # how much is available?
432 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
433 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
434 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
435 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
438 my $data = get_onhand(
439 warehouse => $warehouse,
440 by => [ qw(bin part chargenumber) ],
441 with_objects => [ qw(bin part) ],
445 my @allocations, 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 bin => $bin, # optional, may be arrayref. if provided
453 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
454 my @allocations, allocate_for_assembly(
455 part => $assembly, # part_id works too
456 qty => $qty, # must be positive
459 # create allocation manually, bypassing checks, all of these need to be passed, even undefs
460 my $allocation = SL::Helper::Inventory::Allocation->new(
461 part_id => $part->id,
463 bin_id => $bin_obj->id,
464 warehouse_id => $bin_obj->warehouse_id,
465 chargenumber => '1823772365',
467 for_object_id => $order->id,
472 part => $part, # target assembly
474 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
477 bin => $bin, # needed unless a global standard target is configured
478 chargenumber => $chargenumber, # optional
479 bestbefore => $datetime, # optional
480 comment => $comment, # optional
482 # links, all optional
487 New functions for the warehouse and inventory api.
489 The WH api currently has three large shortcomings. It is very hard to just get
490 the current stock for an item, it's extremely complicated to use it to produce
491 assemblies while ensuring that no stock ends up negative, and it's very hard to
492 use it to get an overview over the actual contents of the inventory.
494 The first problem has spawned several dozen small functions in the program that
495 try to implement that, and those usually miss some details. They may ignore
496 bestbefore times, comments, ignore negative quantities etc.
498 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
502 =item * Stock is defined as the actual contents of the inventory, everything that is
505 =item * Onhand is what is available, which means things that are stocked,
506 not expired and not reserved for other uses.
510 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
511 allow simple access with some optional filters for chargenumbers or warehouses.
512 Both of them have a batch mode that can be used to get these information to
513 supplement simple reports.
515 To address the safe assembly creation a new function has been added.
516 C<allocate> will try to find the requested quantity of a part in the inventory
517 and will return allocations of it which can then be used to create the
518 assembly. Allocation will happen with the C<onhand> semantics defined above,
519 meaning that by default no expired goods will be used. The caller can supply
520 hints of what shold be used and in those cases chargenumbers will be used up as
521 much as possible first. C<allocate> will always try to fulfil the request even
522 beyond those. Should the required amount not be stocked, allocate will throw an
525 C<produce_assembly> has been rewritten to only accept parameters about the
526 target of the production, and requires allocations to complete the request. The
527 allocations can be supplied manually, or can be generated automatically.
528 C<produce_assembly> will check whether enough allocations are given to create
529 the recipe, but will not check whether the allocations are backed. If the
530 allocations are not sufficient or if the auto-allocation fails an exception
531 is returned. If you need to produce something that is not in the inventory, you
532 can bypass those checks by creating the allocations yourself (see
533 L</"ALLOCATION DATA STRUCTURE">).
535 Note: this is only intended to cover the scenarios described above. For other cases:
541 If you need actual inventory objects because of record links or something like
542 that load them directly. And strongly consider redesigning that, because it's
547 You need weight or accounting information you're on your own. The inventory api
548 only concerns itself with the raw quantities.
552 If you need the first stock date of parts, or anything related to a specific
553 transfer type or direction, this is not covered yet.
561 =item * get_stock PARAMS
563 Returns for single parts how much actually exists in the inventory.
571 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
575 If given, will only return stock on these bins. Optional. May be array, May be object or id.
579 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
583 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
587 If given, will only show stock with this chargenumber. Optional. May be array.
591 See L</"STOCK/ONHAND REPORT MODE">
595 See L</"STOCK/ONHAND REPORT MODE">
599 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
600 mode when C<by> is given.
602 =item * get_onhand PARAMS
604 Returns for single parts how much is available in the inventory. That excludes
605 stock with expired bestbefore.
607 It takes the same options as L</get_stock>.
613 If given, will only return stock with a bestbefore at or after the given date.
614 Optional. Must be L<DateTime> object.
618 =item * allocate PARAMS
630 Bin object. Optional.
634 Warehouse object. Optional.
646 Tries to allocate the required quantity using what is currently onhand. If
647 given any of C<bin>, C<warehouse>, C<chargenumber>
649 =item * allocate_for_assembly PARAMS
651 Shortcut to allocate everything for an assembly. Takes the same arguments. Will
652 compute the required amount for each assembly part and allocate all of them.
654 =item * produce_assembly
659 =head1 STOCK/ONHAND REPORT MODE
661 If the special option C<by> is given with an arrayref, the result will instead
662 be an arrayref of partitioned stocks by those fields. Valid partitions are:
668 If this is given, part is optional in the parameters
680 Note: If you want to use the returned data to create allocations you I<need> to
681 enable all of these. To make this easier a special shortcut exists
683 In this mode, C<with_objects> can be used to load C<warehouse>, C<bin>,
684 C<parts> objects in one go, just like with Rose. They
685 need to be present in C<by> before that though.
687 =head1 ALLOCATION ALGORITHM
689 When calling allocate, the current onhand (== available stock) of the item will
690 be used to decide which bins/chargenumbers/bestbefore can be used.
692 In general allocate will try to make the request happen, and will use the
693 provided charges up first, and then tap everything else. If you need to only
694 I<exactly> use the provided charges, you'll need to craft the allocations
695 yourself. See L</"ALLOCATION DATA STRUCTURE"> for that.
697 If C<chargenumber> is given, those will be used up next.
699 After that normal quantities will be used.
701 These are tiebreakers and expected to rarely matter in reality. If you need
702 finegrained control over which allocation is used, you may want to get the
703 onhands yourself and select the appropriate ones.
705 Only quantities with C<bestbefore> unset or after the given date will be
706 considered. If more than one charge is eligible, the earlier C<bestbefore>
709 Allocations do NOT have an internal memory and can't react to other allocations
710 of the same part earlier. Never double allocate the same part within a
713 =head1 ALLOCATION DATA STRUCTURE
715 Allocations are instances of the helper class C<SL::Helper::Inventory::Allocation>. They require
716 each of the following attributes to be set at creation time:
732 =item * for_object_id
734 If set the allocations will be marked as allocated for the given object.
735 If these allocations are later used to produce an assembly, the resulting
736 consuming transactions will be marked as belonging to the given object.
737 The object may be an order, productionorder or other objects
741 C<chargenumber>, C<bestbefore> and C<for_object_id> and C<comment> may be
742 C<undef> (but must still be present at creation time). Instances are considered
745 Allocations also provide the method C<transfer_object> which will create a new
746 C<SL::DB::Inventory> bject with all the playload.
750 # whitelist constraints
754 bin_id => \@allowed_bins,
755 chargenumber => \@allowed_chargenumbers,
762 # only allow chargenumbers with specific format
763 all { $_->chargenumber =~ /^ C \d{8} - \a{d2} $/x } @_
766 # and must all have a bestbefore date
767 all { $_->bestbefore } @_;
771 C<allocation> is "best effort" in nature. It will take the C<bin>,
772 C<chargenumber> etc hints from the parameters, but will try it's bvest to
773 fulfil the request anyway and only bail out if it is absolutely not possible.
775 Sometimes you need to restrict allocations though. For this you can pass
776 additional constraints to C<allocate>. A constraint serves as a whitelist.
777 Every allocation must fulfil every constraint by having that attribute be one
780 In case even that is not enough, you may supply a custom check by passing a
781 function that will be given the allocation objects.
783 Note that both whitelists and constraints do not influence the order of
784 allocations, which is done purely from the initial parameters. They only serve
785 to reject allocations made in good faith which do fulfil required assertions.
787 =head1 ERROR HANDLING
789 C<allocate> and C<produce_assembly> will throw exceptions if the request can
790 not be completed. The usual reason will be insufficient onhand to allocate, or
791 insufficient allocations to process the request.
793 =head1 KNOWN PROBLEMS
795 * It's not currently possible to identify allocations between requests, for
796 example for presenting the user possible allocations and then actually using
797 them on the next request.
798 * It's not currently possible to give C<allocate> prior constraints.
799 Currently all constraints are treated as hints (and will be preferred) but
800 the internal ordering of the hints is fixed and more complex preferentials
802 * bestbefore handling is untested
806 * define and describe error classes
807 * define wrapper classes for stock/onhand batch mode return values
808 * handle extra arguments in produce: shippingdate, project
809 * clean up allocation helper class
819 Sven Schöling E<lt>sven.schoeling@opendynamic.deE<gt>