public static object Run(
HttpContext context
, string configuration
, IList <object> dataservices
)
{
// Get Configuration
if (string.IsNullOrEmpty(configuration))
{
return new { error = "No configuration specified." }
}
;
JObject config = JsonConvert.DeserializeObject <JObject>(configuration);
// Calculate Pagination
string page = WebTools.Get(context, "page"); if (string.IsNullOrEmpty(page))
{
page = "1";
}
string size = WebTools.Get(context, "size"); if (string.IsNullOrEmpty(size))
{
size = "10";
}
// Query Options - Sort
var sort = new List <string>();
if (WebTools.GetArray(context, "_sort")?.Count() > 0)
{
foreach (var sortKey in WebTools.GetArray(context, "_sort"))
{
sort.Add($"{sortKey}");
}
}
if (WebTools.GetArray(context, "_sort_desc")?.Count() > 0)
{
foreach (var sortKey in WebTools.GetArray(context, "_sort_desc"))
{
sort.Add($"{sortKey} DESC");
}
}
// Query - Filters
var where = new List <string>();
var parameters = new Dictionary <string, object>();
// get body
var data = JsonConvert.DeserializeObject <JObject>(WebTools.GetBody(context));
if (data == null)
{
data = new JObject();
}
// append querystring to the data
foreach (var key in context.Request.Query.Keys)
{
data[key] = new JArray(context.Request.Query[key].ToArray());
}
string[] searchFields = config["searchFields"]?.ToObject <string[]>();
if (data != null && data.Count > 0)
{
foreach (var item in data)
{
string parameterName = item.Key
.Replace(".", "_")
.Replace("_lte", "")
.Replace("_gte", "")
.Replace("_lt", "")
.Replace("_gt", "")
;
if (item.Key == "page")
{
continue;
}
else if (item.Key == "size")
{
continue;
}
else if (item.Key == "_export")
{
continue;
}
else if (item.Key == "_aggregation")
{
continue;
}
else if (item.Key == "_sort")
{
continue;
}
else if (item.Key == "_sort_desc")
{
continue;
}
// search keyword
else if (item.Key == "_search" && searchFields != null)
{
if (string.IsNullOrEmpty($"{item.Value.FirstOrDefault()}") == false)
{
IList <string> search = new List <string>();
foreach (var searchKey in searchFields)
{
search.Add($"{searchKey} LIKE '%'+@{parameterName}+'%'");
}
where.Add($"({string.Join(" OR ", search)})");
parameters[parameterName] = $"{item.Value.FirstOrDefault()}";
}
continue;
}
// range filter
else if (item.Key.EndsWith("_date_gte"))
{
where.Add($"{item.Key.Replace("_gte", "")} >= @{parameterName}");
}
else if (item.Key.EndsWith("_date_lte"))
{
where.Add($"{item.Key.Replace("_lte", "")} <= @{parameterName}");
}
else if (item.Key.EndsWith("_date_gt"))
{
where.Add($"{item.Key.Replace("_gt", "")} > @{parameterName}");
}
else if (item.Key.EndsWith("_date_lt"))
{
where.Add($"{item.Key.Replace("_lt", "")} < @{parameterName}");
}
// otherwise string filter
else
{
foreach (var str in item.Value)
{
where.Add($"{item.Key} = @{parameterName}");
}
}
// add to parameters
parameters[parameterName] = $"{item.Value.FirstOrDefault()}";
}
}
// check if any filter options exists
JArray defaultFilters = (JArray)config["defaultFilters"];
if (defaultFilters != null)
{
foreach (var filter in defaultFilters)
{
string filterType = $"{filter["type"]}";
switch (filterType)
{
case "headers":
string key = $"{filter["key"]}";
string column = $"{filter["column"]}";
if (context.Request.Headers.ContainsKey(key))
{
where.Add($"{column} = @{column}");
parameters[column] = $"{context.Request.Headers[key]}";
}
break;
}
}
}
// Retrieve DataService
if (dataservices == null || dataservices.Count == 0)
{
return new { error = "Data Services not provided" }
}
;
SQL db = (SQL)dataservices.FirstOrDefault();
if (db == null)
{
return new { error = "Data Service not provided" }
}
;
// get sql list parameters
string sqlTemplate = $"{config["sql"]}";
if (string.IsNullOrEmpty(sqlTemplate))
{
return new { error = "No sql template specified." }
}
;
var result = List(
db
, sqlTemplate
, config
, where
, parameters
, sort
, Int64.Parse(size)
, Int64.Parse(page));
var total = Count(db, sqlTemplate, where, parameters);
// Return Result
var pagedResult = new
{
page,
size,
total,
data = result
};
return(JsonConvert.SerializeObject(
pagedResult,
new JsonSerializerSettings {
Formatting = Formatting.Indented
}
));
}