1 files changed, 77 insertions, 1 deletions
diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py
index 2b29749..eee7a56 100644
--- a/qc_app/dbinsert.py
+++ b/qc_app/dbinsert.py
@@ -1,6 +1,7 @@
 "Handle inserting data into the database"
 import os
 import json
+from typing import Union
 from functools import reduce
 from datetime import datetime
 
@@ -41,6 +42,18 @@ def species() -> tuple:
 
     return tuple()
 
+def species_by_id(speciesid) -> Union[dict, None]:
+    "Retrieve the species from the database by id."
+    with database_connection() as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                (
+                    "SELECT "
+                    "SpeciesId, SpeciesName, LOWER(Name) AS Name, MenuName "
+                    "FROM Species WHERE SpeciesId=%s"),
+                (speciesid,))
+            return cursor.fetchone()
+
 def genechips():
     "Retrieve the genechip information from the database"
     def __organise_by_species__(acc, chip):
@@ -56,6 +69,15 @@ def genechips():
 
     return {}
 
+def platform_by_id(genechipid:int) -> Union[dict, None]:
+    "Retrieve the gene platform by id"
+    with database_connection() as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                "SELECT * FROM GeneChip WHERE GeneChipId=%s",
+                (genechipid,))
+            return cursor.fetchone()
+
 def studies_by_species_and_platform(speciesid:int, genechipid:int) -> tuple:
     "Retrieve the studies by the related species and gene platform"
     with database_connection() as conn:
@@ -279,7 +301,61 @@ def create_dataset():
         flash(f"Missing data {aserr.args[0]}", "alert-error")
         return redirect(url_for("dbinsert.select_dataset"), code=307)
 
+def study_by_id(studyid:int) -> Union[dict, None]:
+    "Get a study by its Id"
+    with database_connection() as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                "SELECT * FROM ProbeFreeze WHERE ProbeFreezeId=%s",
+                (studyid,))
+            return cursor.fetchone()
+
+def dataset_by_id(datasetid:int) -> Union[dict, None]:
+    "Retrieve a dataset by its id"
+    with database_connection() as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                ("SELECT AvgMethod.Name AS AvgMethodName, ProbeSetFreeze.* "
+                 "FROM ProbeSetFreeze INNER JOIN AvgMethod "
+                 "ON ProbeSetFreeze.AvgId=AvgMethod.AvgMethodId "
+                 "WHERE ProbeSetFreeze.Id=%s"),
+                (datasetid,))
+            return cursor.fetchone()
+
+def selected_keys(original: dict, keys: tuple) -> dict:
+    "Return a new dict from the `original` dict with only `keys` present."
+    return {key: value for key,value in original.items() if key in keys}
+
 @dbinsertbp.route("/final-confirmation", methods=["POST"])
 def final_confirmation():
     "Preview the data before triggering entry into the database"
-    return str(request.form)
+    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("datasetid"), "dataset"
+
+        speciesid = form["species"]
+        genechipid = form["genechipid"]
+        studyid = form["studyid"]
+        datasetid=form["datasetid"]
+        return render_template(
+            "final_confirmation.html", filename=form["filename"],
+            filetype=form["filetype"], species=speciesid, genechipid=genechipid,
+            studyid=studyid, datasetid=datasetid,
+            the_species=selected_keys(
+                species_by_id(speciesid), ("SpeciesName", "Name", "MenuName")),
+            platform=selected_keys(
+                platform_by_id(genechipid),
+                ("GeneChipName", "Name", "GeoPlatform", "Title", "GO_tree_value")),
+            study=selected_keys(
+                study_by_id(studyid), ("Name", "FullName", "ShortName")),
+            dataset=selected_keys(
+                dataset_by_id(datasetid),
+                ("AvgMethodName", "Name", "Name2", "FullName", "ShortName",
+                 "DataScale")))
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")