using HanHe.Utility.Data;
using HH.WMS.Common.MagicModel;
using HH.WMS.Entitys.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.WMS.DAL.Common
{
    /// <summary>
    /// 建模DAL层
    /// </summary>
    /// <history>HanHe [FJ] CREATE(2017/09/15)</history>
    public class MagicDAL : BaseDAL
    {

        #region 分页获得datatable
        /// <summary>
        /// 分页获得datatable
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="SWhere"></param>
        /// <param name="OrderBy"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalPages"></param>
        /// <param name="totalRows"></param>
        /// <returns></returns>
        public DataTable GetData(string tabName, string SWhere, string OrderBy, int pageSize, int pageIndex, out int totalPages, out int totalRows)
        {
            DataTable dt = new DataTable();
            try
            {
                DbCommand cmdall = DataAccess.GetStoredProcCommand("prc_query");

                this.DataAccess.AddInParameter(cmdall, "TableName", ComDbType.STRING, tabName);
                this.DataAccess.AddInParameter(cmdall, "WhereStr", ComDbType.STRING, SWhere);
                this.DataAccess.AddInParameter(cmdall, "OrderByStr", ComDbType.STRING, OrderBy);
                this.DataAccess.AddInParameter(cmdall, "PageSize", ComDbType.INT64, pageSize);
                this.DataAccess.AddInParameter(cmdall, "PageIndex", ComDbType.INT64, pageIndex);
                this.DataAccess.AddOutParameter(cmdall, "TotalPage", ComDbType.INT64, 8);
                this.DataAccess.AddOutParameter(cmdall, "TotalRecord", ComDbType.INT64, 8);

                //如果是oracle  增加特性
                if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
                {
                    //处理游标类型 因DbType无游标类型,游标类型单独处理
                    ComDbType.AddOrcOutParameter(cmdall, "v_cur", OracleType.Cursor);
                }

                dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmdall);

                totalPages = Convert.ToInt32(this.DataAccess.GetParameterValue(cmdall, "TotalPage"));
                totalRows = Convert.ToInt32(this.DataAccess.GetParameterValue(cmdall, "TotalRecord"));
                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region 不分页查询
        /// <summary>
        /// 不分页查询
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable GetData(string strSql)
        {
            DataTable dt = new DataTable();
            try
            {
                DbCommand cmdall = DataAccess.GetSqlStringCommand(strSql);
                dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmdall);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HanHe(fj)] create 2017/7/22</history>
        public SqlExecuteResult Add(MagicAddEntity entity, MagicDefaultEntity defaultEntity, DbTransaction trans)
        {

            List<Column> lstColumn = GetLstColumn(entity.lstColumn, defaultEntity, "Add");

            if (lstColumn == null || lstColumn.Count == 0 || string.IsNullOrEmpty(entity.TableName))
            {
                return new SqlExecuteResult { Success = false };
            }
            //SQL字符串
            StringBuilder strSql = new StringBuilder();

            #region 拼接SQL字符串
            StringBuilder column_str = new StringBuilder();
            StringBuilder column_val = new StringBuilder();

            for (int i = 0; i < lstColumn.Count; i++)
            {
                if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
                {
                    column_str.Append(lstColumn[i].name + ",");
                    column_val.Append(":" + lstColumn[i].name + ",");
                }
            }

            strSql.Append("insert into " + entity.TableName + " (");
            strSql.AppendLine(column_str.ToString().TrimEnd(','));
            strSql.AppendLine(" )");
            strSql.AppendLine(" values (");
            strSql.AppendLine(column_val.ToString().TrimEnd(','));
            strSql.AppendLine(" ) ");
            #endregion

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());

            for (int i = 0; i < lstColumn.Count; i++)
            {
                if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
                {
                    //获得dbtype类型
                    DbType comType = GetDbType(lstColumn[i].type);
                    if (comType != ComDbType.STRING)
                    {

                    }
                    DataAccess.AddInParameter(cmd, lstColumn[i].name, comType, lstColumn[i].value);
                }
            }
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult Update(MagicAddEntity entity, MagicDefaultEntity defaultEntity, DbTransaction trans)
        {
            List<Column> lstColumn = GetLstColumn(entity.lstColumn, defaultEntity, "Edit");
            if (lstColumn == null || lstColumn.Count == 0 || string.IsNullOrEmpty(entity.TableName))
            {
                return new SqlExecuteResult { Success = false };
            }

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("update " + entity.TableName + " set ");
            for (int i = 0; i < lstColumn.Count; i++)
            {
                if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
                {
                    strSql.AppendLine(lstColumn[i].name + "=:" + lstColumn[i].name);
                    if (i != (lstColumn.Count - 1))
                    {
                        strSql.Append(",");
                    }
                }
            }

            strSql.AppendLine(" where CN_GUID=:CN_GUID");

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());

            for (int i = 0; i < lstColumn.Count; i++)
            {
                if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
                {
                    //获得dbtype类型
                    DbType comType = GetDbType(lstColumn[i].type);
                    DataAccess.AddInParameter(cmd, lstColumn[i].name, comType, lstColumn[i].value);
                }
            }
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        #region 获得集合
        /// <summary>
        /// 获得集合
        /// </summary>
        /// <param name="entity_lstColumn">客户端传递的用户操作实体</param>
        /// <param name="defaultEntity">固有集合</param>
        /// <param name="handType">操作类型 Add  Edit</param>
        /// <returns></returns>
        private static List<Column> GetLstColumn(List<Column> entity_lstColumn, MagicDefaultEntity defaultEntity, string handType)
        {
            List<Column> lstColumn = new List<Column>();

            //获得用户自定义字段属性
            List<Column> lstOut = entity_lstColumn.Where(o => o.value != null).ToList();

            //获得固有字段属性
            List<Column> lstIn = GetProperties<MagicDefaultEntity>(defaultEntity, handType);

            //取并集 固有字段以后台添加的为准，前台如果有则去除
            lstColumn = lstIn.Union(lstOut).ToList();
            return lstColumn;
        }
        #endregion

        #region 获得实体数据
        /// <summary>
        /// 获得实体数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fileds"></param>
        /// <param name="guid"></param>
        /// <returns></returns>
        public DataTable GetModel(string tableName, string fileds, string guid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT " + fileds + " FROM " + tableName + " WHERE 1=1 AND CN_GUID=:CN_GUID");

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());

            DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, guid);

            DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);

            return dt;
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="guid"></param>
        /// <returns></returns>
        public SqlExecuteResult Delete(string tableName, string guids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("DELETE FROM " + tableName + " WHERE 1=1 AND CN_GUID in ('" + guids + "')");

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());

            return ExecuteCommand(cmd, null);
        }
        #endregion

        #region 获得DBTYPE类型
        /// <summary>
        /// 获得DBTYPE类型
        /// </summary>
        /// <param name="typeStr"></param>
        /// <returns></returns>
        private DbType GetDbType(string typeStr)
        {
            //默认
            DbType comType = ComDbType.STRING;
            switch (typeStr.ToLower())
            {
                case "string":
                    comType = ComDbType.STRING;
                    break;
                case "varchar":
                    comType = ComDbType.STRING;
                    break;
                case "char":
                    comType = ComDbType.STRING;
                    break;
                case "diction":
                    comType = ComDbType.STRING;
                    break;
                case "quete":
                    comType = ComDbType.STRING;
                    break;
                case "int":
                    comType = ComDbType.INT;
                    break;
                case "datetime":
                    comType = ComDbType.DATE;
                    break;
                case "float":
                    comType = ComDbType.FLOAT;
                    break;
                case "decimal":
                    comType = ComDbType.DECIMAL;
                    break;
                case "double":
                    comType = ComDbType.DOUBLE;
                    break;
            }
            return comType;
        }
        #endregion

        #region 获得固有属性信息
        /// <summary>
        /// 获得固有属性信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="handType">新增或者修改</param>
        /// <returns></returns>
        public static List<Column> GetProperties<T>(T t, string handType)
        {
            List<Column> lstColumn = new List<Column>();

            string tStr = string.Empty;
            if (t == null)
            {
                return lstColumn;
            }

            //将所有属性转换成一个集合供循环读取
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            //如果类中没有属性则返回
            if (properties.Length <= 0)
            {
                return lstColumn;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                //如果类中的属性字段为值类型
                if ((item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String")))
                {
                    string name = item.Name;
                    object value = item.GetValue(t, null);
                    string type = item.PropertyType.Name;

                    //如果修改
                    if (handType.Equals("Edit"))
                    {
                        object[] objArray = item.GetCustomAttributes(false);
                        if (objArray.Length > 0)
                        {
                            if ((objArray[0] as HH.WMS.Common.MagicModel.Property).Value == handType)
                            {
                                Column c = new Column();
                                c.name = name;
                                c.value = value == null ? "" : value.ToString();
                                c.type = type.ToUpper();
                                lstColumn.Add(c);
                            }
                        }
                    }

                    //如果是新增
                    else if (handType.Equals("Add"))
                    {
                        Column c = new Column();
                        c.name = name;
                        c.value = value == null ? "" : value.ToString();
                        c.type = type.ToUpper();
                        lstColumn.Add(c);
                    }
                }
            }
            return lstColumn;
        }
        #endregion

        #region 版本控制的  取状态和版本号
        /// <summary>
        /// 版本控制的  取状态和版本号
        /// </summary>
        /// <param name="CN_GUID"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public VersionControlEntity GetStatusVersioin(string CN_GUID, string TableName)
        {
            string strSql = "SELECT CN_N_VERSION,CN_S_STATUS FROM " + TableName + " WHERE CN_GUID=:CN_GUID";
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
            DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, CN_GUID);
            VersionControlEntity entity = DataAccessExtensive.ExecuteSingleEntity<VersionControlEntity>(this.DataAccess, cmd, SetVersionEntity);
            return entity;
        }
        private void SetVersionEntity(VersionControlEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_N_VERSION", "CN_N_VERSION", reader);
            SetEntityUti(entity, "CN_S_STATUS", "CN_S_STATUS", reader);
        }
        #endregion

        #region 根据GUID更新状态
        /// <summary>
        /// 根据GUID更新状态
        /// </summary>
        /// <param name="CN_GUID"></param>
        /// <param name="CN_S_STATUS"></param>
        /// <param name="tableName"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult UpdateStatus(string CN_GUID, string CN_S_STATUS, string tableName, bool update_version, DbTransaction trans)
        {
            string version = "";
            if (update_version)
            {
                version = " ,CN_C_SCRAP_VERSION='N'";
            }
            string strSql = "UPDATE " + tableName + " SET CN_S_STATUS=:CN_S_STATUS " + version + " WHERE CN_GUID in ('" + CN_GUID + "')";
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
            DataAccess.AddInParameter(cmd, "CN_S_STATUS", ComDbType.STRING, CN_S_STATUS);
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        #region 如果需要过程控制，则根据编码取最大版本的GUID
        /// <summary>
        /// 如果需要过程控制，则根据编码取最大版本号对应的GUID
        /// </summary>
        /// <param name="code_name">编码名称 例如：ITEM目前是CN_S_ITEM_CODE，仓库为CN_S_STOCK_CODE</param>
        /// <param name="code_vale">编码名称对应的值</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public string GetGuid(string code_name, string code_value, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("select CN_GUID from " + tableName + " ");
            strSql.AppendLine(" where " + code_name + "='" + code_value + "' AND CN_N_VERSION=");
            strSql.AppendLine("(SELECT MAX(CN_N_VERSION) FROM tn_ab_item_proc WHERE " + code_name + "='" + code_value + "' )");

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            object obj = DataAccess.ExecuteScalar(cmd);
            return obj == null ? "" : obj.ToString();
        }
        #endregion


        #region 如果需要过程控制，则根据编码取最小版本的GUID
        /// <summary>
        /// 如果需要过程控制，则根据编码取最小版本的GUID
        /// </summary>
        /// <param name="code_name">编码名称 例如：ITEM目前是CN_S_ITEM_CODE，仓库为CN_S_STOCK_CODE</param>
        /// <param name="code_vale">编码名称对应的值</param>
        /// <param name="tableName">表名称</param>
        public string GetMinGuid(string code_name, string code_value, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("select CN_GUID from " + tableName + " ");
            strSql.AppendLine(" where " + code_name + "='" + code_value + "' AND CN_N_VERSION=");
            strSql.AppendLine("(SELECT MIN(CN_N_VERSION) FROM tn_ab_item_proc WHERE " + code_name + "='" + code_value + "' )");

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            object obj = DataAccess.ExecuteScalar(cmd);
            return obj == null ? "" : obj.ToString();
        }
        #endregion
    }
}