public static async Task <StringBuilder> AlterUlns(ApprenticeshipData apprenticeshipData, Dictionary <long, ReadOptimisedProviderData> anonymisedProviders)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("Old ULN, New Uln");
foreach (var apprenticeship in apprenticeshipData.Apprenticeships)
{
var providerData = anonymisedProviders[apprenticeship.Ukprn];
var listOfChangedLearners = providerData.OptimisedLearners[apprenticeship.Uln];
foreach (var changedLearner in listOfChangedLearners)
{
if (changedLearner.OldUln != apprenticeship.Uln)
{
await Logger.Log(
$"Multiple learners for UKPRN: {apprenticeship.Ukprn} and ULN: {apprenticeship.Uln} - results are not guaranteed");
foreach (var learner in listOfChangedLearners)
{
stringBuilder.AppendLine($"{learner.OldUln},{learner.NewUln}");
await Logger.Log($"New ULN: {learner.NewUln}", 1);
}
}
apprenticeship.Uln = changedLearner.NewUln;
}
}
return(stringBuilder);
}
public static ApprenticeshipData LoadProductionApprenticeships(List <long> ukprns)
{
var result = new ApprenticeshipData();
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProductionV2DatabaseConnectionString"].ConnectionString))
{
var apprenticeships = connection.Query <Apprenticeship>(Sql.Apprenticeships, new { ukprns }, commandTimeout: 3600);
var priceEpisodes = connection.Query <ApprenticeshipPriceEpisode>(Sql.ApprenticeshipPriceEpisodes, new { ukprns }, commandTimeout: 3600);
var pauses = connection.Query <ApprenticeshipPause>(Sql.ApprenticeshipPauses, new { ukprns }, commandTimeout: 3600);
result.Apprenticeships.AddRange(apprenticeships);
result.ApprenticeshipPauses.AddRange(pauses);
result.ApprenticeshipPriceEpisodes.AddRange(priceEpisodes);
}
return(result);
}
public static string CreateDeleteByUkprnScript(ApprenticeshipData apprenticeshipData)
{
var ukprns = apprenticeshipData.Apprenticeships
.Select(x => x.Ukprn)
.Distinct()
.ToList();
var stringBuilder = new StringBuilder();
var position = 0;
var ukprnsToRemove = ukprns.Skip(position).Take(500).ToList();
while (ukprnsToRemove.Any())
{
stringBuilder.AppendLine("DELETE Payments2.ApprenticeshipPriceEpisode");
stringBuilder.AppendLine("WHERE ApprenticeshipId IN (SELECT Id ");
stringBuilder.AppendLine("FROM Payments2.Apprenticeship WHERE Ukprn IN (");
stringBuilder.AppendLine(string.Join(",", ukprnsToRemove));
stringBuilder.AppendLine("))");
stringBuilder.AppendLine();
stringBuilder.AppendLine("DELETE Payments2.ApprenticeshipPause");
stringBuilder.AppendLine("WHERE ApprenticeshipId IN (SELECT Id ");
stringBuilder.AppendLine("FROM Payments2.Apprenticeship WHERE Ukprn IN (");
stringBuilder.AppendLine(string.Join(",", ukprnsToRemove));
stringBuilder.AppendLine("))");
stringBuilder.AppendLine();
stringBuilder.AppendLine("DELETE Payments2.Apprenticeship ");
stringBuilder.AppendLine("WHERE Ukprn IN (");
stringBuilder.AppendLine(string.Join(",", ukprnsToRemove));
stringBuilder.AppendLine(")");
stringBuilder.AppendLine();
stringBuilder.AppendLine();
position += 500;
ukprnsToRemove = ukprns.Skip(position).Take(500).ToList();
}
return(stringBuilder.ToString());
}
public static async Task RemoveApprenticeships(ApprenticeshipData apprenticeshipData,
Dictionary <long, ReadOptimisedProviderData> anonymisedProviders)
{
var apprenticeshipsToRemove = apprenticeshipData.Apprenticeships.Where(apprenticeship =>
!anonymisedProviders.ContainsKey(apprenticeship.Ukprn) ||
!anonymisedProviders[apprenticeship.Ukprn].OptimisedLearners.ContainsKey(apprenticeship.Uln))
.Select(a => a.Id)
.ToList();
await Logger.Log($"Removing {apprenticeshipsToRemove.Count} apprenticeships");
var removed = apprenticeshipData.ApprenticeshipPauses.RemoveAll(x =>
apprenticeshipsToRemove.Contains(x.ApprenticeshipId));
await Logger.Log($"Removed {removed} apprenticeship paused", 1);
removed = apprenticeshipData.ApprenticeshipPriceEpisodes.RemoveAll(x =>
apprenticeshipsToRemove.Contains(x.ApprenticeshipId));
await Logger.Log($"Removed {removed} apprenticeship price episodes", 1);
removed = apprenticeshipData.Apprenticeships.RemoveAll(x =>
apprenticeshipsToRemove.Contains(x.Id));
await Logger.Log($"Removed {removed} apprenticeships", 1);
}
public static async Task <List <string> > CreateNewCommitmentsScript(ApprenticeshipData apprenticeshipData)
{
var results = new List <string>();
var stringBuilder = new StringBuilder();
await Logger.Log("Structuring the data for easier lookups");
var priceEpisdesByApprenticeshipId = apprenticeshipData
.ApprenticeshipPriceEpisodes
.ToLookup(x => x.ApprenticeshipId);
var pausesByApprenticeshipId = apprenticeshipData
.ApprenticeshipPauses
.ToLookup(x => x.ApprenticeshipId);
var position = 0;
var batch = 100;
var apprenticeships = apprenticeshipData.Apprenticeships.Skip(position).Take(batch).ToList();
while (apprenticeships.Any())
{
/* DELETE EXISTING */
var apprenticeshipIds = apprenticeships.Select(x => x.Id).ToList();
stringBuilder.Append(CreateDeleteByApprenticeshipId(apprenticeshipIds));
/*--------------------- APPRENTICESHIPS -------------------------*/
/* --------------------------------------------------------------*/
stringBuilder.AppendLine();
stringBuilder.AppendLine();
stringBuilder.AppendLine("INSERT INTO Payments2.Apprenticeship ");
stringBuilder.AppendLine("(Id, AccountId, AgreementId, AgreedOnDate, ULN, UKPRN, " +
"EstimatedStartDate, EstimatedEndDate, Priority, StandardCode, " +
"ProgrammeType, FrameworkCode, PathwayCode, LegalEntityName, " +
"TransferSendingEmployerAccountId, StopDate, [Status], IsLevyPayer, " +
"ApprenticeshipEmployerType)");
stringBuilder.AppendLine("VALUES");
foreach (var apprenticeship in apprenticeships)
{
stringBuilder.AppendLine($"({apprenticeship.Id}, " +
$"{apprenticeship.AccountId}, '{apprenticeship.AgreementId}', " +
$"{DateToSql(apprenticeship.AgreedOnDate)}, " +
$"{apprenticeship.Uln}, {apprenticeship.Ukprn}, " +
$"{DateToSql(apprenticeship.EstimatedStartDate)}, " +
$"{DateToSql(apprenticeship.EstimatedEndDate)}, " +
$"{apprenticeship.Priority}, {apprenticeship.StandardCode}, " +
$"{apprenticeship.ProgrammeType}, {apprenticeship.FrameworkCode}, " +
$"{apprenticeship.PathwayCode}, '{apprenticeship.LegalEntityName.Replace("'","''")}', " +
$"{LongToSql(apprenticeship.TransferSendingemployerAccountId)}," +
$"{DateToSql(apprenticeship.StopDate)}, {apprenticeship.Status}," +
$"{BoolToSql(apprenticeship.IsLevyPayer)}, {apprenticeship.ApprenticeshipEmployerType}),");
}
stringBuilder.Remove(stringBuilder.Length - 3, 1);
/*--------------- APPRENTICESHIP PRICE EPISODES -----------------*/
/* --------------------------------------------------------------*/
stringBuilder.AppendLine();
stringBuilder.AppendLine();
stringBuilder.AppendLine("INSERT INTO Payments2.ApprenticeshipPriceEpisode");
stringBuilder.AppendLine("(ApprenticeshipId, StartDate, EndDate, Cost, Removed)");
stringBuilder.AppendLine("VALUES");
foreach (var apprenticeship in apprenticeships)
{
if (!priceEpisdesByApprenticeshipId.Contains(apprenticeship.Id))
{
continue;
}
var priceEpisodes = priceEpisdesByApprenticeshipId[apprenticeship.Id];
foreach (var apprenticeshipPriceEpisode in priceEpisodes)
{
stringBuilder.AppendLine($"({apprenticeship.Id}, " +
$"{DateToSql(apprenticeshipPriceEpisode.StartDate)}, " +
$"{DateToSql(apprenticeshipPriceEpisode.EndDate)}, " +
$"{apprenticeshipPriceEpisode.Cost}, " +
$"0),");
}
}
stringBuilder.Remove(stringBuilder.Length - 3, 1);
/*--------------- APPRENTICESHIP Pauses -------------------------*/
/* --------------------------------------------------------------*/
var pausesToAdd = new List <ApprenticeshipPause>();
foreach (var apprenticeship in apprenticeships)
{
if (!pausesByApprenticeshipId.Contains(apprenticeship.Id))
{
continue;
}
var pauses = pausesByApprenticeshipId[apprenticeship.Id];
pausesToAdd.AddRange(pauses);
}
if (pausesToAdd.Any())
{
stringBuilder.AppendLine();
stringBuilder.AppendLine();
stringBuilder.AppendLine("INSERT INTO Payments2.ApprenticeshipPause");
stringBuilder.AppendLine("(ApprenticeshipId, PauseDate, ResumeDate)");
stringBuilder.AppendLine("VALUES");
foreach (var pause in pausesToAdd)
{
stringBuilder.AppendLine($"({pause.ApprenticeshipId}, " +
$"{DateToSql(pause.PauseDate)}, " +
$"{DateToSql(pause.ResumeDate)}),");
}
stringBuilder.Remove(stringBuilder.Length - 3, 1);
}
stringBuilder.AppendLine("GO");
stringBuilder.AppendLine();
stringBuilder.AppendLine();
/*--------------- END LOOP -------------------------------------*/
/* --------------------------------------------------------------*/
position += batch;
apprenticeships = apprenticeshipData.Apprenticeships.Skip(position).Take(batch).ToList();
if (position % 10000 == 0)
{
await Logger.Log($"Processed {position} apprenticeships", 1);
}
if (position % 100000 == 0)
{
results.Add(stringBuilder.ToString());
stringBuilder.Clear();
}
}
results.Add(stringBuilder.ToString());
return(results);
}
