TEAMModelOS/TEAMModelOS.SDK/Module/AzureCosmosDB/Configuration/SQLHelper.cs

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<string, object> 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<KeyValuePair<string, object>> stack = new Stack<KeyValuePair<string, object>>();

                foreach (string item in dict.Keys)
                {
                    if (item.EndsWith(".|"))
                    {
                        stack.Push(new KeyValuePair<string, object>(item, dict[item]));
                    }

                }
                foreach (string item in dict.Keys)
                {
                    if (!item.EndsWith(".|"))
                    {
                        stack.Push(new KeyValuePair<string, object>(item, dict[item]));
                    }

                }


                string Join = " join ";
                string instring = " in ";
                Dictionary<string, string> keyValues = new Dictionary<string, string>();
                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<string, object> item in newDict)
                for (int k = 0; k < stackCount; k++)
                {
                    KeyValuePair<string, object> 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.Append(sqlitem);
                            }
                        }
                        else
                        {
                            throw new BizException("数组总共深度不能超过5层", ResponseCode.PARAMS_ERROR);
                        }


                    }
                    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<string, object> 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)
            {
                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()) + " ,");
                    }
                }
                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(key + "," + " " + b + " ) = " + compareOperBool + " ");

                    }
                    else if (obja.Value is double c)
                    {
                        s.Append(key + "," + " " + c + " ) = " + compareOperBool + " ");
                    }
                    else if (obja.Value is bool d)
                    {
                        s.Append(key + "," + " " + d + " ) = " + compareOperBool + " ");

                    }
                    else if (obja.Value is long e)
                    {
                        s.Append(key + "," + " " + e + " ) = " + compareOperBool + " ");
                    }
                    else if (obja.Value is DateTime f)
                    {
                        s.Append(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(key + "," + " " + b + " ) = " + compareOperBool + " ");

                    }
                    if (obja is double c)
                    {
                        s.Append(key + "," + " " + c + " ) = " + compareOperBool + " ");
                    }
                    if (obja is bool d)
                    {
                        s.Append(key + "," + " " + d + " ) = " + compareOperBool + " ");

                    }
                    if (obja is long e)
                    {
                        s.Append(key + "," + " " + e + " ) = " + compareOperBool + " ");
                    }
                    if (obja is DateTime f)
                    {
                        s.Append(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(key + "," + " " + int.Parse(obja.ToString()) + " ) = " + compareOperBool + " ");
                    }
                    if (obja.ValueKind is JsonValueKind.True)
                    {
                        s.Append(key + "," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + " ");
                    }
                    if (obja.ValueKind is JsonValueKind.False)
                    {
                        s.Append(key + "," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + " ");
                    }
                    aa++;
                }
            }
            else
            {
                Type stype = value.GetType();
                TypeCode typeCode = Type.GetTypeCode(stype);
                keyListValueList = false;
                return typeCode switch
                {
                    TypeCode.String => logicOper + "Contains( " + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " ",
                    TypeCode.Char => logicOper + "Contains( " + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " ",
                    TypeCode.Object => logicOper + "Contains( " + key + " , \'" + value.ToString() + "\') = " + compareOperBool + " ",
                    TypeCode.Int32 => logicOper + "Contains( " + key + " , " + int.Parse(value.ToString()) + ") = " + compareOperBool + " ",
                    TypeCode.Double => logicOper + "Contains( " + key + " , " + double.Parse(value.ToString()) + ") = " + compareOperBool + " ",
                    //case TypeCode.Byte: return "and c." + key + "=" + (Byte)obj ;   
                    TypeCode.Boolean => logicOper + "Contains( " + key + " , " + bool.Parse(value.ToString()) + ") = " + compareOperBool + " ",
                    TypeCode.DateTime => logicOper + "Contains( " + key + " , " + (DateTime)value + ") = " + compareOperBool + " ",
                    TypeCode.Int64 => logicOper + "Contains( " + key + " , " + long.Parse(value.ToString()) + ") = " + compareOperBool + " ",
                    _ => null,
                };
            }
            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(" c." + key + ", " + b + " )= " + compareOperBool + "  ");
                        }
                        if (obja.Value is double c)
                        {
                            sql.Append(" c." + key + ", " + c + " ) = " + compareOperBool + "  ");
                        }
                        if (obja.Value is bool d)
                        {
                            sql.Append(" c." + key + ", " + d + " ) = " + compareOperBool + "  ");

                        }
                        if (obja.Value is long e)
                        {
                            sql.Append(" c." + key + ", " + e + " ) = " + compareOperBool + "  ");
                        }
                        if (obja.Value is DateTime f)
                        {
                            sql.Append(" 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(" c." + key + "," + " " + b + " ) = " + compareOperBool + "  ");
                        }
                        if (obja is double c)
                        {
                            sql.Append(" c." + key + "," + " " + c + " ) = " + compareOperBool + "  ");
                        }
                        if (obja is bool d)
                        {
                            sql.Append(" c." + key + "," + " " + d + " ) = " + compareOperBool + "  ");
                        }
                        if (obja is long e)
                        {
                            sql.Append(" c." + key + "," + " " + e + " ) = " + compareOperBool + "  ");
                        }
                        if (obja is DateTime f)
                        {
                            sql.Append(" c." + key + "," + " " + f + " ) = " + compareOperBool + "  ");
                        }
                        aa++;
                    }
                }
                else if (value is JsonElement jsonElement)
                {
                    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(" c." + key + "," + " " + int.Parse(obja.ToString()) + " ) = " + compareOperBool + "  ");
                        }
                        if (obja.ValueKind is JsonValueKind.True)
                        {
                            sql.Append(" c." + key + "," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + "  ");
                        }
                        if (obja.ValueKind is JsonValueKind.False)
                        {
                            sql.Append(" c." + key + "," + " " + bool.Parse(obja.ToString()) + " ) = " + compareOperBool + "  ");
                        }
                        aa++;
                    }
                }
                else
                {
                    Type s = value.GetType();
                    TypeCode typeCode = Type.GetTypeCode(s);

                    return typeCode switch
                    {
                        TypeCode.String => logicOper + "Contains( c." + key + " , \'" + value.ToString() + "\') = " + compareOperBool + "  ",
                        TypeCode.Char => logicOper + "Contains( c." + key + " , \'" + value.ToString() + "\') = " + compareOperBool + "  ",
                        TypeCode.Int32 => logicOper + "Contains( c." + key + " , " + int.Parse(value.ToString()) + ") = " + compareOperBool + "  ",
                        TypeCode.Double => logicOper + "Contains( c." + key + " , " + double.Parse(value.ToString()) + ") = " + compareOperBool + "  ",
                        //case TypeCode.Byte: return "and c." + key + "=" + (Byte)obj ;   
                        TypeCode.Boolean => logicOper + "Contains( c." + key + " , " + bool.Parse(value.ToString()) + ") = " + compareOperBool + "  ",
                        TypeCode.DateTime => logicOper + "Contains( c." + key + " , " + (DateTime)value + ") = " + compareOperBool + "  ",
                        TypeCode.Int64 => logicOper + "Contains( c." + key + " , " + long.Parse(value.ToString()) + ") = " + compareOperBool + "  ",
                        _ => null,
                    };
                }

                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)
                {
                    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()) + " ,");
                        }
                    }
                    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
        }

    }
}