Bugzilla – Attachment 170357 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), 11.68 KB, created by
Martin Renvoize (ashimema)
on 2024-08-15 11:36:28 UTC
(
hide
)
Description:
Bug 10190: DB migration for overduerules
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2024-08-15 11:36:28 UTC
Size:
11.68 KB
patch
obsolete
>From 11c70393c4e313a24918f6743377bbd0fa6f4143 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 >--- > .../data/mysql/atomicupdate/bug_10190.pl | 260 ++++++++++++++++++ > 1 file changed, 260 insertions(+) > 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..ea62a9bf12f >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_10190.pl >@@ -0,0 +1,260 @@ >+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 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 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 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 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 default most frequent delay for overdue_$i"; >+ $insert->execute( >+ $branchcode_value, >+ undef, >+ $itemtype, >+ "overdue_${i}_delay", >+ $most_frequent_delay >+ ); >+ >+ my $most_frequent_notice = $most_frequent_notice{$branchcode}{$i}; >+ say $out "Inserting default most frequent notice for overdue_$i"; >+ $insert->execute( >+ $branchcode_value, >+ undef, >+ undef, >+ "overdue_${i}_notice", >+ $most_frequent_notice >+ ); >+ >+ my $most_frequent_mtt = $most_frequent_mtt{$branchcode}{$i}; >+ say $out "Inserting default most frequent mtt for overdue_$i"; >+ $insert->execute( >+ $branchcode_value, >+ undef, >+ undef, >+ "overdue_${i}_mtt", >+ $most_frequent_mtt >+ ); >+ >+ my $most_frequent_restrict = $most_frequent_restrict{$branchcode}{$i}; >+ say $out "Inserting default most frequent restrict for overdue_$i"; >+ $insert->execute( >+ $branchcode_value, >+ undef, >+ undef, >+ "overdue_${i}_restrict", >+ $most_frequent_restrict >+ ); >+ >+ } >+ } >+ } >+}; >-- >2.46.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