using HH.WMS.Entitys.Basic;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using HH.WMS.Entitys.Common;
using HH.WMS.Common;
using HH.MData;
using System.Data.OracleClient;
using HH.WMS.Entitys.Autobom;
using HH.WMS.Entitys;

namespace HH.WMS.DAL.Basic
{
    public class TN_WM_LOCATION_EXTDAL : DapperBaseDAL
    {
        #region UPDATE

        /// <summary>
        /// 更新贮存状态和货位状态
        /// </summary>
        /// <param name="locationCode">货位编号</param>
        /// <param name="state">货位状态</param>
        /// <param name="useState">贮存状态</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] created 2018-5-18 </History>
        public SqlExecuteResult UpdateStateAndTaskNo(string taskNo, string locationCode, string state, string useState, DbTransaction trans, string currState = "")
        {
            try
            {
                string strSql = @"UPDATE  tn_wm_b_location_ext SET ";
                string strWhere = "";
                if (!string.IsNullOrEmpty(state))
                {
                    strWhere += " CN_S_LOCATION_STATE='" + state + "'";
                    if (state.Equals(Constants.Location_State_Normal))
                    {
                        strWhere += " ,CN_S_TASK_NO='' ";
                    }
                    else
                    {
                        strWhere += " ,CN_S_TASK_NO='" + taskNo + "' ";
                    }

                }
                if (!string.IsNullOrEmpty(useState))
                {
                    if (strWhere.Length != 0)
                        strWhere += " ,CN_S_USE_STATE='" + useState + "'";
                    else
                        strWhere += " CN_S_USE_STATE='" + useState + "'";
                }
                strSql += strWhere + " where CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ";

                if (!string.IsNullOrEmpty(currState))
                    strSql += " and CN_S_LOCATION_STATE='" + currState + "'";

                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", DbType.String, locationCode);
                return ExecuteCommand(cmd, trans);
            }
            catch (Exception ex)
            {
                return new SqlExecuteResult();
            }
        }

        #endregion


        #region 获得可用的货位扩展信息

        /// <summary>
        /// 货位数量超过一定长度则分步骤获取
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<TN_WM_LOCATION_EXTEntity> GetLocationExtListBy(string stockCode, List<string> locationCodes, string opType)
        {
            string strSql = @"SELECT CN_S_STOCK_CODE,CN_S_ROW,CN_S_LOCATION_CODE,CN_S_LOCATION_STATE,CN_S_USE_STATE,
                              CN_S_BEF_FAULT_STATE,CN_N_DELIVERY_BEAT 
                              FROM tn_wm_b_location_ext WHERE 1=1 AND CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ";

            if (!string.IsNullOrEmpty(stockCode))
            {
                strSql += " AND CN_S_STOCK_CODE='" + stockCode + "' ";
            }
            if (locationCodes != null && locationCodes.Count > 0)
            {
                if (locationCodes.Count == 1)
                {
                    strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                }
                else
                {
                    string locations = string.Join(",", locationCodes).Replace(",", "','");
                    strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                }
            }
            switch (opType)
            {
                case "入库":
                    strSql += " AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "'";
                    break;
                case "入流离库":
                    strSql += " AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Empty + "'";
                    break;
                case "出库":
                    strSql += " AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Full + "'";
                    break;
                default:
                    break;
            }
            List<TN_WM_LOCATION_EXTEntity> list = ExecuteQuery<TN_WM_LOCATION_EXTEntity>(strSql.ToString());
            return list;
        }
        /// <summary>
        /// 获取可用货位的集合，为提高效率，该方法只返回货位编码字段
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<TN_WM_LOCATIONCODE_EXT_Entity> GetLocationCodeListBy(string stockCode, string areaCode, List<string> locationCodes, string areaType)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATIONCODE_EXT_Entity> list = new List<TN_WM_LOCATIONCODE_EXT_Entity>();

            strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE
                              FROM tn_wm_b_location_ext WHERE 1=1 AND CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ";

