   end
 
   def species do
-    {:ok, rows} = DB.query("SELECT speciesid,name,fullname FROM Species")
-    for r <- rows, do: ( {id,name,fullname} = r; [id,name,fullname] )
+    query = from s in Species,
+      select: {s."SpeciesId", s."Name", s."FullName"}
+    Repo.all(query) |> Enum.map(&(Tuple.to_list &1))
   end
 
   def groups(species) do
-    subq =
+    {species_field, species_value} =
       case use_type(species) do
-        { :integer, i } -> "Species.id = #{i}"
-        { :string, s }  -> "Species.Name = '#{s}'"
+        { :integer, i } -> {:id, i}
+        { :string, s }  -> {:Name, s}
       end
 
     # note this query can be simplified
-    query = """
-SELECT distinct InbredSet.id,InbredSet.Name,InbredSet.FullName
-FROM InbredSet,Species,ProbeFreeze,GenoFreeze,PublishFreeze
-WHERE #{subq}
-and InbredSet.SpeciesId = Species.Id and InbredSet.Name != 'BXD300'
-and (PublishFreeze.InbredSetId = InbredSet.Id
-     or GenoFreeze.InbredSetId = InbredSet.Id
-     or ProbeFreeze.InbredSetId = InbredSet.Id)
-"""
-    {:ok, rows} = DB.query(query)
-    for r <- rows, do: ( {id,name,full_name} = r ; [id,name,full_name] )
+#     query = """
+# SELECT distinct InbredSet.id,InbredSet.Name,InbredSet.FullName
+# FROM InbredSet,Species,ProbeFreeze,GenoFreeze,PublishFreeze
+# WHERE #{subq}
+# and InbredSet.SpeciesId = Species.Id and InbredSet.Name != 'BXD300'
+# and (PublishFreeze.InbredSetId = InbredSet.Id
+#      or GenoFreeze.InbredSetId = InbredSet.Id
+#      or ProbeFreeze.InbredSetId = InbredSet.Id)
+# """
+    query = from species in Species,
+      join: inbredset in InbredSet,
+      on: species.id == inbredset."SpeciesId",
+      left_join: publishfreeze in PublishFreeze,
+      on: publishfreeze."InbredSetId" == inbredset.id,
+      left_join: genofreeze in GenoFreeze,
+      on: genofreeze."InbredSetId" == inbredset.id,
+      left_join: probefreeze in ProbeFreeze,
+      on: probefreeze."InbredSetId" == inbredset.id,
+      where: field(species, ^species_field) == ^species_value and inbredset."Name" != "BXD300",
+      select: {inbredset.id,inbredset."Name",inbredset."FullName"},
+      distinct: true
+
+    Repo.all(query) |> Enum.map(&(Tuple.to_list &1))
   end
 
   def group_info(group) do
-    subq =
+    {inbredset_field, inbredset_value} =
       case use_type(group) do
-        { :integer, i } -> "C.id = #{i}"
-        { :string, s }  -> "C.Name = '#{s}'"
-      end
-    query = "
-SELECT DISTINCT Species.speciesid,Species.Name,C.InbredSetid,C.name,C.mappingmethodid,C.genetictype
-FROM Species, InbredSet as C
-WHERE #{subq} and C.SpeciesId = Species.Id"
-    {:ok, rows} = DB.query(query)
-    for r <- rows, do: ( {species_id,species,group_id,group_name,method_id,genetic_type} = r; [group_id,group_name,species_id,species,method_id,genetic_type] )
+        { :integer, i } -> {:id, i}
+        { :string, s }  -> {:Name, s}
+      end
+#     query = "
+# SELECT DISTINCT Species.speciesid,Species.Name,C.InbredSetid,C.name,C.mappingmethodid,C.genetictype
+# FROM Species, InbredSet as C
+# WHERE #{subq} and C.SpeciesId = Species.Id"
+    query = from species in Species,
+      join: inbredset in InbredSet,
+      on: species.id == inbredset."SpeciesId",
+      where: field(inbredset, ^inbredset_field) == ^inbredset_value,
+      select: {species."SpeciesId", species."Name", inbredset."InbredSetId", inbredset."Name", inbredset."MappingMethodId", inbredset."GeneticType"},
+      distinct: true
+    # for r <- rows, do: ( {species_id,species,group_id,group_name,method_id,genetic_type} = r; [group_id,group_name,species_id,species,method_id,genetic_type] )
+    Repo.all(query) |> Enum.map(&(Tuple.to_list(&1)))
   end
 
   def chr_info(dataset_name) do
-    subq =
+    {inbredset_field, inbredset_value} =
       case use_type(dataset_name) do
