Display some genotype information.

4 files changed, 220 insertions, 10 deletions

diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py
new file mode 100644
index 0000000..53c5fb8
--- /dev/null
+++ b/uploader/genotypes/models.py
@@ -0,0 +1,41 @@
+"""Functions for handling genotypes."""
+from typing import Optional
+
+import MySQLdb as mdb
+from MySQLdb.cursors import DictCursor
+
+from uploader.db_utils import debug_query
+
+def genocode_by_population(
+        conn: mdb.Connection, population_id: int) -> tuple[dict, ...]:
+    """Get the allele/genotype codes."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT * FROM GenoCode WHERE InbredSetId=%s",
+                       (population_id,))
+        return tuple(dict(item) for item in cursor.fetchall())
+
+
+def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int:
+    """Find the total count of the genotype markers for a species."""
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(
+            "SELECT COUNT(Name) AS markers_count FROM Geno WHERE SpeciesId=%s",
+            (species_id,))
+        return int(cursor.fetchone()["markers_count"])
+
+
+def genotype_markers(
+        conn: mdb.Connection,
+        species_id: int,
+        offset: int = 0,
+        limit: Optional[int] = None
+) -> tuple[dict, ...]:
+    """Retrieve markers from the database."""
+    _query = "SELECT * FROM Geno WHERE SpeciesId=%s"
+    if bool(limit) and limit > 0:
+        _query = _query + f" LIMIT {limit} OFFSET {offset}"
+
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(_query, (species_id,))
+        debug_query(cursor)
+        return tuple(dict(row) for row in cursor.fetchall())
diff --git a/uploader/genotypes/views.py b/uploader/genotypes/views.py
index 885e008..0618949 100644
--- a/uploader/genotypes/views.py
+++ b/uploader/genotypes/views.py
@@ -7,13 +7,17 @@ from flask import (flash,
                    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.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 (genotype_markers,
+                     genotype_markers_count,
+                     genocode_by_population)
+
 genotypesbp = Blueprint("genotypes", __name__)
 
 @genotypesbp.route("populations/genotypes", methods=["GET"])
@@ -72,4 +76,35 @@ def select_population(species_id: int):
 @require_login
 def list_genotypes(species_id: int, population_id: int):
     """List genotype details for species and population."""
-    return f"Would list geno info for population {population_id} from species {species_id}"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        species = species_by_id(conn, species_id)
+        if not bool(species):
+            flash("Invalid species provided!", "alert-danger")
+            return redirect(url_for("species.populations.genotypes.index"))
+
+        population = population_by_species_and_id(
+            conn, species_id, population_id)
+        if not bool(population):
+            flash("Invalid population selected!", "alert-danger")
+            return redirect(url_for(
+                "species.populations.genotypes.select_population",
+                species_id=species_id))
+
+        start_from = safe_int(request.args.get("start_from") or 0)
+        if start_from < 0:
+            start_from = 0
+        count = safe_int(request.args.get("count") or 20)
+        markers = enumerate_sequence(
+            genotype_markers(conn, species_id, offset=start_from, limit=count),
+            start=start_from+1)
+        return render_template("genotypes/list-genotypes.html",
+                               species=species,
+                               population=population,
+                               genocode=genocode_by_population(
+                                   conn, population_id),
+                               total_markers=genotype_markers_count(
+                                   conn, species_id),
+                               start_from=start_from,
+                               count=count,
+                               markers=markers,
+                               activelink="list-genotypes")
diff --git a/uploader/templates/genotypes/index.html b/uploader/templates/genotypes/index.html
index 9ffea73..e749f5a 100644
--- a/uploader/templates/genotypes/index.html
+++ b/uploader/templates/genotypes/index.html
@@ -25,12 +25,4 @@
   {{select_species_form(url_for("species.populations.genotypes.index"),
   species)}}
 </div>
-
-<div class="row">
-  <h3>Some Important Concepts to Consider/Remember</h3>
-  <ul>
-    <li>Reference vs. Non-reference alleles</li>
-    <li>In <em>GenoCode</em> table, items are ordered by <strong>InbredSet</strong></li>
-  </ul>
-</div>
 {%endblock%}
diff --git a/uploader/templates/genotypes/list-genotypes.html b/uploader/templates/genotypes/list-genotypes.html
new file mode 100644
index 0000000..9b27540
--- /dev/null
+++ b/uploader/templates/genotypes/list-genotypes.html
@@ -0,0 +1,142 @@
+{%extends "genotypes/base.html"%}
+{%from "flash_messages.html" import flash_all_messages%}
+{%from "populations/macro-display-population-card.html" import display_population_card%}
+
+{%block title%}Genotypes{%endblock%}
+
+{%block pagetitle%}Genotypes{%endblock%}
+
+{%block lvl4_breadcrumbs%}
+<li {%if activelink=="list-genotypes"%}
+    class="breadcrumb-item active"
+    {%else%}
+    class="breadcrumb-item"
+    {%endif%}>
+  <a href="{{url_for('species.populations.genotypes.list_genotypes',
+           species_id=species.SpeciesId,
+           population_id=population.Id)}}">List genotypes</a>
+</li>
+{%endblock%}
+
+{%block contents%}
+{{flash_all_messages()}}
+
+<div class="row">
+  <p>
+    The genotype encoding used for the "{{population.FullName}}" population from
+    the "{{species.FullName}}" species is as shown in the table below.
+  </p>
+  <table class="table">
+    <legend>Genotype Encoding</legend>
+
+    <thead>
+      <tr>
+        <th>Allele Type</th>
+        <th>Allele Symbol</th>
+        <th>Allele Value</th>
+      </tr>
+    </thead>
+
+    <tbody>
+      {%for row in genocode%}
+      <tr>
+        <td>{{row.AlleleType}}</td>
+        <td>{{row.AlleleSymbol}}</td>
+        <td>{{row.DatabaseValue if row.DatabaseValue is not none else "NULL"}}</td>
+      </tr>
+      {%else%}
+      <tr>
+        <td colspan="7" class="text-info">
+          <span class="glyphicon glyphicon-exclamation-sign"></span>
+          There is no explicit genotype encoding defined for this population.
+        </td>
+      </tr>
+      {%endfor%}
+    </tbody>
+  </table>
+</div>
+
+<div class="row text-danger">
+  <h3>Some Important Concepts to Consider/Remember</h3>
+  <ul>
+    <li>Reference vs. Non-reference alleles</li>
+    <li>In <em>GenoCode</em> table, items are ordered by <strong>InbredSet</strong></li>
+  </ul>
+  <h3>Possible references</h3>
+  <ul>
+    <li>https://mr-dictionary.mrcieu.ac.uk/term/genotype/</li>
+  </ul>
+</div>
+
+<div class="row">
+  <p>
+    There are a total of {{total_markers}} genotype markers for this species.
+  </p>
+  <div class="row">
+    <div class="col-md-2" style="text-align: start;">
+      {%if start_from > 0%}
+      <a href="{{url_for('species.populations.genotypes.list_genotypes',
+               species_id=species.SpeciesId,
+               population_id=population.Id,
+               start_from=start_from-count,
+               count=count)}}">
+        <span class="glyphicon glyphicon-backward"></span>
+        Previous
+      </a>
+      {%endif%}
+    </div>
+    <div class="col-md-8" style="text-align: center;">
+      Displaying markers {{start_from+1}} to {{start_from + count}} of
+      {{total_markers}}
+    </div>
+    <div class="col-md-2" style="text-align: end;">
+      {%if start_from + count < total_markers%}
+      <a href="{{url_for('species.populations.genotypes.list_genotypes',
+               species_id=species.SpeciesId,
+               population_id=population.Id,
+               start_from=start_from+count,
+               count=count)}}">
+        Next
+        <span class="glyphicon glyphicon-forward"></span>
+      </a>
+      {%endif%}
+    </div>
+  </div>
+  <table class="table">
+    <thead>
+      <tr>
+        <th title="">#</th>
+        <th title="">Marker Name</th>
+        <th title="Chromosome">Chr</th>
+        <th title="Physical location of the marker in megabasepairs">
+          Location (Mb)</th>
+        <th title="">Source</th>
+        <th title="">Source2</th>
+    </thead>
+
+    <tbody>
+      {%for marker in markers%}
+      <tr>
+        <td>{{marker.sequence_number}}</td>
+        <td>{{marker.Marker_Name}}</td>
+        <td>{{marker.Chr}}</td>
+        <td>{{marker.Mb}}</td>
+        <td>{{marker.Source}}</td>
+        <td>{{marker.Source2}}</td>
+      </tr>
+      {%else%}
+      <tr>
+        <td colspan="7" class="text-info">
+          <span class="glyphicon glyphicon-exclamation-sign"></span>
+          No markers to display!
+        </td>
+      </tr>
+      {%endfor%}
+    </tbody>
+  </table>
+</div>
+{%endblock%}
+
+{%block sidebarcontents%}
+{{display_population_card(species, population)}}
+{%endblock%}