//条件查询 "key{}":"条件0,条件1..."，条件为任意SQL比较表达式字符串，非Number类型必须用''包含条件的值，如'a'
//&, |, ! 逻辑运算符，对应数据库 SQL 中的 AND, OR, NOT。
// 横或纵与：同一字段的值内条件默认 | 或连接，不同字段的条件默认 & 与连接。
// ① & 可用于"key&{}":"条件"等
// ② | 可用于"key|{}":"条件", "key|{}":[] 等，一般可省略
// ③ ! 可单独使用，如"key!":Object，也可像&,|一样配合其他功能符使用
private void ConditionQuery(string subtable, List <IConditionalModel> conModels, KeyValuePair <string, JToken> va)
{
string vakey = va.Key.Trim();
string field = vakey.TrimEnd("{}".ToCharArray());
if (va.Value.HasValues)
{
List <string> inValues = new List <string>();
foreach (var cm in va.Value)
{
inValues.Add(cm.ToString());
}
conModels.Add(new ConditionalModel()
{
FieldName = field.TrimEnd("!".ToCharArray()),
ConditionalType = field.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In,
FieldValue = string.Join(",", inValues)
});
}
else
{
var ddt = new List <KeyValuePair <WhereType, ConditionalModel> >();
foreach (var and in va.Value.ToString().Split(','))
{
var model = new ConditionalModel();
model.FieldName = field.TrimEnd("&".ToCharArray());//处理&()的查询方式
if (and.StartsWith(">="))
{
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.TrimStart(">=".ToCharArray());
}
else if (and.StartsWith("<="))
{
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.TrimStart("<=".ToCharArray());
}
else if (and.StartsWith(">"))
{
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.TrimStart('>');
}
else if (and.StartsWith("<"))
{
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.TrimStart('<');
}
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>((field.EndsWith("&") ? WhereType.And : WhereType.Or), model));
}
conModels.Add(new ConditionalCollections()
{
ConditionalList = ddt
});
}
}
private static object GetFieldValue(ConditionalModel item)
{
if (item.FieldValueConvertFunc != null)
{
return(item.FieldValueConvertFunc(item.FieldValue));
}
else
{
return(item.FieldValue);
}
}
//"key%":"start,end" => "key%":["start,end"]，其中 start 和 end 都只能为 Boolean, Number, String 中的一种，如 "2017-01-01,2019-01-01" ，["1,90000", "82001,100000"] ，可用于连续范围内的筛选
private void ConditionBetween(string subtable, List <IConditionalModel> conModels, KeyValuePair <string, JToken> va)
{
string vakey = va.Key.Trim();
string field = vakey.TrimEnd("%".ToCharArray());
List <string> inValues = new List <string>();
if (va.Value.HasValues)
{
foreach (var cm in va.Value)
{
inValues.Add(cm.ToString());
}
}
else
{
inValues.Add(va.Value.ToString());
}
for (var i = 0; i < inValues.Count; i++)
{
var fileds = inValues[i].Split(',');
if (fileds.Length == 2)
{
var ddt = new List <KeyValuePair <WhereType, ConditionalModel> >();
var leftCondition = new ConditionalModel()
{
FieldName = field,
ConditionalType = ConditionalType.GreaterThanOrEqual,
FieldValue = fileds[0]
};
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>(i == 0 ? WhereType.And : WhereType.Or, leftCondition));
var rightCondition = new ConditionalModel()
{
FieldName = field,
ConditionalType = ConditionalType.LessThanOrEqual,
FieldValue = fileds[1]
};
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>(WhereType.And, rightCondition));
conModels.Add(new ConditionalCollections()
{
ConditionalList = ddt
});
}
}
}
/// <summary>
/// 转换Or条件
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
private ConditionalCollections ParseKeyOr(ConditionalModel condition)
{
var objectKeys = condition.FieldName.Split(',');
var conditionalList = new List <KeyValuePair <WhereType, ConditionalModel> >();
foreach (var objKey in objectKeys)
{
var cond = new KeyValuePair <WhereType, ConditionalModel>
(WhereType.Or, new ConditionalModel()
{
FieldName = objKey,
ConditionalType = condition.ConditionalType,
FieldValue = condition.FieldValue
});
conditionalList.Add(cond);
}
return(new ConditionalCollections {
ConditionalList = conditionalList
});
}
private ISugarQueryable <System.Dynamic.ExpandoObject> sugarQueryable(string subtable, string selectrole, JObject values, JObject dd)
{
var tb = db.Db.Queryable(subtable, "tb");
if (values["@column"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var item in values["@column"].ToString().Split(","))
{
string[] ziduan = item.Split(":");
if (ziduan.Length > 1)
{
if (_identitySvc.ColIsRole(ziduan[0], selectrole.Split(",")))
{
str.Append(ziduan[0] + " as " + ziduan[1] + ",");
}
}
else
{
if (_identitySvc.ColIsRole(item, selectrole.Split(",")))
{
str.Append(item + ",");
}
}
}
if (string.IsNullOrEmpty(str.ToString()))
{
throw new Exception($"表名{subtable}没有可查询的字段！");
}
tb.Select(str.ToString().TrimEnd(','));
}
else
{
tb.Select(selectrole);
}
List <IConditionalModel> conModels = new List <IConditionalModel>();
foreach (var va in values)
{
string vakey = va.Key.Trim();
if (vakey.EndsWith("$"))//模糊查询
{
if (vakey.TrimEnd('$').IsTable())
{
conModels.Add(new ConditionalModel()
{
FieldName = vakey.TrimEnd('$'), ConditionalType = ConditionalType.Like, FieldValue = va.Value.ToString()
});
}
}
else if (vakey.EndsWith("{}"))//逻辑运算
{
string field = vakey.TrimEnd("{}".ToCharArray());
if (va.Value.HasValues)
{
conModels.Add(new ConditionalModel()
{
FieldName = field, ConditionalType = field.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In, FieldValue = va.Value.ToString()
});
}
else
{
var ddt = new List <KeyValuePair <WhereType, ConditionalModel> >();
foreach (var and in va.Value.ToString().Split(','))
{
var model = new ConditionalModel();
model.FieldName = field;
if (and.StartsWith(">="))
{
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.TrimStart(">=".ToCharArray());
}
else if (and.StartsWith("<="))
{
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.TrimStart("<=".ToCharArray());
}
else if (and.StartsWith(">"))
{
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.TrimStart('>');
}
else if (and.StartsWith("<"))
{
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.TrimStart('<');
}
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>((field.EndsWith("&") ? WhereType.And : WhereType.Or), model));
}
conModels.Add(new ConditionalCollections()
{
ConditionalList = ddt
});
}
}
else if (vakey.EndsWith("@") && dd != null) // 关联上一个table
{
string[] str = va.Value.ToString().Split("/");
string value = string.Empty;
if (str.Length == 3)
{
value = dd[str[1]][str[2]].ToString();
}
else if (str.Length == 2)
{
value = dd[str[0]][str[1]].ToString();
}
conModels.Add(new ConditionalModel()
{
FieldName = vakey.TrimEnd('@'), ConditionalType = ConditionalType.Equal, FieldValue = value
});
}
else if (vakey.IsTable()) //其他where条件
{
conModels.Add(new ConditionalModel()
{
FieldName = vakey, ConditionalType = ConditionalType.Equal, FieldValue = va.Value.ToString()
});
}
}
tb.Where(conModels);
//排序
if (values["@order"].IsValue())
{
foreach (var item in values["@order"].ToString().Split(","))
{
if (item.Replace("-", "").IsTable())
{
if (item.EndsWith("-"))
{
tb.OrderBy($"{item.Replace("-", " desc")}");
}
else
{
tb.OrderBy($"{item.ToString()}");
}
}
}
}
if (values["@group"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var and in values["@group"].ToString().Split(','))
{
if (and.IsField())
{
str.Append(and + ",");
}
}
tb.GroupBy(str.ToString().TrimEnd(','));
}
if (values["@having"].IsValue())
{
tb.Having($"{values["@having"].ToString()}");
}
return(tb);
}
// "@having":"function0(...)?value0;function1(...)?value1;function2(...)?value2..."，
// SQL函数条件，一般和 @group一起用，函数一般在 @column里声明
private void ProcessHaving(JObject values, ISugarQueryable <ExpandoObject> tb)
{
if (values["@having"].IsValue())
{
List <IConditionalModel> hw = new List <IConditionalModel>();
List <string> havingItems = new List <string>();
if (values["@having"].HasValues)
{
havingItems = values["@having"].Select(p => p.ToString()).ToList();
}
else
{
havingItems.Add(values["@having"].ToString());
}
foreach (var item in havingItems)
{
string and = item.ToString();
var model = new ConditionalModel();
if (and.Contains(">="))
{
model.FieldName = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("<="))
{
model.FieldName = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains(">"))
{
model.FieldName = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("<"))
{
model.FieldName = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("!="))
{
model.FieldName = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.NoEqual;
model.FieldValue = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("="))
{
model.FieldName = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.Equal;
model.FieldValue = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
hw.Add(model);
}
var d = db.Context.Utilities.ConditionalModelToSql(hw);
//tb.Having(d.Key, d.Value);
tb.Having(string.Join(",", havingItems));
}
}
public dynamic GetTableData(string subtable, int page, int count, string json, JObject dd, string rolename)
{
if (!subtable.IsTable())
{
throw new Exception($"表名{subtable}不正确！");
}
var role = GetSelectRole(rolename, subtable);
if (!role.Item1)
{
throw new Exception(role.Item2);
}
string selectrole = role.Item2;
if (dict.ContainsKey(subtable.ToLower()))
{
subtable = dict.GetValueOrDefault(subtable.ToLower());
}
JObject values = JObject.Parse(json);
var tb = Db.Queryable(subtable, "tb");
if (values["@column"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var item in values["@column"].ToString().Split(","))
{
string[] ziduan = item.Split(":");
if (ziduan.Length > 1)
{
if (ziduan[0].IsField() && ziduan[1].IsTable() && (selectrole == "*" || selectrole.Split(',').Contains(ziduan[0], StringComparer.CurrentCultureIgnoreCase)))
{
str.Append(ziduan[0] + " as " + ziduan[1] + ",");
}
}
else
{
if (item.IsField() && (selectrole == "*" || selectrole.Split(',').Contains(item, StringComparer.CurrentCultureIgnoreCase)))
{
str.Append(item + ",");
}
}
}
tb.Select(str.ToString().TrimEnd(','));
}
else
{
tb.Select(selectrole);
}
page = values["page"] == null ? page : int.Parse(values["page"].ToString());
count = values["count"] == null ? count : int.Parse(values["count"].ToString());
values.Remove("page");
values.Remove("count");
List <IConditionalModel> conModels = new List <IConditionalModel>();
foreach (var va in values)
{
string vakey = va.Key.Trim();
if (vakey.EndsWith("$"))//模糊查询
{
if (vakey.TrimEnd('$').IsTable())
{
conModels.Add(new ConditionalModel()
{
FieldName = va.Key.TrimEnd('$'), ConditionalType = ConditionalType.Like, FieldValue = va.Value.ToString()
});
}
}
else if (vakey.EndsWith("{}"))//逻辑运算
{
string field = va.Key.TrimEnd("{}".ToCharArray());
if (va.Value.HasValues)
{
conModels.Add(new ConditionalModel()
{
FieldName = field, ConditionalType = field.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In, FieldValue = va.Value.ToString()
});
}
else
{
var ddt = new List <KeyValuePair <WhereType, ConditionalModel> >();
foreach (var and in va.Value.ToString().Split(','))
{
var model = new ConditionalModel();
model.FieldName = field;
if (and.StartsWith(">="))
{
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.TrimStart(">=".ToCharArray());
}
else if (and.StartsWith("<="))
{
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.TrimStart("<=".ToCharArray());
}
else if (and.StartsWith(">"))
{
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.TrimStart('>');
}
else if (and.StartsWith("<"))
{
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.TrimStart('<');
}
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>((field.EndsWith("&") ? WhereType.And : WhereType.Or), model));
}
conModels.Add(new ConditionalCollections()
{
ConditionalList = ddt
});
}
}
else if (vakey.EndsWith("@") && dd != null) // 关联上一个table
{
string[] str = va.Value.ToString().Split("/");
string value = string.Empty;
if (str.Length == 3)
{
value = dd[str[1]][str[2]].ToString();
}
else if (str.Length == 2)
{
value = dd[str[0]][str[1]].ToString();
}
conModels.Add(new ConditionalModel()
{
FieldName = vakey.TrimEnd('@'), ConditionalType = ConditionalType.Equal, FieldValue = value
});
}
else if (vakey.IsTable()) //其他where条件
{
conModels.Add(new ConditionalModel()
{
FieldName = vakey, ConditionalType = ConditionalType.Equal, FieldValue = va.Value.ToString()
});
}
}
tb.Where(conModels);
//排序
if (values["@order"].IsValue())
{
foreach (var item in values["@order"].ToString().Split(","))
{
if (item.Replace("-", "").IsTable())
{
if (item.EndsWith("-"))
{
tb.OrderBy($"{item.Replace("-", " desc")}");
}
else
{
tb.OrderBy($"{item.ToString()}");
}
}
}
}
else
{
tb.OrderBy("id");
}
if (values["@group"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var and in values["@group"].ToString().Split(','))
{
if (and.IsField())
{
str.Append(and + ",");
}
}
tb.GroupBy(str.ToString().TrimEnd(','));
}
if (values["@having"].IsValue())
{
tb.Having($"{values["@having"].ToString()}");
}
if (count > 0)
{
return(tb.ToPageList(page, count));
}
else
{
return(tb.ToList());
}
}
private ISugarQueryable <ExpandoObject> sugarQueryable(string subtable, string selectrole, JObject values, JObject dd)
{
if (!IsTable(subtable))
{
throw new Exception($"表名{subtable}不正确！");
}
var tb = db.Queryable(subtable, "tb");
if (values["@column"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var item in values["@column"].ToString().Split(','))
{
string[] ziduan = item.Split(':');
if (ziduan.Length > 1)
{
if (IsCol(subtable, ziduan[0]) && _identitySvc.ColIsRole(ziduan[0], selectrole.Split(',')))
{
str.Append(ziduan[0] + " as " + ziduan[1] + ",");
}
}
else
{
if (IsCol(subtable, item) && _identitySvc.ColIsRole(item, selectrole.Split(',')))
{
str.Append(item + ",");
}
}
}
if (string.IsNullOrEmpty(str.ToString()))
{
throw new Exception($"表名{subtable}没有可查询的字段！");
}
tb.Select(str.ToString().TrimEnd(','));
}
else
{
tb.Select(selectrole);
}
List <IConditionalModel> conModels = new List <IConditionalModel>();
if (values["identity"].IsValue())
{
conModels.Add(new ConditionalModel()
{
FieldName = values["identity"].ToString(), ConditionalType = ConditionalType.Equal, FieldValue = _identitySvc.GetUserIdentity()
});
}
foreach (var va in values)
{
string vakey = va.Key.Trim();
if (vakey.EndsWith("$"))//模糊查询
{
if (IsCol(subtable, vakey.TrimEnd('$')))
{
conModels.Add(new ConditionalModel()
{
FieldName = vakey.TrimEnd('$'), ConditionalType = ConditionalType.Like, FieldValue = va.Value.ToString()
});
}
}
else if (vakey.EndsWith("{}"))//逻辑运算
{
string field = vakey.TrimEnd("{}".ToCharArray());
if (va.Value.HasValues)
{
conModels.Add(new ConditionalModel()
{
FieldName = field, ConditionalType = field.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In, FieldValue = va.Value.ToString()
});
}
else
{
var ddt = new List <KeyValuePair <WhereType, ConditionalModel> >();
foreach (var and in va.Value.ToString().Split(','))
{
var model = new ConditionalModel();
model.FieldName = field;
if (and.StartsWith(">="))
{
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.TrimStart(">=".ToCharArray());
}
else if (and.StartsWith("<="))
{
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.TrimStart("<=".ToCharArray());
}
else if (and.StartsWith(">"))
{
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.TrimStart('>');
}
else if (and.StartsWith("<"))
{
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.TrimStart('<');
}
ddt.Add(new KeyValuePair <WhereType, ConditionalModel>((field.EndsWith("&") ? WhereType.And : WhereType.Or), model));
}
conModels.Add(new ConditionalCollections()
{
ConditionalList = ddt
});
}
}
else if (vakey.EndsWith("@") && dd != null) // 关联上一个table
{
string[] str = va.Value.ToString().Split('/');
string value = string.Empty;
if (str.Length == 3)
{
value = dd[str[1]][str[2]].ToString();
}
else if (str.Length == 2)
{
value = dd[str[0]][str[1]].ToString();
}
conModels.Add(new ConditionalModel()
{
FieldName = vakey.TrimEnd('@'), ConditionalType = ConditionalType.Equal, FieldValue = value
});
}
else if (IsCol(subtable, vakey)) //其他where条件
{
conModels.Add(new ConditionalModel()
{
FieldName = vakey, ConditionalType = ConditionalType.Equal, FieldValue = va.Value.ToString()
});
}
}
tb.Where(conModels);
//排序
if (values["@order"].IsValue())
{
foreach (var item in values["@order"].ToString().Split(','))
{
if (IsCol(subtable, item.Replace("-", "")))
{
if (item.EndsWith("-"))
{
tb.OrderBy($"{item.Replace("-", " desc")}");
}
else
{
tb.OrderBy($"{item.ToString()}");
}
}
}
}
if (values["@group"].IsValue())
{
var str = new System.Text.StringBuilder(100);
foreach (var and in values["@group"].ToString().Split(','))
{
if (IsCol(subtable, and))
{
str.Append(and + ",");
}
}
tb.GroupBy(str.ToString().TrimEnd(','));
}
if (values["@having"].IsValue())
{
List <IConditionalModel> hw = new List <IConditionalModel>();
JArray jArray = JArray.Parse(values["@having"].ToString());
foreach (var item in jArray)
{
string and = item.ToString();
var model = new ConditionalModel();
if (and.Contains(">="))
{
model.FieldName = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.GreaterThanOrEqual;
model.FieldValue = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("<="))
{
model.FieldName = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.LessThanOrEqual;
model.FieldValue = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains(">"))
{
model.FieldName = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.GreaterThan;
model.FieldValue = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("<"))
{
model.FieldName = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.LessThan;
model.FieldValue = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("!="))
{
model.FieldName = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.NoEqual;
model.FieldValue = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
else if (and.Contains("="))
{
model.FieldName = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[0];
model.ConditionalType = ConditionalType.Equal;
model.FieldValue = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[1];
}
hw.Add(model);
}
var d = db.Context.Utilities.ConditionalModelToSql(hw);
tb.Having(d.Key, d.Value);
}
return(tb);
}
