using HH.WMS.Common;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Common;
using HH.WMS.Entitys.Entitys;
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.OutStock
{
    public class TN_WM_SORTING_LISTDAL : DapperBaseDAL
    {
        #region 分拣单列表
        /// <summary>
        /// 分拣单列表
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public List<TN_WM_SORTING_LISTEntity> GetSortings(string key)
        {
            string sql = @"SELECT * FROM TN_WM_SORTING_LIST WHERE CN_S_STATE IN('待分拣','分拣中')";
            if (!string.IsNullOrEmpty(key))
                sql += " AND CN_S_SORTING_NO LIKE '%" + key.Replace("'", "") + "%' ";
            return ExecuteQuery<TN_WM_SORTING_LISTEntity>(sql);
        }
        #endregion

        #region 批量新增分拣主表
        /// <summary>
        /// 批量新增分拣主表
        /// </summary>
        /// <param name="mst"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[Hanhe(lt)] created 2018-10-8</History>
        public SqlExecuteResult batchAdd(List<TN_WM_SORTING_LISTEntity> list, DbTransaction trans)
        {
            string sql = @"INSERT INTO tn_wm_sorting_list
                           (CN_S_DEPARTMENT,CN_S_CREATE_TYPE
                           ,CN_GUID,CN_S_SORTING_NO
                           ,CN_S_ORGAN,CN_S_MODIFY
                           ,CN_S_FROM,CN_S_OP_FROM
                           ,CN_S_MODIFY_BY,CN_S_FROM_NO
                           ,CN_T_MODIFY,CN_S_CREATOR
                           ,CN_T_OPERATE,CN_S_STOCK_CODE
                           ,CN_S_CREATOR_BY,CN_T_CREATE
                           ,CN_S_STOCK_AREA,CN_S_STATUS
                           ,CN_S_STATE,CN_S_DEVICE_TYPE
                           ,CN_S_SEEDING_MODE,CN_S_GROUP,CN_S_ORDER_NO,CN_C_BIG)
                     VALUES
                           (:CN_S_DEPARTMENT,:CN_S_CREATE_TYPE
                           ,:CN_GUID,:CN_S_SORTING_NO
                           ,:CN_S_ORGAN,:CN_S_MODIFY
                           ,:CN_S_FROM,:CN_S_OP_FROM
                           ,:CN_S_MODIFY_BY,:CN_S_FROM_NO
                           ,:CN_T_MODIFY,:CN_S_CREATOR
                           ,:CN_T_OPERATE,:CN_S_STOCK_CODE
                           ,:CN_S_CREATOR_BY,:CN_T_CREATE
                           ,:CN_S_STOCK_AREA,:CN_S_STATUS
                           ,:CN_S_STATE,:CN_S_DEVICE_TYPE
                           ,:CN_S_SEEDING_MODE,:CN_S_GROUP,:CN_S_ORDER_NO,:CN_C_BIG)";

            List<DbCommand> cmdlist = new List<DbCommand>();
            foreach (TN_WM_SORTING_LISTEntity mst in list)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                this.DataAccess.AddInParameter(cmd, "CN_S_DEPARTMENT", ComDbType.STRING, mst.CN_S_DEPARTMENT);
                this.DataAccess.AddInParameter(cmd, "CN_S_CREATE_TYPE", ComDbType.STRING, mst.CN_S_CREATE_TYPE);
                this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, mst.CN_GUID);
                this.DataAccess.AddInParameter(cmd, "CN_S_SORTING_NO", ComDbType.STRING, mst.CN_S_SORTING_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_ORGAN", ComDbType.STRING, mst.CN_S_ORGAN);
                this.DataAccess.AddInParameter(cmd, "CN_S_FROM", ComDbType.STRING, mst.CN_S_FROM);
                this.DataAccess.AddInParameter(cmd, "CN_S_OP_FROM", ComDbType.STRING, mst.CN_S_OP_FROM);
                this.DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, mst.CN_S_FROM_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, mst.CN_S_STOCK_CODE);
                this.DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, mst.CN_S_STOCK_AREA);
                this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, mst.CN_S_MODIFY);
                this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, mst.CN_S_MODIFY_BY);
                this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, mst.CN_T_MODIFY);
                this.DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, mst.CN_T_OPERATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, mst.CN_S_CREATOR);
                this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, mst.CN_S_CREATOR_BY);
                this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, mst.CN_T_CREATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_STATUS", ComDbType.STRING, mst.CN_S_STATUS);
                this.DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, mst.CN_S_STATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_DEVICE_TYPE", ComDbType.STRING, mst.CN_S_DEVICE_TYPE);
                this.DataAccess.AddInParameter(cmd, "CN_S_SEEDING_MODE", ComDbType.STRING, mst.CN_S_SEEDING_MODE);
                this.DataAccess.AddInParameter(cmd, "CN_S_GROUP", ComDbType.STRING, mst.CN_S_GROUP);
                this.DataAccess.AddInParameter(cmd, "CN_S_ORDER_NO", ComDbType.STRING, mst.CN_S_ORDER_NO);
                this.DataAccess.AddInParameter(cmd, "CN_C_BIG", ComDbType.STRING, mst.CN_C_BIG);
                cmdlist.Add(cmd);
            }
            return ExecuteCommands(cmdlist, trans);
        }
        #endregion

        #region 分拣结果绑定周转箱
        /// <summary>
        /// 分拣结果绑定周转箱
        /// </summary>
        /// <param name="sortingResult"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult MergeBox(List<TN_WM_SORTING_RESULTEntity> sortingResult, IDbTransaction trans)
        {
            string sql = @"UPDATE TN_WM_SORTING_RESULT SET CN_S_TURNOVERBOX_CODE=@CN_S_TURNOVERBOX_CODE WHERE CN_GUID=@CN_GUID";
            return ExecuteTranSql(sql, sortingResult, trans);
        }
        #endregion

        #region 下降托盘物料关联数量(数量为0删除)
        /// <summary>
        /// 下降托盘物料关联数量(数量为0删除)
        /// </summary>
        /// <returns></returns>
        public OperateResult DropTrayItemQty(List<TN_WM_B_TRAY_ITEM_MSTEntity> trayItemList, TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
        {
            string sql = string.Empty;
            foreach (var trayItem in trayItemList)
            {
                if (trayItem.CN_F_QUANTITY == 0)
                {
                    Log.Info("ConfirmSorting DropTrayItemQty", "删除托盘物料关联主表");
                    //删除托盘物料关联主表
                    sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_GUID=@CN_GUID";
                    ExecuteTranSql(sql, trayItem, trans);
                    Log.Info("ConfirmSorting DropTrayItemQty", "删除托盘物料关联子表");
                    //删除托盘物料关联子表
                    sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID=@CN_PARENT_GUID";
                    ExecuteTranSql(sql, trayItem.TrayItemDtlList, trans);
                    //if (trayLocation != null)
                    //{
                    //Log.Info("ConfirmSorting DropTrayItemQty", "删除托盘货位关联表");
                    //删除托盘货位关联表
                    //sql = @"DELETE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
                    //ExecuteTranSql(sql, trayLocation, trans);
                    //Log.Info("ConfirmSorting DropTrayItemQty", "修改货位贮存状态为空");
                    //修改货位贮存状态为空
                    //sql = @"UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='" + Constants.Use_State_Empty + "' WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE";
                    //ExecuteTranSql(sql, trayLocation, trans);
                    //}
                }
                else
                {
                    Log.Info("ConfirmSorting DropTrayItemQty", "下降托盘物料关联主表数量，分配量");
                    //下降托盘物料关联主表数量，分配量
                    sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_F_INQTY=@CN_F_QUANTITY,CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY WHERE CN_GUID=@CN_GUID";
                    ExecuteTranSql(sql, trayItem, trans);
                    foreach (var trayItemDtl in trayItem.TrayItemDtlList)
                    {
                        if (trayItemDtl.CN_F_QUANTITY == 0)
                        {
                            Log.Info("ConfirmSorting DropTrayItemQty", "删除托盘物料关联子表");
                            //删除托盘物料关联子表
                            sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_GUID=@CN_GUID";
                            ExecuteTranSql(sql, trayItemDtl, trans);
                        }
                        else
                        {
                            Log.Info("ConfirmSorting DropTrayItemQty", "下降托盘物料关联子表数量");
                            //下降托盘物料关联子表数量
                            sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_F_PACKING_QTY=@CN_F_PACKING_QTY,CN_S_PACKING_UNIT=@CN_S_PACKING_UNIT WHERE CN_GUID=@CN_GUID";
                            ExecuteTranSql(sql, trayItemDtl, trans);
                        }
                    }
                }
            }
            return OperateResult.Succeed();
        }
        #endregion

        #region 清除托盘货位绑定
        /// <summary>
        /// 清除托盘货位绑定
        /// </summary>
        /// <param name="trayLocation"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ClearLocationByTrayCode(TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
        {
            //删除托盘货位关联表
            string sql = @"DELETE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
            ExecuteTranSql(sql, trayLocation, trans);
            Log.Info("ConfirmSorting DropTrayItemQty", "修改货位贮存状态为空");
            //修改货位贮存状态为空
            sql = @"UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='" + Constants.Use_State_Empty + "' WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE";
            ExecuteTranSql(sql, trayLocation, trans);
            return OperateResult.Succeed();
        }
        #endregion

        #region 下降托盘物料关联数量(数量为0删除)
        /// <summary>
        /// 下降托盘物料关联数量(数量为0删除)
        /// </summary>
        /// <param name="trayItemList"></param>
        /// <param name="trayLocations"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult DropTrayItemQtys(List<TN_WM_B_TRAY_ITEM_MSTEntity> trayItemList, List<TN_WM_B_TRAY_LOCATIONEntity> trayLocations, IDbTransaction trans)
        {
            if (trayItemList == null)
                return OperateResult.Succeed();
            string sql = string.Empty;
            foreach (var trayItem in trayItemList)
            {
                if (trayItem.CN_F_QUANTITY == 0)
                {
                    //删除托盘物料关联主表
                    sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_GUID=@CN_GUID";
                    ExecuteTranSql(sql, trayItem, trans);
                    //删除托盘物料关联子表
                    sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID=@CN_PARENT_GUID";
                    ExecuteTranSql(sql, trayItem.TrayItemDtlList, trans);
                    if (trayLocations != null)
                    {
                        var currentTrayLocation = trayLocations.Find(x => x.CN_S_TRAY_CODE == trayItem.CN_S_TRAY_CODE);
                        if (currentTrayLocation != null)
                        {
                            //删除托盘货位关联表
                            sql = @"DELETE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
                            ExecuteTranSql(sql, currentTrayLocation, trans);
                            //修改货位贮存状态为空
                            sql = @"UPDATE TN_WM_B_LOCATION_EXT SET CN_S_USE_STATE='" + Constants.Use_State_Empty + "' WHERE CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE";
                            ExecuteTranSql(sql, currentTrayLocation, trans);
                        }
                    }
                }
                else
                {
                    //下降托盘物料关联主表数量，分配量
                    sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY WHERE CN_GUID=@CN_GUID";
                    ExecuteTranSql(sql, trayItem, trans);
                    foreach (var trayItemDtl in trayItem.TrayItemDtlList)
                    {
                        if (trayItemDtl.CN_F_QUANTITY == 0)
                        {
                            //删除托盘物料关联子表
                            sql = @"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_GUID=@CN_GUID";
                            ExecuteTranSql(sql, trayItemDtl, trans);
                        }
                        else
                        {
                            //下降托盘物料关联子表数量
                            sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=@CN_F_QUANTITY WHERE CN_GUID=@CN_GUID";
                            ExecuteTranSql(sql, trayItemDtl, trans);
                        }
                    }
                }
            }
            return OperateResult.Succeed();
        }
        #endregion

        #region 新增分拣单的主子表
        /// <summary>
        /// 新增分拣单的主子表
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] created 2018-5-14</History>
        public SqlExecuteResult Add(TN_WM_SORTING_DTLEntity entity, DbTransaction trans)
        {
            string sql = @"INSERT INTO tn_wm_sorting_dtl
                                   (CN_GUID
                                   ,CN_S_SORTING_NO
                                   ,CN_S_CREATOR
                                   ,CN_N_ROW_NO
                                   ,CN_S_CREATOR_BY
                                   ,CN_S_SERIAL_NO
                                   ,CN_T_CREATE
                                   ,CN_S_MODIFY
                                   ,CN_S_ITEM_CODE
                                   ,CN_S_ITEM_NAME
                                   ,CN_S_MODIFY_BY
                                   ,CN_F_QUANTITY
                                   ,CN_T_MODIFY
                                   ,CN_S_STATE
                                   ,CN_S_MSTGUID,CN_S_LOT_NO,CN_S_OWNER,CN_S_ITEM_STATE,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT)
                             VALUES
                                   (:CN_GUID,:CN_S_SORTING_NO,:CN_S_CREATOR,:CN_N_ROW_NO,:CN_S_CREATOR_BY,:CN_S_SERIAL_NO,
                                    :CN_T_CREATE,:CN_S_MODIFY,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_MODIFY_BY,:CN_F_QUANTITY,
                                    :CN_T_MODIFY,:CN_S_STATE,:CN_S_MSTGUID,:CN_S_LOT_NO,:CN_S_OWNER,:CN_S_ITEM_STATE,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_S_MEASURE_UNIT)";

            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
            this.DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.INT, entity.CN_N_ROW_NO);
            this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
            this.DataAccess.AddInParameter(cmd, "CN_S_SORTING_NO", ComDbType.STRING, entity.CN_S_SORTING_NO);
            this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
            this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
            this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
            this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
            this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
            this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
            this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
            this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
            this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
            this.DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, entity.CN_S_STATE);
            this.DataAccess.AddInParameter(cmd, "CN_S_MSTGUID", ComDbType.STRING, entity.CN_S_MSTGUID);
            this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
            this.DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
            this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
            this.DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
            this.DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
            this.DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
            return ExecuteCommand(cmd, trans);
        }


        /// <summary>
        /// 批量新增分拣单子表
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[Hanhe(lt)] created 2018-10-8</History>
        public SqlExecuteResult batchAdd(List<TN_WM_SORTING_DTLEntity> list, DbTransaction trans)
        {
            string sql = @"INSERT INTO tn_wm_sorting_dtl
                                   (CN_GUID
                                   ,CN_S_SORTING_NO
                                   ,CN_S_CREATOR
                                   ,CN_N_ROW_NO
                                   ,CN_S_CREATOR_BY
                                   ,CN_S_SERIAL_NO
                                   ,CN_T_CREATE
                                   ,CN_S_MODIFY
                                   ,CN_S_ITEM_CODE
                                   ,CN_S_ITEM_NAME
                                   ,CN_S_MODIFY_BY
                                   ,CN_F_QUANTITY
                                   ,CN_T_MODIFY
                                   ,CN_S_STATE
                                   ,CN_S_MSTGUID,CN_S_LOT_NO,CN_S_OWNER,CN_S_ITEM_STATE,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT)
                             VALUES
                                   (:CN_GUID,:CN_S_SORTING_NO,:CN_S_CREATOR,:CN_N_ROW_NO,:CN_S_CREATOR_BY,:CN_S_SERIAL_NO,
                                    :CN_T_CREATE,:CN_S_MODIFY,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_MODIFY_BY,:CN_F_QUANTITY,
                                    :CN_T_MODIFY,:CN_S_STATE,:CN_S_MSTGUID,:CN_S_LOT_NO,:CN_S_OWNER,:CN_S_ITEM_STATE,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_S_MEASURE_UNIT)";

            List<DbCommand> cmdlist = new List<DbCommand>();
            foreach (TN_WM_SORTING_DTLEntity entity in list)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
                this.DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.INT, entity.CN_N_ROW_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_SORTING_NO", ComDbType.STRING, entity.CN_S_SORTING_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
                this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
                this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
                this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
                this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
                this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
                this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
                this.DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, entity.CN_S_STATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_MSTGUID", ComDbType.STRING, entity.CN_S_MSTGUID);
                this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
                this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
                this.DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
                this.DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
                this.DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
                cmdlist.Add(cmd);
            }

            return ExecuteCommands(cmdlist, trans);
        }
        #endregion

        #region 批量新增分拣明细
        /// <summary>
        /// 批量新增分拣明细
        /// </summary>
        /// <param name="entitys">集合</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] created 2018-3-14</History>
        public SqlExecuteResult AddLocation(List<TN_WM_SORTING_LOCATIONEntity> entitys, DbTransaction trans)
        {
            string sql = @"INSERT INTO TN_WM_SORTING_LOCATION
                           (CN_S_SORTING_NO
                              ,CN_S_DEPARTMENT
                              ,CN_GUID
                              ,CN_S_CREATE_TYPE
                              ,CN_S_MODIFY
                              ,CN_S_FROM
                              ,CN_S_ORGAN
                              ,CN_N_ROW_NO
                              ,CN_S_MODIFY_BY
                              ,CN_T_MODIFY
                              ,CN_S_SERIAL_NO
                              ,CN_S_CREATOR
                              ,CN_S_ITEM_CODE
                              ,CN_S_CREATOR_BY
                              ,CN_S_ITEM_NAME
                              ,CN_T_CREATE
                              ,CN_F_QUANTITY
                              ,CN_S_STATUS
                              ,CN_S_LOCATION_CODE
                              ,CN_S_TRAY_CODE
                              ,CN_S_TRAY_GRID
                              ,CN_S_STATE
                              ,CN_S_SORTING_PERSON
                              ,CN_S_ORDER_NO,CN_S_LOT_NO,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT)
                     VALUES
                           (:CN_S_SORTING_NO,:CN_S_DEPARTMENT,:CN_GUID,:CN_S_CREATE_TYPE,:CN_S_MODIFY,:CN_S_FROM,
                            :CN_S_ORGAN,:CN_N_ROW_NO,:CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_S_SERIAL_NO,:CN_S_CREATOR,
                            :CN_S_ITEM_CODE,:CN_S_CREATOR_BY,:CN_S_ITEM_NAME,:CN_T_CREATE,:CN_F_QUANTITY,
                            :CN_S_STATUS,:CN_S_LOCATION_CODE,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_STATE,
                            :CN_S_SORTING_PERSON,:CN_S_ORDER_NO,:CN_S_LOT_NO,:CN_S_ITEM_STATE,:CN_S_OWNER,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_S_MEASURE_UNIT)";

            List<DbCommand> cmds = new List<DbCommand>();
            foreach (TN_WM_SORTING_LOCATIONEntity m in entitys)
            {
                DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
                DataAccess.AddInParameter(cmd, "CN_GUID", DbType.String, m.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_SORTING_NO", DbType.String, m.CN_S_SORTING_NO);
                DataAccess.AddInParameter(cmd, "CN_S_DEPARTMENT", DbType.String, m.CN_S_DEPARTMENT);
                DataAccess.AddInParameter(cmd, "CN_S_CREATE_TYPE", DbType.String, m.CN_S_CREATE_TYPE);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY", DbType.String, m.CN_S_MODIFY);
                DataAccess.AddInParameter(cmd, "CN_S_FROM", DbType.String, m.CN_S_FROM);
                DataAccess.AddInParameter(cmd, "CN_S_ORGAN", DbType.String, m.CN_S_ORGAN);
                DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", DbType.String, m.CN_N_ROW_NO);
                DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", DbType.String, m.CN_S_MODIFY_BY);
                DataAccess.AddInParameter(cmd, "CN_T_MODIFY", DbType.Date, m.CN_T_MODIFY);
                DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", DbType.String, m.CN_S_SERIAL_NO);
                DataAccess.AddInParameter(cmd, "CN_S_CREATOR", DbType.String, m.CN_S_CREATOR);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", DbType.String, m.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", DbType.String, m.CN_S_CREATOR_BY);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", DbType.String, m.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_T_CREATE", DbType.Date, m.CN_T_CREATE);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", DbType.Decimal, m.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_S_STATUS", DbType.String, m.CN_S_STATUS);
                DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", DbType.String, m.CN_S_LOCATION_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", DbType.String, m.CN_S_TRAY_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", DbType.String, m.CN_S_TRAY_GRID);
                DataAccess.AddInParameter(cmd, "CN_S_STATE", DbType.String, m.CN_S_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_SORTING_PERSON", DbType.String, m.CN_S_SORTING_PERSON);
                DataAccess.AddInParameter(cmd, "CN_S_ORDER_NO", DbType.String, m.CN_S_ORDER_NO);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", DbType.String, m.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", DbType.String, m.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", DbType.String, m.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_MODEL", DbType.String, m.CN_S_MODEL);
                DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", DbType.String, m.CN_S_FIGURE_NO);
                DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", DbType.String, m.CN_S_MEASURE_UNIT);
                cmds.Add(cmd);
            }
            return ExecuteCommands(cmds, trans);
        }
        #endregion

        #region 获取当前托盘分拣中的分拣单
        /// <summary>
        /// 获取当前托盘分拣中的分拣单
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        public List<TN_WM_SORTING_LISTEntity> GetPickSortingByTray(string trayCode)
        {
            string sql = @"SELECT * FROM TN_WM_SORTING_LIST WHERE CN_S_SORTING_NO IN(
SELECT A.CN_S_SORTING_NO FROM TN_WM_SORTING_LIST A,TN_WM_SORTING_LOCATION B
WHERE A.CN_S_SORTING_NO = B.CN_S_SORTING_NO AND A.CN_S_STATE=@CN_S_STATE AND B.CN_S_TRAY_CODE=@CN_S_TRAY_CODE)";
            return ExecuteQuery<TN_WM_SORTING_LISTEntity>(sql, new
            {
                CN_S_STATE = Constants.Sorting_Being,
                CN_S_TRAY_CODE = trayCode
            });
        }
        #endregion

        #region 出库单获取分拣明细
        /// <summary>
        /// 出库单获取分拣明细
        /// </summary>
        /// <param name="outNo"></param>
        /// <returns></returns>
        public List<TN_WM_SORTING_LOCATIONEntity> GetTrayLocationByOut(string outNo)
        {
            string sql = @"SELECT * FROM TN_WM_SORTING_LOCATION WHERE CN_S_SORTING_NO IN(
SELECT CN_S_SORTING_NO FROM TN_WM_SORTING_LIST WHERE CN_S_FROM_NO IN( SELECT CN_S_WAVE_CODE FROM TN_WM_OUT_MST WHERE CN_S_OP_NO=@CN_S_OP_NO))";
            return ExecuteQuery<TN_WM_SORTING_LOCATIONEntity>(sql, new
            {
                CN_S_OP_NO = outNo
            });
        }
        #endregion
    }
}