private void CreatePayment(List <ProviderEarnedPeriodValue> paymentValues, long ukprn,
long uln, string learnRefNumber,
CommitmentReferenceData commitment,
IlrLearnerReferenceData learningDetails,
TransactionType transactionType,
FundingSource fundingSource)
{
foreach (var payment in paymentValues)
{
if (payment.Value > 0)
{
var requiredPaymentId = Guid.NewGuid().ToString();
var month = int.Parse(payment.PeriodName.Substring(0, 2));
var year = int.Parse(payment.PeriodName.Substring(3, 2)) + 2000;
var date = new DateTime(year, month, 1);
var periodNumber = date.GetPeriodNumber();
var periodName = $"{TestEnvironment.Variables.OpaRulebaseYear}-R" + periodNumber.ToString("00");
PaymentsManager.SavePaymentDue(requiredPaymentId, ukprn, uln, commitment, learnRefNumber, periodName,
month, year, (int)transactionType, payment.Value, learningDetails);
PaymentsManager.SavePayment(requiredPaymentId, periodName, month, year, (int)transactionType, fundingSource, payment.Value);
}
}
}
private static LearningDeliveryFamRecord[] BuildLearningDeliveryFamCodes(IlrLearnerReferenceData learnerDetails,
List <ContractTypeReferenceData> contractTypes, List <LearningSupportReferenceData> learningSupportStatus)
{
var learningEndDate = (!learnerDetails.ActualEndDate.HasValue || learnerDetails.PlannedEndDate > learnerDetails.ActualEndDate.Value)
? learnerDetails.PlannedEndDate : learnerDetails.ActualEndDate.Value;
var actFamCodes = BuildActFamCodes(learnerDetails.LearnerType, learnerDetails.StartDate, learningEndDate, contractTypes);
var lsfFamCodes = BuildLsfFamCodes(learningSupportStatus);
var eefFamCodes = BuildEefFamCodes(learnerDetails);
return(actFamCodes.Concat(lsfFamCodes).Concat(eefFamCodes).ToArray());
}
private static IlrLearnerReferenceData ParseCommitmentsTableRow(TableRow row, IlrTableStructure structure)
{
var rowData = new IlrLearnerReferenceData
{
LearnerId = row.ReadRowColumnValue <string>(structure.UlnIndex, "ULN", Defaults.LearnerId),
AgreedPrice = row.ReadRowColumnValue <int>(structure.AgreedPriceIndex, "agreed price"),
LearnerType = (LearnerType)row.ReadRowColumnValue <string>(structure.LearnerTypeIndex, "learner type", "programme only DAS").ToEnumByDescription(typeof(LearnerType)),
StartDate = row.ReadRowColumnValue <DateTime>(structure.StartDateIndex, "start date"),
PlannedEndDate = row.ReadRowColumnValue <DateTime>(structure.PlannedEndDateIndex, "planned end date"),
ActualEndDate = row.ReadRowColumnValue <DateTime?>(structure.ActualEndDateIndex, "actual end date"),
CompletionStatus = (CompletionStatus)row.ReadRowColumnValue <string>(structure.CompletionStatusIndex, "completion status").ToEnumByDescription(typeof(CompletionStatus)),
Provider = row.ReadRowColumnValue <string>(structure.ProviderIndex, "provider", Defaults.ProviderId),
TotalTrainingPrice1 = row.ReadRowColumnValue <int>(structure.TotalTrainingPrice1Index, "total training price 1"),
TotalTrainingPrice1EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.TotalTrainingPrice1EffectiveDateIndex, "total training price 1 effective date"),
TotalAssessmentPrice1 = row.ReadRowColumnValue <int>(structure.TotalAssessmentPrice1Index, "total assessment price 1"),
TotalAssessmentPrice1EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.TotalAssessmentPrice1EffectiveDateIndex, "total assessment price 1 effective date"),
TotalTrainingPrice2 = row.ReadRowColumnValue <int>(structure.TotalTrainingPrice2Index, "total training price 2"),
TotalTrainingPrice2EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.TotalTrainingPrice2EffectiveDateIndex, "total training price 2 effective date"),
TotalAssessmentPrice2 = row.ReadRowColumnValue <int>(structure.TotalAssessmentPrice2Index, "total assessment price 2"),
TotalAssessmentPrice2EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.TotalAssessmentPrice2EffectiveDateIndex, "total assessment price 2 effective date"),
ResidualTrainingPrice1 = row.ReadRowColumnValue <int>(structure.ResidualTrainingPrice1Index, "residual training price 1"),
ResidualTrainingPrice1EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.ResidualTrainingPrice1EffectiveDateIndex, "residual training price 1 effective date"),
ResidualAssessmentPrice1 = row.ReadRowColumnValue <int>(structure.ResidualAssessmentPrice1Index, "residual assessment price 1"),
ResidualAssessmentPrice1EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.ResidualAssessmentPrice1EffectiveDateIndex, "residual assessment price 1 effective date"),
ResidualTrainingPrice2 = row.ReadRowColumnValue <int>(structure.ResidualTrainingPrice2Index, "residual training price 2"),
ResidualTrainingPrice2EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.ResidualTrainingPrice2EffectiveDateIndex, "residual training price 2 effective date"),
ResidualAssessmentPrice2 = row.ReadRowColumnValue <int>(structure.ResidualAssessmentPrice2Index, "residual assessment price 2"),
ResidualAssessmentPrice2EffectiveDate = row.ReadRowColumnValue <DateTime>(structure.ResidualAssessmentPrice2EffectiveDateIndex, "residual assessment price 2 effective date"),
AimType = (AimType)row.ReadRowColumnValue <string>(structure.AimTypeIndex, "aim type", "Programme").ToEnumByDescription(typeof(AimType)),
AimRate = row.ReadRowColumnValue <string>(structure.AimRateIndex, "aim rate"),
StandardCode = row.ReadRowColumnValue <long>(structure.StandardCodeIndex, "standard code"),
FrameworkCode = row.ReadRowColumnValue <int>(structure.FrameworkCodeIndex, "framework code"),
ProgrammeType = row.ReadRowColumnValue <int>(structure.ProgrammeTypeIndex, "programme type"),
PathwayCode = row.ReadRowColumnValue <int>(structure.PathwayCodeIndex, "pathway code"),
HomePostcodeDeprivation = row.ReadRowColumnValue <string>(structure.HomePostcodeDeprivationIndex, "home postcode deprivation"),
EmploymentStatus = row.ReadRowColumnValue <string>(structure.EmploymentStatusIndex, "employment status"),
EmploymentStatusApplies = row.ReadRowColumnValue <string>(structure.EmploymentStatusAppliesIndex, "employment status applies"),
EmployerId = row.ReadRowColumnValue <string>(structure.EmployerIdIndex, "employer id"),
SmallEmployer = row.ReadRowColumnValue <string>(structure.SmallEmployerIndex, "small employer"),
LearnDelFam = row.ReadRowColumnValue <string>(structure.LearnDelFamIndex, "LearnDelFam")
};
if (rowData.StandardCode == 0 && rowData.FrameworkCode == 0)
{
rowData.StandardCode = Defaults.StandardCode;
}
return(rowData);
}
private static LearningDeliveryFamRecord[] BuildEefFamCodes(IlrLearnerReferenceData learnerDetails)
{
if (learnerDetails.LearnDelFam == null || !learnerDetails.LearnDelFam.ToUpper().StartsWith("EEF"))
{
return(new LearningDeliveryFamRecord[0]);
}
return(new[]
{
new LearningDeliveryFamRecord
{
FamType = "EEF",
Code = int.Parse(learnerDetails.LearnDelFam.Substring(3)),
//From = learnerDetails.StartDate,
//To = learnerDetails.PlannedEndDate
}
});
}
private static FinancialRecord[] BuildLearningDeliveryFinancials(IlrLearnerReferenceData learnerReferenceData)
{
var agreedTrainingPrice = learnerReferenceData.FrameworkCode > 0 ? learnerReferenceData.AgreedPrice :
(int)Math.Floor(learnerReferenceData.AgreedPrice * 0.8m);
var agreedAssesmentPrice = learnerReferenceData.AgreedPrice - agreedTrainingPrice;
var financialRecords = new List <FinancialRecord>();
////////////////////////////////////////////////////////////////////
// TNP1 & 2
////////////////////////////////////////////////////////////////////
if (learnerReferenceData.TotalTrainingPrice1 > 0)
{
financialRecords.Add(new FinancialRecord
{
Code = 1,
Type = "TNP",
Amount = learnerReferenceData.TotalTrainingPrice1 == 0 ? agreedTrainingPrice : learnerReferenceData.TotalTrainingPrice1,
Date = learnerReferenceData.TotalTrainingPrice1EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.TotalTrainingPrice1EffectiveDate
});
}
if (learnerReferenceData.TotalAssessmentPrice1 > 0)
{
financialRecords.Add(new FinancialRecord
{
Code = 2,
Type = "TNP",
Amount = learnerReferenceData.TotalAssessmentPrice1 == 0 ? agreedAssesmentPrice : learnerReferenceData.TotalAssessmentPrice1,
Date = learnerReferenceData.TotalAssessmentPrice1EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.TotalAssessmentPrice1EffectiveDate
});
}
if (learnerReferenceData.TotalTrainingPrice2 > 0 && learnerReferenceData.TotalTrainingPrice2 != learnerReferenceData.TotalTrainingPrice1)
{
financialRecords.Add(new FinancialRecord
{
Code = 1,
Type = "TNP",
Amount = learnerReferenceData.TotalTrainingPrice2,
Date = learnerReferenceData.TotalTrainingPrice2EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.TotalTrainingPrice2EffectiveDate
});
}
if (learnerReferenceData.TotalAssessmentPrice2 > 0 && learnerReferenceData.TotalAssessmentPrice2 != learnerReferenceData.TotalAssessmentPrice1)
{
financialRecords.Add(new FinancialRecord
{
Code = 2,
Type = "TNP",
Amount = learnerReferenceData.TotalAssessmentPrice2,
Date = learnerReferenceData.TotalAssessmentPrice2EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.TotalAssessmentPrice2EffectiveDate
});
}
////////////////////////////////////////////////////////////////////
// TNP3 & 4
////////////////////////////////////////////////////////////////////
if (learnerReferenceData.ResidualTrainingPrice1 > 0 || learnerReferenceData.ResidualAssessmentPrice1 > 0)
{
financialRecords.Add(new FinancialRecord
{
Code = 3,
Type = "TNP",
Amount = learnerReferenceData.ResidualTrainingPrice1,
Date = learnerReferenceData.ResidualTrainingPrice1EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.ResidualTrainingPrice1EffectiveDate
});
financialRecords.Add(new FinancialRecord
{
Code = 4,
Type = "TNP",
Amount = learnerReferenceData.ResidualAssessmentPrice1,
Date = learnerReferenceData.ResidualAssessmentPrice1EffectiveDate == DateTime.MinValue ? learnerReferenceData.StartDate : learnerReferenceData.ResidualAssessmentPrice1EffectiveDate
});
}
// Change in residual
if ((learnerReferenceData.ResidualTrainingPrice2 > 0 && learnerReferenceData.ResidualTrainingPrice2 != learnerReferenceData.ResidualTrainingPrice1) ||
(learnerReferenceData.ResidualAssessmentPrice2 > 0 && learnerReferenceData.ResidualAssessmentPrice2 != learnerReferenceData.ResidualAssessmentPrice1))
{
financialRecords.Add(new FinancialRecord
{
Code = 3,
Type = "TNP",
Amount = learnerReferenceData.ResidualTrainingPrice2 == 0 ? learnerReferenceData.ResidualTrainingPrice1 : learnerReferenceData.ResidualTrainingPrice2,
Date = learnerReferenceData.ResidualTrainingPrice2EffectiveDate == DateTime.MinValue ? learnerReferenceData.ResidualAssessmentPrice2EffectiveDate : learnerReferenceData.ResidualTrainingPrice2EffectiveDate
});
financialRecords.Add(new FinancialRecord
{
Code = 4,
Type = "TNP",
Amount = learnerReferenceData.ResidualAssessmentPrice2 == 0 ? learnerReferenceData.ResidualAssessmentPrice1 : learnerReferenceData.ResidualAssessmentPrice2,
Date = learnerReferenceData.ResidualAssessmentPrice2EffectiveDate == DateTime.MinValue ? learnerReferenceData.ResidualTrainingPrice2EffectiveDate : learnerReferenceData.ResidualAssessmentPrice2EffectiveDate
});
}
////////////////////////////////////////////////////////////////////
// Old style agreed price
////////////////////////////////////////////////////////////////////
if (financialRecords.Count == 0)
{
financialRecords.Add(new FinancialRecord
{
Code = 1,
Type = "TNP",
Amount = agreedTrainingPrice,
Date = learnerReferenceData.StartDate
});
if (learnerReferenceData.FrameworkCode <= 0)
{
financialRecords.Add(new FinancialRecord
{
Code = 2,
Type = "TNP",
Amount = agreedAssesmentPrice,
Date = learnerReferenceData.StartDate
});
}
}
return(financialRecords.ToArray());
}
internal static void SavePaymentDue(string requiredPaymentId,
long ukprn,
long uln,
CommitmentReferenceData commitment,
string learnRefNumber,
string collectionPeriodName,
int collectionPeriodMonth,
int collectionPeriodYear,
int transactionType,
decimal amountDue,
IlrLearnerReferenceData learningDetails)
{
if (TestEnvironment.ValidateSpecsOnly)
{
return;
}
using (var connection = new SqlConnection(TestEnvironment.Variables.DedsDatabaseConnectionString))
{
var isDas = learningDetails.LearnerType == LearnerType.ProgrammeOnlyNonDas ||
learningDetails.LearnerType == LearnerType.ProgrammeOnlyNonDas1618 ||
learningDetails.LearnerType == LearnerType.ProgrammeOnlyNonDas1924? false: true;
connection.Execute("INSERT INTO PaymentsDue.RequiredPayments (" +
"Id," +
"CommitmentId," +
"CommitmentVersionId," +
"AccountId," +
"AccountVersionId," +
"uln," +
"LearnRefNumber," +
"AimSeqNumber," +
"Ukprn," +
"DeliveryMonth," +
"DeliveryYear," +
"CollectionPeriodName," +
"CollectionPeriodMonth," +
"CollectionPeriodYear," +
"TransactionType," +
"AmountDue," +
"StandardCode," +
"ProgrammeType," +
"FrameworkCode," +
"PathwayCode," +
"ApprenticeshipContractType," +
"SfaContributionPercentage," +
"FundingLineType," +
"UseLevyBalance," +
"LearnAimRef," +
"LearningStartDate," +
"IlrSubmissionDateTime," +
"PriceEpisodeIdentifier" +
") VALUES (" +
"@requiredPaymentId," +
"@commitmentId," +
"@versionId," +
"@EmployerAccountId," +
"@accountVersionId," +
"@uln," +
"@learnRefNumber," +
"@aimSequenceNumber," +
"@ukprn," +
"@collectionPeriodMonth," +
"@collectionPeriodYear," +
"@collectionPeriodName," +
"@collectionPeriodMonth," +
"@collectionPeriodYear," +
"@transactionType," +
"@amountDue," +
"@standardCode," +
"@programmeType," +
"@frameworkCode," +
"@pathwayCode," +
"@contractType," +
"@sfaContributionPercentage," +
"@fundingLineType," +
"@useLevyBalance," +
"@learnAimRef," +
"@StartDate," +
"@IlrSubmissionDateTime," +
"'2-403-1-06/05/2017'" +
")",
new
{
requiredPaymentId,
commitmentId = !isDas? (long?)null : commitment.CommitmentId,
VersionId = !isDas ? null : commitment.VersionId,
EmployerAccountId = !isDas ? (long?)null : commitment.EmployerAccountId,
accountVersionId = !isDas ? null : commitment.VersionId.Split('-')[1],
uln,
learnRefNumber,
ukprn,
collectionPeriodName,
collectionPeriodMonth,
collectionPeriodYear,
transactionType,
amountDue,
StandardCode = learningDetails.StandardCode == 0 ? null : (int?)learningDetails.StandardCode,
ProgrammeType = learningDetails.ProgrammeType == 0 ? null : (int?)learningDetails.ProgrammeType,
FrameworkCode = learningDetails.FrameworkCode == 0 ? null : (int?)learningDetails.FrameworkCode,
PathwayCode = learningDetails.PathwayCode == 0 ? null : (int?)learningDetails.PathwayCode,
ContractType = isDas ? 1 : 2,
UseLevyBalance = isDas ? 1 : 0,
SfaContributionPercentage = 0.90,
FundingLineType = isDas? "19 + Apprenticeship(From May 2017) Levy Contract" : "19 + Apprenticeship(From May 2017) Non-Levy Contract",
LearnAimRef = String.IsNullOrEmpty(learningDetails.LearnAimRef) ? "ZPROG001" : learningDetails.LearnAimRef,
StartDate = learningDetails.StartDate,
AimSequenceNumber = learningDetails.AimSequenceNumber == 0 ? 1 : learningDetails.AimSequenceNumber,
ilrSubmissiondateTime = DateTime.Now.AddMonths(-3)
});
}
}
