Bugzilla – Attachment 185324 Details for
Bug 38489
EDI should be updated to use the new FTP/SFTP Servers management page
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 38489: Integrate EDI into new FTP/SFTP management UI
Bug-38489-Integrate-EDI-into-new-FTPSFTP-managemen.patch (text/plain), 41.55 KB, created by
Jake Deery
on 2025-08-12 12:53:55 UTC
(
hide
)
Description:
Bug 38489: Integrate EDI into new FTP/SFTP management UI
Filename:
MIME Type:
Creator:
Jake Deery
Created:
2025-08-12 12:53:55 UTC
Size:
41.55 KB
patch
obsolete
>From 16eac59ac64b50cbf60338d38becf38b56214ac0 Mon Sep 17 00:00:00 2001 >From: Jake Deery <jake.deery@ptfs-europe.com> >Date: Fri, 29 Nov 2024 16:31:53 +0000 >Subject: [PATCH] Bug 38489: Integrate EDI into new FTP/SFTP management UI > >This patch integrates the EDI Transport.pm module into the new FTP/SFTP >management UI by tweaking the existing methods to look at the new SftpServer >object, instead of its own. > >Included also is a change to the database structure to make use of the new >sftp_servers table, as well as an atomic update to copy the schema changes, >and existing config to the new sftp_servers table. > >NOTE: To test this bug, you'll need to have access to a valid FTP/SFTP > server, within which you are able to generate exemplar quotes, orders, > and invoices. If you do not have access to this, contact me, and I will > try to arrange something. Thanks > >To test: >a) Apply Bug 35761, follow that test plan, and enable EDIFACT syspref >b) Notice how there is a duplication of FTP/SFTP details in Koha, between > EDI and FTP/SFTP Servers >c) APPLY THIS BUG, reset_all, and enable EDIFACT syspref >d) Create an FTP/SFTP Server under Koha Administration -> FTP/SFTP Server >e) Create an EDI account > 1) Notice how there is now two dropdowns for upload & download server > 2) Choose the same server for both options > 3) Set valid download & upload directories > 4) Enable quotes, orders, and invoices >f) Run ./misc/cronjobs/edi_cron.pl > 1) Check that a quote is now in Koha from the EDI vendor > 2) Confirm the quote via the staff client >g) Run ./misc/cronjobs/edi_cron.pl > 1) Check that the order is now on the FTP/SFTP server > 2) Generate an invoice on the FTP/SFTP server >h) Run ./misc/cronjobs/edi_cron.pl > 1) Confirm Koha has received the invoice > 2) Mark the invoice as paid in the staff client >i) Run ./misc/cronjobs/edi_cron.pl > 1) Check that the invoice is now on the FTP/SFTP server >j) SIGN OFF! >--- > Koha/Edifact/Transport.pm | 117 ++++++++++----- > Koha/Schema/Result/SftpServer.pm | 133 ++++++++++-------- > Koha/Schema/Result/VendorEdiAccount.pm | 90 ++++++------ > admin/edi_accounts.pl | 69 +++++---- > ...38489-move_edi_SFTP_data_to_SFTP_tables.pl | 115 +++++++++++++++ > installer/data/mysql/kohastructure.sql | 12 +- > .../prog/en/modules/admin/edi_accounts.tt | 101 ++++++++----- > t/db_dependent/Koha/Edifact/Transport.t | 4 +- > 8 files changed, 425 insertions(+), 216 deletions(-) > create mode 100755 installer/data/mysql/atomicupdate/bug_38489-move_edi_SFTP_data_to_SFTP_tables.pl > >diff --git a/Koha/Edifact/Transport.pm b/Koha/Edifact/Transport.pm >index ec59ae41109..70f414dd8dd 100644 >--- a/Koha/Edifact/Transport.pm >+++ b/Koha/Edifact/Transport.pm >@@ -32,7 +32,7 @@ use Net::SFTP::Foreign; > > use Koha::Database; > use Koha::DateUtils qw( dt_from_string ); >-use Koha::Encryption; >+use Koha::SFTP::Servers; > > sub new { > my ( $class, $account_id ) = @_; >@@ -62,29 +62,44 @@ sub download_messages { > my ( $self, $message_type ) = @_; > $self->{message_type} = $message_type; > >+ my $sftp_server_id = $self->{account}->download_sftp_server_id; >+ my $sftp_server_transport; >+ $sftp_server_transport = Koha::SFTP::Servers->find($sftp_server_id)->transport >+ if ($sftp_server_id); >+ > my @retrieved_files; > >- if ( $self->{account}->transport eq 'SFTP' ) { >- @retrieved_files = $self->sftp_download(); >- } elsif ( $self->{account}->transport eq 'FILE' ) { >- @retrieved_files = $self->file_download(); >- } else { # assume FTP >- @retrieved_files = $self->ftp_download(); >+ if ($message_type) { >+ if ( !$sftp_server_transport ) { >+ @retrieved_files = $self->file_download(); >+ } elsif ( $sftp_server_transport eq 'sftp' ) { >+ @retrieved_files = $self->sftp_download(); >+ } elsif ( $sftp_server_transport eq 'ftp' ) { >+ @retrieved_files = $self->ftp_download(); >+ } > } >+ > return @retrieved_files; > } > > sub upload_messages { > my ( $self, @messages ) = @_; >+ >+ my $sftp_server_id = $self->{account}->upload_sftp_server_id; >+ my $sftp_server_transport; >+ $sftp_server_transport = Koha::SFTP::Servers->find($sftp_server_id)->transport >+ if ($sftp_server_id); >+ > if (@messages) { >- if ( $self->{account}->transport eq 'SFTP' ) { >+ if ( !$sftp_server_transport ) { >+ $self->ftp_upload(@messages); >+ } elsif ( $sftp_server_transport eq 'sftp' ) { > $self->sftp_upload(@messages); >- } elsif ( $self->{account}->transport eq 'FILE' ) { >+ } elsif ( $sftp_server_transport eq 'ftp' ) { > $self->file_upload(@messages); >- } else { # assume FTP >- $self->ftp_upload(@messages); > } > } >+ > return; > } > >@@ -125,17 +140,23 @@ sub file_download { > sub sftp_download { > my $self = shift; > >+ my $sftp_server_id = $self->{account}->download_sftp_server_id; >+ return >+ unless $sftp_server_id; >+ my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id); >+ return >+ unless $sftp_server; >+ > my $file_ext = _get_file_ext( $self->{message_type} ); > > # C = ready to retrieve E = Edifact > my $msg_hash = $self->message_hash(); > my @downloaded_files; >- my $port = $self->{account}->download_port ? $self->{account}->download_port : '22'; > my $sftp = Net::SFTP::Foreign->new( >- host => $self->{account}->host, >- user => $self->{account}->username, >- password => Koha::Encryption->new->decrypt_hex( $self->{account}->password ), >- port => $port, >+ host => $sftp_server->host, >+ user => $sftp_server->user_name, >+ password => $sftp_server->plain_text_password, >+ port => $sftp_server->port, > timeout => 10, > ); > if ( $sftp->error ) { >@@ -215,25 +236,31 @@ sub ingest { > sub ftp_download { > my $self = shift; > >+ my $sftp_server_id = $self->{account}->download_sftp_server_id; >+ return >+ unless $sftp_server_id; >+ my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id); >+ return >+ unless $sftp_server; >+ > my $file_ext = _get_file_ext( $self->{message_type} ); > > # C = ready to retrieve E = Edifact > > my $msg_hash = $self->message_hash(); > my @downloaded_files; >- my $port = $self->{account}->download_port ? $self->{account}->download_port : '21'; >- my $ftp = Net::FTP->new( >- $self->{account}->host, >- Port => $port, >+ my $ftp = Net::FTP->new( >+ $sftp_server->host, >+ Port => $sftp_server->port, > Timeout => 10, >- Passive => 1 >+ Passive => $sftp_server->passiv, > ) > or return $self->_abort_download( > undef, >- "Cannot connect to " . $self->{account}->host . ": $EVAL_ERROR" >+ "Cannot connect to " . $sftp_server->host . ": " . $EVAL_ERROR > ); >- $ftp->login( $self->{account}->username, Koha::Encryption->new->decrypt_hex( $self->{account}->password ) ) >- or return $self->_abort_download( $ftp, "Cannot login: " . $ftp->message() ); >+ $ftp->login( $sftp_server->user_name, $sftp_server->plain_text_password ) >+ or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" ); > $ftp->cwd( $self->{account}->download_directory ) > or return $self->_abort_download( > $ftp, >@@ -269,19 +296,26 @@ sub ftp_download { > > sub ftp_upload { > my ( $self, @messages ) = @_; >- my $port = $self->{account}->upload_port ? $self->{account}->upload_port : '21'; >- my $ftp = Net::FTP->new( >- $self->{account}->host, >- Port => $port, >+ >+ my $sftp_server_id = $self->{account}->upload_sftp_server_id; >+ return >+ unless $sftp_server_id; >+ my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id); >+ return >+ unless $sftp_server; >+ >+ my $ftp = Net::FTP->new( >+ $sftp_server->host, >+ Port => $sftp_server->port, > Timeout => 10, >- Passive => 1 >+ Passive => $sftp_server->passiv > ) > or return $self->_abort_download( > undef, >- "Cannot connect to " . $self->{account}->host . ": $EVAL_ERROR" >+ "Cannot connect to " . $sftp_server->host . ": " . $EVAL_ERROR > ); >- $ftp->login( $self->{account}->username, Koha::Encryption->new->decrypt_hex( $self->{account}->password ) ) >- or return $self->_abort_download( $ftp, "Cannot login: " . $ftp->message() ); >+ $ftp->login( $sftp_server->user_name, $sftp_server->plain_text_password ) >+ or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" ); > $ftp->cwd( $self->{account}->upload_directory ) > or return $self->_abort_download( > $ftp, >@@ -310,15 +344,22 @@ sub ftp_upload { > > sub sftp_upload { > my ( $self, @messages ) = @_; >- my $port = $self->{account}->upload_port ? $self->{account}->upload_port : '22'; >+ >+ my $sftp_server_id = $self->{account}->upload_sftp_server_id; >+ return >+ unless $sftp_server_id; >+ my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id); >+ return >+ unless $sftp_server; >+ > my $sftp = Net::SFTP::Foreign->new( >- host => $self->{account}->host, >- user => $self->{account}->username, >- password => Koha::Encryption->new->decrypt_hex( $self->{account}->password ), >- port => $port, >+ host => $sftp_server->host, >+ user => $sftp_server->user_name, >+ password => $sftp_server->plain_text_password, >+ port => $sftp_server->port, > timeout => 10, > ); >- $sftp->die_on_error( "Cannot ssh to " . $self->{account}->host ); >+ $sftp->die_on_error( "Cannot ssh to " . $sftp_server->host ); > $sftp->setcwd( $self->{account}->upload_directory ) > or return $self->_abort_download( > $sftp, >diff --git a/Koha/Schema/Result/SftpServer.pm b/Koha/Schema/Result/SftpServer.pm >index 02d09609ac3..2554b4e60c0 100644 >--- a/Koha/Schema/Result/SftpServer.pm >+++ b/Koha/Schema/Result/SftpServer.pm >@@ -1,5 +1,4 @@ > use utf8; >- > package Koha::Schema::Result::SftpServer; > > # Created by DBIx::Class::Schema::Loader >@@ -56,7 +55,7 @@ __PACKAGE__->table("sftp_servers"); > extra: {list => ["ftp","sftp"]} > is_nullable: 0 > >-=head2 passiv >+=head2 passive > > data_type: 'tinyint' > default_value: 1 >@@ -110,49 +109,49 @@ __PACKAGE__->table("sftp_servers"); > =cut > > __PACKAGE__->add_columns( >- "id", >- { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, >- "name", >- { data_type => "varchar", is_nullable => 0, size => 80 }, >- "host", >- { >- data_type => "varchar", >- default_value => "localhost", >- is_nullable => 0, >- size => 80, >- }, >- "port", >- { data_type => "integer", default_value => 22, is_nullable => 0 }, >- "transport", >- { >- data_type => "enum", >- default_value => "sftp", >- extra => { list => [ "ftp", "sftp" ] }, >- is_nullable => 0, >- }, >- "passiv", >- { data_type => "tinyint", default_value => 1, is_nullable => 0 }, >- "user_name", >- { data_type => "varchar", is_nullable => 1, size => 80 }, >- "password", >- { data_type => "mediumtext", is_nullable => 1 }, >- "key_file", >- { data_type => "mediumtext", is_nullable => 1 }, >- "auth_mode", >- { >- data_type => "enum", >- default_value => "password", >- extra => { list => [ "password", "key_file", "noauth" ] }, >- is_nullable => 0, >- }, >- "download_directory", >- { data_type => "mediumtext", is_nullable => 1 }, >- "upload_directory", >- { data_type => "mediumtext", is_nullable => 1 }, >- "status", >- { data_type => "varchar", is_nullable => 1, size => 32 }, >- "debug", >- { data_type => "tinyint", default_value => 0, is_nullable => 0 }, >+ "id", >+ { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, >+ "name", >+ { data_type => "varchar", is_nullable => 0, size => 80 }, >+ "host", >+ { >+ data_type => "varchar", >+ default_value => "localhost", >+ is_nullable => 0, >+ size => 80, >+ }, >+ "port", >+ { data_type => "integer", default_value => 22, is_nullable => 0 }, >+ "transport", >+ { >+ data_type => "enum", >+ default_value => "sftp", >+ extra => { list => ["ftp", "sftp"] }, >+ is_nullable => 0, >+ }, >+ "passive", >+ { data_type => "tinyint", default_value => 1, is_nullable => 0 }, >+ "user_name", >+ { data_type => "varchar", is_nullable => 1, size => 80 }, >+ "password", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "key_file", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "auth_mode", >+ { >+ data_type => "enum", >+ default_value => "password", >+ extra => { list => ["password", "key_file", "noauth"] }, >+ is_nullable => 0, >+ }, >+ "download_directory", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "upload_directory", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "status", >+ { data_type => "varchar", is_nullable => 1, size => 32 }, >+ "debug", >+ { data_type => "tinyint", default_value => 0, is_nullable => 0 }, > ); > > =head1 PRIMARY KEY >@@ -167,20 +166,42 @@ __PACKAGE__->add_columns( > > __PACKAGE__->set_primary_key("id"); > >-# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-12-17 10:30:17 >-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Dnz0prvPxRoopZCdRaUtnA >+=head1 RELATIONS > >-__PACKAGE__->add_columns( >- '+passive' => { is_boolean => 1 }, >- '+debug' => { is_boolean => 1 }, >+=head2 vendor_edi_accounts_download_sftp_servers >+ >+Type: has_many >+ >+Related object: L<Koha::Schema::Result::VendorEdiAccount> >+ >+=cut >+ >+__PACKAGE__->has_many( >+ "vendor_edi_accounts_download_sftp_servers", >+ "Koha::Schema::Result::VendorEdiAccount", >+ { "foreign.download_sftp_server_id" => "self.id" }, >+ { cascade_copy => 0, cascade_delete => 0 }, > ); > >-sub koha_objects_class { >- 'Koha::SFTP::Servers'; >-} >+=head2 vendor_edi_accounts_upload_sftp_servers >+ >+Type: has_many >+ >+Related object: L<Koha::Schema::Result::VendorEdiAccount> >+ >+=cut >+ >+__PACKAGE__->has_many( >+ "vendor_edi_accounts_upload_sftp_servers", >+ "Koha::Schema::Result::VendorEdiAccount", >+ { "foreign.upload_sftp_server_id" => "self.id" }, >+ { cascade_copy => 0, cascade_delete => 0 }, >+); >+ >+ >+# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-08-12 12:49:53 >+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:1rBIrA/XFM7Yx78v3SKo6Q > >-sub koha_object_class { >- 'Koha::SFTP::Server'; >-} > >+# You can replace this text with custom code or comments, and it will be preserved on regeneration > 1; >diff --git a/Koha/Schema/Result/VendorEdiAccount.pm b/Koha/Schema/Result/VendorEdiAccount.pm >index 8a3d3fa79f5..c13355c03b1 100644 >--- a/Koha/Schema/Result/VendorEdiAccount.pm >+++ b/Koha/Schema/Result/VendorEdiAccount.pm >@@ -34,31 +34,16 @@ __PACKAGE__->table("vendor_edi_accounts"); > data_type: 'mediumtext' > is_nullable: 0 > >-=head2 host >- >- data_type: 'varchar' >- is_nullable: 1 >- size: 40 >- >-=head2 username >- >- data_type: 'varchar' >- is_nullable: 1 >- size: 40 >- >-=head2 password >- >- data_type: 'mediumtext' >- is_nullable: 1 >- >-=head2 upload_port >+=head2 download_sftp_server_id > > data_type: 'integer' >+ is_foreign_key: 1 > is_nullable: 1 > >-=head2 download_port >+=head2 upload_sftp_server_id > > data_type: 'integer' >+ is_foreign_key: 1 > is_nullable: 1 > > =head2 last_activity >@@ -103,13 +88,6 @@ __PACKAGE__->table("vendor_edi_accounts"); > is_nullable: 1 > size: 3 > >-=head2 transport >- >- data_type: 'varchar' >- default_value: 'FTP' >- is_nullable: 1 >- size: 6 >- > =head2 quotes_enabled > > data_type: 'tinyint' >@@ -160,16 +138,10 @@ __PACKAGE__->add_columns( > { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, > "description", > { data_type => "mediumtext", is_nullable => 0 }, >- "host", >- { data_type => "varchar", is_nullable => 1, size => 40 }, >- "username", >- { data_type => "varchar", is_nullable => 1, size => 40 }, >- "password", >- { data_type => "mediumtext", is_nullable => 1 }, >- "upload_port", >- { data_type => "integer", is_nullable => 1 }, >- "download_port", >- { data_type => "integer", is_nullable => 1 }, >+ "download_sftp_server_id", >+ { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, >+ "upload_sftp_server_id", >+ { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, > "last_activity", > { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, > "vendor_id", >@@ -184,8 +156,6 @@ __PACKAGE__->add_columns( > { data_type => "varchar", default_value => "EUR", is_nullable => 1, size => 3 }, > "id_code_qualifier", > { data_type => "varchar", default_value => 14, is_nullable => 1, size => 3 }, >- "transport", >- { data_type => "varchar", default_value => "FTP", is_nullable => 1, size => 6 }, > "quotes_enabled", > { data_type => "tinyint", default_value => 0, is_nullable => 0 }, > "invoices_enabled", >@@ -216,6 +186,26 @@ __PACKAGE__->set_primary_key("id"); > > =head1 RELATIONS > >+=head2 download_sftp_server >+ >+Type: belongs_to >+ >+Related object: L<Koha::Schema::Result::SftpServer> >+ >+=cut >+ >+__PACKAGE__->belongs_to( >+ "download_sftp_server", >+ "Koha::Schema::Result::SftpServer", >+ { id => "download_sftp_server_id" }, >+ { >+ is_deferrable => 1, >+ join_type => "LEFT", >+ on_delete => "RESTRICT", >+ on_update => "RESTRICT", >+ }, >+); >+ > =head2 edifact_messages > > Type: has_many >@@ -251,6 +241,26 @@ __PACKAGE__->belongs_to( > }, > ); > >+=head2 upload_sftp_server >+ >+Type: belongs_to >+ >+Related object: L<Koha::Schema::Result::SftpServer> >+ >+=cut >+ >+__PACKAGE__->belongs_to( >+ "upload_sftp_server", >+ "Koha::Schema::Result::SftpServer", >+ { id => "upload_sftp_server_id" }, >+ { >+ is_deferrable => 1, >+ join_type => "LEFT", >+ on_delete => "RESTRICT", >+ on_update => "RESTRICT", >+ }, >+); >+ > =head2 vendor > > Type: belongs_to >@@ -272,8 +282,8 @@ __PACKAGE__->belongs_to( > ); > > >-# Created by DBIx::Class::Schema::Loader v0.07049 @ 2024-04-19 16:25:44 >-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:jenUF3Wx7J7F5270mLQMbw >+# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-01-02 12:46:08 >+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ZXN7In7aiggTtjnBVHxCBQ > > __PACKAGE__->add_columns( > '+auto_orders' => { is_boolean => 1 }, >diff --git a/admin/edi_accounts.pl b/admin/edi_accounts.pl >index 410bcb8eaf4..0ab517ec48e 100755 >--- a/admin/edi_accounts.pl >+++ b/admin/edi_accounts.pl >@@ -24,7 +24,6 @@ use CGI; > use C4::Auth qw( get_template_and_user ); > use C4::Output qw( output_html_with_http_headers ); > use Koha::Database; >-use Koha::Encryption; > use Koha::Plugins; > > our $input = CGI->new(); >@@ -39,13 +38,11 @@ our ( $template, $loggedinuser, $cookie ) = get_template_and_user( > } > ); > >-my $crypt = Koha::Encryption->new; >- > my $op = $input->param('op'); > $op ||= 'display'; > > if ( $op eq 'acct_form' ) { >- show_account($crypt); >+ show_account(); > $template->param( acct_form => 1 ); > my @vendors = $schema->resultset('Aqbookseller')->search( > undef, >@@ -56,6 +53,15 @@ if ( $op eq 'acct_form' ) { > ); > $template->param( vendors => \@vendors ); > >+ my @sftp_servers = $schema->resultset('SftpServer')->search( >+ undef, >+ { >+ columns => [ 'name', 'host', 'port', 'id' ], >+ order_by => { -asc => 'name' }, >+ } >+ ); >+ $template->param( sftp_servers => \@sftp_servers ); >+ > if ( C4::Context->config("enable_plugins") ) { > my @plugins = Koha::Plugins->new()->GetPlugins( > { >@@ -65,35 +71,29 @@ if ( $op eq 'acct_form' ) { > $template->param( plugins => \@plugins ); > } > } elsif ( $op eq 'delete_confirm' ) { >- show_account($crypt); >+ show_account(); > $template->param( delete_confirm => 1 ); > } else { > if ( $op eq 'cud-save' ) { > > # validate & display >- my $id = $input->param('id'); >- my $password = scalar $input->param('password'); >- $password = $crypt->encrypt_hex($password); >+ my $id = $input->param('id'); > my $fields = { >- description => scalar $input->param('description'), >- host => scalar $input->param('host'), >- username => scalar $input->param('username'), >- password => $password, >- upload_port => scalar $input->param('upload_port') || $input->param('transport') eq 'FTP' ? 21 : 22, >- download_port => scalar $input->param('download_port') || $input->param('transport') eq 'FTP' ? 21 : 22, >- vendor_id => scalar $input->param('vendor_id'), >- upload_directory => scalar $input->param('upload_directory'), >- download_directory => scalar $input->param('download_directory'), >- san => scalar $input->param('san'), >- standard => scalar $input->param('standard'), >- transport => scalar $input->param('transport'), >- quotes_enabled => $input->param('quotes_enabled') ? 1 : 0, >- invoices_enabled => $input->param('invoices_enabled') ? 1 : 0, >- orders_enabled => $input->param('orders_enabled') ? 1 : 0, >- responses_enabled => $input->param('responses_enabled') ? 1 : 0, >- auto_orders => $input->param('auto_orders') ? 1 : 0, >- id_code_qualifier => scalar $input->param('id_code_qualifier'), >- plugin => scalar $input->param('plugin'), >+ description => scalar $input->param('description'), >+ upload_sftp_server_id => $input->param('upload_sftp_server_id') || undef, >+ download_sftp_server_id => $input->param('download_sftp_server_id') || undef, >+ vendor_id => scalar $input->param('vendor_id'), >+ upload_directory => scalar $input->param('upload_directory'), >+ download_directory => scalar $input->param('download_directory'), >+ san => scalar $input->param('san'), >+ standard => scalar $input->param('standard'), >+ quotes_enabled => $input->param('quotes_enabled') ? 1 : 0, >+ invoices_enabled => $input->param('invoices_enabled') ? 1 : 0, >+ orders_enabled => $input->param('orders_enabled') ? 1 : 0, >+ responses_enabled => $input->param('responses_enabled') ? 1 : 0, >+ auto_orders => $input->param('auto_orders') ? 1 : 0, >+ id_code_qualifier => scalar $input->param('id_code_qualifier'), >+ plugin => scalar $input->param('plugin'), > }; > > if ($id) { >@@ -159,14 +159,11 @@ sub get_account { > } > > sub show_account { >- my $crypt = shift; > my $acct_id = $input->param('id'); >- if ($acct_id) { >- my $acct = $schema->resultset('VendorEdiAccount')->find($acct_id); >- $acct->password( $crypt->decrypt_hex( $acct->password ) ); >- if ($acct) { >- $template->param( account => $acct ); >- } >- } >- return; >+ return unless $acct_id; >+ >+ my $acct = $schema->resultset('VendorEdiAccount')->find($acct_id); >+ return unless $acct; >+ >+ return $template->param( account => $acct ); > } >diff --git a/installer/data/mysql/atomicupdate/bug_38489-move_edi_SFTP_data_to_SFTP_tables.pl b/installer/data/mysql/atomicupdate/bug_38489-move_edi_SFTP_data_to_SFTP_tables.pl >new file mode 100755 >index 00000000000..03fde9207d6 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_38489-move_edi_SFTP_data_to_SFTP_tables.pl >@@ -0,0 +1,115 @@ >+use Modern::Perl; >+ >+return { >+ bug_number => "38489", >+ description => "Move SFTP data from EDI module to FTP/SFTP table", >+ up => sub { >+ my ($args) = @_; >+ my ( $dbh, $out ) = @$args{qw(dbh out)}; >+ >+ if ( TableExists('sftp_servers') ) { >+ if ( column_exists( 'vendor_edi_accounts', 'host') >+ && column_exists( 'vendor_edi_accounts', 'download_port' ) >+ && column_exists( 'vendor_edi_accounts', 'upload_port' ) >+ && column_exists( 'vendor_edi_accounts', 'transport' ) >+ && column_exists( 'vendor_edi_accounts', 'username' ) >+ && column_exists( 'vendor_edi_accounts', 'password' ) ) >+ { >+ ## copy edi vendor sftp servers to sftp_servers >+ $dbh->do( >+ q{ >+ INSERT IGNORE INTO `sftp_servers` (name,host,port,transport,user_name,password,download_directory,upload_directory) >+ SELECT description,host,download_port,LOWER(transport),username,password,download_directory,upload_directory >+ FROM vendor_edi_accounts >+ WHERE vendor_edi_accounts.download_port IS NOT NULL; >+ } >+ ); >+ say $out "Copied FTP/SFTP download servers from vendor_edi_accounts table"; >+ >+ $dbh->do( >+ q{ >+ INSERT IGNORE INTO `sftp_servers` (name,host,port,transport,user_name,password,download_directory,upload_directory) >+ SELECT description,host,upload_port,LOWER(transport),username,password,download_directory,upload_directory >+ FROM vendor_edi_accounts >+ WHERE vendor_edi_accounts.upload_port IS NOT NULL; >+ } >+ ); >+ say $out "Copied FTP/SFTP upload servers from vendor_edi_accounts table"; >+ >+ ## add foreign keys >+ unless ( column_exists( 'vendor_edi_accounts', 'download_sftp_server_id' ) >+ || column_exists( 'vendor_edi_accounts', 'upload_sftp_server_id' ) ) >+ { >+ $dbh->do( >+ q{ >+ ALTER TABLE `vendor_edi_accounts` >+ ADD COLUMN `download_sftp_server_id` int(11) DEFAULT NULL AFTER `description`, >+ ADD COLUMN `upload_sftp_server_id` int(11) DEFAULT NULL AFTER `download_sftp_server_id`; >+ } >+ ); >+ say $out "Added download_sftp_server_id column in vendor_edi_accounts table"; >+ say $out "Added upload_sftp_server_id column in vendor_edi_accounts table"; >+ } >+ unless ( foreign_key_exists( 'vendor_edi_accounts', 'vfk_download_sftp_server_id' ) >+ || foreign_key_exists( 'vendor_edi_accounts', 'vfk_upload_sftp_server_id' ) ) >+ { >+ $dbh->do( >+ q{ >+ ALTER TABLE `vendor_edi_accounts` >+ ADD CONSTRAINT `vfk_download_sftp_server_id` FOREIGN KEY (`download_sftp_server_id`) REFERENCES `sftp_servers` (`id`), >+ ADD CONSTRAINT `vfk_upload_sftp_server_id` FOREIGN KEY (`upload_sftp_server_id`) REFERENCES `sftp_servers` (`id`); >+ } >+ ); >+ say $out "Added vfk_download_sftp_server_id constraint in vendor_edi_accounts table"; >+ say $out "Added vfk_upload_sftp_server_id constraint in vendor_edi_accounts table"; >+ } >+ >+ ## set matching sftp server ids in new foreign keys >+ $dbh->do( >+ q{ >+ UPDATE IGNORE `vendor_edi_accounts`, `sftp_servers` >+ SET vendor_edi_accounts.download_sftp_server_id = sftp_servers.id >+ WHERE vendor_edi_accounts.host = sftp_servers.host >+ AND vendor_edi_accounts.download_port = sftp_servers.port >+ AND vendor_edi_accounts.transport = sftp_servers.transport >+ AND vendor_edi_accounts.download_directory = sftp_servers.download_directory >+ AND vendor_edi_accounts.upload_directory = sftp_servers.upload_directory; >+ } >+ ); >+ say $out "Matched sftp_server.id with vendor_edi_accounts.download_sftp_server_id"; >+ $dbh->do( >+ q{ >+ UPDATE IGNORE `vendor_edi_accounts`, `sftp_servers` >+ SET vendor_edi_accounts.upload_sftp_server_id = sftp_servers.id >+ WHERE vendor_edi_accounts.host = sftp_servers.host >+ AND vendor_edi_accounts.upload_port = sftp_servers.port >+ AND vendor_edi_accounts.transport = sftp_servers.transport >+ AND vendor_edi_accounts.download_directory = sftp_servers.download_directory >+ AND vendor_edi_accounts.upload_directory = sftp_servers.upload_directory; >+ } >+ ); >+ say $out "Matched sftp_server.id with vendor_edi_accounts.upload_sftp_server_id"; >+ >+ >+ ## drop useless columns from edi vendors >+ $dbh->do( >+ q{ >+ ALTER TABLE vendor_edi_accounts >+ DROP COLUMN `host`, >+ DROP COLUMN `username`, >+ DROP COLUMN `password`, >+ DROP COLUMN `download_port`, >+ DROP COLUMN `upload_port`, >+ DROP COLUMN `transport`; >+ } >+ ); >+ say $out "Dropped host column in vendor_edi_accounts table"; >+ say $out "Dropped username column in vendor_edi_accounts table"; >+ say $out "Dropped password column in vendor_edi_accounts table"; >+ say $out "Dropped download_port column in vendor_edi_accounts table"; >+ say $out "Dropped upload_port column in vendor_edi_accounts table"; >+ say $out "Dropped transport column in vendor_edi_accounts table"; >+ } >+ } >+ }, >+}; >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 43d895aff4d..ec34ca12bb9 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -6672,11 +6672,8 @@ DROP TABLE IF EXISTS `vendor_edi_accounts`; > CREATE TABLE `vendor_edi_accounts` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `description` mediumtext NOT NULL, >- `host` varchar(40) DEFAULT NULL, >- `username` varchar(40) DEFAULT NULL, >- `password` mediumtext DEFAULT NULL, >- `upload_port` int(11) DEFAULT NULL, >- `download_port` int(11) DEFAULT NULL, >+ `download_sftp_server_id` int(11) DEFAULT NULL, >+ `upload_sftp_server_id` int(11) DEFAULT NULL, > `last_activity` date DEFAULT NULL, > `vendor_id` int(11) DEFAULT NULL, > `download_directory` mediumtext DEFAULT NULL, >@@ -6684,7 +6681,6 @@ CREATE TABLE `vendor_edi_accounts` ( > `san` varchar(20) DEFAULT NULL, > `standard` varchar(3) DEFAULT 'EUR', > `id_code_qualifier` varchar(3) DEFAULT '14', >- `transport` varchar(6) DEFAULT 'FTP', > `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0, > `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0, > `orders_enabled` tinyint(1) NOT NULL DEFAULT 0, >@@ -6695,7 +6691,11 @@ CREATE TABLE `vendor_edi_accounts` ( > PRIMARY KEY (`id`), > KEY `vendorid` (`vendor_id`), > KEY `shipmentbudget` (`shipment_budget`), >+ KEY `vfk_download_sftp_server_id` (`download_sftp_server_id`), >+ KEY `vfk_upload_sftp_server_id` (`upload_sftp_server_id`), >+ CONSTRAINT `vfk_download_sftp_server_id` FOREIGN KEY (`download_sftp_server_id`) REFERENCES `sftp_servers` (`id`), > CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`), >+ CONSTRAINT `vfk_upload_sftp_server_id` FOREIGN KEY (`upload_sftp_server_id`) REFERENCES `sftp_servers` (`id`), > CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; > /*!40101 SET character_set_client = @saved_cs_client */; >diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_accounts.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_accounts.tt >index 6a207b8ba01..b2e97dac6a1 100644 >--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_accounts.tt >+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_accounts.tt >@@ -134,39 +134,31 @@ > <input type="text" name="description" id="description" size="20" value="[% account.description | html %]" /> > </li> > <li> >- [% transport_types = [ 'FTP', 'SFTP', 'FILE' ] %] >- <label for="transport">Transport: </label> >- <select name="transport" title="valid types of transport are FTP and SFTP" id="transport"> >- [% FOREACH transport_type IN transport_types %] >- [% IF transport_type == account.transport %] >- <option value="[% transport_type | html %]" selected="selected">[% transport_type | html %]</option> >+ <label for="download_sftp_server_id">Download FTP/SFTP server: </label> >+ <select name="download_sftp_server_id" id="download_sftp_server_id"> >+ <option value="">(do not use FTP/SFTP Server)</option> >+ [% FOREACH sftp_server IN sftp_servers %] >+ [% IF account.download_sftp_server.id == sftp_server.id %] >+ <option value="[% sftp_server.id | html %]" selected="selected">[% sftp_server.name | html %] ([% sftp_server.host | html %]:[% sftp_server.port | html %])</option> > [% ELSE %] >- <option value="[% transport_type | html %]">[% transport_type | html %]</option> >+ <option value="[% sftp_server.id | html %]"> [% sftp_server.name | html %]([% sftp_server.host | html %]:[% sftp_server.port | html %])</option> > [% END %] > [% END %] > </select> > </li> > <li> >- <label for="host">Remote host: </label> >- <input type="text" name="host" id="host" size="20" maxlength="40" value="[% account.host | html %]" /> >- </li> >- <li> >- <label for="username">Username: </label> >- <input type="text" name="username" id="username" size="20" maxlength="40" value="[% account.username | html %]" /> >- </li> >- <li> >- <label for="password">Password: </label> >- <input type="text" name="password" id="password" size="20" maxlength="40" value="[% account.password | html %]" /> >- </li> >- <li> >- <label for="upload_port">Upload port: </label> >- <input type="text" name="upload_port" id="upload_port" size="20" maxlength="40" value="[% account.upload_port | html %]" /> >- <div class="hint">The upload port will default to 22 (for SFTP) or 21 (for FTP) if not set.</div> >- </li> >- <li> >- <label for="download_port">Download port: </label> >- <input type="text" name="download_port" id="download_port" size="20" maxlength="40" value="[% account.download_port | html %]" /> >- <div class="hint">The download port will default to 22 (for SFTP) or 21 (for FTP) if not set.</div> >+ <label for="upload_sftp_server_id">Upload FTP/SFTP server: </label> >+ <select name="upload_sftp_server_id" id="upload_sftp_server_id"> >+ <option value="">(do not use FTP/SFTP Server)</option> >+ [% FOREACH sftp_server IN sftp_servers %] >+ [% IF account.upload_sftp_server.id == sftp_server.id %] >+ <option value="[% sftp_server.id | html %]" selected="selected">[% sftp_server.name | html %] ([% sftp_server.host | html %]:[% sftp_server.port | html %])</option> >+ [% ELSE %] >+ <option value="[% sftp_server.id | html %]">[% sftp_server.name | html %] ([% sftp_server.host | html %]:[% sftp_server.port | html %])</option> >+ [% END %] >+ [% END %] >+ </select> >+ <div class="hint">Configure under <a href="/cgi-bin/koha/admin/sftp_servers.pl">FTP/SFTP Servers</a></div> > </li> > <li> > <label for="download_directory">Download directory: </label> >@@ -300,12 +292,8 @@ > <th>ID</th> > <th>Vendor</th> > <th>Description</th> >- <th>Transport</th> >- <th>Remote host</th> >- <th>Username</th> >- <th>Password</th> >- <th>Upload port</th> >- <th>Download port</th> >+ <th>Upload FTP/SFTP server</th> >+ <th>Download FTP/SFTP server</th> > <th>Download directory</th> > <th>Upload directory</th> > <th>Qualifier</th> >@@ -325,12 +313,20 @@ > <td>[% account.id | html %]</td> > <td><a href="/cgi-bin/koha/acquisition/vendors/[% account.vendor_id | uri %]">[% account.vendor.name | html %]</a></td> > <td>[% account.description | html %]</td> >- <td>[% account.transport | html %]</td> >- <td>[% account.host | html %]</td> >- <td>[% account.username | html %]</td> >- <td>[% IF account.password %]*****[% END %]</td> >- <td>[% account.upload_port | html %]</td> >- <td>[% account.download_port | html %]</td> >+ <td> >+ [% IF (account.upload_sftp_server) %] >+ <a href="/cgi-bin/koha/admin/sftp_servers.pl?op=edit_form&sftp_server_id=[% account.upload_sftp_server.id | url %]">[% account.upload_sftp_server.name | html %]</a> >+ [% ELSE %] >+ <em>None set</em> >+ [% END %] >+ </td> >+ <td> >+ [% IF (account.download_sftp_server) %] >+ <a href="/cgi-bin/koha/admin/sftp_servers.pl?op=edit_form&sftp_server_id=[% account.download_sftp_server.id | url %]">[% account.download_sftp_server.name | html %]</a> >+ [% ELSE %] >+ <em>None set</em> >+ [% END %] >+ </td> > <td>[% account.download_directory | html %]</td> > <td>[% account.upload_directory | html %]</td> > <td> >@@ -403,6 +399,33 @@ > [% MACRO jsinclude BLOCK %] > [% Asset.js("js/admin-menu.js") | $raw %] > [% INCLUDE 'datatables.inc' %] >+ [% IF acct_form %] >+ <script> >+ document.addEventListener("DOMContentLoaded", function (event) { >+ $('select[name="download_sftp_server_id"]').on("change", function () { >+ var parent = $('select[name="download_sftp_server_id"]'); >+ var option = parent.find("option:selected").first(); >+ var value = option.val(); >+ var target = $('input[name="download_directory"]'); >+ >+ $.get("/api/v1/config/sftp_servers/" + value, function (data, status) { >+ if (data.download_directory) target.val(data.download_directory); >+ }); >+ }); >+ >+ $('select[name="upload_sftp_server_id"]').on("change", function () { >+ var parent = $('select[name="upload_sftp_server_id"]'); >+ var option = parent.find("option:selected").first(); >+ var value = option.val(); >+ var target = $('input[name="upload_directory"]'); >+ >+ $.get("/api/v1/config/sftp_servers/" + value, function (data, status) { >+ if (data.upload_directory) target.val(data.upload_directory); >+ }); >+ }); >+ }); >+ </script> >+ [% END %] > <script> > $(document).ready(function(){ > var table_settings = [% TablesSettings.GetTableSettings( 'admin', 'edi_accounts', 'edi_accounts_table', 'json' ) | $raw %]; >diff --git a/t/db_dependent/Koha/Edifact/Transport.t b/t/db_dependent/Koha/Edifact/Transport.t >index 3e4efbb45ac..a635b950ec7 100755 >--- a/t/db_dependent/Koha/Edifact/Transport.t >+++ b/t/db_dependent/Koha/Edifact/Transport.t >@@ -21,7 +21,9 @@ my $account = $builder->build( > { > source => 'VendorEdiAccount', > value => { >- description => 'test vendor', transport => 'FILE', >+ description => 'test vendor', >+ upload_sftp_server_id => undef, >+ download_sftp_server_id => undef, > } > } > ); >-- >2.43.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 38489
:
175204
|
175205
|
175206
|
175207
|
176118
|
176119
|
176161
| 185324