TEAMModelOS/TEAMModelOS.SDK/Module/AzureCosmosDBV3/SQLHelperParametric.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 System.Text.RegularExpressions;
using TEAMModelOS.SDK.Context.Exception;
using TEAMModelOS.SDK.Helper.Common.CollectionHelper;

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 StringBuilder GetSQLSelect(List<string> propertys)
        {
            StringBuilder sql;
            if (propertys.IsNotEmpty())
            {
                string tmpSQL = " SELECT ";
                int i = 0;
                foreach (string item in propertys)
                {
                    if (i == 0)
                    {
                        tmpSQL += " c." + item + " ";
                    }
                    else
                    {
                        tmpSQL += " ,c." + item + " ";
                    }
                    i++;
                }
                tmpSQL += "from c";

                sql = new StringBuilder(tmpSQL);
            }
            else
            {
                sql = new StringBuilder("select value(c) from c");
            }

            return sql;
        }

        public static CosmosDbQuery GetSQL(Dictionary<string, object> dict, StringBuilder sql)
        {

            if (dict != null)
            {
                Dictionary<string, object> parmeters = new Dictionary<string, object>();

                int offsetNum = 0;
                int limitNum = 0;
                bool pageBool = false;
                GetPageNum(dict, ref offsetNum, ref limitNum, ref pageBool);

                string OrderByValue = "";

                // 处理OrderBy
                if (dict.TryGetValue("@ASC", out object ASC))
                {
                    OrderByValue = Regex.Replace(ASC.ToString(), "\\s{1,}", "");
                    dict.Remove("@ASC");
                }
                if (dict.TryGetValue("@DESC", out object DESC))
                {
                    OrderByValue = Regex.Replace(DESC.ToString(), "\\s{1,}", "");
                    dict.Remove("@DESC");
                };

                
              


                //处理顺序
                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;
                int keyListValueListNum = 0;
                int stackCount = stack.Count;
                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)
                    {
                        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)
                            {

                                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 && keyListValueListNum == 0)
                            {
                                sql = sql.Replace("select ", "select distinct ");
                                keyListValueList = false;
                                keyListValueListNum++;
                            }
                        }
                        else
                        {
                            throw new BizException("数组总共深度不能超过5层", ResponseCode.PARAMS_ERROR);
                        }
                        heada += 1;

                    }
                    else
                    {
                        string itemKey = key.Replace(".", "");
                        WhereString.Append(KeyNotElement(dict[item.Key], item.Key, itemKey, LogicOpers[logicOper], logicOperNum, compareOper));
                    }
                    logicOperNum += 1;
                }

                sql.Append(" where 1=1 ").Append(WhereString);

                if (ASC != null)
                {
                    sql.Append(" Order By c." + OrderByValue );
                    //sql.Append(" Order By c." + "@OrderByValue");
                }
                else if (DESC != null) {
                    sql.Append(" Order By c." + OrderByValue );
                   // sql.Append(" Order By c." + "@OrderByValue"+ " DESC " );
                }

                if (pageBool && offsetNum != 0 && limitNum != 0)
                {
                    //sql.Append(" OFFSET " + offsetNum + " LIMIT " + limitNum);
                    sql.Append(" OFFSET " + " @offsetNum " + " LIMIT " + " @limitNum ");
                }


                ReplaceKeyWords(ref sql);
                parmeters = GetParmeter(dict, parmeters, offsetNum, limitNum);
                CosmosDbQuery cosmosDbQuery = new CosmosDbQuery
                {
                    QueryText = sql.ToString(),
                    Parameters = parmeters
                };
                return cosmosDbQuery;
            }
            return null;
        }

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

                    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
                {
                    if (compareOperNum == 4) keyListValueList = false;
                    return logicOper + key + compareOper + " @" + key1 + " ";
                }

                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 + " ";

            }
        }

        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)
                    {
                        s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                    }
                    else
                    {
                        s.Append("ToString( " + 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) s.Append("or Contains(");
                    if (obja is string)
                    {
                        s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                    }
                    else
                    {
                        s.Append("ToString( " + 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) s.Append("or Contains(");
                    if (obja.ValueKind is JsonValueKind.String)
                    {
                        s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                    }
                    else
                    {
                        s.Append("ToString( " + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
                    }

                    aa++;
                }
            }
            else
            {
                Type stype = value.GetType();
                TypeCode typeCode = Type.GetTypeCode(stype);
                keyListValueList = false;
                string sql = "";
                if (value is string)
                {
                    sql = logicOper + "Contains( " + key + " ,  @" + key1 + " ) = " + compareOperBool + " ";
                }
                else if (value is JsonElement jsonElement1 && jsonElement1.ValueKind is JsonValueKind.String)
                {
                    sql = logicOper + "Contains( " + key + " ,  @" + key1 + " ) = " + compareOperBool + " ";
                }
                else
                {
                    sql = logicOper + "Contains( ToString(" + 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(");
                        if (obja.Value is string)
                        {
                            sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                        }
                        else
                        {
                            sql.Append("ToString( c." + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
                        }
                        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(");
                        if (obja is string)
                        {
                            sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                        }
                        else
                        {
                            sql.Append("ToString( 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(");
                        if (obja.ValueKind is JsonValueKind.String)
                        {
                            sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
                        }
                        else
                        {
                            sql.Append("ToString( c." + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
                        }
                        aa++;
                    }
                }
                else
                {
                    Type s = value.GetType();
                    TypeCode typeCode = Type.GetTypeCode(s);

                    string sql1 = "";
                    if (value is string)
                    {
                        sql1 = logicOper + "Contains(  c." + key + " ,  @" + key1 + " ) = " + compareOperBool + " ";
                    }
                    else
                    {
                        sql1 = logicOper + "Contains(  ToString( 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()),
                        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<string, object> GetParmeter(Dictionary<string, object> dict, Dictionary<string, object> parmeters,int offset = 0,int limit = 0)
        {
            foreach (KeyValuePair<string, object> 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 IEnumerable enumerable && !(keyValue.Value is String))
                {
                    int aa = 0;
                    foreach (object obja in enumerable)
                    {
                        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
                {
                    parmeters.Add("@" + key, keyValue.Value);
                }
            }
            if (offset != 0 && limit != 0)
            {
                parmeters.Add("@offsetNum", offset);
                parmeters.Add("@limitNum", limit);
            }

            return parmeters;
        }


    }
}