Added dropdown generation to REST API and switched to using it to generate the main search page dropdown

3 files changed, 243 insertions, 2 deletions

diff --git a/wqflask/wqflask/api/gen_menu.py b/wqflask/wqflask/api/gen_menu.py
new file mode 100644
index 00000000..a2faa463
--- /dev/null
+++ b/wqflask/wqflask/api/gen_menu.py
@@ -0,0 +1,231 @@
+from __future__ import print_function, division
+
+import sys
+
+from flask import g
+
+from utility.tools import locate, locate_ignore_error, TEMPDIR, SQL_URI
+
+import MySQLdb
+
+import urlparse
+
+import utility.logger
+logger = utility.logger.getLogger(__name__ )
+
+def gen_dropdown_json():
+    """Generates and outputs (as json file) the data for the main dropdown menus on the home page"""
+
+    species = get_species()
+    groups = get_groups(species)
+    types = get_types(groups)
+    datasets = get_datasets(types)
+
+    species.append(('All Species', 'All Species'))
+    groups['All Species'] = [('All Groups', 'All Groups')]
+    types['All Species'] = {}
+    types['All Species']['All Groups'] = [('Phenotypes', 'Phenotypes')]
+    datasets['All Species'] = {}
+    datasets['All Species']['All Groups'] = {}
+    datasets['All Species']['All Groups']['Phenotypes'] = [('All Phenotypes','All Phenotypes')]
+
+    data = dict(species=species,
+                groups=groups,
+                types=types,
+                datasets=datasets)
+
+    return data
+
+def get_species():
+    """Build species list"""
+    results = g.db.execute("""SELECT Name, MenuName
+                              FROM Species
+                              WHERE Species.Name != 'macaque monkey'
+                              ORDER BY OrderId""").fetchall()
+
+    species = []
+    for result in results:
+        species.append([str(result[0]), str(result[1])])
+
+    return species
+
+def get_groups(species):
+    """Build groups list"""
+    groups = {}
+    for species_name, _species_full_name in species:
+        groups[species_name] = []
+        results = g.db.execute("""SELECT InbredSet.Name, InbredSet.FullName
+                                  FROM InbredSet, Species, ProbeFreeze, GenoFreeze, PublishFreeze
+                                  WHERE Species.Name = '{}' AND
+                                        InbredSet.SpeciesId = Species.Id AND
+                                        (PublishFreeze.InbredSetId = InbredSet.Id OR
+                                         GenoFreeze.InbredSetId = InbredSet.Id OR
+                                         ProbeFreeze.InbredSetId = InbredSet.Id)
+                                  GROUP by InbredSet.Name
+                                  ORDER BY InbredSet.FullName""".format(species_name)).fetchall()
+
+        for result in results:
+            groups[species_name].append([str(result[0]), str(result[1])])
+
+    return groups
+
+def get_types(groups):
+    """Build types list"""
+    types = {}
+
+    for species, group_dict in groups.iteritems():
+        types[species] = {}
+        for group_name, _group_full_name in group_dict:
+            if phenotypes_exist(group_name):
+                types[species][group_name] = [("Phenotypes", "Phenotypes")]
+            if genotypes_exist(group_name):
+                if group_name in types[species]:
+                    types[species][group_name] += [("Genotypes", "Genotypes")]
+                else:
+                    types[species][group_name] = [("Genotypes", "Genotypes")]
+            if group_name in types[species]:
+                types_list = build_types(species, group_name)
+                if len(types_list) > 0:
+                    types[species][group_name] += types_list
+                else:
+                    if not phenotypes_exist(group_name) and not genotypes_exist(group_name):
+                        types[species].pop(group_name, None)
+                        groups[species] = list(group for group in groups[species] if group[0] != group_name)
+            else: #ZS: This whole else statement might be unnecessary, need to check
+                types_list = build_types(species, group_name)
+                if len(types_list) > 0:
+                    types[species][group_name] = types_list
+                else:
+                    types[species].pop(group_name, None)
+                    groups[species] = list(group for group in groups[species] if group[0] != group_name)
+    return types
+
+def phenotypes_exist(group_name):
+    results = g.db.execute("""SELECT Name
+                              FROM PublishFreeze
+                              WHERE PublishFreeze.Name = '{}'""".format(group_name+"Publish")).fetchone()
+
+    if results != None:
+        return True
+    else:
+        return False
+
+def genotypes_exist(group_name):
+    results = g.db.execute("""SELECT Name
+                              FROM GenoFreeze
+                              WHERE GenoFreeze.Name = '{}'""".format(group_name+"Geno")).fetchone()
+
+    if results != None:
+        return True
+    else:
+        return False
+
+def build_types(species, group):
+    """Fetches tissues
+
+    Gets the tissues with data for this species/group
+    (all types except phenotype/genotype are tissues)
+
+    """
+
+    query = """SELECT DISTINCT Tissue.Name
+               FROM ProbeFreeze, ProbeSetFreeze, InbredSet, Tissue, Species
+               WHERE Species.Name = '{0}' AND
+                     Species.Id = InbredSet.SpeciesId AND
+                     InbredSet.Name = '{1}' AND
+                     ProbeFreeze.TissueId = Tissue.Id AND
+                     ProbeFreeze.InbredSetId = InbredSet.Id AND
+                     ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND
+                     ProbeSetFreeze.public > 0 AND
+                     ProbeSetFreeze.confidentiality < 1
+               ORDER BY Tissue.Name""".format(species, group)
+
+    results = []
+    for result in g.db.execute(query).fetchall():
+        if len(result):
+            these_datasets = build_datasets(species, group, result[0])
+            if len(these_datasets) > 0:
+                results.append([str(result[0]), str(result[0])])
+
+    return results
+
+def get_datasets(types):
+    """Build datasets list"""
+    datasets = {}
+    for species, group_dict in types.iteritems():
+        datasets[species] = {}
+        for group, type_list in group_dict.iteritems():
+            datasets[species][group] = {}
+            for type_name in type_list:
+                these_datasets = build_datasets(species, group, type_name[0])
+                if len(these_datasets) > 0:
+                    datasets[species][group][type_name[0]] = these_datasets
+
+    return datasets
+
+
+def build_datasets(species, group, type_name):
+    """Gets dataset names from database"""
+    dataset_text = dataset_value = None
+    datasets = []
+    if type_name == "Phenotypes":
+        results = g.db.execute("""SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name, PublishFreeze.FullName
+                                  FROM InfoFiles, PublishFreeze, InbredSet
+                                  WHERE InbredSet.Name = '{}' AND
+                                        PublishFreeze.InbredSetId = InbredSet.Id AND
+                                        InfoFiles.InfoPageName = PublishFreeze.Name
+                                  ORDER BY PublishFreeze.CreateTime ASC""".format(group)).fetchall()
+
+        if len(results) > 0:
+            for result in results:
+                dataset_id = str(result[0])
+                dataset_value = str(result[1])
+                if group == 'MDP':
+                    dataset_text = "Mouse Phenome Database"
+                else:
+                    #dataset_text = "%s Phenotypes" % group
+                    dataset_text = str(result[2])
+                datasets.append([dataset_id, dataset_value, dataset_text])
+        else:
+            dataset_id = "None"
+            dataset_value = "%sPublish" % group
+            dataset_text = "%s Phenotypes" % group
+            datasets.append([dataset_id, dataset_value, dataset_text])
+
+    elif type_name == "Genotypes":
+        results = g.db.execute("""SELECT InfoFiles.GN_AccesionId
+                                  FROM InfoFiles, GenoFreeze, InbredSet
+                                  WHERE InbredSet.Name = '{}' AND
+                                        GenoFreeze.InbredSetId = InbredSet.Id AND
+                                        InfoFiles.InfoPageName = GenoFreeze.ShortName AND
+                                        GenoFreeze.public > 0 AND
+                                        GenoFreeze.confidentiality < 1
+                                  ORDER BY GenoFreeze.CreateTime DESC""".format(group)).fetchone()
+
+        if results != None:
+            dataset_id = str(results[0])
+        else:
+            dataset_id = "None"
+        dataset_value = "%sGeno" % group
+        dataset_text = "%s Genotypes" % group
+        datasets.append([dataset_id, dataset_value, dataset_text])
+
+    else: # for mRNA expression/ProbeSet
+        results = g.db.execute("""SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName
+                                  FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue, Species
+                                  WHERE Species.Name = '{0}' AND
+                                        Species.Id = InbredSet.SpeciesId AND
+                                        InbredSet.Name = '{1}' AND
+                                        ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and Tissue.Name = '{2}' AND
+                                        ProbeFreeze.TissueId = Tissue.Id and ProbeFreeze.InbredSetId = InbredSet.Id AND
+                                        ProbeSetFreeze.confidentiality < 1 and ProbeSetFreeze.public > 0
+                                  ORDER BY ProbeSetFreeze.CreateTime DESC""".format(species, group, type_name)).fetchall()
+
+        datasets = []
+        for dataset_info in results:
+            this_dataset_info = []
+            for info in dataset_info:
+                this_dataset_info.append(str(info))
+            datasets.append(this_dataset_info)
+
+    return datasets
\ No newline at end of file
diff --git a/wqflask/wqflask/api/router.py b/wqflask/wqflask/api/router.py
index 1a948b1a..8ec93e48 100644
--- a/wqflask/wqflask/api/router.py
+++ b/wqflask/wqflask/api/router.py
@@ -11,7 +11,7 @@ from flask import g, Response, request, make_response, render_template, send_fro
 import sqlalchemy
 from wqflask import app
 
