public IList <Hashtable> ReadItmes(string deviceId)
{
DataTable dt = null;
IList <Hashtable> Ilist = new List <Hashtable>();
sql = "select * from T_BASE_ITEM where T_DEVICEID='" + deviceId + "'";
dt = DBdb2.RunDataTable(sql, out errMsg);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Hashtable ht = new Hashtable();
foreach (DataColumn col in dt.Columns)
{
ht.Add(col.ColumnName, dr[col.ColumnName]);
Ilist.Add(ht);
}
}
}
return(Ilist);
}
/// <summary>
/// 根据时间获取四类信息（首页使用）。
/// </summary>
/// <param name="times"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetHomeByTime(string times, out string errMsg)
{
this.init();
errMsg = "";
string sql = "select * from T_INFO_STATISTIC where 1=1";
DataTable dt = null;
if (!string.IsNullOrEmpty(times))
{
sql += " AND T_TIME='" + times + "'";
}
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
///根据时间段取得 参数描述（指标名称），平均基准值。取得的paraId留着使用。注：paraId+_el_B为实际值，paraId+_B为耗差值
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetValueByPara(string tableName, string beginTime, string endTime, string unitId, string paraId, out string errMsg)
{
this.init();
errMsg = "";
string sql = "select c.T_PARAID, b.T_DESC,b.I_TARGETTYPE,b.I_CONSUMETYPE,avg(c.D_VALUE) AS counts from " + tableName + " as c left join T_BASE_CONSUMEPARA as b on c.T_UNITID=b.T_UNITID and c.T_PARAID=b.T_PARAID WHERE 1=1";
DataTable dt = null;
if (!string.IsNullOrEmpty(beginTime) && !string.IsNullOrEmpty(endTime))
{
sql += " AND c.T_DATETIME between " + beginTime + "' and " + endTime + "";
}
if (!string.IsNullOrEmpty(unitId) && unitId != "0")
{
sql += " AND c.T_UNITID='" + unitId + "'";
}
if (!string.IsNullOrEmpty(paraId))
{
sql += " AND c.T_PARAID='" + paraId + "'";
}
sql += " group by c.T_PARAID,b.T_DESC,b.I_TARGETTYPE,b.I_CONSUMETYPE ";
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
/// 点检项分页
/// </summary>
/// <param name="sbID"></param>
/// <param name="sCount"></param>
/// <param name="eCount"></param>
/// <returns></returns>
public DataTable RetTabItemByDevID(string sbID, int sCount, int eCount)
{
//sql = "select * from (select b.*,rownumber() over(order by b.T_TIME desc ) as rowid from ( select * from T_BASE_STATUS as s inner join (SELECT i.ID_KEY,b.T_DEVICEID,b.T_DEVICEDESC,i.T_TIME,i.I_STATUS FROM T_INFO_DEVICE as i inner join T_BASE_DEVICE as b on i.T_DEVICEID=b.T_DEVICEID where i.T_DEVICEID='" + sbID + "' order by i.T_TIME desc) as t on s.I_STATUSID=t.I_STATUS) as b )as a where a.rowid BETWEEN " + sCount + " AND " + eCount;
try
{
//什么时候调用此方法分页 需要重新确定
if (int.Parse(sbID) > 0)
{ //sql ="select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID =(select T_NODEID from T_INFO_ROUTE where T_NODEKEY='" + sbID + "')) as y where y.rk<=1;";
sql = "select * from (select z.* , rownumber() over (order by z.id_key asc ) as rowid from ";
sql += " (select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID =(select T_NODEID from T_INFO_ROUTE where T_NODEKEY='" + sbID + "')) as y where y.rk<=1) as z)";
sql += " as f where f.rowid between " + sCount + " and " + eCount;
}
}
catch
{
//sql = "select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID ='" + sbID + "') as y where y.rk<=1;";
sql = "select * from ( select c.*,rownumber() over(order by c.id_key asc ) as rowid from ";
sql += "(select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID ='" + sbID + "') as y where y.rk<=1) as c) as d ";
sql += "where d.rowid BETWEEN " + sCount + " AND " + eCount;
}
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取ParaId及对应的数据表名。（已过滤）
/// </summary>
/// <returns></returns>
public List <ParaTableInfo> GetInfo(string capacityLevel, string unitType, string BoilerId, string SteamId, out string errMsg)
{
this.init();
errMsg = "";
List <ParaTableInfo> infoList = new List <ParaTableInfo>();
string sql = "select b.T_PARAID,c.T_DESC,c.T_OUTTABLE,b.T_UNITID,p.T_PLANTDESC ,u.T_UNITDESC from T_BASE_CONSUMEPARA as b left join T_BASE_CALCPARA as c on b.T_PARAID=c.T_PARAID left join T_BASE_UNIT as u on b.T_UNITID=u.T_UNITID left join T_BASE_BOILER as j on u.T_BOILERID = j.T_BOILERID left join T_BASE_STEAM as s on u.T_STEAMID=s.T_STEAMID left join T_BASE_PLANT as p on u.T_PLANTID=p.T_PLANTID where c.I_CONSUMETYPE is not null and c.I_TARGETTYPE IS NOT NULL";
if (!String.IsNullOrEmpty(capacityLevel) && capacityLevel != "0")
{
sql += " and u.T_CAPABILITYLEVEL='" + capacityLevel + "'";
}
if (!String.IsNullOrEmpty(unitType) && unitType != "0")
{
sql += " and u.T_PLANTTYPE='" + unitType + "'";
}
if (!String.IsNullOrEmpty(BoilerId) && BoilerId != "0")
{
sql += " and u.T_BOILERID='" + BoilerId + "'";
}
if (!String.IsNullOrEmpty(SteamId) && SteamId != "0")
{
sql += " and u.T_STEAMID='" + SteamId + "'";
}
DataTable dt = null;
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(dt.Rows[i]["T_OUTTABLE"].ToString()))
{
ParaTableInfo info = new ParaTableInfo();
info.ParaId = String.IsNullOrEmpty(dt.Rows[i]["T_PARAID"].ToString()) ? String.Empty : dt.Rows[i]["T_PARAID"].ToString();
info.ParaDesc = String.IsNullOrEmpty(dt.Rows[i]["T_DESC"].ToString()) ? String.Empty : dt.Rows[i]["T_DESC"].ToString();
info.OutTableName = dt.Rows[i]["T_OUTTABLE"].ToString();
info.UnitId = dt.Rows[i]["T_UNITID"].ToString();
info.UnitName = dt.Rows[i]["T_PLANTDESC"].ToString() + dt.Rows[i]["T_UNITDESC"].ToString();
infoList.Add(info);
}
}
}
//去掉重复。
//return infoList.Distinct(new EqualCompare<ParaTableInfo>((x, y) => (x != null && y != null) &&(x.OutTableName == y.OutTableName))).ToList();
//不能过滤，因为会把ParaId过滤掉。以ParaId来取值的。
return(infoList);
}
public DataTable ReadItemByIdKey(string id_key)
{
DataTable dt = null;
//IList<Hashtable> Ilist = new List<Hashtable>();
sql = "select ID_KEY,T_ITEMID,T_ITEMPOSITION, T_ITEMDESC,T_CONTENT,T_TYPE,I_STATUS,T_OBSERVE,T_UNIT,F_LOWER,F_UPPER,F_UPPER,I_SPECTRUM,T_DEVICEID,to_char(T_STARTTIME,'yyyy-mm-dd hh24:mi:ss') as T_STARTTIME,T_PERIODTYPE,T_PERIODVALUE,T_STATUS from T_BASE_ITEM where ID_KEY=" + id_key + "";
dt = DBdb2.RunDataTable(sql, out errMsg);
//if (dt != null && dt.Rows.Count > 0)
//{
// foreach (DataRow dr in dt.Rows)
// {
// Hashtable ht = new Hashtable();
// foreach (DataColumn col in dt.Columns)
// {
// ht.Add(col.ColumnName, dr[col.ColumnNameT_STATUS
// Ilist.Add(ht);
// }
// }
//}
return(dt);
}
/// <summary>
/// 获取所有的ParaId
/// </summary>
/// <param name="counts">返回ParaId总数，计算平均值使用</param>
/// <returns></returns>
public string GetParaId(out int counts, out string errMsg)
{
this.init();
errMsg = "";
counts = 0;
string sql = "select T_PARAID from T_BASE_ALLCONSUMEPARA ";
DataTable dt = null;
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
StringBuilder sb = new StringBuilder();
counts = dt.Rows.Count;
if (counts > 0)
{
for (int i = 0; i < counts; i++)
{
sb.Append(dt.Rows[i]["T_PARAID"].ToString());
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);
}
return(sb.ToString());
}
/// <summary>
/// 根据条件获取数据，可靠性分析(强迫停运次数分析（按容量分类）)。
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetInitByCapality(string time, out string errMsg)
{
this.init();
errMsg = "";
string sql = "SELECT SUM(I_FOT) AS FOH ,B.D_CAPABILITY FROM T_INFO_UNIT AS I LEFT JOIN T_BASE_UNIT AS B ON B.T_UNITID=I.T_CODE ";
DataTable dt = null;
if (!string.IsNullOrEmpty(time))
{
sql += " WHERE I.T_TIME='" + time + "'";
}
sql += " GROUP BY B.D_CAPABILITY ";
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
/// 根据条件获取数据，可靠性分析(强迫停运次数分析（按故障原因分类）)。
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetInitByReason(string time, out string errMsg)
{
this.init();
errMsg = "";
string sql = "SELECT SUM(I_FOT) AS FOH ,R.T_REASONDESC FROM T_INFO_UNIT AS I LEFT JOIN T_BASE_FAULTREASON AS R ON R.T_REASONID=I.T_FREASONID ";
DataTable dt = null;
if (!string.IsNullOrEmpty(time))
{
sql += " WHERE I.T_TIME='" + time + "'";
}
sql += " GROUP BY R.T_REASONDESC ";
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
/// 获取设备信息 某个区域下面
/// </summary>
/// <param name="AreaID">区域编号</param>
/// <returns></returns>
public IList <Hashtable> GetDevices(string AreaID)
{
sql = "select T_DEVICEID,T_DEVICEDESC from T_BASE_DEVICE where T_DEVICEID in( select T_NODEID from T_INFO_ROUTE where T_PARAENTID in(select T_NODEKEY from T_INFO_ROUTE where T_NODEID='" + AreaID + "'))";
dt = DBdb2.RunDataTable(sql, out errMsg);
list = dh.DataTableToList(dt);
return(list);
}
/// <summary>
/// 获取某个线路下面的区域
/// </summary>
/// <param name="routeID">线路编号</param>
/// <returns></returns>
public IList <Hashtable> GetAreas(string routeID)
{
sql = "select T_AREAID,T_AREANAME from T_BASE_AREA where T_AREAID in( select T_NODEID from T_INFO_ROUTE where T_PARAENTID in(select T_NODEKEY from T_INFO_ROUTE where T_NODEID='" + routeID + "'))";
dt = DBdb2.RunDataTable(sql, out errMsg);
list = dh.DataTableToList(dt);
return(list);
}
/// <summary>
/// 根据测点获取T_INFO_VALUE表中指定测点集合的信息
/// </summary>
/// <param name="pointsName">测点集合的名称</param>
/// <returns></returns>
public DataTable GetValueByPoints(string pointsName)
{
sql = "SELECT T_POINT,T_VALUE FROM ADMINISTRATOR.T_INFO_VALUE where T_POINT in (" + pointsName + ")";
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取某个设备下点检项所有ID_KEY
/// </summary>
/// <param name="sbID"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public string RetStrItemsBySbID(string sbID, out string errMsg)
{
errMsg = "";
string str = "";
string strName = "";
string strDevName = "";
DataTable dt = null;
//sql = "select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID ='"+sbID+"') as y where y.rk<=1;";
//try
//{
// if (int.Parse(sbID) > 0)
// sql = "select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID =(select T_NODEID from T_INFO_ROUTE where T_NODEKEY='" + sbID + "')) as y where y.rk<=1;";
//}
//catch
//{
// sql = "select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b where b.T_DeviceID ='" + sbID + "') as y where y.rk<=1;";
//}
sql = "select * from (select rank() over( partition by T_ITEMID order by T_STARTTIME desc ) rk,b.* from (SELECT * FROM T_INFO_ROUTE as i INNER JOIN T_BASE_ITEM as b ON b.T_ITEMID =i.T_NODEID where i.T_DESC ='DJX' and i.T_PARAENTID='" + sbID + "' ) as b) as y where y.rk<=1;";
dt = DBdb2.RunDataTable(sql, out errMsg);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
str += dt.Rows[i]["ID_KEY1"].ToString() + ',';
strName += dt.Rows[i]["T_ITEMDESC"].ToString() + ',';
}
}
return(str + ";" + strName);
}
/// <summary>
/// 根据报表名称获取报表样式
/// </summary>
/// <param name="rptName">报表NAME</param>
/// <returns></returns>
public DataTable GetRptStyleByName(string rptName)
{
var query = "SELECT * FROM T_INFO_SISREPORT WHERE REPORTNAME='" + rptName + "'";
var errMsg = string.Empty;
var dt = new DataTable();
if (dbTypeValue == "SQL")
{
dt = DBsql.RunDataTable(query, out errMsg);
}
else if (dbTypeValue == "ORACLE")
{
dt = OracleHelper.Query(query).Tables[0];
}
else
{
dt = DBdb2.RunDataTable(query, out errMsg);
}
if (errMsg != string.Empty)
{
throw new Exception(errMsg);
}
else
{
return(dt);
}
}
///// <summary>
///// 根据条件获取数据。
///// </summary>
///// <param name="errMsg"></param>
///// <returns></returns>
//public DataTable GetInitByCondition(string companyId, string plantId, string unitId, string beginTime, string endTime, int sCount, int eCount, out int count, out string errMsg)
//{
// this.init();
// errMsg = "";
// count = 0;
// string sql = "SELECT T_CODE,T_UNITDESC,D_CAPABILITY,I_UTH,D_EAF,D_FOF,D_FOR,D_UOF,D_UOR,I_GAAG,I_PH,I_AH,I_SH,I_UOH,I_FOH,I_EUNDH, rownumber() over(order by I.ID_KEY asc ) as rowid FROM T_INFO_UNIT AS I LEFT JOIN T_BASE_UNIT AS B ON I.T_CODE=B.T_UNITID LEFT JOIN T_BASE_PLANT AS P ON P.T_PLANTID=B.T_PLANTID LEFT JOIN T_BASE_COMPANY AS C ON P.T_COMPANYID= C.T_COMPANYID WHERE 1=1";
// if ((!string.IsNullOrEmpty(companyId)) && companyId != "0")
// {
// sql += " AND C.T_COMPANYID='" + companyId + "'";
// }
// if ((!string.IsNullOrEmpty(plantId)) && plantId != "0")
// {
// sql += " AND P.T_PLANTID='" + plantId + "'";
// }
// if ((!string.IsNullOrEmpty(unitId)) && unitId != "0")
// {
// sql += " AND B.T_UNITID='" + unitId + "'";
// }
// if (!string.IsNullOrEmpty(beginTime))
// {
// sql += " AND I.BEGINTIME>='" + beginTime + " 00:00:00.000" + "'";
// }
// if (!string.IsNullOrEmpty(endTime))
// {
// sql += " AND I.ENDTIME<='" + endTime + " 00:00:00.000" + "'";
// }
// string sqlStr = "select * from (" + sql + ") as a where a.rowid between " + sCount + " and " + eCount + "";
// DataTable dt = null;
// if (rlDBType == "SQL")
// {
// // sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
// //dt = DBsql.RunDataTable(sql, out errMsg);
// }
// else
// {
// dt = DBdb2.RunDataTable(sqlStr, out errMsg);
// count = DBdb2.RunDataTable(sql, out errMsg).Rows.Count;
// }
// return dt;
//}
/// <summary>
/// 根据条件获取数据，可靠性分析。
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetInitByCondition(string time, out string errMsg)
{
this.init();
errMsg = "";
//string sql1 = "select min(ID_KEY) as ID_KEY,UGROUP from WebUser group by UGROUP order by ID_KEY";
//string sql = "SELECT I_FOT,I.ID_KEY,T_PLANTDESC,T_UNITDESC,D_CAPABILITY,T_BEGINTIME,T_ENDTIME,I_PH,T_PROFESSIONALDESC,T_REASONDESC, rownumber() over(order by I.ID_KEY asc ) as rowid FROM T_INFO_UNIT AS I LEFT JOIN T_BASE_UNIT AS B ON I.T_CODE=B.T_UNITID LEFT JOIN T_BASE_PLANT AS P ON P.T_PLANTID=B.T_PLANTID LEFT JOIN T_BASE_COMPANY AS C ON P.T_COMPANYID= C.T_COMPANYID LEFT JOIN T_BASE_FAULTPROFESSIONAL AS BF ON I.T_FPROFEESIOID=BF.T_PROFESSIONALID LEFT JOIN T_BASE_FAULTREASON AS BR ON I.T_FREASONID=BR.T_REASONID WHERE 1=1";
string sql = "SELECT SUM(I_FOT) AS fot,SUM(I_FOH) AS foh FROM T_INFO_UNIT WHERE 1=1";
DataTable dt = null;
if (!string.IsNullOrEmpty(time))
{
sql += " AND T_TIME='" + time + "'";
}
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
///根据unitId 从T_BASE_CALCPARA表（趋势分析表）中 获取不同(unitId和paraId)唯一的表名。数据存储在了不同的表中，耗差类型为0或1。
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetTableName(string unitId, string paraId, out string errMsg)
{
this.init();
errMsg = "";
string sql = "select c1.T_OUTTABLE,t.T_UNITID,t.T_PARAID,t.T_UNIT,t.I_TARGETTYPE,t.I_CONSUMETYPE,t.I_ORDER from T_BASE_CONSUMEPARA as t left join T_BASE_CALCPARA as c1 on t.T_UNITID=c1.T_UNITID and t.T_PARAID=c1.T_PARAID where 1=1";
DataTable dt = null;
if (!string.IsNullOrEmpty(unitId))
{
sql += " AND t.T_UNITID='" + unitId + "'";
}
if (!string.IsNullOrEmpty(paraId))
{
sql += " AND t.T_PARAID='" + paraId + "'";
}
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
return(dt);
}
/// <summary>
/// 获取设备和区域关联记录
/// </summary>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetTableDevAndItemInfo(out string errMsg)
{
//sql = "SELECT * FROM T_INFO_ROUTE as i INNER JOIN T_BASE_ITEM as b ON b.T_ITEMID =i.T_NODEID where i.T_DESC ='DJX' ";
sql = "select * from (select rank() over( partition by T_ITEMID order by T_STARTTIME desc ) rk,b.* from (SELECT * FROM T_INFO_ROUTE as i INNER JOIN T_BASE_ITEM as b ON b.T_ITEMID =i.T_NODEID where i.T_DESC ='DJX') as b) as y where y.rk<=1;";
return(DBdb2.RunDataTable(sql, out errMsg));
}
/// <summary>
/// 设备状态分页
/// </summary>
/// <param name="sbID"></param>
/// <param name="sCount"></param>
/// <param name="eCount"></param>
/// <returns></returns>
public DataTable RetTabDevByDevID(string sbID, int sCount, int eCount)
{
sql = "select * from (select b.*,rownumber() over(order by b.T_TIME desc ) as rowid from ( select * from T_BASE_STATUS as s inner join (SELECT i.ID_KEY,b.T_DEVICEID,b.T_DEVICEDESC,i.T_TIME,i.I_STATUS FROM T_INFO_DEVICE as i inner join T_BASE_DEVICE as b on i.T_DEVICEID=b.T_DEVICEID where i.T_DEVICEID='" + sbID + "' order by i.T_TIME desc) as t on s.I_STATUSID=t.I_STATUS) as b )as a where a.rowid BETWEEN " + sCount + " AND " + eCount;
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 区域数据分页查询
/// </summary>
/// <param name="sCount"></param>
/// <param name="eCount"></param>
/// <returns></returns>
public DataTable RetTabAreas(int sCount, int eCount)
{
sql = "select * from (select b.* ,rownumber() over(order by b.id_key asc ) as rowid from (SELECT * FROM ADMINISTRATOR.T_BASE_AREA) as b) as c where c.rowid between " + sCount + " and " + eCount;
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 从T_ORIGINAL_POINT表中得到不同类型条件的测点（实时数据读到关系数据库表T_INFO_VALUE中）
/// </summary>
/// <param name="companyType">电类型（风电，水电...）</param>
/// <param name="type">点所属层次（电厂，机组）</param>
/// <param name="pointType">点类型（负荷，风速，日电量，月电量，年电量）</param>
/// <param name="?"></param>
/// <returns></returns>
public DataTable GetTagByKind(string companyType, string type, string pointType)
{
sql = "select * from Administrator.T_ORIGINAL_POINT where T_COMPANY_TYPE='" + companyType + "' and T_TYPE='" + type + "' and T_POINT_TYPE='" + pointType + "'";
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取区域表信息
/// </summary>
/// <returns></returns>
public DataTable RetTable()
{
sql = "SELECT a.T_AREAID,a.T_AREANAME,r.T_ROUTENAME FROM T_BASE_AREA as a left join T_BASE_ROUTE as r on a.T_ROUTEID =r.T_ROUTEID order by a.T_ROUTEID";
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 循环遍历ParaId,获取该ParaId的平均值（得到所有ParaId的值）
/// </summary>
/// <param name="AllParaId">所有ParaId</param>
/// <param name="errMsg"></param>
/// <returns></returns>
public List <ConsumeInfo> GetInfos(string beginTime, string endTime, out string errMsg)
{
this.init();
errMsg = "";
//counts = 0;
List <ConsumeInfo> cInfoList = new List <ConsumeInfo>();
//获取outtable表
List <ParaTableInfo> infoList = GetInfo(out errMsg);
//获取所有ParaId
//string ParaId = GetParaId(out counts,out errMsg);
if (infoList.Count > 0)
{
foreach (var info in infoList)
{
if (!string.IsNullOrEmpty(info.OutTableName))
{
ConsumeInfo pa = new ConsumeInfo();
string sql = "select avg(D_VALUE) as value from " + info.OutTableName + " where T_PARAID='" + info.ParaId + "' ";
if (!String.IsNullOrEmpty(beginTime) && !String.IsNullOrEmpty(endTime))
{
sql += " and T_DATETIME between '" + beginTime + "' and '" + endTime + "'";
}
else
{
if (!String.IsNullOrEmpty(beginTime))
{
sql += " and T_DATETIME>'" + beginTime + "'";
}
if (!String.IsNullOrEmpty(endTime))
{
sql += " and T_DATETIME<'" + endTime + "'";
}
}
DataTable dt = new DataTable();
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
if (dt != null)
{
pa.Name = info.ParaDesc;
pa.Count = string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? 0 : Convert.ToDouble(dt.Rows[0][0].ToString());
cInfoList.Add(pa);
}
}
}
}
return(cInfoList);
}
/// <summary>
/// 点检项分页ALL
/// </summary>
/// <param name="sbID"></param>
/// <param name="sCount"></param>
/// <param name="eCount"></param>
/// <returns></returns>
public DataTable RetTabItemByDevIDAll(int sCount, int eCount)
{
sql = "select * from (select z.* ,rownumber () over ( order by id_key asc ) as rowid from (select * from (select rank() over(partition by T_ITEMID order by T_STARTTIME desc) rk,b.* from T_BASE_ITEM as b ) as y where y.rk<=1) as z) as f";
sql += " where f.rowid between " + sCount + " and " + eCount;
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取设备数据集
/// </summary>
/// <param name="id">区域编号</param>
/// <param name="sCount">开始条数</param>
/// <param name="eCount">结束条数</param>
/// <returns></returns>
public DataTable RetTabSB(string id, int sCount, int eCount)
{
sql = "select * from (select c.*,rownumber () over (order by c.ik asc) as rowid from (select i.id_key as ik,d.id_key as dk ,d.t_deviceid,d.t_devicedesc,d.t_parentid,d.b_attachment, i.t_nodekey,i.T_nodeid from t_info_route as i inner join t_base_device as d on i.t_nodeid=d.t_deviceid where t_desc='SB' and i.t_paraentid='" + id + "') as c) as z";
sql += " where z.rowid between " + sCount + " and " + eCount;
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取区域数据集
/// </summary>
/// <param name="id">区域编号</param>
/// <param name="sCount">开始条数</param>
/// <param name="eCount">结束条数</param>
/// <returns></returns>
public DataTable RetTabArea(string id, int sCount, int eCount)
{
sql = "select * from (select c.*, rownumber() over(order by c.bk asc )as rowid from ";
sql += "(select b.id_key as bk,i.id_key as ik, i.T_NODEID,i.T_NODEKEY,i.T_PARAENTID,b.T_AREANAME,b.t_areaid,b.t_areacd from T_INFO_ROUTE as i inner join T_BASE_AREA as b on i.T_NODEID=b.T_AREAID where T_PARAENTID ='" + id + "' and i.t_desc='QY') as c) as z";
sql += " where z.rowid between " + sCount + " and " + eCount + "";
dt = DBdb2.RunDataTable(sql, out errMsg);
return(dt);
}
/// <summary>
/// 获取线路Tree
/// </summary>
/// <returns></returns>
public DataTable GetRouteTree()
{
sql = "select r.T_NODEKEY ID,r.T_NODEID IDE,br.T_ROUTENAME NAME,r.T_PARAENTID PARMENTID from T_INFO_ROUTE r left join T_BASE_ROUTE br on r.T_NODEID=br.T_ROUTEID where br.T_ROUTENAME is not null union select r.T_NODEKEY ID,r.T_NODEID IDE,br.T_AREANAME NAME,r.T_PARAENTID PARMENTID from T_INFO_ROUTE r left join T_BASE_AREA br on r.T_NODEID=br.T_AREAID where br.T_AREANAME is not null union select r.T_NODEKEY ID,r.T_NODEID IDE,br.T_DEVICEDESC NAME,r.T_PARAENTID PARMENTID from T_INFO_ROUTE r left join T_BASE_DEVICE br on r.T_NODEID=br.T_DEVICEID where br.T_DEVICEDESC is not null union select r.T_NODEKEY ID,r.T_NODEID IDE,br.T_ITEMDESC NAME,r.T_PARAENTID PARMENTID from T_INFO_ROUTE r left join T_BASE_ITEM br on r.T_NODEID=br.T_ITEMID where br.T_ITEMDESC is not null;";
try
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
catch (Exception ex) { }
return(dt);
}
/// <summary>
/// 获取工期信息
/// </summary>
/// <param name="orgID">组织机构编号</param>
/// <returns></returns>
public IList <Hashtable> GetPeriod(string orgID)
{
IList <Hashtable> list = new List <Hashtable>();
sql = "select T_PERIODID,T_PERIODDESC from Administrator.T_BASE_PERIOD where T_ORGID='" + orgID + "'";
dt = DBdb2.RunDataTable(sql, out errMsg);
list = DataTableToList(dt);
return(list);
}
public DataTable GetOrg(string comID)
{
IList <Hashtable> list = new List <Hashtable>();
sql = "SELECT * FROM ADMINISTRATOR.T_BASE_ORG where T_COMID='" + comID + "'";
dt = DBdb2.RunDataTable(sql, out errMsg);
//list = DataTableToList(dt);
return(dt);
}
/// <summary>
/// 根据时间获取各个类型机组能耗信息
/// </summary>
/// <returns></returns>
public List <UnitConsumeInfo> GetUnitConsumeList(string time, out string errMsg)
{
this.init();
errMsg = "";
List <UnitConsumeInfo> infoList = new List <UnitConsumeInfo>();
string sql = "SELECT * FROM ADMINISTRATOR.T_INFO_UNITCONSUME AS U LEFT JOIN T_INFO_UNIT AS T ON U.T_UNITCODE=T.T_CODE LEFT JOIN T_BASE_UNIT AS S ON S.T_UNITID=U.T_UNITCODE LEFT JOIN T_BASE_PLANT AS B ON B.T_PLANTID=S.T_PLANTID LEFT JOIN T_BASE_COMPANY AS C ON C.T_COMPANYID=B.T_COMPANYID WHERE 1=1";
if (!string.IsNullOrEmpty(time))
{
sql += " AND U.T_TIME = '" + time + "'";
}
DataTable dt = null;
if (rlDBType == "SQL")
{
// sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY";
//dt = DBsql.RunDataTable(sql, out errMsg);
}
else
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
UnitConsumeInfo info = new UnitConsumeInfo();
//info.ParaId = String.IsNullOrEmpty(dt.Rows[i]["T_PARAID"].ToString()) ? String.Empty : dt.Rows[i]["T_PARAID"].ToString();
////
info.T_DWNAME = String.IsNullOrEmpty(dt.Rows[i]["T_DWNAME"].ToString()) ? String.Empty : dt.Rows[i]["T_DWNAME"].ToString();
info.T_PLANTNAME = String.IsNullOrEmpty(dt.Rows[i]["T_PLANTDESC"].ToString()) ? String.Empty : dt.Rows[i]["T_PLANTDESC"].ToString();
info.T_UNITCODE = String.IsNullOrEmpty(dt.Rows[i]["T_UNITCODE"].ToString()) ? String.Empty : dt.Rows[i]["T_UNITCODE"].ToString();
info.T_COUNT = String.IsNullOrEmpty(dt.Rows[i]["T_COUNT"].ToString()) ? String.Empty : dt.Rows[i]["T_COUNT"].ToString();
info.T_USEHOUR = String.IsNullOrEmpty(dt.Rows[i]["T_USEHOUR"].ToString()) ? String.Empty : dt.Rows[i]["T_USEHOUR"].ToString();
info.T_OF = String.IsNullOrEmpty(dt.Rows[i]["T_OF"].ToString()) ? String.Empty : dt.Rows[i]["T_OF"].ToString();
info.T_RDB = String.IsNullOrEmpty(dt.Rows[i]["T_RDB"].ToString()) ? String.Empty : dt.Rows[i]["T_RDB"].ToString();
info.T_CYDL = String.IsNullOrEmpty(dt.Rows[i]["T_CYDL"].ToString()) ? String.Empty : dt.Rows[i]["T_CYDL"].ToString();
info.T_GDMH = String.IsNullOrEmpty(dt.Rows[i]["T_GDMH"].ToString()) ? String.Empty : dt.Rows[i]["T_GDMH"].ToString();
info.T_DBMH = String.IsNullOrEmpty(dt.Rows[i]["T_DBMH"].ToString()) ? String.Empty : dt.Rows[i]["T_DBMH"].ToString();
info.T_GDL = String.IsNullOrEmpty(dt.Rows[i]["T_GDL"].ToString()) ? String.Empty : dt.Rows[i]["T_GDL"].ToString();
info.T_JTPJB = String.IsNullOrEmpty(dt.Rows[i]["T_JTPJB"].ToString()) ? String.Empty : dt.Rows[i]["T_JTPJB"].ToString();
info.T_TYPE = String.IsNullOrEmpty(dt.Rows[i]["T_CAPABILITYLEVEL"].ToString()) ? String.Empty : dt.Rows[i]["T_CAPABILITYLEVEL"].ToString();
///
infoList.Add(info);
}
}
//去掉重复。
//return infoList.Distinct(new EqualCompare<ParaTableInfo>((x, y) => (x != null && y != null) &&(x.OutTableName == y.OutTableName))).ToList();
//不能过滤，因为会把ParaId过滤掉。以ParaId来取值的。
return(infoList);
}
/// <summary>
/// 获取某个人员的所有线路
/// </summary>
/// <param name="userID">人员编号</param>
/// <returns></returns>
public IList <Hashtable> GetRouteTree(string userID)
{
DateHelper dh = new DateHelper();
sql = "select T_ROUTEID,T_ROUTENAME from T_BASE_ROUTE where T_ORGID=( select T_ORGID from T_SYS_MEMBERRELATION where T_USERID='" + userID + "');";
try
{
dt = DBdb2.RunDataTable(sql, out errMsg);
}
catch (Exception ex) { }
return(dh.DataTableToList(dt));
}
