private Statement GetDateTimeFilterClause(ColumnFilter filter)
{
var clauseParameters = new List <DBParameterKeyValue>();
var clauseText = string.Empty;
for (var i = 0; i < filter.Values.Count(); i++)
{
var currentValue = filter.Values.ElementAt(i);
DateTime parsedValue;
if (DateTime.TryParseExact(currentValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue) ||
DateTime.TryParseExact(currentValue, @"yyyy-MM-dd\THH:mm:ss\Z", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue) ||
DateTime.TryParseExact(currentValue, "yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue))
{
// Current format sample: '2019-05-14 14:44:39'
currentValue = $"{parsedValue.Year.ToString("D4")}-{parsedValue.Month.ToString("D2")}-{parsedValue.Day.ToString("D2")}";
currentValue += $" {parsedValue.Hour.ToString("D2")}:{parsedValue.Minute.ToString("D2")}:{parsedValue.Second.ToString("D2")}";
}
if (i != 0)
{
clauseText += filter.FilterMethod == FilterMethod.Including ? " OR " : " AND ";
}
if (filter.FilterRigor == FilterRigor.Contains)
{
clauseText += $" CONVERT(varchar(19), {filter.Column.Name.SanitizeSQL()}, 20)";
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += " NOT";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("DateTimeFilterValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $" LIKE '%' + {filterValueParameter.Key} + '%'";
}
else if (filter.FilterRigor == FilterRigor.Equals)
{
clauseText += $" CONVERT(DATETIME, CONVERT(VARCHAR(19), {filter.Column.Name.SanitizeSQL()}, 20)) ";
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += "!";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("DateTimeFilterValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $"= {filterValueParameter.Key}";
}
}
return(new Statement(clauseText, clauseParameters));
}
private Statement GetNumberFilterClause(ColumnFilter filter)
{
var clauseParameters = new List <DBParameterKeyValue>();
var clauseText = string.Empty;
var sanitizedColumnName = filter.Column.Name.SanitizeSQL();
for (var i = 0; i < filter.Values.Count(); i++)
{
var currentValue = filter.Values.ElementAt(i);
if (i != 0)
{
clauseText += filter.FilterMethod == FilterMethod.Including ? " OR " : " AND ";
}
if (filter.FilterRigor == FilterRigor.Contains)
{
clauseText += $" CONVERT(varchar(64), {sanitizedColumnName})";
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += " NOT";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("FilterNumberValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $" LIKE '%' + {filterValueParameter.Key} + '%'";
}
else if (filter.FilterRigor == FilterRigor.Equals)
{
clauseText += $" {sanitizedColumnName} ";
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += "!";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("FilterNumberValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $"= {filterValueParameter.Key}";
}
}
return(new Statement(clauseText, clauseParameters));
}
private Statement getStringFilterClause(ColumnFilter filter)
{
var clauseParameters = new List <DBParameterKeyValue>();
var clauseText = $"( {filter.Column.Name.SanitizeSQL()} ";
for (var i = 0; i < filter.Values.Count(); i++)
{
var currentValue = filter.Values.ElementAt(i);
if (i != 0)
{
clauseText += filter.FilterMethod == FilterMethod.Including
? " OR"
: " AND";
clauseText += $" {filter.Column.Name.SanitizeSQL()} ";
}
if (filter.FilterRigor == FilterRigor.Contains)
{
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += "NOT ";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("StringFilterValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $"LIKE '%' + {filterValueParameter.Key} + '%'";
}
else if (filter.FilterRigor == FilterRigor.Equals)
{
if (filter.FilterMethod == FilterMethod.Excluding)
{
clauseText += "!";
}
var filterValueParameter = new DBParameterKeyValue(GetParameterName("StringFilterValue"), currentValue);
clauseParameters.Add(filterValueParameter);
clauseText += $"= {filterValueParameter.Key}";
}
}
clauseText += " )";
return(new Statement(clauseText, clauseParameters));
}
