From 8c6c3e0c671d8f5570e110358892c15398633bc0 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 11 Mar 2016 11:58:47 +0000 Subject: [PATCH] Bug 7736 [QA Followup] - Fix SQL, Add description field for Library EANs Signed-off-by: Kyle M Hall --- Koha/Schema/Result/EdifactEan.pm | 20 ++++--- admin/edi_ean_accounts.pl | 3 + installer/data/mysql/atomicupdate/edifact.sql | 67 +++++++++++----------- installer/data/mysql/kohastructure.sql | 66 ++++++++++----------- .../intranet-tmpl/prog/en/modules/acqui/edi_ean.tt | 11 ++-- .../prog/en/modules/admin/edi_ean_accounts.tt | 12 +++- 6 files changed, 96 insertions(+), 83 deletions(-) diff --git a/Koha/Schema/Result/EdifactEan.pm b/Koha/Schema/Result/EdifactEan.pm index a9e5011..96dd729 100644 --- a/Koha/Schema/Result/EdifactEan.pm +++ b/Koha/Schema/Result/EdifactEan.pm @@ -26,10 +26,15 @@ __PACKAGE__->table("edifact_ean"); =head2 ee_id data_type: 'integer' - extra: {unsigned => 1} is_auto_increment: 1 is_nullable: 0 +=head2 description + + data_type: 'varchar' + is_nullable: 1 + size: 128 + =head2 branchcode data_type: 'varchar' @@ -54,12 +59,9 @@ __PACKAGE__->table("edifact_ean"); __PACKAGE__->add_columns( "ee_id", - { - data_type => "integer", - extra => { unsigned => 1 }, - is_auto_increment => 1, - is_nullable => 0, - }, + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "description", + { data_type => "varchar", is_nullable => 1, size => 128 }, "branchcode", { data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 10 }, "ean", @@ -98,8 +100,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07042 @ 2015-06-12 10:22:04 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:WWcMBSXeuzgCPqM0KMxfBg +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2016-03-11 12:04:29 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:wziFbge4g2Ek1lc8Anto+Q # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/admin/edi_ean_accounts.pl b/admin/edi_ean_accounts.pl index e9bf1d7..4b32c69 100755 --- a/admin/edi_ean_accounts.pl +++ b/admin/edi_ean_accounts.pl @@ -118,6 +118,7 @@ sub addsubmit { my $new_ean = $schema->resultset('EdifactEan')->new( { branchcode => $input->param('branchcode'), + description => $input->param('description'), ean => $input->param('ean'), id_code_qualifier => $input->param('id_code_qualifier'), } @@ -127,6 +128,7 @@ sub addsubmit { } sub editsubmit { + warn "DESC: " . $input->param('description'); $schema->resultset('EdifactEan')->search( { branchcode => $input->param('oldbranchcode'), @@ -135,6 +137,7 @@ sub editsubmit { )->update_all( { branchcode => $input->param('branchcode'), + description => $input->param('description'), ean => $input->param('ean'), id_code_qualifier => $input->param('id_code_qualifier'), } diff --git a/installer/data/mysql/atomicupdate/edifact.sql b/installer/data/mysql/atomicupdate/edifact.sql index 25ffb07..62b29dd 100644 --- a/installer/data/mysql/atomicupdate/edifact.sql +++ b/installer/data/mysql/atomicupdate/edifact.sql @@ -1,23 +1,23 @@ -- Holds details for vendors supplying goods by EDI CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( - id int(11) NOT NULL auto_increment, - description text NOT NULL, - host varchar(40), - username varchar(40), - password varchar(40), - last_activity date, - vendor_id int(11) references aqbooksellers( id ), - download_directory text, - upload_directory text, - san varchar(20), - 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, - responses_enabled tinyint(1) not null default 0, - auto_orders tinyint(1) not null default 0, - shipment_budget integer(11) references aqbudgets( budget_id ), + id INT(11) NOT NULL auto_increment, + description TEXT NOT NULL, + host VARCHAR(40), + username VARCHAR(40), + password VARCHAR(40), + last_activity DATE, + vendor_id INT(11) REFERENCES aqbooksellers( id ), + download_directory TEXT, + upload_directory TEXT, + san VARCHAR(20), + 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, + responses_enabled TINYINT(1) not null default 0, + auto_orders TINYINT(1) not null default 0, + shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), PRIMARY KEY (id), KEY vendorid (vendor_id), KEY shipmentbudget (shipment_budget), @@ -27,16 +27,16 @@ CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( -- Hold the actual edifact messages with links to associated baskets CREATE TABLE IF NOT EXISTS edifact_messages ( - id int(11) NOT NULL auto_increment, - message_type varchar(10) NOT NULL, - transfer_date date, - vendor_id int(11) references aqbooksellers( id ), - edi_acct integer references vendor_edi_accounts( id ), - status text, - basketno int(11) REFERENCES aqbasket( basketno), - raw_msg mediumtext, - filename text, - deleted boolean not null default 0, + id INT(11) NOT NULL auto_increment, + message_type VARCHAR(10) NOT NULL, + transfer_date DATE, + vendor_id INT(11) REFERENCES aqbooksellers( id ), + edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), + status TEXT, + basketno INT(11) REFERENCES aqbasket( basketno), + raw_msg MEDIUMTEXT, + filename TEXT, + deleted BOOLEAN NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY vendorid ( vendor_id), KEY ediacct (edi_acct), @@ -54,19 +54,20 @@ ALTER TABLE aqinvoices ADD CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) -- Hold the supplier ids from quotes for ordering -- although this is an EAN-13 article number the standard says 35 characters ??? -ALTER TABLE aqorders ADD COLUMN line_item_id varchar(35); +ALTER TABLE aqorders ADD COLUMN line_item_id VARCHAR(35); -- The suppliers unique reference usually a quotation line number ('QLI') -- Otherwise Suppliers unique orderline reference ('SLI') -ALTER TABLE aqorders ADD COLUMN suppliers_reference_number varchar(35); -ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier varchar(3); +ALTER TABLE aqorders ADD COLUMN suppliers_reference_number VARCHAR(35); +ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier VARCHAR(3); ALTER TABLE aqorders ADD COLUMN suppliers_report text; -- hold the EAN/SAN used in ordering CREATE TABLE IF NOT EXISTS edifact_ean ( - ee_id integer(11) unsigned not null auto_increment primary key, + ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + description VARCHAR(128) NULL DEFAULT NULL, branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), - ean varchar(15) NOT NULL, + ean VARCHAR(15) NOT NULL, id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index eef5317..154cadb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3178,24 +3178,24 @@ CREATE TABLE aqorders_transfers ( DROP TABLE IF EXISTS vendor_edi_accounts; CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( - id int(11) NOT NULL auto_increment, - description text NOT NULL, - host varchar(40), - username varchar(40), - password varchar(40), - last_activity date, - vendor_id int(11) references aqbooksellers( id ), - download_directory text, - upload_directory text, - san varchar(20), - 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, - responses_enabled tinyint(1) NOT NULL DEFAULT '0', - auto_orders tinyint(1) NOT NULL DEFAULT '0', - shipment_budget integer(11) references aqbudgets( budget_id ), + id INT(11) NOT NULL auto_increment, + description TEXT NOT NULL, + host VARCHAR(40), + username VARCHAR(40), + password VARCHAR(40), + last_activity DATE, + vendor_id INT(11) REFERENCES aqbooksellers( id ), + download_directory TEXT, + upload_directory TEXT, + san VARCHAR(20), + 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, + responses_enabled TINYINT(1) not null default 0, + auto_orders TINYINT(1) not null default 0, + shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), PRIMARY KEY (id), KEY vendorid (vendor_id), KEY shipmentbudget (shipment_budget), @@ -3209,16 +3209,16 @@ CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( DROP TABLE IF EXISTS edifact_messages; CREATE TABLE IF NOT EXISTS edifact_messages ( - id int(11) NOT NULL auto_increment, - message_type varchar(10) NOT NULL, - transfer_date date, - vendor_id int(11) references aqbooksellers( id ), - edi_acct integer references vendor_edi_accounts( id ), - status text, - basketno int(11) references aqbasket( basketno), - raw_msg mediumtext, - filename text, - deleted boolean not null default 0, + id INT(11) NOT NULL auto_increment, + message_type VARCHAR(10) NOT NULL, + transfer_date DATE, + vendor_id INT(11) REFERENCES aqbooksellers( id ), + edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), + status TEXT, + basketno INT(11) REFERENCES aqbasket( basketno), + raw_msg MEDIUMTEXT, + filename TEXT, + deleted BOOLEAN NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY vendorid ( vendor_id), KEY ediacct (edi_acct), @@ -3713,11 +3713,11 @@ CREATE TABLE audio_alerts ( DROP TABLE IF EXISTS edifact_ean; CREATE TABLE IF NOT EXISTS edifact_ean ( - ee_id int(11) NOT NULL AUTO_INCREMENT, - branchcode varchar(10) not null references branches (branchcode), - ean varchar(15) NOT NULL, - id_code_qualifier varchar(3) NOT NULL default '14', - PRIMARY KEY (ee_id), + ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + description VARCHAR(128) NULL DEFAULT NULL, + branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), + ean VARCHAR(15) NOT NULL, + id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/edi_ean.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/edi_ean.tt index bd30e08..fdf5a31 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/edi_ean.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/edi_ean.tt @@ -15,19 +15,20 @@
-

Identify the branch account submitting the EDI order

+

Select the library account submitting the EDI order


-

Select ordering branch account:

+

Select ordering library account:

-
- +

+ +

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_ean_accounts.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_ean_accounts.tt index 3191529..4aa2b2c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_ean_accounts.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi_ean_accounts.tt @@ -52,8 +52,8 @@
[% IF ean %] - - + + [% END %]
@@ -79,8 +79,12 @@
  • + + +
  • +
  • - +
  • @@ -128,6 +132,7 @@ + @@ -135,6 +140,7 @@ [% FOREACH ean IN eans %] [% IF loop.even %][% ELSE %][% END %] +
    LibraryDescription EAN Qualifier Actions
    [% ean.branch.branchname %][% ean.description %] [% ean.ean %] [% FOREACH qualifier IN code_qualifiers %] -- 2.1.4