db: Extend csv query to fetch case attributes

* gn3/db/traits.py (get_trait_csv_sample_data): Fetch case attribute data if
it exists.

1 files changed, 23 insertions, 16 deletions

diff --git a/gn3/db/traits.py b/gn3/db/traits.py
index f82beb0..5013844 100644
--- a/gn3/db/traits.py
+++ b/gn3/db/traits.py
@@ -80,23 +80,30 @@ def export_trait_data(
 
 
 def get_trait_csv_sample_data(conn: Any,
-                              trait_name: int, phenotype_id: int):
+                              trait_name: int, phenotype_id: int) -> str:
     """Fetch a trait and return it as a csv string"""
-    def __float_strip(num_str):
-        if str(num_str)[-2:] == ".0":
-            return str(int(num_str))
-        return str(num_str)
-    def __process_for_csv__(record):
-        return ",".join([
-            __float_strip(record[key]) if record[key] is not None else "x"
-            for key in ("sample_name", "value", "se_error", "nstrain")])
-    csv_data = ["Strain Name,Value,SE,Count"] + [
-        __process_for_csv__(record) for record in
-        retrieve_publish_trait_data(
-            {"trait_name": trait_name, "db": {"dataset_id": phenotype_id}},
-            conn)]
-    return "\n".join(csv_data)
-
+    __query = ("SELECT concat(st.Name, ',', ifnull(pd.value, 'x'), ',', "
+               "ifnull(ps.error, 'x'), ',', ifnull(ns.count, 'x')) as 'Data' "
+               "ifnull(ca.Name, 'x') as 'CaseAttr', "
+               "ifnull(cxref.value, 'x') as 'Value' "
+               "FROM PublishFreeze pf "
+               "JOIN PublishXRef px ON px.InbredSetId = pf.InbredSetId "
+               "JOIN PublishData pd ON pd.Id = px.DataId "
+               "JOIN Strain st ON pd.StrainId = st.Id "
+               "LEFT JOIN PublishSE ps ON ps.DataId = pd.Id "
+               "AND ps.StrainId = pd.StrainId "
+               "LEFT JOIN NStrain ns ON ns.DataId = pd.Id "
+               "AND ns.StrainId = pd.StrainId "
+               "LEFT JOIN CaseAttributeXRefNew cxref ON "
+               "(cxref.InbredSetId = px.InbredSetId AND "
+               "cxref.StrainId = st.Id) "
+               "LEFT JOIN CaseAttribute ca ON ca.Id = cxref.CaseAttributeId"
+               "WHERE px.Id = %s AND px.PhenotypeId = %s ORDER BY st.Name")
+    with conn.cursor() as cursor:
+        cursor.execute(__query, (trait_name, phenotype_id))
+        return ("Strain Name,Value,SE,Count\n" +
+                "\n".join(map(lambda x:x[0], cursor.fetchall())))
+    return "No Sample Data Found"
 
 def update_sample_data(conn: Any, #pylint: disable=[R0913]
                        trait_name: str,