Bugzilla – Attachment 7072 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.71 KB, created by
Julian Maurice
on 2012-01-06 15:55:50 UTC
(
hide
)
Description:
Update database patch
Filename:
MIME Type:
Creator:
Julian Maurice
Created:
2012-01-06 15:55:50 UTC
Size:
5.71 KB
patch
obsolete
>From b7f46b011399eb75b27ce9ba6c003a66173fd738 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 | 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 >
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