Provide selection for GeneChips

The GeneChipId value is required for the data being inserted, so this
commit provides the UI to enable selection of the chip.

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>