Create new ProbeSet study.

6 files changed, 272 insertions, 20 deletions

diff --git a/qc_app/db/datasets.py b/qc_app/db/datasets.py
index 086c103..5f6a2d5 100644
--- a/qc_app/db/datasets.py
+++ b/qc_app/db/datasets.py
@@ -1,4 +1,6 @@
 """Functions for accessing the database relating to datasets."""
+from datetime import date
+
 import MySQLdb as mdb
 from MySQLdb.cursors import DictCursor
 
@@ -43,9 +45,43 @@ def probeset_datasets_by_study(conn: mdb.Connection,
                        (studyid,))
         return tuple(dict(row) for row in cursor.fetchall())
 
-def probe_study_by_id(conn: mdb.Connection, studyid: int) -> dict:
+def probeset_study_by_id(conn: mdb.Connection, studyid: int) -> dict:
     """Retrieve ProbeSet study by ID"""
     with conn.cursor(cursorclass=DictCursor) as cursor:
         cursor.execute("SELECT * FROM ProbeFreeze WHERE Id=%s",
                        (studyid,))
         return dict(cursor.fetchone())
+
+def probeset_create_study(conn: mdb.Connection,#pylint: disable=[too-many-arguments]
+                          populationid: int,
+                          platformid: int,
+                          tissueid: int,
+                          studyname: str,
+                          studyfullname: str = "",
+                          studyshortname: str = ""):
+    """Create a new ProbeSet study."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        studydata = {
+            "platid": platformid,
+            "tissueid": tissueid,
+            "name": studyname,
+            "fname": studyfullname or studyname,
+            "sname": studyshortname,
+            "today": date.today().isoformat(),
+            "popid": populationid
+        }
+        cursor.execute(
+            """
+            INSERT INTO ProbeFreeze(
+              ChipId, TissueId, Name, FullName, ShortName, CreateTime,
+              InbredSetId
+            ) VALUES (
+              %(platid)s, %(tissueid)s, %(name)s, %(fname)s, %(sname)s,
+              %(today)s, %(popid)s
+            )
+            """,
+            studydata)
+        studyid = cursor.lastrowid
+        cursor.execute("UPDATE ProbeFreeze SET ProbeFreezeId=%s",
+                       (studyid,))
+        return {**studydata, "studyid": studyid}
diff --git a/qc_app/db/platforms.py b/qc_app/db/platforms.py
new file mode 100644
index 0000000..cb527a7
--- /dev/null
+++ b/qc_app/db/platforms.py
@@ -0,0 +1,25 @@
+"""Handle db interactions for platforms."""
+from typing import Optional
+
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+
+def platforms_by_species(
+        conn: mdb.Connection, speciesid: int) -> tuple[dict, ...]:
+    """Retrieve platforms by the species"""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM GeneChip WHERE SpeciesId=%s "
+                       "ORDER BY GeneChipName ASC",
+                       (speciesid,))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def platform_by_id(conn: mdb.Connection, platformid: int) -> Optional[dict]:
+    """Retrieve a platform by its ID"""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM GeneChip WHERE Id=%s",
+                       (platformid,))
+        result = cursor.fetchone()
+        if bool(result):
+            return dict(result)
+
+    return None
diff --git a/qc_app/db/tissues.py b/qc_app/db/tissues.py
new file mode 100644
index 0000000..ebf24fd
--- /dev/null
+++ b/qc_app/db/tissues.py
@@ -0,0 +1,22 @@
+"""Handle db interactions for tissue."""
+from typing import Optional
+
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+
+def all_tissues(conn: mdb.Connection) -> tuple[dict, ...]:
+    """All available tissue."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM Tissue ORDER BY TissueName")
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def tissue_by_id(conn: mdb.Connection, tissueid: int) -> Optional[dict]:
+    """Retrieve a tissue by its ID"""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM Tissue WHERE Id=%s",
+                       (tissueid,))
+        result = cursor.fetchone()
+        if bool(result):
+            return dict(result)
+
+    return None
diff --git a/qc_app/templates/rqtl2/create-probe-study-success.html b/qc_app/templates/rqtl2/create-probe-study-success.html
new file mode 100644
index 0000000..1b85d0c
--- /dev/null
+++ b/qc_app/templates/rqtl2/create-probe-study-success.html
@@ -0,0 +1,53 @@
+{%extends "base.html"%}
+{%from "flash_messages.html" import flash_messages%}
+
+{%block title%}Upload R/qtl2 Bundle{%endblock%}
+
+{%block contents%}
+<h2 class="heading">Create ProbeSet Study</h2>
+
+<div class="explainer">
+  <p>You successfully created the ProbeSet study with the following
+    information.
+    <dl>
+      <dt>ID</dt>
+      <dd>{{study.id}}</dd>
+
+      <dt>Name</dt>
+      <dd>{{study.name}}</dd>
+
+      <dt>Full Name</dt>
+      <dd>{{study.fname}}</dd>
+
+      <dt>Short Name</dt>
+      <dd>{{study.sname}}</dd>
+
+      <dt>Created On</dt>
+      <dd>{{study.today}}</dd>
+    </dl>
+  </p>
+</div>
+
+<form id="frm-upload-rqtl2-bundle"
+      action="{{url_for('upload.rqtl2.select_dataset_info',
+	      species_id=species.SpeciesId,
+	      population_id=population.InbredSetId)}}"
+      method="POST"
+      enctype="multipart/form-data">
+  <legend class="heading">Create ProbeSet study</legend>
+
+  <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
+  <input type="hidden" name="population_id"
+	 value="{{population.InbredSetId}}" />
+  <input type="hidden" name="rqtl2_bundle_file" value="{{rqtl2_bundle_file}}" />
+  <input type="hidden" name="geno-dataset-id" value="{{geno_dataset.Id}}" />
+  <input type="hidden" name="probe-study-id" value="{{study.studyid}}" />
+
+  <fieldset>
+    <input type="submit"
+	   value="continue"
+	   class="btn btn-main form-col-2" />
+  </fieldset>
+</form>
+
+{%endblock%}
diff --git a/qc_app/templates/rqtl2/select-probeset-study-id.html b/qc_app/templates/rqtl2/select-probeset-study-id.html
index bb31fde..34aa54e 100644
--- a/qc_app/templates/rqtl2/select-probeset-study-id.html
+++ b/qc_app/templates/rqtl2/select-probeset-study-id.html
@@ -1,5 +1,5 @@
 {%extends "base.html"%}
