private void CreateFileSummary(XWPFDocument document)
{
var table = InsertTable(document, ExtractionResults.Length + 1, 5);
int tableLine = 0;
SetTableCell(table, tableLine, 0, "Data Requirement");
SetTableCell(table, tableLine, 1, "Notes");
SetTableCell(table, tableLine, 2, "Filename");
SetTableCell(table, tableLine, 3, "Records");
SetTableCell(table, tableLine, 4, "Unique Individuals");
tableLine++;
foreach (var result in ExtractionResults)
{
var filename = GetFileName(result);
SetTableCell(table, tableLine, 0, _repository.GetObjectByID <ExtractableDataSet>(result.ExtractableDataSet_ID).ToString());
SetTableCell(table, tableLine, 1, result.FiltersUsed);
SetTableCell(table, tableLine, 2, filename);
SetTableCell(table, tableLine, 3, result.RecordsExtracted.ToString("N0"));
SetTableCell(table, tableLine, 4, result.DistinctReleaseIdentifiersEncountered.ToString("N0"));
tableLine++;
}
}
private void CreateFileSummary(XWPFDocument document)
{
var table = InsertTable(document, ExtractionResults.Length + 1, 5);
int tableLine = 0;
SetTableCell(table, tableLine, 0, "Data Requirement");
SetTableCell(table, tableLine, 1, "Notes");
SetTableCell(table, tableLine, 2, "Filename");
SetTableCell(table, tableLine, 3, "No. of records extracted");
SetTableCell(table, tableLine, 4, "Unique Patient Count");
tableLine++;
foreach (var result in ExtractionResults)
{
string filename = "";
if (IsValidFilename(result.DestinationDescription))
{
filename = new FileInfo(result.DestinationDescription).Name;
}
else
{
filename = result.DestinationDescription;
}
SetTableCell(table, tableLine, 0, _repository.GetObjectByID <ExtractableDataSet>(result.ExtractableDataSet_ID).ToString());
SetTableCell(table, tableLine, 1, result.FiltersUsed);
SetTableCell(table, tableLine, 2, filename);
SetTableCell(table, tableLine, 3, result.RecordsExtracted.ToString());
SetTableCell(table, tableLine, 4, result.DistinctReleaseIdentifiersEncountered.ToString());
tableLine++;
}
}
public WordDataReleaseFileGenerator(IExtractionConfiguration configuration, IDataExportRepository repository)
{
_repository = repository;
Configuration = configuration;
Project = configuration.Project;
if (Configuration.Cohort_ID == null)
{
throw new NullReferenceException("Configuration has no Cohort");
}
Cohort = _repository.GetObjectByID <ExtractableCohort>((int)Configuration.Cohort_ID);
ExtractionResults =
Configuration.CumulativeExtractionResults
.OrderBy(
c => _repository.GetObjectByID <ExtractableDataSet>(c.ExtractableDataSet_ID).ToString()
).ToArray();
}
protected virtual void AuditExtractionConfigurationDetails(StreamWriter sw, DirectoryInfo configurationSubDirectory, KeyValuePair <IExtractionConfiguration, List <ReleasePotential> > kvp, string extractionIdentifier)
{
//audit in contents.txt
sw.WriteLine("Folder:" + configurationSubDirectory.Name);
sw.WriteLine("ConfigurationName:" + kvp.Key.Name);
sw.WriteLine("ConfigurationDescription:" + kvp.Key.Description);
sw.WriteLine("ExtractionConfiguration.ID:" + kvp.Key.ID);
sw.WriteLine("ExtractionConfiguration Identifier:" + extractionIdentifier);
sw.WriteLine("CumulativeExtractionResult.ID(s):" +
kvp.Value.Select(v => v.DatasetExtractionResult.ID)
.Distinct()
.Aggregate("", (s, n) => s + n + ",")
.TrimEnd(','));
sw.WriteLine("CohortName:" + _repository.GetObjectByID <ExtractableCohort>((int)kvp.Key.Cohort_ID));
sw.WriteLine("CohortID:" + kvp.Key.Cohort_ID);
}
/// <summary>
/// This produces the SQL that would retrieve the specified dataset columns including any JOINS
///
/// <para>It uses:
/// QueryBuilder and then it adds some custom lines for linking to the cohort</para>
/// </summary>
/// <returns></returns>
public QueryBuilder GetSQLCommandForFullExtractionSet(ExtractDatasetCommand request, out List <ReleaseIdentifierSubstitution> substitutions)
{
if (request.QueryBuilder != null)
{
throw new Exception("Creation of a QueryBuilder from a request can only happen once, to access the results of the creation use the cached answer in the request.QueryBuilder property");
}
if (!request.ColumnsToExtract.Any())
{
throw new Exception("No columns are marked for extraction in this configuration");
}
if (request.ExtractableCohort == null)
{
throw new NullReferenceException("No Cohort selected");
}
var databaseType = request.Catalogue.GetDistinctLiveDatabaseServerType();
if (databaseType == null)
{
throw new NotSupportedException("Catalogue " + request.Catalogue + " did not know what DatabaseType it hosted, how can we extract from it! does it have no TableInfos?");
}
var syntaxHelper = new QuerySyntaxHelperFactory().Create(databaseType.Value);
substitutions = new List <ReleaseIdentifierSubstitution>();
var memoryRepository = new MemoryRepository();
switch (request.ColumnsToExtract.Count(c => c.IsExtractionIdentifier))
{
//no extraction identifiers
case 0: throw new Exception("There are no Columns in this dataset (" + request + ") marked as IsExtractionIdentifier");
//a single extraction identifier e.g. CHI X died on date Y with conditions a,b and c
case 1: substitutions.Add(new ReleaseIdentifierSubstitution(memoryRepository, request.ColumnsToExtract.FirstOrDefault(c => c.IsExtractionIdentifier), request.ExtractableCohort, false, syntaxHelper));
break;
//multiple extraction identifiers e.g. Mother X had Babies A, B, C where A,B and C are all CHIs that must be subbed for ProCHIs
default:
foreach (IColumn columnToSubstituteForReleaseIdentifier in request.ColumnsToExtract.Where(c => c.IsExtractionIdentifier))
{
substitutions.Add(new ReleaseIdentifierSubstitution(memoryRepository, columnToSubstituteForReleaseIdentifier, request.ExtractableCohort, true, syntaxHelper));
}
break;
}
string hashingAlgorithm = _repository.DataExportPropertyManager.GetValue(DataExportProperty.HashingAlgorithmPattern);
if (string.IsNullOrWhiteSpace(hashingAlgorithm))
{
hashingAlgorithm = null;
}
//identify any tables we are supposed to force join to
var forcedJoins = request.SelectedDataSets.SelectedDataSetsForcedJoins;
QueryBuilder queryBuilder = new QueryBuilder("DISTINCT ", hashingAlgorithm, forcedJoins.Select(s => s.TableInfo).ToArray());
queryBuilder.TopX = request.TopX;
queryBuilder.SetSalt(request.Salt.GetSalt());
//add the constant parameters
foreach (ConstantParameter parameter in GetConstantParameters(syntaxHelper, request.Configuration, request.ExtractableCohort))
{
queryBuilder.ParameterManager.AddGlobalParameter(parameter);
}
//add the global parameters
foreach (var globalExtractionFilterParameter in request.Configuration.GlobalExtractionFilterParameters)
{
queryBuilder.ParameterManager.AddGlobalParameter(globalExtractionFilterParameter);
}
//remove the identification column from the query
request.ColumnsToExtract.RemoveAll(c => c.IsExtractionIdentifier);
//add in the ReleaseIdentifier in place of the identification column
queryBuilder.AddColumnRange(substitutions.ToArray());
//add the rest of the columns to the query
queryBuilder.AddColumnRange(request.ColumnsToExtract.Cast <IColumn>().ToArray());
//add the users selected filters
queryBuilder.RootFilterContainer = request.Configuration.GetFilterContainerFor(request.DatasetBundle.DataSet);
ExternalCohortTable externalCohortTable = _repository.GetObjectByID <ExternalCohortTable>(request.ExtractableCohort.ExternalCohortTable_ID);
if (request.ExtractableCohort != null)
{
//the JOIN with the cohort table:
string cohortJoin;
if (substitutions.Count == 1)
{
cohortJoin = " INNER JOIN " + externalCohortTable.TableName + " ON " + substitutions.Single().JoinSQL;
}
else
{
cohortJoin = " INNER JOIN " + externalCohortTable.TableName + " ON " + string.Join(" OR ", substitutions.Select(s => s.JoinSQL));
}
//add the JOIN in after any other joins
queryBuilder.AddCustomLine(cohortJoin, QueryComponent.JoinInfoJoin);
//add the filter cohortID because our new Cohort system uses ID number and a giant combo table with all the cohorts in it we need to say Select XX from XX join Cohort Where Cohort number = Y
queryBuilder.AddCustomLine(request.ExtractableCohort.WhereSQL(), QueryComponent.WHERE);
}
request.QueryBuilder = queryBuilder;
return(queryBuilder);
}