private string BuildSql(PolicyFilter filter)
{
var sql = new StringBuilder();
sql.Append(baseQuery);
if (!IsNullOrWhiteSpace(filter.PolicyNumber))
{
sql.Append(" and policy_number = @PolicyNumber");
}
if (filter.PolicyStartDateFrom.HasValue)
{
sql.Append(" and cover_from >= @PolicyStartDateFrom");
}
if (filter.PolicyStartDateTo.HasValue)
{
sql.Append(" and cover_to <= @PolicyStartDateTo");
}
if (!IsNullOrWhiteSpace(filter.CarPlateNumber))
{
sql.Append(" and vehicle LIKE @CarPlateNumber || ' %'");
}
if (!IsNullOrWhiteSpace(filter.PolicyHolder))
{
sql.Append(" and policy_holder = @PolicyHolder");
}
return(sql.ToString());
}
public IList <PolicyInfoDto> FindByFilter(PolicyFilter filter)
{
using (var cn = new NpgsqlConnection(cnString))
{
var sql = BuildSql(filter);
return(cn
.Query <PolicyInfoDto>(sql, filter)
.ToList());
}
}