X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fupdatedatabase.pl;h=d61b4e8cd575f87784823406ddac9f3844916696;hb=642f7386924bc799f0855ecf7baff6197c1ffaa6;hp=e3fc4ae6d5b66a11cc418006f5a1f61a0edca550;hpb=9d9bb45865c9d6c88952f47f52f0e92ddcce0054;p=koha-ffzg.git diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index e3fc4ae6d5..d61b4e8cd5 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -14358,7 +14358,7 @@ if( CheckVersion( $DBversion ) ) { SELECT expirationdate, waitingdate, branchcode FROM reserves WHERE found = 'W' AND priority = 0 - |); + |, { Slice => {} }); my $update_sth = $dbh->prepare(q| UPDATE reserves SET expirationdate = ? @@ -17542,6 +17542,17 @@ if( CheckVersion( $DBversion ) ) { } if ( column_exists( 'issuingrules', 'maxissueqty' ) ) { + # Cleaning invalid rules before, to avoid FK contraints to fail + $dbh->do(q| + DELETE FROM issuingrules WHERE categorycode != '*' AND categorycode NOT IN (SELECT categorycode FROM categories); + |); + $dbh->do(q| + DELETE FROM issuingrules WHERE branchcode != '*' AND branchcode NOT IN (SELECT branchcode FROM branches); + |); + $dbh->do(q| + DELETE FROM issuingrules WHERE itemtype != '*' AND itemtype NOT IN (SELECT itemtype FROM itemtypes); + |); + $dbh->do(" INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value ) SELECT IF(categorycode='*', NULL, categorycode), @@ -17621,6 +17632,17 @@ if( CheckVersion( $DBversion ) ) { $DBversion = '18.12.00.024'; if ( CheckVersion($DBversion) ) { + # Fixup any pre-existing bad suggestedby, manageddate, accepteddate dates + $dbh->do( + "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';" + ); + $dbh->do( + "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';" + ); + $dbh->do( + "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';" + ); + # Add constraint for suggestedby unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) ) { @@ -18077,7 +18099,8 @@ VALUES $DBversion = '18.12.00.051'; if( CheckVersion( $DBversion ) ) { - $dbh->do( "UPDATE borrowers SET login_attempts = ? WHERE login_attempts > ?", undef, C4::Context->preference('FailedLoginAttempts'), C4::Context->preference('FailedLoginAttempts') ); + my $failed_attempts = C4::Context->preference('FailedLoginAttempts'); + $dbh->do( "UPDATE borrowers SET login_attempts = ? WHERE login_attempts > ?", undef, $failed_attempts, $failed_attempts ) if $failed_attempts && $failed_attempts > 0; SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 21336 - Reset login_attempts)\n"; } @@ -18721,7 +18744,7 @@ if ( CheckVersion($DBversion) ) { $dbh->do(q{UPDATE subscriptionhistory SET librariannote = NULL WHERE librariannote = ''}); SetVersion ($DBversion); - print "Upgrade to $DBversion done (Bug 10215 - Increase the size of opacnote and librariannote for table subscriptionhistory)\n"; + print "Upgrade to $DBversion done (Bug 10215: Increase the size of opacnote and librariannote for table subscriptionhistory)\n"; } $DBversion = '19.06.00.003'; @@ -18729,7 +18752,7 @@ if( CheckVersion( $DBversion ) ) { $dbh->do(q{UPDATE systempreferences SET value = REPLACE( value, ' ', '|' ) WHERE variable = 'UniqueItemFields'; }); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22867 - UniqueItemFields preference value should be pipe-delimited)\n"; + print "Upgrade to $DBversion done (Bug 22867: UniqueItemFields preference value should be pipe-delimited)\n"; } $DBversion = '19.06.00.004'; @@ -18737,7 +18760,7 @@ if( CheckVersion( $DBversion ) ) { $dbh->do( 'UPDATE language_descriptions SET description = "Griechisch (Modern 1453-)" WHERE subtag = "el" and type = "language" and lang ="de"' ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22770 - Fix typo in language description for el in German)\n"; + print "Upgrade to $DBversion done (Bug 22770: Fix typo in language description for el in German)\n"; } $DBversion = '19.06.00.005'; @@ -18750,7 +18773,7 @@ if( CheckVersion( $DBversion ) ) { } SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 9834 - Add the reserves.item_level_hold column)\n"; + print "Upgrade to $DBversion done (Bug 9834: Add the reserves.item_level_hold column)\n"; } $DBversion = '19.06.00.006'; @@ -18770,21 +18793,21 @@ if( CheckVersion( $DBversion ) ) { Koha::Plugins->new({ enable_plugins => 1 })->InstallPlugins; SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 21073 - Improve plugin performance)\n"; + print "Upgrade to $DBversion done (Bug 21073: Improve plugin performance)\n"; } $DBversion = '19.06.00.007'; if( CheckVersion( $DBversion ) ) { $dbh->do( "DELETE FROM systempreferences WHERE variable = 'RotationPreventTransfers'" ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22653 - Remove unimplemented RotationPreventTransfers system preference)\n"; + print "Upgrade to $DBversion done (Bug 22653: Remove unimplemented RotationPreventTransfers system preference)\n"; } $DBversion = '19.06.00.008'; if( CheckVersion( $DBversion ) ) { $dbh->do( "UPDATE userflags SET flagdesc = 'Allow staff members to modify permissions and passwords for other staff members' WHERE flag = 'staffaccess'" ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23109 - Improve description of staffaccess permission)\n"; + print "Upgrade to $DBversion done (Bug 23109: Improve description of staffaccess permission)\n"; } $DBversion = '19.06.00.009'; @@ -18795,7 +18818,7 @@ if( CheckVersion( $DBversion ) ) { }); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 17178 - add shortcut to keyboard_shortcuts)\n"; + print "Upgrade to $DBversion done (Bug 17178: add shortcut to keyboard_shortcuts)\n"; } $DBversion = '19.06.00.010'; @@ -18886,7 +18909,7 @@ if( CheckVersion( $DBversion ) ) { } SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 18928 - Move holdallowed, hold_fulfillment_policy, returnbranch to circulation_rules)\n"; + print "Upgrade to $DBversion done (Bug 18928: Move holdallowed, hold_fulfillment_policy, returnbranch to circulation_rules)\n"; } $DBversion = '19.06.00.011'; @@ -18904,7 +18927,7 @@ if( CheckVersion( $DBversion ) ) { } SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 18930 - Move lost item refund rules to circulation_rules table)\n"; + print "Upgrade to $DBversion done (Bug 18930: Move lost item refund rules to circulation_rules table)\n"; } $DBversion = '19.06.00.012'; @@ -18984,7 +19007,7 @@ if ( CheckVersion($DBversion) ) { }); SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 22563 - Fix accounttypes for 'L', 'LR' and 'CR')\n"; + print "Upgrade to $DBversion done (Bug 22563: Fix accounttypes for 'L', 'LR' and 'CR')\n"; } $DBversion = '19.06.00.013'; @@ -18993,7 +19016,7 @@ if ( CheckVersion( $DBversion ) ) { $dbh->do("ALTER TABLE borrower_modifications ADD changed_fields MEDIUMTEXT AFTER verification_token;"); } SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23151 - Add borrower_modifications.changed_fields column)\n"; + print "Upgrade to $DBversion done (Bug 23151: Add borrower_modifications.changed_fields column)\n"; } $DBversion = '19.06.00.014'; @@ -19043,7 +19066,7 @@ if ( CheckVersion($DBversion) ) { }); SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 11573 - Fix accounttypes for 'Rent')\n"; + print "Upgrade to $DBversion done (Bug 11573: Fix accounttypes for 'Rent')\n"; } $DBversion = '19.06.00.015'; @@ -19051,7 +19074,7 @@ if( CheckVersion( $DBversion ) ) { $dbh->do( "UPDATE `search_field` SET `name` = 'date-time-last-modified', `label` = 'date-time-last-modified' WHERE `name` = 'date/time-last-modified'" ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22524 - Fix date/time-last-modified search with Elasticsearch)\n"; + print "Upgrade to $DBversion done (Bug 22524: Fix date/time-last-modified search with Elasticsearch)\n"; } $DBversion = '19.06.00.016'; @@ -19074,7 +19097,7 @@ if( CheckVersion( $DBversion ) ) { ;|); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23396 - Fix missing keyboard_shortcuts table)\n"; + print "Upgrade to $DBversion done (Bug 23396: Fix missing keyboard_shortcuts table)\n"; } $DBversion = '19.06.00.017'; @@ -19149,7 +19172,7 @@ if ( CheckVersion($DBversion) ) { } SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 22610 - Fix accounttypes for SIP2 payments)\n"; + print "Upgrade to $DBversion done (Bug 22610: Fix accounttypes for SIP2 payments)\n"; } $DBversion = '19.06.00.018'; @@ -19267,7 +19290,7 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); print -"Upgrade to $DBversion done (Bug 23228 - Add option to automatically display payment receipt for printing after making a payment)\n"; +"Upgrade to $DBversion done (Bug 23228: Add option to automatically display payment receipt for printing after making a payment)\n"; } $DBversion = '19.06.00.020'; @@ -19278,7 +19301,7 @@ if( CheckVersion( $DBversion ) ) { |); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23416 - Add PreserveSerialNotes syspref)\n"; + print "Upgrade to $DBversion done (Bug 23416: Add PreserveSerialNotes syspref)\n"; } $DBversion = '19.06.00.021'; @@ -19289,7 +19312,7 @@ if( CheckVersion( $DBversion ) ) { |); # Always end with this (adjust the bug info) SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23309 - Can't add new subfields to bibliographic frameworks in strict mode)\n"; + print "Upgrade to $DBversion done (Bug 23309: Can't add new subfields to bibliographic frameworks in strict mode)\n"; } $DBversion = '19.06.00.022'; @@ -19357,7 +19380,7 @@ if ( CheckVersion($DBversion) ) { } SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 14570 - Make it possible to add multiple guarantors to a record)\n"; + print "Upgrade to $DBversion done (Bug 14570: Make it possible to add multiple guarantors to a record)\n"; } $DBversion = '19.06.00.023'; @@ -19368,7 +19391,7 @@ if( CheckVersion( $DBversion ) ) { }); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22258 - Add ElasticsearchMARCFormat preference)\n"; + print "Upgrade to $DBversion done (Bug 22258: Add ElasticsearchMARCFormat preference)\n"; } $DBversion = '19.06.00.024'; @@ -19376,14 +19399,14 @@ if( CheckVersion( $DBversion ) ) { $dbh->do(q{ALTER TABLE accountlines CHANGE COLUMN accounttype accounttype varchar(80) default NULL}); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23539 - accountlines.accounttype should match authorised_values.authorised_value in size)\n"; + print "Upgrade to $DBversion done (Bug 23539: accountlines.accounttype should match authorised_values.authorised_value in size)\n"; } $DBversion = '19.06.00.025'; if( CheckVersion( $DBversion ) ) { $dbh->do( q/INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES (?, ?, ?, ?, ?)/, undef, 'BarcodeSeparators','\s\r\n','','Splitting characters for barcodes','Free' ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 22996 - Add pref BarcodeSeparators)\n"; + print "Upgrade to $DBversion done (Bug 22996: Add pref BarcodeSeparators)\n"; } $DBversion = '19.06.00.026'; @@ -19415,7 +19438,7 @@ if( CheckVersion( $DBversion ) ) { }); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 20691 - Add ability for guarantors to view guarantee's fines in OPAC)\n"; + print "Upgrade to $DBversion done (Bug 20691: Add ability for guarantors to view guarantee's fines in OPAC)\n"; } $DBversion = '19.06.00.027'; @@ -19433,7 +19456,7 @@ if( CheckVersion( $DBversion ) ) { } SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 15497 - Add itemtypes_branches table)\n"; + print "Upgrade to $DBversion done (Bug 15497: Add itemtypes_branches table)\n"; } $DBversion = '19.06.00.028'; @@ -19449,7 +19472,7 @@ if ( CheckVersion($DBversion) ) { }); SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 11573 - Fix accounttypes for 'A')\n"; + print "Upgrade to $DBversion done (Bug 11573: Fix accounttypes for 'A')\n"; } $DBversion = '19.06.00.029'; @@ -19498,7 +19521,7 @@ if ( CheckVersion($DBversion) ) { }); SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 23321 - Add cash_registers table, permissions and preferences)\n"; + print "Upgrade to $DBversion done (Bug 23321: Add cash_registers table, permissions and preferences)\n"; } $DBversion = '19.06.00.030'; @@ -19541,7 +19564,7 @@ if( CheckVersion( $DBversion ) ) { # Always end with this (adjust the bug info) SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 19618 - add club_holds tables)\n"; + print "Upgrade to $DBversion done (Bug 19618: add club_holds tables)\n"; } $DBversion = '19.06.00.031'; @@ -19552,7 +19575,7 @@ if( CheckVersion( $DBversion ) ) { |); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23566 - Add OPACDetailQRCode system preference)\n"; + print "Upgrade to $DBversion done (Bug 23566: Add OPACDetailQRCode system preference)\n"; } $DBversion = '19.06.00.032'; @@ -19575,7 +19598,7 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); print -"Upgrade to $DBversion done (Bug 20589 - Add field boosting and use elastic query fields parameter instead of depricated _all)\n"; +"Upgrade to $DBversion done (Bug 20589: Add field boosting and use elastic query fields parameter instead of depricated _all)\n"; } $DBversion = '19.06.00.033'; @@ -19639,14 +19662,14 @@ if( CheckVersion( $DBversion ) ) { }); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 21701 - PayPal return URL option)\n"; + print "Upgrade to $DBversion done (Bug 21701: PayPal return URL option)\n"; } $DBversion = '19.06.00.038'; if( CheckVersion( $DBversion ) ) { $dbh->do( "UPDATE systempreferences SET variable='PatronAutoComplete' WHERE variable='CircAutocompl' LIMIT 1" ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 23697 - Rename CircAutocompl system preference to PatronAutoComplete)\n"; + print "Upgrade to $DBversion done (Bug 23697: Rename CircAutocompl system preference to PatronAutoComplete)\n"; } $DBversion = '19.06.00.039'; @@ -19660,7 +19683,7 @@ if( CheckVersion( $DBversion ) ) { ; |); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 17179 - Add additional keyboard_shortcuts)\n"; + print "Upgrade to $DBversion done (Bug 17179: Add additional keyboard_shortcuts)\n"; } $DBversion = '19.06.00.040'; @@ -19673,7 +19696,578 @@ if( CheckVersion( $DBversion ) ) { |); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug 17140 - Add pref to allow rounding fines at payment)\n"; + print "Upgrade to $DBversion done (Bug 17140: Add pref to allow rounding fines at payment)\n"; +} + +$DBversion = '19.06.00.041'; +if( CheckVersion( $DBversion ) ) { + my ($socialnetworks) = $dbh->selectrow_array( q| + SELECT value FROM systempreferences WHERE variable='socialnetworks'; + |); + if( $socialnetworks ){ + # If the socialnetworks preference is enabled, enable all social networks + $dbh->do("UPDATE systempreferences SET value = 'email,facebook,linkedin,twitter', explanation = 'email|facebook|linkedin|twitter', type = 'multiple' WHERE variable = 'SocialNetworks'"); + } else { + $dbh->do("UPDATE systempreferences SET value = '', explanation = 'email|facebook|linkedin|twitter', type = 'multiple' WHERE variable = 'SocialNetworks'"); + } + SetVersion ($DBversion); + print "Upgrade to $DBversion done (Bug 22880: Allow granular control of socialnetworks preference)\n"; +} + +$DBversion = '19.06.00.042'; +if( CheckVersion( $DBversion ) ) { + $dbh->do(q{ + INSERT IGNORE INTO systempreferences + ( variable, value, options, explanation, type ) + VALUES + ('CustomCoverImages','0',NULL,'If enabled, the custom cover images will be displayed in the staff client. CustomCoverImagesURL must be defined.','YesNo'), + ('OPACCustomCoverImages','0',NULL,'If enabled, the custom cover images will be displayed at the OPAC. CustomCoverImagesURL must be defined.','YesNo'), + ('CustomCoverImagesURL','',NULL,'Define an URL serving book cover images, using the following patterns: {issn}, {isbn}, {normalized_isbn}, {field$subfield} (use it with CustomCoverImages and/or OPACCustomCoverImages)','free') + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22445: Add new pref *CustomCoverImages*)\n"; +} + +$DBversion = '19.06.00.043'; +if ( CheckVersion($DBversion) ) { + + # Adding account_debit_types + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_debit_types ( + code varchar(80) NOT NULL, + description varchar(200) NULL, + can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, + default_amount decimal(28, 6) NULL, + is_system tinyint(1) NOT NULL DEFAULT 0, + archived tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + # Adding account_debit_types_branches + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_debit_types_branches ( + debit_type_code VARCHAR(80), + branchcode VARCHAR(10), + FOREIGN KEY (debit_type_code) REFERENCES account_debit_types(code) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci + } + ); + + # Populating account_debit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + description, + can_be_added_manually, + default_amount, + is_system + ) + VALUES + ('ACCOUNT', 'Account creation fee', 0, NULL, 1), + ('ACCOUNT_RENEW', 'Account renewal fee', 0, NULL, 1), + ('RESERVE_EXPIRED', 'Hold waiting too long', 0, NULL, 1), + ('LOST', 'Lost item', 1, NULL, 1), + ('MANUAL', 'Manual fee', 1, NULL, 0), + ('NEW_CARD', 'New card fee', 1, NULL, 1), + ('OVERDUE', 'Overdue fine', 0, NULL, 1), + ('PROCESSING', 'Lost item processing fee', 0, NULL, 1), + ('RENT', 'Rental fee', 0, NULL, 1), + ('RENT_DAILY', 'Daily rental fee', 0, NULL, 1), + ('RENT_RENEW', 'Renewal of rental item', 0, NULL, 1), + ('RENT_DAILY_RENEW', 'Rewewal of daily rental item', 0, NULL, 1), + ('RESERVE', 'Hold fee', 0, NULL, 1) + } + ); + + # Update accountype 'Res' to 'RESERVE' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'RESERVE' WHERE accounttype = 'Res' + } + ); + + # Update accountype 'PF' to 'PROCESSING' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'PROCESSING' WHERE accounttype = 'PF' + } + ); + + # Update accountype 'HE' to 'RESERVE_EXPIRED' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'RESERVE_EXPIRED' WHERE accounttype = 'HE' + } + ); + + # Update accountype 'N' to 'NEW_CARD' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'NEW_CARD' WHERE accounttype = 'N' + } + ); + + # Update accountype 'M' to 'MANUAL' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'MANUAL' WHERE accounttype = 'M' + } + ); + + # Catch 'F' cases introduced since bug 22521 + $dbh->do(qq{ + UPDATE + accountlines + SET + accounttype = 'OVERDUE', + status = 'RETURNED' + WHERE + accounttype = 'F'; + }); + + # Moving MANUAL_INV to account_debit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + default_amount, + description, + can_be_added_manually, + is_system + ) + SELECT + authorised_value, + lib, + authorised_value, + 1, + 0 + FROM + authorised_values + WHERE + category = 'MANUAL_INV' + } + ); + + # Update uncaught partial accounttypes left behind after bugs 23539 and 22521 + my $sth = $dbh->prepare( "SELECT code, SUBSTR(code, 1,5) AS subcode FROM account_debit_types" ); + $sth->execute(); + while ( my $row = $sth->fetchrow_hashref ) { + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = ? WHERE accounttype = ? + }, + {}, + ( + $row->{code}, + $row->{subcode} + ) + ); + } + + # Add any unexpected accounttype codes to debit_types as appropriate + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + description, + can_be_added_manually, + default_amount, + is_system + ) + SELECT + DISTINCT(accounttype), + "Unexpected type found during upgrade", + 1, + NULL, + 0 + FROM + accountlines + WHERE + amount >= 0 + } + ); + + # Adding debit_type_code to accountlines + unless ( column_exists('accountlines', 'debit_type_code') ) { + $dbh->do( + qq{ + ALTER TABLE accountlines + ADD + debit_type_code varchar(80) DEFAULT NULL + AFTER + accounttype + } + ); + } + + # Linking debit_type_code in accountlines to code in account_debit_types + unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_debit_type' ) ) { + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE + } + ); + } + + # Populating debit_type_code + $dbh->do( + qq{ + UPDATE accountlines SET debit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_debit_types) + } + ); + + # Remove MANUAL_INV + $dbh->do( + qq{ + DELETE FROM authorised_values WHERE category = 'MANUAL_INV' + } + ); + $dbh->do( + qq{ + DELETE FROM authorised_value_categories WHERE category_name = 'MANUAL_INV' + } + ); + + # Add new permission + $dbh->do( + q{ + INSERT IGNORE INTO permissions (module_bit, code, description) + VALUES + ( + 3, + 'manage_accounts', + 'Manage Account Debit and Credit Types' + ) + } + ); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23049: Add account debit_types)\n"; +} + +$DBversion = '19.06.00.044'; +if ( CheckVersion($DBversion) ) { + + # Adding account_credit_types + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types ( + code varchar(80) NOT NULL, + description varchar(200) NULL, + can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, + is_system tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + # Adding account_credit_types_branches + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types_branches ( + credit_type_code VARCHAR(80), + branchcode VARCHAR(10), + FOREIGN KEY (credit_type_code) REFERENCES account_credit_types(code) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci + } + ); + + # Populating account_credit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + code, + description, + can_be_added_manually, + is_system + ) + VALUES + ('PAYMENT', 'Payment', 0, 1), + ('WRITEOFF', 'Writeoff', 0, 1), + ('FORGIVEN', 'Forgiven', 1, 1), + ('CREDIT', 'Credit', 1, 1), + ('LOST_RETURN', 'Lost item fee refund', 0, 1) + } + ); + + # Adding credit_type_code to accountlines + unless ( column_exists('accountlines', 'credit_type_code') ) { + $dbh->do( + qq{ + ALTER TABLE accountlines + ADD + credit_type_code varchar(80) DEFAULT NULL + AFTER + accounttype + } + ); + } + + # Linking credit_type_code in accountlines to code in account_credit_types + unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) { + $dbh->do( + qq{ + ALTER TABLE accountlines + ADD CONSTRAINT + `accountlines_ibfk_credit_type` + FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) + ON DELETE RESTRICT + ON UPDATE CASCADE + } + ); + } + + # Update accountype 'C' to 'CREDIT' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'CREDIT' WHERE accounttype = 'C' OR accounttype = 'CR' + } + ); + + # Update accountype 'FOR' to 'FORGIVEN' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'FORGIVEN' WHERE accounttype = 'FOR' OR accounttype = 'FORW' + } + ); + + # Update accountype 'Pay' to 'PAYMENT' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'PAYMENT' WHERE accounttype = 'Pay' OR accounttype = 'PAY' + } + ); + + # Update accountype 'W' to 'WRITEOFF' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'WRITEOFF' WHERE accounttype = 'W' OR accounttype = 'WO' + } + ); + + # Add any unexpected accounttype codes to credit_types as appropriate + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + code, + description, + can_be_added_manually, + is_system + ) + SELECT + DISTINCT(accounttype), + "Unexpected type found during upgrade", + 1, + 0 + FROM + accountlines + WHERE + amount < 0 + } + ); + + # Populating credit_type_code + $dbh->do( + qq{ + UPDATE + accountlines + SET + credit_type_code = accounttype, accounttype = NULL + WHERE accounttype IN (SELECT code from account_credit_types) + } + ); + + # Drop accounttype field + $dbh->do( + qq{ + ALTER TABLE accountlines + DROP COLUMN `accounttype` + } + ); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23805: Add account credit_types)\n"; +} + +$DBversion = '19.06.00.045'; +if( CheckVersion( $DBversion ) ) { + $dbh->do( "UPDATE systempreferences SET value = '2' WHERE value = '0' AND variable = 'UsageStats'" ); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23866: Set HEA syspref to prompt for review)\n"; +} + +$DBversion = '19.06.00.046'; +if( CheckVersion( $DBversion ) ) { + $dbh->do(qq{ + UPDATE systempreferences + SET + options = "Calendar|Days|Datedue|Dayweek", + explanation = "Choose the method for calculating due date: select Calendar, Datedue or Dayweek to use the holidays module, and Days to ignore the holidays module" + WHERE + variable = "useDaysMode" + }); + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 15260: Option for extended loan with useDaysMode)\n"; +} + +$DBversion = '19.06.00.047'; +if ( CheckVersion($DBversion) ) { + if ( !TableExists('return_claims') ) { + $dbh->do( + q{ + CREATE TABLE return_claims ( + id int(11) auto_increment, -- Unique ID of the return claim + itemnumber int(11) NOT NULL, -- ID of the item + issue_id int(11) NULL DEFAULT NULL, -- ID of the checkout that triggered the claim + borrowernumber int(11) NOT NULL, -- ID of the patron + notes MEDIUMTEXT DEFAULT NULL, -- Notes about the claim + created_on TIMESTAMP NULL, -- Time and date the claim was created + created_by int(11) NULL DEFAULT NULL, -- ID of the staff member that registered the claim + updated_on TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- Time and date of the latest change on the claim (notes) + updated_by int(11) NULL DEFAULT NULL, -- ID of the staff member that updated the claim + resolution varchar(80) NULL DEFAULT NULL, -- Resolution code (RETURN_CLAIM_RESOLUTION AVs) + resolved_on TIMESTAMP NULL DEFAULT NULL, -- Time and date the claim was resolved + resolved_by int(11) NULL DEFAULT NULL, -- ID of the staff member that resolved the claim + PRIMARY KEY (`id`), + KEY `itemnumber` (`itemnumber`), + CONSTRAINT UNIQUE `issue_id` ( issue_id ), + CONSTRAINT `issue_id` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_items_ibfk` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_borrowers_ibfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_created_by_ibfk` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_updated_by_ibfk` FOREIGN KEY (`updated_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_resolved_by_ibfk` FOREIGN KEY (`resolved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + } + + $dbh->do( + q{ + INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES + ('ClaimReturnedChargeFee', 'ask', 'ask|charge|no_charge', 'Controls whether or not a lost item fee is charged for return claims', 'Choice'), + ('ClaimReturnedLostValue', '', '', 'Sets the LOST AV value that represents "Claims returned" as a lost value', 'Free'), + ('ClaimReturnedWarningThreshold', '', '', 'Sets the number of return claims past which the librarian will be warned the patron has many return claims', 'Integer'); + } + ); + + $dbh->do( + q{ + INSERT IGNORE INTO authorised_value_categories ( category_name ) VALUES + ('RETURN_CLAIM_RESOLUTION'); + } + ); + + $dbh->do( + q{ + INSERT IGNORE INTO `authorised_values` ( category, authorised_value, lib ) + VALUES + ('RETURN_CLAIM_RESOLUTION', 'RET_BY_PATRON', 'Returned by patron'), + ('RETURN_CLAIM_RESOLUTION', 'FOUND_IN_LIB', 'Found in library'); + } + ); + + SetVersion($DBversion); + print +"Upgrade to $DBversion done (Bug 14697: Extend and enhance 'Claims returned' lost status)\n"; +} + +$DBversion = '19.06.00.048'; +if( CheckVersion( $DBversion ) ) { + # you can use $dbh here like: + $dbh->do( qq{ + INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) + VALUES ('OPACShowMusicalInscripts','0','','Display musical inscripts on the OPAC record details page when available.','YesNo'), + ('OPACPlayMusicalInscripts','0','','If displayed musical inscripts, play midi conversion on the OPAC record details page.','YesNo') + } ); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22581: add new OPACShowMusicalInscripts and OPACPlayMusicalInscripts system preferences)\n"; +} + +$DBversion = '19.06.00.049'; +if( CheckVersion( $DBversion ) ) { + + $dbh->do(q{ + INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) + SELECT + 'SuspensionsCalendar', + IF( value='noFinesWhenClosed', 'noSuspensionsWhenClosed', 'ignoreCalendar'), + 'ignoreCalendar|noSuspensionsWhenClosed', + 'Specify whether to use the Calendar in calculating suspensions', + 'Choice' + FROM systempreferences + WHERE variable='finesCalendar'; + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 13958: Add a SuspensionsCalendar syspref)\n"; +} + +$DBversion = '19.06.00.050'; +if( CheckVersion( $DBversion ) ) { + $dbh->do( q{ + INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) + VALUES ('OPACFineNoRenewalsIncludeCredits','1',NULL,'If enabled the value specified in OPACFineNoRenewals should include any unapplied account credits in the calculation','YesNo') + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23293: Add 'OPACFineNoRenewalsIncludeCredits' system preference)\n"; +} + +$DBversion = '19.11.00.000'; +if( CheckVersion( $DBversion ) ) { + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (19.11.00 release)\n"; +} + +$DBversion = '19.12.00.000'; +if( CheckVersion( $DBversion ) ) { + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Dobbie is a free elf...)\n"; +} + +$DBversion = '19.12.00.001'; +if( CheckVersion( $DBversion ) ) { + $dbh->do( "UPDATE marc_subfield_structure SET kohafield = NULL WHERE kohafield = 'bibliosubject.subject';" ); + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 17831 - Remove non-existing bibliosubject.subject from frameworks)\n"; +} + +$DBversion = '19.12.00.002'; +if( CheckVersion( $DBversion ) ) { + $dbh->do(q{ + UPDATE systempreferences SET + variable = 'AllowItemsOnHoldCheckoutSIP', + explanation = 'Do not generate RESERVE_WAITING and RESERVED warning when checking out items reserved to someone else via SIP. This allows self checkouts for those items.' + WHERE variable = 'AllowItemsOnHoldCheckout' + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23233 - Rename AllowItemsOnHoldCheckout syspref)\n"; +} + +$DBversion = '19.12.00.003'; +if( CheckVersion( $DBversion ) ) { + + if( !column_exists( 'library_groups', 'ft_local_hold_group' ) ) { + $dbh->do( "ALTER TABLE library_groups ADD COLUMN ft_local_hold_group tinyint(1) NOT NULL DEFAULT 0 AFTER ft_search_groups_staff" ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22284 - Add ft_local_hold_group column to library_groups)\n"; } # SEE bug 13068