public async Task <SchemaContext> ReCreateTablesForSpecificationAndFundingStream(string specificationId,
string fundingStreamId)
{
ApiResponse <SpecificationSummary> specificationResponse = await _specificationResilience.ExecuteAsync(()
=> _specifications.GetSpecificationSummaryById(specificationId));
SpecificationSummary specification = specificationResponse?.Content;
if (specification == null)
{
throw new NonRetriableException(
$"Did not locate a specification {specificationId}. Unable to complete Qa Schema Generation");
}
SchemaContext schemaContext = new SchemaContext();
await EnsureTablesForFundingStream(specification, fundingStreamId, schemaContext);
return(schemaContext);
}
private async Task <Dictionary <string, IEnumerable <SqlColumnDefinition> > > GenerateProfiling(string fundingStreamId,
string fundingPeriodId,
UniqueTemplateContents templateMetadata,
SchemaContext schemaContext)
{
ApiResponse <IEnumerable <FundingStreamPeriodProfilePattern> > profilePatternResults =
await _profilingClient.GetProfilePatternsForFundingStreamAndFundingPeriod(fundingStreamId, fundingPeriodId);
IEnumerable <FundingStreamPeriodProfilePattern> profilePatterns = profilePatternResults?.Content?.ToArray();
if (profilePatterns == null)
{
throw new NonRetriableException(
$"Did not locate any profile patterns for funding stream {fundingStreamId} and funding period {fundingPeriodId}. Unable to continue with Qa Schema Generation");
}
Dictionary <string, IEnumerable <SqlColumnDefinition> > profiling = new Dictionary <string, IEnumerable <SqlColumnDefinition> >();
foreach (FundingLine fundingLine in templateMetadata.FundingLines.Where(f => f.Type == FundingLineType.Payment))
{
string fundingLineCode = fundingLine.FundingLineCode;
IEnumerable <ProfilePeriodPattern> allPatterns = profilePatterns
.Where(p => p.FundingLineId == fundingLineCode)
.SelectMany(p => p.ProfilePattern)
.ToArray();
IEnumerable <ProfilePeriodPattern> uniqueProfilePatterns = allPatterns.DistinctBy(_ => new
{
_.Occurrence,
_.Period,
_.PeriodType,
_.PeriodYear
});
schemaContext.AddFundingLineProfilePatterns(fundingLineCode, uniqueProfilePatterns.ToArray());
List <SqlColumnDefinition> fundingSqlFields = new List <SqlColumnDefinition>();
foreach (ProfilePeriodPattern pattern in uniqueProfilePatterns)
{
string patternPrefix = $"{pattern.Period}_{pattern.PeriodType}_{pattern.PeriodYear}_{pattern.Occurrence}";
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_Period",
Type = "[varchar](64)",
AllowNulls = true
});
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_PeriodType",
Type = "[varchar](64)",
AllowNulls = true
});
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_Year",
Type = "[varchar](64)",
AllowNulls = true
});
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_Occurrence",
Type = "[varchar](64)",
AllowNulls = true
});
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_DistributionPeriod",
Type = "[varchar](64)",
AllowNulls = true
});
fundingSqlFields.Add(new SqlColumnDefinition
{
Name = $"{patternPrefix}_Value",
Type = "[decimal](30, 2)",
AllowNulls = true
});
}
profiling.Add(fundingLineCode, fundingSqlFields);
}
return(profiling);
}
private async Task EnsureTablesForFundingStream(SpecificationSummary specification, string fundingStreamId, SchemaContext schemaContext)
{
// TODO: handle multiple version of the template for fields....
UniqueTemplateContents templateMetadata = await GetTemplateData(specification, fundingStreamId);
Dictionary <string, IEnumerable <SqlColumnDefinition> > profilingTables =
await GenerateProfiling(fundingStreamId, specification.FundingPeriod.Id, templateMetadata, schemaContext);
string fundingStreamTablePrefix = $"{fundingStreamId}_{specification.FundingPeriod.Id}";
DropForeignKeys(fundingStreamTablePrefix, "Funding", "Providers");
DropForeignKeys(fundingStreamTablePrefix, "Funding", "InformationFundingLines");
DropForeignKeys(fundingStreamTablePrefix, "Funding", "PaymentFundingLines");
DropForeignKeys(fundingStreamTablePrefix, "Funding", "Calculations");
foreach (KeyValuePair <string, IEnumerable <SqlColumnDefinition> > profileTable in profilingTables)
{
DropForeignKeys(fundingStreamTablePrefix, "Funding", $"Profiles_{profileTable.Key}");
}
EnsureTable($"{fundingStreamTablePrefix}_Funding", GetSqlColumnDefinitionsForFunding());
EnsureTable($"{fundingStreamTablePrefix}_Providers", GetSqlColumnDefinitionsForProviderInformation());
(IEnumerable <SqlColumnDefinition> informationFundingLineFields,
IEnumerable <SqlColumnDefinition> paymentFundingLineFields,
IEnumerable <SqlColumnDefinition> calculationFields) = GetUniqueFundingLinesAndCalculationsForFundingStream(templateMetadata);
foreach (KeyValuePair <string, IEnumerable <SqlColumnDefinition> > profileTable in profilingTables)
{
EnsureTable($"{fundingStreamTablePrefix}_Profiles_{profileTable.Key}", profileTable.Value);
}
EnsureTable($"{fundingStreamTablePrefix}_InformationFundingLines", informationFundingLineFields);
EnsureTable($"{fundingStreamTablePrefix}_PaymentFundingLines", paymentFundingLineFields);
EnsureTable($"{fundingStreamTablePrefix}_Calculations", calculationFields);
AddForeignKey(fundingStreamTablePrefix, "Funding", "Providers");
AddForeignKey(fundingStreamTablePrefix, "Funding", "InformationFundingLines");
AddForeignKey(fundingStreamTablePrefix, "Funding", "PaymentFundingLines");
AddForeignKey(fundingStreamTablePrefix, "Funding", "Calculations");
foreach (KeyValuePair <string, IEnumerable <SqlColumnDefinition> > profileTable in profilingTables)
{
AddForeignKey(fundingStreamTablePrefix, "Funding", $"Profiles_{profileTable.Key}");
}
}