From 9f9e0ea002dfbf9da92285edbea2df975b6d7de9 Mon Sep 17 00:00:00 2001 From: Brendan A Gallagher Date: Fri, 1 Apr 2016 20:54:09 +0000 Subject: [PATCH] DBRev Bug 7736 - Edifact QUOTE and ORDER functionality Signed-off-by: Brendan A Gallagher --- Koha.pm | 2 +- installer/data/mysql/atomicupdate/edifact.sql | 79 ------------- installer/data/mysql/updatedatabase.pl | 108 ++++++++++++++++++ 3 files changed, 109 insertions(+), 80 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/edifact.sql diff --git a/Koha.pm b/Koha.pm index 0f100f90e1..bed3443c94 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "3.23.00.044"; +$VERSION = "3.23.00.045"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/edifact.sql b/installer/data/mysql/atomicupdate/edifact.sql deleted file mode 100644 index 62b29dde69..0000000000 --- a/installer/data/mysql/atomicupdate/edifact.sql +++ /dev/null @@ -1,79 +0,0 @@ --- 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 ), - PRIMARY KEY (id), - KEY vendorid (vendor_id), - KEY shipmentbudget (shipment_budget), - CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), - CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- 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, - PRIMARY KEY (id), - KEY vendorid ( vendor_id), - KEY ediacct (edi_acct), - KEY basketno ( basketno), - CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), - CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ), - CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- invoices link back to the edifact message it was generated from -ALTER TABLE aqinvoices ADD COLUMN message_id INT(11) REFERENCES edifact_messages( id ); - --- clean up link on deletes -ALTER TABLE aqinvoices ADD CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL; - --- 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); - --- 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_report text; - --- hold the EAN/SAN used in ordering -CREATE TABLE IF NOT EXISTS edifact_ean ( - 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; - --- Syspref budget to hold shipping costs -INSERT INTO systempreferences (variable, explanation, type) VALUES('EDIInvoicesShippingBudget','The budget code used to allocate shipping charges to when processing EDI Invoice messages', 'free'); - --- Add a permission for managing EDI -INSERT INTO permissions (module_bit, code, description) values (11, 'edi_manage', 'Manage EDIFACT transmissions'); diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6603453489..7f0ce61f48 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -12087,6 +12087,114 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { print "Upgrade to $DBversion done (Bug 10988 - Allow login via Google OAuth2 (OpenID Connect))\n"; SetVersion($DBversion); } + +$DBversion = "3.23.00.045"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { +## Holds details for vendors supplying goods by EDI + $dbh->do(q{ + 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 ), + PRIMARY KEY (id), + KEY vendorid (vendor_id), + KEY shipmentbudget (shipment_budget), + CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), + CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + }); + +## Hold the actual edifact messages with links to associated baskets + $dbh->do(q{ + 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, + PRIMARY KEY (id), + KEY vendorid ( vendor_id), + KEY ediacct (edi_acct), + KEY basketno ( basketno), + CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), + CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ), + CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + }); + +## invoices link back to the edifact message it was generated from + $dbh->do(q{ + ALTER TABLE aqinvoices ADD COLUMN message_id INT(11) REFERENCES edifact_messages( id ); + }); + +## clean up link on deletes + $dbh->do(q{ + ALTER TABLE aqinvoices ADD CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL; + }); + +## Hold the supplier ids from quotes for ordering +## although this is an EAN-13 article number the standard says 35 characters ??? + $dbh->do(q{ + 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') + $dbh->do(q{ + ALTER TABLE aqorders ADD COLUMN suppliers_reference_number VARCHAR(35); + }); + $dbh->do(q{ + ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier VARCHAR(3); + }); + $dbh->do(q{ + ALTER TABLE aqorders ADD COLUMN suppliers_report text; + }); + +## hold the EAN/SAN used in ordering + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS edifact_ean ( + 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; + }); +## Syspref budget to hold shipping costs + $dbh->do(q{ + INSERT INTO systempreferences (variable, explanation, type) VALUES('EDIInvoicesShippingBudget','The budget code used to allocate shipping charges to when processing EDI Invoice messages', 'free'); + }); + +## Add a permission for managing EDI + $dbh->do(q{ + INSERT INTO permissions (module_bit, code, description) values (11, 'edi_manage', 'Manage EDIFACT transmissions'); + }); + + print "Upgrade to $DBversion done (Bug 7736 - Edifact QUOTE and ORDER functionality))\n"; + SetVersion($DBversion); +} + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.39.5