Bugzilla – Attachment 7692 Details for
Bug 5339
Parcel closing in acq
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Update database patch
0001-Bug-5339-Invoices-management-improvement.patch (text/plain), 5.68 KB, created by
Julian Maurice
on 2012-02-16 15:02:57 UTC
(
hide
)
Description:
Update database patch
Filename:
MIME Type:
Creator:
Julian Maurice
Created:
2012-02-16 15:02:57 UTC
Size:
5.68 KB
patch
obsolete
>From 07c0fb863221070d413faf862e9ca1fd9895fa39 Mon Sep 17 00:00:00 2001 >From: Julian Maurice <julian.maurice@biblibre.com> >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 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 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 5339
:
7072
|
7073
|
7447
|
7448
|
7690
|
7691
|
7692
|
7693
|
7862
|
7863
|
7961
|
7962
|
8330
|
8331
|
8354
|
8355
|
8555
|
8556
|
8664
|
8665
|
8858
|
8948
|
8949
|
9382
|
10135
|
10137
|
10460
|
10934
|
11077
|
11535
|
11574
|
12266
|
12267
|
12352
|
12353
|
12369