From 9ade3993d5c19a9c169581aec3c17934a2227d14 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Mon, 16 Dec 2019 17:20:31 +0100
Subject: [PATCH] Bug 24161: DB changes

Sponsored-by: Cork Institute of Technology
---
 installer/data/mysql/atomicupdate/bug_24161.perl | 36 ++++++++++++++++++++++++
 installer/data/mysql/kohastructure.sql           | 14 +++++++--
 2 files changed, 48 insertions(+), 2 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_24161.perl

diff --git a/installer/data/mysql/atomicupdate/bug_24161.perl b/installer/data/mysql/atomicupdate/bug_24161.perl
new file mode 100644
index 0000000000..16666759e1
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_24161.perl
@@ -0,0 +1,36 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+    unless ( TableExists( 'aqorders_claims' ) ) {
+        $dbh->do(q|
+            CREATE TABLE aqorders_claims (
+                id int(11) AUTO_INCREMENT,
+                ordernumber INT(11) NOT NULL,
+                claimed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                PRIMARY KEY (id),
+                CONSTRAINT aqorders_claims_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE
+            ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
+        |);
+
+        my $orders = $dbh->selectall_arrayref(q|
+            SELECT ordernumber, claims_count, claimed_date
+            FROM aqorders
+            WHERE claims_count > 0
+        |, { Slice => {} });
+        my $insert_claim_sth = $dbh->prepare(q|
+            INSERT INTO aqorders_claims (ordernumber, claimed_on)
+            VALUES (?,?)
+        |);
+
+        for my $order ( @$orders ) {
+            for my $claim (1..$order->{claims_count}) {
+                $insert_claim_sth->execute($order->{ordernumber}, $order->{claimed_on});
+            }
+        }
+
+        $dbh->do(q|ALTER TABLE aqorders DROP COLUMN claims_count, DROP COLUMN claimed_date|);
+    }
+
+    # Always end with this (adjust the bug info)
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug 24161 - Add new join table aqorders_claims to keep track of claims)\n";
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a82a20830b..c447af24ed 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3174,8 +3174,6 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items
   `sort1_authcat` varchar(10) default NULL,
   `sort2_authcat` varchar(10) default NULL,
   `uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no)
-  `claims_count` int(11) default 0, -- count of claim letters generated
-  `claimed_date` date default NULL, -- last date a claim was generated
   `subscriptionid` int(11) default NULL, -- links this order line to a subscription (subscription.subscriptionid)
   parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent
   `orderstatus` varchar(16) default 'new', -- the current status for this line item. Can be 'new', 'ordered', 'partial', 'complete' or 'cancelled'
@@ -3240,6 +3238,18 @@ CREATE TABLE aqorders_transfers (
   CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
+--
+-- Table structure for table aqorders_claims
+--
+
+DROP TABLE IF EXISTS aqorders_claims;
+CREATE TABLE aqorders_claims (
+    id int(11) AUTO_INCREMENT,
+    ordernumber INT(11) NOT NULL,
+    claimed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (id),
+    CONSTRAINT aqorders_claims_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
 
 --
 -- Table structure for table `transport_cost`
-- 
2.11.0