1 files changed, 88 insertions, 12 deletions

diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py
index 34d2cfe..41270da 100644
--- a/uploader/genotypes/models.py
+++ b/uploader/genotypes/models.py
@@ -1,13 +1,17 @@
 """Functions for handling genotypes."""
+import logging
 from typing import Optional
+from functools import reduce
 from datetime import datetime
 
 import MySQLdb as mdb
 from MySQLdb.cursors import Cursor, DictCursor
-from flask import current_app as app
 
 from gn_libs.mysqldb import debug_query
 
+logger = logging.getLogger(__name__)
+
+
 def genocode_by_population(
         conn: mdb.Connection, population_id: int) -> tuple[dict, ...]:
     """Get the allele/genotype codes."""
@@ -29,30 +33,102 @@ def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int:
 def genotype_markers(
         conn: mdb.Connection,
         species_id: int,
+        population_id: int,
         offset: int = 0,
-        limit: Optional[int] = None
+        limit: int = -1# no limit if negative, zero returns empty list.
 ) -> tuple[tuple[dict, ...], int]:
-    """Retrieve markers from the database."""
+    """Retrieve markers from the database.
+
+    Return: A tuple of:
+            - Listing of the markers,
+            - The total number of markers found in the system.
+    """
     _query_template = (
-        "SELECT %%COLS%% FROM Geno AS gno "
-        "WHERE gno.SpeciesId=%s "
+        "SELECT %%COLS%% "
+        "FROM Species AS spc "
+        "INNER JOIN InbredSet AS iset "
+        "ON spc.Id = iset.SpeciesId "
+        "INNER JOIN GenoFreeze AS gfr "
+        "ON iset.Id = gfr.InbredSetId "
+        "INNER JOIN GenoXRef AS gxr "
+        "ON gfr.Id = gxr.GenoFreezeId "
+        "INNER JOIN Geno AS gno "
+        "ON gxr.GenoId = gno.Id "
+        "WHERE spc.Id=%s "
+        "AND iset.Id=%s "
         "%%LIMIT%%")
 
     with conn.cursor(cursorclass=DictCursor) as cursor:
         cursor.execute(
             _query_template.replace("%%LIMIT%%", "").replace(
                 "%%COLS%%", "COUNT(gno.Id) AS total_records"),
-            (species_id,))
+            (species_id, population_id))
         _total_records = cursor.fetchone()["total_records"]
         cursor.execute(
-            _query_template.replace("%%COLS%%", "gno.*").replace(
+            _query_template.replace("%%COLS%%", "gno.*, gxr.cM").replace(
                 "%%LIMIT%%",
                 (f"LIMIT {int(limit)} OFFSET {int(offset)}"
-                 if bool(limit) and limit > 0
+                 if bool(limit) and limit >= 0
                  else "")),
-            (species_id,))
-        debug_query(cursor, app.logger)
-        return tuple(dict(row) for row in cursor.fetchall()), _total_records
+            (species_id, population_id))
+        debug_query(cursor, logger)
+        _records = tuple(dict(row) for row in cursor.fetchall())
+        return _records, _total_records
+
+
+def genotype_records(
+        conn: mdb.Connection,
+        species_id: int,
+        population_id: int,
+        offset: int = 0,
+        limit: int = -1# no limit if negative, zero returns empty list.
+) -> tuple[tuple[dict, ...], int]:
+    """Retrieve the actual genotype records from the database.
+
+    Returns: A tuple of:
+             - the listing of the genotype data,
+             - the total number of genotype records for this population.
+    """
+    def __organise_geno_records__(acc, row):
+        _current_row = acc.get(row["GenoId"], {
+            "GenoId": row["GenoId"],
+            "data": {}
+        })
+        _current_row["data"][row["StrainName"]] = row["value"]
+        return {
+            **acc,
+            _current_row["GenoId"]: _current_row
+        }
+
+    _query_template = (
+        "SELECT gxr.GenoId, gxr.DataId, gdt.value, strn.Name AS StrainName "
+        "FROM GenoXRef AS gxr "
+        "INNER JOIN GenoData AS gdt ON gxr.DataId = gdt.Id "
+        "INNER JOIN Strain AS strn ON gdt.StrainId = strn.Id "
+        "WHERE gxr.GenoId IN (%%PARAMS_STR%%)")
+
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        _markers, _num_records = genotype_markers(
+            conn, species_id, population_id, offset, limit)
+        if len(_markers) == 0:
+            return (tuple(), 0)
+
+        _genoids = tuple(_marker["Id"] for _marker in _markers)
+        cursor.execute(
+            _query_template.replace(
+                "%%PARAMS_STR%%", ",".join(["%s"] * len(_genoids))),
+            _genoids)
+        debug_query(cursor, logger)
+        _records: dict[str, dict] = reduce(
+            __organise_geno_records__, cursor.fetchall(), {})
+        return (
+            tuple({
+                **_marker,
+                "data": _records.get(
+                    _marker["Id"], {}
+                ).get("data", {})
+            } for _marker in _markers),
+            _num_records)
 
 
 def genotype_dataset(
@@ -77,7 +153,7 @@ def genotype_dataset(
 
     with conn.cursor(cursorclass=DictCursor) as cursor:
         cursor.execute(_query, _params)
-        debug_query(cursor, app.logger)
+        debug_query(cursor, logger)
         result = cursor.fetchone()
         if bool(result):
             return dict(result)