private static Dictionary <string, object> PrepareValues(Dictionary <string, object> row, List <TableDescription> tabDesc)
{
var values = new Dictionary <string, object>();
foreach (var prop in row)
{
var key = prop.Key.ToLower();
var desc = tabDesc.Where(x => x.ColumnName.ToLower() == key).FirstOrDefault();
if (desc != null)
{
dynamic value;
string[] dbDateTypes = { "date", "timestamp", "timestamp without time zone", "timestamp with local time zone" };
string[] dbNumberTypes = { "numeric", "smallint", "integer", "bigint", "decimal", "numeric", "real", "double precision", "serial", "bigserial" };
if (dbDateTypes.Contains(desc.DataType.ToLower()))
{
var propVal = Convert.ToString(prop.Value);
value = RestHelper.GetDateTime(propVal);
}
else if (dbNumberTypes.Contains(desc.DataType.ToLower()))
{
var propVal = Convert.ToString(prop.Value);
value = RestHelper.GetDouble(propVal);
}
else
{
value = prop.Value;
}
values.Add("P_" + key.ToLower(), value);
}
}
return(values);
}
private static (string, DynamicParameters) WhereDate(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 (!valueIsEmpty)
{
filterMeta.value = RestHelper.GetDateTime(filterMeta.value);
}
if (!valueToIsEmpty)
{
filterMeta.valueTo = RestHelper.GetDateTime(filterMeta.valueTo);
}
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_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);
}