[Koha-patches] [PATCH 12/78] Updatedatabase (big)
paul.poulain at biblibre.com
paul.poulain at biblibre.com
Thu May 28 18:32:22 CEST 2009
From: Paul Poulain <paul.poulain at biblibre.com>
* adding aqcontracts, that will contain contracts attached to a given bookseller. Each basket can be attached to a contract
* adding columns to aqbasket: basket name, note, booksellernote and contractnumber
* adding column to aqorders: uncertainprice, budget_id and statistical fields
* adding table aqbasketgroups: will be used to collate X basket into a single basketgroup
* adding table aqbudgetperiods: will be used to manage budget periods, 1st step to define a budget
* dropping & re-creating a aqbudgets table: completly new content, no update from previous acq
* adding table aqbudget_planning table: will be used to store library budget planning (by ccode, branch, month, and other authorised_values)
* adding column to currencies table: active, that will tell which currency is the used one.
* adding sub permissions for acquisitions module
* adding column to booksellers table: gstrate that will tell the gst rate for the bookseller.
* adding sysprefs: AcqCreateItem (define when item creation is done ordering/recieving/cataloguing), CurrencyFormat
* adding aqordersitems table: link between order and items
* dropping aqorderbreakdown: useless
---
admin/systempreferences.pl | 10 +-
installer/data/mysql/en/mandatory/sysprefs.sql | 3 +
.../1-Obligatoire/unimarc_standard_systemprefs.sql | 3 +
installer/data/mysql/kohastructure.sql | 152 ++++++++---
installer/data/mysql/updatedatabase.pl | 295 ++++++++++++++++++++
5 files changed, 422 insertions(+), 41 deletions(-)
diff --git a/admin/systempreferences.pl b/admin/systempreferences.pl
index 245145d..712fcab 100755
--- a/admin/systempreferences.pl
+++ b/admin/systempreferences.pl
@@ -67,9 +67,13 @@ use C4::Output;
my %tabsysprefs;
# Acquisitions
-$tabsysprefs{acquisitions} = "Acquisitions";
-$tabsysprefs{gist} = "Acquisitions";
-$tabsysprefs{emailPurchaseSuggestions} = "Acquisitions";
+ $tabsysprefs{acquisitions}="Acquisitions";
+ $tabsysprefs{gist}="Acquisitions";
+ $tabsysprefs{emailPurchaseSuggestions}="Acquisitions";
+ $tabsysprefs{RenewSerialAddsSuggestion}="Acquisitions";
+ $tabsysprefs{AcqCreateItem}="Acquisitions";
+ $tabsysprefs{pdfformat}="Acquisitions";
+ $tabsysprefs{CurrencyFormat}="Acquisitions";
# Admin
$tabsysprefs{singleBranchMode} = "Admin";
diff --git a/installer/data/mysql/en/mandatory/sysprefs.sql b/installer/data/mysql/en/mandatory/sysprefs.sql
index cdbfb9b..2c20656 100644
--- a/installer/data/mysql/en/mandatory/sysprefs.sql
+++ b/installer/data/mysql/en/mandatory/sysprefs.sql
@@ -213,6 +213,9 @@ INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('OpacSuppression', '0', '', 'Turn ON the OPAC Suppression feature, requires further setup, ask your system administrator for details', 'YesNo');
-- FIXME: add FrameworksLoaded, noOPACUserLogin, ReadingHistory ?
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('SMSSendDriver','','','Sets which SMS::Send driver is used to send SMS messages.','free');
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('pdfformat','pdfformat/example.pl','Controls what script is used for printing (basketgroups)','','free'));
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('CurrencyFormat','US','US|FR','Determines the display format of currencies. eg: \'36000\' is displayed as \'360 000,00\' in \'FR\' or 360,000.00\' in \'US\'.','Choice');
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('AcqCreateItem','ordering','ordering|receiving|cataloguing','Define when the item is created : when ordering, when receiving, or in cataloguing module','Choice');
INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowRenewalLimitOverride', '0', 'if ON, allows renewal limits to be overridden on the circulation screen',NULL,'YesNo');
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACDisplayRequestPriority','0','','Show patrons the priority level on holds in the OPAC','YesNo');
INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'UseBranchTransferLimits', '0', '', 'If ON, Koha will will use the rules defined in branch_transfer_limits to decide if an item transfer should be allowed.', 'YesNo');
diff --git a/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql b/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
index f815024..a1aa2e5 100644
--- a/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
+++ b/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
@@ -212,6 +212,9 @@ INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo');
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('OpacSuppression', '0', '', 'Turn ON the OPAC Suppression feature, requires further setup, ask your system administrator for details', 'YesNo');
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('SMSSendDriver','','','Détermine le pilote utilisé par SMS::Send pour envoyer des SMS.','free');
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('pdfformat','pdfformat/example.pl','Détermine le script utilisé pour imprimer les groupes de paniers','','free')");
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('CurrencyFormat','US','US|FR','Précise comment les montant financiers sont affichés. Ex: \'36000\' est affiché \'360 000,00\' en \'FR\' ou 360,000.00\' en \'US\'.','Choice');
+INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('AcqCreateItem','ordering','ordering|receiving|cataloguing','Indique quand les exemplaires sont créés : à la commande, à la réception, dans le module catalogage','Choice');
INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowRenewalLimitOverride', '0', "S'il est activé, autorise le dépassement des limites du renouvellement sur la page de circulation",NULL,'YesNo');
INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACDisplayRequestPriority','0','','Afficher l\'ordre des réservation pour les adhérents á l\'opac','YesNo');
INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'UseBranchTransferLimits', '0', '', 'If ON, Koha will will use the rules defined in branch_transfer_limits to decide if an item transfer should be allowed.', 'YesNo');
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c17c4ba..5f0dd77 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -88,33 +88,44 @@ CREATE TABLE `alert` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- Table structure for table `aqbasketgroups`
+--
+
+DROP TABLE IF EXISTS `aqbasketgroups`;
+CREATE TABLE `aqbasketgroups` (
+ `id` int(11) NOT NULL auto_increment,
+ `name` varchar(50) default NULL,
+ `closed` tinyint(1) default NULL,
+ `booksellerid` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `booksellerid` (`booksellerid`),
+ CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
-- Table structure for table `aqbasket`
--
DROP TABLE IF EXISTS `aqbasket`;
CREATE TABLE `aqbasket` (
`basketno` int(11) NOT NULL auto_increment,
+ `basketname` varchar(50) default NULL,
+ `note` mediumtext,
+ `booksellernote` mediumtext,
+ `contractnumber` int(11),
`creationdate` date default NULL,
`closedate` date default NULL,
`booksellerid` int(11) NOT NULL default 1,
`authorisedby` varchar(10) default NULL,
`booksellerinvoicenumber` mediumtext,
+ `basketgroupid` int(11),
PRIMARY KEY (`basketno`),
KEY `booksellerid` (`booksellerid`),
- CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `aqbookfund`
---
-
-DROP TABLE IF EXISTS `aqbookfund`;
-CREATE TABLE `aqbookfund` (
- `bookfundid` varchar(10) NOT NULL default '',
- `bookfundname` mediumtext,
- `bookfundgroup` varchar(5) default NULL,
- `branchcode` varchar(10) NOT NULL default '',
- PRIMARY KEY (`bookfundid`,`branchcode`)
+ KEY `basketgroupid` (`basketgroupid`),
+ KEY `contractnumber` (`contractnumber`),
+ CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
+ CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
+ CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
@@ -156,6 +167,7 @@ CREATE TABLE `aqbooksellers` (
`gstreg` tinyint(4) default NULL,
`listincgst` tinyint(4) default NULL,
`invoiceincgst` tinyint(4) default NULL,
+ `gstrate` decimal(6,4) default NULL,
`discount` float(6,4) default NULL,
`fax` varchar(50) default NULL,
`nocalc` int(11) default NULL,
@@ -168,38 +180,84 @@ CREATE TABLE `aqbooksellers` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--- Table structure for table `aqbudget`
+-- Table structure for table `aqbudgets`
--
-DROP TABLE IF EXISTS `aqbudget`;
-CREATE TABLE `aqbudget` (
- `bookfundid` varchar(10) NOT NULL default '',
- `startdate` date NOT NULL default 0,
- `enddate` date default NULL,
- `budgetamount` decimal(13,2) default NULL,
- `aqbudgetid` tinyint(4) NOT NULL auto_increment,
- `branchcode` varchar(10) default NULL,
- PRIMARY KEY (`aqbudgetid`)
+DROP TABLE IF EXISTS `aqbudgets`;
+CREATE TABLE `aqbudgets` (
+ `budget_id` int(11) NOT NULL auto_increment,
+ `budget_parent_id` int(11) default NULL,
+ `budget_code` varchar(30) default NULL,
+ `budget_name` varchar(80) default NULL,
+ `budget_branchcode` varchar(10) default NULL,
+ `budget_amount` decimal(28,6) NULL default '0.00',
+ `budget_amount_sublevel` decimal(28,6) NULL default '0.00',
+ `budget_encumb` decimal(28,6) NULL default '0.00',
+ `budget_expend` decimal(28,6) NULL default '0.00',
+ `budget_notes` mediumtext,
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `budget_period_id` int(11) default NULL,
+ `sort1_authcat` varchar(80) default NULL,
+ `sort2_authcat` varchar(80) default NULL,
+ `budget_owner_id` int(11) default NULL,
+ `budget_permission` int(1) default '0',
+ PRIMARY KEY (`budget_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
--
--- Table structure for table `aqorderbreakdown`
+-- Table structure for table `aqbudgetperiods`
--
-DROP TABLE IF EXISTS `aqorderbreakdown`;
-CREATE TABLE `aqorderbreakdown` (
- `ordernumber` int(11) default NULL,
- `linenumber` int(11) default NULL,
- `branchcode` varchar(10) default NULL,
- `bookfundid` varchar(10) NOT NULL default '',
- `allocation` smallint(6) default NULL,
- KEY `ordernumber` (`ordernumber`),
- KEY `bookfundid` (`bookfundid`),
- CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
+
+DROP TABLE IF EXISTS `aqbudgetperiods`;
+CREATE TABLE `aqbudgetperiods` (
+ `budget_period_id` int(11) NOT NULL auto_increment,
+ `budget_period_startdate` date NOT NULL,
+ `budget_period_enddate` date NOT NULL,
+ `budget_period_active` tinyint(1) default '0',
+ `budget_period_description` mediumtext,
+ `budget_period_total` decimal(28,6),
+ `budget_period_locked` tinyint(1) default NULL,
+ `sort1_authcat` varchar(10) default NULL,
+ `sort2_authcat` varchar(10) default NULL,
+ PRIMARY KEY (`budget_period_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `aqbudgets_planning`
+--
+
+DROP TABLE IF EXISTS `aqbudgets_planning`;
+CREATE TABLE `aqbudgets_planning` (
+ `plan_id` int(11) NOT NULL auto_increment,
+ `budget_id` int(11) NOT NULL,
+ `budget_period_id` int(11) NOT NULL,
+ `estimated_amount` decimal(28,6) default NULL,
+ `authcat` varchar(30) NOT NULL,
+ `authvalue` varchar(30) NOT NULL,
+ PRIMARY KEY (`plan_id`),
+ CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- Table structure for table 'aqcontract'
+--
+
+DROP TABLE IF EXISTS `aqcontract`;
+CREATE TABLE `aqcontract` (
+ `contractnumber` int(11) NOT NULL auto_increment,
+ `contractstartdate` date default NULL,
+ `contractenddate` date default NULL,
+ `contractname` varchar(50) default NULL,
+ `contractdescription` mediumtext,
+ `booksellerid` int(11) not NULL,
+ PRIMARY KEY (`contractnumber`),
+ CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
+ REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+--
-- Table structure for table `aqorderdelivery`
--
@@ -220,7 +278,6 @@ DROP TABLE IF EXISTS `aqorders`;
CREATE TABLE `aqorders` (
`ordernumber` int(11) NOT NULL auto_increment,
`biblionumber` int(11) default NULL,
- `title` mediumtext,
`entrydate` date default NULL,
`quantity` smallint(6) default NULL,
`currency` varchar(3) default NULL,
@@ -244,14 +301,32 @@ CREATE TABLE `aqorders` (
`rrp` decimal(13,2) default NULL,
`ecost` decimal(13,2) default NULL,
`gst` decimal(13,2) default NULL,
+ `budget_id` int(11) NOT NULL,
+ `budgetgroup_id` int(11) NOT NULL,
`budgetdate` date default NULL,
`sort1` varchar(80) default NULL,
`sort2` varchar(80) default NULL,
+ `sort1_authcat` varchar(10) default NULL,
+ `sort2_authcat` varchar(10) default NULL,
+ `uncertainprice` tinyint(1),
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
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 SET NULL
+ CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `aqorders_items`
+--
+
+DROP TABLE IF EXISTS `aqorders_items`;
+CREATE TABLE `aqorders_items` (
+ `ordernumber` int(11) NOT NULL,
+ `itemnumber` int(11) NOT NULL,
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ PRIMARY KEY (`itemnumber`),
+ KEY `ordernumber` (`ordernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
@@ -765,6 +840,7 @@ CREATE TABLE `currency` (
`symbol` varchar(5) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rate` float(7,5) default NULL,
+ `active` tinyint(1) default NULL,
PRIMARY KEY (`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index dc1fbe9..a58567f 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -2454,6 +2454,301 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
# borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
$dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
+
+$DBversion = '3.01.00.020';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do(<<'END_SQL');
+CREATE TABLE IF NOT EXISTS `aqcontract` (
+ `contractnumber` int(11) NOT NULL auto_increment,
+ `contractstartdate` date default NULL,
+ `contractenddate` date default NULL,
+ `contractname` varchar(50) default NULL,
+ `contractdescription` mediumtext,
+ `booksellerid` int(11) not NULL,
+ PRIMARY KEY (`contractnumber`),
+ CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
+ REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+END_SQL
+ print "Upgrade to $DBversion done (adding aqcontract table)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.021';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
+ $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
+ $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
+ $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
+ $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
+ print "Upgrade to $DBversion done (edit aqbasket table done)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.022';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
+
+ print "Upgrade to $DBversion done (adding uncertainprices)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.023';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
+ `id` int(11) NOT NULL auto_increment,
+ `name` varchar(50) default NULL,
+ `closed` tinyint(1) default NULL,
+ `booksellerid` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `booksellerid` (`booksellerid`),
+ CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
+ $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
+ $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE");
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat/example.pl','Controls what script is used for printing (basketgroups)','','free')");
+ print "Upgrade to $DBversion done (adding basketgroups)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.024';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
+ $dbh->do(qq|
+ CREATE TABLE `aqbudgetperiods` (
+ `budget_period_id` int(11) NOT NULL auto_increment,
+ `budget_period_startdate` date NOT NULL,
+ `budget_period_enddate` date NOT NULL,
+ `budget_period_active` tinyint(1) default '0',
+ `budget_period_description` mediumtext,
+ `budget_period_locked` tinyint(1) default NULL,
+ `sort1_authcat` varchar(10) default NULL,
+ `sort2_authcat` varchar(10) default NULL,
+ PRIMARY KEY (`budget_period_id`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
+
+# SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
+# BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
+
+ DropAllForeignKeys('aqbudget');
+ $dbh->do("drop table aqbudget;");
+
+ $dbh->do("CREATE TABLE `aqbudgets` (
+ `budget_id` int(11) NOT NULL auto_increment,
+ `budget_parent_id` int(11) default NULL,
+ `budget_code` varchar(30) default NULL,
+ `budget_name` varchar(80) default NULL,
+ `budget_branchcode` varchar(10) default NULL,
+ `budget_amount` decimal(13,2) NOT NULL default '0.00',
+ `budget_encumb` tinyint(3) default NULL,
+ `budget_expend` tinyint(3) default NULL,
+ `budget_notes` mediumtext,
+ `budget_desciption` mediumtext,
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `budget_period_id` int(11) default NULL,
+ `sort1_authcat` varchar(80) default NULL,
+ `sort2_authcat` varchar(80) default NULL,
+ `owner` tinyint(11) default NULL,
+ `budget_owner_id` tinyint(11) default NULL,
+ `budget_permission` int(1) default '0',
+ PRIMARY KEY (`budget_id`),
+ CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
+
+ $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
+ $dbh->do("CREATE TABLE `aqbudgets_planning` (
+ `plan_id` int(11) NOT NULL auto_increment,
+ `budget_id` int(11) NOT NULL,
+ `budget_period_id` int(11) NOT NULL,
+ `estimated_amount` decimal(28,6) default NULL,
+ `authcat` varchar(30) NOT NULL,
+ `authvalue` varchar(30) NOT NULL,
+ PRIMARY KEY (`plan_id`),
+ CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
+
+ $dbh->do("ALTER TABLE `aqorders`
+ ADD COLUMN `budget_id` tinyint(4) NOT NULL,
+ ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
+ ADD COLUMN `sort1_authcat` varchar(10) default NULL,
+ ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
+
+
+ $dbh->do("ALTER TABLE `aqorderbreakdown`
+ ADD COLUMN `budget_id` int(11) NOT NULL" );
+
+
+# $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
+
+ print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.025';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+
+# removes 'aqbudgets' NOT NULL
+ $dbh->do("ALTER table `aqbudgets` MODIFY COLUMN `budget_amount` decimal(28,6) ");
+ $dbh->do("ALTER table `aqbudgets` ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount` ");
+ $dbh->do("ALTER table `aqbudgets` DROP COLUMN `owner` ");
+ $dbh->do("ALTER table `aqbudgets` DROP COLUMN `budget_desciption` ");
+ # $dbh->do("ALTER table `aqbudgets` DROP COLUMN `budgetgroup_id` ");
+ $dbh->do("ALTER table `aqbudgets` MODIFY COLUMN `budget_encumb` decimal(28,6) default '0.00' ");
+ $dbh->do("ALTER table `aqbudgets` MODIFY COLUMN `budget_expend` decimal(28,6) default '0.00' ");
+
+ print "Upgrade to $DBversion done (adding `budget_amount_sublevel` colmn, and removing temp columns )\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.026';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+
+# removes 'aqbudgets' NOT NULL
+ $dbh->do("ALTER TABLE aqorderbreakdown DROP FOREIGN KEY aqorderbreakdown_ibfk_2 ");
+ $dbh->do("ALTER TABLE aqorderbreakdown DROP COLUMN bookfundid ");
+ $dbh->do("ALTER TABLE aqorderbreakdown ADD KEY budget_id (budget_id)" );
+
+ print "Upgrade to $DBversion done (aqorderbreakdown table tidy)\n";
+ SetVersion ($DBversion);
+}
+
+
+$DBversion = '3.01.00.027';
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+
+# removes 'aqbudgets' NOT NULL
+# $dbh->do("ALTER TABLE aqorderbreakdown ADD COLUMN budget_id int(11) " );
+# $dbh->do("ALTER TABLE aqorderbreakdown ADD KEY budget_id (budget_id)" );
+
+ print "Upgrade to $DBversion done (aqorderbreakdown table tidy2)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = '3.01.00.028';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
+ print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
+ SetVersion($DBversion);
+}
+
+
+$DBversion = '3.01.00.029';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
+
+ print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '3.01.00.030';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
+ (11, 'vendors_manage', 'Manage vendors'),
+ (11, 'contracts_manage', 'Manage contracts'),
+ (11, 'period_manage', 'Manage periods'),
+ (11, 'budget_manage', 'Manage budgets'),
+ (11, 'budget_modify', 'Modify budget (can''t create lines, but can modify existing ones)'),
+ (11, 'planning_manage', 'Manage budget plannings'),
+ (11, 'order_manage', 'Manage orders & basket'),
+ (11, 'group_manage', 'Manage orders & basketgroups'),
+ (11, 'order_receive', 'Manage orders & basket')
+ ");
+
+ print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '3.01.00.031';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("ALTER TABLE aqbudgets CHANGE COLUMN budget_owner_id budget_owner_id int(11)");
+
+ print "Upgrade to $DBversion done (changes 'budget_owner_id' col to int(11)\n";
+ SetVersion($DBversion);
+}
+
+
+$DBversion = '3.01.00.032';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(qq| INSERT INTO permissions (module_bit, code, description) VALUES
+ (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets) )") |);
+
+ print "Upgrade to $DBversion done (small budget perm change)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '3.01.00.033';
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(5,2) default NULL");
+ print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.01.00.034";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ if (C4::Context->preference("opaclanguages") eq "fr") {
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering','Définit quand l'exemplaire est créé : à la commande, à la livraison, au catalogage','ordering|receiving|cataloguing','Choice')");
+ } else {
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering','Define when the item is created : when ordering, when receiving, or in cataloguing module','ordering|receiving|cataloguing','Choice')");
+ }
+ print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.01.00.035";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do(qq|
+ CREATE TABLE `aqorders_items` (
+ `ordernumber` int(11) NOT NULL,
+ `itemnumber` int(11) NOT NULL,
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ PRIMARY KEY (`itemnumber`),
+ KEY `ordernumber` (`ordernumber`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+ );
+
+ $dbh->do(qq| DROP TABLE aqorderbreakdown |);
+ print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.01.00.036";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ my $query = "SELECT * FROM `aqbooksellers`";
+ my $sth = $dbh->prepare($query);
+ $sth->execute;
+ my $booksellers=$sth->fetchall_arrayref({});
+ $sth->finish;
+ $dbh->do("ALTER TABLE `aqbooksellers` DROP COLUMN `gstrate`");
+ $dbh->do("ALTER TABLE `aqbooksellers` ADD COLUMN `gstrate` decimal(6,4) default NULL");
+ for my $bookseller (@$booksellers) {
+ my $sth = $dbh->prepare("UPDATE aqbooksellers SET gstrate=? WHERE id=?");
+ $sth->execute($bookseller->{gstrate} / 100, $bookseller->{id});
+ $sth->finish;
+ }
+ print "Upgrade to $DBversion done (modify gstrate to be consistent with syspref)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.01.00.037";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('CurrencyFormat','US','US|FR','Determines the display format of currencies. eg: ''36000'' is displayed as ''360 000,00'' in ''FR'' or 360,000.00'' in ''US''.','Choice') #);
+
+ print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.01.00.038";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER table aqorders drop column title");
+
+ print "Upgrade to $DBversion done (dropped the title column from the aqorders table)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.01.00.039";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
+ print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
SetVersion ($DBversion);
}
--
1.6.0.4
More information about the Koha-patches
mailing list