14 |
14 |
|
15 |
15 |
use Rose::Object::MakeMethods::Generic (
|
16 |
16 |
'scalar' => [ qw(objects objects_or_ids shipped_qty keep_matches) ],
|
17 |
|
'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out fill_up item_identity_fields oi2oe oi_qty delivered matches
|
18 |
|
services_deliverable) ],
|
|
17 |
'scalar --get_set_init' => [ qw(oe_ids dbh require_stock_out oi2oe oi_qty delivered matches services_deliverable) ],
|
19 |
18 |
);
|
20 |
19 |
|
21 |
20 |
my $no_stock_item_links_query = <<'';
|
... | ... | |
26 |
25 |
WHERE oi.trans_id IN (%s)
|
27 |
26 |
ORDER BY oi.trans_id, oi.position
|
28 |
27 |
|
29 |
|
# oi not item linked. takes about 250ms for 100k hits
|
30 |
|
# obsolete since 3.5.6
|
31 |
|
my $fill_up_oi_query = <<'';
|
32 |
|
SELECT oi.id, oi.trans_id, oi.position, oi.parts_id, oi.description, oi.reqdate, oi.serialnumber, oi.qty, oi.unit
|
33 |
|
FROM orderitems oi
|
34 |
|
WHERE oi.trans_id IN (%s)
|
35 |
|
ORDER BY oi.trans_id, oi.position
|
36 |
|
|
37 |
|
# doi linked by record, but not by items; 250ms for 100k hits
|
38 |
|
# obsolete since 3.5.6
|
39 |
|
my $no_stock_fill_up_doi_query = <<'';
|
40 |
|
SELECT doi.id, doi.delivery_order_id, doi.position, doi.parts_id, doi.description, doi.reqdate, doi.serialnumber, doi.qty, doi.unit
|
41 |
|
FROM delivery_order_items doi
|
42 |
|
WHERE doi.delivery_order_id IN (
|
43 |
|
SELECT to_id
|
44 |
|
FROM record_links
|
45 |
|
WHERE from_id IN (%s)
|
46 |
|
AND from_table = 'oe'
|
47 |
|
AND to_table = 'delivery_orders'
|
48 |
|
AND to_id = doi.delivery_order_id)
|
49 |
|
AND NOT EXISTS (
|
50 |
|
SELECT NULL
|
51 |
|
FROM record_links
|
52 |
|
WHERE from_table = 'orderitems'
|
53 |
|
AND to_table = 'delivery_order_items'
|
54 |
|
AND to_id = doi.id)
|
55 |
|
|
56 |
28 |
my $stock_item_links_query = <<'';
|
57 |
29 |
SELECT oi.trans_id, oi.id AS oi_id, oi.qty AS oi_qty, oi.unit AS oi_unit, doi.id AS doi_id,
|
58 |
30 |
(CASE WHEN doe.customer_id > 0 THEN -1 ELSE 1 END) * i.qty AS doi_qty, p.unit AS doi_unit
|
... | ... | |
88 |
60 |
AND to_table = 'delivery_order_items'
|
89 |
61 |
AND to_id = doi.id)
|
90 |
62 |
|
91 |
|
my $oe_do_record_links = <<'';
|
92 |
|
SELECT from_id, to_id
|
93 |
|
FROM record_links
|
94 |
|
WHERE from_id IN (%s)
|
95 |
|
AND from_table = 'oe'
|
96 |
|
AND to_table = 'delivery_orders'
|
97 |
|
|
98 |
|
my @known_item_identity_fields = qw(parts_id description reqdate serialnumber);
|
99 |
|
my %item_identity_fields = (
|
100 |
|
parts_id => t8('Part'),
|
101 |
|
description => t8('Description'),
|
102 |
|
reqdate => t8('Reqdate'),
|
103 |
|
serialnumber => t8('Serial Number'),
|
104 |
|
);
|
105 |
|
|
106 |
63 |
sub calculate {
|
107 |
64 |
my ($self, $data) = @_;
|
108 |
65 |
|
... | ... | |
113 |
70 |
return $self unless @{ $self->oe_ids };
|
114 |
71 |
|
115 |
72 |
$self->calculate_item_links;
|
116 |
|
$self->calculate_fill_up if $self->fill_up;
|
117 |
73 |
|
118 |
74 |
$self;
|
119 |
75 |
}
|
... | ... | |
140 |
96 |
}
|
141 |
97 |
}
|
142 |
98 |
|
143 |
|
sub _intersect {
|
144 |
|
my ($a1, $a2) = @_;
|
145 |
|
my %seen;
|
146 |
|
grep { $seen{$_}++ } @$a1, @$a2;
|
147 |
|
}
|
148 |
|
|
149 |
|
sub calculate_fill_up {
|
150 |
|
my ($self) = @_;
|
151 |
|
|
152 |
|
my @oe_ids = @{ $self->oe_ids };
|
153 |
|
|
154 |
|
my $fill_up_doi_query = $self->require_stock_out ? $stock_fill_up_doi_query : $no_stock_fill_up_doi_query;
|
155 |
|
|
156 |
|
my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@oe_ids);
|
157 |
|
my $doi_query = sprintf $fill_up_doi_query, join (', ', ('?')x@oe_ids);
|
158 |
|
my $rl_query = sprintf $oe_do_record_links, join (', ', ('?')x@oe_ids);
|
159 |
|
|
160 |
|
my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @oe_ids);
|
161 |
|
|
162 |
|
return unless @$oi;
|
163 |
|
|
164 |
|
my $doi = selectall_hashref_query($::form, $self->dbh, $doi_query, @oe_ids);
|
165 |
|
my $rl = selectall_hashref_query($::form, $self->dbh, $rl_query, @oe_ids);
|
166 |
|
|
167 |
|
my %oi_by_identity = partition_by { $self->item_identity($_) } @$oi;
|
168 |
|
my %doi_by_id = partition_by { $_->{delivery_order_id} } @$doi;
|
169 |
|
my %doi_by_trans_id;
|
170 |
|
push @{ $doi_by_trans_id{$_->{from_id}} //= [] }, @{ $doi_by_id{$_->{to_id}} }
|
171 |
|
for grep { exists $doi_by_id{$_->{to_id}} } @$rl;
|
172 |
|
|
173 |
|
my %doi_by_identity = partition_by { $self->item_identity($_) } @$doi;
|
174 |
|
|
175 |
|
for my $match (sort keys %oi_by_identity) {
|
176 |
|
next unless exists $doi_by_identity{$match};
|
177 |
|
|
178 |
|
my %oi_by_oe = partition_by { $_->{trans_id} } @{ $oi_by_identity{$match} };
|
179 |
|
for my $trans_id (sort { $a <=> $b } keys %oi_by_oe) {
|
180 |
|
next unless my @sorted_doi = _intersect($doi_by_identity{$match}, $doi_by_trans_id{$trans_id});
|
181 |
|
|
182 |
|
# sorting should be quite fast here, because there are usually only a handful of matches
|
183 |
|
next unless my @sorted_oi = sort { $a->{position} <=> $b->{position} } @{ $oi_by_oe{$trans_id} };
|
184 |
|
|
185 |
|
# parallel walk through sorted oi/doi entries
|
186 |
|
my $oi_i = my $doi_i = 0;
|
187 |
|
my ($oi, $doi) = ($sorted_oi[$oi_i], $sorted_doi[$doi_i]);
|
188 |
|
while ($oi_i < @sorted_oi && $doi_i < @sorted_doi) {
|
189 |
|
$oi = $sorted_oi[++$oi_i], next if $oi->{qty} <= $self->shipped_qty->{$oi->{id}};
|
190 |
|
$doi = $sorted_doi[++$doi_i], next if 0 == $doi->{qty};
|
191 |
|
|
192 |
|
my $factor = AM->convert_unit($doi->{unit} => $oi->{unit});
|
193 |
|
my $min_qty = min($oi->{qty} - $self->shipped_qty->{$oi->{id}}, $doi->{qty} * $factor);
|
194 |
|
|
195 |
|
# min_qty should never be 0 now. the first part triggers the first next,
|
196 |
|
# the second triggers the second next and factor must not be 0
|
197 |
|
# but it would lead to an infinite loop, so catch that.
|
198 |
|
die 'panic! invalid shipping quantity' unless $min_qty;
|
199 |
|
|
200 |
|
$self->shipped_qty->{$oi->{id}} += $min_qty;
|
201 |
|
$doi->{qty} -= $min_qty / $factor; # TODO: find a way to avoid float rounding
|
202 |
|
push @{ $self->matches }, [ $oi->{id}, $doi->{id}, $min_qty, 0 ] if $self->keep_matches;
|
203 |
|
}
|
204 |
|
}
|
205 |
|
}
|
206 |
|
|
207 |
|
$self->oi2oe->{$_->{id}} = $_->{trans_id} for @$oi;
|
208 |
|
$self->oi_qty->{$_->{id}} = $_->{qty} for @$oi;
|
209 |
|
}
|
210 |
|
|
211 |
99 |
sub write_to {
|
212 |
100 |
my ($self, $objects) = @_;
|
213 |
101 |
|
... | ... | |
245 |
133 |
$self->write_to($self->objects);
|
246 |
134 |
}
|
247 |
135 |
|
248 |
|
sub item_identity {
|
249 |
|
my ($self, $row) = @_;
|
250 |
|
|
251 |
|
join $;, map $row->{$_}, @{ $self->item_identity_fields };
|
252 |
|
}
|
253 |
|
|
254 |
136 |
sub normalize_input {
|
255 |
137 |
my ($self, $data) = @_;
|
256 |
138 |
|
... | ... | |
270 |
152 |
$self->shipped_qty({});
|
271 |
153 |
}
|
272 |
154 |
|
273 |
|
# some of the invocations never need to load all orderitems to copute their answers
|
274 |
|
# delivered however needs oi_qty to be set for each orderitem to decide whether
|
275 |
|
# delivered should be set or not.
|
276 |
|
sub ensure_all_orderitems_for_orders {
|
277 |
|
my ($self) = @_;
|
278 |
|
|
279 |
|
return if $self->fill_up;
|
280 |
|
|
281 |
|
my $oi_query = sprintf $fill_up_oi_query, join (', ', ('?')x@{ $self->oe_ids });
|
282 |
|
my $oi = selectall_hashref_query($::form, $self->dbh, $oi_query, @{ $self->oe_ids });
|
283 |
|
for (@$oi) {
|
284 |
|
$self->{oi_qty}{ $_->{id} } //= $_->{qty};
|
285 |
|
$self->{oi2oe}{ $_->{id} } //= $_->{trans_id};
|
286 |
|
}
|
287 |
|
}
|
288 |
|
|
289 |
|
sub available_item_identity_fields {
|
290 |
|
map { [ $_ => $item_identity_fields{$_} ] } @known_item_identity_fields;
|
291 |
|
}
|
292 |
155 |
|
293 |
156 |
sub init_oe_ids {
|
294 |
157 |
my ($self) = @_;
|
... | ... | |
308 |
171 |
sub init_delivered {
|
309 |
172 |
my ($self) = @_;
|
310 |
173 |
|
311 |
|
# is needed in odyn
|
312 |
|
# $self->ensure_all_orderitems_for_orders;
|
313 |
|
|
314 |
174 |
my $d = { };
|
315 |
175 |
for (keys %{ $self->oi_qty }) {
|
316 |
176 |
my $oe_id = $self->oi2oe->{$_};
|
... | ... | |
321 |
181 |
}
|
322 |
182 |
|
323 |
183 |
sub init_require_stock_out { $::instance_conf->get_shipped_qty_require_stock_out }
|
324 |
|
sub init_item_identity_fields { [ grep $item_identity_fields{$_}, @{ $::instance_conf->get_shipped_qty_item_identity_fields } ] }
|
325 |
|
sub init_fill_up { $::instance_conf->get_shipped_qty_fill_up }
|
326 |
184 |
|
327 |
185 |
sub init_services_deliverable {
|
328 |
186 |
my ($self) = @_;
|
... | ... | |
350 |
208 |
use SL::Helper::ShippedQty;
|
351 |
209 |
|
352 |
210 |
my $helper = SL::Helper::ShippedQty->new(
|
353 |
|
fill_up => 0,
|
354 |
211 |
require_stock_out => 0,
|
355 |
212 |
item_identity_fields => [ qw(parts_id description reqdate serialnumber) ],
|
356 |
213 |
);
|
... | ... | |
402 |
259 |
|
403 |
260 |
=item *
|
404 |
261 |
|
405 |
|
How to find the correct matching elements. After the changes
|
406 |
|
to record item links it's natural to assume that each position is linked, but
|
407 |
|
for various reasons this might not be the case. Positions that are not linked
|
408 |
|
in the database need to be matched by marching.
|
409 |
|
|
410 |
|
=item *
|
411 |
|
|
412 |
|
Double links need to be accounted for (these can stem from buggy code).
|
413 |
|
|
414 |
|
=item *
|
415 |
|
|
416 |
262 |
orderitems and oe entries may link to many of their counterparts in
|
417 |
|
delivery_orders. delivery_orders my be created from multiple orders. The
|
|
263 |
delivery_orders. delivery_orders may be created from multiple orders. The
|
418 |
264 |
only constant is that a single entry in delivery_order_items has at most one
|
419 |
265 |
link from an orderitem.
|
420 |
266 |
|
421 |
267 |
=item *
|
422 |
268 |
|
423 |
|
For the fill up case the identity of positions is not clear. The naive approach
|
424 |
|
is just the same part, but description, charge number, reqdate and qty can all
|
425 |
|
be part of the identity of a position for finding shipped matches.
|
426 |
|
|
427 |
|
=item *
|
428 |
|
|
429 |
269 |
Certain delivery orders might not be eligible for qty calculations if delivery
|
430 |
270 |
orders are used for other purposes.
|
431 |
271 |
|
... | ... | |
463 |
303 |
Boolean. If set, delivery orders must be stocked out to be considered
|
464 |
304 |
delivered. The default is a client setting.
|
465 |
305 |
|
466 |
|
=item * C<fill_up>
|
467 |
|
|
468 |
|
Boolean. If set, unlinked delivery order items will be used to fill up
|
469 |
|
undelivered order items. Not needed in newer installations. The default is a
|
470 |
|
client setting.
|
471 |
|
|
472 |
|
=item * C<item_identity_fields ARRAY>
|
473 |
|
|
474 |
|
If set, the fields are used to compute the identity of matching positions. The
|
475 |
|
default is a client setting. Possible values include:
|
476 |
|
|
477 |
|
=over 4
|
478 |
|
|
479 |
|
=item * C<parts_id>
|
480 |
|
|
481 |
|
=item * C<description>
|
482 |
|
|
483 |
|
=item * C<reqdate>
|
484 |
|
|
485 |
|
=item * C<serialnumber>
|
486 |
|
|
487 |
|
=back
|
488 |
306 |
|
489 |
307 |
=item * C<keep_matches>
|
490 |
308 |
|
S/H/ShippedQty Berechnung nur über verlinkte Positionen