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

(-)a/installer/data/mysql/atomicupdate/bug_10190.pl (-262 / +46 lines)
Lines 8-16 return { Link Here
8
        my ($args) = @_;
8
        my ($args) = @_;
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
10
11
        # This script first pulls data from these tables, then deletes them. -> Do not run if they do not exist.
11
        # This script pulls data from these tables then drops them.
12
        # Skip silently if they no longer exist (e.g. fresh install or already migrated).
12
        if ( !TableExists('overduerules_transport_types') || !TableExists('overduerules') ) {
13
        if ( !TableExists('overduerules_transport_types') || !TableExists('overduerules') ) {
13
            exit;
14
            say_info( $out, "Tables overduerules / overduerules_transport_types not found, skipping migration" );
15
            return;
14
        }
16
        }
15
17
16
        # Remove the tools sub-permission for notice triggers; access is now
18
        # Remove the tools sub-permission for notice triggers; access is now
Lines 19-67 return { Link Here
19
        $dbh->do(q|DELETE FROM permissions WHERE code = 'edit_notice_status_triggers'|);
21
        $dbh->do(q|DELETE FROM permissions WHERE code = 'edit_notice_status_triggers'|);
20
        say_success( $out, "Removed deprecated permission 'edit_notice_status_triggers'" );
22
        say_success( $out, "Removed deprecated permission 'edit_notice_status_triggers'" );
21
23
22
        # Populate empty overduerules table for missing categories
24
        # Fetch all explicit overdue rules with their transport types.
23
        $dbh->do(
25
        # No phantom rows are added - only rules that were explicitly configured are migrated.
24
            q|
25
            INSERT IGNORE INTO overduerules (branchcode, categorycode)
26
            SELECT '', categorycode
27
            FROM categories
28
            WHERE categorycode NOT IN (SELECT categorycode FROM overduerules);
29
        |
30
        );
31
32
        # Fetch all overdue rules
33
        my $rules = $dbh->selectall_arrayref(
26
        my $rules = $dbh->selectall_arrayref(
34
            q|
27
            q|
35
            SELECT
28
            SELECT
36
                o.overduerules_id,
37
                o.branchcode,
29
                o.branchcode,
38
                o.categorycode,
30
                o.categorycode,
39
                o.delay1,
31
                o.delay1,
40
                o.letter1,
32
                o.letter1,
41
                o.debarred1,
33
                o.debarred1,
42
                o.delay2,
34
                o.delay2,
43
                o.debarred2,
44
                o.letter2,
35
                o.letter2,
36
                o.debarred2,
45
                o.delay3,
37
                o.delay3,
46
                o.letter3,
38
                o.letter3,
47
                o.debarred3,
39
                o.debarred3,
48
                GROUP_CONCAT(
40
                GROUP_CONCAT(
49
                    CASE WHEN ott.letternumber = 1 THEN ott.message_transport_type END
41
                    CASE WHEN ott.letternumber = 1 THEN ott.message_transport_type END
50
                    ORDER BY ott.message_transport_type ASC
42
                    ORDER BY ott.message_transport_type ASC
51
                ) AS message_transport_type_1,
43
                ) AS mtt_1,
52
                GROUP_CONCAT(
44
                GROUP_CONCAT(
53
                    CASE WHEN ott.letternumber = 2 THEN ott.message_transport_type END
45
                    CASE WHEN ott.letternumber = 2 THEN ott.message_transport_type END
54
                    ORDER BY ott.message_transport_type ASC
46
                    ORDER BY ott.message_transport_type ASC
55
                ) AS message_transport_type_2,
47
                ) AS mtt_2,
56
                GROUP_CONCAT(
48
                GROUP_CONCAT(
57
                    CASE WHEN ott.letternumber = 3 THEN ott.message_transport_type END
49
                    CASE WHEN ott.letternumber = 3 THEN ott.message_transport_type END
58
                    ORDER BY ott.message_transport_type ASC
50
                    ORDER BY ott.message_transport_type ASC
59
                ) AS message_transport_type_3
51
                ) AS mtt_3
60
            FROM
52
            FROM
61
                overduerules o
53
                overduerules o
62
            LEFT JOIN
54
            LEFT JOIN
63
                overduerules_transport_types ott
55
                overduerules_transport_types ott ON o.overduerules_id = ott.overduerules_id
64
                ON o.overduerules_id = ott.overduerules_id
65
            GROUP BY
56
            GROUP BY
66
                o.overduerules_id,
57
                o.overduerules_id,
67
                o.branchcode,
58
                o.branchcode,
Lines 70-77 return { Link Here
70
                o.letter1,
61
                o.letter1,
71
                o.debarred1,
62
                o.debarred1,
72
                o.delay2,
63
                o.delay2,
73
                o.debarred2,
74
                o.letter2,
64
                o.letter2,
65
                o.debarred2,
75
                o.delay3,
66
                o.delay3,
76
                o.letter3,
67
                o.letter3,
77
                o.debarred3
68
                o.debarred3
Lines 79-344 return { Link Here
79
            { Slice => {} }
70
            { Slice => {} }
80
        );
71
        );
81
72
82
        # Initialize hashes to store the frequency of delays, notices, and mtt per group (1, 2, 3)
83
        my %branchcodes;
84
        my %delay_count;
85
        my %notice_count;
86
        my %mtt_count;
87
        my %restrict_count;
88
89
        # Populate the hashes with the frequency of each value by group
90
        for my $rule ( @{$rules} ) {
91
            my $branchcode = $rule->{'branchcode'};
92
            $branchcodes{$branchcode} = 1;
93
94
            foreach my $i ( 1 .. 3 ) {
95
                my $delay    = $rule->{"delay$i"}                   // '';
96
                my $notice   = $rule->{"letter$i"}                  // '';
97
                my $mtt      = $rule->{"message_transport_type_$i"} // '';
98
                my $restrict = $rule->{"debarred$i"}                // '';
99
100
                # Increment counts only if values are defined
101
                $delay_count{$branchcode}{$i}{$delay}++       if defined $delay;
102
                $notice_count{$branchcode}{$i}{$notice}++     if defined $notice;
103
                $mtt_count{$branchcode}{$i}{$mtt}++           if defined $mtt;
104
                $restrict_count{$branchcode}{$i}{$restrict}++ if defined $restrict;
105
            }
106
        }
107
108
        # Find the most frequent delay, notice, and mtt for each branchcode and group
109
        my ( %most_frequent_delay, %most_frequent_notice, %most_frequent_mtt, %most_frequent_restrict );
110
        for my $branchcode ( keys %branchcodes ) {
111
            foreach my $i ( 1 .. 3 ) {
112
113
                # Find the most frequent delay in group $i
114
                my $max_delay_count = 0;
115
                for my $delay ( keys %{ $delay_count{$branchcode}{$i} } ) {
116
                    if ( $delay_count{$branchcode}{$i}{$delay} > $max_delay_count ) {
117
                        $max_delay_count = $delay_count{$branchcode}{$i}{$delay};
118
                        $most_frequent_delay{$branchcode}{$i} = $delay;
119
                    }
120
                }
121
122
                # Find the most frequent notice in group $i
123
                my $max_notice_count = 0;
124
                for my $notice ( keys %{ $notice_count{$branchcode}{$i} } ) {
125
                    if ( $notice_count{$branchcode}{$i}{$notice} > $max_notice_count ) {
126
                        $max_notice_count = $notice_count{$branchcode}{$i}{$notice};
127
                        $most_frequent_notice{$branchcode}{$i} = $notice;
128
                    }
129
                }
130
131
                # Find the most frequent mtt in group $i
132
                my $max_mtt_count = 0;
133
                for my $mtt ( keys %{ $mtt_count{$branchcode}{$i} } ) {
134
                    if ( $mtt_count{$branchcode}{$i}{$mtt} > $max_mtt_count ) {
135
                        $max_mtt_count = $mtt_count{$branchcode}{$i}{$mtt};
136
                        $most_frequent_mtt{$branchcode}{$i} = $mtt;
137
                    }
138
                }
139
140
                # Find the most frequent mtt in group $i
141
                my $max_restrict_count = 0;
142
                for my $restrict ( keys %{ $restrict_count{$branchcode}{$i} } ) {
143
                    if ( $restrict_count{$branchcode}{$i}{$restrict} > $max_restrict_count ) {
144
                        $max_restrict_count = $restrict_count{$branchcode}{$i}{$restrict};
145
                        $most_frequent_restrict{$branchcode}{$i} = $restrict;
146
                    }
147
                }
148
            }
149
        }
150
151
        # Migrate rules from overduerules to circulation_rules, skipping most frequent values as those will be our defaults
152
        my $insert = $dbh->prepare(
73
        my $insert = $dbh->prepare(
153
            "INSERT IGNORE INTO circulation_rules (branchcode, categorycode, itemtype, rule_name, rule_value) VALUES (?, ?, ?, ?, ?)"
74
            "INSERT IGNORE INTO circulation_rules (branchcode, categorycode, itemtype, rule_name, rule_value) VALUES (?, ?, ?, ?, ?)"
154
        );
75
        );
155
76
156
        my $itemtype = undef;
77
        my $migrated = 0;
78
157
        for my $rule ( @{$rules} ) {
79
        for my $rule ( @{$rules} ) {
158
            my $branchcode   = $rule->{'branchcode'}   || undef;
159
            my $categorycode = $rule->{'categorycode'} || undef;
160
80
161
            my $branchcode_key = $branchcode // '';
81
            # In the old system branchcode='' is the global/default library.
82
            # In circulation_rules this is represented as branchcode=NULL.
83
            my $branchcode   = $rule->{branchcode}   || undef;
84
            my $categorycode = $rule->{categorycode} || undef;
85
            my $itemtype     = undef;
86
87
            my $context_label = ( $branchcode // '*' ) . ':' . ( $categorycode // '*' );
88
162
            foreach my $i ( 1 .. 3 ) {
89
            foreach my $i ( 1 .. 3 ) {
90
                my $delay    = $rule->{"delay$i"};
91
                my $notice   = $rule->{"letter$i"};
92
                my $mtt      = $rule->{"mtt_$i"};
93
                my $restrict = $rule->{"debarred$i"};
163
94
164
                my $has_rules = 0;
95
                # A zero or null delay means this trigger was not configured in the old system.
96
                # Only migrate explicitly configured triggers.
97
                next unless defined $delay && $delay > 0;
165
98
166
                # Insert the delay rule for group $i, skipping if it matches the most frequent delay
99
                say $out "Migrating trigger $i for $context_label (delay=$delay)";
167
                if ( my $delay = $rule->{"delay$i"} ) {
168
                    $delay ||= '';
169
                    unless ( $delay eq $most_frequent_delay{$branchcode_key}{$i} ) {
170
                        $delay ||= undef;
171
                        say $out "Inserting "
172
                            . ( $branchcode   // '' ) . ":"
173
                            . ( $categorycode // '' ) . ':'
174
                            . ( $itemtype     // '' )
175
                            . " overdue_$i"
176
                            . '_delay: '
177
                            . ( $delay // '' );
178
                        $insert->execute(
179
                            $branchcode,
180
                            $categorycode,
181
                            $itemtype,
182
                            "overdue_$i" . '_delay',
183
                            $delay
184
                        );
185
                        $has_rules = 1;
186
                    }
187
                }
188
100
189
                # Insert the notice rule for group $i, skipping if it matches the most frequent notice
101
                $insert->execute( $branchcode, $categorycode, $itemtype, "overdue_${i}_delay", $delay );
190
                if ( my $notice = $rule->{"letter$i"} ) {
191
                    unless ( $notice eq $most_frequent_notice{$branchcode_key}{$i} ) {
192
                        say $out "Inserting "
193
                            . ( $branchcode   // '' ) . ":"
194
                            . ( $categorycode // '' ) . ':'
195
                            . ( $itemtype     // '' )
196
                            . " overdue_$i"
197
                            . '_notice: '
198
                            . ( $notice // '' );
199
                        $insert->execute(
200
                            $branchcode,
201
                            $categorycode,
202
                            $itemtype,
203
                            "overdue_$i" . '_notice',
204
                            $notice
205
                        );
206
                        $has_rules = 1;
207
                    }
208
                }
209
102
210
                # Insert the message transport type rule for group $i, skipping if it matches the most frequent mtt
103
                if ($notice) {
211
                if ( my $mtt = $rule->{"message_transport_type_$i"} ) {
104
                    $insert->execute( $branchcode, $categorycode, $itemtype, "overdue_${i}_notice", $notice );
212
                    unless ( $mtt eq $most_frequent_mtt{$branchcode_key}{$i} ) {
213
                        say $out "Inserting "
214
                            . ( $branchcode   // '' ) . ":"
215
                            . ( $categorycode // '' ) . ':'
216
                            . ( $itemtype     // '' )
217
                            . " overdue_$i"
218
                            . '_mtt: '
219
                            . ( $mtt // '' );
220
                        $insert->execute(
221
                            $branchcode,
222
                            $categorycode,
223
                            $itemtype,
224
                            "overdue_$i" . '_mtt',
225
                            $mtt
226
                        );
227
                        $has_rules = 1;
228
                    }
229
                }
105
                }
230
106
231
                # Insert the restrict rule for group $i
107
                if ($mtt) {
232
                if ( my $restrict = $rule->{"debarred$i"} ) {
108
                    $insert->execute( $branchcode, $categorycode, $itemtype, "overdue_${i}_mtt", $mtt );
233
                    unless ( $restrict eq $most_frequent_restrict{$branchcode_key}{$i} ) {
234
                        say $out "Inserting "
235
                            . ( $branchcode   // '' ) . ":"
236
                            . ( $categorycode // '' ) . ':'
237
                            . ( $itemtype     // '' )
238
                            . " overdue_$i"
239
                            . '_restrict: '
240
                            . ( $restrict // '' );
241
                        $insert->execute(
242
                            $branchcode,
243
                            $categorycode,
244
                            $itemtype,
245
                            "overdue_$i" . '_restrict',
246
                            $restrict
247
                        );
248
                        $has_rules = 1;
249
                    }
250
                }
109
                }
251
110
252
                # Insert the has_rules rule for group $i if and only if a rule set has been added into the db for this context.
111
                # Only insert restrict when explicitly enabled (=1); absence defaults to no restriction.
253
                if ( $has_rules == 1 ) {
112
                if ($restrict) {
254
                    say $out "Inserting "
113
                    $insert->execute( $branchcode, $categorycode, $itemtype, "overdue_${i}_restrict", $restrict );
255
                        . ( $branchcode   // '' ) . ":"
256
                        . ( $categorycode // '' ) . ':'
257
                        . ( $itemtype     // '' )
258
                        . " overdue_$i"
259
                        . "has_rules: 1";
260
                    $insert->execute(
261
                        $branchcode,
262
                        $categorycode,
263
                        $itemtype,
264
                        "overdue_$i" . '_has_rules',
265
                        1
266
                    );
267
                }
114
                }
268
            }
269
        }
270
271
        # Insert the default rules for each group
272
        for my $branchcode ( keys %branchcodes ) {
273
            my $branchcode_value = $branchcode || undef;
274
            foreach my $i ( 1 .. 3 ) {
275
                my $most_frequent_delay = $most_frequent_delay{$branchcode}{$i};
276
                say $out "Inserting "
277
                    . ( $branchcode_value // '' ) . ":all:"
278
                    . ( $itemtype         // '' )
279
                    . " default most frequent delay for overdue_$i: "
280
                    . $most_frequent_delay;
281
                $insert->execute(
282
                    $branchcode_value,
283
                    undef,
284
                    $itemtype,
285
                    "overdue_${i}_delay",
286
                    $most_frequent_delay
287
                );
288
289
                my $most_frequent_notice = $most_frequent_notice{$branchcode}{$i};
290
                say $out "Inserting "
291
                    . ( $branchcode_value // '' )
292
                    . ":all:all default most frequent notice for overdue_$i: "
293
                    . $most_frequent_notice;
294
                $insert->execute(
295
                    $branchcode_value,
296
                    undef,
297
                    undef,
298
                    "overdue_${i}_notice",
299
                    $most_frequent_notice
300
                );
301
302
                my $most_frequent_mtt = $most_frequent_mtt{$branchcode}{$i};
303
                say $out "Inserting "
304
                    . ( $branchcode_value // '' )
305
                    . ":all:all default most frequent mtt for overdue_$i: "
306
                    . $most_frequent_mtt;
307
                $insert->execute(
308
                    $branchcode_value,
309
                    undef,
310
                    undef,
311
                    "overdue_${i}_mtt",
312
                    $most_frequent_mtt
313
                );
314
115
315
                my $most_frequent_restrict = $most_frequent_restrict{$branchcode}{$i};
116
                # has_rules is a UI sentinel that marks an explicit ruleset for this context/trigger.
316
                say $out "Inserting "
117
                # It is not used by the backend (overdue_notices.pl).
317
                    . ( $branchcode_value // '' )
118
                $insert->execute( $branchcode, $categorycode, $itemtype, "overdue_${i}_has_rules", 1 );
318
                    . ":all:all default most frequent restrict for overdue_$i: "
319
                    . $most_frequent_restrict;
320
                $insert->execute(
321
                    $branchcode_value,
322
                    undef,
323
                    undef,
324
                    "overdue_${i}_restrict",
325
                    $most_frequent_restrict
326
                );
327
328
                # Insert the has_rules rule for group $i's default context
329
                say $out "Inserting " . ( $branchcode_value // '' ) . ":all:all default has_rules for overdue_$i: 1";
330
                $insert->execute(
331
                    $branchcode_value,
332
                    undef,
333
                    undef,
334
                    "overdue_${i}_has_rules",
335
                    1
336
                );
337
119
120
                $migrated++;
338
            }
121
            }
339
        }
122
        }
340
123
341
        $dbh->do(q|DROP TABLE overduerules_transport_types|) if TableExists('overduerules_transport_types');
124
        say_success( $out, "Migrated $migrated trigger(s) from overduerules to circulation_rules" );
342
        $dbh->do(q|DROP TABLE overduerules|)                 if TableExists('overduerules');
125
126
        $dbh->do(q|DROP TABLE overduerules_transport_types|);
127
        $dbh->do(q|DROP TABLE overduerules|);
343
    }
128
    }
344
};
129
};
345
- 

Return to bug 10190