From cf50600e028ed38148347dfa2ad095d1d54a1a94 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Wed, 7 Aug 2024 15:53:09 +0100 Subject: [PATCH] Bug 10190: DB migration for overduerules This patch adds the data migration for overduerules to circulation rules. We attempt to create the default rules based on most commonly occuring rule values and then add overrides for all other rule values we find. Sponsored-by: Glasgow Colleges Library Group --- .../data/mysql/atomicupdate/bug_10190.pl | 263 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 45 --- 2 files changed, 263 insertions(+), 45 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_10190.pl diff --git a/installer/data/mysql/atomicupdate/bug_10190.pl b/installer/data/mysql/atomicupdate/bug_10190.pl new file mode 100755 index 00000000000..5c0fa6ee7cc --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_10190.pl @@ -0,0 +1,263 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_failure say_success say_info); + +return { + bug_number => "10190", + description => "Migrate overduerules to circulation_rules", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Populate empty overduerules table for missing categories + $dbh->do( + q| + INSERT IGNORE INTO overduerules (branchcode, categorycode) + SELECT '', categorycode + FROM categories + WHERE categorycode NOT IN (SELECT categorycode FROM overduerules); + | + ); + + # Fetch all overdue rules + my $rules = $dbh->selectall_arrayref( + q| + SELECT + o.overduerules_id, + o.branchcode, + o.categorycode, + o.delay1, + o.letter1, + o.debarred1, + o.delay2, + o.debarred2, + o.letter2, + o.delay3, + o.letter3, + o.debarred3, + GROUP_CONCAT( + CASE WHEN ott.letternumber = 1 THEN ott.message_transport_type END + ORDER BY ott.message_transport_type ASC + ) AS message_transport_type_1, + GROUP_CONCAT( + CASE WHEN ott.letternumber = 2 THEN ott.message_transport_type END + ORDER BY ott.message_transport_type ASC + ) AS message_transport_type_2, + GROUP_CONCAT( + CASE WHEN ott.letternumber = 3 THEN ott.message_transport_type END + ORDER BY ott.message_transport_type ASC + ) AS message_transport_type_3 + FROM + overduerules o + LEFT JOIN + overduerules_transport_types ott + ON o.overduerules_id = ott.overduerules_id + GROUP BY + o.overduerules_id, + o.branchcode, + o.categorycode, + o.delay1, + o.letter1, + o.debarred1, + o.delay2, + o.debarred2, + o.letter2, + o.delay3, + o.letter3, + o.debarred3 + |, + { Slice => {} } + ); + + # Initialize hashes to store the frequency of delays, notices, and mtt per group (1, 2, 3) + my %branchcodes; + my %delay_count; + my %notice_count; + my %mtt_count; + my %restrict_count; + + # Populate the hashes with the frequency of each value by group + for my $rule ( @{$rules} ) { + my $branchcode = $rule->{'branchcode'}; + $branchcodes{$branchcode} = 1; + + foreach my $i ( 1 .. 3 ) { + my $delay = $rule->{"delay$i"} // ''; + my $notice = $rule->{"letter$i"} // ''; + my $mtt = $rule->{"message_transport_type_$i"} // ''; + my $restrict = $rule->{"debarred$i"} // ''; + + # Increment counts only if values are defined + $delay_count{$branchcode}{$i}{$delay}++ if defined $delay; + $notice_count{$branchcode}{$i}{$notice}++ if defined $notice; + $mtt_count{$branchcode}{$i}{$mtt}++ if defined $mtt; + $restrict_count{$branchcode}{$i}{$restrict}++ if defined $restrict; + } + } + + # Find the most frequent delay, notice, and mtt for each branchcode and group + my ( %most_frequent_delay, %most_frequent_notice, %most_frequent_mtt, %most_frequent_restrict ); + for my $branchcode ( keys %branchcodes ) { + foreach my $i ( 1 .. 3 ) { + + # Find the most frequent delay in group $i + my $max_delay_count = 0; + for my $delay ( keys %{ $delay_count{$branchcode}{$i} } ) { + if ( $delay_count{$branchcode}{$i}{$delay} > $max_delay_count ) { + $max_delay_count = $delay_count{$branchcode}{$i}{$delay}; + $most_frequent_delay{$branchcode}{$i} = $delay; + } + } + + # Find the most frequent notice in group $i + my $max_notice_count = 0; + for my $notice ( keys %{ $notice_count{$branchcode}{$i} } ) { + if ( $notice_count{$branchcode}{$i}{$notice} > $max_notice_count ) { + $max_notice_count = $notice_count{$branchcode}{$i}{$notice}; + $most_frequent_notice{$branchcode}{$i} = $notice; + } + } + + # Find the most frequent mtt in group $i + my $max_mtt_count = 0; + for my $mtt ( keys %{ $mtt_count{$branchcode}{$i} } ) { + if ( $mtt_count{$branchcode}{$i}{$mtt} > $max_mtt_count ) { + $max_mtt_count = $mtt_count{$branchcode}{$i}{$mtt}; + $most_frequent_mtt{$branchcode}{$i} = $mtt; + } + } + + # Find the most frequent mtt in group $i + my $max_restrict_count = 0; + for my $restrict ( keys %{ $restrict_count{$branchcode}{$i} } ) { + if ( $restrict_count{$branchcode}{$i}{$restrict} > $max_restrict_count ) { + $max_restrict_count = $restrict_count{$branchcode}{$i}{$restrict}; + $most_frequent_restrict{$branchcode}{$i} = $restrict; + } + } + } + } + + # Migrate rules from overduerules to circulation_rules, skipping most frequent values as those will be our defaults + my $insert = $dbh->prepare( + "INSERT IGNORE INTO circulation_rules (branchcode, categorycode, itemtype, rule_name, rule_value) VALUES (?, ?, ?, ?, ?)" + ); + + my $itemtype = undef; + for my $rule ( @{$rules} ) { + my $branchcode = $rule->{'branchcode'} || undef; + my $categorycode = $rule->{'categorycode'} || undef; + + my $branchcode_key = $branchcode // ''; + foreach my $i ( 1 .. 3 ) { + + # Insert the delay rule for group $i, skipping if it matches the most frequent delay + if ( my $delay = $rule->{"delay$i"} ) { + $delay ||= ''; + unless ( $delay eq $most_frequent_delay{$branchcode_key}{$i} ) { + $delay ||= undef; + say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_delay: ' . $delay; + $insert->execute( + $branchcode, + $categorycode, + $itemtype, + "overdue_$i" . '_delay', + $delay + ); + } + } + + # Insert the notice rule for group $i, skipping if it matches the most frequent notice + if ( my $notice = $rule->{"letter$i"} ) { + unless ( $notice eq $most_frequent_notice{$branchcode_key}{$i} ) { + say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_notice: ' . $notice; + $insert->execute( + $branchcode, + $categorycode, + $itemtype, + "overdue_$i" . '_notice', + $notice + ); + } + } + + # Insert the message transport type rule for group $i, skipping if it matches the most frequent mtt + if ( my $mtt = $rule->{"message_transport_type_$i"} ) { + unless ( $mtt eq $most_frequent_mtt{$branchcode_key}{$i} ) { + say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_mtt: ' . $mtt; + $insert->execute( + $branchcode, + $categorycode, + $itemtype, + "overdue_$i" . '_mtt', + $mtt + ); + } + } + + # Insert the restrict rule for group $i + if ( my $restrict = $rule->{"debarred$i"} ) { + unless ( $restrict eq $most_frequent_restrict{$branchcode_key}{$i} ) { + say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_restrict: ' . $restrict; + $insert->execute( + $branchcode, + $categorycode, + $itemtype, + "overdue_$i" . '_restrict', + $restrict + ); + } + } + } + } + + # Insert the default rules for each group + for my $branchcode ( keys %branchcodes ) { + my $branchcode_value = $branchcode || undef; + foreach my $i ( 1 .. 3 ) { + my $most_frequent_delay = $most_frequent_delay{$branchcode}{$i}; + say $out "Inserting $branchcode_value:undef:$itemtype default most frequent delay for overdue_$i: " . $most_frequent_delay; + $insert->execute( + $branchcode_value, + undef, + $itemtype, + "overdue_${i}_delay", + $most_frequent_delay + ); + + my $most_frequent_notice = $most_frequent_notice{$branchcode}{$i}; + say $out "Inserting $branchcode_value:undef:undef default most frequent notice for overdue_$i: " . $most_frequent_notice; + $insert->execute( + $branchcode_value, + undef, + undef, + "overdue_${i}_notice", + $most_frequent_notice + ); + + my $most_frequent_mtt = $most_frequent_mtt{$branchcode}{$i}; + say $out "Inserting $branchcode_value:undef:undef default most frequent mtt for overdue_$i: " . $most_frequent_mtt; + $insert->execute( + $branchcode_value, + undef, + undef, + "overdue_${i}_mtt", + $most_frequent_mtt + ); + + my $most_frequent_restrict = $most_frequent_restrict{$branchcode}{$i}; + say $out "Inserting $branchcode_value:undef:undef default most frequent restrict for overdue_$i: " . $most_frequent_restrict; + $insert->execute( + $branchcode_value, + undef, + undef, + "overdue_${i}_restrict", + $most_frequent_restrict + ); + + } + } + + $dbh->do(q|DROP TABLE overduerules|); + $dbh->do(q|DROP TABLE overduerules_transport_types|); + } +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 31f0828a24e..c01fa7afe79 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4958,51 +4958,6 @@ CREATE TABLE `old_reserves` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; --- --- Table structure for table `overduerules` --- - -DROP TABLE IF EXISTS `overduerules`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `overduerules` ( - `overduerules_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the overduerules', - `branchcode` varchar(10) NOT NULL DEFAULT '' COMMENT 'foreign key from the branches table to define which branch this rule is for (if blank it''s all libraries)', - `categorycode` varchar(10) NOT NULL DEFAULT '' COMMENT 'foreign key from the categories table to define which patron category this rule is for', - `delay1` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the first notice is sent', - `letter1` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the first notice', - `debarred1` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the first notice is sent (1 for yes, 0 for no)', - `delay2` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the second notice is sent', - `debarred2` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the second notice is sent (1 for yes, 0 for no)', - `letter2` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the second notice', - `delay3` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the third notice is sent', - `letter3` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the third notice', - `debarred3` int(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)', - PRIMARY KEY (`overduerules_id`), - UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `overduerules_transport_types` --- - -DROP TABLE IF EXISTS `overduerules_transport_types`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `overduerules_transport_types` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `letternumber` int(1) NOT NULL DEFAULT 1, - `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', - `overduerules_id` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `overduerules_fk` (`overduerules_id`), - KEY `mtt_fk` (`message_transport_type`), - CONSTRAINT `mtt_fk` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `overduerules_fk` FOREIGN KEY (`overduerules_id`) REFERENCES `overduerules` (`overduerules_id`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - -- -- Table structure for table `patron_consent` -- -- 2.47.0