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 单例模式
///
/// 单例模式对象
///
private static BasCommon_Dal _instance = null;
private static readonly object lockObj = new object();
///
/// 单例模式方法
///
public static BasCommon_Dal Instance
{
get
{
if (_instance == null)
{
lock (lockObj)
{
if (_instance == null)
{
_instance = new BasCommon_Dal();
}
}
}
return _instance;
}
}
#endregion
#region 获取基础配置信息 PLC、设备、设备类型
///
/// 获取基础PLC配置信息数据
///
///
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取基础PLC配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
///
/// 获取基础设备配置信息数据
///
///
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取基础设备配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
///
/// 获取基础设备类型配置信息数据
///
///
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取基础设备类型配置信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
///
/// 获取所有OPCItem变量数据
///
///
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取所有OPCItem变量数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
///
/// 获取所有配置好的站台信息数据
///
///
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasWcsLocMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item);
retLst.Add(tmpMd);
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取所有配置好的站台信息数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
#endregion
#region 获取序列号
///
/// 调用存储过程获取序列号
///
/// 序列号编码
///
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 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasBarcodeScannerMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item);
retLst.Add(tmpMd);
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取库位监控数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasBinMonitorMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty(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(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 trayDtlExtLst = new List();
if (dtTrayDtlExt != null && dtTrayDtlExt.Rows.Count > 0)
{
foreach (DataRow drTrayDtlExtMd in dtTrayDtlExt.Rows)
{
WmsTrayDtlExtMd trayDtlExtMd = ColumnToClassPropertyHelper.ColumnToClassProperty(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 trayDtlLst = new List();
if (dtTrayDtl != null && dtTrayDtl.Rows.Count > 0)
{
foreach (DataRow drTrayDtlMd in dtTrayDtl.Rows)
{
WmsTrayDtlMd trayDtlMd = ColumnToClassPropertyHelper.ColumnToClassProperty(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 GetEmptyOutSideExtentionBinLst(string regionCode, List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item);
retLst.Add(retMd);
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取空闲的内伸无货的外伸库位数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasShelfMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item);
retLst.Add(retMd);
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取堆垛机关联的货架排数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List GetIsGoodsOutSideExtentionBinLst(string regionCode, List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item);
retLst.Add(retMd);
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取同一个商品已分配的外伸库位数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List GetEmptyIntroversionBinLst(string regionCode, List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
BasBinMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty(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 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(dt.Columns, item);
return retMd;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取库区：【{regionCode}】的空闲库位发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
WcsOpcItemMonitorMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty(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 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(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(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 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取设备报警信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
public List 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 retLst = new List();
foreach (DataRow item in dt.Rows)
{
retLst.Add(ColumnToClassPropertyHelper.ColumnToClassProperty(dt.Columns, item));
}
return retLst;
}
else
{
return null;
}
}
catch (Exception ex)
{
LogHelper.WriteLog($"获取基础报警代码信息发生异常！【{ex.Message}】", LogTypeEnum.Err);
return null;
}
}
}
}