Enable creation of new dataset

Enable the user to create a new dataset should the need arise.

A few extra fixes were done, such as:

- Provide list of average methods to choose from
- Provide input elements for some expected fields
- Add a new confirmation step before doing the actual data update

1 files changed, 71 insertions, 22 deletions

diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py
index c106366..2b29749 100644
--- a/qc_app/dbinsert.py
+++ b/qc_app/dbinsert.py
@@ -1,8 +1,8 @@
 "Handle inserting data into the database"
 import os
 import json
-import datetime
 from functools import reduce
+from datetime import datetime
 
 import requests
 from redis import Redis
@@ -167,7 +167,7 @@ def create_study():
                     form["tissueid"], form["studyname"],
                     form.get("studyfullname", ""),
                     form.get("studyshortname", ""),
-                    datetime.datetime.now().date().strftime("%Y-%m-%d"),
+                    datetime.now().date().strftime("%Y-%m-%d"),
                     form["inbredsetid"])
                 query = (
                     "INSERT INTO ProbeFreeze() "
@@ -188,12 +188,34 @@ def datasets_by_study(studyid:int) -> tuple:
     with database_connection() as conn:
         with conn.cursor(cursorclass=DictCursor) as cursor:
             query = "SELECT * FROM ProbeSetFreeze WHERE ProbeFreezeId=%s"
-            print(f"QUERY: {query}\n\tPARAMS: ({studyid},)")
             cursor.execute(query, (studyid,))
             return tuple(cursor.fetchall())
 
     return tuple()
 
+def averaging_methods() -> tuple:
+    "Retrieve averaging methods from database"
+    with database_connection() as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute("SELECT * FROM AvgMethod")
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
+def dataset_datascales() -> tuple:
+    "Retrieve datascales from database"
+    with database_connection() as conn:
+        with conn.cursor() as cursor:
+            cursor.execute(
+                'SELECT DISTINCT DataScale FROM ProbeSetFreeze '
+                'WHERE DataScale IS NOT NULL AND DataScale != ""')
+            return tuple(
+                item for item in
+                (res[0].strip() for res in cursor.fetchall())
+                if (item is not None and item != ""))
+
+    return tuple()
+
 @dbinsertbp.route("/dataset", methods=["POST"])
 def select_dataset():
     "Select the dataset to add the file contents against"
@@ -210,27 +232,54 @@ def select_dataset():
         return render_template(
             "select_dataset.html", filename=form["filename"],
             filetype=form["filetype"], species=form["species"],
-            genechipid=form["genechipid"], studyid=studyid, datasets=datasets)
+            genechipid=form["genechipid"], studyid=studyid, datasets=datasets,
+            avgmethods=averaging_methods(), datascales=dataset_datascales())
     except AssertionError as aserr:
         return render_error(f"Missing data: {aserr.args[0]}")
 
+@dbinsertbp.route("/create-dataset", methods=["POST"])
+def create_dataset():
+    "Select the dataset to add the file contents against"
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyid"), "study"
+        assert form.get("avgid"), "averaging method"
+        assert form.get("datasetname2"), "Dataset Name 2"
+        assert form.get("datasetfullname"), "Dataset Full Name"
+        assert form.get("datasetshortname"), "Dataset Short Name"
+        assert form.get("datasetpublic"), "Dataset public specification"
+        assert form.get("datasetconfidentiality"), "Dataset confidentiality"
+        assert form.get("datasetdatascale"), "Dataset Datascale"
+
+        with database_connection() as conn:
+            with conn.cursor(cursorclass=DictCursor) as cursor:
+                cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeSetFreeze")
+                new_datasetid = cursor.fetchone()["last_id"] + 1
+                values = (
+                    new_datasetid, new_datasetid, form["avgid"],
+                    form.get("datasetname",""), form["datasetname2"],
+                    form["datasetfullname"], form["datasetshortname"],
+                    datetime.now().date().strftime("%Y-%m-%d"),
+                    form["datasetpublic"], form["datasetconfidentiality"],
+                    form["datasetdatascale"])
+                query = (
+                    "INSERT INTO ProbeSetFreeze VALUES"
+                    "(%s, %s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, NULL, %s)")
+                cursor.execute(query, values)
+                return render_template(
+                    "continue_from_create_dataset.html",
+                    filename=form["filename"], filetype=form["filetype"],
+                    species=form["species"], genechipid=form["genechipid"],
+                    studyid=form["studyid"], datasetid=new_datasetid)
+    except AssertionError as aserr:
+        flash(f"Missing data {aserr.args[0]}", "alert-error")
+        return redirect(url_for("dbinsert.select_dataset"), code=307)
 
-@dbinsertbp.route("/insert_data", methods=["POST"])
-def insert_data():
+@dbinsertbp.route("/final-confirmation", methods=["POST"])
+def final_confirmation():
     "Preview the data before triggering entry into the database"
-    form = request.form
-    filename = form["filename"]
-    filepath = f"{app.config['UPLOAD_FOLDER']}/{filename}"
-    if os.path.exists(filepath):
-        try:
-            species = form["species"]
-            filetype = form["filetype"]
-            datasetid = int(form["dataset"])
-            genechipid = int(form["genechipid"])
-            return (f"Would insert '{species}' data in '{filetype}' file "
-                    f"'{filepath}' into the database with the dataset "
-                    f"'{datasetid}' and genechip '{genechipid}'.")
-        except ValueError as verr:
-            msg = "::".join(verr.args)
-            return render_error(f"Invalid value: {msg}")
-    return render_error(f"File '{filename}' no longer exists.")
+    return str(request.form)