INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
});
- print "Upgrade to $DBversion done (Bug 16274 - Make the selfregistration branchcode selection configurable)\n";
+ print "Upgrade to $DBversion done (Bug 16276: Add a new pref TrackLastPatronActivity and new column borrowers.lastseen)\n";
SetVersion($DBversion);
}
$DBversion = '16.12.00.032';
if( CheckVersion( $DBversion ) ) {
require Koha::Calendar;
- require Koha::Holds;
$dbh->do(q{
INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
VALUES ('ExcludeHolidaysFromMaxPickUpDelay', '0', 'If ON, reserves max pickup delay takes into account the closed days.', NULL, 'Integer');
});
- my $waiting_holds = Koha::Holds->search({ found => 'W', priority => 0 });
+ my $waiting_holds = $dbh->selectall_arrayref(q|
+ SELECT expirationdate, waitingdate, branchcode
+ FROM reserves
+ WHERE found = 'W' AND priority = 0
+ |, { Slice => {} });
+ my $update_sth = $dbh->prepare(q|
+ UPDATE reserves
+ SET expirationdate = ?
+ WHERE reserve_id = ?
+ |);
my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
- while ( my $hold = $waiting_holds->next ) {
+ for my $hold ( @$waiting_holds ) {
my $requested_expiration;
- if ($hold->expirationdate) {
- $requested_expiration = dt_from_string($hold->expirationdate);
+ if ($hold->{expirationdate}) {
+ $requested_expiration = dt_from_string($hold->{expirationdate});
}
- my $expirationdate = dt_from_string($hold->waitingdate);
+ my $expirationdate = dt_from_string($hold->{waitingdate});
if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) {
- my $calendar = Koha::Calendar->new( branchcode => $hold->branchcode );
+ my $calendar = Koha::Calendar->new( branchcode => $hold->{branchcode} );
$expirationdate = $calendar->days_forward( $expirationdate, $max_pickup_delay );
} else {
$expirationdate->add( days => $max_pickup_delay );
}
my $cmp = $requested_expiration ? DateTime->compare($requested_expiration, $expirationdate) : 0;
- $hold->expirationdate($cmp == -1 ? $requested_expiration->ymd : $expirationdate->ymd)->store;
+ $update_sth->execute($cmp == -1 ? $requested_expiration->ymd : $expirationdate->ymd, $hold->{reserve_id});
}
SetVersion( $DBversion );
unless ( foreign_key_exists('aqorders', 'aqorders_created_by') ) {
$dbh->do( "ALTER TABLE aqorders ADD CONSTRAINT aqorders_created_by FOREIGN KEY (created_by) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE;" );
}
- $dbh->do( "UPDATE aqorders, aqbasket SET aqorders.created_by = aqbasket.authorisedby WHERE aqorders.basketno = aqbasket.basketno AND aqorders.created_by IS NULL;" );
+ $dbh->do( "UPDATE aqbasket LEFT JOIN borrowers ON ( aqbasket.authorisedby = borrowers.borrowernumber ) SET aqbasket.authorisedby = NULL WHERE borrowers.borrowernumber IS NULL;" );
+ $dbh->do( "UPDATE aqorders LEFT JOIN aqbasket ON ( aqorders.basketno = aqbasket.basketno ) SET aqorders.created_by = aqbasket.authorisedby WHERE aqorders.created_by IS NULL;" );
}
SetVersion( $DBversion );
print "Upgrade to $DBversion done (Bug 12395 - Save order line's creator)\n";
ADD COLUMN class_split_rule varchar(10) NOT NULL default ''
AFTER class_sort_rule
|);
+
$dbh->do(q|
UPDATE class_sources
SET class_split_rule = class_sort_rule
|);
$dbh->do(q|
+ UPDATE class_sources
+ SET class_split_rule = 'generic'
+ WHERE class_split_rule NOT IN('dewey', 'generic', 'lcc')
+ |);
+
+ $dbh->do(q|
INSERT INTO class_split_rules(class_split_rule, description, split_routine)
VALUES
('dewey', 'Default sorting rules for DDC', 'Dewey'),
INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) VALUES ('FallbackToSMSIfNoEmail', 0, 'Enable|Disable', 'Send messages by SMS if no patron email is defined', 'YesNo');
});
SetVersion( $DBversion );
- print "Upgrade to $DBversion done (Bug 17047 - Mana knowledge base)\n";
+ print "Upgrade to $DBversion done (Bug 21241 - Add FallbackToSMSIfNoEmail syspref )\n";
}
$DBversion = '18.12.00.010';
print "Upgrade to $DBversion done (Bug 13515 - Add a FOREIGN KEY constaint on messages.borrowernumber)\n";
}
-# SEE bug 13068
-# if there is anything in the atomicupdate, read and execute it.
+$DBversion = '18.12.00.015';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE action_logs SET info = REPLACE(info,'cardnumber_replaced','cardnumber') WHERE module='MEMBERS' AND action='MODIFY'" );
+ $dbh->do( "UPDATE action_logs SET info = REPLACE(info,'previous_cardnumber','before') WHERE module='MEMBERS' AND action='MODIFY'" );
+ $dbh->do( "UPDATE action_logs SET info = REPLACE(info,'new_cardnumber','after') WHERE module='MEMBERS' AND action='MODIFY'" );
-my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/';
-opendir( my $dirh, $update_dir );
-foreach my $file ( sort readdir $dirh ) {
- next if $file !~ /\.(sql|perl)$/; #skip other files
- next if $file eq 'skeleton.perl'; # skip the skeleton file
- print "DEV atomic update: $file\n";
- if ( $file =~ /\.sql$/ ) {
- my $installer = C4::Installer->new();
- my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
- } elsif ( $file =~ /\.perl$/ ) {
- my $code = read_file( $update_dir . $file );
- eval $code;
- say "Atomic update generated errors: $@" if $@;
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 3820 - Update patron modification logs)\n";
+}
+
+$DBversion = '18.12.00.016';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( !column_exists( 'illrequests', 'status_alias' ) ) {
+ # Fresh upgrade, just add the column and constraint
+ $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias varchar(80) DEFAULT NULL AFTER status" );
+ } else {
+ # Migrate all existing foreign keys from referencing authorised_values.id
+ # to referencing authorised_values.authorised_value
+ # First remove the foreign key constraint and index
+ if ( foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
+ $dbh->do( "ALTER TABLE illrequests DROP FOREIGN KEY illrequests_safk");
+ }
+ if ( index_exists( 'illrequests', 'illrequests_safk' ) ) {
+ $dbh->do( "DROP INDEX illrequests_safk ON illrequests" );
+ }
+ # Now change the illrequests.status_alias column definition from int to varchar
+ $dbh->do( "ALTER TABLE illrequests MODIFY COLUMN status_alias varchar(80)" );
+ # Now replace all references to authorised_values.id with their
+ # corresponding authorised_values.authorised_value
+ my $sth = $dbh->prepare( "SELECT illrequest_id, status_alias FROM illrequests WHERE status_alias IS NOT NULL" );
+ $sth->execute();
+ while (my @row = $sth->fetchrow_array()) {
+ my $r_id = $row[0];
+ my $av_id = $row[1];
+ # Get the authorised value's authorised_value value
+ my ($av_val) = $dbh->selectrow_array( "SELECT authorised_value FROM authorised_values WHERE id = ?", {}, $av_id );
+ # Now update illrequests.status_alias
+ if ($av_val) {
+ $dbh->do( "UPDATE illrequests SET status_alias = ? WHERE illrequest_id = ?", {}, ($av_val, $r_id) );
+ }
+ }
}
+ if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
+ $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
+ }
+ $dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'");
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20581 - Allow manual selection of custom ILL request statuses)\n";
}
-=head1 FUNCTIONS
+$DBversion = '18.12.00.017';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'fine_increase' ), ( 'fine_decrease' );
+ });
+ $dbh->do(q{
+ UPDATE account_offsets SET type = 'fine_increase' WHERE type = 'Fine Update' AND amount > 0;
+ });
+ $dbh->do(q{
+ UPDATE account_offsets SET type = 'fine_decrease' WHERE type = 'Fine Update' AND amount < 0;
+ });
+
+ $dbh->do(q{
+ DELETE FROM account_offset_types WHERE type = 'Fine Update';
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21747 - Update account_offset_types to include 'fine_increase' and 'fine_decrease')\n";
+}
-=head2 TableExists($table)
+$DBversion = '18.12.00.018';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE `search_field` SET `name` = 'date-of-publication', `label` = 'date-of-publication' WHERE `name` = 'pubdate'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'title-series', `label` = 'title-series' WHERE `name` = 'se'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'identifier-standard', `label` = 'identifier-standard' WHERE `name` = 'identifier-standard'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'author', `label` = 'author' WHERE `name` = 'author'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'control-number', `label` = 'control-number' WHERE `name` = 'control-number'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'place-of-publication', `label` = 'place-of-publication' WHERE `name` = 'place'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'date-of-acquisition', `label` = 'date-of-acquisition' WHERE `name` = 'acqdate'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'isbn', `label` = 'isbn' WHERE `name` = 'isbn'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'koha-auth-number', `label` = 'koha-auth-number' WHERE `name` = 'an'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'subject', `label` = 'subject' WHERE `name` = 'subject'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'publisher', `label` = 'publisher' WHERE `name` = 'publisher'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'record-source', `label` = 'record-source' WHERE `name` = 'record-source'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'title', `label` = 'title' WHERE `name` = 'title'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'local-classification', `label` = 'local-classification' WHERE `name` = 'local-classification'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'bib-level', `label` = 'bib-level' WHERE `name` = 'bib-level'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'microform-generation', `label` = 'microform-generation' WHERE `name` = 'microform-generation'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'material-type', `label` = 'material-type' WHERE `name` = 'material-type'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'bgf-number', `label` = 'bgf-number' WHERE `name` = 'bgf-number'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'number-db', `label` = 'number-db' WHERE `name` = 'number-db'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'number-natl-biblio', `label` = 'number-natl-biblio' WHERE `name` = 'number-natl-biblio'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'number-legal-deposit', `label` = 'number-legal-deposit' WHERE `name` = 'number-legal-deposit'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'issn', `label` = 'issn' WHERE `name` = 'issn'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'local-number', `label` = 'local-number' WHERE `name` = 'local-number'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'suppress', `label` = 'supress' WHERE `name` = 'suppress'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'bnb-card-number', `label` = 'bnb-card-number' WHERE `name` = 'bnb-card-number'" );
+ $dbh->do( "UPDATE `search_field` SET `name` = 'date/time-last-modified', `label` = 'date/time-last-modified' WHERE `name` = 'date-time-last-modified'" );
+ $dbh->do( "DELETE FROM `search_field` WHERE `name` = 'lc-cardnumber'" );
+ $dbh->do( "DELETE FROM `search_marc_map` WHERE `id` NOT IN(SELECT `search_marc_map_id` FROM `search_marc_to_field`)" );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 19575 - Use canonical field names and resolve aliased fields)\n";
+}
+
+$DBversion = '18.12.00.019';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'Reserve Fee' );
+ });
-=cut
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21728 - Add 'Reserve Fee' to the account_offset_types table if missing)\n";
+}
-sub TableExists {
- my $table = shift;
- eval {
- local $dbh->{PrintError} = 0;
- local $dbh->{RaiseError} = 1;
- $dbh->do(qq{SELECT * FROM $table WHERE 1 = 0 });
- };
- return 1 unless $@;
- return 0;
+$DBversion = '18.12.00.020';
+if( CheckVersion( $DBversion ) ) {
+ if ( TableExists( 'branch_borrower_circ_rules' ) ) {
+ if ( column_exists( 'branch_borrower_circ_rules', 'maxissueqty' ) ) {
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT categorycode, branchcode, NULL, 'patron_maxissueqty', COALESCE( maxissueqty, '' )
+ FROM branch_borrower_circ_rules
+ ");
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT categorycode, branchcode, NULL, 'patron_maxonsiteissueqty', COALESCE( maxonsiteissueqty, '' )
+ FROM branch_borrower_circ_rules
+ ");
+ $dbh->do("DROP TABLE branch_borrower_circ_rules");
+ }
+ }
+
+ if ( TableExists( 'default_borrower_circ_rules' ) ) {
+ if ( column_exists( 'default_borrower_circ_rules', 'maxissueqty' ) ) {
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT categorycode, NULL, NULL, 'patron_maxissueqty', COALESCE( maxissueqty, '' )
+ FROM default_borrower_circ_rules
+ ");
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT categorycode, NULL, NULL, 'patron_maxonsiteissueqty', COALESCE( maxonsiteissueqty, '' )
+ FROM default_borrower_circ_rules
+ ");
+ $dbh->do("DROP TABLE default_borrower_circ_rules");
+ }
+ }
+
+ if ( column_exists( 'default_circ_rules', 'maxissueqty' ) ) {
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'patron_maxissueqty', COALESCE( maxissueqty, '' )
+ FROM default_circ_rules
+ ");
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'patron_maxonsiteissueqty', COALESCE( maxonsiteissueqty, '' )
+ FROM default_circ_rules
+ ");
+ $dbh->do("ALTER TABLE default_circ_rules DROP COLUMN maxissueqty, DROP COLUMN maxonsiteissueqty");
+ }
+
+ if ( column_exists( 'default_branch_circ_rules', 'maxissueqty' ) ) {
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, NULL, 'patron_maxissueqty', COALESCE( maxissueqty, '' )
+ FROM default_branch_circ_rules
+ ");
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'patron_maxonsiteissueqty', COALESCE( maxonsiteissueqty, '' )
+ FROM default_branch_circ_rules
+ ");
+ $dbh->do("ALTER TABLE default_branch_circ_rules DROP COLUMN maxissueqty, DROP COLUMN maxonsiteissueqty");
+ }
+
+ 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),
+ IF(branchcode='*', NULL, branchcode),
+ IF(itemtype='*', NULL, itemtype),
+ 'maxissueqty',
+ COALESCE( maxissueqty, '' )
+ FROM issuingrules
+ ");
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT IF(categorycode='*', NULL, categorycode),
+ IF(branchcode='*', NULL, branchcode),
+ IF(itemtype='*', NULL, itemtype),
+ 'maxonsiteissueqty',
+ COALESCE( maxonsiteissueqty, '' )
+ FROM issuingrules
+ ");
+ $dbh->do("ALTER TABLE issuingrules DROP COLUMN maxissueqty, DROP COLUMN maxonsiteissueqty");
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18925 - Move maxissueqty and maxonsiteissueqty to circulation_rules)\n";
+}
+
+$DBversion = '18.12.00.021';
+if ( CheckVersion($DBversion) ) {
+
+ if ( !column_exists( 'itemtypes', 'rentalcharge_daily' ) ) {
+ $dbh->do("ALTER TABLE `itemtypes` ADD COLUMN `rentalcharge_daily` decimal(28,6) default NULL AFTER `rentalcharge`");
+ }
+
+ if ( !column_exists( 'itemtypes', 'rentalcharge_hourly' ) ) {
+ $dbh->do("ALTER TABLE `itemtypes` ADD COLUMN `rentalcharge_hourly` decimal(28,6) default NULL AFTER `rentalcharge_daily`");
+ }
+
+ if ( column_exists( 'itemtypes', 'rental_charge_daily' ) ) {
+ $dbh->do("UPDATE `itemtypes` SET `rentalcharge_daily` = `rental_charge_daily`");
+ $dbh->do("ALTER TABLE `itemtypes` DROP COLUMN `rental_charge_daily`");
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 20912 - Support granular rental charges)\n";
+}
+
+$DBversion = '18.12.00.022';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO permissions (module_bit,code,description)
+ VALUES
+ (3,'manage_additional_fields','Add, edit, or delete additional custom fields for baskets or subscriptions (also requires order_manage or edit_subscription permissions)')
+ });
+ $dbh->do( q{
+ INSERT INTO user_permissions (borrowernumber, module_bit, code)
+ SELECT borrowernumber, 3, 'manage_additional_fields' FROM borrowers WHERE borrowernumber IN (SELECT DISTINCT borrowernumber FROM user_permissions WHERE code = 'order_manage' OR code = 'edit_subscription');
+ });
+ $dbh->do( q{
+ INSERT INTO user_permissions (borrowernumber, module_bit, code)
+ SELECT borrowernumber, 3, 'manage_additional_fields' FROM borrowers WHERE borrowernumber IN (SELECT borrowernumber FROM borrowers WHERE MOD(flags DIV POWER(2,11),2)=1 OR MOD(flags DIV POWER(2,15),2) =1);
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 15774 - Add permission for managing additional fields)\n";
+}
+
+$DBversion = '18.12.00.023';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES ('ILLOpacbackends',NULL,NULL,'ILL backends to enabled for OPAC initiated requests','multiple');
+ |);
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20639 - Add ILLOpacbackends syspref)\n";
+}
+
+$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' ) )
+ {
+ $dbh->do(
+"ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;"
+ );
+ $dbh->do(
+"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.suggestedby = borrowers.borrowernumber) SET suggestedby = null WHERE borrowernumber IS null"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ # Add constraint for managedby
+ unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_managedby' ) )
+ {
+ $dbh->do(
+"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.managedby = borrowers.borrowernumber) SET managedby = null WHERE borrowernumber IS NULL"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ # Add constraint for acceptedby
+ unless (
+ foreign_key_exists( 'suggestions', 'suggestions_ibfk_acceptedby' ) )
+ {
+ $dbh->do(
+"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.acceptedby = borrowers.borrowernumber) SET acceptedby = null WHERE borrowernumber IS NULL"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ # Add constraint for rejectedby
+ unless (
+ foreign_key_exists( 'suggestions', 'suggestions_ibfk_rejectedby' ) )
+ {
+ $dbh->do(
+"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.rejectedby = borrowers.borrowernumber) SET rejectedby = null WHERE borrowernumber IS null"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ # Add constraint for biblionumber
+ unless (
+ foreign_key_exists( 'suggestions', 'suggestions_ibfk_biblionumber' ) )
+ {
+ $dbh->do(
+"UPDATE suggestions s LEFT JOIN biblio b ON (s.biblionumber = b.biblionumber) SET s.biblionumber = null WHERE b.biblionumber IS null"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ # Add constraint for branchcode
+ unless (
+ foreign_key_exists( 'suggestions', 'suggestions_ibfk_branchcode' ) )
+ {
+ $dbh->do(
+"UPDATE suggestions s LEFT JOIN branches b ON (s.branchcode = b.branchcode) SET s.branchcode = null WHERE b.branchcode IS null"
+ );
+ $dbh->do(
+"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE"
+ );
+ }
+
+ SetVersion($DBversion);
+ print
+"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n";
+}
+
+$DBversion = '18.12.00.025';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do('SET FOREIGN_KEY_CHECKS=0');
+
+ # Change columns accordingly
+ $dbh->do(q{
+ ALTER TABLE tags_index
+ MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL;
+ });
+
+ $dbh->do(q{
+ ALTER TABLE tags_approval
+ MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL;
+ });
+
+ $dbh->do(q{
+ ALTER TABLE tags_all
+ MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL;
+ });
+
+ $dbh->do('SET FOREIGN_KEY_CHECKS=1');
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21846 - Using emoji as tags has broken weights)\n";
+ my $maintenance_script = C4::Context->config("intranetdir") . "/misc/maintenance/fix_tags_weight.pl";
+ print "WARNING: (Bug 21846) You need to manually run $maintenance_script to fix possible issues with tags.\n";
+}
+
+$DBversion = '18.12.00.026';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "INSERT IGNORE INTO systempreferences (variable, value, explanation, type) VALUES ('IllLog', 0, 'If ON, log information about ILL requests', 'YesNo')" );
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20750 - Allow timestamped auditing of ILL request events)\n";
+}
+
+$DBversion = '18.12.00.027';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES
+ ('ILLModuleUnmediated','0','','If enabled, try to immediately progress newly placed ILL requests.','YesNo');
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18837: Add ILLModuleUnmediated Syspref)\n";
+}
+
+$DBversion = '18.12.00.028';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'Account Fee' );
+ });
+
+ $dbh->do(q{
+ INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'Hold Expired' );
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21756 - Add 'Account Fee' and 'Hold Expired' to the account_offset_types table if missing)\n";
+}
+
+$DBversion = '18.12.00.029';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPriceRounding',NULL,'Local preference for rounding orders before calculations to ensure correct calculations','|nearest_cent','Choice')" );
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18736 - Add syspref to control order rounding)\n";
+}
+
+$DBversion = '18.12.00.030';
+if( CheckVersion( $DBversion ) ) {
+ if( column_exists( 'accountlines', 'accountno' ) ) {
+ $dbh->do( "ALTER TABLE accountlines DROP COLUMN accountno" );
+ }
+ if( column_exists( 'statistics', 'proccode' ) ) {
+ $dbh->do( "ALTER TABLE statistics DROP COLUMN proccode" );
+ }
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21683 - Remove accountlines.accountno and statistics.proccode fields)\n";
+}
+
+$DBversion = '18.12.00.031';
+if( CheckVersion( $DBversion ) ) {
+
+ # Add constraint for manager_id
+ unless( foreign_key_exists( 'accountlines', 'accountlines_ibfk_borrowers_2' ) ) {
+ $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL");
+ $dbh->do("UPDATE accountlines a LEFT JOIN borrowers b ON ( a.manager_id = b.borrowernumber) SET a.manager_id = NULL WHERE b.borrowernumber IS NULL");
+ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE");
+ }
+
+ # Rename accountlines_ibfk_2 to accountlines_ibfk_items
+ if ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_2' ) && !foreign_key_exists( 'accountlines', 'accountlines_ibfk_items' ) ) {
+ $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2");
+ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE");
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints for accountlines.manager_id)\n";
+}
+
+$DBversion = '18.12.00.032';
+if( CheckVersion( $DBversion ) ) {
+ if( !column_exists( 'search_field', 'facet_order' ) ) {
+ $dbh->do("ALTER TABLE search_field ADD COLUMN facet_order TINYINT(4) DEFAULT NULL AFTER weight");
+ }
+ $dbh->do("UPDATE search_field SET facet_order=1 WHERE name='author'");
+ $dbh->do("UPDATE search_field SET facet_order=2 WHERE name='itype'");
+ $dbh->do("UPDATE search_field SET facet_order=3 WHERE name='location'");
+ $dbh->do("UPDATE search_field SET facet_order=4 WHERE name='su-geo'");
+ $dbh->do("UPDATE search_field SET facet_order=5 WHERE name='title-series'");
+ $dbh->do("UPDATE search_field SET facet_order=6 WHERE name='subject'");
+ $dbh->do("UPDATE search_field SET facet_order=7 WHERE name='ccode'");
+ $dbh->do("UPDATE search_field SET facet_order=8 WHERE name='holdingbranch'");
+ $dbh->do("UPDATE search_field SET facet_order=9 WHERE name='homebranch'");
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18235 - Elastic search - make facets configurable)\n";
+}
+
+$DBversion = '18.12.00.033';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE search_field SET facet_order=10 WHERE name='ln'" );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18213 - Add language facets to Elasticsearch)\n";
+}
+
+$DBversion = '18.12.00.034';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( column_exists( 'accountlines', 'lastincrement' ) ) {
+ $dbh->do("ALTER TABLE `accountlines` DROP COLUMN `lastincrement`");
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22516 - Drop deprecated accountlines.lastincrement field)\n";
}
+$DBversion = '18.12.00.035';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type)
+ VALUES ('MaxItemsToDisplayForBatchMod','1000',NULL,'Display up to a given number of items in a single item modification batch.','Integer')"
+ );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 19722 - Add a MaxItemsToDisplayForBatchMod preference)\n";
+}
+
+$DBversion = '18.12.00.036';
+if ( CheckVersion($DBversion) ) {
+
+ my $rows = $dbh->do(
+ qq{
+ UPDATE `accountlines`
+ SET
+ `accounttype` = 'FU'
+ WHERE
+ `accounttype` = 'O'
+ }
+ );
+
+ SetVersion($DBversion);
+ printf "Upgrade to $DBversion done (Bug 22518 - Fix accounttype 'O' to 'FU' - %d updated)\n", $rows;
+}
+
+$DBversion = '18.12.00.037';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do( "UPDATE issues SET renewals = 0 WHERE renewals IS NULL" );
+ $dbh->do( "UPDATE old_issues SET renewals = 0 WHERE renewals IS NULL" );
+
+ $dbh->do( "ALTER TABLE issues MODIFY COLUMN renewals tinyint(4) NOT NULL default 0");
+ $dbh->do( "ALTER TABLE old_issues MODIFY COLUMN renewals tinyint(4) NOT NULL default 0");
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22607 - Set default value of issues.renewals to 0)\n";
+}
+
+$DBversion = '18.12.00.038';
+if ( CheckVersion($DBversion) ) {
+
+ if ( !column_exists( 'accountlines', 'status' ) ) {
+ $dbh->do(
+ qq{
+ ALTER TABLE `accountlines`
+ ADD
+ `status` varchar(16) DEFAULT NULL
+ AFTER
+ `accounttype`
+ }
+ );
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22512 - Add status to accountlines)\n";
+}
+
+$DBversion = '18.12.00.039';
+if ( CheckVersion($DBversion) ) {
+
+ if ( !column_exists( 'accountlines', 'interface' ) ) {
+ $dbh->do(
+ qq{
+ ALTER TABLE `accountlines`
+ ADD
+ `interface` varchar(16)
+ AFTER
+ `manager_id`;
+ }
+ );
+ }
+
+ $dbh->do(qq{
+ UPDATE
+ `accountlines`
+ SET
+ interface = 'opac'
+ WHERE
+ borrowernumber = manager_id;
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ `accountlines`
+ SET
+ interface = 'cron'
+ WHERE
+ manager_id IS NULL
+ AND
+ branchcode IS NULL;
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ `accountlines`
+ SET
+ interface = 'intranet'
+ WHERE
+ interface IS NULL;
+ });
+
+ $dbh->do(qq{
+ ALTER TABLE `accountlines`
+ MODIFY COLUMN `interface` varchar(16) NOT NULL;
+ });
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22600 - Add interface to accountlines)\n";
+}
+
+$DBversion = '18.12.00.040';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do("UPDATE accountlines SET description = REPLACE(description, 'Reserve Charge - ', '') WHERE description LIKE 'Reserve Charge - %'");
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 12166 - Remove 'Reserve Charge' text from accountlines description)\n";
+}
+
+$DBversion = '18.12.00.041';
+if( CheckVersion( $DBversion ) ) {
+ my $table_sth = $dbh->prepare('SHOW CREATE TABLE `search_marc_map`');
+ $table_sth->execute();
+ my @table = $table_sth->fetchrow_array();
+ unless ( $table[1] =~ /`marc_field`.*COLLATE utf8mb4_bin/ ) { #catches utf8mb4 collated tables
+ $dbh->do("ALTER TABLE `search_marc_map` MODIFY `marc_field` VARCHAR(255) NOT NULL COLLATE utf8mb4_bin COMMENT 'the MARC specifier for this field'");
+ }
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 19670 - Change collation of marc_field to allow mixed case search field mappings)\n";
+}
+
+$DBversion = '18.12.00.042';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE systempreferences SET value = 'default' WHERE variable = 'XSLTDetailsDisplay' AND value = ''" );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 29891 - Remove non-XSLT detail view in the staff client)\n";
+}
+
+$DBversion = '18.12.00.043';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do("UPDATE accountlines SET description = REPLACE(description, 'Lost Item ', '') WHERE description LIKE 'Lost Item %'");
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 21953 - Remove 'Lost Item' text from accountlines description)\n";
+}
+
+$DBversion = '18.12.00.044';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( !column_exists( 'categories', 'reset_password' ) ) {
+ $dbh->do(q{
+ ALTER TABLE categories
+ ADD COLUMN reset_password TINYINT(1) NULL DEFAULT NULL
+ AFTER checkprevcheckout
+ });
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21890 - Patron password reset by category)\n";
+}
+
+$DBversion = '18.12.00.045';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( !column_exists( 'categories', 'change_password' ) ) {
+ $dbh->do(q{
+ ALTER TABLE categories
+ ADD COLUMN change_password TINYINT(1) NULL DEFAULT NULL
+ AFTER reset_password
+ });
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 10796 - Patron password change by category)\n";
+}
+
+$DBversion = '18.12.00.046';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE systempreferences SET value = 'default' WHERE variable = 'XSLTResultsDisplay' AND value = ''" );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22695 - Remove non-XSLT search results view from the staff client)\n";
+}
+
+$DBversion = '18.12.00.047';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('LibrisKey', '', 'This key must be obtained at http://api.libris.kb.se/. It is unique for the IP of the server.', NULL, 'Free');
+ |);
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('LibrisURL', 'http://api.libris.kb.se/bibspell/', 'This is the base URL for the Libris spellchecking API.',NULL,'Free');
+ |);
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 14557: Add Libris spellchecking system preferences)\n";
+}
+
+$DBversion = '18.12.00.048';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES ('NoRenewalBeforePrecision', 'exact_time', 'Calculate "No renewal before" based on date or exact time. Only relevant for loans calculated in days, hourly loans are not affected.', 'date|exact_time', 'Choice');
+ });
+ $dbh->do("UPDATE systempreferences SET value='exact_time' WHERE variable='NoRenewalBeforePrecision' AND value IS NULL;" );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22044 - Set a default value for NoRenewalBeforePrecision)\n";
+}
+
+$DBversion = '18.12.00.049';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ ALTER TABLE borrowers
+ ADD COLUMN flgAnonymized tinyint DEFAULT 0
+ AFTER overdrive_auth_token
+ }) if !column_exists('borrowers', 'flgAnonymized');
+
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers
+ ADD COLUMN flgAnonymized tinyint DEFAULT 0
+ AFTER overdrive_auth_token
+ }) if !column_exists('deletedborrowers', 'flgAnonymized');
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21336 - Add field flgAnonymized)\n";
+}
+
+$DBversion = '18.12.00.050';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q|
+INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` )
+VALUES
+('UnsubscribeReflectionDelay','',NULL,'Delay for locking unsubscribers', 'Integer'),
+('PatronAnonymizeDelay','',NULL,'Delay for anonymizing patrons', 'Integer'),
+('PatronRemovalDelay','',NULL,'Delay for removing anonymized patrons', 'Integer')
+ |);
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21336 - Add preferences)\n";
+}
+
+$DBversion = '18.12.00.051';
+if( CheckVersion( $DBversion ) ) {
+ 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";
+}
+
+$DBversion = '18.12.00.052';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
+ ('OpacMoreSearches', '', NULL, 'Add additional elements to the OPAC more searches bar', 'Textarea')
+ } );
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22311 - Add a SysPref to allow adding content to the #moresearches div in the opac)\n";
+}
+
+$DBversion = '18.12.00.053';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES
+ ('AutoReturnCheckedOutItems', '0', '', 'If disabled, librarian must confirm return of checked out item when checking out to another.', 'YesNo');
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 17171 - Add a syspref to allow currently issued items to be issued to a new patron without staff confirmation)\n";
+}
+
+$DBversion = '18.12.00.054';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE permissions (module_bit, code, description)
+ VALUES
+ (9,'advanced_editor','Use the advanced cataloging editor')
+ });
+ if( C4::Context->preference('EnableAdvancedCatalogingEditor') ){
+ $dbh->do(q{
+ INSERT INTO user_permissions (borrowernumber, module_bit, code)
+ SELECT borrowernumber, 9, 'advanced_editor' FROM borrowers WHERE borrowernumber IN (SELECT DISTINCT borrowernumber FROM user_permissions WHERE code = 'edit_catalogue');
+ });
+ }
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20128: Add permission for Advanced Cataloging Editor)\n";
+}
+
+$DBversion = '18.12.00.055';
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(qq{
+ UPDATE
+ `account_offset_types`
+ SET
+ type = 'OVERDUE'
+ WHERE
+ type = 'Fine';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ `account_offset_types`
+ SET
+ type = 'OVERDUE_INCREASE'
+ WHERE
+ type = 'fine_increase';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ `account_offset_types`
+ SET
+ type = 'OVERDUE_DECREASE'
+ WHERE
+ type = 'fine_decrease';
+ });
+
+ if ( column_exists( 'accountlines', 'accounttype' ) ) {
+ $dbh->do(
+ qq{
+ ALTER TABLE `accountlines`
+ CHANGE COLUMN `accounttype`
+ `accounttype` varchar(16) DEFAULT NULL;
+ }
+ );
+ }
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'OVERDUE',
+ status = 'UNRETURNED'
+ WHERE
+ accounttype = 'FU';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'OVERDUE',
+ status = 'FORGIVEN'
+ WHERE
+ accounttype = 'FFOR';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'OVERDUE',
+ status = 'RETURNED'
+ WHERE
+ accounttype = 'F';
+ });
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22521 - Update accountlines.accounttype to varchar(16), and map new statuses)\n";
+}
+
+$DBversion = '18.12.00.056';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE systempreferences SET explanation = 'This syspref allows to define custom rules for hiding specific items at the OPAC. See http://wiki.koha-community.org/wiki/OpacHiddenItems for more information.' WHERE variable = 'OpacHiddenItems'");
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 8701 - Update OpacHiddenItems system preference description)\n";
+}
+
+$DBversion = '18.12.00.057';
+if( CheckVersion( $DBversion ) ) {
+ if( column_exists('statistics', 'associatedborrower') ) {
+ $dbh->do(q{ ALTER TABLE statistics DROP COLUMN associatedborrower });
+ }
+ if( column_exists('statistics', 'usercode') ) {
+ $dbh->do(q{ ALTER TABLE statistics DROP COLUMN usercode });
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 13795 - Delete unused fields from statistics table)\n";
+}
+
+$DBversion = '18.12.00.058';
+if( CheckVersion( $DBversion ) ) {
+ my $opaclang = C4::Context->preference("opaclanguages");
+ my @langs;
+ push @langs, split ( '\,', $opaclang );
+ # Get any existing value from the OpacNavRight system preference
+ my ($OpacNavRight) = $dbh->selectrow_array( q|
+ SELECT value FROM systempreferences WHERE variable='OpacNavRight';
+ |);
+ if( $OpacNavRight ){
+ # If there is a value in the OpacNavRight preference, insert it into opac_news
+ $dbh->do("INSERT INTO opac_news (branchcode, lang, title, content ) VALUES (NULL, ?, '', ?)", undef, "OpacNavRight_$langs[0]", $OpacNavRight);
+ }
+ # Remove the OpacNavRight system preference
+ $dbh->do("DELETE FROM systempreferences WHERE variable='OpacNavRight'");
+ SetVersion ($DBversion);
+ print "Upgrade to $DBversion done (Bug 22318: Move contents of OpacNavRight preference to Koha news system)\n";
+}
+
+$DBversion = '18.12.00.059';
+if( CheckVersion( $DBversion ) ) {
+ if( column_exists( 'import_records', 'z3950random' ) ) {
+ $dbh->do( "ALTER TABLE import_records DROP COLUMN z3950random" );
+ }
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22532 - Remove import_records z3950random column)\n";
+}
+
+$DBversion = '18.12.00.060';
+if ( CheckVersion($DBversion) ) {
+
+ my $rows = $dbh->do(
+ qq{
+ UPDATE `accountlines`
+ SET
+ `accounttype` = 'L',
+ `status` = 'REPLACED'
+ WHERE
+ `accounttype` = 'Rep'
+ }
+ );
+
+ SetVersion($DBversion);
+ printf "Upgrade to $DBversion done (Bug 22564 - Fix accounttype 'Rep' - %d updated)\n", $rows;
+}
+
+$DBversion = '18.12.00.061';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( column_exists( 'borrowers', 'flgAnonymized' ) ) {
+ $dbh->do(q{
+ UPDATE borrowers SET flgAnonymized = 0 WHERE flgAnonymized IS NULL
+ });
+ $dbh->do(q{
+ ALTER TABLE borrowers
+ CHANGE `flgAnonymized` `anonymized` TINYINT(1) NOT NULL DEFAULT 0
+ });
+ }
+
+ if ( column_exists( 'deletedborrowers', 'flgAnonymized' ) ) {
+ $dbh->do(q{
+ UPDATE deletedborrowers SET flgAnonymized = 0 WHERE flgAnonymized IS NULL
+ });
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers
+ CHANGE `flgAnonymized` `anonymized` TINYINT(1) NOT NULL DEFAULT 0
+ });
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21336 - (follow-up) Rename flgAnonymized column)\n";
+}
+
+$DBversion = '18.12.00.062';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='007_/0'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='007_/1' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='ff7-00')
+ )
+ |);
+
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='007_/1'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='007_/2' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='ff7-01')
+ )
+ |);
+
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='007_/2'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='007_/3' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='ff7-02')
+ )
+ |);
+
+ # N.B. ff7-01-02 really is 00-01!
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='007_/0-1'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='007_/1-2' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='ff7-01-02')
+ )
+ |);
+
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='008_/0-5'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='008_/1-5' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='date-entered-on-file')
+ )
+ |);
+
+ $dbh->do( q|
+ UPDATE search_marc_map SET marc_field='leader_/0-4'
+ WHERE marc_type IN ('marc21', 'normarc') AND marc_field='leader_/1-5' AND id IN
+ (SELECT search_marc_map_id FROM search_marc_to_field WHERE search_field_id IN
+ (SELECT id FROM search_field WHERE label='llength')
+ )
+ |);
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22339 - Fix search field mappings of MARC fixed fields)\n";
+}
+
+$DBversion = '18.12.00.063';
+if ( CheckVersion($DBversion) ) {
+
+ my $types_map = {
+ 'Writeoff' => 'W',
+ 'Payment' => 'Pay',
+ 'Lost Item' => 'CR',
+ 'Manual Credit' => 'C',
+ 'Forgiven' => 'FOR'
+ };
+
+ my $sth = $dbh->prepare( "SELECT accountlines_id FROM accountlines WHERE accounttype = 'VOID'" );
+ my $sth2 = $dbh->prepare( "SELECT type FROM account_offsets WHERE credit_id = ? ORDER BY created_on LIMIT 1" );
+ my $sth3 = $dbh->prepare( "UPDATE accountlines SET accounttype = ?, status = 'VOID' WHERE accountlines_id = ?" );
+ $sth->execute();
+ while (my $row = $sth->fetchrow_hashref) {
+ $sth2->execute($row->{accountlines_id});
+ my $result = $sth2->fetchrow_hashref;
+ my $type = $types_map->{$result->{'type'}} // 'Pay';
+ $sth3->execute($type,$row->{accountlines_id});
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22511 - Update existing VOID accountlines)\n";
+}
+
+$DBversion = '18.12.00.064';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('UpdateItemLocationOnCheckin', 'PROC: _PERM_\n', 'NULL', 'This is a list of value pairs.\n Examples:\n PROC: FIC - causes an item in the Processing Center location to be updated into the Fiction location on check in.\n FIC: GEN - causes an item in the Fiction location to be updated into the General stacks location on check in.\n _BLANK_:FIC - causes an item that has no location to be updated into the Fiction location on check in.\nFIC: _BLANK_ - causes an item in location FIC to be updated to a blank location on check in.\n_ALL_:FIC - causes all items to be updated into the Fiction location on check in.\nPROC: _PERM_ - causes an item that is in the Processing Center to be updated to it''s permanent location.\nGeneral rule: if the location value on the left matches the item''s current location, it will be updated to match the location value on the right.\nNote: PROC and CART are special values, for these locations only can location and permanent_location differ, in all other cases an update will affect both. Items in the CART location will be returned to their permanent location on checkout.\nThe special term _BLANK_ may be used on either side of a value pair to update or remove the location from items with no location assigned. The special term _ALL_ is used on the left side of the colon (:) to affect all items.\nThe special term _PERM_ is used on the right side of the colon (:) to return items to their permanent location.', 'Free');
+ });
+ $dbh->do(q{
+ UPDATE systempreferences s1, (SELECT IF(value,'PROC: CART\n','') AS p2c FROM systempreferences WHERE variable='InProcessingToShelvingCart') s2 SET s1.value= CONCAT(s2.p2c, REPLACE(s1.value,'PROC: _PERM_\n','') ) WHERE s1.variable='UpdateItemLocationOnCheckin' AND s1.value NOT LIKE '%PROC: CART%';
+ });
+ $dbh->do(q{
+ DELETE FROM systempreferences WHERE variable='InProcessingToShelvingCart';
+ });
+ $dbh->do(q{
+ UPDATE systempreferences s1, (SELECT IF(value,'_ALL_: CART\n','') AS rtc FROM systempreferences WHERE variable='ReturnToShelvingCart') s2 SET s1.value= CONCAT(s2.rtc,s1.value) WHERE s1.variable='UpdateItemLocationOnCheckin' AND s1.value NOT LIKE '%_ALL_: CART%';
+ });
+ $dbh->do(q{
+ DELETE FROM systempreferences WHERE variable='ReturnToShelvingCart';
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 14576: Add UpdateItemLocationOnCheckin syspref)\n";
+}
+
+$DBversion = '18.12.00.065';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` )
+ SELECT 'IndependentBranchesTransfers', value, NULL, 'Allow non-superlibrarians to transfer items between libraries','YesNo'
+ FROM systempreferences WHERE variable = 'IndependentBranches'
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 10300 - Allow transferring of items to be have separate IndependentBranches syspref)\n";
+}
+
+$DBversion = '18.12.00.066';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO `systempreferences` (`variable`, `value`, `explanation`, `options`, `type`) VALUES
+ ('OpenURLResolverURL', '', 'URL of OpenURL Resolver', NULL, 'Free'),
+ ('OpenURLText', '', 'Text of OpenURL links (or image title if OpenURLImageLocation is defined)', NULL, 'Free'),
+ ('OpenURLImageLocation', '', 'Location of image for OpenURL links', NULL, 'Free'),
+ ('OPACShowOpenURL', '', 'Enable display of OpenURL links in OPAC search results and detail page', NULL, 'YesNo'),
+ ('OPACOpenURLItemTypes', '', 'Show the OpenURL link only for these item types', NULL, 'Free');
+ });
+
+ SetVersion($DBversion);
+ print
+"Upgrade to $DBversion done (Bug 8995 - Add new preferences for OpenURLResolvers)\n";
+}
+
+$DBversion = '18.12.00.067';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('SendAllEmailsTo','',NULL,'All emails will be redirected to this email if it is not empty','free');
+ });
+ SetVersion($DBversion);
+ print
+"Upgrade to $DBversion done (Bug 8000 - Add new preferences for SendAllEmailsTo)\n";
+}
+
+$DBversion = '18.12.00.068';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES
+ ('AllowRenewalOnHoldOverride','0','','If on, allow items on hold to be renewed with a specified due date','YesNo');
+ });
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 7088: Cannot renew items on hold even with override)\n";
+}
+
+$DBversion = '18.12.00.069';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ INSERT INTO plugin_data
+ (plugin_class, plugin_key, plugin_value)
+ SELECT
+ plugin_class,
+ '__ENABLED__',
+ 1
+ FROM plugin_data
+ WHERE plugin_key='__INSTALLED_VERSION__'
+ });
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22053 - enable all plugins)\n";
+}
+
+$DBversion = '18.12.00.070';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences
+ ( `variable`, `value`, `options`, `explanation`, `type` )
+ VALUES
+ ('SelfCheckAllowByIPRanges','',NULL,'(Leave blank if not used. Use ranges or simple ip addresses separated by spaces, like <code>192.168.1.1 192.168.0.0/24</code>.)','Short');
+ });
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 14407 - Limit web-based self-checkout to specific IP addresses)\n";
+}
+
+$DBversion = '18.12.00.071';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`, `lang`) VALUES
+('circulation', 'ACCOUNT_CREDIT', '', 'Account payment', 0, 'Account payment', '<table>
+[% IF ( LibraryName ) %]
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h3>[% LibraryName | html %]</h3>
+ </th>
+ </tr>
+[% END %]
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h2><u>Fee receipt</u></h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h2>[% Branches.GetName( patron.branchcode ) | html %]</h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="4">
+ Received with thanks from [% patron.firstname | html %] [% patron.surname | html %] <br />
+ Card number: [% patron.cardnumber | html %]<br />
+ </th>
+ </tr>
+ <tr>
+ <th>Date</th>
+ <th>Description of charges</th>
+ <th>Note</th>
+ <th>Amount</th>
+ </tr>
+
+ [% FOREACH account IN accounts %]
+ <tr class="highlight">
+ <td>[% account.date | $KohaDates %]</td>
+ <td>
+ [% PROCESS account_type_description account=account %]
+ [%- IF account.description %], [% account.description | html %][% END %]
+ </td>
+ <td>[% account.note | html %]</td>
+ [% IF ( account.amountcredit ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% account.amount | $Price %]</td>
+ </tr>
+
+ [% END %]
+<tfoot>
+ <tr>
+ <td colspan="3">Total outstanding dues as on date: </td>
+ [% IF ( totalcredit ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% total | $Price %]</td>
+ </tr>
+</tfoot>
+</table>', 'print', 'default');
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22809 - Move 'ACCOUNT_CREDIT' from template to a slip)\n";
+}
+
+$DBversion = '18.12.00.072';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`, `lang`) VALUES
+('circulation', 'ACCOUNT_DEBIT', '', 'Account fee', 0, 'Account fee', '<table>
+ [% IF ( LibraryName ) %]
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h3>[% LibraryName | html %]</h3>
+ </th>
+ </tr>
+ [% END %]
+
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h2><u>INVOICE</u></h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h2>[% Branches.GetName( patron.branchcode ) | html %]</h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="5" >
+ Bill to: [% patron.firstname | html %] [% patron.surname | html %] <br />
+ Card number: [% patron.cardnumber | html %]<br />
+ </th>
+ </tr>
+ <tr>
+ <th>Date</th>
+ <th>Description of charges</th>
+ <th>Note</th>
+ <th style="text-align:right;">Amount</th>
+ <th style="text-align:right;">Amount outstanding</th>
+ </tr>
+
+ [% FOREACH account IN accounts %]
+ <tr class="highlight">
+ <td>[% account.date | $KohaDates%]</td>
+ <td>
+ [% PROCESS account_type_description account=account %]
+ [%- IF account.description %], [% account.description | html %][% END %]
+ </td>
+ <td>[% account.note | html %]</td>
+ [% IF ( account.amountcredit ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% account.amount | $Price %]</td>
+ [% IF ( account.amountoutstandingcredit ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% account.amountoutstanding | $Price %]</td>
+ </tr>
+ [% END %]
+
+ <tfoot>
+ <tr>
+ <td colspan="4">Total outstanding dues as on date: </td>
+ [% IF ( totalcredit ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% total | $Price %]</td>
+ </tr>
+ </tfoot>
+</table>', 'print', 'default');
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22809 - Move 'INVOICE' from template to a slip)\n";
+}
+
+$DBversion = '18.12.00.073';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES
+ ('EmailPurchaseSuggestions','0','0|EmailAddressForSuggestions|BranchEmailAddress|KohaAdminEmailAddress','Choose email address that will be sent new purchase suggestions','Choice'),
+ ('EmailAddressForSuggestions','','','If you choose EmailAddressForSuggestions you should enter a valid email address','free')
+ });
+
+ $dbh->do( q{
+ INSERT IGNORE INTO `letter` (module, code, name, title, content, is_html, message_transport_type) VALUES
+ ('suggestions','NEW_SUGGESTION','New suggestion','New suggestion','<h3>Suggestion pending approval</h3>
+ <p><h4>Suggested by</h4>
+ <ul>
+ <li><<borrowers.firstname>> <<borrowers.surname>></li>
+ <li><<borrowers.cardnumber>></li>
+ <li><<borrowers.phone>></li>
+ <li><<borrowers.email>></li>
+ </ul>
+ </p>
+ <p><h4>Title suggested</h4>
+ <ul>
+ <li><b>Library:</b> <<branches.branchname>></li>
+ <li><b>Title:</b> <<suggestions.title>></li>
+ <li><b>Author:</b> <<suggestions.author>></li>
+ <li><b>Copyright date:</b> <<suggestions.copyrightdate>></li>
+ <li><b>Standard number (ISBN, ISSN or other):</b> <<suggestions.isbn>></li>
+ <li><b>Publisher:</b> <<suggestions.publishercode>></li>
+ <li><b>Collection title:</b> <<suggestions.collectiontitle>></li>
+ <li><b>Publication place:</b> <<suggestions.place>></li>
+ <li><b>Quantity:</b> <<suggestions.quantity>></li>
+ <li><b>Item type:</b> <<suggestions.itemtype>></li>
+ <li><b>Reason for suggestion:</b> <<suggestions.patronreason>></li>
+ <li><b>Notes:</b> <<suggestions.note>></li>
+ </ul>
+ </p>',1, 'email')
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 5770 - Email librarian when purchase suggestion made)\n";
+}
+
+$DBversion = '18.12.00.074';
+if( CheckVersion( $DBversion ) ) {
+ unless ( TableExists( 'keyboard_shortcuts' ) ) {
+ $dbh->do(q|
+ CREATE TABLE keyboard_shortcuts (
+ shortcut_name varchar(80) NOT NULL,
+ shortcut_keys varchar(80) NOT NULL,
+ PRIMARY KEY (shortcut_name)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;|
+ );
+ }
+ $dbh->do(q|
+ INSERT IGNORE INTO keyboard_shortcuts (shortcut_name, shortcut_keys) VALUES
+ ("insert_copyright","Alt-C"),
+ ("insert_copyright_sound","Alt-P"),
+ ("insert_delimiter","Ctrl-D"),
+ ("subfield_help","Ctrl-H"),
+ ("link_authorities","Shift-Ctrl-L"),
+ ("delete_field","Ctrl-X"),
+ ("delete_subfield","Shift-Ctrl-X"),
+ ("new_line","Enter"),
+ ("line_break","Shift-Enter"),
+ ("next_position","Tab"),
+ ("prev_position","Shift-Tab")
+ ;|
+ );
+ $dbh->do(q|
+ INSERT IGNORE permissions (module_bit, code, description)
+ VALUES
+ (3,'manage_keyboard_shortcuts','Manage keyboard shortcuts for advanced cataloging editor')
+ ;|
+ );
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 21411 - Add keyboard_shortcuts table)\n";
+}
+
+$DBversion = '18.12.00.075';
+if( CheckVersion( $DBversion ) ) {
+ # you can use $dbh here like:
+ unless ( foreign_key_exists( 'tmp_holdsqueue', 'tmp_holdsqueue_ibfk_1' ) ) {
+ $dbh->do(q{
+ DELETE t FROM tmp_holdsqueue t
+ LEFT JOIN items i ON t.itemnumber=i.itemnumber
+ WHERE i.itemnumber IS NULL
+ });
+ $dbh->do(q{
+ ALTER TABLE tmp_holdsqueue
+ ADD CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`)
+ REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+ });
+ }
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22899 - Add items constraint to tmp_holdsqueue)\n";
+}
+
+$DBversion = '19.05.00.000';
+if( CheckVersion( $DBversion ) ) {
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (19.05.00 release)\n";
+}
+
+$DBversion = '19.06.00.000';
+if( CheckVersion( $DBversion ) ) {
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Wingardium Leviosa!)\n";
+}
+
+$DBversion = '19.06.00.001';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ UPDATE systempreferences
+ SET explanation = 'This is a list of value pairs.\n Examples:\n PROC: FIC - causes an item in the Processing Center location to be updated into the Fiction location on check in.\n FIC: GEN - causes an item in the Fiction location to be updated into the General stacks location on check in.\n _BLANK_:FIC - causes an item that has no location to be updated into the Fiction location on check in.\nFIC: _BLANK_ - causes an item in location FIC to be updated to a blank location on check in.\n_ALL_:FIC - causes all items to be updated into the Fiction location on check in.\nPROC: _PERM_ - causes an item that is in the Processing Center to be updated to it''s permanent location.\nGeneral rule: if the location value on the left matches the item''s current location, it will be updated to match the location value on the right.\nNote: PROC and CART are special values, for these locations only can location and permanent_location differ, in all other cases an update will affect both. Items in the CART location will be returned to their permanent location on checkout.\nThe special term _BLANK_ may be used on either side of a value pair to update or remove the location from items with no location assigned. The special term _ALL_ is used on the left side of the colon (:) to affect all items.\nThe special term _PERM_ is used on the right side of the colon (:) to return items to their permanent location.'
+ WHERE variable = 'UpdateItemLocationOnCheckin'
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22960: Fix typo in syspref description)\n";
+}
+
+$DBversion = '19.06.00.002';
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(q{ALTER TABLE subscriptionhistory CHANGE opacnote opacnote LONGTEXT NULL});
+ $dbh->do(q{ALTER TABLE subscriptionhistory CHANGE librariannote librariannote LONGTEXT NULL});
+
+ $dbh->do(q{UPDATE subscriptionhistory SET opacnote = NULL WHERE opacnote = ''});
+ $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";
+}
+
+$DBversion = '19.06.00.003';
+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";
+}
+
+$DBversion = '19.06.00.004';
+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";
+}
+
+$DBversion = '19.06.00.005';
+if( CheckVersion( $DBversion ) ) {
+ unless ( column_exists( 'reserves', 'item_level_hold' ) ) {
+ $dbh->do( "ALTER TABLE reserves ADD COLUMN item_level_hold BOOLEAN NOT NULL DEFAULT 0 AFTER itemtype" );
+ }
+ unless ( column_exists( 'old_reserves', 'item_level_hold' ) ) {
+ $dbh->do( "ALTER TABLE old_reserves ADD COLUMN item_level_hold BOOLEAN NOT NULL DEFAULT 0 AFTER itemtype" );
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 9834: Add the reserves.item_level_hold column)\n";
+}
+
+$DBversion = '19.06.00.006';
+if( CheckVersion( $DBversion ) ) {
+
+ unless ( TableExists('plugin_methods') ) {
+ $dbh->do(q{
+ CREATE TABLE plugin_methods (
+ plugin_class varchar(255) NOT NULL,
+ plugin_method varchar(255) NOT NULL,
+ PRIMARY KEY ( `plugin_class` (191), `plugin_method` (191) )
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+ });
+ }
+
+ require Koha::Plugins;
+ Koha::Plugins->new({ enable_plugins => 1 })->InstallPlugins;
+
+ SetVersion( $DBversion );
+ 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";
+}
+
+$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";
+}
+
+$DBversion = '19.06.00.009';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO keyboard_shortcuts (shortcut_name, shortcut_keys)
+ VALUES ("toggle_keyboard", "Shift-Ctrl-K")
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 17178: add shortcut to keyboard_shortcuts)\n";
+}
+
+$DBversion = '19.06.00.010';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( TableExists('default_circ_rules') ) {
+ if ( column_exists( 'default_circ_rules', 'holdallowed' ) ) {
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'holdallowed', holdallowed
+ FROM default_circ_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'hold_fulfillment_policy', hold_fulfillment_policy
+ FROM default_circ_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, NULL, 'returnbranch', returnbranch
+ FROM default_circ_rules
+ ");
+ $dbh->do("DROP TABLE default_circ_rules");
+ }
+ }
+
+ if ( TableExists('default_branch_circ_rules') ) {
+ if ( column_exists( 'default_branch_circ_rules', 'holdallowed' ) ) {
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, NULL, 'holdallowed', holdallowed
+ FROM default_branch_circ_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, NULL, 'hold_fulfillment_policy', hold_fulfillment_policy
+ FROM default_branch_circ_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, NULL, 'returnbranch', returnbranch
+ FROM default_branch_circ_rules
+ ");
+ $dbh->do("DROP TABLE default_branch_circ_rules");
+ }
+ }
+
+ if ( TableExists('branch_item_rules') ) {
+ if ( column_exists( 'branch_item_rules', 'holdallowed' ) ) {
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, itemtype, 'holdallowed', holdallowed
+ FROM branch_item_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, itemtype, 'hold_fulfillment_policy', hold_fulfillment_policy
+ FROM branch_item_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, branchcode, itemtype, 'returnbranch', returnbranch
+ FROM branch_item_rules
+ ");
+ $dbh->do("DROP TABLE branch_item_rules");
+ }
+ }
+
+ if ( TableExists('default_branch_item_rules') ) {
+ if ( column_exists( 'default_branch_item_rules', 'holdallowed' ) ) {
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, itemtype, 'holdallowed', holdallowed
+ FROM default_branch_item_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, itemtype, 'hold_fulfillment_policy', hold_fulfillment_policy
+ FROM default_branch_item_rules
+ ");
+ $dbh->do("
+ INSERT IGNORE INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, NULL, itemtype, 'returnbranch', returnbranch
+ FROM default_branch_item_rules
+ ");
+ $dbh->do("DROP TABLE default_branch_item_rules");
+ }
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18928: Move holdallowed, hold_fulfillment_policy, returnbranch to circulation_rules)\n";
+}
+
+$DBversion = '19.06.00.011';
+if( CheckVersion( $DBversion ) ) {
+
+ if ( TableExists('refund_lost_item_fee_rules') ) {
+ if ( column_exists( 'refund_lost_item_fee_rules', 'refund' ) ) {
+ $dbh->do("
+ INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
+ SELECT NULL, IF(branchcode='*', NULL, branchcode), NULL, 'refund', refund
+ FROM refund_lost_item_fee_rules
+ ");
+ $dbh->do("DROP TABLE refund_lost_item_fee_rules");
+ }
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18930: Move lost item refund rules to circulation_rules table)\n";
+}
+
+$DBversion = '19.06.00.012';
+if ( CheckVersion($DBversion) ) {
+
+ # Find and correct pathological cases of LR becoming a credit
+ my $sth = $dbh->prepare( "SELECT accountlines_id, issue_id, borrowernumber, itemnumber, amount, manager_id FROM accountlines WHERE accounttype = 'LR' AND amount < 0" );
+ $sth->execute();
+ while ( my $row = $sth->fetchrow_hashref ) {
+ $dbh->do(
+ "INSERT INTO accountlines (accounttype, issue_id, borrowernumber, itemnumber, amount, manager_id, interface) VALUES ( ?, ?, ?, ?, ?, ?, ? );",
+ {},
+ (
+ 'CR', $row->{issue_id},
+ $row->{borrowernumber}, $row->{itemnumber},
+ $row->{amount}, $row->{manager_id},
+ 'upgrade'
+ )
+ );
+ my $credit_id = $dbh->last_insert_id(undef, undef, 'accountlines', undef);
+ my $amount = $row->{amount} * -1;
+ $dbh->do("INSERT INTO account_offsets (credit_id, debit_id, type, amount) VALUES (?,?,?,?);",{},($credit_id, $row->{accountlines_id}, 'Lost Item', $amount));
+ $dbh->do("UPDATE accountlines SET amount = '$amount' WHERE accountlines_id = '$row->{accountlines_id}';");
+ }
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'LOST',
+ status = 'RETURNED'
+ WHERE
+ accounttype = 'LR';
+ });
+
+ # Find and correct pathalogical cases of L having been converted to W
+ $sth = $dbh->prepare( "SELECT accountlines_id, issue_id, borrowernumber, itemnumber, amount, manager_id FROM accountlines WHERE accounttype = 'W' AND itemnumber IS NOT NULL" );
+ $sth->execute();
+ while ( my $row = $sth->fetchrow_hashref ) {
+ my $amount = $row->{amount} * -1;
+ $dbh->do(
+ "INSERT INTO accountlines (accounttype, issue_id, borrowernumber, itemnumber, amount, manager_id, interface) VALUES ( ?, ?, ?, ?, ?, ?, ? );",
+ {},
+ (
+ 'LOST', $row->{issue_id}, $row->{borrowernumber},
+ $row->{itemnumber}, $amount, $row->{manager_id},
+ 'upgrade'
+ )
+ );
+ my $debit_id = $dbh->last_insert_id(undef, undef, 'accountlines', undef);
+ $dbh->do(
+ "INSERT INTO account_offsets (credit_id, debit_id, type, amount) VALUES (?,?,?,?);",
+ {},
+ (
+ $row->{accountlines_id}, $debit_id,
+ 'Lost Item', $amount
+ )
+ );
+ }
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'LOST'
+ WHERE
+ accounttype = 'L';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'LOST_RETURNED'
+ WHERE
+ accounttype = 'CR';
+ });
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22563: Fix accounttypes for 'L', 'LR' and 'CR')\n";
+}
+
+$DBversion = '19.06.00.013';
+if ( CheckVersion( $DBversion ) ) {
+ unless ( column_exists( 'borrower_modifications', 'changed_fields' ) ) {
+ $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";
+}
+
+$DBversion = '19.06.00.014';
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'RENT_DAILY_RENEW'
+ WHERE
+ accounttype = 'Rent'
+ AND
+ description LIKE 'Renewal of Daily Rental Item%';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'RENT_DAILY'
+ WHERE
+ accounttype = 'Rent'
+ AND
+ description LIKE 'Daily rental';
+ });
+
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'RENT_RENEW'
+ WHERE
+ accounttype = 'Rent'
+ AND
+ description LIKE 'Renewal of Rental Item%';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'RENT'
+ WHERE
+ accounttype = 'Rent';
+ });
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 11573: Fix accounttypes for 'Rent')\n";
+}
+
+$DBversion = '19.06.00.015';
+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";
+}
+
+$DBversion = '19.06.00.016';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q|
+ INSERT IGNORE INTO keyboard_shortcuts (shortcut_name, shortcut_keys) VALUES
+ ("insert_copyright","Alt-C"),
+ ("insert_copyright_sound","Alt-P"),
+ ("insert_delimiter","Ctrl-D"),
+ ("subfield_help","Ctrl-H"),
+ ("link_authorities","Shift-Ctrl-L"),
+ ("delete_field","Ctrl-X"),
+ ("delete_subfield","Shift-Ctrl-X"),
+ ("new_line","Enter"),
+ ("line_break","Shift-Enter"),
+ ("next_position","Tab"),
+ ("prev_position","Shift-Tab"),
+ ("toggle_keyboard", "Shift-Ctrl-K")
+ ;|);
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 23396: Fix missing keyboard_shortcuts table)\n";
+}
+
+$DBversion = '19.06.00.017';
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(qq{
+ INSERT INTO
+ authorised_values (category,authorised_value,lib)
+ VALUES
+ ('PAYMENT_TYPE','SIP00','Cash via SIP2'),
+ ('PAYMENT_TYPE','SIP01','VISA via SIP2'),
+ ('PAYMENT_TYPE','SIP02','Creditcard via SIP2')
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'Pay',
+ payment_type = 'SIP00'
+ WHERE
+ accounttype = 'Pay00';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'Pay',
+ payment_type = 'SIP01'
+ WHERE
+ accounttype = 'Pay01';
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'Pay',
+ payment_type = 'SIP02'
+ WHERE
+ accounttype = 'Pay02';
+ });
+
+ my $sth = $dbh->prepare( q{SELECT * FROM accountlines WHERE accounttype REGEXP '^Pay[[:digit:]]{2}$' } );
+ $sth->execute();
+ my $seen = {};
+ while (my $row = $sth->fetchrow_hashref) {
+ my $type = $row->{accounttype};
+ my $sipcode = $type;
+ $sipcode =~ s/Pay/SIP/g;
+ unless ($seen->{$sipcode}) {
+ $dbh->do(qq{
+ INSERT INTO
+ authorised_values (category,authorised_value,lib)
+ VALUES
+ ('PAYMENT_TYPE',"$sipcode",'Unrecognised SIP2 payment type')
+ });
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'Pay',
+ payment_type = "$sipcode"
+ WHERE
+ accounttype = "$type";
+ });
+
+ $seen->{$sipcode} = 1;
+ }
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 22610: Fix accounttypes for SIP2 payments)\n";
+}
+
+$DBversion = '19.06.00.018';
+if( CheckVersion( $DBversion ) ) {
+ if( !column_exists( 'biblio', 'subtitle' ) ) {
+ $dbh->do( "ALTER TABLE biblio ADD COLUMN medium LONGTEXT AFTER title" );
+ $dbh->do( "ALTER TABLE biblio ADD COLUMN subtitle LONGTEXT AFTER medium" );
+ $dbh->do( "ALTER TABLE biblio ADD COLUMN part_number LONGTEXT AFTER subtitle" );
+ $dbh->do( "ALTER TABLE biblio ADD COLUMN part_name LONGTEXT AFTER part_number" );
+
+ $dbh->do( "ALTER TABLE deletedbiblio ADD COLUMN medium LONGTEXT AFTER title" );
+ $dbh->do( "ALTER TABLE deletedbiblio ADD COLUMN subtitle LONGTEXT AFTER medium" );
+ $dbh->do( "ALTER TABLE deletedbiblio ADD COLUMN part_number LONGTEXT AFTER subtitle" );
+ $dbh->do( "ALTER TABLE deletedbiblio ADD COLUMN part_name LONGTEXT AFTER part_number" );
+ }
+
+ $dbh->do( "UPDATE marc_subfield_structure SET kohafield='biblio.subtitle' WHERE kohafield='bibliosubtitle.subtitle'" );
+
+ my $marcflavour = C4::Context->preference('marcflavour');
+
+ if ( $marcflavour eq 'UNIMARC' ) {
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.medium'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='200' AND tagsubfield='b'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.subtitle'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='200' AND tagsubfield='e'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.part_number'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='200' AND tagsubfield='h'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.part_name'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='200' AND tagsubfield='i'
+ });
+ } else {
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.medium'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='245' AND tagsubfield='h'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.subtitle'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='245' AND tagsubfield='b'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.part_number'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='245' AND tagsubfield='n'
+ });
+ $dbh->do(qq{
+ UPDATE marc_subfield_structure SET kohafield='biblio.part_name'
+ WHERE (kohafield IS NULL OR kohafield='') AND frameworkcode='' AND tagfield='245' AND tagsubfield='p'
+ });
+ }
+
+ $dbh->do("UPDATE marc_subfield_structure JOIN fieldmapping ON tagfield = fieldcode AND subfieldcode=tagsubfield SET kohafield='biblio.subtitle' WHERE fieldmapping.frameworkcode=''");
+ $sth = $dbh->prepare("SELECT * FROM fieldmapping WHERE frameworkcode != '' OR field != 'subtitle'");
+ $sth->execute;
+ my @fails_11529;
+ if ( $sth->rows ) {
+ while ( my $value = $sth->fetchrow_hashref() ) {
+ my $framework =
+ $value->{frameworkcode} eq ""
+ ? "Default"
+ : $value->{frameworkcode};
+ push @fails_11529,
+ {
+ field => $value->{field},
+ fieldcode => $value->{fieldcode},
+ subfieldcode => $value->{subfieldcode},
+ framework => $framework
+ };
+ }
+ }
+
+ $dbh->do( "DROP TABLE IF EXISTS fieldmapping" );
+
+ $dbh->do( "DELETE FROM user_permissions WHERE code='manage_keywords2koha_mappings'" );
+
+ $dbh->do( "DELETE FROM permissions WHERE code='manage_keywords2koha_mappings'" );
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 11529: Add medium, subtitle and part information to biblio table)\n";
+ if ( @fails_11529 ) {
+ print "WARNING: Not all Keyword to MARC mappings could be preserved\n";
+ for my $fail_11529 ( @fails_11529 ) {
+ print " keyword: "
+ . $fail_11529->{field}
+ . " to field: "
+ . $fail_11529->{fieldcode} . "\$"
+ . $fail_11529->{subfieldcode} . " for "
+ . $fail_11529->{framework}
+ . " framework\n";
+ }
+ print "You will need to remap using Koha to MARC mappings in administration\n";
+ }
+ print "NOTE: misc/batchRebuildBiblioTables.pl should be run to populate the fields introduced in bug 11529. It may take some time for larger databases.\n\n"
+}
+
+$DBversion = '19.06.00.019';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type)
+ VALUES
+ (
+ 'FinePaymentAutoPopup',
+ '0',
+ NULL,
+ 'If enabled, automatically display a print dialog for a payment receipt when making a payment.',
+ 'YesNo'
+ )
+ });
+
+ SetVersion($DBversion);
+ print
+"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';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
+ ('PreserveSerialNotes','1','','When a new "Expected" issue is generated, should it be prefilled with last created issue notes?','YesNo');
+ |);
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 23416: Add PreserveSerialNotes syspref)\n";
+}
+
+$DBversion = '19.06.00.021';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q|
+ ALTER TABLE marc_subfield_structure CHANGE COLUMN hidden hidden TINYINT(1) DEFAULT 8 NOT NULL;
+ |);
+ # 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";
+}
+
+$DBversion = '19.06.00.022';
+if ( CheckVersion($DBversion) ) {
+
+ unless ( TableExists('borrower_relationships') ) {
+ $dbh->do(q{
+ CREATE TABLE `borrower_relationships` (
+ id INT(11) NOT NULL AUTO_INCREMENT,
+ guarantor_id INT(11) NOT NULL,
+ guarantee_id INT(11) NOT NULL,
+ relationship VARCHAR(100) NOT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY `guarantor_guarantee_idx` ( `guarantor_id`, `guarantee_id` ),
+ CONSTRAINT r_guarantor FOREIGN KEY ( guarantor_id ) REFERENCES borrowers ( borrowernumber ) ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT r_guarantee FOREIGN KEY ( guarantee_id ) REFERENCES borrowers ( borrowernumber ) ON UPDATE CASCADE ON DELETE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+ });
+
+ $dbh->do(q{
+ UPDATE borrowers
+ LEFT JOIN borrowers guarantor ON ( borrowers.guarantorid = guarantor.borrowernumber )
+ SET borrowers.guarantorid = NULL WHERE guarantor.borrowernumber IS NULL;
+ });
+
+ # Bad data handling: guarantorid IS NOT NULL AND relationship IS NULL
+ $dbh->do(q{
+ UPDATE borrowers
+ SET relationship = '_bad_data'
+ WHERE guarantorid IS NOT NULL AND
+ relationship IS NULL
+ });
+
+ $dbh->do(q{
+ INSERT INTO borrower_relationships ( guarantor_id, guarantee_id, relationship )
+ SELECT guarantorid, borrowernumber, relationship FROM borrowers WHERE guarantorid IS NOT NULL;
+ });
+
+ # Clean migrated guarantor data
+ $dbh->do(q{
+ UPDATE borrowers
+ SET contactname=NULL,
+ contactfirstname=NULL,
+ relationship=NULL
+ WHERE guarantorid IS NOT NULL
+ });
+ }
+
+ if ( column_exists( 'borrowers', 'guarantorid' ) ) {
+ $dbh->do(q{
+ ALTER TABLE borrowers DROP guarantorid;
+ });
+ }
+
+ if ( column_exists( 'deletedborrowers', 'guarantorid' ) ) {
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers DROP guarantorid;
+ });
+ }
+
+ if ( column_exists( 'borrower_modifications', 'guarantorid' ) ) {
+ $dbh->do(q{
+ ALTER TABLE borrower_modifications DROP guarantorid;
+ });
+ }
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 14570: Make it possible to add multiple guarantors to a record)\n";
+}
+
+$DBversion = '19.06.00.023';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO `systempreferences` (`variable`,`value`,`explanation`,`options`,`type`) VALUES
+ ('ElasticsearchMARCFormat', 'ISO2709', 'ISO2709|ARRAY', 'Elasticsearch MARC format. ISO2709 format is recommended as it is faster and takes less space, whereas array is searchable.', 'Choice')
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 22258: Add ElasticsearchMARCFormat preference)\n";
+}
+
+$DBversion = '19.06.00.024';
+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";
+}
+
+$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";
+}
+
+$DBversion = '19.06.00.026';
+if( CheckVersion( $DBversion ) ) {
+
+ unless ( column_exists( 'borrowers', 'privacy_guarantor_fines' ) ) {
+ $dbh->do(q{
+ ALTER TABLE borrowers
+ ADD privacy_guarantor_fines TINYINT(1) NOT NULL DEFAULT '0' AFTER privacy;
+ });
+ }
+
+ unless ( column_exists( 'deletedborrowers', 'privacy_guarantor_fines' ) ) {
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers
+ ADD privacy_guarantor_fines TINYINT(1) NOT NULL DEFAULT '0' AFTER privacy;
+ });
+ }
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type )
+ VALUES (
+ 'AllowStaffToSetFinesVisibilityForGuarantor', '0', NULL,
+ 'If enabled, library staff can set a patron''s fines to be visible to linked patrons from the opac.', 'YesNo'
+ ), (
+ 'AllowPatronToSetFinesVisibilityForGuarantor', '0', NULL,
+ 'If enabled, the patron can set fines to be visible to his or her guarantor', 'YesNo'
+ )
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20691: Add ability for guarantors to view guarantee's fines in OPAC)\n";
+}
+
+$DBversion = '19.06.00.027';
+if( CheckVersion( $DBversion ) ) {
+
+ if( !TableExists( 'itemtypes_branches' ) ) {
+ $dbh->do( "
+ CREATE TABLE itemtypes_branches( -- association table between authorised_values and branches
+ itemtype VARCHAR(10) NOT NULL,
+ branchcode VARCHAR(10) NOT NULL,
+ FOREIGN KEY (itemtype) REFERENCES itemtypes(itemtype) ON DELETE CASCADE,
+ FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+ ");
+ }
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 15497: Add itemtypes_branches table)\n";
+}
+
+$DBversion = '19.06.00.028';
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(qq{
+ UPDATE
+ accountlines
+ SET
+ accounttype = 'ACCOUNT'
+ WHERE
+ accounttype = 'A';
+ });
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 11573: Fix accounttypes for 'A')\n";
+}
+
+$DBversion = '19.06.00.029';
+if ( CheckVersion($DBversion) ) {
+
+ unless ( TableExists( 'cash_registers' ) ) {
+ $dbh->do(qq{
+ CREATE TABLE `cash_registers` (
+ `id` int(11) NOT NULL auto_increment, -- unique identifier for each account register
+ `name` varchar(24) NOT NULL, -- the user friendly identifier for each account register
+ `description` longtext NOT NULL, -- the user friendly description for each account register
+ `branch` varchar(10) NOT NULL, -- the foreign key the library this account register belongs
+ `branch_default` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote that this till is the branch default
+ `starting_float` decimal(28, 6), -- the starting float this account register should be assigned
+ `archived` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote if this till is archived or not
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`,`branch`),
+ CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE
+ ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
+ });
+ }
+
+ unless ( column_exists( 'accountlines', 'register_id' ) ) {
+ $dbh->do(qq{ALTER TABLE `accountlines` ADD `register_id` int(11) NULL DEFAULT NULL AFTER `manager_id`});
+ $dbh->do(qq{
+ ALTER TABLE `accountlines`
+ ADD CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`)
+ REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
+ });
+ }
+
+ $dbh->do(qq{
+ INSERT IGNORE INTO `userflags` (`bit`, `flag`, `flagdesc`, `defaulton`)
+ VALUES (25, 'cash_management', 'Cash management', 0)
+ });
+
+ $dbh->do(qq{
+ INSERT IGNORE permissions (module_bit, code, description)
+ VALUES
+ (25, 'manage_cash_registers', 'Add and remove cash registers')
+ });
+
+ $dbh->do(qq{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES
+ ('UseCashRegisters','0','','Use cash registers with the accounting system and assign patron transactions to them.','YesNo')
+ });
+
+ SetVersion($DBversion);
+ print "Upgrade to $DBversion done (Bug 23321: Add cash_registers table, permissions and preferences)\n";
+}
+
+$DBversion = '19.06.00.030';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q|
+ CREATE TABLE IF NOT EXISTS club_holds (
+ id INT(11) NOT NULL AUTO_INCREMENT,
+ club_id INT(11) NOT NULL, -- id for the club the hold was generated for
+ biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
+ item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
+ date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
+ PRIMARY KEY (id),
+ -- KEY club_id (club_id),
+ CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+ |);
+
+ $dbh->do(q|
+ CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
+ id INT(11) NOT NULL AUTO_INCREMENT,
+ club_hold_id INT(11) NOT NULL,
+ patron_id INT(11) NOT NULL,
+ hold_id INT(11),
+ error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
+ 'tooManyHoldsForThisRecord', 'tooManyReservesToday',
+ 'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
+ 'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
+ ) NULL DEFAULT NULL,
+ error_message varchar(100) NULL DEFAULT NULL,
+ PRIMARY KEY (id),
+ -- KEY club_hold_id (club_hold_id),
+ CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+ |);
+
+ # Always end with this (adjust the bug info)
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 19618: add club_holds tables)\n";
+}
+
+$DBversion = '19.06.00.031';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
+ ('OPACDetailQRCode','0','','Enable the display of a QR Code on the OPAC detail page','YesNo');
+ |);
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 23566: Add OPACDetailQRCode system preference)\n";
+}
+
+$DBversion = '19.06.00.032';
+if ( CheckVersion($DBversion) ) {
+ if ( !column_exists( 'search_marc_to_field', 'search' ) ) {
+ $dbh->do(q|
+ ALTER TABLE `search_marc_to_field` ADD COLUMN `search` tinyint(1) NOT NULL DEFAULT 1
+ |);
+ }
+ if ( !column_exists( 'search_field', 'staff_client' ) ) {
+ $dbh->do(q|
+ ALTER TABLE `search_field` ADD COLUMN `staff_client` tinyint(1) NOT NULL DEFAULT 1
+ |);
+ }
+ if ( !column_exists( 'search_field', 'opac' ) ) {
+ $dbh->do(q|
+ ALTER TABLE `search_field` ADD COLUMN `opac` tinyint(1) NOT NULL DEFAULT 1
+ |);
+ }
+
+ SetVersion($DBversion);
+ print
+"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';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(qq{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES
+ ('OnSiteCheckoutAutoCheck','0','','Enable/Do not enable onsite checkout by default if last checkout was an onsite checkout','YesNo')
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 23686: Add OnSiteCheckoutAutoCheck system preference)\n";
+}
+
+$DBversion = '19.06.00.034';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
+ ('TransfersBlockCirc','1',NULL,'Should the transfer modal block circulation staff from continuing scanning items','YesNo')
+ });
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 23007: Make transfer modals optionally block circ)\n";
+}
+
+$DBversion = '19.06.00.035';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES
+ ( 'IntranetCoce','0', NULL, 'If on, enables cover retrieval from the configured Coce server in the staff client', 'YesNo')
+ });
+
+ $dbh->do(qq{
+ UPDATE systempreferences SET
+ variable = 'OpacCoce',
+ explanation = 'If on, enables cover retrieval from the configured Coce server in the OPAC'
+ WHERE
+ variable = 'Coce'
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 18421: Add Coce image cache to the Intranet)\n";
+}
+
+$DBversion = '19.06.00.036';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type`) VALUES
+ ('QueryRegexEscapeOptions', 'escape', 'dont_escape|escape|unescape_escaped', 'Escape option for regexps delimiters in Elasicsearch queries.', 'Choice')
+ });
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 20334: Add elasticsearch escape options preference)\n";
+}
+
+$DBversion = '19.06.00.037';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` )
+ VALUES ('PayPalReturnURL','BaseURL','BaseURL|OPACAlias','Specify whether PayPal will return to the url specified in the OPACBaseURL option or to the OPAC\'s alias url.','Choice')
+ });
+
+ SetVersion( $DBversion );
+ 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";
+}
+
+$DBversion = '19.06.00.039';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO keyboard_shortcuts (shortcut_name, shortcut_keys) VALUES
+ ("copy_line","Ctrl-C"),
+ ("copy_subfield","Shift-Ctrl-C"),
+ ("paste_line","Ctrl-P"),
+ ("insert_line","Ctrl-I")
+ ;
+ |);
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 17179: Add additional keyboard_shortcuts)\n";
+}
+
+$DBversion = '19.06.00.040';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences
+ (variable,value,explanation,options,type)
+ VALUES
+ ('RoundFinesAtPayment','0','If enabled any fines with fractions of a cent will be rounded to the nearest cent when payments are collected. e.g. 1.004 will be paid off by a 1.00 payment','0','YesNo')
+ |);
+
+ SetVersion( $DBversion );
+ 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
+# if there is anything in the atomicupdate, read and execute it.
+my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/';
+opendir( my $dirh, $update_dir );
+foreach my $file ( sort readdir $dirh ) {
+ next if $file !~ /\.(sql|perl)$/; #skip other files
+ next if $file eq 'skeleton.perl'; # skip the skeleton file
+ print "DEV atomic update: $file\n";
+ if ( $file =~ /\.sql$/ ) {
+ my $installer = C4::Installer->new();
+ my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
+ } elsif ( $file =~ /\.perl$/ ) {
+ my $code = read_file( $update_dir . $file );
+ eval $code;
+ say "Atomic update generated errors: $@" if $@;
+ }
+}
+
+=head1 FUNCTIONS
+
=head2 DropAllForeignKeys($table)
Drop all foreign keys of the table $table
projects / koha-ffzg.git / blobdiff