-{%from "flash_messages.html" import flash_messages%}
+{%from "flash_messages.html" import flash_messages, flash_all_messages%}
 
 {%block title%}Upload R/qtl2 Bundle{%endblock%}
 
@@ -12,9 +12,13 @@
   <p>This page gives you the ability to do that.</p>
 </div>
 
+{{flash_all_messages()}}
+
 <form method="POST"
-      action="#">
-  <legend class="heading">Select from existing ProbeSet datasets</legend>
+      action="{{url_for('upload.rqtl2.select_probeset_study',
+	      species_id=species.SpeciesId, population_id=population.Id)}}"
+      id="frm:select-probeset-study">
+  <legend class="heading">Select from existing ProbeSet studies</legend>
 
   <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
   <input type="hidden" name="population_id"
@@ -51,9 +55,72 @@
 <p style="color:#FE3535; padding-left:20em; font-weight:bolder;">OR</p>
 
 <form method="POST"
-      action="#">
-  <legend class="heading">Create new ProbeSet dataset</legend>
-  <strong style="color: red;font-weight:bold;">Under construction</strong>
+      action="{{url_for('upload.rqtl2.create_probeset_study',
+	      species_id=species.SpeciesId, population_id=population.Id)}}"
+      id="frm:create-probeset-study">
+  <legend class="heading">Create new ProbeSet study</legend>
+
+  <input type="hidden" name="species_id" value="{{species.SpeciesId}}" />
+  <input type="hidden" name="population_id"
+	 value="{{population.InbredSetId}}" />
+  <input type="hidden" name="rqtl2_bundle_file"
+	 value="{{rqtl2_bundle_file}}" />
+  <input type="hidden" name="geno-dataset-id" value="{{geno_dataset.Id}}" />
+
+  <fieldset>
+    <label for="select:platform">Platform</label>
+    <select id="select:platform"
+	    name="platformid"
+	    required="required"
+	    {%if platforms | length == 0%}disabled="disabled"{%endif%}>
+      <option value="">Select a platform</option>
+      {%for platform in platforms%}
+      <option value="{{platform.GeneChipId}}">
+	{{platform.GeneChipName}} ({{platform.Name}})
+      </option>
+      {%endfor%}
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <label for="select:tissue">Tissue</label>
+    <select id="select:tissue"
+	    name="tissueid"
+	    required="required"
+	    {%if tissues | length == 0%}disabled="disabled"{%endif%}>
+      <option value="">Select tissue</option>
+      {%for tissue in tissues%}
+      <option value="{{tissue.Id}}">
+	{{tissue.TissueName}} ({{tissue.Short_Name}})
+      </option>
+      {%endfor%}
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:studyname">Study name</label>
+    <input type="text" id="txt:studyname" name="studyname"
+	   placeholder="Name of the study. (Required)"
+	   required="required" />
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:studyfullname">Study name</label>
+    <input type="text" id="txt:studyfullname" name="studyfullname"
+	   placeholder="Longer name of the study. (Optional)" />
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:studyshortname">Study name</label>
+    <input type="text" id="txt:studyshortname" name="studyshortname"
+	   placeholder="Shorter name of the study. (Optional)" />
+  </fieldset>
+
+    <fieldset>
+    <input type="submit"
+	   value="create study"
+	   class="btn btn-main form-col-2" />
+  </fieldset>
 </form>
 
 {%endblock%}
