public DomainEmployer GetById(int employerId, bool currentOnly = true)
{
var employer =
_getOpenConnection.Query <Employer>("SELECT * FROM dbo.Employer WHERE EmployerId = @EmployerId" + (currentOnly ? " AND EmployerStatusTypeId != 2" : ""),
new { EmployerId = employerId }).SingleOrDefault();
return(MapEmployers(new [] { employer }).Single());
}
public IEnumerable <ApiUser> GetApiUsers()
{
var sql = SelectApiUsersSql + " ORDER BY FullName";
var externalSystemPermissions = _getOpenConnection.Query <ExternalSystem, ExternalSystemPermission, ExternalSystem>(sql,
(es, esp) =>
{
es.ExternalSystemPermission = esp;
return(es);
}, splitOn: "Username");
return(externalSystemPermissions.Select(MapApiUser));
}
public ProviderSite GetById(int providerSiteId)
{
_logger.Debug("Getting provider site with ProviderSiteId={0}", providerSiteId);
const string sql = "SELECT * FROM dbo.ProviderSite WHERE ProviderSiteId = @providerSiteId";
var sqlParams = new
{
providerSiteId
};
var dbProviderSite = _getOpenConnection.Query <Entities.ProviderSite>(sql, sqlParams).SingleOrDefault();
_logger.Debug(dbProviderSite == null
? "Did not find provider site with ProviderSiteId={0}"
: "Got provider site with ProviderSiteId={0}",
providerSiteId);
if (dbProviderSite == null)
{
return(null);
}
var providerSiteRelationships = GetProviderIdByProviderSiteId(new List <int> {
dbProviderSite.ProviderSiteId
});
return(MapProviderSite(dbProviderSite, providerSiteRelationships));
}
public AgencyUser GetByUsername(string username)
{
var user = _getOpenConnection.Query <Entities.AgencyUser>("SELECT * FROM UserProfile.AgencyUser WHERE Username = @username", new { username = username }).SingleOrDefault();
var result = _mapper.Map <Entities.AgencyUser, AgencyUser>(user);
return(result);
}
public bool IsVacancyOwnedByTargetDatabase(int vacancyId)
{
// Another implementation of this would be to add to a table with an incrementing primary key + VacancyId containing only records that are owned by us
// Upon editing a record we would add a record to this table if it didn't already exists.
// The update would look for records with a primary key higher than the last one, which could be done every second without impact.
if (DateTime.UtcNow > _vacanciesOwnedByUsLastFullyUpdatedUtc + _cacheTime)
{
_log.Debug("Refreshing _vacanciesOwnedByUs");
_vacanciesOwnedByUs = new HashSet <int>(_targetDatabase.Query <int>("SELECT VacancyId FROM Vacancy WHERE EditedInRaa = 1"));
_vacanciesOwnedByUsLastFullyUpdatedUtc = DateTime.UtcNow;
}
return(_vacanciesOwnedByUs.Contains(vacancyId));
}
public List <VacancyLocation> GetForVacancyId(int vacancyId)
{
_logger.Debug("Calling database to get vacancy with locations for vacancy with Id={0}", vacancyId);
var vacancyLocations =
_getOpenConnection.Query <Entities.VacancyLocation>("SELECT * FROM dbo.VacancyLocation WHERE VacancyId = @VacancyId ORDER BY VacancyLocationId DESC",
new { VacancyId = vacancyId });
return(vacancyLocations.Select(vl =>
{
var vacancyLocation = _mapper.Map <Entities.VacancyLocation, VacancyLocation>(vl);
MapLocalAuthorityCode(vl, vacancyLocation);
MapCountyId(vl, vacancyLocation);
return vacancyLocation;
}).ToList());
}
public IList <Standard> GetStandards()
{
_logger.Debug("Getting all standards");
const string standardSql = "SELECT * FROM Reference.Standard ORDER BY FullName;";
//TODO: Does this need to be here? If not, test and remove.
var sqlParams = new
{
};
var educationLevels = GetEducationLevels();
var dbStandards = _getOpenConnection.Query <Entities.Standard>(standardSql, sqlParams);
var standards = dbStandards.Select(x =>
{
var level = educationLevels.FirstOrDefault(el => el.EducationLevelId == x.EducationLevelId);
var levelAsInt = int.Parse(level.CodeName);
var std = new Standard()
{
ApprenticeshipLevel = (ApprenticeshipLevel)levelAsInt,
Id = x.StandardId,
Name = x.FullName,
ApprenticeshipSectorId = x.StandardSectorId
};
return(std);
}).ToList();
return(standards);
}
public VacancyOwnerRelationship GetByProviderSiteAndEmployerId(int providerSiteId, int employerId)
{
const string sql = @"
SELECT * FROM dbo.VacancyOwnerRelationship
WHERE ProviderSiteID = @ProviderSiteId
AND EmployerId = @EmployerId
AND StatusTypeId = @StatusTypeId";
var sqlParams = new
{
ProviderSiteId = providerSiteId,
EmployerId = employerId,
StatusTypeId = VacancyOwnerRelationshipStatusTypes.Live
};
var vacancyOwnerRelationship = _getOpenConnection.Query <Entities.VacancyOwnerRelationship>(sql, sqlParams).SingleOrDefault();
return(_mapper.Map <Entities.VacancyOwnerRelationship, VacancyOwnerRelationship>(vacancyOwnerRelationship));
}
public Provider GetById(int providerId)
{
_logger.Debug("Getting provider with ProviderId={0}", providerId);
const string sql = "SELECT * FROM dbo.Provider WHERE ProviderId = @providerId";
var sqlParams = new
{
providerId
};
var dbProvider = _getOpenConnection.Query <Entities.Provider>(sql, sqlParams).SingleOrDefault();
_logger.Debug(dbProvider == null
? "Did not find provider with ProviderId={0}"
: "Got provider with ProviderId={0}",
providerId);
return(MapProvider(dbProvider));
}
public IDictionary <string, int> GetPersonIdsByEmails(IEnumerable <string> emails)
{
var personIds = new Dictionary <string, int>();
//There are duplicates in the person table so use the most recent based on id
var keyValuePairs = _getOpenConnection.Query <KeyValuePair <string, int> >("SELECT Email as [Key], PersonId as Value FROM Person WHERE Email in @emails ORDER BY Value", new { emails });
foreach (var keyValuePair in keyValuePairs)
{
personIds[keyValuePair.Key] = keyValuePair.Value;
}
return(personIds);
}
public ProviderUser GetById(int id)
{
_logger.Debug("Getting provider user with Id={0}", id);
const string sql = ProviderUserSelect + "ProviderUserId = @ProviderUserId";
var sqlParams = new
{
ProviderUserId = id
};
var dbProviderUser = _getOpenConnection
.Query <Entities.ProviderUser>(sql, sqlParams)
.SingleOrDefault();
_logger.Debug(dbProviderUser == null
? "Did not find provider user with id=\"{0}\""
: "Got provider user with id=\"{0}\"", id);
return(MapProviderUser(dbProviderUser));
}
public ApprenticeshipVacancy Get(Guid id)
{
_logger.Debug("Calling database to get apprenticeship vacancy with Id={0}", id);
var dbVacancy = _getOpenConnection.Query <Vacancy.Vacancy>("SELECT * FROM Vacancy.Vacancy WHERE VacancyId = @VacancyGuid", new { VacancyGuid = id }).SingleOrDefault();
return(MapVacancy(dbVacancy));
}
public IDictionary <Guid, int> GetAllCandidateIds()
{
return(_getOpenConnection.Query <KeyValuePair <Guid, int> >("SELECT CandidateGuid as [Key], CandidateId as Value FROM Candidate").ToDictionary(kvp => kvp.Key, kvp => kvp.Value));
}
public IDictionary <int, SubVacancy> GetApplicationSummariesByIds(IEnumerable <int> applicationIds)
{
return(_getOpenConnection.Query <SubVacancy>("SELECT * FROM SubVacancy WHERE AllocatedApplicationId in @applicationIds", new { applicationIds }).ToDictionary(a => a.AllocatedApplicationId, a => a));
}
public List <CandidateSummary> SearchCandidates(CandidateSearchRequest request)
{
_logService.Debug("Calling repository to find candidates matching search request {0}", request);
if (string.IsNullOrEmpty(request.FirstName) && string.IsNullOrEmpty(request.LastName) && request.DateOfBirth == null && string.IsNullOrEmpty(request.Postcode) && string.IsNullOrEmpty(request.CandidateGuidPrefix) && !request.CandidateId.HasValue)
{
throw new ArgumentException("You must specify at least one search parameter");
}
var query = new List <string>(4);
if (!string.IsNullOrEmpty(request.FirstName))
{
query.Add("p.FirstName LIKE @FirstName + '%'");
}
if (!string.IsNullOrEmpty(request.LastName))
{
query.Add("p.Surname LIKE @LastName + '%'");
}
if (request.DateOfBirth.HasValue)
{
query.Add("c.DateofBirth = @DateOfBirth");
}
if (!string.IsNullOrEmpty(request.Postcode))
{
query.Add("c.Postcode LIKE @Postcode + '%'");
}
if (!string.IsNullOrEmpty(request.CandidateGuidPrefix))
{
query.Add("c.CandidateGuid LIKE @CandidateGuidPrefix + '%'");
}
if (request.CandidateId.HasValue)
{
query.Add("c.CandidateId = @CandidateId");
}
if (request.ProviderSiteIds != null)
{
query.Add("(VacancyManagerId IN @providerSiteIds OR DeliveryOrganisationId IN @providerSiteIds)");
}
if (request.HasSubmittedApplications)
{
query.Add("a.ApplicationStatusTypeId >= 2");
}
var sql =
@"SELECT DISTINCT c.CandidateId, c.CandidateGuid, p.FirstName, p.MiddleNames, p.Surname, c.DateofBirth,
c.AddressLine1, c.AddressLine2, c.AddressLine3, c.AddressLine4, c.Postcode, c.Town, ct.FullName As County, c.Latitude, c.Longitude
FROM Person p
JOIN Candidate c ON p.PersonId = c.PersonId
JOIN County ct on c.CountyId = ct.CountyId
LEFT JOIN [Application] a ON c.CandidateId = a.CandidateId
LEFT JOIN Vacancy v ON a.VacancyId = v.VacancyId
WHERE " + string.Join(" AND ", query);
var candidates = CandidateMapper.Map <IEnumerable <DbCandidateSummary>, IEnumerable <CandidateSummary> >(
_getOpenConnection.Query <DbCandidateSummary>(sql,
new
{
request.FirstName,
request.LastName,
request.DateOfBirth,
request.Postcode,
request.CandidateGuidPrefix,
request.CandidateId,
request.ProviderSiteIds
})).ToList();
_logService.Debug("Found {1} candidates matching search request {0}", request, candidates.Count);
return(candidates);
}
/// <summary>
/// Return cached data for the query and associated parameters if available, otherwise query the data and add it to the cache. In the query case
/// it is very similar to Dapper's IDbConnection.Query except:
/// <list type="bullet">
/// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item>
/// <item><description>The result is always entirely loaded and returned as an IList ("buffered" cannot be set to false)</description></item>
/// <item><description>Transient errors are automatically retried</description></item>
/// <item><description>Transactions are not supported (in order to support retries)</description></item>
/// </list>
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TReturn"></typeparam>
/// <param name="goc"></param>
/// <param name="cacheDuration">This is advisory only. It end up being cached for less time (perhaps due to memory limitations) or longer
/// (perhaps if the query fails, or is slow).</param>
/// <param name="sql"></param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="splitOn"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
/// <remarks>Once the first value has been returned any transient errors will not be retried. Ideally the caller would be carrying out an idempotent operation and would retry from the beginning.</remarks>
public static IList <TReturn> QueryCached <TFirst, TSecond, TReturn>(this IGetOpenConnection goc, TimeSpan cacheDuration, string sql, Func <TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
// TODO: Implement caching. Consider using older values in case of error / slow response
return(goc.Query <TFirst, TSecond, TReturn>(sql, map, param, splitOn, commandTimeout, commandType));
}
private Vacancy GetBy(int vacancyId)
{
var sqlParams = new
{
vacancyId
};
var sql = $@"SELECT v.*,
--CASE v.ApplyOutsideNAVMS
-- WHEN 1 THEN 0
-- ELSE dbo.GetApplicantCount(v.VacancyId)
--END
--AS ApplicantCount,
--CASE v.ApplyOutsideNAVMS
-- WHEN 1 THEN 0
-- ELSE dbo.GetNewApplicantCount(v.VacancyId)
--END
--AS NewApplicantCount,
dbo.GetFirstSubmittedDate(v.VacancyID) AS DateFirstSubmitted,
dbo.GetSubmittedDate(v.VacancyID) AS DateSubmitted,
dbo.GetCreatedDate(v.VacancyID) AS CreatedDate,
e.FullName AS EmployerName,
e.EmployerId,
e.Town AS EmployerLocation,
af.CodeName AS FrameworkCodeName,
el.CodeName AS ApprenticeshipLevel,
ao.CodeName AS SectorCodeName,
dbo.GetCreatedByProviderUsername(v.VacancyId) AS CreatedByProviderUsername,
dbo.GetDateQAApproved(v.VacancyId) AS DateQAApproved,
rt.TeamName AS RegionalTeam,
af.StandardId,
aq1.Question AS FirstQuestion,
aq2.Question AS SecondQuestion,
TBP.Value AS TrainingProvided,
QR.Value AS DesiredQualifications,
SR.Value AS DesiredSkills,
PQ.Value AS PersonalQualities,
RC.Value AS ThingsToConsider,
FP.Value AS FutureProspects,
OII.Value AS OtherInformation,
TIT.Comments AS TitleComment,
ALE.Comments AS ApprenticeshipLevelComment,
CLD.Comments AS ClosingDateComment,
CDE.Comments AS ContactDetailsComment,
QUA.Comments AS DesiredQualificationsComment,
SKL.Comments AS DesiredSkillsComment,
EAD.Comments AS DurationComment,
EDE.Comments AS EmployerDescriptionComment,
EWB.Comments AS EmployerWebsiteUrlComment,
QU1.Comments AS FirstQuestionComment,
QU2.Comments AS SecondQuestionComment,
APF.Comments AS FrameworkCodeNameComment,
FUT.Comments AS FutureProspectsComment,
FDE.Comments AS LongDescriptionComment,
NPO.Comments AS NumberOfPositionsComment,
OAI.Comments AS OfflineApplicationInstructionsComment,
OAU.Comments AS OfflineApplicationUrlComment,
PER.Comments AS PersonalQualitiesComment,
PSD.Comments AS PossibleStartDateComment,
APO.Comments AS SectorCodeNameComment,
SDE.Comments AS ShortDescriptionComment,
[SID].Comments AS StandardIdComment,
IOI.Comments AS ThingsToConsiderComment,
TRP.Comments AS TrainingProvidedComment,
WWG.Comments AS WageComment,
WWK.Comments AS WorkingWeekComment,
LAD.Comments AS LocationAddressesComment,
ALI.Comments AS AdditionalLocationInformationComment,
AED.Comments AS AnonymousEmployerDescriptionComment,
AER.Comments AS AnonymousEmployerReasonComment,
AAE.Comments AS AnonymousAboutTheEmployerComment,
la.CodeName AS LocalAuthorityCode,
v.DurationTypeId AS DurationType,
v.DurationValue AS Duration,
c.FullName AS County
FROM Vacancy v
JOIN VacancyOwnerRelationship o
ON o.VacancyOwnerRelationshipId = v.VacancyOwnerRelationshipId
JOIN Employer e
ON o.EmployerId = e.EmployerId
JOIN ProviderSite s
ON s.ProviderSiteId = v.VacancyManagerId
LEFT OUTER JOIN ApprenticeshipFramework af
ON af.ApprenticeshipFrameworkId = v.ApprenticeshipFrameworkId
LEFT OUTER JOIN ApprenticeshipType AS at
ON at.ApprenticeshipTypeId = v.ApprenticeshipType
LEFT OUTER JOIN Reference.EducationLevel el
ON el.EducationLevelId = at.EducationLevelId
LEFT OUTER JOIN ApprenticeshipOccupation ao
ON v.SectorId = ao.ApprenticeshipOccupationId
LEFT OUTER JOIN Reference.[Standard] rs
ON rs.FullName = af.FullName
LEFT OUTER JOIN RegionalTeamMappings t
ON s.PostCode LIKE t.PostcodeStart + '[0-9]%'
LEFT OUTER JOIN RegionalTeams rt
ON rt.Id = t.RegionalTeam_Id
LEFT OUTER JOIN AdditionalQuestion aq1
ON aq1.VacancyId = v.VacancyId AND aq1.QuestionId = 1
LEFT OUTER JOIN AdditionalQuestion aq2
ON aq2.VacancyId = v.VacancyId AND aq2.QuestionId = 2
LEFT OUTER JOIN LocalAuthority la
ON la.LocalAuthorityId = v.LocalAuthorityId
LEFT OUTER JOIN County c
ON c.CountyId = v.CountyId
LEFT OUTER JOIN VacancyTextField TBP ON TBP.VacancyId = v.VacancyId AND TBP.Field = dbo.GetTextFieldId('TBP')
LEFT OUTER JOIN VacancyTextField QR ON QR.VacancyId = v.VacancyId AND QR.Field = dbo.GetTextFieldId('QR')
LEFT OUTER JOIN VacancyTextField SR ON SR.VacancyId = v.VacancyId AND SR.Field = dbo.GetTextFieldId('SR')
LEFT OUTER JOIN VacancyTextField PQ ON PQ.VacancyId = v.VacancyId AND PQ.Field = dbo.GetTextFieldId('PQ')
LEFT OUTER JOIN VacancyTextField RC ON RC.VacancyId = v.VacancyId AND RC.Field = dbo.GetTextFieldId('RC')
LEFT OUTER JOIN VacancyTextField FP ON FP.VacancyId = v.VacancyId AND FP.Field = dbo.GetTextFieldId('FP')
LEFT OUTER JOIN VacancyTextField OII ON OII.VacancyId = v.VacancyId AND OII.Field = dbo.GetTextFieldId('OII')
LEFT OUTER JOIN VacancyReferralComments TIT ON TIT.VacancyId = v.VacancyId AND TIT.FieldTypeId = dbo.GetCommentFieldId('TIT')
LEFT OUTER JOIN VacancyReferralComments ALE ON ALE.VacancyId = v.VacancyId AND ALE.FieldTypeId = dbo.GetCommentFieldId('ALE')
LEFT OUTER JOIN VacancyReferralComments CLD ON CLD.VacancyId = v.VacancyId AND CLD.FieldTypeId = dbo.GetCommentFieldId('CLD')
LEFT OUTER JOIN VacancyReferralComments CDE ON CDE.VacancyId = v.VacancyId AND CDE.FieldTypeId = dbo.GetCommentFieldId('CDE')
LEFT OUTER JOIN VacancyReferralComments QUA ON QUA.VacancyId = v.VacancyId AND QUA.FieldTypeId = dbo.GetCommentFieldId('QUA')
LEFT OUTER JOIN VacancyReferralComments SKL ON SKL.VacancyId = v.VacancyId AND SKL.FieldTypeId = dbo.GetCommentFieldId('SKL')
LEFT OUTER JOIN VacancyReferralComments EAD ON EAD.VacancyId = v.VacancyId AND EAD.FieldTypeId = dbo.GetCommentFieldId('EAD')
LEFT OUTER JOIN VacancyReferralComments EDE ON EDE.VacancyId = v.VacancyId AND EDE.FieldTypeId = dbo.GetCommentFieldId('EDE')
LEFT OUTER JOIN VacancyReferralComments EWB ON EWB.VacancyId = v.VacancyId AND EWB.FieldTypeId = dbo.GetCommentFieldId('EWB')
LEFT OUTER JOIN VacancyReferralComments QU1 ON QU1.VacancyId = v.VacancyId AND QU1.FieldTypeId = dbo.GetCommentFieldId('QU1')
LEFT OUTER JOIN VacancyReferralComments QU2 ON QU2.VacancyId = v.VacancyId AND QU2.FieldTypeId = dbo.GetCommentFieldId('QU2')
LEFT OUTER JOIN VacancyReferralComments APF ON APF.VacancyId = v.VacancyId AND APF.FieldTypeId = dbo.GetCommentFieldId('APF')
LEFT OUTER JOIN VacancyReferralComments FUT ON FUT.VacancyId = v.VacancyId AND FUT.FieldTypeId = dbo.GetCommentFieldId('FUT')
LEFT OUTER JOIN VacancyReferralComments FDE ON FDE.VacancyId = v.VacancyId AND FDE.FieldTypeId = dbo.GetCommentFieldId('FDE')
LEFT OUTER JOIN VacancyReferralComments NPO ON NPO.VacancyId = v.VacancyId AND NPO.FieldTypeId = dbo.GetCommentFieldId('NPO')
LEFT OUTER JOIN VacancyReferralComments OAI ON OAI.VacancyId = v.VacancyId AND OAI.FieldTypeId = dbo.GetCommentFieldId('OAI')
LEFT OUTER JOIN VacancyReferralComments OAU ON OAU.VacancyId = v.VacancyId AND OAU.FieldTypeId = dbo.GetCommentFieldId('OAU')
LEFT OUTER JOIN VacancyReferralComments PER ON PER.VacancyId = v.VacancyId AND PER.FieldTypeId = dbo.GetCommentFieldId('PER')
LEFT OUTER JOIN VacancyReferralComments PSD ON PSD.VacancyId = v.VacancyId AND PSD.FieldTypeId = dbo.GetCommentFieldId('PSD')
LEFT OUTER JOIN VacancyReferralComments APO ON APO.VacancyId = v.VacancyId AND APO.FieldTypeId = dbo.GetCommentFieldId('APO')
LEFT OUTER JOIN VacancyReferralComments SDE ON SDE.VacancyId = v.VacancyId AND SDE.FieldTypeId = dbo.GetCommentFieldId('SDE')
LEFT OUTER JOIN VacancyReferralComments [SID] ON [SID].VacancyId = v.VacancyId AND [SID].FieldTypeId = dbo.GetCommentFieldId('SID')
LEFT OUTER JOIN VacancyReferralComments IOI ON IOI.VacancyId = v.VacancyId AND IOI.FieldTypeId = dbo.GetCommentFieldId('IOI')
LEFT OUTER JOIN VacancyReferralComments TRP ON TRP.VacancyId = v.VacancyId AND TRP.FieldTypeId = dbo.GetCommentFieldId('TRP')
LEFT OUTER JOIN VacancyReferralComments WWG ON WWG.VacancyId = v.VacancyId AND WWG.FieldTypeId = dbo.GetCommentFieldId('WWG')
LEFT OUTER JOIN VacancyReferralComments WWK ON WWK.VacancyId = v.VacancyId AND WWK.FieldTypeId = dbo.GetCommentFieldId('WWK')
LEFT OUTER JOIN VacancyReferralComments LAD ON LAD.VacancyId = v.VacancyId AND LAD.FieldTypeId = dbo.GetCommentFieldId('LAD')
LEFT OUTER JOIN VacancyReferralComments ALI ON ALI.VacancyId = v.VacancyId AND ALI.FieldTypeId = dbo.GetCommentFieldId('ALI')
LEFT OUTER JOIN VacancyReferralComments AED ON AED.VacancyId = v.VacancyId AND AED.FieldTypeId = dbo.GetCommentFieldId('AED')
LEFT OUTER JOIN VacancyReferralComments AER ON AER.VacancyId = v.VacancyId AND AER.FieldTypeId = dbo.GetCommentFieldId('AER')
LEFT OUTER JOIN VacancyReferralComments AAE ON AAE.VacancyId = v.VacancyId AND AAE.FieldTypeId = dbo.GetCommentFieldId('AAE')
WHERE v.VacancyID = @vacancyId
";
var vacancy = _getOpenConnection.Query <Vacancy>(sql, sqlParams).SingleOrDefault();
return(vacancy);
}
public int GetNextVacancyReferenceNumber()
{
const string sql = "SELECT NEXT VALUE FOR dbo.VacancyReferenceNumberSequence";
return(_connection.Query <int>(sql).Single());
}
/// <summary>
/// Return cached data for the query and associated parameters if available, otherwise query the data and add it to the cache. In the query case
/// it is very similar to Dapper's IDbConnection.Query except:
/// <list type="bullet">
/// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item>
/// <item><description>The result is always entirely loaded and returned as an IList ("buffered" cannot be set to false)</description></item>
/// <item><description>Transient errors are automatically retried</description></item>
/// <item><description>Transactions are not supported (in order to support retries)</description></item>
/// </list>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="goc"></param>
/// <param name="cacheDuration">This is advisory only. It end up being cached for less time (perhaps due to memory limitations) or longer
/// (perhaps if the query fails, or is slow).</param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
/// <remarks>Once the first value has been returned any transient errors will not be retried. Ideally the caller would be carrying out an idempotent operation and would retry from the beginning.</remarks>
public static IList <T> QueryCached <T>(this IGetOpenConnection goc, TimeSpan cacheDuration, string sql, object param = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
// TODO: Implement caching. Consider using older values in case of error / slow response
return(goc.Query <T>(sql, param, commandTimeout, commandType));
}
public IDictionary <Guid, ApplicationIds> GetApplicationIdsByGuid(IEnumerable <Guid> applicationGuids)
{
return(_getOpenConnection.Query <ApplicationIds>("SELECT ApplicationId, CandidateId, VacancyId, ApplicationGuid FROM Application WHERE ApplicationGuid in @applicationGuids", new { applicationGuids }).ToDictionary(a => a.ApplicationGuid, a => a));
}
public IDictionary <int, int> GetLocalAuthorityCountyIds()
{
return(_getOpenConnection.Query <KeyValuePair <int, int> >("SELECT LocalAuthorityId as [Key], CountyId as Value FROM LocalAuthority").ToDictionary(kvp => kvp.Key, kvp => kvp.Value));
}
public IList <VacancySummary> GetSummariesForProvider(VacancySummaryQuery query, out int totalRecords)
{
var sqlParams = new
{
Skip = query.PageSize * (query.RequestedPage - 1),
Take = query.PageSize,
QueryMode = query.SearchMode,
Query = query.SearchString,
query.ProviderId,
query.ProviderSiteId
};
string orderByField = "";
switch (query.OrderByField)
{
case VacancySummaryOrderByColumn.Title:
orderByField = "v.Title";
break;
case VacancySummaryOrderByColumn.Applications:
orderByField = @"(CASE v.ApplyOutsideNAVMS
WHEN 1 THEN COALESCE(v.NoOfOfflineApplicants, 0)
ELSE dbo.GetApplicantCount(v.VacancyId)
END)" ;
break;
case VacancySummaryOrderByColumn.Employer:
orderByField = "e.FullName";
break;
case VacancySummaryOrderByColumn.Location:
orderByField = $"v.Town {(query.Order == Order.Descending ? "DESC" : "")}, v.PostCode";
break;
}
if (query.OrderByField == VacancySummaryOrderByColumn.OrderByFilter)
{
switch (query.Filter)
{
case VacanciesSummaryFilterTypes.ClosingSoon:
case VacanciesSummaryFilterTypes.NewApplications:
orderByField = @"v.ApplicationClosingDate, CreatedDate"; //created date
query.Order = Order.Descending;
break;
case VacanciesSummaryFilterTypes.Closed:
case VacanciesSummaryFilterTypes.Live:
case VacanciesSummaryFilterTypes.Completed:
orderByField = "e.FullName, v.Title";
break;
case VacanciesSummaryFilterTypes.All:
case VacanciesSummaryFilterTypes.Submitted:
case VacanciesSummaryFilterTypes.Rejected:
case VacanciesSummaryFilterTypes.Draft:
// Requirement is "most recently created first" (Faizal 30/6/2016).
// Previously there was no ordering in the code and it was coming out in natural database order
orderByField = "CreatedDate";
query.Order = Order.Descending;
break;
default:
throw new ArgumentException($"{query.Filter}");
}
}
var filterSql = GetFilterSql(query.Filter);
var sql = $@"{CoreQuery}
JOIN ProviderSiteRelationship r
ON r.ProviderSiteId = o.ProviderSiteId
WHERE o.ProviderSiteID = @ProviderSiteId
AND (v.VacancyManagerId = @ProviderSiteId
OR v.DeliveryOrganisationId = @ProviderSiteId)
AND r.ProviderId = @providerId
AND r.ProviderSiteRelationshipTypeId = 1
AND (v.VacancyTypeId = {(int)query.VacancyType} OR v.VacancyTypeId = {(int)VacancyType.Unknown})
--Text search
AND ((@query IS NULL OR @query = '')
OR ((CAST(v.VacancyReferenceNumber AS VARCHAR(255)) = @query AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.ReferenceNumber}'))
OR (v.Title LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.VacancyTitle}'))
OR (e.FullName LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.EmployerName}'))
OR (REPLACE(v.PostCode, ' ', '') LIKE REPLACE(@query, ' ', '') + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.Postcode}')))
)
AND v.VacancyStatusId != 4
{filterSql}
ORDER BY {orderByField} {(query.Order == Order.Descending ? "DESC" : "")}
OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY";
var vacancies = _getOpenConnection.Query <DbVacancySummary>(sql, sqlParams);
// return the total record count as well
totalRecords = vacancies.Any() ? vacancies.First().TotalResultCount : 0;
var mapped = Mapper.Map <IList <DbVacancySummary>, IList <VacancySummary> >(vacancies);
return(mapped);
}