From d3122c73497650d7165afb8fc6c75f2650ef956c Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Thu, 26 Sep 2024 16:02:12 -0500
Subject: List the phenotype datasets.

---
 uploader/phenotypes/models.py                    | 22 +++++++++
 uploader/phenotypes/views.py                     | 22 +++++++++
 uploader/templates/phenotypes/list-datasets.html | 63 ++++++++++++++++++++++++
 3 files changed, 107 insertions(+)
 create mode 100644 uploader/phenotypes/models.py
 create mode 100644 uploader/templates/phenotypes/list-datasets.html

diff --git a/uploader/phenotypes/models.py b/uploader/phenotypes/models.py
new file mode 100644
index 0000000..1f72dbd
--- /dev/null
+++ b/uploader/phenotypes/models.py
@@ -0,0 +1,22 @@
+"""Database and utility functions for phenotypes."""
+from typing import Optional
+
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+
+from uploader.db_utils import debug_query
+
+def datasets_by_population(
+        conn: mdb.Connection,
+        species_id: int,
+        population_id: int
+) -> tuple[dict, ...]:
+    """Retrieve all of a population's phenotype studies."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(
+            "SELECT s.SpeciesId, pf.* FROM Species AS s "
+            "INNER JOIN InbredSet AS iset ON s.Id=iset.SpeciesId "
+            "INNER JOIN PublishFreeze AS pf ON iset.Id=pf.InbredSetId "
+            "WHERE s.Id=%s AND iset.Id=%s;",
+                       (species_id, population_id))
+        return tuple(dict(row) for row in cursor.fetchall())
diff --git a/uploader/phenotypes/views.py b/uploader/phenotypes/views.py
index 9185f4c..7ad32ad 100644
--- a/uploader/phenotypes/views.py
+++ b/uploader/phenotypes/views.py
@@ -14,6 +14,8 @@ 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
+
 phenotypesbp = Blueprint("phenotypes", __name__)
 
 @phenotypesbp.route("/phenotypes", methods=["GET"])
@@ -61,3 +63,23 @@ def select_population(species: dict, **kwargs):
         return redirect(url_for("species.populations.phenotypes.list_datasets",
                                 species_id=species["SpeciesId"],
                                 population_id=population["Id"]))
+
+
+
+@phenotypesbp.route(
+    "<int:species_id>/populations/<int:population_id>/phenotypes/datasets",
+    methods=["GET"])
+@require_login
+@with_population(species_redirect_uri="species.populations.phenotypes.index",
+                 redirect_uri="species.populations.phenotypes.select_population")
+def list_datasets(species: int, population: int, **kwargs):
+    """List available phenotype datasets."""
+    with database_connection(app.config["SQL_URI"]) as conn:
+        return render_template("phenotypes/list-datasets.html",
+                               species=species,
+                               population=population,
+                               datasets=datasets_by_population(
+                                   conn,
+                                   species["SpeciesId"],
+                                   population["Id"]),
+                               activelink="list-datasets")
diff --git a/uploader/templates/phenotypes/list-datasets.html b/uploader/templates/phenotypes/list-datasets.html
new file mode 100644
index 0000000..360fd2c
--- /dev/null
+++ b/uploader/templates/phenotypes/list-datasets.html
@@ -0,0 +1,63 @@
+{%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=="list-datasets"%}
+    class="breadcrumb-item active"
+    {%else%}
+    class="breadcrumb-item"
+    {%endif%}>
+  <a href="{{url_for('species.populations.phenotypes.list_datasets',
+           species_id=species.SpeciesId,
+           population_id=population.Id)}}">List Datasets</a>
+</li>
+{%endblock%}
+
+{%block contents%}
+{{flash_all_messages()}}
+
+<div class="row">
+  {%if datasets | length > 0%}
+  <p>The dataset(s) available for this population is/are:</p>
+
+  <table class="table">
+    <thead>
+      <tr>
+        <th>Name</th>
+        <th>Full Name</th>
+        <th>Short Name</th>
+      </tr>
+    </thead>
+
+    <tbody>
+      {%for dataset in datasets%}
+      <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>
+      {%endfor%}
+    </tbody>
+  </table>
+  {%else%}
+  <p class="text-warning">
+    <span class="glyphicon glyphicon-exclamation-sign"></span>
+    There is no dataset for this population!</p>
+  <p><a href="#"
+        class="not-implemented btn btn-primary"
+        title="Create a new phenotype dataset.">create dataset</a></p>
+  {%endif%}
+</div>
+{%endblock%}
+
+{%block sidebarcontents%}
+{{display_population_card(species, population)}}
+{%endblock%}
-- 
cgit v1.2.3