using HanHe.Utility.Data;
using HH.Redis.ReisModel;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Check;
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.Check
{
    public class TN_WM_CHECK_LISTDAL : DapperBaseDAL
    {
        public DataTable GetGroupDT(string sqlwhere)
        {
            //CN_S_LOT_NO注释
            string sql = "SELECT CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MODEL,CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_CHECK_QTY) AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,'' as CN_S_LOT_NO,SUM(CN_F_CHECK_QTY)-SUM(CN_F_QUANTITY) AS BREAKQUANTITY FROM dbo.tn_wm_check_list WHERE CN_S_ITEM_CODE<>'' {0} GROUP BY CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql, null);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/21</history>
        public DataTable GetEntity(string sqlwhere)
        {
            string sql = "select ROW_NUMBER() OVER (ORDER BY CN_S_OP_NO) AS ROWS,* from TN_WM_CHECK_LIST where {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);

            //获得列表
            DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
            return dt;
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/10/25</history>
        public DataTable GetList(string sqlwhere)
        {
            string sql = "SELECT CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_CHECK_QTY) AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT FROM dbo.tn_wm_check_list where {0} GROUP BY CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);

            //获得列表
            DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
            return dt;
        }

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public DataTable GetDataTableGroup(string sqlWhere, string OrdeBy, int pageSize, int pageIndex)
        {
            string sql = @"(SELECT CN_S_STOCK_CODE,CN_S_OP_NO,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CAST(SUM(CN_F_QUANTITY) AS INT)AS CN_F_QUANTITY,
CAST(SUM(CN_F_CHECK_QTY) AS INT) AS CN_F_CHECK_QTY  FROM dbo.tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_STOCK_CODE
) as a";
            DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
            this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, sql);
            this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, sqlWhere);
            this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, OrdeBy);
            this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, pageSize);
            this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, pageIndex);
            this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
            this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);

            //如果是oracle  增加特性
            if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
            {
                //处理游标类型 因DbType无游标类型,游标类型单独处理
                ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
            }
            try
            {
                //获得列表
                DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
                //obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        #region 获取盘点结果查询
        /// <summary>
        /// 获取盘点结果查询
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/21</history>
        public DataTable GetDataTable(string sqlwhere)
        {
            string sql = "select * ,(SELECT COUNT(CN_S_UNIQUE_CODE) FROM dbo.TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE=CN_S_PACK_CODE) AS isexist from TN_WM_CHECK_LIST {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }


        /// <summary>
        /// 获取盘点结果查询
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/21</history>
        public List<TN_WM_CHECK_LISTEntity> GetCheckList(string sqlwhere)
        {
            string sql = "select * ,(SELECT COUNT(CN_S_UNIQUE_CODE) FROM dbo.TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE=CN_S_PACK_CODE) AS isexist from TN_WM_CHECK_LIST {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
        }


        /// <summary>
        /// 根据盘点单位获取盘点明细，托盘号、货位号
        /// </summary>
        /// <returns></returns>
        ///  <history>[HanHe(DBS)] CREATED 2019/3/26</history>
        public List<TN_WM_CHECK_LISTEntity> GetUnitCheckList(string sqlwhere)
        {
            string sql = @"SELECT 
                               CN_S_STOCK_AREA
                              ,CN_S_LOCATION_CODE
                              ,CN_S_TRAY_CODE
                              ,CN_S_TRAY_GRID
                              ,CN_S_ITEM_CODE
                              ,CN_S_ITEM_NAME
                              ,CN_S_ITEM_STATE
                              ,CN_S_MODEL
                              ,CN_S_FIGURE_NO
                              ,CN_S_MEASURE_UNIT
                              ,CN_S_ISCHECK
                              ,sum(CN_F_QUANTITY) CN_F_QUANTITY
                              ,sum(CN_F_CHECK_QTY) CN_F_CHECK_QTY
                          FROM tn_wm_check_list " + sqlwhere + @"
                          group by CN_S_STOCK_AREA
                              ,CN_S_LOCATION_CODE
                              ,CN_S_TRAY_CODE
                              ,CN_S_TRAY_GRID
                              ,CN_S_ITEM_CODE
                              ,CN_S_ITEM_NAME
                              ,CN_S_ITEM_STATE
                              ,CN_S_MODEL
                              ,CN_S_FIGURE_NO
                              ,CN_S_MEASURE_UNIT
                              ,CN_S_ISCHECK";
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
        }

        /// <summary>
        /// 获取二次盘点盘点结果查询
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/21</history>
        public List<TN_WM_CHECK_LISTEntity> GetChecklist(string sqlwhere)
        {
            string sql = @"SELECT CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,0.0000 AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE FROM tn_wm_check_list {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
        }
        #endregion

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public SqlExecuteResult Addlist(List<TN_WM_CHECK_LISTEntity> listentity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tn_wm_check_list(");
            strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY)");
            strSql.Append(" values (");
            strSql.Append(":CN_S_OP_NO,:CN_S_DTL_ROW,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_OWNER,:CN_S_LOT_NO,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_F_QUANTITY,:CN_F_CHECK_QTY,:CN_S_MEASURE_UNIT,:CN_S_NOTE,:CN_S_ISCHECK,:CN_S_ISWIN,:CN_S_OPERATOR,:CN_T_OPERATE,:CN_S_PACK_CODE,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_S_PRODUCTION_BATCH,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY)");
            List<DbCommand> listcmd = new List<DbCommand>();
            int i = 1;
            foreach (TN_WM_CHECK_LISTEntity entity in listentity)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
                DataAccess.AddInParameter(cmd, "CN_S_DTL_ROW", ComDbType.INT32, i++);
                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_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
                DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
                DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
                DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, entity.CN_S_ISCHECK);
                DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_S_ISWIN);
                DataAccess.AddInParameter(cmd, "CN_S_OPERATOR", ComDbType.STRING, entity.CN_S_OPERATOR);
                DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, entity.CN_T_OPERATE);
                DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT);
                DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
                DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
                DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
                DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
                DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
                listcmd.Add(cmd);
            }


            return ExecuteCommands(listcmd, trans);
        }


        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public SqlExecuteResult Addlist(List<TN_WM_CHECK_LISTEntity> listentity, string CN_S_OPERATOR, DateTime CN_T_OPERATE, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tn_wm_check_list(");
            strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE)");
            strSql.Append(" values (");
            strSql.Append(":CN_S_OP_NO,:CN_S_DTL_ROW,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_OWNER,:CN_S_LOT_NO,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_F_QUANTITY,:CN_F_CHECK_QTY,:CN_S_MEASURE_UNIT,:CN_S_NOTE,:CN_S_ISCHECK,:CN_S_ISWIN,:CN_S_OPERATOR,:CN_T_OPERATE,:CN_S_PACK_CODE)");
            List<DbCommand> listcmd = new List<DbCommand>();

            foreach (TN_WM_CHECK_LISTEntity entity in listentity)
            {

                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
                DataAccess.AddInParameter(cmd, "CN_S_DTL_ROW", ComDbType.INT32, entity.CN_S_DTL_ROW);
                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_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
                DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
                DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
                DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, entity.CN_S_ISCHECK);
                DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_S_ISWIN);
                DataAccess.AddInParameter(cmd, "CN_S_OPERATOR", ComDbType.STRING, CN_S_OPERATOR);
                DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, CN_T_OPERATE);
                DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
                listcmd.Add(cmd);
            }


            return ExecuteCommands(listcmd, trans);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public SqlExecuteResult UpdateQTY(List<TN_WM_CHECK_LISTEntity> entitylist, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tn_wm_check_list set "); ;
            strSql.Append("CN_F_CHECK_QTY=:CN_F_CHECK_QTY,");
            strSql.Append("CN_S_ISCHECK=:CN_S_ISCHECK,");
            strSql.Append("CN_S_ISWIN=:CN_S_ISWIN,");

            strSql.Append("CN_S_MODIFY=:CN_S_MODIFY,");
            strSql.Append("CN_S_MODIFY_BY=:CN_S_MODIFY_BY,");
            strSql.Append("CN_T_MODIFY=:CN_T_MODIFY");
            strSql.Append(" where CN_S_OP_NO=:CN_S_OP_NO AND  CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_PACK_CODE=:CN_S_PACK_CODE AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE");
            List<DbCommand> cmdlist = new List<DbCommand>();
            foreach (TN_WM_CHECK_LISTEntity entity in entitylist)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
                DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
                DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, "Y");
                DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_F_QUANTITY < entity.CN_F_CHECK_QTY ? "Y" : "N");
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
                DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
                cmdlist.Add(cmd);
            }
            return ExecuteCommands(cmdlist, trans);
        }



        /// <summary>
        /// 根据盘点单托盘编码更新
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public SqlExecuteResult UpdateByCode(string CN_S_OP_NO, string CN_S_TRAY_CODE, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tn_wm_check_list set "); ;
            strSql.Append("CN_S_ISCHECK=:CN_S_ISCHECK");
            strSql.Append(" where CN_S_OP_NO=:CN_S_OP_NO AND  CN_S_TRAY_CODE=:CN_S_TRAY_CODE");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, CN_S_OP_NO);
            DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, CN_S_TRAY_CODE);
            DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, "Y");
            return ExecuteCommand(cmd, trans);
        }


        /// <summary>
        /// 更新（二次盘点）
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public SqlExecuteResult UpdateByTrayCode(string CN_S_OP_NO, string CN_S_TRAY_CODE, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tn_wm_check_list set "); ;
            strSql.Append("CN_F_CHECK_QTY=0,");
            strSql.Append("CN_S_ISCHECK='N',");
            strSql.Append("CN_S_ISWIN=''");
            strSql.Append(" where CN_S_OP_NO='" + CN_S_OP_NO + "' AND  CN_S_TRAY_CODE in ('" + CN_S_TRAY_CODE + "')");

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

            return ExecuteCommand(cmd, trans);
        }


        #region 根据盘点查询托盘与物料表
        /// <summary>
        /// 根据盘点查询托盘与物料表
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/24</history>
        public List<TN_WM_CHECK_LISTEntity> GetTrayitemByOpno(string sqlwhere, string type)
        {
            string sql = "";
            if (type == "货位盘点" || type == "自定义盘点")
            {
                sql = @"SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,ISNULL(c.CN_S_TRAY_CODE,b.CN_S_TRAY_CODE) AS CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,'' AS CN_S_LOT_NO,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE 
--码盘汇总开始
INNER JOIN  dbo.TN_WM_B_TRAY_ITEM_MST AS c ON 
ISNULL(a.CN_S_ITEM_CODE,'')=ISNULL(c.CN_S_ITEM_CODE,'') AND ISNULL(c.CN_S_ITEM_NAME,'') = ISNULL(a.CN_S_ITEM_NAME,'') AND ISNULL(a.CN_S_ITEM_STATE,'')=ISNULL(c.CN_S_ITEM_STATE,'') AND ISNULL(a.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND ISNULL(a.CN_S_MODEL,'') = ISNULL(c.CN_S_MODEL,'') AND ISNULL(a.CN_S_FIGURE_NO,'')=ISNULL(c.CN_S_FIGURE_NO,'') AND ISNULL(c.CN_S_MEASURE_UNIT,'') = ISNULL(a.CN_S_MEASURE_UNIT,'')
AND ISNULL(c.CN_S_TRAY_CODE,'')=ISNULL(b.CN_S_TRAY_CODE,'')
INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO    {0} 
UNION ALL --空货位

SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,b.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,ISNULL(d.CN_S_TRAY_CODE,'') AS CN_S_TRAY_CODE,'' as CN_S_TRAY_GRID,'' AS CN_S_ITEM_CODE,'' AS CN_S_ITEM_NAME,'' AS CN_S_ITEM_STATE,'' AS CN_S_OWNER,'' AS CN_S_LOT_NO,'' AS CN_S_MODEL,'' AS CN_S_FIGURE_NO,0  AS CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,b.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,b.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
INNER JOIN dbo.tn_wm_check_mst AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO
LEFT JOIN dbo.tn_wm_b_location_ext AS c ON c.CN_S_LOCATION_CODE = a.CN_S_LOCATION_CODE
LEFT JOIN dbo.tn_wm_b_tray_location AS d ON d.CN_S_LOCATION_CODE=a.CN_S_LOCATION_CODE {0}  AND a.CN_S_ITEM_CODE IS NULL";

            }
            else
            {
                sql = @"SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_LOCATION_CODE,ISNULL(c.CN_S_TRAY_CODE,b.CN_S_TRAY_CODE) AS CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,'' AS CN_S_LOT_NO,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
LEFT JOIN  dbo.TN_WM_B_TRAY_ITEM_MST AS c ON 
ISNULL(a.CN_S_ITEM_CODE,'')=ISNULL(c.CN_S_ITEM_CODE,'') AND ISNULL(c.CN_S_ITEM_NAME,'') = ISNULL(a.CN_S_ITEM_NAME,'') AND ISNULL(a.CN_S_ITEM_STATE,'')=ISNULL(c.CN_S_ITEM_STATE,'') AND ISNULL(a.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND ISNULL(a.CN_S_MODEL,'') = ISNULL(c.CN_S_MODEL,'') AND ISNULL(a.CN_S_FIGURE_NO,'')=ISNULL(c.CN_S_FIGURE_NO,'') AND ISNULL(c.CN_S_MEASURE_UNIT,'') = ISNULL(a.CN_S_MEASURE_UNIT,'')
INNER JOIN dbo.tn_wm_b_tray_location AS b ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE 
INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO {0} ";

                //UNION ALL --空托盘
                //SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_LOCATION_CODE,b.CN_S_TRAY_CODE,'' as CN_S_TRAY_GRID,'' AS CN_S_ITEM_CODE,'' AS CN_S_ITEM_NAME,'' AS CN_S_ITEM_STATE,'' AS CN_S_OWNER,'' AS CN_S_LOT_NO,'' AS CN_S_MODEL,'' AS CN_S_FIGURE_NO,0  AS CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
                //LEFT JOIN (SELECT *,'' AS CN_S_ITEM_CODE FROM tn_wm_b_tray_location WHERE CN_S_TRAY_CODE NOT IN(SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_ITEM_MST)) AS b ON b.CN_S_ITEM_CODE=a.CN_S_ITEM_CODE AND a.CN_S_STOCK_AREA=b.CN_S_STOCK_AREA
                //INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO {0}";
            }
            sql = string.Format(sql, sqlwhere);
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
        }
        #endregion

        #region 根据托盘编码获取盘点托盘信息
        /// <summary>
        /// 根据托盘编码获取盘点托盘信息
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/27</history>
        public DataTable GetTrayCheckByCode(string sqlwhere)
        {
            string sql = @"SELECT b.CN_S_STOCK_CODE,b.CN_S_STOCK_AREA,b.CN_S_OP_NO,b.CN_S_TRAY_CODE,ISNULL(a.CN_S_TRAY_GRID,'') AS CN_S_TRAY_GRID,ISNULL(a.CN_S_PACKING_CODE,'') AS CN_S_PACKING_CODE,ISNULL(a.CN_S_ITEM_CODE,'') AS CN_S_ITEM_CODE,ISNULL(a.CN_S_ITEM_NAME,'') AS CN_S_ITEM_NAME,ISNULL(a.CN_S_ITEM_STATE,'') AS CN_S_ITEM_STATE,CAST(b.CN_F_QUANTITY AS INT) AS CN_F_QUANTITY,ISNULL(CAST(a.CN_F_PACKING_QTY AS INT),0) AS CN_F_PACKING_QTY,b.CN_S_LOCATION_CODE,ISNULL(CAST(d.CN_F_QUANTITY AS INT),0) AS CN_F_QUANTITY_DTL,ISNULL(c.CN_N_ROW_NUM,0) AS CN_N_ROW_NUM,ISNULL(c.CN_N_COLUMN_NUM,0) AS CN_N_COLUMN_NUM,b.CN_S_LOT_NO,b.CN_S_OWNER FROM  tn_wm_check_list  AS b
LEFT JOIN .dbo.tn_wm_b_tray_item_rel AS a ON a.CN_S_TRAY_CODE =b.CN_S_TRAY_CODE AND b.CN_S_TRAY_GRID = a.CN_S_TRAY_GRID AND a.CN_S_PACKING_CODE=b.CN_S_PACK_CODE
LEFT JOIN dbo.tn_wm_tray_info AS c ON a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
LEFT JOIN (SELECT CN_S_OP_NO,CN_S_TRAY_CODE,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,CN_S_ITEM_CODE  FROM tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_TRAY_CODE,CN_S_ITEM_CODE) AS d ON b.CN_S_OP_NO=d.CN_S_OP_NO AND d.CN_S_TRAY_CODE = b.CN_S_TRAY_CODE AND d.CN_S_ITEM_CODE = b.CN_S_ITEM_CODE {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion

        #region 根据托盘编码获取盘点托盘信息
        /// <summary>
        /// 根据托盘编码获取盘点托盘信息
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/27</history>
        public DataTable GetTrayItemByCode(string sqlwhere)
        {
            string sql = @"SELECT a.CN_S_TRAY_CODE,a.CN_S_TRAY_GRID,CN_F_PACKING_QTY,b.CN_S_OP_NO,ISNULL(b.CN_F_QUANTITY,0) AS CN_F_QUANTITY FROM (SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CAST(SUM(CN_F_PACKING_QTY) AS INT) AS CN_F_PACKING_QTY FROM dbo.tn_wm_b_tray_item_rel GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID) AS a LEFT JOIN (SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CAST(SUM(CN_F_QUANTITY) AS INT) AS CN_F_QUANTITY,CN_S_OP_NO FROM dbo.tn_wm_check_list GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OP_NO) AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE AND b.CN_S_TRAY_GRID = a.CN_S_TRAY_GRID  {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion

        #region 根据货位获取托盘信息
        /// <summary>
        /// 根据托盘编码获取盘点托盘信息
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        ///  <history>[HanHe(lt)] CREATED 2018/8/27</history>
        public DataTable GetTrayByLocationCode(string sqlwhere)
        {
            string sql = @"SELECT CN_S_OP_NO,CN_S_LOCATION_CODE,a.CN_S_TRAY_CODE,b.CN_N_ROW_NUM,b.CN_N_COLUMN_NUM,b.CN_S_TRAY_TYPE FROM (SELECT CN_S_OP_NO,CN_S_LOCATION_CODE,CN_S_TRAY_CODE FROM dbo.tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_LOCATION_CODE,CN_S_TRAY_CODE) AS a INNER JOIN dbo.tn_wm_tray_info AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0} ";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion

        #region 盘点单最大行号
        /// <summary>
        /// 盘点单最大行号
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public int GetMaxRow(string sqlwhere)
        {
            string sql = "SELECT ISNULL(MAX(CN_S_DTL_ROW),0) AS CN_S_DTL_ROW FROM dbo.tn_wm_check_list {0} GROUP BY CN_S_OP_NO";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
            return Convert.ToInt32(dt.Rows[0]["CN_S_DTL_ROW"]);
        }
        #endregion

        #region 是否存在已经执行数据
        /// <summary>
        /// 是否存在已经执行数据
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public DataTable GetExist(string sqlwhere)
        {
            string sql = "SELECT * FROM dbo.tn_wm_check_mst WHERE CN_S_STATE='执行' {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
            return dt;
        }
        #endregion

        #region 快速盘点
        #region 根据货位获取托盘中物料
        /// <summary>
        /// 根据货位获取托盘中物料
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2019/1/2</history>
        public DataTable GetTrayitemBylocation(string locationCode)
        {
            string sql = @"SELECT a.CN_S_STOCK_CODE,a.CN_S_AREA_CODE,a.CN_S_LOCATION_CODE,b.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_MEASURE_UNIT,c.CN_F_QUANTITY,CAST(d.CN_F_PACKING_QTY AS INT) AS CN_F_BOXPACKING_QTY,d.CN_S_PACKING_UNIT AS CN_F_BOXPACKING_UNIT
 FROM dbo.tn_wm_b_location_ext AS a 
 LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
LEFT JOIN (SELECT TOP 1 CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT FROM dbo.TN_WM_B_UNIQUE_BARCODE WHERE  CN_S_PARENT_CODE='' GROUP BY CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT) AS d ON c.CN_S_ITEM_CODE=d.CN_S_ITEM_CODE
where a.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE";

            return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
        }
        #endregion

        #endregion

        #region 获取子表明细表最大行号
        /// <summary>
        /// 获取子表明细表最大行号
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2019/1/17</history>
        public List<TN_WM_CHECK_LISTEntity> GetMaxCheckRow()
        {
            string sql = @"SELECT MAX(b.CN_N_ROW_NO) AS CN_S_DTL_ROW,a.CN_S_OP_NO FROM dbo.tn_wm_check_mst AS a INNER JOIN 
dbo.tn_wm_check_dtl AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO WHERE a.CN_S_STATE='执行' AND a.CN_S_TYPE='快速盘点' GROUP BY a.CN_S_OP_NO
UNION ALL
SELECT MAX(b.CN_S_DTL_ROW) AS CN_S_DTL_ROW,a.CN_S_OP_NO FROM dbo.tn_wm_check_mst AS a INNER JOIN 
dbo.tn_wm_check_list AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO WHERE a.CN_S_STATE='执行' AND a.CN_S_TYPE='快速盘点' GROUP BY a.CN_S_OP_NO";
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
        }
        #endregion


        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/8/23</history>
        public OperateResult AddCheckLists(List<TN_WM_CHECK_LISTEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_CHECK_LISTEntity entity in list)
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_CHECK_LIST  " + entity.sqlWhere + ") ");
                strSql.Append("UPDATE TN_WM_CHECK_LIST SET CN_F_CHECK_QTY=@CN_F_CHECK_QTY,CN_S_ISCHECK=@CN_S_ISCHECK,CN_S_ISWIN=@CN_S_ISWIN " + entity.sqlWhere + "");
                strSql.Append(" else ");
                strSql.Append(" insert into tn_wm_check_list(");
                strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY)");
                strSql.Append(" values (");
                strSql.Append("@CN_S_OP_NO,@CN_S_DTL_ROW,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_OWNER,@CN_S_LOT_NO,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_F_QUANTITY,@CN_F_CHECK_QTY,@CN_S_MEASURE_UNIT,@CN_S_NOTE,@CN_S_ISCHECK,@CN_S_ISWIN,@CN_S_OPERATOR,@CN_T_OPERATE,@CN_S_PACK_CODE,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_S_PRODUCTION_BATCH,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY)");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_OP_NO = entity.CN_S_OP_NO,
                    CN_S_DTL_ROW = entity.CN_S_DTL_ROW,
                    CN_S_STOCK_CODE = entity.CN_S_STOCK_CODE,
                    CN_S_STOCK_AREA = entity.CN_S_STOCK_AREA,
                    CN_S_LOCATION_CODE = entity.CN_S_LOCATION_CODE,
                    CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
                    CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID,
                    CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
                    CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
                    CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
                    CN_S_OWNER = entity.CN_S_OWNER,
                    CN_S_LOT_NO = entity.CN_S_LOT_NO,
                    CN_S_MODEL = entity.CN_S_MODEL,
                    CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
                    CN_F_QUANTITY = entity.CN_F_QUANTITY,
                    CN_F_CHECK_QTY = entity.CN_F_CHECK_QTY,
                    CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT,
                    CN_S_NOTE = entity.CN_S_NOTE,
                    CN_S_ISCHECK = entity.CN_S_ISCHECK,
                    CN_S_ISWIN = entity.CN_F_QUANTITY < entity.CN_F_CHECK_QTY ? "Y" : "N",
                    CN_S_OPERATOR = entity.CN_S_OPERATOR,
                    CN_T_OPERATE = entity.CN_T_OPERATE,
                    CN_S_PACK_CODE = entity.CN_S_PACK_CODE,
                    CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT,
                    CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY,
                    CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH,
                    CN_S_CREATOR = entity.CN_S_CREATOR,
                    CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY,
                    CN_T_CREATE = entity.CN_T_CREATE,
                    CN_S_MODIFY = entity.CN_S_MODIFY,
                    CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY,
                    CN_T_MODIFY = entity.CN_T_MODIFY
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public OperateResult DeleteCheckLists(string sqlwhere, IDbTransaction trans)
        {
            string sql = "delete dbo.tn_wm_check_list {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteTranSql(sql, null, trans);
        }
        #endregion
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/10/25</history>
        public List<TN_WM_CHECK_LISTEntity> CheckList(string locationCode)
        {
            StringBuilder sbStr = new StringBuilder();
            sbStr.Append("SELECT * FROM tn_wm_check_list WHERE CN_S_OP_NO!=''");
            if (!string.IsNullOrEmpty(locationCode))
            {
                sbStr.Append(" AND CN_S_LOCATION_CODE='" + locationCode + "' ");
            }
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sbStr.ToString());
        }

        /// <summary>
        /// 更新盘点结果
        /// </summary>
        /// <param name="planCheck">盘亏数据</param>
        /// <param name="trans"></param>
        public OperateResult UpdateCheckResult(List<TN_WM_CHECK_LISTEntity> planCheck, RedisUserEntity user, IDbTransaction trans)
        {
            OperateResult or = new OperateResult();
            string sql = @"UPDATE TN_WM_CHECK_LIST SET 
                        CN_F_CHECK_QTY=@CN_F_CHECK_QTY,
                        CN_S_ISCHECK=@CN_S_ISCHECK,
                        CN_S_ISWIN=@CN_S_ISWIN,
                        CN_S_MODIFY=@CN_S_MODIFY,
                        CN_S_MODIFY_BY=@CN_S_MODIFY_BY,
                        CN_T_MODIFY=@CN_T_MODIFY
                        where CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
                                and CN_S_ITEM_CODE=@CN_S_ITEM_CODE 
                                and CN_S_PACKING_UNIT=@CN_S_PACKING_UNIT 
                                and CN_F_PACKING_QTY=@CN_F_PACKING_QTY";
            foreach (TN_WM_CHECK_LISTEntity m in planCheck)
            {
                or = ExecuteTranSql(sql, new
                {
                    CN_F_CHECK_QTY = m.CN_F_CHECK_QTY,
                    CN_S_ISCHECK = "Y",
                    CN_S_ISWIN = "N",
                    CN_S_MODIFY = user.CN_S_LOGIN,
                    CN_S_MODIFY_BY = user.CN_S_NAME,
                    CN_T_MODIFY = m.CN_T_MODIFY,
                    CN_S_LOCATION_CODE = m.CN_S_LOCATION_CODE,
                    CN_S_ITEM_CODE = m.CN_S_ITEM_CODE,
                    CN_S_PACKING_UNIT = m.CN_S_PACKING_UNIT,
                    CN_F_PACKING_QTY = m.CN_F_PACKING_QTY
                }, trans);
                if (or.Status != ResultStatus.Success)
                    return or;
            }
            return OperateResult.Succeed();
        }
    }
}