using Newtonsoft.Json.Linq; using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.Json; using System.Threading.Tasks; namespace TEAMModelOS.SDK.Module.AzureCosmosDBV3 { public class SQLHelperParametric { 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 CosmosDbQuery GetSQL(Dictionary dict, StringBuilder sql) { if (dict != null) { Dictionary parmeters = new Dictionary(); 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) { key = key.Replace("[*].", ""); key = key.Replace(".", ""); 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] + "", key, 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] + "", key, 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] + "", key, jsonElement1, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } } else { s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", key, item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } } else { s = ValueNotLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", key, item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList); } WhereString.Append(s); if (keyListValueList) { sql = sql.Replace("select ", "select distinct "); } } else { //throw new BizException("数组总共深度不能超过5层", ResponseCode.PARAMS_ERROR); } heada += 1; } else { string itemKey = item.Key.Replace(".", ""); WhereString.Append(KeyNotElement(dict[item.Key], item.Key, itemKey, 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); //sql = sql.Replace("[*].", ""); parmeters = GetParmeter(dict, parmeters); CosmosDbQuery cosmosDbQuery = new CosmosDbQuery { QueryText = sql.ToString(), Parameters = parmeters }; return cosmosDbQuery; } return null; } 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, string key1, 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) { int aa = 0; foreach (JValue obja in array) { sql.Append(" @" + key1 + aa + " ,"); //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 + ","); //} aa++; } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += " ) "; return sqls; } else if (value is IEnumerable enumerable && !(value is String)) { int aa = 0; foreach (object obja in enumerable) { sql.Append(" @" + key1 + aa + " ,"); //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 + " ,"); //} aa++; } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += ") "; return sqls; } else if (value is JsonElement jsonElement) { if (jsonElement.ValueKind is JsonValueKind.Array) { int aa = 0; foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { sql.Append(" @" + key1 + aa + " ,"); //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()) + " ,"); //} aa++; } } else { return logicOper + key + compareOper + " @" + key1 + " "; //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 logicOper + key + compareOper + " @" + key1 + " "; //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, string key1, 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("); s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " "); //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("); s.Append(key + "," + " @" + key1 + aa + " ) = " + 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("); s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " "); aa++; } } else { Type stype = value.GetType(); TypeCode typeCode = Type.GetTypeCode(stype); keyListValueList = false; string sql = ""; sql = logicOper + "Contains( " + key + " , @" + key1 + " ) = " + compareOperBool + " "; 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 key1, 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("); sql.Append(" c." + key + ", @" + key1 + aa + " )= " + 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("); sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + 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("); sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " "); aa++; } } else { Type s = value.GetType(); TypeCode typeCode = Type.GetTypeCode(s); string sql1 = ""; sql1 = logicOper + "Contains( c." + key + " , @" + key1 + " ) = " + compareOperBool + " "; return sql1; } sql.Append(")"); return sql.ToString(); } else { StringBuilder sql = new StringBuilder(logicOper + " c." + key + " in ("); if (value is JArray array) { int aa = 0; foreach (JValue obja in array) { sql.Append(" @" + key1 + aa + " ,"); aa++; } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += " ) "; return sqls; } else if (value is IEnumerable enumerable && !(value is String)) { int aa = 0; foreach (object obja in enumerable) { sql.Append(" @" + key1 + aa + " ,"); aa++; } string sqls = sql.ToString().Substring(0, sql.Length - 1); sqls += " ) "; return sqls; } else if (value is JsonElement jsonElement) { if (jsonElement.ValueKind is JsonValueKind.Array) { int aa = 0; foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { sql.Append(" @" + key1 + aa + " ,"); aa++; } } else { return logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1; } 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] + " @" + key1,// + "\'" + value.ToString() + "\'", TypeCode.Char => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + "\'" + value.ToString() + "\'", TypeCode.Int32 => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + int.Parse(value.ToString()), TypeCode.Double => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + double.Parse(value.ToString()), //case TypeCode.Byte: return "and c." + key + "=" + (Byte)obj ; TypeCode.Boolean => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + bool.Parse(value.ToString()), TypeCode.DateTime => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + (DateTime)value, TypeCode.Int64 => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + 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 } private static Dictionary GetParmeter(Dictionary dict, Dictionary parmeters) { foreach (KeyValuePair keyValue in dict) { string key = ""; string[] keyHead = keyValue.Key.Split("."); switch (keyHead[0]) { case ">": key = keyValue.Key.Replace(">.", ""); break; case "<": key = keyValue.Key.Replace("<.", ""); break; case "<=": key = keyValue.Key.Replace("<=.", ""); break; case ">=": key = keyValue.Key.Replace(">=.", ""); break; case "=": key = keyValue.Key.Replace("=.", ""); break; case "!=": key = keyValue.Key.Replace("!=.", ""); break; case "$": key = keyValue.Key.Replace("$.", ""); break; case "!$": key = keyValue.Key.Replace("!$.", ""); break; default: key = keyValue.Key; break; } if (key.EndsWith(".&")) { key = key.Replace(".&", ""); } else if (key.EndsWith(".|")) { key = key.Replace(".|", ""); } key = key.Replace("[*].", ""); key = key.Replace(".", ""); if (keyValue.Value is JArray array) { int aa = 0; foreach (JValue obja in array) { parmeters.Add("@" + key + aa, obja); aa++; } } else if (keyValue.Value is JsonElement jsonElement) { if (jsonElement.ValueKind is JsonValueKind.Array) { int aa = 0; foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray()) { if (obja.ValueKind is JsonValueKind.String) { parmeters.Add("@" + key + aa, obja.ToString()); } if (obja.ValueKind is JsonValueKind.Number) { parmeters.Add("@" + key + aa, double.Parse(obja.ToString())); } if (obja.ValueKind is JsonValueKind.True) { parmeters.Add("@" + key + aa, bool.Parse(obja.ToString())); } if (obja.ValueKind is JsonValueKind.False) { parmeters.Add("@" + key + aa, bool.Parse(obja.ToString())); } aa++; } } else { if (jsonElement.ValueKind is JsonValueKind.String) { parmeters.Add("@" + key, keyValue.Value.ToString()); } else if (jsonElement.ValueKind is JsonValueKind.Number) { parmeters.Add("@" + key, double.Parse(keyValue.Value.ToString())); } else if (jsonElement.ValueKind is JsonValueKind.True) { parmeters.Add("@" + key, bool.Parse(keyValue.Value.ToString())); } else if (jsonElement.ValueKind is JsonValueKind.False) { parmeters.Add("@" + key, bool.Parse(keyValue.Value.ToString())); } else { parmeters.Add("@" + key, keyValue.Value.ToString()); } } } else if (keyValue.Value is IEnumerable enumerable) { int aa = 0; foreach (object obja in enumerable) { parmeters.Add("@" + key + aa, obja); aa++; } } else { parmeters.Add("@" + key, keyValue.Value); } //parmeters.Add("@" + key, keyValue.Value.ToString()); } return parmeters; } } }