From a3dfe58e014c316687fffdc1a38b4c7618ba5cfc Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Tue, 28 Apr 2009 21:30:15 +0200 Subject: [PATCH] Updatedatabase (big) * 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 +- .../data/mysql/en/mandatory/sysprefs.sql | 3 + .../unimarc_standard_systemprefs.sql | 7 + installer/data/mysql/kohastructure.sql | 151 ++++++--- installer/data/mysql/updatedatabase.pl | 316 ++++++++++++++++++ 5 files changed, 446 insertions(+), 41 deletions(-) diff --git a/admin/systempreferences.pl b/admin/systempreferences.pl index e96c109d60..fa7c749549 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 209ef95570..eabecd1ba5 100644 --- a/installer/data/mysql/en/mandatory/sysprefs.sql +++ b/installer/data/mysql/en/mandatory/sysprefs.sql @@ -215,6 +215,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 eb32f4bf4d..95aacdf58e 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 @@ -217,6 +217,13 @@ 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,explanation,options,type) VALUES('MergeAuthoritiesOnUpdate', '0', 'Si Activé, met à jour toutes les notices bibliographiques afférentes dès la modification d''une autorité',NULL,'YesNo'); +INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowNotForLoanOverride', '0', 'Si activé, permet au bibliothécaire de choisir de prêter tout de même un exemplaire normalement exclu du prêt',NULL,'YesNo'); +INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewalPeriodBase', 'date_due', 'Permet de déterminer si la période de renouvellement doit être calculée sur la date de retour ou sur le jour du renouvellement','date_due|now','Choice'); +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'); +>>>>>>> Updatedatabase (big):installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql 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 eaf47e09c2..a4b1e2c35d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -87,6 +87,21 @@ CREATE TABLE `alert` ( KEY `type` (`type`,`externalid`) ) 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` -- @@ -94,27 +109,23 @@ CREATE TABLE `alert` ( 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,37 +180,82 @@ 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`) +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 +277,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 +300,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; -- @@ -775,6 +849,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 10c3451fbe..c8e5a61dc9 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -2438,6 +2438,322 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.01.00.035"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacPrivacy', '0', 'if ON, allows patrons to define their privacy rules (reading history)',NULL,'YesNo')"); + # create a new syspref for the 'Mr anonymous' patron + $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AnonymousPatron', '0', \"Set the identifier (borrowernumber) of the 'Mister anonymous' patron. Used for Suggestion and reading history privacy\",NULL,'')"); + # fill AnonymousPatron with AnonymousSuggestion value (copy) + my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'"); + $sth->execute; + my ($value) = $sth->fetchrow(); + $dbh->do("UPDATE systempreferences SET value=$value WHERE variable='AnonymousPatron'"); + # set AnonymousSuggestion do YesNo + # 1st, set the value (1/True if it had a borrowernumber) + $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0"); + # 2nd, change the type to Choice + $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'"); + # 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); +} + $DBversion = '3.01.00.035'; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; }); -- 2.39.5