public string OrderBy(IEnumerable <DynamicQueryOrder> orders, bool fieldConverter = true)
{
if (orders == null || !orders.Any())
{
return(string.Empty);
}
return(string.Join(",", orders.Select(item =>
{
var name = item.Name;
if (fieldConverter)
{
name = CharacterConverter.FieldConverter(item.Name);
}
if (SqlFieldMappings.Any(x => x.Key == name))
{
name = SqlFieldMappings.First(x => x.Key == name).SqlField;
}
var order = item.Sort == ListSortDirection.Ascending
? "ASC"
: "DESC";
return $" {name} {order} ";
})));
}
private object GetSqlValue(SqlFieldMappings mappings, DynamicQueryParam param, string parentKey)
{
var file = param.Field;
if (!string.IsNullOrWhiteSpace(parentKey))
{
file = $"{parentKey}.{param.Field}";
}
var item = mappings.SingleOrDefault(i => i.Key == file);
if (item?.Type != null && item.Type.GetTypeInfo().IsEnum)
{
if (param.Operator == QueryOperation.In)
{
var arr = param.Value.ToString().Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var objArr = new List <string>();
foreach (var s in arr)
{
var enu = Convert.ChangeType(Enum.Parse(item.Type, s), typeof(int));
objArr.Add(enu.ToString());
}
return(string.Join(",", objArr));
}
return(Convert.ChangeType(Enum.Parse(item.Type, param.Value.ToString()), typeof(int)));
}
return(param.Value);
}
public SqlFieldMappings BuildMappings2()
{
var mappings = new SqlFieldMappings();
mappings.Map("zxc", "t1.Address")
.Map("fgh", "t1.Disabled")
.Map("asd", "t1.Amout")
.Map("rty", "t1.Price")
.Map("wer", "t1.Drink")
.Map("qwe", "t1.Count")
.Map("Abc", "t1.Total");
// Native api also be supported
mappings.Add("Url", "t1.Url");
return(mappings);
}
public SqlFieldMappings BuildMappings()
{
var mappings = new SqlFieldMappings();
mappings.Map("Extras.Guest", "t2.Guest")
.Map("TestAddress", "t1.Address")
.Map("Disabled", "t1.Disabled")
.Map("Amout", "t1.Amout")
.Map("Price", "t1.Price")
.Map("Drink", "t1.Drink")
.Map("Count", "t1.Count")
.Map("Total", "t1.Total");
// Native api also be supported
mappings.Add("Url", "t1.Url");
return(mappings);
}
private string GetSqlKey(SqlFieldMappings mappings, DynamicQueryParam param, string parentKey)
{
var file = param.Field;
if (!string.IsNullOrWhiteSpace(parentKey))
{
file = $"{parentKey}.{param.Field}";
}
var item = mappings.SingleOrDefault(i => i.Key == file);
if (item != null)
{
return(item.SqlField);
}
return(file);
}
public void SqlBuilderSample(FeiniuBus.DynamicQuery dynamicQuery, ISelectBuilder selectBuilder)
{
var sb = new StringBuilder();
var mappings = new SqlFieldMappings();
mappings.Map("Guest", "t1.Guest")
.Map("Address", "t1.Address")
.Map("Disabled", "t1.Disabled")
.Map("Amout", "t1.Amout")
.Map("Price", "t1.Price")
.Map("Drink", "t1.Drink")
.Map("Count", "t1.Count")
.Map("Total", "t1.Total")
.Map("Url", "t1.Url");
selectBuilder.Mapping(mappings);
selectBuilder.Where(dynamicQuery.ParamGroup);
var whereClause = selectBuilder.BuildWhere();
var orderbyClause = selectBuilder.OrderBy(dynamicQuery.Order);
System.Console.WriteLine(whereClause);
System.Console.WriteLine(orderbyClause);
}
public void ConverterQueryParamGroups(List <DynamicQueryParamGroup> groups, SqlBuiderResult collection, QueryRelation relation, SqlFieldMappings mappings, string parentKey)
{
if (!groups.Any())
{
return;
}
var list = groups.ToList();
for (var i = 0; i < list.Count; i++)
{
var item = list[i];
CheckQueryParamGroup(item);
if (IsParam(item))
{
ConverterQueryParams(item.Params, collection, item.Relation, mappings, parentKey);
}
else
{
collection.SqlString.Append("(");
ConverterQueryParamGroups(item.ChildGroups, collection, item.Relation, mappings, parentKey);
collection.SqlString.Append(")");
}
if (i < list.Count - 1)
{
collection.SqlString.Append(relation == QueryRelation.And ? " AND " : " OR ");
}
}
}
public void ConverterQueryParamGroup(DynamicQueryParamGroup group, SqlBuiderResult collection, SqlFieldMappings mappings, string
parentKey)
{
CheckQueryParamGroup(group);
if (IsParam(group))
{
ConverterQueryParams(group.Params, collection, group.Relation, mappings, parentKey);
}
else
{
ConverterQueryParamGroups(group.ChildGroups, collection, group.Relation, mappings, parentKey);
}
}
public void ConverterQueryParams(List <DynamicQueryParam> queryParams, SqlBuiderResult collection, QueryRelation relation, SqlFieldMappings mappings, string parentKey)
{
if (!queryParams.Any())
{
return;
}
var list = queryParams.ToList();
collection.SqlString.Append("(");
for (var i = 0; i < list.Count; i++)
{
var item = list[i];
var field = item.Operator == QueryOperation.Any ? "" : GetSqlKey(mappings, item, parentKey);
var param = $"@{__PAM_}{collection.Params.Count}";
var val = GetSqlValue(mappings, item, parentKey);
switch (item.Operator)
{
case QueryOperation.Equal:
collection.SqlString.Append($"{field} = {param}");
collection.Params.Add(param, val);
break;
case QueryOperation.LessThan:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} < {param}");
break;
case QueryOperation.LessThanOrEqual:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} <= {param}");
break;
case QueryOperation.GreaterThan:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} > {param}");
break;
case QueryOperation.GreaterThanOrEqual:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} >= {param}");
break;
case QueryOperation.Contains:
collection.Params.Add(param, $"%{val}%");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.StartsWith:
collection.Params.Add(param, $"{val}%");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.EndsWith:
collection.Params.Add(param, $"%{val}");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.DataTimeLessThanOrEqualThenDay:
if (DateTime.TryParse(val.ToString(), out DateTime date))
{
val = date.AddDays(1).AddMilliseconds(-1);
}
else
{
break;
}
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} <= {param}");
break;
case QueryOperation.In:
//collection.Params.Add(param, val);
collection.SqlString.Append($"{field} IN ({val.ToString().Trim(',')})");
break;
case QueryOperation.Any:
var group = JsonConvert.DeserializeObject <DynamicQueryParamGroup>((val ?? "").ToString());
if ((group == null) || (group.ChildGroups == null))
{
break;
}
ConverterQueryParamGroup(group, collection, mappings, item.Field);
break;
default:
throw new ArgumentException($"{nameof(QueryOperation)}无效");
}
if (i < list.Count - 1)
{
collection.SqlString.Append(relation == QueryRelation.Or ? " OR " : " AND ");
}
}
collection.SqlString.Append(")");
}
