Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/Balance/BalanceOperateService.cs

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.BaseModel;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using DapperORMCore.String.Consts;
using DapperORMCore.String.Enums;
using Microsoft.Extensions.Configuration;
using NXWMS.Code;
using NXWMS.DataAccess.Entity;
using NXWMS.IService.NXWMS.Balance;
using NXWMS.Model.AppModels.Condition.Balance;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.OutStock;
using NXWMS.Model.Common;
using NXWMS.String.Enums;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WestDistance.DapperORM.Repository.Repositorys;

namespace NXWMS.Service.NXWMS.Balance
{
    /// <summary>
    /// 库存操作接口
    /// </summary>
    [AutoInject(typeof(IBalanceOperateService), InjectType.Scope)]
    public class BalanceOperateService : ServiceBase, IBalanceOperateService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

        /// <summary>
        /// SQL节点仓储
        /// </summary>
        private ISQLNodeRepository _iSQLNodeRepository;

        /// <summary>
        /// 配置
        /// </summary>
        private IConfiguration _configuration;

        /// <summary>
        /// 库存查询服务
        /// </summary>
        private IBalanceSearchService _balanceSearchService;

        public BalanceOperateService(IDataRepositoryContext dataRepositoryContext,
                    IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository, IBalanceSearchService balanceSearchService)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
            this._balanceSearchService = balanceSearchService;
        }

        /// <summary>
        /// 库存状态变更
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo BalanceStatusChange(BalanceStatusChangeCondition info)
        {
            if (info.BalanceId == 0 && string.IsNullOrWhiteSpace(info.BalanceIds))
            {
                return FailMessageStatus("参数错误!");
            }

            var sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = info.BalanceId != null ?
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_ID", info.BalanceId, DBOperationString._Equal)) :
                    sqlAndBuilder;
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BalanceIds) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_ID", info.BalanceIds, DBOperationString._In));
            if (sqlAndBuilder.Length == 0)
            {
                return FailMessageStatus("参数错误!");
            }
            var now = DateTime.Now;
            var sql = $@"UPDATE WMS_STK_BALANCE SET BALANCE_STATUS={(int)info.BalanceStatus},UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 {sqlAndBuilder}";
            var affectedRows = new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
            if (!string.IsNullOrWhiteSpace(info.BalanceIds))
            {
                return GetStatus(affectedRows, info.BalanceIds.Split(',').Length);
            }
            if (info.BalanceId != null)
            {
                return GetStatus(affectedRows, 1);
            }
            return SuccessStatus();
        }

        /// <summary>
        /// 盘点状态变更
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo InventoryStatusChange(InventoryStatusChangeCondition info)
        {
            var sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = info.Id != null ?
                    info.Id > 0 ?
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_ID", info.Id, DBOperationString._Equal)) :
                    sqlAndBuilder : sqlAndBuilder;
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.InventoryNo) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_NO", info.InventoryNo, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.Ids) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_ID", info.Ids, DBOperationString._In));
            if (sqlAndBuilder.Length == 0)
            {
                return FailMessageStatus("参数错误!");
            }
            try
            {
                _dataContext.BeginTran();
                var now = DateTime.Now;
                var sql = string.Empty;
                int statusBalance;
                if (info.InventoryModeMsg == "结束盘点")
                {
                    statusBalance = 55;
                }
                else if (info.InventoryModeMsg == "正常盘点")
                {
                    statusBalance = 88;
                }
                else
                {
                    statusBalance = 88;
                }
                if (info.InventoryModeMsg == "正常盘点" || info.InventoryModeMsg == "结束盘点")
                {
                    sql = $@"UPDATE WMS_STK_INVENTORY SET INVENTORY_STATUS={(int)info.InventoryStatus},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 {sqlAndBuilder}";
                    new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
                    sql = $@"UPDATE WMS_STK_INVENTORY_DTL SET INVENTORY_DTL_STATUS={(int)info.InventoryStatus},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND EXISTS(SELECT * FROM WMS_STK_INVENTORY WHERE 1=1 {sqlAndBuilder})";
                    new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
                }
                sql = $"update WMS_STK_BALANCE set BALANCE_STATUS = '{statusBalance}',UPDATE_BY = '{info.OperationUserId}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE in (select TRAY_CODE from WMS_STK_INVENTORY_DTL where INVENTORY_ID = '{info.Ids}') and BALANCE_STATUS < 99";
                new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
                sql = $"update WMS_STK_TRAY set TRAY_STATUS = '{statusBalance}',UPDATE_BY = '{info.OperationUserId}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE in (select TRAY_CODE from WMS_STK_INVENTORY_DTL where INVENTORY_ID = '{info.Ids}') and TRAY_STATUS < 99";
                new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
                sql = $"update WMS_STK_TRAY_DTL set TRAY_DTL_STATUS = '{statusBalance}',UPDATE_BY = '{info.OperationUserId}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_DTL_ID in (select TRAY_DTL_ID from WMS_STK_INVENTORY_DTL where INVENTORY_ID = '{info.Ids}') and TRAY_DTL_STATUS < 99";
                new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);
                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }
        }


        public OperateResultInfo ConfirmInventory(ResultConfirmInventoryCondition info)
        {
            try
            {


                _dataContext.BeginTran();
                var now = DateTime.Now;
                var sql = $@"UPDATE WMS_STK_INVENTORY SET INVENTORY_STATUS={(int)info.InventoryStatus},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND INVENTORY_NO='{info.InventoryNo}'";
                new DataRepository<WMS_STK_INVENTORY>(_dataContext).Execute(sql);

                foreach (var item in info.ResultConfirmInventoryDetail)
                {
                    sql = $@"UPDATE WMS_STK_INVENTORY_DTL SET ACTUAL_INVENTORY_QTY={item.ActualInventoryQty},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND INVENTORY_DTL_ID={item.InventoryDTLId}";
                    
                    new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Execute(sql);
                }

                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }


        }


        /// <summary>
        /// 盘点单结果确认
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo ResultConfirmInventory(ResultConfirmInventoryCondition info)
        {



            //TODO 暂时注释,,减少麻烦...
            //var whereList = new List<FieldKeyInfo>()
            //              .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY.INVENTORY_NO), info.InventoryNo,
            //                        EnumCSharpPropertyType.STRING, DBOperationString._Equal,
            //              !string.IsNullOrWhiteSpace(info.InventoryNo))
            //              .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY.RESULT_FLAG), 1,
            //                        EnumCSharpPropertyType.STRING, DBOperationString._NoEqual);
            //var result = new DataRepository<WMS_STK_INVENTORY>(_dataContext).Query(whereList).FirstOrDefault();
            //if (result == null)
            //{
            //    return FailMessageStatus($"盘点单数据出现变更,请重新刷新数据!");
            //}
            //if (result.INVENTORY_STATUS != (int)InventoryStatus.FirstInventorComplate
            //    && result.INVENTORY_STATUS != (int)InventoryStatus.TwoInventorComplate)
            //{
            //    return FailMessageStatus($"盘点单状态不在盘点完成,请重新刷新数据!");
            //}
             
            try
            {

               
                _dataContext.BeginTran();
                var now = DateTime.Now;
                var sql = $@"UPDATE WMS_STK_INVENTORY SET RESULT_FLAG=1,
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND INVENTORY_NO='{info.InventoryNo}'";
                new DataRepository<WMS_STK_INVENTORY>(_dataContext).Execute(sql);

                foreach(var item in info.ResultConfirmInventoryDetail)
                { 
                    sql = $@"UPDATE WMS_STK_INVENTORY_DTL SET  INVENTORY_RESULT_QTY={item.InventoryResultQTY},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND INVENTORY_DTL_ID={item.InventoryDTLId}";
                    new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Execute(sql);
                }

          
                sql = $@"update [dbo].[WMS_STK_BALANCE]
                        set BALANCE_STATUS=55 where PALLET_CODE in (
                        SELECT PALLET_CODE
                          FROM [dbo].[WMS_STK_INVENTORY_DTL] ,WMS_STK_INVENTORY where WMS_STK_INVENTORY.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID
                          and WMS_STK_INVENTORY.INVENTORY_NO='{info.InventoryNo}'
                          ) and BALANCE_STATUS={(int)BalanceStatus.Complete}";
                new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);

                sql = $@"update WMS_STK_TRAY_DTL set WMS_STK_TRAY_DTL.QTY=tbl2.INVENTORY_RESULT_QTY   from WMS_STK_TRAY_DTL,
                    (select tbl.TRAY_DTL_ID,tbl.MATERIEL_CODE,WMS_STK_INVENTORY_DTL.INVENTORY_RESULT_QTY,WMS_STK_INVENTORY.INVENTORY_NO from WMS_STK_INVENTORY_DTL
                    left join (
                    select WMS_STK_TRAY_DTL.TRAY_DTL_ID,WMS_STK_TRAY.TRAY_CODE,WMS_STK_TRAY_DTL.MATERIEL_CODE  from  WMS_STK_TRAY_DTL
                    left join WMS_STK_TRAY on WMS_STK_TRAY_DTL.TRAY_ID=WMS_STK_TRAY.TRAY_ID) as tbl on WMS_STK_INVENTORY_DTL.TRAY_CODE=tbl.TRAY_CODE
                    LEFT JOIN WMS_STK_INVENTORY ON WMS_STK_INVENTORY_DTL.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID
                    ) tbl2
                    where WMS_STK_TRAY_DTL.TRAY_DTL_ID=tbl2.TRAY_DTL_ID and tbl2.INVENTORY_NO='{info.InventoryNo}'";
                new DataRepository<WMS_STK_TRAY_DTL>(_dataContext).Execute(sql);
                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }
        }

        /// <summary>
        /// 新增盘点单
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo AddInventory(InventoryOperateCondition info)
        {
            var whereList = new List<FieldKeyInfo>()
                      .AddFieldKeyInfo(nameof(WMS_STK_BALANCE.BALANCE_ID), string.Join(",", info.InventoryAddDetailList.Select(s => s.BalanceId).ToList()),
                                EnumCSharpPropertyType.STRING, DBOperationString._In,
                      info.InventoryAddDetailList.Any())
                      .AddFieldKeyInfo(nameof(WMS_STK_BALANCE.BALANCE_STATUS), (int)BalanceStatus.InStock,
                                EnumCSharpPropertyType.STRING, DBOperationString._NoEqual, true);
            var result = new DataRepository<WMS_STK_BALANCE>(_dataContext).Query(whereList);
            if (result.Any())
            {
                return FailMessageStatus($"新增的盘点单明细中,【{string.Join(",", result.Select(s => s.BIN_CODE))}】库位均不是在库状态,请检查!");
            }
            try
            {
                _dataContext.BeginTran();
                var now = DateTime.Now;

                int inventoryId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inventory_Id"));
                string InventoryNo = new DataRepository<object>(_dataContext).GetSequenceMsg("Inventory_No");
                //盘点主表
                var entity = new WMS_STK_INVENTORY
                {
                
                    NEWID =Guid.NewGuid().ToString(),
                    RESULT_FLAG = (int)ResultFlag.UnConfirm,
                    INVENTORY_END_TIME = info.InventoryEndDate,
                    INVENTORY_MODE = info.InventoryMode,
                    UPDATE_BY = info.OperationUserId,
                    CREATE_BY = info.OperationUserId,
                    UPDATE_TIME = now,
                    CREATE_TIME = now,
                    INVENTORY_BEGIN_TIME = info.InventoryBeginDate,
                    INVENTORY_PERCENTAGE = info.InventoryPercentage,
                    INVENTORY_STATUS = (int)InventoryStatus.Init,
                    DESCRIBE = info.Describe,
                    INVENTORY_NO = InventoryNo,
                    INVENTORY_TYPE = info.InventoryType,
                    INVENTORY_ID = inventoryId,
                };
                new DataRepository<WMS_STK_INVENTORY>(_dataContext).Add(entity);

                var balanceList = _balanceSearchService.GetDetailList(new BalanceDetailSearchCondition
                {
                    Ids = string.Join(",", info.InventoryAddDetailList.Select(s => s.BalanceId).ToList())
                }).Data.RowData;
                if (balanceList.Count() != info.InventoryAddDetailList.Count)
                {
                    if (result.Any())
                    {
                        return FailMessageStatus($"当前库存数据出现变更,请重新检查!");
                    }
                }
                //盘点明细
                foreach (var item in balanceList)
                {
                    int inventoryDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inventory_Dtl_Id"));
                    var dtlEntity = new WMS_STK_INVENTORY_DTL
                    {
                        BIN_CODE = item.BIN_CODE,
                        INVENTORY_DTL_ID = inventoryDtlId,
                        INVENTORY_ID = inventoryId,
                        INVENTORY_DTL_STATUS = (int)InventoryDTLStatus.Init,
                        MATERIEL_BARCODE = info.InventoryAddDetailList.Where(s => s.BinCode == item.BIN_CODE).
                                            Select(s => s.MaterielBarCode).FirstOrDefault(),
                        MATERIEL_CODE = item.MaterielCode,
                        
                        PACKAGE_CODE = item.PalletCode,
                        PALLET_CODE = item.PalletCode,
                        REGION_CODE = item.REGION_CODE,
                        SUPPLIER_CODE = item.SupplierCode,
                        TRAY_DTL_ID = item.TrayDtlId,
                        TRAY_CODE = item.TRAY_CODE,
                        UNIT_CODE = item.UnitCode,
                        UPDATE_BY = info.OperationUserId,
                        CREATE_BY = info.OperationUserId,
                        UPDATE_TIME = now,
                        CREATE_TIME = now,
                        MATERIEL_NAME = item.MaterielName,
                        BATCH_NO = item.BatchNo,
                        BIN_NAME = item.BIN_NAME,
                        SUPPLIER_NAME = item.SupplierName,
                        MATERIEL_SPEC = item.SpecsModel,
                        QTY = item.QTY,
                        REGION_NAME = item.REGION_NAME,
                    };
                    new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Add(dtlEntity);
                }
                #region 20210218 孙亚龙舍弃。这里不需要修改库存。
                //更新库存状态  
                //BalanceStatusChange(new BalanceStatusChangeCondition
                //{
                //    BalanceStatus = BalanceStatus.CheckOutStock,
                //    BalanceIds = string.Join(",", info.InventoryAddDetailList.Select(s => s.BalanceId).ToList())
                //}); 
                #endregion
                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }
        }

        /// <summary>
        /// 编辑盘点单
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo EditInventory(InventoryOperateCondition info)
        {

            if (info.InventoryId == null || string.IsNullOrWhiteSpace(info.InventoryNo))
            {
                return FailMessageStatus($"参数错误!");
            }
            var whereList = new List<FieldKeyInfo>()
                      .AddFieldKeyInfo(nameof(WMS_STK_BALANCE.BALANCE_ID), string.Join(",", info.InventoryAddDetailList.
                      Where(s => s.BalanceId != null).Select(s => s.BalanceId).ToList()),
                                EnumCSharpPropertyType.STRING, DBOperationString._In,
                      string.Join(",", info.InventoryAddDetailList.
                      Where(s => s.BalanceId != null).Select(s => s.BalanceId).ToList()).Any())
                      .AddFieldKeyInfo(nameof(WMS_STK_BALANCE.BALANCE_STATUS), (int)BalanceStatus.InStock,
                                EnumCSharpPropertyType.STRING, DBOperationString._NoEqual, true);
            var result = new DataRepository<WMS_STK_BALANCE>(_dataContext).Query(whereList);
            if (result.Any())
            {
                return FailMessageStatus($"编辑的盘点单明细中,【{string.Join(",", result.Select(s => s.BIN_CODE))}】库位均不是在库状态,请检查!");
            }
            //TODO只做这个验证了..单据中状态验证 后期填补
            if (info.InventoryId != null)
            {
                var inventoryTemp = new DataRepository<WMS_STK_INVENTORY>(_dataContext).
                                Query("INVENTORY_ID", info.InventoryId.ToString()).FirstOrDefault();
                if (inventoryTemp == null)
                {
                    return FailMessageStatus($"参数错误");
                }

                switch (inventoryTemp.INVENTORY_STATUS)
                {
                    case (int)InventoryStatus.Deleted:
                        return FailMessageStatus($"盘点单已被删除,无法编辑,请检查!");
                        //TODO 这里可以填补其他验证
                }
            }
            if (string.IsNullOrWhiteSpace(info.InventoryNo))
            {
                var inventoryTemp = new DataRepository<WMS_STK_INVENTORY>(_dataContext).
                                Query("INVENTORY_NO", info.InventoryNo).FirstOrDefault();
                if (inventoryTemp == null)
                {
                    return FailMessageStatus($"参数错误");
                }
                switch (inventoryTemp.INVENTORY_STATUS)
                {
                    case (int)InventoryStatus.Deleted:
                        return FailMessageStatus($"盘点单明细已被删除,无法编辑,请检查!");
                        //TODO 这里可以填补其他验证,单据中状态验证 后期填补
                }
            }

            try
            {
                _dataContext.BeginTran();

                var now = DateTime.Now;
                var entity = new WMS_STK_INVENTORY();
                if (info.InventoryId != null)
                {
                    entity = new DataRepository<WMS_STK_INVENTORY>(_dataContext).
                                        Query("INVENTORY_ID", info.InventoryId.ToString()).FirstOrDefault();
                }
                else
                {
                    entity = new DataRepository<WMS_STK_INVENTORY>(_dataContext).
                                        Query("INVENTORY_NO", info.InventoryNo).FirstOrDefault();
                }
                //盘点主表
                entity = new WMS_STK_INVENTORY
                {
                    RESULT_FLAG = (int)ResultFlag.UnConfirm,
                    INVENTORY_END_TIME = info.InventoryEndDate,
                    INVENTORY_MODE = info.InventoryMode,
                    UPDATE_BY = info.OperationUserId,
                    CREATE_BY = info.OperationUserId,
                    UPDATE_TIME = now,
                    CREATE_TIME = now,
                    INVENTORY_BEGIN_TIME = info.InventoryBeginDate,
                    INVENTORY_PERCENTAGE = info.InventoryPercentage,
                    //盘点单主表状态不变更
                    //INVENTORY_STATUS = info.InventoryStatus == null ? (int)InventoryStatus.Init : (int)info.InventoryStatus,
                    DESCRIBE = info.Describe,
                    INVENTORY_NO = info.InventoryNo,
                    INVENTORY_TYPE = info.InventoryType,
                    INVENTORY_ID = entity.INVENTORY_ID,
                };
                new DataRepository<WMS_STK_INVENTORY>(_dataContext).Update(entity, "INVENTORY_ID", "NEWID");

                if (info.InventoryAddDetailList.Select(s => s.BalanceId).Distinct().Any())
                {
                    var balanceList = _balanceSearchService.GetDetailList(new BalanceDetailSearchCondition
                    {
                        Ids = string.Join(",", info.InventoryAddDetailList.Select(s => s.BalanceId).ToList())
                    }).Data.RowData;
                    if (balanceList.Count() != info.InventoryAddDetailList.Count)
                    {
                        return FailMessageStatus($"当前库存数据出现变更,请检查!");
                    }
                    //之前操作的明细更新成删除状态,后面的明细进行产生新的数据
                    var sql = $@"UPDATE WMS_STK_INVENTORY_DTL SET INVENTORY_DTL_STATUS={((int)InventoryStatus.Deleted).ToString()},
                        UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE 1=1 AND EXISTS(SELECT * FROM WMS_STK_INVENTORY WHERE 1=1 INVENTORY_ID={entity.INVENTORY_ID})";
                    new DataRepository<WMS_STK_BALANCE>(_dataContext).Execute(sql);

                    //盘点明细
                    foreach (var item in balanceList)
                    {
                        var detailInfo = info.InventoryAddDetailList.Where(s => s.BalanceId == item.BALANCE_ID).FirstOrDefault();

                        whereList = new List<FieldKeyInfo>()
                                      .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY_DTL.INVENTORY_ID), entity.INVENTORY_ID,
                                                EnumCSharpPropertyType.INT, DBOperationString._Equal)
                                      //TODO　以下三个条件确定 盘点明细表的唯一性
                                      .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY_DTL.BIN_CODE), item.BIN_CODE,
                                                EnumCSharpPropertyType.INT, DBOperationString._Equal, EnumSelectWhereType.And, 1, true)
                                      .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY_DTL.TRAY_CODE), item.TRAY_CODE,
                                                EnumCSharpPropertyType.INT, DBOperationString._Equal, EnumSelectWhereType.And, 1, true)
                                      .AddFieldKeyInfo(nameof(WMS_STK_INVENTORY_DTL.PALLET_CODE), item.PALLET_CODE,
                                                EnumCSharpPropertyType.INT, DBOperationString._Equal, EnumSelectWhereType.And, 1, true);

                        var detailEntity = new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Query(whereList);
                        //不存在,说明需要新增
                        if (detailEntity == null)
                        {
                            int inventoryDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inventory_Dtl_Id"));
                            var dtlEntity = new WMS_STK_INVENTORY_DTL
                            {
                                BIN_CODE = item.BIN_CODE,
                                INVENTORY_DTL_ID = inventoryDtlId,
                                INVENTORY_ID = entity.INVENTORY_ID,
                                INVENTORY_DTL_STATUS = detailInfo.InventoryDTLStatus == null ? (int)InventoryDTLStatus.Init : (int)detailInfo.InventoryDTLStatus,
                                MATERIEL_BARCODE = info.InventoryAddDetailList.Where(s => s.BinCode == item.BIN_CODE).
                                                    Select(s => s.MaterielBarCode).FirstOrDefault(),
                                MATERIEL_CODE = item.MaterielCode,
                                PACKAGE_CODE = item.PalletCode,
                                PALLET_CODE = item.PalletCode,
                                REGION_CODE = item.REGION_CODE,
                                SUPPLIER_CODE = item.SupplierCode,
                                TRAY_CODE = item.TRAY_CODE,
                                UNIT_CODE = item.UnitCode,
                                UPDATE_BY = info.OperationUserId,
                                CREATE_BY = info.OperationUserId,
                                UPDATE_TIME = now,
                                CREATE_TIME = now,
                                MATERIEL_NAME = item.MaterielName,
                                BATCH_NO = item.BatchNo,
                                BIN_NAME = item.BIN_NAME,
                                SUPPLIER_NAME = item.SupplierName,
                                MATERIEL_SPEC = item.SpecsModel,
                                QTY = item.QTY,
                                REGION_NAME = item.REGION_NAME,
                            };
                            new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Add(dtlEntity);
                        }
                        //存在需要修改
                        else
                        {
                            var dtlEntity = new WMS_STK_INVENTORY_DTL
                            {
                                BIN_CODE = item.BIN_CODE,
                                INVENTORY_DTL_ID = detailInfo.InventoryDTLId.Value,
                                INVENTORY_ID = entity.INVENTORY_ID,
                                //盘点单明细表状态不变更
                                //INVENTORY_DTL_STATUS = detailInfo.InventoryDTLStatus == null ? (int)InventoryDTLStatus.Init : (int)detailInfo.InventoryDTLStatus,
                                MATERIEL_BARCODE = info.InventoryAddDetailList.Where(s => s.BinCode == item.BIN_CODE).
                                                    Select(s => s.MaterielBarCode).FirstOrDefault(),
                                MATERIEL_CODE = item.MaterielCode,
                                PACKAGE_CODE = item.PalletCode,
                                PALLET_CODE = item.PalletCode,
                                REGION_CODE = item.REGION_CODE,
                                SUPPLIER_CODE = item.SupplierCode,
                                TRAY_CODE = item.TRAY_CODE,
                                UNIT_CODE = item.UnitCode,
                                UPDATE_BY = info.OperationUserId,
                                CREATE_BY = info.OperationUserId,
                                UPDATE_TIME = now,
                                CREATE_TIME = now,
                                MATERIEL_NAME = item.MaterielName,
                                BATCH_NO = item.BatchNo,
                                BIN_NAME = item.BIN_NAME,
                                SUPPLIER_NAME = item.SupplierName,
                                MATERIEL_SPEC = item.SpecsModel,
                                QTY = item.QTY,
                                REGION_NAME = item.REGION_NAME,
                            };
                            new DataRepository<WMS_STK_INVENTORY_DTL>(_dataContext).Update(entity, "INVENTORY_DTL_ID", "NEWID");
                        }
                    }
                    //更新库存状态
                    BalanceStatusChange(new BalanceStatusChangeCondition
                    {
                        BalanceStatus = BalanceStatus.CheckOutStock,
                        BalanceIds = string.Join(",", info.InventoryAddDetailList.Select(s => s.BalanceId).ToList())
                    });
                }
                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }
        }

        #region 20210218 孙亚龙新增盘点操作代码
        public OperateResultInfo<List<WmsAwitInventoryPalletMd>> GetAwitInventoryPalletLstData(WmsAwitInventoryPalletMdCondition paraMd)
        {
            try
            {
                if (paraMd.InventoryModeMsg == "正常盘点")
                {
                    string sql = $@"select * from VW_WMS_STK_NORMAL_INVENTORY_PALLET ORDER BY INVENTORY_ID";
                    List<WmsAwitInventoryPalletMd> resultList = new DataRepository<WmsAwitInventoryPalletMd>(_dataContext).Query(sql).ToList();
                    OperateResultInfo<List<WmsAwitInventoryPalletMd>> retDataMsg = SuccessStatus(resultList);

                    return retDataMsg;
                }
                else
                {
                    string sql = $@"select * from VW_WMS_STK_DIFFERENCE_INVENTORY_PALLET ORDER BY INVENTORY_ID";
                    List<WmsAwitInventoryPalletMd> resultList = new DataRepository<WmsAwitInventoryPalletMd>(_dataContext).Query(sql).ToList();
                    OperateResultInfo<List<WmsAwitInventoryPalletMd>> retDataMsg = SuccessStatus(resultList);

                    return retDataMsg;
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsAwitInventoryPalletMd>>($"获取待盘点托盘数据发生异常！【{ex.Message}】", null);
            }
        }
        public OperateResultInfo CreateInventoryTask(InventoryOperateCondition paraMd)
        {
            try
            {
                if (paraMd == null)
                {
                    return FailMessageStatus("传入参数异常！");
                }
                string[] arrTrayCode = paraMd.Describe.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in arrTrayCode)
                {
                    string sqlQueryBalanceDtl = $@"
                                                SELECT
	                                                *
                                                FROM
	                                                VW_WMS_STK_BALANCE_DTL
                                                WHERE
	                                                TRAY_CODE = '{item}'
                                            ";
                    List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                    int putdownId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Id"));
                    string putdownNo = new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_No");
                    string sqlAddPutDown = $@"
                                                INSERT INTO [WMS_OUT_PUTDOWN] (
	                                                [PUTDOWN_ID],
	                                                [PUTDOWN_NO],
	                                                [PUTDOWN_TYPE],
	                                                [TRAY_CODE],
	                                                [PALLET_CODE],
	                                                [SBIN_CODE],
	                                                [EBIN_CODE],
	                                                [PUTDOWN_PRIORITY],
	                                                [PUTDOWN_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5],
	                                                [SOURCE_NO]
                                                )
                                                VALUES
	                                                (
		                                                '{putdownId}',
		                                                '{putdownNo}',
		                                                '{5}',
		                                                '{item}',
		                                                '{balanceDtlData[0].PALLET_CODE}',
		                                                '{balanceDtlData[0].BIN_CODE}',
		                                                NULL,
		                                                '{100}',
		                                                '{0}',
		                                                NULL,
		                                                '{paraMd.OperationUserId}',
		                                                getdate(),
		                                                '{paraMd.OperationUserId}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                '{paraMd.InventoryNo}'
	                                                );
                                                ";
                    sqlList.Add(sqlAddPutDown);
                    string taskNo = new DataRepository<object>(_dataContext).GetSequenceMsg("Task_No");
                    string sqlInsertWmsTask = $@"
                                            INSERT INTO [dbo].[WMS_TSK_TASK] (
	                                            [WAREHOUSE_CODE],
	                                            [WAREHOUSE_NAME],
	                                            [TASK_NO],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [TRAY_LOADED_TYPE],
	                                            [SLOC_CODE],
	                                            [ELOC_CODE],
	                                            [CLOC_CODE],
	                                            [TASK_TYPE],
	                                            [TASK_MSG],
	                                            [ERR_FLAG],
	                                            [TASK_PRIORITY],
	                                            [TASK_STATUS],
	                                            [SBIN_CODE],
	                                            [EBIN_CODE],
	                                            [CMD_NO],
	                                            [ROUTE_CODE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
                                            (
	                                            'WarehouseTest1',
	                                            'WarehouseTest1',
	                                            '{taskNo}',
	                                            '{item}',
	                                            '{balanceDtlData[0].PALLET_CODE}',
	                                            '{1}',
	                                            '{1012}',
	                                            '{1008}',
	                                            '{1012}',
	                                            '{8}',
	                                            NULL,
	                                            '{0}',
	                                            '{100}',
	                                            '{0}',
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            '',
	                                            '{paraMd.OperationUserId}',
	                                            getdate(),
	                                            '{paraMd.OperationUserId}',
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                         ";
                    sqlList.Add(sqlInsertWmsTask);
                    foreach (WmsStkBalanceDtlResult balanceDtlMd in balanceDtlData)
                    {
                        int putdownDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Dtl_Id"));
                        string sqlAddPutDownDtl = $@"
                                                        INSERT INTO [WMS_OUT_PUTDOWN_DTL] (
	                                                        [PUTDOWN_DTL_ID],
	                                                        [PUTDOWN_ID],
	                                                        [MATERIEL_CODE],
	                                                        [MATERIEL_NAME],
	                                                        [MATERIEL_BARCODE],
	                                                        [MATERIEL_SPEC],
	                                                        [PACKAGE_CODE],
	                                                        [UNIT_CODE],
	                                                        [BATCH_NO],
	                                                        [SUPPLIER_CODE],
	                                                        [SUPPLIER_NAME],
	                                                        [PUTDOWN_QTY],
	                                                        [PRODUCT_DATE],
	                                                        [EXP_DATE],
	                                                        [PUTDOWN_DTL_STATUS],
	                                                        [INSPECTION_RESULT],
	                                                        [ITEM_STATUS],
	                                                        [DESCRIBE],
	                                                        [CREATE_BY],
	                                                        [CREATE_TIME],
	                                                        [UPDATE_BY],
	                                                        [UPDATE_TIME],
	                                                        [DATA_VERSION],
	                                                        [REMARKS1],
	                                                        [REMARKS2],
	                                                        [REMARKS3],
	                                                        [REMARKS4],
	                                                        [REMARKS5]
                                                        )
                                                        VALUES
	                                                        (
		                                                        '{putdownDtlId}',
		                                                        '{putdownId}',
		                                                        '{balanceDtlMd.MATERIEL_CODE}',
		                                                        '{balanceDtlMd.MATERIEL_NAME}',
		                                                        '{balanceDtlMd.MATERIEL_BARCODE}',
		                                                        '{balanceDtlMd.MATERIEL_SPEC}',
		                                                        '{balanceDtlMd.PACKAGE_CODE}',
		                                                        '{balanceDtlMd.UNIT_CODE}',
		                                                        '{balanceDtlMd.BATCH_NO}',
		                                                        '{balanceDtlMd.SUPPLIER_CODE}',
		                                                        '{balanceDtlMd.SUPPLIER_NAME}',
		                                                        '{balanceDtlMd.QTY}',
		                                                        '{balanceDtlMd.PRODUCT_DATE}',
		                                                        '{balanceDtlMd.EXP_DATE}',
		                                                        '{0}',
		                                                        '{balanceDtlMd.INSPECTION_RESULT}',
		                                                        '{balanceDtlMd.ITEM_STATUS}',
		                                                        NULL,
		                                                        '{paraMd.OperationUserId}',
		                                                        getdate(),
		                                                        '{paraMd.OperationUserId}',
		                                                        getdate(),
		                                                        0,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL
	                                                        );
                                                        ";
                        sqlList.Add(sqlAddPutDownDtl);
                    }
                }
                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("创建盘点任务成功！", row);
                }
                else
                {
                    return FailMessageStatus("创建盘点任务失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus("创建盘点任务发生异常！" + ex.Message);
            }
        }
        public OperateResultInfo<List<InventoryDetailSearchResult>> GetInventoryPallletOperateData(WmsAwitInventoryPalletMd paraMd)
        {
            try
            {
                string sqlQueryWmsOutPutDown = $@"
                                                SELECT
	                                                PUTDOWN_ID,
	                                                PUTDOWN_NO,
	                                                SOURCE_NO,
	                                                PUTDOWN_TYPE,
	                                                PUTDOWN_TYPE_NAME,
                                                    TRAY_CODE,
	                                                PALLET_CODE,
	                                                SBIN_CODE,
	                                                EBIN_CODE,
	                                                PUTDOWN_PRIORITY,
	                                                PUTDOWN_STATUS,
	                                                PUTDOWN_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_OUT_PUTDOWN
                                                WHERE   
                                                    TRAY_CODE = '{paraMd.TRAY_CODE}'
                                                AND PUTDOWN_STATUS < 99
                                              ";
                List<WmsOutPutDownResult> resultList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryWmsOutPutDown).ToList();
                WmsOutPutDownResult wmsOutPutDown = resultList[0];
                if (resultList.Count > 0)
                {
                    if (wmsOutPutDown.PUTDOWN_STATUS < 55)
                    {
                        return FailMessageStatus<List<InventoryDetailSearchResult>>($"托盘号：【{paraMd.PALLET_CODE}】未下架，无法进行盘点操作！", null);
                    }
                }
                else
                {
                    return FailMessageStatus<List<InventoryDetailSearchResult>>($"托盘号：【{paraMd.PALLET_CODE}】未生成下架任务，无法进行盘点操作！", null);
                }
                string sql = $@"SELECT CreateName=(SELECT A.USER_NAME FROM SYS_USER A WHERE A.USER_ID=WMS_STK_INVENTORY.CREATE_BY),
                        UpdateName=(SELECT B.USER_NAME FROM SYS_USER B WHERE B.USER_ID=WMS_STK_INVENTORY.UPDATE_BY),
                        WMS_STK_INVENTORY_DTL.MATERIEL_CODE,WMS_STK_INVENTORY_DTL.MATERIEL_NAME,
                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_ID,
                        MaterielTypeCode = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE,MaterielTypeName = BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                        BAS_SUPPLIER.SUPPLIER_NAME,BATCH_NO,WMS_STK_INVENTORY_DTL.QTY,WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                        WMS_STK_INVENTORY_DTL.INVENTORY_RESULT_QTY,UnitName = BAS_UNIT.UNIT_NAME,WarehouseName = BAS_WAREHOUSE.WAREHOUSE_NAME,WarehouseCode = BAS_WAREHOUSE.WAREHOUSE_CODE,
                        BAS_BIN.AREA_NAME,
                        BAS_BIN.BIN_CODE,WMS_STK_INVENTORY_DTL.TRAY_CODE,WMS_STK_INVENTORY_DTL.PALLET_CODE,
                        InventoryDetailStatusName = {_iSQLNodeRepository.GetEnumIntCaseString<InventoryStatus>("INVENTORY_DTL_STATUS", "WMS_STK_INVENTORY_DTL")},
                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS,
                        CREATE_TIME=WMS_STK_INVENTORY.CREATE_TIME,UPDATE_TIME=WMS_STK_INVENTORY.UPDATE_TIME
                        FROM WMS_STK_INVENTORY 
                        left JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID=WMS_STK_INVENTORY_DTL.INVENTORY_ID
                        left JOIN BAS_BIN ON BAS_BIN.BIN_CODE=WMS_STK_INVENTORY_DTL.BIN_CODE
                        left JOIN BAS_WAREHOUSE ON BAS_BIN.WAREHOUSE_CODE=BAS_WAREHOUSE.WAREHOUSE_CODE
                        left JOIN BAS_AREA ON BAS_AREA.AREA_CODE=BAS_BIN.AREA_CODE
                        left JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_STK_INVENTORY_DTL.MATERIEL_CODE
                        left JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE=BAS_MATERIEL.MATERIEL_TYPE
                        LEFT JOIN BAS_SUPPLIER ON BAS_SUPPLIER.SUPPLIER_CODE=WMS_STK_INVENTORY_DTL.SUPPLIER_CODE
                        left JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=WMS_STK_INVENTORY_DTL.UNIT_CODE 
                        WHERE 1=1 AND WMS_STK_INVENTORY.INVENTORY_ID = '{paraMd.INVENTORY_ID}' AND WMS_STK_INVENTORY_DTL.TRAY_CODE = '{paraMd.TRAY_CODE}'";
                if (paraMd.BIN_NAME == "差异盘点")
                {
                    sql += " AND WMS_STK_INVENTORY_DTL.QTY <> WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY";
                }
                var results = new DataRepository<InventoryDetailSearchResult>(_dataContext).Query(sql);
                return SuccessStatus(results.ToList());
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<InventoryDetailSearchResult>>($"获取待盘点托盘明细数据发生异常！【{ex.Message}】", null);
            }
        }
        public OperateResultInfo ConfirmInventoryPalletData(InventoryMainSearchResult paraMd)
        {
            try
            {
                List<string> sqlList = new List<string>();
                bool isDiff = false;
                foreach (var item in paraMd.InventoryDtlLst)
                {
                    string statusDtl = string.Empty;
                    if (paraMd.InventoryModeName == "正常盘点")
                    {
                        statusDtl = "88";
                    }
                    else
                    {
                        statusDtl = "99";
                    }
                    sqlList.Add($"update WMS_STK_INVENTORY_DTL set INVENTORY_DTL_STATUS = '{statusDtl}',ACTUAL_INVENTORY_QTY = '{item.ACTUAL_INVENTORY_QTY}',UPDATE_BY = '{paraMd.UPDATE_BY}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where INVENTORY_DTL_ID = '{item.INVENTORY_DTL_ID}'");
                    if (item.QTY != item.ACTUAL_INVENTORY_QTY)
                    {
                        isDiff = true;
                    }
                }

                #region Todo: 修改库存状态为盘点入库占用状态，同时生成大任务数据，同时生成盘点上架单数据。
                string sqlUpdatePutDown = $@"
                                            UPDATE WMS_OUT_PUTDOWN
                                            SET PUTDOWN_STATUS = '{99}',
                                            UPDATE_BY = '{paraMd.UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}' AND PUTDOWN_STATUS < 99;
                                          ";
                sqlList.Add(sqlUpdatePutDown);
                string sqlUpdatePutDownDtl = $@"
                                            UPDATE WMS_OUT_PUTDOWN_DTL
                                            SET PUTDOWN_DTL_STATUS = '{99}',
                                            UPDATE_BY = '{paraMd.UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID = (select PUTDOWN_ID from WMS_OUT_PUTDOWN where TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}' AND PUTDOWN_STATUS < 99);
                                          ";
                sqlList.Add(sqlUpdatePutDownDtl);
                string sqlUpdateWmsTask = $@"
                                            UPDATE WMS_TSK_TASK
                                            SET CLOC_CODE = ELOC_CODE,
                                                UPDATE_BY = '{paraMd.UPDATE_BY}',
                                                UPDATE_TIME = GETDATE(),
                                                DATA_VERSION = DATA_VERSION + 1,
                                            TASK_STATUS = 99,
                                            TASK_MSG = '手动更新WMS大任务为：【任务完成】状态'
                                            WHERE
                                                TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}'
                                            AND TASK_STATUS < 99
                                         ";
                sqlList.Add(sqlUpdateWmsTask);
                string sqlQueryBalanceDtl = $@"
                                                SELECT
	                                                *
                                                FROM
	                                                VW_WMS_STK_BALANCE_DTL
                                                WHERE
	                                                TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}'
                                            ";
                List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                bool isAddPutDownAndWmsTaskData = true;
                if (paraMd.InventoryModeName == "正常盘点")
                {
                    if (isDiff)
                    {
                        isAddPutDownAndWmsTaskData = false;
                    }
                }
                if (isAddPutDownAndWmsTaskData)
                {
                    int putawayId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_Id"));
                    string puawayNo = new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_No");
                    string sqlAddWmsInPutAway = $@"
                                                    INSERT INTO [WMS_IN_PUTAWAY] (
	                                                    [PUTAWAY_ID],
	                                                    [PUTAWAY_NO],
	                                                    [SOURCE_NO],
	                                                    [PUTAWAY_TYPE],
	                                                    [TRAY_CODE],
	                                                    [PALLET_CODE],
	                                                    [EBIN_CODE],
	                                                    [PUTAWAY_PRIORITY],
	                                                    [PUTAWAY_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{putawayId}',
		                                                    '{puawayNo}',
		                                                    '{paraMd.InventoryNo}',
		                                                    '{6}',
		                                                    '{paraMd.InventoryDtlLst[0].TRAY_CODE}',
		                                                    '{paraMd.InventoryDtlLst[0].PALLET_CODE}',
		                                                    '{paraMd.InventoryDtlLst[0].BIN_CODE}',
		                                                    '{100}',
		                                                    '{0}',
		                                                    NULL,
		                                                    '{paraMd.UPDATE_BY}',
		                                                    getdate(),
		                                                    '{paraMd.UPDATE_BY}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                                    ";
                    sqlList.Add(sqlAddWmsInPutAway);
                    string taskNo = new DataRepository<object>(_dataContext).GetSequenceMsg("Task_No");
                    string sqlInsertWmsTask = $@"
                                            INSERT INTO [dbo].[WMS_TSK_TASK] (
	                                            [WAREHOUSE_CODE],
	                                            [WAREHOUSE_NAME],
	                                            [TASK_NO],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [TRAY_LOADED_TYPE],
	                                            [SLOC_CODE],
	                                            [ELOC_CODE],
	                                            [CLOC_CODE],
	                                            [TASK_TYPE],
	                                            [TASK_MSG],
	                                            [ERR_FLAG],
	                                            [TASK_PRIORITY],
	                                            [TASK_STATUS],
	                                            [SBIN_CODE],
	                                            [EBIN_CODE],
	                                            [CMD_NO],
	                                            [ROUTE_CODE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
                                            (
	                                            'WarehouseTest1',
	                                            'WarehouseTest1',
	                                            '{taskNo}',
	                                            '{paraMd.InventoryDtlLst[0].TRAY_CODE}',
	                                            '{paraMd.InventoryDtlLst[0].PALLET_CODE}',
	                                            '{1}',
	                                            '{1005}',
	                                            '{1012}',
	                                            '{1005}',
	                                            '{4}',
	                                            NULL,
	                                            '{0}',
	                                            '{100}',
	                                            '{0}',
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            '',
	                                            '{paraMd.UPDATE_BY}',
	                                            getdate(),
	                                            '{paraMd.UPDATE_BY}',
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                         ";
                    sqlList.Add(sqlInsertWmsTask);
                    foreach (var item in balanceDtlData)
                    {
                        int putawayDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id"));
                        string sqlAddWmsInPutAwayDtl = $@"
                                                            INSERT INTO [WMS_IN_PUTAWAY_DTL] (
	                                                            [PUTAWAY_DTL_ID],
	                                                            [PUTAWAY_ID],
	                                                            [MATERIEL_CODE],
	                                                            [MATERIEL_NAME],
	                                                            [MATERIEL_BARCODE],
	                                                            [MATERIEL_SPEC],
	                                                            [BATCH_NO],
	                                                            [PACKAGE_CODE],
	                                                            [UNIT_CODE],
	                                                            [PUTAWAY_QTY],
	                                                            [SUPPLIER_CODE],
	                                                            [SUPPLIER_NAME],
	                                                            [PRODUCT_DATE],
	                                                            [EXP_DATE],
	                                                            [INSPECTION_RESULT],
	                                                            [PUTAWAY_DTL_STATUS],
	                                                            [ITEM_STATUS],
	                                                            [DESCRIBE],
	                                                            [CREATE_BY],
	                                                            [CREATE_TIME],
	                                                            [UPDATE_BY],
	                                                            [UPDATE_TIME],
	                                                            [DATA_VERSION],
	                                                            [REMARKS1],
	                                                            [REMARKS2],
	                                                            [REMARKS3],
	                                                            [REMARKS4],
	                                                            [REMARKS5]
                                                            )
                                                            VALUES
	                                                            (
		                                                            '{putawayDtlId}',
		                                                            '{putawayId}',
		                                                            '{item.MATERIEL_CODE}',
		                                                            '{item.MATERIEL_NAME}',
		                                                            '{item.MATERIEL_BARCODE}',
		                                                            '{item.MATERIEL_SPEC}',
		                                                            '{item.BATCH_NO}',
		                                                            '{item.PACKAGE_CODE}',
		                                                            '{item.UNIT_CODE}',
		                                                            '{item.QTY}',
		                                                            '{item.SUPPLIER_CODE}',
		                                                            '{item.SUPPLIER_NAME}',
		                                                            '{item.PRODUCT_DATE}',
		                                                            '{item.EXP_DATE}',
		                                                            '{item.INSPECTION_RESULT}',
		                                                            '{0}',
		                                                            '{item.ITEM_STATUS}',
		                                                            '{item.DESCRIBE}',
		                                                            '{paraMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            '{paraMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                        ";
                        sqlList.Add(sqlAddWmsInPutAwayDtl);
                    }
                    string sqlUpdateBalance = $"update WMS_STK_BALANCE set BALANCE_STATUS = '33',UPDATE_BY = '{paraMd.UPDATE_BY}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE in (select TRAY_CODE from WMS_STK_INVENTORY_DTL where TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}')";
                    string sqlUpdateTray = $"update WMS_STK_TRAY set TRAY_STATUS = '33',UPDATE_BY = '{paraMd.UPDATE_BY}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE in (select TRAY_CODE from WMS_STK_INVENTORY_DTL where TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}')";
                    string sqlUpdateTrayDtl = $"update WMS_STK_TRAY_DTL set TRAY_DTL_STATUS = '33',UPDATE_BY = '{paraMd.UPDATE_BY}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_DTL_ID in (select TRAY_DTL_ID from WMS_STK_INVENTORY_DTL where TRAY_CODE = '{paraMd.InventoryDtlLst[0].TRAY_CODE}')";
                    sqlList.Add(sqlUpdateBalance);
                    sqlList.Add(sqlUpdateTray);
                    sqlList.Add(sqlUpdateTrayDtl);
                }
                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                string status = string.Empty;
                if (paraMd.InventoryModeName == "正常盘点")
                {
                    status = "88";
                }
                else
                {
                    status = "99";
                }
                string sqlUpdateReceiptDtl = $@"
                                                IF NOT EXISTS (
	                                                SELECT
		                                                1
	                                                FROM
		                                                WMS_STK_INVENTORY_DTL
	                                                WHERE
		                                                INVENTORY_DTL_STATUS < '{status}'
	                                                AND INVENTORY_ID = '{paraMd.INVENTORY_ID}'
	                                                AND QTY <> ACTUAL_INVENTORY_QTY            
                                                ) UPDATE WMS_STK_INVENTORY
                                                SET INVENTORY_STATUS = '{status}',
                                                    UPDATE_BY = '{paraMd.UPDATE_BY}',
                                                    UPDATE_TIME = GETDATE(),
                                                    DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INVENTORY_ID = '{paraMd.INVENTORY_ID}' ;
                                                ";
                new DataRepository<object>(_dataContext).Execute(sqlUpdateReceiptDtl);
                if (paraMd.InventoryModeName != "正常盘点")
                {
                    string sqlUpdateInventoryDtl = $@"
                                                IF NOT EXISTS (
	                                                SELECT
		                                                1
	                                                FROM
		                                                WMS_STK_INVENTORY_DTL
	                                                WHERE
		                                                INVENTORY_DTL_STATUS < '{status}'
	                                                AND INVENTORY_ID = '{paraMd.INVENTORY_ID}'
	                                                AND QTY <> ACTUAL_INVENTORY_QTY            
                                                ) UPDATE WMS_STK_INVENTORY_DTL
                                                SET INVENTORY_DTL_STATUS = 99,
                                                 UPDATE_BY = '{paraMd.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INVENTORY_DTL_STATUS < '{status}'
                                                AND INVENTORY_ID = '{paraMd.INVENTORY_ID}'
                                                ";
                    new DataRepository<object>(_dataContext).Execute(sqlUpdateInventoryDtl);
                }
                if (row > 0)
                {
                    return SuccessMessageStatus("提交盘点托盘数据成功！", row);
                }
                else
                {
                    return FailMessageStatus("提交盘点托盘数据失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus("提交盘点托盘数据发生异常！" + ex.Message);
            }
        }
        #endregion

    }
}