diff options
| -rw-r--r-- | uploader/phenotypes/models.py | 48 | ||||
| -rw-r--r-- | uploader/phenotypes/views.py | 47 | ||||
| -rw-r--r-- | uploader/templates/phenotypes/view-dataset.html | 23 |
3 files changed, 98 insertions, 20 deletions
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> |