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);
17 use SL::Helper::Inventory::Allocation;
20 our @EXPORT_OK = qw(get_stock get_onhand allocate allocate_for_assembly produce_assembly check_constraints);
21 our %EXPORT_TAGS = (ALL => \@EXPORT_OK);
23 sub _get_stock_onhand {
26 my $onhand_mode = !!$params{onhand};
30 'MIN(EXTRACT(epoch FROM inventory.itime)) AS itime',
37 my @ids = map { ref $_ ? $_->id : $_ } listify($params{part});
38 push @where, sprintf "parts_id IN (%s)", join ', ', ("?") x @ids;
43 my @ids = map { ref $_ ? $_->id : $_ } listify($params{bin});
44 push @where, sprintf "bin_id IN (%s)", join ', ', ("?") x @ids;
48 if ($params{warehouse}) {
49 my @ids = map { ref $_ ? $_->id : $_ } listify($params{warehouse});
50 push @where, sprintf "warehouse.id IN (%s)", join ', ', ("?") x @ids;
54 if ($params{chargenumber}) {
55 my @ids = listify($params{chargenumber});
56 push @where, sprintf "chargenumber IN (%s)", join ', ', ("?") x @ids;
61 Carp::croak("not DateTime ".$params{date}) unless ref($params{date}) eq 'DateTime';
62 push @where, sprintf "shippingdate <= ?";
63 push @values, $params{date};
66 if (!$params{bestbefore} && $onhand_mode && default_show_bestbefore()) {
67 $params{bestbefore} = DateTime->now_local;
70 if ($params{bestbefore}) {
71 Carp::croak("not DateTime ".$params{date}) unless ref($params{bestbefore}) eq 'DateTime';
72 push @where, sprintf "(bestbefore IS NULL OR bestbefore >= ?)";
73 push @values, $params{bestbefore};
78 part => [ qw(parts_id) ],
79 bin => [ qw(bin_id inventory.warehouse_id)],
80 warehouse => [ qw(inventory.warehouse_id) ],
81 chargenumber => [ qw(chargenumber) ],
82 bestbefore => [ qw(bestbefore) ],
83 for_allocate => [ qw(parts_id bin_id inventory.warehouse_id chargenumber bestbefore) ],
87 for (listify($params{by})) {
88 my $selects = $allowed_by{$_} or Carp::croak("unknown option for by: $_");
89 push @selects, @$selects;
90 push @groups, @$selects;
94 my $select = join ',', @selects;
95 my $where = @where ? 'WHERE ' . join ' AND ', @where : '';
96 my $group_by = @groups ? 'GROUP BY ' . join ', ', @groups : '';
99 SELECT $select FROM inventory
100 LEFT JOIN bin ON bin_id = bin.id
101 LEFT JOIN warehouse ON bin.warehouse_id = warehouse.id
106 $query .= ' HAVING SUM(qty) > 0';
109 my $results = selectall_hashref_query($::form, SL::DB->client->dbh, $query, @values);
112 part => 'SL::DB::Manager::Part',
113 bin => 'SL::DB::Manager::Bin',
114 warehouse => 'SL::DB::Manager::Warehouse',
120 warehouse => 'warehouse_id',
123 if ($params{by} && $params{with_objects}) {
124 for my $with_object (listify($params{with_objects})) {
125 Carp::croak("unknown with_object $with_object") if !exists $with_objects{$with_object};
127 my $manager = $with_objects{$with_object};
128 my $slot = $slots{$with_object};
129 next if !(my @ids = map { $_->{$slot} } @$results);
130 my $objects = $manager->get_all(query => [ id => \@ids ]);
131 my %objects_by_id = map { $_->id => $_ } @$objects;
133 $_->{$with_object} = $objects_by_id{$_->{$slot}} for @$results;
140 return $results->[0]{qty};
145 _get_stock_onhand(@_, onhand => 0);
149 _get_stock_onhand(@_, onhand => 1);
155 croak('allocate needs a part') unless $params{part};
156 croak('allocate needs a qty') unless $params{qty};
158 my $part = $params{part};
159 my $qty = $params{qty};
161 return () if $qty <= 0;
163 my $results = get_stock(part => $part, by => 'for_allocate');
164 my %bin_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{bin});
165 my %wh_whitelist = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{warehouse});
166 my %chargenumbers = map { (ref $_ ? $_->id : $_) => 1 } grep defined, listify($params{chargenumber});
168 # sort results so that chargenumbers are matched first, then wanted bins, then wanted warehouses
169 my @sorted_results = sort {
170 exists $chargenumbers{$b->{chargenumber}} <=> exists $chargenumbers{$a->{chargenumber}} # then prefer wanted chargenumbers
171 || exists $bin_whitelist{$b->{bin_id}} <=> exists $bin_whitelist{$a->{bin_id}} # then prefer wanted bins
172 || exists $wh_whitelist{$b->{warehouse_id}} <=> exists $wh_whitelist{$a->{warehouse_id}} # then prefer wanted bins
173 || $a->{itime} <=> $b->{itime} # and finally prefer earlier charges
178 for my $chunk (@sorted_results) {
179 my $qty = min($chunk->{qty}, $rest_qty);
181 # since allocate operates on stock, this also ensures that no negative stock results are used
183 push @allocations, SL::Helper::Inventory::Allocation->new(
184 parts_id => $chunk->{parts_id},
186 comment => $params{comment},
187 bin_id => $chunk->{bin_id},
188 warehouse_id => $chunk->{warehouse_id},
189 chargenumber => $chunk->{chargenumber},
190 bestbefore => $chunk->{bestbefore},
191 for_object_id => undef,
193 $rest_qty -= _round_number($qty, 5);
195 $rest_qty = _round_number($rest_qty, 5);
196 last if $rest_qty == 0;
199 die SL::X::Inventory::Allocation->new(
200 code => 'not enough to allocate',
201 message => t8("can not allocate #1 units of #2, missing #3 units", _format_number($qty), $part->displayable_name, _format_number($rest_qty)),
204 if ($params{constraints}) {
205 check_constraints($params{constraints},\@allocations);
211 sub allocate_for_assembly {
214 my $part = $params{part} or Carp::croak('allocate needs a part');
215 my $qty = $params{qty} or Carp::croak('allocate needs a qty');
216 my $wh = $params{warehouse};
217 my $wh_strict = $::instance_conf->get_produce_assembly_same_warehouse;
219 Carp::croak('not an assembly') unless $part->is_assembly;
220 Carp::croak('No warehouse selected') if $wh_strict && !$wh;
222 my %parts_to_allocate;
224 for my $assembly ($part->assemblies) {
225 next if $assembly->part->type eq 'service' && 1;
226 $parts_to_allocate{ $assembly->part->id } //= 0;
227 $parts_to_allocate{ $assembly->part->id } += $assembly->qty * $qty;
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});
236 die SL::X::Inventory::Allocation->new(
237 code => "wrong warehouse for part",
238 message => t8('Part #1 exists in warehouse #2, but not in warehouse #3 ',
239 $part->partnumber . ' ' . $part->description,
240 SL::DB::Manager::Warehouse->find_by(id => $allocations[-1]->{warehouse_id})->description,
242 ) unless $allocations[-1]->{warehouse_id} == $wh->id;
249 sub check_constraints {
250 my ($constraints, $allocations) = @_;
251 if ('CODE' eq ref $constraints) {
252 if (!$constraints->(@$allocations)) {
253 die SL::X::Inventory::Allocation->new(
254 code => 'allocation constraints failure',
255 message => t8("Allocations didn't pass constraints"),
259 croak 'constraints needs to be a hashref' unless 'HASH' eq ref $constraints;
261 my %supported_constraints = (
263 warehouse_id => 'warehouse_id',
264 chargenumber => 'chargenumber',
267 for (keys %$constraints ) {
268 croak "unsupported constraint '$_'" unless $supported_constraints{$_};
269 next unless defined $constraints->{$_};
271 my %whitelist = map { (ref $_ ? $_->id : $_) => 1 } listify($constraints->{$_});
272 my $accessor = $supported_constraints{$_};
274 if (any { !$whitelist{$_->$accessor} } @$allocations) {
275 my %error_constraints = (
276 bin_id => t8('Bins'),
277 warehouse_id => t8('Warehouses'),
278 chargenumber => t8('Chargenumbers'),
280 my @allocs = grep { $whitelist{$_->$accessor} } @$allocations;
281 my $needed = sum map { $_->qty } grep { !$whitelist{$_->$accessor} } @$allocations;
282 my $err = t8("Cannot allocate parts.");
283 $err .= ' '.t8('part \'#\'1 in bin \'#2\' only with qty #3 (need additional #4) and chargenumber \'#5\'.',
284 SL::DB::Part->load_cached($_->parts_id)->description,
285 SL::DB::Bin->load_cached($_->bin_id)->full_description,
286 _format_number($_->qty), _format_number($needed), $_->chargenumber ? $_->chargenumber : '--') for @allocs;
287 die SL::X::Inventory::Allocation->new(
288 code => 'allocation constraints failure',
296 sub produce_assembly {
299 my $part = $params{part} or Carp::croak('produce_assembly needs a part');
300 my $qty = $params{qty} or Carp::croak('produce_assembly needs a qty');
301 my $bin = $params{bin} or Carp::croak("need target bin");
303 my $allocations = $params{allocations};
304 my $strict_wh = $::instance_conf->get_produce_assembly_same_warehouse ? $bin->warehouse : undef;
305 if ($params{auto_allocate}) {
306 Carp::croak("produce_assembly: can't have both allocations and auto_allocate") if $params{allocations};
307 $allocations = [ allocate_for_assembly(part => $part, qty => $qty, warehouse => $strict_wh) ];
309 Carp::croak("produce_assembly: need allocations or auto_allocate to produce something") if !$params{allocations};
310 $allocations = $params{allocations};
313 my $chargenumber = $params{chargenumber};
314 my $bestbefore = $params{bestbefore};
315 my $for_object_id = $params{for_object_id};
316 my $comment = $params{comment} // '';
317 my $invoice = $params{invoice};
318 my $project = $params{project};
319 my $shippingdate = $params{shippingsdate} // DateTime->now_local;
320 my $trans_id = $params{trans_id};
322 ($trans_id) = selectrow_query($::form, SL::DB->client->dbh, qq|SELECT nextval('id')| ) unless $trans_id;
324 my $trans_type_out = SL::DB::Manager::TransferType->find_by(direction => 'out', description => 'used');
325 my $trans_type_in = SL::DB::Manager::TransferType->find_by(direction => 'in', description => 'assembled');
327 # check whether allocations are sane
328 if (!$params{no_check_allocations} && !$params{auto_allocate}) {
329 my %allocations_by_part = map { $_->parts_id => $_->qty } @$allocations;
330 for my $assembly ($part->assemblies) {
331 $allocations_by_part{ $assembly->parts_id } -= $assembly->qty * $qty;
334 die SL::X::Inventory::Allocation->new(
335 code => "allocations are insufficient for production",
336 message => t8('can not allocate enough resources for production'),
337 ) if any { $_ < 0 } values %allocations_by_part;
341 for my $allocation (@$allocations) {
342 my $oe_id = delete $allocation->{for_object_id};
343 push @transfers, $allocation->transfer_object(
344 trans_id => $trans_id,
345 qty => -$allocation->qty,
346 trans_type => $trans_type_out,
347 shippingdate => $shippingdate,
348 employee => SL::DB::Manager::Employee->current,
349 comment => t8('Used for assembly #1 #2', $part->partnumber, $part->description),
353 push @transfers, SL::DB::Inventory->new(
354 trans_id => $trans_id,
355 trans_type => $trans_type_in,
359 warehouse => $bin->warehouse_id,
360 chargenumber => $chargenumber,
361 bestbefore => $bestbefore,
362 shippingdate => $shippingdate,
366 employee => SL::DB::Manager::Employee->current,
367 oe_id => $for_object_id,
370 SL::DB->client->with_transaction(sub {
371 $_->save for @transfers;
374 die SL::DB->client->error;
380 sub default_show_bestbefore {
381 $::instance_conf->get_show_bestbefore
390 SL::WH - Warehouse and Inventory API
394 # See description for an intro to the concepts used here.
396 use SL::Helper::Inventory qw(:ALL);
398 # stock, get "what's there" for a part with various conditions:
399 my $qty = get_stock(part => $part); # how much is on stock?
400 my $qty = get_stock(part => $part, date => $date); # how much was on stock at a specific time?
401 my $qty = get_stock(part => $part, bin => $bin); # how much is on stock in a specific bin?
402 my $qty = get_stock(part => $part, warehouse => $warehouse); # how much is on stock in a specific warehouse?
403 my $qty = get_stock(part => $part, chargenumber => $chargenumber); # how much is on stock of a specific chargenumber?
405 # onhand, get "what's available" for a part with various conditions:
406 my $qty = get_onhand(part => $part); # how much is available?
407 my $qty = get_onhand(part => $part, date => $date); # how much was available at a specific time?
408 my $qty = get_onhand(part => $part, bin => $bin); # how much is available in a specific bin?
409 my $qty = get_onhand(part => $part, warehouse => $warehouse); # how much is available in a specific warehouse?
410 my $qty = get_onhand(part => $part, chargenumber => $chargenumber); # how much is availbale of a specific chargenumber?
413 my $data = get_onhand(
414 warehouse => $warehouse,
415 by => [ qw(bin part chargenumber) ],
416 with_objects => [ qw(bin part) ],
420 my @allocations = allocate(
421 part => $part, # part_id works too
422 qty => $qty, # must be positive
423 chargenumber => $chargenumber, # optional, may be arrayref. if provided these charges will be used first
424 bestbefore => $datetime, # optional, defaults to today. items with bestbefore prior to that date wont be used
425 bin => $bin, # optional, may be arrayref. if provided
428 # shortcut to allocate all that is needed for producing an assembly, will use chargenumbers as appropriate
429 my @allocations = allocate_for_assembly(
430 part => $assembly, # part_id works too
431 qty => $qty, # must be positive
434 # create allocation manually, bypassing checks. all of these need to be passed, even undefs
435 my $allocation = SL::Helper::Inventory::Allocation->new(
436 part_id => $part->id,
438 bin_id => $bin_obj->id,
439 warehouse_id => $bin_obj->warehouse_id,
440 chargenumber => '1823772365',
442 for_object_id => $order->id,
447 part => $part, # target assembly
449 allocations => \@allocations, # allocations to use. alternatively use "auto_allocate => 1,"
452 bin => $bin, # needed unless a global standard target is configured
453 chargenumber => $chargenumber, # optional
454 bestbefore => $datetime, # optional
455 comment => $comment, # optional
460 New functions for the warehouse and inventory api.
462 The WH api currently has three large shortcomings: It is very hard to just get
463 the current stock for an item, it's extremely complicated to use it to produce
464 assemblies while ensuring that no stock ends up negative, and it's very hard to
465 use it to get an overview over the actual contents of the inventory.
467 The first problem has spawned several dozen small functions in the program that
468 try to implement that, and those usually miss some details. They may ignore
469 bestbefore times, comments, ignore negative quantities etc.
471 To get this cleaned up a bit this code introduces two concepts: stock and onhand.
475 =item * Stock is defined as the actual contents of the inventory, everything that is
478 =item * Onhand is what is available, which means things that are stocked,
479 not expired and not in any other way reserved for other uses.
483 The two new functions C<get_stock> and C<get_onhand> encapsulate these principles and
484 allow simple access with some optional filters for chargenumbers or warehouses.
485 Both of them have a batch mode that can be used to get these information to
486 supplement simple reports.
488 To address the safe assembly creation a new function has been added.
489 C<allocate> will try to find the requested quantity of a part in the inventory
490 and will return allocations of it which can then be used to create the
491 assembly. Allocation will happen with the C<onhand> semantics defined above,
492 meaning that by default no expired goods will be used. The caller can supply
493 hints of what shold be used and in those cases chargenumbers will be used up as
494 much as possible first. C<allocate> will always try to fulfil the request even
495 beyond those. Should the required amount not be stocked, allocate will throw an
498 C<produce_assembly> has been rewritten to only accept parameters about the
499 target of the production, and requires allocations to complete the request. The
500 allocations can be supplied manually, or can be generated automatically.
501 C<produce_assembly> will check whether enough allocations are given to create
502 the assembly, but will not check whether the allocations are backed. If the
503 allocations are not sufficient or if the auto-allocation fails an exception
504 is returned. If you need to produce something that is not in the inventory, you
505 can bypass those checks by creating the allocations yourself (see
506 L</"ALLOCATION DATA STRUCTURE">).
508 Note: this is only intended to cover the scenarios described above. For other cases:
514 If you need actual inventory objects because of record links or something like
515 that load them directly. And strongly consider redesigning that, because it's
520 You need weight or accounting information you're on your own. The inventory api
521 only concerns itself with the raw quantities.
525 If you need the first stock date of parts, or anything related to a specific
526 transfer type or direction, this is not covered yet.
534 =item * get_stock PARAMS
536 Returns for single parts how much actually exists in the inventory.
544 The part. Must be present without C<by>. May be arrayref with C<by>. Can be object or id.
548 If given, will only return stock on these bins. Optional. May be array, May be object or id.
552 If given, will only return stock on these warehouses. Optional. May be array, May be object or id.
556 If given, will return stock as it were on this timestamp. Optional. Must be L<DateTime> object.
560 If given, will only show stock with this chargenumber. Optional. May be array.
564 See L</"STOCK/ONHAND REPORT MODE">
568 See L</"STOCK/ONHAND REPORT MODE">
572 Will return a single qty normally, see L</"STOCK/ONHAND REPORT MODE"> for batch
573 mode when C<by> is given.
575 =item * get_onhand PARAMS
577 Returns for single parts how much is available in the inventory. That excludes
578 stock with expired bestbefore.
580 It takes the same options as L</get_stock>.
586 If given, will only return stock with a bestbefore at or after the given date.
587 Optional. Must be L<DateTime> object.
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> and C<comment> may be
715 C<undef> (but must still be present at creation time). Instances are considered
718 Allocations also provide the method C<transfer_object> which will create a new
719 C<SL::DB::Inventory> bject with all the playload.
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.
766 =head1 KNOWN PROBLEMS
768 * It's not currently possible to identify allocations between requests, for
769 example for presenting the user possible allocations and then actually using
770 them on the next request.
771 * It's not currently possible to give C<allocate> prior constraints.
772 Currently all constraints are treated as hints (and will be preferred) but
773 the internal ordering of the hints is fixed and more complex preferentials
775 * bestbefore handling is untested
776 * interaction with config option "transfer_default_ignore_onhand" is
777 currently undefined (and implicitly ignores it)
781 * define and describe error classes
782 * define wrapper classes for stock/onhand batch mode return values
783 * handle extra arguments in produce: shippingdate, project
793 Sven Schöling E<lt>sven.schoeling@googlemail.comE<gt>