NX_WCS/NX_WcsDal/WcsBusiness/Crn_Dal.cs

using NX_CommonClassLibrary;
using NX_DbClassLibrary;
using NX_LogClassLibrary;
using NX_ModelClassLibrary.BaseModel;
using NX_ModelClassLibrary.CrnModel;
using NX_ModelClassLibrary.CustomEnum;
using NX_ModelClassLibrary.WmsTask;
using NX_WcsDal.CommonBusiness;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace NX_WcsDal.WcsBusiness
{
    public class Crn_Dal
    {
        #region 单例模式
        /// <summary>
        /// 单例模式对象
        /// </summary>
        private static Crn_Dal _instance = null;
        private static readonly object lockObj = new object();
        /// <summary>
        /// 单例模式方法
        /// </summary>
        public static Crn_Dal Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (lockObj)
                    {
                        if (_instance == null)
                        {
                            _instance = new Crn_Dal();
                        }
                    }
                }
                return _instance;
            }
        }
        #endregion

        /// <summary>
        /// 加载堆垛机未完成指令
        /// </summary>
        /// <param name="basWcsDevMd">堆垛机设备对象</param>
        /// <param name="isLoadUnFinish">是否加载未完成指令</param>
        /// <returns></returns>
        public WcsCrnCmdMd LoadCrnUnFinishedCmd(BasWcsDevMd basWcsDevMd,bool isLoadUnFinish, string crnCmdTypeMd)
        {
            try
            {
                string sqlCondition = string.Empty;
                if (isLoadUnFinish)
                {
                    sqlCondition = $" AND A.CMD_STATUS > '{(int)CrnCmdStatusEnum.初始创建}' AND A.CMD_STATUS < '{(int)CrnCmdStatusEnum.完成}'";
                }
                else
                {
                    sqlCondition = $" AND A.CMD_STATUS = '{(int)CrnCmdStatusEnum.初始创建}'";
                }
                if (Convert.ToInt32(AppConfigHelper.Get("CrnAutoTest")) == 1)
                {
                    sqlCondition = $" AND A.REMARKS1 = 'AutoTest'";
                }
                if (!string.IsNullOrEmpty(crnCmdTypeMd))
                {
                    if (Enum.TryParse(crnCmdTypeMd, out CrnCmdTypeEnum tmpCrnTypeMd))
                    {
                        sqlCondition = $" AND A.CMD_TYPE = '{tmpCrnTypeMd}'";
                    }
                }
                string sqlQueryCrnUnFinishedCmd = $@"
                                                    SELECT
	                                                    TOP 1
	                                                    A.CRN_CMD_ID,
	                                                    A.NEWID,
	                                                    A.TASK_NO,
	                                                    A.CMD_NO,
	                                                    A.TRAY_CODE,
	                                                    A.PALLET_CODE,
	                                                    A.TRAY_STATUS,
	                                                    A.CRN_DEV_NO,
	                                                    A.CRN_FORK_DEV_NO,
	                                                    A.SBIN_NO,
	                                                    A.SBIN_ROW,
	                                                    A.SBIN_COL,
	                                                    A.SBIN_LAYER,
	                                                    A.S_EXTENSION_GROUP,
	                                                    A.S_EXTENSION_IDX,
	                                                    A.EBIN_NO,
	                                                    A.EBIN_ROW,
	                                                    A.EBIN_COL,
	                                                    A.EBIN_LAYER,
	                                                    A.E_EXTENSION_GROUP,
	                                                    A.E_EXTENSION_IDX,
	                                                    A.CMD_TYPE,
	                                                    A.ACTIVE_TYPE,
	                                                    A.CMD_PRIORITY,
	                                                    A.CMD_STATUS,
	                                                    A.CMD_LOG,
	                                                    A.ERR_FLAG,
	                                                    A.ERR_LOG,
	                                                    A.CREATE_BY,
	                                                    B.USER_CODE CreateCode,
	                                                    B.USER_NAME CreateName,
	                                                    A.CREATE_TIME,
	                                                    A.EXECUTE_TIME,
	                                                    A.FINISH_TIME,
	                                                    A.UPDATE_BY,
	                                                    C.USER_CODE UpdateCode,
	                                                    C.USER_NAME UpdateName,
	                                                    A.UPDATE_TIME,
	                                                    A.DATA_VERSION,
	                                                    A.REMARKS1,
	                                                    A.REMARKS2,
	                                                    A.REMARKS3,
	                                                    A.REMARKS4,
	                                                    A.REMARKS5
                                                    FROM
	                                                    WCS_CRN_CMD 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.CRN_DEV_NO = '{basWcsDevMd.DevCode}'
                                                        AND A.REGION_CODE = '{basWcsDevMd.RegionCode}'
                                                        {sqlCondition} 
                                                    ORDER BY A.CMD_PRIORITY,A.CREATE_TIME
                                                ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryCrnUnFinishedCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    WcsCrnCmdMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsCrnCmdMd>(dt.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"加载堆垛机未完成指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public WmsBalanceMd GetIntroversionBinBalanceMd(string ExtensionGroupCode, int ExtensionIdx ,string RegionCode)
        {
            try
            {
                string sql = $@"
                                SELECT
	                                *
                                FROM
	                                VW_WMS_STK_BALANCE
                                WHERE
	                                BIN_CODE = (
		                                SELECT
			                                BIN_CODE
		                                FROM
			                                BAS_BIN
		                                WHERE
			                                EXTENSION_GROUP = '{ExtensionGroupCode}'
		                                AND EXTENSION_IDX = '{ExtensionIdx}'
		                                AND REGION_CODE = '{RegionCode}'
	                                )
                                AND BALANCE_STATUS < 99
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    WmsBalanceMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WmsBalanceMd>(dt.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取内伸库位库存信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public WmsTskTaskMd GetIntroversionBinMoveWmsTaskMd(WmsBalanceMd balanceMd)
        {
            try
            {
                string sqlGetUnfinishWmsTask = $@"
                                                    SELECT
                                                        TOP 1
	                                                    A.TASK_ID,
	                                                    A.NEWID,
	                                                    A.WAREHOUSE_CODE,
	                                                    A.WAREHOUSE_NAME,
	                                                    A.TASK_NO,
	                                                    A.TRAY_CODE,
	                                                    A.PALLET_CODE,
	                                                    A.TRAY_LOADED_TYPE,
	                                                    A.SLOC_CODE,
	                                                    A.ELOC_CODE,
	                                                    A.CLOC_CODE,
	                                                    A.TASK_TYPE,
	                                                    A.TASK_MSG,
	                                                    A.ERR_FLAG,
	                                                    A.TASK_PRIORITY,
	                                                    A.TASK_STATUS,
	                                                    A.SBIN_CODE,
	                                                    A.EBIN_CODE,
	                                                    A.CMD_NO,
	                                                    A.ROUTE_CODE,
	                                                    A.CREATE_BY,
	                                                    B.USER_CODE CreateCode,
	                                                    B.USER_NAME CreateName,
	                                                    A.CREATE_TIME,
	                                                    A.UPDATE_BY,
	                                                    C.USER_CODE UpdateCode,
	                                                    C.USER_NAME UpdateName,
	                                                    A.UPDATE_TIME,
	                                                    A.DATA_VERSION,
	                                                    A.REMARKS1,
	                                                    A.REMARKS2,
	                                                    A.REMARKS3,
	                                                    A.REMARKS4,
	                                                    A.REMARKS5
                                                    FROM
	                                                    WMS_TSK_TASK 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
                                                    1=1 
                                                    AND A.TASK_TYPE = '{(int)WmsTaskTypeEnum.内伸货位移库任务}'
                                                    AND A.REGION_CODE = '{balanceMd.RegionCode}'
                                                    AND A.TRAY_CODE = '{balanceMd.TrayCode}'
                                                ";
                DataTable dtGetUnfinishWmsTask = LeadDbHelperSQLServer.Query(sqlGetUnfinishWmsTask).Tables[0];
                if (dtGetUnfinishWmsTask != null && dtGetUnfinishWmsTask.Rows.Count > 0)
                {
                    DataRow item = dtGetUnfinishWmsTask.Rows[0];
                    WmsTskTaskMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WmsTskTaskMd>(dtGetUnfinishWmsTask.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取内伸库位WMS移库大任务信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public BasRegionMd GetRegionMd(string regionCode)
        {
            try
            {
                string sql = $@"select * from BAS_REGION where REGION_CODE = '{regionCode}'";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    BasRegionMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasRegionMd>(dt.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取库区信息发生异常！{ex.Message}", LogTypeEnum.Err);
                return null;
            }
        }
        public void InsertIntroversionBinMoveWmsTask(BasWcsLocMd RegiongLocNo, BasRegionMd regionMd, WmsBalanceMd balanceMd, BasBinMd EbinMd)
        {
            try
            {
                string taskNo = BasCommon_Dal.Instance.GetSysSequence("Task_No");
                string sqlInsertWmsTask = $@"
                                            INSERT INTO [dbo].[WMS_TSK_TASK] (
	                                            [WAREHOUSE_CODE],
	                                            [WAREHOUSE_NAME],
	                                            [REGION_CODE],
	                                            [TASK_NO],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [TRAY_LOADED_TYPE],
	                                            [SLOC_CODE],
	                                            [ELOC_CODE],
	                                            [CLOC_CODE],
	                                            [TASK_TYPE],
	                                            [TASK_MSG],
	                                            [ERR_FLAG],
	                                            [TASK_PRIORITY],
	                                            [TASK_STATUS],
	                                            [SBIN_CODE],
	                                            [EBIN_CODE],
	                                            [CMD_NO],
	                                            [ROUTE_CODE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
                                            (
	                                            '{regionMd.WarehouseCode}',
	                                            '{regionMd.WarehouseName}',
	                                            '{regionMd.RegionCode}',
	                                            '{taskNo}',
	                                            '{balanceMd.TrayCode}',
	                                            '{balanceMd.PalletCode}',
	                                            '{(int)TrayStatusEnum.满盘}',
	                                            '{RegiongLocNo.LocCode}',
	                                            '{RegiongLocNo.LocCode}',
	                                            '{RegiongLocNo.LocCode}',
	                                            '{(int)WmsTaskTypeEnum.内伸货位移库任务}',
	                                            NULL,
	                                            '{(int)WcsCmdErrFlagEnum.正常}',
	                                            '{1}',
	                                            '{(int)WmsTaskStatusEnum.初始创建}',
	                                            '{balanceMd.BinCode}',
	                                            '{EbinMd.BinCode}',
	                                            NULL,
	                                            '',
	                                            1,
	                                            getdate(),
	                                            1,
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                         ";
                string sqlUpdateBalance = $@"update WMS_STK_BALANCE set BALANCE_STATUS = '66',UPDATE_TIME = getdate() where BALANCE_ID = '{balanceMd.BalanceId}'";
                string balanceId = BasCommon_Dal.Instance.GetSysSequence("Balance_Id");
                string sqlAddWmsStkBalance = $@"
                                                INSERT INTO [WMS_STK_BALANCE] (
	                                                [BALANCE_ID],
	                                                [AREA_CODE],
	                                                [AREA_NAME],
	                                                [WAREHOUSE_CODE],
	                                                [WAREHOUSE_NAME],
	                                                [REGION_CODE],
	                                                [REGION_NAME],
	                                                [BIN_CODE],
	                                                [BIN_NAME],
	                                                [TRAY_ID],
	                                                [TRAY_CODE],
	                                                [PALLET_CODE],
	                                                [INWH_TIME],
	                                                [BALANCE_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5]
                                                )
                                                VALUES
	                                                (
		                                                '{balanceId}',
		                                                '{balanceMd.AreaCode}',
		                                                '{balanceMd.AreaName}',
		                                                '{balanceMd.WarehouseCode}',
		                                                '{balanceMd.WarehouseName}',
		                                                '{regionMd.RegionCode}',
		                                                '{regionMd.RegionName}',
		                                                '{EbinMd.BinCode}',
		                                                '{EbinMd.BinName}',
		                                                '{balanceMd.TrayId}',
		                                                '{balanceMd.TrayCode}',
		                                                '{balanceMd.PalletCode}',
		                                                getdate(),
		                                                '11',
		                                                '{balanceMd.Describe}',
		                                                '{balanceMd.CreateBy}',
		                                                getdate(),
		                                                '{balanceMd.CreateBy}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL
	                                                );
                                                ";
                List<string> sqlList = new List<string>();
                sqlList.Add(sqlInsertWmsTask);
                sqlList.Add(sqlUpdateBalance);
                sqlList.Add(sqlAddWmsStkBalance);
                LeadDbHelperSQLServer.ExecuteSql(sqlUpdateBalance);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"插入内伸货位WMS移库大任务数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
            }
        }

        #region 演示模式

        public void InsertTestCrnCmd(string dev_no)
        {
            try
            {
                string cmdNo = BasCommon_Dal.Instance.GetSysSequence("Cmd_No");
                string taskNo = BasCommon_Dal.Instance.GetSysSequence("Task_No");
                string trayCode = BasCommon_Dal.Instance.GetSysSequence("Tray_Code");
                string sqlTestBin = $@"
                                        SELECT
	                                        A.*, B.USER_CODE CreateCode,
	                                        B.USER_NAME CreateName,
	                                        C.USER_CODE UpdateCode,
	                                        C.USER_NAME UpdateName
                                        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.USED_FLAG = 1
                                        AND A.DEL_FLAG = 0
                                        AND A.BIN_TYPE = 3
                                        AND A.REGION_CODE = 'YCLK_Region'
                                    ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlTestBin).Tables[0];
                List<BasBinMd> binLst = new List<BasBinMd>();
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt.Columns, item);
                        binLst.Add(retMd);
                    }
                }
                string sqlTestBin1 = $@"
                                        SELECT
	                                        Top 1 A.*, B.USER_CODE CreateCode,
	                                        B.USER_NAME CreateName,
	                                        C.USER_CODE UpdateCode,
	                                        C.USER_NAME UpdateName
                                        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_TYPE = 3
                                        AND A.REGION_CODE = 'YCLK_Region'
                                        AND A.BIN_CODE = '{AppConfigHelper.Get("CrnAutoTestLockedBin")}'
                                    ";
                DataTable dt1 = LeadDbHelperSQLServer.Query(sqlTestBin1).Tables[0];
                DataRow item1 = dt1.Rows[0];
                BasBinMd randomSBin = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt1.Columns, item1);
                BasBinMd randomSBin1 = binLst.FirstOrDefault(x => x.BinCode == AppConfigHelper.Get("CrnAutoTestLockedBin"));
                if (randomSBin1 != null)
                {
                    binLst.Remove(randomSBin1);
                }
                Random r = new Random();
                BasBinMd randomEBin = binLst[r.Next(0, binLst.Count)];
                string sql = $@"
                                INSERT INTO [dbo].[WCS_CRN_CMD] (
	                                [TASK_NO],
	                                [CMD_NO],
	                                [TRAY_CODE],
	                                [PALLET_CODE],
	                                [TRAY_STATUS],
	                                [CRN_DEV_NO],
	                                [CRN_FORK_DEV_NO],
	                                [SBIN_NO],
	                                [SBIN_ROW],
	                                [SBIN_COL],
	                                [SBIN_LAYER],
	                                [S_EXTENSION_GROUP],
	                                [S_EXTENSION_IDX],
	                                [EBIN_NO],
	                                [EBIN_ROW],
	                                [EBIN_COL],
	                                [EBIN_LAYER],
	                                [E_EXTENSION_GROUP],
	                                [E_EXTENSION_IDX],
	                                [CMD_TYPE],
	                                [ACTIVE_TYPE],
	                                [CMD_PRIORITY],
	                                [CMD_STATUS],
	                                [CMD_LOG],
	                                [ERR_FLAG],
	                                [ERR_LOG],
	                                [CREATE_BY],
	                                [CREATE_TIME],
	                                [UPDATE_BY],
	                                [UPDATE_TIME],
	                                [DATA_VERSION],
	                                [REMARKS1],
	                                [REMARKS2],
	                                [REMARKS3],
	                                [REMARKS4],
	                                [REMARKS5]
                                )
                                VALUES
                                (
	                                '{taskNo}',
	                                '{cmdNo}',
	                                '{trayCode}',
	                                '123456',
	                                '{1}',
	                                '{dev_no}',
	                                '{dev_no}',
	                                '{randomSBin.BinCode}',
	                                '{randomSBin.BinRow}',
	                                '{randomSBin.BinColumn}',
	                                '{randomSBin.BinLayer}',
	                                '{randomSBin.EXTENSION_GROUP}',
	                                '{randomSBin.EXTENSION_IDX}',
	                                '{randomEBin.BinCode}',
	                                '{randomEBin.BinRow}',
	                                '{randomEBin.BinColumn}',
	                                '{randomEBin.BinLayer}',
	                                '{randomEBin.EXTENSION_GROUP}',
	                                '{randomEBin.EXTENSION_IDX}',
	                                '{CrnCmdTypeEnum.IN}',
	                                '{(int)CrnActiveTypeEnum.取放货}',
	                                100,
	                                '{(int)CrnCmdStatusEnum.初始创建}',
	                                NULL,
	                                '{(int)WcsCmdErrFlagEnum.正常}',
	                                NULL,
	                                '{1}',
	                                getdate(),
	                                '{1}',
	                                getdate(),
	                                0,
	                                'AutoTest',
	                                NULL,
	                                NULL,
	                                NULL,
	                                NULL
                                );
                            ";
                LeadDbHelperSQLServer.ExecuteSql(sql);
                string sqlUpdateBin = $@"update BAS_BIN set USED_FLAG = 1 where BIN_CODE = '{randomSBin.BinCode}'";
                LeadDbHelperSQLServer.ExecuteSql(sqlUpdateBin);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"插入堆垛机演示指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
            }
        }

        public void DeleteTestCrnCmd()
        {
            try
            {
                string sqlQueryCrnUnFinishedCmd = $@"
                                                    SELECT
	                                                    TOP 1
	                                                    A.CRN_CMD_ID,
	                                                    A.NEWID,
	                                                    A.TASK_NO,
	                                                    A.CMD_NO,
	                                                    A.TRAY_CODE,
	                                                    A.PALLET_CODE,
	                                                    A.TRAY_STATUS,
	                                                    A.CRN_DEV_NO,
	                                                    A.CRN_FORK_DEV_NO,
	                                                    A.SBIN_NO,
	                                                    A.SBIN_ROW,
	                                                    A.SBIN_COL,
	                                                    A.SBIN_LAYER,
	                                                    A.S_EXTENSION_GROUP,
	                                                    A.S_EXTENSION_IDX,
	                                                    A.EBIN_NO,
	                                                    A.EBIN_ROW,
	                                                    A.EBIN_COL,
	                                                    A.EBIN_LAYER,
	                                                    A.E_EXTENSION_GROUP,
	                                                    A.E_EXTENSION_IDX,
	                                                    A.CMD_TYPE,
	                                                    A.ACTIVE_TYPE,
	                                                    A.CMD_PRIORITY,
	                                                    A.CMD_STATUS,
	                                                    A.CMD_LOG,
	                                                    A.ERR_FLAG,
	                                                    A.ERR_LOG,
	                                                    A.CREATE_BY,
	                                                    B.USER_CODE CreateCode,
	                                                    B.USER_NAME CreateName,
	                                                    A.CREATE_TIME,
	                                                    A.EXECUTE_TIME,
	                                                    A.FINISH_TIME,
	                                                    A.UPDATE_BY,
	                                                    C.USER_CODE UpdateCode,
	                                                    C.USER_NAME UpdateName,
	                                                    A.UPDATE_TIME,
	                                                    A.DATA_VERSION,
	                                                    A.REMARKS1,
	                                                    A.REMARKS2,
	                                                    A.REMARKS3,
	                                                    A.REMARKS4,
	                                                    A.REMARKS5
                                                    FROM
	                                                    WCS_CRN_CMD 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.CRN_DEV_NO = 'Crn001'
                                                    AND A.REMARKS1 = 'AutoTest'
                                                ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryCrnUnFinishedCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    WcsCrnCmdMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsCrnCmdMd>(dt.Columns, item);
                    if (retMd.CmdStatus == CrnCmdStatusEnum.初始创建)
                    {
                        string sqlUpdateBin = $@"update BAS_BIN set USED_FLAG = 1 where BIN_CODE = '{retMd.SbinNo}'";
                        LeadDbHelperSQLServer.ExecuteSql(sqlUpdateBin);
                    }
                    else
                    {
                        string sqlUpdateBin = $@"update BAS_BIN set USED_FLAG = 1 where BIN_CODE = '{retMd.EbinNo}'";
                        LeadDbHelperSQLServer.ExecuteSql(sqlUpdateBin);
                    }
                    string sqlDeleteCrnCmd = $@"delete from WCS_CRN_CMD where CRN_CMD_ID = '{retMd.CrnCmdId}' AND REMARKS1 = 'AutoTest'";
                    LeadDbHelperSQLServer.ExecuteSql(sqlDeleteCrnCmd);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"插入堆垛机演示指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
            }
        }
        #endregion
        /// <summary>
        /// 更新堆垛机指令状态
        /// </summary>
        /// <param name="wcsCrnCmdMd">堆垛机指令对象</param>
        public void UpdateCrnCmdStatus(WcsCrnCmdMd wcsCrnCmdMd)
        {
            try
            {
                string sqlUpdateCrnCmdStatus = string.Empty;
                string sqlDeleteCrnCmd = string.Empty;
                if (wcsCrnCmdMd.CmdStatus == CrnCmdStatusEnum.已下发PLC)
                {
                    sqlUpdateCrnCmdStatus = $@"
                                                UPDATE WCS_CRN_CMD
                                                SET CMD_STATUS = '{(int)wcsCrnCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsCrnCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE(),
                                                    EXECUTE_TIME = GETDATE()
                                                WHERE 
                                                CRN_CMD_ID = '{wcsCrnCmdMd.CrnCmdId}'
                                            ";
                    if (AppConfigHelper.Get("CrnAutoTest") == "1")
                    {
                        string sqlUpdateBin = $@"update BAS_BIN set USED_FLAG = 0 where BIN_CODE = '{wcsCrnCmdMd.EbinNo}'";
                        LeadDbHelperSQLServer.ExecuteSql(sqlUpdateBin);
                        AppConfigHelper.Update("CrnAutoTestLockedBin", wcsCrnCmdMd.EbinNo);
                    }
                }
                else if (wcsCrnCmdMd.CmdStatus == CrnCmdStatusEnum.完成)
                {
                    sqlUpdateCrnCmdStatus = $@"
                                                UPDATE WCS_CRN_CMD
                                                SET CMD_STATUS = '{(int)wcsCrnCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsCrnCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE(),
                                                    FINISH_TIME = GETDATE()
                                                WHERE 
                                                CRN_CMD_ID = '{wcsCrnCmdMd.CrnCmdId}'
                                            ";
                    sqlDeleteCrnCmd = $@"delete from WCS_CRN_CMD where CRN_CMD_ID = '{wcsCrnCmdMd.CrnCmdId}' AND REMARKS1 = 'AutoTest'";
                }
                else
                {
                    sqlUpdateCrnCmdStatus = $@"
                                                UPDATE WCS_CRN_CMD
                                                SET CMD_STATUS = '{(int)wcsCrnCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsCrnCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE()
                                                WHERE 
                                                CRN_CMD_ID = '{wcsCrnCmdMd.CrnCmdId}'
                                            ";
                }
                LeadDbHelperSQLServer.ExecuteSql(sqlUpdateCrnCmdStatus);
                if (AppConfigHelper.Get("CrnAutoTest") == "1")
                {
                    LeadDbHelperSQLServer.ExecuteSql(sqlDeleteCrnCmd);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"更新堆垛机指令状态发生异常！【{ex.Message}】", LogTypeEnum.Err);
            }
        }

        public BasBinMd GetBinMdOfCode(string binCode)
        {
            try
            {
                string sqlGetBinMdOfCodeAndType = $@"
                                                    SELECT
	                                                    TOP 1 A.*, B.USER_CODE CreateCode,
	                                                    B.USER_NAME CreateName,
	                                                    C.USER_CODE UpdateCode,
	                                                    C.USER_NAME UpdateName
                                                    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.USED_FLAG = 1
                                                    AND A.DEL_FLAG = 0
                                                    AND A.BIN_CODE = '{binCode}'
                                                ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlGetBinMdOfCodeAndType).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取库位号：【{binCode}】的详细库位信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public List<WcsCrnCmdMd> LoadCurrentWorkingCrnCmd()
        {
            try
            {
                string sqlLoadCurrentWorkingCrnCmd = $@"
                                                        SELECT
	                                                        TOP 1
	                                                        A.CRN_CMD_ID,
	                                                        A.NEWID,
	                                                        A.TASK_NO,
	                                                        A.CMD_NO,
	                                                        A.TRAY_CODE,
	                                                        A.PALLET_CODE,
	                                                        A.TRAY_STATUS,
	                                                        A.CRN_DEV_NO,
	                                                        A.CRN_FORK_DEV_NO,
	                                                        A.SBIN_NO,
	                                                        A.SBIN_ROW,
	                                                        A.SBIN_COL,
	                                                        A.SBIN_LAYER,
	                                                        A.S_EXTENSION_GROUP,
	                                                        A.S_EXTENSION_IDX,
	                                                        A.EBIN_NO,
	                                                        A.EBIN_ROW,
	                                                        A.EBIN_COL,
	                                                        A.EBIN_LAYER,
	                                                        A.E_EXTENSION_GROUP,
	                                                        A.E_EXTENSION_IDX,
	                                                        A.CMD_TYPE,
	                                                        A.ACTIVE_TYPE,
	                                                        A.CMD_PRIORITY,
	                                                        A.CMD_STATUS,
	                                                        A.CMD_LOG,
	                                                        A.ERR_FLAG,
	                                                        A.ERR_LOG,
	                                                        A.CREATE_BY,
	                                                        B.USER_CODE CreateCode,
	                                                        B.USER_NAME CreateName,
	                                                        A.CREATE_TIME,
	                                                        A.EXECUTE_TIME,
	                                                        A.FINISH_TIME,
	                                                        A.UPDATE_BY,
	                                                        C.USER_CODE UpdateCode,
	                                                        C.USER_NAME UpdateName,
	                                                        A.UPDATE_TIME,
	                                                        A.DATA_VERSION,
	                                                        A.REMARKS1,
	                                                        A.REMARKS2,
	                                                        A.REMARKS3,
	                                                        A.REMARKS4,
	                                                        A.REMARKS5
                                                        FROM
	                                                        WCS_CRN_CMD 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.CMD_STATUS > '{(int)CrnCmdStatusEnum.初始创建}' AND A.CMD_STATUS < '{CrnCmdStatusEnum.完成}'
                                                    ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlLoadCurrentWorkingCrnCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsCrnCmdMd> retLst = new List<WcsCrnCmdMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        WcsCrnCmdMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsCrnCmdMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"加载堆垛机当前作业指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public List<WcsCrnCmdMd> LoadAllCrnCmd(WcsCrnCmdSearchMd searchMd)
        {
            try
            {
                string sqlQuery = string.Empty;
                if (!string.IsNullOrEmpty(searchMd.TaskNo))
                {
                    sqlQuery += $" AND TASK_NO = '{searchMd.TaskNo}'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdNo))
                {
                    sqlQuery += $" AND CMD_NO = '{searchMd.CmdNo}'";
                }
                if (!string.IsNullOrEmpty(searchMd.PalletCode))
                {
                    sqlQuery += $" AND PALLET_CODE like '%{searchMd.PalletCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.DevMsg))
                {
                    sqlQuery += $" AND CRN_DEV_NO = '{searchMd.DevMsg}'";
                }
                if (!string.IsNullOrEmpty(searchMd.SbinCode))
                {
                    sqlQuery += $" AND SBIN_NO like '%{searchMd.SbinCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.EbinCode))
                {
                    sqlQuery += $" AND EBIN_NO like '%{searchMd.EbinCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdType))
                {
                    sqlQuery += $" AND CMD_TYPE = '{searchMd.CmdType}'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdStatus))
                {
                    sqlQuery += $" AND CMD_STATUS = '{searchMd.CmdStatus}'";
                }
                if (!string.IsNullOrEmpty(searchMd.TrayLoadedType))
                {
                    sqlQuery += $" AND TRAY_STATUS = '{searchMd.TrayLoadedType}'";
                }
                if (!string.IsNullOrEmpty(searchMd.StartExcuteTime))
                {
                    sqlQuery += $" AND EXECUTE_TIME >= '{searchMd.StartExcuteTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.EndExcuteTime))
                {
                    sqlQuery += $" AND EXECUTE_TIME <= '{searchMd.EndExcuteTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.StartFinishTime))
                {
                    sqlQuery += $" AND FINISH_TIME >= '{searchMd.StartFinishTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.EndFinishTime))
                {
                    sqlQuery += $" AND FINISH_TIME <= '{searchMd.EndFinishTime}'";
                }
                int pageStartIndex = (searchMd.PageNum - 1) * searchMd.EveryPageQty;
                int pageEndIndex = searchMd.PageNum * searchMd.EveryPageQty;
                string sql = $@"select * from VW_ZBK_WCS_CRN_CMD where 1 = 1 {sqlQuery} 
                                ORDER BY A.UPDATE_TIME DESC,A.CREATE_TIME DESC
                                OFFSET {pageStartIndex} ROWS 
                                FETCH NEXT {searchMd.EveryPageQty} ROWS ONLY";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsCrnCmdMd> retLst = new List<WcsCrnCmdMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        WcsCrnCmdMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsCrnCmdMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"加载堆垛机所有作业指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
    }
}