Csv Import in Datenbank zwischenspeichern und rudimentärer Report.

diff --git a/SL/Controller/CsvImport.pm b/SL/Controller/CsvImport.pm
index 34b794fdbe1fcbb98455e490d0a58742b0ef463e..5171576ebaba7752c286622281ea8ef8a65b519f 100644 (file)
--- 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::Buchungsgruppe;
 use SL::DB::CsvImportProfile;

+use SL::DB::CsvImportReport;

 use SL::DB::Unit;
 use SL::Helper::Flash;
 use SL::SessionFile;
 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_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');
 
 #
 __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->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');
 
   $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";
 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 ea7b4fe287cec7addf07618f12477e77294593fa..0b1a00cd1d07c97a0b82bffe686c4774e79e20c3 100644 (file)
--- 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 List::Util qw(first);
 
 use SL::DB::MetaSetup::CsvImportProfile;

+use Rose::DB::Object::Helpers qw(clone_and_reset);

 
 __PACKAGE__->meta->add_relationship(
   settings => {
 
 __PACKAGE__->meta->add_relationship(
   settings => {


@@ -94,6 +95,16 @@ sub _set_defaults {
   return $self;
 }
 
   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
 #
 #
 # hooks
 #

diff --git a/SL/DB/CsvImportProfileSetting.pm b/SL/DB/CsvImportProfileSetting.pm
index 6da5b34be7ab34332bb14e37331ef0184952928e..b1acf2431a843c9a1e019467badc63fcbbe7ac79 100644 (file)
--- 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 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;
 
 
 # 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;
 1;

diff --git a/SL/DB/CsvImportReport.pm b/SL/DB/CsvImportReport.pm
new file mode 100644 (file)
index 0000000..879c040
--- /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 (file)
index 0000000..b8efb31
--- /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 (file)
index 0000000..4951691
--- /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 8382f1f7c8d809ac5f1170c45edb17ac17fc2440..138807437f23d6d640e12ad95be0813f39016b6d 100644 (file)
--- 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',
   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',
   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 (file)
index 0000000..cdc4f77
--- /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 (file)
index 0000000..5bc6337
--- /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 (file)
index 0000000..f0f55f7
--- /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 (file)
index 0000000..440c346
--- /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 (file)
index 0000000..7965b09
--- /dev/null
+++ b/templates/webpages/csv_import/report.html
@@ -0,0 +1,30 @@
+[% USE HTML %]
+[% USE LxERP %]
+[% USE L %]
+
+ <h3>[%- LxERP.t8('Import result') %]</h3>
+
+ <table>
+[%- FOREACH row = SELF.report.folded_rows %]
+ [%- IF loop.first %]
+  <tr class="listheading">
+  [%- FOREACH value = row %]
+   <th>[% value | html %]</th>
+  [%- END  %]
+   <th>[%- LxERP.t8('Notes') %]</th>
+  </tr>
+ [%- ELSE %]
+  <tr class="[% IF row.errors.size %]redrow[% ELSE %]listrow[% END %][% loop.count % 2 %]">
+  [%- FOREACH value = row %]
+   <td>[%- value | html  %]</td>
+  [%- END %]
+   <td>
+    [%- FOREACH error = row.errors %][%- HTML.escape(error) %][% UNLESS loop.last %]<br>[%- END %][%- END %]
+    [%- FOREACH info  = row.information %][% IF !loop.first || row.errors.size %]<br>[%- END %][%- HTML.escape(info) %][%- END %]
+   </td>
+  </tr>
+ [%- END %]
+[%- END %]
+
+
+ </table>