NX_WCS/NX_WcsDal/CommonBusiness/BasCommon_Dal.cs

using NX_CommonClassLibrary;
using NX_DbClassLibrary;
using NX_LogClassLibrary;
using NX_ModelClassLibrary.BaseModel;
using NX_ModelClassLibrary.CustomEnum;
using NX_ModelClassLibrary.OpcModel;
using NX_ModelClassLibrary.SysModel;
using NX_ModelClassLibrary.WmsBalance;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

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

        #region 获取基础配置信息 PLC、设备、设备类型

        /// <summary>
        /// 获取基础PLC配置信息数据
        /// </summary>
        /// <returns></returns>
        public List<BasWcsPlcMd> GetBasWcsPlcMsg(string regionCode)
        {
            try
            {
                string sqlQueryBasWcsPlc = $@"
                                                SELECT
	                                                A.PLC_ID,
	                                                A.NEWID,
	                                                A.REGION_CODE,
	                                                A.PLC_CODE,
	                                                A.PLC_NAME,
	                                                A.CONN_TYPE_CODE,
	                                                A.PLC_TYPE_CODE,
	                                                A.IP_ADDR,
	                                                A.PORT_NO,
	                                                A.READ_BLOCK_NO,
	                                                A.READ_START_POS,
	                                                A.READ_LEN,
	                                                A.WRITE_BLOCK_NO,
	                                                A.WRITE_START_POS,
	                                                A.WRITE_LEN,
	                                                A.USED_FLAG,
	                                                A.DEL_FLAG,
	                                                A.CREATE_BY,
	                                                A.CREATE_TIME,
	                                                A.UPDATE_BY,
	                                                A.UPDATE_TIME,
	                                                A.DATA_VERSION,
	                                                A.REMARKS1,
	                                                A.REMARKS2,
	                                                A.REMARKS3,
	                                                A.REMARKS4,
	                                                A.REMARKS5,
	                                                B.USER_CODE CreateCode,
	                                                B.USER_NAME CreateName,
	                                                C.USER_CODE UpdateCode,
	                                                C.USER_NAME UpdateName
                                                FROM
	                                                WCS_BAS_PLC 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.REGION_CODE = '{regionCode}'
                                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryBasWcsPlc).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasWcsPlcMd> retLst = new List<BasWcsPlcMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<BasWcsPlcMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取基础PLC配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        /// <summary>
        /// 获取基础设备配置信息数据
        /// </summary>
        /// <returns></returns>
        public List<BasWcsDevMd> GetBasWcsDevMsg(string regionCode)
        {
            try
            {
                string sqlQueryBasWcsDev = $@"
                                                SELECT
	                                                A.DEV_ID,
	                                                A.NEWID,
	                                                A.REGION_CODE,
	                                                A.DEV_TYPE_CODE,
	                                                A.DEV_CODE,
	                                                A.DEV_NAME,
	                                                A.DEV_PLC_NO,
	                                                A.USED_FLAG,
	                                                A.DEL_FLAG,
	                                                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
	                                                WCS_BAS_DEV 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.REGION_CODE = '{regionCode}'
                                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryBasWcsDev).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasWcsDevMd> retLst = new List<BasWcsDevMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<BasWcsDevMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取基础设备配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        /// <summary>
        /// 获取基础设备类型配置信息数据
        /// </summary>
        /// <returns></returns>
        public List<BasWcsDevTypeMd> GetBasWcsDevTypeMsg()
        {
            try
            {
                string sqlQueryBasWcsDevType = $@"
                                                SELECT
	                                                A.DEV_TYPE_ID,
	                                                A.NEWID,
	                                                A.DEV_TYPE_CODE,
	                                                A.DEV_TYPE_NAME,
	                                                A.[DESCRIBE],
	                                                A.USED_FLAG,
	                                                A.DEL_FLAG,
	                                                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
	                                                WCS_BAS_DEV_TYPE 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
                                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryBasWcsDevType).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasWcsDevTypeMd> retLst = new List<BasWcsDevTypeMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<BasWcsDevTypeMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取基础设备类型配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        /// <summary>
        /// 获取所有OPCItem变量数据
        /// </summary>
        /// <returns></returns>
        public List<WcsOpcItemMd> GetAllWcsOpcItemMsg(string regionCode)
        {
            try
            {
                string sqlQueryOpcItem = $@"
                                            SELECT
	                                            A.OPC_ITEM_ID,
	                                            A.NEWID,
	                                            A.OPC_GROUP_CODE,
	                                            A.OPC_ITEM_CODE,
	                                            A.OPC_ITEM_NAME,
	                                            A.OPC_ITEM_DESC,
	                                            A.PLC_CODE,
	                                            D.PLC_NAME,
	                                            A.DEV_CODE,
	                                            E.DEV_NAME,
	                                            A.OPC_ITEM_TYPE,
                                                F.PLC_ITEM_DATA_TYPE,
	                                            A.OPC_ITEM_POS,
	                                            A.USED_FLAG,
	                                            A.DEL_FLAG,
	                                            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
	                                            WCS_BAS_OPC_ITEM 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
                                            left JOIN WCS_BAS_PLC D ON A.PLC_CODE = D.PLC_CODE
                                            LEFT JOIN WCS_BAS_DEV E ON A.DEV_CODE = E.DEV_CODE
                                            LEFT JOIN WCS_MOT_PLC_ITEM F ON A.OPC_ITEM_CODE = F.PLC_ITEM_CODE 
                                            AND A.DEV_CODE = F.DEV_CODE
                                            WHERE
	                                            A.USED_FLAG = 1
                                            AND A.DEL_FLAG = 0
                                            AND D.REGION_CODE = '{regionCode}'
                                            AND E.REGION_CODE = '{regionCode}'
                                        ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryOpcItem).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsOpcItemMd> retLst = new List<WcsOpcItemMd>();
                    foreach (DataRow item in dt.Rows)
                    {                     
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<WcsOpcItemMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取所有OPCItem变量数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        /// <summary>
        /// 获取所有配置好的站台信息数据
        /// </summary>
        /// <returns></returns>
        public List<BasWcsLocMd> GetAllWcsLocMsg(string regionCode)
        {
            try
            {
                string sqlQueryAllWcsLoc = $@"
                                                SELECT
	                                                A.LOC_ID,
	                                                A.NEWID,
	                                                A.REGION_CODE,
	                                                A.LOC_CODE,
	                                                A.LOC_NAME,
	                                                A.LOC_TYPE_CODE,
	                                                A.LOC_EXT_MSG,
	                                                A.LOC_BIN_EXT_MSG,
	                                                A.VIRTUAL_FLAG,
	                                                A.CACHE_FLAG,
	                                                A.CACHE_SIZE,
	                                                A.USED_FLAG,
	                                                A.DEL_FLAG,
	                                                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
	                                                WCS_BAS_LOC 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.REGION_CODE = '{regionCode}'
                                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryAllWcsLoc).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasWcsLocMd> retLst = new List<BasWcsLocMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasWcsLocMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasWcsLocMd>(dt.Columns, item);
                        retLst.Add(tmpMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取所有配置好的站台信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        #endregion

        #region 获取序列号
        /// <summary>
        /// 调用存储过程获取序列号
        /// </summary>
        /// <param name="sqCode">序列号编码</param>
        /// <returns></returns>
        public string GetSysSequence(string sqCode)
        {
            try
            {
                SqlParameter[] pr = { new SqlParameter("@sq_code",SqlDbType.VarChar,100) ,
                                new SqlParameter("@count",SqlDbType.Int),
                                new SqlParameter("@out_id",SqlDbType.VarChar,100)
                                };
                pr[0].Direction = ParameterDirection.Input;
                pr[0].Value = sqCode;
                pr[1].Direction = ParameterDirection.Input;
                pr[1].Value = 1;
                pr[2].Direction = ParameterDirection.Output;
                pr[2].Value = "";
                LeadDbHelperSQLServer.RunProcedure("Sys_Proc_Get_Sequences", pr);
                return pr[2].Value.ToString();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取序列号发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        #endregion

        public List<BasBarcodeScannerMd> GetBarCodeScannerLst(string regionCode)
        {
            try
            {
                string sql = $@"select * from BAS_BARCODE_SCANNER where REGION_CODE = '{regionCode}' and USED_FLAG = 1";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasBarcodeScannerMd> retLst = new List<BasBarcodeScannerMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBarcodeScannerMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBarcodeScannerMd>(dt.Columns, item);
                        retLst.Add(tmpMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取库位监控数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public List<BasBinMonitorMd> GetBinMonitorData()
        {
            try
            {
                string sql = $@"select * from VW_WMS_BALANCE_MONITOR";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasBinMonitorMd> retLst = new List<BasBinMonitorMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBinMonitorMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMonitorMd>(dt.Columns, item);
                        retLst.Add(tmpMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取库位监控数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public WmsTrayMd GetWmsTrayMd(string trayCode)
        {
            try
            {
                string sqlWmsTray = $@"select * from VW_WMS_STK_TRAY where TRAY_CODE = '{trayCode}' and TRAY_STATUS < 99";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlWmsTray).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    WmsTrayMd trayMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WmsTrayMd>(dt.Columns, item);

                    string sqlWmsTrayDtlExt = $@"select * from VW_WMS_STK_TRAY_DTL_EXT where TRAY_ID = '{trayMd.TrayId}'";
                    DataTable dtTrayDtlExt = LeadDbHelperSQLServer.Query(sqlWmsTrayDtlExt).Tables[0];
                    List<WmsTrayDtlExtMd> trayDtlExtLst = new List<WmsTrayDtlExtMd>();
                    if (dtTrayDtlExt != null && dtTrayDtlExt.Rows.Count > 0)
                    {
                        foreach (DataRow drTrayDtlExtMd in dtTrayDtlExt.Rows)
                        {
                            WmsTrayDtlExtMd trayDtlExtMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WmsTrayDtlExtMd>(dtTrayDtlExt.Columns, drTrayDtlExtMd);
                            trayDtlExtLst.Add(trayDtlExtMd);
                        }
                    }

                    string sqlWmsTrayDtl = $@"select * from VW_WMS_STK_TRAY_DTL where TRAY_ID = '{trayMd.TrayId}'";
                    DataTable dtTrayDtl = LeadDbHelperSQLServer.Query(sqlWmsTrayDtl).Tables[0];
                    List<WmsTrayDtlMd> trayDtlLst = new List<WmsTrayDtlMd>();
                    if (dtTrayDtl != null && dtTrayDtl.Rows.Count > 0)
                    {
                        foreach (DataRow drTrayDtlMd in dtTrayDtl.Rows)
                        {
                            WmsTrayDtlMd trayDtlMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WmsTrayDtlMd>(dtTrayDtl.Columns, drTrayDtlMd);
                            trayDtlMd.trayDtlExtMd = trayDtlExtLst.Find(x => x.TrayDtlId == trayDtlMd.TrayDtlId);
                            trayDtlLst.Add(trayDtlMd);
                        }
                    }
                    trayMd.TrayDtls = trayDtlLst;
                    return trayMd;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取组盘数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public List<BasBinMd> GetEmptyOutSideExtentionBinLst(string regionCode, List<BasShelfMd> shelfLst)
        {
            try
            {
                string str = string.Join("','", shelfLst.Select(x => x.ShelfCode));
                str = str.Substring(0, str.Length - 2);
                string shelfCondition = "('" + str + ")";
                string sql = $@"
                                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.REGION_CODE = '{regionCode}'
                                AND A.BIN_TYPE = '3'
                                AND A.USED_FLAG = '1'
                                AND A.EXTENSION_IDX = 2 -- 外伸
                                AND A.BIN_CODE NOT IN (
	                                SELECT
		                                BIN_CODE
	                                FROM
		                                VW_BALANCE_TEMP
                                )
                                AND A.EXTENSION_GROUP NOT IN (
	                                SELECT
		                                B.EXTENSION_GROUP
	                                FROM
		                                VW_BALANCE_TEMP A
	                                LEFT JOIN BAS_BIN B ON A.BIN_CODE = B.BIN_CODE
                                )
                                AND A.SHELF_CODE IN {shelfCondition}
                                ORDER BY
	                                A.BIN_LAYER,
	                                A.BIN_COLUMN,
	                                A.BIN_ROW
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasBinMd> retLst = new List<BasBinMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取空闲的内伸无货的外伸库位数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public List<BasShelfMd> GetCrnRelationShelfLst(BasBinMd binMd)
        {
            try
            {
                string sql = $@"
                                SELECT
	                                *
                                FROM
	                                BAS_SHELF
                                WHERE
	                                DEV_CODE = (
		                                SELECT
			                                DEV_CODE
		                                FROM
			                                BAS_SHELF
		                                WHERE
			                                SHELF_CODE = '{binMd.ShelfCode}'
	                                )
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasShelfMd> retLst = new List<BasShelfMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasShelfMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasShelfMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取堆垛机关联的货架排数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public List<BasBinMd> GetIsGoodsOutSideExtentionBinLst(string regionCode, List<BasShelfMd> shelfLst, string materielCode)
        {
            try
            {
                string str = string.Join("','", shelfLst.Select(x => x.ShelfCode));
                str = str.Substring(0, str.Length - 2);
                string shelfCondition = "('" + str + ")";
                string sql = $@"
                                SELECT
	                                B.*, C.USER_CODE CreateCode,
	                                C.USER_NAME CreateName,
	                                D.USER_CODE UpdateCode,
	                                D.USER_NAME UpdateName
                                FROM
	                                WMS_STK_BALANCE A
                                LEFT JOIN BAS_BIN B ON A.BIN_CODE = B.BIN_CODE
                                LEFT JOIN SYS_USER C ON B.CREATE_BY = C.USER_ID
                                LEFT JOIN SYS_USER D ON B.UPDATE_BY = D.USER_ID
                                WHERE
	                                A.TRAY_ID IN (
		                                SELECT
			                                TRAY_ID
		                                FROM
			                                WMS_STK_TRAY_DTL
		                                WHERE
			                                MATERIEL_CODE = '{materielCode}'
		                                AND TRAY_DTL_STATUS < 99
	                                ) -- 如果后续管控批次，管理库存拓展表，在此添加批次条件即可
                                AND A.BALANCE_STATUS < 99
                                AND B.EXTENSION_IDX = 2
                                AND B.REGION_CODE = '{regionCode}'
                                AND A.REGION_CODE = '{regionCode}'
                                AND B.SHELF_CODE IN {shelfCondition}
                                ORDER BY
	                                B.BIN_COLUMN,
	                                B.BIN_LAYER,
	                                B.BIN_ROW
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasBinMd> retLst = new List<BasBinMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取同一个商品已分配的外伸库位数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public List<BasBinMd> GetEmptyIntroversionBinLst(string regionCode, List<BasShelfMd> shelfLst)
        {
            try
            {
                string str = string.Join("','", shelfLst.Select(x => x.ShelfCode));
                str = str.Substring(0, str.Length - 2);
                string shelfCondition = "('" + str + ")";
                string sql = $@"
                                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.REGION_CODE = '{regionCode}'
                                AND A.BIN_TYPE = '3'
                                AND A.USED_FLAG = '1'
                                AND A.EXTENSION_IDX = 1 -- 内伸
                                AND A.BIN_CODE NOT IN (
	                                SELECT
		                                BIN_CODE
	                                FROM
		                                VW_BALANCE_TEMP
	                                WHERE
		                                REGION_CODE = '{regionCode}'
                                )
                                AND A.SHELF_CODE IN {shelfCondition}
                                ORDER BY
	                                A.BIN_COLUMN,
	                                A.BIN_LAYER,
	                                A.BIN_ROW 
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasBinMd> retLst = new List<BasBinMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<BasBinMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取空闲的内伸库位发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        public BasBinMd GetEmptyBinMd(string regionCode, List<BasShelfMd> shelfLst)
        {
            try
            {
                string str = string.Join("','", shelfLst.Select(x => x.ShelfCode));
                str = str.Substring(0, str.Length - 2);
                string shelfCondition = "('" + str + ")";
                string sqlQueryEmptyBin = $@"
                                            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 NOT IN (
	                                            SELECT
		                                            BIN_CODE
	                                            FROM
		                                            WMS_STK_BALANCE
	                                            WHERE
		                                            BALANCE_STATUS < 99
	                                            AND REGION_CODE = '{regionCode}'
                                            )                                            
                                            AND A.SHELF_CODE IN {shelfCondition}
                                            AND A.REGION_CODE = '{regionCode}'
                                            ORDER BY
	                                            A.BIN_COLUMN,
	                                            A.BIN_LAYER,
	                                            A.BIN_ROW 
                                        ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryEmptyBin).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($"获取库区：【{regionCode}】的空闲库位发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public List<WcsOpcItemMonitorMd> GetOpcItemMonitorData(string devTypeCode)
        {
            try
            {
                string sql = $@"SELECT A.*,B.DEV_NAME FROM WCS_MOT_PLC_ITEM A LEFT JOIN WCS_BAS_DEV B ON A.DEV_CODE = B.DEV_CODE LEFT JOIN WCS_BAS_DEV_TYPE C ON B.DEV_TYPE_CODE = C.DEV_TYPE_CODE WHERE B.DEV_TYPE_CODE = '{devTypeCode}'";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsOpcItemMonitorMd> retLst = new List<WcsOpcItemMonitorMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        WcsOpcItemMonitorMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsOpcItemMonitorMd>(dt.Columns, item);
                        retLst.Add(tmpMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取OPC变量监控数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }

        private readonly object thLock = new object();
        public void UpdateOpcItemMonitorToDataBase(WcsOpcItemMd wcsOpcItemMd)
        {
            lock (thLock)
            {
                try
                {
                    string sql = $@"update WCS_MOT_PLC_ITEM set PLC_ITEM_VALUE = '{wcsOpcItemMd.OpcItemValue}' where DEV_CODE = '{wcsOpcItemMd.DevCode}' and PLC_ITEM_CODE = '{wcsOpcItemMd.OpcItemCode}'";
                    LeadDbHelperSQLServer.ExecuteSql(sql);
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog($"OPC变量监控实时数据更新到数据库发生异常！【{ex.Message}】", LogTypeEnum.Err);
                }
            }
        }
        public List<SysParameterMd> GetWcsParameterLst(SysParameterSearchMd searchMd)
        {
            try
            {
                string sqlQuery = string.Empty;
                if (!string.IsNullOrEmpty(searchMd.ParameterMsg))
                {
                    sqlQuery += $" AND (A.PARAMETER_CODE like '%{searchMd.ParameterMsg}%' or A.PARAMETER_NAME like '%{searchMd.ParameterMsg}%')";
                }
                if (!string.IsNullOrEmpty(searchMd.ParameterValue))
                {
                    sqlQuery += $" AND A.PARAMETER_VALUE = '{searchMd.ParameterValue}'";
                }
                if (!string.IsNullOrEmpty(searchMd.ParameterEditFlag))
                {
                    sqlQuery += $" AND A.ALLOW_EDIT_FLAG = '{searchMd.ParameterEditFlag}'";
                }
                string sql = $@"
                                SELECT
	                                A.*, B.USER_CODE CreateCode,
	                                B.USER_NAME CreateName,
	                                C.USER_CODE UpdateCode,
	                                C.USER_NAME UpdateName
                                FROM
	                                SYS_PARAMETER 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 PARAMETER_TYPE = 'WCS'
                                {sqlQuery}
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<SysParameterMd> retLst = new List<SysParameterMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<SysParameterMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取WCS参数信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public SysParameterMd GetWcsParameterMd(string paramId)
        {
            try
            {
                string sql = $@"
                                SELECT
	                                A.*, B.USER_CODE CreateCode,
	                                B.USER_NAME CreateName,
	                                C.USER_CODE UpdateCode,
	                                C.USER_NAME UpdateName
                                FROM
	                                SYS_PARAMETER 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
	                                PARAMETER_ID = '{paramId}'
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow item = dt.Rows[0];
                    return ColumnToClassPropertyHelper.ColumnToClassProperty<SysParameterMd>(dt.Columns, item);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取WCS参数信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public int EditParamMsg(SysParameterMd paramMd)
        {
            try
            {
                string sql = $@"update SYS_PARAMETER set PARAMETER_NAME = '{paramMd.ParameterName}',PARAMETER_VALUE = '{paramMd.ParameterValue}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where PARAMETER_ID = '{paramMd.ParameterId}'";
                return LeadDbHelperSQLServer.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"编辑WCS参数信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return 0;
            }
        }
        public List<BasDevAlarmMd> GetDevAlarmMsg(BasDevAlarmSearchMd searchMd)
        {
            try
            {
                string sqlQuery = string.Empty;
                if (!string.IsNullOrEmpty(searchMd.DevTypeMsg))
                {
                    sqlQuery += $" AND C.DEV_TYPE_CODE = '{searchMd.DevTypeMsg}'";
                }
                if (!string.IsNullOrEmpty(searchMd.DevMsg))
                {
                    sqlQuery += $" AND B.DEV_CODE = '{searchMd.DevMsg}'";
                }
                if (!string.IsNullOrEmpty(searchMd.FaultMsg))
                {
                    sqlQuery += $" AND (A.FAULT_NO like '%{searchMd.FaultMsg}%' OR A.FAULT_DESC like '%{searchMd.FaultMsg}%')";
                }
                if (!string.IsNullOrEmpty(searchMd.StartFaultTime))
                {
                    sqlQuery += $" AND A.FAULT_TIME >= '{searchMd.StartFaultTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.EndFaultTime))
                {
                    sqlQuery += $" AND A.FAULT_TIME <= '{searchMd.EndFaultTime}'";
                }
                int pageStartIndex = (searchMd.PageNum - 1) * searchMd.EveryPageQty;
                int pageEndIndex = searchMd.PageNum * searchMd.EveryPageQty;
                string sql = $@"
                                SELECT
	                                A.LOG_FAULT_ID,
	                                C.DEV_TYPE_NAME,
	                                B.DEV_NAME,
	                                A.FAULT_NO,
	                                A.FAULT_DESC,
	                                A.FAULT_TIME,
	                                A.WARING_STATUS,
	                                A.END_TIME
                                FROM
	                                WMS_LOG_FAULT_MSG A
                                LEFT JOIN WCS_BAS_DEV B ON A.FAULT_DEV_CODE = B.DEV_CODE
                                LEFT JOIN WCS_BAS_DEV_TYPE C ON B.DEV_TYPE_CODE = C.DEV_TYPE_CODE
                                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<BasDevAlarmMd> retLst = new List<BasDevAlarmMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<BasDevAlarmMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取设备报警信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        public List<BasFaultMd> GetBasFaultMsg()
        {
            try
            {
                string sql = $@"
                                SELECT
	                                A.FAULT_ID,
	                                A.NEWID,
	                                A.FAULT_CODE,
	                                A.FAULT_NAME,
	                                A.DEV_TYPE_CODE,
	                                A.[DESCRIBE],
	                                A.USED_FLAG,
	                                A.DEL_FLAG,
	                                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
	                                BAS_FAULT 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
                            ";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<BasFaultMd> retLst = new List<BasFaultMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty<BasFaultMd>(dt.Columns, item));
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"获取基础报警代码信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
    }
}