Revision 5f8fd730
Von Tamino Steinert vor etwa 1 Jahr hinzugefügt
| scripts/import_variant_csv.pl | ||
|---|---|---|
|
#!/usr/bin/perl
|
||
|
|
||
|
use strict;
|
||
|
use warnings;
|
||
|
use utf8;
|
||
|
|
||
|
BEGIN {
|
||
|
use FindBin;
|
||
|
|
||
|
unshift(@INC, $FindBin::Bin . '/../modules/override'); # Use our own versions of various modules (e.g. YAML).
|
||
|
push (@INC, $FindBin::Bin . '/..'); # '.' will be removed from @INC soon.
|
||
|
}
|
||
|
|
||
|
use Data::Dumper;
|
||
|
use List::Util qw(first);
|
||
|
|
||
|
use SL::DBUtils;
|
||
|
use SL::LXDebug;
|
||
|
use SL::LxOfficeConf;
|
||
|
use SL::Locale::String qw(t8);
|
||
|
|
||
|
SL::LxOfficeConf->read;
|
||
|
our $lxdebug = LXDebug->new();
|
||
|
|
||
|
use SL::Auth;
|
||
|
use SL::Form;
|
||
|
use SL::Locale;
|
||
|
|
||
|
use SL::Helper::Csv;
|
||
|
use SL::DB::Part;
|
||
|
use SL::DB::PartsGroup;
|
||
|
use SL::DB::VariantProperty;
|
||
|
use SL::DB::Warehouse;
|
||
|
use SL::DB::Inventory;
|
||
|
|
||
|
use feature "say";
|
||
|
|
||
|
chdir($FindBin::Bin . '/..');
|
||
|
|
||
|
my ($opt_user, $opt_client, $opt_part_csv_file);
|
||
|
our (%myconfig, $form, $user, $employee, $auth, $locale);
|
||
|
|
||
|
$opt_client = "variant";
|
||
|
$opt_user = "test_user";
|
||
|
|
||
|
# $opt_part_csv_file = "Artikel_Auswahl.csv";
|
||
|
$opt_part_csv_file = "Artikel_small.csv";
|
||
|
# $opt_part_csv_file = "Artikel_komplett.csv";
|
||
|
|
||
|
|
||
|
$locale = Locale->new;
|
||
|
$form = Form->new;
|
||
|
|
||
|
sub connect_auth {
|
||
|
return $auth if $auth;
|
||
|
|
||
|
$auth = SL::Auth->new;
|
||
|
if (!$auth->session_tables_present) {
|
||
|
$form->error("The session and user management tables are not present in the authentication database. Please use the administration web interface to create them.");
|
||
|
}
|
||
|
|
||
|
return $auth;
|
||
|
}
|
||
|
|
||
|
|
||
|
if ($opt_client && !connect_auth()->set_client($opt_client)) {
|
||
|
$form->error($form->format_string("The client '#1' does not exist.", $opt_client));
|
||
|
}
|
||
|
|
||
|
if ($opt_user) {
|
||
|
$form->error("Need a client, too.") if !$auth || !$auth->client;
|
||
|
|
||
|
%myconfig = connect_auth()->read_user(login => $opt_user);
|
||
|
|
||
|
if (!$myconfig{login}) {
|
||
|
$form->error($form->format_string("The user '#1' does not exist.", $opt_user));
|
||
|
}
|
||
|
|
||
|
$locale = Locale->new($myconfig{countrycode}, "all");
|
||
|
$user = User->new(login => $opt_user);
|
||
|
$employee = SL::DB::Manager::Employee->find_by(login => $opt_user);
|
||
|
}
|
||
|
|
||
|
# Mappings
|
||
|
my %parts_mapping = (
|
||
|
VK => 'sellprice',
|
||
|
EANNR => 'ean',
|
||
|
WAR_KURZBEZEICHNUNG => 'description',
|
||
|
WARENGRUPPENBEZEICHNUNG => 'warengruppe',
|
||
|
GROESSE => 'varianten_groesse',
|
||
|
LAENGE => 'varianten_laenge',
|
||
|
FARBNR => 'varianten_farbnummer',
|
||
|
INFO1 => 'varianten_farbname',
|
||
|
ARTIKELNR => 'makemodel_model',
|
||
|
LIEFERANT => 'vendor_name',
|
||
|
FIL_KURZBEZEICHNUNG => 'warehouse_description',
|
||
|
STUECK => 'part_qty',
|
||
|
);
|
||
|
|
||
|
# STA_ID => ?
|
||
|
# STD_ID => ?
|
||
|
# ARTIKELNR + LIEFERANT => Stammartikel mit Lieferantenartikelnummer ARTIKELNR
|
||
|
# FARBNR => Varianten Eigenschaft 'farbnr'
|
||
|
# LAENGE => Varianten Eigenschaft 'laenge'; hat 32,34,U
|
||
|
# LST_ID => Lager?
|
||
|
# GROESSE => Varianten Eigenschaft 'groesse'
|
||
|
# FIL_ID => Filialen ID 2,3,4,9
|
||
|
# FILIALE => Filiale 1,2,3,4
|
||
|
# FIL_KURZBEZEICHNUNG => Name der Filiale
|
||
|
# SAI_ID => Saison ID
|
||
|
# SAISON => Saison Nummer
|
||
|
# WAR_ID => Warehouse ID?
|
||
|
# STUECK => Anzahl im Lager
|
||
|
# VK => Verkaufspreis
|
||
|
# LIEFERANT => Lieferantenname
|
||
|
# SAISONBEZEICHNUNG => Saisonbezeichnung
|
||
|
# WARENGRUPPE => Warengruppennummer
|
||
|
# WARENGRUPPENBEZEICHNUNG => Warengruppenbezeichnung
|
||
|
# LIEFARTIKELBEZEICHNUNG => Lieferanten Artikelbezeichung
|
||
|
# EIGENEARTIKELNR => Eigene Artikelnummer; ist eher Bezeichnung
|
||
|
# KOLLEKTION => Kollektion (hat auch -)
|
||
|
# INFO1 => Farbenname
|
||
|
# INFO2 => ?
|
||
|
# INFO3 => ?
|
||
|
# INFO4 => ?
|
||
|
# INFO5 => ?
|
||
|
# BILD1 => ?
|
||
|
# BILD2 => ?
|
||
|
# BILD3 => ?
|
||
|
# BILD4 => ?
|
||
|
# BILD5 => ?
|
||
|
# EANNR => ean
|
||
|
# VKWEB => Verkaufspreis online Shop?
|
||
|
# WAR_KURZBEZEICHNUNG => Warenbezeichnung
|
||
|
# LETZTERWE => Letzter Wareneingang?
|
||
|
|
||
|
my $part_csv = SL::Helper::Csv->new(
|
||
|
file => $opt_part_csv_file,
|
||
|
encoding => 'utf-8', # undef means utf8
|
||
|
sep_char => ';', # default ';'
|
||
|
quote_char => '"', # default '"'
|
||
|
escape_char => '"', # default '"'
|
||
|
);
|
||
|
|
||
|
$part_csv->parse or die "Could not parse csv";
|
||
|
my $hrefs = $part_csv->get_data;
|
||
|
|
||
|
my %parent_variants_to_variants;
|
||
|
foreach my $part_row (@$hrefs) {
|
||
|
my %part =
|
||
|
map {$parts_mapping{$_} => $part_row->{$_}}
|
||
|
grep {$part_row->{$_}}
|
||
|
keys %parts_mapping;
|
||
|
|
||
|
if ($part{varianten_farbnummer} || $part{varianten_farbname}) {
|
||
|
$part{varianten_farbe} = (delete $part{varianten_farbnummer}) . '-' . (delete $part{varianten_farbname});
|
||
|
}
|
||
|
|
||
|
push @{$parent_variants_to_variants{$part_row->{LIEFERANT}}->{$part_row->{ARTIKELNR}}}, \%part;
|
||
|
}
|
||
|
|
||
|
my %variant_property_groups;
|
||
|
my @parent_variants;
|
||
|
my @variants;
|
||
|
|
||
|
my %partsgroup_name_to_partsgroup = map {$_->partsgroup => $_} @{SL::DB::Manager::PartsGroup->get_all()};
|
||
|
my %vendor_name_to_vendor = map {$_->name => $_} @{SL::DB::Manager::Vendor->get_all()};
|
||
|
my %warehouse_description_to_warehouse = map {$_->description => $_} @{SL::DB::Manager::Warehouse->get_all()};
|
||
|
|
||
|
my @all_variant_properties = @{SL::DB::Manager::VariantProperty->get_all()};
|
||
|
|
||
|
my $transfer_type = SL::DB::Manager::TransferType->find_by(
|
||
|
direction => 'in',
|
||
|
description => 'stock',
|
||
|
) or die "Could no find transfer_type";
|
||
|
|
||
|
SL::DB->client->with_transaction(sub {
|
||
|
foreach my $vendor (keys %parent_variants_to_variants) {
|
||
|
foreach my $partnumber (keys %{$parent_variants_to_variants{$vendor}}) {
|
||
|
my $grouped_variant_values = $parent_variants_to_variants{$vendor}->{$partnumber};
|
||
|
|
||
|
#get data for parent_variant
|
||
|
my $first_part = $grouped_variant_values->[0];
|
||
|
my $description = $first_part->{description};
|
||
|
my $partsgroup_name = $first_part->{warengruppe};
|
||
|
my $warehouse_description = $first_part->{warehouse_description};
|
||
|
my $vendor_name = $first_part->{vendor_name};
|
||
|
my $makemodel_model = $first_part->{makemodel_model};
|
||
|
my $best_sellprice = first {$_} sort map {$_->{sellprice}} @$grouped_variant_values;
|
||
|
my $partsgroup = $partsgroup_name_to_partsgroup{$partsgroup_name} or die
|
||
|
die "Could not find partsgroup '$partsgroup_name' for part '$makemodel_model $description'";
|
||
|
my $vendor = $vendor_name_to_vendor{$vendor_name} or
|
||
|
die "Could not find vendor: '$vendor_name' for part '$makemodel_model $description'";
|
||
|
my $warehouse = $warehouse_description_to_warehouse{$warehouse_description} or
|
||
|
die "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description'";
|
||
|
my $parent_variant = SL::DB::Part->new_parent_variant(
|
||
|
description => $description,
|
||
|
sellprice => $best_sellprice,
|
||
|
partsgroup => $partsgroup,
|
||
|
warehouse => $warehouse,
|
||
|
bin => $warehouse->bins->[0],
|
||
|
part_type => 'part',
|
||
|
unit => 'Stck',
|
||
|
);
|
||
|
|
||
|
# add makemodel
|
||
|
my $makemodel = SL::DB::MakeModel->new(
|
||
|
make => $vendor->id,
|
||
|
model => $makemodel_model,
|
||
|
part_description => $description,
|
||
|
);
|
||
|
$parent_variant->add_makemodels($makemodel);
|
||
|
|
||
|
# get active variant_properties
|
||
|
my %group_variant_property_vales;
|
||
|
foreach my $variant_values (@$grouped_variant_values) {
|
||
|
$group_variant_property_vales{$_}->{$variant_values->{$_}} = 1 for
|
||
|
grep { $_ =~ m/^variant/ }
|
||
|
keys %$variant_values;
|
||
|
}
|
||
|
foreach my $variant_property (keys %group_variant_property_vales) {
|
||
|
$group_variant_property_vales{$variant_property} = [sort keys %{$group_variant_property_vales{$variant_property}}];
|
||
|
}
|
||
|
|
||
|
# find variant_properties
|
||
|
my %property_name_to_variant_property;
|
||
|
foreach my $property_name (keys %group_variant_property_vales) {
|
||
|
# TODO find valid properties for partsgroup
|
||
|
my $needed_property_values = $group_variant_property_vales{$property_name};
|
||
|
my ($best_match) =
|
||
|
sort {scalar @{$a->{missing}} <=> scalar @{$b->{missing}}}
|
||
|
map {
|
||
|
my @property_values = map {$_->value} @{$_->property_values};
|
||
|
my @missing;
|
||
|
foreach my $needed_property_value (@$needed_property_values) {
|
||
|
push @missing, $needed_property_value unless scalar grep {$needed_property_value eq $_} @property_values;
|
||
|
}
|
||
|
{property => $_, missing => \@missing};
|
||
|
}
|
||
|
@all_variant_properties;
|
||
|
|
||
|
if (scalar @{$best_match->{missing}}) {
|
||
|
die "Could not find variant property with values '@{$needed_property_values}'.\n" .
|
||
|
"Best match is ${\$best_match->{property}->name} with missing values '@{$best_match->{missing}}'";
|
||
|
}
|
||
|
$property_name_to_variant_property{$property_name} = $best_match->{property};
|
||
|
}
|
||
|
my @variant_properties = values %property_name_to_variant_property;
|
||
|
$parent_variant->variant_properties(@variant_properties);
|
||
|
|
||
|
$parent_variant->save();
|
||
|
|
||
|
foreach my $variant_values (@$grouped_variant_values) {
|
||
|
my @property_values =
|
||
|
map {
|
||
|
my $value = $variant_values->{$_};
|
||
|
first {$_->value eq $value} @{$property_name_to_variant_property{$_}->property_values}}
|
||
|
grep { $_ =~ m/^variant/ }
|
||
|
keys %$variant_values;
|
||
|
|
||
|
my $variant = $parent_variant->create_new_variant(\@property_values);
|
||
|
|
||
|
$variant->update_attributes(
|
||
|
ean => $variant_values->{ean},
|
||
|
description => $variant_values->{description},
|
||
|
sellprice => $variant_values->{sellprice},
|
||
|
);
|
||
|
|
||
|
# set stock
|
||
|
my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
|
||
|
SL::DB::Inventory->new(
|
||
|
part => $variant,
|
||
|
trans_id => $trans_id,
|
||
|
trans_type => $transfer_type,
|
||
|
shippingdate => 'now()',
|
||
|
comment => 'initialer Bestand',
|
||
|
warehouse => $variant->warehouse,
|
||
|
bin => $variant->bin,
|
||
|
qty => $variant_values->{part_qty},
|
||
|
employee => $employee,
|
||
|
)->save;
|
||
|
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
1;
|
||
|
}) or do {
|
||
|
die t8('Error while creating variants: '), SL::DB->client->error;
|
||
|
};
|
||
|
|
||
Auch abrufbar als: Unified diff
kuw: Script Varianten anlegen