Attachment 89467 Details for Bug 22563 – Bug 22563: (follow-up) Clean up remaing L, LR and CR cases in the database

[patch] Bug 22563: (follow-up) Clean up remaing L, LR and CR cases in the database

Bug-22563-follow-up-Clean-up-remaing-L-LR-and-CR-c.patch (text/plain), 3.62 KB, created by Martin Renvoize (ashimema) on 2019-05-08 11:31:01 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2019-05-08 11:31:01 UTC

Size: 3.62 KB

patch

obsolete

>From 5eed5bfa5f8727cfd4c24efa2a77d197b527de25 Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>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 <martin.renvoize@ptfs-europe.com>
>---
> .../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

Actions: View | Diff | Splinter Review

Attachments on bug 22563: 86913 | 86914 | 87092 | 87093 | 87094 | 87637 | 87638 | 87639 | 87640 | 87641 | 87642 | 87801 | 87802 | 87803 | 87804 | 87805 | 87806 | 88279 | 88280 | 88281 | 88282 | 88283 | 88284 | 88285 | 88323 | 88324 | 88325 | 88326 | 88327 | 88328 | 88720 | 88721 | 88722 | 88723 | 88724 | 88725 | 89462 | 89463 | 89464 | 89465 | 89466 | 89467 | 90221 | 90222 | 90223 | 90224 | 90225 | 90226 | 90227 | 90307 | 90308 | 90309 | 90310 | 90311 | 90312 | 90330 | 90331 | 90332 | 90333 | 90334 | 90335 | 90632 | 90637 | 90667 | 91338 | 91339 | 91340 | 91341 | 91342 | 91343 | 91344 | 91345 | 91346 | 91347 | 91348