From f04e340323d0e289aaa379ab1a8d82033da73e62 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Fri, 27 Sep 2024 11:43:48 -0500
Subject: Show some details for a phenotype dataset.

---
 uploader/phenotypes/models.py                   | 48 +++++++++++++++++++++++++
 uploader/phenotypes/views.py                    | 47 ++++++++++++++++++------
 uploader/templates/phenotypes/view-dataset.html | 23 ++++++------
 3 files changed, 98 insertions(+), 20 deletions(-)

(limited to 'uploader')

diff --git a/uploader/phenotypes/models.py b/uploader/phenotypes/models.py
index 4ef674f..c3b6dfb 100644
--- a/uploader/phenotypes/models.py
+++ b/uploader/phenotypes/models.py
@@ -22,6 +22,54 @@ def datasets_by_population(
         return tuple(dict(row) for row in cursor.fetchall())
 
 
+def dataset_by_id(conn: mdb.Connection,
+                  species_id: int,
+                  population_id: int,
+                  dataset_id: int) -> dict:
+    """Fetch dataset details by identifier"""
+    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 AND pf.Id=%s",
+            (species_id, population_id, dataset_id))
+        return dict(cursor.fetchone())
+
+
+def phenotypes_count(conn: mdb.Connection,
+                     population_id: int,
+                     dataset_id: int) -> int:
+    """Count the number of phenotypes in the dataset."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(
+            "SELECT COUNT(*) AS total_phenos FROM Phenotype AS pheno "
+            "INNER JOIN PublishXRef AS pxr ON pheno.Id=pxr.PhenotypeId "
+            "INNER JOIN PublishFreeze AS pf ON pxr.InbredSetId=pf.InbredSetId "
+            "WHERE pxr.InbredSetId=%s AND pf.Id=%s",
+        (population_id, dataset_id))
+        return int(cursor.fetchone()["total_phenos"])
+
+
+def dataset_phenotypes(conn: mdb.Connection,
+                       population_id: int,
+                       dataset_id: int,
+                       offset: int = 0,
+                       limit: Optional[int] = None) -> tuple[dict, ...]:
+    """Fetch the actual phenotypes."""
+    _query = (
+        "SELECT pheno.*, pxr.Id, ist.InbredSetCode FROM Phenotype AS pheno "
+        "INNER JOIN PublishXRef AS pxr ON pheno.Id=pxr.PhenotypeId "
+        "INNER JOIN PublishFreeze AS pf ON pxr.InbredSetId=pf.InbredSetId "
+        "INNER JOIN InbredSet AS ist ON pf.InbredSetId=ist.Id "
+        "WHERE pxr.InbredSetId=%s AND pf.Id=%s") + (
+            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_data(conn: mdb.Connection,
                     population_id: int,
                     dataset_id: int,
diff --git a/uploader/phenotypes/views.py b/uploader/phenotypes/views.py
index 88cb89c..4565844 100644
--- a/uploader/phenotypes/views.py
+++ b/uploader/phenotypes/views.py
@@ -1,20 +1,24 @@
 """Views handling ('classical') phenotypes."""
-from flask import (request,
+from flask import (flash,
+                   request,
                    url_for,
                    redirect,
                    Blueprint,
                    render_template,
                    current_app as app)
 
-from uploader.datautils import order_by_family
 from uploader.authorisation import require_login
 from uploader.db_utils import database_connection
 from uploader.species.models import all_species, species_by_id
 from uploader.request_checks import with_species, with_population
+from uploader.datautils import safe_int, order_by_family, enumerate_sequence
 from uploader.population.models import (populations_by_species,
                                         population_by_species_and_id)
 
-from .models import datasets_by_population, phenotypes_data
+from .models import (dataset_by_id,
+                     phenotypes_count,
+                     dataset_phenotypes,
+                     datasets_by_population)
 
 phenotypesbp = Blueprint("phenotypes", __name__)
 
@@ -40,7 +44,7 @@ def index():
                     methods=["GET"])
 @require_login
 @with_species(redirect_uri="species.populations.phenotypes.index")
-def select_population(species: dict, **kwargs):
+def select_population(species: dict, **kwargs):# pylint: disable=[unused-argument]
     """Select the population for your phenotypes."""
     with database_connection(app.config["SQL_URI"]) as conn:
         if not bool(request.args.get("population_id")):
@@ -58,7 +62,7 @@ def select_population(species: dict, **kwargs):
             flash("No such population found!", "alert-danger")
             return redirect(url_for(
                 "species.populations.phenotypes.select_population",
-                species_id=species_id))
+                species_id=species["SpeciesId"]))
 
         return redirect(url_for("species.populations.phenotypes.list_datasets",
                                 species_id=species["SpeciesId"],
@@ -72,7 +76,7 @@ def select_population(species: dict, **kwargs):
 @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):
+def list_datasets(species: dict, population: dict, **kwargs):# pylint: disable=[unused-argument]
     """List available phenotype datasets."""
     with database_connection(app.config["SQL_URI"]) as conn:
         return render_template("phenotypes/list-datasets.html",
@@ -92,9 +96,32 @@ def list_datasets(species: int, population: int, **kwargs):
 @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):
+def view_dataset(# pylint: disable=[unused-argument]
+        species: dict, population: dict, 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))
+        dataset = dataset_by_id(
+            conn, species["SpeciesId"], population["Id"], dataset_id)
+        if not bool(dataset):
+            flash("Could not find such a phenotype dataset!", "alert-danger")
+            return redirect(url_for(
+                "species.populations.phenotypes.list_datasets",
+                species_id=species["SpeciesId"],
+                population_id=population["Id"]))
+
+        start_at = max(safe_int(request.args.get("start_at") or 0), 0)
+        count = int(request.args.get("count") or 20)
+        return render_template("phenotypes/view-dataset.html",
+                               species=species,
+                               population=population,
+                               dataset={"Id": dataset_id},
+                               phenotype_count=phenotypes_count(
+                                   conn, population["Id"], dataset_id),
+                               phenotypes=enumerate_sequence(
+                                   dataset_phenotypes(conn,
+                                                      population["Id"],
+                                                      dataset_id,
+                                                      offset=start_at,
+                                                      limit=count),
+                                   start=start_at+1),
+                               activelink="view-dataset")
diff --git a/uploader/templates/phenotypes/view-dataset.html b/uploader/templates/phenotypes/view-dataset.html
index 219e61e..fc393d6 100644
--- a/uploader/templates/phenotypes/view-dataset.html
+++ b/uploader/templates/phenotypes/view-dataset.html
@@ -7,14 +7,15 @@
 {%block pagetitle%}Phenotypes{%endblock%}
 
 {%block lvl4_breadcrumbs%}
-<li {%if activelink=="view-datasets"%}
+<li {%if activelink=="view-dataset"%}
     class="breadcrumb-item active"
     {%else%}
     class="breadcrumb-item"
     {%endif%}>
-  <a href="{{url_for('species.populations.phenotypes.view_datasets',
+  <a href="{{url_for('species.populations.phenotypes.view_dataset',
            species_id=species.SpeciesId,
-           population_id=population.Id)}}">View Datasets</a>
+           population_id=population.Id,
+           dataset_id=dataset.Id)}}">View Datasets</a>
 </li>
 {%endblock%}
 
@@ -49,15 +50,18 @@
 <div class="row">
   <h2>Phenotype Data</h2>
 
-  <p>The dataset has the following phenotypes:</p>
+  <p>This dataset has a total of {{phenotype_count}} phenotypes.</p>
+  <p class="text-danger">
+    <span class="glyphicon glyphicon-exclamation-sign"></span>
+    Limit access here, according to the authorisation privileges the user has on
+    this dataset!</p>
 
   <table class="table">
     <thead>
       <tr>
         <th>#</th>
-        <th>Name</th>
-        <th>Full Name</th>
-        <th>Short Name</th>
+        <th>Record</th>
+        <th>Description</th>
       </tr>
     </thead>
 
@@ -65,9 +69,8 @@
       {%for pheno in phenotypes%}
       <tr>
         <td>{{pheno.sequence_number}}</td>
-        <td>{{pheno.Id}}</td>
-        <td>{{pheno.FullName}}</td>
-        <td>{{pheno.ShortName}}</td>
+        <td>{{pheno.InbredSetCode}}_{{pheno["pxr.Id"]}}</td>
+        <td>{{pheno.Post_publication_description or pheno.Pre_publication_abbreviation or pheno.Original_description}}</td>
       </tr>
       {%else%}
       <tr><td colspan="5"></td></tr>
-- 
cgit v1.2.3