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

namespace NXWMS.Service.NXWMS.OutStock
{
    /// <summary>
    /// 下架单服务
    /// </summary>
    [AutoInject(typeof(IWmsOutPutDownService), InjectType.Scope)]
    public class WmsOutPutDownService : ServiceBase, IWmsOutPutDownService
    {
        #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 WmsOutPutDownService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
        }
        #endregion

        /// <summary>
        /// 分页查询下架单主表数据
        /// </summary>
        /// <param name="putdownSearchMd">下架单查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsOutPutDownResult>> GetWmsOutPutDownListForPage(WmsOutPutDownSearchMd putdownSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(putdownSearchMd.PutdownNoMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_NO = '{putdownSearchMd.PutdownNoMsg}'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.PalletNoMsg))
                {
                    sqlCondition.Append($" AND PALLET_CODE like '%{putdownSearchMd.PalletNoMsg}%'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.SBinMsg))
                {
                    sqlCondition.Append($" AND SBIN_CODE = '{putdownSearchMd.SBinMsg}'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.PutdownTypeMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_TYPE = '{putdownSearchMd.PutdownTypeMsg}'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.PutdownStatusMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_STATUS = '{putdownSearchMd.PutdownStatusMsg}'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_ID IN (SELECT PUTDOWN_ID FROM VW_WMS_OUT_PUTDOWN_DTL WHERE BATCH_NO = '{putdownSearchMd.BatchNoMsg}')");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_ID IN (SELECT PUTDOWN_ID FROM VW_WMS_OUT_PUTDOWN_DTL WHERE MATERIEL_CODE LIKE '%{putdownSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{putdownSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{putdownSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND PUTDOWN_ID IN (SELECT PUTDOWN_ID FROM VW_WMS_OUT_PUTDOWN_DTL WHERE SUPPLIER_CODE LIKE '%{putdownSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{putdownSearchMd.SupplierMsg}%')");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.StartCreatTimeMsg))
                {
                    sqlCondition.Append($" AND CREATE_TIME >= '{putdownSearchMd.StartCreatTimeMsg}'");
                }
                if (!string.IsNullOrEmpty(putdownSearchMd.EndCreatTimeMsg))
                {
                    sqlCondition.Append($" AND CREATE_TIME <= '{putdownSearchMd.EndCreatTimeMsg}'");
                }

                StringBuilder sqlCountPutDownData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_OUT_PUTDOWN WHERE 1=1");
                sqlCountPutDownData.Append(sqlCondition.ToString());

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

                StringBuilder sqlQueryPutDownData = new StringBuilder($@"
                                                                        SELECT
	                                                                        PUTDOWN_ID,
	                                                                        PUTDOWN_NO,
	                                                                        SOURCE_NO,
	                                                                        PUTDOWN_TYPE,
	                                                                        PUTDOWN_TYPE_NAME,
                                                                            TRAY_CODE,
	                                                                        PALLET_CODE,
	                                                                        SBIN_CODE,
	                                                                        EBIN_CODE,
	                                                                        PUTDOWN_PRIORITY,
	                                                                        PUTDOWN_STATUS,
	                                                                        PUTDOWN_STATUS_NAME,
	                                                                        [DESCRIBE],
	                                                                        CREATE_BY,
	                                                                        CREATE_NAME,
	                                                                        CREATE_TIME,
	                                                                        UPDATE_BY,
	                                                                        UPDATE_NAME,
	                                                                        UPDATE_TIME
                                                                        FROM
	                                                                        VW_WMS_OUT_PUTDOWN
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {putdownSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion
                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountPutDownData.ToString()));
                List<WmsOutPutDownResult> resultList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryPutDownData.ToString()).ToList();
                OperateResultInfo<List<WmsOutPutDownResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

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

        /// <summary>
        /// 根据下架单No查询下架单明细数据
        /// </summary>
        /// <param name="wmsOutPutDown">下架单对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsOutPutDownResult> GetWmsOutPutDownDtlListForNo(string No,bool byDelete=false)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsOutPutDown = $@"
                                                SELECT
	                                                PUTDOWN_ID,
	                                                PUTDOWN_NO,
	                                                SOURCE_NO,
	                                                PUTDOWN_TYPE,
	                                                PUTDOWN_TYPE_NAME,
                                                    TRAY_CODE,
	                                                PALLET_CODE,
	                                                SBIN_CODE,
	                                                EBIN_CODE,
	                                                PUTDOWN_PRIORITY,
	                                                PUTDOWN_STATUS,
	                                                PUTDOWN_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_OUT_PUTDOWN
                                                WHERE   
                                                    PUTDOWN_NO = '{No}'
                                              ";
                string strWhere = string.Empty;
                if (byDelete)
                {
                    strWhere = "1=1";
                }
                else
                {
                    strWhere = "PUTDOWN_DTL_STATUS < 111";
                }
                string sqlQueryWmsPutDownDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_PUTDOWN_DTL
                                                    WHERE
	                                                    PUTDOWN_NO = '{No}'
                                                    AND {strWhere}
                                                    ORDER BY PUTDOWN_DTL_STATUS,PUTDOWN_DTL_ID
                                                    ";
                #endregion
                List<WmsOutPutDownResult> resultList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryWmsOutPutDown).ToList();
                List<WmsOutPutDownDtlResult> resultDtlList = new DataRepository<WmsOutPutDownDtlResult>(_dataContext).Query(sqlQueryWmsPutDownDtl).ToList();
                var result = resultList[0];
                result.WmsOutPutDownDtlList = resultDtlList;
                OperateResultInfo<WmsOutPutDownResult> retDataMsg = SuccessStatus(result);

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

        /// <summary>
        /// 根据下架单主键Id查询下架单明细数据
        /// </summary>
        /// <param name="wmsOutPutDown">下架单对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsOutPutDownResult> GetWmsOutPutDownDtlListForID(WmsOutPutDownResult wmsOutPutDown)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsOutPutDown = $@"
                                                SELECT
	                                                PUTDOWN_ID,
	                                                PUTDOWN_NO,
	                                                SOURCE_NO,
	                                                PUTDOWN_TYPE,
	                                                PUTDOWN_TYPE_NAME,
                                                    TRAY_CODE,
	                                                PALLET_CODE,
	                                                SBIN_CODE,
	                                                EBIN_CODE,
	                                                PUTDOWN_PRIORITY,
	                                                PUTDOWN_STATUS,
	                                                PUTDOWN_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_OUT_PUTDOWN
                                                WHERE   
                                                    PUTDOWN_ID = '{wmsOutPutDown.PUTDOWN_ID}'
                                              ";
                string strWhere = string.Empty;
                if (wmsOutPutDown.REMARKS1 == "查询已删除明细数据")
                {
                    strWhere = "1=1";
                }
                else
                {
                    strWhere = "PUTDOWN_DTL_STATUS < 111";
                }
                string sqlQueryWmsPutDownDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_PUTDOWN_DTL
                                                    WHERE
	                                                    PUTDOWN_ID = '{wmsOutPutDown.PUTDOWN_ID}'
                                                    AND {strWhere}
                                                    ORDER BY PUTDOWN_DTL_STATUS,PUTDOWN_DTL_ID
                                                    ";
                #endregion
                List<WmsOutPutDownResult> resultList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryWmsOutPutDown).ToList();
                List<WmsOutPutDownDtlResult> resultDtlList = new DataRepository<WmsOutPutDownDtlResult>(_dataContext).Query(sqlQueryWmsPutDownDtl).ToList();
                wmsOutPutDown = resultList[0];
                wmsOutPutDown.WmsOutPutDownDtlList = resultDtlList;
                OperateResultInfo<WmsOutPutDownResult> retDataMsg = SuccessStatus(wmsOutPutDown);
                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<WmsOutPutDownResult>($"查询下架单明细数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 新增下架单数据
        /// </summary>
        /// <param name="wmsOutPutDown">下架单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsOutPutDownData(WmsOutPutDownResult wmsOutPutDown)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                wmsOutPutDown.PUTDOWN_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Id"));
                wmsOutPutDown.PUTDOWN_NO = new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_No");
                wmsOutPutDown.PUTDOWN_STATUS = 0;
                StringBuilder sqlAddWmsPutDown = new StringBuilder();
                sqlAddWmsPutDown.Append($@"
                                            INSERT INTO [WMS_OUT_PUTDOWN] (
	                                            [PUTDOWN_ID],
	                                            [PUTDOWN_NO],
	                                            [SOURCE_NO],
	                                            [PUTDOWN_TYPE],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [SBIN_CODE],
	                                            [EBIN_CODE],
	                                            [PUTDOWN_PRIORITY],
	                                            [PUTDOWN_STATUS],
	                                            [DESCRIBE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
	                                            (
		                                            '{wmsOutPutDown.PUTDOWN_ID}',
		                                            '{wmsOutPutDown.PUTDOWN_NO}',
		                                            '{wmsOutPutDown.SOURCE_NO}',
		                                            '{wmsOutPutDown.PUTDOWN_TYPE}',
		                                            '{wmsOutPutDown.TRAY_CODE}',
		                                            '{wmsOutPutDown.PALLET_CODE}',
		                                            '{wmsOutPutDown.SBIN_CODE}',
		                                            '{wmsOutPutDown.EBIN_CODE}',
		                                            '{wmsOutPutDown.PUTDOWN_PRIORITY}',
		                                            '0',
		                                            '{wmsOutPutDown.DESCRIBE}',
		                                            '{wmsOutPutDown.CREATE_BY}',
		                                            getdate(),
		                                            '{wmsOutPutDown.UPDATE_BY}',
		                                            getdate(),
		                                            0,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL
	                                            );
                                         ");
                sqlList.Add(sqlAddWmsPutDown.ToString());

                foreach (WmsOutPutDownDtlResult item in wmsOutPutDown.WmsOutPutDownDtlList)
                {
                    StringBuilder sqlAddWmsOutPutDownDtl = new StringBuilder();
                    item.PUTDOWN_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Dtl_Id"));
                    item.PUTDOWN_ID = wmsOutPutDown.PUTDOWN_ID;
                    item.PUTDOWN_DTL_STATUS = 0;
                    sqlAddWmsOutPutDownDtl.Append($@"
                                                    INSERT INTO [WMS_OUT_PUTDOWN_DTL] (
	                                                    [PUTDOWN_DTL_ID],
	                                                    [PUTDOWN_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [PUTDOWN_QTY],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [PUTDOWN_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.PUTDOWN_DTL_ID}',
		                                                    '{item.PUTDOWN_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.PUTDOWN_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(sqlAddWmsOutPutDownDtl.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="wmsOutPutDown">下架主表对象</param>
        /// <returns></returns>
        public OperateResultInfo EditWmsOutPutDownData(WmsOutPutDownResult wmsOutPutDown)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string sqlUpdateWmsOutPutDown = $@"
                                                UPDATE [WMS_OUT_PUTDOWN]
                                                SET 
                                                 [PUTDOWN_NO] = '{wmsOutPutDown.PUTDOWN_NO}',
                                                 [SOURCE_NO] = '{wmsOutPutDown.SOURCE_NO}',
                                                 [PUTDOWN_TYPE] = '{wmsOutPutDown.PUTDOWN_TYPE}',
                                                 [TRAY_CODE] = '{wmsOutPutDown.TRAY_CODE}',
                                                 [PALLET_CODE] = '{wmsOutPutDown.PALLET_CODE}',
                                                 [SBIN_CODE] = '{wmsOutPutDown.SBIN_CODE}',
                                                 [EBIN_CODE] = '{wmsOutPutDown.EBIN_CODE}',
                                                 [PUTDOWN_PRIORITY] = '{wmsOutPutDown.PUTDOWN_PRIORITY}',
                                                 [PUTDOWN_STATUS] = '{wmsOutPutDown.PUTDOWN_STATUS}',
                                                 [DESCRIBE] = '{wmsOutPutDown.DESCRIBE}',
                                                 [UPDATE_BY] = '{wmsOutPutDown.UPDATE_BY}',
                                                 [UPDATE_TIME] = GETDATE(),
                                                 [DATA_VERSION] = [DATA_VERSION] + 1
                                                WHERE
	                                                [PUTDOWN_ID] = '{wmsOutPutDown.PUTDOWN_ID}';
                                                ";
                sqlList.Add(sqlUpdateWmsOutPutDown);
                foreach (WmsOutPutDownDtlResult item in wmsOutPutDown.WmsOutPutDownDtlList)
                {
                    if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除")
                    {
                        string sqlUpdateWmsOutPutDownDtl = $@"
                                                            UPDATE [WMS_OUT_PUTDOWN_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}',
                                                             [PUTDOWN_QTY] = '{item.PUTDOWN_QTY}',
                                                             [SUPPLIER_CODE] = '{item.SUPPLIER_CODE}',
                                                             [SUPPLIER_NAME] = '{item.SUPPLIER_CODE}',
                                                             [PRODUCT_DATE] = '{item.PRODUCT_DATE}',
                                                             [EXP_DATE] = '{item.EXP_DATE}',
                                                             [PUTDOWN_DTL_STATUS] = '{item.PUTDOWN_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
	                                                            [PUTDOWN_DTL_ID] = '{item.PUTDOWN_DTL_ID}' 
                                                            AND [PUTDOWN_ID] = '{wmsOutPutDown.PUTDOWN_ID}';
                                                        ";
                        sqlList.Add(sqlUpdateWmsOutPutDownDtl);
                    }
                    if (item.REMARKS1 == "添加")
                    {
                        StringBuilder sqlAddWmsOutPutDownDtl = new StringBuilder();
                        item.PUTDOWN_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id"));
                        item.PUTDOWN_ID = wmsOutPutDown.PUTDOWN_ID;
                        item.PUTDOWN_DTL_STATUS = 0;
                        sqlAddWmsOutPutDownDtl.Append($@"
                                                    INSERT INTO [WMS_OUT_PUTDOWN_DTL] (
	                                                    [PUTDOWN_DTL_ID],
	                                                    [PUTDOWN_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [BATCH_NO],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [PUTDOWN_QTY],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [PUTDOWN_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.PUTDOWN_DTL_ID}',
		                                                    '{item.PUTDOWN_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.PUTDOWN_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(sqlAddWmsOutPutDownDtl.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="wmsOutPutDown">下架单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo DeleteWmsOutPutDownData(WmsOutPutDownResult wmsOutPutDown)
        {
            try
            {
                #region SQL语句生成
                string[] putdownIdList = wmsOutPutDown.PUTDOWN_NO.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in putdownIdList)
                {
                    string sqlDeleteWmsPutDown = $@"
                                                UPDATE WMS_OUT_PUTDOWN
                                                SET PUTDOWN_STATUS = '111',
                                                 UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                PUTDOWN_ID = '{item}';
                                                ";
                    string sqlDeleteWmsOutPutDownDtl = $@"
                                                    UPDATE WMS_OUT_PUTDOWN_DTL
                                                    SET PUTDOWN_DTL_STATUS = '111',
                                                     UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    PUTDOWN_ID = '{item}';
                                                    ";
                    sqlList.Add(sqlDeleteWmsPutDown);
                    sqlList.Add(sqlDeleteWmsOutPutDownDtl);
                    /*
                     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>
        /// <returns></returns>
        public OperateResultInfo<List<WmsOutPutDownPalletMsgResult>> GetPutDownPalletMsgList()
        {
            try
            {
                string sql = $@"SELECT * FROM VW_WMS_OUT_PUTDOWN_PALLET_MSG";
                List<WmsOutPutDownPalletMsgResult> resultList = new DataRepository<WmsOutPutDownPalletMsgResult>(_dataContext).Query(sql).ToList();
                OperateResultInfo<List<WmsOutPutDownPalletMsgResult>> retDataMsg = SuccessStatus(resultList);
                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsOutPutDownPalletMsgResult>>($"查询待下架托盘数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 手动下架
        /// </summary>
        /// <param name="wmsOutPutDown">下架单主表实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo ManualPutDownPallet(WmsOutPutDownResult wmsOutPutDown, PutDownMethodsEnum methodsEnum)
        {
            try
            {
                #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.BIN_CODE = '{wmsOutPutDown.SBIN_CODE}'                        
                                                        ";
                List<BinResult> resultBinList = new DataRepository<BinResult>(_dataContext).Query(sqlQueryRetreatRegionBinMsg).ToList();
                if (resultBinList.Count <= 0)
                {
                    return FailMessageStatus($"下架库位【{wmsOutPutDown.SBIN_CODE}】不存在，请重新输入或扫描。");
                }
                int status = 55;
                string sqlUpdatePutDown = $@"
                                            UPDATE WMS_OUT_PUTDOWN
                                            SET PUTDOWN_STATUS = '{status}',
                                            UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID = '{wmsOutPutDown.PUTDOWN_ID}';
                                          ";
                sqlList.Add(sqlUpdatePutDown);
                string sqlUpdatePutDownDtl = $@"
                                            UPDATE WMS_OUT_PUTDOWN_DTL
                                            SET PUTDOWN_DTL_STATUS = '{status}',
                                            UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID = '{wmsOutPutDown.PUTDOWN_ID}';
                                          ";
                sqlList.Add(sqlUpdatePutDownDtl);
                if (wmsOutPutDown.PUTDOWN_TYPE != 5)
                {
                    foreach (WmsOutPutDownDtlResult item in wmsOutPutDown.WmsOutPutDownDtlList)
                    {
                        string sqlQueryInvoiceRecord = $@"SELECT * FROM VW_WMS_OUT_INVOICE_RECORD WHERE TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}' AND PALLET_CODE = '{wmsOutPutDown.PALLET_CODE}' AND INVOICE_RECORD_STATUS = 0";
                        List<WmsOutInvoiceRecordResult> resultInvoiceRecordList = new DataRepository<WmsOutInvoiceRecordResult>(_dataContext).Query(sqlQueryInvoiceRecord).ToList();
                        WmsOutInvoiceRecordResult invRecordMd = resultInvoiceRecordList.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO);
                        string sqlUpdateInvoiceDtl = $@"
                                                    UPDATE WMS_OUT_INVOICE_DTL
                                                    SET PUTDOWN_QTY = PUTDOWN_QTY + '{item.PUTDOWN_QTY}',
                                                     INVOICE_DTL_STATUS = 55,
                                                     UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    INVOICE_DTL_ID = '{invRecordMd.INVOICE_DTL_ID}' ;
                                                ";
                        sqlList.Add(sqlUpdateInvoiceDtl);
                    }
                    string sqlUpdateInvoice = $@"
                                                UPDATE WMS_OUT_INVOICE
                                                SET INVOICE_STATUS = 55,
                                                 UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INVOICE_NO = '{wmsOutPutDown.SOURCE_NO}'
                                                ";
                    string sqlUpdateInvoiceRecord = $@"
                                                UPDATE WMS_OUT_INVOICE_RECORD
                                                SET INVOICE_RECORD_STATUS = 1,
                                                 UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}'
                                                AND PALLET_CODE = '{wmsOutPutDown.PALLET_CODE}'
                                                AND INVOICE_RECORD_STATUS = 0;
                                                ";
                    sqlList.Add(sqlUpdateInvoiceRecord);
                    sqlList.Add(sqlUpdateInvoice);

                    string sqlUpdateWmsStkTray = $@"
                                                UPDATE WMS_STK_TRAY
                                                SET TRAY_STATUS = 66,
                                                 UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}';
                                                ";
                    string sqlUpdateWmsStkTrayDtl = $@"
                                                UPDATE WMS_STK_TRAY_DTL
                                                SET TRAY_DTL_STATUS = 66,
                                                 UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_ID IN (
		                                                SELECT
			                                                TRAY_ID
		                                                FROM
			                                                WMS_STK_TRAY
		                                                WHERE
			                                                TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}'
	                                                );
                                                ";
                    string sqlUpdateWmsStkBalance = $@"
                                                    UPDATE WMS_STK_BALANCE
                                                    SET BALANCE_STATUS = 66,
                                                    UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                    UPDATE_TIME = GETDATE(),
                                                    DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
                                                    TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}';
                                                 ";
                    sqlList.Add(sqlUpdateWmsStkTray);
                    sqlList.Add(sqlUpdateWmsStkTrayDtl);
                    sqlList.Add(sqlUpdateWmsStkBalance);
                }

                #region ToDo ： 更新WMS大任务
                string sqlUpdateWmsTask = $@"
                                            UPDATE WMS_TSK_TASK
                                            SET CLOC_CODE = ELOC_CODE,
                                                UPDATE_BY = '{wmsOutPutDown.UPDATE_BY}',
                                                UPDATE_TIME = GETDATE(),
                                                DATA_VERSION = DATA_VERSION + 1,
                                            TASK_STATUS = 77,
                                            TASK_MSG = '【{methodsEnum}】-- 更新WMS大任务为：【托盘到目标】状态'
                                            WHERE
                                                TRAY_CODE = '{wmsOutPutDown.TRAY_CODE}'
                                            AND TASK_STATUS < 77
                                         ";
                sqlList.Add(sqlUpdateWmsTask);
                #endregion

                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus($"【{methodsEnum}】-- 操作成功！", row);
                }
                else
                {
                    return FailMessageStatus($"【{methodsEnum}】-- 操作失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"【{methodsEnum}】-- 操作发生异常，【{ex.Message}】");
            }
        }

        #region 20210224 孙亚龙新增WCS上报托盘运行状态
        public OperateResultInfo UploadPalletRunStatus(NoticeWmsPalletStatus parm)
        {
            try
            {
                int wmsTaskStatus;
                if (parm.palletCurrRunStatus == PalletTaskRunStatusEnum.托盘出库执行中)
                {
                    wmsTaskStatus = 55;
                }
                else
                {
                    wmsTaskStatus = 77;
                }
                string sqlQueryWmsTask = $@"
                                            SELECT
	                                            *
                                            FROM
                                                VW_WMS_TSK_TASK 
                                            WHERE
                                                TASK_NO = '{parm.TaskNo}'
                                            AND TASK_STATUS = '{wmsTaskStatus}'
                                        ";
                List<WmsTaskResult> resultList = new DataRepository<WmsTaskResult>(_dataContext).Query(sqlQueryWmsTask).ToList();
                if (resultList == null || resultList.Count <= 0)
                {
                    return FailMessageStatus($"WCS上报托盘运行状态的参数错误，无法获取WMS大任务信息！");
                }
                string sqlQueryWmsOutPutDown = $@"
                                                SELECT
	                                                PUTDOWN_ID,
	                                                PUTDOWN_NO,
	                                                SOURCE_NO,
	                                                PUTDOWN_TYPE,
	                                                PUTDOWN_TYPE_NAME,
                                                    TRAY_CODE,
	                                                PALLET_CODE,
	                                                SBIN_CODE,
	                                                EBIN_CODE,
	                                                PUTDOWN_PRIORITY,
	                                                PUTDOWN_STATUS,
	                                                PUTDOWN_STATUS_NAME,
	                                                [DESCRIBE],
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
	                                                VW_WMS_OUT_PUTDOWN
                                                WHERE   
                                                    TRAY_CODE = '{parm.TrayCode}' AND PUTDOWN_STATUS < 99
                                              ";
                List<WmsOutPutDownResult> resultPutDownList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryWmsOutPutDown).ToList();
                if (resultPutDownList == null || resultPutDownList.Count <= 0)
                {
                    return FailMessageStatus($"WCS上报托盘运行状态的参数错误，无法获取托盘下架单信息！");
                }
                WmsTaskResult wmsTaskResultDataBase = resultList[0];
                WmsOutPutDownResult wmsOutPutDownInDataBase = resultPutDownList[0];

                if (parm.palletCurrRunStatus == PalletTaskRunStatusEnum.托盘出库执行中)
                {
                    List<string> sqlList = new List<string>();
                    int status = 55;
                    string sqlUpdatePutDown = $@"
                                            UPDATE WMS_OUT_PUTDOWN
                                            SET PUTDOWN_STATUS = '{status}',
                                            UPDATE_BY = '{parm.OperateUserId}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID = '{wmsOutPutDownInDataBase.PUTDOWN_ID}';
                                          ";
                    sqlList.Add(sqlUpdatePutDown);
                    string sqlUpdatePutDownDtl = $@"
                                            UPDATE WMS_OUT_PUTDOWN_DTL
                                            SET PUTDOWN_DTL_STATUS = '{status}',
                                            UPDATE_BY = '{parm.OperateUserId}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID = '{wmsOutPutDownInDataBase.PUTDOWN_ID}';
                                          ";
                    sqlList.Add(sqlUpdatePutDownDtl);
                    int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                    if (row > 0)
                    {
                        return SuccessMessageStatus("WCS上报托盘运行状态成功！", row);
                    }
                    else
                    {
                        return FailMessageStatus("WCS上报托盘运行状态失败！", row);
                    }
                }
                else
                {
                    string sqlQueryWmsPutDownDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_PUTDOWN_DTL
                                                    WHERE
	                                                    PUTDOWN_ID = '{wmsOutPutDownInDataBase.PUTDOWN_ID}'
                                                    ";
                    List<WmsOutPutDownDtlResult> resultDtlList = new DataRepository<WmsOutPutDownDtlResult>(_dataContext).Query(sqlQueryWmsPutDownDtl).ToList();
                    wmsOutPutDownInDataBase.WmsOutPutDownDtlList = resultDtlList;
                    return ManualPutDownPallet(wmsOutPutDownInDataBase, PutDownMethodsEnum.WCS自动下架);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"WCS上报托盘运行状态函数发生异常：【{ex.Message}】");
            }
        }
        #endregion
    }
}