Implement confirmation stage

Provide user with a confirmation stage where they can verify all the
data before inserting into the database.

2 files changed, 124 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]}")
diff --git a/qc_app/templates/final_confirmation.html b/qc_app/templates/final_confirmation.html
new file mode 100644
index 0000000..f2de028
--- /dev/null
+++ b/qc_app/templates/final_confirmation.html
@@ -0,0 +1,47 @@
+{%extends "base.html"%}
+{%from "dbupdate_hidden_fields.html" import hidden_fields%}
+
+{%block title%}Confirmation{%endblock%}
+
+{%macro display_item(item_name, item_data):%}
+<li>
+  <strong>{{item_name}}</strong>
+  {%if item_data%}
+  <ul>
+    {%for term,value in item_data.items():%}
+    <li><strong>{{term}}:</strong> {{value}}</li>
+    {%endfor%}
+  </ul>
+  {%endif%}
+</li>
+{%endmacro%}
+
+{%block contents%}
+<h2 class="heading">Final Confirmation</h2>
+
+<div  class="two-col-sep-col1">
+  <p><strong>Selected Data</strong></p>
+  <ul>
+    <li><strong>File</strong>
+      <ul>
+	<li><strong>Filename</strong>: {{filename}}</li>
+	<li><strong>File Type</strong>: {{filetype}}</li>
+      </ul>
+    </li>
+    {{display_item("Species", the_species)}}
+    {{display_item("Platform", platform)}}
+    {{display_item("Study", study)}}
+    {{display_item("Dataset", dataset)}}
+  </ul>
+</div>
+
+<form method="POST" action="#">
+  {{hidden_fields(
+  filename, filetype, species=species, genechipid=genechipid,
+  studyid=studyid,datasetid=datasetid)}}
+  <fieldset>
+    <input type="submit" class="btn btn-main" value="confirm" />
+  </fieldset>
+</form>
+</div>
+{%endblock%}