[Koha-patches] [PATCH 1/2] Bug 5339: Invoices management improvement
julian.maurice at biblibre.com
julian.maurice at biblibre.com
Thu Feb 16 16:04:15 CET 2012
From: Julian Maurice <julian.maurice at biblibre.com>
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
More information about the Koha-patches
mailing list