public async Task <BlindAssessmentOutcome> GetBlindAssessmentOutcome(Guid applicationId, int sequenceNumber, int sectionNumber, string pageId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var blindAssessmentOutcomeResults = await connection.QueryAsync <BlindAssessmentOutcome>(
@"SELECT outcome.[ApplicationId]
,outcome.[SequenceNumber]
,outcome.[SectionNumber]
,outcome.[PageId]
,apply.[Assessor1Name]
,outcome.[Assessor1UserId]
,outcome.[Assessor1ReviewStatus]
,outcome.[Assessor1ReviewComment]
,apply.[Assessor2Name]
,outcome.[Assessor2UserId]
,outcome.[Assessor2ReviewStatus]
,outcome.[Assessor2ReviewComment]
FROM [dbo].[AssessorPageReviewOutcome] outcome
INNER JOIN [dbo].[Apply] apply ON outcome.ApplicationId = apply.ApplicationId
WHERE outcome.[ApplicationId] = @applicationId AND
outcome.[SequenceNumber] = @sequenceNumber AND
outcome.[SectionNumber] = @sectionNumber AND
outcome.[PageId] = @pageId AND
(apply.[Assessor1UserId] IS NULL OR outcome.[Assessor1UserId] = apply.[Assessor1UserId]) AND
(apply.[Assessor2UserId] IS NULL OR outcome.[Assessor2UserId] = apply.[Assessor2UserId])",
new { applicationId, sequenceNumber, sectionNumber, pageId });
return(blindAssessmentOutcomeResults.FirstOrDefault());
}
}
public async Task <Contact> CreateContact(string email, string givenName, string familyName)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
await connection.ExecuteAsync(@"INSERT INTO Contacts (Email, GivenNames, FamilyName, SignInType, CreatedAt, CreatedBy, Status)
VALUES (@email, @givenName, @familyName, 'ASLogin', @createdAt, @email, 'New')",
new { email, givenName, familyName, createdAt = DateTime.UtcNow });
return(await GetContactByEmail(email));
}
}
public async Task <int> CreateOrganisationAddresses(OrganisationAddresses organisationAddresses)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
organisationAddresses.Id = await connection.QuerySingleAsync <int>(@"INSERT INTO OrganisationAddresses (OrganisationId, AddressType, AddressLine1, AddressLine2, AddressLine3, City, Postcode)" +
"OUTPUT INSERTED.[Id] " + "VALUES (@OrganisationId, @AddressType, @AddressLine1, @AddressLine2, @AddressLine3, @City, @Postcode)",
new { organisationAddresses.OrganisationId, organisationAddresses.AddressType, organisationAddresses.AddressLine1, organisationAddresses.AddressLine2,
organisationAddresses.AddressLine3, organisationAddresses.City, organisationAddresses.Postcode });
return(organisationAddresses.Id);
}
}
public async Task <OversightReview> GetByApplicationId(Guid applicationId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return(await connection.QuerySingleOrDefaultAsync <OversightReview>(
"select * from OversightReview where ApplicationId = @applicationId",
new
{
applicationId
}));
}
}
public async Task Commit()
{
if (_operations.Count == 0)
{
return;
}
using (var connection = _dbConnectionHelper.GetDatabaseConnection() as SqlConnection)
{
await connection.OpenAsync();
using (_transaction = connection.BeginTransaction())
{
foreach (var o in _operations)
{
await o();
}
_transaction.Commit();
}
_transaction = null;
}
Clear();
}
public async Task <IEnumerable <FatDataExportDto> > GetFatDataExport()
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return((await connection.QueryAsync <FatDataExportDto>(FatDataExportStoredProcedure,
commandType: CommandType.StoredProcedure)).ToList());
}
}
public async Task <List <BankHoliday> > GetBankHolidays()
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return((await connection.QueryAsync <BankHoliday>(
@"SELECT * FROM BankHoliday where Active=1")).ToList());
}
}
public async Task <Apply> GetApplication(Guid applicationId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return(await connection.QuerySingleOrDefaultAsync <Apply>(
@"SELECT * FROM apply WHERE ApplicationId = @applicationId",
new { applicationId }));
}
}
public async Task <bool> CanUkprnStartApplication(int ukprn)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return(await connection.QuerySingleAsync <bool>(@"SELECT
CASE WHEN EXISTS
(
SELECT UKPRN
FROM AllowedProviders
WHERE UKPRN = @ukprn
AND GETUTCDATE() BETWEEN StartDateTime and EndDateTime
)
THEN 'TRUE'
ELSE 'FALSE'
END",
new { ukprn }));
}
}
public async Task <bool> AddOrganisationStatusEvents(long ukprn, int organisationStatusId, DateTime createdOn)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var sql = $@"INSERT INTO [dbo].[OrganisationStatusEvent]
([OrganisationStatusId]
,[CreatedOn]
,[ProviderId]) " +
"VALUES " +
"(@organisationStatusId, @createdOn, @ukprn)";
var eventsCreated = await connection.ExecuteAsync(sql,
new
{
organisationStatusId,
createdOn,
ukprn
});
return(eventsCreated > 0);
}
}
public async Task <DuplicateCheckResponse> DuplicateUKPRNExists(Guid organisationId, long ukprn)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var sql = "select LegalName AS DuplicateOrganisationName, " +
"CASE WHEN LegalName IS NOT NULL THEN 1 ELSE 0 END AS DuplicateFound, " +
"Id AS DuplicateOrganisationId " +
"FROM [Organisations] " +
"WHERE UKPRN = @ukprn " +
"AND Id != @organisationId";
var results = await connection.QueryAsync <DuplicateCheckResponse>(sql, new { organisationId, ukprn });
var duplicate = results.FirstOrDefault();
if (duplicate == null)
{
return(new DuplicateCheckResponse {
DuplicateFound = false
});
}
return(duplicate);
}
}
public async Task <IEnumerable <ProviderType> > GetProviderTypes()
{
var results = _cacheHelper.Get <ProviderType>();
if (results != null)
{
return(results.ToList());
}
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var sql = $"select Id, ProviderType AS [Type], Description, " +
"CreatedAt, CreatedBy, UpdatedAt, UpdatedBy, Status " +
"from [ProviderTypes] " +
"order by Id";
var providerTypes = await connection.QueryAsync <ProviderType>(sql);
_cacheHelper.Cache(providerTypes);
return(providerTypes);
}
}
public async Task <EmailTemplate> GetEmailTemplate(string templateName)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var sql =
"SELECT * " +
"FROM [EmailTemplates] " +
"WHERE TemplateName = @templateName " +
"AND Status = 'Live'";
var emailTemplates = await connection.QueryAsync <EmailTemplate>(sql, new { templateName });
return(emailTemplates.FirstOrDefault());
}
}
public async Task <PendingOversightReviews> GetPendingOversightReviews(string searchTerm, string sortColumn, string sortOrder)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var orderByClause = $"{GetSortColumnForNew(sortColumn)} { GetOrderByDirection(sortOrder)}";
var reviews = (await connection.QueryAsync <PendingOversightReview>($@"SELECT
apply.ApplicationId AS ApplicationId,
apply.ApplicationStatus,
org.Name AS OrganisationName,
apply.GatewayReviewStatus,
fr.Status as FinancialReviewStatus,
apply.ModerationStatus AS ModerationReviewStatus,
apply.UKPRN,
REPLACE(JSON_VALUE(apply.ApplyData, '$.ApplyDetails.ProviderRouteName'),' provider','') AS ProviderRoute,
JSON_VALUE(apply.ApplyData, '$.ApplyDetails.ReferenceNumber') AS ApplicationReferenceNumber,
JSON_VALUE(apply.ApplyData, '$.ApplyDetails.ApplicationSubmittedOn') AS ApplicationSubmittedDate
FROM Apply apply
INNER JOIN Organisations org ON org.Id = apply.OrganisationId
LEFT JOIN OversightReview r ON r.ApplicationId = apply.ApplicationId
LEFT OUTER JOIN FinancialReview fr on fr.ApplicationId = apply.ApplicationId
LEFT JOIN Appeal Appeal on apply.ApplicationId = Appeal.ApplicationId
WHERE apply.DeletedAt IS NULL
AND Appeal.Status IS NULL
AND ( @searchString = '%%' OR apply.UKPRN LIKE @searchString OR org.Name LIKE @searchString )
and r.Status is null
and ((GatewayReviewStatus in (@gatewayReviewStatusPass)
and AssessorReviewStatus in (@assessorReviewStatusApproved,@assessorReviewStatusDeclined)
and fr.Status in (@financialReviewStatusApproved,@financialReviewStatusDeclined, @financialReviewStatusExempt))
OR GatewayReviewStatus in (@gatewayReviewStatusFail, @gatewayReviewStatusRejected)
OR apply.ApplicationStatus = @applicationStatusRemoved)
ORDER BY {orderByClause}, org.Name ASC", new
{
searchString = $"%{searchTerm}%",
gatewayReviewStatusPass = GatewayReviewStatus.Pass,
gatewayReviewStatusFail = GatewayReviewStatus.Fail,
GatewayReviewStatusRejected = GatewayReviewStatus.Rejected,
assessorReviewStatusApproved = AssessorReviewStatus.Approved,
assessorReviewStatusDeclined = AssessorReviewStatus.Declined,
financialReviewStatusApproved = FinancialReviewStatus.Pass,
financialReviewStatusDeclined = FinancialReviewStatus.Fail,
financialReviewStatusExempt = FinancialReviewStatus.Exempt,
applicationStatusRemoved = ApplicationStatus.Removed
})).ToList();
return(new PendingOversightReviews
{
Reviews = reviews
});
}
}
private async Task <ApplyData> GetApplyData(Guid applicationId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return(await connection.QueryFirstOrDefaultAsync <ApplyData>(@"SELECT ApplyData FROM Apply WHERE ApplicationId = @applicationId",
new { applicationId }));
}
}
public async Task <Guid> CreateOrganisation(Organisation organisation, Guid userId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var organisationId = await connection.QuerySingleAsync <Guid>(
"INSERT INTO [Organisations] ([Id],[Name],[TradingName],[OrganisationType],[OrganisationUKPRN], " +
"[CompanyRegistrationNumber],[CharityRegistrationNumber],[OrganisationDetails],[Status],[CreatedAt],[CreatedBy],[RoATPApproved]) " +
"OUTPUT INSERTED.[Id] " +
"VALUES (NEWID(), @Name, @TradingName, REPLACE(@OrganisationType, ' ', ''), @OrganisationUkprn, @CompanyNumber, @CharityNumber, @OrganisationDetails, 'New', GETUTCDATE(), @CreatedBy, @RoATPApproved)",
new { organisation.Name, organisation.OrganisationDetails.TradingName, organisation.OrganisationType, organisation.OrganisationUkprn,
organisation.OrganisationDetails.CompanyNumber, organisation.OrganisationDetails.CharityNumber,
organisation.OrganisationDetails, organisation.CreatedBy, organisation.RoATPApproved });
connection.Execute(
"UPDATE [Contacts] " +
"SET ApplyOrganisationID = @Id " +
"WHERE Id = @userId",
new { Id = organisationId, userId });
return(organisationId);
}
}
public async Task <Guid> StartApplication(Guid applicationId, ApplyData applyData, Guid organisationId, Guid createdBy)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
return(await connection.QuerySingleAsync <Guid>(
@"INSERT INTO Apply (ApplicationId, OrganisationId, ApplicationStatus, ApplyData, AssessorReviewStatus, GatewayReviewStatus, CreatedBy, CreatedAt)
OUTPUT INSERTED.[ApplicationId]
VALUES (@applicationId, @organisationId, @applicationStatus, @applyData, @assessorReviewStatus, @gatewayReviewStatus,@createdBy, GETUTCDATE())",
new { applicationId, organisationId, applicationStatus = ApplicationStatus.InProgress, applyData, assessorReviewStatus = AssessorReviewStatus.Draft, gatewayReviewStatus = GatewayReviewStatus.Draft, createdBy }));
}
}
public async Task <Guid?> CreateOrganisation(CreateOrganisationCommand command)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var startDate = command.StartDate;
var organisationId = Guid.NewGuid();
var createdAt = DateTime.Now;
var createdBy = command.Username;
var providerTypeId = command.ProviderTypeId;
var organisationTypeId = command.OrganisationTypeId;
var statusId = command.OrganisationStatusId;
var organisationData = new OrganisationData
{
CompanyNumber = command.CompanyNumber?.ToUpper(),
CharityNumber = command.CharityNumber,
ParentCompanyGuarantee = command.ParentCompanyGuarantee,
FinancialTrackRecord = command.FinancialTrackRecord,
NonLevyContract = command.NonLevyContract,
StartDate = startDate,
SourceIsUKRLP = command.SourceIsUKRLP,
ApplicationDeterminedDate = command.ApplicationDeterminedDate
};
string sql = $"INSERT INTO [dbo].[Organisations] " +
" ([Id] " +
",[CreatedAt] " +
",[CreatedBy] " +
",[StatusId] " +
",[ProviderTypeId] " +
",[OrganisationTypeId] " +
",[UKPRN] " +
",[LegalName] " +
",[TradingName] " +
",[StatusDate] " +
",[OrganisationData]) " +
"VALUES " +
"(@organisationId, @createdAt, @createdBy, @statusId, @providerTypeId, @organisationTypeId," +
" @ukprn, @legalName, @tradingName, @statusDate, @organisationData)";
var organisationsCreated = await connection.ExecuteAsync(sql,
new
{
organisationId,
createdAt,
createdBy,
statusId,
providerTypeId,
organisationTypeId,
command.Ukprn,
command.LegalName,
command.TradingName,
command.StatusDate,
organisationData
});
if (organisationsCreated > 0)
{
return(organisationId);
}
return(null);
}
}
public async Task <Organisation> GetOrganisation(Guid organisationId)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
const string sql = "select * from [Organisations] o " +
"inner join ProviderTypes pt on o.ProviderTypeId = pt.Id " +
"inner join OrganisationTypes ot on o.OrganisationTypeId = ot.Id " +
"inner join OrganisationStatus os on o.StatusId = os.Id " +
"where o.Id = @organisationId";
var organisations = await connection.QueryAsync <Organisation, ProviderType, OrganisationType,
OrganisationStatus, Organisation>
(sql, (org, providerType, type, status) => {
org.OrganisationType = type;
org.ProviderType = providerType;
org.OrganisationStatus = status;
return(org);
},
new { organisationId });
return(organisations.FirstOrDefault());
}
}
public async Task <bool> UpdateOrganisation(UpdateOrganisationCommand command)
{
var organisationId = command.OrganisationId;
var providerTypeId = command.ProviderTypeId;
var organisationTypeId = command.OrganisationTypeId;
var legalName = command.LegalName;
var tradingName = command.TradingName;
var companyNumber = command.CompanyNumber;
var charityNumber = command.CharityNumber;
var updatedBy = command.Username;
var applicationDeterminedDateValue = command.ApplicationDeterminedDate.ToString(RoatpDateTimeFormat);
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var updatedAt = DateTime.Now;
const string sql = "update [Organisations] SET LegalName = @legalName,organisationTypeId=@organisationTypeId, providerTypeId=@providerTypeId, tradingName= @tradingName," +
" OrganisationData = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(OrganisationData, '$.ApplicationDeterminedDate', @applicationDeterminedDateValue),'$.CompanyNumber',@companyNumber),'$.CharityNumber',@CharityNumber), " +
" UpdatedBy = @updatedBy, UpdatedAt = @updatedAt " +
" WHERE Id = @organisationId";
int recordsAffected = await connection.ExecuteAsync(sql, new { legalName, organisationTypeId, providerTypeId, tradingName, companyNumber,
charityNumber, applicationDeterminedDateValue, updatedBy, updatedAt, organisationId });
return(recordsAffected > 0);
}
}
public async Task <List <AssessorApplicationSummary> > GetNewAssessorApplications(string userId, string searchTerm, string sortColumn, string sortOrder)
{
using (var connection = _dbConnectionHelper.GetDatabaseConnection())
{
var orderByClause = $"{GetSortColumnForNew(sortColumn)} { GetOrderByDirection(sortOrder)}";
return((await connection
.QueryAsync <AssessorApplicationSummary>(
$@"SELECT
{ApplicationSummaryFields}
FROM Apply apply
INNER JOIN Organisations org ON org.Id = apply.OrganisationId
WHERE {NewApplicationsWhereClause} AND {SearchStringWhereClause}
ORDER BY {orderByClause}, org.Name ASC",
new
{
gatewayReviewStatusApproved = GatewayReviewStatus.Pass,
applicationStatusGatewayAssessed = ApplicationStatus.GatewayAssessed,
userId = userId,
searchString = $"%{searchTerm}%"
})).ToList());
}
}