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}"); } }