View | Details | Raw Unified | Return to bug 10190
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_10190.pl (+267 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
3
4
return {
5
    bug_number  => "10190",
6
    description => "Migrate overduerules to circulation_rules",
7
    up          => sub {
8
        my ($args) = @_;
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
11
        # Populate empty overduerules table for missing categories
12
        $dbh->do(
13
            q|
14
            INSERT IGNORE INTO overduerules (branchcode, categorycode)
15
            SELECT '', categorycode
16
            FROM categories
17
            WHERE categorycode NOT IN (SELECT categorycode FROM overduerules);
18
        |
19
        );
20
21
        # Fetch all overdue rules
22
        my $rules = $dbh->selectall_arrayref(
23
            q|
24
            SELECT
25
                o.overduerules_id,
26
                o.branchcode,
27
                o.categorycode,
28
                o.delay1,
29
                o.letter1,
30
                o.debarred1,
31
                o.delay2,
32
                o.debarred2,
33
                o.letter2,
34
                o.delay3,
35
                o.letter3,
36
                o.debarred3,
37
                GROUP_CONCAT(
38
                    CASE WHEN ott.letternumber = 1 THEN ott.message_transport_type END
39
                    ORDER BY ott.message_transport_type ASC
40
                ) AS message_transport_type_1,
41
                GROUP_CONCAT(
42
                    CASE WHEN ott.letternumber = 2 THEN ott.message_transport_type END
43
                    ORDER BY ott.message_transport_type ASC
44
                ) AS message_transport_type_2,
45
                GROUP_CONCAT(
46
                    CASE WHEN ott.letternumber = 3 THEN ott.message_transport_type END
47
                    ORDER BY ott.message_transport_type ASC
48
                ) AS message_transport_type_3
49
            FROM
50
                overduerules o
51
            LEFT JOIN
52
                overduerules_transport_types ott
53
                ON o.overduerules_id = ott.overduerules_id
54
            GROUP BY
55
                o.overduerules_id,
56
                o.branchcode,
57
                o.categorycode,
58
                o.delay1,
59
                o.letter1,
60
                o.debarred1,
61
                o.delay2,
62
                o.debarred2,
63
                o.letter2,
64
                o.delay3,
65
                o.letter3,
66
                o.debarred3
67
            |,
68
            { Slice => {} }
69
        );
70
71
        # Initialize hashes to store the frequency of delays, notices, and mtt per group (1, 2, 3)
72
        my %branchcodes;
73
        my %delay_count;
74
        my %notice_count;
75
        my %mtt_count;
76
        my %restrict_count;
77
78
        # Populate the hashes with the frequency of each value by group
79
        for my $rule ( @{$rules} ) {
80
            my $branchcode = $rule->{'branchcode'};
81
            $branchcodes{$branchcode} = 1;
82
83
            foreach my $i ( 1 .. 3 ) {
84
                my $delay    = $rule->{"delay$i"}                   // '';
85
                my $notice   = $rule->{"letter$i"}                  // '';
86
                my $mtt      = $rule->{"message_transport_type_$i"} // '';
87
                my $restrict = $rule->{"debarred$i"}                // '';
88
89
                # Increment counts only if values are defined
90
                $delay_count{$branchcode}{$i}{$delay}++       if defined $delay;
91
                $notice_count{$branchcode}{$i}{$notice}++     if defined $notice;
92
                $mtt_count{$branchcode}{$i}{$mtt}++           if defined $mtt;
93
                $restrict_count{$branchcode}{$i}{$restrict}++ if defined $restrict;
94
            }
95
        }
96
97
        # Find the most frequent delay, notice, and mtt for each branchcode and group
98
        my ( %most_frequent_delay, %most_frequent_notice, %most_frequent_mtt, %most_frequent_restrict );
99
        for my $branchcode ( keys %branchcodes ) {
100
            foreach my $i ( 1 .. 3 ) {
101
102
                # Find the most frequent delay in group $i
103
                my $max_delay_count = 0;
104
                for my $delay ( keys %{ $delay_count{$branchcode}{$i} } ) {
105
                    if ( $delay_count{$branchcode}{$i}{$delay} > $max_delay_count ) {
106
                        $max_delay_count = $delay_count{$branchcode}{$i}{$delay};
107
                        $most_frequent_delay{$branchcode}{$i} = $delay;
108
                    }
109
                }
110
111
                # Find the most frequent notice in group $i
112
                my $max_notice_count = 0;
113
                for my $notice ( keys %{ $notice_count{$branchcode}{$i} } ) {
114
                    if ( $notice_count{$branchcode}{$i}{$notice} > $max_notice_count ) {
115
                        $max_notice_count = $notice_count{$branchcode}{$i}{$notice};
116
                        $most_frequent_notice{$branchcode}{$i} = $notice;
117
                    }
118
                }
119
120
                # Find the most frequent mtt in group $i
121
                my $max_mtt_count = 0;
122
                for my $mtt ( keys %{ $mtt_count{$branchcode}{$i} } ) {
123
                    if ( $mtt_count{$branchcode}{$i}{$mtt} > $max_mtt_count ) {
124
                        $max_mtt_count = $mtt_count{$branchcode}{$i}{$mtt};
125
                        $most_frequent_mtt{$branchcode}{$i} = $mtt;
126
                    }
127
                }
128
129
                # Find the most frequent mtt in group $i
130
                my $max_restrict_count = 0;
131
                for my $restrict ( keys %{ $restrict_count{$branchcode}{$i} } ) {
132
                    if ( $restrict_count{$branchcode}{$i}{$restrict} > $max_restrict_count ) {
133
                        $max_restrict_count = $restrict_count{$branchcode}{$i}{$restrict};
134
                        $most_frequent_restrict{$branchcode}{$i} = $restrict;
135
                    }
136
                }
137
            }
138
        }
139
140
        # Migrate rules from overduerules to circulation_rules, skipping most frequent values as those will be our defaults
141
        my $insert = $dbh->prepare(
142
            "INSERT IGNORE INTO circulation_rules (branchcode, categorycode, itemtype, rule_name, rule_value) VALUES (?, ?, ?, ?, ?)"
143
        );
144
145
        my $itemtype = undef;
146
        for my $rule ( @{$rules} ) {
147
            my $branchcode   = $rule->{'branchcode'}   || undef;
148
            my $categorycode = $rule->{'categorycode'} || undef;
149
150
            my $branchcode_key = $branchcode // '';
151
            foreach my $i ( 1 .. 3 ) {
152
153
                # Insert the delay rule for group $i, skipping if it matches the most frequent delay
154
                if ( my $delay = $rule->{"delay$i"} ) {
155
                    $delay ||= '';
156
                    unless ( $delay eq $most_frequent_delay{$branchcode_key}{$i} ) {
157
                        $delay ||= undef;
158
                        say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_delay: ' . $delay;
159
                        $insert->execute(
160
                            $branchcode,
161
                            $categorycode,
162
                            $itemtype,
163
                            "overdue_$i" . '_delay',
164
                            $delay
165
                        );
166
                    }
167
                }
168
169
                # Insert the notice rule for group $i, skipping if it matches the most frequent notice
170
                if ( my $notice = $rule->{"letter$i"} ) {
171
                    unless ( $notice eq $most_frequent_notice{$branchcode_key}{$i} ) {
172
                        say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_notice: ' . $notice;
173
                        $insert->execute(
174
                            $branchcode,
175
                            $categorycode,
176
                            $itemtype,
177
                            "overdue_$i" . '_notice',
178
                            $notice
179
                        );
180
                    }
181
                }
182
183
                # Insert the message transport type rule for group $i, skipping if it matches the most frequent mtt
184
                if ( my $mtt = $rule->{"message_transport_type_$i"} ) {
185
                    unless ( $mtt eq $most_frequent_mtt{$branchcode_key}{$i} ) {
186
                        say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_mtt: ' . $mtt;
187
                        $insert->execute(
188
                            $branchcode,
189
                            $categorycode,
190
                            $itemtype,
191
                            "overdue_$i" . '_mtt',
192
                            $mtt
193
                        );
194
                    }
195
                }
196
197
                # Insert the restrict rule for group $i
198
                if ( my $restrict = $rule->{"debarred$i"} ) {
199
                    unless ( $restrict eq $most_frequent_restrict{$branchcode_key}{$i} ) {
200
                        say $out "Inserting $branchcode:$categorycode:$itemtype overdue_$i" . '_restrict: ' . $restrict;
201
                        $insert->execute(
202
                            $branchcode,
203
                            $categorycode,
204
                            $itemtype,
205
                            "overdue_$i" . '_restrict',
206
                            $restrict
207
                        );
208
                    }
209
                }
210
            }
211
        }
212
213
        # Insert the default rules for each group
214
        for my $branchcode ( keys %branchcodes ) {
215
            my $branchcode_value = $branchcode || undef;
216
            foreach my $i ( 1 .. 3 ) {
217
                my $most_frequent_delay = $most_frequent_delay{$branchcode}{$i};
218
                say $out "Inserting $branchcode_value:undef:$itemtype default most frequent delay for overdue_$i: "
219
                    . $most_frequent_delay;
220
                $insert->execute(
221
                    $branchcode_value,
222
                    undef,
223
                    $itemtype,
224
                    "overdue_${i}_delay",
225
                    $most_frequent_delay
226
                );
227
228
                my $most_frequent_notice = $most_frequent_notice{$branchcode}{$i};
229
                say $out "Inserting $branchcode_value:undef:undef default most frequent notice for overdue_$i: "
230
                    . $most_frequent_notice;
231
                $insert->execute(
232
                    $branchcode_value,
233
                    undef,
234
                    undef,
235
                    "overdue_${i}_notice",
236
                    $most_frequent_notice
237
                );
238
239
                my $most_frequent_mtt = $most_frequent_mtt{$branchcode}{$i};
240
                say $out "Inserting $branchcode_value:undef:undef default most frequent mtt for overdue_$i: "
241
                    . $most_frequent_mtt;
242
                $insert->execute(
243
                    $branchcode_value,
244
                    undef,
245
                    undef,
246
                    "overdue_${i}_mtt",
247
                    $most_frequent_mtt
248
                );
249
250
                my $most_frequent_restrict = $most_frequent_restrict{$branchcode}{$i};
251
                say $out "Inserting $branchcode_value:undef:undef default most frequent restrict for overdue_$i: "
252
                    . $most_frequent_restrict;
253
                $insert->execute(
254
                    $branchcode_value,
255
                    undef,
256
                    undef,
257
                    "overdue_${i}_restrict",
258
                    $most_frequent_restrict
259
                );
260
261
            }
262
        }
263
264
        $dbh->do(q|DROP TABLE overduerules|);
265
        $dbh->do(q|DROP TABLE overduerules_transport_types|);
266
    }
267
};
(-)a/installer/data/mysql/kohastructure.sql (-46 lines)
Lines 5106-5156 CREATE TABLE `old_reserves` ( Link Here
5106
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5106
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5107
/*!40101 SET character_set_client = @saved_cs_client */;
5107
/*!40101 SET character_set_client = @saved_cs_client */;
5108
5108
5109
--
5110
-- Table structure for table `overduerules`
5111
--
5112
5113
DROP TABLE IF EXISTS `overduerules`;
5114
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5115
/*!40101 SET character_set_client = utf8 */;
5116
CREATE TABLE `overduerules` (
5117
  `overduerules_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the overduerules',
5118
  `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)',
5119
  `categorycode` varchar(10) NOT NULL DEFAULT '' COMMENT 'foreign key from the categories table to define which patron category this rule is for',
5120
  `delay1` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the first notice is sent',
5121
  `letter1` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the first notice',
5122
  `debarred1` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the first notice is sent (1 for yes, 0 for no)',
5123
  `delay2` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the second notice is sent',
5124
  `debarred2` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the second notice is sent (1 for yes, 0 for no)',
5125
  `letter2` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the second notice',
5126
  `delay3` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the third notice is sent',
5127
  `letter3` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the third notice',
5128
  `debarred3` int(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',
5129
  PRIMARY KEY (`overduerules_id`),
5130
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
5131
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5132
/*!40101 SET character_set_client = @saved_cs_client */;
5133
5134
--
5135
-- Table structure for table `overduerules_transport_types`
5136
--
5137
5138
DROP TABLE IF EXISTS `overduerules_transport_types`;
5139
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5140
/*!40101 SET character_set_client = utf8 */;
5141
CREATE TABLE `overduerules_transport_types` (
5142
  `id` int(11) NOT NULL AUTO_INCREMENT,
5143
  `letternumber` int(1) NOT NULL DEFAULT 1,
5144
  `message_transport_type` varchar(20) NOT NULL DEFAULT 'email',
5145
  `overduerules_id` int(11) NOT NULL,
5146
  PRIMARY KEY (`id`),
5147
  KEY `overduerules_fk` (`overduerules_id`),
5148
  KEY `mtt_fk` (`message_transport_type`),
5149
  CONSTRAINT `mtt_fk` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
5150
  CONSTRAINT `overduerules_fk` FOREIGN KEY (`overduerules_id`) REFERENCES `overduerules` (`overduerules_id`) ON DELETE CASCADE ON UPDATE CASCADE
5151
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5152
/*!40101 SET character_set_client = @saved_cs_client */;
5153
5154
--
5109
--
5155
-- Table structure for table `patron_consent`
5110
-- Table structure for table `patron_consent`
5156
--
5111
--
5157
- 

Return to bug 10190