@@ -, +, @@ --- installer/data/mysql/kohastructure.sql | 26 +++++++++++++- installer/data/mysql/updatedatabase.pl | 60 ++++++++++++++++++++++++++++++++ 2 files changed, 84 insertions(+), 2 deletions(-) --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2631,7 +2631,7 @@ CREATE TABLE `aqorders` ( `listprice` decimal(28,6) default NULL, `totalamount` decimal(28,6) default NULL, `datereceived` date default NULL, - `booksellerinvoicenumber` mediumtext, + invoiceid int(11) default NULL, `freight` decimal(28,6) default NULL, `unitprice` decimal(28,6) default NULL, `quantityreceived` smallint(6) NOT NULL default 0, @@ -2663,7 +2663,8 @@ CREATE TABLE `aqorders` ( KEY `biblionumber` (`biblionumber`), KEY `budget_id` (`budget_id`), CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2680,6 +2681,27 @@ CREATE TABLE `aqorders_items` ( KEY `ordernumber` (`ordernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table aqinvoices +-- + +DROP TABLE IF EXISTS aqinvoices; +CREATE TABLE aqinvoices ( + invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key + invoicenumber mediumtext NOT NULL, -- Name of invoice + booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers + shipmentdate date default NULL, -- date of shipment + billingdate date default NULL, -- date of billing + closedate date default NULL, -- invoice close date, NULL means the invoice is open + shipmentcost decimal(28,6) default NULL, -- shipment cost + shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget + PRIMARY KEY (invoiceid), + CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + -- -- Table structure for table `fieldmapping` -- --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -4932,6 +4932,66 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(" + CREATE TABLE aqinvoices ( + invoiceid int(11) NOT NULL AUTO_INCREMENT, + invoicenumber mediumtext NOT NULL, + booksellerid int(11) NOT NULL, + shipmentdate date default NULL, + billingdate date default NULL, + closedate date default NULL, + shipmentcost decimal(28,6) default NULL, + shipmentcost_budgetid int(11) default NULL, + PRIMARY KEY (invoiceid), + CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + "); + + # Fill this new table with existing invoices + my $sth = $dbh->prepare(" + SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + WHERE aqorders.booksellerinvoicenumber IS NOT NULL + AND aqorders.booksellerinvoicenumber != '' + GROUP BY aqorders.booksellerinvoicenumber + "); + $sth->execute; + my $results = $sth->fetchall_arrayref({}); + $sth = $dbh->prepare(" + INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?) + "); + foreach(@$results) { + $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'}); + } + + # Add the column in aqorders, fill it with correct value + # and then drop booksellerinvoicenumber column + $dbh->do(" + ALTER TABLE aqorders + ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber, + ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE + "); + + $dbh->do(" + UPDATE aqorders, aqinvoices + SET aqorders.invoiceid = aqinvoices.invoiceid + WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber + "); + + $dbh->do(" + ALTER TABLE aqorders + DROP COLUMN booksellerinvoicenumber + "); + + print "Upgrade to $DBversion done (Add aqinvoices table) \n"; + SetVersion ($DBversion); +} + + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) --