Revision 58c39ac5
Von Tamino Steinert vor 6 Tagen hinzugefügt
scripts/import_variant_csv.pl | ||
---|---|---|
1 |
#!/usr/bin/perl |
|
2 |
|
|
3 |
use strict; |
|
4 |
use warnings; |
|
5 |
use utf8; |
|
6 |
|
|
7 |
BEGIN { |
|
8 |
use FindBin; |
|
9 |
|
|
10 |
unshift(@INC, $FindBin::Bin . '/../modules/override'); # Use our own versions of various modules (e.g. YAML). |
|
11 |
push (@INC, $FindBin::Bin . '/..'); # '.' will be removed from @INC soon. |
|
12 |
} |
|
13 |
|
|
14 |
use Data::Dumper; |
|
15 |
use List::Util qw(first); |
|
16 |
|
|
17 |
use SL::DBUtils; |
|
18 |
use SL::LXDebug; |
|
19 |
use SL::LxOfficeConf; |
|
20 |
use SL::Locale::String qw(t8); |
|
21 |
|
|
22 |
SL::LxOfficeConf->read; |
|
23 |
our $lxdebug = LXDebug->new(); |
|
24 |
|
|
25 |
use SL::Auth; |
|
26 |
use SL::Form; |
|
27 |
use SL::Locale; |
|
28 |
|
|
29 |
use SL::Helper::Csv; |
|
30 |
use SL::DB::Part; |
|
31 |
use SL::DB::PartsGroup; |
|
32 |
use SL::DB::VariantProperty; |
|
33 |
use SL::DB::Warehouse; |
|
34 |
use SL::DB::Inventory; |
|
35 |
|
|
36 |
use feature "say"; |
|
37 |
|
|
38 |
chdir($FindBin::Bin . '/..'); |
|
39 |
|
|
40 |
my ($opt_user, $opt_client, $opt_part_csv_file); |
|
41 |
our (%myconfig, $form, $user, $employee, $auth, $locale); |
|
42 |
|
|
43 |
$opt_client = "variant"; |
|
44 |
$opt_user = "test_user"; |
|
45 |
|
|
46 |
# $opt_part_csv_file = "Artikel_Auswahl.csv"; |
|
47 |
$opt_part_csv_file = "Artikel_small.csv"; |
|
48 |
# $opt_part_csv_file = "Artikel_komplett.csv"; |
|
49 |
|
|
50 |
|
|
51 |
$locale = Locale->new; |
|
52 |
$form = Form->new; |
|
53 |
|
|
54 |
sub connect_auth { |
|
55 |
return $auth if $auth; |
|
56 |
|
|
57 |
$auth = SL::Auth->new; |
|
58 |
if (!$auth->session_tables_present) { |
|
59 |
$form->error("The session and user management tables are not present in the authentication database. Please use the administration web interface to create them."); |
|
60 |
} |
|
61 |
|
|
62 |
return $auth; |
|
63 |
} |
|
64 |
|
|
65 |
|
|
66 |
if ($opt_client && !connect_auth()->set_client($opt_client)) { |
|
67 |
$form->error($form->format_string("The client '#1' does not exist.", $opt_client)); |
|
68 |
} |
|
69 |
|
|
70 |
if ($opt_user) { |
|
71 |
$form->error("Need a client, too.") if !$auth || !$auth->client; |
|
72 |
|
|
73 |
%myconfig = connect_auth()->read_user(login => $opt_user); |
|
74 |
|
|
75 |
if (!$myconfig{login}) { |
|
76 |
$form->error($form->format_string("The user '#1' does not exist.", $opt_user)); |
|
77 |
} |
|
78 |
|
|
79 |
$locale = Locale->new($myconfig{countrycode}, "all"); |
|
80 |
$user = User->new(login => $opt_user); |
|
81 |
$employee = SL::DB::Manager::Employee->find_by(login => $opt_user); |
|
82 |
} |
|
83 |
|
|
84 |
# Mappings |
|
85 |
my %parts_mapping = ( |
|
86 |
VK => 'sellprice', |
|
87 |
EANNR => 'ean', |
|
88 |
WAR_KURZBEZEICHNUNG => 'description', |
|
89 |
WARENGRUPPENBEZEICHNUNG => 'warengruppe', |
|
90 |
GROESSE => 'varianten_groesse', |
|
91 |
LAENGE => 'varianten_laenge', |
|
92 |
FARBNR => 'varianten_farbnummer', |
|
93 |
INFO1 => 'varianten_farbname', |
|
94 |
ARTIKELNR => 'makemodel_model', |
|
95 |
LIEFERANT => 'vendor_name', |
|
96 |
FIL_KURZBEZEICHNUNG => 'warehouse_description', |
|
97 |
STUECK => 'part_qty', |
|
98 |
); |
|
99 |
|
|
100 |
# STA_ID => ? |
|
101 |
# STD_ID => ? |
|
102 |
# ARTIKELNR + LIEFERANT => Stammartikel mit Lieferantenartikelnummer ARTIKELNR |
|
103 |
# FARBNR => Varianten Eigenschaft 'farbnr' |
|
104 |
# LAENGE => Varianten Eigenschaft 'laenge'; hat 32,34,U |
|
105 |
# LST_ID => Lager? |
|
106 |
# GROESSE => Varianten Eigenschaft 'groesse' |
|
107 |
# FIL_ID => Filialen ID 2,3,4,9 |
|
108 |
# FILIALE => Filiale 1,2,3,4 |
|
109 |
# FIL_KURZBEZEICHNUNG => Name der Filiale |
|
110 |
# SAI_ID => Saison ID |
|
111 |
# SAISON => Saison Nummer |
|
112 |
# WAR_ID => Warehouse ID? |
|
113 |
# STUECK => Anzahl im Lager |
|
114 |
# VK => Verkaufspreis |
|
115 |
# LIEFERANT => Lieferantenname |
|
116 |
# SAISONBEZEICHNUNG => Saisonbezeichnung |
|
117 |
# WARENGRUPPE => Warengruppennummer |
|
118 |
# WARENGRUPPENBEZEICHNUNG => Warengruppenbezeichnung |
|
119 |
# LIEFARTIKELBEZEICHNUNG => Lieferanten Artikelbezeichung |
|
120 |
# EIGENEARTIKELNR => Eigene Artikelnummer; ist eher Bezeichnung |
|
121 |
# KOLLEKTION => Kollektion (hat auch -) |
|
122 |
# INFO1 => Farbenname |
|
123 |
# INFO2 => ? |
|
124 |
# INFO3 => ? |
|
125 |
# INFO4 => ? |
|
126 |
# INFO5 => ? |
|
127 |
# BILD1 => ? |
|
128 |
# BILD2 => ? |
|
129 |
# BILD3 => ? |
|
130 |
# BILD4 => ? |
|
131 |
# BILD5 => ? |
|
132 |
# EANNR => ean |
|
133 |
# VKWEB => Verkaufspreis online Shop? |
|
134 |
# WAR_KURZBEZEICHNUNG => Warenbezeichnung |
|
135 |
# LETZTERWE => Letzter Wareneingang? |
|
136 |
|
|
137 |
my $part_csv = SL::Helper::Csv->new( |
|
138 |
file => $opt_part_csv_file, |
|
139 |
encoding => 'utf-8', # undef means utf8 |
|
140 |
sep_char => ';', # default ';' |
|
141 |
quote_char => '"', # default '"' |
|
142 |
escape_char => '"', # default '"' |
|
143 |
); |
|
144 |
|
|
145 |
$part_csv->parse or die "Could not parse csv"; |
|
146 |
my $hrefs = $part_csv->get_data; |
|
147 |
|
|
148 |
my %parent_variants_to_variants; |
|
149 |
foreach my $part_row (@$hrefs) { |
|
150 |
my %part = |
|
151 |
map {$parts_mapping{$_} => $part_row->{$_}} |
|
152 |
grep {$part_row->{$_}} |
|
153 |
keys %parts_mapping; |
|
154 |
|
|
155 |
if ($part{varianten_farbnummer} || $part{varianten_farbname}) { |
|
156 |
$part{varianten_farbe} = (delete $part{varianten_farbnummer}) . '-' . (delete $part{varianten_farbname}); |
|
157 |
} |
|
158 |
|
|
159 |
push @{$parent_variants_to_variants{$part_row->{LIEFERANT}}->{$part_row->{ARTIKELNR}}}, \%part; |
|
160 |
} |
|
161 |
|
|
162 |
my %variant_property_groups; |
|
163 |
my @parent_variants; |
|
164 |
my @variants; |
|
165 |
|
|
166 |
my %partsgroup_name_to_partsgroup = map {$_->partsgroup => $_} @{SL::DB::Manager::PartsGroup->get_all()}; |
|
167 |
my %vendor_name_to_vendor = map {$_->name => $_} @{SL::DB::Manager::Vendor->get_all()}; |
|
168 |
my %warehouse_description_to_warehouse = map {$_->description => $_} @{SL::DB::Manager::Warehouse->get_all()}; |
|
169 |
|
|
170 |
my @all_variant_properties = @{SL::DB::Manager::VariantProperty->get_all()}; |
|
171 |
|
|
172 |
my $transfer_type = SL::DB::Manager::TransferType->find_by( |
|
173 |
direction => 'in', |
|
174 |
description => 'stock', |
|
175 |
) or die "Could no find transfer_type"; |
|
176 |
|
|
177 |
SL::DB->client->with_transaction(sub { |
|
178 |
foreach my $vendor (keys %parent_variants_to_variants) { |
|
179 |
foreach my $partnumber (keys %{$parent_variants_to_variants{$vendor}}) { |
|
180 |
my $grouped_variant_values = $parent_variants_to_variants{$vendor}->{$partnumber}; |
|
181 |
|
|
182 |
#get data for parent_variant |
|
183 |
my $first_part = $grouped_variant_values->[0]; |
|
184 |
my $description = $first_part->{description}; |
|
185 |
my $partsgroup_name = $first_part->{warengruppe}; |
|
186 |
my $warehouse_description = $first_part->{warehouse_description}; |
|
187 |
my $vendor_name = $first_part->{vendor_name}; |
|
188 |
my $makemodel_model = $first_part->{makemodel_model}; |
|
189 |
my $best_sellprice = first {$_} sort map {$_->{sellprice}} @$grouped_variant_values; |
|
190 |
my $partsgroup = $partsgroup_name_to_partsgroup{$partsgroup_name} or die |
|
191 |
die "Could not find partsgroup '$partsgroup_name' for part '$makemodel_model $description'"; |
|
192 |
my $vendor = $vendor_name_to_vendor{$vendor_name} or |
|
193 |
die "Could not find vendor: '$vendor_name' for part '$makemodel_model $description'"; |
|
194 |
my $warehouse = $warehouse_description_to_warehouse{$warehouse_description} or |
|
195 |
die "Could not find warehouse '$warehouse_description' for part '$makemodel_model $description'"; |
|
196 |
my $parent_variant = SL::DB::Part->new_parent_variant( |
|
197 |
description => $description, |
|
198 |
sellprice => $best_sellprice, |
|
199 |
partsgroup => $partsgroup, |
|
200 |
warehouse => $warehouse, |
|
201 |
bin => $warehouse->bins->[0], |
|
202 |
part_type => 'part', |
|
203 |
unit => 'Stck', |
|
204 |
); |
|
205 |
|
|
206 |
# add makemodel |
|
207 |
my $makemodel = SL::DB::MakeModel->new( |
|
208 |
make => $vendor->id, |
|
209 |
model => $makemodel_model, |
|
210 |
part_description => $description, |
|
211 |
); |
|
212 |
$parent_variant->add_makemodels($makemodel); |
|
213 |
|
|
214 |
# get active variant_properties |
|
215 |
my %group_variant_property_vales; |
|
216 |
foreach my $variant_values (@$grouped_variant_values) { |
|
217 |
$group_variant_property_vales{$_}->{$variant_values->{$_}} = 1 for |
|
218 |
grep { $_ =~ m/^variant/ } |
|
219 |
keys %$variant_values; |
|
220 |
} |
|
221 |
foreach my $variant_property (keys %group_variant_property_vales) { |
|
222 |
$group_variant_property_vales{$variant_property} = [sort keys %{$group_variant_property_vales{$variant_property}}]; |
|
223 |
} |
|
224 |
|
|
225 |
# find variant_properties |
|
226 |
my %property_name_to_variant_property; |
|
227 |
foreach my $property_name (keys %group_variant_property_vales) { |
|
228 |
# TODO find valid properties for partsgroup |
|
229 |
my $needed_property_values = $group_variant_property_vales{$property_name}; |
|
230 |
my ($best_match) = |
|
231 |
sort {scalar @{$a->{missing}} <=> scalar @{$b->{missing}}} |
|
232 |
map { |
|
233 |
my @property_values = map {$_->value} @{$_->property_values}; |
|
234 |
my @missing; |
|
235 |
foreach my $needed_property_value (@$needed_property_values) { |
|
236 |
push @missing, $needed_property_value unless scalar grep {$needed_property_value eq $_} @property_values; |
|
237 |
} |
|
238 |
{property => $_, missing => \@missing}; |
|
239 |
} |
|
240 |
@all_variant_properties; |
|
241 |
|
|
242 |
if (scalar @{$best_match->{missing}}) { |
|
243 |
die "Could not find variant property with values '@{$needed_property_values}'.\n" . |
|
244 |
"Best match is ${\$best_match->{property}->name} with missing values '@{$best_match->{missing}}'"; |
|
245 |
} |
|
246 |
$property_name_to_variant_property{$property_name} = $best_match->{property}; |
|
247 |
} |
|
248 |
my @variant_properties = values %property_name_to_variant_property; |
|
249 |
$parent_variant->variant_properties(@variant_properties); |
|
250 |
|
|
251 |
$parent_variant->save(); |
|
252 |
|
|
253 |
foreach my $variant_values (@$grouped_variant_values) { |
|
254 |
my @property_values = |
|
255 |
map { |
|
256 |
my $value = $variant_values->{$_}; |
|
257 |
first {$_->value eq $value} @{$property_name_to_variant_property{$_}->property_values}} |
|
258 |
grep { $_ =~ m/^variant/ } |
|
259 |
keys %$variant_values; |
|
260 |
|
|
261 |
my $variant = $parent_variant->create_new_variant(\@property_values); |
|
262 |
|
|
263 |
$variant->update_attributes( |
|
264 |
ean => $variant_values->{ean}, |
|
265 |
description => $variant_values->{description}, |
|
266 |
sellprice => $variant_values->{sellprice}, |
|
267 |
); |
|
268 |
|
|
269 |
# set stock |
|
270 |
my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|); |
|
271 |
SL::DB::Inventory->new( |
|
272 |
part => $variant, |
|
273 |
trans_id => $trans_id, |
|
274 |
trans_type => $transfer_type, |
|
275 |
shippingdate => 'now()', |
|
276 |
comment => 'initialer Bestand', |
|
277 |
warehouse => $variant->warehouse, |
|
278 |
bin => $variant->bin, |
|
279 |
qty => $variant_values->{part_qty}, |
|
280 |
employee => $employee, |
|
281 |
)->save; |
|
282 |
|
|
283 |
} |
|
284 |
} |
|
285 |
} |
|
286 |
1; |
|
287 |
}) or do { |
|
288 |
die t8('Error while creating variants: '), SL::DB->client->error; |
|
289 |
}; |
|
290 |
|
Auch abrufbar als: Unified diff
kuw: Script Varianten anlegen