using HanHe.Utility.Data;
using HH.MData;
using HH.WMS.Entitys.Algorithm;
using HH.WMS.Entitys.Common;
using HH.WMS.Entitys.External;
using MongoDB.Driver.Builders;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.WMS.DAL.External
{
    public class TN_WM_B_OBJ_LOCATIONDAL : BaseDAL
    {
        #region 新增关联
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/03/24</history>
        public SqlExecuteResult Add(TN_WM_B_OBJ_LOCATIONEntity entity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
            strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
            strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
            DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
            DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
            DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
            DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
            return ExecuteCommand(cmd, trans);
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/03/24</history>
        public SqlExecuteResult AddList(List<TN_WM_B_OBJ_LOCATIONEntity> entityList, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
            strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
            strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");

            List<DbCommand> cmds = new List<DbCommand>();
            foreach (TN_WM_B_OBJ_LOCATIONEntity entity in entityList)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
                DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
                DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
                cmds.Add(cmd);
            }
            return ExecuteCommands(cmds, trans);
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/07/30</history>
        public SqlExecuteResult AddLocationList(List<TN_WM_B_OBJ_LOCATIONEntity> entityList, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
            strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
            strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");

            List<DbCommand> cmds = new List<DbCommand>();
            foreach (TN_WM_B_OBJ_LOCATIONEntity entity in entityList)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
                DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
                DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, entity.CN_T_OPERATE);
                cmds.Add(cmd);
            }
            return ExecuteCommands(cmds, trans);
        }
        #endregion

        #region 根据对象编码获取列表
        /// <summary>
        /// 根据对象编码获取
        /// </summary>
        /// <param name="objCode"></param>
        /// <returns></returns>
        public List<TN_WM_B_OBJ_LOCATIONEntity> GetListByObjCode(string obj, string objCode)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE AND CN_S_OBJ=:CN_S_OBJ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }
        #endregion

        /// <summary>
        /// 设置实体的各列
        /// <summary>
        private void SetEntity(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
            SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
            SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
            SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
            SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
            //   SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
            //   SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
        }
        private void SetEntityItem(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
            SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
            SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
            SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
            SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
            SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
            SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
            SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
        }
        private void SetEntityItemSxcc(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
            SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
            SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
            SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
            SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
            SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
            SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
            SetEntityUti(entity, "CN_S_VENDOR_NO", "CN_S_VENDOR_NO", reader);
            SetEntityUti(entity, "CN_S_VENDOR_NAME", "CN_S_VENDOR_NAME", reader);
            SetEntityUti(entity, "CN_S_FROM_NO", "CN_S_FROM_NO", reader);
            SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
        }
        private void SetEntityObj(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
            SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
            SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
            SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
            SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
        }

        #region 根据对象编码获取
        /// <summary>
        /// 根据对象编码获取
        /// </summary>
        /// <param name="objCode"></param>
        /// <returns></returns>
        public TN_WM_B_OBJ_LOCATIONEntity GetEntityByObjCode(string obj, string objCode)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE AND CN_S_OBJ=:CN_S_OBJ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
            return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }
        #endregion

        public DataTable GetLocationContent(string areaCode, string location)
        {
            string sql = @" select * from TN_WM_B_OBJ_LOCATION where 1=1 ";
            if (!string.IsNullOrEmpty(areaCode))
                sql += " and CN_S_STOCK_AREA ='" + areaCode + "'";
            if (!string.IsNullOrEmpty(location))
                sql += " and CN_S_LOCATION_CODE ='" + location + "'";
            sql += " order by CN_S_LOCATION_CODE,CN_N_INDEX ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }

        #region 根据对象、 多个库区获取货位
        /// <summary>
        /// 根据对象、 多个库区获取货位
        /// </summary>
        /// <param name="outOrder"></param>
        /// <param name="area"></param>
        /// <returns></returns>
        /// <history>[HanHe(zh)] CREATED 2018/05/9</history>
        public string GetLocationCode(string objCode, string obj)
        {
            string sql = @"SELECT CN_S_LOCATION_CODE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ=:CN_S_OBJ AND CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            var entity = DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd,
                (e, r) =>
                {
                    SetEntityUti(e, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", r);
                });
            if (entity != null)
                return entity.CN_S_LOCATION_CODE;
            return string.Empty;
        }
        #endregion

        #region 批量清空货位
        /// <summary>
        /// 批量清空货位
        /// </summary>
        /// <param name="pType"></param>
        /// <param name="pCodes"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult Clean(string pType, string[] pCodes, DbTransaction trans)
        {
            List<DbCommand> cmds = new List<DbCommand>();
            if (!string.IsNullOrEmpty(pType) && pCodes.Length > 0)
            {
                string sql = string.Empty;
                switch (pType)
                {
                    case "托盘":
                        foreach (string m in pCodes)
                        {
                            sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ='托盘' AND CN_S_OBJ_CODE='" + m + "'";
                            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                            cmds.Add(cmd);
                        }
                        break;
                    case "货位":
                        foreach (string m in pCodes)
                        {
                            sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE='" + m + "'";
                            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                            cmds.Add(cmd);
                        }
                        break;
                }
                return ExecuteCommands(cmds, trans);
            }
            return new SqlExecuteResult();
        }
        #endregion

        #region 清空货位、托盘
        /// <summary>
        /// 清空货位、托盘
        /// </summary>
        /// <param name="pType"></param>
        /// <param name="pCode"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HanHe(zh)] CREATED 2018/5/4</history>
        public SqlExecuteResult Clean(string pType, string pCode, DbTransaction trans)
        {
            if (!string.IsNullOrEmpty(pType) && !string.IsNullOrEmpty(pCode))
            {
                string sql = string.Empty;
                pCode = pCode.Replace("\'", "");
                switch (pType)
                {
                    case "托盘":
                        sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ='托盘' AND CN_S_OBJ_CODE='" + pCode + "'";
                        break;
                    case "货位":
                        sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE='" + pCode + "'";
                        break;
                }
                if (!string.IsNullOrEmpty(pCode))
                {
                    DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                    return ExecuteCommand(cmd, trans);
                }
            }
            return new SqlExecuteResult();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="locaion"></param>
        /// <param name="trayCode"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult UnBanding(string locaion, string trayCode, DbTransaction trans)
        {
            string sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            this.DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, locaion);
            this.DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, trayCode);
            return ExecuteCommand(cmd, trans);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public SqlExecuteResult DeleteObj(string sqlwhere, DbTransaction trans)
        {
            string sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE 1=1 {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        #region 批量新增
        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="objLocationRels"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        ///<History>[Hanhe(DBS)] created 2018-5-8</History>
        public SqlExecuteResult Add(List<TN_WM_B_OBJ_LOCATIONEntity> objLocationRels, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
            strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
            strSql.Append("values ( @CN_GUID,@CN_S_OBJ,@CN_S_OBJ_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_T_OPERATE)");

            List<DbCommand> cmds = new List<DbCommand>();
            foreach (TN_WM_B_OBJ_LOCATIONEntity entity in objLocationRels)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", DbType.String, entity.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ", DbType.String, entity.CN_S_OBJ);
                DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", DbType.String, entity.CN_S_OBJ_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", DbType.String, entity.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", DbType.String, entity.CN_S_STOCK_AREA);
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", DbType.String, entity.CN_S_LOCATION_CODE);
                DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
                DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
                cmds.Add(cmd);
            }
            return ExecuteCommands(cmds, trans);
        }
        #endregion

        #region 判断货位是否含有关联
        /// <summary>
        /// 判断货位是否含有关联
        /// </summary>
        /// <param name="locationCode"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public List<TN_WM_B_OBJ_LOCATIONEntity> GetListByLocationCode(string locationCode, DbTransaction trans, string objCode = "")
        {
            List<TN_WM_B_OBJ_LOCATIONEntity> list = new List<TN_WM_B_OBJ_LOCATIONEntity>();
            StringBuilder strSql = new StringBuilder();

            if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("MYSQL"))
            {
                strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,IFNULL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
            }
            if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("MSSQL"))
            {
                strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,ISNULL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
            }
            if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
            {
                strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,NVL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
            }

            if (!string.IsNullOrEmpty(locationCode))
            {
                strSql.Append(" WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");
            }
            else
            {
                return list;
            }
            if (!string.IsNullOrEmpty(objCode))
            {
                strSql.Append(" AND CN_S_OBJ_CODE!='" + objCode + "' ");
            }
            strSql.Append(" ORDER BY CN_N_INDEX DESC ");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());

            list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
            return list;

        }
        #endregion

        public TN_WM_B_OBJ_LOCATIONEntity GetModel(string location, string objCode)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }

        public List<TN_WM_B_OBJ_LOCATIONEntity> GetModelByLocation(string location)
        {
            string sql = @" SELECT 
                                A.*,B.CN_S_ITEM_CODE,CN_S_ITEM_NAME,B.CN_S_ITEM_STATE 
                            FROM 
                                TN_WM_B_OBJ_LOCATION A LEFT JOIN [dbo].[tn_wm_b_tray_item_rel] B 
                            ON 
                                A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
                            WHERE 
                                CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY A.CN_T_OPERATE ASC ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntityItem);
        }


        public List<TN_WM_B_OBJ_LOCATIONEntity> GetModelByLocationSxcc(string location)
        {
            string sql = @" SELECT 
                                A.*,B.CN_S_ITEM_CODE,CN_S_ITEM_NAME,B.CN_S_VENDOR_NO,B.CN_S_VENDOR_NAME,B.CN_S_FIGURE_NO,B.CN_S_FROM_NO  
                            FROM 
                                TN_WM_B_OBJ_LOCATION A LEFT JOIN [dbo].[tn_wm_b_tray_item_rel] B 
                            ON 
                                A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
                            WHERE 
                                CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY A.CN_T_OPERATE ASC ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntityItemSxcc);
        }


        public List<TN_WM_B_OBJ_LOCATIONEntity> SxccGetModel(string qybw, string kh, string qzsd, string xh)
        {
            string sql = @" SELECT 
                               DISTINCT  A.*
                            FROM 
                                TN_WM_B_OBJ_LOCATION A INNER JOIN [dbo].[tn_wm_b_tray_item_rel] B 
                            ON 
                                A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
                            WHERE  1=1 ";
            if (!string.IsNullOrEmpty(qybw))
                sql += " and B.CN_S_VENDOR_NO like '%" + qybw + "%'";
            if (!string.IsNullOrEmpty(kh))
                sql += " and B.CN_S_VENDOR_NAME  like '%" + kh + "%'";
            if (!string.IsNullOrEmpty(qzsd))
                sql += " and B.CN_S_FIGURE_NO like '%" + qzsd + "%'";
            if (!string.IsNullOrEmpty(xh))
                sql += " and B.CN_S_FROM_NO  like '%" + xh + "%'";


            sql += " ORDER BY A.CN_S_LOCATION_CODE,A.CN_N_INDEX ASC ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }

        #region 更新对象位置
        /// <summary>
        /// 更新对象位置
        /// </summary>
        /// <param name="objCode"></param>
        /// <param name="area"></param>
        /// <param name="location"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult UpdateObjPosition(string objCode, string area, string location, DbTransaction trans)
        {
            string sql = @"UPDATE TN_WM_B_OBJ_LOCATION SET CN_S_STOCK_AREA=:CN_S_STOCK_AREA , CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE  WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, area);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        #region 获取对象列表
        public DataTable GetList(string objCode)
        {
            string sql = "select * from TN_WM_B_OBJ_LOCATION where CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);

            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }


        public DataTable GetList(string obj, string code)
        {
            string sql = "select * from TN_WM_B_OBJ_LOCATION CN_S_OBJ=:CN_S_OBJ and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, code);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }

        public List<TN_WM_B_OBJ_LOCATIONEntity> GetExtendList(string objCode)
        {
            string sql = "select * from TN_WM_B_OBJ_LOCATION where CN_S_OBJ_CODE in(" + objCode + ")";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            List<TN_WM_B_OBJ_LOCATIONEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
            return list;
        }

        public List<TN_WM_B_OBJ_LOCATIONEntity> GetExtendListByArea(string areaCode)
        {
            string sql = "select a.* from TN_WM_B_OBJ_LOCATION a inner join tn_wm_b_location_ext b on a.cn_s_location_code = b.cn_s_location_code where a.CN_S_STOCK_AREA = '" + areaCode + "' AND a.CN_S_OBJ = '托盘' and b.cn_s_location_state = '正常'  ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            List<TN_WM_B_OBJ_LOCATIONEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
            return list;
        }
        #endregion

        #region 根据对象、 库区获取货位
        /// <summary>
        /// 根据对象、 库区获取货位
        /// </summary>
        /// <param name="outOrder"></param>
        /// <param name="area"></param>
        /// <returns></returns>
        /// <history>[HanHe(zh)] CREATED 2018/05/9</history>
        public string GetLocationCode(string objCode, string area, string obj)
        {
            string sql = @"SELECT CN_S_LOCATION_CODE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ=:CN_S_OBJ AND CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
            if (!string.IsNullOrEmpty(area))
                sql += " AND CN_S_STOCK_AREA='" + area + "' ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
            DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
            var entity = DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd,
                (e, r) =>
                {
                    SetEntityUti(e, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", r);
                });
            if (entity != null)
                return entity.CN_S_LOCATION_CODE;
            return string.Empty;
        }
        #endregion

        #region 根据仓库和库区编码获得库区结构
        /// <summary>
        /// 根据仓库和库区编码获得库区结构
        /// </summary>
        /// <param name="stockCode">仓库编号</param>
        /// <param name="areaCode">库区编号</param>
        /// <returns></returns>
        ///  <history>[HanHe(LT)] CREATED 2018/11/30</history>
        public List<AutoBomStockStructreEntity> GetStructList(string stockCode, string areaCode)
        {
            var query = Query.And(Query.EQ("CN_S_STOCK_CODE", stockCode), Query.EQ("CN_S_AREA_CODE", areaCode));
            //仓库结构
            List<AutoBomStockStructreEntity> lstStruct = MongoDBSingleton.Instance.Find<AutoBomStockStructreEntity>(query, "TN_AB_B_STOCK_STRUCTURE");

            return lstStruct;
        }
        #endregion

        public List<TN_WM_B_OBJ_LOCATIONEntity> GetEntityList(string location)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY CN_T_OPERATE ASC";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }
        public TN_WM_B_OBJ_LOCATIONEntity GetEntity(string location)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY CN_T_OPERATE ASC";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
            return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }
        public List<TN_WM_B_OBJ_LOCATIONEntity> GetObjLocationList(string locations)
        {
            string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE in(" + locations + ") ";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
        }
    }
}