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)); }