Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/Instock/WmsInReceiptService.cs

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
using NXWMS.DataAccess.Entity;
using NXWMS.IService.NXWMS;
using NXWMS.IService.NXWMS.Instock;
using NXWMS.Model.AppModels.Condition.Instock;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Base;
using NXWMS.Model.AppModels.Result.Instock;
using NXWMS.Model.AppModels.Result.OutStock;
using NXWMS.Model.AppModels.Result.WmsTask;
using NXWMS.Model.Common;
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.Instock
{
    /// <summary>
    /// 收货单服务
    /// </summary>
    [AutoInject(typeof(IWmsInReceiptService), InjectType.Scope)]
    public class WmsInReceiptService : ServiceBase, IWmsInReceiptService
    {
        #region 全局变量、构造注入
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

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


        private IERPServer _eRPServer;
        /// <summary>
        /// 构造注入
        /// </summary>
        /// <param name="dataRepositoryContext"></param>
        /// <param name="configuration"></param>
        /// <param name="iSQLNodeRepository"></param>
        public WmsInReceiptService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository, IERPServer eRPServer)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
            this._eRPServer = eRPServer;
        }
        #endregion

        /// <summary>
        /// 分页查询收货单主表数据
        /// </summary>
        /// <param name="receiptSearchMd">收货单查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInReceiptResult>> GetWmsInReceiptListForPage(WmsInReceiptSearchMd receiptSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(receiptSearchMd.ReceiptNoMsg))
                {
                    sqlCondition.Append($" AND RECEIPT_NO = '{receiptSearchMd.ReceiptNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.ArrivalNoMsg))
                {
                    sqlCondition.Append($" AND ARRIVAL_NO = '{receiptSearchMd.ArrivalNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.ReceipterMsg))
                {
                    sqlCondition.Append($" AND RECEIPTER = '{receiptSearchMd.ReceipterMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.ReceiptType))
                {
                    sqlCondition.Append($" AND RECEIPT_TYPE = '{receiptSearchMd.ReceiptType}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.ReceiptStatus))
                {
                    sqlCondition.Append($" AND RECEIPT_STATUS = '{receiptSearchMd.ReceiptStatus}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE BATCH_NO = '{receiptSearchMd.BatchNoMsg}')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE MATERIEL_CODE LIKE '%{receiptSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{receiptSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{receiptSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE SUPPLIER_CODE LIKE '%{receiptSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{receiptSearchMd.SupplierMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.StartCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME >= '{receiptSearchMd.StartCreateTime}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.EndCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME <= '{receiptSearchMd.EndCreateTime}'");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.StartProductTime))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE PRODUCT_DATE >= '{receiptSearchMd.StartProductTime}')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.EndProductTime))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE PRODUCT_DATE <= '{receiptSearchMd.EndProductTime}')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.StartExpTime))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE EXP_DATE >= '{receiptSearchMd.StartExpTime}')");
                }
                if (!string.IsNullOrEmpty(receiptSearchMd.EndExpTime))
                {
                    sqlCondition.Append($" AND RECEIPT_ID IN (SELECT RECEIPT_ID FROM VW_WMS_IN_RECEIPT_DTL WHERE EXP_DATE <= '{receiptSearchMd.EndExpTime}')");
                }
                StringBuilder sqlCountReceiptData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RECEIPT WHERE 1=1");
                sqlCountReceiptData.Append(sqlCondition.ToString());

                int pageStartIndex = (receiptSearchMd.PageNum - 1) * receiptSearchMd.EveryPageQty;
                int pageEndIndex = receiptSearchMd.PageNum * receiptSearchMd.EveryPageQty;

                StringBuilder sqlQueryReceiptData = new StringBuilder($@"
                                                                        SELECT
	                                                                        RECEIPT_ID,
	                                                                        RECEIPT_NO,
	                                                                        ARRIVAL_NO,
	                                                                        RECEIPT_TYPE,
	                                                                        RECEIPT_TYPE_NAME,
                                                                            RECEIPT_TIME,
	                                                                        RECEIPTER,
	                                                                        RECEIPT_STATUS,
	                                                                        RECEIPT_STATUS_NAME,
	                                                                        [DESCRIBE],
	                                                                        CREATE_BY,
	                                                                        CREATE_NAME,
	                                                                        CREATE_TIME,
	                                                                        UPDATE_BY,
	                                                                        UPDATE_NAME,
	                                                                        UPDATE_TIME
                                                                        FROM
	                                                                        VW_WMS_IN_RECEIPT
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {receiptSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion
                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountReceiptData.ToString()));
                List<WmsInReceiptResult> resultList = new DataRepository<WmsInReceiptResult>(_dataContext).Query(sqlQueryReceiptData.ToString()).ToList();
                OperateResultInfo<List<WmsInReceiptResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsInReceiptResult>>($"查询收货单数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 根据收货单主键Id查询收货单明细数据
        /// </summary>
        /// <param name="wmsInReceipt">收货单对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsInReceiptResult> GetWmsInReceiptDtlListForID(WmsInReceiptResult wmsInReceipt)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsInReceipt = $@"
                                                SELECT
	                                                RECEIPT_ID,
	                                                RECEIPT_NO,
	                                                ARRIVAL_NO,
	                                                RECEIPT_TYPE,
	                                                RECEIPT_TYPE_NAME,
                                                    RECEIPT_TIME,
	                                                RECEIPTER,
	                                                RECEIPT_STATUS,
	                                                RECEIPT_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_IN_RECEIPT
                                                WHERE   
                                                    RECEIPT_ID = '{wmsInReceipt.ReceiptId}'
                                              ";
                string strWhere = string.Empty;
                if (wmsInReceipt.Remarks1 == "查询已删除明细数据")
                {
                    strWhere = "1=1";
                }
                else if (wmsInReceipt.Remarks1 == "查询未组盘完成的收货单明细")
                {
                    strWhere = "RECEIPT_DTL_STATUS < 99 AND RECEIPT_TRAY_QTY < RECEIPT_DTL_QTY";
                    //2021 0223 孙亚龙 修改质检流程
                    // AND INSPECTION_RESULT = 'OK'
                }
                else
                {
                    strWhere = "RECEIPT_DTL_STATUS < 111";
                }
                string sqlQueryWmsReceiptDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_IN_RECEIPT_DTL
                                                    WHERE
	                                                    RECEIPT_ID = '{wmsInReceipt.ReceiptId}'
                                                    AND {strWhere}
                                                    ORDER BY RECEIPT_DTL_STATUS,RECEIPT_DTL_ID
                                                    ";
                #endregion
                List<WmsInReceiptResult> resultList = new DataRepository<WmsInReceiptResult>(_dataContext).Query(sqlQueryWmsInReceipt).ToList();
                List<WmsInReceiptDtlResult> resultDtlList = new DataRepository<WmsInReceiptDtlResult>(_dataContext).Query(sqlQueryWmsReceiptDtl).ToList();
                wmsInReceipt = resultList[0];
                wmsInReceipt.WmsInReceiptDtlList = resultDtlList;
                OperateResultInfo<WmsInReceiptResult> retDataMsg = SuccessStatus(wmsInReceipt);

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<WmsInReceiptResult>($"查询收货单明细数据发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 查询组盘未完成的收货单明细数据
        /// </summary>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInReceiptResult>> GetWmsInReceiptDtl_UnFinishedTray()
        {
            try
            {
                string sqlQueryWmsInReceipt = $@"
                                                SELECT
	                                                RECEIPT_ID,
	                                                RECEIPT_NO,
	                                                ARRIVAL_NO,
	                                                RECEIPT_TYPE,
	                                                RECEIPT_TYPE_NAME,
                                                    RECEIPT_TIME,
	                                                RECEIPTER,
	                                                RECEIPT_STATUS,
	                                                RECEIPT_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_IN_RECEIPT
                                                WHERE   
                                                    RECEIPT_ID IN (
                                                                    SELECT
	                                                                    RECEIPT_ID
                                                                    FROM
	                                                                    VW_WMS_IN_RECEIPT_DTL
                                                                    WHERE
	                                                                    RECEIPT_DTL_STATUS < 99
                                                                    AND RECEIPT_TRAY_QTY < RECEIPT_DTL_QTY)
                                                    ORDER BY RECEIPT_ID
                                              ";
                string sqlQueryWmsReceiptDtlUnFinishedTray = $@"
                                                                SELECT
	                                                                *
                                                                FROM
	                                                                VW_WMS_IN_RECEIPT_DTL
                                                                WHERE
	                                                                RECEIPT_DTL_STATUS < 99
                                                                AND RECEIPT_TRAY_QTY < RECEIPT_DTL_QTY
                                                                ORDER BY
	                                                                RECEIPT_ID,
	                                                                RECEIPT_DTL_ID
                                                            ";
                List<WmsInReceiptResult> resultList = new DataRepository<WmsInReceiptResult>(_dataContext).Query(sqlQueryWmsInReceipt).ToList();
                List<WmsInReceiptDtlResult> resultDtlList = new DataRepository<WmsInReceiptDtlResult>(_dataContext).Query(sqlQueryWmsReceiptDtlUnFinishedTray).ToList();
                foreach (WmsInReceiptResult item in resultList)
                {
                    List<WmsInReceiptDtlResult> tmpLst = resultDtlList.FindAll(x=>x.ReceiptId == item.ReceiptId);
                    if (tmpLst != null && tmpLst.Count > 0)
                    {
                        item.WmsInReceiptDtlList = tmpLst;
                    }
                }
                return SuccessStatus(resultList);
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsInReceiptResult>>($"查询未组盘完成的收货单明细数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 新增收货单数据
        /// </summary>
        /// <param name="wmsInReceipt">收货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsInReceiptData(WmsInReceiptResult wmsInReceipt)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                wmsInReceipt.ReceiptId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Receipt_Id"));
                wmsInReceipt.ReceiptNo = new DataRepository<object>(_dataContext).GetSequenceMsg("Receipt_No");
                wmsInReceipt.ReceiptStatus = 0;
                StringBuilder sqlAddWmsReceipt = new StringBuilder();
                string receiptTime = DateTime.Compare(wmsInReceipt.ReceiptTime, new DateTime()) == 0 ? "NULL" : $"'{wmsInReceipt.ReceiptTime}'";
                sqlAddWmsReceipt.Append($@"
                                            INSERT INTO [WMS_IN_RECEIPT] (
	                                            [RECEIPT_ID],
	                                            [RECEIPT_NO],
	                                            [RECEIPTER],
	                                            [RECEIPT_TYPE],
	                                            [RECEIPT_TIME],
	                                            [ARRIVAL_NO],
	                                            [RECEIPT_STATUS],
	                                            [DESCRIBE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
	                                            (
		                                            '{wmsInReceipt.ReceiptId}',
		                                            '{wmsInReceipt.ReceiptNo}',
		                                            '{wmsInReceipt.Receipter}',
		                                            '{wmsInReceipt.ReceiptType}',
		                                            {receiptTime},
		                                            '{wmsInReceipt.ArrivalNo}',
		                                            '{wmsInReceipt.ReceiptStatus}',
		                                            '{wmsInReceipt.Describe}',
		                                            '{wmsInReceipt.CreateBy}',
		                                            getdate(),
		                                            '{wmsInReceipt.UpdateBy}',
		                                            getdate(),
		                                            0,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL
	                                            );
                                         ");
                sqlList.Add(sqlAddWmsReceipt.ToString());
                List<WmsInArrivalDtlResult> arrivalDtlList = new List<WmsInArrivalDtlResult>();
                if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo))
                {
                    string sqlQueryWmsInArivalDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_IN_ARRIVAL_DTL
                                                    WHERE
	                                                    ARRIVAL_NO = '{wmsInReceipt.ArrivalNo}'
                                                    AND RECEIPT_QTY < ARRIVAL_QTY
                                                    AND ARRIVAL_DTL_STATUS < 99
                                                    ";
                    arrivalDtlList = new DataRepository<WmsInArrivalDtlResult>(_dataContext).Query(sqlQueryWmsInArivalDtl).ToList();
                    if (arrivalDtlList.Count == 0)
                    {
                        return FailMessageStatus("到货通知单明细已经全部生成收货单，本次新增收货单操作失败！", 0);
                    }
                }
                foreach (WmsInReceiptDtlResult item in wmsInReceipt.WmsInReceiptDtlList)
                {
                    StringBuilder sqlAddWmsInReceiptDtl = new StringBuilder();
                    item.ReceiptDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Receipt_Dtl_Id"));
                    item.ReceiptId = wmsInReceipt.ReceiptId;
                    item.ReceiptDtlStatus = 0;
                 //   item.MaterielBarcode = new DataRepository<object>(_dataContext).GetSequenceMsg("Materiel_Barcode");
                    sqlAddWmsInReceiptDtl.Append($@"
                                                    INSERT INTO [WMS_IN_RECEIPT_DTL] (
	                                                    [RECEIPT_DTL_ID],
	                                                    [RECEIPT_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [ARRIVAL_QTY],
	                                                    [RECEIPT_DTL_QTY],
	                                                    [RECEIPT_TRAY_QTY],
	                                                    [PUTAWAY_QTY],
	                                                    [REJECT_QTY],
	                                                    [REJECT_REASON],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [RECEIPT_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [ARRIVAL_DTL_ID],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.ReceiptDtlId}',
		                                                    '{item.ReceiptId}',
		                                                    '{item.MaterielCode}',
		                                                    '{item.MaterielName}',
		                                                    '{item.MaterielBarcode}',
		                                                    '{item.MaterielSpec}',
		                                                    '{item.BatchNo}',
		                                                    '{item.PackageCode}',
		                                                    '{item.UnitCode}',
		                                                    '{item.ArrivalQty}',
		                                                    '{item.ReceiptDtlQty}',
		                                                    '{0}',
		                                                    '{0}',
		                                                    '{item.RejectQty}',
		                                                    '{item.RejectReason}',
		                                                    '{item.SupplierCode}',
		                                                    '{item.SupplierName}',
		                                                    '{item.ProductDate}',
		                                                    '{item.ExpDate}',
		                                                    '0',
		                                                    'Wait',
		                                                    '1',
		                                                    '{item.ArrivalDtlId}',
		                                                    '{item.Describe}',
		                                                    '{item.CreateBy}',
		                                                    getdate(),
		                                                    '{item.UpdateBy}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                    sqlList.Add(sqlAddWmsInReceiptDtl.ToString());
                    if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo) && item.ArrivalDtlId != 0)
                    {
                        WmsInArrivalDtlResult temArrDtlMd = arrivalDtlList.Find(x => x.ArrivalDtlId == item.ArrivalDtlId);
                        if (temArrDtlMd == null)
                        {
                            return FailMessageStatus($"到货通知单明细行（物料编码：【{item.MaterielCode}】物料名称：【{item.MaterielName}】批次号：【{item.BatchNo}】到货明细ID：【{item.ArrivalDtlId}】）信息发生变动，本次新增收货单操作失败！", 0);
                        }
                        else
                        {
                            if ((item.ReceiptDtlQty + temArrDtlMd.ReceiptQty) > temArrDtlMd.ArrivalQty)
                            {
                                return FailMessageStatus($"到货通知单明细行（物料编码：【{item.MaterielCode}】物料名称：【{item.MaterielName}】批次号：【{item.BatchNo}】到货明细ID：【{item.ArrivalDtlId}】）已收货数量不能大于到货数量，本次新增收货单操作失败！", 0);
                            }
                            else
                            {
                                string sqlUpdateArrivalDtlStatus = $@"
                                                                    UPDATE WMS_IN_ARRIVAL_DTL
                                                                    SET RECEIPT_QTY = '{item.ReceiptDtlQty + temArrDtlMd.ReceiptQty}',
                                                                     UPDATE_BY = '{item.UpdateBy}',
                                                                     UPDATE_TIME = GETDATE(),
                                                                     ARRIVAL_DTL_STATUS = 11,
                                                                     DATA_VERSION = DATA_VERSION + 1
                                                                    WHERE
	                                                                    ARRIVAL_DTL_ID = '{item.ArrivalDtlId}';
                                                                    ";
                                sqlList.Add(sqlUpdateArrivalDtlStatus);
                            }
                        }
                    }
                }

                if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo))
                {
                    string sqlUpdateArrivalStatus = $@"
                                                    IF NOT EXISTS (
	                                                    SELECT
		                                                    1
	                                                    FROM
		                                                    WMS_IN_ARRIVAL
	                                                    WHERE
		                                                    ARRIVAL_ID = '{arrivalDtlList[0].ArrivalId}'
	                                                    AND ARRIVAL_STATUS > 0
	                                                    AND ARRIVAL_STATUS < 99
                                                    ) UPDATE WMS_IN_ARRIVAL
                                                    SET ARRIVAL_STATUS = 11,
                                                        UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                        UPDATE_TIME = GETDATE(),
                                                        DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    ARRIVAL_ID = '{arrivalDtlList[0].ArrivalId}' ;
                                                ";
                    sqlList.Add(sqlUpdateArrivalStatus);
                }
                #endregion

                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}】");
            }
        }

        /// <summary>
        /// 修改收货单数据
        /// </summary>
        /// <param name="wmsInReceipt">收货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo EditWmsInReceiptData(WmsInReceiptResult wmsInReceipt)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string receiptTime = DateTime.Compare(wmsInReceipt.ReceiptTime, new DateTime()) == 0 ? "NULL" : $"'{wmsInReceipt.ReceiptTime}'";
                string sqlUpdateWmsInReceipt = $@"
                                                UPDATE [WMS_IN_RECEIPT]
                                                SET 
                                                 [RECEIPT_NO] = '{wmsInReceipt.ReceiptNo}',
                                                 [RECEIPTER] = '{wmsInReceipt.Receipter}',
                                                 [RECEIPT_TYPE] = '{wmsInReceipt.ReceiptType}',
                                                 [RECEIPT_TIME] = {receiptTime},
                                                 [ARRIVAL_NO] = '{wmsInReceipt.ArrivalNo}',
                                                 [RECEIPT_STATUS] = '{wmsInReceipt.ReceiptStatus}',
                                                 [DESCRIBE] = '{wmsInReceipt.Describe}',
                                                 [UPDATE_BY] = '{wmsInReceipt.UpdateBy}',
                                                 [UPDATE_TIME] = GETDATE(),
                                                 [DATA_VERSION] = [DATA_VERSION] + 1
                                                WHERE
	                                                [RECEIPT_ID] = '{wmsInReceipt.ReceiptId}';
                                                ";
                sqlList.Add(sqlUpdateWmsInReceipt);
                List<WmsInArrivalDtlResult> arrivalDtlList = new List<WmsInArrivalDtlResult>();
                if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo))
                {
                    string sqlQueryWmsInArivalDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_IN_ARRIVAL_DTL
                                                    WHERE
	                                                    ARRIVAL_NO = '{wmsInReceipt.ArrivalNo}'
                                                    AND RECEIPT_QTY < ARRIVAL_QTY
                                                    AND ARRIVAL_DTL_STATUS < 99
                                                    ";
                    arrivalDtlList = new DataRepository<WmsInArrivalDtlResult>(_dataContext).Query(sqlQueryWmsInArivalDtl).ToList();
                    if (arrivalDtlList.Count == 0)
                    {
                        return FailMessageStatus("到货通知单明细已经全部生成收货单，本次新增收货单操作失败！", 0);
                    }
                }
                foreach (WmsInReceiptDtlResult item in wmsInReceipt.WmsInReceiptDtlList)
                {
                    if (item.Remarks1 == "更新" || item.Remarks1 == "删除")
                    {
                        string sqlUpdateWmsInReceiptDtl = $@"
                                                            UPDATE [WMS_IN_RECEIPT_DTL]
                                                            SET 
                                                             [MATERIEL_CODE] = '{item.MaterielCode}',
                                                             [MATERIEL_NAME] = '{item.MaterielName}',
                                                             [MATERIEL_BARCODE] = '{item.MaterielBarcode}',
                                                             [MATERIEL_SPEC] = '{item.MaterielSpec}',
                                                             [BATCH_NO] = '{item.BatchNo}',
                                                             [PACKAGE_CODE] = '{item.PackageCode}',
                                                             [UNIT_CODE] = '{item.UnitCode}',
                                                             [ARRIVAL_QTY] = '{item.ArrivalQty}',
                                                             [RECEIPT_DTL_QTY] = '{item.ReceiptDtlQty}',
                                                             [RECEIPT_TRAY_QTY] = '{item.ReceiptTrayQty}',
                                                             [PUTAWAY_QTY] = '{item.PutawayQty}',
                                                             [REJECT_QTY] = '{item.RejectQty}',
                                                             [REJECT_REASON] = '{item.RejectReason}',
                                                             [SUPPLIER_CODE] = '{item.SupplierCode}',
                                                             [SUPPLIER_NAME] = '{item.SupplierCode}',
                                                             [PRODUCT_DATE] = '{item.ProductDate}',
                                                             [EXP_DATE] = '{item.ExpDate}',
                                                             [RECEIPT_DTL_STATUS] = '{item.ReceiptDtlStatus}',
                                                             [INSPECTION_RESULT] = '{item.InspectionResult}',
                                                             [ITEM_STATUS] = '{item.ItemStatus}',
                                                             [ARRIVAL_DTL_ID] = '{item.ArrivalDtlId}',
                                                             [DESCRIBE] = '{item.Describe}',
                                                             [UPDATE_BY] = '{item.UpdateBy}',
                                                             [UPDATE_TIME] = getdate(),
                                                             [DATA_VERSION] = [DATA_VERSION] + 1
                                                            WHERE
	                                                            [RECEIPT_DTL_ID] = '{item.ReceiptDtlId}' 
                                                            AND [RECEIPT_ID] = '{wmsInReceipt.ReceiptId}';
                                                        ";
                        sqlList.Add(sqlUpdateWmsInReceiptDtl);
                    }
                    if (item.Remarks1 == "添加")
                    {
                        StringBuilder sqlAddWmsInReceiptDtl = new StringBuilder();
                        item.ReceiptDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Receipt_Dtl_Id"));
                        item.ReceiptId = wmsInReceipt.ReceiptId;
                        item.ReceiptDtlStatus = 0;
                        item.MaterielBarcode = new DataRepository<object>(_dataContext).GetSequenceMsg("Materiel_Barcode");
                        sqlAddWmsInReceiptDtl.Append($@"
                                                    INSERT INTO [WMS_IN_RECEIPT_DTL] (
	                                                    [RECEIPT_DTL_ID],
	                                                    [RECEIPT_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [ARRIVAL_QTY],
	                                                    [RECEIPT_DTL_QTY],
	                                                    [RECEIPT_TRAY_QTY],
	                                                    [PUTAWAY_QTY],
	                                                    [REJECT_QTY],
	                                                    [REJECT_REASON],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [RECEIPT_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [ARRIVAL_DTL_ID],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.ReceiptDtlId}',
		                                                    '{item.ReceiptId}',
		                                                    '{item.MaterielCode}',
		                                                    '{item.MaterielName}',
		                                                    '{item.MaterielBarcode}',
		                                                    '{item.MaterielSpec}',
		                                                    '{item.BatchNo}',
		                                                    '{item.PackageCode}',
		                                                    '{item.UnitCode}',
		                                                    '{item.ArrivalQty}',
		                                                    '{item.ReceiptDtlQty}',
		                                                    '{0}',
		                                                    '{0}',
		                                                    '{item.RejectQty}',
		                                                    '{item.RejectReason}',
		                                                    '{item.SupplierCode}',
		                                                    '{item.SupplierName}',
		                                                    '{item.ProductDate}',
		                                                    '{item.ExpDate}',
		                                                    '0',
		                                                    'Wait',
		                                                    '1',
		                                                    '{item.ArrivalDtlId}',
		                                                    '{item.Describe}',
		                                                    '{item.CreateBy}',
		                                                    getdate(),
		                                                    '{item.UpdateBy}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                        sqlList.Add(sqlAddWmsInReceiptDtl.ToString());
                    }
                    if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo) && item.ArrivalDtlId != 0)
                    {
                        if (item.Remarks1 != "删除")
                        {
                            WmsInArrivalDtlResult temArrDtlMd = arrivalDtlList.Find(x => x.ArrivalDtlId == item.ArrivalDtlId);
                            if (temArrDtlMd == null)
                            {
                                return FailMessageStatus($"到货通知单明细行（物料编码：【{item.MaterielCode}】物料名称：【{item.MaterielName}】批次号：【{item.BatchNo}】到货明细ID：【{item.ArrivalDtlId}】）信息发生变动，本次新增收货单操作失败！", 0);
                            }
                            else
                            {
                                if ((item.ReceiptDtlQty + temArrDtlMd.ReceiptQty) > temArrDtlMd.ArrivalQty)
                                {
                                    return FailMessageStatus($"到货通知单明细行（物料编码：【{item.MaterielCode}】物料名称：【{item.MaterielName}】批次号：【{item.BatchNo}】到货明细ID：【{item.ArrivalDtlId}】）已收货数量不能大于到货数量，本次新增收货单操作失败！", 0);
                                }
                                else
                                {
                                    string sqlUpdateArrivalDtlStatus = $@"
                                                                    UPDATE WMS_IN_ARRIVAL_DTL
                                                                    SET RECEIPT_QTY = '{item.ReceiptDtlQty + temArrDtlMd.ReceiptQty}',
                                                                     UPDATE_BY = '{item.UpdateBy}',
                                                                     UPDATE_TIME = GETDATE(),
                                                                     ARRIVAL_DTL_STATUS = 11,
                                                                     DATA_VERSION = DATA_VERSION + 1
                                                                    WHERE
	                                                                    ARRIVAL_DTL_ID = '{item.ArrivalDtlId}'
                                                                    ";
                                    sqlList.Add(sqlUpdateArrivalDtlStatus);
                                }
                            }
                        }
                        else
                        {
                            string sqlRollBackArrivalData = $@"
                                                                IF NOT EXISTS (
	                                                                    SELECT
		                                                                    1
	                                                                    FROM
		                                                                    WMS_IN_ARRIVAL_DTL
	                                                                    WHERE
		                                                                    RECEIPT_QTY - '{item.ReceiptDtlQty}' = 0
                                                                    ) UPDATE WMS_IN_ARRIVAL_DTL
                                                                    SET RECEIPT_QTY = RECEIPT_QTY - '{item.ReceiptDtlQty}',
                                                                        UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                                        UPDATE_TIME = GETDATE(),
                                                                        DATA_VERSION = DATA_VERSION + 1
                                                                    WHERE
	                                                                    ARRIVAL_DTL_ID = '{item.ArrivalDtlId}'
                                                                    ELSE
	                                                                    UPDATE WMS_IN_ARRIVAL_DTL
                                                                    SET RECEIPT_QTY = RECEIPT_QTY - '{item.ReceiptDtlQty}',
                                                                        ARRIVAL_DTL_STATUS = '0',
                                                                        UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                                        UPDATE_TIME = GETDATE(),
                                                                        DATA_VERSION = DATA_VERSION + 1
                                                                    WHERE
	                                                                    ARRIVAL_DTL_ID = '{item.ArrivalDtlId}';
                                                                ";
                            sqlList.Add(sqlRollBackArrivalData);
                            decimal oldArrReceiptQty = arrivalDtlList.Find(x => x.ArrivalDtlId == item.ArrivalDtlId).ReceiptQty;
                            arrivalDtlList.Find(x => x.ArrivalDtlId == item.ArrivalDtlId).ReceiptQty = oldArrReceiptQty - item.ReceiptDtlQty;

                        }
                    }
                }
                if (!string.IsNullOrEmpty(wmsInReceipt.ArrivalNo))
                {
                    string sqlUpdateArrivalStatus = $@"
                                                    IF NOT EXISTS (
	                                                    SELECT
		                                                    1
	                                                    FROM
		                                                    WMS_IN_ARRIVAL
	                                                    WHERE
		                                                    ARRIVAL_ID = '{arrivalDtlList[0].ArrivalId}'
	                                                    AND ARRIVAL_STATUS > 0
	                                                    AND ARRIVAL_STATUS < 99
                                                    ) UPDATE WMS_IN_ARRIVAL
                                                    SET ARRIVAL_STATUS = 11,
                                                        UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                        UPDATE_TIME = GETDATE(),
                                                        DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    ARRIVAL_ID = '{arrivalDtlList[0].ArrivalId}' ;
                                                ";
                    sqlList.Add(sqlUpdateArrivalStatus);
                }
                #endregion

                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}】");
            }
        }
        /// <summary>
        /// 删除收货单数据
        /// </summary>
        /// <param name="wmsInReceipt">收货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo DeleteWmsInReceiptData(WmsInReceiptResult wmsInReceipt)
        {
            try
            {
                #region SQL语句生成
                string[] receiptIdList = wmsInReceipt.ReceiptNo.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in receiptIdList)
                {
                    string sqlDeleteWmsReceipt = $@"
                                                UPDATE WMS_IN_RECEIPT
                                                SET RECEIPT_STATUS = '111',
                                                 UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                RECEIPT_ID = '{item}';
                                                ";
                    string sqlDeleteWmsInReceiptDtl = $@"
                                                    UPDATE WMS_IN_RECEIPT_DTL
                                                    SET RECEIPT_DTL_STATUS = '111',
                                                     UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    RECEIPT_ID = '{item}';
                                                    ";
                    sqlList.Add(sqlDeleteWmsReceipt);
                    sqlList.Add(sqlDeleteWmsInReceiptDtl);
                    /*
                     ToDo: 后续增加删除移至历史表中。
                    */
                    string sqlQueryWmsReceiptDtlData = $@"
                                                        SELECT
	                                                        *
                                                        FROM
	                                                        VW_WMS_IN_RECEIPT_DTL
                                                        WHERE
	                                                        RECEIPT_ID = '{item}'
                                                        AND RECEIPT_DTL_STATUS = 0
                                                        ORDER BY RECEIPT_DTL_STATUS,RECEIPT_DTL_ID
                                                    ";
                    List<WmsInReceiptDtlResult> resultDtlList = new DataRepository<WmsInReceiptDtlResult>(_dataContext).Query(sqlQueryWmsReceiptDtlData).ToList();
                    if (resultDtlList.Count > 0)
                    {
                        foreach (WmsInReceiptDtlResult mdDtl in resultDtlList)
                        {
                            if (mdDtl.ArrivalDtlId != 0)
                            {
                                string sqlRollBackArrivaldDtlData = $@"
                                                                    IF NOT EXISTS (
	                                                                        SELECT
		                                                                        1
	                                                                        FROM
		                                                                        WMS_IN_ARRIVAL_DTL
	                                                                        WHERE
		                                                                        RECEIPT_QTY - '{mdDtl.ReceiptDtlQty}' = 0
                                                                        ) UPDATE WMS_IN_ARRIVAL_DTL
                                                                        SET RECEIPT_QTY = RECEIPT_QTY - '{mdDtl.ReceiptDtlQty}',
                                                                         UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                                         UPDATE_TIME = GETDATE(),
                                                                         DATA_VERSION = DATA_VERSION + 1
                                                                        WHERE
	                                                                        ARRIVAL_DTL_ID = '{mdDtl.ArrivalDtlId}'
                                                                        ELSE
	                                                                        UPDATE WMS_IN_ARRIVAL_DTL
                                                                        SET RECEIPT_QTY = RECEIPT_QTY - '{mdDtl.ReceiptDtlQty}',
                                                                         ARRIVAL_DTL_STATUS = '0',
                                                                         UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                                         UPDATE_TIME = GETDATE(),
                                                                         DATA_VERSION = DATA_VERSION + 1
                                                                        WHERE
	                                                                        ARRIVAL_DTL_ID = '{mdDtl.ArrivalDtlId}';
                                                                ";
                                sqlList.Add(sqlRollBackArrivaldDtlData);
                                string sqlRollBackArrivalData = $@"
                                                                    IF NOT EXISTS (
	                                                                    SELECT
		                                                                    1
	                                                                    FROM
		                                                                    WMS_IN_ARRIVAL_DTL
	                                                                    WHERE
		                                                                    ARRIVAL_ID IN (
			                                                                    SELECT
				                                                                    ARRIVAL_ID
			                                                                    FROM
				                                                                    WMS_IN_ARRIVAL_DTL
			                                                                    WHERE
				                                                                    ARRIVAL_DTL_ID = '{mdDtl.ArrivalDtlId}'
		                                                                    )
		                                                                    AND ARRIVAL_DTL_STATUS > 0
		                                                                    AND ARRIVAL_DTL_STATUS < 99
                                                                    ) UPDATE WMS_IN_ARRIVAL
                                                                    SET ARRIVAL_STATUS = '0',
                                                                     UPDATE_BY = '{wmsInReceipt.UpdateBy}',
                                                                     UPDATE_TIME = GETDATE(),
                                                                     DATA_VERSION = DATA_VERSION + 1
                                                                    WHERE
	                                                                    ARRIVAL_ID = (
			                                                                    SELECT
				                                                                    ARRIVAL_ID
			                                                                    FROM
				                                                                    WMS_IN_ARRIVAL_DTL
			                                                                    WHERE
				                                                                    ARRIVAL_DTL_ID = '{mdDtl.ArrivalDtlId}'
                                                                    );
                                                                ";
                                sqlList.Add(sqlRollBackArrivalData);
                            }
                        }
                    }
                }
                #endregion

                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}】");
            }
        }

        /// <summary>
        /// 分页查询收货记录数据
        /// </summary>
        /// <param name="receiptRecordSearchMd">收货记录查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInReceiptRecordResult>> GetWmsInReceiptRecordListForPage(WmsInReceiptRecordSearchMd receiptRecordSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ReceiptNoMsg))
                {
                    sqlCondition.Append($" AND RECEIPT_NO = '{receiptRecordSearchMd.ReceiptNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ArrivalNoMsg))
                {
                    sqlCondition.Append($" AND ARRIVAL_NO = '{receiptRecordSearchMd.ArrivalNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.RegionNoMsg))
                {
                    sqlCondition.Append($" AND REGION_CODE = '{receiptRecordSearchMd.RegionNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.BinNoMsg))
                {
                    sqlCondition.Append($" AND (BIN_CODE like '%{receiptRecordSearchMd.BinNoMsg}%' OR BIN_NAME like '%{receiptRecordSearchMd.BinNoMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.PalletNoMsg))
                {
                    sqlCondition.Append($" AND PALLET_CODE = '{receiptRecordSearchMd.PalletNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ReceipterMsg))
                {
                    sqlCondition.Append($" AND RECEIPTER = '{receiptRecordSearchMd.ReceipterMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_CODE like '%{receiptRecordSearchMd.MaterielMsg}%' OR MATERIEL_NAME = '%{receiptRecordSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.MaterielTypeMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_TYPE_CODE = '%{receiptRecordSearchMd.MaterielTypeMsg}%' OR MATERIEL_TYPE_NAME = '%{receiptRecordSearchMd.MaterielTypeMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND SUPPLIER_CODE = '{receiptRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND BATCH_NO = '{receiptRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ItemStatusMsg))
                {
                    sqlCondition.Append($" AND ITEM_STATUS = '{receiptRecordSearchMd.ItemStatusMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.InspectionResultMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_RESULT = '{receiptRecordSearchMd.InspectionResultMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ReceiptRecordStatus))
                {
                    sqlCondition.Append($" AND RECEIPT_RECORD_STATUS = '{receiptRecordSearchMd.ReceiptRecordStatus}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartReceiptTime))
                {
                    sqlCondition.Append($" AND RECEIPT_TIME >= '{receiptRecordSearchMd.StartReceiptTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndReceiptTime))
                {
                    sqlCondition.Append($" AND RECEIPT_TIME <= '{receiptRecordSearchMd.EndReceiptTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE >= '{receiptRecordSearchMd.StartProductTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE <= '{receiptRecordSearchMd.EndProductTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE >= '{receiptRecordSearchMd.StartExpTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE <= '{receiptRecordSearchMd.EndExpTime}'");
                }
                StringBuilder sqlCountReceiptData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RECEIPT_RECORD WHERE 1=1");
                sqlCountReceiptData.Append(sqlCondition.ToString());

                int pageStartIndex = (receiptRecordSearchMd.PageNum - 1) * receiptRecordSearchMd.EveryPageQty;
                int pageEndIndex = receiptRecordSearchMd.PageNum * receiptRecordSearchMd.EveryPageQty;

                StringBuilder sqlQueryReceiptData = new StringBuilder($@"
                                                                        SELECT
                                                                            RECEIPT_RECORD_ID,
                                                                            NEWID,
                                                                            RECEIPT_ID,
                                                                            RECEIPT_NO,
                                                                            RECEIPT_DTL_ID,
                                                                            ARRIVAL_NO,
                                                                            REGION_CODE,
                                                                            REGION_NAME,
                                                                            BIN_CODE,
                                                                            BIN_NAME,
                                                                            TRAY_CODE,
                                                                            PALLET_CODE,
                                                                            RECEIPT_TIME,
                                                                            RECEIPTER,
                                                                            MATERIEL_TYPE_CODE,
                                                                            MATERIEL_TYPE_NAME,
                                                                            MATERIEL_CODE,
                                                                            MATERIEL_NAME,
                                                                            MATERIEL_BARCODE,
                                                                            MATERIEL_SPEC,
                                                                            SUPPLIER_CODE,
                                                                            SUPPLIER_NAME,
                                                                            BATCH_NO,
                                                                            PACKAGE_CODE,
                                                                            UNIT_CODE,
                                                                            RECEIPT_QTY,
                                                                            REJECTION_QTY,
                                                                            REJECTION_REASON,
                                                                            PRODUCT_DATE,
                                                                            EXP_DATE,
                                                                            RECEIPT_RECORD_STATUS,
                                                                            RECEIPT_RECORD_STATUS_NAME,
                                                                            INSPECTION_RESULT,
                                                                            INSPECTION_RESULT_NAME,
                                                                            ITEM_STATUS,
                                                                            ITEM_STATUS_NAME,
                                                                            [DESCRIBE],
                                                                            CREATE_BY,
                                                                            CREATE_NAME,
                                                                            CREATE_TIME,
                                                                            UPDATE_BY,
                                                                            UPDATE_NAME,
                                                                            UPDATE_TIME,
                                                                            DATA_VERSION,
                                                                            REMARKS1,
                                                                            REMARKS2,
                                                                            REMARKS3,
                                                                            REMARKS4,
                                                                            REMARKS5
                                                                        FROM
	                                                                        VW_WMS_IN_RECEIPT_RECORD
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {receiptRecordSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion

                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountReceiptData.ToString()));
                List<WmsInReceiptRecordResult> resultList = new DataRepository<WmsInReceiptRecordResult>(_dataContext).Query(sqlQueryReceiptData.ToString()).ToList();
                OperateResultInfo<List<WmsInReceiptRecordResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsInReceiptRecordResult>>($"查询收货记录数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 添加收货组盘数据
        /// </summary>
        /// <param name="wmsStkTrayResult">组盘信息实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsInReceiptTrayData(WmsStkTrayResult wmsStkTrayResult)
        {
            try
            {
                OperateResultInfo checkRetMsg = CheckPalletValidity(wmsStkTrayResult.PALLET_CODE);
                if (checkRetMsg.Status != OperateStatus.Success)
                {
                    return checkRetMsg;
                }
                if (wmsStkTrayResult.WmsStkTrayDtlList.Count > 0)
                {
                    #region SQL语句生成

                    //分配货位暂时这么写
                    List<string> sqlList = new List<string>();
                    string sqlQueryReceiptRegionBinMsg = $@"
                                                            SELECT
	                                                            A.*, B.USER_NAME Create_Name,
	                                                            C.USER_NAME Update_Name
                                                            FROM
	                                                            BAS_BIN A
                                                            LEFT JOIN SYS_USER B ON A.CREATE_BY = B.USER_ID
                                                            LEFT JOIN SYS_USER C ON A.UPDATE_BY = C.USER_ID
                                                            WHERE
	                                                            A.REGION_CODE = 'SHDJ_Region'                        
                                                        ";
                    List<BinResult> resultList = new DataRepository<BinResult>(_dataContext).Query(sqlQueryReceiptRegionBinMsg).ToList();    
                    BinResult binMd = resultList[0];
                    wmsStkTrayResult.TRAY_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Tray_Id"));
                    wmsStkTrayResult.TRAY_CODE = new DataRepository<object>(_dataContext).GetSequenceMsg("Tray_Code");
                    string sqlAddWmsStkTray = $@"
                                                INSERT INTO [WMS_STK_TRAY] (
	                                                [TRAY_ID],
	                                                [TRAY_CODE],
	                                                [PALLET_CODE],
	                                                [WEIGHT],
	                                                [HEIGHT],
	                                                [TRAYS_TYPE],
	                                                [TRAY_LOADED_TYPE],
	                                                [TRAY_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5]
                                                )
                                                VALUES
	                                                (
		                                                '{wmsStkTrayResult.TRAY_ID}',
		                                                '{wmsStkTrayResult.TRAY_CODE}',
		                                                '{wmsStkTrayResult.PALLET_CODE}',
		                                                '{wmsStkTrayResult.WEIGHT}',
		                                                '{wmsStkTrayResult.HEIGHT}',
		                                                '{wmsStkTrayResult.TRAYS_TYPE}',
		                                                '{wmsStkTrayResult.TRAY_LOADED_TYPE}',
		                                                '{wmsStkTrayResult.TRAY_STATUS}',
		                                                '{wmsStkTrayResult.DESCRIBE}',
		                                                '{wmsStkTrayResult.CREATE_BY}',
		                                                getdate(),
		                                                '{wmsStkTrayResult.UPDATE_BY}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL
	                                                );
                                            ";
                    sqlList.Add(sqlAddWmsStkTray);
                    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],
	                                                    [SBIN_CODE],
	                                                    [PUTAWAY_PRIORITY],
	                                                    [PUTAWAY_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{putawayId}',
		                                                    '{puawayNo}',
		                                                    '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInReceiptRecord.RECEIPT_NO}',
		                                                    '{1}',
		                                                    '{wmsStkTrayResult.TRAY_CODE}',
		                                                    '{wmsStkTrayResult.PALLET_CODE}',
		                                                    '{binMd.BIN_CODE}',
		                                                    '{100}',
		                                                    '{0}',
		                                                    '{wmsStkTrayResult.DESCRIBE}',
		                                                    '{wmsStkTrayResult.CREATE_BY}',
		                                                    getdate(),
		                                                    '{wmsStkTrayResult.UPDATE_BY}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                                    ";
                    sqlList.Add(sqlAddWmsInPutAway);
                    string sqlAddWmsStkBalance = $@"
                                                INSERT INTO [WMS_STK_BALANCE] (
	                                                [BALANCE_ID],
	                                                [AREA_CODE],
	                                                [AREA_NAME],
	                                                [WAREHOUSE_CODE],
	                                                [WAREHOUSE_NAME],
	                                                [REGION_CODE],
	                                                [REGION_NAME],
	                                                [BIN_CODE],
	                                                [BIN_NAME],
	                                                [TRAY_ID],
	                                                [TRAY_CODE],
	                                                [PALLET_CODE],
	                                                [INWH_TIME],
	                                                [BALANCE_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5]
                                                )
                                                VALUES
	                                                (
		                                                '{Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Balance_Id"))}',
		                                                'AreaTest1',
		                                                '区域测试1',
		                                                'WarehouseTest1',
		                                                '仓库测试1',
		                                                '{binMd.REGION_CODE}',
		                                                '{binMd.REGION_NAME}',
		                                                '{binMd.BIN_CODE}',
		                                                '{binMd.BIN_NAME}',
		                                                '{wmsStkTrayResult.TRAY_ID}',
		                                                '{wmsStkTrayResult.TRAY_CODE}',
		                                                '{wmsStkTrayResult.PALLET_CODE}',
		                                                getdate(),
		                                                '55',
		                                                '{wmsStkTrayResult.DESCRIBE}',
		                                                '{wmsStkTrayResult.CREATE_BY}',
		                                                getdate(),
		                                                '{wmsStkTrayResult.CREATE_BY}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL
	                                                );
                                                ";
                    sqlList.Add(sqlAddWmsStkBalance);

                    List<WmsStkTrayDtlResult> tmpTrayDtlMergeLst= new List<WmsStkTrayDtlResult>();
                    List<int> tmpReceiptIdLst = new List<int>();
                    List<string> sqlList_ReceiprRecordAndPutAway = new List<string>();
                    foreach (WmsStkTrayDtlResult item in wmsStkTrayResult.WmsStkTrayDtlList)
                    {
                        item.TRAY_ID = wmsStkTrayResult.TRAY_ID;
                        WmsStkTrayDtlExtResult trayDtlExtMd = item.WmsStkTrayDtlExtMd;
                        WmsInReceiptRecordResult receiptRecordMd = item.WmsInReceiptRecord;
                        var tmpResult = tmpTrayDtlMergeLst.FirstOrDefault(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.WmsStkTrayDtlExtMd.BATCH_NO == trayDtlExtMd.BATCH_NO);
                        if (tmpResult != null)
                        {
                            tmpResult.QTY += item.QTY;
                        }
                        else
                        {
                            tmpTrayDtlMergeLst.Add(item);
                        }
                        int receiptId = tmpReceiptIdLst.FirstOrDefault(x => x == receiptRecordMd.RECEIPT_ID);
                        if (receiptId <= 0)
                        {
                            tmpReceiptIdLst.Add(receiptRecordMd.RECEIPT_ID);
                        }
                        receiptRecordMd.RECEIPT_RECORD_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Receipt_Record_Id"));
                        int putawayDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id"));
                        receiptRecordMd.TRAY_CODE = wmsStkTrayResult.TRAY_CODE;
                        receiptRecordMd.REGION_CODE = binMd.REGION_CODE;
                        receiptRecordMd.REGION_NAME = binMd.REGION_NAME;
                        receiptRecordMd.BIN_CODE = binMd.BIN_CODE;
                        receiptRecordMd.BIN_NAME = binMd.BIN_NAME;

                        string receiptTime = string.Empty;
                        if (receiptRecordMd.RECEIPT_TIME == new DateTime())
                        {
                            receiptTime = "NULL";
                        }
                        else
                        {
                            receiptTime = $"'{receiptRecordMd.RECEIPT_TIME}'";
                        }
                        string sqlAddWmsInReceiptRecord = $@"
                                                            INSERT INTO [WMS_IN_RECEIPT_RECORD] (
	                                                            [RECEIPT_RECORD_ID],
	                                                            [RECEIPT_ID],
	                                                            [RECEIPT_NO],
	                                                            [RECEIPT_DTL_ID],
	                                                            [ARRIVAL_NO],
	                                                            [REGION_CODE],
	                                                            [REGION_NAME],
	                                                            [BIN_CODE],
	                                                            [BIN_NAME],
	                                                            [TRAY_CODE],
	                                                            [PALLET_CODE],
	                                                            [RECEIPT_TIME],
	                                                            [RECEIPTER],
	                                                            [MATERIEL_CODE],
	                                                            [MATERIEL_NAME],
	                                                            [MATERIEL_BARCODE],
	                                                            [MATERIEL_SPEC],
	                                                            [SUPPLIER_CODE],
	                                                            [SUPPLIER_NAME],
	                                                            [BATCH_NO],
	                                                            [PACKAGE_CODE],
	                                                            [UNIT_CODE],
	                                                            [RECEIPT_QTY],
	                                                            [REJECTION_QTY],
	                                                            [REJECTION_REASON],
	                                                            [PRODUCT_DATE],
	                                                            [EXP_DATE],
	                                                            [RECEIPT_RECORD_STATUS],
	                                                            [INSPECTION_RESULT],
	                                                            [ITEM_STATUS],
	                                                            [DESCRIBE],
	                                                            [CREATE_BY],
	                                                            [CREATE_TIME],
	                                                            [UPDATE_BY],
	                                                            [UPDATE_TIME],
	                                                            [DATA_VERSION],
	                                                            [REMARKS1],
	                                                            [REMARKS2],
	                                                            [REMARKS3],
	                                                            [REMARKS4],
	                                                            [REMARKS5]
                                                            )
                                                            VALUES
	                                                            (
		                                                            '{receiptRecordMd.RECEIPT_RECORD_ID}',
		                                                            '{receiptRecordMd.RECEIPT_ID}',
		                                                            '{receiptRecordMd.RECEIPT_NO}',
		                                                            '{receiptRecordMd.RECEIPT_DTL_ID}',
		                                                            '{receiptRecordMd.ARRIVAL_NO}',
		                                                            '{receiptRecordMd.REGION_CODE}',
		                                                            '{receiptRecordMd.REGION_NAME}',
		                                                            '{receiptRecordMd.BIN_CODE}',
		                                                            '{receiptRecordMd.BIN_NAME}',
		                                                            '{receiptRecordMd.TRAY_CODE}',
		                                                            '{receiptRecordMd.PALLET_CODE}',
		                                                            {receiptTime},
		                                                            '{receiptRecordMd.RECEIPTER}',
		                                                            '{receiptRecordMd.MATERIEL_CODE}',
		                                                            '{receiptRecordMd.MATERIEL_NAME}',
		                                                            '{receiptRecordMd.MATERIEL_BARCODE}',
		                                                            '{receiptRecordMd.MATERIEL_SPEC}',
		                                                            '{receiptRecordMd.SUPPLIER_CODE}',
		                                                            '{receiptRecordMd.SUPPLIER_NAME}',
		                                                            '{receiptRecordMd.BATCH_NO}',
		                                                            '{receiptRecordMd.PACKAGE_CODE}',
		                                                            '{receiptRecordMd.UNIT_CODE}',
		                                                            '{receiptRecordMd.RECEIPT_QTY}',
		                                                            '{receiptRecordMd.REJECTION_QTY}',
		                                                            '{receiptRecordMd.REJECTION_REASON}',
		                                                            '{receiptRecordMd.PRODUCT_DATE}',
		                                                            '{receiptRecordMd.EXP_DATE}',
		                                                            '{receiptRecordMd.RECEIPT_RECORD_STATUS}',
		                                                            '{receiptRecordMd.INSPECTION_RESULT}',
		                                                            '{receiptRecordMd.ITEM_STATUS}',
		                                                            '{receiptRecordMd.DESCRIBE}',
		                                                            '{receiptRecordMd.CREATE_BY}',
		                                                            getdate(),
		                                                            '{receiptRecordMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                            ";
                        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}',
		                                                            '{trayDtlExtMd.MATERIEL_SPEC}',
		                                                            '{trayDtlExtMd.BATCH_NO}',
		                                                            '{trayDtlExtMd.PACKAGE_CODE}',
		                                                            '{trayDtlExtMd.UNIT_CODE}',
		                                                            '{item.QTY}',
		                                                            '{trayDtlExtMd.SUPPLIER_CODE}',
		                                                            '{trayDtlExtMd.SUPPLIER_NAME}',
		                                                            '{trayDtlExtMd.PRODUCT_DATE.ToString("yyyy-MM-dd hh:mm:ss")}',
		                                                            '{trayDtlExtMd.EXP_DATE.ToString("yyyy-MM-dd hh:mm:ss")}',
		                                                            '{trayDtlExtMd.INSPECTION_RESULT}',
		                                                            '{0}',
		                                                            '{trayDtlExtMd.ITEM_STATUS}',
		                                                            '{item.DESCRIBE}',
		                                                            '{trayDtlExtMd.CREATE_BY}',
		                                                            getdate(),
		                                                            '{trayDtlExtMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                        ";
                        sqlList_ReceiprRecordAndPutAway.Add(sqlAddWmsInReceiptRecord);
                        sqlList_ReceiprRecordAndPutAway.Add(sqlAddWmsInPutAwayDtl);
                        string sqlUpdateWmsInReceiptDtl = $@"
                                                            UPDATE WMS_IN_RECEIPT_DTL
                                                            SET 
                                                             RECEIPT_TRAY_QTY = RECEIPT_TRAY_QTY + '{item.QTY}',
                                                             RECEIPT_DTL_STATUS = '{55}',
                                                             UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
                                                             UPDATE_TIME = GETDATE(),
                                                             DATA_VERSION = DATA_VERSION + 1
                                                            WHERE
	                                                            RECEIPT_DTL_ID = '{receiptRecordMd.RECEIPT_DTL_ID}'
                                                            AND RECEIPT_ID = '{receiptRecordMd.RECEIPT_ID}'
                                                         ";
                        sqlList_ReceiprRecordAndPutAway.Add(sqlUpdateWmsInReceiptDtl);
                    }

                    List<string> sqlList_TrayDtlAndExt = new List<string>();
                    foreach (WmsStkTrayDtlResult item in tmpTrayDtlMergeLst)
                    {
                        item.TRAY_ID = wmsStkTrayResult.TRAY_ID;
                        WmsStkTrayDtlExtResult trayDtlExtMd = item.WmsStkTrayDtlExtMd;
                        item.TRAY_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Tray_Dtl_Id"));
                        trayDtlExtMd.TRAY_DTL_EXT_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Tray_Dtl_Ext_Id"));
                        trayDtlExtMd.TRAY_DTL_ID = item.TRAY_DTL_ID;
                        trayDtlExtMd.TRAY_ID = wmsStkTrayResult.TRAY_ID;
                        string sqlAddWmsStkTrayDtl = $@"
                                                        INSERT INTO [WMS_STK_TRAY_DTL] (
	                                                        [TRAY_DTL_ID],
	                                                        [TRAY_ID],
	                                                        [MATERIEL_ID],
	                                                        [MATERIEL_CODE],
	                                                        [MATERIEL_NAME],
	                                                        [MATERIEL_BARCODE],
	                                                        [QTY],
	                                                        [TRAY_DTL_STATUS],
	                                                        [DESCRIBE],
	                                                        [CREATE_BY],
	                                                        [CREATE_TIME],
	                                                        [UPDATE_BY],
	                                                        [UPDATE_TIME],
	                                                        [DATA_VERSION],
	                                                        [REMARKS1],
	                                                        [REMARKS2],
	                                                        [REMARKS3],
	                                                        [REMARKS4],
	                                                        [REMARKS5]
                                                        )
                                                        VALUES
	                                                        (
		                                                        '{item.TRAY_DTL_ID}',
		                                                        '{item.TRAY_ID}',
		                                                        '{item.MATERIEL_ID}',
		                                                        '{item.MATERIEL_CODE}',
		                                                        '{item.MATERIEL_NAME}',
		                                                        '{item.MATERIEL_BARCODE}',
		                                                        '{item.QTY}',
		                                                        '{55}',
		                                                        '{item.DESCRIBE}',
		                                                        '{item.CREATE_BY}',
		                                                        getdate(),
		                                                        '{item.UPDATE_BY}',
		                                                        getdate(),
		                                                        0,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL
	                                                        );
                                                    ";
                        string sqlAddWmsStkTrayDtlExt = $@"
                                                            INSERT INTO [WMS_STK_TRAY_DTL_EXT] (
	                                                            [TRAY_DTL_EXT_ID],
	                                                            [TRAY_DTL_ID],
	                                                            [MATERIEL_SPEC],
	                                                            [PACKAGE_CODE],
	                                                            [UNIT_CODE],
	                                                            [BATCH_NO],
	                                                            [SUPPLIER_CODE],
	                                                            [SUPPLIER_NAME],
	                                                            [PRODUCT_DATE],
	                                                            [EXP_DATE],
	                                                            [INSPECTION_RESULT],
	                                                            [LOCK_FLAG],
	                                                            [MIN_PKG_QTY],
	                                                            [ECTEND_TIME_LEN],
	                                                            [SUPPLIER_BATCH],
	                                                            [ITEM_STATUS],
	                                                            [DESCRIBE],
	                                                            [CREATE_BY],
	                                                            [CREATE_TIME],
	                                                            [UPDATE_BY],
	                                                            [UPDATE_TIME],
	                                                            [DATA_VERSION],
	                                                            [REMARKS1],
	                                                            [REMARKS2],
	                                                            [REMARKS3],
	                                                            [REMARKS4],
	                                                            [REMARKS5]
                                                            )
                                                            VALUES
	                                                            (
		                                                            '{trayDtlExtMd.TRAY_DTL_EXT_ID}',
		                                                            '{trayDtlExtMd.TRAY_DTL_ID}',
		                                                            '{trayDtlExtMd.MATERIEL_SPEC}',
		                                                            '{trayDtlExtMd.PACKAGE_CODE}',
		                                                            '{trayDtlExtMd.UNIT_CODE}',
		                                                            '{trayDtlExtMd.BATCH_NO}',
		                                                            '{trayDtlExtMd.SUPPLIER_CODE}',
		                                                            '{trayDtlExtMd.SUPPLIER_NAME}',
		                                                            '{trayDtlExtMd.PRODUCT_DATE}',
		                                                            '{trayDtlExtMd.EXP_DATE}',
		                                                            '{trayDtlExtMd.INSPECTION_RESULT}',
		                                                            '{0}',
		                                                            '{trayDtlExtMd.MIN_PKG_QTY}',
		                                                            '{trayDtlExtMd.ECTEND_TIME_LEN}',
		                                                            '{trayDtlExtMd.SUPPLIER_BATCH}',
		                                                            '{trayDtlExtMd.ITEM_STATUS}',
		                                                            '{trayDtlExtMd.DESCRIBE}',
		                                                            '{trayDtlExtMd.CREATE_BY}',
		                                                            getdate(),
		                                                            '{trayDtlExtMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                            ";
                        sqlList_TrayDtlAndExt.Add(sqlAddWmsStkTrayDtl);
                        sqlList_TrayDtlAndExt.Add(sqlAddWmsStkTrayDtlExt);
                    }

                    sqlList.AddRange(sqlList_TrayDtlAndExt);
                    sqlList.AddRange(sqlList_ReceiprRecordAndPutAway);

                    foreach (int item in tmpReceiptIdLst)
                    {
                        string sqlUpdateWmsInReceipt = $@"
                                                        IF EXISTS (
	                                                        SELECT
		                                                        1
	                                                        FROM
		                                                        WMS_IN_RECEIPT_DTL
	                                                        WHERE
		                                                        RECEIPT_DTL_STATUS = 55
	                                                        AND RECEIPT_ID = '{item}'
                                                        ) UPDATE WMS_IN_RECEIPT
                                                        SET RECEIPT_STATUS = 55,
                                                            UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
                                                            UPDATE_TIME = GETDATE(),
                                                            DATA_VERSION = DATA_VERSION + 1
                                                        WHERE
	                                                        RECEIPT_ID = '{item}' ;
                                                    ";
                        sqlList.Add(sqlUpdateWmsInReceipt);
                    }
                    
                    #endregion

                    int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                    if (row > 0)
                    {
                        return SuccessMessageStatus("新增收货组盘数据成功！", row);
                    }
                    else
                    {
                        return FailMessageStatus("新增收货组盘数据失败！", row);
                    }
                }
                else
                {
                    return FailMessageStatus($"传入数据不存在组盘明细数据！");
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"添加收货组盘数据发生异常，【{ex.Message}】");
            }
        }

        public OperateResultInfo CheckPalletValidity(string palletCode)
        {
            try
            {
                string sqlQueryBasPallet = $@"
                                            SELECT
	                                            *
                                            FROM
	                                            BAS_PALLET
                                            WHERE
	                                            PALLET_CODE = '{palletCode}'
                                            ";
                List<PalletResult> palletList = new DataRepository<PalletResult>(_dataContext).Query(sqlQueryBasPallet).ToList();
                if (palletList == null && palletList.Count <= 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】没有在基础托盘表维护数据，请先在维护托盘基础数据！");
                }
                string sqlQueryPutAway = $@"
                                            SELECT
	                                            PUTAWAY_ID,
	                                            PUTAWAY_NO,
	                                            SOURCE_NO,
	                                            PUTAWAY_TYPE,
	                                            PUTAWAY_TYPE_NAME,
                                                TRAY_CODE,
	                                            PALLET_CODE,
	                                            SBIN_CODE,
	                                            EBIN_CODE,
	                                            PUTAWAY_PRIORITY,
	                                            PUTAWAY_STATUS,
	                                            PUTAWAY_STATUS_NAME,
	                                            [DESCRIBE],
	                                            CREATE_BY,
	                                            CREATE_NAME,
	                                            CREATE_TIME,
	                                            UPDATE_BY,
	                                            UPDATE_NAME,
	                                            UPDATE_TIME
                                            FROM
	                                            VW_WMS_IN_PUTAWAY
                                            WHERE
                                                PALLET_CODE = '{palletCode}' AND PUTAWAY_STATUS < 99
                                            ";
                List<WmsInPutAwayResult> putawayList = new DataRepository<WmsInPutAwayResult>(_dataContext).Query(sqlQueryPutAway).ToList();
                if (putawayList != null && putawayList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在未完成的上架单数据，无法再次进行组盘！");
                }
                string sqlQueryPutDown = $@"
                                            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
                                                PALLET_CODE = '{palletCode}' AND PUTDOWN_STATUS < 99
                                            ";
                List<WmsOutPutDownResult> putdownList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryPutDown).ToList();
                if (putdownList != null && putdownList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在未完成的下架单数据，无法再次进行组盘！");
                }
                string sqlQueryWmsTask = $@"
                                            SELECT
	                                            *
                                            FROM
                                                VW_WMS_TSK_TASK 
                                            WHERE
                                                PALLET_CODE = '{palletCode}' AND TASK_STATUS < 99
                                        ";
                List<WmsTaskResult> wmstaskList = new DataRepository<WmsTaskResult>(_dataContext).Query(sqlQueryWmsTask).ToList();
                if (wmstaskList != null && wmstaskList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在未完成的WMS大任务数据，无法再次进行组盘！");
                }
                string sqlQueryCrnCmd = $@"
                                            SELECT
	                                            *
                                            FROM
                                                VW_WCS_CRN_CMD 
                                            WHERE
                                                PALLET_CODE = '{palletCode}' AND CMD_STATUS < 99
                                        ";
                List<WcsCrnCmdResult> crncmdList = new DataRepository<WcsCrnCmdResult>(_dataContext).Query(sqlQueryCrnCmd).ToList();
                if (crncmdList != null && crncmdList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在未完成的堆垛机指令数据，无法再次进行组盘！");
                }
                string sqlQueryTranCmd = $@"
                                            SELECT
	                                            *
                                            FROM
                                                VW_WCS_TRAN_CMD 
                                            WHERE
                                                 PALLET_CODE = '{palletCode}' AND CMD_STATUS < 99
                                        ";
                List<WcsTranCmdResult> trancmdList = new DataRepository<WcsTranCmdResult>(_dataContext).Query(sqlQueryTranCmd).ToList();
                if (trancmdList != null && trancmdList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在未完成的输送线指令数据，无法再次进行组盘！");
                }
                string sqlQueryBalance = $@"
                                            SELECT
	                                            *
                                            FROM
                                                VW_WMS_STK_BALANCE 
                                            WHERE
                                                PALLET_CODE = '{palletCode}' AND BALANCE_STATUS < 99
                                        ";
                List<WmsStkBalanceResult> balanceList = new DataRepository<WmsStkBalanceResult>(_dataContext).Query(sqlQueryBalance).ToList();
                if (balanceList != null && balanceList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在库存数据，无法再次进行组盘！");
                }
                string sqlQueryWmsStkTray = $@"SELECT * FROM VW_WMS_STK_TRAY WHERE PALLET_CODE = '{palletCode}' AND TRAY_STATUS < 99";
                List<WmsStkTrayResult> resultWmsStkTrayList = new DataRepository<WmsStkTrayResult>(_dataContext).Query(sqlQueryWmsStkTray).ToList();
                if (balanceList != null && balanceList.Count > 0)
                {
                    return FailMessageStatus($"托盘号：【{palletCode}】正在已存在组盘数据，无法再次进行组盘！");
                }
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"托盘号：【{palletCode}】合法性校验发生异常：{ex.Message}");
            }
        }
        /// <summary>
        /// 查询待检验的收货单数据
        /// </summary>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInReceiptDtlResult>> GetAllWaitForQaReceiptDtlData()
        {
            try
            {
                string sqlQueryWaitQaReceiptDtl = "SELECT * FROM VW_WMS_IN_RECEIPT_DTL WHERE RECEIPT_DTL_STATUS = 0 AND INSPECTION_RESULT = 'Wait'";
                List<WmsInReceiptDtlResult> resultList = new DataRepository<WmsInReceiptDtlResult>(_dataContext).Query(sqlQueryWaitQaReceiptDtl).ToList();
                OperateResultInfo<List<WmsInReceiptDtlResult>> retDataMsg = SuccessStatus(resultList);
                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsInReceiptDtlResult>>($"查询待检验的收货单数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 同步ERP
        /// </summary>
        /// <param name="wmsInPutAway"></param>
        /// <returns>Erp生成的入库单号</returns>

        public int AsyncErp(string ReceiptNo)
        {
            //获取入库单
            var Receipt = new DataRepository<DataAccess.Entity.WMS_IN_RECEIPT>(this._dataContext).QueryFirst(" RECEIPT_NO=@ReceiptNo", new { ReceiptNo });
            var RecepipRecord = new DataRepository<DataAccess.Entity.WMS_IN_RECEIPT_RECORD>(this._dataContext).Query(" RECEIPT_NO=@ReceiptNo", new { ReceiptNo });
            var RecepipDetails = new DataRepository<DataAccess.Entity.WMS_IN_RECEIPT_DTL>(this._dataContext).Query("RECEIPT_ID=@ReceiptId", new { ReceiptId = Receipt.RECEIPT_ID });
            //查询对应到货单信息
            var Arrival = new DataRepository<DataAccess.Entity.WMS_IN_ARRIVAL>(this._dataContext).QueryFirst("ARRIVAL_NO=@ArrivalNo", new { ArrivalNo = Receipt.ARRIVAL_NO });
            var ArrivalDetails = new DataRepository<DataAccess.Entity.WMS_IN_ARRIVAL_DTL>(this._dataContext).Query("ARRIVAL_ID=@ArrivalId", new { ArrivalId = Arrival.ARRIVAL_ID });
            //构造查询对象
            var lookupRecepipDetails = RecepipDetails.ToDictionary(m => m.RECEIPT_DTL_ID);
            var lookupArrivalDetail = ArrivalDetails.ToDictionary(m => m.ARRIVAL_DTL_ID);
            var inBound = new Model.AppModels.Result.ERP.Inbound()
            {
                InboundDate = DateTime.Now,
                InboundNo = Receipt.ARRIVAL_NO,
                InboundType = Model.AppModels.Result.ERP.InboundType.PurchaseIn,
                SourceName = "采购到货单",
                SourceNo = Arrival.ERP_ID,
                WarehouseCode = "2"
            };
            foreach (var item in RecepipRecord)
            {
                int ERPDetailId = -1;
                if (lookupRecepipDetails.TryGetValue(item.RECEIPT_DTL_ID, out var RecepipDetail))
                {
                    if (lookupArrivalDetail.TryGetValue(RecepipDetail.ARRIVAL_DTL_ID.Value, out var ArrivalDetail))
                    {

                        ERPDetailId = ArrivalDetail.ERP_DTL_ID;
                    }
                }

                if (ERPDetailId != -1)
                {
                    inBound.InboundDetail.Add(new Model.AppModels.Result.ERP.InboundDetail()
                    {
                        BinCode = item.BIN_CODE,
                        Qty = item.RECEIPT_QTY,
                        MaterialCode = item.MATERIEL_CODE,
                        SourceDtlId = ERPDetailId
                    });
                }
            }
            return this._eRPServer.PuStockIn(inBound).Result;
        }
    }
}