diff options
| -rw-r--r-- | qc_app/dbinsert.py | 78 | ||||
| -rw-r--r-- | qc_app/templates/final_confirmation.html | 47 |
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%} |