From b7f46b011399eb75b27ce9ba6c003a66173fd738 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 | 57 ++++++++++++++++++++++++++++++++ kohaversion.pl | 2 +- 3 files changed, 82 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e4388a4..34baa8f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2604,7 +2604,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, @@ -2634,7 +2634,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 `invoices` (`invoiceid`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2651,6 +2652,27 @@ CREATE TABLE `aqorders_items` ( KEY `ordernumber` (`ordernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `invoices` +-- + +DROP TABLE IF EXISTS `invoices`; +CREATE TABLE `invoices` ( + `invoiceid` int(11) NOT NULL AUTO_INCREMENT, + `invoicenumber` varchar(80) 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 `invoices_fk_aqbooksellerid` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `invoices_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 a8cd166..a77d9a5 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4550,6 +4550,63 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(" + CREATE TABLE `invoices` ( + `invoiceid` int(11) NOT NULL AUTO_INCREMENT, + `invoicenumber` varchar(80) 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 `invoices_fk_aqbooksellerid` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `invoices_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 + GROUP BY aqorders.booksellerinvoicenumber + "); + $sth->execute; + my $results = $sth->fetchall_arrayref({}); + $sth = $dbh->prepare(" + INSERT INTO invoices (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 + "); + + $dbh->do(" + UPDATE aqorders, invoices + SET aqorders.invoiceid = invoices.invoiceid + WHERE aqorders.booksellerinvoicenumber = invoices.invoicenumber + "); + + $dbh->do(" + ALTER TABLE aqorders + DROP COLUMN booksellerinvoicenumber + "); + + print "Upgrade to $DBversion done (Add invoices table) \n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) diff --git a/kohaversion.pl b/kohaversion.pl index 9075247..aa2eac2 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -16,7 +16,7 @@ the kohaversion is divided in 4 parts : use strict; sub kohaversion { - our $VERSION = '3.06.00.000'; + our $VERSION = 'XXX'; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install -- 1.7.7.3