From 38c44a495851ea5f483683796b2c26eec66639eb Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 18 Apr 2019 12:53:42 +0100 Subject: [PATCH] Bug 22563: (follow-up) Clean up remaing L, LR and CR cases in the database Test plan: 1) Confirm that after the DB update there are no L, LR or CR accountlines present. 2) Read the db update and confirm is makes sense. Signed-off-by: Martin Renvoize --- .../data/mysql/atomicupdate/bug_22563.perl | 70 +++++++++++++++++++ 1 file changed, 70 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_22563.perl diff --git a/installer/data/mysql/atomicupdate/bug_22563.perl b/installer/data/mysql/atomicupdate/bug_22563.perl new file mode 100644 index 0000000000..1c27e04676 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22563.perl @@ -0,0 +1,70 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + # Find and correct pathological cases of LR becoming a credit + my $sth = $dbh->prepare( "SELECT accountlines_id, issue_id, borrowernumber, itemnumber, amount, manager_id FROM accountlines WHERE accounttype = 'LR' AND amount < 0" ); + $sth->execute(); + while ( my $row = $sth->fetchrow_hashref ) { + $dbh->do( + "INSERT INTO accountlines (accounttype, issue_id, borrowernumber, itemnumber, amount, manager_id) VALUES ( ?, ?, ?, ?, ?, ? );", + {}, + ( + 'CR', $row->{issue_id}, + $row->{borrowernumber}, $row->{itemnumber}, + $row->{amount}, $row->{manager_id} + ) + ); + my $credit_id = $dbh->last_insert_id(); + my $amount = $row->{amount} * -1; + $dbh->do("INSERT INTO account_offsets (credit_id, debit_id, type, amount) VALUES (?,?,?,?);",{},($credit_id, $row->{accountlines_id}, 'Lost Item', $amount)); + $dbh->do("UPDATE accountlines SET amount = '$amount' WHERE accountlines_id = '$row->{accountlines_id}';"); + } + + $dbh->do(qq{ + UPDATE + accountlines + SET + accounttype = 'LOST', + status = 'RETURNED' + WHERE + accounttype = 'LR'; + }); + + # Find and correct pathalogical cases of L having been converted to W + $sth = $dbh->prepare( "SELECT accountlines_id, issue_id, borrowernumber, itemnumber, amount, manager_id FROM accountlines WHERE accounttype = 'W' AND itemnumber IS NOT NULL" ); + $sth->execute(); + while ( my $row = $sth->fetchrow_hashref ) { + my $amount = $row->{amount} * -1; + $dbh->do( + "INSERT INTO accountlines (accounttype, issue_id, borrowernumber, itemnumber, amount, manager_id) VALUES ( ?, ?, ?, ?, ?, ? );", + {}, + ( + 'LOST', $row->{issue_id}, $row->{borrowernumber}, + $row->{itemnumber}, $amount, $row->{manager_id} + ) + ); + my $debit_id = $dbh->last_insert_id(); + $dbh->do("INSERT INTO account_offsets (credit_id, debit_id, type, amount) VALUES (?,?,?,?);",{},($row->{accountlines_id}, $debit_id, 'Lost Item Returned', $amount)); + } + + $dbh->do(qq{ + UPDATE + accountlines + SET + accounttype = 'LOST', + WHERE + accounttype = 'L'; + }); + + $dbh->do(qq{ + UPDATE + accountlines + SET + accounttype = 'LOST_RETURNED', + WHERE + accounttype = 'CR'; + }); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 22563 - Fix accounttypes for 'L', 'LR' and 'CR')\n"; +} -- 2.20.1