From 02209f512d9a9116868341c360b27f257cad65bf Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Fri, 6 Jan 2012 16:51:57 +0100 Subject: [PATCH 1/2] Bug 5339: Invoices management improvement Database update files --- installer/data/mysql/kohastructure.sql | 26 +++++++++++++- installer/data/mysql/updatedatabase.pl | 59 ++++++++++++++++++++++++++++++++ 2 files changed, 83 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 67149c9d..08f1dd8 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2608,7 +2608,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, @@ -2640,7 +2640,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; @@ -2657,6 +2658,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` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d2d9d97..cf65d10 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4712,6 +4712,65 @@ 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) -- 1.7.9