Feature: Upload Samples/Cases

Implements the code enabling the upload of the samples/cases to the database.

8 files changed, 390 insertions, 7 deletions

diff --git a/qc_app/__init__.py b/qc_app/__init__.py
index 4810d45..f2001ab 100644
--- a/qc_app/__init__.py
+++ b/qc_app/__init__.py
@@ -6,6 +6,7 @@ from flask import Flask
 
 from .entry import entrybp
 from .parse import parsebp
+from .samples import samples
 from .dbinsert import dbinsertbp
 from .errors import register_error_handlers
 
@@ -30,6 +31,7 @@ def create_app(instance_dir):
     app.register_blueprint(entrybp, url_prefix="/")
     app.register_blueprint(parsebp, url_prefix="/parse")
     app.register_blueprint(dbinsertbp, url_prefix="/dbinsert")
+    app.register_blueprint(samples, url_prefix="/samples")
 
     register_error_handlers(app)
     return app
diff --git a/qc_app/db_utils.py b/qc_app/db_utils.py
index a04c5e1..75b6b73 100644
--- a/qc_app/db_utils.py
+++ b/qc_app/db_utils.py
@@ -2,7 +2,7 @@
 import logging
 import traceback
 import contextlib
-from typing import Tuple, Optional, Iterator
+from typing import Any, Tuple, Optional, Iterator, Callable
 
 from urllib.parse import urlparse
 import MySQLdb as mdb
