Bugzilla – Attachment 172631 Details for
Bug 10190
Overdue notice triggers based on item type
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 10190: DB migration for overduerules
Bug-10190-DB-migration-for-overduerules.patch (text/plain), 15.77 KB, created by
Martin Renvoize (ashimema)
on 2024-10-10 16:19:28 UTC
(
hide
)
Description:
Bug 10190: DB migration for overduerules
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2024-10-10 16:19:28 UTC
Size:
15.77 KB
patch
obsolete
>From 6e6020eb7a2d2b2aacbbadb752bb52647670f37a Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >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 <https://library.cityofglasgowcollege.ac.uk> >Signed-off-by: George Harkins <George.Harkins@cityofglasgowcollege.ac.uk> >--- > .../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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 10190
:
170357
|
170358
|
170359
|
170360
|
170361
|
170362
|
170363
|
170364
|
170365
|
170366
|
170367
|
170368
|
170369
|
170370
|
170371
|
170372
|
170373
|
170374
|
170375
|
170376
|
170380
|
170449
|
171727
|
171728
|
171729
|
171730
|
171731
|
171732
|
171733
|
171734
|
171735
|
171736
|
171737
|
171738
|
171739
|
171740
|
171741
|
171742
|
171743
|
171744
|
171745
|
171746
|
171747
|
171748
|
171749
|
171750
|
171751
|
171752
|
171753
|
171754
|
171755
|
172588
|
172589
|
172590
|
172591
|
172592
|
172593
|
172594
|
172595
|
172596
|
172597
|
172598
|
172599
|
172600
|
172601
|
172602
|
172603
|
172604
|
172605
|
172606
|
172607
|
172608
|
172609
|
172612
|
172614
|
172617
|
172619
|
172621
|
172622
|
172623
|
172624
|
172625
|
172626
|
172630
| 172631 |
172632
|
172633
|
172634
|
172635
|
172636
|
172637
|
172638
|
172639
|
172640
|
172641
|
172642
|
172643
|
172644
|
172645
|
172646
|
172647
|
172648
|
172649
|
172650
|
172651
|
172652
|
172653
|
172654
|
172655
|
172656
|
172657
|
172658
|
172659
|
172660
|
172661
|
172662
|
172663