private async Task <DataTable> ExportSampleTable(DataExportObject exportData, ExportFileDefinition fileDefinition) // FEATURE BY FEATURE MAIN QUICK
{
return(await Task.Factory.StartNew(() =>
{
var datatable = new DataTable();
datatable.TableName = fileDefinition.Name;
foreach (var field in fileDefinition.Fields)
{
datatable.Columns.Add(field.ColumnHeader.ToLower());
}
foreach (var biosample in exportData.Samples)
{
var row = datatable.NewRow();
row["subjectid"] = biosample.Subject.UniqueSubjectId;
row["sampleid"] = biosample.BiosampleStudyId;
foreach (var samplePropField in fileDefinition.Fields)
{
var charVal = _queryService.GetSubjectOrSampleProperty(biosample, samplePropField.QueryObject);
if (charVal != null)
{
row[samplePropField.ColumnHeader.ToLower()] = charVal;
}
}
datatable.Rows.Add(row);
}
return datatable;
}));
}
private async Task <FileInfo> ExportAssayTable(DataExportObject exportData, ExportFileDefinition fileDefinition, string filePath) // FEATURE BY FEATURE MAIN QUICK
{
return(await Task.Factory.StartNew(() =>
{
var projectId = fileDefinition.ProjectId;
var activityId = exportData.Samples.First().AssayId;
// GET sampleIds, observations, and features
var sampleIds = exportData.Samples.OrderBy(o => o.BiosampleStudyId).Select(s => s.BiosampleStudyId).ToList();
var assayObservations = _queryService.GetAssayObservations(projectId, activityId, sampleIds);
var orderObservations = assayObservations.OrderBy(o => o.FeatureName).ThenBy(o => o.SubjectOfObservationName);
//////var features = orderObservations.Select(a => a.FeatureName).Distinct().ToList();
//////var values = orderObservations.Select(c => ((NumericalValue)c.ObservedValue).Value);
var features = orderObservations.Select(a => a.FeatureName).Distinct().ToList();
var values = orderObservations.Select(a => a.Value).ToList();
var samples = orderObservations.Select(a => a.SubjectOfObservationName).Distinct().ToList();
StreamWriter writer = File.CreateText(filePath);
// First write the header (which are samples' Ids)
writer.WriteLine("features" + "," + string.Join(",", samples));
var sb = new StringBuilder();
var i = 0;
var j = 0;
foreach (var value in values)
{
i++;
sb.Append(value + ",");
if (i == exportData.Samples.Count)
{
writer.WriteLine(features[j] + "," + sb);
sb.Clear();
i = 0;
writer.Flush();
j++;
}
}
writer.Dispose();
return new FileInfo(filePath);
}));
}
private void _getObservations(CombinedQuery combinedQuery, ref DataExportObject queryResult)
{
List <SdtmRow> observations = new List <SdtmRow>();
HashSet <string> ObsFilteredSubjectIds = new HashSet <string>();
//GROUP CLINICAL OBSERVATIONS (GROUP AND INDIVIDUAL)
var obsQueries = combinedQuery.ClinicalObservations.Union(combinedQuery.GroupedObservations).ToList();
//GROUP CLINICAL OBSERVATIONS BY O3 TO QUERY FOR THE OBSERVATION (E.G. HEADACHE ONLY ONCE EVEN IF TWO REQUEST ARE MADE (e.g. AEOCCUR and AESEV)
var queriesByO3Id = obsQueries.GroupBy(f => f.QueryObjectName).ToList();
foreach (var sameO3queries in queriesByO3Id)
{
var o3q_g = sameO3queries.Select(group => group).First();
var o3q_list = new List <ObservationQuery>();
//EXPANIDNG ALL to GROUP TO ACCOMMODATE FOR GROUP OBSERVATIONS AS WELL IN THE SAME CODE BLOCK
if (o3q_g.GetType().Name == nameof(GroupedObservationsQuery))
{
((GroupedObservationsQuery)o3q_g).GroupedObservations.ForEach(oq => o3q_list.Add(oq));
}
else
{
o3q_list.Add(o3q_g);
}
//////////////////////////////////////////////////////////////////////////////////////////////
var obsQueryResult = new List <SdtmRow>();
//QUERYING FOR OBSERVATIONS
foreach (var o3q in o3q_list)
{
var _observations = o3q.IsOntologyEntry
? _sdtmRepository.FindAll(s => s.QualifierQualifiers[o3q.TermCategory] == o3q.TermId.ToString() && s.Group == o3q.Group && s.ProjectId == o3q.ProjectId).ToList()
: _sdtmRepository.FindAll(s => s.DBTopicId == o3q.TermId && s.ProjectId == o3q.ProjectId).ToList();
obsQueryResult.AddRange(_observations);
if (_observations.Count > queryResult.Subjects.Count)
{
queryResult.HasLongitudinalData = true;
}
}
//FILTERING OBSERVATIONS
foreach (var oq in sameO3queries) //AEOCCUR / AESEV
{
if (!oq.IsFiltered)
{
continue;
}
//HACK FOR FINDINGS SHOULD GO AWAY IN THE NEW OBSERVATION MODEL
obsQueryResult.ForEach(o => o.Qualifiers = o.ResultQualifiers.Union(o.Qualifiers).ToDictionary(p => p.Key, p => p.Value));
string v = null;
float vn;
obsQueryResult = oq.DataType == "string"
? obsQueryResult.FindAll(
s => s.Qualifiers.TryGetValue(oq.PropertyName, out v) &&
oq.FilterExactValues.Contains(s.Qualifiers[oq.PropertyName]))
: obsQueryResult.FindAll(
s => float.TryParse(s.Qualifiers[oq.PropertyName], out vn) &&
vn >= oq.FilterRangeFrom &&
vn <= oq.FilterRangeTo);
var filteredSubjectIds = obsQueryResult.Select(o => o.USubjId).Distinct().ToList();
if (!ObsFilteredSubjectIds.Any())
{
ObsFilteredSubjectIds.UnionWith(filteredSubjectIds);
}
ObsFilteredSubjectIds.IntersectWith(filteredSubjectIds);
queryResult.ObservationsFiltered = true;
}
observations.AddRange(obsQueryResult);
}
//CASCADE FILTERING OF OBERVATIONS FROM ALL FILTERS ON OBSERVATIONS
if (queryResult.ObservationsFiltered)
{
observations = observations.FindAll(o => ObsFilteredSubjectIds.Contains(o.USubjId));
}
queryResult.Observations = observations;
}
public DataExportObject GetQueryResult(Guid combinedQueryId)
{
var combinedQuery = _combinedQueryRepository.Get(combinedQueryId);
var queryResult = new DataExportObject();
var projectId = combinedQuery.ProjectId;
//TODO: the list of included proerties should reflect the selected subject properties
//IE if StudyArm is selected as a field, then include it, if CollectionStudyDay is included, then include it ...etc
//INSTEAD OF INCLUDING ALL PROPERTIES WHICH WOOULD MAKE THE QUERY REALLY SLOW
queryResult.Subjects = _subjectRepository.FindAll(
s => s.Study.ProjectId == projectId,
new List <string>()
{
"StudyArm", "Study", "SubjectCharacteristics.CharacteristicFeature"
}).ToList();
//QUERY FOR CLINICAL OBSERVATIONS
_getObservations(combinedQuery, ref queryResult);
//QUERY FOR SUBJECT CHARACTERISITIC (e.g. AGE)
foreach (var subjCharQuery in combinedQuery.SubjectCharacteristics)
{
var characteristics = _subjectCharacteristicRepository.FindAll(
sc =>
sc.Subject.Study.ProjectId == projectId &&
subjCharQuery.QueryWhereValue == sc.CharacteristicFeatureId.ToString(),
new List <string>()
{
"Subject"
}).ToList();
//APPLY FILTERING IF FILTER PRESENT
if (characteristics.Any() && subjCharQuery.IsFiltered)
{
characteristics = (subjCharQuery.DataType == "string")
? characteristics.FindAll(sc => subjCharQuery.FilterExactValues.Contains(sc.VerbatimValue))
: characteristics.FindAll(sc =>
int.Parse(sc.VerbatimValue) >= subjCharQuery.FilterRangeFrom &&
int.Parse(sc.VerbatimValue) <= subjCharQuery.FilterRangeTo);
}
//ADD TO EXPORT DATA
queryResult.SubjChars.AddRange(characteristics);
}
//IF A REQUESTED OBSERVATION HAS LONGITUDINAL DATA, AUTOMATICALLY ADD A TIMING COLUMN (ASSUMING VISIT AS DEFAULT FOR NOW) SHOULD BE LATER DECIDED BASED ON O3
if (queryResult.HasLongitudinalData)
{
combinedQuery.DesignElements.Add(new Query()
{
QueryFor = nameof(Visit)
});
}
foreach (var deQuery in combinedQuery.DesignElements)
{
switch (deQuery.QueryFor)
{
case nameof(HumanSubject.StudyArm):
if (deQuery.IsFiltered)
{
queryResult.Arms = _armRepository.FindAll(
a => a.Studies.Select(s => s.Study).All(s => s.ProjectId == projectId) &&
deQuery.FilterExactValues.Contains(a.Name)).ToList();
}
else
{
queryResult.Arms = _armRepository.FindAll(
a => a.Studies.Select(s => s.Study.ProjectId).Contains(projectId)).ToList();
}
break;
case nameof(HumanSubject.Study):
if (deQuery.IsFiltered)
{
queryResult.Studies = _studyRepository.FindAll(
s => s.ProjectId == projectId &&
deQuery.FilterExactValues.Contains(s.Name),
new List <string>()
{
"Subjects"
}).ToList();
}
else
{
queryResult.Studies = _studyRepository.FindAll(s => s.ProjectId == projectId).ToList();
}
break;
case nameof(Visit):
var visits = _visitRepository.FindAll(
v => v.Study.ProjectId == projectId
//apply filter if present
).ToList();
queryResult.Visits = visits;
break;
}
}
//FOR NOW SHOULD NOT BE ALLOWED UNLESS WE DECIDED TO PROPAGATE ALL ASSAY SPECIFIC FILTERS TO ALL ASSAYS IN THE COMBINED QUERY
//if (combinedQuery.AssayPanels.Any() && combinedQuery.AssayPanels.Count > 1)
// return null;
var apQuery = combinedQuery.AssayPanels.FirstOrDefault();
if (apQuery != null)
{
//TODO:BRING BACK COLLECTIONSTUDYDAY
var assaySamples =
_biosampleRepository.FindAll(s => s.AssayId == apQuery.AssayId,
new List <string>()
{
"Subject", "SampleCharacteristics.CharacteristicFeature", "CollectionStudyDay"
}).ToList();
queryResult.Samples = assaySamples;
//HOW DO I FILTER THE SAMPLES BY THE QUERED PROPERTIES?
//HOW DO I filter samples by collectionStudyDay?
//TODO: TEMP SOLUTION FOR COLLECTION STUDY DAY
//foreach (var sampleQuery in apQuery.SampleQueries.Where(sq=>sq.QueryFor != nameof(Biosample.SampleCharacteristics)))
//{
// assaySamples.FindAll(b =>
// int.Parse(b.GetType().GetProperty("CollectionStudyDay").GetValue(b).properyObj?.GetType().GetProperty(query.QuerySelectProperty)?.GetValue(properyObj)?.ToString().ToString()) >= sampleQuery.FilterRangeFrom &&
// int.Parse(b.GetType().GetProperty("CollectionStudyDay").GetValue(b).ToString()) <= sampleQuery.FilterRangeTo
// );
//}
if (apQuery.SampleQueries.Exists(sq => sq.QueryFor == nameof(Biosample.CollectionStudyDay)))
{
var dayQuery = apQuery.SampleQueries.Find(sq => sq.QueryFor == nameof(Biosample.CollectionStudyDay));
if (dayQuery.IsFiltered)
{
queryResult.Samples = assaySamples.FindAll(
s =>
s.CollectionStudyDay?.Number != null &&
dayQuery.FilterExactValues.Contains(s.CollectionStudyDay?.Number.Value.ToString()));
}
}
//QUERY AND FILTER SAMPLE CHARACTERISTICS
foreach (var sampleQuery in apQuery.SampleQueries.Where(sq => sq.QueryFor == nameof(Biosample.SampleCharacteristics)))
{
var characteristics = _sampleCharacteristicRepository.FindAll(
sc =>
sc.Sample.Study.ProjectId == projectId &&
sampleQuery.QueryWhereValue == sc.CharacteristicFeatureId.ToString(),
new List <string>()
{
"Sample"
}).ToList();
//APPLY FILTERING IF FILTER PRESENT
if (characteristics.Any() && sampleQuery.IsFiltered)
{
characteristics = (sampleQuery.DataType == "string")
? characteristics.FindAll(sc => sampleQuery.FilterExactValues.Contains(sc.VerbatimValue))
: characteristics.FindAll(sc =>
int.Parse(sc.VerbatimValue) >= sampleQuery.FilterRangeFrom &&
int.Parse(sc.VerbatimValue) <= sampleQuery.FilterRangeTo);
}
//ADD TO EXPORT DATA
queryResult.SampleCharacteristics.AddRange(characteristics);
}
}
queryResult.FilterAndJoin();
return(queryResult);
}
private async Task <DataTable> ExportSubjectClinicalTable(DataExportObject exportData, ExportFileDefinition fileDefinition) // FEATURE BY FEATURE MAIN QUICK
{
return(await Task.Factory.StartNew(() =>
{
#region Create Table Columns
var datatable = new DataTable();
datatable.TableName = fileDefinition.Name;
foreach (var field in fileDefinition.Fields)
{
datatable.Columns.Add(field.ColumnHeader.ToLower());
}
#endregion
//var subjGroupedObservations = exportData.Observations.GroupBy(ob => new { subjId = ob.USubjId });
var fieldsByO3Id = fileDefinition.Fields.FindAll(f => f.QueryObjectType == nameof(SdtmRow)).GroupBy(f => f.QueryObject.QueryObjectName).ToList();
var subjPropertiesFields = fileDefinition.Fields.FindAll(f => f.QueryObjectType != nameof(SdtmRow)).ToList();
foreach (var subject in exportData.Subjects)
{
var uniqSubjectId = subject.UniqueSubjectId;
var subjectObservations = exportData.Observations.FindAll(o => o.USubjId == uniqSubjectId).ToList();
var subjectCharacteristics = exportData.SubjChars.FindAll(sc => sc.SubjectId == subject.Id).ToList();
var firstRow = true;
while (subjectObservations.Any() || firstRow)
{
var row = datatable.NewRow();
firstRow = false;
#region Design Elements
// row["studyid"] = subject.Study.Name;
#endregion
#region Subject Properties
foreach (var subjPropField in subjPropertiesFields)
{
var charVal = _queryService.GetSubjectOrSampleProperty(subject, subjPropField.QueryObject);
if (charVal != null)
{
row[subjPropField.ColumnHeader.ToLower()] = charVal;
}
}
#endregion
#region WRITE CLINICAL OBSERVATIONS
foreach (var fieldgrp in fieldsByO3Id)//HEADACHE //BMI (EVENTS AND FINDINGS TOGETHER)//NOTE .. TIMEING ARE NOT synchronized YET
{
SdtmRow obs = null;
foreach (var field in fieldgrp) //AEOCCUR / AESEV
{
//ONTOLOGY TERM REQUEST
var query = (ObservationQuery)field.QueryObject;
if (query.IsOntologyEntry)
{
obs = subjectObservations.FirstOrDefault(
o => o.QualifierQualifiers.ContainsKey(query.TermCategory) &&
((ObservationQuery)field.QueryObject).TermId.ToString() == o.QualifierQualifiers[query.TermCategory]);
}
//GROUP OF OBSERVATIONS
else if (field.QueryObject.GetType() == typeof(GroupedObservationsQuery))
{
//ASSUMPTION: GROUPS AREONLY COMPOSED OF ONTOLOGY ENTRY
//ASSUMPTION:
string v;
foreach (var obsQuery in ((GroupedObservationsQuery)field.QueryObject).GroupedObservations)
{
obs = subjectObservations.FirstOrDefault(
o => o.QualifierQualifiers.TryGetValue(obsQuery.TermCategory, out v) &&
obsQuery.TermId.ToString() == o.QualifierQualifiers[obsQuery.TermCategory]);
if (obs != null)
{
break;
}
}
}
//SINGLE OBSERVATION OBJECT TERM REQUEST
else
{
obs = subjectObservations.FirstOrDefault(
o => ((ObservationQuery)field.QueryObject).TermId == o.DBTopicId);
}
string val = "";
//WRITE OBSERVATION INSTANCE TO ROW
obs?.Qualifiers.TryGetValue(((ObservationQuery)field.QueryObject).PropertyName, out val);
if (val == null)
{
obs?.ResultQualifiers.TryGetValue(((ObservationQuery)field.QueryObject).PropertyName, out val);
}
//Write
//if (val == null)
//if (((ObservationQuery)field.QueryObject).PropertyName.ToLower().Equals("visit"))
//val = obs.VisitName;
//if (val==null && ((ObservationQuery)field.QueryObject).PropertyName.EndsWith("DY") && obs.CollectionStudyDay.Number!=null)
//{
// val = obs.CollectionStudyDay.Number.HasValue ? obs.CollectionStudyDay.Number.ToString() : "";
// //row[field.ColumnHeader.ToLower()] = val;
//}
row[field.ColumnHeader.ToLower()] = val ?? "";
}
var visitField = fileDefinition.Fields.Find(f => f.QueryObjectType == nameof(Visit));
if (visitField != null && obs != null)
{
row[visitField.ColumnHeader.ToLower()] = obs?.VisitName ?? "";
}
var timepointField = fileDefinition.Fields.FirstOrDefault(f => f.QueryObject.QueryFor != null &&
f.QueryObject.QueryFor.Equals(nameof(SdtmRow.CollectionStudyTimePoint)));
if (timepointField != null && obs != null)
{
row[timepointField.ColumnHeader.ToLower()] = obs?.CollectionStudyTimePoint?.Name ?? "";
}
subjectObservations.Remove(obs);
}
#endregion
#region Write Timing Variables
#endregion
datatable.Rows.Add(row);
}
}
return datatable;
}));
}
