using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
using NXWMS.IService.NXWMS.Inspection;
using NXWMS.Model.AppModels.Condition.Inspection;
using NXWMS.Model.AppModels.Result.Inspection;
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.Inspection
{
    /// <summary>
    /// 质检单服务
    /// </summary>
    [AutoInject(typeof(IWmsQaInspectionService), InjectType.Scope)]
    public class WmsQaInspectionService : ServiceBase, IWmsQaInspectionService
    {
        #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 WmsQaInspectionService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
        }
        #endregion

        /// <summary>
        /// 分页查询质检单主表数据
        /// </summary>
        /// <param name="inspectionSearchMd">质检单查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsQaInspectionResult>> GetWmsQaInspectionListForPage(WmsQaInspectionSearchMd inspectionSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(inspectionSearchMd.InspectionNoMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_NO = '{inspectionSearchMd.InspectionNoMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.MaterielTypeMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID in (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE MATERIEL_TYPE_CODE = '{inspectionSearchMd.MaterielTypeMsg}')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID IN (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE MATERIEL_CODE LIKE '%{inspectionSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{inspectionSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{inspectionSearchMd.MaterielMsg}%'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.InspectionStatusMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_STATUS = '{inspectionSearchMd.InspectionStatusMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.InspectionTypeMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_TYPE = '{inspectionSearchMd.InspectionTypeMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.InspectionResultMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID in (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE INSPECTION_RESULT = '{inspectionSearchMd.InspectionResultMsg}')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.BillNoMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID in (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE BILL_NO = '{inspectionSearchMd.BillNoMsg}')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.RegionNoMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID in (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE REGION_CODE = '{inspectionSearchMd.RegionNoMsg}')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.BinNoMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID in (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE BIN_CODE = '{inspectionSearchMd.BinNoMsg}')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_ID IN (SELECT INSPECTION_ID FROM VW_WMS_QA_INSPECTION_DTL WHERE SUPPLIER_CODE LIKE '%{inspectionSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{inspectionSearchMd.SupplierMsg}%')");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.StartCreateMsg))
                {
                    sqlCondition.Append($" AND CREATE_TIME >= '{inspectionSearchMd.StartCreateMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.EndCreateMsg))
                {
                    sqlCondition.Append($" AND CREATE_TIME <= '{inspectionSearchMd.EndCreateMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.StartInspectionTimeMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_TIME >= '{inspectionSearchMd.StartInspectionTimeMsg}'");
                }
                if (!string.IsNullOrEmpty(inspectionSearchMd.EndInspectionTimeMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_TIME <= '{inspectionSearchMd.EndInspectionTimeMsg}'");
                }
                StringBuilder sqlCountInspectionData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_QA_INSPECTION WHERE 1=1");
                sqlCountInspectionData.Append(sqlCondition.ToString());

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

                StringBuilder sqlQueryInspectionData = new StringBuilder($@"
                                                                        SELECT
	                                                                        INSPECTION_ID,
	                                                                        INSPECTION_NO,
	                                                                        INSPECTION_TYPE,
	                                                                        INSPECTION_TYPE_NAME,
	                                                                        INSPECTION_WAY,
	                                                                        INSPECTION_WAY_NAME,
	                                                                        INSPECTION_USER,
	                                                                        INSPECTION_TIME,
	                                                                        INSPECTION_STATUS,
	                                                                        INSPECTION_STATUS_NAME,
                                                                            DESCRIBE,
	                                                                        CREATE_BY,
	                                                                        CREATE_NAME,
	                                                                        CREATE_TIME,
	                                                                        UPDATE_BY,
	                                                                        UPDATE_NAME,
	                                                                        UPDATE_TIME
                                                                        FROM
                                                                            VW_WMS_QA_INSPECTION 
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {inspectionSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion

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

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsQaInspectionResult>>($"查询质检单数据发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 根据质检单主键Id查询质检单明细数据
        /// </summary>
        /// <param name="wmsQaInspection">质检单实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsQaInspectionResult> GetWmsQaInspectionDtlListForID(WmsQaInspectionResult wmsQaInspection)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsQaInspection = $@"
                                                SELECT
	                                                INSPECTION_ID,
	                                                INSPECTION_NO,
	                                                INSPECTION_TYPE,
	                                                INSPECTION_TYPE_NAME,
	                                                INSPECTION_WAY,
	                                                INSPECTION_WAY_NAME,
	                                                INSPECTION_USER,
	                                                INSPECTION_TIME,
	                                                INSPECTION_STATUS,
	                                                INSPECTION_STATUS_NAME,
                                                    DESCRIBE,
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
                                                    VW_WMS_QA_INSPECTION 
                                                WHERE   
                                                    INSPECTION_ID = '{wmsQaInspection.INSPECTION_ID}'
                                              ";
                string strWhere = string.Empty;
                if (wmsQaInspection.REMARKS1 == "查询已删除明细数据")
                {
                    strWhere = "1=1";
                }
                else
                {
                    strWhere = "INSPECTION_DTL_STATUS < 99";
                }
                string sqlQueryWmsInArivalDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_QA_INSPECTION_DTL
                                                    WHERE
	                                                    INSPECTION_ID = '{wmsQaInspection.INSPECTION_ID}'
                                                    AND {strWhere}
                                                    ORDER BY INSPECTION_DTL_STATUS,INSPECTION_DTL_ID
                                                    ";
                #endregion
                List<WmsQaInspectionResult> resultList = new DataRepository<WmsQaInspectionResult>(_dataContext).Query(sqlQueryWmsQaInspection).ToList();
                List<WmsQaInspectionDtlResult> resultDtlList = new DataRepository<WmsQaInspectionDtlResult>(_dataContext).Query(sqlQueryWmsInArivalDtl).ToList();
                wmsQaInspection = resultList[0];
                wmsQaInspection.WmsQaInspectionDtlList = resultDtlList;
                OperateResultInfo<WmsQaInspectionResult> retDataMsg = SuccessStatus(wmsQaInspection);

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

        /// <summary>
        /// 新增质检单数据
        /// </summary>
        /// <param name="wmsQaInspection">质检单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsQaInspectionData(WmsQaInspectionResult wmsQaInspection)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                wmsQaInspection.INSPECTION_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inspection_Id"));
                wmsQaInspection.INSPECTION_NO = new DataRepository<object>(_dataContext).GetSequenceMsg("Inspection_No");
                wmsQaInspection.INSPECTION_STATUS = 0;
                StringBuilder sqlAddWmsQaInspection = new StringBuilder();
                string inspectionTime = DateTime.Compare(wmsQaInspection.INSPECTION_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsQaInspection.INSPECTION_TIME}'";
                sqlAddWmsQaInspection.Append($@"
                                                INSERT INTO [WMS_QA_INSPECTION] (
	                                                [INSPECTION_ID],
	                                                [INSPECTION_NO],
	                                                [INSPECTION_TYPE],
	                                                [INSPECTION_WAY],
	                                                [INSPECTION_USER],
	                                                [INSPECTION_TIME],
	                                                [INSPECTION_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5]
                                                )
                                                VALUES
	                                                (
		                                                '{wmsQaInspection.INSPECTION_ID}',
		                                                '{wmsQaInspection.INSPECTION_NO}',
		                                                '{wmsQaInspection.INSPECTION_TYPE}',
		                                                '{wmsQaInspection.INSPECTION_WAY}',
		                                                '{wmsQaInspection.INSPECTION_USER}',
		                                                {inspectionTime},
		                                                '{wmsQaInspection.INSPECTION_STATUS}',
		                                                '{wmsQaInspection.DESCRIBE}',
		                                                '{wmsQaInspection.CREATE_BY}',
		                                                getdate(),
		                                                '{wmsQaInspection.UPDATE_BY}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL
	                                                );
                                         ");
                sqlList.Add(sqlAddWmsQaInspection.ToString());
                foreach (WmsQaInspectionDtlResult item in wmsQaInspection.WmsQaInspectionDtlList)
                {
                    StringBuilder sqlAddWmsQaInspectionDtl = new StringBuilder();
                    item.INSPECTION_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inspection_Dtl_Id"));
                    item.INSPECTION_ID = wmsQaInspection.INSPECTION_ID;
                    item.INSPECTION_DTL_STATUS = 0;
                    sqlAddWmsQaInspectionDtl.Append($@"
                                                        INSERT INTO [WMS_QA_INSPECTION_DTL] (
	                                                        [INSPECTION_DTL_ID],
	                                                        [INSPECTION_ID],
	                                                        [MATERIEL_CODE],
	                                                        [MATERIEL_NAME],
	                                                        [MATERIEL_BARCODE],
	                                                        [MATERIEL_SPEC],
	                                                        [PACKAGE_CODE],
	                                                        [UNIT_CODE],
	                                                        [BATCH_NO],
	                                                        [SUPPLIER_CODE],
	                                                        [SUPPLIER_NAME],
	                                                        [PRODUCT_DATE],
	                                                        [EXP_DATE],
	                                                        [REGION_CODE],
	                                                        [REGION_NAME],
	                                                        [BIN_CODE],
	                                                        [BIN_NAME],
	                                                        [BILL_NO],
	                                                        [BILL_DTL_ID],
	                                                        [TRAY_CODE],
	                                                        [PALLET_CODE],
	                                                        [TRAY_DTL_ID],
	                                                        [INSPECTION_RESULT],
	                                                        [INSPECTION_DTL_STATUS],
	                                                        [ITEM_STATUS],
	                                                        [DESCRIBE],
	                                                        [CREATE_BY],
	                                                        [CREATE_TIME],
	                                                        [UPDATE_BY],
	                                                        [UPDATE_TIME],
	                                                        [DATA_VERSION],
	                                                        [REMARKS1],
	                                                        [REMARKS2],
	                                                        [REMARKS3],
	                                                        [REMARKS4],
	                                                        [REMARKS5]
                                                        )
                                                        VALUES
	                                                        (
		                                                        '{item.INSPECTION_DTL_ID}',
		                                                        '{item.INSPECTION_ID}',
		                                                        '{item.MATERIEL_CODE}',
		                                                        '{item.MATERIEL_NAME}',
		                                                        '{item.MATERIEL_BARCODE}',
		                                                        '{item.MATERIEL_SPEC}',
		                                                        '{item.PACKAGE_CODE}',
		                                                        '{item.UNIT_CODE}',
		                                                        '{item.BATCH_NO}',
		                                                        '{item.SUPPLIER_CODE}',
		                                                        '{item.SUPPLIER_NAME}',
		                                                        '{item.PRODUCT_DATE}',
		                                                        '{item.EXP_DATE}',
		                                                        '{item.REGION_CODE}',
		                                                        '{item.REGION_NAME}',
		                                                        '{item.BIN_CODE}',
		                                                        '{item.BIN_NAME}',
		                                                        '{item.BILL_NO}',
		                                                        '{item.BILL_DTL_ID}',
		                                                        '{item.TRAY_CODE}',
		                                                        '{item.PALLET_CODE}',
		                                                        '{item.TRAY_DTL_ID}',
		                                                        '{item.INSPECTION_RESULT}',
		                                                        '{item.INSPECTION_DTL_STATUS}',
		                                                        '{item.ITEM_STATUS}',
		                                                        '{item.DESCRIBE}',
		                                                        '{item.CREATE_BY}',
		                                                        getdate(),
		                                                        '{item.UPDATE_BY}',
		                                                        getdate(),
		                                                        '{0}',
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL
	                                                        );
                                             ");
                    sqlList.Add(sqlAddWmsQaInspectionDtl.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="wmsQaInspection">质检单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo EditWmsQaInspectionData(WmsQaInspectionResult wmsQaInspection)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string inspectionTime = DateTime.Compare(wmsQaInspection.INSPECTION_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsQaInspection.INSPECTION_TIME}'";
                string sqlUpdateWmsQaInspection = $@"
                                                    UPDATE [WMS_QA_INSPECTION]
                                                    SET 
                                                     [INSPECTION_NO] = '{wmsQaInspection.INSPECTION_NO}',
                                                     [INSPECTION_TYPE] = '{wmsQaInspection.INSPECTION_TYPE}',
                                                     [INSPECTION_WAY] = '{wmsQaInspection.INSPECTION_WAY}',
                                                     [INSPECTION_USER] = '{wmsQaInspection.INSPECTION_USER}',
                                                     [INSPECTION_TIME] = {inspectionTime},
                                                     [INSPECTION_STATUS] = '{wmsQaInspection.INSPECTION_STATUS}',
                                                     [DESCRIBE] = '{wmsQaInspection.DESCRIBE}',
                                                     [UPDATE_BY] = '{wmsQaInspection.UPDATE_BY}',
                                                     [UPDATE_TIME] = getdate(),
                                                     [DATA_VERSION] = [DATA_VERSION] + 1
                                                    WHERE
	                                                    ([INSPECTION_ID] = '{wmsQaInspection.INSPECTION_ID}');
                                                ";
                sqlList.Add(sqlUpdateWmsQaInspection);
                foreach (WmsQaInspectionDtlResult item in wmsQaInspection.WmsQaInspectionDtlList)
                {
                    if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除")
                    {
                        string sqlUpdateWmsQaInspectionDtl = $@"
                                                                UPDATE [WMS_QA_INSPECTION_DTL]
                                                                SET 
                                                                 [MATERIEL_CODE] = '{item.MATERIEL_CODE}',
                                                                 [MATERIEL_NAME] = '{item.MATERIEL_NAME}',
                                                                 [MATERIEL_BARCODE] = '{item.MATERIEL_BARCODE}',
                                                                 [MATERIEL_SPEC] = '{item.MATERIEL_SPEC}',
                                                                 [PACKAGE_CODE] = '{item.PACKAGE_CODE}',
                                                                 [UNIT_CODE] = '{item.UNIT_CODE}',
                                                                 [BATCH_NO] = '{item.BATCH_NO}',
                                                                 [SUPPLIER_CODE] = '{item.SUPPLIER_CODE}',
                                                                 [SUPPLIER_NAME] = '{item.SUPPLIER_NAME}',
                                                                 [PRODUCT_DATE] = '{item.PRODUCT_DATE}',
                                                                 [EXP_DATE] = '{item.EXP_DATE}',
                                                                 [REGION_CODE] = '{item.REGION_CODE}',
                                                                 [REGION_NAME] = '{item.REGION_NAME}',
                                                                 [BIN_CODE] = '{item.BIN_CODE}',
                                                                 [BIN_NAME] = '{item.BIN_NAME}',
                                                                 [BILL_NO] = '{item.BILL_NO}',
                                                                 [BILL_DTL_ID] = '{item.BILL_DTL_ID}',
                                                                 [TRAY_CODE] = '{item.TRAY_CODE}',
                                                                 [PALLET_CODE] = '{item.PALLET_CODE}',
                                                                 [TRAY_DTL_ID] = '{item.TRAY_DTL_ID}',
                                                                 [INSPECTION_RESULT] = '{item.INSPECTION_RESULT}',
                                                                 [INSPECTION_DTL_STATUS] = '{item.INSPECTION_DTL_STATUS}',
                                                                 [ITEM_STATUS] = '{item.ITEM_STATUS}',
                                                                 [DESCRIBE] = '{item.DESCRIBE}',
                                                                 [UPDATE_BY] = '{item.UPDATE_BY}',
                                                                 [UPDATE_TIME] = GETDATE(),
                                                                 [DATA_VERSION] = [DATA_VERSION] + 1
                                                                WHERE
	                                                                ([INSPECTION_DTL_ID] = '{item.INSPECTION_DTL_ID}');
                                                        ";
                        sqlList.Add(sqlUpdateWmsQaInspectionDtl);
                    }
                    if (item.REMARKS1 == "添加")
                    {
                        StringBuilder sqlAddWmsQaInspectionDtl = new StringBuilder();
                        item.INSPECTION_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Inspection_Dtl_Id"));
                        item.INSPECTION_ID = wmsQaInspection.INSPECTION_ID;
                        item.INSPECTION_DTL_STATUS = 0;
                        sqlAddWmsQaInspectionDtl.Append($@"
                                                        INSERT INTO [WMS_QA_INSPECTION_DTL] (
	                                                        [INSPECTION_DTL_ID],
	                                                        [INSPECTION_ID],
	                                                        [MATERIEL_CODE],
	                                                        [MATERIEL_NAME],
	                                                        [MATERIEL_BARCODE],
	                                                        [MATERIEL_SPEC],
	                                                        [PACKAGE_CODE],
	                                                        [UNIT_CODE],
	                                                        [BATCH_NO],
	                                                        [SUPPLIER_CODE],
	                                                        [SUPPLIER_NAME],
	                                                        [PRODUCT_DATE],
	                                                        [EXP_DATE],
	                                                        [REGION_CODE],
	                                                        [REGION_NAME],
	                                                        [BIN_CODE],
	                                                        [BIN_NAME],
	                                                        [BILL_NO],
	                                                        [BILL_DTL_ID],
	                                                        [TRAY_CODE],
	                                                        [PALLET_CODE],
	                                                        [TRAY_DTL_ID],
	                                                        [INSPECTION_RESULT],
	                                                        [INSPECTION_DTL_STATUS],
	                                                        [ITEM_STATUS],
	                                                        [DESCRIBE],
	                                                        [CREATE_BY],
	                                                        [CREATE_TIME],
	                                                        [UPDATE_BY],
	                                                        [UPDATE_TIME],
	                                                        [DATA_VERSION],
	                                                        [REMARKS1],
	                                                        [REMARKS2],
	                                                        [REMARKS3],
	                                                        [REMARKS4],
	                                                        [REMARKS5]
                                                        )
                                                        VALUES
	                                                        (
		                                                        '{item.INSPECTION_DTL_ID}',
		                                                        '{item.INSPECTION_ID}',
		                                                        '{item.MATERIEL_CODE}',
		                                                        '{item.MATERIEL_NAME}',
		                                                        '{item.MATERIEL_BARCODE}',
		                                                        '{item.MATERIEL_SPEC}',
		                                                        '{item.PACKAGE_CODE}',
		                                                        '{item.UNIT_CODE}',
		                                                        '{item.BATCH_NO}',
		                                                        '{item.SUPPLIER_CODE}',
		                                                        '{item.SUPPLIER_NAME}',
		                                                        '{item.PRODUCT_DATE}',
		                                                        '{item.EXP_DATE}',
		                                                        '{item.REGION_CODE}',
		                                                        '{item.REGION_NAME}',
		                                                        '{item.BIN_CODE}',
		                                                        '{item.BIN_NAME}',
		                                                        '{item.BILL_NO}',
		                                                        '{item.BILL_DTL_ID}',
		                                                        '{item.TRAY_CODE}',
		                                                        '{item.PALLET_CODE}',
		                                                        '{item.TRAY_DTL_ID}',
		                                                        '{item.INSPECTION_RESULT}',
		                                                        '{item.INSPECTION_DTL_STATUS}',
		                                                        '{item.ITEM_STATUS}',
		                                                        '{item.DESCRIBE}',
		                                                        '{item.CREATE_BY}',
		                                                        getdate(),
		                                                        '{item.UPDATE_BY}',
		                                                        getdate(),
		                                                        '{0}',
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL
	                                                        );
                                             ");
                        sqlList.Add(sqlAddWmsQaInspectionDtl.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="wmsQaInspection">质检单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo DeleteWmsQaInspectionData(WmsQaInspectionResult wmsQaInspection)
        {
            try
            {
                #region SQL语句生成
                string[] inspectionIdList = wmsQaInspection.INSPECTION_NO.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in inspectionIdList)
                {
                    string sqlDeleteWmsQaInspection = $@"
                                                UPDATE WMS_QA_INSPECTION
                                                SET INSPECTION_STATUS = '111',
                                                 UPDATE_BY = '{wmsQaInspection.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INSPECTION_ID = '{item}';
                                                ";
                    string sqlDeleteWmsQaInspectionDtl = $@"
                                                    UPDATE WMS_QA_INSPECTION_DTL
                                                    SET INSPECTION_DTL_STATUS = '111',
                                                     UPDATE_BY = '{wmsQaInspection.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    INSPECTION_ID = '{item}';
                                                    ";
                    sqlList.Add(sqlDeleteWmsQaInspection);
                    sqlList.Add(sqlDeleteWmsQaInspectionDtl);
                    /*
                     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="wmsQaInspection">质检单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo QaInspectionExamine(WmsQaInspectionResult wmsQaInspection)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string sqlUpdateWmsQaInspection = $@"
                                                    UPDATE [WMS_QA_INSPECTION]
                                                    SET 
                                                     [INSPECTION_STATUS] = '{wmsQaInspection.INSPECTION_STATUS}',
                                                     [UPDATE_BY] = '{wmsQaInspection.UPDATE_BY}',
                                                     [UPDATE_TIME] = getdate(),
                                                     [DATA_VERSION] = [DATA_VERSION] + 1
                                                    WHERE
	                                                    ([INSPECTION_ID] = '{wmsQaInspection.INSPECTION_ID}');
                                                ";
                sqlList.Add(sqlUpdateWmsQaInspection);
                foreach (WmsQaInspectionDtlResult item in wmsQaInspection.WmsQaInspectionDtlList)
                {
                    string sqlUpdateWmsQaInspectionDtl = $@"
                                                            UPDATE [WMS_QA_INSPECTION_DTL]
                                                            SET 
                                                                [INSPECTION_RESULT] = '{item.INSPECTION_RESULT}',
                                                                [INSPECTION_DTL_STATUS] = '{item.INSPECTION_DTL_STATUS}',
                                                                [UPDATE_BY] = '{item.UPDATE_BY}',
                                                                [UPDATE_TIME] = GETDATE(),
                                                                [DATA_VERSION] = [DATA_VERSION] + 1
                                                            WHERE
	                                                            ([INSPECTION_DTL_ID] = '{item.INSPECTION_DTL_ID}');
                                                        ";
                    sqlList.Add(sqlUpdateWmsQaInspectionDtl);
                    //string sqlUpdateWmsInReceiptDtl = $@"
                    //                                    UPDATE WMS_IN_RECEIPT_DTL
                    //                                    SET INSPECTION_RESULT = '{item.INSPECTION_RESULT}',
                    //                                        UPDATE_BY = '{item.UPDATE_BY}',
                    //                                        UPDATE_TIME = GETDATE()
                    //                                    WHERE
                    //                                     RECEIPT_DTL_ID = '{item.BILL_DTL_ID}'
                    //                                ";
                    //sqlList.Add(sqlUpdateWmsInReceiptDtl);
                    string sqlUpdateWmsInReceiptDtl = $@"
                                                        UPDATE WMS_IN_ARRIVAL_DTL
                                                        SET INSPECTION_RESULT = '{item.INSPECTION_RESULT}',
                                                            UPDATE_BY = '{item.UPDATE_BY}',
                                                            UPDATE_TIME = GETDATE()
                                                        WHERE
	                                                        ARRIVAL_DTL_ID = '{item.BILL_DTL_ID}'
                                                    ";
                    sqlList.Add(sqlUpdateWmsInReceiptDtl);
                }
                #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}】");
            }
        }
    }
}