internal static void AddCommitment(CommitmentReferenceData commitment)
{
if (TestEnvironment.ValidateSpecsOnly)
{
return;
}
using (var connection = new SqlConnection(TestEnvironment.Variables.DedsDatabaseConnectionString))
{
connection.Execute("INSERT INTO dbo.DasCommitments " +
"(CommitmentId, VersionId, Uln, Ukprn, AccountId, StartDate, EndDate, AgreedCost, StandardCode, ProgrammeType, FrameworkCode, PathwayCode, PaymentStatus, PaymentStatusDescription, Priority, EffectiveFromDate, EffectiveToDate) " +
"VALUES" +
"(@CommitmentId, @VersionId, @Uln, @Ukprn, @AccountId, @StartDate, @EndDate, @AgreedCost, @StandardCode, @ProgrammeType, @FrameworkCode, @PathwayCode, @PaymentStatus, @PaymentStatusDescription, @Priority, @EffectiveFromDate, @EffectiveToDate)",
new
{
CommitmentId = commitment.CommitmentId,
VersionId = commitment.VersionId,
Uln = commitment.Uln,
Ukprn = commitment.Ukprn,
AccountId = commitment.EmployerAccountId,
StartDate = commitment.StartDate,
EndDate = commitment.EndDate,
AgreedCost = commitment.AgreedPrice,
StandardCode = commitment.StandardCode,
ProgrammeType = commitment.ProgrammeType,
FrameworkCode = commitment.FrameworkCode,
PathwayCode = commitment.PathwayCode,
PaymentStatus = (int)commitment.Status,
PaymentStatusDescription = commitment.Status.ToString(),
Priority = commitment.Priority,
EffectiveFromDate = commitment.EffectiveFrom,
EffectiveToDate = commitment.EffectiveTo
});
}
}
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);
}
}
}
internal static void SavePaymentDue(string requiredPaymentId,
long ukprn,
long uln,
int?FrameworkCode,
int?PathwayCode,
int?ProgrammeType,
long?StandardCode,
CommitmentReferenceData commitment,
string learnRefNumber,
string collectionPeriodName,
int collectionPeriodMonth,
int collectionPeriodYear,
int transactionType,
ContractType contractType,
decimal amountDue)
{
if (TestEnvironment.ValidateSpecsOnly)
{
return;
}
using (var connection = new SqlConnection(TestEnvironment.Variables.DedsDatabaseConnectionString))
{
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," +
"UseLevyBalance" +
") VALUES (" +
"@requiredPaymentId," +
"@commitmentId," +
"@versionId," +
"@EmployerAccountId," +
"@accountVersionId," +
"@uln," +
"@learnRefNumber," +
"1," +
"@ukprn," +
"@collectionPeriodMonth," +
"@collectionPeriodYear," +
"@collectionPeriodName," +
"@collectionPeriodMonth," +
"@collectionPeriodYear," +
"@transactionType," +
"@amountDue," +
"@standardCode," +
"@programmeType," +
"@frameworkCode," +
"@pathwayCode," +
"@contractType," +
"0.9," +
"1" +
")",
new
{
requiredPaymentId,
commitmentId = commitment == null ? (long?)null : commitment.CommitmentId,
VersionId = commitment == null ? 0 : commitment.VersionId,
EmployerAccountId = commitment == null ? 0 : commitment.EmployerAccountId,
accountVersionId = commitment == null ? 0 : commitment.VersionId,
uln,
learnRefNumber,
ukprn,
collectionPeriodName,
collectionPeriodMonth,
collectionPeriodYear,
transactionType,
amountDue,
StandardCode = StandardCode,
ProgrammeType = ProgrammeType,
FrameworkCode = FrameworkCode,
PathwayCode = PathwayCode,
contractType
});
}
}
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)
});
}
}
