diff options
| -rw-r--r-- | etc/default_config.py | 2 | ||||
| -rw-r--r-- | manifest.scm | 3 | ||||
| -rw-r--r-- | qc_app/dbinsert.py | 46 | ||||
| -rw-r--r-- | qc_app/static/css/styles.css | 27 | ||||
| -rw-r--r-- | qc_app/static/js/dbinsert.js | 55 | ||||
| -rw-r--r-- | qc_app/templates/select_dataset.html | 40 |
6 files changed, 153 insertions, 20 deletions
diff --git a/etc/default_config.py b/etc/default_config.py index fcb17d9..5bafd1a 100644 --- a/etc/default_config.py +++ b/etc/default_config.py @@ -11,3 +11,5 @@ UPLOAD_FOLDER = "/tmp/qc_app_files" REDIS_URL = "redis://" JOBS_TTL_SECONDS = 1209600 # 14 days GN3_URL="http://localhost:8080" +SQL_URI = os.getenv( + "SQL_URI", "mysql://gn2:mysql_password@localhost/db_webqtl_s") diff --git a/manifest.scm b/manifest.scm index 407834a..d18be5b 100644 --- a/manifest.scm +++ b/manifest.scm @@ -8,4 +8,5 @@ "python-pylint" "python-pytest" "python-hypothesis" - "python-jsonpickle")) + "python-jsonpickle" + "python-mysqlclient")) diff --git a/qc_app/dbinsert.py b/qc_app/dbinsert.py index 0733e5f..5381fb5 100644 --- a/qc_app/dbinsert.py +++ b/qc_app/dbinsert.py @@ -5,9 +5,11 @@ from functools import reduce import requests from redis import Redis +from MySQLdb.cursors import DictCursor from flask import request, Blueprint, render_template, current_app as app from . import jobs +from .db_utils import database_connection dbinsertbp = Blueprint("dbinsert", __name__) @@ -25,6 +27,24 @@ def make_menu_items_grouper(grouping_fn=lambda item: item): return {**acc, grouping: (acc[grouping] + (row_values,))} return __grouper__ +def genechips(): + "Retrieve the genechip information from the database" + def __organise_by_species__(acc, chip): + species = chip["species_name"] + if acc.get(species) is None: + return {**acc, species: (chip,)} + return {**acc, species: acc[species] + (chip,)} + + with database_connection() as conn: + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute( + "SELECT GeneChip.*, LOWER(Species.Name) AS species_name " + "FROM GeneChip INNER JOIN Species " + "ON GeneChip.SpeciesId=Species.SpeciesId") + return reduce(__organise_by_species__, cursor.fetchall(), {}) + + return {} + @dbinsertbp.route("/select-dataset", methods=["POST"]) def select_dataset(): "Select the dataset to add the file contents against" @@ -52,12 +72,34 @@ def select_dataset(): "datasets"][default_species][default_group][ default_type] + gchips = genechips() + return render_template( - "select_dataset.html", job_id=job_id, job_name=filename, + "select_dataset.html", filename=filename, species=menu_contents["species"], default_species=default_species, groups=mouse_groups, types=group_types, datasets=datasets, - menu_contents=json.dumps(menu_contents)) + menu_contents=json.dumps(menu_contents), + genechips_data=json.dumps(gchips), + genechips=gchips.get(default_species, [])) return render_error(f"File '{filename}' no longer exists.") return render_error(f"Job '{job_id}' no longer exists.") return render_error("Unknown error") + + +@dbinsertbp.route("/insert_data", methods=["POST"]) +def insert_data(): + "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: + dataset_id = int(form["dataset"]) + return (f"Would insert data in file '{filepath}' " + "into the database with the dataset " + f"{dataset_id}") + except ValueError as ve: + msg = "::".join(ve.args) + return render_error(f"Invalid value: {msg}") + return render_error(f"File '{filename}' no longer exists.") diff --git a/qc_app/static/css/styles.css b/qc_app/static/css/styles.css index 4d0fa8c..922924c 100644 --- a/qc_app/static/css/styles.css +++ b/qc_app/static/css/styles.css @@ -1,3 +1,7 @@ +* { + box-sizing: border-box; +} + label, legend { text-transform: capitalize; } @@ -57,14 +61,11 @@ fieldset { table { border-collapse: collapse; -} - -.reports-table { border: 1px solid; border-color: #336699; } -.reports-table thead { +table thead { color: #FEFEFE; background-color: #336699; border-width: 0 1px 0 1px; @@ -72,12 +73,12 @@ table { border-color: #336699; } -.reports-table thead tr th { +table thead tr th { text-transform: capitalize; } -.reports-table th,td { - border-width: 0 1px 0 1px; +table th,td { + border-width: 0 1px 1px 1px; border-style: solid; border-color: #336699; padding: 0 0.3em 0.3em 0.3em; @@ -102,10 +103,6 @@ table { border-radius: 1em; } -form { - width: 30%; -} - fieldset { border-style: none; display: grid; @@ -117,6 +114,12 @@ label { grid-column: 1 / 2; } -input,select,button { +fieldset input,select,button { grid-column: 2 / 3; + width: 20%; +} + +input[disabled="true"],input[disabled="disabled"] { + border-color: #878787; + background-color: #A9A9A9; } diff --git a/qc_app/static/js/dbinsert.js b/qc_app/static/js/dbinsert.js index 3c0be54..63d2c86 100644 --- a/qc_app/static/js/dbinsert.js +++ b/qc_app/static/js/dbinsert.js @@ -62,7 +62,6 @@ function setup_types(type_data) { } function setup_datasets(dataset_data) { - console.info("DATASET DATA:", dataset_data); elt = document.getElementById("dataset"); remove_children(elt); dataset_data.forEach(dataset => { @@ -75,14 +74,65 @@ function setup_datasets(dataset_data) { trigger_change_event(elt); } +function radio_column(chip) { + col = document.createElement("td"); + radio = document.createElement("input"); + radio.setAttribute("type", "radio"); + radio.setAttribute("name", "genechipid"); + radio.setAttribute("value", chip["GeneChipId"]); + radio.setAttribute("required", "required"); + col.appendChild(radio); + return col; +} + +function setup_genechips(genechip_data) { + columns = ["GeneChipName", "Name", "GeoPlatform", "GO_tree_value"] + submit_button = document.querySelector( + "#select-dataset-form input[type='submit']"); + elt = document.getElementById( + "genechips-table").getElementsByTagName("tbody")[0]; + remove_children(elt); + if((genechip_data === undefined) || genechip_data.length === 0) { + row = document.createElement("tr"); + col = document.createElement("td"); + col.setAttribute("colspan", "5"); + text = document.createTextNode("No chips found for selected species"); + col.appendChild(text); + row.appendChild(col); + elt.appendChild(row); + submit_button.setAttribute("disabled", true); + return false; + } + + submit_button.removeAttribute("disabled") + genechip_data.forEach(chip => { + row = document.createElement("tr"); + row.appendChild(radio_column(chip)); + columns.forEach(column => { + col = document.createElement("td"); + content = document.createTextNode(chip[column]); + col.appendChild(content); + row.appendChild(col); + }); + elt.appendChild(row); + }); +} + function menu_contents() { return JSON.parse( - document.getElementsByTagName("form")[0].getAttribute( + document.getElementById("select-dataset-form").getAttribute( "data-menu-content")); } +function genechips() { + return JSON.parse( + document.getElementById("select-dataset-form").getAttribute( + "data-genechips")); +} + function update_menu(event) { menu = menu_contents(); + genec = genechips(); species_elt = document.getElementById("species"); group_elt = document.getElementById("group"); @@ -91,6 +141,7 @@ function update_menu(event) { if(event.target == species_elt) { setup_groups(menu["groups"][species_elt.value]); + setup_genechips(genec[species_elt.value.toLowerCase()]); } if(event.target == group_elt) { diff --git a/qc_app/templates/select_dataset.html b/qc_app/templates/select_dataset.html index 5fefccc..db48694 100644 --- a/qc_app/templates/select_dataset.html +++ b/qc_app/templates/select_dataset.html @@ -3,10 +3,13 @@ {%block title%}Select Dataset{%endblock%} {%block contents%} -<h1 class="heading">{{job_name}}: select dataset</h2> +<h1 class="heading">{{filename}}: select dataset</h2> -<form method="POST" data-menu-content="{{menu_contents}}"> - <input type="hidden" name="job_id" value="{{job_id}}" /> +<form method="POST" action="{{url_for('dbinsert.insert_data')}}" + id="select-dataset-form" + data-menu-content="{{menu_contents}}" + data-genechips="{{genechips_data}}"> + <input type="hidden" name="filename" value="{{filename}}" /> <fieldset> <label for="species">species:</label> @@ -57,6 +60,37 @@ </select> </fieldset> + <table id="genechips-table"> + <thead> + <tr> + <th>Select</th> + <th>GeneChip Name</th> + <th>Name</th> + <th>GeoPlatform</th> + <th>GO Tree Value</th> + </tr> + </thead> + + <tbody> + {%for chip in genechips:%} + <tr> + <td> + <input type="radio" name="genechipid" value="{{chip['GeneChipId']}}" + required="required" /> + </td> + <td>{{chip["GeneChipName"]}}</td> + <td>{{chip["Name"]}}</td> + <td>{{chip["GeoPlatform"]}}</td> + <td>{{chip["GO_tree_value"]}}</td> + </tr> + {%else%} + <tr> + <td colspan="5">No chips found for selected species</td> + </tr> + {%endfor%} + </tbody> + </table> + <fieldset> <input type="submit" class="btn btn-main" value="update database" /> </fieldset> |