1 package SL::Controller::CsvImport::Part;
8 use SL::DB::Buchungsgruppe;
9 use SL::DB::CustomVariable;
10 use SL::DB::CustomVariableConfig;
11 use SL::DB::PartsGroup;
12 use SL::DB::PaymentTerm;
13 use SL::DB::PriceFactor;
14 use SL::DB::Pricegroup;
16 use SL::DB::Translation;
19 use parent qw(SL::Controller::CsvImport::Base);
21 use Rose::Object::MakeMethods::Generic
23 scalar => [ qw(table makemodel_columns) ],
24 'scalar --get_set_init' => [ qw(bg_by settings parts_by price_factors_by units_by partsgroups_by
25 translation_columns all_pricegroups) ],
30 $self->class('SL::DB::Part');
36 my $all_bg = SL::DB::Manager::Buchungsgruppe->get_all;
37 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_bg } } ) } qw(id description) };
40 sub init_price_factors_by {
43 my $all_price_factors = SL::DB::Manager::PriceFactor->get_all;
44 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_price_factors } } ) } qw(id description) };
47 sub init_partsgroups_by {
50 my $all_partsgroups = SL::DB::Manager::PartsGroup->get_all;
51 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_partsgroups } } ) } qw(id partsgroup) };
57 my $all_units = SL::DB::Manager::Unit->get_all;
58 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_units } } ) } qw(name) };
64 # my $parts_by = { id => { map { ( $_->id => $_ ) } grep { !$_->assembly } @{ $self->existing_objects } },
65 # partnumber => { part => { },
68 # foreach my $part (@{ $self->existing_objects }) {
69 # next if $part->assembly;
70 # $parts_by->{partnumber}->{ $part->type }->{ $part->partnumber } = $part;
74 my $sth = prepare_execute_query($::form, $::form->get_standard_dbh, 'SELECT partnumber FROM parts');
75 while (my ($partnumber) = $sth->fetchrow_array()) {
76 $parts_by->{partnumber}{$partnumber} = 1;
82 sub init_all_pricegroups {
85 return SL::DB::Manager::Pricegroup->get_all(sort => 'id');
91 return { map { ( $_ => $self->controller->profile->get($_) ) } qw(apply_buchungsgruppe default_buchungsgruppe article_number_policy
92 sellprice_places sellprice_adjustment sellprice_adjustment_type
93 shoparticle_if_missing parts_type default_unit) };
96 sub init_all_cvar_configs {
99 return SL::DB::Manager::CustomVariableConfig->get_all(where => [ module => 'IC' ]);
102 sub init_translation_columns {
105 return [ map { ("description_" . $_->article_code, "notes_" . $_->article_code) } (@{ $self->all_languages }) ];
111 return unless @{ $self->controller->data };
113 $self->controller->track_progress(phase => 'building data', progress => 0);
115 $self->makemodel_columns({});
118 my $num_data = scalar @{ $self->controller->data };
119 foreach my $entry (@{ $self->controller->data }) {
120 $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0;
122 $self->check_buchungsgruppe($entry);
123 $self->check_type($entry);
124 $self->check_unit($entry);
125 $self->check_price_factor($entry);
126 $self->check_payment($entry);
127 $self->check_partsgroup($entry);
128 $self->handle_pricegroups($entry);
129 $self->check_existing($entry) unless @{ $entry->{errors} };
130 $self->handle_prices($entry) if $self->settings->{sellprice_adjustment};
131 $self->handle_shoparticle($entry);
132 $self->handle_translations($entry);
133 $self->handle_cvars($entry);
134 $self->handle_makemodel($entry);
135 $self->set_various_fields($entry);
140 $self->add_columns(qw(type)) if $self->settings->{parts_type} eq 'mixed';
141 $self->add_columns(qw(buchungsgruppen_id unit));
142 $self->add_columns(map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw (price_factor payment partsgroup));
143 $self->add_columns(qw(shop)) if $self->settings->{shoparticle_if_missing};
144 $self->add_cvar_raw_data_columns;
145 map { $self->add_raw_data_columns("pricegroup_${_}") if exists $self->controller->data->[0]->{raw_data}->{"pricegroup_$_"} } (1..scalar(@{ $self->all_pricegroups }));
146 map { $self->add_raw_data_columns($_) if exists $self->controller->data->[0]->{raw_data}->{$_} } @{ $self->translation_columns };
147 map { $self->add_raw_data_columns("make_${_}", "model_${_}", "lastcost_${_}") } sort { $a <=> $b } keys %{ $self->makemodel_columns };
150 sub get_duplicate_check_fields {
153 label => $::locale->text('Part Number'),
158 label => $::locale->text('Description'),
161 my $desc = shift->description;
162 $desc =~ s/[\s,\.\-]//g;
169 sub check_buchungsgruppe {
170 my ($self, $entry) = @_;
172 my $object = $entry->{object};
174 # Check Buchungsgruppe
176 # Store and verify default ID.
177 my $default_id = $self->settings->{default_buchungsgruppe};
178 $default_id = undef unless $self->bg_by->{id}->{ $default_id };
180 # 1. Use default ID if enforced.
181 $object->buchungsgruppen_id($default_id) if $default_id && ($self->settings->{apply_buchungsgruppe} eq 'all');
183 # 2. Use supplied ID if valid
184 $object->buchungsgruppen_id(undef) if $object->buchungsgruppen_id && !$self->bg_by->{id}->{ $object->buchungsgruppen_id };
186 # 3. Look up name if supplied.
187 if (!$object->buchungsgruppen_id) {
188 my $bg = $self->bg_by->{description}->{ $entry->{raw_data}->{buchungsgruppe} };
189 $object->buchungsgruppen_id($bg->id) if $bg;
192 # 4. Use default ID if not valid.
193 $object->buchungsgruppen_id($default_id) if !$object->buchungsgruppen_id && $default_id && ($self->settings->{apply_buchungsgruppe} eq 'missing');
195 return 1 if $object->buchungsgruppen_id;
197 push @{ $entry->{errors} }, $::locale->text('Error: Buchungsgruppe missing or invalid');
202 my ($self, $entry) = @_;
204 my $object = $entry->{object};
206 if ($object->partnumber && $self->parts_by->{partnumber}{$object->partnumber}) {
207 $entry->{part} = SL::DB::Manager::Part->find_by(
208 SL::DB::Manager::Part->type_filter($object->type),
209 ( partnumber => $object->partnumber ) x!! $object->partnumber,
213 if ($entry->{part}) {
214 if ($self->settings->{article_number_policy} eq 'update_prices') {
215 map { $entry->{part}->$_( $object->$_ ) if defined $object->$_ } qw(sellprice listprice lastcost);
218 my %prices_by_pricegroup_id = map { $_->pricegroup->id => $_ } $entry->{part}->prices, $object->prices;
219 $entry->{part}->prices(grep { $_ } map { $prices_by_pricegroup_id{$_->id} } @{ $self->all_pricegroups });
221 push @{ $entry->{information} }, $::locale->text('Updating prices of existing entry in database');
222 $entry->{object_to_save} = $entry->{part};
223 } elsif ( $self->settings->{article_number_policy} eq 'skip' ) {
224 push(@{$entry->{errors}}, $::locale->text('Skipping due to existing entry in database'));
226 $object->partnumber('####');
233 my ($self, $entry) = @_;
235 foreach my $column (qw(sellprice)) {
236 my $object = $entry->{object_to_save} || $entry->{object};
237 my $adjustment = $self->settings->{sellprice_adjustment};
238 my $value = $object->$column;
240 $value = $self->settings->{sellprice_adjustment_type} eq 'percent' ? $value * (100 + $adjustment) / 100 : $value + $adjustment;
241 $object->$column($::form->round_amount($value, $self->settings->{sellprice_places}));
245 sub handle_shoparticle {
246 my ($self, $entry) = @_;
248 $entry->{object}->shop(1) if $self->settings->{shoparticle_if_missing} && !$self->controller->headers->{used}->{shop};
252 my ($self, $entry) = @_;
254 my $bg = $self->bg_by->{id}->{ $entry->{object}->buchungsgruppen_id };
255 $bg ||= SL::DB::Buchungsgruppe->new(inventory_accno_id => 1, income_accno_id_0 => 1, expense_accno_id_0 => 1);
257 my $type = $self->settings->{parts_type};
258 if ($type eq 'mixed') {
259 $type = $entry->{raw_data}->{type} =~ m/^p/i ? 'part'
260 : $entry->{raw_data}->{type} =~ m/^s/i ? 'service'
264 $entry->{object}->income_accno_id( $bg->income_accno_id_0 );
265 $entry->{object}->expense_accno_id( $bg->expense_accno_id_0 );
267 if ($type eq 'part') {
268 $entry->{object}->inventory_accno_id( $bg->inventory_accno_id );
270 } elsif ($type ne 'service') {
271 push @{ $entry->{errors} }, $::locale->text('Error: Invalid part type');
278 sub check_price_factor {
279 my ($self, $entry) = @_;
281 my $object = $entry->{object};
283 # Check whether or not price factor ID is valid.
284 if ($object->price_factor_id && !$self->price_factors_by->{id}->{ $object->price_factor_id }) {
285 push @{ $entry->{errors} }, $::locale->text('Error: Invalid price factor');
289 # Map name to ID if given.
290 if (!$object->price_factor_id && $entry->{raw_data}->{price_factor}) {
291 my $pf = $self->price_factors_by->{description}->{ $entry->{raw_data}->{price_factor} };
294 push @{ $entry->{errors} }, $::locale->text('Error: Invalid price factor');
298 $object->price_factor_id($pf->id);
304 sub check_partsgroup {
305 my ($self, $entry) = @_;
307 my $object = $entry->{object};
309 # Check whether or not part group ID is valid.
310 if ($object->partsgroup_id && !$self->partsgroups_by->{id}->{ $object->partsgroup_id }) {
311 push @{ $entry->{errors} }, $::locale->text('Error: Invalid parts group');
315 # Map name to ID if given.
316 if (!$object->partsgroup_id && $entry->{raw_data}->{partsgroup}) {
317 my $pg = $self->partsgroups_by->{partsgroup}->{ $entry->{raw_data}->{partsgroup} };
320 push @{ $entry->{errors} }, $::locale->text('Error: Invalid parts group');
324 $object->partsgroup_id($pg->id);
331 my ($self, $entry) = @_;
333 my $object = $entry->{object};
335 $object->unit($self->settings->{default_unit}) unless $object->unit;
337 # Check whether or unit is valid.
338 if (!$self->units_by->{name}->{ $object->unit }) {
339 push @{ $entry->{errors} }, $::locale->text('Error: Unit missing or invalid');
346 sub handle_translations {
347 my ($self, $entry) = @_;
350 foreach my $language (@{ $self->all_languages }) {
351 my ($desc, $notes) = @{ $entry->{raw_data} }{ "description_" . $language->article_code, "notes_" . $language->article_code };
352 next unless $desc || $notes;
354 push @translations, SL::DB::Translation->new(language_id => $language->id,
355 translation => $desc,
356 longdescription => $notes);
359 $entry->{object}->translations(\@translations);
362 sub handle_pricegroups {
363 my ($self, $entry) = @_;
367 foreach my $pricegroup (@{ $self->all_pricegroups }) {
369 my $sellprice = $entry->{raw_data}->{"pricegroup_${idx}"};
370 next if $sellprice eq '';
372 push @prices, SL::DB::Price->new(pricegroup_id => $pricegroup->id,
373 price => $::form->parse_amount(\%::myconfig, $sellprice));
376 $entry->{object}->prices(\@prices);
379 sub handle_makemodel {
380 my ($self, $entry) = @_;
381 my $object = $entry->{object};
385 foreach my $idx (sort map { substr $_, 5 } grep { m/^make_\d+$/ && $entry->{raw_data}->{$_} } keys %{ $entry->{raw_data} }) {
386 my $vendor = $entry->{raw_data}->{"make_${idx}"};
387 $vendor = $self->vc_by->{id}-> { $vendor }
388 || $self->vc_by->{number}->{vendors}->{ $vendor }
389 || $self->vc_by->{name}-> {vendors}->{ $vendor };
391 if (ref($vendor) ne 'SL::DB::Vendor') {
392 push @{ $entry->{errors} }, $::locale->text('Error: Invalid vendor in column make_#1', $idx);
396 push @makemodels, SL::DB::MakeModel->new(make => $vendor->id,
397 model => $entry->{raw_data}->{"model_${idx}"},
398 lastcost_as_number => $entry->{raw_data}->{"lastcost_${idx}"});
400 $self->makemodel_columns->{$idx} = 1;
401 $entry->{raw_data}->{"make_${idx}"} = $vendor->name;
405 $object->makemodels(\@makemodels);
406 $object->makemodel(scalar(@makemodels) ? 1 : 0);
408 if ( !$entry->{part} || $self->settings->{article_number_policy} ne 'update_prices' ) {
412 my %old_makemodels_by_make = map { $_->make => $_ } $entry->{part}->makemodels;
414 foreach my $makemodel ($object->makemodels()) {
415 my $makemodel_orig = $old_makemodels_by_make{$makemodel->make};
418 if ($makemodel_orig) {
419 $makemodel_orig->model($makemodel->model);
420 $makemodel_orig->lastcost($makemodel->lastcost);
423 $entry->{part}->add_makemodels($makemodel);
427 $entry->{part}->makemodel($object->makemodel);
429 $self->save_with_cascade(1) if $found_any;
432 sub set_various_fields {
433 my ($self, $entry) = @_;
435 $entry->{object}->priceupdate(DateTime->now_local);
441 my $profile = $self->SUPER::init_profile;
442 delete @{$profile}{qw(alternate assembly bom expense_accno_id income_accno_id inventory_accno_id makemodel priceupdate stockable type)};
444 $profile->{"pricegroup_$_"} = '' for 1 .. scalar @{ $_[0]->all_pricegroups };
450 my ($self, %params) = @_;
452 my $with_number = [ grep { $_->{object}->partnumber ne '####' } @{ $self->controller->data } ];
453 my $without_number = [ grep { $_->{object}->partnumber eq '####' } @{ $self->controller->data } ];
455 map { $_->{object}->partnumber('') } @{ $without_number };
457 $self->SUPER::save_objects(data => $with_number);
458 $self->SUPER::save_objects(data => $without_number);
461 sub setup_displayable_columns {
464 $self->SUPER::setup_displayable_columns;
465 $self->add_cvar_columns_to_displayable_columns;
467 $self->add_displayable_columns({ name => 'bin', description => $::locale->text('Bin') },
468 { name => 'buchungsgruppen_id', description => $::locale->text('Buchungsgruppe (database ID)') },
469 { name => 'buchungsgruppe', description => $::locale->text('Buchungsgruppe (name)') },
470 { name => 'description', description => $::locale->text('Description') },
471 { name => 'drawing', description => $::locale->text('Drawing') },
472 { name => 'ean', description => $::locale->text('EAN') },
473 { name => 'formel', description => $::locale->text('Formula') },
474 { name => 'gv', description => $::locale->text('Business Volume') },
475 { name => 'has_sernumber', description => $::locale->text('Has serial number') },
476 { name => 'image', description => $::locale->text('Image') },
477 { name => 'lastcost', description => $::locale->text('Last Cost') },
478 { name => 'listprice', description => $::locale->text('List Price') },
479 { name => 'make_X', description => $::locale->text('Make (vendor\'s database ID, number or name; with X being a number)') . ' [1]' },
480 { name => 'microfiche', description => $::locale->text('Microfiche') },
481 { name => 'model_X', description => $::locale->text('Model (with X being a number)') . ' [1]' },
482 { name => 'lastcost_X', description => $::locale->text('Lastcost (with X being a number)') . ' [1]' },
483 { name => 'not_discountable', description => $::locale->text('Not Discountable') },
484 { name => 'notes', description => $::locale->text('Notes') },
485 { name => 'obsolete', description => $::locale->text('Obsolete') },
486 { name => 'onhand', description => $::locale->text('On Hand') . ' [2]' },
487 { name => 'partnumber', description => $::locale->text('Part Number') },
488 { name => 'partsgroup_id', description => $::locale->text('Partsgroup (database ID)') },
489 { name => 'partsgroup', description => $::locale->text('Partsgroup (name)') },
490 { name => 'payment_id', description => $::locale->text('Payment terms (database ID)') },
491 { name => 'payment', description => $::locale->text('Payment terms (name)') },
492 { name => 'price_factor_id', description => $::locale->text('Price factor (database ID)') },
493 { name => 'price_factor', description => $::locale->text('Price factor (name)') },
494 { name => 'rop', description => $::locale->text('ROP') },
495 { name => 'sellprice', description => $::locale->text('Sellprice') },
496 { name => 'shop', description => $::locale->text('Shopartikel') },
497 { name => 'type', description => $::locale->text('Article type') . ' [3]' },
498 { name => 'unit', description => $::locale->text('Unit (if missing or empty default unit will be used)') },
499 { name => 've', description => $::locale->text('Verrechnungseinheit') },
500 { name => 'weight', description => $::locale->text('Weight') },
503 foreach my $language (@{ $self->all_languages }) {
504 $self->add_displayable_columns({ name => 'description_' . $language->article_code,
505 description => $::locale->text('Description (translation for #1)', $language->description) },
506 { name => 'notes_' . $language->article_code,
507 description => $::locale->text('Notes (translation for #1)', $language->description) });
511 foreach my $pricegroup (@{ $self->all_pricegroups }) {
513 $self->add_displayable_columns({ name => 'pricegroup_' . $idx,
514 description => $::locale->text("Sellprice for price group '#1'", $pricegroup->pricegroup) });