            if (!string.IsNullOrEmpty(stockCode))
            {
                strSql += " AND CN_S_STOCK_CODE='" + stockCode + "' ";
            }
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql += " AND CN_S_AREA_CODE='" + areaCode + "' ";
            }
            if (locationCodes != null && locationCodes.Count > 0)
            {
                if (locationCodes.Count == 1)
                {
                    strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                }
                else
                {
                    string locations = string.Join(",", locationCodes).Replace(",", "','");
                    strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                }
            }
            switch (areaType)
            {
                case "平库":
                    strSql += " AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "'";
                    break;
                case "立库":
                    strSql += " AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "'";
                    break;
                case "流离式":
                    strSql += " AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Empty + "')";
                    break;
                default:
                    break;
            }
            Log.AlgorInfo("InAssign-GetLocationCodeExtListBy", "获取货位扩展表中可用货位的sql语句：" + strSql);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
            list = ExecuteQuery<TN_WM_LOCATIONCODE_EXT_Entity>(strSql.ToString());

            return list;

        }

        public List<TN_WM_LOCATIONCODE_EXT_Entity> GetLocationCodeListByLocationCode(List<string> locationCodes)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATIONCODE_EXT_Entity> list = new List<TN_WM_LOCATIONCODE_EXT_Entity>();

            strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE,CN_S_ROW,CN_S_COL
                              FROM tn_wm_b_location_ext WHERE 1=1  ";

       
            if (locationCodes != null && locationCodes.Count > 0)
            {
                if (locationCodes.Count == 1)
                {
                    strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                }
                else
                {
                    string locations = string.Join(",", locationCodes).Replace(",", "','");
                    strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                }
            }
       
            Log.AlgorInfo("InAssign-GetLocationCodeExtListBy", "获取货位扩展表中可用货位的sql语句：" + strSql);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
            list = ExecuteQuery<TN_WM_LOCATIONCODE_EXT_Entity>(strSql.ToString());

            return list;

        }
        #region 获取库区中包含某物料的货位
        /// <summary>
        /// 获取出入库锁定的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetLocationByItemCode(string itemCode, string itemState, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT CN_S_LOCATION_CODE,CN_S_ROW,CN_S_COL,CN_S_FLOOR   FROM  ");
            strSql.AppendLine("  tn_wm_b_location_ext  ");
            strSql.AppendLine(" WHERE  CN_S_LOCATION_STATE='正常' AND CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND CN_S_AREA_CODE = '" + areaCode + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());

            return list;

        }
        #endregion
        #region 获取库区中包含某物料的货位
        /// <summary>
        /// 获取出入库锁定的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetLocationByInLockYM(string itemCode, string itemState, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT CN_S_LOCATION_CODE,CN_S_ROW,CN_S_COL,CN_S_FLOOR   FROM  ");
            strSql.AppendLine("  tn_wm_b_location_ext  ");
            strSql.AppendLine(" WHERE  CN_S_LOCATION_STATE = '预入库锁定' ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND CN_S_AREA_CODE = '" + areaCode + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());

            return list;

        }
        #endregion
        /// <summary>
        /// 获取可用货位的集合，为提高效率，该方法只返回货位编码字段
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<TN_WM_LOCATION_EXTEntity> GetLocationCodeExtListBy(string stockCode, string areaCode, List<string> locationCodes, string opType)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATION_EXTEntity> list = new List<TN_WM_LOCATION_EXTEntity>();
            try
            {
                strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE
                              FROM tn_wm_b_location_ext WHERE  CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ";

                if (!string.IsNullOrEmpty(stockCode))
                {
                    strSql += " AND CN_S_STOCK_CODE='" + stockCode + "' ";
                }
                if (!string.IsNullOrEmpty(areaCode))
                {
                    strSql += " AND CN_S_AREA_CODE='" + areaCode + "' ";
                }
                if (locationCodes != null && locationCodes.Count > 0)
                {
                    if (locationCodes.Count == 1)
                    {
                        strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                    }
                    else
                    {
                        string locations = string.Join(",", locationCodes).Replace(",", "','");
                        strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                    }
                }
                switch (opType)
                {
                    case "入库":
                        strSql += " AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "'";
                        break;
                    case "入流离库":
                        strSql += " AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Empty + "')";
                        break;
                    case "出库":
                        strSql += " AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Full + "')";
                        break;
                    default:
                        break;
                }
                Log.AlgorInfo("GetTrueLocationCodes", strSql);
                list = ExecuteQuery<TN_WM_LOCATION_EXTEntity>(strSql.ToString());
            }
            catch (Exception ex)
            {

            }
            return list;

        }

        public List<TN_WM_LOCATION_EXTEntity> GetLocationCreateTime(List<string> locationCodes)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATION_EXTEntity> list = new List<TN_WM_LOCATION_EXTEntity>();
            try
            {
                strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE,CN_T_CREATE
                              FROM tn_wm_b_location_ext WHERE  CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ";

   
                if (locationCodes != null && locationCodes.Count > 0)
                {
                    if (locationCodes.Count == 1)
                    {
                        strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                    }
                    else
                    {
                        string locations = string.Join(",", locationCodes).Replace(",", "','");
                        strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                    }
                }
                Log.AlgorInfo("GetLocationCreateTime", strSql);
                list = ExecuteQuery<TN_WM_LOCATION_EXTEntity>(strSql.ToString());
            }
            catch (Exception ex)
            {

            }
            return list;

        }

        #endregion

        #region 出入库计算货位时修改货位状态
        /// <summary>
        /// 出入库计算货位时修改货位状态
        /// </summary>
        /// <param name="locationCode"></param>
        /// <param name="state"></param>
        /// <param name="opType"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult UpdateLocationState(string locationCode, string state, string opType, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("UPDATE tn_wm_b_location_ext SET CN_S_LOCATION_STATE=:CN_S_LOCATION_STATE ");
            strSql.AppendLine("WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ");
            switch (opType)
            {
                case "入库":
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ");
                    strSql.AppendLine(" AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "' ");
                    break;
                case "入库回滚":
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + Constants.Location_State_InLock + "' ");
                    strSql.AppendLine(" AND CN_S_USE_STATE = '" + Constants.Use_State_Empty + "' ");
                    break;
                case "补料":
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ");
                    strSql.AppendLine(" AND CN_S_USE_STATE = '" + Constants.Use_State_Full + "' ");
                    break;
                case "补料回滚":
                    strSql.AppendLine("  ");
                    break;
                case "出库":
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + Constants.Location_State_Normal + "' ");
                    strSql.AppendLine(" AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Full + "') ");
                    break;
                case "出库回滚":
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + Constants.Location_State_OutLock + "' ");
                    strSql.AppendLine(" AND (CN_S_USE_STATE='" + Constants.Use_State_NoFull + "' or CN_S_USE_STATE='" + Constants.Use_State_Full + "') ");
                    break;
                default:
                    break;
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, locationCode);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_STATE", ComDbType.STRING, state);
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        /// <summary>
        /// 根据巷道获取巷道内的货位状态
        /// </summary>
        /// <param name="stockCode">仓库号</param>
        /// <param name="areaCode">库区号</param>
        /// <param name="roadway">巷道号</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] created 2018/12/03</History>
        public DataTable ViewState(string stockCode, string areaCode, string roadway)
        {
            string sql = @"SELECT CN_S_LOCATION_CODE,CN_S_FLOOR,CN_S_COL,CN_S_ROW,
                            VIEW_STATE = case CN_S_LOCATION_STATE
	                            when '正常' then 
		                            case CN_S_USE_STATE
			                            when '满' then '满'
			                            when '空' then '空'
			                            when '不满' then '不满'
		                            end
	                            else CN_S_LOCATION_STATE 
                            end
                            FROM tn_wm_b_location_ext WHERE CN_S_STOCK_CODE=@CN_S_STOCK_CODE 
            and CN_S_AREA_CODE=@CN_S_AREA_CODE ";

            if (!string.IsNullOrEmpty(roadway))
            {
                sql += "and CN_S_ROADWAY in('" + roadway.Replace(",", "','") + "')";
            }

            return ExecuteDataTable(sql, new
            {
                CN_S_STOCK_CODE = stockCode,
                CN_S_AREA_CODE = areaCode,
            });
        }

        public DataTable ViewStateByAreaCode(string stockCode, string areaCodes, string roadway)
        {
            string sql = string.Format(@"SELECT CN_S_LOCATION_CODE,CN_S_FLOOR,CN_S_COL,CN_S_ROW,
                            VIEW_STATE = case CN_S_LOCATION_STATE
	                            when '正常' then 
		                            case CN_S_USE_STATE
			                            when '满' then '满'
			                            when '空' then '空'
			                            when '不满' then '不满'
		                            end
	                            else CN_S_LOCATION_STATE 
                            end
                            FROM tn_wm_b_location_ext WHERE CN_S_STOCK_CODE='{0}'
            and CN_S_AREA_CODE IN ('{1}') ", stockCode, areaCodes.Replace(",", "','"));

            if (!string.IsNullOrEmpty(roadway))
            {
                sql += "and CN_S_ROADWAY in('" + roadway.Replace(",", "','") + "')";
            }
            
            return ExecuteDataTable(sql);
        }

        public List<TN_WM_LOCATION_EXTEntity> GetModel(string strWhere)
        {
            string sql = @" SELECT * FROM TN_WM_B_LOCATION_EXT " + strWhere;
            return ExecuteQuery<TN_WM_LOCATION_EXTEntity>(sql);
        }
        public List<TN_WM_LOCATION_EXTEntity> GetModelByRow(string strRow,string strArea)
        {
            string sql = @" SELECT * FROM TN_WM_B_LOCATION_EXT WHERE CN_S_ROW = '" + strRow +"'" + " AND CN_S_AREA_CODE = '"+ strArea +"' ";
            return ExecuteQuery<TN_WM_LOCATION_EXTEntity>(sql);
        }
        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CN_GUID"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult Delete(string Where, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            string sql = @" delete  from tn_wm_b_location_ext " + Where;
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return ExecuteCommand(cmd, trans);
        }
        #endregion

        public SqlExecuteResult UpdateSync(TN_WM_LOCATION_EXTEntity entity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" UPDATE tn_wm_b_location_ext SET ");
            strSql.Append(" CN_S_AREA_CODE = @CN_S_AREA_CODE,");
            if (!string.IsNullOrEmpty(entity.CN_S_LOCATION_STATE))
                strSql.Append(" CN_S_LOCATION_STATE = @CN_S_LOCATION_STATE,");
            strSql.Append(" CN_S_ROADWAY = @CN_S_ROADWAY");
            strSql.Append(" where CN_S_STOCK_CODE=@CN_S_STOCK_CODE");
            strSql.Append(" and CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            DataAccess.AddInParameter(cmd, "CN_S_AREA_CODE", DbType.String, entity.CN_S_AREA_CODE);
            DataAccess.AddInParameter(cmd, "CN_S_ROADWAY", DbType.String, entity.CN_S_ROADWAY);
            DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", DbType.String, entity.CN_S_STOCK_CODE);
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", DbType.String, entity.CN_S_LOCATION_CODE);
            if (!string.IsNullOrEmpty(entity.CN_S_LOCATION_STATE))
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_STATE", DbType.String, entity.CN_S_LOCATION_STATE);
            return ExecuteCommand(cmd, trans);
        }

        public SqlExecuteResult UpdateBFState(string where, string tableName, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            string sql = "";
            if (!string.IsNullOrEmpty(tableName))
            {
                sql = @" UPDATE " + tableName + " SET CN_S_LOCATION_STATE='报废' " + where;
            }
            else
            {
                sql = @" UPDATE " + "TN_WM_B_LOCATION_EXT" + " SET CN_S_LOCATION_STATE='报废' " + where;
            }

            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return ExecuteCommand(cmd, trans);
        }

        /// <summary>
        /// 更新货位扩展表
        /// </summary>
        /// <param name="locationCode">货位编码</param>
        /// <param name="locationState">货位状态</param>
        /// <param name="locationUseState">贮存状态</param>
        /// <param name="taskNo">任务编号</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateLocationExtState(string locationCode, string locationState, string bLocationState, string taskNo, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            try
            {
                if (string.IsNullOrEmpty(locationState) && string.IsNullOrEmpty(bLocationState) && string.IsNullOrEmpty(taskNo))
                    return OperateResult.Error("更新参数缺失！");

                StringBuilder strSql = new StringBuilder();
                strSql.AppendLine("UPDATE TN_WM_B_LOCATION_EXT SET CN_T_CREATE =CN_T_CREATE ");
                if (!string.IsNullOrEmpty(locationState))
                {
                    strSql.AppendLine(" ,CN_S_LOCATION_STATE='" + locationState + "' ");
                }
                if (!string.IsNullOrEmpty(taskNo))
                {
                    strSql.AppendLine(" ,CN_S_TASK_NO='" + taskNo + "' ");
                }
                strSql.AppendLine("WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");

                if (!string.IsNullOrEmpty(bLocationState))
                {
                    strSql.AppendLine(" AND CN_S_LOCATION_STATE='" + bLocationState + "' ");
                }
                result = ExecuteTranSql(strSql.ToString(), null, trans);
            }
            catch(Exception ex)
            {
                result = OperateResult.Error(ex.Message);
            }
            return result;
        }
  

        public OperateResult UpdateStateByTrayCode(string trayCode, string locationState, string useState, IDbTransaction trans)
        {
            if (string.IsNullOrEmpty(trayCode))
            {
                return OperateResult.Error("更新参数缺失！");
            }

            if (string.IsNullOrEmpty(locationState) && string.IsNullOrEmpty(useState))
            {
                return OperateResult.Error("更新参数缺失！");
            }

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("UPDATE TN_WM_B_LOCATION_EXT SET CN_T_CREATE =CN_T_CREATE ");
            if (!string.IsNullOrEmpty(locationState))
            {
                strSql.AppendLine(" ,CN_S_LOCATION_STATE='" + locationState + "' ");
            }
            if (!string.IsNullOrEmpty(useState))
            {
                strSql.AppendLine(" ,CN_S_USE_STATE='" + useState + "' ");
            }
            strSql.AppendLine(" WHERE CN_S_LOCATION_CODE IN (SELECT CN_S_LOCATION_CODE FROM tn_wm_b_tray_location WHERE CN_S_TRAY_CODE='" + trayCode + "')");
            return ExecuteTranSql(strSql.ToString(), null, trans);
        }

        /// <summary>
        /// 更新货位扩展表
        /// </summary>
        /// <param name="locationCode">货位编码</param>
        /// <param name="locationState">货位状态</param>
        /// <param name="locationUseState">贮存状态</param>
        /// <param name="taskNo">任务编号</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateLocationExtStateLock(string locationCode, string locationState, string locationBeforeState, string locationUseState, string taskNo, IDbTransaction trans)
        {
            if (string.IsNullOrEmpty(locationState) && string.IsNullOrEmpty(locationUseState) && string.IsNullOrEmpty(taskNo))
                return OperateResult.Error("更新参数缺失！");

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("UPDATE TN_WM_B_LOCATION_EXT SET CN_T_CREATE =CN_T_CREATE ");
            if (!string.IsNullOrEmpty(locationState))
            {
                strSql.AppendLine(" ,CN_S_LOCATION_STATE='" + locationState + "' ");
            }
            if (!string.IsNullOrEmpty(locationUseState))
            {
                strSql.AppendLine(" ,CN_S_USE_STATE='" + locationUseState + "' ");
            }
            if (!string.IsNullOrEmpty(taskNo))
            {
                strSql.AppendLine(" ,CN_S_TASK_NO='" + taskNo + "' ");
            }
            strSql.AppendLine("WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");
            if (!string.IsNullOrEmpty(locationBeforeState))
            {
                strSql.AppendLine("  AND CN_S_LOCATION_STATE='" + locationBeforeState + "' ");
            }
            return ExecuteTranSql(strSql.ToString(), null, trans);
        }



        #region 查询货位拓展表
        /// <summary>
        /// 查询货位拓展表
        /// </summary>
        /// <param name="searchModel">实体</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-12-18</History>
        public OperateResult GetLocationExtList(SearchModel searchModel)
        {
            var condition = searchModel.SearchCondition;
            string ConStr = "where 1=1 ";
            if (condition != null)
            {
                if (!string.IsNullOrEmpty(condition.CN_S_LOCATION_CODE.ToString()))
                {
                    ConStr = ConStr + " and CN_S_LOCATION_CODE like '%" + condition.CN_S_LOCATION_CODE + "%'";
                }
                if (!string.IsNullOrEmpty(condition.CN_S_AREA_CODE.ToString()))
                {
                    ConStr = ConStr + " and CN_S_AREA_CODE = '" + condition.CN_S_AREA_CODE + "'";
                }

            }
            string sql = @"(SELECT * FROM TN_WM_B_LOCATION_EXT) T " + ConStr;

            OperateResult result = new OperateResult();

            return ExecutePagingResult(sql, searchModel.PageIndex, searchModel.PageSize, "", "");
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="stockCode"></param>
        /// <returns></returns>
        public DataTable GetAreaStorageInfo(string stockCode, string[] areas)
        {
            string sql = @"  select CN_S_AREA_CODE,ISNULL([不满],0) 不满,ISNULL([空],0) 空,ISNULL([满],0) 满 from (select CN_S_AREA_CODE,COUNT(CN_S_LOCATION_CODE) SumLocation,CN_S_USE_STATE from tn_wm_b_location_ext ";
            if (!string.IsNullOrEmpty(stockCode))
                sql += " where CN_S_STOCK_CODE='" + stockCode + "' ";
            if (!string.IsNullOrEmpty(stockCode) && areas.Length > 0)
                sql += " and CN_S_AREA_CODE in ('" + string.Join("','", areas) + "')";
            else
                sql += " WHERE CN_S_AREA_CODE in ('" + string.Join("','", areas) + "')";
            sql += @"group by CN_S_AREA_CODE,CN_S_USE_STATE ) k
                            PIVOT 
                            (
                                SUM(SumLocation)  FOR 
                                k.CN_S_USE_STATE IN ([不满],[空],[满])
                            ) AS T ";

            return ExecuteDataTable(sql);
        }

        public List<TN_WM_LOCATION_EXTEntity> GetLocationList()
        {
            string sql = @" SELECT * FROM TN_WM_B_LOCATION_EXT ";
            return ExecuteQuery<TN_WM_LOCATION_EXTEntity>(sql);
        }

        public OperateResult UpdateLocationUseState(List<ImportTemplateEntity> InitEntity, string useState, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='{0}' WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE ";
            sql = string.Format(sql, useState);
            return ExecuteTranSql(sql, InitEntity, trans);
        }

        public DataTable GetStateQty(string stockCode)
        {
            string sql = @"select RTRIM(CN_S_ROADWAY) CN_S_ROADWAY,RTRIM(CN_S_LOCATION_STATE) CN_S_LOCATION_STATE,COUNT(CN_S_LOCATION_STATE) QTY from (
                        SELECT case when CN_S_LOCATION_STATE='正常'
                              then CN_S_USE_STATE
                              else CN_S_LOCATION_STATE
                              end CN_S_LOCATION_STATE,CN_S_ROADWAY
                          FROM tn_wm_b_location_ext where CN_S_STOCK_CODE='{0}') t
                          group by CN_S_ROADWAY,CN_S_LOCATION_STATE";

            sql = string.Format(sql, stockCode);
            return ExecuteDataTable(sql);
        }

        public OperateResult UpdateLocationByCode(string locationCode, string state, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='{1}' WHERE CN_S_LOCATION_CODE like '%{0}%' ";
            sql = string.Format(sql, locationCode, state);
            return ExecuteTranSql(sql, null, trans);
        }


        public OperateResult UpdateLocationByListCode(List<string> list, string state, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var locationCode in list)
            {
                string sql = "UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='{1}' WHERE CN_S_LOCATION_CODE like '%{0}%' ";
                sql = string.Format(sql, locationCode, state);
                result = ExecuteTranSql(sql, null, trans);
            }
            return result;
        }

        public SqlExecuteResult UpdateLocationNormal(string locationCodeS, string state, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("UPDATE tn_wm_b_location_ext SET CN_S_LOCATION_STATE=@CN_S_LOCATION_STATE ");
            strSql.AppendLine("WHERE CN_S_LOCATION_CODE IN  IN (" + locationCodeS + ")");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            DataAccess.AddInParameter(cmd, "CN_S_LOCATION_STATE", ComDbType.STRING, state);
            return ExecuteCommand(cmd, trans);
        }

        /// <summary>
        /// 释放任务起点
        /// </summary>
        /// <param name="taskNo">任务号</param>
        /// <param name="stratBit">起点</param>
        /// <param name="trans">事务</param>
        /// <History>[Hanhe(DBS)] created 2019/3/12</History>
        public OperateResult UnLockTaskStartBit(string taskNo, string stratBit, IDbTransaction trans)
        {
            string sql = @"update tn_wm_b_location_ext SET CN_S_LOCATION_STATE=@CN_S_LOCATION_STATE,CN_S_USE_STATE=@CN_S_USE_STATE,CN_S_TASK_NO=''
                        WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE and CN_S_TASK_NO=@CN_S_TASK_NO";
            return ExecuteTranSql(sql, new
            {
                CN_S_LOCATION_STATE = Constants.Location_State_Normal,
                CN_S_USE_STATE = Constants.Use_State_Empty,
                CN_S_LOCATION_CODE = stratBit,
                CN_S_TASK_NO = taskNo
            }, trans);
        }

        /// <summary>
        /// 释放任务结束点
        /// </summary>
        /// <param name="taskNo">任务号</param>
        /// <param name="endBit">终点</param>
        /// <param name="trans">事务</param>
        /// <History>[Hanhe(DBS)] created 2019/3/12</History>
        public OperateResult UnLockTaskEndBit(string taskNo, string endBit, IDbTransaction trans)
        {
            string sql = @"update tn_wm_b_location_ext SET CN_S_LOCATION_STATE=@CN_S_LOCATION_STATE,CN_S_USE_STATE=@CN_S_USE_STATE,CN_S_TASK_NO=''
                        WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE and CN_S_TASK_NO=@CN_S_TASK_NO";
            return ExecuteTranSql(sql, new
            {
                CN_S_LOCATION_STATE = Constants.Location_State_Normal,
                CN_S_USE_STATE = Constants.Use_State_Full,
                CN_S_LOCATION_CODE = endBit,
                CN_S_TASK_NO = taskNo
            }, trans);
        }

        #region 更新货位状态（由货位编号、货位状态）

        public OperateResult UpdateLocationStateByCodeAndState(string locationCode, string beforeLocationState, string afterLocationState, IDbTransaction trans)
        {
            string sql = " UPDATE tn_wm_b_location_ext SET CN_S_LOCATION_STATE = '" + beforeLocationState + "'  WHERE CN_S_LOCATION_CODE='" + locationCode + "'  AND CN_S_LOCATION_STATE = '" + afterLocationState + "' ";
            return ExecuteTranSql(sql, null, trans);
        }

        #endregion

        #region 获取出入库锁定的货位
        /// <summary>
        /// 获取出入库锁定的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<TN_WM_LOCATIONCODE_EXT_Entity> GetLockLocationByState(string stockCode, string areaCode, List<string> locationCodes, string locState)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATIONCODE_EXT_Entity> list = new List<TN_WM_LOCATIONCODE_EXT_Entity>();

            strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE, CN_S_ROW, CN_S_COL, CN_S_FLOOR
                              FROM tn_wm_b_location_ext WHERE 1=1 AND CN_S_LOCATION_STATE='" + locState + "' ";

            if (!string.IsNullOrEmpty(stockCode))
            {
                strSql += " AND CN_S_STOCK_CODE='" + stockCode + "' ";
            }
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql += " AND CN_S_AREA_CODE='" + areaCode + "' ";
            }
            if (locationCodes != null && locationCodes.Count > 0)
            {
                if (locationCodes.Count == 1)
                {
                    strSql += " AND CN_S_LOCATION_CODE = '" + locationCodes[0] + "' ";
                }
                else
                {
                    string locations = string.Join(",", locationCodes).Replace(",", "','");
                    strSql += " AND CN_S_LOCATION_CODE in ('" + locations + "') ";
                }
            }
            list = ExecuteQuery<TN_WM_LOCATIONCODE_EXT_Entity>(strSql);

            return list;

        }
        #endregion
        #region 获取库区中包含某物料的货位
        /// <summary>
        /// 获取出入库锁定的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetLocationByItemCode(string itemCode, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_LOCATION_CODE,TB2.CN_S_ROW,TB2.CN_S_COL,TB2.CN_S_FLOOR   FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine("  INNER JOIN tn_wm_b_location_ext TB2 on B.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");

         
    
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND B.CN_S_STOCK_AREA = '" + areaCode + "' ");
            }
            //追加查询条件
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCode + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());

            return list;

        }
        #endregion
        #region 查找该库区中即将要存放该物料的货位
        /// <summary>
        /// 查找该库区中即将要存放该物料的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetInTaskLocationByItemCode(string itemCode, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_END_BIT AS CN_S_LOCATION_CODE,TB2.CN_S_ROW,TB2.CN_S_COL,TB2.CN_S_FLOOR   FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_TASK B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine("  INNER JOIN tn_wm_b_location_ext TB2 on B.CN_S_END_BIT=TB2.CN_S_LOCATION_CODE ");

            strSql.AppendLine(" WHERE  B.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成')   ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND B.CN_S_END_AREA = '" + areaCode + "' ");
            }
            //追加查询条件
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCode + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetInTaskLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());
            return list;

        }
        #endregion
        #region 华凯 获取库区中包含某物料和批次的货位
        /// <summary>
        /// 获取出入库锁定的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetLocationByItemCodeAndLotNo(string itemCode,string lotNo, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_LOCATION_CODE,TB2.CN_S_ROW,TB2.CN_S_COL,TB2.CN_S_FLOOR   FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL C ON A.CN_GUID = C.CN_PARENT_GUID ");
            strSql.AppendLine(" INNER JOIN dbo.tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine("  INNER JOIN tn_wm_b_location_ext TB2 on B.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND B.CN_S_STOCK_AREA = '" + areaCode + "' ");
            }
            //追加查询条件
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCode + "' ");
            }
            if (!string.IsNullOrEmpty(lotNo))
            {
                strSql.AppendLine(" AND C.CN_S_PRODUCTION_BATCH = '" + lotNo + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());

            return list;

        }
        #endregion
        #region 华凯 查找该库区中即将要存放该物料和批次的货位
        /// <summary>
        /// 查找该库区中即将要存放该物料的货位
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="locationCodes"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public List<AutoBomLocationAbbreEntity> GetInTaskLocationByItemCodeAndLotNo(string itemCode, string lotNo, string areaCode)
        {

            List<AutoBomLocationAbbreEntity> list = new List<AutoBomLocationAbbreEntity>();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_END_BIT AS CN_S_LOCATION_CODE,TB2.CN_S_ROW,TB2.CN_S_COL,TB2.CN_S_FLOOR   FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL C ON A.CN_GUID = C.CN_PARENT_GUID ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_TASK B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine("  INNER JOIN tn_wm_b_location_ext TB2 on B.CN_S_END_BIT=TB2.CN_S_LOCATION_CODE ");

            strSql.AppendLine(" WHERE  B.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成')   ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" AND B.CN_S_END_AREA = '" + areaCode + "' ");
            }
            //追加查询条件
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCode + "' ");
            }
            if (!string.IsNullOrEmpty(lotNo))
            {
                strSql.AppendLine(" AND C.CN_S_PRODUCTION_BATCH = '" + lotNo + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("GetInTaskLocationByItemCode", "sql语句为：" + strSql.ToString());
            list = ExecuteQuery<AutoBomLocationAbbreEntity>(strSql.ToString());
            return list;

        }
        #endregion
        #region 检验区按排下达任务
        public List<TN_WM_LOCATION_EXTEntity> GetLocationByRow(string rowNo)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATION_EXTEntity> list = new List<TN_WM_LOCATION_EXTEntity>();

            strSql = @"SELECT RTRIM(CN_S_LOCATION_CODE) AS CN_S_LOCATION_CODE,CN_S_LOCATION_STATE,CN_S_USE_STATE,CN_S_COL
                              FROM tn_wm_b_location_ext WHERE  CN_S_ROW ='" + rowNo + "' order by CN_S_COL desc";
            list = ExecuteQuery<TN_WM_LOCATION_EXTEntity>(strSql.ToString());
            return list;
        }
        public List<TN_WM_LOCATION_EXTEntity> GetLocationByArea(string areaCode)
        {
            string strSql = string.Empty;
            List<TN_WM_LOCATION_EXTEntity> list = new List<TN_WM_LOCATION_EXTEntity>();

            strSql = @"SELECT DISTINCT CN_S_ROW
                              FROM tn_wm_b_location_ext WHERE  CN_S_AREA_CODE ='" + areaCode + "' ";
            list = ExecuteQuery<TN_WM_LOCATION_EXTEntity>(strSql.ToString());
            return list;
        }
        #endregion


    }
}