Implement select study

Implement the select study UI

1 files changed, 35 insertions, 1 deletions

diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py
index ea47806..e506175 100644
--- a/qc_app/dbinsert.py
+++ b/qc_app/dbinsert.py
@@ -53,6 +53,23 @@ def genechips():
 
     return {}
 
+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:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            query = (
+                "SELECT Species.SpeciesId, ProbeFreeze.* "
+                "FROM Species INNER JOIN InbredSet "
+                "ON Species.SpeciesId=InbredSet.SpeciesId "
+                "INNER JOIN ProbeFreeze "
+                "ON InbredSet.InbredSetId=ProbeFreeze.InbredSetId "
+                "WHERE Species.SpeciesId = %s "
+                "AND ProbeFreeze.ChipId = %s")
+            cursor.execute(query, (speciesid, genechipid))
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
 @dbinsertbp.route("/platform", methods=["POST"])
 def select_platform():
     "Select the platform (GeneChipId) used for the data."
@@ -76,7 +93,24 @@ def select_platform():
 
 @dbinsertbp.route("/study", methods=["POST"])
 def select_study():
-    return "Not implemented yet"
+    "View to select/create the study (ProbeFreeze) associated with the data."
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+
+        speciesid = form["species"]
+        genechipid = form["genechipid"]
+
+        the_studies = studies_by_species_and_platform(speciesid, genechipid)
+        return render_template(
+            "select_study.html", filename=form["filename"],
+            filetype=form["filetype"], species=speciesid, genechipid=genechipid,
+            studies=the_studies)
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")
 
 @dbinsertbp.route("/select-dataset", methods=["POST"])
 def select_dataset():