// Admin.NET 项目的版æƒã€å•†æ ‡ã€ä¸“利和其他相关æƒåˆ©å‡å—ç›¸åº”æ³•å¾‹æ³•è§„çš„ä¿æŠ¤ã€‚ä½¿ç”¨æœ¬é¡¹ç›®åº”éµå®ˆç›¸å…³æ³•律法规和许å¯è¯çš„è¦æ±‚。 // // 本项目主è¦éµå¾ª MIT 许å¯è¯å’Œ Apache 许å¯è¯ï¼ˆç‰ˆæœ¬ 2.0)进行分å‘和使用。许å¯è¯ä½äºŽæºä»£ç æ ‘æ ¹ç›®å½•ä¸çš„ LICENSE-MIT å’Œ LICENSE-APACHE 文件。 // // ä¸å¾—利用本项目从事å±å®³å›½å®¶å®‰å…¨ã€æ‰°ä¹±ç¤¾ä¼šç§©åºã€ä¾µçŠ¯ä»–äººåˆæ³•æƒç›Šç‰æ³•å¾‹æ³•è§„ç¦æ¢çš„æ´»åЍï¼ä»»ä½•基于本项目二次开å‘è€Œäº§ç”Ÿçš„ä¸€åˆ‡æ³•å¾‹çº çº·å’Œè´£ä»»ï¼Œæˆ‘ä»¬ä¸æ‰¿æ‹…ä»»ä½•è´£ä»»ï¼ using AspectCore.Extensions.Reflection; using System.Dynamic; namespace Admin.NET.Core.Service; /// <summary> /// /// </summary> public class SelectTable : ISingleton { private readonly IdentityService _identitySvc; private readonly TableMapper _tableMapper; private readonly ISqlSugarClient _db; public SelectTable(IdentityService identityService, TableMapper tableMapper, ISqlSugarClient dbClient) { _identitySvc = identityService; _tableMapper = tableMapper; _db = dbClient; } /// <summary> /// 判æ–è¡¨åæ˜¯å¦æ£ç¡®ï¼Œè‹¥ä¸æ£ç¡®åˆ™æŠ›å¼‚常 /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual bool IsTable(string table) { return _db.DbMaintenance.GetTableInfoList().Any(it => it.Name.Equals(table, StringComparison.CurrentCultureIgnoreCase)) ? true : throw new Exception($"表åã€{table}ã€‘ä¸æ£ç¡®ï¼"); } /// <summary> /// 判æ–è¡¨çš„åˆ—åæ˜¯å¦æ£ç¡®,å¦‚æžœä¸æ£ç¡®åˆ™æŠ›å¼‚常,更早地暴露给调用方 /// </summary> /// <param name="table"></param> /// <param name="col"></param> /// <returns></returns> public virtual bool IsCol(string table, string col) { return _db.DbMaintenance.GetColumnInfosByTableName(table).Any(it => it.DbColumnName.Equals(col, StringComparison.CurrentCultureIgnoreCase)) ? true : throw new Exception($"表ã€{table}】ä¸å˜åœ¨åˆ—ã€{col}】ï¼è¯·æ£€æŸ¥è¾“入傿•°"); } /// <summary> /// æŸ¥è¯¢åˆ—è¡¨æ•°æ® /// </summary> /// <param name="subtable"></param> /// <param name="page"></param> /// <param name="count"></param> /// <param name="query"></param> /// <param name="json"></param> /// <param name="dd"></param> /// <returns></returns> public virtual Tuple<dynamic, int> GetTableData(string subtable, int page, int count, int query, string json, JObject dd) { var role = _identitySvc.GetSelectRole(subtable); if (!role.Item1) throw new Exception(role.Item2); var selectrole = role.Item2; subtable = _tableMapper.GetTableName(subtable); var values = JObject.Parse(json); page = values["page"] == null ? page : int.Parse(values["page"].ToString()); count = values["count"] == null ? count : int.Parse(values["count"].ToString()); query = values["query"] == null ? query : int.Parse(values["query"].ToString()); values.Remove("page"); values.Remove("count"); // æž„é€ æŸ¥è¯¢è¿‡ç¨‹ var tb = SugarQueryable(subtable, selectrole, values, dd); // 实际会在这里执行 if (query == 1) // 1-总数 { return new Tuple<dynamic, int>(null, tb.MergeTable().Count()); } else { if (page > 0) // 分页 { int total = 0; if (query == 0) return new Tuple<dynamic, int>(tb.ToPageList(page, count), total); // 0-对象 else return new Tuple<dynamic, int>(tb.ToPageList(page, count, ref total), total); // 2-以上全部 } else // 列表 { IList l = tb.ToList(); return query == 0 ? new Tuple<dynamic, int>(l, 0) : new Tuple<dynamic, int>(l, l.Count); } } } /// <summary> /// è§£æžå¹¶æŸ¥è¯¢ /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public virtual JObject Query(string queryJson) { var queryJobj = JObject.Parse(queryJson); return Query(queryJobj); } /// <summary> /// å•表查询 /// </summary> /// <param name="queryObj"></param> /// <param name="nodeName">返回数æ®çš„节点åç§° 默认为 infos</param> /// <returns></returns> public virtual JObject QuerySingle(JObject queryObj, string nodeName = "infos") { var resultObj = new JObject(); var total = 0; foreach (var item in queryObj) { var key = item.Key.Trim(); if (key.EndsWith("[]")) { total = QuerySingleList(resultObj, item, nodeName); } else if (key.Equals("func")) { ExecFunc(resultObj, item); } else if (key.Equals("total@") || key.Equals("total")) { resultObj.Add("total", total); } } return resultObj; } /// <summary> /// èŽ·å–æŸ¥è¯¢è¯å¥ /// </summary> /// <param name="queryObj"></param> /// <returns></returns> public virtual string ToSql(JObject queryObj) { foreach (var item in queryObj) { if (item.Key.Trim().EndsWith("[]")) return ToSql(item); } return string.Empty; } /// <summary> /// è§£æžå¹¶æŸ¥è¯¢ /// </summary> /// <param name="queryObj"></param> /// <returns></returns> public virtual JObject Query(JObject queryObj) { var resultObj = new JObject(); int total; foreach (var item in queryObj) { var key = item.Key.Trim(); if (key.Equals("[]")) // 列表 { total = QueryMoreList(resultObj, item); resultObj.Add("total", total); // åªè¦æ˜¯åˆ—表查询都自动返回总数 } else if (key.EndsWith("[]")) { total = QuerySingleList(resultObj, item); } else if (key.Equals("func")) { ExecFunc(resultObj, item); } else if (key.Equals("total@") || key.Equals("total")) { // resultObj.Add("total", total); continue; } else // 啿¡ { var template = GetFirstData(key, item.Value.ToString(), resultObj); if (template != null) resultObj.Add(key, JToken.FromObject(template)); } } return resultObj; } // 动æ€è°ƒç”¨æ–¹æ³• private static object ExecFunc(string funcname, object[] param, Type[] types) { var method = typeof(FuncList).GetMethod(funcname); var reflector = method.GetReflector(); var result = reflector.Invoke(new FuncList(), param); return result; } // 生æˆsql private string ToSql(string subtable, int page, int count, int query, string json) { var values = JObject.Parse(json); page = values["page"] == null ? page : int.Parse(values["page"].ToString()); count = values["count"] == null ? count : int.Parse(values["count"].ToString()); _ = values["query"] == null ? query : int.Parse(values["query"].ToString()); values.Remove("page"); values.Remove("count"); subtable = _tableMapper.GetTableName(subtable); var tb = SugarQueryable(subtable, "*", values, null); var sqlObj = tb.Skip((page - 1) * count).Take(10).ToSql(); return sqlObj.Key; } /// <summary> /// æŸ¥è¯¢ç¬¬ä¸€æ¡æ•°æ® /// </summary> /// <param name="subtable"></param> /// <param name="json"></param> /// <param name="job"></param> /// <returns></returns> /// <exception cref="Exception"></exception> private dynamic GetFirstData(string subtable, string json, JObject job) { var role = _identitySvc.GetSelectRole(subtable); if (!role.Item1) throw new Exception(role.Item2); var selectrole = role.Item2; subtable = _tableMapper.GetTableName(subtable); var values = JObject.Parse(json); values.Remove("page"); values.Remove("count"); var tb = SugarQueryable(subtable, selectrole, values, job).First(); var dic = (IDictionary<string, object>)tb; foreach (var item in values.Properties().Where(it => it.Name.EndsWith("()"))) { if (item.Value.IsNullOrEmpty()) { var func = item.Value.ToString().Substring(0, item.Value.ToString().IndexOf("(")); var param = item.Value.ToString().Substring(item.Value.ToString().IndexOf("(") + 1).TrimEnd(')'); var types = new List<Type>(); var paramss = new List<object>(); foreach (var va in param.Split(',')) { types.Add(typeof(object)); paramss.Add(tb.Where(it => it.Key.Equals(va)).Select(i => i.Value)); } dic[item.Name] = ExecFunc(func, paramss.ToArray(), types.ToArray()); } } return tb; } // å•表查询,返回的数æ®åœ¨æŒ‡å®šçš„NodeName节点 private int QuerySingleList(JObject resultObj, KeyValuePair<string, JToken> item, string nodeName) { var key = item.Key.Trim(); var jb = JObject.Parse(item.Value.ToString()); int page = jb["page"] == null ? 0 : int.Parse(jb["page"].ToString()); int count = jb["count"] == null ? 10 : int.Parse(jb["count"].ToString()); int query = jb["query"] == null ? 2 : int.Parse(jb["query"].ToString()); // 默认输出数æ®å’Œæ•°é‡ int total = 0; jb.Remove("page"); jb.Remove("count"); jb.Remove("query"); var htt = new JArray(); foreach (var t in jb) { var datas = GetTableData(t.Key, page, count, query, t.Value.ToString(), null); if (query > 0) total = datas.Item2; foreach (var data in datas.Item1) { htt.Add(JToken.FromObject(data)); } } if (!string.IsNullOrEmpty(nodeName)) resultObj.Add(nodeName, htt); else resultObj.Add(key, htt); return total; } // 生æˆsql private string ToSql(KeyValuePair<string, JToken> item) { var jb = JObject.Parse(item.Value.ToString()); int page = jb["page"] == null ? 0 : int.Parse(jb["page"].ToString()); int count = jb["count"] == null ? 10 : int.Parse(jb["count"].ToString()); int query = jb["query"] == null ? 2 : int.Parse(jb["query"].ToString()); // 默认输出数æ®å’Œæ•°é‡ jb.Remove("page"); jb.Remove("count"); jb.Remove("query"); foreach (var t in jb) { return ToSql(t.Key, page, count, query, t.Value.ToString()); } return string.Empty; } // å•表查询 private int QuerySingleList(JObject resultObj, KeyValuePair<string, JToken> item) { var key = item.Key.TrimEnd("[]"); return QuerySingleList(resultObj, item, key); } /// <summary> /// 多列表查询 /// </summary> /// <param name="resultObj"></param> /// <param name="item"></param> /// <returns></returns> private int QueryMoreList(JObject resultObj, KeyValuePair<string, JToken> item) { int total = 0; var jb = JObject.Parse(item.Value.ToString()); var page = jb["page"] == null ? 0 : int.Parse(jb["page"].ToString()); var count = jb["count"] == null ? 10 : int.Parse(jb["count"].ToString()); var query = jb["query"] == null ? 2 : int.Parse(jb["query"].ToString()); // 默认输出数æ®å’Œæ•°é‡ jb.Remove("page"); jb.Remove("count"); jb.Remove("query"); var htt = new JArray(); List<string> tables = new List<string>(), where = new List<string>(); foreach (var t in jb) { tables.Add(t.Key); where.Add(t.Value.ToString()); } if (tables.Count > 0) { string table = tables[0].TrimEnd("[]"); var temp = GetTableData(table, page, count, query, where[0], null); if (query > 0) total = temp.Item2; // å…³è”æŸ¥è¯¢ï¼Œå…ˆæŸ¥å表数æ®ï¼Œå†æ ¹æ®å¤–键循环查询主表 foreach (var dd in temp.Item1) { var zht = new JObject { { table, JToken.FromObject(dd) } }; for (int i = 1; i < tables.Count; i++) // 从第二个表开始循环 { string subtable = tables[i]; // 有bugï¼Œæš‚ä¸æ”¯æŒ[]分支 //if (subtable.EndsWith("[]")) //{ // string tableName = subtable.TrimEnd("[]".ToCharArray()); // var jbb = JObject.Parse(where[i]); // page = jbb["page"] == null ? 0 : int.Parse(jbb["page"].ToString()); // count = jbb["count"] == null ? 0 : int.Parse(jbb["count"].ToString()); // var lt = new JArray(); // foreach (var d in GetTableData(tableName, page, count, query, item.Value[subtable].ToString(), zht).Item1) // { // lt.Add(JToken.FromObject(d)); // } // zht.Add(tables[i], lt); //} //else //{ var ddf = GetFirstData(subtable, where[i].ToString(), zht); if (ddf != null) zht.Add(subtable, JToken.FromObject(ddf)); } htt.Add(zht); } } if (query != 1) resultObj.Add("[]", htt); // åˆ†é¡µè‡ªåŠ¨æ·»åŠ å½“å‰é¡µæ•°å’Œæ•°é‡ if (page > 0 && count > 0) { resultObj.Add("page", page); resultObj.Add("count", count); resultObj.Add("max", (int)Math.Ceiling((decimal)total / count)); } return total; } // 执行方法 private void ExecFunc(JObject resultObj, KeyValuePair<string, JToken> item) { var jb = JObject.Parse(item.Value.ToString()); var dataJObj = new JObject(); foreach (var f in jb) { var types = new List<Type>(); var param = new List<object>(); foreach (var va in JArray.Parse(f.Value.ToString())) { types.Add(typeof(object)); param.Add(va); } dataJObj.Add(f.Key, JToken.FromObject(ExecFunc(f.Key, param.ToArray(), types.ToArray()))); } resultObj.Add("func", dataJObj); } /// <summary> /// æž„é€ æŸ¥è¯¢è¿‡ç¨‹ /// </summary> /// <param name="subtable"></param> /// <param name="selectrole"></param> /// <param name="values"></param> /// <param name="dd"></param> /// <returns></returns> private ISugarQueryable<ExpandoObject> SugarQueryable(string subtable, string selectrole, JObject values, JObject dd) { IsTable(subtable); var tb = _db.Queryable(subtable, "tb"); // select if (!values["@column"].IsNullOrEmpty()) { ProcessColumn(subtable, selectrole, values, tb); } else { tb.Select(selectrole); } // å‰å‡ 行 ProcessLimit(values, tb); // where ProcessWhere(subtable, values, tb, dd); // æŽ’åº ProcessOrder(subtable, values, tb); // 分组 PrccessGroup(subtable, values, tb); // Having ProcessHaving(values, tb); return tb; } // 处ç†å—段é‡å‘½å "@column":"toId:parentId",对应SQL是toId AS parentIdï¼Œå°†æŸ¥è¯¢çš„å—æ®µtoIdå˜ä¸ºparentId返回 private void ProcessColumn(string subtable, string selectrole, JObject values, ISugarQueryable<ExpandoObject> tb) { var str = new System.Text.StringBuilder(100); foreach (var item in values["@column"].ToString().Split(',')) { var ziduan = item.Split(':'); var colName = ziduan[0]; var ma = new Regex(@"\((\w+)\)").Match(colName); // 处ç†maxã€minè¿™æ ·çš„å‡½æ•° if (ma.Success && ma.Groups.Count > 1) colName = ma.Groups[1].Value; // 判æ–åˆ—è¡¨æ˜¯å¦æœ‰æƒé™ sum(1)ã€sum(*)ã€Count(1)è¿™æ ·çš„å€¼ç›´æŽ¥æœ‰æ•ˆ if (colName == "*" || int.TryParse(colName, out int colNumber) || (IsCol(subtable, colName) && _identitySvc.ColIsRole(colName, selectrole.Split(',')))) { if (ziduan.Length > 1) { if (ziduan[1].Length > 20) throw new Exception("别åä¸èƒ½è¶…过20个å—符"); str.Append(ziduan[0] + " as `" + ReplaceSQLChar(ziduan[1]) + "`,"); } // ä¸å¯¹å‡½æ•°åŠ ``,解决sum(*)ã€Count(1)ç‰ä¸èƒ½ä½¿ç”¨çš„问题 else if (ziduan[0].Contains('(')) { str.Append(ziduan[0] + ","); } else str.Append("`" + ziduan[0] + "`" + ","); } } if (string.IsNullOrEmpty(str.ToString())) throw new Exception($"表å{subtable}æ²¡æœ‰å¯æŸ¥è¯¢çš„å—æ®µï¼"); tb.Select(str.ToString().TrimEnd(',')); } /// <summary> /// æž„é€ æŸ¥è¯¢æ¡ä»¶ where /// </summary> /// <param name="subtable"></param> /// <param name="values"></param> /// <param name="tb"></param> /// <param name="dd"></param> private void ProcessWhere(string subtable, JObject values, ISugarQueryable<ExpandoObject> tb, JObject dd) { var conModels = new List<IConditionalModel>(); if (!values["identity"].IsNullOrEmpty()) conModels.Add(new ConditionalModel() { FieldName = values["identity"].ToString(), ConditionalType = ConditionalType.Equal, FieldValue = _identitySvc.GetUserIdentity() }); foreach (var va in values) { string key = va.Key.Trim(); string fieldValue = va.Value.ToString(); if (key.StartsWith("@")) { continue; } if (key.EndsWith("$")) // 模糊查询 { FuzzyQuery(subtable, conModels, va); } else if (key.EndsWith("{}")) // 逻辑è¿ç®— { ConditionQuery(subtable, conModels, va); } else if (key.EndsWith("%")) // bwtween查询 { ConditionBetween(subtable, conModels, va, tb); } else if (key.EndsWith("@")) // å…³è”上一个table { if (dd == null) continue; var str = fieldValue.Split('/'); var lastTableRecord = ((JObject)dd[str[^2]]); if (!lastTableRecord.ContainsKey(str[^1])) throw new Exception($"找ä¸åˆ°å…³è”列:{str},请在{str[^2]}@columnä¸è®¾ç½®"); var value = lastTableRecord[str[^1]].ToString(); conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('@'), ConditionalType = ConditionalType.Equal, FieldValue = value }); } else if (key.EndsWith("~")) // ä¸ç‰äºŽï¼ˆåº”该是æ£åˆ™åŒ¹é…) { //conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('~'), ConditionalType = ConditionalType.NoEqual, FieldValue = fieldValue }); } else if (IsCol(subtable, key.TrimEnd('!'))) // å…¶ä»–whereæ¡ä»¶ { ConditionEqual(subtable, conModels, va); } } if (conModels.Any()) tb.Where(conModels); } // "@having":"function0(...)?value0;function1(...)?value1;function2(...)?value2...", // SQL函数æ¡ä»¶ï¼Œä¸€èˆ¬å’Œ @group一起用,函数一般在 @column里声明 private static void ProcessHaving(JObject values, ISugarQueryable<ExpandoObject> tb) { if (!values["@having"].IsNullOrEmpty()) { var hw = new List<IConditionalModel>(); var 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) { var 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)); } } // "@group":"column0,column1...",分组方å¼ã€‚如果 @column里声明了Tableçš„id,则id也必须在 @groupä¸å£°æ˜Žï¼›å…¶å®ƒæƒ…况下必须满足至少一个æ¡ä»¶: // 1.分组的key在 @column里声明 // 2.Table主键在 @groupä¸å£°æ˜Ž private void PrccessGroup(string subtable, JObject values, ISugarQueryable<ExpandoObject> tb) { if (!values["@group"].IsNullOrEmpty()) { var groupList = new List<GroupByModel>(); // 多库兼容写法 foreach (var col in values["@group"].ToString().Split(',')) { if (IsCol(subtable, col)) { // str.Append(and + ","); groupList.Add(new GroupByModel() { FieldName = col }); } } if (groupList.Any()) tb.GroupBy(groupList); } } // å¤„ç†æŽ’åº "@order":"name-,id"查询按 nameé™åºã€idé»˜è®¤é¡ºåº æŽ’åºçš„User数组 private void ProcessOrder(string subtable, JObject values, ISugarQueryable<ExpandoObject> tb) { if (!values["@order"].IsNullOrEmpty()) { var orderList = new List<OrderByModel>(); // 多库兼容写法 foreach (var item in values["@order"].ToString().Split(',')) { string col = item.Replace("-", "").Replace("+", "").Replace(" desc", "").Replace(" asc", ""); // å¢žåŠ å¯¹åŽŸç”ŸæŽ’åºçš„æ”¯æŒ if (IsCol(subtable, col)) { orderList.Add(new OrderByModel() { FieldName = col, OrderByType = item.EndsWith("-") || item.EndsWith(" desc") ? OrderByType.Desc : OrderByType.Asc }); } } if (orderList.Any()) tb.OrderBy(orderList); } } /// <summary> /// è¡¨å†…å‚æ•°"@count"(int):查询å‰å‡ 行,ä¸èƒ½åŒæ—¶ä½¿ç”¨countå’Œ@count函数 /// </summary> /// <param name="values"></param> /// <param name="tb"></param> private static void ProcessLimit(JObject values, ISugarQueryable<ExpandoObject> tb) { if (!values["@count"].IsNullOrEmpty()) { int c = values["@count"].ToObject<int>(); tb.Take(c); } } // æ¡ä»¶æŸ¥è¯¢ "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) { var vakey = va.Key.Trim(); var field = vakey.TrimEnd("{}".ToCharArray()); var columnName = field.TrimEnd(new char[] { '&', '|' }); IsCol(subtable, columnName); var ddt = new List<KeyValuePair<WhereType, ConditionalModel>>(); foreach (var and in va.Value.ToString().Split(',')) { var model = new ConditionalModel { FieldName = columnName }; 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('<'); } model.CSharpTypeName = FuncList.GetValueCSharpType(model.FieldValue); ddt.Add(new KeyValuePair<WhereType, ConditionalModel>(field.EndsWith("!") ? WhereType.Or : WhereType.And, model)); } conModels.Add(new ConditionalCollections() { ConditionalList = ddt }); } /// <summary> /// "key%":"start,end" => "key%":["start,end"]ï¼Œå…¶ä¸ start å’Œ end 都åªèƒ½ä¸º Boolean, Number, String ä¸çš„一ç§ï¼Œå¦‚ "2017-01-01,2019-01-01" ,["1,90000", "82001,100000"] ,å¯ç”¨äºŽè¿žç»èŒƒå›´å†…çš„ç›é€‰ /// ç›®å‰ä¸æ”¯æŒæ•°ç»„å½¢å¼ /// </summary> /// <param name="subtable"></param> /// <param name="conModels"></param> /// <param name="va"></param> /// <param name="tb"></param> private static void ConditionBetween(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va, ISugarQueryable<ExpandoObject> tb) { var vakey = va.Key.Trim(); var field = vakey.TrimEnd("%".ToCharArray()); var 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 type = FuncList.GetValueCSharpType(fileds[0]); ObjectFuncModel f = ObjectFuncModel.Create("between", field, $"{{{type}}}:{fileds[0]}", $"{{{type}}}:{fileds[1]}"); tb.Where(f); } } } /// <summary> /// ç‰äºŽã€ä¸ç‰äºŽã€in ã€not in /// </summary> /// <param name="subtable"></param> /// <param name="conModels"></param> /// <param name="va"></param> private void ConditionEqual(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va) { var key = va.Key; var fieldValue = va.Value.ToString(); // in / not in if (va.Value is JArray) { conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('!'), ConditionalType = key.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In, FieldValue = va.Value.ToObject<string[]>().Aggregate((a, b) => a + "," + b) }); } else { if (string.IsNullOrEmpty(fieldValue)) { // is not null or '' if (key.EndsWith("!")) { conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('!'), ConditionalType = ConditionalType.IsNot, FieldValue = null }); conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('!'), ConditionalType = ConditionalType.IsNot, FieldValue = "" }); } //is null or '' else { conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('!'), FieldValue = null }); } } // = / != else { conModels.Add(new ConditionalModel() { FieldName = key.TrimEnd('!'), ConditionalType = key.EndsWith("!") ? ConditionalType.NoEqual : ConditionalType.Equal, FieldValue = fieldValue }); } } } // 模糊æœç´¢ "key$":"SQLæœç´¢è¡¨è¾¾å¼" => "key$":["SQLæœç´¢è¡¨è¾¾å¼"],任æ„SQLæœç´¢è¡¨è¾¾å¼å—符串,如 %key%(包å«key), key%(以key开始), %k%e%y%(包å«å—æ¯k,e,y) ç‰ï¼Œ%表示任æ„å—符 private void FuzzyQuery(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va) { var vakey = va.Key.Trim(); var fieldValue = va.Value.ToString(); var conditionalType = ConditionalType.Like; if (IsCol(subtable, vakey.TrimEnd('$'))) { // 支æŒä¸‰ç§like查询 if (fieldValue.StartsWith("%") && fieldValue.EndsWith("%")) { conditionalType = ConditionalType.Like; } else if (fieldValue.StartsWith("%")) { conditionalType = ConditionalType.LikeRight; } else if (fieldValue.EndsWith("%")) { conditionalType = ConditionalType.LikeLeft; } conModels.Add(new ConditionalModel() { FieldName = vakey.TrimEnd('$'), ConditionalType = conditionalType, FieldValue = fieldValue.TrimEnd("%".ToArray()).TrimStart("%".ToArray()) }); } } // 处ç†sql注入 private string ReplaceSQLChar(string str) { if (string.IsNullOrWhiteSpace(str)) return string.Empty; str = str.Replace("'", ""); str = str.Replace(";", ""); str = str.Replace(",", ""); str = str.Replace("?", ""); str = str.Replace("<", ""); str = str.Replace(">", ""); str = str.Replace("(", ""); str = str.Replace(")", ""); str = str.Replace("@", ""); str = str.Replace("=", ""); str = str.Replace("+", ""); str = str.Replace("*", ""); str = str.Replace("&", ""); str = str.Replace("#", ""); str = str.Replace("%", ""); str = str.Replace("$", ""); str = str.Replace("\"", ""); // åˆ é™¤ä¸Žæ•°æ®åº“ç›¸å…³çš„è¯ str = Regex.Replace(str, "delete from", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "drop table", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "truncate", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "xp_cmdshell", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "exec master", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "net localgroup administrators", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "net user", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "-", "", RegexOptions.IgnoreCase); str = Regex.Replace(str, "truncate", "", RegexOptions.IgnoreCase); return str; } /// <summary> /// 啿¡æ’å…¥ /// </summary> /// <param name="tableName"></param> /// <param name="cols"></param> /// <param name="role"></param> /// <returns>(å„ç§ç±»åž‹çš„)id</returns> public object InsertSingle(string tableName, JObject cols, APIJSON_Role role = null) { role ??= _identitySvc.GetRole(); var dt = new Dictionary<string, object>(); foreach (var f in cols) // éåŽ†å—æ®µ { if (//f.Key.ToLower() != "id" && //是å¦ä¸€å®šè¦ä¼ id IsCol(tableName, f.Key) && (role.Insert.Column.Contains("*") || role.Insert.Column.Contains(f.Key, StringComparer.CurrentCultureIgnoreCase))) dt.Add(f.Key, FuncList.TransJObjectToSugarPara(f.Value)); } // å¦‚æžœå¤–éƒ¨æ²¡ä¼ Id,就åŽç«¯ç”Ÿæˆæˆ–使用数æ®åº“默认值,如果都没有会出错 object id; if (!dt.ContainsKey("id")) { id = YitIdHelper.NextId();//自己生æˆid的方法,å¯ä»¥ç”±å¤–éƒ¨ä¼ å…¥ dt.Add("id", id); } else { id = dt["id"]; } _db.Insertable(dt).AS(tableName).ExecuteCommand();//æ ¹æ®ä¸»é”®ç±»åž‹è®¾ç½®è¿”回雪花或自增,ç›®å‰è¿”å›žæ¡æ•° return id; } /// <summary> /// 为æ¯å¤©è®°å½•创建udpate sql /// </summary> /// <param name="tableName"></param> /// <param name="record"></param> /// <param name="role"></param> /// <returns></returns> public int UpdateSingleRecord(string tableName, JObject record, APIJSON_Role role = null) { role ??= _identitySvc.GetRole(); if (!record.ContainsKey("id")) throw Oops.Bah("æœªä¼ ä¸»é”®id"); var dt = new Dictionary<string, object>(); var sb = new StringBuilder(100); object id = null; foreach (var f in record)//é历æ¯ä¸ªå—段 { if (f.Key.Equals("id", StringComparison.OrdinalIgnoreCase)) { if (f.Value is JArray) { sb.Append($"{f.Key} in (@{f.Key})"); id = FuncList.TransJArrayToSugarPara(f.Value); } else { sb.Append($"{f.Key}=@{f.Key}"); id = FuncList.TransJObjectToSugarPara(f.Value); } } else if (IsCol(tableName, f.Key) && (role.Update.Column.Contains("*") || role.Update.Column.Contains(f.Key, StringComparer.CurrentCultureIgnoreCase))) { dt.Add(f.Key, FuncList.TransJObjectToSugarPara(f.Value)); } } string whereSql = sb.ToString(); int count = _db.Updateable(dt).AS(tableName).Where(whereSql, new { id }).ExecuteCommand(); return count; } /// <summary> /// æ›´æ–°å•表,支æŒåŒè¡¨å¤šæ¡è®°å½• /// </summary> /// <param name="tableName"></param> /// <param name="records"></param> /// <param name="role"></param> /// <returns></returns> public int UpdateSingleTable(string tableName, JToken records, APIJSON_Role role = null) { role ??= _identitySvc.GetRole(); int count = 0; if (records is JArray) { foreach (var record in records.ToObject<JObject[]>()) { count += UpdateSingleRecord(tableName, record, role); } } else { count = UpdateSingleRecord(tableName, records.ToObject<JObject>(), role); } return count; } }