Revision 286dc87c
Von Jan Büren vor etwa 10 Jahren hinzugefügt
| SL/DO.pm | ||
|---|---|---|
| 240 | 240 |
|
| 241 | 241 |
my $ml = ($form->{type} eq 'sales_delivery_order') ? 1 : -1;
|
| 242 | 242 |
|
| 243 |
if ($form->{id}) {
|
|
| 244 |
|
|
| 245 |
$query = qq|DELETE FROM delivery_order_items_stock WHERE delivery_order_item_id IN (SELECT id FROM delivery_order_items WHERE delivery_order_id = ?)|; |
|
| 246 |
do_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 243 |
my (@processed_doi, @processed_dois); |
|
| 247 | 244 |
|
| 248 |
$query = qq|DELETE FROM delivery_order_items WHERE delivery_order_id = ?|; |
|
| 249 |
do_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 245 |
if ($form->{id}) {
|
|
| 250 | 246 |
|
| 247 |
# only delete shipto complete |
|
| 251 | 248 |
$query = qq|DELETE FROM shipto WHERE trans_id = ? AND module = 'DO'|; |
| 252 | 249 |
do_query($form, $dbh, $query, conv_i($form->{id}));
|
| 253 | 250 |
|
| ... | ... | |
| 275 | 272 |
$query = qq|SELECT id, unit FROM parts WHERE id IN (| . join(', ', map { '?' } @part_ids) . qq|)|;
|
| 276 | 273 |
%part_unit_map = selectall_as_map($form, $dbh, $query, 'id', 'unit', @part_ids); |
| 277 | 274 |
} |
| 278 |
|
|
| 279 |
my $q_item_id = qq|SELECT nextval('delivery_order_items_id')|;
|
|
| 280 |
my $h_item_id = prepare_query($form, $dbh, $q_item_id); |
|
| 281 |
|
|
| 282 |
my $q_item = |
|
| 283 |
qq|INSERT INTO delivery_order_items ( |
|
| 284 |
id, delivery_order_id, parts_id, description, longdescription, qty, base_qty, |
|
| 285 |
sellprice, discount, unit, reqdate, project_id, serialnumber, |
|
| 286 |
ordnumber, transdate, cusordnumber, |
|
| 287 |
lastcost, price_factor_id, price_factor, marge_price_factor, pricegroup_id, |
|
| 288 |
active_price_source, active_discount_source) |
|
| 289 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, |
|
| 290 |
(SELECT factor FROM price_factors WHERE id = ?), ?, ?, ?, ?)|; |
|
| 275 |
my $q_item = <<SQL; |
|
| 276 |
UPDATE delivery_order_items SET |
|
| 277 |
delivery_order_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?, |
|
| 278 |
sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, |
|
| 279 |
ordnumber = ?, transdate = ?, cusordnumber = ?, |
|
| 280 |
lastcost = ? , price_factor_id = ?, price_factor = (SELECT factor FROM price_factors where id = ?), |
|
| 281 |
marge_price_factor = ?, pricegroup_id = ?, active_price_source = ?, active_discount_source = ? |
|
| 282 |
WHERE id = ? |
|
| 283 |
SQL |
|
| 291 | 284 |
my $h_item = prepare_query($form, $dbh, $q_item); |
| 292 | 285 |
|
| 293 |
my $q_item_stock = |
|
| 294 |
qq|INSERT INTO delivery_order_items_stock (delivery_order_item_id, qty, unit, warehouse_id, bin_id, chargenumber, bestbefore) |
|
| 295 |
VALUES (?, ?, ?, ?, ?, ?, ?)|; |
|
| 286 |
my $q_item_stock = <<SQL; |
|
| 287 |
UPDATE delivery_order_items_stock SET |
|
| 288 |
delivery_order_item_id = ?, qty = ?, unit = ?, warehouse_id = ?, |
|
| 289 |
bin_id = ?, chargenumber = ?, bestbefore = ? |
|
| 290 |
WHERE id = ? |
|
| 291 |
SQL |
|
| 296 | 292 |
my $h_item_stock = prepare_query($form, $dbh, $q_item_stock); |
| 297 | 293 |
|
| 298 | 294 |
my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in';
|
| ... | ... | |
| 300 | 296 |
for my $i (1 .. $form->{rowcount}) {
|
| 301 | 297 |
next if (!$form->{"id_$i"});
|
| 302 | 298 |
|
| 299 |
if (!$form->{"delivery_order_items_id_$i"}) {
|
|
| 300 |
# there is no persistent id, therefore create one with all necessary constraints |
|
| 301 |
my $q_item_id = qq|SELECT nextval('delivery_order_items_id')|;
|
|
| 302 |
my $h_item_id = prepare_query($form, $dbh, $q_item_id); |
|
| 303 |
do_statement($form, $h_item_id, $q_item_id); |
|
| 304 |
$form->{"delivery_order_items_id_$i"} = $h_item_id->fetchrow_array();
|
|
| 305 |
$query = qq|INSERT INTO delivery_order_items (id, delivery_order_id, parts_id) VALUES (?, ?, ?)|; |
|
| 306 |
do_query($form, $dbh, $query, conv_i($form->{"delivery_order_items_id_$i"}),
|
|
| 307 |
conv_i($form->{"id"}), conv_i($form->{"id_$i"}));
|
|
| 308 |
$h_item_id->finish(); |
|
| 309 |
} |
|
| 303 | 310 |
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
|
| 304 | 311 |
|
| 305 | 312 |
my $item_unit = $part_unit_map{$form->{"id_$i"}};
|
| ... | ... | |
| 320 | 327 |
|
| 321 | 328 |
$items_reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
|
| 322 | 329 |
|
| 323 |
do_statement($form, $h_item_id, $q_item_id); |
|
| 324 |
my ($item_id) = $h_item_id->fetchrow_array(); |
|
| 325 | 330 |
|
| 326 | 331 |
# Get pricegroup_id and save it. Unfortunately the interface |
| 327 | 332 |
# also uses ID "0" for signalling that none is selected, but "0" |
| ... | ... | |
| 331 | 336 |
$pricegroup_id = undef if !$pricegroup_id; |
| 332 | 337 |
|
| 333 | 338 |
# save detail record in delivery_order_items table |
| 334 |
@values = (conv_i($item_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
|
| 339 |
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
|
| 335 | 340 |
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
|
| 336 | 341 |
$form->{"qty_$i"}, $baseqty,
|
| 337 | 342 |
$form->{"sellprice_$i"}, $form->{"discount_$i"} / 100,
|
| ... | ... | |
| 343 | 348 |
conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
|
| 344 | 349 |
conv_i($form->{"marge_price_factor_$i"}),
|
| 345 | 350 |
$pricegroup_id, |
| 346 |
$form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"});
|
|
| 351 |
$form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
|
|
| 352 |
conv_i($form->{"delivery_order_items_id_$i"}));
|
|
| 347 | 353 |
do_statement($form, $h_item, $q_item, @values); |
| 354 |
push @processed_doi, $form->{"delivery_order_items_id_$i"}; # transaction safe?
|
|
| 348 | 355 |
|
| 349 | 356 |
my $stock_info = DO->unpack_stock_information('packed' => $form->{"stock_${in_out}_$i"});
|
| 350 | 357 |
|
| 351 | 358 |
foreach my $sinfo (@{ $stock_info }) {
|
| 352 |
@values = ($item_id, $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}),
|
|
| 353 |
conv_i($sinfo->{bin_id}), $sinfo->{chargenumber}, conv_date($sinfo->{bestbefore}));
|
|
| 359 |
# if we have stock_info, we have to check for persistents entries |
|
| 360 |
if (!$sinfo->{"delivery_order_items_stock_id"}) {
|
|
| 361 |
my $q_item_stock_id = qq|SELECT nextval('id')|;
|
|
| 362 |
my $h_item_stock_id = prepare_query($form, $dbh, $q_item_stock_id); |
|
| 363 |
do_statement($form, $h_item_stock_id, $q_item_stock_id); |
|
| 364 |
$sinfo->{"delivery_order_items_stock_id"} = $h_item_stock_id->fetchrow_array();
|
|
| 365 |
$query = qq|INSERT INTO delivery_order_items_stock (id, delivery_order_item_id, qty, unit, warehouse_id, bin_id) |
|
| 366 |
VALUES (?, ?, ?, ?, ?, ?)|; |
|
| 367 |
do_query($form, $dbh, $query, conv_i($sinfo->{"delivery_order_items_stock_id"}),
|
|
| 368 |
conv_i($form->{"delivery_order_items_id_$i"}), $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}),
|
|
| 369 |
conv_i($sinfo->{bin_id}));
|
|
| 370 |
$h_item_stock_id->finish(); |
|
| 371 |
} |
|
| 372 |
@values = ($form->{"delivery_order_items_id_$i"}, $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}),
|
|
| 373 |
conv_i($sinfo->{bin_id}), $sinfo->{chargenumber}, conv_date($sinfo->{bestbefore}),
|
|
| 374 |
conv_i($sinfo->{"delivery_order_items_stock_id"}));
|
|
| 354 | 375 |
do_statement($form, $h_item_stock, $q_item_stock, @values); |
| 355 | 376 |
} |
| 356 | 377 |
|
| 357 | 378 |
CVar->save_custom_variables(module => 'IC', |
| 358 | 379 |
sub_module => 'delivery_order_items', |
| 359 |
trans_id => $item_id,
|
|
| 380 |
trans_id => $form->{"delivery_order_items_id_$i"},
|
|
| 360 | 381 |
configs => $ic_cvar_configs, |
| 361 | 382 |
variables => $form, |
| 362 | 383 |
name_prefix => 'ic_', |
| ... | ... | |
| 364 | 385 |
dbh => $dbh); |
| 365 | 386 |
} |
| 366 | 387 |
|
| 367 |
$h_item_id->finish(); |
|
| 388 |
# search for orphaned doi |
|
| 389 |
$query = sprintf 'SELECT id FROM delivery_order_items WHERE delivery_order_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_doi;
|
|
| 390 |
@values = (conv_i($form->{id}), map { conv_i($_) } @processed_doi);
|
|
| 391 |
my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
|
|
| 392 |
if (scalar @orphaned_ids) {
|
|
| 393 |
# clean up delivery_order_items |
|
| 394 |
$query = sprintf 'DELETE FROM delivery_order_items WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
|
|
| 395 |
do_query($form, $dbh, $query, @orphaned_ids); |
|
| 396 |
} |
|
| 368 | 397 |
$h_item->finish(); |
| 369 | 398 |
$h_item_stock->finish(); |
| 370 | 399 |
|
| ... | ... | |
| 717 | 746 |
my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in';
|
| 718 | 747 |
|
| 719 | 748 |
$query = |
| 720 |
qq|SELECT qty, unit, bin_id, warehouse_id, chargenumber, bestbefore |
|
| 749 |
qq|SELECT id as delivery_order_items_stock_id, qty, unit, bin_id, |
|
| 750 |
warehouse_id, chargenumber, bestbefore |
|
| 721 | 751 |
FROM delivery_order_items_stock |
| 722 | 752 |
WHERE delivery_order_item_id = ?|; |
| 723 | 753 |
my $sth = prepare_query($form, $dbh, $query); |
Auch abrufbar als: Unified diff
Persistente delivery_order_items_(stocK)_id
id in tabelle delivery_order_items und
id in tabelle delivery_order_items_stock persistent gemacht.
Testszenarien:
delivery_order_items:
Einkauf / Verkauf-LS -> als neu speichern. => i.O.
Einlagern => i.O.
Mittlere Position löschen => i.O.
delivery_order_items_stock:
Mittlere Position löschen => n.i.O.
als neu Speichern => i.O.
Einlagern => i.O. (Lieferschein kann standardmässig nicht mehr verändert werden)