private async Task <UniqueTemplateContents> GetTemplateData(SpecificationSummary specification, string fundingStreamId)
{
UniqueTemplateContents uniqueTemplateContents = new UniqueTemplateContents();
TemplateMetadataContents templateMetadata = await GetTemplateMetadataContents(specification, fundingStreamId);
if (templateMetadata == null)
{
throw new NonRetriableException(
$"Did not locate template information for specification {specification.Id} in {fundingStreamId}. Unable to complete Qa Schema Generation");
}
IEnumerable <FundingLine> flattenedFundingLines = templateMetadata.RootFundingLines.Flatten(_ => _.FundingLines)
?? new FundingLine[0];
IEnumerable <FundingLine> uniqueFundingLines = flattenedFundingLines.GroupBy(x => x.TemplateLineId)
.Select(f => f.First());
IEnumerable <Calculation> flattenedCalculations =
flattenedFundingLines.SelectMany(_ => _.Calculations.Flatten(cal => cal.Calculations)) ?? new Calculation[0];
IEnumerable <Calculation> uniqueFlattenedCalculations =
flattenedCalculations.GroupBy(x => x.TemplateCalculationId)
.Select(x => x.FirstOrDefault());
uniqueTemplateContents.FundingLines = uniqueFundingLines;
uniqueTemplateContents.Calculations = uniqueFlattenedCalculations;
return(uniqueTemplateContents);
}
IEnumerable <SqlColumnDefinition> calculationFields) GetUniqueFundingLinesAndCalculationsForFundingStream(
UniqueTemplateContents templateContents)
{
IEnumerable <SqlColumnDefinition> informationFundingLineFields =
GetSqlColumnDefinitionsForFundingLines(templateContents.FundingLines.Where(f => f.Type == FundingLineType.Information));
IEnumerable <SqlColumnDefinition> paymentFundingLineFields =
GetSqlColumnDefinitionsForFundingLines(templateContents.FundingLines.Where(f => f.Type == FundingLineType.Payment));
IEnumerable <SqlColumnDefinition> calculationFields = GetSqlColumnDefinitionsForCalculations(templateContents.Calculations);
return(informationFundingLineFields, paymentFundingLineFields, calculationFields);
}
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}");
}
}
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);
}
