merge changes

1 files changed, 217 insertions, 0 deletions

diff --git a/gn2/wqflask/api/gen_menu.py b/gn2/wqflask/api/gen_menu.py
new file mode 100644
index 00000000..45d5739e
--- /dev/null
+++ b/gn2/wqflask/api/gen_menu.py
@@ -0,0 +1,217 @@
+from gn3.db.species import get_all_species
+
+def gen_dropdown_json(conn):
+    """Generates and outputs (as json file) the data for the main dropdown menus on
+    the home page
+    """
+    species = get_all_species(conn)
+    groups = get_groups(species, conn)
+    types = get_types(groups, conn)
+    datasets = get_datasets(types, conn)
+    return dict(species=species,
+                groups=groups,
+                types=types,
+                datasets=datasets)
+
+
+def get_groups(species, conn):
+    """Build groups list"""
+    groups = {}
+    with conn.cursor() as cursor:
+        for species_name, _species_full_name in species:
+            groups[species_name] = []
+            query = ("SELECT InbredSet.Name, InbredSet.FullName, "
+                 "IFNULL(InbredSet.Family, 'None') "
+                 "FROM InbredSet, Species WHERE Species.Name = '{}' "
+                 "AND InbredSet.SpeciesId = Species.Id GROUP by "
+                 "InbredSet.Name ORDER BY IFNULL(InbredSet.FamilyOrder, "
+                 "InbredSet.FullName) ASC, IFNULL(InbredSet.Family, "
+                 "InbredSet.FullName) ASC, InbredSet.FullName ASC, "
+                 "InbredSet.MenuOrderId ASC").format(species_name)
+            cursor.execute(query)
+            results = cursor.fetchall()
+            for result in results:
+                family_name = "Family:" + str(result[2])
+                groups[species_name].append(
+                    [str(result[0]), str(result[1]), family_name])
+    return groups
+
+
+def get_types(groups, conn):
+    """Build types list"""
+    types = {}
+
+    for species, group_dict in list(groups.items()):
+        types[species] = {}
+        for group_name, _group_full_name, _family_name in group_dict:
+            if phenotypes_exist(group_name, conn):
+                types[species][group_name] = [
+                    ("Phenotypes", "Traits and Cofactors", "Phenotypes")]
+            if genotypes_exist(group_name, conn):
+                if group_name in types[species]:
+                    types[species][group_name] += [
+                        ("Genotypes", "DNA Markers and SNPs", "Genotypes")]
+                else:
+                    types[species][group_name] = [
+                        ("Genotypes", "DNA Markers and SNPs", "Genotypes")]
+            if group_name in types[species]:
+                types_list = build_types(species, group_name, conn)
+                if len(types_list) > 0:
+                    types[species][group_name] += types_list
+            else:
+                types_list = build_types(species, group_name, conn)
+                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, conn):
+    results = []
+    with conn.cursor() as cursor:
+        cursor.execute(
+            ("SELECT Name FROM PublishFreeze "
+             "WHERE PublishFreeze.Name = "
+             "'{}'").format(group_name + "Publish"))
+        results = cursor.fetchone()
+    return bool(results)
+
+
+def genotypes_exist(group_name, conn):
+    with conn.cursor() as cursor:
+        cursor.execute(
+            ("SELECT Name FROM GenoFreeze " +
+             "WHERE GenoFreeze.Name = '{}'").format(
+                 group_name + "Geno"))
+        results = cursor.fetchone()
+        return bool(results)
+
+
+def build_types(species, group, conn):
+    """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 "
+             "ORDER BY Tissue.Name").format(species, group)
+
+    results = []
+    with conn.cursor() as cursor:
+        cursor.execute(query)
+        for result in cursor.fetchall():
+            if bool(result):
+                these_datasets = build_datasets(species,
+                                                group, result[0], conn)
+                if len(these_datasets) > 0:
+                    results.append([str(result[0]), str(result[0]),
+                                    "Molecular Traits"])
+    return results
+
+
+def get_datasets(types, conn):
+    """Build datasets list"""
+    datasets = {}
+    for species, group_dict in list(types.items()):
+        datasets[species] = {}
+        for group, type_list in list(group_dict.items()):
+            datasets[species][group] = {}
+            for type_name in type_list:
+                these_datasets = build_datasets(species, group,
+                                                type_name[0], conn)
+                if bool(these_datasets):
+                    datasets[species][group][type_name[0]] = these_datasets
+
+    return datasets
+
+
+def build_datasets(species, group, type_name, conn):
+    """Gets dataset names from database"""
+    dataset_text = dataset_value = None
+    datasets = []
+    with conn.cursor() as cursor:
+        if type_name == "Phenotypes":
+            cursor.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))
+            results = cursor.fetchall()
+            if bool(results):
+                for result in results:
+                    dataset_id = str(result[0])
+                    dataset_value = str(result[1])
+                    dataset_text = str(result[2])
+                    if group == 'MDP':
+                        dataset_text = "Mouse Phenome Database"
+
+                    datasets.append([dataset_id, dataset_value, dataset_text])
+            else:
+                cursor.execute(
+                    ("SELECT PublishFreeze.Name, PublishFreeze.FullName "
+                     "FROM PublishFreeze, InbredSet "
+                     "WHERE InbredSet.Name = '{}' AND "
+                     "PublishFreeze.InbredSetId = InbredSet.Id "
+                     "ORDER BY PublishFreeze.CreateTime ASC")
+                    .format(group))
+                result = cursor.fetchone()
+                dataset_id = "None"
+                dataset_value = str(result[0])
+                dataset_text = str(result[1])
+                datasets.append([dataset_id, dataset_value, dataset_text])
+
+        elif type_name == "Genotypes":
+            cursor.execute(
+                ("SELECT InfoFiles.GN_AccesionId "
+                 "FROM InfoFiles, GenoFreeze, InbredSet "
+                 "WHERE InbredSet.Name = '{}' AND "
+                 "GenoFreeze.InbredSetId = InbredSet.Id AND "
+                 "InfoFiles.InfoPageName = GenoFreeze.ShortName "
+                 "ORDER BY GenoFreeze.CreateTime "
+                 "DESC").format(group))
+            results = cursor.fetchone()
+            dataset_id = "None"
+            if bool(results):
+                dataset_id = str(results[0])
+
+            dataset_value = "%sGeno" % group
+            dataset_text = "%s Genotypes" % group
+            datasets.append([dataset_id, dataset_value, dataset_text])
+
+        else:  # for mRNA expression/ProbeSet
+            cursor.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.public > 0 "
+                 "ORDER BY -ProbeSetFreeze.OrderList DESC, "
+                 "ProbeSetFreeze.CreateTime "
+                 "DESC").format(species, group, type_name))
+            results = cursor.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