using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using NG3.Data;

namespace SUP.Common.Base
{
    public class DataConverterHelper
    {
        private static NGJsonConverter converter = new NGJsonConverter();
        public DataConverterHelper()
        {
 
        }

        /// <summary>
        /// 把json数据转换成DataTable
        /// </summary>
        /// <param name="json">json串</param>
        /// <param name="selectSql">取表结构的sql语句</param>
        /// <returns></returns>
        public static DataTable ToDataTable(string json, string selectSql)
        {
            return converter.ToDataTable(json, selectSql);
        }

        /// <summary>
        /// 把json数据转换成DataTable
        /// </summary>
        /// <param name="jo">json对象</param>
        /// <param name="selectSql">取表结构的sql语句</param>
        /// <returns></returns>
        public static DataTable ToDataTable(JObject jo, string selectSql)
        {
            return converter.ToDataTable(jo, selectSql);
        }


        /// <summary>
        /// 把DataTable转成json数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static string ToJson(DataTable dt, int totalRecord)
        {
            return converter.ToJson(dt, totalRecord);
        }


        /// <summary>
        /// 根据json串构建查询条件语句
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static string BuildQuery(string json)
        {
            if (string.IsNullOrEmpty(json)) return string.Empty;

            Dictionary<string, object> d = JsonConvert.DeserializeObject<Dictionary<string, object>>(json);

            int i = 0;
            StringBuilder strb = new StringBuilder();
            foreach (KeyValuePair<string, object> item in d)
            {                

                if (item.Value is string)
                {
                    if (!string.IsNullOrEmpty(item.Value.ToString()))
                    {
                        if (i > 0)
                        {
                            strb.Append(" and ");
                        }
                        
                        if (item.Key.ToString().EndsWith("*ngLow"))//下限
                        {
                            string[] arr = item.Key.Split('*');

                            if (arr[1] == "num")//数字字段
                            {
                                strb.Append(arr[0] + " >= " + item.Value);
                            }
                            else//日期字段
                            {
                                strb.Append(arr[0] + " >= '" + item.Value + "'");
                            }
                        }
                        else if (item.Key.ToString().EndsWith("*ngUP"))//上限
                        {
                            string[] arr = item.Key.Split('*');

                            if (arr[1] == "num")//数字字段
                            {
                                strb.Append(arr[0] + " <= " + item.Value);
                            }
                            else//日期
                            {
                                strb.Append(arr[0] + " <= '" + item.Value + "'");
                            }
                        }
                        else
                        {
                            strb.Append(item.Key + " like '%" + item.Value + "%'");
                        }

                        i++;
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(item.Value.ToString()))
                    {
                        if (i > 0)
                        {
                            strb.Append(" and ");
                        }

                        if (item.Key.ToString().EndsWith("*ngLow"))//下限
                        {
                            string[] arr = item.Key.Split('*');

                            if (arr[1] == "num")//数字字段
                            {
                                strb.Append(arr[0] + " >= " + item.Value);
                            }
                            else//日期
                            {
                                strb.Append(arr[0] + " >= '" + item.Value + "'");
                            }
                        }
                        else if (item.Key.ToString().EndsWith("*ngUP"))//上限
                        {
                            string[] arr = item.Key.Split('*');

                            if (arr[1] == "num")//数字字段
                            {
                                strb.Append(arr[0] + " <= " + item.Value);
                            }
                            else//日期
                            {
                                strb.Append(arr[0] + " <= '" + item.Value + "'");
                            }
                        }
                        else
                        {
                            strb.Append(item.Key + "=" + item.Value);
                        }

                        i++;
                    }
                }

               
            }

            return strb.ToString();
            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="json"></param>
        /// <param name="outFilterJson">通用帮助的外部查询条件</param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static IDataParameter[]  BuildQueryWithParam(string json, string outFilterJson, ref string where)
        {
            int i = 0;
            int paramCout = 0;
            StringBuilder strb = new StringBuilder();
            List<NGDataParameter> paramList = new List<NGDataParameter>();

            if (!string.IsNullOrEmpty(json))
            {
                Dictionary<string, object> d = JsonConvert.DeserializeObject<Dictionary<string, object>>(json);

                foreach (KeyValuePair<string, object> item in d)
                {

                    if (item.Value is string)
                    {
                        if (!string.IsNullOrEmpty(item.Value.ToString()))
                        {
                            if (i > 0)
                            {
                                strb.Append(" and ");
                            }

                            string columnName = string.Empty;
                            DbType dbtype = DbType.AnsiString;

                            if (item.Key.ToString().EndsWith("*ngLow"))//下限
                            {
                                string[] arr = item.Key.Split('*');

                                if (arr[1] == "num")//数字字段
                                {
                                    strb.Append(arr[0] + " >= " + item.Value);
                                }
                                else//日期、字符字段
                                {
                                    strb.Append(arr[0] + " >= '" + item.Value + "'");
                                }
                            }
                            else if (item.Key.ToString().EndsWith("*ngUP"))//上限
                            {
                                string[] arr = item.Key.Split('*');

                                if (arr[1] == "num")//数字字段
                                {
                                    strb.Append(arr[0] + " <= " + item.Value);
                                }
                                else//日期、字符字段
                                {
                                    strb.Append(arr[0] + " <= '" + item.Value + "'");
                                }
                            }
                            else
                            {
                                columnName = item.Key;
                                strb.Append(item.Key + " like '%' +{" + paramCout.ToString() + "}+ '%'");

                                NGDataParameter p = new NGDataParameter(columnName, dbtype);
                                p.Value = item.Value;
                                paramList.Add(p);

                                paramCout++;
                            }

                            i++;
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(outFilterJson))
            {
                if (!string.IsNullOrEmpty(outFilterJson))//通用帮助外部条件处理
                {
                    Dictionary<string, object> outFilter = JsonConvert.DeserializeObject<Dictionary<string, object>>(outFilterJson);//通用帮助
                    foreach (KeyValuePair<string, object> item in outFilter)
                    {
                        if (item.Value is string)
                        {
                            if (!string.IsNullOrEmpty(item.Value.ToString()))
                            {
                                if (i > 0)
                                {
                                    strb.Append(" and ");
                                }

                                string columnName = string.Empty;
                                DbType dbtype = DbType.AnsiString;

                                if (item.Key.ToString().EndsWith("*ngLow"))//下限
                                {
                                    string[] arr = item.Key.Split('*');

                                    if (arr[1] == "num")//数字字段
                                    {
                                        strb.Append(arr[0] + " >= " + item.Value);
                                    }
                                    else//日期或者字符
                                    {
                                        strb.Append(arr[0] + " >= '" + item.Value + "'");
                                    }
                                }
                                else if (item.Key.ToString().EndsWith("*ngUP"))//上限
                                {
                                    string[] arr = item.Key.Split('*');

                                    if (arr[1] == "num")//数字字段
                                    {
                                        strb.Append(arr[0] + " <= " + item.Value);
                                    }
                                    else//日期或者字符
                                    {
                                        strb.Append(arr[0] + " <= '" + item.Value + "'");
                                    }
                                }
                                else
                                {
                                    columnName = item.Key;
                                    strb.Append(item.Key + "={" + paramCout.ToString() + "}");//外部条件用"="

                                    NGDataParameter p = new NGDataParameter(columnName, dbtype);
                                    p.Value = item.Value;
                                    paramList.Add(p);

                                    paramCout++;
                                }
                                i++;
                            }
                        }
                    }
                }
            }

            where = strb.ToString();
            return paramList.ToArray();
        }
    }
}