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

(-)a/misc/maintenance/migrate_circulation_logs_to_json.pl (-1 / +250 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
2
3
# Copyright (C) 2025 OpenFifth
4
#
5
# This file is part of Koha.
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <https://www.gnu.org/licenses>.
19
20
use Modern::Perl;
21
use Getopt::Long qw( GetOptions );
22
use Pod::Usage   qw( pod2usage );
23
use JSON         qw( encode_json decode_json );
24
25
use Koha::Script;
26
use C4::Context;
27
use Koha::ActionLogs;
28
use Koha::Checkouts;
29
use Koha::Old::Checkouts;
30
31
=head1 NAME
32
33
migrate_circulation_logs_to_json.pl - Migrate old circulation log entries to JSON format
34
35
=head1 SYNOPSIS
36
37
migrate_circulation_logs_to_json.pl [ -c | --commit ] [ -v | --verbose ] [ --help ]
38
39
 Options:
40
   --help or -h       Brief usage message
41
   --commit or -c     Actually update the database (default is dry run)
42
   --verbose or -v    Print detailed information about each conversion
43
   --batch-size       Number of records to process per batch (default: 1000)
44
45
=head1 DESCRIPTION
46
47
This script migrates old CIRCULATION ISSUE action log entries from the legacy
48
itemnumber-only format to the new consistent JSON format. This is necessary
49
after the fix for Bug 41358 which ensures all circulation logs are stored in
50
JSON format for consistent reporting.
51
52
The script will:
53
1. Find all CIRCULATION ISSUE log entries where info is just an itemnumber (not JSON)
54
2. Convert them to JSON format with the structure:
55
   {
56
     "issue": <issue_id if available>,
57
     "branchcode": <branchcode if available>,
58
     "itemnumber": <itemnumber>,
59
     "confirmations": [],
60
     "forced": []
61
   }
62
63
By default, the script runs in dry-run mode. Use --commit to actually update the database.
64
65
=head1 WARNING
66
67
This script may take a long time to run on large databases with many action_logs entries.
68
It's recommended to run it during off-peak hours.
69
70
=cut
71
72
my $help       = 0;
73
my $commit     = 0;
74
my $verbose    = 0;
75
my $batch_size = 1000;
76
77
GetOptions(
78
    'h|help'       => \$help,
79
    'c|commit'     => \$commit,
80
    'v|verbose'    => \$verbose,
81
    'batch-size=i' => \$batch_size,
82
) || pod2usage(1);
83
84
if ($help) {
85
    pod2usage(1);
86
}
87
88
my $dbh = C4::Context->dbh;
89
90
print "Starting migration of CIRCULATION ISSUE logs to JSON format...\n";
91
print $commit
92
    ? "COMMIT MODE - Changes will be saved\n"
93
    : "DRY RUN MODE - No changes will be saved (use --commit to save)\n";
94
print "\n";
95
96
# First, count how many entries need conversion
97
my $count_sql = q(
98
    SELECT COUNT(*)
99
    FROM action_logs
100
    WHERE module = 'CIRCULATION'
101
      AND action = 'ISSUE'
102
      AND info IS NOT NULL
103
      AND info NOT LIKE '{%'
104
      AND info REGEXP '^[0-9]+$'
105
);
106
107
my ($total_count) = $dbh->selectrow_array($count_sql);
108
109
print "Found $total_count log entries to migrate\n";
110
print "\n";
111
112
if ( $total_count == 0 ) {
113
    print "No entries need migration. Exiting.\n";
114
    exit 0;
115
}
116
117
# Process in batches to avoid memory issues
118
my $offset    = 0;
119
my $converted = 0;
120
my $skipped   = 0;
121
my $errors    = 0;
122
123
while ( $offset < $total_count ) {
124
    my $select_sql = q(
125
        SELECT action_id, info, object, timestamp
126
        FROM action_logs
127
        WHERE module = 'CIRCULATION'
128
          AND action = 'ISSUE'
129
          AND info IS NOT NULL
130
          AND info NOT LIKE '{%'
131
          AND info REGEXP '^[0-9]+$'
132
        ORDER BY action_id
133
        LIMIT ? OFFSET ?
134
    );
135
136
    my $sth = $dbh->prepare($select_sql);
137
    $sth->execute( $batch_size, $offset );
138
139
    my $batch_count = 0;
140
141
    while ( my $row = $sth->fetchrow_hashref ) {
142
        my $action_id      = $row->{action_id};
143
        my $info           = $row->{info};
144
        my $borrowernumber = $row->{object};
145
        my $timestamp      = $row->{timestamp};
146
147
        # The info should be an itemnumber
148
        my $itemnumber = $info;
149
150
        # Try to find the corresponding issue to get issue_id and branchcode
151
        my $issue_id;
152
        my $branchcode;
153
154
        # First check old_issues (most likely location for old logs)
155
        my $old_checkout = Koha::Old::Checkouts->search(
156
            {
157
                itemnumber     => $itemnumber,
158
                borrowernumber => $borrowernumber,
159
            },
160
            {
161
                order_by => { -desc => 'returndate' },
162
                rows     => 1
163
            }
164
        )->next;
165
166
        if ($old_checkout) {
167
            $issue_id   = $old_checkout->issue_id;
168
            $branchcode = $old_checkout->branchcode;
169
        } else {
170
171
            # Try current issues table (unlikely but possible)
172
            my $current_issue = Koha::Checkouts->search(
173
                {
174
                    itemnumber     => $itemnumber,
175
                    borrowernumber => $borrowernumber,
176
                }
177
            )->next;
178
179
            if ($current_issue) {
180
                $issue_id   = $current_issue->issue_id;
181
                $branchcode = $current_issue->branchcode;
182
            }
183
        }
184
185
        # Build the JSON structure
186
        my $json_data = {
187
            issue         => $issue_id,
188
            branchcode    => $branchcode,
189
            itemnumber    => $itemnumber,
190
            confirmations => [],
191
            forced        => []
192
        };
193
194
        my $json_info = encode_json($json_data);
195
196
        # Make it pretty like the code does
197
        $json_data = decode_json($json_info);
198
        $json_info = JSON->new->pretty(1)->canonical(1)->encode($json_data);
199
200
        if ($verbose) {
201
            print "Converting action_id $action_id:\n";
202
            print "  Old: $info\n";
203
            print "  New: " .        ( $json_info =~ s/\n/ /gr ) . "\n";
204
            print "  issue_id: " .   ( $issue_id   // 'NULL' ) . "\n";
205
            print "  branchcode: " . ( $branchcode // 'NULL' ) . "\n";
206
            print "\n";
207
        }
208
209
        if ($commit) {
210
            my $update_sql = q(
211
                UPDATE action_logs
212
                SET info = ?
213
                WHERE action_id = ?
214
            );
215
            my $update_sth = $dbh->prepare($update_sql);
216
            if ( $update_sth->execute( $json_info, $action_id ) ) {
217
                $converted++;
218
            } else {
219
                print STDERR "ERROR: Failed to update action_id $action_id: " . $dbh->errstr . "\n";
220
                $errors++;
221
            }
222
        } else {
223
            $converted++;
224
        }
225
226
        $batch_count++;
227
    }
228
229
    $offset += $batch_size;
230
231
    unless ($verbose) {
232
        print "Processed $offset / $total_count records...\r";
233
    }
234
}
235
236
print "\n";
237
print "Migration complete!\n";
238
print "  Converted: $converted\n";
239
print "  Errors: $errors\n";
240
print "\n";
241
242
if ( !$commit && $converted > 0 ) {
243
    print "This was a DRY RUN. Use --commit to actually update the database.\n";
244
}
245
246
=head1 AUTHOR
247
248
Koha Development Team
249
250
=cut

Return to bug 41358