From 6f45549b6f3d1f4f178b8799c9997fdc6e1447d0 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Wed, 13 Mar 2013 10:08:52 +0100 Subject: [PATCH] Auftrags-Import MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Ändert den Controller, dass er mit Multiplex-Daten umgehen kann. Neue Klasse BaseMulti für Mulitplex-Daten (abgeleitet von Base). Neue Klasse Order für Auftrags-Import (abgeleitet von BaseMulti). Eintrag im Menü. Anpassungen der templates. --- SL/Controller/CsvImport.pm | 154 ++++++- SL/Controller/CsvImport/Base.pm | 8 +- SL/Controller/CsvImport/BaseMulti.pm | 232 ++++++++++ SL/Controller/CsvImport/Order.pm | 416 ++++++++++++++++++ menus/erp.ini | 5 + .../webpages/csv_import/_form_orders.html | 9 + templates/webpages/csv_import/form.html | 62 ++- templates/webpages/csv_import/report.html | 4 +- 8 files changed, 863 insertions(+), 27 deletions(-) create mode 100644 SL/Controller/CsvImport/BaseMulti.pm create mode 100644 SL/Controller/CsvImport/Order.pm create mode 100644 templates/webpages/csv_import/_form_orders.html diff --git a/SL/Controller/CsvImport.pm b/SL/Controller/CsvImport.pm index c3b9d99cc..a9df5adfe 100644 --- a/SL/Controller/CsvImport.pm +++ b/SL/Controller/CsvImport.pm @@ -15,6 +15,7 @@ use SL::Controller::CsvImport::CustomerVendor; use SL::Controller::CsvImport::Part; use SL::Controller::CsvImport::Shipto; use SL::Controller::CsvImport::Project; +use SL::Controller::CsvImport::Order; use SL::BackgroundJob::CsvImport; use SL::System::TaskServer; @@ -125,10 +126,21 @@ sub action_download_sample { my $file = SL::SessionFile->new($file_name, mode => '>', encoding => $self->profile->get('charset')); my $csv = Text::CSV_XS->new({ binary => 1, map { ( $_ => $self->profile->get($_) ) } qw(sep_char escape_char quote_char),}); - $csv->print($file->fh, [ map { $_->{name} } @{ $self->displayable_columns } ]); - $file->fh->print("\r\n"); - $csv->print($file->fh, [ map { $_->{description} } @{ $self->displayable_columns } ]); - $file->fh->print("\r\n"); + if ($self->worker->is_multiplexed) { + foreach my $ri (keys %{ $self->displayable_columns }) { + $csv->print($file->fh, [ map { $_->{name} } @{ $self->displayable_columns->{$ri} } ]); + $file->fh->print("\r\n"); + } + foreach my $ri (keys %{ $self->displayable_columns }) { + $csv->print($file->fh, [ map { $_->{description} } @{ $self->displayable_columns->{$ri} } ]); + $file->fh->print("\r\n"); + } + } else { + $csv->print($file->fh, [ map { $_->{name} } @{ $self->displayable_columns } ]); + $file->fh->print("\r\n"); + $csv->print($file->fh, [ map { $_->{description} } @{ $self->displayable_columns } ]); + $file->fh->print("\r\n"); + } $file->fh->close; @@ -158,20 +170,30 @@ sub action_report { : $page; $pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{cur}, $pages->{max}) } ]; + $self->{report_numheaders} = $self->{report}->numheaders; + my $first_row_header = 0; + my $last_row_header = $self->{report_numheaders} - 1; + my $first_row_data = $pages->{per_page} * ($pages->{cur}-1) + $self->{report_numheaders}; + my $last_row_data = min($pages->{per_page} * $pages->{cur}, $num_rows) + $self->{report_numheaders} - 1; $self->{display_rows} = [ - 0, - $pages->{per_page} * ($pages->{cur}-1) + 1 + $first_row_header + .. + $last_row_header, + $first_row_data .. - min($pages->{per_page} * $pages->{cur}, $num_rows) + $last_row_data ]; my @query = ( csv_import_report_id => $report_id, or => [ - row => 0, and => [ - row => { gt => $pages->{per_page} * ($pages->{cur}-1) }, - row => { le => $pages->{per_page} * $pages->{cur} }, + row => { ge => $first_row_header }, + row => { le => $last_row_header }, + ], + and => [ + row => { ge => $first_row_data }, + row => { le => $last_row_data }, ] ] ); @@ -199,7 +221,7 @@ sub check_auth { sub check_type { my ($self) = @_; - die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts customers_vendors addresses contacts projects); + die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts customers_vendors addresses contacts projects orders); $self->type($::form->{profile}->{type}); } @@ -242,6 +264,7 @@ sub render_inputs { : $self->type eq 'contacts' ? $::locale->text('CSV import: contacts') : $self->type eq 'parts' ? $::locale->text('CSV import: parts and services') : $self->type eq 'projects' ? $::locale->text('CSV import: projects') + : $self->type eq 'orders' ? $::locale->text('CSV import: orders') : die; if ($self->{type} eq 'parts') { @@ -363,6 +386,11 @@ sub profile_from_form { $::form->{settings}->{sellprice_adjustment} = $::form->parse_amount(\%::myconfig, $::form->{settings}->{sellprice_adjustment}); } + if ($self->type eq 'orders') { + $::form->{settings}->{order_column} = 'Order'; + $::form->{settings}->{item_column} = 'OrderItem'; + } + delete $::form->{profile}->{id}; $self->profile($existing_profile || SL::DB::CsvImportProfile->new(login => $::myconfig{login})); $self->profile->assign_attributes(%{ $::form->{profile} }); @@ -389,6 +417,16 @@ sub char_map { sub save_report { my ($self, $report_id) = @_; + if ($self->worker->is_multiplexed) { + return $self->save_report_multi($report_id); + } else { + return $self->save_report_single($report_id); + } +} + +sub save_report_single { + my ($self, $report_id) = @_; + $self->track_progress(phase => 'building report', progress => 0); my $clone_profile = $self->profile->clone_and_reset_deep; @@ -400,6 +438,7 @@ sub save_report { type => $self->type, file => '', numrows => scalar @{ $self->data }, + numheaders => 1, ); $report->save(cascade => 1) or die $report->db->error; @@ -455,6 +494,98 @@ sub save_report { return $report->id; } +sub save_report_multi { + my ($self, $report_id) = @_; + + $self->track_progress(phase => 'building report', progress => 0); + + my $clone_profile = $self->profile->clone_and_reset_deep; + $clone_profile->save; # weird bug. if this isn't saved before adding it to the report, it will default back to the last profile. + + my $report = SL::DB::CsvImportReport->new( + session_id => $::auth->create_or_refresh_session, + profile => $clone_profile, + type => $self->type, + file => '', + numrows => scalar @{ $self->data }, + numheaders => scalar @{ $self->worker->profile }, + ); + + $report->save(cascade => 1) or die $report->db->error; + + my $dbh = $::form->get_standard_dbh; + $dbh->begin_work; + + my $query = 'INSERT INTO csv_import_report_rows (csv_import_report_id, col, row, value) VALUES (?, ?, ?, ?)'; + my $query2 = 'INSERT INTO csv_import_report_status (csv_import_report_id, row, type, value) VALUES (?, ?, ?, ?)'; + + my $sth = $dbh->prepare($query); + my $sth2 = $dbh->prepare($query2); + + # save headers + my ($headers, $info_methods, $raw_methods, $methods); + + for my $i (0 .. $#{ $self->worker->profile }) { + my $row_ident = $self->worker->profile->[$i]->{row_ident}; + + for my $i (0 .. $#{ $self->info_headers->{$row_ident}->{headers} }) { + next unless $self->info_headers->{$row_ident}->{used}->{ $self->info_headers->{$row_ident}->{methods}->[$i] }; + push @{ $headers->{$row_ident} }, $self->info_headers->{$row_ident}->{headers}->[$i]; + push @{ $info_methods->{$row_ident} }, $self->info_headers->{$row_ident}->{methods}->[$i]; + } + for my $i (0 .. $#{ $self->headers->{$row_ident}->{headers} }) { + next unless $self->headers->{$row_ident}->{used}->{ $self->headers->{$row_ident}->{headers}->[$i] }; + push @{ $headers->{$row_ident} }, $self->headers->{$row_ident}->{headers}->[$i]; + push @{ $methods->{$row_ident} }, $self->headers->{$row_ident}->{methods}->[$i]; + } + + for my $i (0 .. $#{ $self->raw_data_headers->{$row_ident}->{headers} }) { + next unless $self->raw_data_headers->{$row_ident}->{used}->{ $self->raw_data_headers->{$row_ident}->{headers}->[$i] }; + push @{ $headers->{$row_ident} }, $self->raw_data_headers->{$row_ident}->{headers}->[$i]; + push @{ $raw_methods->{$row_ident} }, $self->raw_data_headers->{$row_ident}->{headers}->[$i]; + } + + } + + for my $i (0 .. $#{ $self->worker->profile }) { + my $row_ident = $self->worker->profile->[$i]->{row_ident}; + $sth->execute($report->id, $_, $i, $headers->{$row_ident}->[$_]) for 0 .. $#{ $headers->{$row_ident} }; + } + + # col offsets + my ($off1, $off2); + for my $i (0 .. $#{ $self->worker->profile }) { + my $row_ident = $self->worker->profile->[$i]->{row_ident}; + my $n_info_methods = $info_methods->{$row_ident} ? scalar @{ $info_methods->{$row_ident} } : 0; + my $n_methods = $methods->{$row_ident} ? scalar @{ $methods->{$row_ident} } : 0; + + $off1->{$row_ident} = $n_info_methods; + $off2->{$row_ident} = $off1->{$row_ident} + $n_methods; + } + + my $n_header_rows = scalar @{ $self->worker->profile }; + + for my $row (0 .. $#{ $self->data }) { + $self->track_progress(progress => $row / @{ $self->data } * 100) if $row % 1000 == 0; + my $data_row = $self->{data}[$row]; + my $row_ident = $data_row->{raw_data}{datatype}; + + my $o1 = $off1->{$row_ident}; + my $o2 = $off2->{$row_ident}; + + $sth->execute($report->id, $_, $row + $n_header_rows, $data_row->{info_data}{ $info_methods->{$row_ident}->[$_] }) for 0 .. $#{ $info_methods->{$row_ident} }; + $sth->execute($report->id, $o1 + $_, $row + $n_header_rows, $data_row->{object}->${ \ $methods->{$row_ident}->[$_] }) for 0 .. $#{ $methods->{$row_ident} }; + $sth->execute($report->id, $o2 + $_, $row + $n_header_rows, $data_row->{raw_data}{ $raw_methods->{$row_ident}->[$_] }) for 0 .. $#{ $raw_methods->{$row_ident} }; + + $sth2->execute($report->id, $row + $n_header_rows, 'information', $_) for @{ $data_row->{information} || [] }; + $sth2->execute($report->id, $row + $n_header_rows, 'errors', $_) for @{ $data_row->{errors} || [] }; + } + + $dbh->commit; + + return $report->id; +} + sub csv_file_name { my ($self) = @_; return "csv-import-" . $self->type . ".csv"; @@ -474,6 +605,7 @@ sub init_worker { : $self->{type} eq 'addresses' ? SL::Controller::CsvImport::Shipto->new(@args) : $self->{type} eq 'parts' ? SL::Controller::CsvImport::Part->new(@args) : $self->{type} eq 'projects' ? SL::Controller::CsvImport::Project->new(@args) + : $self->{type} eq 'orders' ? SL::Controller::CsvImport::Order->new(@args) : die "Program logic error"; } diff --git a/SL/Controller/CsvImport/Base.pm b/SL/Controller/CsvImport/Base.pm index 6390e7d04..09a488617 100644 --- a/SL/Controller/CsvImport/Base.pm +++ b/SL/Controller/CsvImport/Base.pm @@ -18,7 +18,7 @@ use parent qw(Rose::Object); use Rose::Object::MakeMethods::Generic ( scalar => [ qw(controller file csv test_run save_with_cascade) ], - 'scalar --get_set_init' => [ qw(profile displayable_columns existing_objects class manager_class cvar_columns all_cvar_configs all_languages payment_terms_by all_currencies default_currency_id all_vc vc_by) ], + 'scalar --get_set_init' => [ qw(is_multiplexed profile displayable_columns existing_objects class manager_class cvar_columns all_cvar_configs all_languages payment_terms_by all_currencies default_currency_id all_vc vc_by) ], ); sub run { @@ -311,6 +311,12 @@ sub init_manager_class { $self->manager_class("SL::DB::Manager::" . $1); } +sub init_is_multiplexed { + my ($self) = @_; + + $self->is_multiplexed('ARRAY' eq ref ($self->class) && scalar @{ $self->class } > 1); +} + sub check_objects { } diff --git a/SL/Controller/CsvImport/BaseMulti.pm b/SL/Controller/CsvImport/BaseMulti.pm new file mode 100644 index 000000000..2f31be6d7 --- /dev/null +++ b/SL/Controller/CsvImport/BaseMulti.pm @@ -0,0 +1,232 @@ +package SL::Controller::CsvImport::BaseMulti; + +use strict; + +use List::MoreUtils qw(pairwise); + +use SL::Helper::Csv; +use SL::DB::Customer; +use SL::DB::Language; +use SL::DB::PaymentTerm; +use SL::DB::Vendor; +use SL::DB::Contact; + +use parent qw(SL::Controller::CsvImport::Base); + +sub run { + my ($self, %params) = @_; + + $self->test_run($params{test_run}); + + $self->controller->track_progress(phase => 'parsing csv', progress => 0); + + my $profile = $self->profile; + + $self->csv(SL::Helper::Csv->new(file => $self->file->file_name, + encoding => $self->controller->profile->get('charset'), + profile => $profile, + ignore_unknown_columns => 1, + strict_profile => 1, + case_insensitive_header => 1, + map { ( $_ => $self->controller->profile->get($_) ) } qw(sep_char escape_char quote_char), + )); + + $self->controller->track_progress(progress => 10); + + my $old_numberformat = $::myconfig{numberformat}; + $::myconfig{numberformat} = $self->controller->profile->get('numberformat'); + + $self->csv->parse; + + $self->controller->track_progress(progress => 50); + + # bb: make sanity-check of it? + #if ($self->csv->is_multiplexed != $self->is_multiplexed) { + # die "multiplex controller on simplex data or vice versa"; + #} + + $self->controller->errors([ $self->csv->errors ]) if $self->csv->errors; + + return if ( !$self->csv->header || $self->csv->errors ); + + my $headers; + my $i = 0; + foreach my $header (@{ $self->csv->header }) { + + my $profile = $self->csv->profile->[$i]->{profile}; + my $row_ident = $self->csv->profile->[$i]->{row_ident}; + + my $h = { headers => [ grep { $profile->{$_} } @{ $header } ] }; + $h->{methods} = [ map { $profile->{$_} } @{ $h->{headers} } ]; + $h->{used} = { map { ($_ => 1) } @{ $h->{headers} } }; + + $headers->{$row_ident} = $h; + $i++; + } + + $self->controller->headers($headers); + + my $raw_data_headers; + my $info_headers; + foreach my $p (@{ $self->csv->profile }) { + $raw_data_headers->{ $p->{row_ident} } = { used => { }, headers => [ ] }; + $info_headers->{ $p->{row_ident} } = { used => { }, headers => [ ] }; + } + $self->controller->raw_data_headers($raw_data_headers); + $self->controller->info_headers($info_headers); + + + my @objects = $self->csv->get_objects; + $self->controller->track_progress(progress => 70); + + my @raw_data = @{ $self->csv->get_data }; + + $self->controller->track_progress(progress => 80); + + $self->controller->data([ pairwise { { object => $a, raw_data => $b, errors => [], information => [], info_data => {} } } @objects, @raw_data ]); + + $self->controller->track_progress(progress => 90); + + $self->check_objects; + if ( $self->controller->profile->get('duplicates', 'no_check') ne 'no_check' ) { + $self->check_std_duplicates(); + $self->check_duplicates(); + } + $self->fix_field_lengths; + + $self->controller->track_progress(progress => 100); + + $::myconfig{numberformat} = $old_numberformat; +} + +sub add_columns { + my ($self, $row_ident, @columns) = @_; + + my $h = $self->controller->headers->{$row_ident}; + + foreach my $column (grep { !$h->{used}->{$_} } @columns) { + $h->{used}->{$column} = 1; + push @{ $h->{methods} }, $column; + push @{ $h->{headers} }, $column; + } +} + +sub add_info_columns { + my ($self, $row_ident, @columns) = @_; + + my $h = $self->controller->info_headers->{$row_ident}; + + foreach my $column (grep { !$h->{used}->{ $_->{method} } } map { ref $_ eq 'HASH' ? $_ : { method => $_, header => $_ } } @columns) { + $h->{used}->{ $column->{method} } = 1; + push @{ $h->{methods} }, $column->{method}; + push @{ $h->{headers} }, $column->{header}; + } +} + +sub add_raw_data_columns { + my ($self, $row_ident, @columns) = @_; + + my $h = $self->controller->raw_data_headers->{$row_ident}; + + foreach my $column (grep { !$h->{used}->{$_} } @columns) { + $h->{used}->{$column} = 1; + push @{ $h->{headers} }, $column; + } +} + +sub add_cvar_raw_data_columns { + my ($self) = @_; + + map { $self->add_raw_data_columns($_) if exists $self->controller->data->[0]->{raw_data}->{$_} } @{ $self->cvar_columns }; +} + +sub init_profile { + my ($self) = @_; + + my @profile; + foreach my $class (@{ $self->class }) { + eval "require " . $class; + + my %unwanted = map { ( $_ => 1 ) } (qw(itime mtime), map { $_->name } @{ $class->meta->primary_key_columns }); + my %prof; + $prof{datatype} = ''; + for my $col ($class->meta->columns) { + next if $unwanted{$col}; + + my $name = $col->isa('Rose::DB::Object::Metadata::Column::Numeric') ? "$col\_as_number" + : $col->isa('Rose::DB::Object::Metadata::Column::Date') ? "$col\_as_date" + : $col->isa('Rose::DB::Object::Metadata::Column::Timestamp') ? "$col\_as_date" + : $col->name; + + $prof{$col} = $name; + } + + $prof{ 'cvar_' . $_->name } = '' for @{ $self->all_cvar_configs }; + + $class =~ m/^SL::DB::(.+)/; + push @profile, {'profile' => \%prof, 'class' => $class, 'row_ident' => $1}; + } + + \@profile; +} + +sub add_displayable_columns { + my ($self, $row_ident, @columns) = @_; + + my $dis_cols = $self->controller->displayable_columns || {}; + + my @cols = @{ $dis_cols->{$row_ident} || [] }; + my %ex_col_map = map { $_->{name} => $_ } @cols; + + foreach my $column (@columns) { + if ($ex_col_map{ $column->{name} }) { + @{ $ex_col_map{ $column->{name} } }{ keys %{ $column } } = @{ $column }{ keys %{ $column } }; + } else { + push @cols, $column; + } + } + + my $by_name_datatype_first = sub { 'datatype' eq $a->{name} ? -1 : + 'datatype' eq $b->{name} ? 1 : + $a->{name} cmp $b->{name} }; + $dis_cols->{$row_ident} = [ sort $by_name_datatype_first @cols ]; + + $self->controller->displayable_columns($dis_cols); +} + +sub setup_displayable_columns { + my ($self) = @_; + + foreach my $p (@{ $self->profile }) { + $self->add_displayable_columns($p->{row_ident}, map { { name => $_ } } keys %{ $p->{profile} }); + } +} + +sub add_cvar_columns_to_displayable_columns { + my ($self) = @_; + + $self->add_displayable_columns(map { { name => 'cvar_' . $_->name, + description => $::locale->text('#1 (custom variable)', $_->description) } } + @{ $self->all_cvar_configs }); +} + +sub init_existing_objects { + my ($self) = @_; + + eval "require " . $self->class; + $self->existing_objects($self->manager_class->get_all); +} + +sub init_class { + die "class not set"; +} + +sub init_manager_class { + my ($self) = @_; + + $self->class =~ m/^SL::DB::(.+)/; + $self->manager_class("SL::DB::Manager::" . $1); +} + +1; + diff --git a/SL/Controller/CsvImport/Order.pm b/SL/Controller/CsvImport/Order.pm new file mode 100644 index 000000000..81b9fd317 --- /dev/null +++ b/SL/Controller/CsvImport/Order.pm @@ -0,0 +1,416 @@ +package SL::Controller::CsvImport::Order; + + +use strict; + +use List::MoreUtils qw(any); + +use SL::Helper::Csv; +use SL::DB::Order; +use SL::DB::OrderItem; +use SL::DB::Part; +use SL::DB::PaymentTerm; +use SL::DB::Contact; + +use parent qw(SL::Controller::CsvImport::BaseMulti); + + +use Rose::Object::MakeMethods::Generic +( + 'scalar --get_set_init' => [ qw(settings languages_by all_parts parts_by all_contacts contacts_by) ], +); + + +sub init_class { + my ($self) = @_; + $self->class(['SL::DB::Order', 'SL::DB::OrderItem']); +} + + +sub init_settings { + my ($self) = @_; + + return { map { ( $_ => $self->controller->profile->get($_) ) } qw(order_column item_column) }; +} + + +sub init_profile { + my ($self) = @_; + + my $profile = $self->SUPER::init_profile; + + foreach my $p (@{ $profile }) { + my $prof = $p->{profile}; + if ($p->{row_ident} eq 'Order') { + # no need to handle + delete @{$prof}{qw(delivery_customer_id delivery_vendor_id proforma quotation amount netamount)}; + # handable, but not handled by now + } + if ($p->{row_ident} eq 'OrderItem') { + delete @{$prof}{qw(trans_id)}; + } + } + + return $profile; +} + + +sub setup_displayable_columns { + my ($self) = @_; + + $self->SUPER::setup_displayable_columns; + + $self->add_displayable_columns('Order', + { name => 'datatype', description => $::locale->text('Zeilenkennung') }, + { name => 'verify_amount', description => $::locale->text('Amount (for verification)') }, + { name => 'verify_netamount', description => $::locale->text('Net amount (for verification)') }, + { name => 'taxincluded', description => $::locale->text('Tax Included') }, + { name => 'customer', description => $::locale->text('Customer (name)') }, + { name => 'customernumber', description => $::locale->text('Customer Number') }, + { name => 'customer_id', description => $::locale->text('Customer (database ID)') }, + { name => 'vendor', description => $::locale->text('Vendor (name)') }, + { name => 'vendornumber', description => $::locale->text('Vendor Number') }, + { name => 'vendor_id', description => $::locale->text('Vendor (database ID)') }, + { name => 'language_id', description => $::locale->text('Language (database ID)') }, + { name => 'language', description => $::locale->text('Language (name)') }, + { name => 'payment_id', description => $::locale->text('Payment terms (database ID)') }, + { name => 'payment', description => $::locale->text('Payment terms (name)') }, + { name => 'taxzone_id', description => $::locale->text('Steuersatz') }, + { name => 'contact_id', description => $::locale->text('Contact Person (database ID)') }, + { name => 'contact', description => $::locale->text('Contact Person (name)') }, + ); + + $self->add_displayable_columns('OrderItem', + { name => 'parts_id', description => $::locale->text('Part (database ID)') }, + { name => 'partnumber', description => $::locale->text('Part Number') }, + ); +} + + +sub init_languages_by { + my ($self) = @_; + + return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $self->all_languages } } ) } qw(id description article_code) }; +} + +sub init_all_parts { + my ($self) = @_; + + return SL::DB::Manager::Part->get_all; +} + +sub init_parts_by { + my ($self) = @_; + + return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $self->all_parts } } ) } qw(id partnumber ean description) }; +} + +sub init_all_contacts { + my ($self) = @_; + + return SL::DB::Manager::Contact->get_all; +} + +sub init_contacts_by { + my ($self) = @_; + + my $cby = { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $self->all_contacts } } ) } qw(cp_id cp_name) }; + + # by customer/vendor id _and_ contact person id + $cby->{'cp_cv_id+cp_id'} = { map { ( $_->cp_cv_id . '+' . $_->cp_id => $_ ) } @{ $self->all_contacts } }; + + return $cby; +} + +sub check_objects { + my ($self) = @_; + + $self->controller->track_progress(phase => 'building data', progress => 0); + + my $i; + my $num_data = scalar @{ $self->controller->data }; + foreach my $entry (@{ $self->controller->data }) { + $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0; + + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'}) { + + my $vc_obj; + if (any { $entry->{raw_data}->{$_} } qw(customer customernumber customer_id)) { + $self->check_vc($entry, 'customer_id'); + $vc_obj = SL::DB::Customer->new(id => $entry->{object}->customer_id)->load if $entry->{object}->customer_id; + } elsif (any { $entry->{raw_data}->{$_} } qw(vendor vendornumber vendor_id)) { + $self->check_vc($entry, 'vendor_id'); + $vc_obj = SL::DB::Vendor->new(id => $entry->{object}->vendor_id)->load if $entry->{object}->vendor_id; + } else { + push @{ $entry->{errors} }, $::locale->text('Error: Customer/vendor missing'); + } + + $self->check_contact($entry); + $self->check_language($entry); + $self->check_payment($entry); + + if ($vc_obj) { + # copy from customer if not given + foreach (qw(payment_id language_id taxzone_id)) { + $entry->{object}->$_($vc_obj->$_) unless $entry->{object}->$_; + } + } + + # ToDo: salesman and emloyee by name + # salesman from customer or login if not given + if (!$entry->{object}->salesman) { + if ($vc_obj && $vc_obj->salesman_id) { + $entry->{object}->salesman(SL::DB::Manager::Employee->find_by(id => $vc_obj->salesman_id)); + } else { + $entry->{object}->salesman(SL::DB::Manager::Employee->find_by(login => $::myconfig{login})); + } + } + + # employee from login if not given + if (!$entry->{object}->employee_id) { + $entry->{object}->employee_id(SL::DB::Manager::Employee->find_by(login => $::myconfig{login})->id); + } + + } + } + + $self->add_info_columns($self->settings->{'order_column'}, + { header => $::locale->text('Customer/Vendor'), method => 'vc_name' }); + $self->add_columns($self->settings->{'order_column'}, + map { "${_}_id" } grep { exists $self->controller->data->[0]->{raw_data}->{$_} } qw(business payment)); + + + foreach my $entry (@{ $self->controller->data }) { + if ($entry->{raw_data}->{datatype} eq $self->settings->{'item_column'} && $entry->{object}->can('part')) { + + next if !$self->check_part($entry); + + my $part_obj = SL::DB::Part->new(id => $entry->{object}->parts_id)->load; + + # copy from part if not given + $entry->{object}->description($part_obj->description) unless $entry->{object}->description; + $entry->{object}->longdescription($part_obj->notes) unless $entry->{object}->longdescription; + $entry->{object}->unit($part_obj->unit) unless $entry->{object}->unit; + + # set to 0 if not given + $entry->{object}->discount(0) unless $entry->{object}->discount; + $entry->{object}->ship(0) unless $entry->{object}->ship; + } + } + + $self->add_info_columns($self->settings->{'item_column'}, + { header => $::locale->text('Part Number'), method => 'partnumber' }); + + # add orderitems to order + my $order_entry; + my @orderitems; + foreach my $entry (@{ $self->controller->data }) { + # search first Order + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'}) { + + # new order entry: add collected orderitems to the last one + if (defined $order_entry) { + $order_entry->{object}->orderitems(@orderitems); + @orderitems = (); + } + + $order_entry = $entry; + + } elsif ( defined $order_entry && $entry->{raw_data}->{datatype} eq $self->settings->{'item_column'} ) { + # collect orderitems to add to order (if they have no errors) + # ( add_orderitems does not work here if we want to call + # calculate_prices_and_taxes afterwards ... + # so collect orderitems and add them at once) + if (scalar @{ $entry->{errors} } == 0) { + push @orderitems, $entry->{object}; + } + } + } + # add last collected orderitems to last order + if ($order_entry) { + $order_entry->{object}->orderitems(@orderitems); + } + + # calculate prices and taxes + foreach my $entry (@{ $self->controller->data }) { + next if @{ $entry->{errors} }; + + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'}) { + + $entry->{object}->calculate_prices_and_taxes; + + $entry->{info_data}->{calc_amount} = $entry->{object}->amount_as_number; + $entry->{info_data}->{calc_netamount} = $entry->{object}->netamount_as_number; + } + } + + # If amounts are given, show calculated amounts as info and given amounts (verify_xxx). + # And throw an error if the differences are too big. + my $max_diff = 0.02; + my @to_verify = ( { column => 'amount', + raw_column => 'verify_amount', + info_header => 'Calc. Amount', + info_method => 'calc_amount', + err_msg => 'Amounts differ too much', + }, + { column => 'netamount', + raw_column => 'verify_netamount', + info_header => 'Calc. Net amount', + info_method => 'calc_netamount', + err_msg => 'Net amounts differ too much', + } ); + + foreach my $tv (@to_verify) { + if (exists $self->controller->data->[0]->{raw_data}->{ $tv->{raw_column} }) { + $self->add_raw_data_columns($self->settings->{'order_column'}, $tv->{raw_column}); + $self->add_info_columns($self->settings->{'order_column'}, + { header => $::locale->text($tv->{info_header}), method => $tv->{info_method} }); + } + + # check differences + foreach my $entry (@{ $self->controller->data }) { + next if @{ $entry->{errors} }; + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'}) { + next if !$entry->{raw_data}->{ $tv->{raw_column} }; + my $parsed_value = $::form->parse_amount(\%::myconfig, $entry->{raw_data}->{ $tv->{raw_column} }); + if (abs($entry->{object}->${ \$tv->{column} } - $parsed_value) > $max_diff) { + push @{ $entry->{errors} }, $::locale->text($tv->{err_msg}); + } + } + } + } + + # If order has errors set error for orderitems as well + my $order_entry; + foreach my $entry (@{ $self->controller->data }) { + # Search first order + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'}) { + $order_entry = $entry; + } elsif ( defined $order_entry + && $entry->{raw_data}->{datatype} eq $self->settings->{'item_column'} + && scalar @{ $order_entry->{errors} } > 0 ) { + push @{ $entry->{errors} }, $::locale->text('order not valid for this orderitem!'); + } + } + +} + + +sub check_language { + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + # Check whether or not language ID is valid. + if ($object->language_id && !$self->languages_by->{id}->{ $object->language_id }) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid language'); + return 0; + } + + # Map name to ID if given. + if (!$object->language_id && $entry->{raw_data}->{language}) { + my $language = $self->languages_by->{description}->{ $entry->{raw_data}->{language} } + || $self->languages_by->{article_code}->{ $entry->{raw_data}->{language} }; + + if (!$language) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid language'); + return 0; + } + + $object->language_id($language->id); + } + + if ($object->language_id) { + $entry->{info_data}->{language} = $self->languages_by->{id}->{ $object->language_id }->description; + } + + return 1; +} + +sub check_part { + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + # Check wether or non part ID is valid. + if ($object->parts_id && !$self->parts_by->{id}->{ $object->parts_id }) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid part'); + return 0; + } + + # Map number to ID if given. + if (!$object->parts_id && $entry->{raw_data}->{partnumber}) { + my $part = $self->parts_by->{partnumber}->{ $entry->{raw_data}->{partnumber} }; + if (!$part) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid part'); + return 0; + } + + $object->parts_id($part->id); + } + + if ($object->parts_id) { + $entry->{info_data}->{partnumber} = $self->parts_by->{id}->{ $object->parts_id }->partnumber; + } else { + push @{ $entry->{errors} }, $::locale->text('Error: Part not found'); + return 0; + } + + return 1; +} + +sub check_contact { + my ($self, $entry) = @_; + + my $object = $entry->{object}; + + # Check wether or non contact ID is valid. + if ($object->cp_id && !$self->contacts_by->{cp_id}->{ $object->cp_id }) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid contact'); + return 0; + } + + # Map number to ID if given. + if (!$object->cp_id && $entry->{raw_data}->{contact}) { + my $cp = $self->contacts_by->{cp_name}->{ $entry->{raw_data}->{contact} }; + if (!$cp) { + push @{ $entry->{errors} }, $::locale->text('Error: Invalid contact'); + return 0; + } + + $object->cp_id($cp->cp_id); + } + + # Check if the contact belongs to this customer/vendor. + if ($object->cp_id && $object->customer_id && !$self->contacts_by->{'cp_cv_id+cp_id'}) { + push @{ $entry->{errors} }, $::locale->text('Error: Contact not found for this customer/vendor'); + return 0; + } + + if ($object->cp_id) { + $entry->{info_data}->{contact} = $self->contacts_by->{cp_id}->{ $object->cp_id }->cp_name; + } + + return 1; +} + +sub save_objects { + my ($self, %params) = @_; + + # set order number and collect to save + my $objects_to_save; + foreach my $entry (@{ $self->controller->data }) { + next if @{ $entry->{errors} }; + + if ($entry->{raw_data}->{datatype} eq $self->settings->{'order_column'} && !$entry->{object}->ordnumber) { + $entry->{object}->create_trans_number; + } + + push @{ $objects_to_save }, $entry; + } + + $self->SUPER::save_objects(data => $objects_to_save); +} + + +1; diff --git a/menus/erp.ini b/menus/erp.ini index b0bff6139..ee7082499 100644 --- a/menus/erp.ini +++ b/menus/erp.ini @@ -636,6 +636,11 @@ module=controller.pl action=CsvImport/new profile.type=projects +[System--Import CSV--Orders] +module=controller.pl +action=CsvImport/new +profile.type=orders + [System--Templates] ACCESS=admin module=menu.pl diff --git a/templates/webpages/csv_import/_form_orders.html b/templates/webpages/csv_import/_form_orders.html new file mode 100644 index 000000000..221f22910 --- /dev/null +++ b/templates/webpages/csv_import/_form_orders.html @@ -0,0 +1,9 @@ +[% USE LxERP %] +[% USE L %] + + [%- LxERP.t8('Order/Item columns') %]: + + [% L.input_tag('settings.order_column', SELF.profile.get('order_column'), size => "5") %] + [% L.input_tag('settings.item_column', SELF.profile.get('item_column'), size => "5") %] + + diff --git a/templates/webpages/csv_import/form.html b/templates/webpages/csv_import/form.html index 9fd30ac1d..cb9d17e1a 100644 --- a/templates/webpages/csv_import/form.html +++ b/templates/webpages/csv_import/form.html @@ -57,19 +57,48 @@