public void Setup()
{
_registerRepository = new Mock <IRegisterRepository>();
_cleanserService = new Mock <ISpecialCharacterCleanserService>();
_validator = new Mock <IEpaOrganisationValidator>();
_logger = new Mock <ILogger <CreateEpaOrganisationStandardHandler> >();
_organisationId = "EPA999";
_requestNoIssuesId = 1;
_requestNoIssues = BuildRequest(_organisationId, 123321, new List <int> {
1
});
_expectedOrganisationStandardNoIssues = BuildOrganisationStandard(_requestNoIssues, _requestNoIssuesId);
_registerRepository.Setup(r => r.CreateEpaOrganisationStandard(It.IsAny <EpaOrganisationStandard>(), new List <int> {
1
}))
.Returns(Task.FromResult(_expectedOrganisationStandardNoIssues.Id.ToString()));
_validator.Setup(v => v.ValidatorCreateEpaOrganisationStandardRequest(_requestNoIssues)).Returns(new ValidationResponse());
_cleanserService.Setup(c => c.CleanseStringForSpecialCharacters(It.IsAny <string>()))
.Returns((string s) => s);
_createEpaOrganisationStandardHandler = new CreateEpaOrganisationStandardHandler(_registerRepository.Object, _validator.Object, _logger.Object, _cleanserService.Object);
}
private static EpaOrganisationStandard MapOrganisationStandardRequestToOrganisationStandard(CreateEpaOrganisationStandardRequest request)
{
Guid?contactId = null;
if (Guid.TryParse(request.ContactId, out var contactIdGuid))
{
contactId = contactIdGuid;
}
var organisationStandard = new EpaOrganisationStandard
{
OrganisationId = request.OrganisationId,
StandardCode = request.StandardCode,
StandardReference = request.StandardReference,
StandardVersions = request.StandardVersions,
EffectiveFrom = request.EffectiveFrom,
EffectiveTo = request.EffectiveTo,
DateStandardApprovedOnRegister = request.DateStandardApprovedOnRegister,
Comments = request.Comments,
ContactId = contactId,
OrganisationStandardData = new OrganisationStandardData {
DeliveryAreasComments = request.DeliveryAreasComments
}
};
return(organisationStandard);
}
public async Task <string> CreateEpaOrganisationStandard(EpaOrganisationStandard organisationStandard, List <int> deliveryAreas)
{
using (var connection = new SqlConnection(_configuration.SqlConnectionString))
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var osdaId = connection.Query <string>(
"INSERT INTO [dbo].[OrganisationStandard] ([EndPointAssessorOrganisationId],[StandardCode],[EffectiveFrom],[EffectiveTo],[DateStandardApprovedOnRegister] ,[Comments],[Status], [ContactId], [OrganisationStandardData]) VALUES (" +
"@organisationId, @standardCode, @effectiveFrom, @effectiveTo, getutcdate(), @comments, 'Live', @ContactId, @OrganisationStandardData); SELECT CAST(SCOPE_IDENTITY() as varchar); ",
new
{
organisationStandard.OrganisationId, organisationStandard.StandardCode,
organisationStandard.EffectiveFrom, organisationStandard.EffectiveTo,
organisationStandard.DateStandardApprovedOnRegister, organisationStandard.Comments,
organisationStandard.ContactId, organisationStandard.OrganisationStandardData
}).Single();
foreach (var deliveryAreaId in deliveryAreas.Distinct())
{
connection.Execute("INSERT INTO OrganisationStandardDeliveryArea ([OrganisationStandardId],DeliveryAreaId, Status) VALUES " +
"(@osdaId, @deliveryAreaId,'Live'); ",
new { osdaId, deliveryAreaId }
);
}
return(osdaId);
}
}
private static EpaOrganisationStandard MapOrganisationStandardRequestToOrganisationStandard(UpdateEpaOrganisationStandardRequest request)
{
Guid?contactId = null;
if (!string.IsNullOrEmpty(request.ContactId) && Guid.TryParse(request.ContactId, out Guid contactIdGuid))
{
contactId = contactIdGuid;
}
var organisationStandard = new EpaOrganisationStandard
{
Id = request.OrganisationStandardId,
OrganisationId = request.OrganisationId,
StandardCode = request.StandardCode,
EffectiveFrom = request.EffectiveFrom,
EffectiveTo = request.EffectiveTo,
Comments = request.Comments,
ContactId = contactId,
OrganisationStandardData = new OrganisationStandardData {
DeliveryAreasComments = request.DeliveryAreasComments
}
};
return(organisationStandard);
}
public async Task <string> UpdateEpaOrganisationStandard(EpaOrganisationStandard orgStandard,
List <int> deliveryAreas)
{
using (var connection = new SqlConnection(_configuration.SqlConnectionString))
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var osdaId = connection.Query <string>(
"UPDATE [OrganisationStandard] SET [EffectiveFrom] = @effectiveFrom, [EffectiveTo] = @EffectiveTo, " +
"[Comments] = @comments, [ContactId] = @contactId, [OrganisationStandardData] = @organisationStandardData, [Status]='Live' " +
"WHERE [EndPointAssessorOrganisationId] = @organisationId and [StandardCode] = @standardCode; SELECT top 1 id from [organisationStandard] where [EndPointAssessorOrganisationId] = @organisationId and [StandardCode] = @standardCode;",
new
{
orgStandard.EffectiveFrom,
orgStandard.EffectiveTo,
orgStandard.Comments,
orgStandard.ContactId,
orgStandard.OrganisationId,
orgStandard.StandardCode,
orgStandard.OrganisationStandardData
}).Single();
connection.Execute(
"Delete from OrganisationStandardDeliveryArea where OrganisationStandardId = @osdaId and DeliveryAreaId not in @deliveryAreas",
new { osdaId, deliveryAreas });
foreach (var deliveryAreaId in deliveryAreas.Distinct())
{
connection.Execute(
"IF NOT EXISTS (select * from OrganisationStandardDeliveryArea where OrganisationStandardId = @osdaId and DeliveryAreaId = @DeliveryAreaId) " +
"INSERT INTO OrganisationStandardDeliveryArea ([OrganisationStandardId],DeliveryAreaId, Status) VALUES " +
"(@osdaId, @deliveryAreaId,'Live'); ",
new { osdaId, deliveryAreaId }
);
}
connection.Execute(
"UPDATE [OrganisationStandard] SET [DateStandardApprovedOnRegister] = getutcdate() where Id = @osdaId and [DateStandardApprovedOnRegister] is null",
new { osdaId }
);
return(osdaId);
}
}
public async Task <string> CreateEpaOrganisationStandard(EpaOrganisationStandard organisationStandard, List <int> deliveryAreas)
{
var sqlToSelectExisting =
"select Id FROM [OrganisationStandard] " +
"WHERE EndPointAssessorOrganisationId = @organisationId and standardCode = @standardCode";
var orgStandardId = await _unitOfWork.Connection.ExecuteScalarAsync <int>(sqlToSelectExisting, new { organisationId = organisationStandard.OrganisationId, standardCode = organisationStandard.StandardCode });
if (default(int) == orgStandardId)
{
orgStandardId = (await _unitOfWork.Connection.QueryAsync <int>(
"INSERT INTO [dbo].[OrganisationStandard] ([EndPointAssessorOrganisationId],[StandardCode],[EffectiveFrom],[EffectiveTo],[DateStandardApprovedOnRegister] ,[Comments],[Status], [ContactId], [OrganisationStandardData], StandardReference) VALUES (" +
"@organisationId, @standardCode, @effectiveFrom, @effectiveTo, getutcdate(), @comments, 'Live', @ContactId, @OrganisationStandardData, @StandardReference); SELECT CAST(SCOPE_IDENTITY() as varchar); ",
new
{
organisationStandard.OrganisationId,
organisationStandard.StandardCode,
organisationStandard.EffectiveFrom,
organisationStandard.EffectiveTo,
organisationStandard.DateStandardApprovedOnRegister,
organisationStandard.Comments,
organisationStandard.ContactId,
organisationStandard.OrganisationStandardData,
organisationStandard.StandardReference
})).Single();
foreach (var deliveryAreaId in deliveryAreas.Distinct())
{
await _unitOfWork.Connection.ExecuteAsync(
"INSERT INTO OrganisationStandardDeliveryArea ([OrganisationStandardId],DeliveryAreaId, Status) VALUES " +
"(@osdaId, @deliveryAreaId,'Live'); ",
new { osdaId = orgStandardId, deliveryAreaId });
}
}
else
{
// Fix StandardReference on the existing record
await _unitOfWork.Connection.ExecuteAsync(
"UPDATE [dbo].[OrganisationStandard] SET StandardReference = @standardReference WHERE Id = @id",
new
{
standardReference = organisationStandard.StandardReference,
id = orgStandardId
});
}
if (null != organisationStandard.StandardVersions)
{
foreach (var version in organisationStandard.StandardVersions)
{
var standardUid = $"{organisationStandard.StandardReference.Trim()}_{version.Trim()}";
await _unitOfWork.Connection.ExecuteAsync(
"INSERT INTO OrganisationStandardVersion (StandardUid, Version, OrganisationStandardId, EffectiveFrom, EffectiveTo, DateVersionApproved, Comments, Status) " +
"VALUES(@StandardUid, @Version, @OrganisationStandardId, @EffectiveFrom, @EffectiveTo, @DateVersionApproved, @Comments, 'Live')",
new
{
standardUid,
version,
OrganisationStandardId = orgStandardId,
organisationStandard.EffectiveFrom,
organisationStandard.EffectiveTo,
DateVersionApproved = organisationStandard.DateStandardApprovedOnRegister,
organisationStandard.Comments
});
}
}
return(orgStandardId.ToString());
}
public async Task <string> UpdateEpaOrganisationStandardAndOrganisationStandardVersions(EpaOrganisationStandard orgStandard,
List <int> deliveryAreas, bool applyFollowingWithdrawal = false)
{
var osdaId = (await _unitOfWork.Connection.QueryAsync <string>(
"UPDATE [OrganisationStandard] SET [EffectiveFrom] = @effectiveFrom, [EffectiveTo] = @EffectiveTo, " +
"[Comments] = @comments, [ContactId] = @contactId, [OrganisationStandardData] = @organisationStandardData, [Status]='Live' " +
"WHERE [EndPointAssessorOrganisationId] = @organisationId and [StandardCode] = @standardCode; SELECT top 1 id from [organisationStandard] where [EndPointAssessorOrganisationId] = @organisationId and [StandardCode] = @standardCode;",
new
{
orgStandard.EffectiveFrom,
orgStandard.EffectiveTo,
orgStandard.Comments,
orgStandard.ContactId,
orgStandard.OrganisationId,
orgStandard.StandardCode,
orgStandard.OrganisationStandardData
})).Single();
await _unitOfWork.Connection.ExecuteAsync(
"Delete from OrganisationStandardDeliveryArea where OrganisationStandardId = @osdaId and DeliveryAreaId not in @deliveryAreas",
new { osdaId, deliveryAreas });
foreach (var deliveryAreaId in deliveryAreas.Distinct())
{
await _unitOfWork.Connection.ExecuteAsync(
"IF NOT EXISTS (select * from OrganisationStandardDeliveryArea where OrganisationStandardId = @osdaId and DeliveryAreaId = @DeliveryAreaId) " +
"INSERT INTO OrganisationStandardDeliveryArea ([OrganisationStandardId],DeliveryAreaId, Status) VALUES " +
"(@osdaId, @deliveryAreaId,'Live'); ",
new { osdaId, deliveryAreaId });
}
await _unitOfWork.Connection.ExecuteAsync(
"UPDATE [OrganisationStandard] SET [DateStandardApprovedOnRegister] = getutcdate() where Id = @osdaId and [DateStandardApprovedOnRegister] is null",
new { osdaId });
if (null != orgStandard.StandardVersions && applyFollowingWithdrawal)
{
foreach (var version in orgStandard.StandardVersions)
{
var standardUid = $"{orgStandard.StandardReference.Trim()}_{version.Trim()}";
await _unitOfWork.Connection.ExecuteAsync(
"IF NOT EXISTS (select * from OrganisationStandardVersion where StandardUId = @StandardUid and Version = @version and OrganisationStandardId = @OrganisationStandardId) " +
"INSERT INTO OrganisationStandardVersion (StandardUid, Version, OrganisationStandardId, EffectiveFrom, EffectiveTo, DateVersionApproved, Comments, Status) " +
"VALUES(@StandardUid, @Version, @OrganisationStandardId, @EffectiveFrom, @EffectiveTo, @DateVersionApproved, @Comments, 'Live') " +
"ELSE " +
"UPDATE [OrganisationStandardVersion] SET[EffectiveFrom] = @effectiveFrom, [EffectiveTo] = @effectiveTo " +
"WHERE StandardUId = @StandardUid and Version = @version and OrganisationStandardId = @OrganisationStandardId"
,
new
{
standardUid,
version,
OrganisationStandardId = osdaId,
orgStandard.EffectiveFrom,
orgStandard.EffectiveTo,
DateVersionApproved = orgStandard.DateStandardApprovedOnRegister,
orgStandard.Comments
});
}
}
else
{
await _unitOfWork.Connection.ExecuteAsync(
@"UPDATE [OrganisationStandardVersion]
SET [EffectiveFrom] = @effectiveFrom,
[EffectiveTo] = @effectiveTo
WHERE
[OrganisationStandardId] = @id",
new
{
orgStandard.EffectiveFrom,
orgStandard.EffectiveTo,
orgStandard.Id
});
}
return(osdaId);
}
