From af485aff7d25c1f5128586c550ca36debe24fd66 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Thu, 18 Jan 2024 14:43:00 +0300
Subject: UI: Create new ProbeSet dataset.

---
 qc_app/db/averaging.py                             | 23 ++++++
 qc_app/db/datasets.py                              | 33 +++++++++
 .../rqtl2/create-probe-dataset-success.html        | 60 ++++++++++++++++
 .../templates/rqtl2/select-probeset-dataset.html   | 82 +++++++++++++++++++++-
 qc_app/upload/rqtl2.py                             | 65 ++++++++++++++++-
 5 files changed, 260 insertions(+), 3 deletions(-)
 create mode 100644 qc_app/db/averaging.py
 create mode 100644 qc_app/templates/rqtl2/create-probe-dataset-success.html

diff --git a/qc_app/db/averaging.py b/qc_app/db/averaging.py
new file mode 100644
index 0000000..62bbe67
--- /dev/null
+++ b/qc_app/db/averaging.py
@@ -0,0 +1,23 @@
+"""Functions for db interactions for averaging methods"""
+from typing import Optional
+
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+
+def averaging_methods(conn: mdb.Connection) -> tuple[dict, ...]:
+    """Fetch all available averaging methods"""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM AvgMethod")
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def averaging_method_by_id(
+        conn: mdb.Connection, averageid: int) -> Optional[dict]:
+    """Fetch the averaging method by its ID"""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM AvgMethod WHERE Id=%s",
+                       (averageid,))
+        result = cursor.fetchone()
+        if bool(result):
+            return dict(result)
+
+    return None
diff --git a/qc_app/db/datasets.py b/qc_app/db/datasets.py
index 5f6a2d5..5816146 100644
--- a/qc_app/db/datasets.py
+++ b/qc_app/db/datasets.py
@@ -85,3 +85,36 @@ def probeset_create_study(conn: mdb.Connection,#pylint: disable=[too-many-argume
         cursor.execute("UPDATE ProbeFreeze SET ProbeFreezeId=%s",
                        (studyid,))
         return {**studydata, "studyid": studyid}
+
+def probeset_create_dataset(conn: mdb.Connection,#pylint: disable=[too-many-arguments]
+                            studyid: int,
+                            averageid: int,
+                            datasetname: str,
+                            datasetfullname: str,
+                            datasetshortname: str="",
+                            public: bool = True,
+                            datascale="log2") -> dict:
+    """Create a new ProbeSet dataset."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        dataset = {
+            "studyid": studyid,
+            "averageid": averageid,
+            "name2": datasetname,
+            "fname": datasetfullname,
+            "name": datasetshortname,
+            "sname": datasetshortname,
+            "today": date.today().isoformat(),
+            "public": 2 if public else 0,
+            "datascale": datascale
+        }
+        cursor.execute(
+            """
+            insert into ProbeSetFreeze(
+              ProbeFreezeId, AvgId, Name, Name2, FullName, ShortName,
+              CreateTime, public, DataScale)
+            VALUES(
+              %(studyid)s, %(averageid)s, %(name)s, %(name2)s, %(fname)s,
+              %(sname)s, %(today)s, %(public)s, %(datascale)s)
+            """,
+            dataset)
+        return {**dataset, "datasetid": cursor.lastrowid}
diff --git a/qc_app/templates/rqtl2/create-probe-dataset-success.html b/qc_app/templates/rqtl2/create-probe-dataset-success.html
new file mode 100644
index 0000000..33612c4
--- /dev/null
+++ b/qc_app/templates/rqtl2/create-probe-dataset-success.html
@@ -0,0 +1,60 @@
+{%extends "base.html"%}
+{%from "flash_messages.html" import flash_messages%}
+
+{%block title%}Upload R/qtl2 Bundle{%endblock%}
+
+{%block contents%}
+<h2 class="heading">Create ProbeSet Dataset</h2>
+
+<div class="explainer">
+  <p>You successfully created the ProbeSet dataset with the following
+    information.
+    <dl>
+      <dt>Averaging Method</dt>
+      <dd>{{avgmethod.Name}}</dd>
+
+      <dt>ID</dt>
+      <dd>{{dataset.datasetid}}</dd>
+
+      <dt>Name</dt>
+      <dd>{{dataset.name2}}</dd>
+
+      <dt>Full Name</dt>
+      <dd>{{dataset.fname}}</dd>
+
+      <dt>Short Name</dt>
+      <dd>{{dataset.sname}}</dd>
+
+      <dt>Created On</dt>
+      <dd>{{dataset.today}}</dd>
+
+      <dt>DataScale</dt>
+      <dd>{{dataset.datascale}}</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 dataset</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.Id}}" />
+  <input type="hidden" name="probe-dataset-id" value="{{dataset.datasetid}}" />
+
+  <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-dataset.html b/qc_app/templates/rqtl2/select-probeset-dataset.html
index 8b9e5ed..3e160fd 100644
--- a/qc_app/templates/rqtl2/select-probeset-dataset.html
+++ b/qc_app/templates/rqtl2/select-probeset-dataset.html
@@ -14,6 +14,7 @@
 
 {{flash_all_messages()}}
 
+{%if datasets | length > 0%}
 <form method="POST"
       action="{{url_for('upload.rqtl2.select_probeset_dataset',
 	      species_id=species.SpeciesId, population_id=population.Id)}}"
@@ -53,6 +54,85 @@
   </fieldset>
 </form>
 
-<p style="color: red; font-weight: bold;">Under Construction</p>
+<p style="color:#FE3535; padding-left:20em; font-weight:bolder;">OR</p>
+{%endif%}
+
+<form method="POST"
+      action="{{url_for('upload.rqtl2.create_probeset_dataset',
+	      species_id=species.SpeciesId, population_id=population.Id)}}"
+      id="frm:create-probeset-dataset">
+  <legend class="heading">Create a new ProbeSet dataset</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="{{probe_study.Id}}" />
+
+  <fieldset>
+    <label for="select:average">averaging method</label>
+    <select id="select:average"
+	    name="averageid"
+	    required="required">
+      <option value="">Select averaging method</option>
+      {%for avgmethod in avgmethods%}
+      <option value="{{avgmethod.Id}}">
+	{{avgmethod.Name}}
+	{%if avgmethod.Normalization%}
+	({{avgmethod.Normalization}})
+	{%endif%}
+      </option>
+      {%endfor%}
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:datasetname">Name</label>
+    <input type="text" id="txt:datasetname" name="datasetname"
+	   required="required"
+	   title="Name of the dataset, e.g 'BXDMicroArray_ProbeSet_June03'. (Required)" />
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:datasetfullname">Full Name</label>
+    <input type="text" id="txt:datasetfullname" name="datasetfullname"
+	   required="required"
+	   title="A longer name for the dataset, e.g. 'UTHSC Brain mRNA U74Av2 (Jun03) MAS5'. (Required)" />
+  </fieldset>
+
+  <fieldset>
+    <label for="txt:datasetshortname">Short Name</label>
+    <input type="text" id="txt:datasetshortname" name="datasetshortname"
+	   title="An abbreviated name for the dataset, e.g 'Br_U_0603_M'. (Optional)" />
+  </fieldset>
+
+  <fieldset>
+    <input type="checkbox" id="chk:public" name="datasetpublic"
+	   checked="checked"
+	   title="Whether or not the dataset is accessible by the general public." />
+    <label for="chk:datasetpublic">Public?</label>
+  </fieldset>
+
+  <fieldset>
+    <label for="select:datasetdatascale">Data Scale</label>
+    <select id="select:datasetdatascale"
+	    name="datasetdatascale"
+	    required="required">
+      <option value="log2" selected="selected">log2</option>
+      <option value="z_score">z_score</option>
+      <option value="log2_ratio">log2_ratio</option>
+      <option value="linear">linear</option>
+      <option value="linear_positive">linear_positive</option>
+    </select>
+  </fieldset>
+
+  <fieldset>
+    <input type="submit"
+	   value="create dataset"
+	   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 fc4e0e8..0b446ac 100644
--- a/qc_app/upload/rqtl2.py
+++ b/qc_app/upload/rqtl2.py
@@ -22,6 +22,7 @@ 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.averaging import averaging_methods, averaging_method_by_id
 from qc_app.db import (
     species_by_id,
     save_population,
@@ -33,6 +34,7 @@ from qc_app.db.datasets import (
 
     probeset_study_by_id,
     probeset_create_study,
+    probeset_create_dataset,
     probeset_datasets_by_study,
     probeset_studies_by_species_and_population)
 
@@ -349,7 +351,7 @@ def select_probeset_dataset(species_id: int, population_id: int):
                                         population_id=population_id),
                                 code=307)
         if not bool(probeset_study_by_id(conn, int(request.form["probe-study-id"]))):
-            flash("Invalid dataset selected!", "alert-error alert-rqtl2")
+            flash("Invalid study selected!", "alert-error alert-rqtl2")
             return summary_page
 
         return summary_page
@@ -397,6 +399,64 @@ def create_probeset_study(species_id: int, population_id: int):
                 int(request.form["geno-dataset-id"])),
             study=study)
 
+@rqtl2.route(("/upload/species/<int:species_id>/population/<int:population_id>"
+              "/rqtl2-bundle/create-probeset-dataset"),
+             methods=["POST"])
+def create_probeset_dataset(species_id: int, population_id: int):#pylint: disable=[too-many-return-statements]
+    """Create a new probeset dataset."""
+    with database_connection(app.config["SQL_URI"]) as conn:
+        error = check_errors(
+            conn, "species", "population", "rqtl2_bundle_file", "geno-dataset",
+            "probe-study-id")
+        if bool(error):
+            return error
+
+        form = request.form
+        summary_page = redirect(url_for("upload.rqtl2.select_dataset_info",
+                                        species_id=species_id,
+                                        population_id=population_id),
+                                code=307)
+        if not bool(form.get("averageid")):
+            flash("Averaging method not selected!", "alert-error alert-rqtl2")
+            return summary_page
+        if not bool(form.get("datasetname")):
+            flash("Dataset name not provided!", "alert-error alert-rqtl2")
+            return summary_page
+        if not bool(form.get("datasetfullname")):
+            flash("Dataset full name not provided!", "alert-error alert-rqtl2")
+            return summary_page
+
+        study = probeset_study_by_id(conn, int(form["probe-study-id"]))
+        if not bool(study):
+            flash("Invalid ProbeSet study provided!", "alert-error alert-rqtl2")
+            return summary_page
+
+        avgmethod = averaging_method_by_id(conn, int(form["averageid"]))
+        if not bool(avgmethod):
+            flash("Invalid averaging method provided!", "alert-error alert-rqtl2")
+            return summary_page
+
+        dset = probeset_create_dataset(conn,
+                                       int(form["probe-study-id"]),
+                                       int(form["averageid"]),
+                                       form["datasetname"],
+                                       form["datasetfullname"],
+                                       form["datasetshortname"],
+                                       form["datasetpublic"] == "on",
+                                       form.get("datasetdatascale", "log2"))
+        return render_template(
+            "rqtl2/create-probe-dataset-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,
+            avgmethod=avgmethod,
+            dataset=dset)
+
 @rqtl2.route(("/upload/species/<int:species_id>/population/<int:population_id>"
               "/rqtl2-bundle/dataset-info"),
              methods=["POST"])
@@ -450,7 +510,8 @@ def select_dataset_info(species_id: int, population_id: int):
                     geno_dataset=geno_dataset,
                     probe_study=probeset_study,
                     datasets=probeset_datasets_by_study(
-                        conn, int(form["probe-study-id"])))
+                        conn, int(form["probe-study-id"])),
+                    avgmethods=averaging_methods(conn))
 
     return render_template("rqtl2/summary-info.html",
                            species=species,
-- 
cgit v1.2.3