Revision d814a525
Von Martin Helmling mh@waldpark.octosoft.eu vor mehr als 8 Jahren hinzugefügt
| SL/DO.pm | ||
|---|---|---|
| 536 | 536 |
|
| 537 | 537 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
| 538 | 538 |
|
| 539 |
my @links = RecordLinks->get_links('dbh' => $dbh,
|
|
| 540 |
'from_table' => 'oe', |
|
| 541 |
'to_table' => 'delivery_orders', |
|
| 542 |
'to_id' => $params{do_id});
|
|
| 539 |
my %ship = $self->get_shipped_qty('do_id' => $form->{id}, 'delivered' => 1);
|
|
| 543 | 540 |
|
| 544 |
my $oe_id = @links ? $links[0]->{from_id} : undef;
|
|
| 545 |
|
|
| 546 |
return $main::lxdebug->leave_sub() if (!$oe_id); |
|
| 547 |
|
|
| 548 |
my $all_units = AM->retrieve_all_units(); |
|
| 549 |
|
|
| 550 |
my $query = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit |
|
| 551 |
FROM orderitems oi |
|
| 552 |
LEFT JOIN parts p ON (oi.parts_id = p.id) |
|
| 553 |
WHERE (oi.trans_id = ?)|; |
|
| 554 |
my $sth = prepare_execute_query($form, $dbh, $query, $oe_id); |
|
| 555 |
|
|
| 556 |
my %shipped = $self->get_shipped_qty('type' => $params{type},
|
|
| 557 |
'oe_id' => $oe_id,); |
|
| 558 |
my %ordered = (); |
|
| 559 |
|
|
| 560 |
while (my $ref = $sth->fetchrow_hashref()) {
|
|
| 561 |
$ref->{baseqty} = $ref->{qty} * $all_units->{$ref->{unit}}->{factor} / $all_units->{$ref->{partunit}}->{factor};
|
|
| 562 |
|
|
| 563 |
if ($ordered{$ref->{parts_id}}) {
|
|
| 564 |
$ordered{$ref->{parts_id}}->{baseqty} += $ref->{baseqty};
|
|
| 565 |
} else {
|
|
| 566 |
$ordered{$ref->{parts_id}} = $ref;
|
|
| 567 |
} |
|
| 568 |
} |
|
| 569 |
|
|
| 570 |
$sth->finish(); |
|
| 571 |
|
|
| 572 |
map { $_->{baseqty} = $_->{qty} * $all_units->{$_->{unit}}->{factor} / $all_units->{$_->{partunit}}->{factor} } values %shipped;
|
|
| 573 |
|
|
| 574 |
my $delivered = 1; |
|
| 575 |
foreach my $part (values %ordered) {
|
|
| 576 |
if (!$shipped{$part->{parts_id}} || ($shipped{$part->{parts_id}}->{baseqty} < $part->{baseqty})) {
|
|
| 577 |
$delivered = 0; |
|
| 578 |
last; |
|
| 579 |
} |
|
| 580 |
} |
|
| 581 |
|
|
| 582 |
if ($delivered) {
|
|
| 583 |
$query = qq|UPDATE oe |
|
| 584 |
SET delivered = TRUE |
|
| 585 |
WHERE id = ?|; |
|
| 586 |
do_query($form, $dbh, $query, $oe_id); |
|
| 587 |
$dbh->commit() if (!$params{dbh});
|
|
| 541 |
foreach my $oe_id (keys %ship) {
|
|
| 542 |
do_query($form, $dbh,"UPDATE oe SET delivered = ".($ship{$oe_id}->{delivered}?"TRUE":"FALSE")." WHERE id = ?", $oe_id);
|
|
| 588 | 543 |
} |
| 544 |
$dbh->commit() if (!$params{dbh});
|
|
| 589 | 545 |
|
| 590 | 546 |
$main::lxdebug->leave_sub(); |
| 591 | 547 |
} |
| ... | ... | |
| 1244 | 1200 |
$main::lxdebug->leave_sub(); |
| 1245 | 1201 |
} |
| 1246 | 1202 |
|
| 1203 |
|
|
| 1247 | 1204 |
sub get_shipped_qty {
|
| 1248 | 1205 |
$main::lxdebug->enter_sub(); |
| 1249 | 1206 |
|
| 1207 |
# Drei Fälle: |
|
| 1208 |
# $params{oe_id} : Alle Lieferscheine zu diesem Auftrag durchsuchen und pro Auftragsposition die Mengen zurückgeben
|
|
| 1209 |
# Wird zur Darstellung der gelieferten Mengen im Auftrag benötigt |
|
| 1210 |
# $params{do_id} : Alle Aufträge zu diesem Lieferschein durchsuchen und pro Lieferscheinposition die Mengen zurückgeben
|
|
| 1211 |
# Wird für LaTeX benötigt um im Lieferschein pro Position die Mengen auszugeben |
|
| 1212 |
# $params{delivered}: Alle Aufträge zum Lieferschein $params{do_id} prüfen ob sie vollständiger ausgeliefert sind
|
|
| 1213 |
# Wird für das Setzen des 'delivered' Flag in der Datenbank beim "save" des Lieferscheins benötigt |
|
| 1214 |
|
|
| 1250 | 1215 |
my $self = shift; |
| 1251 | 1216 |
my %params = @_; |
| 1252 | 1217 |
|
| 1253 |
Common::check_params(\%params, qw(type oe_id)); |
|
| 1254 |
|
|
| 1255 | 1218 |
my $myconfig = \%main::myconfig; |
| 1256 | 1219 |
my $form = $main::form; |
| 1220 |
my $dbh = $form->get_standard_dbh($myconfig); |
|
| 1221 |
my %ship = (); |
|
| 1257 | 1222 |
|
| 1258 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 1223 |
my @oe_ids;
|
|
| 1259 | 1224 |
|
| 1260 |
my @links = RecordLinks->get_links('dbh' => $dbh,
|
|
| 1261 |
'from_table' => 'oe', |
|
| 1262 |
'from_id' => $params{oe_id},
|
|
| 1263 |
'to_table' => 'delivery_orders'); |
|
| 1264 |
my @values = map { $_->{to_id} } @links;
|
|
| 1225 |
if ( $params{oe_id} ) {
|
|
| 1226 |
push @oe_ids, $params{oe_id};
|
|
| 1227 |
} |
|
| 1228 |
elsif ($params{do_id}) {
|
|
| 1229 |
my @links = RecordLinks->get_links( 'dbh' => $dbh, |
|
| 1230 |
'from_table' => 'oe', |
|
| 1231 |
'to_table' => 'delivery_orders', |
|
| 1232 |
'to_id' => $params{do_id});
|
|
| 1265 | 1233 |
|
| 1266 |
if (!scalar @values) {
|
|
| 1267 |
$main::lxdebug->leave_sub(); |
|
| 1268 |
return (); |
|
| 1234 |
@oe_ids = map { $_->{from_id} } @links;
|
|
| 1269 | 1235 |
} |
| 1270 | 1236 |
|
| 1271 |
my $query = |
|
| 1272 |
qq|SELECT doi.parts_id, doi.qty, doi.unit, p.unit AS partunit |
|
| 1273 |
FROM delivery_order_items doi |
|
| 1274 |
LEFT JOIN delivery_orders o ON (doi.delivery_order_id = o.id) |
|
| 1275 |
LEFT JOIN parts p ON (doi.parts_id = p.id) |
|
| 1276 |
WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|;
|
|
| 1237 |
if (scalar (@oe_ids) > 0 ) {
|
|
| 1238 |
|
|
| 1239 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"oeid=".$params{oe_id}." doid=".$params{do_id});
|
|
| 1240 |
my $all_units = AM->retrieve_all_units(); |
|
| 1241 |
my $query = qq|SELECT oi.id, oi.position, oi.parts_id, oi.qty, oi.unit, oi.trans_id, |
|
| 1242 |
p.unit AS partunit FROM orderitems oi |
|
| 1243 |
LEFT JOIN parts p ON (oi.parts_id = p.id) |
|
| 1244 |
WHERE trans_id IN (| . |
|
| 1245 |
join(', ', ('?') x scalar @oe_ids) . qq|) ORDER BY position ASC|;
|
|
| 1246 |
|
|
| 1247 |
my $orderitems = selectall_hashref_query($form, $dbh, $query, @oe_ids); |
|
| 1248 |
foreach my $oe_entry (@{ $orderitems }) {
|
|
| 1249 |
$oe_entry->{qty} *= AM->convert_unit($oe_entry->{unit}, $oe_entry->{partunit}, $all_units);
|
|
| 1250 |
$oe_entry->{qty_notdelivered} = $oe_entry->{qty};
|
|
| 1251 |
|
|
| 1252 |
# Bei oe Modus auf jeden Fall einen Record anlegen |
|
| 1253 |
if ( $params{oe_id} ) {
|
|
| 1254 |
$ship{$oe_entry->{position}} = {
|
|
| 1255 |
'qty_ordered' => $oe_entry->{qty} ,
|
|
| 1256 |
'qty_notdelivered' => $oe_entry->{qty}
|
|
| 1257 |
}; |
|
| 1258 |
} |
|
| 1259 |
} |
|
| 1277 | 1260 |
|
| 1278 |
my %ship = (); |
|
| 1279 |
my $entries = selectall_hashref_query($form, $dbh, $query, @values); |
|
| 1280 |
my $all_units = AM->retrieve_all_units(); |
|
| 1261 |
my @dolinks = RecordLinks->get_links('dbh' => $dbh,
|
|
| 1262 |
'from_table' => 'oe', |
|
| 1263 |
'to_table' => 'delivery_orders', |
|
| 1264 |
'from_id' => @oe_ids); |
|
| 1281 | 1265 |
|
| 1282 |
foreach my $entry (@{ $entries }) {
|
|
| 1283 |
$entry->{qty} *= AM->convert_unit($entry->{unit}, $entry->{partunit}, $all_units);
|
|
| 1266 |
my @do_ids = map { $_->{to_id} } @dolinks ;
|
|
| 1267 |
if (scalar (@do_ids) == 0) {
|
|
| 1268 |
$main::lxdebug->leave_sub(); |
|
| 1269 |
return %ship; |
|
| 1270 |
} |
|
| 1284 | 1271 |
|
| 1285 |
if (!$ship{$entry->{parts_id}}) {
|
|
| 1286 |
$ship{$entry->{parts_id}} = $entry;
|
|
| 1287 |
} else {
|
|
| 1288 |
$ship{$entry->{parts_id}}->{qty} += $entry->{qty};
|
|
| 1289 |
} |
|
| 1290 |
} |
|
| 1272 |
my %oeitems_by_id = map { $_->{id} => $_ } @{ $orderitems };
|
|
| 1273 |
|
|
| 1274 |
|
|
| 1275 |
$query = qq|SELECT doi.parts_id, doi.id, doi.qty, doi.unit, doi.position, |
|
| 1276 |
doi.delivery_order_id, COALESCE(rlitem.from_id,0) as from_id, |
|
| 1277 |
p.unit AS partunit |
|
| 1278 |
FROM delivery_order_items doi |
|
| 1279 |
LEFT JOIN parts p ON (doi.parts_id = p.id) |
|
| 1280 |
LEFT JOIN record_links rlitem |
|
| 1281 |
ON (rlitem.to_id = doi.id AND rlitem.to_table='delivery_order_items') |
|
| 1282 |
WHERE doi.delivery_order_id IN (| . join(', ', ('?') x scalar @do_ids) . qq|)|;
|
|
| 1283 |
|
|
| 1284 |
my $deliveryorderitems = selectall_hashref_query($form, $dbh, $query, @do_ids); |
|
| 1285 |
|
|
| 1286 |
# erst mal qty der links bearbeiten |
|
| 1287 |
foreach my $do_entry (@{ $deliveryorderitems }) {
|
|
| 1288 |
$do_entry->{qty} *= AM->convert_unit($do_entry->{unit}, $do_entry->{partunit}, $all_units);
|
|
| 1289 |
if ($do_entry->{from_id} > 0 ) {
|
|
| 1290 |
# record link zwischen items vorhanden, kann auch von anderem Auftrag sein |
|
| 1291 |
my $oe_entry = $oeitems_by_id{$do_entry->{from_id}};
|
|
| 1292 |
if ( $oe_entry ) {
|
|
| 1293 |
$oe_entry->{qty_notdelivered} -= $do_entry->{qty};
|
|
| 1294 |
# derzeit nur ein link pro do_item |
|
| 1295 |
$do_entry->{oe_entry} = $oe_entry;
|
|
| 1296 |
} |
|
| 1297 |
} else {
|
|
| 1298 |
$main::lxdebug->message(LXDebug->DEBUG2(),"no entry for=".$do_entry->{id}." part=".$do_entry->{parts_id});
|
|
| 1299 |
} |
|
| 1300 |
} |
|
| 1301 |
# nun den rest ohne links bearbeiten |
|
| 1302 |
foreach my $do_entry (@{ $deliveryorderitems }) {
|
|
| 1303 |
next if $do_entry->{from_id} > 0;
|
|
| 1304 |
next if $do_entry->{qty} == 0;
|
|
| 1305 |
|
|
| 1306 |
foreach my $oe_entry (@{ $orderitems }) {
|
|
| 1307 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"do oe_entry ".$oe_entry." id=".$oe_entry->{id}." not del=".$oe_entry->{qty_notdelivered});
|
|
| 1308 |
next if $oe_entry->{qty_notdelivered} == 0;
|
|
| 1309 |
if ( $do_entry->{parts_id} == $oe_entry->{parts_id} ) {
|
|
| 1310 |
# zu viele geliefert auf andere position ? |
|
| 1311 |
if ( $oe_entry->{qty_notdelivered} < 0 ) {
|
|
| 1312 |
$do_entry->{qty} += - $oe_entry->{qty_notdelivered};
|
|
| 1313 |
$oe_entry->{qty_notdelivered} = 0;
|
|
| 1314 |
} else {
|
|
| 1315 |
if ( $do_entry->{qty} < $oe_entry->{qty_notdelivered} ) {
|
|
| 1316 |
$oe_entry->{qty_notdelivered} -= $do_entry->{qty};
|
|
| 1317 |
$do_entry->{qty} = 0;
|
|
| 1318 |
} else {
|
|
| 1319 |
$do_entry->{qty} -= $oe_entry->{qty_notdelivered};
|
|
| 1320 |
$oe_entry->{qty_notdelivered} = 0;
|
|
| 1321 |
} |
|
| 1322 |
# derzeit nur ein link pro do_item |
|
| 1323 |
$do_entry->{oe_entry} = $oe_entry if !$do_entry->{oe_entry};
|
|
| 1324 |
} |
|
| 1325 |
} |
|
| 1326 |
last if $do_entry->{qty} <= 0;
|
|
| 1327 |
} |
|
| 1291 | 1328 |
|
| 1329 |
} |
|
| 1330 |
if ( $params{oe_id} ) {
|
|
| 1331 |
map { $ship{$_->{position}}->{qty_notdelivered} = $_->{qty_notdelivered}; } @{ $orderitems };
|
|
| 1332 |
} |
|
| 1333 |
elsif ($params{do_id} && $params{delivered}) {
|
|
| 1334 |
map {
|
|
| 1335 |
if ( !$ship{$_->{trans_id}} ) {
|
|
| 1336 |
$ship{$_->{trans_id}} = { 'delivered' => 1 };
|
|
| 1337 |
} |
|
| 1338 |
$ship{$_->{trans_id}}->{delivered} = 0 if $_->{qty_notdelivered} > 0;
|
|
| 1339 |
} @{ $orderitems };
|
|
| 1340 |
} |
|
| 1341 |
elsif ($params{do_id}) {
|
|
| 1342 |
foreach my $do_entry (@{ $deliveryorderitems }) {
|
|
| 1343 |
next if $params{do_id} != $do_entry->{delivery_order_id};
|
|
| 1344 |
my $position = $do_entry->{position};
|
|
| 1345 |
if ( $position > 0 && $do_entry->{oe_entry}) {
|
|
| 1346 |
if ( !$ship{$position} ) {
|
|
| 1347 |
$ship{$position} = {
|
|
| 1348 |
'qty_ordered' => $do_entry->{oe_entry}->{qty} ,
|
|
| 1349 |
'qty_notdelivered' => $do_entry->{oe_entry}->{qty_notdelivered}
|
|
| 1350 |
}; |
|
| 1351 |
} |
|
| 1352 |
else {
|
|
| 1353 |
$ship{$position}->{qty_ordered} += $do_entry->{oe_entry}->{qty};
|
|
| 1354 |
$ship{$position}->{qty_notdelivered} += $do_entry->{oe_entry}->{qty_notdelivered};
|
|
| 1355 |
} |
|
| 1356 |
} |
|
| 1357 |
} |
|
| 1358 |
} |
|
| 1359 |
} |
|
| 1292 | 1360 |
$main::lxdebug->leave_sub(); |
| 1293 |
|
|
| 1294 | 1361 |
return %ship; |
| 1295 | 1362 |
} |
| 1296 | 1363 |
|
Auch abrufbar als: Unified diff
Gelieferte Mengen über Recordlinks ermitteln
Die gelieferte Menge pro Position wird über die Recordlinks der Items
zwischen Auftrag und Lieferschein(en) ermittelt.
So werden auch gleiche Artikel auf unterschiedlichen Positionen getrennt behandelt.
Ebenso ob ein Auftrag 'delivered' ist, d.h. ob alle Mengen vollständig in Lieferscheinen erfasst sind.
Für nachträglich hinzugefügte Lieferscheine oder Lieferscheine ohne Item-Recodlinks wird ein
Fallback-Verfahren durchgeführt, das beginnend von der ersten Auftragsposition
versucht die Artikel in den Lieferscheinen zuzuordnen.