public List <Abnormal> AbnormalNumber(string plantId, string id)
{
List <Abnormal> AbnormalList = new List <Abnormal>();
// string DBName = BeanTools.getPlantDB(plantId).realTimeDB;
Plant plant = BeanTools.getPlantDB(plantId);
string DBName = plant.realTimeDB;
if (!string.IsNullOrEmpty(id) && DBName.Length > 13)
{
IDao dao = new Dao(plant, false);
string sql = string.Format("select m.AS_Equipment_ID,m.AS_Equipment_FileName,m.AS_Equipment_Name,m.AS_Equipment_State from PSOG_AS_Equipment m,PSOG_ProcessMonitorObject n where n.PSOG_MonitorObject_MSPCModelID='{0}'AND n.PSOG_MonitorObject_Name=m.AS_Equipment_Process AND n.PSOG_MonitorObject_Name=m.AS_Equipment_Process AND m.AS_Equipment_State=-1 ", id);
DataSet ds = dao.executeQuery(sql.ToString());
if (BeanTools.DataSetIsNotNull(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Abnormal node = new Abnormal();
node.AS_Equipment_ID = BeanTools.ObjectToString(dr["AS_Equipment_ID"]);
node.AS_Equipment_Name = BeanTools.ObjectToString(dr["AS_Equipment_Name"]);
node.AS_Equipment_State = BeanTools.ObjectToString(dr["AS_Equipment_State"]);
//node.AS_Equipment_FileName = BeanTools.ObjectToString(dr["AS_Equipment_FileName"]);
AbnormalList.Add(node);
}
}
}
return(AbnormalList);
}
/// <summary>
/// 设备类型查询
/// </summary>
/// <param name="PesFileName"></param>
/// <param name="dao"></param>
/// <returns></returns>
public List <Equipment> loadEquipmentFuntion(Plant plant)
{
IDao dao = new Dao(plant, false);
List <Equipment> list = new List <Equipment>();
String sql = "select * from psog_processmonitorObject t where t.psog_monitorobject_type = '设备' order by id asc";
DataSet ds = dao.executeQuery(sql);
if (BeanTools.DataSetIsNotNull(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Equipment bo = new Equipment();
bo.id = BeanTools.ObjectToString(dr["PSOG_MonitorObject_MSPCModelID"]);
bo.monitorObject_Name = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Name"]);
bo.monitorObject_Type = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Type"]);
bo.monitorObject_Source = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Source"]);
bo.monitorObject_Status = BeanTools.ObjectToString(dr["MonitorObject_CurrStatus"]);
if (CommonStr.monitorObject_Source == bo.monitorObject_Source) //替换为自己的连接
{
string[] url = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Url"]).Split('?');
String parames = 2 <= url.Length ? url[1] : "";
bo.monitorObject_Url = "web_runstate_notiem.aspx?" + parames;
}
else
{
bo.monitorObject_Url = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Url"]);
}
list.Add(bo);
}
}
return(list);
}
public static Equipment getClickProcess(string id, Plant plant, string plantId)
{
Equipment equip = new Equipment();
string DBName = plant.historyDB;
if (!string.IsNullOrEmpty(id) && DBName.Length > 13)
{
IDao dao = new Dao(plant, false);
string sql = string.Format("select * from PSOG_ProcessMonitorObject where PSOG_MonitorObject_MSPCModelID='{0}' ", id);
DataSet ds = dao.executeQuery(sql.ToString());
if (BeanTools.DataSetIsNotNull(ds))
{
DataRow dr = ds.Tables[0].Rows[0];
equip.id = BeanTools.ObjectToString(dr["PSOG_MonitorObject_MSPCModelID"]);
equip.monitorObject_Name = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Name"]);
equip.monitorObject_Type = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Type"]);
equip.monitorObject_Source = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Source"]);
equip.monitorObject_Status = BeanTools.ObjectToString(dr["MonitorObject_CurrStatus"]);
if (CommonStr.monitorObject_Source == equip.monitorObject_Source) //替换为自己的连接
{
string[] url = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Url"]).Split('?');
String parames = 2 <= url.Length ? url[1] : "";
//equip.monitorObject_Url = url[0] + "?" + parames + "&modelName=" + equip.monitorObject_Name;
equip.monitorObject_Url = url[0] + "?" + parames;
}
else
{
equip.monitorObject_Url = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Url"]);
}
}
}
return(equip);
}
/// <summary>
/// 报警次数统计
/// </summary>
/// <param name="dt"></param>
/// <param name="sheet"></param>
/// <param name="row"></param>
/// <param name="datastyle"></param>
/// <param name="datafont"></param>
/// <param name="style"></param>
/// <param name="font"></param>
/// <param name="rowIndex"></param>
public static void fillStatisData(DataTable dt, ISheet sheet, IRow row, ICellStyle datastyle, IFont datafont, ICellStyle style, IFont font, int rowIndex)
{
//设置列宽
sheet.SetColumnWidth(0, 4500);
sheet.SetColumnWidth(1, 4500);
sheet.SetColumnWidth(2, 10000);
//设置行高
sheet.DefaultRowHeight = 350;
//表头数据
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("位号");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("次数");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("描述");
cell2.CellStyle = style;
//填充数据
if (null != dt)
{
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell0 = row.CreateCell(0);
cell0.SetCellValue(BeanTools.ObjectToString(dr["alarmmanager_history_items"]));
cell0.CellStyle = datastyle;
cell1 = row.CreateCell(1);
cell1.SetCellValue(BeanTools.ObjectToString(dr["statisNo"]));
cell1.CellStyle = datastyle;
cell1.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell2 = row.CreateCell(2);
cell2.SetCellValue(BeanTools.ObjectToString(dr["alarmmanager_describe"]));
cell2.CellStyle = datastyle;
}
}
}
/// <summary>
/// 获取工段名跟工段报警数
/// </summary>
/// <param name="DBName"></param>
/// <returns></returns>
public string qryMonitorJson(List <Plant> plants)
{
String monitorJson = "[";
if (null != plants && 1 <= plants.Count)
{
foreach (Plant plant in plants)
{
String monitorStr = "";
monitorJson += "{'plantId':'" + plant.id + "','plantName':'" + plant.organtreeName + "','monitorList':[";
String sql = "with tt as (select max(g.Time) recordtime from RTResEx_FDPCA g) ";
sql += "select m.PSOG_MonitorObject_Name,isnull(alarmNum,0) alarmNum,isnull(FDPCA_T2RealValue,0) FDPCA_T2RealValue from ( ";
sql += "select sum(case when FDPCA_TagDCSAlarmFlag <> 0 and FDPCA_TagDCSAlarmFlag is not null then 1 else 0 end) alarmNum, ";
sql += "ModelID,ModelName, max(FDPCA_T2RealValue) FDPCA_T2RealValue ";
sql += "from RTResEx_FDPCA t,tt ";
sql += "where t.Time = tt.recordtime ";
sql += "group by ModelID,ModelName ) alarm right join PSOG_ProcessMonitorObject m ";
sql += "on m.PSOG_MonitorObject_Name = ModelName ";
IDao dao = new Dao(plant, true);
DataSet ds = dao.executeQuery(sql);
if (BeanTools.DataSetIsNotNull(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
String monitorName = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Name"]);
String alarmNum = BeanTools.ObjectToString(dr["alarmNum"]);
double realValue = Convert.ToDouble(BeanTools.ObjectToString(dr["FDPCA_T2RealValue"]));
monitorStr += "{'monitorName':'" + monitorName + "','alarmNum':'" + alarmNum + "','realValue':" + realValue + "},";
}
}
monitorStr = "" == monitorStr ? "" : monitorStr.Substring(0, monitorStr.Length - 1) + "]},"; //去除最后的，
monitorJson += monitorStr;
}
if (monitorJson.EndsWith(","))
{
monitorJson = monitorJson.Substring(0, monitorJson.Length - 1);
}
monitorJson += "]";
}
return(monitorJson);
}
public static string getEquipName(Plant plant, string url)
{
// Equipment equip = new Equipment();
string monitorObject_Name = "";
string DBName = plant.historyDB;
if (DBName.Length > 13)
{
IDao dao = new Dao(plant, false);
string sql = string.Format("select * from PSOG_ProcessMonitorObject where PSOG_MonitorObject_Url like '{0}' ", url);
DataSet ds = dao.executeQuery(sql.ToString());
if (BeanTools.DataSetIsNotNull(ds))
{
DataRow dr = ds.Tables[0].Rows[0];
monitorObject_Name = BeanTools.ObjectToString(dr["PSOG_MonitorObject_Name"]);
}
}
return(monitorObject_Name);
}
public static void fillcompToData(SqlDataReader dr, ISheet sheet, IRow row, ICellStyle datastyle, IFont datafont, ICellStyle style, IFont font, int rowIndex)
{
//设置列宽
sheet.SetColumnWidth(0, 5500);
sheet.SetColumnWidth(1, 5500);
sheet.SetColumnWidth(2, 3700);
sheet.SetColumnWidth(3, 5000);
sheet.SetColumnWidth(4, 9000);
//设置行高
sheet.DefaultRowHeight = 350;
//表头数据
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("开始时间");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("结束时间");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("报警个数");
cell2.CellStyle = style;
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("10Min平均报警率");
cell3.CellStyle = style;
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("报警位号");
cell4.CellStyle = style;
//填充数据
if (null != dr)
{
while (dr.Read())
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell0 = row.CreateCell(0);
cell0.SetCellValue(BeanTools.DataTimeToString(dr["ZHStartTime"]));
cell0.CellStyle = datastyle;
cell1 = row.CreateCell(1);
cell1.SetCellValue(BeanTools.DataTimeToString(dr["ZHEndTime"]));
cell1.CellStyle = datastyle;
cell2 = row.CreateCell(2);
cell2.SetCellValue(BeanTools.ObjectToString(dr["ZHErrorCount"]));
cell2.CellStyle = datastyle;
cell2.CellStyle.Alignment = HorizontalAlignment.RIGHT;
cell3 = row.CreateCell(3);
cell3.SetCellValue(Math.Round(BeanTools.ObjectToDouble(dr["ZHPercent"]), 7));
cell3.CellStyle = datastyle;
cell3.CellStyle.Alignment = HorizontalAlignment.RIGHT;
string startTime = BeanTools.ObjectToString(dr["ZHAlarmItems"]);
cell4 = row.CreateCell(4);
cell4.SetCellValue(startTime);
cell4.CellStyle = datastyle;
cell3.CellStyle.Alignment = HorizontalAlignment.LEFT;
}
}
}
public static void fillStableRateData(SqlDataReader dr, ISheet sheet, IRow row, ICellStyle datastyle, IFont datafont, ICellStyle style, IFont font, int rowIndex)
{
//设置列宽
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 8000);
sheet.SetColumnWidth(2, 2600);
sheet.SetColumnWidth(3, 3200);
sheet.SetColumnWidth(4, 3200);
//sheet.SetColumnWidth(5, 3200);
//sheet.SetColumnWidth(6, 3200);
sheet.SetColumnWidth(5, 3200);
sheet.SetColumnWidth(6, 3200);
sheet.SetColumnWidth(7, 3200);
sheet.SetColumnWidth(8, 4200);
sheet.SetColumnWidth(9, 4200);
sheet.SetColumnWidth(10, 4200);
sheet.SetColumnWidth(11, 4200);
sheet.SetColumnWidth(12, 3800);
//设置行高
sheet.DefaultRowHeight = 350;
//表头数据
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("位号");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("描述");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("类型");
cell2.CellStyle = style;
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("工艺上限");
cell3.CellStyle = style;
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("工艺下限");
cell4.CellStyle = style;
//ICell cell5 = row.CreateCell(5);
//cell5.SetCellValue("上限USL");
//cell5.CellStyle = style;
//ICell cell6 = row.CreateCell(6);
//cell6.SetCellValue("下限LSL");
//cell6.CellStyle = style;
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("数据总数");
cell5.CellStyle = style;
ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("超工艺阈值数");
cell6.CellStyle = style;
ICell cell7 = row.CreateCell(7);
cell7.SetCellValue("合格率");
cell7.CellStyle = style;
ICell cell8 = row.CreateCell(8);
cell8.SetCellValue("准确度Ca");
cell8.CellStyle = style;
ICell cell9 = row.CreateCell(9);
cell9.SetCellValue("精密度Cp");
cell9.CellStyle = style;
ICell cell10 = row.CreateCell(10);
cell10.SetCellValue("操作质量指数");
cell10.CellStyle = style;
//填充数据
if (null != dr)
{
while (dr.Read())
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell0 = row.CreateCell(0);
cell0.SetCellValue(BeanTools.ObjectToString(dr["insItems"]));
cell0.CellStyle = datastyle;
cell1 = row.CreateCell(1);
cell1.SetCellValue(BeanTools.ObjectToString(dr["insDescribe"]));
cell1.CellStyle = datastyle;
cell1.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell2 = row.CreateCell(2);
cell2.SetCellValue(BeanTools.ObjectToString(dr["insAlarmClass"]));
cell2.CellStyle = datastyle;
cell3 = row.CreateCell(3);
cell3.SetCellValue(BeanTools.ObjectToString(dr["insTechnicsH"]));
cell3.CellStyle = datastyle;
cell3.CellStyle.Alignment = HorizontalAlignment.LEFT;
string insTechnicsH = BeanTools.ObjectToString(dr["insTechnicsL"]);
cell4 = row.CreateCell(4);
cell4.SetCellValue(insTechnicsH);
cell4.CellStyle = datastyle;
//cell5 = row.CreateCell(5);
//cell5.SetCellValue(BeanTools.ObjectToString(dr["insCpkUSL"]));
//cell5.CellStyle = datastyle;
//cell5.CellStyle.Alignment = HorizontalAlignment.LEFT;
//cell6 = row.CreateCell(6);
//cell6.SetCellValue(BeanTools.ObjectToString(dr["insCpkLSL"]));
//cell6.CellStyle = datastyle;
cell5 = row.CreateCell(5);
cell5.SetCellValue(BeanTools.ObjectToString(dr["insDataCount"]));
cell5.CellStyle = datastyle;
cell6 = row.CreateCell(6);
cell6.SetCellValue(BeanTools.ObjectToString(dr["insErrorDataCount"]));
cell6.CellStyle = datastyle;
cell6.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell7 = row.CreateCell(7);
cell7.SetCellValue(BeanTools.ObjectToString(dr["insPercent"]));
cell7.CellStyle = datastyle;
cell8 = row.CreateCell(8);
cell8.SetCellValue(BeanTools.ObjectToString(dr["insCpkCa"]));
cell8.CellStyle = datastyle;
cell8.CellStyle.Alignment = HorizontalAlignment.LEFT;
string insCpkCa = BeanTools.ObjectToString(dr["insCpkCp"]);
cell9 = row.CreateCell(9);
cell9.SetCellValue(insCpkCa);
cell9.CellStyle = datastyle;
cell10 = row.CreateCell(10);
cell10.SetCellValue(BeanTools.ObjectToString(dr["insCpk"]));
cell10.CellStyle = datastyle;
cell10.CellStyle.Alignment = HorizontalAlignment.LEFT;
}
}
}
/// <summary>
/// 报警历史统计
/// </summary>
/// <param name="dt"></param>
/// <param name="sheet"></param>
/// <param name="row"></param>
/// <param name="datastyle"></param>
/// <param name="datafont"></param>
/// <param name="style"></param>
/// <param name="font"></param>
/// <param name="rowIndex"></param>
public static void fillHisData(DataTable dt, ISheet sheet, IRow row, ICellStyle datastyle, IFont datafont, ICellStyle style, IFont font, int rowIndex)
{
//设置列宽
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 8000);
sheet.SetColumnWidth(2, 2600);
sheet.SetColumnWidth(3, 2000);
sheet.SetColumnWidth(4, 6000);
sheet.SetColumnWidth(5, 5500);
sheet.SetColumnWidth(6, 5500);
//设置行高
sheet.DefaultRowHeight = 350;
//表头数据
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("位号");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("描述");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("状态");
cell2.CellStyle = style;
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("分级");
cell3.CellStyle = style;
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("持续时间");
cell4.CellStyle = style;
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("开始时间");
cell5.CellStyle = style;
ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("结束时间");
cell6.CellStyle = style;
//填充数据
if (null != dt)
{
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell0 = row.CreateCell(0);
cell0.SetCellValue(BeanTools.ObjectToString(dr["AlarmManager_History_Items"]));
cell0.CellStyle = datastyle;
cell1 = row.CreateCell(1);
cell1.SetCellValue(BeanTools.ObjectToString(dr["AlarmManager_History_Describe"]));
cell1.CellStyle = datastyle;
cell1.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell2 = row.CreateCell(2);
cell2.SetCellValue(BeanTools.ObjectToString(dr["AlarmManager_History_State"]));
cell2.CellStyle = datastyle;
cell3 = row.CreateCell(3);
cell3.SetCellValue(BeanTools.ObjectToString(dr["AlarmManager_History_AlarmClass"]));
cell3.CellStyle = datastyle;
cell3.CellStyle.Alignment = HorizontalAlignment.LEFT;
string startTime = BeanTools.ObjectToString(dr["AlarmManager_History_StartTime"]);
string endTime = BeanTools.ObjectToString(dr["AlarmManager_History_EndTime"]);
long duration = (string.IsNullOrEmpty(endTime) ? DateTime.Now.Ticks : DateTime.Parse(endTime).Ticks - DateTime.Parse(startTime).Ticks) / 555000000;
cell4 = row.CreateCell(4);
cell4.SetCellValue(duration);
cell4.CellStyle = datastyle;
cell5 = row.CreateCell(5);
cell5.SetCellValue(BeanTools.DataTimeToString(dr["AlarmManager_History_StartTime"]));
cell5.CellStyle = datastyle;
cell5.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell6 = row.CreateCell(6);
cell6.SetCellValue(BeanTools.DataTimeToString(dr["AlarmManager_History_EndTime"]));
cell6.CellStyle = datastyle;
}
}
}