private List <DomainEmployer> MapEmployers(IReadOnlyCollection <Employer> employers)
{
var results = new List <DomainEmployer>(employers.Count);
var countyIds = employers.Where(e => e.CountyId > 0).Select(e => e.CountyId).Distinct();
var countyIdsMap = _getOpenConnection.QueryCached <County>(_cacheDuration, @"
SELECT *
FROM dbo.County
WHERE CountyId IN @countyIds",
new
{
countyIds
}).ToDictionary(c => c.CountyId, c => c.FullName);
foreach (var employer in employers)
{
var result = _mapper.Map <Employer, DomainEmployer>(employer);
if (countyIdsMap.ContainsKey(employer.CountyId))
{
result.Address.County = countyIdsMap[employer.CountyId];
}
results.Add(result);
}
return(results);
}
private void PopulateLocalAuthorityId(VacancyLocation entity, Entities.VacancyLocation dbVacancyLocation)
{
if (!string.IsNullOrWhiteSpace(entity.LocalAuthorityCode))
{
dbVacancyLocation.LocalAuthorityId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT LocalAuthorityId
FROM dbo.LocalAuthority
WHERE CodeName = @LocalAuthorityCode",
new
{
entity.LocalAuthorityCode
}).Single();
}
else
{
dbVacancyLocation.LocalAuthorityId = null;
}
}
public DomainVacancy GetByReferenceNumber(int vacancyReferenceNumber)
{
_logger.Debug("Calling database to get apprenticeship vacancy with Vacancy Reference Number={0}",
vacancyReferenceNumber);
var vacancyId = _getOpenConnection.QueryCached <int?>(_cacheDuration, @"
SELECT VacancyId
FROM dbo.Vacancy
WHERE VacancyReferenceNumber = @vacancyReferenceNumber",
new
{
vacancyReferenceNumber
}).SingleOrDefault();
return(vacancyId == null ? null : Get(vacancyId.Value));
}
private ApprenticeshipVacancy MapVacancy(Vacancy.Vacancy dbVacancy)
{
if (dbVacancy == null)
{
return(null);
}
// Vacancy
var vacancyLocations = _getOpenConnection.Query <Vacancy.VacancyLocation>(@"
SELECT *
FROM Vacancy.VacancyLocation
WHERE VacancyId = @VacancyId",
new { VacancyId = dbVacancy.VacancyId });
// TODO: Would like to make addresses immutable to allow caching - they probably don't
// change that often. Also should have access methods that don't return the address as
// most screens don't need it
var addresses = _getOpenConnection.Query <Address.PostalAddress>(@"
SELECT *
FROM Address.PostalAddress
WHERE PostalAddressId IN @PostalAddressIds",
new { PostalAddressIds = vacancyLocations.Select(l => l.PostalAddressId) /*.Union(dbVacancy.ManagerVacancyParty.PostalAddressId } */ });
var result = _mapper.Map <Vacancy.Vacancy, ApprenticeshipVacancy>(dbVacancy);
result.LocationAddresses = new List <VacancyLocationAddress>();
foreach (var dbLocation in vacancyLocations)
{
result.LocationAddresses.Add(new VacancyLocationAddress
{
NumberOfPositions = dbLocation.NumberOfPositions,
Address = _mapper.Map <Address.PostalAddress, SFA.Apprenticeships.Domain.Entities.Locations.Address>(addresses.Single(a => a.PostalAddressId == dbLocation.PostalAddressId))
});
}
// TODO: Method which looks up in cache and if not found refreshes cache / loads new record
result.Ukprn = _getOpenConnection
.QueryCached <Vacancy.VacancyParty>(TimeSpan.FromHours(1), "SELECT * FROM Vacancy.VacancyParty")
.Single(p => p.VacancyPartyId == dbVacancy.ManagerVacancyPartyId) // TODO: Verify
.UKPrn.ToString(); // TODO: Casing. TODO: Type?
// TODO: Method which looks up in cache and if not found refreshes cache / loads new record
var employer = _getOpenConnection
.QueryCached <Vacancy.VacancyParty>(TimeSpan.FromHours(1), "SELECT * FROM Vacancy.VacancyParty")
.Single(p => p.VacancyPartyId == dbVacancy.EmployerVacancyPartyId); // TODO: Verify
result.ProviderSiteEmployerLink.ProviderSiteErn = employer.EdsErn.ToString(); // TODO: Verify. TODO: Type?
result.ProviderSiteEmployerLink.Employer = new Domain.Entities.Organisations.Employer()
{
Address = new Domain.Entities.Locations.Address()
{
// TODO
},
//DateCreated = employer.DateCreated, TODO
//DateUpdated = employer.DateUpdated, TODO
//EntityId = employer.VacancyPartyId, // TODO: Verify
Ern = employer.EdsErn.ToString(), // TODO: Verify. TODO: Case. TODO: Type?
Name = employer.FullName
};
// ApprenticeshipVacancy
if (dbVacancy.FrameworkId != null)
{
// TODO: QueryCachedDictionary
result.FrameworkCodeName = _getOpenConnection
.QueryCached <Reference.Framework>(TimeSpan.FromHours(1), "SELECT * FROM Reference.Framework")
.Single(f => f.FrameworkId == dbVacancy.FrameworkId)
.CodeName;
}
// TODO: Inconsistency of location of comment fields Vacancy vs ApprenticeshipVacancy
return(result);
}