NX_WCS/NX_WcsDal/WcsDispatch/TaskRequest_Dal.cs

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

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

        public WmsTskTaskMd GetUnfinishWmsTaskOfPalletCode(string palletCode)
        {
            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
	                                                    A.PALLET_CODE = '{palletCode}'
                                                    AND A.TASK_STATUS < '{(int)WmsTaskStatusEnum.任务完成}'
                                                ";
                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 BasWcsDevMd GetCrnDevMsg(string shelfCode)
        {
            try
            {
                string sql = $@"
                                SELECT
	                                *
                                FROM
	                                WCS_BAS_DEV
                                WHERE
	                                DEV_CODE = (
		                                SELECT
			                                DEV_CODE
		                                FROM
			                                BAS_SHELF
		                                WHERE
			                                SHELF_CODE = '{shelfCode}'
	                                )
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    BasWcsDevMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasWcsDevMd>(dt.Columns, item);
                    return retMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取堆垛机设备信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public WcsCrnCmdMd GetUnfinishWcsCrnCmdOfPalletCode(string palletCode)
        {
            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.PALLET_CODE = '{palletCode}' AND A.CMD_STATUS < '{(int)CrnCmdStatusEnum.完成}'
                                                    ";
                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 WcsTranCmdMd GetUnfinishWcsTranCmdOfPalletCode(string palletCode)
        {
            try
            {
                string sqlQueryCrnUnFinishedCmd = $@"
                                                        SELECT
	                                                        TOP 1
	                                                        A.TRAN_CMD_ID,
	                                                        A.NEWID,
	                                                        A.TASK_NO,
	                                                        A.CMD_NO,
	                                                        A.TRAY_CODE,
	                                                        A.PALLET_CODE,
	                                                        A.TRAY_STATUS,
	                                                        A.TRAN_DEV_NO,
	                                                        A.SLOC_NO,
	                                                        A.SPLC_NO,
	                                                        A.ELOC_NO,
	                                                        A.EPLC_NO,
	                                                        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_TRAN_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.PALLET_CODE = '{palletCode}' AND A.CMD_STATUS < '{(int)TranCmdStatusEnum.完成}'
                                                    ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryCrnUnFinishedCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    WcsTranCmdMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsTranCmdMd>(dt.Columns, item);
                    return tmpMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"根据托盘号获取未完成的堆垛机指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public bool VerifyTrayMsgAndBalanceMsg(string palletCode,out string trayCode)
        {
            try
            {
                string sqlVerifyTrayAndBalanceMsg = $@"
                                                        SELECT
	                                                        A.TRAY_CODE
                                                        FROM
	                                                        WMS_STK_BALANCE A
                                                        LEFT JOIN WMS_STK_TRAY B ON A.TRAY_ID = B.TRAY_ID
                                                        WHERE
	                                                        A.PALLET_CODE = '{palletCode}'
                                                        AND A.REGION_CODE = 'SHDJ_Region'
                                                        AND A.BALANCE_STATUS = '55'
                                                        AND B.TRAY_STATUS = '55'
                                                    ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlVerifyTrayAndBalanceMsg).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    trayCode = dt.Rows[0][0].ToString();
                    return true;
                }
                else
                {
                    trayCode = null;
                    return false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"校验托盘号库存和组盘信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                trayCode = null;
                return false;
            }
        }

        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 BasNextLocRouteMd GetRouteMsg(BasWcsLocMd sLocNo, BasWcsLocMd eLocNo)
        {
            try
            {
                string sqlGetRoute = $@"SELECT * FROM dbo.FN_GetAllNextRoute('{sLocNo.LocCode}','{eLocNo.LocCode}')";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlGetRoute).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    BasNextLocRouteMd nextLocRouteMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasNextLocRouteMd>(dt.Columns, item);
                    return nextLocRouteMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取起点站台：【{sLocNo.LocCode}】到终点站台：【{eLocNo.LocCode}】的路径信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public int InsertWmsTaskData(TaskRequestMd taskRequestMd, BasWcsLocMd eLocNo, string trayCode, BasNextLocRouteMd basNextLocRouteMd, BasRegionMd regionMd)
        {
            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}',
	                                            '{trayCode}',
	                                            '{taskRequestMd.PalletCode}',
	                                            '{(int)TrayStatusEnum.满盘}',
	                                            '{taskRequestMd.CurrentLocNo}',
	                                            '{eLocNo.LocCode}',
	                                            '{taskRequestMd.CurrentLocNo}',
	                                            '{(int)WmsTaskTypeEnum.正常收货入库}',
	                                            NULL,
	                                            '{(int)WcsCmdErrFlagEnum.正常}',
	                                            '{100}',
	                                            '{(int)WmsTaskStatusEnum.初始创建}',
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            '{basNextLocRouteMd.SectionNo}',
	                                            1,
	                                            getdate(),
	                                            1,
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                         ";
                return LeadDbHelperSQLServer.ExecuteSql(sqlInsertWmsTask);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"插入WMS大任务数据发生异常！【{ex.Message}】请求参数：【{JsonHelper.ToJson(taskRequestMd)}】终点站台：【{JsonHelper.ToJson(eLocNo)}】", LogTypeEnum.Err);
                return 0;
            }
        }

        public int GetRuningWmsTaskNum()
        {
            try
            {
                string sqlGetUnfinishWmsTaskNum = $@"
                                                    SELECT
	                                                    COUNT (1)
                                                    FROM
	                                                    WMS_TSK_TASK
                                                    WHERE
	                                                    TASK_STATUS > '{(int)WmsTaskStatusEnum.初始创建}'
                                                    AND TASK_STATUS < '{(int)WmsTaskStatusEnum.任务完成}'
                                                ";
                object obj = LeadDbHelperSQLServer.GetSingle(sqlGetUnfinishWmsTaskNum);
                if (obj == null)
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取正在执行的WMS大任务总数发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return 0;
            }
        }

        public WmsTskTaskMd GetWmsTaskOriginalState(string regionCode)
        {
            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_STATUS = '{(int)WmsTaskStatusEnum.初始创建}'
                                                    AND A.REGION_CODE = '{regionCode}'
                                                    ORDER BY A.TASK_PRIORITY,A.TASK_TYPE,A.CREATE_TIME
                                                ";
                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 WmsTskTaskMd InsertTranCmdAndUpdateWmsTask(WmsTskTaskMd wmsTskTaskMd, BasWcsLocMd sLocNo, BasWcsLocMd eLocNo, BasNextLocRouteMd locRouteMd)
        {
            try
            {
                string cmdNo = BasCommon_Dal.Instance.GetSysSequence("Cmd_No");
                string sqlInsertTranCmd = $@"
                                            INSERT INTO [dbo].[WCS_TRAN_CMD] (
	                                            [TASK_NO],
	                                            [CMD_NO],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [TRAY_STATUS],
	                                            [TRAN_DEV_NO],
	                                            [SLOC_NO],
	                                            [SPLC_NO],
	                                            [ELOC_NO],
	                                            [EPLC_NO],
	                                            [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
                                            (
	                                            '{wmsTskTaskMd.TaskNo}',
	                                            '{cmdNo}',
	                                            '{wmsTskTaskMd.TrayCode}',
	                                            '{wmsTskTaskMd.PalletCode}',
	                                            '{(int)wmsTskTaskMd.TrayLoadedType}',
	                                            '{locRouteMd.DevNo}',
	                                            '{sLocNo.LocCode}',
	                                            '{sLocNo.LocCode}',
	                                            '{eLocNo.LocCode}',
	                                            '{eLocNo.LocCode}',
	                                            '{(int)TranCmdTypeEnum.TRANSFER}',
	                                            '{(int)TranActiveTypeEnum.直行}',
	                                            100,
	                                            '{(int)TranCmdStatusEnum.初始创建}',
	                                            NULL,
	                                            '{(int)WcsCmdErrFlagEnum.正常}',
	                                            NULL,
	                                            '{wmsTskTaskMd.UpdateBy}',
	                                            getdate(),
	                                            {wmsTskTaskMd.UpdateBy},
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                        ";
                string sqlUpdateWmsTask = $@"
                                        UPDATE WMS_TSK_TASK
                                        SET CLOC_CODE = '{locRouteMd.SlocNo}',
                                         CMD_NO = '{cmdNo}',
                                         ROUTE_CODE = '{locRouteMd.SectionNo}',
                                         TASK_STATUS = '{(int)WmsTaskStatusEnum.指令下发}',
                                         UPDATE_TIME = GETDATE()
                                        WHERE
	                                        TASK_ID = '{wmsTskTaskMd.TaskId}'
                                    ";
                List<string> sqlLst = new List<string>
                {
                    sqlUpdateWmsTask,
                    sqlInsertTranCmd
                };
                int row = LeadDbHelperSQLServer.ExecuteSqlTran(sqlLst);
                if (row > 0)
                {
                    wmsTskTaskMd.ClocCode = locRouteMd.SlocNo;
                    wmsTskTaskMd.CmdNo = Convert.ToInt32(cmdNo);
                    wmsTskTaskMd.RouteCode = locRouteMd.SectionNo;
                    wmsTskTaskMd.TaskStatus = WmsTaskStatusEnum.指令下发;
                    wmsTskTaskMd.UpdateTime = DateTime.Now;
                    return wmsTskTaskMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"修改WMS大任务状态、新增输送线指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        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 WmsTskTaskMd InsertCrnCmdAndUpdateWmsTask(WmsTskTaskMd wmsTskTaskMd, BasBinMd sBinMd, BasBinMd eBinMd, BasWcsDevMd devMd)
        {
            try
            {
                string cmdNo = BasCommon_Dal.Instance.GetSysSequence("Cmd_No");
                string sqlInsertCrnCmd = $@"
                                            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
                                            (
	                                            '{wmsTskTaskMd.TaskNo}',
	                                            '{cmdNo}',
	                                            '{wmsTskTaskMd.TrayCode}',
	                                            '{wmsTskTaskMd.PalletCode}',
	                                            '{(int)wmsTskTaskMd.TrayLoadedType}',
	                                            '{devMd.DevCode}',
	                                            '{devMd.DevCode}',
	                                            '{sBinMd.BinCode}',
	                                            '{sBinMd.BinRow}',
	                                            '{sBinMd.BinColumn}',
	                                            '{sBinMd.BinLayer}',
	                                            '{sBinMd.EXTENSION_GROUP}',
	                                            '{sBinMd.EXTENSION_IDX}',
	                                            '{eBinMd.BinCode}',
	                                            '{eBinMd.BinRow}',
	                                            '{eBinMd.BinColumn}',
	                                            '{eBinMd.BinLayer}',
	                                            '{eBinMd.EXTENSION_GROUP}',
	                                            '{eBinMd.EXTENSION_IDX}',
	                                            '{CrnCmdTypeEnum.MOVE}',
	                                            '{(int)CrnActiveTypeEnum.取放货}',
	                                            1,
	                                            '{(int)CrnCmdStatusEnum.初始创建}',
	                                            NULL,
	                                            '{(int)WcsCmdErrFlagEnum.正常}',
	                                            NULL,
	                                            '{wmsTskTaskMd.UpdateBy}',
	                                            getdate(),
	                                            '{wmsTskTaskMd.UpdateBy}',
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                        ";
                string sqlUpdateWmsTask = $@"
                                        UPDATE WMS_TSK_TASK
                                        SET CLOC_CODE = '{wmsTskTaskMd.SlocCode}',
                                         CMD_NO = '{cmdNo}',
                                         ROUTE_CODE = '',
                                         TASK_STATUS = '{(int)WmsTaskStatusEnum.指令下发}',
                                         UPDATE_TIME = GETDATE()
                                        WHERE
	                                        TASK_ID = '{wmsTskTaskMd.TaskId}'
                                    ";
                List<string> sqlLst = new List<string>
                {
                    sqlUpdateWmsTask,
                    sqlInsertCrnCmd
                };
                int row = LeadDbHelperSQLServer.ExecuteSqlTran(sqlLst);
                if (row > 0)
                {
                    wmsTskTaskMd.ClocCode = wmsTskTaskMd.SlocCode;
                    wmsTskTaskMd.CmdNo = Convert.ToInt32(cmdNo);
                    wmsTskTaskMd.RouteCode = "";
                    wmsTskTaskMd.TaskStatus = WmsTaskStatusEnum.指令下发;
                    wmsTskTaskMd.UpdateTime = DateTime.Now;
                    return wmsTskTaskMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"修改WMS大任务状态、新增堆垛机指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public WmsTskTaskMd InsertCrnCmdAndUpdateWmsTask(WmsTskTaskMd wmsTskTaskMd, BasBinMd sBinMd, BasBinMd eBinMd, BasNextLocRouteMd locRouteMd)
        {
            try
            {
                string cmdNo = BasCommon_Dal.Instance.GetSysSequence("Cmd_No");
                string sqlInsertCrnCmd = $@"
                                            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
                                            (
	                                            '{wmsTskTaskMd.TaskNo}',
	                                            '{cmdNo}',
	                                            '{wmsTskTaskMd.TrayCode}',
	                                            '{wmsTskTaskMd.PalletCode}',
	                                            '{(int)wmsTskTaskMd.TrayLoadedType}',
	                                            '{locRouteMd.DevNo}',
	                                            '{locRouteMd.DevNo}',
	                                            '{sBinMd.BinCode}',
	                                            '{sBinMd.BinRow}',
	                                            '{sBinMd.BinColumn}',
	                                            '{sBinMd.BinLayer}',
	                                            '{sBinMd.EXTENSION_GROUP}',
	                                            '{sBinMd.EXTENSION_IDX}',
	                                            '{eBinMd.BinCode}',
	                                            '{eBinMd.BinRow}',
	                                            '{eBinMd.BinColumn}',
	                                            '{eBinMd.BinLayer}',
	                                            '{eBinMd.EXTENSION_GROUP}',
	                                            '{eBinMd.EXTENSION_IDX}',
	                                            '{CrnCmdTypeEnum.IN}',
	                                            '{(int)CrnActiveTypeEnum.取放货}',
	                                            '{wmsTskTaskMd.TaskPriority}',
	                                            '{(int)CrnCmdStatusEnum.初始创建}',
	                                            NULL,
	                                            '{(int)WcsCmdErrFlagEnum.正常}',
	                                            NULL,
	                                            '{wmsTskTaskMd.UpdateBy}',
	                                            getdate(),
	                                            '{wmsTskTaskMd.UpdateBy}',
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                        ";
                string sqlUpdateWmsTask = $@"
                                        UPDATE WMS_TSK_TASK
                                        SET CLOC_CODE = '{locRouteMd.SlocNo}',
                                         CMD_NO = '{cmdNo}',
                                         ROUTE_CODE = '{locRouteMd.SectionNo}',
                                         TASK_STATUS = '{(int)WmsTaskStatusEnum.指令下发}',
                                         UPDATE_TIME = GETDATE()
                                        WHERE
	                                        TASK_ID = '{wmsTskTaskMd.TaskId}'
                                    ";
                List<string> sqlLst = new List<string>
                {
                    sqlUpdateWmsTask,
                    sqlInsertCrnCmd
                };
                int row = LeadDbHelperSQLServer.ExecuteSqlTran(sqlLst);
                if (row > 0)
                {
                    wmsTskTaskMd.ClocCode = locRouteMd.SlocNo;
                    wmsTskTaskMd.CmdNo = Convert.ToInt32(cmdNo);
                    wmsTskTaskMd.RouteCode = locRouteMd.SectionNo;
                    wmsTskTaskMd.TaskStatus = WmsTaskStatusEnum.指令下发;
                    wmsTskTaskMd.UpdateTime = DateTime.Now;
                    return wmsTskTaskMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"修改WMS大任务状态、新增堆垛机指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public int GetLimitWmsTaskNum(string val)
        {
            try
            {
                string sql = $@"SELECT PARAMETER_VALUE FROM SYS_PARAMETER WHERE PARAMETER_CODE = '{val}'";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取参数信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return 0;
            }
        }

        public int GetRuningWmsInstockTaskNum()
        {
            try
            {
                string sql = $@"SELECT COUNT(1) FROM WMS_TSK_TASK WHERE TASK_TYPE IN (1,2,3,4,5) AND TASK_STATUS < 99";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取正在作业的入库大任务统计数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return 0;
            }
        }

        public int GetRuningWmsOutstockTaskNum()
        {
            try
            {
                string sql = $@"SELECT COUNT(1) FROM WMS_TSK_TASK WHERE TASK_TYPE IN (6,7,8,9) AND TASK_STATUS < 99";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取正在作业的入库大任务统计数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return 0;
            }
        }

    }
}