-from wqflask.api import correlation, mapping
+from wqflask.api import correlation, mapping, gen_menu
 
 from utility.tools import flat_files
 
@@ -746,6 +746,15 @@ def get_genotypes(group_name, file_format="csv"):
 
     return output
 
+@app.route("/api/v_{}/gen_dropdown".format(version), methods=("GET",))
+def gen_dropdown_menu():
+    results = gen_menu.gen_dropdown_json()
+
+    if len(results) > 0:
+        return flask.jsonify(results)
+    else:
+        return return_error(code=500, source=request.url_rule.rule, title="Some error occurred", details="")
+
 def return_error(code, source, title, details):
     json_ob = {"errors": [
         {
diff --git a/wqflask/wqflask/static/new/javascript/dataset_select_menu_orig.js b/wqflask/wqflask/static/new/javascript/dataset_select_menu_orig.js
index 9bca1517..b80539bd 100644
--- a/wqflask/wqflask/static/new/javascript/dataset_select_menu_orig.js
+++ b/wqflask/wqflask/static/new/javascript/dataset_select_menu_orig.js
@@ -6,10 +6,11 @@ process_json = function(data) {
     return apply_default();
   }
 };
-$.ajax('/static/new/javascript/dataset_menu_structure.json', {
+$.ajax('/api/v_pre1/gen_dropdown', {
   dataType: 'json',
   success: process_json
 });
+
 populate_species = function() {
   var species_list;
   species_list = this.jdata.species;