-        { :integer, i } -> "C.id = #{i}"
-        { :string, s }  -> "C.Name = '#{s}'"
-      end
-
-      query = """
-SELECT Chr_Length.Name, Length
-FROM Chr_Length, InbredSet as C
-WHERE #{subq}
-AND Chr_Length.SpeciesId = C.SpeciesId
-ORDER BY Chr_Length.OrderId
-      """
-    {:ok, rows} = DB.query(query)
-    for r <- rows, do: ( {chr_name,chr_len} = r; [chr_name,chr_len] )
+        { :integer, i } -> {:id, i}
+        { :string, s }  -> {:Name, s}
+      end
+
+    query = from chr_length in Chr_Length,
+      join: inbredset in InbredSet,
+      on: chr_length."SpeciesId" == inbredset."SpeciesId",
+      where: field(inbredset, ^inbredset_field) == ^inbredset_value,
+      select: {chr_length."Name", chr_length."Length"},
+      order_by: chr_length."OrderId"
+
+    Repo.all(query) |> Enum.map(&(Tuple.to_list(&1)))
+#       query = """
+# SELECT Chr_Length.Name, Length
+# FROM Chr_Length, InbredSet as C
+# WHERE #{subq}
+# AND Chr_Length.SpeciesId = C.SpeciesId
+# ORDER BY Chr_Length.OrderId
+#       """
+#     {:ok, rows} = DB.query(query)
+#     for r <- rows, do: ( {chr_name,chr_len} = r; [chr_name,chr_len] )
   end
 
   def datasets(group) do
     authorize_group(group)
-    query = """
-SELECT DISTINCT D.Id,D.Name,D.FullName
-FROM ProbeSetFreeze AS D, ProbeFreeze as D2, InbredSet, Tissue, Species
-WHERE
-    InbredSet.Name = '#{group}' and
-    D.ProbeFreezeId = D2.Id
-    AND D2.TissueId = Tissue.Id
-    AND D2.InbredSetId = InbredSet.Id
-    AND D.confidentiality < 1
-    AND D.public > 0
-"""
-    {:ok, rows} = DB.query(query)
-    for r <- rows, do: ( {id,name,full_name} = r ; [id,name,full_name] )
+#     query = """
+# SELECT DISTINCT D.Id,D.Name,D.FullName
+# FROM ProbeSetFreeze AS D, ProbeFreeze as D2, InbredSet, Tissue, Species
+# WHERE
+#     InbredSet.Name = '#{group}' and
+#     D.ProbeFreezeId = D2.Id
+#     AND D2.TissueId = Tissue.Id
+#     AND D2.InbredSetId = InbredSet.Id
+#     AND D.confidentiality < 1
+#     AND D.public > 0
+# """
+    query = from probesetfreeze in ProbeSetFreeze,
+      join: probefreeze in ProbeFreeze,
+      on: probesetfreeze."ProbeFreezeId" == probefreeze."Id",
+      join: tissue in Tissue,
+      on: probefreeze."TissueId" == tissue."Id",
+      join: inbredset in InbredSet,
+      on:  probefreeze."InbredSetId" == inbredset."Id",
+      where: inbredset."Name" == ^group and probesetfreeze.confidentiality < 1 and probesetfreeze.public > 0,
+      select: {probesetfreeze.id, probesetfreeze."Name", probesetfreeze."FullName"},
+      distinct: true
+
+      Repo.all(query) |> Enum.map(&(Tuple.to_list(&1)))
+
+    # {:ok, rows} = DB.query(query)
+    # for r <- rows, do: ( {id,name,full_name} = r ; [id,name,full_name] )
   end
 
   def dataset_info(dataset_name) do
     authorize_dataset(dataset_name)
-    subq =
+    {probesetfreeze_field, probesetfreeze_value} =
       case use_type(dataset_name) do
-        { :integer, i } -> "D.id = #{i}"
-        { :string, s }  -> "D.Name = '#{s}'"
-      end
-
-    query = """
-SELECT D.Id, D.Name, D.FullName, D.ShortName, D.DataScale, D2.TissueId, Tissue.Name, D.public, D.confidentiality
-FROM ProbeSetFreeze as D, ProbeFreeze as D2, Tissue
-WHERE #{subq}
-    AND D.public > 0
-    AND D.ProbeFreezeId = D2.Id
-    AND D2.TissueId = Tissue.Id
-    """
-    {:ok, rows} = DB.query(query)
-    for r <- rows, do: ( {id,name,full_name,short_name,data_scale,tissue_id,tissue_name,public,confidential} = r; [id,name,full_name,short_name,data_scale,tissue_id,tissue_name,public,confidential] )
+        { :integer, i } -> {:id, i}
+        { :string, s }  -> {:Name, s}
+      end
+
+#     query = """
+# SELECT D.Id, D.Name, D.FullName, D.ShortName, D.DataScale, D2.TissueId, Tissue.Name, D.public, D.confidentiality
+# FROM ProbeSetFreeze as D, ProbeFreeze as D2, Tissue
+# WHERE #{subq}
+#     AND D.public > 0
+#     AND D.ProbeFreezeId = D2.Id
+#     AND D2.TissueId = Tissue.Id
+#     """
+#     {:ok, rows} = DB.query(query)
+#     for r <- rows, do: ( {id,name,full_name,short_name,data_scale,tissue_id,tissue_name,public,confidential} = r; [id,name,full_name,short_name,data_scale,tissue_id,tissue_name,public,confidential] )
+
+    query = from probesetfreeze in ProbeSetFreeze,
+      join: probefreeze in ProbeFreeze,
+      on: probesetfreeze."ProbeFreezeId" == probefreeze."Id",
+      join: tissue in Tissue,
+      on: probefreeze."TissueId" == tissue."Id",
+      where: field(probesetfreeze, ^probesetfreeze_field) == ^probesetfreeze_value and probesetfreeze.public > 0,
+      select: {probesetfreeze.id, probesetfreeze."Name", probesetfreeze."FullName",
+               probesetfreeze."ShortName", probesetfreeze."DataScale", probefreeze."TissueId",
+               tissue."Name", probesetfreeze.public, probesetfreeze.confidentiality}
+    Repo.all(query) |> Enum.map(&(Tuple.to_list(&1)))
   end
 
   def phenotypes(dataset_name, start, stop) do

                       (PublishFreeze.InbredSetId = InbredSet.Id
                        or GenoFreeze.InbredSetId = InbredSet.Id
                        or ProbeFreeze.InbredSetId = InbredSet.Id)
                        group by InbredSet.Name
                        order by InbredSet.Name
