public async Task <OrganisationSearchResults> OrganisationSearchByUkPrn(string ukPrn)
{
long ukPrnValue = Convert.ToInt64(ukPrn);
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 UKPRN = @ukPrnValue";
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 { ukPrnValue });
var searchResults = new OrganisationSearchResults
{
SearchResults = organisations,
TotalCount = organisations.Count()
};
return(searchResults);
}
}
public void Organisation_search_by_company_name()
{
_validator.Setup(x => x.IsValidUKPRN(It.IsAny <string>())).Returns(false);
var organisations = new List <Organisation>
{
new Organisation {
UKPRN = 10001234, LegalName = "TEST PROVIDER"
}
};
var organisationSearchResults = new OrganisationSearchResults
{
SearchResults = organisations,
TotalCount = 1
};
_repository.Setup(x => x.OrganisationSearchByName(It.IsAny <string>())).ReturnsAsync(organisationSearchResults);
_repository.Setup(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()));
var organisationSearchRequest = new OrganisationSearchRequest {
SearchTerm = "TEST"
};
Task <OrganisationSearchResults> searchResults =
_organisationSearchHandler.Handle(organisationSearchRequest, new CancellationToken());
searchResults.Result.SearchResults.Count().Should().Be(1);
_repository.Verify(x => x.OrganisationSearchByName(It.IsAny <string>()), Times.Once);
_repository.Verify(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()), Times.Never);
}
public void Organisation_search_will_not_search_by_legal_name_if_valid_UKPRN_but_no_match_found()
{
_validator.Setup(x => x.IsValidUKPRN(It.IsAny <string>())).Returns(true);
var organisationSearchResults = new OrganisationSearchResults
{
SearchResults = new List <Organisation>(),
TotalCount = 0
};
_repository.Setup(x => x.OrganisationSearchByName(It.IsAny <string>())).ReturnsAsync(organisationSearchResults);
_repository.Setup(x => x.OrganisationSearchByUkPrn(It.IsAny <string>())).ReturnsAsync(organisationSearchResults);
var organisationSearchRequest = new OrganisationSearchRequest {
SearchTerm = "10002345"
};
Task <OrganisationSearchResults> searchResults =
_organisationSearchHandler.Handle(organisationSearchRequest, new CancellationToken());
searchResults.Result.SearchResults.Count().Should().Be(0);
searchResults.Result.TotalCount.Should().Be(0);
_repository.Verify(x => x.OrganisationSearchByName(It.IsAny <string>()), Times.Never);
_repository.Verify(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()), Times.Once);
}
protected async Task <IActionResult> RefreshSearchResults(string searchTerm = null)
{
if (String.IsNullOrWhiteSpace(searchTerm))
{
var searchModel = _sessionService.GetSearchResults();
searchTerm = searchModel.SearchTerm;
}
OrganisationSearchResults searchResults = await _apiClient.Search(searchTerm);
var viewModel = new OrganisationSearchResultsViewModel
{
SearchTerm = searchTerm,
Title = BuildSearchResultsTitle(searchResults.TotalCount, searchTerm),
SearchResults = searchResults.SearchResults,
TotalCount = searchResults.TotalCount,
SelectedIndex = 0
};
_sessionService.SetSearchResults(viewModel);
var actionName = "SearchResults";
if (searchResults.TotalCount == 0)
{
actionName = "NoSearchResults";
}
return(RedirectToAction(actionName, "RoatpSearch"));
}
public async Task <OrganisationSearchResults> OrganisationSearchByName(string organisationName)
{
int rowLimit = 5;
int.TryParse(_appConfiguration["OrganisationSearchResultsLimit"], out rowLimit);
var connectionString = _webConfiguration.SqlConnectionString;
var organisationNameFilter = $"%{organisationName}%";
using (var connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var sql = $"select top (@rowLimit) * 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.LegalName LIKE @organisationNameFilter " +
"order by legalname asc; " +
"select count(*) from[Organisations] " +
"where LegalName like @organisationNameFilter";
var searchQuery = await connection.QueryMultipleAsync
(sql, new { rowLimit, organisationNameFilter });
var results =
searchQuery.Read <Organisation, ProviderType, OrganisationType, OrganisationStatus, Organisation>(
(org, providerType, type, status) =>
{
org.OrganisationType = type;
org.ProviderType = providerType;
org.OrganisationStatus = status;
return(org);
});
var resultCount = searchQuery.ReadFirst <int>();
var searchResult = new OrganisationSearchResults
{
SearchResults = results,
TotalCount = resultCount
};
return(await Task.FromResult(searchResult));
}
}
public void Organisation_search_with_more_results_than_result_limit()
{
_validator.Setup(x => x.IsValidUKPRN(It.IsAny <string>())).Returns(false);
var organisations = new List <Organisation>
{
new Organisation {
UKPRN = 10001001, LegalName = "TEST PROVIDER 1"
},
new Organisation {
UKPRN = 10001002, LegalName = "TEST PROVIDER 2"
},
new Organisation {
UKPRN = 10001003, LegalName = "TEST PROVIDER 3"
},
new Organisation {
UKPRN = 10001004, LegalName = "TEST PROVIDER 4"
},
new Organisation {
UKPRN = 10001005, LegalName = "TEST PROVIDER 5"
}
};
var organisationSearchResults = new OrganisationSearchResults
{
SearchResults = organisations,
TotalCount = 17
};
_repository.Setup(x => x.OrganisationSearchByName(It.IsAny <string>())).ReturnsAsync(organisationSearchResults);
_repository.Setup(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()));
var organisationSearchRequest = new OrganisationSearchRequest {
SearchTerm = "TEST PROVIDER"
};
Task <OrganisationSearchResults> searchResults =
_organisationSearchHandler.Handle(organisationSearchRequest, new CancellationToken());
searchResults.Result.SearchResults.Count().Should().Be(5);
searchResults.Result.TotalCount.Should().Be(17);
_repository.Verify(x => x.OrganisationSearchByName(It.IsAny <string>()), Times.Once);
_repository.Verify(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()), Times.Never);
}
public void Organisation_search_by_company_name_with_no_results_found()
{
_validator.Setup(x => x.IsValidUKPRN(It.IsAny <string>())).Returns(false);
var emptyResults = new OrganisationSearchResults
{
SearchResults = new List <Organisation>(),
TotalCount = 0
};
_repository.Setup(x => x.OrganisationSearchByName(It.IsAny <string>())).ReturnsAsync(emptyResults);
_repository.Setup(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()));
var organisationSearchRequest = new OrganisationSearchRequest {
SearchTerm = "10001234"
};
Task <OrganisationSearchResults> searchResults =
_organisationSearchHandler.Handle(organisationSearchRequest, new CancellationToken());
searchResults.Result.SearchResults.Should().BeEmpty();
_repository.Verify(x => x.OrganisationSearchByName(It.IsAny <string>()), Times.Once);
_repository.Verify(x => x.OrganisationSearchByUkPrn(It.IsAny <string>()), Times.Never);
}
public async Task <OrganisationSearchResults> OrganisationSearchByUkPrn(string ukPrn)
{
long ukPrnValue = Convert.ToInt64(ukPrn);
var connectionString = _webConfiguration.SqlConnectionString;
using (var connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var 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 UKPRN = @ukPrnValue";
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 { ukPrnValue });
var searchResults = new OrganisationSearchResults
{
SearchResults = organisations,
TotalCount = organisations.Count()
};
return(await Task.FromResult(searchResults));
}
}