From 1b6b9a90a4dbe38aefc00293309fb48d9f478b13 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Thu, 26 Sep 2024 17:03:31 -0500
Subject: Start building up the view dataset endpoint

---
 uploader/phenotypes/models.py                   | 31 ++++++++++
 uploader/phenotypes/views.py                    | 17 ++++-
 uploader/templates/phenotypes/view-dataset.html | 82 +++++++++++++++++++++++++
 3 files changed, 129 insertions(+), 1 deletion(-)
 create mode 100644 uploader/templates/phenotypes/view-dataset.html

diff --git a/uploader/phenotypes/models.py b/uploader/phenotypes/models.py
index 1f72dbd..4ef674f 100644
--- a/uploader/phenotypes/models.py
+++ b/uploader/phenotypes/models.py
@@ -20,3 +20,34 @@ def datasets_by_population(
             "WHERE s.Id=%s AND iset.Id=%s;",
                        (species_id, population_id))
         return tuple(dict(row) for row in cursor.fetchall())
+
+
+def phenotypes_data(conn: mdb.Connection,
+                    population_id: int,
+                    dataset_id: int,
+                    offset: int = 0,
+                    limit: Optional[int] = None) -> tuple[dict, ...]:
+    """Fetch the data for the phenotypes."""
+    #TODO: This query isn't exactly right, it misses some data.
+    # — Phenotype -> PublishXRef -> PublishData -> Strain -> PublishFreeze
+    _query = ("SELECT pxr.*, pd.*, str.* FROM PublishFreeze AS pf "
+              "INNER JOIN PublishXRef AS pxr ON pf.InbredSetId=pxr.InbredSetId "
+              "INNER JOIN PublishData AS pd ON pxr.DataId=pd.Id "
+              "INNER JOIN Strain AS str ON pd.StrainId=str.Id "
+              "WHERE pf.InbredSetId=%s AND pf.Id=%s "
+              "ORDER BY pxr.DataId ASC, str.Id ASC") + (
+                  f" LIMIT {limit} OFFSET {offset}" if bool(limit) else "")
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(_query, (population_id, dataset_id))
+        debug_query(cursor)
+        return tuple(dict(row) for row in cursor.fetchall())
+
+
+def phenotypes_se(conn: mdb.Connection, dataset_id: int) -> tuple[dict, ...]:
+    """Fetch the standard errors for the phenotypes."""
+    return tuple()
+
+
+def phenotypes_sample_counts(conn: mdb.Connection, dataset_id: int) -> tuple[dict, ...]:
+    """Fetch the standard errors for the phenotypes."""
+    return tuple()
diff --git a/uploader/phenotypes/views.py b/uploader/phenotypes/views.py
index 7ad32ad..88cb89c 100644
--- a/uploader/phenotypes/views.py
+++ b/uploader/phenotypes/views.py
@@ -14,7 +14,7 @@ from uploader.request_checks import with_species, with_population
 from uploader.population.models import (populations_by_species,
                                         population_by_species_and_id)
 
-from .models import datasets_by_population
+from .models import datasets_by_population, phenotypes_data
 
 phenotypesbp = Blueprint("phenotypes", __name__)
 
@@ -83,3 +83,18 @@ def list_datasets(species: int, population: int, **kwargs):
                                    species["SpeciesId"],
                                    population["Id"]),
                                activelink="list-datasets")
+
+
+@phenotypesbp.route(
+    "<int:species_id>/populations/<int:population_id>/phenotypes/datasets"
+    "/<int:dataset_id>/view",
+    methods=["GET"])
+@require_login
+@with_population(species_redirect_uri="species.populations.phenotypes.index",
+                 redirect_uri="species.populations.phenotypes.select_population")
+def view_dataset(species: int, population: int, dataset_id: int, **kwargs):
+    """View a specific dataset"""
+    with database_connection(app.config["SQL_URI"]) as conn:
+        from flask import jsonify
+        return jsonify(phenotypes_data(
+            conn, population["Id"], dataset_id, offset=0, limit=20))
diff --git a/uploader/templates/phenotypes/view-dataset.html b/uploader/templates/phenotypes/view-dataset.html
new file mode 100644
index 0000000..219e61e
--- /dev/null
+++ b/uploader/templates/phenotypes/view-dataset.html
@@ -0,0 +1,82 @@
+{%extends "phenotypes/base.html"%}
+{%from "flash_messages.html" import flash_all_messages%}
+{%from "populations/macro-display-population-card.html" import display_population_card%}
+
+{%block title%}Phenotypes{%endblock%}
+
+{%block pagetitle%}Phenotypes{%endblock%}
+
+{%block lvl4_breadcrumbs%}
+<li {%if activelink=="view-datasets"%}
+    class="breadcrumb-item active"
+    {%else%}
+    class="breadcrumb-item"
+    {%endif%}>
+  <a href="{{url_for('species.populations.phenotypes.view_datasets',
+           species_id=species.SpeciesId,
+           population_id=population.Id)}}">View Datasets</a>
+</li>
+{%endblock%}
+
+{%block contents%}
+{{flash_all_messages()}}
+
+<div class="row">
+  <p>The basic dataset details are:</p>
+
+  <table class="table">
+    <thead>
+      <tr>
+        <th>Name</th>
+        <th>Full Name</th>
+        <th>Short Name</th>
+      </tr>
+    </thead>
+
+    <tbody>
+      <tr>
+        <td><a href="{{url_for('species.populations.phenotypes.view_dataset',
+                     species_id=species.SpeciesId,
+                     population_id=population.Id,
+                     dataset_id=dataset.Id)}}">{{dataset.Name}}</a></td>
+        <td>{{dataset.FullName}}</td>
+        <td>{{dataset.ShortName}}</td>
+      </tr>
+    </tbody>
+  </table>
+</div>
+
+<div class="row">
+  <h2>Phenotype Data</h2>
+
+  <p>The dataset has the following phenotypes:</p>
+
+  <table class="table">
+    <thead>
+      <tr>
+        <th>#</th>
+        <th>Name</th>
+        <th>Full Name</th>
+        <th>Short Name</th>
+      </tr>
+    </thead>
+
+    <tbody>
+      {%for pheno in phenotypes%}
+      <tr>
+        <td>{{pheno.sequence_number}}</td>
+        <td>{{pheno.Id}}</td>
+        <td>{{pheno.FullName}}</td>
+        <td>{{pheno.ShortName}}</td>
+      </tr>
+      {%else%}
+      <tr><td colspan="5"></td></tr>
+      {%endfor%}
+    </tbody>
+  </table>
+</div>
+{%endblock%}
+
+{%block sidebarcontents%}
+{{display_population_card(species, population)}}
+{%endblock%}
-- 
cgit v1.2.3