@@ -32,3 +32,8 @@ def database_connection(db_url: Optional[str] = None) -> Iterator[mdb.Connection
         connection.rollback()
     finally:
         connection.close()
+
+def with_db_connection(func: Callable[[mdb.Connection], Any]) -> Any:
+    """Call `func` with a MySQDdb database connection."""
+    with database_connection(app.config["SQL_URI"]) as conn:
+        return func(conn)
diff --git a/qc_app/entry.py b/qc_app/entry.py
index abea5ed..bf78037 100644
--- a/qc_app/entry.py
+++ b/qc_app/entry.py
@@ -14,6 +14,8 @@ from flask import (
     render_template,
     current_app as app)
 
+from .dbinsert import species
+
 entrybp = Blueprint("entry", __name__)
 
 def errors(rqst) -> Tuple[str, ...]:
@@ -76,14 +78,14 @@ def zip_file_errors(filepath, upload_dir) -> Tuple[str, ...]:
 def upload_file():
     """Enables uploading the files"""
     if request.method == "GET":
-        return render_template("index.html")
+        return render_template("index.html", species = species())
 
     upload_dir = app.config["UPLOAD_FOLDER"]
     request_errors = errors(request)
     if request_errors:
         for error in request_errors:
             flash(error, "alert-error")
-        return render_template("index.html"), 400
+        return render_template("index.html", species = species()), 400
 
     filename = secure_filename(request.files["qc_text_file"].filename)
     if not os.path.exists(upload_dir):
@@ -96,7 +98,7 @@ def upload_file():
     if zip_errors:
         for error in zip_errors:
             flash(error, "alert-error")
-        return render_template("index.html"), 400
+        return render_template("index.html", species = species()), 400
 
     return redirect(url_for(
         "parse.parse", filename=filename,
diff --git a/qc_app/samples.py b/qc_app/samples.py
new file mode 100644
index 0000000..cc745ca
--- /dev/null
+++ b/qc_app/samples.py
@@ -0,0 +1,108 @@
+"""Code regarding samples"""
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+from flask import (
+    flash, request, url_for, redirect, Blueprint, render_template, current_app as app)
+
+from .db_utils import with_db_connection
+from .dbinsert import species_by_id, groups_by_species
+
+samples = Blueprint("samples", __name__)
+
+@samples.route("/upload/species", methods=["POST"])
+def select_species():
+    """Select the species."""
+    index_page = redirect(url_for("entry.upload_file"))
+    species_id = request.form.get("species_id")
+    if bool(species_id):
+        species_id = int(species_id)
+        species = species_by_id(species_id)
+        if bool(species):
+            return render_template(
+                "samples/select-population.html",
+                species=species,
+                populations=groups_by_species(species_id))
+        flash("Invalid species selected!", "alert-error")
+    flash("You need to select a species", "alert-error")
+    return index_page
+
+def save_population(conn: mdb.Connection, population_details: dict) -> int:
+    """Save the population details to the db."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT MAX(Id) AS last_id FROM InbredSet")
+        new_id = cursor.fetchone()["last_id"] + 1
+        cursor.execute(
+            "INSERT INTO InbredSet("
+            "Id, InbredSetId, InbredSetName, Name, SpeciesId, FullName, "
+            "MenuOrderId, Description"
+            ") "
+            "VALUES ("
+            "%(Id)s, %(InbredSetId)s, %(InbredSetName)s, %(Name)s, "
+            "%(SpeciesId)s, %(FullName)s, %(MenuOrderId)s, %(Description)s"
+            ")",
+            {
+                "Id": new_id,
+                "InbredSetId": new_id,
+                "MenuOrderId": 0,
+                **population_details
+            })
+        return new_id
+
+def population_by_id(conn: mdb.Connection, population_id: int) -> dict:
+    """Get the grouping/population by id."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM InbredSet WHERE InbredSetId=%s",
+                       (population_id,))
+        return cursor.fetchone()
+
+@samples.route("/upload/create-population", methods=["POST"])
+def create_population():
+    """Create new grouping/population."""
+    species_page = redirect(url_for("samples.select_species"), code=307)
+    species = species_by_id(request.form.get("species_id"))
+    pop_name = request.form.get("inbredset_name").strip()
+    pop_fullname = request.form.get("inbredset_fullname").strip()
+
+    if not bool(species):
+        flash("Invalid species!", "alert-error error-create-population")
+        return species_page
+    if (not bool(pop_name)) or (not bool(pop_fullname)):
+        flash("You *MUST* provide a grouping/population name",
+              "alert-error error-create-population")
+        return species_page
+
+    pop_id = with_db_connection(lambda conn: save_population(conn, {
+        "SpeciesId": species["SpeciesId"],
+        "Name": pop_name,
+        "InbredSetName": pop_fullname,
+        "FullName": pop_fullname,
+        "Family": request.form.get("inbredset_family") or None,
+        "Description": request.form.get("description") or None
+    }))
+    flash("Grouping/Population created successfully.", "alert-success")
+    return render_template(
+        "samples/upload-samples.html",
+        species=species,
+        population=with_db_connection(
+            lambda conn: population_by_id(conn, pop_id)))
+
+@samples.route("/upload/select-population", methods=["POST"])
+def select_population():
+    """Select from existing groupings/populations."""
+    species_page = redirect(url_for("samples.select_species"), code=307)
+    species = species_by_id(request.form.get("species_id"))
+    pop_id = int(request.form.get("inbredset_id"))
+    population = with_db_connection(lambda conn: population_by_id(conn, pop_id))
+
+    if not bool(species):
+        flash("Invalid species!", "alert-error error-select-population")
+        return species_page
+
+    if not bool(population):
+        flash("Invalid grouping/population!",
+              "alert-error error-select-population")
+        return species_page
+
+    return render_template("samples/upload-samples.html",
+                           species=species,
+                           population=population)
diff --git a/qc_app/templates/flash_messages.html b/qc_app/templates/flash_messages.html
new file mode 100644
index 0000000..b7af178
--- /dev/null
+++ b/qc_app/templates/flash_messages.html
@@ -0,0 +1,25 @@
+{%macro flash_all_messages()%}
+{%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%}
+{%endmacro%}
+
+{%macro flash_messages(filter_class)%}
+{%with messages = get_flashed_messages(with_categories=true)%}
+{%if messages:%}
+<ul>
+  {%for category, message in messages:%}
+  {%if filter_class in category%}
+  <li class="{{category}}">{{message}}</li>
+  {%endif%}
+  {%endfor%}
+</ul>
+{%endif%}
+{%endwith%}
+{%endmacro%}
diff --git a/qc_app/templates/index.html b/qc_app/templates/index.html
index 2db6048..e534bcb 100644
--- a/qc_app/templates/index.html
+++ b/qc_app/templates/index.html
@@ -1,9 +1,11 @@
 {%extends "base.html"%}
 
-{%block title%}Upload File{%endblock%}
+{%block title%}Data Upload{%endblock%}
 
 {%block contents%}
-<h1 class="heading">upload file</h1>
+<h1 class="heading">data upload</h1>
+
+<h2>Expression Data</h2>
 
 <div id="explainer">
   <p>This application assumes that you are familiar with the basics of data
@@ -26,7 +28,7 @@
 
 <form action="{{url_for('entry.upload_file')}}"
       method="POST" enctype="multipart/form-data">
-  <legend class="heading">upload file</legend>
+  <legend class="heading">upload expression data</legend>
   {%with messages = get_flashed_messages(with_categories=True) %}
   {%if messages %}
   <div class="alerts">
@@ -81,6 +83,33 @@
     </button>
   </form>
 </div>
+
+<h2>samples/cases</h2>
+
+<div>
+  <p>For the expression data above, you need the samples/cases in your file to
+    already exist in the GeneNetwork database. If there are any samples that do
+    not already exist the upload of the expression data will fail.</p>
+  <p>This section gives you the opportunity to upload any missing samples</p>
+</div>
+
+<form method="POST" action="{{url_for('samples.select_species')}}">
+  <legend class="heading">upload samples</legend>
+  <fieldset>
+    <label for="select:species">Species</label>
+    <select id="select:species" name="species_id" required="required">
+      <option value="">Select species</option>
+      {%for spec in species%}
+      <option value="{{spec.SpeciesId}}">{{spec.MenuName}}</option>
+      {%endfor%}
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <input type="submit" value="submit" class="btn btn-main form-col-2" />
+  </fieldset>
+</form>
+
 {%endblock%}
 
 
diff --git a/qc_app/templates/samples/select-population.html b/qc_app/templates/samples/select-population.html
new file mode 100644
index 0000000..24decb4
--- /dev/null
+++ b/qc_app/templates/samples/select-population.html
@@ -0,0 +1,105 @@
+{%extends "base.html"%}
+{%from "flash_messages.html" import flash_messages%}
+
+{%block title%}Select Grouping/Population{%endblock%}
+
+{%block contents%}
+<h1 class="heading">Select grouping/population</h1>
+
+<div>
+  <p>We organise the samples/cases/strains in a hierarchichal form, starting
+    with <strong>species</strong> at the very top. Under species, we have a
+    grouping in terms of the relevant population
+    (e.g. Inbred populations, cell tissue, etc.)</p>
+</div>
+
+<hr />
+
+<form method="POST" action="{{url_for('samples.select_population')}}">
+  <legend class="heading">select grouping/population</legend>
+  {{flash_messages("error-select-population")}}
+
+  <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
+  <fieldset>
+    <label for="select:inbredset" class="form-col-1">grouping/population</label>
+    <select id="select:inbredset"
+	    name="inbredset_id"
+	    required="required"
+	    class="form-col-2">
+      <option value="">Select a grouping/population</option>
+      {%for pop in populations%}
+      <option value="{{pop.InbredSetId}}">
+	{{pop.InbredSetName}} ({{pop.FullName}})</option>
+      {%endfor%}
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <input type="submit"
+	   value="select population"
+	   class="btn btn-main form-col-2" />
+  </fieldset>
+</form>
+
+<p style="color:#FE3535; padding-left:20em; font-weight:bolder;">OR</p>
+
+<form method="POST" action="{{url_for('samples.create_population')}}">
+  <legend class="heading">create new grouping/population</legend>
+  {{flash_messages("error-create-population")}}
+
+  <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
+  <fieldset>
+    <legend>mandatory</legend>
+    <label for="txt:inbredset-name" class="form-col-1">name</label>
+    <input id="txt:inbredset-name"
+	   name="inbredset_name"
+	   type="text"
+	   required="required"
+	   placeholder="Enter grouping/population name"
+	   class="form-col-2" />
+
+    <label for="txt:" class="form-col-1">full name</label>
+    <input id="txt:inbredset-fullname"
+	   name="inbredset_fullname"
+	   type="text"
+	   required = "required"
+	   placeholder="Enter the grouping/population's full name"
+	   class="form-col-2" />
+  </fieldset>
+  <fieldset>
+    <legend>Optional</legend>
+
+    <label for="num:public" class="form-col-1">public?</label>
+    <input id="num:public"
+	   name="public"
+	   type="number"
+	   min="0" max="2" value="2"
+	   class="form-col-2" />
+
+    <label for="txt:inbredset-family" class="form-col-1">family</label>
+    <input id="txt:inbredset-family"
+	   name="inbredset_family"
+	   type="text"
+	   placeholder="I am not sure what this is about."
+	   class="form-col-2" />
+
+    <label for="txtarea:" class="form-col-1">Description</label>
+    <textarea id="txtarea:description"
+	      name="description"
+	      rows="5"
+	      placeholder="Enter a description of this grouping/population"
+	      class="form-col-2"></textarea>
+  </fieldset>
+
+  <fieldset>
+    <input type="submit"
+	   value="create grouping/population"
+	   class="btn btn-main form-col-2" />
+  </fieldset>
+</form>
+
+{%endblock%}
+
+
+{%block javascript%}
+{%endblock%}
diff --git a/qc_app/templates/samples/upload-samples.html b/qc_app/templates/samples/upload-samples.html
new file mode 100644
index 0000000..b19e38c
--- /dev/null
+++ b/qc_app/templates/samples/upload-samples.html
@@ -0,0 +1,107 @@
+{%extends "base.html"%}
+{%from "flash_messages.html" import flash_messages%}
+
+{%block title%}Upload Samples{%endblock%}
+
+{%block css%}
+<style type="text/css">
+  #form-samples {
+      background-color: #D1D1D1;
+  }
+  #form-samples fieldset:nth-child(odd){
+      background-color: white;
+  }
+</style>
+{%endblock%}
+
+{%block contents%}
+<h1 class="heading">upload samples</h1>
+
+{{flash_messages("alert-success")}}
+
+<p>You can now upload a character-separated value (CSV) file that contains
+    details about your samples. The CSV file should have the following fields:
+    <dl>
+      <dt>Name</dt>
+      <dd>The primary name for the sample</dd>
+
+      <dt>Name2</dt>
+      <dd>A secondary name for the sample. This can simply be the same as
+	<strong>Name</strong> above. This field <strong>MUST</strong> contain a
+	value.</dd>
+
+      <dt>Symbol</dt>
+      <dd>A symbol for the sample. Can be an empty field.</dd>
+
+      <dt>Alias</dt>
+      <dd>An alias for the sample. Can be an empty field.</dd>
+    </dl>
+  </p>
+
+<form id="form-samples"
+      method="POST"
+      action="#"
+      enctype="multipart/form-data">
+  <legend class="heading">upload samples</legend>
+  <fieldset>
+    <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
+    <label class="form-col-1">species:</label>
+    <label class="form-col-2">{{species.SpeciesName}} [{{species.MenuName}}]</label>
+  </fieldset>
+
+  <fieldset>
+    <input type="hidden" name="inbredset_id" value="{{population.InbredSetId}}" />
+    <label class="form-col-1">grouping/population:</label>
+    <label class="form-col-2">{{population.Name}} [{{population.FullName}}]</label>
+  </fieldset>
+
+  <fieldset>
+    <label for="select:separator" class="form-col-1">field separator</label>
+    <select id="select:separator"
+	    name="separator"
+	    required="required"
+	    class="form-col-2">
+      <option value="">Select separator for your file</option>
+      <option value="&#x0009;">TAB</option>
+      <option value="&#x0020;">Space</option>
+      <option value=",">Comma</option>
+      <option value=";">Semicolon</option>
+      <option value="other">Other</option>
+    </select>
+    <input type="text" name="other_separator" class="form-col-2" />
+    <label class="form-col-2">
+      This is the character that separates the fields in your CSV file. If you
+      select "<strong>Other</strong>", then you must provide the separator in
+      the text field provided.
+    </label>
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:delimiter" class="form-col-1">field delimiter</label>
+    <input type="text" name="field_delimiter" class="form-col-2" />
+    <label class="form-col-2">
+      If there is a character delimiting the string texts within particular
+      fields in your CSV, provide the character here. This can be left blank if
+      no such delimiters exist in your file.
+    </label>
+  </fieldset>
+
+  <fieldset>
+    <label for="file_upload" class="form-col-1">select file</label>
+    <input type="file" name="samples_file" id="file_upload"
+	   accept="text/csv, text/tab-separated-values"
+	   class="form-col-2" />
+  </fieldset>
+
+  <fieldset>
+    <input type="submit"
+	   value="upload samples file"
+	   class="btn btn-main form-col-2" />
+  </fieldset>
+</form>
+
+{%endblock%}
+
+
+{%block javascript%}
+{%endblock%}