diff options
| -rw-r--r-- | qc_app/dbinsert.py | 93 | ||||
| -rw-r--r-- | qc_app/static/css/styles.css | 2 | ||||
| -rw-r--r-- | qc_app/templates/continue_from_create_dataset.html | 56 | ||||
| -rw-r--r-- | qc_app/templates/dbupdate_hidden_fields.html | 3 | ||||
| -rw-r--r-- | qc_app/templates/select_dataset.html | 82 |
5 files changed, 199 insertions, 37 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) diff --git a/qc_app/static/css/styles.css b/qc_app/static/css/styles.css index a42ffb0..5340701 100644 --- a/qc_app/static/css/styles.css +++ b/qc_app/static/css/styles.css @@ -107,7 +107,7 @@ table th,td { fieldset { border-style: none; display: grid; - grid-template-columns: 1fr 9fr; + grid-template-columns: 2fr 8fr; column-gap: 5px; } diff --git a/qc_app/templates/continue_from_create_dataset.html b/qc_app/templates/continue_from_create_dataset.html new file mode 100644 index 0000000..1e493c5 --- /dev/null +++ b/qc_app/templates/continue_from_create_dataset.html @@ -0,0 +1,56 @@ +{%extends "base.html"%} +{%from "dbupdate_hidden_fields.html" import hidden_fields%} + +{%block title%}Create Study{%endblock%} + +{%block css%} +<link rel="stylesheet" href="/static/css/two-column-with-separator.css" /> +{%endblock%} + +{%block contents%} +<h2 class="heading">{{filename}}: create study</h2> + +{%with messages = get_flashed_messages(with_categories=true)%} +{%if messages:%} +<ul> + {%for category, message in messages:%} + <li class="{{category}}">{{message}}</li> + {%endfor%} +</ul> +{%endif%} +{%endwith%} + +<div class="two-column-with-separator"> + <form method="POST" action="{{url_for('dbinsert.final_confirmation')}}" + id="select-platform-form" data-genechips="{{genechips_data}}" + class="two-col-sep-col1"> + <legend>continue with new dataset</legend> + {{hidden_fields( + filename, filetype, species=species, genechipid=genechipid, + studyid=studyid, datasetid=datasetid)}} + + <fieldset> + <input type="submit" + value="continue" + class="btn btn-main form-col-2" /> + </fieldset> + </form> + + <p class="two-col-sep-separator">OR</p> + + <form method="POST" action="{{url_for('dbinsert.select_dataset')}}" + id="select-platform-form" data-genechips="{{genechips_data}}" + class="two-col-sep-col2"> + <legend>Select from existing dataset</legend> + {{hidden_fields( + filename, filetype, species=species, genechipid=genechipid, + studyid=studyid, datasetid=datasetid)}} + + <fieldset> + <input type="submit" + value="go back" + class="btn btn-main form-col-2" /> + </fieldset> + </form> +</div> +{%endblock%} diff --git a/qc_app/templates/dbupdate_hidden_fields.html b/qc_app/templates/dbupdate_hidden_fields.html index c66ebba..5a95cbb 100644 --- a/qc_app/templates/dbupdate_hidden_fields.html +++ b/qc_app/templates/dbupdate_hidden_fields.html @@ -19,5 +19,8 @@ {%if kwargs.get("studyid"):%} <input type="hidden" name="studyid" value="{{kwargs['studyid']}}" /> {%endif%} +{%if kwargs.get("datasetid"):%} +<input type="hidden" name="datasetid" value="{{kwargs['datasetid']}}" /> +{%endif%} {%endmacro%} diff --git a/qc_app/templates/select_dataset.html b/qc_app/templates/select_dataset.html index 59ead59..a16fc75 100644 --- a/qc_app/templates/select_dataset.html +++ b/qc_app/templates/select_dataset.html @@ -8,10 +8,10 @@ {%endblock%} {%block contents%} -<h1 class="heading">{{filename}}: select dataset</h2> +<h2 class="heading">{{filename}}: select dataset</h2> <div class="two-column-with-separator"> - <form method="POST" action="{{url_for('dbinsert.insert_data')}}" + <form method="POST" action="{{url_for('dbinsert.final_confirmation')}}" id="select-dataset-form" class="two-col-sep-col1"> <legend>choose existing dataset</legend> {{hidden_fields( @@ -19,8 +19,8 @@ studyid=studyid)}} <fieldset> - <label for="dataset" class="form-col-1">dataset:</label> - <select id="dataset" name="dataset" class="form-col-2" + <label for="datasetid" class="form-col-1">dataset:</label> + <select id="datasetid" name="datasetid" class="form-col-2" {%if datasets | length == 0:%} disabled="disabled" {%endif%}> @@ -44,37 +44,91 @@ <p class="two-col-sep-separator">OR</p> <form method="POST" id="create-dataset-form" + action="{{url_for('dbinsert.create_dataset')}}" class="two-col-sep-col2"> <legend>create new dataset</legend> {{hidden_fields( filename, filetype, species=species, genechipid=genechipid, studyid=studyid)}} + {%with messages = get_flashed_messages(with_categories=true)%} + {%if messages:%} + <ul> + {%for category, message in messages:%} + <li class="{{category}}">{{message}}</li> + {%endfor%} + </ul> + {%endif%} + {%endwith%} + <fieldset> - <label for="avgid">average:</label> - <select id="avgid" name="avgid" required="required"> + <label for="avgid" class="form-col-1">average:</label> + <select id="avgid" name="avgid" required="required" class="form-col-2"> <option value="">Select averaging method</option> + {%for method in avgmethods:%} + <option value="{{method['AvgMethodId']}}"> + {{method["Name"]}} + </option> + {%endfor%} </select> </fieldset> <fieldset> - <label for="datasetname">name:</label> - <input id="datasetname" name="datasetname" type="text" /> + <label for="datasetname" class="form-col-1">name:</label> + <input id="datasetname" name="datasetname" type="text" + class="form-col-2" /> + </fieldset> + + <fieldset> + <label for="datasetname2" class="form-col-1">name 2:</label> + <input id="datasetname2" name="datasetname2" type="text" + required="required" class="form-col-2" /> + </fieldset> + + <fieldset> + <label for="datasetfullname" class="form-col-1">full name:</label> + <input id="datasetfullname" name="datasetfullname" type="text" + required="required" class="form-col-2" /> </fieldset> <fieldset> - <label for="datasetname2">name 2:</label> - <input id="datasetname2" name="datasetname2" type="text" /> + <label for="datasetshortname" class="form-col-1">short name:</label> + <input id="datasetshortname" name="datasetshortname" type="text" + required="required" class="form-col-2" /> </fieldset> <fieldset> - <label for="datasetfullname">full name:</label> - <input id="datasetfullname" name="datasetfullname" type="text" /> + <label for="datasetpublic" class="form-col-1">public:</label> + <input id="datasetpublic" name="datasetpublic" type="number" + required="required" min="0" max="2" value="0" + class="form-col-2" /> + </fieldset> + + <fieldset> + <label for="datasetconfidentiality">confidentiality:</label> + <input id="datasetconfidentiality" name="datasetconfidentiality" + type="number" required="required" min="0" max="2" value="0" + class="form-col-2" /> + </fieldset> + + <fieldset> + <label for="datasetdatascale" class="form-col-1">data scale:</label> + <select id="datasetdatascale" name="datasetdatascale" class="form-col-2"> + <option value="">None</option> + {%for dscale in datascales:%} + <option value="{{dscale}}" + {%if dscale == "log2":%} + selected="selected" + {%endif%}> + {{dscale}} + </option> + {%endfor%} + </select> </fieldset> <fieldset> - <label for="datasetshortname">full name:</label> - <input id="datasetshortname" name="datasetshortname" type="text" /> + <input type="submit" value="create new dataset" + class="btn btn-main form-col-2" /> </fieldset> </form> |