using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Script.Serialization;

namespace HH.WMS.Utils
{
    public class SqlManager
    {
        #region 根据JSON字符串 获得表名、查询条件、排序
        /// <summary>
        /// 根据JSON字符串 获得表名、查询条件、排序 [分页调用]
        /// </summary>
        /// <param name="jsonString"></param>
        /// <param name="tabName">表名[分页调用]</param>
        /// <param name="strWhere">查询条件[分页调用]</param>
        /// <param name="orderBy">排序[分页调用]</param>
        /// <returns>不分页的SQL语句[不分页获得]</returns>
        public static string GetSqlString_Page(string jsonString, out string tabName, out string strWhere, out string orderBy)
        {
            JavaScriptSerializer js = new JavaScriptSerializer();
            Dictionary<string, string>[] dicArray = js.Deserialize<Dictionary<string, string>[]>(jsonString);

            //不分页查询
            StringBuilder strSql = new StringBuilder();

            //定义表名称
            string tableName = string.Empty;
            //定义查询条件
            StringBuilder sqlWhere = new StringBuilder();
            sqlWhere.Append(" WHERE 1=1 ");
            //定义排序
            string orderby = string.Empty;

            foreach (var item in dicArray)
            {
                Dictionary<string, string> dic = item;

                string name = dic["name"].ToString();
                //如果是表名称
                if (name.Equals("tableName"))
                {
                    tableName = dic["value"] + " ";
                }
                //如果是查询条件
                else if (name.Equals("select"))
                {
                    string colmn_id = dic["value"].ToString();
                    string colmn_value = dic["text"].ToString();
                    string colmn_key = dic["key"].ToString();
                    string colmn_type = dic["type"].ToString();
                    if (!string.IsNullOrEmpty(colmn_value))
                    {
                        sqlWhere.Append(GetWhereUnit(colmn_id, colmn_value, colmn_key, colmn_type));
                    }
                }
                //如果是排序
                else if (name.Equals("orderby"))
                {
                    string orderby_colmn = dic["value"].ToString();
                    string sort = dic["type"].ToString();

                    orderby = " order by " + orderby_colmn + " " + sort;
                }
            }
            tabName = tableName;
            strWhere = sqlWhere.ToString();
            orderBy = orderby;


            strSql.AppendLine("SELECT * FROM " + tableName + sqlWhere.ToString() + orderby);

            return strSql.ToString();
        }
        #endregion

        #region 获得查询条件
        /// <summary>
        /// 获得查询条件
        /// </summary>
        /// <param name="colmn_id">字段名</param>
        /// <param name="colmn_value">字段值</param>
        /// <param name="colmn_key">查询时的关键字( '=' 'like')</param>
        /// <param name="colmn_type">字段类型   char  varchar  int</param>
        /// <returns></returns>
        private static string GetWhereUnit(string colmn_id, string colmn_value, string colmn_key, string colmn_type)
        {
            string sqlString = string.Empty;

            switch (colmn_key)
            {
                case "=":
                    sqlString = " AND " + colmn_id + "=" + GetValue(colmn_value, colmn_type);
                    break;
                case "!=":
                    sqlString = " AND " + colmn_id + " != " + GetValue(colmn_value, colmn_type);
                    break;
                case "like":
                    sqlString = " AND " + colmn_id + " like '%" + colmn_value + "%' ";
                    break;
                default:
                    break;
            }
            return sqlString;
        }
        #endregion

        #region 判断是否需要加单引号
        /// <summary>
        /// 判断是否需要加单引号
        /// </summary>
        /// <param name="colmn_value"></param>
        /// <param name="colmn_type"></param>
        /// <returns></returns>
        private static string GetValue(string colmn_value, string colmn_type)
        {
            string value = string.Empty;
            switch (colmn_type.ToLower())
            {
                case "varchar":
                    value = " '" + colmn_value + "' ";
                    break;
                case "char":
                    value = " '" + colmn_value + "' ";
                    break;
                case "int":
                    value = colmn_value;
                    break;
                default:
                    break;
            }
            return value;
        }
        #endregion
    }
}