Move sql for modifying case-attributes from gn2 to gn3

2 files changed, 133 insertions, 9 deletions

diff --git a/gn3/db/case_attributes.py b/gn3/db/case_attributes.py
new file mode 100644
index 0000000..8eb2bf6
--- /dev/null
+++ b/gn3/db/case_attributes.py
@@ -0,0 +1,132 @@
+"""Module that contains functions for editing case-attribute data"""
+from typing import Any, Optional, Tuple
+
+import json
+import MySQLdb
+
+
+def get_case_attributes(conn) -> Optional[Tuple]:
+    """Get all the case attributes from the database."""
+    with conn.cursor() as cursor:
+        cursor.execute("SELECT Id, Name, Description FROM CaseAttribute")
+        return cursor.fetchall()
+
+
+def get_unreviewed_diffs(conn: Any) -> Optional[tuple]:
+    """Fetch all case attributes in GN"""
+    with conn.cursor() as cursor:
+        cursor.execute(
+            "SELECT id, editor, json_diff_data FROM "
+            "caseattributes_audit WHERE status = 'review'"
+        )
+        return cursor.fetchall()
+
+
+def insert_case_attribute_audit(
+    conn: Any, status: str, author: str, data: str
+) -> int:
+    """Update the case_attribute_audit table"""
+    rowcount = 0
+    try:
+        with conn.cursor() as cursor:
+            cursor.execute(
+                "INSERT INTO caseattributes_audit "
+                "(status, editor, json_diff_data) "
+                "VALUES (%s, %s, %s)",
+                (status, author, data),
+            )
+            rowcount = cursor.rowcount
+            conn.commit()
+    except Exception as _e:
+        conn.rollback()
+        raise MySQLdb.Error(_e) from _e
+    return rowcount
+
+
+def reject_case_attribute(conn: Any, case_attr_audit_id: int) -> int:
+    """Given the id of the json_diff in the case_attribute_audit table, reject
+    it"""
+    rowcount = 0
+    try:
+        with conn.cursor() as cursor:
+            cursor.execute(
+                "UPDATE caseattributes_audit SET "
+                "status = 'rejected' WHERE id = %s",
+                (case_attr_audit_id,),
+            )
+            rowcount = cursor.rowcount
+            conn.commit()
+    except Exception as _e:
+        conn.rollback()
+        raise MySQLdb.Error(_e) from _e
+    return rowcount
+
+
+def approve_case_attribute(conn: Any, case_attr_audit_id: int) -> int:
+    """Given the id of the json_diff in the case_attribute_audit table,
+    approve it
+
+    """
+    rowcount = 0
+    try:
+        with conn.cursor() as cursor:
+            cursor.execute(
+                "SELECT json_diff_data FROM caseattributes_audit "
+                "WHERE id = %s",
+                (case_attr_audit_id,),
+            )
+            diff_data = cursor.fetchone()
+            if diff_data:
+                diff_data = json.loads(diff_data[0])
+                # Insert (Most Important)
+                if diff_data.get("Insert"):
+                    data = diff_data.get("Insert")
+                    cursor.execute(
+                        "INSERT INTO CaseAttribute "
+                        "(Name, Description) VALUES "
+                        "(%s, %s)",
+                        (
+                            data.get("name").strip(),
+                            data.get("description").strip(),
+                        ),
+                    )
+                # Delete
+                elif diff_data.get("Deletion"):
+                    data = diff_data.get("Deletion")
+                    cursor.execute(
+                        "DELETE FROM CaseAttribute " "WHERE Id = %s",
+                        (data.get("id"),),
+                    )
+                # Modification
+                elif diff_data.get("Modification"):
+                    data = diff_data.get("Modification")
+                    if desc_ := data.get("description"):
+                        cursor.execute(
+                            "UPDATE CaseAttribute SET "
+                            "Description = %s WHERE Id = %s",
+                            (
+                                desc_.get("Current"),
+                                diff_data.get("id"),
+                            ),
+                        )
+                    if name_ := data.get("name"):
+                        cursor.execute(
+                            "UPDATE CaseAttribute SET "
+                            "Name = %s WHERE Id = %s",
+                            (
+                                name_.get("Current"),
+                                diff_data.get("id"),
+                            ),
+                        )
+                if cursor.rowcount:
+                    cursor.execute(
+                        "UPDATE caseattributes_audit SET "
+                        "status = 'approved' WHERE id = %s",
+                        (case_attr_audit_id,),
+                    )
+            rowcount = cursor.rowcount
+            conn.commit()
+    except Exception as _e:
+        conn.rollback()
+        raise MySQLdb.Error(_e) from _e
+    return rowcount
diff --git a/gn3/db/sample_data.py b/gn3/db/sample_data.py
index f79ce97..d29f6c8 100644
--- a/gn3/db/sample_data.py
+++ b/gn3/db/sample_data.py
@@ -1,8 +1,7 @@
 """Module containing functions that work with sample data"""
-from typing import Any, Tuple, Dict, Callable, Optional
+from typing import Any, Tuple, Dict, Callable
 
 import re
-import collections
 import MySQLdb
 
 from gn3.csvcmp import extract_strain_name
@@ -423,10 +422,3 @@ def insert_sample_data(
     except Exception as _e:
         conn.rollback()
         raise MySQLdb.Error(_e) from _e
-
-
-def get_case_attributes(conn) -> Optional[Tuple]:
-    """Get all the case attributes from the database."""
-    with conn.cursor() as cursor:
-        cursor.execute("SELECT Id, Name, Description FROM CaseAttribute")
-        return cursor.fetchall()