public async Task <IActionResult> Get([FromQuery] SearchTenancyRequest request)
{
var result = await _searchTenancyUseCase.ExecuteAsync(request, HttpContext.GetCancellationToken()).ConfigureAwait(false);
//We convert the result to an APIResponse via extensions on BaseController
return(HandleResponse(result));
}
public async Task GivenValidSearchTenancyRequest_WhenCallingGet_ThenShouldReturnAPIResponseListOfTenancySearch()
{
//arrange
_mock.Setup(s => s.ExecuteAsync(It.IsAny <SearchTenancyRequest>(), CancellationToken.None))
.ReturnsAsync(new SearchTenancyResponse
{
Tenancies = new List <SearchTenancySummary>
{
}
});
var request = new SearchTenancyRequest
{
SearchTerm = "test"
};
//act
var response = await _classUnderTest.Get(request).ConfigureAwait(false);
//assert
response.Should().NotBeNull();
response.Should().BeOfType <ObjectResult>();
var objectResult = response as ObjectResult;
var getContacts = objectResult?.Value as APIResponse <SearchTenancyResponse>;
getContacts.Should().NotBeNull();
}
public async Task GivenValidedInput_WhenGatewayRespondsWithNullArrearsAgreement_ThenArrearsAgreementShouldBeNull()
{
//arrange
var tenancyAgreementRef = "Test";
var results = new PagedResults <TenancyListItem>
{
Results = new List <TenancyListItem>
{
new TenancyListItem
{
ArrearsAgreementStatus = "",
ArrearsAgreementStartDate = DateTime.MinValue
}
},
TotalResultsCount = 1,
};
_fakeGateway.Setup(s => s.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None))
.ReturnsAsync(results);
var request = new SearchTenancyRequest
{
SearchTerm = tenancyAgreementRef,
PageSize = 10,
Page = 0
};
//act
var response = await _classUnderTest.ExecuteAsync(request, CancellationToken.None);
//assert
response.Should().NotBeNull();
response.Tenancies[0].Should().NotBeNull();
}
public async Task Given_InvalidInput_ThenShouldThrowBadRequestException()
{
//arrange
var request = new SearchTenancyRequest();
//act
//assert
await Assert.ThrowsAsync <BadRequestException>(async() => await _classUnderTest.ExecuteAsync(request, CancellationToken.None));
}
public async Task GivenNullInput_WhenExecuteAsync_ThenShouldThrowBadRequestException()
{
//arrange
SearchTenancyRequest request = null;
//act
//assert
// ReSharper disable once ExpressionIsAlwaysNull
await Assert.ThrowsAsync <BadRequestException>(async() => await _classUnderTest.ExecuteAsync(request, CancellationToken.None));
}
/// <summary>
/// Execute the Search Tenancy Use Case
/// </summary>
/// <param name="request"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <SearchTenancyResponse> ExecuteAsync(SearchTenancyRequest request, CancellationToken cancellationToken)
{
//validate
if (request == null)
{
//
throw new BadRequestException();
}
//validate
var validationResponse = request.Validate(request);
if (!validationResponse.IsValid)
{
throw new BadRequestException(validationResponse);
}
//Execute Gateway - which will determine how to get the data we requested
var response = await _searchGateway.SearchTenanciesAsync(request, cancellationToken).ConfigureAwait(false);
//tenancy could have no attached contacts
if (response == null)
{
return(new SearchTenancyResponse());
}
//Create real response and map to response object
var useCaseResponse = new SearchTenancyResponse
{
Tenancies = response.Results.ConvertAll(tenancy => new SearchTenancySummary
{
TenancyRef = tenancy.TenancyRef,
PropertyRef = tenancy.PropertyRef,
Tenure = tenancy.Tenure,
CurrentBalance = new Currency
{
Value = tenancy.CurrentBalance,
CurrencyCode = "GBP",
},
PrimaryContact = new PrimaryContact
{
Name = tenancy.PrimaryContactName,
ShortAddress = tenancy.PrimaryContactShortAddress,
Postcode = tenancy.PrimaryContactPostcode
}
}),
TotalCount = response.TotalResultsCount,
PageCount = response.CalculatePageCount(request.PageSize, response.TotalResultsCount)
};
return(useCaseResponse);
}
public async Task GivenValidedInput__WhenExecuteAsync_GatewayReceivesCorrectInput()
{
//arrange
var tenancyAgreementRef = "Test";
_fakeGateway.Setup(s => s.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None))
.ReturnsAsync(new PagedResults <TenancyListItem>());
var request = new SearchTenancyRequest
{
SearchTerm = tenancyAgreementRef
};
//act
await _classUnderTest.ExecuteAsync(request, CancellationToken.None);
//assert
_fakeGateway.Verify(v => v.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None));
}
public async Task GivenValidedInput_WhenGatewayRespondsWithNull_ThenContactsListShouldBeNull()
{
//arrange
var tenancyAgreementRef = "Test";
_fakeGateway.Setup(s => s.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None))
.ReturnsAsync(null as PagedResults <TenancyListItem>);
var request = new SearchTenancyRequest
{
SearchTerm = tenancyAgreementRef
};
//act
var response = await _classUnderTest.ExecuteAsync(request, CancellationToken.None);
//assert
response.Should().NotBeNull();
response.Tenancies.Should().BeNull();
}
/// <summary>
/// Search for Tenants attached to tenancies
/// </summary>
/// <param name="request"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <PagedResults <TenancyListItem> > SearchTenanciesAsync(SearchTenancyRequest request, CancellationToken cancellationToken)
{
var results = new PagedResults <TenancyListItem>();
var validate = request.Validate(request);
if (!validate.IsValid)
{
return(results);
}
string whiteSpace = " ";
//Build actual query
var queryStringBuilder = BuildQuery(request, whiteSpace);
//Build query to find out total results count
var countStringBuilder = BuildCountQuery(request, whiteSpace);
using (var conn = new SqlConnection(_connectionString))
{
//open connection explicity
conn.Open();
//get paged results
var all = await conn.QueryAsync <TenancyListItem>(queryStringBuilder.ToString(),
new { tenancyRef = request.TenancyRef, firstName = request.FirstName, lastName = request.LastName, address = request.Address, postcode = request.PostCode, page = request.Page > 0 ? request.Page - 1: 0, pageSize = request.PageSize }
).ConfigureAwait(false);
//add to paged results
results.Results = all?.ToList();
//get results total count
var totalCount = await conn.QueryAsync <int>(countStringBuilder.ToString(),
new { tenancyRef = request.TenancyRef, firstName = request.FirstName, lastName = request.LastName, address = request.Address, postcode = request.PostCode }
).ConfigureAwait(false);
//add to pages results
results.TotalResultsCount = totalCount.Sum();
//close connection explicitly - do not pool connections
//experienced sql connection issues with connection pooling due to UH database
conn.Close();
}
return(results);
}
public async Task GivenValidedInput_WhenGatewayRespondsTotalCount_ThenPageCountShouldBe(int totalCount, int pageSize, int expectedPageCount)
{
//arrange
var tenancyAgreementRef = "Test";
_fakeGateway.Setup(s => s.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None))
.ReturnsAsync(new PagedResults <TenancyListItem>
{
TotalResultsCount = totalCount
});
var request = new SearchTenancyRequest
{
SearchTerm = tenancyAgreementRef,
PageSize = pageSize
};
//act
var response = await _classUnderTest.ExecuteAsync(request, CancellationToken.None);
//assert
response.Should().NotBeNull();
response.PageCount.Should().Be(expectedPageCount);
}
public async Task GivenValidedInput__WhenExecuteAsync_ThenShouldReturnListOfTenancySummaries()
{
//arrange
var tenancy1 = new TenancyListItem
{
PrimaryContactName = "test",
TenancyRef = "tRef",
ArrearsAgreementStartDate = DateTime.Now,
ArrearsAgreementStatus = "Active",
CurrentBalance = (decimal)1000.12,
LastActionCode = "ACC",
LastActionDate = DateTime.Now.AddDays(-1),
PrimaryContactPostcode = "test",
PrimaryContactShortAddress = "123DreryLane",
PropertyRef = "2",
Tenure = "LongLease"
};
var tenancy2 = new TenancyListItem
{
PrimaryContactName = "test2",
TenancyRef = "tRef2",
ArrearsAgreementStartDate = DateTime.Now,
ArrearsAgreementStatus = "Active2",
CurrentBalance = (decimal)2000.34,
LastActionCode = "ACC2",
LastActionDate = DateTime.Now.AddDays(-2),
PrimaryContactPostcode = "test2",
PrimaryContactShortAddress = "123DreryLane2",
PropertyRef = "22",
Tenure = "LongLease2"
};
var tenancyAgreementRef = "Test";
_fakeGateway.Setup(s => s.SearchTenanciesAsync(It.Is <SearchTenancyRequest>(i => i.SearchTerm.Equals("Test")), CancellationToken.None))
.ReturnsAsync(new PagedResults <TenancyListItem>
{
Results = new List <TenancyListItem>
{
tenancy1,
tenancy2
}
});
var request = new SearchTenancyRequest
{
SearchTerm = tenancyAgreementRef
};
//act
var response = await _classUnderTest.ExecuteAsync(request, CancellationToken.None);
//assert
response.Should().NotBeNull();
response.Tenancies.Should().NotBeNullOrEmpty();
response.Tenancies[0].PropertyRef.Should().BeEquivalentTo(tenancy1.PropertyRef);
response.Tenancies[0].TenancyRef.Should().BeEquivalentTo(tenancy1.TenancyRef);
response.Tenancies[0].Tenure.Should().BeEquivalentTo(tenancy1.Tenure);
response.Tenancies[0].CurrentBalance.Should().NotBeNull();
response.Tenancies[0].CurrentBalance.Value.Should().Be(tenancy1.CurrentBalance);
response.Tenancies[0].CurrentBalance.CurrencyCode.Should().BeEquivalentTo("GBP");
response.Tenancies[0].PrimaryContact.Name.Should().BeEquivalentTo(tenancy1.PrimaryContactName);
response.Tenancies[0].PrimaryContact.Postcode.Should().BeEquivalentTo(tenancy1.PrimaryContactPostcode);
response.Tenancies[0].PrimaryContact.ShortAddress.Should().BeEquivalentTo(tenancy1.PrimaryContactShortAddress);
response.Tenancies[1].PropertyRef.Should().BeEquivalentTo(tenancy2.PropertyRef);
response.Tenancies[1].TenancyRef.Should().BeEquivalentTo(tenancy2.TenancyRef);
response.Tenancies[1].Tenure.Should().BeEquivalentTo(tenancy2.Tenure);
response.Tenancies[1].CurrentBalance.Should().NotBeNull();
response.Tenancies[1].CurrentBalance.Value.Should().Be(tenancy2.CurrentBalance);
response.Tenancies[1].CurrentBalance.CurrencyCode.Should().BeEquivalentTo("GBP");
response.Tenancies[1].PrimaryContact.Name.Should().BeEquivalentTo(tenancy2.PrimaryContactName);
response.Tenancies[1].PrimaryContact.Postcode.Should().BeEquivalentTo(tenancy2.PrimaryContactPostcode);
response.Tenancies[1].PrimaryContact.ShortAddress.Should().BeEquivalentTo(tenancy2.PrimaryContactShortAddress);
}
public async Task GivenValidGetContactsForTenancyRequest_WhenCallingGet_ThenShouldReturnListOfContactsForTenancy_AndThePropertiesShouldBeMappedCorrectly(
string firstName, string lastName)
{
//arrange
var primaryContactName = $"{firstName} {lastName}";
var postcode = "EC12 1DS";
var arrearsAgreementStatus = "status";
var arrearsStartDate = DateTime.Now;
var tenancyRef = "tenRef";
decimal currentBalance = (decimal)100.12;
var propertyRef = "propRef";
var tenure = "tenure";
_mock.Setup(s => s.ExecuteAsync(It.IsAny <SearchTenancyRequest>(), CancellationToken.None))
.ReturnsAsync(new SearchTenancyResponse
{
Tenancies = new List <SearchTenancySummary>
{
new SearchTenancySummary
{
PrimaryContact = new PrimaryContact
{
Name = primaryContactName,
Postcode = postcode
},
TenancyRef = tenancyRef,
CurrentBalance = new Currency
{
Value = currentBalance,
CurrencyCode = "GBP"
},
PropertyRef = propertyRef,
Tenure = tenure
}
}
});
var request = new SearchTenancyRequest
{
SearchTerm = "test"
};
//act
var response = await _classUnderTest.Get(request).ConfigureAwait(false);
//assert
response.Should().NotBeNull();
response.Should().BeOfType <ObjectResult>();
var objectResult = response as ObjectResult;
var getContacts = objectResult?.Value as APIResponse <SearchTenancyResponse>;
getContacts.Should().NotBeNull();
getContacts.Data.Tenancies.Should().NotBeNullOrEmpty();
getContacts.Data.Tenancies[0].PrimaryContact.Name.Should().BeEquivalentTo(primaryContactName);
getContacts.Data.Tenancies[0].PrimaryContact.Postcode.Should().BeEquivalentTo(postcode);
getContacts.Data.Tenancies[0].TenancyRef.Should().BeEquivalentTo(tenancyRef);
getContacts.Data.Tenancies[0].CurrentBalance.Should().NotBeNull();
getContacts.Data.Tenancies[0].CurrentBalance.Value.Should().Be(currentBalance);
getContacts.Data.Tenancies[0].CurrentBalance.CurrencyCode.Should().BeEquivalentTo("GBP");
getContacts.Data.Tenancies[0].PropertyRef.Should().BeEquivalentTo(propertyRef);
getContacts.Data.Tenancies[0].Tenure.Should().BeEquivalentTo(tenure);
}
/// <summary>
/// Builds the query to return the TotalCount of the resultset
/// A stored procedure would be much faster but we are unable to modify the UH Database
/// in any way or risk breaking the support agreement
/// </summary>
/// <param name="request"></param>
/// <param name="whiteSpace"></param>
/// <returns></returns>
private static StringBuilder BuildCountQuery(SearchTenancyRequest request, string whiteSpace)
{
var countStringBuilder = new StringBuilder();
countStringBuilder.AppendLine($"{whiteSpace}");
if (request.TenancyRef.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerTenancyRef nvarchar(16); SET @lowerTenancyRef = LOWER(@tenancyRef);");
}
if (request.FirstName.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerFirstName nvarchar(64); SET @lowerFirstName = LOWER(@firstName);");
}
if (request.LastName.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerLastName nvarchar(64); SET @lowerLastName = LOWER(@lastName);");
}
if (request.Address.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerAddress nvarchar(128); SET @lowerAddress = LOWER(@address);");
}
if (request.PostCode.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerPostCode nvarchar(16); SET @lowerPostCode = LOWER(@postCode);");
}
countStringBuilder.Append(
@"
SELECT count(tenagree.tag_ref)
FROM tenagree
Left JOIN dbo.member member WITH(NOLOCK)
ON member.house_ref = tenagree.house_ref
LEFT JOIN property WITH(NOLOCK)
ON property.prop_ref = tenagree.prop_ref
WHERE tenagree.tag_ref IS NOT NULL
");
if (request.TenancyRef.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine($"{whiteSpace}AND LOWER(tenagree.tag_ref) = @lowerTenancyRef");
}
if (request.FirstName.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine($"{whiteSpace}AND LOWER(member.forename) = @lowerFirstName");
}
if (request.LastName.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine($"{whiteSpace}AND LOWER(member.surname) = @lowerLastName");
}
if (request.Address.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine($@"{whiteSpace}AND LOWER(property.short_address) like '%' + @lowerAddress + '%'");
}
if (request.PostCode.IsNotNullOrEmptyOrWhiteSpace())
{
countStringBuilder.AppendLine($@"{whiteSpace}AND LOWER(property.post_code) like '%' + @lowerPostCode + '%'");
}
return(countStringBuilder);
}
/// <summary>
/// Builds a query to return the paged subset of the resultset
/// A stored procedure would be much faster but we are unable to modify the UH Database
/// in any way or risk breaking the support agreement
/// </summary>
/// <param name="request"></param>
/// <param name="whiteSpace"></param>
/// <returns></returns>
private static StringBuilder BuildQuery(SearchTenancyRequest request, string whiteSpace)
{
var queryStringBuilder = new StringBuilder();
//Add conditional Parameter declarations to query
queryStringBuilder.AppendLine($"{whiteSpace}");
if (request.TenancyRef.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerTenancyRef nvarchar(16); SET @lowerTenancyRef = LOWER(@tenancyRef);");
}
if (request.FirstName.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerFirstName nvarchar(64); SET @lowerFirstName = LOWER(@firstName);");
}
if (request.LastName.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerLastName nvarchar(64); SET @lowerLastName = LOWER(@lastName);");
}
if (request.Address.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerAddress nvarchar(128); SET @lowerAddress = LOWER(@address);");
}
if (request.PostCode.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine(
$"{whiteSpace}DECLARE @lowerPostCode nvarchar(16); SET @lowerPostCode = LOWER(@postCode);");
}
queryStringBuilder.Append(
@"
DECLARE @Upper Integer;
DECLARE @Lower integer;
if(@page = 0)
begin
Set @Lower = 1
Set @Upper = @Lower + @pageSize -1
end
if(@page > 0)
begin
Set @Lower = (@pageSize * @page) + 1
Set @Upper = @Lower + @pageSize -1
end
SELECT
Seq,
CurrentBalance,
TenancyRef,
PropertyRef,
Tenure,
PrimaryContactPostcode,
PrimaryContactShortAddress,
PrimaryContactName
FROM
(
SELECT
tenagree.cur_bal as CurrentBalance,
tenagree.tag_ref as TenancyRef,
tenagree.prop_ref as PropertyRef,
tenagree.tenure as Tenure,
property.post_code as PrimaryContactPostcode,
property.short_address as PrimaryContactShortAddress,
RTRIM(LTRIM(member.forename)) + ' ' + RTRIM(LTRIM(member.surname)) as PrimaryContactName,
ROW_NUMBER() OVER (ORDER BY member.surname, member.forename ASC) AS Seq
FROM tenagree
Left JOIN dbo.member member WITH(NOLOCK)
ON member.house_ref = tenagree.house_ref
LEFT JOIN property WITH(NOLOCK)
ON property.prop_ref = tenagree.prop_ref
WHERE tenagree.tag_ref IS NOT NULL
");
//Add conditional AND clauses to query
if (request.TenancyRef.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine($"{whiteSpace}AND LOWER(tenagree.tag_ref) = @lowerTenancyRef");
}
if (request.FirstName.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine($"{whiteSpace}AND LOWER(member.forename) = @lowerFirstName");
}
if (request.LastName.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine($"{whiteSpace}AND LOWER(member.surname) = @lowerLastName");
}
if (request.Address.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine($@"{whiteSpace}AND LOWER(property.short_address) like '%' + @lowerAddress + '%' ");
}
if (request.PostCode.IsNotNullOrEmptyOrWhiteSpace())
{
queryStringBuilder.AppendLine($"{whiteSpace}AND LOWER(property.post_code) like '%' + @lowerPostCode + '%' ");
}
queryStringBuilder.Append(@"
)
orderByWithSequenceSubQuery
WHERE Seq BETWEEN @Lower AND @Upper");
return(queryStringBuilder);
}
public async Task <PagedResults <TenancyListItem> > SearchTenanciesAsync(SearchTenancyRequest request, CancellationToken cancellationToken)
{
var results = new PagedResults <TenancyListItem>();
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var all = await conn.QueryAsync <TenancyListItem>(
@"
DECLARE @Upper Integer;
DECLARE @Lower integer;
DECLARE @lowerSearchTerm nvarchar(256);
SET @lowerSearchTerm = LOWER(@searchTerm)
if(@page = 0)
begin
Set @Lower = 1
Set @Upper = @Lower + @pageSize -1
end
if(@page > 0)
begin
Set @Lower = (@pageSize * @page) + 1
Set @Upper = @Lower + @pageSize -1
end
SELECT
Seq,
CurrentBalance,
TenancyRef,
PropertyRef,
Tenure,
PrimaryContactPostcode,
PrimaryContactShortAddress,
PrimaryContactName
FROM
(
SELECT
tenagree.cur_bal as CurrentBalance,
tenagree.tag_ref as TenancyRef,
tenagree.prop_ref as PropertyRef,
tenagree.tenure as Tenure,
property.post_code as PrimaryContactPostcode,
property.short_address as PrimaryContactShortAddress,
RTRIM(LTRIM(member.forename)) + ' ' + RTRIM(LTRIM(member.surname)) as PrimaryContactName,
ROW_NUMBER() OVER (ORDER BY member.surname, member.forename ASC) AS Seq
FROM tenagree
Left JOIN dbo.member member WITH(NOLOCK)
ON member.house_ref = tenagree.house_ref
LEFT JOIN property WITH(NOLOCK)
ON property.prop_ref = tenagree.prop_ref
WHERE tenagree.tag_ref IS NOT NULL
AND (LOWER(tenagree.tag_ref) = @lowerSearchTerm
OR LOWER(member.forename) = @lowerSearchTerm
OR LOWER(member.surname) = @lowerSearchTerm
OR LOWER(property.short_address) like '%'+ @lowerSearchTerm +'%'
OR LOWER(property.post_code) like '%'+ @lowerSearchTerm +'%')
)
orderByWithSequenceSubQuery
WHERE Seq BETWEEN @Lower AND @Upper",
new { searchTerm = request.SearchTerm, page = request.Page > 0 ? request.Page - 1 : 0, pageSize = request.PageSize }
).ConfigureAwait(false);
results.Results = all?.ToList();
var totalCount = await conn.QueryAsync <int>(
@"
DECLARE @lowerSearchTerm nvarchar(256);
SET @lowerSearchTerm = LOWER(@searchTerm)
SELECT count(tenagree.tag_ref)
FROM tenagree
Left JOIN dbo.member member WITH(NOLOCK)
ON member.house_ref = tenagree.house_ref
LEFT JOIN property WITH(NOLOCK)
ON property.prop_ref = tenagree.prop_ref
WHERE tenagree.tag_ref IS NOT NULL
AND (LOWER(tenagree.tag_ref) = @lowerSearchTerm
OR LOWER(member.forename) = @lowerSearchTerm
OR LOWER(member.surname) = @lowerSearchTerm
OR LOWER(property.short_address) like '%'+ @lowerSearchTerm +'%'
OR LOWER(property.post_code) like '%'+ @lowerSearchTerm +'%')",
new { searchTerm = request.SearchTerm }
).ConfigureAwait(false);
results.TotalResultsCount = totalCount.Sum();
conn.Close();
}
return(results);
}
public async Task <IActionResult> Get([FromQuery] SearchTenancyRequest request)
{
var result = await _searchTenancyUseCase.ExecuteAsync(request, HttpContext.GetCancellationToken()).ConfigureAwait(false);
return(HandleResponse(result));
}