From 67643d0386e7ad051009af639cada8a5ad60d1ea Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Mon, 5 Nov 2012 19:50:06 +0100 Subject: [PATCH] =?utf8?q?Csv=20Import=20in=20Datenbank=20zwischenspeicher?= =?utf8?q?n=20und=20rudiment=C3=A4rer=20Report.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/Controller/CsvImport.pm | 60 ++++++++++++++++++++- SL/DB/CsvImportProfile.pm | 11 ++++ SL/DB/CsvImportProfileSetting.pm | 8 +++ SL/DB/CsvImportReport.pm | 42 +++++++++++++++ SL/DB/CsvImportReportRow.pm | 13 +++++ SL/DB/CsvImportReportRowStatus.pm | 13 +++++ SL/DB/Helper/Mappings.pm | 3 ++ SL/DB/MetaSetup/CsvImportReport.pm | 31 +++++++++++ SL/DB/MetaSetup/CsvImportReportRow.pm | 31 +++++++++++ SL/DB/MetaSetup/CsvImportReportRowStatus.pm | 30 +++++++++++ sql/Pg-upgrade2/csv_import_report_cache.sql | 29 ++++++++++ templates/webpages/csv_import/report.html | 30 +++++++++++ 12 files changed, 300 insertions(+), 1 deletion(-) create mode 100644 SL/DB/CsvImportReport.pm create mode 100644 SL/DB/CsvImportReportRow.pm create mode 100644 SL/DB/CsvImportReportRowStatus.pm create mode 100644 SL/DB/MetaSetup/CsvImportReport.pm create mode 100644 SL/DB/MetaSetup/CsvImportReportRow.pm create mode 100644 SL/DB/MetaSetup/CsvImportReportRowStatus.pm create mode 100644 sql/Pg-upgrade2/csv_import_report_cache.sql create mode 100644 templates/webpages/csv_import/report.html diff --git a/SL/Controller/CsvImport.pm b/SL/Controller/CsvImport.pm index 34b794fdb..5171576eb 100644 --- a/SL/Controller/CsvImport.pm +++ b/SL/Controller/CsvImport.pm @@ -4,6 +4,7 @@ use strict; use SL::DB::Buchungsgruppe; use SL::DB::CsvImportProfile; +use SL::DB::CsvImportReport; use SL::DB::Unit; use SL::Helper::Flash; use SL::SessionFile; @@ -26,7 +27,7 @@ use Rose::Object::MakeMethods::Generic __PACKAGE__->run_before('check_auth'); __PACKAGE__->run_before('ensure_form_structure'); -__PACKAGE__->run_before('check_type'); +__PACKAGE__->run_before('check_type', except => [ qw(report) ]); __PACKAGE__->run_before('load_all_profiles'); # @@ -182,6 +183,8 @@ sub test_and_import { $self->num_imported(0); $worker->save_objects if !$params{test}; + $self->save_report; + $self->num_importable(scalar grep { !$_ } map { scalar @{ $_->{errors} } } @{ $self->data || [] }); $self->import_status($params{test} ? 'tested' : 'imported'); @@ -257,6 +260,61 @@ sub char_map { ); } +sub save_report { + my ($self, $report_id) = @_; + + 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 => '', + )->save(cascade => 1); + + 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 $sth = $dbh->prepare($query); + + # save headers + my @headers = ( + @{ $self->info_headers->{headers} || [] }, + @{ $self->headers->{headers} || [] }, + @{ $self->raw_data_headers->{headers} || [] }, + ); + my @info_methods = keys %{ $self->info_headers->{methods} || {} }; + my @methods = @{ $self->headers->{methods} || [] }; + my @raw_methods = keys %{ $self->raw_data_headers->{used} || {} }; + + $sth->execute($report->id, $_, 0, $headers[$_]) for 0 .. $#headers; + + # col offsets + my $o1 = @info_methods; + my $o2 = $o1 + @methods; + + for my $row (0 .. $#{ $self->data }) { + my $data_row = $self->{data}[$row]; + + $sth->execute($report->id, $_, $row + 1, $data_row->{info_data}{ $info_methods[$_] }) for 0 .. $#info_methods; + $sth->execute($report->id, $o1 + $_, $row + 1, $data_row->{object}->${ \ $methods[$_] }) for 0 .. $#methods; + $sth->execute($report->id, $o2 + $_, $row + 1, $data_row->{raw_data}{ $raw_methods[$_] }) for 0 .. $#raw_methods; + } + + $dbh->commit; +} + +sub action_report { + my ($self) = @_; + + $self->{report} = SL::DB::Manager::CsvImportReport->find_by(id => $::form->{id}); + + $self->render('csv_import/report'); +} + sub csv_file_name { my ($self) = @_; return "csv-import-" . $self->type . ".csv"; diff --git a/SL/DB/CsvImportProfile.pm b/SL/DB/CsvImportProfile.pm index ea7b4fe28..0b1a00cd1 100644 --- a/SL/DB/CsvImportProfile.pm +++ b/SL/DB/CsvImportProfile.pm @@ -5,6 +5,7 @@ use strict; use List::Util qw(first); use SL::DB::MetaSetup::CsvImportProfile; +use Rose::DB::Object::Helpers qw(clone_and_reset); __PACKAGE__->meta->add_relationship( settings => { @@ -94,6 +95,16 @@ sub _set_defaults { return $self; } +sub clone_and_reset_deep { + my ($self) = @_; + + my $clone = $self->clone_and_reset; + $clone->settings(map { $_->clone_and_reset } $self->settings); + $clone->name(''); + + return $clone; +} + # # hooks # diff --git a/SL/DB/CsvImportProfileSetting.pm b/SL/DB/CsvImportProfileSetting.pm index 6da5b34be..b1acf2431 100644 --- a/SL/DB/CsvImportProfileSetting.pm +++ b/SL/DB/CsvImportProfileSetting.pm @@ -6,8 +6,16 @@ package SL::DB::CsvImportProfileSetting; use strict; use SL::DB::MetaSetup::CsvImportProfileSetting; +use Rose::DB::Object::Helpers qw(clone); # Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. __PACKAGE__->meta->make_manager_class; +# Helpers' clone_and_reset also kills compund keys like in this case kay+id +sub clone_and_reset { + my $clone = $_[0]->clone; + $clone->id(undef); + return $clone; +} + 1; diff --git a/SL/DB/CsvImportReport.pm b/SL/DB/CsvImportReport.pm new file mode 100644 index 000000000..879c0404c --- /dev/null +++ b/SL/DB/CsvImportReport.pm @@ -0,0 +1,42 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::CsvImportReport; + +use strict; + +use SL::DB::MetaSetup::CsvImportReport; + +__PACKAGE__->meta->add_relationships( + rows => { + type => 'one to many', + class => 'SL::DB::CsvImportReportRow', + column_map => { id => 'csv_import_report_id' }, + }, +); + +__PACKAGE__->meta->make_manager_class; +__PACKAGE__->meta->initialize; + +sub folded_rows { + my ($self) = @_; + + $self->_fold_rows unless $self->{folded_rows}; + + return $self->{folded_rows}; +} + +sub _fold_rows { + my ($self) = @_; + + $self->{folded_rows} = []; + + for my $row_obj (@{ $self->rows }) { + $::lxdebug->dump(0, "adding", $row_obj->row . ' ' . $row_obj->col . ' ' . $row_obj->value); + $self->{folded_rows}->[ $row_obj->row ] ||= []; + $self->{folded_rows}->[ $row_obj->row ][ $row_obj->col ] = $row_obj->value; + $::lxdebug->dump(0, "now", $self->{folded_rows}); + } +} + +1; diff --git a/SL/DB/CsvImportReportRow.pm b/SL/DB/CsvImportReportRow.pm new file mode 100644 index 000000000..b8efb31e7 --- /dev/null +++ b/SL/DB/CsvImportReportRow.pm @@ -0,0 +1,13 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::CsvImportReportRow; + +use strict; + +use SL::DB::MetaSetup::CsvImportReportRow; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/CsvImportReportRowStatus.pm b/SL/DB/CsvImportReportRowStatus.pm new file mode 100644 index 000000000..495169185 --- /dev/null +++ b/SL/DB/CsvImportReportRowStatus.pm @@ -0,0 +1,13 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::CsvImportReportRowStatus; + +use strict; + +use SL::DB::MetaSetup::CsvImportReportRowStatus; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 8382f1f7c..138807437 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -50,6 +50,9 @@ my %lxoffice_package_names = ( contacts => 'contact', csv_import_profiles => 'csv_import_profile', csv_import_profile_settings => 'csv_import_profile_setting', + csv_import_reports => 'csv_import_report', + csv_import_report_rows => 'csv_import_report_row', + csv_import_report_row_status => 'csv_import_report_row_status', custom_variable_configs => 'custom_variable_config', custom_variables => 'custom_variable', custom_variables_validity => 'custom_variable_validity', diff --git a/SL/DB/MetaSetup/CsvImportReport.pm b/SL/DB/MetaSetup/CsvImportReport.pm new file mode 100644 index 000000000..cdc4f77e5 --- /dev/null +++ b/SL/DB/MetaSetup/CsvImportReport.pm @@ -0,0 +1,31 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::CsvImportReport; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'csv_import_reports', + + columns => [ + id => { type => 'serial', not_null => 1 }, + session_id => { type => 'text', not_null => 1 }, + profile_id => { type => 'integer', not_null => 1 }, + type => { type => 'text', not_null => 1 }, + file => { type => 'text', not_null => 1 }, + ], + + primary_key_columns => [ 'id' ], + + foreign_keys => [ + profile => { + class => 'SL::DB::CsvImportProfile', + key_columns => { profile_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/CsvImportReportRow.pm b/SL/DB/MetaSetup/CsvImportReportRow.pm new file mode 100644 index 000000000..5bc633722 --- /dev/null +++ b/SL/DB/MetaSetup/CsvImportReportRow.pm @@ -0,0 +1,31 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::CsvImportReportRow; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'csv_import_report_rows', + + columns => [ + id => { type => 'serial', not_null => 1 }, + csv_import_report_id => { type => 'integer', not_null => 1 }, + col => { type => 'integer', not_null => 1 }, + row => { type => 'integer', not_null => 1 }, + value => { type => 'text' }, + ], + + primary_key_columns => [ 'id' ], + + foreign_keys => [ + csv_import_report => { + class => 'SL::DB::CsvImportReport', + key_columns => { csv_import_report_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/CsvImportReportRowStatus.pm b/SL/DB/MetaSetup/CsvImportReportRowStatus.pm new file mode 100644 index 000000000..f0f55f797 --- /dev/null +++ b/SL/DB/MetaSetup/CsvImportReportRowStatus.pm @@ -0,0 +1,30 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::CsvImportReportRowStatus; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'csv_import_report_row_status', + + columns => [ + id => { type => 'serial', not_null => 1 }, + csv_import_report_row_id => { type => 'integer', not_null => 1 }, + type => { type => 'text', not_null => 1 }, + value => { type => 'text' }, + ], + + primary_key_columns => [ 'id' ], + + foreign_keys => [ + csv_import_report_row => { + class => 'SL::DB::CsvImportReportRow', + key_columns => { csv_import_report_row_id => 'id' }, + }, + ], +); + +1; +; diff --git a/sql/Pg-upgrade2/csv_import_report_cache.sql b/sql/Pg-upgrade2/csv_import_report_cache.sql new file mode 100644 index 000000000..440c34610 --- /dev/null +++ b/sql/Pg-upgrade2/csv_import_report_cache.sql @@ -0,0 +1,29 @@ +-- @tag: csv_import_report_cache +-- @description: Csv Import Cache +-- @depends: csv_import_profiles_2 +-- @encoding: utf-8 + +CREATE TABLE csv_import_reports ( + id SERIAL PRIMARY KEY, + session_id TEXT NOT NULL, + profile_id INTEGER NOT NULL REFERENCES csv_import_profiles(id), + type TEXT NOT NULL, + file TEXT NOT NULL +); + +CREATE TABLE csv_import_report_rows ( + id SERIAL PRIMARY KEY, + csv_import_report_id INTEGER NOT NULL REFERENCES csv_import_reports(id), + col INTEGER NOT NULL, + row INTEGER NOT NULL, + value TEXT +); + +CREATE TABLE csv_import_report_row_status ( + id SERIAL PRIMARY KEY, + csv_import_report_row_id INTEGER NOT NULL REFERENCES csv_import_report_rows(id), + type TEXT NOT NULL, + value TEXT +); + +ALTER TABLE csv_import_profiles DROP constraint "csv_import_profiles_name_key"; diff --git a/templates/webpages/csv_import/report.html b/templates/webpages/csv_import/report.html new file mode 100644 index 000000000..7965b092f --- /dev/null +++ b/templates/webpages/csv_import/report.html @@ -0,0 +1,30 @@ +[% USE HTML %] +[% USE LxERP %] +[% USE L %] + +

[%- LxERP.t8('Import result') %]

+ + +[%- FOREACH row = SELF.report.folded_rows %] + [%- IF loop.first %] + + [%- FOREACH value = row %] + + [%- END %] + + + [%- ELSE %] + + [%- FOREACH value = row %] + + [%- END %] + + + [%- END %] +[%- END %] + + +
[% value | html %][%- LxERP.t8('Notes') %]
[%- value | html %] + [%- FOREACH error = row.errors %][%- HTML.escape(error) %][% UNLESS loop.last %]
[%- END %][%- END %] + [%- FOREACH info = row.information %][% IF !loop.first || row.errors.size %]
[%- END %][%- HTML.escape(info) %][%- END %] +
-- 2.20.1