View | Details | Raw Unified | Return to bug 38489
Collapse All | Expand All

(-)a/Koha/Edifact/Transport.pm (-38 / +75 lines)
Lines 32-38 use Net::SFTP::Foreign; Link Here
32
32
33
use Koha::Database;
33
use Koha::Database;
34
use Koha::DateUtils qw( dt_from_string );
34
use Koha::DateUtils qw( dt_from_string );
35
use Koha::Encryption;
35
use Koha::SFTP::Servers;
36
36
37
sub new {
37
sub new {
38
    my ( $class, $account_id ) = @_;
38
    my ( $class, $account_id ) = @_;
Lines 62-94 sub download_messages { Link Here
62
    my ( $self, $message_type ) = @_;
62
    my ( $self, $message_type ) = @_;
63
    $self->{message_type} = $message_type;
63
    $self->{message_type} = $message_type;
64
64
65
    my $sftp_server_id = $self->{account}->download_sftp_server_id;
66
    my $sftp_server_transport;
67
    $sftp_server_transport = Koha::SFTP::Servers->find($sftp_server_id)->transport
68
        if ( $sftp_server_id );
69
65
    my @retrieved_files;
70
    my @retrieved_files;
66
71
67
    if ( $self->{account}->transport eq 'SFTP' ) {
72
    if ($message_type) {
68
        @retrieved_files = $self->sftp_download();
73
        if ( !$sftp_server_transport ) {
69
    }
74
            @retrieved_files = $self->file_download();
70
    elsif ( $self->{account}->transport eq 'FILE' ) {
75
        } elsif ( $sftp_server_transport eq 'sftp' ) {
71
        @retrieved_files = $self->file_download();
76
            @retrieved_files = $self->sftp_download();
72
    }
77
        } elsif ( $sftp_server_transport eq 'ftp' ) {
73
    else {    # assume FTP
78
            @retrieved_files = $self->ftp_download();
74
        @retrieved_files = $self->ftp_download();
79
        }
75
    }
80
    }
81
76
    return @retrieved_files;
82
    return @retrieved_files;
77
}
83
}
78
84
79
sub upload_messages {
85
sub upload_messages {
80
    my ( $self, @messages ) = @_;
86
    my ( $self, @messages ) = @_;
87
88
    my $sftp_server_id = $self->{account}->upload_sftp_server_id;
89
    my $sftp_server_transport;
90
    $sftp_server_transport = Koha::SFTP::Servers->find($sftp_server_id)->transport
91
        if ( $sftp_server_id );
92
81
    if (@messages) {
93
    if (@messages) {
82
        if ( $self->{account}->transport eq 'SFTP' ) {
94
        if ( !$sftp_server_transport ) {
95
            $self->ftp_upload(@messages);
96
        } elsif ( $sftp_server_transport eq 'sftp' ) {
83
            $self->sftp_upload(@messages);
97
            $self->sftp_upload(@messages);
84
        }
98
        } elsif ( $sftp_server_transport eq 'ftp' ) {
85
        elsif ( $self->{account}->transport eq 'FILE' ) {
86
            $self->file_upload(@messages);
99
            $self->file_upload(@messages);
87
        }
100
        }
88
        else {    # assume FTP
89
            $self->ftp_upload(@messages);
90
        }
91
    }
101
    }
102
92
    return;
103
    return;
93
}
104
}
94
105
Lines 131-147 sub file_download { Link Here
131
sub sftp_download {
142
sub sftp_download {
132
    my $self = shift;
143
    my $self = shift;
133
144
145
    my $sftp_server_id = $self->{account}->download_sftp_server_id;
146
    return
147
        unless $sftp_server_id;
148
    my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id);
149
    return
150
        unless $sftp_server;
151
134
    my $file_ext = _get_file_ext( $self->{message_type} );
152
    my $file_ext = _get_file_ext( $self->{message_type} );
135
153
136
    # C = ready to retrieve E = Edifact
154
    # C = ready to retrieve E = Edifact
137
    my $msg_hash = $self->message_hash();
155
    my $msg_hash = $self->message_hash();
138
    my @downloaded_files;
156
    my @downloaded_files;
139
    my $port = $self->{account}->download_port ? $self->{account}->download_port : '22';
140
    my $sftp = Net::SFTP::Foreign->new(
157
    my $sftp = Net::SFTP::Foreign->new(
141
        host     => $self->{account}->host,
158
        host     => $sftp_server->host,
142
        user     => $self->{account}->username,
159
        user     => $sftp_server->user_name,
143
        password => Koha::Encryption->new->decrypt_hex($self->{account}->password),
160
        password => $sftp_server->plain_text_password,
144
        port     => $port,
161
        port     => $sftp_server->port,
145
        timeout  => 10,
162
        timeout  => 10,
146
    );
163
    );
147
    if ( $sftp->error ) {
164
    if ( $sftp->error ) {
Lines 213-236 sub ingest { Link Here
213
sub ftp_download {
230
sub ftp_download {
214
    my $self = shift;
231
    my $self = shift;
215
232
233
    my $sftp_server_id = $self->{account}->download_sftp_server_id;
234
    return
235
        unless $sftp_server_id;
236
    my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id);
237
    return
238
        unless $sftp_server;
239
216
    my $file_ext = _get_file_ext( $self->{message_type} );
240
    my $file_ext = _get_file_ext( $self->{message_type} );
217
241
218
    # C = ready to retrieve E = Edifact
242
    # C = ready to retrieve E = Edifact
219
243
220
    my $msg_hash = $self->message_hash();
244
    my $msg_hash = $self->message_hash();
221
    my @downloaded_files;
245
    my @downloaded_files;
222
    my $port = $self->{account}->download_port ? $self->{account}->download_port : '21';
223
    my $ftp  = Net::FTP->new(
246
    my $ftp  = Net::FTP->new(
224
        $self->{account}->host,
247
        $sftp_server->host,
225
        Port    => $port,
248
        Port    => $sftp_server->port,
226
        Timeout => 10,
249
        Timeout => 10,
227
        Passive => 1
250
        Passive => $sftp_server->passiv,
228
        )
251
        )
229
        or return $self->_abort_download(
252
        or return $self->_abort_download(
230
        undef,
253
        undef,
231
        "Cannot connect to $self->{account}->host: $EVAL_ERROR"
254
        "Cannot connect to " . $sftp_server->host . ": " . $EVAL_ERROR
232
        );
255
        );
233
    $ftp->login( $self->{account}->username, Koha::Encryption->new->decrypt_hex($self->{account}->password) )
256
    $ftp->login( $sftp_server->user_name, $sftp_server->plain_text_password )
234
      or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" );
257
      or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" );
235
    $ftp->cwd( $self->{account}->download_directory )
258
    $ftp->cwd( $self->{account}->download_directory )
236
      or return $self->_abort_download( $ftp,
259
      or return $self->_abort_download( $ftp,
Lines 264-281 sub ftp_download { Link Here
264
287
265
sub ftp_upload {
288
sub ftp_upload {
266
    my ( $self, @messages ) = @_;
289
    my ( $self, @messages ) = @_;
267
    my $port = $self->{account}->upload_port ? $self->{account}->upload_port : '21';
290
291
    my $sftp_server_id = $self->{account}->upload_sftp_server_id;
292
    return
293
        unless $sftp_server_id;
294
    my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id);
295
    return
296
        unless $sftp_server;
297
268
    my $ftp  = Net::FTP->new(
298
    my $ftp  = Net::FTP->new(
269
        $self->{account}->host,
299
        $sftp_server->host,
270
        Port    => $port,
300
        Port    => $sftp_server->port,
271
        Timeout => 10,
301
        Timeout => 10,
272
        Passive => 1
302
        Passive => $sftp_server->passiv
273
        )
303
        )
274
        or return $self->_abort_download(
304
        or return $self->_abort_download(
275
        undef,
305
        undef,
276
        "Cannot connect to $self->{account}->host: $EVAL_ERROR"
306
        "Cannot connect to " . $sftp_server->host . ": " . $EVAL_ERROR
277
        );
307
        );
278
    $ftp->login( $self->{account}->username, Koha::Encryption->new->decrypt_hex($self->{account}->password) )
308
    $ftp->login( $sftp_server->user_name, $sftp_server->plain_text_password )
279
      or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" );
309
      or return $self->_abort_download( $ftp, "Cannot login: $ftp->message()" );
280
    $ftp->cwd( $self->{account}->upload_directory )
310
    $ftp->cwd( $self->{account}->upload_directory )
281
      or return $self->_abort_download( $ftp,
311
      or return $self->_abort_download( $ftp,
Lines 303-317 sub ftp_upload { Link Here
303
333
304
sub sftp_upload {
334
sub sftp_upload {
305
    my ( $self, @messages ) = @_;
335
    my ( $self, @messages ) = @_;
306
    my $port = $self->{account}->upload_port ? $self->{account}->upload_port : '22';
336
337
    my $sftp_server_id = $self->{account}->upload_sftp_server_id;
338
    return
339
        unless $sftp_server_id;
340
    my $sftp_server = Koha::SFTP::Servers->find($sftp_server_id);
341
    return
342
        unless $sftp_server;
343
307
    my $sftp = Net::SFTP::Foreign->new(
344
    my $sftp = Net::SFTP::Foreign->new(
308
        host     => $self->{account}->host,
345
        host     => $sftp_server->host,
309
        user     => $self->{account}->username,
346
        user     => $sftp_server->user_name,
310
        password => Koha::Encryption->new->decrypt_hex($self->{account}->password),
347
        password => $sftp_server->plain_text_password,
311
        port     => $port,
348
        port     => $sftp_server->port,
312
        timeout  => 10,
349
        timeout  => 10,
313
    );
350
    );
314
    $sftp->die_on_error("Cannot ssh to $self->{account}->host");
351
    $sftp->die_on_error("Cannot ssh to " . $sftp_server->host);
315
    $sftp->setcwd( $self->{account}->upload_directory )
352
    $sftp->setcwd( $self->{account}->upload_directory )
316
      or return $self->_abort_download( $sftp,
353
      or return $self->_abort_download( $sftp,
317
        "Cannot change remote dir : " . $sftp->error );
354
        "Cannot change remote dir : " . $sftp->error );
(-)a/Koha/Schema/Result/SftpServer.pm (-2 / +34 lines)
Lines 146-154 __PACKAGE__->add_columns( Link Here
146
146
147
__PACKAGE__->set_primary_key("id");
147
__PACKAGE__->set_primary_key("id");
148
148
149
=head1 RELATIONS
149
150
150
# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-12-02 11:43:19
151
=head2 vendor_edi_accounts_download_sftp_servers
151
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:K1+15gYfWmqYKldm0NdgGQ
152
153
Type: has_many
154
155
Related object: L<Koha::Schema::Result::VendorEdiAccount>
156
157
=cut
158
159
__PACKAGE__->has_many(
160
  "vendor_edi_accounts_download_sftp_servers",
161
  "Koha::Schema::Result::VendorEdiAccount",
162
  { "foreign.download_sftp_server_id" => "self.id" },
163
  { cascade_copy => 0, cascade_delete => 0 },
164
);
165
166
=head2 vendor_edi_accounts_upload_sftp_servers
167
168
Type: has_many
169
170
Related object: L<Koha::Schema::Result::VendorEdiAccount>
171
172
=cut
173
174
__PACKAGE__->has_many(
175
  "vendor_edi_accounts_upload_sftp_servers",
176
  "Koha::Schema::Result::VendorEdiAccount",
177
  { "foreign.upload_sftp_server_id" => "self.id" },
178
  { cascade_copy => 0, cascade_delete => 0 },
179
);
180
181
182
# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-12-02 15:04:20
183
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:obHhjrZvl6IUrRYeUaIn4A
152
184
153
__PACKAGE__->add_columns(
185
__PACKAGE__->add_columns(
154
    '+passiv'     => { is_boolean => 1 },
186
    '+passiv'     => { is_boolean => 1 },
(-)a/Koha/Schema/Result/VendorEdiAccount.pm (-40 / +50 lines)
Lines 34-64 __PACKAGE__->table("vendor_edi_accounts"); Link Here
34
  data_type: 'mediumtext'
34
  data_type: 'mediumtext'
35
  is_nullable: 0
35
  is_nullable: 0
36
36
37
=head2 host
37
=head2 download_sftp_server_id
38
39
  data_type: 'varchar'
40
  is_nullable: 1
41
  size: 40
42
43
=head2 username
44
45
  data_type: 'varchar'
46
  is_nullable: 1
47
  size: 40
48
49
=head2 password
50
51
  data_type: 'mediumtext'
52
  is_nullable: 1
53
54
=head2 upload_port
55
38
56
  data_type: 'integer'
39
  data_type: 'integer'
40
  is_foreign_key: 1
57
  is_nullable: 1
41
  is_nullable: 1
58
42
59
=head2 download_port
43
=head2 upload_sftp_server_id
60
44
61
  data_type: 'integer'
45
  data_type: 'integer'
46
  is_foreign_key: 1
62
  is_nullable: 1
47
  is_nullable: 1
63
48
64
=head2 last_activity
49
=head2 last_activity
Lines 103-115 __PACKAGE__->table("vendor_edi_accounts"); Link Here
103
  is_nullable: 1
88
  is_nullable: 1
104
  size: 3
89
  size: 3
105
90
106
=head2 transport
107
108
  data_type: 'varchar'
109
  default_value: 'FTP'
110
  is_nullable: 1
111
  size: 6
112
113
=head2 quotes_enabled
91
=head2 quotes_enabled
114
92
115
  data_type: 'tinyint'
93
  data_type: 'tinyint'
Lines 160-175 __PACKAGE__->add_columns( Link Here
160
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
138
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
161
  "description",
139
  "description",
162
  { data_type => "mediumtext", is_nullable => 0 },
140
  { data_type => "mediumtext", is_nullable => 0 },
163
  "host",
141
  "download_sftp_server_id",
164
  { data_type => "varchar", is_nullable => 1, size => 40 },
142
  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
165
  "username",
143
  "upload_sftp_server_id",
166
  { data_type => "varchar", is_nullable => 1, size => 40 },
144
  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
167
  "password",
168
  { data_type => "mediumtext", is_nullable => 1 },
169
  "upload_port",
170
  { data_type => "integer", is_nullable => 1 },
171
  "download_port",
172
  { data_type => "integer", is_nullable => 1 },
173
  "last_activity",
145
  "last_activity",
174
  { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 },
146
  { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 },
175
  "vendor_id",
147
  "vendor_id",
Lines 184-191 __PACKAGE__->add_columns( Link Here
184
  { data_type => "varchar", default_value => "EUR", is_nullable => 1, size => 3 },
156
  { data_type => "varchar", default_value => "EUR", is_nullable => 1, size => 3 },
185
  "id_code_qualifier",
157
  "id_code_qualifier",
186
  { data_type => "varchar", default_value => 14, is_nullable => 1, size => 3 },
158
  { data_type => "varchar", default_value => 14, is_nullable => 1, size => 3 },
187
  "transport",
188
  { data_type => "varchar", default_value => "FTP", is_nullable => 1, size => 6 },
189
  "quotes_enabled",
159
  "quotes_enabled",
190
  { data_type => "tinyint", default_value => 0, is_nullable => 0 },
160
  { data_type => "tinyint", default_value => 0, is_nullable => 0 },
191
  "invoices_enabled",
161
  "invoices_enabled",
Lines 216-221 __PACKAGE__->set_primary_key("id"); Link Here
216
186
217
=head1 RELATIONS
187
=head1 RELATIONS
218
188
189
=head2 download_sftp_server
190
191
Type: belongs_to
192
193
Related object: L<Koha::Schema::Result::SftpServer>
194
195
=cut
196
197
__PACKAGE__->belongs_to(
198
  "download_sftp_server",
199
  "Koha::Schema::Result::SftpServer",
200
  { id => "download_sftp_server_id" },
201
  {
202
    is_deferrable => 1,
203
    join_type     => "LEFT",
204
    on_delete     => "RESTRICT",
205
    on_update     => "RESTRICT",
206
  },
207
);
208
219
=head2 edifact_messages
209
=head2 edifact_messages
220
210
221
Type: has_many
211
Type: has_many
Lines 251-256 __PACKAGE__->belongs_to( Link Here
251
  },
241
  },
252
);
242
);
253
243
244
=head2 upload_sftp_server
245
246
Type: belongs_to
247
248
Related object: L<Koha::Schema::Result::SftpServer>
249
250
=cut
251
252
__PACKAGE__->belongs_to(
253
  "upload_sftp_server",
254
  "Koha::Schema::Result::SftpServer",
255
  { id => "upload_sftp_server_id" },
256
  {
257
    is_deferrable => 1,
258
    join_type     => "LEFT",
259
    on_delete     => "RESTRICT",
260
    on_update     => "RESTRICT",
261
  },
262
);
263
254
=head2 vendor
264
=head2 vendor
255
265
256
Type: belongs_to
266
Type: belongs_to
Lines 272-279 __PACKAGE__->belongs_to( Link Here
272
);
282
);
273
283
274
284
275
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2024-04-19 16:25:44
285
# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-12-02 15:04:20
276
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:jenUF3Wx7J7F5270mLQMbw
286
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:rej2ARtjUUHS8AB4H6KuUw
277
287
278
288
279
# You can replace this text with custom code or comments, and it will be preserved on regeneration
289
# You can replace this text with custom code or comments, and it will be preserved on regeneration
(-)a/admin/edi_accounts.pl (-35 / +32 lines)
Lines 24-30 use CGI; Link Here
24
use C4::Auth qw( get_template_and_user );
24
use C4::Auth qw( get_template_and_user );
25
use C4::Output qw( output_html_with_http_headers );
25
use C4::Output qw( output_html_with_http_headers );
26
use Koha::Database;
26
use Koha::Database;
27
use Koha::Encryption;
28
use Koha::Plugins;
27
use Koha::Plugins;
29
28
30
our $input = CGI->new();
29
our $input = CGI->new();
Lines 39-51 our ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
39
    }
38
    }
40
);
39
);
41
40
42
my $crypt = Koha::Encryption->new;
43
44
my $op = $input->param('op');
41
my $op = $input->param('op');
45
$op ||= 'display';
42
$op ||= 'display';
46
43
47
if ( $op eq 'acct_form' ) {
44
if ( $op eq 'acct_form' ) {
48
    show_account($crypt);
45
    show_account();
49
    $template->param( acct_form => 1 );
46
    $template->param( acct_form => 1 );
50
    my @vendors = $schema->resultset('Aqbookseller')->search(
47
    my @vendors = $schema->resultset('Aqbookseller')->search(
51
        undef,
48
        undef,
Lines 56-61 if ( $op eq 'acct_form' ) { Link Here
56
    );
53
    );
57
    $template->param( vendors => \@vendors );
54
    $template->param( vendors => \@vendors );
58
55
56
    my @sftp_servers = $schema->resultset('SftpServer')->search(
57
        undef,
58
        {
59
            columns  => [ 'name', 'host', 'port', 'id' ],
60
            order_by => { -asc => 'name' },
61
        }
62
    );
63
    $template->param( sftp_servers => \@sftp_servers );
64
59
    if ( C4::Context->config("enable_plugins") ) {
65
    if ( C4::Context->config("enable_plugins") ) {
60
        my @plugins = Koha::Plugins->new()->GetPlugins({
66
        my @plugins = Koha::Plugins->new()->GetPlugins({
61
            method => 'edifact',
67
            method => 'edifact',
Lines 64-70 if ( $op eq 'acct_form' ) { Link Here
64
    }
70
    }
65
}
71
}
66
elsif ( $op eq 'delete_confirm' ) {
72
elsif ( $op eq 'delete_confirm' ) {
67
    show_account($crypt);
73
    show_account();
68
    $template->param( delete_confirm => 1 );
74
    $template->param( delete_confirm => 1 );
69
}
75
}
70
else {
76
else {
Lines 72-99 else { Link Here
72
78
73
        # validate & display
79
        # validate & display
74
        my $id     = $input->param('id');
80
        my $id     = $input->param('id');
75
        my $password = scalar $input->param('password');
76
        $password = $crypt->encrypt_hex($password);
77
        my $fields = {
81
        my $fields = {
78
            description        => scalar $input->param('description'),
82
            description             => scalar $input->param('description'),
79
            host               => scalar $input->param('host'),
83
            upload_sftp_server_id   => $input->param('upload_sftp_server_id') || undef,
80
            username           => scalar $input->param('username'),
84
            download_sftp_server_id => $input->param('download_sftp_server_id') || undef,
81
            password           => $password,
85
            vendor_id               => scalar $input->param('vendor_id'),
82
            upload_port        => scalar $input->param('upload_port')   || 22,
86
            upload_directory        => scalar $input->param('upload_directory'),
83
            download_port      => scalar $input->param('download_port') || 22,
87
            download_directory      => scalar $input->param('download_directory'),
84
            vendor_id          => scalar $input->param('vendor_id'),
88
            san                     => scalar $input->param('san'),
85
            upload_directory   => scalar $input->param('upload_directory'),
89
            standard                => scalar $input->param('standard'),
86
            download_directory => scalar $input->param('download_directory'),
90
            quotes_enabled          => $input->param('quotes_enabled') ? 1 : 0,
87
            san                => scalar $input->param('san'),
91
            invoices_enabled        => $input->param('invoices_enabled') ? 1 : 0,
88
            standard           => scalar $input->param('standard'),
92
            orders_enabled          => $input->param('orders_enabled') ? 1 : 0,
89
            transport          => scalar $input->param('transport'),
93
            responses_enabled       => $input->param('responses_enabled') ? 1 : 0,
90
            quotes_enabled     => $input->param('quotes_enabled') ? 1 : 0,
94
            auto_orders             => $input->param('auto_orders') ? 1 : 0,
91
            invoices_enabled   => $input->param('invoices_enabled') ? 1 : 0,
95
            id_code_qualifier       => scalar $input->param('id_code_qualifier'),
92
            orders_enabled     => $input->param('orders_enabled') ? 1 : 0,
96
            plugin                  => scalar $input->param('plugin'),
93
            responses_enabled  => $input->param('responses_enabled') ? 1 : 0,
94
            auto_orders        => $input->param('auto_orders') ? 1 : 0,
95
            id_code_qualifier  => scalar $input->param('id_code_qualifier'),
96
            plugin             => scalar $input->param('plugin'),
97
        };
97
        };
98
98
99
        if ($id) {
99
        if ($id) {
Lines 162-175 sub get_account { Link Here
162
}
162
}
163
163
164
sub show_account {
164
sub show_account {
165
    my $crypt = shift;
166
    my $acct_id = $input->param('id');
165
    my $acct_id = $input->param('id');
167
    if ($acct_id) {
166
    return unless $acct_id;
168
        my $acct = $schema->resultset('VendorEdiAccount')->find($acct_id);
167
169
        $acct->password( $crypt->decrypt_hex($acct->password) );
168
    my $acct = $schema->resultset('VendorEdiAccount')->find($acct_id);
170
        if ($acct) {
169
    return unless $acct;
171
            $template->param( account => $acct );
170
    
172
        }
171
    return $template->param( account => $acct );
173
    }
174
    return;
175
}
172
}
(-)a/installer/data/mysql/atomicupdate/bug_38489-move_edi_SFTP_data_to_SFTP_tables.pl (+111 lines)
Line 0 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number  => "38489",
5
    description => "Move SFTP data from EDI module to FTP/SFTP table",
6
    up          => sub {
7
        my ($args)        = @_;
8
        my ( $dbh, $out ) = @$args{qw(dbh out)};
9
10
        if ( TableExists('sftp_servers') ) {
11
            if ( column_exists( 'vendor_edi_accounts', 'host')
12
            && column_exists( 'vendor_edi_accounts', 'download_port' )
13
            && column_exists( 'vendor_edi_accounts', 'upload_port' )
14
            && column_exists( 'vendor_edi_accounts', 'transport' )
15
            && column_exists( 'vendor_edi_accounts', 'username' )
16
            && column_exists( 'vendor_edi_accounts', 'password' ) )
17
            {
18
                ## copy edi vendor sftp servers to sftp_servers
19
                $dbh->do(
20
                    q{
21
                        INSERT IGNORE INTO `sftp_servers` (name,host,port,transport,user_name,password)
22
                        SELECT description,host,download_port,LOWER(transport),username,password
23
                        FROM vendor_edi_accounts
24
                        WHERE vendor_edi_accounts.download_port IS NOT NULL;
25
                    }
26
                );
27
                say $out "Copied FTP/SFTP download servers from vendor_edi_accounts table";
28
29
                $dbh->do(
30
                    q{
31
                        INSERT IGNORE INTO `sftp_servers` (name,host,port,transport,user_name,password)
32
                        SELECT description,host,upload_port,LOWER(transport),username,password
33
                        FROM vendor_edi_accounts
34
                        WHERE vendor_edi_accounts.upload_port IS NOT NULL;
35
                    }
36
                );
37
                say $out "Copied FTP/SFTP upload servers from vendor_edi_accounts table";
38
39
                ## add foreign keys
40
                unless ( column_exists( 'vendor_edi_accounts', 'download_sftp_server_id' )
41
                    || column_exists( 'vendor_edi_accounts', 'upload_sftp_server_id' ) )
42
                {
43
                    $dbh->do(
44
                        q{
45
                            ALTER TABLE `vendor_edi_accounts`
46
                                ADD COLUMN `download_sftp_server_id` int(11) DEFAULT NULL AFTER `description`,
47
                                ADD COLUMN `upload_sftp_server_id` int(11) DEFAULT NULL AFTER `download_sftp_server_id`;
48
                        }
49
                    );
50
                    say $out "Added download_sftp_server_id column in vendor_edi_accounts table";
51
                    say $out "Added upload_sftp_server_id column in vendor_edi_accounts table";
52
                }
53
                unless ( foreign_key_exists( 'vendor_edi_accounts', 'vfk_download_sftp_server_id' )
54
                    || foreign_key_exists( 'vendor_edi_accounts', 'vfk_upload_sftp_server_id' ) )
55
                {
56
                    $dbh->do(
57
                        q{
58
                            ALTER TABLE `vendor_edi_accounts`
59
                                ADD CONSTRAINT `vfk_download_sftp_server_id` FOREIGN KEY (`download_sftp_server_id`) REFERENCES `sftp_servers` (`id`),
60
                                ADD CONSTRAINT `vfk_upload_sftp_server_id` FOREIGN KEY (`upload_sftp_server_id`) REFERENCES `sftp_servers` (`id`);
61
                        }
62
                    );
63
                    say $out "Added vfk_download_sftp_server_id constraint in vendor_edi_accounts table";
64
                    say $out "Added vfk_upload_sftp_server_id constraint in vendor_edi_accounts table";
65
                }
66
67
                ## set matching sftp server ids in new foreign keys
68
                $dbh->do(
69
                    q{
70
                        UPDATE IGNORE `vendor_edi_accounts`, `sftp_servers`
71
                            SET vendor_edi_accounts.download_sftp_server_id = sftp_servers.id
72
                            WHERE vendor_edi_accounts.host = sftp_servers.host
73
                            AND vendor_edi_accounts.download_port = sftp_servers.port
74
                            AND vendor_edi_accounts.transport = sftp_servers.transport;
75
                    }
76
                );
77
                say $out "Matched sftp_server.id with vendor_edi_accounts.download_sftp_server_id";
78
                $dbh->do(
79
                    q{
80
                        UPDATE IGNORE `vendor_edi_accounts`, `sftp_servers`
81
                            SET vendor_edi_accounts.upload_sftp_server_id = sftp_servers.id
82
                            WHERE vendor_edi_accounts.host = sftp_servers.host
83
                            AND vendor_edi_accounts.upload_port = sftp_servers.port
84
                            AND vendor_edi_accounts.transport = sftp_servers.transport;
85
                    }
86
                );
87
                say $out "Matched sftp_server.id with vendor_edi_accounts.upload_sftp_server_id";
88
89
90
                ## drop useless columns from edi vendors
91
                $dbh->do(
92
                    q{
93
                        ALTER TABLE vendor_edi_accounts
94
                            DROP COLUMN `host`,
95
                            DROP COLUMN `username`,
96
                            DROP COLUMN `password`,
97
                            DROP COLUMN `download_port`,
98
                            DROP COLUMN `upload_port`,
99
                            DROP COLUMN `transport`;
100
                    }
101
                );
102
                say $out "Dropped host column in vendor_edi_accounts table";
103
                say $out "Dropped username column in vendor_edi_accounts table";
104
                say $out "Dropped password column in vendor_edi_accounts table";
105
                say $out "Dropped download_port column in vendor_edi_accounts table";
106
                say $out "Dropped upload_port column in vendor_edi_accounts table";
107
                say $out "Dropped transport column in vendor_edi_accounts table";
108
            }
109
        }
110
    },
111
};
(-)a/installer/data/mysql/kohastructure.sql (-6 / +6 lines)
Lines 6626-6636 DROP TABLE IF EXISTS `vendor_edi_accounts`; Link Here
6626
CREATE TABLE `vendor_edi_accounts` (
6626
CREATE TABLE `vendor_edi_accounts` (
6627
  `id` int(11) NOT NULL AUTO_INCREMENT,
6627
  `id` int(11) NOT NULL AUTO_INCREMENT,
6628
  `description` mediumtext NOT NULL,
6628
  `description` mediumtext NOT NULL,
6629
  `host` varchar(40) DEFAULT NULL,
6629
  `download_sftp_server_id` int(11) DEFAULT NULL,
6630
  `username` varchar(40) DEFAULT NULL,
6630
  `upload_sftp_server_id` int(11) DEFAULT NULL,
6631
  `password` mediumtext DEFAULT NULL,
6632
  `upload_port` int(11) DEFAULT NULL,
6633
  `download_port` int(11) DEFAULT NULL,
6634
  `last_activity` date DEFAULT NULL,
6631
  `last_activity` date DEFAULT NULL,
6635
  `vendor_id` int(11) DEFAULT NULL,
6632
  `vendor_id` int(11) DEFAULT NULL,
6636
  `download_directory` mediumtext DEFAULT NULL,
6633
  `download_directory` mediumtext DEFAULT NULL,
Lines 6638-6644 CREATE TABLE `vendor_edi_accounts` ( Link Here
6638
  `san` varchar(20) DEFAULT NULL,
6635
  `san` varchar(20) DEFAULT NULL,
6639
  `standard` varchar(3) DEFAULT 'EUR',
6636
  `standard` varchar(3) DEFAULT 'EUR',
6640
  `id_code_qualifier` varchar(3) DEFAULT '14',
6637
  `id_code_qualifier` varchar(3) DEFAULT '14',
6641
  `transport` varchar(6) DEFAULT 'FTP',
6642
  `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0,
6638
  `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0,
6643
  `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0,
6639
  `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0,
6644
  `orders_enabled` tinyint(1) NOT NULL DEFAULT 0,
6640
  `orders_enabled` tinyint(1) NOT NULL DEFAULT 0,
Lines 6649-6655 CREATE TABLE `vendor_edi_accounts` ( Link Here
6649
  PRIMARY KEY (`id`),
6645
  PRIMARY KEY (`id`),
6650
  KEY `vendorid` (`vendor_id`),
6646
  KEY `vendorid` (`vendor_id`),
6651
  KEY `shipmentbudget` (`shipment_budget`),
6647
  KEY `shipmentbudget` (`shipment_budget`),
6648
  KEY `vfk_download_sftp_server_id` (`download_sftp_server_id`),
6649
  KEY `vfk_upload_sftp_server_id` (`upload_sftp_server_id`),
6650
  CONSTRAINT `vfk_download_sftp_server_id` FOREIGN KEY (`download_sftp_server_id`) REFERENCES `sftp_servers` (`id`),
6652
  CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`),
6651
  CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`),
6652
  CONSTRAINT `vfk_upload_sftp_server_id` FOREIGN KEY (`upload_sftp_server_id`) REFERENCES `sftp_servers` (`id`),
6653
  CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`)
6653
  CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`)
6654
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6654
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6655
/*!40101 SET character_set_client = @saved_cs_client */;
6655
/*!40101 SET character_set_client = @saved_cs_client */;
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_accounts.tt (-44 / +47 lines)
Lines 135-174 Link Here
135
     <input type="text" name="description" id="description" size="20" value="[% account.description | html %]" />
135
     <input type="text" name="description" id="description" size="20" value="[% account.description | html %]" />
136
  </li>
136
  </li>
137
  <li>
137
  <li>
138
     [% transport_types = [ 'FTP', 'SFTP', 'FILE' ] %]
138
     <label for="upload_sftp_server_id">Upload FTP/SFTP server: </label>
139
     <label for="transport">Transport: </label>
139
     <select name="upload_sftp_server_id" id="upload_sftp_server_id">
140
     <select name="transport" title="valid types of transport are FTP and SFTP"
140
        <option value="">(do not use FTP/SFTP Server)</option>
141
      id="transport">
141
        [% FOREACH sftp_server IN sftp_servers %]
142
      [% FOREACH transport_type IN transport_types %]
142
        [% IF account.upload_sftp_server.id == sftp_server.id %]
143
           [% IF transport_type == account.transport %]
143
            <option value="[% sftp_server.id | html %]" selected="selected">
144
              <option value="[% transport_type | html %]" selected="selected">[% transport_type | html %]</option>
144
        [% ELSE %]
145
           [% ELSE %]
145
            <option value="[% sftp_server.id | html %]">
146
              <option value="[% transport_type | html %]">[% transport_type | html %]</option>
146
        [% END %]
147
           [% END %]
147
            [% sftp_server.name | html %] ([% sftp_server.host | html %]:[% sftp_server.port | html %])
148
       [% END %]
148
        </option>
149
     </select>
149
        [% END %]
150
  </li>
150
    </select>
151
  <li>
152
     <label for="host">Remote host: </label>
153
     <input type="text" name="host" id="host" size="20" maxlength="40" value="[% account.host | html %]" />
154
  </li>
155
  <li>
156
     <label for="username">Username: </label>
157
     <input type="text" name="username" id="username" size="20" maxlength="40" value="[% account.username | html %]" />
158
  </li>
159
  <li>
160
     <label for="password">Password: </label>
161
     <input type="text" name="password" id="password" size="20" maxlength="40" value="[% account.password | html %]" />
162
  </li>
163
  <li>
164
     <label for="upload_port">Upload port: </label>
165
     <input type="text" name="upload_port" id="upload_port" size="20" maxlength="40" value="[% account.upload_port | html %]" />
166
        <div class="hint">The upload port will default to 22 if not set.</div>
167
  </li>
151
  </li>
168
  <li>
152
  <li>
169
     <label for="download_port">Download port: </label>
153
    <label for="download_sftp_server_id">Download FTP/SFTP server: </label>
170
     <input type="text" name="download_port" id="download_port" size="20" maxlength="40" value="[% account.download_port | html %]" />
154
    <select name="download_sftp_server_id" id="download_sftp_server_id">
171
        <div class="hint">The download port will default to 22 if not set.</div>
155
        <option value="">(do not use FTP/SFTP Server)</option>
156
        [% FOREACH sftp_server IN sftp_servers %]
157
        [% IF account.download_sftp_server.id == sftp_server.id %]
158
            <option value="[% sftp_server.id | html %]" selected="selected">
159
        [% ELSE %]
160
            <option value="[% sftp_server.id | html %]">
161
        [% END %]
162
            [% sftp_server.name | html %] ([% sftp_server.host | html %]:[% sftp_server.port | html %])
163
        </option>
164
        [% END %]
165
    </select>
166
        <div class="hint">Configure under <a href="/cgi-bin/koha/admin/sftp_servers.pl">FTP/SFTP Servers</a></div>
172
  </li>
167
  </li>
173
  <li>
168
  <li>
174
     <label for="download_directory">Download directory: </label>
169
     <label for="download_directory">Download directory: </label>
Lines 313-324 Link Here
313
       <th>ID</th>
308
       <th>ID</th>
314
       <th>Vendor</th>
309
       <th>Vendor</th>
315
       <th>Description</th>
310
       <th>Description</th>
316
       <th>Transport</th>
311
       <th>Upload FTP/SFTP server</th>
317
       <th>Remote host</th>
312
       <th>Download FTP/SFTP server</th>
318
       <th>Username</th>
319
       <th>Password</th>
320
       <th>Upload port</th>
321
       <th>Download port</th>
322
       <th>Download directory</th>
313
       <th>Download directory</th>
323
       <th>Upload directory</th>
314
       <th>Upload directory</th>
324
       <th>Qualifier</th>
315
       <th>Qualifier</th>
Lines 336-347 Link Here
336
      <td>[% account.id | html %]</td>
327
      <td>[% account.id | html %]</td>
337
      <td><a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% account.vendor_id | uri %]">[% account.vendor.name | html %]</a></td>
328
      <td><a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% account.vendor_id | uri %]">[% account.vendor.name | html %]</a></td>
338
      <td>[% account.description | html %]</td>
329
      <td>[% account.description | html %]</td>
339
      <td>[% account.transport | html %]</td>
330
      <td>
340
      <td>[% account.host | html %]</td>
331
        [% IF (account.upload_sftp_server) %]
341
      <td>[% account.username | html %]</td>
332
        <a href="/cgi-bin/koha/admin/sftp_servers.pl?op=edit_form&sftp_server_id=[% account.upload_sftp_server.id | html %]">
342
      <td>[% IF account.password %]*****[% END %]</td>
333
            [% account.upload_sftp_server.name | html %]
343
      <td>[% account.upload_port | html %]</td>
334
        </a>
344
      <td>[% account.download_port | html %]</td>
335
        [% ELSE %]
336
        <em>None set</em>
337
        [% END %]
338
      </td>
339
      <td>
340
        [% IF (account.download_sftp_server) %]
341
        <a href="/cgi-bin/koha/admin/sftp_servers.pl?op=edit_form&sftp_server_id=[% account.download_sftp_server.id | html %]">
342
            [% account.download_sftp_server.name | html %]
343
        </a>
344
        [% ELSE %]
345
        <em>None set</em>
346
        [% END %]
347
      </td>
345
      <td>[% account.download_directory | html %]</td>
348
      <td>[% account.download_directory | html %]</td>
346
      <td>[% account.upload_directory | html %]</td>
349
      <td>[% account.upload_directory | html %]</td>
347
      <td>
350
      <td>
(-)a/t/db_dependent/Koha/Edifact/Transport.t (-2 / +3 lines)
Lines 20-26 my $account = $builder->build( Link Here
20
    {
20
    {
21
        source => 'VendorEdiAccount',
21
        source => 'VendorEdiAccount',
22
        value  => {
22
        value  => {
23
            description => 'test vendor', transport => 'FILE',
23
            description             => 'test vendor',
24
            upload_sftp_server_id   => undef,
25
            download_sftp_server_id => undef,
24
        }
26
        }
25
    }
27
    }
26
);
28
);
27
- 

Return to bug 38489