using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
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.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(IWmsInRetreatService), InjectType.Scope)]
    public class WmsInRetreatService : ServiceBase, IWmsInRetreatService
    {
        #region 全局变量、构造注入
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

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

        /// <summary>
        /// 分页查询退料单主表数据
        /// </summary>
        /// <param name="retreatSearchMd">退料单查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInRetreatResult>> GetWmsInRetreatListForPage(WmsInRetreatSearchMd retreatSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(retreatSearchMd.RetreatNoMsg))
                {
                    sqlCondition.Append($" AND RETREAT_NO = '{retreatSearchMd.RetreatNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.SourceNoMsg))
                {
                    sqlCondition.Append($" AND SOUCE_NO = '{retreatSearchMd.SourceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.RetreaterMsg))
                {
                    sqlCondition.Append($" AND RETREATER = '{retreatSearchMd.RetreaterMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.RetreatType))
                {
                    sqlCondition.Append($" AND RETREAT_TYPE = '{retreatSearchMd.RetreatType}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.RetreatStatus))
                {
                    sqlCondition.Append($" AND RETREAT_STATUS = '{retreatSearchMd.RetreatStatus}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE BATCH_NO = '{retreatSearchMd.BatchNoMsg}')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE MATERIEL_CODE LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{retreatSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE SUPPLIER_CODE LIKE '%{retreatSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{retreatSearchMd.SupplierMsg}%')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.StartCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME >= '{retreatSearchMd.StartCreateTime}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.EndCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME <= '{retreatSearchMd.EndCreateTime}'");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.StartProductTime))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE >= '{retreatSearchMd.StartProductTime}')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.EndProductTime))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE <= '{retreatSearchMd.EndProductTime}')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.StartExpTime))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE >= '{retreatSearchMd.StartExpTime}')");
                }
                if (!string.IsNullOrEmpty(retreatSearchMd.EndExpTime))
                {
                    sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE <= '{retreatSearchMd.EndExpTime}')");
                }
                StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT WHERE 1=1");
                sqlCountRetreatData.Append(sqlCondition.ToString());

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

                StringBuilder sqlQueryRetreatData = new StringBuilder($@"
                                                                        SELECT
	                                                                        RETREAT_ID,
	                                                                        RETREAT_NO,
	                                                                        SOUCE_NO,
	                                                                        RETREAT_TYPE,
	                                                                        RETREAT_TYPE_NAME,
                                                                            RETREAT_TIME,
	                                                                        RETREATER,
	                                                                        RETREAT_STATUS,
	                                                                        RETREAT_STATUS_NAME,
	                                                                        [DESCRIBE],
	                                                                        CREATE_BY,
	                                                                        CREATE_NAME,
	                                                                        CREATE_TIME,
	                                                                        UPDATE_BY,
	                                                                        UPDATE_NAME,
	                                                                        UPDATE_TIME
                                                                        FROM
	                                                                        VW_WMS_IN_RETREAT
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {retreatSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion
                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountRetreatData.ToString()));
                List<WmsInRetreatResult> resultList = new DataRepository<WmsInRetreatResult>(_dataContext).Query(sqlQueryRetreatData.ToString()).ToList();
                OperateResultInfo<List<WmsInRetreatResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

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

        /// <summary>
        /// 根据退料单主键Id查询退料单明细数据
        /// </summary>
        /// <param name="wmsInRetreat">退料单对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsInRetreatResult> GetWmsInRetreatDtlListForID(WmsInRetreatResult wmsInRetreat)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsInRetreat = $@"
                                                SELECT
	                                                RETREAT_ID,
	                                                RETREAT_NO,
	                                                SOUCE_NO,
	                                                RETREAT_TYPE,
	                                                RETREAT_TYPE_NAME,
                                                    RETREAT_TIME,
	                                                RETREATER,
	                                                RETREAT_STATUS,
	                                                RETREAT_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_IN_RETREAT
                                                WHERE   
                                                    RETREAT_ID = '{wmsInRetreat.RETREAT_ID}'
                                              ";
                string strWhere = string.Empty;
                if (wmsInRetreat.REMARKS1 == "查询已删除明细数据")
                {
                    strWhere = "1=1";
                }
                else if (wmsInRetreat.REMARKS1 == "查询未组盘完成的退料单明细")
                {
                    strWhere = "RETREAT_DTL_STATUS < 99 AND RETREAT_TRAY_QTY < RETREAT_QTY";
                }
                else
                {
                    strWhere = "RETREAT_DTL_STATUS < 111";
                }
                string sqlQueryWmsRetreatDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_IN_RETREAT_DTL
                                                    WHERE
	                                                    RETREAT_ID = '{wmsInRetreat.RETREAT_ID}'
                                                    AND {strWhere}
                                                    ORDER BY RETREAT_DTL_STATUS,RETREAT_DTL_ID
                                                    ";
                #endregion
                List<WmsInRetreatResult> resultList = new DataRepository<WmsInRetreatResult>(_dataContext).Query(sqlQueryWmsInRetreat).ToList();
                List<WmsInRetreatDtlResult> resultDtlList = new DataRepository<WmsInRetreatDtlResult>(_dataContext).Query(sqlQueryWmsRetreatDtl).ToList();
                wmsInRetreat = resultList[0];
                wmsInRetreat.WmsInRetreatDtlList = resultDtlList;
                OperateResultInfo<WmsInRetreatResult> retDataMsg = SuccessStatus(wmsInRetreat);

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<WmsInRetreatResult>($"查询退料单明细数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 新增退料单数据
        /// </summary>
        /// <param name="wmsInRetreat">退料单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                wmsInRetreat.RETREAT_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Retreat_Id"));
                wmsInRetreat.RETREAT_NO = new DataRepository<object>(_dataContext).GetSequenceMsg("Retreat_No");
                wmsInRetreat.RETREAT_STATUS = 0;
                StringBuilder sqlAddWmsRetreat = new StringBuilder();
                string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'";
                sqlAddWmsRetreat.Append($@"
                                            INSERT INTO [WMS_IN_RETREAT] (
	                                            [RETREAT_ID],
	                                            [RETREAT_NO],
	                                            [RETREATER],
	                                            [RETREAT_TYPE],
	                                            [RETREAT_TIME],
	                                            [SOUCE_NO],
	                                            [RETREAT_STATUS],
	                                            [DESCRIBE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
	                                            (
		                                            '{wmsInRetreat.RETREAT_ID}',
		                                            '{wmsInRetreat.RETREAT_NO}',
		                                            '{wmsInRetreat.RETREATER}',
		                                            '{wmsInRetreat.RETREAT_TYPE}',
		                                            {retreatTime},
		                                            '{wmsInRetreat.SOUCE_NO}',
		                                            '{wmsInRetreat.RETREAT_STATUS}',
		                                            '{wmsInRetreat.DESCRIBE}',
		                                            '{wmsInRetreat.CREATE_BY}',
		                                            getdate(),
		                                            '{wmsInRetreat.UPDATE_BY}',
		                                            getdate(),
		                                            0,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL
	                                            );
                                         ");
                sqlList.Add(sqlAddWmsRetreat.ToString());
                
                foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList)
                {
                    StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder();
                    item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Retreat_Dtl_Id"));
                    item.RETREAT_ID = wmsInRetreat.RETREAT_ID;
                    item.RETREAT_DTL_STATUS = 0;
                    sqlAddWmsInRetreatDtl.Append($@"
                                                    INSERT INTO [WMS_IN_RETREAT_DTL] (
	                                                    [RETREAT_DTL_ID],
	                                                    [RETREAT_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [RETREAT_QTY],
	                                                    [RETREAT_TRAY_QTY],
	                                                    [PUTAWAY_QTY],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [RETREAT_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.RETREAT_DTL_ID}',
		                                                    '{item.RETREAT_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.RETREAT_QTY}',
		                                                    '{item.RETREAT_TRAY_QTY}',
		                                                    '{item.PUTAWAY_QTY}',
		                                                    '{item.SUPPLIER_CODE}',
		                                                    '{item.SUPPLIER_NAME}',
		                                                    '{item.PRODUCT_DATE}',
		                                                    '{item.EXP_DATE}',
		                                                    '0',
		                                                    'Wait',
		                                                    '1',
		                                                    '{item.DESCRIBE}',
		                                                    '{item.CREATE_BY}',
		                                                    getdate(),
		                                                    '{item.UPDATE_BY}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                    sqlList.Add(sqlAddWmsInRetreatDtl.ToString());
                }
                #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="wmsInRetreat">退料主表对象</param>
        /// <returns></returns>
        public OperateResultInfo EditWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'";
                string sqlUpdateWmsInRetreat = $@"
                                                UPDATE [WMS_IN_RETREAT]
                                                SET 
                                                 [RETREAT_NO] = '{wmsInRetreat.RETREAT_NO}',
                                                 [RETREATER] = '{wmsInRetreat.RETREATER}',
                                                 [RETREAT_TYPE] = '{wmsInRetreat.RETREAT_TYPE}',
                                                 [RETREAT_TIME] = {retreatTime},
                                                 [SOUCE_NO] = '{wmsInRetreat.SOUCE_NO}',
                                                 [RETREAT_STATUS] = '{wmsInRetreat.RETREAT_STATUS}',
                                                 [DESCRIBE] = '{wmsInRetreat.DESCRIBE}',
                                                 [UPDATE_BY] = '{wmsInRetreat.UPDATE_BY}',
                                                 [UPDATE_TIME] = GETDATE(),
                                                 [DATA_VERSION] = [DATA_VERSION] + 1
                                                WHERE
	                                                [RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}';
                                                ";
                sqlList.Add(sqlUpdateWmsInRetreat);
                foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList)
                {
                    if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除")
                    {
                        string sqlUpdateWmsInRetreatDtl = $@"
                                                            UPDATE [WMS_IN_RETREAT_DTL]
                                                            SET 
                                                             [MATERIEL_CODE] = '{item.MATERIEL_CODE}',
                                                             [MATERIEL_NAME] = '{item.MATERIEL_NAME}',
                                                             [MATERIEL_BARCODE] = '{item.MATERIEL_BARCODE}',
                                                             [MATERIEL_SPEC] = '{item.MATERIEL_SPEC}',
                                                             [BATCH_NO] = '{item.BATCH_NO}',
                                                             [PACKAGE_CODE] = '{item.PACKAGE_CODE}',
                                                             [UNIT_CODE] = '{item.UNIT_CODE}',
                                                             [RETREAT_QTY] = '{item.RETREAT_QTY}',
                                                             [RETREAT_TRAY_QTY] = '{item.RETREAT_TRAY_QTY}',
                                                             [PUTAWAY_QTY] = '{item.PUTAWAY_QTY}',
                                                             [SUPPLIER_CODE] = '{item.SUPPLIER_CODE}',
                                                             [SUPPLIER_NAME] = '{item.SUPPLIER_CODE}',
                                                             [PRODUCT_DATE] = '{item.PRODUCT_DATE}',
                                                             [EXP_DATE] = '{item.EXP_DATE}',
                                                             [RETREAT_DTL_STATUS] = '{item.RETREAT_DTL_STATUS}',
                                                             [INSPECTION_RESULT] = '{item.INSPECTION_RESULT}',
                                                             [ITEM_STATUS] = '{item.ITEM_STATUS}',
                                                             [DESCRIBE] = '{item.DESCRIBE}',
                                                             [UPDATE_BY] = '{item.UPDATE_BY}',
                                                             [UPDATE_TIME] = getdate(),
                                                             [DATA_VERSION] = [DATA_VERSION] + 1
                                                            WHERE
	                                                            [RETREAT_DTL_ID] = '{item.RETREAT_DTL_ID}' 
                                                            AND [RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}';
                                                        ";
                        sqlList.Add(sqlUpdateWmsInRetreatDtl);
                    }
                    if (item.REMARKS1 == "添加")
                    {
                        StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder();
                        item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Retreat_Dtl_Id"));
                        item.RETREAT_ID = wmsInRetreat.RETREAT_ID;
                        item.RETREAT_DTL_STATUS = 0;
                        sqlAddWmsInRetreatDtl.Append($@"
                                                    INSERT INTO [WMS_IN_RETREAT_DTL] (
	                                                    [RETREAT_DTL_ID],
	                                                    [RETREAT_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [RETREAT_QTY],
	                                                    [RETREAT_TRAY_QTY],
	                                                    [PUTAWAY_QTY],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [RETREAT_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.RETREAT_DTL_ID}',
		                                                    '{item.RETREAT_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.RETREAT_QTY}',
		                                                    '{item.RETREAT_TRAY_QTY}',
		                                                    '{item.PUTAWAY_QTY}',
		                                                    '{item.SUPPLIER_CODE}',
		                                                    '{item.SUPPLIER_NAME}',
		                                                    '{item.PRODUCT_DATE}',
		                                                    '{item.EXP_DATE}',
		                                                    '0',
		                                                    'Wait',
		                                                    '1',
		                                                    '{item.DESCRIBE}',
		                                                    '{item.CREATE_BY}',
		                                                    getdate(),
		                                                    '{item.UPDATE_BY}',
		                                                    getdate(),
		                                                    0,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                        sqlList.Add(sqlAddWmsInRetreatDtl.ToString());
                    }
                }
                #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="wmsInRetreat">退料单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo DeleteWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
        {
            try
            {
                #region SQL语句生成
                string[] retreatIdList = wmsInRetreat.RETREAT_NO.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in retreatIdList)
                {
                    string sqlDeleteWmsRetreat = $@"
                                                UPDATE WMS_IN_RETREAT
                                                SET RETREAT_STATUS = '111',
                                                 UPDATE_BY = '{wmsInRetreat.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                RETREAT_ID = '{item}';
                                                ";
                    string sqlDeleteWmsInRetreatDtl = $@"
                                                    UPDATE WMS_IN_RETREAT_DTL
                                                    SET RETREAT_DTL_STATUS = '111',
                                                     UPDATE_BY = '{wmsInRetreat.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    RETREAT_ID = '{item}';
                                                    ";
                    sqlList.Add(sqlDeleteWmsRetreat);
                    sqlList.Add(sqlDeleteWmsInRetreatDtl);
                    /*
                     ToDo: 后续增加删除移至历史表中。
                     */
                }
                #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="retreatRecordSearchMd">退料记录查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsInRetreatRecordResult>> GetWmsInRetreatRecordListForPage(WmsInRetreatRecordSearchMd retreatRecordSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatNoMsg))
                {
                    sqlCondition.Append($" AND RETREAT_NO = '{retreatRecordSearchMd.RetreatNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.SourceNoMsg))
                {
                    sqlCondition.Append($" AND SOURCE_NO = '{retreatRecordSearchMd.SourceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.RegionNoMsg))
                {
                    sqlCondition.Append($" AND REGION_CODE = '{retreatRecordSearchMd.RegionNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.BinNoMsg))
                {
                    sqlCondition.Append($" AND (BIN_CODE like '%{retreatRecordSearchMd.BinNoMsg}%' OR BIN_NAME like '%{retreatRecordSearchMd.BinNoMsg}%')");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.PalletNoMsg))
                {
                    sqlCondition.Append($" AND PALLET_CODE = '{retreatRecordSearchMd.PalletNoMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreaterMsg))
                {
                    sqlCondition.Append($" AND RETREATER = '{retreatRecordSearchMd.RetreaterMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_CODE like '%{retreatRecordSearchMd.MaterielMsg}%' OR MATERIEL_NAME = '%{retreatRecordSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielTypeMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_TYPE_CODE = '%{retreatRecordSearchMd.MaterielTypeMsg}%' OR MATERIEL_TYPE_NAME = '%{retreatRecordSearchMd.MaterielTypeMsg}%')");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND SUPPLIER_CODE = '{retreatRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND BATCH_NO = '{retreatRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.ItemStatusMsg))
                {
                    sqlCondition.Append($" AND ITEM_STATUS = '{retreatRecordSearchMd.ItemStatusMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.InspectionResultMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_RESULT = '{retreatRecordSearchMd.InspectionResultMsg}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatRecordStatus))
                {
                    sqlCondition.Append($" AND RETREAT_RECORD_STATUS = '{retreatRecordSearchMd.RetreatRecordStatus}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartRetreatTime))
                {
                    sqlCondition.Append($" AND RETREAT_TIME >= '{retreatRecordSearchMd.StartRetreatTime}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndRetreatTime))
                {
                    sqlCondition.Append($" AND RETREAT_TIME <= '{retreatRecordSearchMd.EndRetreatTime}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE >= '{retreatRecordSearchMd.StartProductTime}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE <= '{retreatRecordSearchMd.EndProductTime}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE >= '{retreatRecordSearchMd.StartExpTime}'");
                }
                if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE <= '{retreatRecordSearchMd.EndExpTime}'");
                }
                StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT_RECORD WHERE 1=1");
                sqlCountRetreatData.Append(sqlCondition.ToString());

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

                StringBuilder sqlQueryRetreatData = new StringBuilder($@"
                                                                        SELECT
                                                                            RETREAT_RECORD_ID,
                                                                            NEWID,
                                                                            RETREAT_ID,
                                                                            RETREAT_NO,
                                                                            RETREAT_DTL_ID,
                                                                            SOURCE_NO,
                                                                            REGION_CODE,
                                                                            REGION_NAME,
                                                                            BIN_CODE,
                                                                            BIN_NAME,
                                                                            TRAY_CODE,
                                                                            PALLET_CODE,
                                                                            RETREAT_TIME,
                                                                            RETREATER,
                                                                            MATERIEL_TYPE_CODE,
                                                                            MATERIEL_TYPE_NAME,
                                                                            MATERIEL_CODE,
                                                                            MATERIEL_NAME,
                                                                            MATERIEL_BARCODE,
                                                                            MATERIEL_SPEC,
                                                                            SUPPLIER_CODE,
                                                                            SUPPLIER_NAME,
                                                                            BATCH_NO,
                                                                            PACKAGE_CODE,
                                                                            UNIT_CODE,
                                                                            RETREAT_QTY,
                                                                            RETREAT_REASON,
                                                                            PRODUCT_DATE,
                                                                            EXP_DATE,
                                                                            RETREAT_RECORD_STATUS,
                                                                            RETREAT_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_RETREAT_RECORD
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {retreatRecordSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion

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

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

        /// <summary>
        /// 添加退料组盘数据
        /// </summary>
        /// <param name="wmsStkTrayResult">组盘信息实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsInRetreatTrayData(WmsStkTrayResult wmsStkTrayResult)
        {
            try
            {
                if (wmsStkTrayResult.WmsStkTrayDtlList.Count > 0)
                {
                    #region SQL语句生成
                    List<string> sqlList = new List<string>();
                    string sqlQueryRetreatRegionBinMsg = $@"
                                                            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(sqlQueryRetreatRegionBinMsg).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].WmsInRetreatRecord.RETREAT_NO}',
		                                                    '{2}',
		                                                    '{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);

                    foreach (WmsStkTrayDtlResult item in wmsStkTrayResult.WmsStkTrayDtlList)
                    {
                        WmsStkTrayDtlExtResult trayDtlExtMd = item.WmsStkTrayDtlExtMd;
                        WmsInRetreatRecordResult retreatRecordMd = item.WmsInRetreatRecord;
                        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;
                        retreatRecordMd.RETREAT_RECORD_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Retreat_Record_Id"));
                        int putawayDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id"));
                        retreatRecordMd.TRAY_CODE = wmsStkTrayResult.TRAY_CODE;
                        retreatRecordMd.REGION_CODE = binMd.REGION_CODE;
                        retreatRecordMd.REGION_NAME = binMd.REGION_NAME;
                        retreatRecordMd.BIN_CODE = binMd.BIN_CODE;
                        retreatRecordMd.BIN_NAME = binMd.BIN_NAME;

                        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}',
		                                                        '{0}',
		                                                        '{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
	                                                            );
                                                            ";
                        string sqlAddWmsInRetreatRecord = $@"
                                                            INSERT INTO [WMS_IN_RETREAT_RECORD] (
	                                                            [RETREAT_RECORD_ID],
	                                                            [RETREAT_ID],
	                                                            [RETREAT_NO],
	                                                            [RETREAT_DTL_ID],
	                                                            [SOURCE_NO],
	                                                            [REGION_CODE],
	                                                            [REGION_NAME],
	                                                            [BIN_CODE],
	                                                            [BIN_NAME],
	                                                            [TRAY_CODE],
	                                                            [PALLET_CODE],
	                                                            [RETREAT_TIME],
	                                                            [RETREATER],
	                                                            [MATERIEL_CODE],
	                                                            [MATERIEL_NAME],
	                                                            [MATERIEL_BARCODE],
	                                                            [MATERIEL_SPEC],
	                                                            [SUPPLIER_CODE],
	                                                            [SUPPLIER_NAME],
	                                                            [BATCH_NO],
	                                                            [PACKAGE_CODE],
	                                                            [UNIT_CODE],
	                                                            [RETREAT_QTY],
	                                                            [PRODUCT_DATE],
	                                                            [EXP_DATE],
	                                                            [RETREAT_RECORD_STATUS],
	                                                            [INSPECTION_RESULT],
	                                                            [ITEM_STATUS],
	                                                            [DESCRIBE],
	                                                            [CREATE_BY],
	                                                            [CREATE_TIME],
	                                                            [UPDATE_BY],
	                                                            [UPDATE_TIME],
	                                                            [DATA_VERSION],
	                                                            [REMARKS1],
	                                                            [REMARKS2],
	                                                            [REMARKS3],
	                                                            [REMARKS4],
	                                                            [REMARKS5]
                                                            )
                                                            VALUES
	                                                            (
		                                                            '{retreatRecordMd.RETREAT_RECORD_ID}',
		                                                            '{retreatRecordMd.RETREAT_ID}',
		                                                            '{retreatRecordMd.RETREAT_NO}',
		                                                            '{retreatRecordMd.RETREAT_DTL_ID}',
		                                                            '{retreatRecordMd.SOURCE_NO}',
		                                                            '{retreatRecordMd.REGION_CODE}',
		                                                            '{retreatRecordMd.REGION_NAME}',
		                                                            '{retreatRecordMd.BIN_CODE}',
		                                                            '{retreatRecordMd.BIN_NAME}',
		                                                            '{retreatRecordMd.TRAY_CODE}',
		                                                            '{retreatRecordMd.PALLET_CODE}',
		                                                            '{retreatRecordMd.RETREAT_TIME}',
		                                                            '{retreatRecordMd.RETREATER}',
		                                                            '{retreatRecordMd.MATERIEL_CODE}',
		                                                            '{retreatRecordMd.MATERIEL_NAME}',
		                                                            '{retreatRecordMd.MATERIEL_BARCODE}',
		                                                            '{retreatRecordMd.MATERIEL_SPEC}',
		                                                            '{retreatRecordMd.SUPPLIER_CODE}',
		                                                            '{retreatRecordMd.SUPPLIER_NAME}',
		                                                            '{retreatRecordMd.BATCH_NO}',
		                                                            '{retreatRecordMd.PACKAGE_CODE}',
		                                                            '{retreatRecordMd.UNIT_CODE}',
		                                                            '{retreatRecordMd.RETREAT_QTY}',
		                                                            '{retreatRecordMd.PRODUCT_DATE}',
		                                                            '{retreatRecordMd.EXP_DATE}',
		                                                            '{retreatRecordMd.RETREAT_RECORD_STATUS}',
		                                                            '{retreatRecordMd.INSPECTION_RESULT}',
		                                                            '{retreatRecordMd.ITEM_STATUS}',
		                                                            '{retreatRecordMd.DESCRIBE}',
		                                                            '{retreatRecordMd.CREATE_BY}',
		                                                            getdate(),
		                                                            '{retreatRecordMd.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}',
		                                                            '{trayDtlExtMd.EXP_DATE}',
		                                                            '{trayDtlExtMd.INSPECTION_RESULT}',
		                                                            '{0}',
		                                                            '{trayDtlExtMd.ITEM_STATUS}',
		                                                            '{item.DESCRIBE}',
		                                                            '{trayDtlExtMd.CREATE_BY}',
		                                                            getdate(),
		                                                            '{trayDtlExtMd.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                        ";
                        sqlList.Add(sqlAddWmsStkTrayDtl);
                        sqlList.Add(sqlAddWmsStkTrayDtlExt);
                        sqlList.Add(sqlAddWmsInRetreatRecord);
                        sqlList.Add(sqlAddWmsInPutAwayDtl);
                        string sqlUpdateWmsInRetreatDtl = $@"
                                                            UPDATE WMS_IN_RETREAT_DTL
                                                            SET 
                                                             RETREAT_TRAY_QTY = RETREAT_TRAY_QTY + '{item.QTY}',
                                                             RETREAT_DTL_STATUS = '{55}',
                                                             UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
                                                             UPDATE_TIME = GETDATE(),
                                                             DATA_VERSION = DATA_VERSION + 1
                                                            WHERE
	                                                            RETREAT_DTL_ID = '{retreatRecordMd.RETREAT_DTL_ID}'
                                                            AND RETREAT_ID = '{retreatRecordMd.RETREAT_ID}'
                                                         ";
                        sqlList.Add(sqlUpdateWmsInRetreatDtl);
                    }

                    string sqlUpdateWmsInRetreat = $@"
                                                        IF EXISTS (
	                                                        SELECT
		                                                        1
	                                                        FROM
		                                                        WMS_IN_RETREAT_DTL
	                                                        WHERE
		                                                        RETREAT_DTL_STATUS = 55
	                                                        AND RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}'
                                                        ) UPDATE WMS_IN_RETREAT
                                                        SET RETREAT_STATUS = 55,
                                                            UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
                                                            UPDATE_TIME = GETDATE(),
                                                            DATA_VERSION = DATA_VERSION + 1
                                                        WHERE
	                                                        RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}' ;
                                                    ";
                    sqlList.Add(sqlUpdateWmsInRetreat);
                    #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}】");
            }
        }

    }
}