using HH.WMS.Common.MagicModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Script.Serialization;

namespace HH.WMS.Common.MagicModel
{
    public class MagicDataAccess
    {
        #region 根据实体 获得表名、查询条件、排序
        /// <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 GetMagic_Page_Model(MagicQueryEntity entity, out string strWhere, out string orderBy, out string tbName)
        {
            //定义表名称
            string tableName = entity.tableName;

            //定义查询条件
            StringBuilder sqlWhere = new StringBuilder();
            sqlWhere.Append(" WHERE 1=1 ");

            #region 查询条件
            //查询条件
            List<QueryEntity> lstQuery = entity.lstQuery;
            if (lstQuery != null && lstQuery.Count > 0)
            {
                foreach (QueryEntity item in lstQuery)
                {
                    //字段名
                    string colmn_name = item.name.ToString();
                    //查询关键字
                    string colmn_key = item.key.ToString();
                    //字段值
                    string colmn_value = item.value.ToString();
                    //字段类型
                    string colmn_type = item.type.ToString();

                    //字段名不为空
                    if (!string.IsNullOrEmpty(colmn_name))
                    {
                        sqlWhere.Append(GetWhereUnit(colmn_name, colmn_value, colmn_key, colmn_type));
                    }
                }
            }
            #endregion

            #region 定义排序
            //定义排序
            List<OrderByEntity> listSort = entity.lstOrderBy;
            //定义排序
            string orderby = string.Empty;
            if (listSort != null && listSort.Count > 0)
            {
                orderby = " order by ";
                for (int j = 0; j < listSort.Count; j++)
                {
                    if (string.IsNullOrEmpty(listSort[j].name) || string.IsNullOrEmpty(listSort[j].type))
                    {
                        orderby = "";
                        break;
                    }
                    orderby += listSort[j].name + " " + listSort[j].type + ",";
                }

                if (!string.IsNullOrEmpty(orderby))
                {
                    orderby = orderby.TrimEnd(',');
                }
            }
            #endregion

            string fileds = "*";
            if (!string.IsNullOrEmpty(entity.fileds))
            {
                fileds = entity.fileds;
            }

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

            string tree_tbName = entity.tableName;
            //如果是树状结构展示
            if (entity.isTree)
            {
                StringBuilder  treeWhere = new StringBuilder();
                string className = entity.className;
                if (!string.IsNullOrEmpty(className))
                {
                    treeWhere.Append(" AND A.CN_S_CLASS='" + className + "' ");
                }
                string funcType = entity.funcType;
                if (!string.IsNullOrEmpty(funcType))
                {
                    treeWhere.Append(" AND A.CN_S_FUNCTYPE='" + funcType + "' ");
                }

                strSql.AppendLine(" SELECT C.* FROM TN_AB_B_MAGIC_TREE A ");
                strSql.AppendLine(" INNER JOIN TN_AB_B_MAGIC_RELATION B ON A.CN_S_CODE=B.CN_S_CODE ");
                strSql.AppendLine(" INNER JOIN " + tableName + " C ON B.CN_S_VALUE = C." + entity.joinNameTree + " ");
                strSql.AppendLine("WHERE 1=1 " + entity.loadWhere + treeWhere.ToString());

                //树形结构tableName重构
                tree_tbName = "( " + strSql.ToString() + " ) tb";
            }
            else
            {
                strSql.AppendLine("SELECT " + fileds + " FROM " + tableName + sqlWhere.ToString() + orderby);
            }

            //out返回类型复制
            strWhere = sqlWhere.ToString();
            orderBy = orderby;
            tbName = tree_tbName;

            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_name, string colmn_value, string colmn_key, string colmn_type)
        {
            string sqlString = string.Empty;

            //如果时间格式  单独处理
            if (colmn_type.ToLower() == "datetime")
            {
                string times = Convert.ToDateTime(colmn_value).AddDays(1).ToString("yyyy-MM-dd");
                sqlString = "AND ( " + colmn_name + " >='" + colmn_value + "' and  " + colmn_name + " <= '" + times + "')";
                return sqlString;
            }

            //判断关键字
            switch (colmn_key)
            {
                case "=":
                    sqlString = " AND " + colmn_name + "=" + GetValue(colmn_value, colmn_type);
                    break;
                case "!=":
                    sqlString = " AND " + colmn_name + " != " + GetValue(colmn_value, colmn_type);
                    break;
                case "like":
                    sqlString = " AND " + colmn_name + " like '%" + colmn_value + "%' ";
                    break;
                case "left like":
                    sqlString = " AND " + colmn_name + " like '" + colmn_value + "%' ";
                    break;
                case ">":
                    sqlString = " AND " + colmn_name + " > " + GetValue(colmn_value, colmn_type);
                    break;
                case ">=":
                    sqlString = " AND " + colmn_name + " >= " + GetValue(colmn_value, colmn_type);
                    break;
                case "<":
                    sqlString = " AND " + colmn_name + " < " + GetValue(colmn_value, colmn_type);
                    break;
                case "<=":
                    sqlString = " AND " + colmn_name + " <= " + GetValue(colmn_value, colmn_type);
                    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;

            //获得字段类型
            string type = colmn_type.ToLower();
            if (type == "string" || type == "diction" || type == "quote" || type == "varchar")
            {
                value = " '" + colmn_value + "' ";
            }
            else
            {
                value = colmn_value;
            }
            return value;
        }
        #endregion
    }
}