From: Julian Maurice <[email protected]>
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 37113c2..c4f2ee7 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2607,7 +2607,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,
@@ -2639,7 +2639,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;
@@ -2656,6 +2657,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 0ef0b1f..4d9cede 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4684,6 +4684,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
_______________________________________________
Koha-patches mailing list
[email protected]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
