/// <summary>
/// 获取设备实时数据
/// </summary>
/// <returns></returns>
public List <Machine> GetStateNowListQuery(string query, string iMachineGroupID, string status, string field, string sort)
{
//拼接where条件
string where = " WHERE A.iMachineGroupID=" + iMachineGroupID;
if (!String.IsNullOrEmpty(query))
{
where += " AND A.iMachineID=" + query;
}
if (!String.IsNullOrEmpty(status))
{
where += " AND A.iStatusID1=" + status;
}
//拼接排序
string order = "";
if (!String.IsNullOrEmpty(field) && !String.IsNullOrEmpty(sort) && field == "iMachineID")
{
order = " ORDER BY " + field + " " + sort;
}
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader(@"SELECT A.iMachineID,A.iStatusID1,A.iStatusID2,A.sWorkName,A.sTaskNo1
,A.sTaskNo2,A.nSpeed1,A.nSpeed2,A.nEfficiency1,A.nEfficiency2
FROM dbo.psYarnMachine A(NOLOCK)" + where + order);
List <Machine> list = TableListChange.TableToList <Machine>(data);
return(list);
}
/// <summary>
/// 获取设备实时数据
/// </summary>
/// <returns></returns>
public List <Warping> GetStateNowListQuery()
{
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader("EXEC dbo.sppbGetWarpingNow");
List <Warping> list = TableListChange.TableToList <Warping>(data);
return(list);
}
/// <summary>
/// 获取挡车工每日情况
/// </summary>
/// <returns></returns>
public List <LoomWorkerDay> GetWorkerDay(int iClassId)
{
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader("EXEC [dbo].[spzzReportfmWorkProductDaySumReport] @iDeptID = 1,@iClassListID=:iClassId", iClassId);
List <LoomWorkerDay> list = TableListChange.TableToList <LoomWorkerDay>(data);
return(list);
}
/// <summary>
/// 获取织机实时状态
/// </summary>
/// <returns></returns>
public List <LoomStateCoordinate> GetLoomStateQuery(int machineVar)
{
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader("EXEC dbo.sppbGetLoomStateQuery @iType=:machineVar", machineVar);
List <LoomStateCoordinate> list = TableListChange.TableToList <LoomStateCoordinate>(data);
return(list);
}
/// <summary>
/// 获取停台情况
/// </summary>
/// <returns></returns>
public List <IntData> GetLoomPie1()
{
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader(@"SELECT * FROM
(SELECT value=COUNT(1),name='运转'
FROM dbo.OpMachine A(NOLOCK)
WHERE A.iStatusID = 0
UNION
SELECT count = COUNT(1), name = '停台'
FROM dbo.OpMachine A(NOLOCK)
WHERE A.iStatusID <> 0) X
ORDER BY X.name DESC");
List <IntData> list = TableListChange.TableToList <IntData>(data);
return(list);
}
public List <OpMachine> GetListSimple(string field, string sort)
{
//拼接排序
string order = "";
if (!String.IsNullOrEmpty(field) && !String.IsNullOrEmpty(sort))
{
order = " ORDER BY " + field + " " + sort;
}
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader("SELECT iIden,iMachineID,sMachineName,sProductInfo,iPlanSpeed,iCurSpeed,nCurEfficiency FROM dbo.OpMachine(NOLOCK)" + order);
//数据集转换为列表
List <OpMachine> list = TableListChange.TableToList <OpMachine>(data);
return(list);
}
/// <summary>
/// 获取织机数据
/// </summary>
/// <returns></returns>
public List <Loom> GetLoomStateNowListQuery(string query, string status, string field, string sort)
{
//拼接where条件
string where = "";
if (!String.IsNullOrEmpty(query))
{
where = " WHERE X.iMachineID=" + query;
}
if (!String.IsNullOrEmpty(status))
{
if (where == "")
{
where = " WHERE C.iStatusID=" + status;
}
else
{
where += " AND C.iStatusID=" + status;
}
}
//拼接排序
string order = "";
if (!String.IsNullOrEmpty(field) && !String.IsNullOrEmpty(sort) && field == "iMachineID")
{
order = " ORDER BY " + field + " " + sort;
}
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader(@"SELECT X.iMachineID,X.iPickCount
,nBancieff=CONVERT(DECIMAL(18, 2),CASE WHEN (X.iRunTime + X.iAllStopTime)=0 THEN 0 ELSE X.iRunTime * 100.0/ (X.iRunTime + X.iAllStopTime)END)
,sBancieff=CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,2), CASE WHEN (X.iRunTime + X.iAllStopTime)=0 THEN 0 ELSE X.iRunTime * 100.0/ (X.iRunTime + X.iAllStopTime)END))+'%'
,nBanciSpeed=CASE WHEN X.iRunTime =0 THEN 0 ELSE CONVERT(DECIMAL(18,0),(X.iPickCount*60.0)/iRunTime) END
,iStatusID=C.iStatusID
,sRunTime=CONVERT(NVARCHAR(50),X.iRunTime/3600)+'小时'+CONVERT(NVARCHAR(50),X.iRunTime%3600/60)+'分'
,sAllStopTime=CONVERT(NVARCHAR(50),X.iAllStopTime/3600)+'小时'+CONVERT(NVARCHAR(50),X.iAllStopTime%3600/60)+'分'
FROM(SELECT A.iMachineID
,iRunTime=SUM(ISNULL(B.iRunTime, 0))
,iAllStopTime=SUM(ISNULL(B.iAllStopTime, 0))
,iPickCount=SUM(ISNULL(B.iPickCount, 0))
FROM dbo.OpMachine A(NOLOCK)
LEFT JOIN dbo.OpClassDataList B(NOLOCK) ON B.iMachineID = A.iMachineID AND B.iClassListID = dbo.fnzzGetClassid('NOW')
GROUP BY A.iMachineID)X
LEFT JOIN dbo.vwMachineMap C(NOLOCK) ON C.iMachineID=X.iMachineID" + where + order);
List <Loom> list = TableListChange.TableToList <Loom>(data);
return(list);
}
/// <summary>
/// 获取轴库明细统计
/// </summary>
/// <returns></returns>
public List <BeamLibraryCount> GetBeamLibraryCountQuery(string field, string sort)
{
//拼接排序
string order = "";
if (!String.IsNullOrEmpty(field) && !String.IsNullOrEmpty(sort) && (field == "count" || field == "sMaterialNo" || field == "nLength"))
{
order = " ORDER BY " + field + " " + sort;
}
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader(@"SELECT sMaterialNo,[count]=COUNT(1),nlength=SUM(CONVERT(DECIMAL(18,2),CASE nlength WHEN 0x THEN 0 ELSE nlength END))
FROM dbo.vwZhouKu A(NOLOCK)
WHERE A.kuwei<>'实时显示'
GROUP BY sMaterialNo" + order);
List <BeamLibraryCount> list = TableListChange.TableToList <BeamLibraryCount>(data);
return(list);
}
/// <summary>
/// 获取轴库明细数据
/// </summary>
/// <returns></returns>
public List <BeamLibrary> GetBeamLibraryQuery(string query, string sMaterialNo, string field, string sort)
{
//拼接where条件
string where = "";
if (!String.IsNullOrEmpty(query))
{
where += " AND A.kuwei=" + query;
}
if (!String.IsNullOrEmpty(sMaterialNo))
{
where += " AND A.sMaterialNo='" + sMaterialNo + "'";
}
//拼接排序
string order = "";
if (!String.IsNullOrEmpty(field) && !String.IsNullOrEmpty(sort) && (field == "kuwei" || field == "sMaterialNo" || field == "nLength" || field == "tDate"))
{
if (field == "kuwei")
{
order = " ORDER BY CONVERT(INT,kuwei)" + sort;
}
else
{
order = " ORDER BY " + field + " " + sort;
}
}
else
{
order = " ORDER BY CONVERT(INT,kuwei) asc";
}
//获取数据集
DataTable data = DBHelper.DbContext().m_ExecuteReader(@"SELECT A.*
FROM dbo.vwZhouKu A(NOLOCK)
WHERE A.kuwei<>'实时显示'" + where + order);
List <BeamLibrary> list = TableListChange.TableToList <BeamLibrary>(data);
return(list);
}