"""
    {:ok, rows} = DB.query(query)
    for r <- rows, do: ( {id,name,fullname} = r; [id,name,fullname] )
  end

  def menu_types(species, group) do
    query = """
    select distinct Tissue.Name
    from ProbeFreeze,ProbeSetFreeze,InbredSet,Tissue,Species
    where Species.Name = '#{species}' and Species.Id = InbredSet.SpeciesId and
      InbredSet.Name = '#{group}' and
      ProbeFreeze.TissueId = Tissue.Id and
      ProbeFreeze.InbredSetId = InbredSet.Id and
      ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and
      ProbeSetFreeze.public > 0
      order by Tissue.Name
    """
    {:ok, rows} = DB.query(query)
    for r <- rows, do: ( {tissue} = r; [tissue] )
  end

  def menu_datasets(species, group, type) do
    query = """
    select ProbeSetFreeze.Id,ProbeSetFreeze.Name,ProbeSetFreeze.FullName
    from ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue, Species
    where
    Species.Name = '#{species}' and Species.Id = InbredSet.SpeciesId and
    InbredSet.Name = '#{group}' and
    ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and
    Tissue.Name = '#{type}' and
    ProbeFreeze.TissueId = Tissue.Id and ProbeFreeze.InbredSetId = InbredSet.Id and
    ProbeSetFreeze.confidentiality < 1 and ProbeSetFreeze.public > 0 order by
    ProbeSetFreeze.CreateTime desc
    """
    {:ok, rows} = DB.query(query)
    for r <- rows, do: ( {id,name,fullname} = r; [id,name,fullname] )
  end

end

      end

    limit = stop2 - start2 + 1
#     query = """
# SELECT distinct ProbeSet.Name,
#   ProbeSetXRef.Mean, ProbeSetXRef.LRS,
#   ProbeSetXRef.PVALUE, ProbeSetXRef.additive, ProbeSetXRef.locus, ProbeSet.Chr_num,
#   ProbeSet.Mb, ProbeSet.Symbol,
#   ProbeSet.name_num
# FROM ProbeSetXRef, ProbeSet
# WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId
#   and ProbeSetXRef.ProbeSetFreezeId = #{dataset_id}
#   ORDER BY ProbeSet.symbol ASC LIMIT #{limit}
#     """
#     {:ok, rows} = DB.query(query)
    # for r <- rows, do: ( {name,mean,lrs,pvalue,additive,locus,chr,mb,symbol,name_num} = r ;
    #   %{ name: name,
    #      name_id: name_num,
    #      mean: mean,
    #      "MAX_LRS": lrs,
    #      "p_value": pvalue,
    #      additive: additive,
    #      locus: locus,
    #      chr: chr,
    #      "Mb": mb,
    #      symbol: symbol
    #   })

    query = from probeset in ProbeSet,
      join: probesetxref in ProbeSetXRef,
      on: probeset.id == probesetxref."ProbeSetId",
      where: probesetxref."ProbeSetFreezeId" == ^dataset_id,
      select: {probeset."Name", probesetxref.mean, probesetxref."LRS",
               probesetxref.pValue, probesetxref.additive, probesetxref."Locus",
               probeset.chr_num, probeset."Mb", probeset."Symbol", probeset.name_num},
      distinct: true,
      order_by: probeset."symbol",
      limit: ^limit

    from_tuple_to_structure = fn(query_result) ->
      {name,mean,lrs,pvalue,additive,locus,chr,mb,symbol,name_num} = query_result
      %{ name: name,
         name_id: name_num,
         mean: mean,
