Revision 1f609b0a
Von Tamino Steinert vor 11 Monaten hinzugefügt
| scripts/import_variant_csv.pl | ||
|---|---|---|
| 12 | 12 |
} |
| 13 | 13 |
|
| 14 | 14 |
use Data::Dumper; |
| 15 |
use List::Util qw(first); |
|
| 15 |
use List::Util qw(first sum0);
|
|
| 16 | 16 |
use List::MoreUtils qw(any); |
| 17 | 17 |
use File::Basename; |
| 18 | 18 |
use Encode; |
| ... | ... | |
| 45 | 45 |
$opt_user, $opt_client, |
| 46 | 46 |
$opt_warengruppen_csv_file, $opt_farben_folder, |
| 47 | 47 |
$opt_part_csv_file, $opt_groessen_staffeln_csv_file, |
| 48 |
$opt_test_run, |
|
| 48 |
$opt_test_run, $opt_force
|
|
| 49 | 49 |
); |
| 50 | 50 |
our (%myconfig, $form, $user, $employee, $auth, $locale); |
| 51 | 51 |
|
| ... | ... | |
| 58 | 58 |
$opt_part_csv_file = "kuw/Export_bearbeitet.csv"; |
| 59 | 59 |
|
| 60 | 60 |
$opt_test_run = 1; |
| 61 |
$opt_force = 0; # Writes all valid entries to DB |
|
| 62 |
|
|
| 63 |
if ($opt_test_run && $opt_force) {
|
|
| 64 |
die "Can't do test run and force the write to the db.\n". |
|
| 65 |
"Change \$opt_test_run or \$opt_force." |
|
| 66 |
} |
|
| 61 | 67 |
|
| 62 | 68 |
$locale = Locale->new; |
| 63 | 69 |
$form = Form->new; |
| ... | ... | |
| 275 | 281 |
my $vendor_name = basename($farb_csv_file); |
| 276 | 282 |
$vendor_name =~ s/\.csv//; |
| 277 | 283 |
|
| 278 |
my $variant_property = SL::DB::VariantProperty->new(
|
|
| 284 |
my %variant_property_att = (
|
|
| 279 | 285 |
name => "Farbliste $vendor_name", |
| 280 | 286 |
unique_name => "Farbliste $vendor_name", |
| 281 | 287 |
abbreviation => "fa", |
| 282 |
)->save; |
|
| 288 |
); |
|
| 289 |
my $variant_property = SL::DB::Manager::VariantProperty->find_by( |
|
| 290 |
%variant_property_att |
|
| 291 |
); |
|
| 292 |
$variant_property ||= SL::DB::VariantProperty->new( |
|
| 293 |
%variant_property_att |
|
| 294 |
); |
|
| 295 |
$variant_property->save; |
|
| 283 | 296 |
|
| 284 | 297 |
my $pos = 1; |
| 285 | 298 |
SL::DB::VariantPropertyValue->new( |
| 286 | 299 |
variant_property => $variant_property, |
| 287 | 300 |
value => $_->{Joined},
|
| 288 | 301 |
abbreviation => $_->{Joined},
|
| 302 |
)->update_attributes( |
|
| 289 | 303 |
sortkey => $pos++, |
| 290 | 304 |
)->save for @$farb_hrefs; |
| 291 | 305 |
} |
| ... | ... | |
| 293 | 307 |
# create groessen staffeln |
| 294 | 308 |
foreach my $groessen_staffel_row (@$groessen_staffel_hrefs) {
|
| 295 | 309 |
my $name = delete $groessen_staffel_row->{BEZEICHNUNG};
|
| 296 |
my $variant_property = SL::DB::VariantProperty->new(
|
|
| 310 |
my %variant_property_att = (
|
|
| 297 | 311 |
name => $name, |
| 298 | 312 |
unique_name => $name, |
| 299 | 313 |
abbreviation => "gr", |
| 300 |
)->save; |
|
| 314 |
); |
|
| 315 |
my $variant_property = SL::DB::Manager::VariantProperty->find_by( |
|
| 316 |
%variant_property_att |
|
| 317 |
); |
|
| 318 |
$variant_property ||= SL::DB::VariantProperty->new( |
|
| 319 |
%variant_property_att |
|
| 320 |
); |
|
| 321 |
$variant_property->save; |
|
| 301 | 322 |
|
| 302 | 323 |
my $pos = 1; |
| 303 | 324 |
SL::DB::VariantPropertyValue->new( |
| 304 | 325 |
variant_property => $variant_property, |
| 305 | 326 |
value => $_, |
| 306 | 327 |
abbreviation => $_, |
| 328 |
)->update_attributes( |
|
| 307 | 329 |
sortkey => $pos++, |
| 308 | 330 |
)->save for |
| 309 | 331 |
map {$groessen_staffel_row->{$_}}
|
| ... | ... | |
| 330 | 352 |
join(';', map {$partsgroup_row->{$_}} @hierachy_descrioptions);
|
| 331 | 353 |
next; |
| 332 | 354 |
} |
| 333 |
my $partsgroup = SL::DB::PartsGroup->new(
|
|
| 355 |
my %partsgroup_att = (
|
|
| 334 | 356 |
partsgroup => $name, |
| 335 | 357 |
sortkey => $number, |
| 336 | 358 |
description => "$number $name", |
| 337 | 359 |
parent_id => $last_hierachy_key ? $current_partsgroup_hierachy{$last_hierachy_key}->id : undef,
|
| 338 |
)->save; |
|
| 360 |
); |
|
| 361 |
my $partsgroup = SL::DB::Manager::PartsGroup->find_by( |
|
| 362 |
%partsgroup_att |
|
| 363 |
); |
|
| 364 |
$partsgroup ||= SL::DB::PartsGroup->new( |
|
| 365 |
%partsgroup_att |
|
| 366 |
); |
|
| 367 |
$partsgroup->save(); |
|
| 339 | 368 |
$current_partsgroup_hierachy{$hierachy_key} = $partsgroup;
|
| 340 | 369 |
} |
| 341 | 370 |
$last_hierachy_key = $hierachy_key; |
| ... | ... | |
| 361 | 390 |
foreach my $vendor_kurz_name (keys %parent_variants_to_variants) {
|
| 362 | 391 |
foreach my $partnumber (keys %{$parent_variants_to_variants{$vendor_kurz_name}}) {
|
| 363 | 392 |
my $count_errors_at_start = scalar @errors; |
| 364 |
# TODO: logic for |
|
| 365 |
# bestand anpasen |
|
| 366 |
# stammartikel da neue variante |
|
| 367 |
# alles neu |
|
| 368 | 393 |
my $grouped_variant_values = $parent_variants_to_variants{$vendor_kurz_name}->{$partnumber};
|
| 394 |
# check for variants the same variant_values |
|
| 395 |
my %variant_values_to_variant; |
|
| 396 |
foreach my $variant_values (@$grouped_variant_values) {
|
|
| 397 |
my $key = |
|
| 398 |
join ';', |
|
| 399 |
map {$variant_values->{$_}}
|
|
| 400 |
sort |
|
| 401 |
grep { $_ =~ m/^variant/ }
|
|
| 402 |
keys %$variant_values; |
|
| 403 |
|
|
| 404 |
push @{$variant_values_to_variant{$key}}, $variant_values
|
|
| 405 |
} |
|
| 369 | 406 |
|
| 370 | 407 |
#get data for parent_variant |
| 371 | 408 |
my $first_part = $grouped_variant_values->[0]; |
| ... | ... | |
| 383 | 420 |
my $warehouse = $warehouse_description_to_warehouse{lc($warehouse_description)} or
|
| 384 | 421 |
push @errors, "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description' in row " . $first_part->{csv_row};
|
| 385 | 422 |
next if $count_errors_at_start != scalar @errors; |
| 386 |
my $parent_variant = SL::DB::Part->new_parent_variant( |
|
| 387 |
partnumber => $vendor_number . '-' . $makemodel_model, |
|
| 423 |
my %parent_variant_fix_att = ( |
|
| 424 |
partnumber => $vendor_number . '-' . $makemodel_model, |
|
| 425 |
variant_type => 'parent_variant', |
|
| 426 |
part_type => 'part', |
|
| 427 |
); |
|
| 428 |
my $parent_variant = SL::DB::Manager::Part->find_by( |
|
| 429 |
%parent_variant_fix_att |
|
| 430 |
); |
|
| 431 |
$parent_variant ||= SL::DB::Part->new( |
|
| 432 |
%parent_variant_fix_att |
|
| 433 |
); |
|
| 434 |
$parent_variant->update_attributes( |
|
| 388 | 435 |
description => $description, |
| 389 | 436 |
sellprice => $best_sellprice, |
| 390 | 437 |
partsgroup => $partsgroup, |
| 391 | 438 |
warehouse => $warehouse, |
| 392 | 439 |
bin => $warehouse->bins->[0], |
| 393 |
part_type => 'part', |
|
| 394 | 440 |
unit => 'Stck', |
| 395 | 441 |
); |
| 396 | 442 |
|
| 397 |
# add makemodel |
|
| 398 |
my $makemodel = SL::DB::MakeModel->new( |
|
| 399 |
make => $vendor->id, |
|
| 400 |
model => $makemodel_model, |
|
| 401 |
part_description => $description, |
|
| 402 |
); |
|
| 403 |
$parent_variant->add_makemodels($makemodel); |
|
| 443 |
unless (scalar $parent_variant->makemodels) {
|
|
| 444 |
# add makemodel |
|
| 445 |
my $makemodel = SL::DB::MakeModel->new( |
|
| 446 |
make => $vendor->id, |
|
| 447 |
model => $makemodel_model, |
|
| 448 |
part_description => $description, |
|
| 449 |
); |
|
| 450 |
$parent_variant->add_makemodels($makemodel); |
|
| 451 |
} |
|
| 404 | 452 |
|
| 405 | 453 |
# get active variant_properties |
| 406 | 454 |
my %group_variant_property_vales; |
| ... | ... | |
| 471 | 519 |
$property_name_to_variant_property{$property_name} = $best_match->{property};
|
| 472 | 520 |
} |
| 473 | 521 |
my @variant_properties = values %property_name_to_variant_property; |
| 474 |
$parent_variant->variant_properties(@variant_properties); |
|
| 522 |
if (scalar @{$parent_variant->variant_properties}) {
|
|
| 523 |
my @current_variant_property_ids = sort map {$_->id} $parent_variant->variant_properties;
|
|
| 524 |
my @new_variant_property_ids = sort map {$_->id} @variant_properties;
|
|
| 525 |
if ("@current_variant_property_ids" ne "@new_variant_property_ids") {
|
|
| 526 |
push @errors, "The variant properties changed for part '$makemodel_model $description' in row " . $first_part->{csv_row} . "\n" .
|
|
| 527 |
"Please change them manually before import.\n"; |
|
| 528 |
} |
|
| 529 |
} else {
|
|
| 530 |
$parent_variant->variant_properties(@variant_properties); |
|
| 531 |
} |
|
| 475 | 532 |
|
| 476 | 533 |
next if ($opt_test_run); |
| 477 | 534 |
|
| 478 | 535 |
next if $count_errors_at_start != scalar @errors; |
| 479 | 536 |
$parent_variant->save(); |
| 480 | 537 |
|
| 481 |
foreach my $variant_values (@$grouped_variant_values) {
|
|
| 538 |
foreach my $variant_values_list (values %variant_values_to_variant) {
|
|
| 539 |
my $variant_values = $variant_values_list->[0]; |
|
| 482 | 540 |
my @property_values = |
| 483 | 541 |
map {
|
| 484 | 542 |
my $value = $variant_values->{$_};
|
| ... | ... | |
| 493 | 551 |
|
| 494 | 552 |
my $variant = first {join(' ', sort map {$_->id} @property_values) eq join(' ', sort map {$_->id} $_->variant_property_values)}
|
| 495 | 553 |
$parent_variant->variants; |
| 554 |
my $variant_is_new = !$variant; |
|
| 496 | 555 |
$variant ||= $parent_variant->create_new_variant(\@property_values); |
| 497 | 556 |
|
| 498 |
my $warehouse_description = $variant_values->{warehouse_description};
|
|
| 499 |
my $warehouse = $warehouse_description_to_warehouse{lc($warehouse_description)};
|
|
| 500 |
unless ($warehouse) {
|
|
| 501 |
push @errors, "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description' in row " . $variant_values->{csv_row};
|
|
| 557 |
my @stocks; |
|
| 558 |
foreach my $variant_values_with_stock_info (@$variant_values_list) {
|
|
| 559 |
my $warehouse_description = $variant_values_with_stock_info->{warehouse_description};
|
|
| 560 |
my $warehouse = $warehouse_description_to_warehouse{lc($warehouse_description)};
|
|
| 561 |
unless ($warehouse) {
|
|
| 562 |
push @errors, "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description' in row " . $variant_values_with_stock_info->{csv_row};
|
|
| 563 |
next; |
|
| 564 |
} |
|
| 565 |
push @stocks, {
|
|
| 566 |
part_qty => $variant_values_with_stock_info->{part_qty} || 0,
|
|
| 567 |
warehouse => $warehouse, |
|
| 568 |
}; |
|
| 569 |
} |
|
| 570 |
my $main_warehouse = |
|
| 571 |
first {$_}
|
|
| 572 |
map {$_->{warehouse}}
|
|
| 573 |
sort {$b->{part_qty} <=> $a->{part_qty}} # descending
|
|
| 574 |
@stocks; |
|
| 575 |
|
|
| 576 |
unless ($main_warehouse) {
|
|
| 577 |
push @errors, "Could not find warehouse for part '$makemodel_model $description'"; |
|
| 502 | 578 |
next; |
| 503 | 579 |
} |
| 504 | 580 |
|
| ... | ... | |
| 508 | 584 |
ean => $variant_values->{ean},
|
| 509 | 585 |
description => $variant_values->{description},
|
| 510 | 586 |
sellprice => $sellprice, |
| 511 |
warehouse => $warehouse, |
|
| 512 |
bin => $warehouse->bins->[0], |
|
| 587 |
warehouse => $main_warehouse,
|
|
| 588 |
bin => $main_warehouse->bins->[0],
|
|
| 513 | 589 |
); |
| 514 | 590 |
|
| 515 | 591 |
# set stock |
| 516 |
my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
|
|
| 517 |
SL::DB::Inventory->new( |
|
| 518 |
part => $variant, |
|
| 519 |
trans_id => $trans_id, |
|
| 520 |
trans_type => $transfer_type, |
|
| 521 |
shippingdate => 'now()', |
|
| 522 |
comment => 'initialer Bestand', |
|
| 523 |
warehouse => $variant->warehouse, |
|
| 524 |
bin => $variant->bin, |
|
| 525 |
qty => $variant_values->{part_qty},
|
|
| 526 |
employee => $employee, |
|
| 527 |
)->save; |
|
| 592 |
if ($variant_is_new) {
|
|
| 593 |
foreach my $stock (@stocks) {
|
|
| 594 |
next unless $stock->{part_qty} > 0;
|
|
| 595 |
my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
|
|
| 596 |
SL::DB::Inventory->new( |
|
| 597 |
part => $variant, |
|
| 598 |
trans_id => $trans_id, |
|
| 599 |
trans_type => $transfer_type, |
|
| 600 |
shippingdate => 'now()', |
|
| 601 |
comment => 'initialer Bestand', |
|
| 602 |
warehouse => $stock->{warehouse},
|
|
| 603 |
bin => $stock->{warehouse}->bins->[0],
|
|
| 604 |
qty => $stock->{part_qty},
|
|
| 605 |
employee => $employee, |
|
| 606 |
)->save; |
|
| 607 |
} |
|
| 608 |
} else {
|
|
| 609 |
foreach my $stock (@stocks) {
|
|
| 610 |
my $current_part_qty = |
|
| 611 |
sum0 |
|
| 612 |
map {$_->qty}
|
|
| 613 |
@{SL::DB::Manager::Inventory->get_all(
|
|
| 614 |
part => $variant, |
|
| 615 |
warehouse => $stock->{warehouse},
|
|
| 616 |
bin => $stock->{warehouse}->bins->[0],
|
|
| 617 |
)}; |
|
| 618 |
next if $current_part_qty == $stock->{part_qty};
|
|
| 619 |
|
|
| 620 |
my $transfer_type_correction = SL::DB::Manager::TransferType->find_by( |
|
| 621 |
direction => $current_part_qty > $stock->{part_qty} ? 'out' : 'in',
|
|
| 622 |
description => 'correction', |
|
| 623 |
) or die "Could no find transfer_type correction"; |
|
| 624 |
my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
|
|
| 625 |
SL::DB::Inventory->new( |
|
| 626 |
part => $variant, |
|
| 627 |
trans_id => $trans_id, |
|
| 628 |
trans_type => $transfer_type_correction, |
|
| 629 |
shippingdate => 'now()', |
|
| 630 |
comment => 'Korrektur bei Import', |
|
| 631 |
warehouse => $stock->{warehouse},
|
|
| 632 |
bin => $stock->{warehouse}->bins->[0],
|
|
| 633 |
qty => $stock->{part_qty} - $current_part_qty,
|
|
| 634 |
employee => $employee, |
|
| 635 |
)->save; |
|
| 636 |
} |
|
| 637 |
} |
|
| 528 | 638 |
|
| 529 | 639 |
} |
| 530 | 640 |
} |
| 531 | 641 |
} |
| 532 | 642 |
if (scalar @errors) {
|
| 533 | 643 |
say join("\n", @errors);
|
| 534 |
die join("\n", @errors);
|
|
| 644 |
die join("\n", @errors) unless $opt_force;
|
|
| 535 | 645 |
} |
| 536 | 646 |
if ($opt_test_run) {
|
| 537 |
die "Test Durchlauf Erfolgereich: Keine Fehler in den Daten";
|
|
| 647 |
die "Test run successfull: no erros in the data.";
|
|
| 538 | 648 |
} |
| 539 | 649 |
}) or do {
|
| 540 | 650 |
if (SL::DB->client->error) {
|
Auch abrufbar als: Unified diff
kuw: CSV-Import-Script: Anpassung für wiederholte Ausführung