Revision 051db7cc
Von Tamino Steinert vor 11 Monaten hinzugefügt
| scripts/import_variant_csv.pl | ||
|---|---|---|
| 13 | 13 |
|
| 14 | 14 |
use Data::Dumper; |
| 15 | 15 |
use List::Util qw(first); |
| 16 |
use File::Basename; |
|
| 16 | 17 |
|
| 17 | 18 |
use SL::DBUtils; |
| 18 | 19 |
use SL::LXDebug; |
| ... | ... | |
| 38 | 39 |
|
| 39 | 40 |
chdir($FindBin::Bin . '/..'); |
| 40 | 41 |
|
| 41 |
my ($opt_user, $opt_client, $opt_part_csv_file, $opt_groessen_staffeln_csv_file); |
|
| 42 |
my ( |
|
| 43 |
$opt_user, $opt_client, |
|
| 44 |
$opt_warengruppen_csv_file, $opt_farben_folder, |
|
| 45 |
$opt_part_csv_file, $opt_groessen_staffeln_csv_file |
|
| 46 |
); |
|
| 42 | 47 |
our (%myconfig, $form, $user, $employee, $auth, $locale); |
| 43 | 48 |
|
| 44 | 49 |
$opt_client = "variant"; |
| 45 | 50 |
$opt_user = "test_user"; |
| 46 | 51 |
|
| 47 |
$opt_part_csv_file = "Artikel_komplett.csv"; |
|
| 48 |
|
|
| 49 |
$opt_groessen_staffeln_csv_file = "Größenstaffeln.csv"; |
|
| 52 |
$opt_warengruppen_csv_file = "kuw/Warengruppen.csv"; |
|
| 53 |
$opt_groessen_staffeln_csv_file = "kuw/Größenstaffeln.csv"; |
|
| 54 |
$opt_farben_folder = "kuw/Farben"; |
|
| 55 |
$opt_part_csv_file = "kuw/Export_bearbeitet.csv"; |
|
| 50 | 56 |
|
| 51 | 57 |
|
| 52 | 58 |
$locale = Locale->new; |
| ... | ... | |
| 86 | 92 |
my %parts_mapping = ( |
| 87 | 93 |
VK => 'sellprice', |
| 88 | 94 |
EANNR => 'ean', |
| 89 |
WAR_KURZBEZEICHNUNG => 'description',
|
|
| 90 |
WARENGRUPPENBEZEICHNUNG => 'warengruppe',
|
|
| 95 |
EIGENEARTIKELNR => 'description',
|
|
| 96 |
WAR_KURZBEZEICHNUNG => 'warengruppe',
|
|
| 91 | 97 |
GROESSE => 'varianten_groesse', |
| 92 | 98 |
LAENGE => 'varianten_laenge', |
| 93 | 99 |
FARBNR => 'varianten_farbnummer', |
| ... | ... | |
| 171 | 177 |
$groessen_staffel_csv->parse or die "Could not parse csv"; |
| 172 | 178 |
my $groessen_staffel_hrefs = $groessen_staffel_csv->get_data; |
| 173 | 179 |
|
| 180 |
my $warengruppen_csv = SL::Helper::Csv->new( |
|
| 181 |
file => $opt_warengruppen_csv_file, |
|
| 182 |
encoding => 'utf-8', # undef means utf8 |
|
| 183 |
sep_char => ';', # default ';' |
|
| 184 |
quote_char => '"', # default '"' |
|
| 185 |
escape_char => '"', # default '"' |
|
| 186 |
); |
|
| 187 |
$warengruppen_csv->parse or die "Could not parse csv"; |
|
| 188 |
my $warengruppen_hrefs = $warengruppen_csv->get_data; |
|
| 189 |
|
|
| 174 | 190 |
my $transfer_type = SL::DB::Manager::TransferType->find_by( |
| 175 | 191 |
direction => 'in', |
| 176 | 192 |
description => 'stock', |
| ... | ... | |
| 178 | 194 |
|
| 179 | 195 |
SL::DB->client->with_transaction(sub {
|
| 180 | 196 |
|
| 197 |
# create farben listen |
|
| 198 |
foreach my $farb_csv_file (glob( $opt_farben_folder . '/*' )) {
|
|
| 199 |
my $farb_csv = SL::Helper::Csv->new( |
|
| 200 |
file => $farb_csv_file, |
|
| 201 |
encoding => 'utf-8', # undef means utf8 |
|
| 202 |
sep_char => ';', # default ';' |
|
| 203 |
quote_char => '"', # default '"' |
|
| 204 |
escape_char => '"', # default '"' |
|
| 205 |
); |
|
| 206 |
$farb_csv->parse or die "Could not parse csv"; |
|
| 207 |
my $farb_hrefs = $farb_csv->get_data; |
|
| 208 |
|
|
| 209 |
my $vendor_name = basename($farb_csv_file); |
|
| 210 |
$vendor_name =~ s/\.csv//; |
|
| 211 |
|
|
| 212 |
my $variant_property = SL::DB::VariantProperty->new( |
|
| 213 |
name => "Farbliste $vendor_name", |
|
| 214 |
unique_name => "Farbliste $vendor_name", |
|
| 215 |
abbreviation => "fa", |
|
| 216 |
)->save; |
|
| 217 |
|
|
| 218 |
my $pos = 1; |
|
| 219 |
SL::DB::VariantPropertyValue->new( |
|
| 220 |
variant_property => $variant_property, |
|
| 221 |
value => $_, |
|
| 222 |
abbreviation => $_, |
|
| 223 |
sortkey => $pos++, |
|
| 224 |
)->save for |
|
| 225 |
map {$_->{Joined}}
|
|
| 226 |
@$farb_hrefs; |
|
| 227 |
} |
|
| 228 |
|
|
| 229 |
# create partsgroups |
|
| 230 |
my @hierachy_descrioptions = qw( |
|
| 231 |
Bereich Hauptabteilung Abteilung Hauptwarengruppe Warengruppe |
|
| 232 |
); |
|
| 233 |
my %current_partsgroup_hierachy; |
|
| 234 |
foreach my $partsgroup_row (@$warengruppen_hrefs) {
|
|
| 235 |
# TODO: store valid groessen staffeln |
|
| 236 |
my $valid_groessen_staffen = delete $partsgroup_row->{Größenstaffeln};
|
|
| 237 |
my $last_hierachy_key; |
|
| 238 |
foreach my $hierachy_key (@hierachy_descrioptions) {
|
|
| 239 |
if ($partsgroup_row->{$hierachy_key}) {
|
|
| 240 |
my ($number, @rest) = split(' ', $partsgroup_row->{$hierachy_key});
|
|
| 241 |
my $name = join(' ', @rest);
|
|
| 242 |
unless ($number && $name) {
|
|
| 243 |
die "Could not find number and name for $hierachy_key partsgroup '".$partsgroup_row->{$hierachy_key}."' in the row:'\n".
|
|
| 244 |
join(';', map {$partsgroup_row->{$_}} @hierachy_descrioptions);
|
|
| 245 |
} |
|
| 246 |
my $partsgroup = SL::DB::PartsGroup->new( |
|
| 247 |
partsgroup => $name, |
|
| 248 |
sortkey => $number, |
|
| 249 |
description => "$number $name", |
|
| 250 |
parent_id => $last_hierachy_key ? $current_partsgroup_hierachy{$last_hierachy_key}->id : undef,
|
|
| 251 |
)->save; |
|
| 252 |
$current_partsgroup_hierachy{$hierachy_key} = $partsgroup;
|
|
| 253 |
} |
|
| 254 |
my $last_hierachy_key = $hierachy_key; |
|
| 255 |
} |
|
| 256 |
} |
|
| 257 |
|
|
| 181 | 258 |
# create groessen staffeln |
| 182 | 259 |
foreach my $groessen_staffel_row (@$groessen_staffel_hrefs) {
|
| 183 | 260 |
my $name = delete $groessen_staffel_row->{BEZEICHNUNG};
|
| ... | ... | |
| 200 | 277 |
keys %$groessen_staffel_row; |
| 201 | 278 |
} |
| 202 | 279 |
|
| 203 |
die "end"; |
|
| 204 |
|
|
| 205 |
my %partsgroup_name_to_partsgroup = map {$_->partsgroup => $_} @{SL::DB::Manager::PartsGroup->get_all()};
|
|
| 206 |
my %vendor_name_to_vendor = map {$_->name => $_} @{SL::DB::Manager::Vendor->get_all()};
|
|
| 207 |
my %warehouse_description_to_warehouse = map {$_->description => $_} @{SL::DB::Manager::Warehouse->get_all()};
|
|
| 280 |
my %partsgroup_name_to_partsgroup = map {lc($_->partsgroup) => $_} @{SL::DB::Manager::PartsGroup->get_all()};
|
|
| 281 |
my %vendor_name_to_vendor = map {lc($_->name) => $_} @{SL::DB::Manager::Vendor->get_all()};
|
|
| 282 |
my %warehouse_description_to_warehouse = map {lc($_->description) => $_} @{SL::DB::Manager::Warehouse->get_all()};
|
|
| 208 | 283 |
|
| 209 | 284 |
my @all_variant_properties = @{SL::DB::Manager::VariantProperty->get_all()};
|
| 210 | 285 |
# create parts |
| ... | ... | |
| 220 | 295 |
my $vendor_name = $first_part->{vendor_name};
|
| 221 | 296 |
my $makemodel_model = $first_part->{makemodel_model};
|
| 222 | 297 |
my $best_sellprice = first {$_} sort map {$_->{sellprice}} @$grouped_variant_values;
|
| 223 |
my $partsgroup = $partsgroup_name_to_partsgroup{$partsgroup_name} or die
|
|
| 298 |
$best_sellprice =~ s/,/./; |
|
| 299 |
my $partsgroup = $partsgroup_name_to_partsgroup{lc($partsgroup_name)} or die
|
|
| 224 | 300 |
die "Could not find partsgroup '$partsgroup_name' for part '$makemodel_model $description'"; |
| 225 |
my $vendor = $vendor_name_to_vendor{$vendor_name} or
|
|
| 301 |
my $vendor = $vendor_name_to_vendor{lc($vendor_name)} or
|
|
| 226 | 302 |
die "Could not find vendor: '$vendor_name' for part '$makemodel_model $description'"; |
| 227 |
my $warehouse = $warehouse_description_to_warehouse{$warehouse_description} or
|
|
| 303 |
my $warehouse = $warehouse_description_to_warehouse{lc($warehouse_description)} or
|
|
| 228 | 304 |
die "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description'"; |
| 229 | 305 |
my $parent_variant = SL::DB::Part->new_parent_variant( |
| 230 | 306 |
description => $description, |
| ... | ... | |
| 273 | 349 |
@all_variant_properties; |
| 274 | 350 |
|
| 275 | 351 |
if (scalar @{$best_match->{missing}}) {
|
| 276 |
die "Could not find variant property with values '@{$needed_property_values}'.\n" .
|
|
| 352 |
die "Could not find variant property with values for $property_name '@{$needed_property_values}' of part '$makemodel_model $description'.\n" .
|
|
| 277 | 353 |
"Best match is ${\$best_match->{property}->name} with missing values '@{$best_match->{missing}}'";
|
| 278 | 354 |
} |
| 279 | 355 |
$property_name_to_variant_property{$property_name} = $best_match->{property};
|
| ... | ... | |
| 293 | 369 |
|
| 294 | 370 |
my $variant = $parent_variant->create_new_variant(\@property_values); |
| 295 | 371 |
|
| 372 |
my $sellprice = $variant_values->{sellprice};
|
|
| 373 |
$sellprice =~ s/,/./; |
|
| 296 | 374 |
$variant->update_attributes( |
| 297 | 375 |
ean => $variant_values->{ean},
|
| 298 | 376 |
description => $variant_values->{description},
|
| 299 |
sellprice => $variant_values->{sellprice},
|
|
| 377 |
sellprice => $sellprice,
|
|
| 300 | 378 |
); |
| 301 | 379 |
|
| 302 | 380 |
# set stock |
Auch abrufbar als: Unified diff
kuw: CSV-Import-Script um Farbtabellen erweitert