diff --git a/qc_app/upload/rqtl2.py b/qc_app/upload/rqtl2.py
index 3411f98..0d80776 100644
--- a/qc_app/upload/rqtl2.py
+++ b/qc_app/upload/rqtl2.py
@@ -19,6 +19,9 @@ from r_qtl.errors import InvalidFormat
 from qc_app.files import save_file, fullpath
 from qc_app.dbinsert import species as all_species
 from qc_app.db_utils import with_db_connection, database_connection
+
+from qc_app.db.tissues import all_tissues, tissue_by_id
+from qc_app.db.platforms import platform_by_id, platforms_by_species
 from qc_app.db import (
     species_by_id,
     save_population,
@@ -28,6 +31,8 @@ from qc_app.db.datasets import (
     geno_dataset_by_id,
     geno_datasets_by_species_and_population,
 
+    probeset_study_by_id,
+    probeset_create_study,
     probeset_datasets_by_study,
     probeset_studies_by_species_and_population)
 
@@ -311,17 +316,59 @@ def select_probeset_study(species_id: int, population_id: int):
             return error
 
         summary_page = redirect(url_for("upload.rqtl2.select_dataset_info",
-                                        species=species_by_id(conn, species_id),
-                                        population=population_by_species_and_id(
-                                            conn, species_id, population_id)),
+                                        species_id=species_id,
+                                        population_id=population_id),
                                 code=307)
-        if not bool(probe_study_by_id(conn, int(request.form["probe-study-id"]))):
+        if not bool(probeset_study_by_id(conn, int(request.form["probe-study-id"]))):
             flash("Invalid study selected!", "alert-error alert-rqtl2")
             return summary_page
 
         return summary_page
 
 @rqtl2.route(("/upload/species/<int:species_id>/population/<int:population_id>"
+              "/rqtl2-bundle/create-probeset-study"),
+             methods=["POST"])
+def create_probeset_study(species_id: int, population_id: int):
+    """Create a new probeset study."""
+    with database_connection(app.config["SQL_URI"]) as conn:
+        error = check_errors(
+            conn, "species", "population", "rqtl2_bundle_file", "geno-dataset")
+        if bool(error):
+            return error
+
+        form = request.form
+        select_study_page = redirect(
+            url_for("upload.rqtl2.select_probeset_study",
+                    species_id=species_id,
+                    population_id=population_id),
+            code=307)
+
+        if not (bool(form.get("platformid")) and
+                bool(platform_by_id(conn, int(form["platformid"])))):
+            flash("Invalid platform selected.", "alert-error alert-rqtl2")
+            return select_study_page
+
+        if not (bool(form.get("tissueid")) and
+                bool(tissue_by_id(conn, int(form["tissueid"])))):
+            flash("Invalid tissue selected.", "alert-error alert-rqtl2")
+            return select_study_page
+
+        study = probeset_create_study(
+            conn, population_id, int(form["platformid"]), int(form["tissueid"]),
+            form["studyname"], form.get("studyfullname") or "",
+            form.get("studyshortname") or "")
+        return render_template(
+            "rqtl2/create-probe-study-success.html",
+            species=species_by_id(conn, species_id),
+            population=population_by_species_and_id(
+                conn, species_id, population_id),
+            rqtl2_bundle_file=request.form["rqtl2_bundle_file"],
+            geno_dataset=geno_dataset_by_id(
+                conn,
+                int(request.form["geno-dataset-id"])),
+            study=study)
+
+@rqtl2.route(("/upload/species/<int:species_id>/population/<int:population_id>"
               "/rqtl2-bundle/dataset-info"),
              methods=["POST"])
 def select_dataset_info(species_id: int, population_id: int):
@@ -359,17 +406,19 @@ def select_dataset_info(species_id: int, population_id: int):
                     rqtl2_bundle_file=thefile.name,
                     geno_dataset=geno_dataset,
                     studies=probeset_studies_by_species_and_population(
-                            conn, species_id, population_id))
+                            conn, species_id, population_id),
+                    platforms=platforms_by_species(conn, species_id),
+                    tissues=all_tissues(conn))
 
             if "pheno" in cdata and not bool(form.get("probe-dataset-id")):
-                    return render_template(
-                        "rqtl2/select-probeset-dataset.html",
-                        species=species,
-                        population=population,
-                        rqtl2_bundle_file=thefile.name,
-                        geno_dataset=geno_dataset,
-                        datasets=probeset_datasets_by_study(
-                            conn, int(form["probe-study-id"])))
+                return render_template(
+                    "rqtl2/select-probeset-dataset.html",
+                    species=species,
+                    population=population,
+                    rqtl2_bundle_file=thefile.name,
+                    geno_dataset=geno_dataset,
+                    datasets=probeset_datasets_by_study(
+                        conn, int(form["probe-study-id"])))
 
     return render_template("rqtl2/summary-info.html",
                            species=species,