public IActionResult Get(int limit, int year, int?month, int?day,
[FromQuery(Name = "filters")] List <Dictionary <string, string> > filters,
[FromQuery(Name = "order[]")] string[] order)
{
var path = _config.GetValue <string>("LogPath");
var file = String.Format("u_ex{0}{1}{2}.log", year % 100, month?.ToString("00") ?? "??", day?.ToString("00") ?? "??");
if (Directory.GetFiles(path, file).Length == 0)
{
return(BadRequest("No log files"));
}
var sb = new SelectBuilder()
.Select($"TOP {limit} *")
.From(Path.Combine(path, file))
.Where("1 = 1");
foreach (var dict in filters)
{
var value = "'" + (dict["condition"].Contains("LIKE") ? "%" + dict["value"] + "%" : dict["value"]) + "'";
sb.And(String.Concat(dict["field"], " ", dict["condition"], " ", value));
}
sb.OrderBy(order);
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
return(Ok(GetData(sb.Build())));
}
else
{
return(StatusCode(StatusCodes.Status501NotImplemented));
}
}
public static IQueryable Select(this IQueryable query, Action <SelectBuilder> callback)
{
var sb = new SelectBuilder(query);
callback(sb);
var ret = sb.Build();
return(ret);
}
public void complex_non_fluent_test()
{
const string toBuild = "select id, name as b from products p join groups g on p.group_id=g.id left join stores s on g.id=s.group_id where (price>'100' and price<='1000') or code like 'a%' group by product_group having avg(price)>100 order by price desc";
var fields = new List <SelectField> {
new SelectField("id"), new SelectField("name", "b")
};
var qbuilder = new SelectBuilder();
qbuilder.Fields(fields).From("products", "p").Join("groups", "g", "p.group_id", "g.id").Join("stores", "s", "g.id", "s.group_id", JoinType.Left).OpenBracket()
.Where("price", Compare.GreaterThan, 100).And.Where("price", Compare.LessOrEquals, 1000)
.CloseBracket().Or.Where("code", Compare.Like, "a%")
.GroupBy("product_group").Having("avg(price)>100").OrderByDesc("price").Build();
Assert.Equal(toBuild.ToLower(), qbuilder.Build().ToLower());
}
public virtual ResultSet SelectSql(string[] tables, string[] columns, Filter filter, OrderBy[] orderBys, int skip, int take)
{
var selectBuilder = new SelectBuilder(Dialect, tables, columns);
selectBuilder.Filter = filter;
selectBuilder.OrderBys = orderBys;
selectBuilder.Skip = skip;
selectBuilder.Take = take;
string sql = selectBuilder.Build();
if (selectBuilder.HasFilter)
{
return(Database.Query(sql, selectBuilder.Parameters));
}
return(Database.Query(sql));
}
