Attachment #172631 for bug #10190

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




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|);
    }
};




) 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`
--
- 

Return to bug 10190

Lines 4958-5008 CREATE TABLE `old_reserves` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-46 lines)
4958	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	4958	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4959	/!40101 SET character_set_client = @saved_cs_client /;	4959	/!40101 SET character_set_client = @saved_cs_client /;
4960		4960
4961	--
4962	-- Table structure for table `overduerules`
4963	--
4964
4965	DROP TABLE IF EXISTS `overduerules`;
4966	/!40101 SET @saved_cs_client = @@character_set_client /;
4967	/!40101 SET character_set_client = utf8 /;
4968	CREATE TABLE `overduerules` (
4969	`overduerules_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the overduerules',
4970	`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)',
4971	`categorycode` varchar(10) NOT NULL DEFAULT '' COMMENT 'foreign key from the categories table to define which patron category this rule is for',
4972	`delay1` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the first notice is sent',
4973	`letter1` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the first notice',
4974	`debarred1` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the first notice is sent (1 for yes, 0 for no)',
4975	`delay2` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the second notice is sent',
4976	`debarred2` varchar(1) DEFAULT '0' COMMENT 'is the patron restricted when the second notice is sent (1 for yes, 0 for no)',
4977	`letter2` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the second notice',
4978	`delay3` int(4) DEFAULT NULL COMMENT 'number of days after the item is overdue that the third notice is sent',
4979	`letter3` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the third notice',
4980	`debarred3` int(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',
4981	PRIMARY KEY (`overduerules_id`),
4982	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
4983	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4984	/!40101 SET character_set_client = @saved_cs_client /;
4985
4986	--
4987	-- Table structure for table `overduerules_transport_types`
4988	--
4989
4990	DROP TABLE IF EXISTS `overduerules_transport_types`;
4991	/!40101 SET @saved_cs_client = @@character_set_client /;
4992	/!40101 SET character_set_client = utf8 /;
4993	CREATE TABLE `overduerules_transport_types` (
4994	`id` int(11) NOT NULL AUTO_INCREMENT,
4995	`letternumber` int(1) NOT NULL DEFAULT 1,
4996	`message_transport_type` varchar(20) NOT NULL DEFAULT 'email',
4997	`overduerules_id` int(11) NOT NULL,
4998	PRIMARY KEY (`id`),
4999	KEY `overduerules_fk` (`overduerules_id`),
5000	KEY `mtt_fk` (`message_transport_type`),
5001	CONSTRAINT `mtt_fk` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
5002	CONSTRAINT `overduerules_fk` FOREIGN KEY (`overduerules_id`) REFERENCES `overduerules` (`overduerules_id`) ON DELETE CASCADE ON UPDATE CASCADE
5003	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5004	/!40101 SET character_set_client = @saved_cs_client /;
5005
5006	--	4961	--
5007	-- Table structure for table `patron_consent`	4962	-- Table structure for table `patron_consent`
5008	--	4963	--
5009	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_10190.pl (+263 lines)
		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: " . $most_frequent_delay;
219	$insert->execute(
220	$branchcode_value,
221	undef,
222	$itemtype,
223	"overdue_${i}_delay",
224	$most_frequent_delay
225	);
226
227	my $most_frequent_notice = $most_frequent_notice{$branchcode}{$i};
228	say $out "Inserting $branchcode_value:undef:undef default most frequent notice for overdue_$i: " . $most_frequent_notice;
229	$insert->execute(
230	$branchcode_value,
231	undef,
232	undef,
233	"overdue_${i}_notice",
234	$most_frequent_notice
235	);
236
237	my $most_frequent_mtt = $most_frequent_mtt{$branchcode}{$i};
238	say $out "Inserting $branchcode_value:undef:undef default most frequent mtt for overdue_$i: " . $most_frequent_mtt;
239	$insert->execute(
240	$branchcode_value,
241	undef,
242	undef,
243	"overdue_${i}_mtt",
244	$most_frequent_mtt
245	);
246
247	my $most_frequent_restrict = $most_frequent_restrict{$branchcode}{$i};
248	say $out "Inserting $branchcode_value:undef:undef default most frequent restrict for overdue_$i: " . $most_frequent_restrict;
249	$insert->execute(
250	$branchcode_value,
251	undef,
252	undef,
253	"overdue_${i}_restrict",
254	$most_frequent_restrict
255	);
256
257	}
258	}
259
260	$dbh->do(q\|DROP TABLE overduerules\|);
261	$dbh->do(q\|DROP TABLE overduerules_transport_types\|);
262	}
263	};