using Newtonsoft.Json.Linq; using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.Json; using TEAMModelOS.SDK.Context.Exception; namespace TEAMModelOS.SDK.Module.AzureCosmosDB.Configuration { public class SQLHelper { static readonly string[] LogicOpers = new string[] { " and ", " or " }; static readonly string[] CompareOpers = new string[] { " > ", " < ", " <= ", " >= ", " = ", " != ", " like ", " not like ", " in " }; public static void ReplaceKeyWords(ref StringBuilder sql) { sql.Replace(".order.", "['order']."); sql.Replace(".order ", "['order'] "); sql.Replace(".group.", "['group']."); sql.Replace(".group ", "['group'] "); sql.Replace(".end.", "['end']."); sql.Replace(".end ", "['end'] "); sql.Replace(".having.", "['having']."); sql.Replace(".having ", "['having'] "); } public static void GetSQL(Dictionary dict, ref StringBuilder sql) { if (dict != null) { int offsetNum = 0; int limitNum = 0; bool pageBool = false; GetPageNum(dict, ref offsetNum, ref limitNum, ref pageBool); //处理顺序 Stack> stack = new Stack>(); foreach (string item in dict.Keys) { if (item.EndsWith(".|")) { stack.Push(new KeyValuePair(item, dict[item])); } } foreach (string item in dict.Keys) { if (!item.EndsWith(".|")) { stack.Push(new KeyValuePair(item, dict[item])); } } string Join = " join "; string instring = " in "; Dictionary keyValues = new Dictionary(); StringBuilder WhereString = new StringBuilder(); int heada = 0; string[] sqlHead = new string[] { "A", "B", "C", "D", "E", "F" }; int kslength = 0; int logicOperNum = 0; bool keyListValueList = true; string distinctHead = "select distinct value(c) from c "; int stackCount = stack.Count; //foreach (KeyValuePair item in newDict) for (int k = 0; k < stackCount; k++) { KeyValuePair item = stack.Pop(); bool isLikeSQL = false; if (item.Key.StartsWith("$.") || item.Key.StartsWith("!$.")) { isLikeSQL = true; } string key = item.Key; string[] keyHead = key.Split("."); int index = 0; int compareOper = 4; int logicOper = 0; if (key.EndsWith(".&")) { logicOper = (int)LogicOper.and; key = key.Replace(".&", ""); } else if (key.EndsWith(".|")) { logicOper = (int)LogicOper.or; key = key.Replace(".|", ""); } CompareOperSwitch(keyHead[0], ref key, ref compareOper); string[] keyBody = key.Split("[*]"); if (keyBody.Length > 1) { kslength += keyBody.Length; if (kslength < (7 + heada)) { StringBuilder sqlitem = new StringBuilder(); for (int i = 0; i < keyBody.Length - 1; i++) { //Console.WriteLine(ks[i]); if (i == 0) { sqlitem.Append(Join); string a = sqlHead[heada] + index; sqlitem.Append(a + " "); //keyValues.Add(ks[i], a); keyValues[keyBody[i]] = a; sqlitem.Append(instring); sqlitem.Append("c."); sqlitem.Append(keyBody[i]); } else { sqlitem.Append(Join); string a = sqlHead[heada] + index; sqlitem.Append(a + " "); //keyValues.Add(ks[i], a); keyValues[keyBody[i]] = a; sqlitem.Append(instring); sqlitem.Append(keyValues[keyBody[i - 1]]); sqlitem.Append(keyBody[i]); } index += 1; } sql.Append(sqlitem); string s = ""; if (isLikeSQL) { if (item.Value is JArray array) { s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", array, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } else if (item.Value is IEnumerable enumerable && !(item.Value is String)) { s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", enumerable, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } else if (item.Value is JsonElement jsonElement1) { if (jsonElement1.ValueKind is JsonValueKind.Object) { string compareOperBool = " true "; compareOperBool = CompareBoolSwitch(compareOper); string logicOperString = " and "; if (logicOperNum != 0) logicOperString = LogicOpers[logicOper]; s = logicOperString + "Contains(" + sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + " , \'" + item.Value.ToString() + "\') = " + compareOperBool + " "; } else { s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", jsonElement1, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } } else { s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } } else { s = ValueNotLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index], item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } WhereString.Append(s); if (keyListValueList) { sql = sql.Replace("select ", "select distinct "); //sql.Clear(); //sql.Append(distinctHead).Append(sqlitem); } } else { throw new BizException("数组总共深度不能超过5层", ResponseCode.PARAMS_ERROR); } heada += 1; } else { WhereString.Append(KeyNotElement(dict[item.Key], item.Key, LogicOpers[logicOper], logicOperNum, compareOper)); } // heada += 1; logicOperNum += 1; } sql.Append(" where 1=1 ").Append(WhereString); if (pageBool) { sql.Append(" OFFSET " + offsetNum + " LIMIT " + limitNum); } ReplaceKeyWords(ref sql); } } private static void GetPageNum(Dictionary dict, ref int offsetNum, ref int limitNum, ref bool pageBool) { dict.TryGetValue("OFFSET", out object offset); dict.Remove("OFFSET"); dict.TryGetValue("LIMIT", out object limit); dict.Remove("LIMIT"); if (offset != null && limit != null) { pageBool = true; offsetNum = int.Parse(offset.ToString()); limitNum = int.Parse(limit.ToString()); } } private static void CompareOperSwitch(string keyHead, ref string key, ref int compareOper) { switch (keyHead) { case ">": compareOper = (int)CompareOper.moreThan; key = key.Replace(">.", ""); break; case "<": compareOper = (int)CompareOper.lessThan; key = key.Replace("<.", ""); break; case "<=": compareOper = (int)CompareOper.notMoreThan; key = key.Replace("<=.", ""); break; case ">=": compareOper = (int)CompareOper.notLessThan; key = key.Replace(">=.", ""); break; case "=": compareOper = (int)CompareOper.equal; key = key.Replace("=.", ""); break; case "!=": compareOper = (int)CompareOper.notEqual; key = key.Replace("!=.", ""); break; case "$": compareOper = (int)CompareOper.like; key = key.Replace("$.", ""); break; case "!$": compareOper = (int)CompareOper.notLike; key = key.Replace("!$.", ""); break; default: compareOper = 4; break; } } private static string ValueNotLike(string key, object value, string logicOperParams, int logicOperNum, int compareOperNum, ref bool keyListValueList) { string logicOper = " and "; string compareOper = " = "; if (compareOperNum != 4) compareOper = CompareOpers[compareOperNum]; if (logicOperNum != 0) logicOper = logicOperParams; StringBuilder sql = new StringBuilder(logicOper + key + " in ("); if (value is JArray array) { foreach (JValue obja in array) { if (obja.Value is string a) { sql.Append("\'" + a + "\',"); } if (obja.Value is int b) { sql.Append(b + ","); } if (obja.Value is double c) { sql.Append(c + ","); } if (obja.Value is bool d) { sql.Append(d + ","); } if (obja.Value is long e) { sql.Append(e + ","); } if (obja.Value is DateTime f) { sql.Append(f + ","); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += " ) "; return sqls; } else if (value is IEnumerable enumerable && !(value is String)) { foreach (object obja in enumerable) { if (obja is string a) { sql.Append("\'" + a + "\',"); } if (obja is int b) { sql.Append(" " + b + " ,"); } if (obja is double c) { sql.Append(" " + c + " ,"); } if (obja is bool d) { sql.Append(" " + d + " ,"); } if (obja is long e) { sql.Append(" " + e + " ,"); } if (obja is DateTime f) { sql.Append(" " + f + " ,"); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += ") "; return sqls; } else if (value is JsonElement jsonElement) { if (jsonElement.ValueKind is JsonValueKind.Array) { foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { if (obja.ValueKind is JsonValueKind.String) { sql.Append("\'" + obja.ToString() + "\',"); } if (obja.ValueKind is JsonValueKind.Number) { sql.Append(" " + int.Parse(obja.ToString()) + " ,"); } if (obja.ValueKind is JsonValueKind.True) { sql.Append(" " + bool.Parse(obja.ToString()) + " ,"); } if (obja.ValueKind is JsonValueKind.False) { sql.Append(" " + bool.Parse(obja.ToString()) + " ,"); } } } else { if (jsonElement.ValueKind is JsonValueKind.String) { return logicOper + key + compareOper + "\'" + value.ToString() + "\'"; } if (jsonElement.ValueKind is JsonValueKind.Number) { return logicOper + key + compareOper + double.Parse(value.ToString()); } if (jsonElement.ValueKind is JsonValueKind.True) { return logicOper + key + compareOper + bool.Parse(value.ToString()); } if (jsonElement.ValueKind is JsonValueKind.False) { return logicOper + key + compareOper + bool.Parse(value.ToString()); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += ") "; return sqls; } else { Type s = value.GetType(); TypeCode typeCode = Type.GetTypeCode(s); if (compareOperNum == 4) keyListValueList = false; return typeCode switch { TypeCode.String => logicOper + key + compareOper + "\'" + value.ToString() + "\'", TypeCode.Char => logicOper + key + compareOper + "\'" + value.ToString() + "\'", TypeCode.Int32 => logicOper + key + compareOper + int.Parse(value.ToString()), TypeCode.Double => logicOper + key + compareOper + double.Parse(value.ToString()), //case TypeCode.Byte: return "and c." + key + "=" + (Byte)obj ; TypeCode.Boolean => logicOper + key + compareOper + bool.Parse(value.ToString()), TypeCode.DateTime => logicOper + key + compareOper + (DateTime)value, TypeCode.Int64 => logicOper + key + compareOper + long.Parse(value.ToString()), _ => null, }; } } private static string ValueIsLike(string key, object value, string logicOperParams, int logicOperNum, int compareOperNum, ref bool keyListValueList) { string compareOperBool = " true "; compareOperBool = CompareBoolSwitch(compareOperNum); string logicOper = " and "; if (logicOperNum != 0) logicOper = logicOperParams; StringBuilder s = new StringBuilder(logicOper + " ( Contains( "); if (value is JArray array) { int aa = 0; foreach (JValue obja in array) { if (aa != 0) s.Append("or Contains("); if (obja.Value is string a) { s.Append(key + "," + "\'" + a + "\') = " + compareOperBool + " "); } else if (obja.Value is int b) { s.Append("ToString( " + key + " )," + " \'" + b + "\' ) = " + compareOperBool + " "); } else if (obja.Value is double c) { s.Append("ToString( " + key + " )," + c + "\' ) = " + compareOperBool + " "); } else if (obja.Value is bool d) { s.Append("ToString( " + key + " )," + "\' " + d + "\' ) = " + compareOperBool + " "); } else if (obja.Value is long e) { s.Append("ToString( " + key + " )," + " \'" + e + "\' ) = " + compareOperBool + " "); } else if (obja.Value is DateTime f) { s.Append("ToString( " + key + " )," + " \'" + f + "\' ) = " + compareOperBool + " "); } aa++; } } else if (value is IEnumerable enumerable && !(value is String)) { int aa = 0; foreach (object obja in enumerable) { if (aa != 0) s.Append("or Contains("); if (obja is string a) { s.Append(key + "," + "\'" + a + "\') = " + compareOperBool + " "); } if (obja is int b) { s.Append("ToString( " + key + " )," + " \'" + b + " \') = " + compareOperBool + " "); } if (obja is double c) { s.Append("ToString( " + key + " )," + "\' " + c + " \') = " + compareOperBool + " "); } if (obja is bool d) { s.Append("ToString( " + key + " )," + "\' " + d + "\' ) = " + compareOperBool + " "); } if (obja is long e) { s.Append("ToString( " + key + " )," + "\' " + e + " \') = " + compareOperBool + " "); } if (obja is DateTime f) { s.Append("ToString( " + key + " )," + " \'" + f + " \') = " + compareOperBool + " "); } aa++; } } else if (value is JsonValueKind.Array && value is JsonElement jsonElement) { int aa = 0; //jsonElement.EnumerateArray().ToArray(); foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { if (aa != 0) s.Append("or Contains("); if (obja.ValueKind is JsonValueKind.String) { s.Append(key + "," + "\'" + obja.ToString() + "\') = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.Number) { s.Append("ToString( " + key + " )," + " \'" + obja.ToString() + " \') = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.True) { s.Append("ToString( " + key + " )," + " \'" + obja.ToString() + " \') = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.False) { s.Append("ToString( " + key + " )," + " \'" + obja.ToString() + " \') = " + compareOperBool + " "); } aa++; } } else { Type stype = value.GetType(); TypeCode typeCode = Type.GetTypeCode(stype); keyListValueList = false; string sql = ""; switch (typeCode) { case TypeCode.String: sql = logicOper + "Contains( " + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " "; break; case TypeCode.Char: sql = logicOper + "Contains( " + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " "; break; case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.Double: case TypeCode.Boolean: case TypeCode.DateTime: case TypeCode.Object: sql = logicOper + "Contains( ToString( " + key + " ), \'" + value.ToString() + "\' ) = " + compareOperBool + " "; break; default: break; } return sql; } s.Append(" )"); return s.ToString(); } private static string CompareBoolSwitch(int compareOperNum) { return compareOperNum switch { 6 => " true ", 7 => " false ", _ => " true ", }; } private static string KeyNotElement(object value, string key, string logicOperParams, int logicOperNum, int compareOperNum) { string compareOperBool = " true "; compareOperBool = CompareBoolSwitch(compareOperNum); string logicOper = " and "; int compareOper = 4; if (logicOperNum != 0) logicOper = logicOperParams; if (key.EndsWith(".&")) { key = key.Replace(".&", ""); } else if (key.EndsWith(".|")) { key = key.Replace(".|", ""); } string[] keyHead = key.Split("."); CompareOperSwitch(keyHead[0], ref key, ref compareOper); if (compareOper == 6 || compareOper == 7) { StringBuilder sql = new StringBuilder(logicOper + " ( Contains( "); if (value is JArray jarray) { int aa = 0; foreach (JValue obja in jarray) { if (aa != 0) sql.Append("or Contains("); if (obja.Value is string a) { sql.Append(" c." + key + ",\'" + a + "\')= " + compareOperBool + " "); } if (obja.Value is int b) { sql.Append(" ToString( c." + key + " ), \'" + b + " \')= " + compareOperBool + " "); } if (obja.Value is double c) { sql.Append(" ToString( c." + key + " ), \'" + c + " \') = " + compareOperBool + " "); } if (obja.Value is bool d) { sql.Append(" ToString( c." + key + " ), \'" + d + "\' ) = " + compareOperBool + " "); } if (obja.Value is long e) { sql.Append(" ToString( c." + key + " ), \'" + e + " \') = " + compareOperBool + " "); } if (obja.Value is DateTime f) { sql.Append(" ToString( c." + key + " ), \'" + f + " \') = " + compareOperBool + " "); } aa++; } } else if (value is IEnumerable enumerable && !(value is String)) { int aa = 0; foreach (object obja in enumerable) { if (aa != 0) sql.Append("or Contains("); if (obja is string a) { sql.Append(" c." + key + "," + "\'" + a + "\') = " + compareOperBool + " "); } if (obja is int b) { sql.Append(" ToString( c." + key + " )," + "\' " + b + "\' ) = " + compareOperBool + " "); } if (obja is double c) { sql.Append(" ToString( c." + key + " )," + "\' " + c + "\' ) = " + compareOperBool + " "); } if (obja is bool d) { sql.Append(" ToString( c." + key + " )," + "\' " + d + " \') = " + compareOperBool + " "); } if (obja is long e) { sql.Append(" ToString( c." + key + " )," + " \'" + e + "\' ) = " + compareOperBool + " "); } if (obja is DateTime f) { sql.Append(" ToString( c." + key + " )," + " \'" + f + "\') = " + compareOperBool + " "); } aa++; } } else if (value is JsonElement jsonElement && jsonElement.ValueKind! is JsonValueKind.Array) { int aa = 0; foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { if (aa != 0) sql.Append("or Contains("); if (obja.ValueKind is JsonValueKind.String) { sql.Append(" c." + key + "," + "\'" + obja.ToString() + "\') = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.Number) { sql.Append(" ToString( c." + key + " )," + " " + int.Parse(obja.ToString()) + " ) = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.True) { sql.Append(" ToString( c." + key + " )," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + " "); } if (obja.ValueKind is JsonValueKind.False) { sql.Append(" ToString( c." + key + " )," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + " "); } aa++; } } else { Type s = value.GetType(); TypeCode typeCode = Type.GetTypeCode(s); string sql1 = ""; switch (typeCode) { case TypeCode.String: sql1 = logicOper + "Contains( c." + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " "; break; case TypeCode.Char: sql1 = logicOper + "Contains( c." + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " "; break; case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.Double: case TypeCode.Boolean: case TypeCode.DateTime: case TypeCode.Object: sql1 = logicOper + "Contains( ToString( c." + key + " ), \'" + value.ToString() + "\' ) = " + compareOperBool + " "; break; default: break; } return sql1; } sql.Append(")"); return sql.ToString(); } else { StringBuilder sql = new StringBuilder(logicOper + " c." + key + " in ("); if (value is JArray array) { foreach (JValue obja in array) { if (obja.Value is string a) { sql.Append("\'" + a + "\',"); } if (obja.Value is int b) { sql.Append(b + ","); } if (obja.Value is double c) { sql.Append(c + ","); } if (obja.Value is bool d) { sql.Append(d + ","); } if (obja.Value is long e) { sql.Append(e + ","); } if (obja.Value is DateTime f) { sql.Append(f + ","); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += " ) "; return sqls; } else if (value is IEnumerable enumerable && !(value is String)) { foreach (object obja in enumerable) { if (obja is string a) { sql.Append("\'" + a + "\',"); } if (obja is int b) { sql.Append(" " + b + " ,"); } if (obja is double c) { sql.Append(" " + c + " ,"); } if (obja is bool d) { sql.Append(" " + d + " ,"); } if (obja is long e) { sql.Append(" " + e + " ,"); } if (obja is DateTime f) { sql.Append(" " + f + " ,"); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += ") "; return sqls; } else if (value is JsonElement jsonElement) { if (jsonElement.ValueKind is JsonValueKind.Array) { foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { if (obja.ValueKind is JsonValueKind.String) { sql.Append("\'" + obja.ToString() + "\',"); } if (obja.ValueKind is JsonValueKind.Number) { sql.Append(" " + int.Parse(obja.ToString()) + " ,"); } if (obja.ValueKind is JsonValueKind.True) { sql.Append(" " + bool.Parse(obja.ToString()) + " ,"); } if (obja.ValueKind is JsonValueKind.False) { sql.Append(" " + bool.Parse(obja.ToString()) + " ,"); } } } else { if (jsonElement.ValueKind is JsonValueKind.String) { return logicOper + " c." + key + CompareOpers[compareOperNum] + "\'" + value.ToString() + "\'"; } if (jsonElement.ValueKind is JsonValueKind.Number) { return logicOper + " c." + key + CompareOpers[compareOperNum] + double.Parse(value.ToString()); } if (jsonElement.ValueKind is JsonValueKind.True) { return logicOper + " c." + key + CompareOpers[compareOperNum] + bool.Parse(value.ToString()); } if (jsonElement.ValueKind is JsonValueKind.False) { return logicOper + " c." + key + CompareOpers[compareOperNum] + bool.Parse(value.ToString()); } } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += ") "; return sqls; } else { Type s = value.GetType(); TypeCode typeCode = Type.GetTypeCode(s); return typeCode switch { TypeCode.String => logicOper + " c." + key + CompareOpers[compareOperNum] + "\'" + value.ToString() + "\'", TypeCode.Char => logicOper + " c." + key + CompareOpers[compareOperNum] + "\'" + value.ToString() + "\'", TypeCode.Int32 => logicOper + " c." + key + CompareOpers[compareOperNum] + int.Parse(value.ToString()), TypeCode.Double => logicOper + " c." + key + CompareOpers[compareOperNum] + double.Parse(value.ToString()), //case TypeCode.Byte: return "and c." + key + "=" + (Byte)obj ; TypeCode.Boolean => logicOper + " c." + key + CompareOpers[compareOperNum] + bool.Parse(value.ToString()), TypeCode.DateTime => logicOper + " c." + key + CompareOpers[compareOperNum] + (DateTime)value, TypeCode.Int64 => logicOper + " c." + key + CompareOpers[compareOperNum] + long.Parse(value.ToString()), _ => null, }; } } } public enum LogicOper : int { and = 0, or = 1 } public enum CompareOper : int { moreThan = 0, lessThan = 1, notMoreThan = 2, notLessThan = 3, equal = 4, notEqual = 5, like = 6, notLike = 7, IN = 8 } } }