private static (string, DynamicParameters) WhereString(string where, DynamicParameters parameters, string colName, FilterMetadata filterMeta)
{
bool valueIsEmpty = filterMeta.value == null || (filterMeta.value.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.value));
bool valueToIsEmpty = filterMeta.valueTo == null || (filterMeta.valueTo.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.valueTo));
if (valueIsEmpty && filterMeta.matchMode != IN_RANGE)
{
return(where, parameters);
}
switch (filterMeta.matchMode)
{
case EQUALS:
where = where + $" AND {colName} = :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case NOT_EQUAL:
where = where + $" AND {colName} != :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case IN:
var list = string.Empty;
if (filterMeta.value.GetType().Name == "JArray")
{
string[] arr = filterMeta.value.ToObject <string[]>();
list = string.Join(",", arr.Select(x => string.Format("'{0}'", x)));
}
else
{
list = string.Join(",", filterMeta.value);
}
where = where + $" AND {colName} in ({list})";
break;
case CONTAINS:
where = where + $" AND lower({colName}) LIKE lower(:{colName})";
parameters.Add(colName, string.Format("%{0}%", filterMeta.value));
break;
case NOT_CONTAINS:
where = where + $" AND lower({colName}) NOT LIKE lower(:{colName})";
parameters.Add(colName, string.Format("%{0}%", filterMeta.value));
break;
case STARTS_WITH:
where = where + $" AND lower({colName}) LIKE lower(:{colName})";
parameters.Add(colName, string.Format("{0}%", filterMeta.value));
break;
case ENDS_WITH:
where = where + $" AND lower({colName}) LIKE lower(:{colName})";
parameters.Add(colName, string.Format("%{0}", filterMeta.value));
break;
case IS_EMPTY:
where = where + $" AND {colName} IS NULL";
break;
case IS_NOT_EMPTY:
where = where + $" AND {colName} IS NOT NULL";
break;
default:
where = where + $" AND {colName} = :{colName}";
parameters.Add(colName, filterMeta.value);
break;
}
return(where, parameters);
}
private static (string, DynamicParameters) WhereNumeric(string where, DynamicParameters parameters, string colName, FilterMetadata filterMeta)
{
bool valueIsEmpty = filterMeta.value == null || (filterMeta.value.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.value));
bool valueToIsEmpty = filterMeta.valueTo == null || (filterMeta.valueTo.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.valueTo));
if (valueIsEmpty && filterMeta.matchMode != IN_RANGE)
{
return(where, parameters);
}
if (filterMeta.value.GetType() == typeof(string))
{
filterMeta.value = RestHelper.GetDouble(filterMeta.value);
}
switch (filterMeta.matchMode)
{
case EQUALS:
where = where + $" AND {colName} = :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case NOT_EQUAL:
where = where + $" AND {colName} != :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case IN:
var list = string.Join(",", filterMeta.value);
where = where + $" AND {colName} in ({list})";
break;
case IN_RANGE:
if (valueIsEmpty)
{
where = where + $" AND {colName} < :{colName}";
parameters.Add(colName, filterMeta.valueTo);
}
else if (valueToIsEmpty)
{
where = where + $" AND {colName} > :{colName}";
parameters.Add(colName, filterMeta.value);
}
else
{
where = where + $" AND {colName} > :{colName} AND {colName} < :{colName}TO";
parameters.Add(colName, filterMeta.value);
parameters.Add(colName + "TO", filterMeta.valueTo);
}
break;
case LESS_THAN:
where = where + $" AND {colName} < :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case LESS_THAN_OR_EQUAL:
where = where + $" AND {colName} <= :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case GREATER_THAN:
where = where + $" AND {colName} > :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case GREATER_THAN_OR_EQUAL:
where = where + $" AND {colName} >= :{colName}";
parameters.Add(colName, filterMeta.value);
break;
case IS_EMPTY:
where = where + $" AND {colName} IS NULL";
break;
case IS_NOT_EMPTY:
where = where + $" AND {colName} IS NOT NULL";
break;
default:
where = where + $" AND {colName} = :{colName}";
parameters.Add(colName, filterMeta.value);
break;
}
return(where, parameters);
}