基于WinCC归档数据实现设备停机统计分析

dephixf

已于 2024-08-17 16:43:12 修改

阅读量871

点赞数 16

分类专栏： MOM相关文章标签： c# 制造

于 2024-08-17 16:39:18 首次发布

本文链接：https://blog.csdn.net/bjhtgy/article/details/141282860

版权

MOM相关专栏收录该内容

15 篇文章 0 订阅

订阅专栏

使用WinCC归档数据，主要采集设备运行、停机以及故障信号，针对这些数据对设备进行统计统计分析。

1.连接WinCC归档数据库；

2.使用托盘方式实时监控计算设备停机率；

using System;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace WinccCollection
{
public partial class DataStatics : Form
{
//定义全局变量
string strSqlcon = "SERVER=ip地址;DATABASE=数据库名称;PWD=密码#EDC;UID=用户名;";
string wincccon = @"Provider=WinCCOLEDBProvider.1;Catalog=CC_AUX_SCAD_18_11_29_18_32_16R; Data Source=JD-NMES-PRDSC2\WINCC";
OleDbConnection DBConnection;
OleDbCommand DBCommand;
SqlConnection sqlcon;
SqlCommand sqlcom;
DateTime starttime;
int formlablecount = 0;
string formlabletext = "";
string beingtime = string.Empty;
string frequency = string.Empty;
string filter = string.Empty;
public DataStatics()
{
InitializeComponent();
}

//设置采集环境变量
private bool SetCollectPara()
{
sqlcom = sqlcon.CreateCommand();
sqlcom.CommandText = "select * from AUXMESDB.dbo.WINCC_TbFlag";
DataTable dtflagTemp = new DataTable();
dtflagTemp.Load(sqlcom.ExecuteReader());
bool beingtimesign = false, frequencysign = false, filtersign = false;
for (int i = 0; i < dtflagTemp.Rows.Count; i++)
{
if (dtflagTemp.Rows[i]["Flag"].ToString() == "1")
{
beingtime = dtflagTemp.Rows[i]["FlagTime"].ToString();
beingtimesign = true;
}
else if (dtflagTemp.Rows[i]["Flag"].ToString() == "2")
{
frequency = dtflagTemp.Rows[i]["FlagTime"].ToString();
frequencysign = true;
}
else if (dtflagTemp.Rows[i]["Flag"].ToString() == "3")
{
filter = dtflagTemp.Rows[i]["FlagTime"].ToString();
filtersign = true;
}
}
return (beingtimesign && frequencysign && filtersign);
}

//WinCC数据查询以及计算归档
public void CollectionWincc()
{
SetCollectPara();
try
{
string begintimeTemp = DateTime.Parse(beingtime).AddSeconds(double.Parse(frequency)).ToString("yyyy-MM-dd HH:mm:ss");
sqlcom.CommandText = "select WinccAbbreviation,WinccName from AUXMESDB.dbo.WINCC_CollectionDictionaries where DeleteFlag='0'";
DataTable dtflag = new DataTable();
dtflag.Load(sqlcom.ExecuteReader());
if (dtflag.Rows.Count > 0)
{

for (int i = 0; i < dtflag.Rows.Count; i++)
{
OleDbDataReader DBReader;
try
{
string CommandString = @"TAG:R," + "'" + dtflag.Rows[i]["WinccAbbreviation"] + "\\" + dtflag.Rows[i]["WinccName"] + "'" + ",'" + beingtime + "','" + begintimeTemp + "'";
DBCommand = new System.Data.OleDb.OleDbCommand(CommandString, DBConnection);
DBReader = DBCommand.ExecuteReader();
//结果集获取数值，目前查询结果包括6列，ValueID-变量ID、
//Timestamp -时间戳、TimestampExt-扩展时间长时间戳、VariantValue-变量值、Quality-变量质量、Flags-标识
while (DBReader.Read())
{
string tagid = DBReader["ValueID"].ToString();
string timestamp = DBReader["Timestamp"].ToString();
string value = DBReader["VariantValue"].ToString();
string quality = DBReader["Quality"].ToString();
string flag = DBReader["Flags"].ToString();
string stamp = DateTime.Parse(timestamp).ToString("yyyy-MM-dd");
if (quality == "192")
{
string tempstamp = DateTime.Parse(timestamp).ToString("yyyy-MM-dd");
if (DateTime.Parse(DateTime.Parse(timestamp).ToString("yyyy-MM-dd HH:mm:ss")) < DateTime.Parse(DateTime.Parse(timestamp).ToString("yyyy-MM-dd") + " 08:00:00"))
{
tempstamp = DateTime.Parse(tempstamp).AddDays(-1).ToString("yyyy-MM-dd");
}
if (value == "0")//停机
{
string sql = "INSERT INTO AUXMESDB.[dbo].[WINCC_FileHistory] (WinccName,FileTime,startTime,deptState) values ( '" + dtflag.Rows[i]["WinccName"] + "','" + tempstamp + "','" + timestamp + "','0')";
sqlcom.CommandText = sql;
sqlcom.ExecuteNonQuery();
}

if (value == "1")//开机
{
int tempConut = 0;
string ckupdateSql = "UPDATE [AUXMESDB].[dbo].[WINCC_FileHistory] set endTime='" + stamp + " 07:59:59' , Duration=DATEDIFF( Second, startTime, '" + stamp + " 07:59:59') where endTime is null and WinccName='" + dtflag.Rows[i]["WinccName"] + "' and startTime< '" + stamp + " 07:59:59' and '" + DateTime.Parse(timestamp).ToString("yyyy-MM-dd HH:mm:ss") + "'>'" + stamp + " 07:59:59' ";
sqlcom.CommandText = ckupdateSql;
tempConut = sqlcom.ExecuteNonQuery();
if (tempConut > 0)
{
ckupdateSql = "INSERT INTO AUXMESDB.[dbo].[WINCC_FileHistory] (WinccName,FileTime,startTime,deptState) values ( '" + dtflag.Rows[i]["WinccName"] + "','" + tempstamp + "','" + stamp + " 08:00:00','0')";
sqlcom.CommandText = ckupdateSql;
tempConut = sqlcom.ExecuteNonQuery();
}
string updateSql = "UPDATE AUXMESDB.[dbo].[WINCC_FileHistory] set endTime='" + timestamp + "' ,Duration=DATEDIFF( Second, startTime, '" + timestamp + "') where endTime is null and WinccName='" + dtflag.Rows[i]["WinccName"] + "'";
sqlcom.CommandText = updateSql;
sqlcom.ExecuteNonQuery();

}
}
}
}
catch (Exception ex)
{
string logSql = "INSERT INTO AUXMESDB.[dbo].[WINCC_ErrorLog] ([ErrorContent] ,[ErrorTime] ,[ErrorType]) VALUES ('" + ex.Message.Replace("'", "''") + "','" + DateTime.Now + "','WinccError')";
sqlcom.CommandText = logSql;
sqlcom.ExecuteNonQuery();
continue;
}

DBReader.Close();
}
}
if (DateTime.Parse(begintimeTemp) >= DateTime.Now)
{
sqlcom.CommandText = "update AUXMESDB.dbo.WINCC_TbFlag set FlagTime='" + DateTime.Now + "' where Flag='1'";
}
else
{
sqlcom.CommandText = "update AUXMESDB.dbo.WINCC_TbFlag set FlagTime='" + begintimeTemp + "' where Flag='1'";
}
sqlcom.ExecuteNonQuery();
}
catch(Exception exp)
{
lb_Info.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"--->归档时发生错误:"+exp.Message);
}
}
//Form窗体走马灯定时器
private void timer_Lable_Tick(object sender, EventArgs e)
{
if (formlablecount < 20)
{
formlabletext += " ";
this.Text = formlabletext + "设备停机归档统计服务";
formlablecount += 1;
}
else
{
formlablecount = 0;
formlabletext = "";
}
}

//窗体加载时执行内容
private void DataStatics_Load(object sender, EventArgs e)
{
tsl_Server.Image = image_State.Images[2];
tsl_Server.Text = "开始:" + DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分mm秒");
starttime = DateTime.Now;
timer_Lable.Enabled = true;
CheckConState();
}

//Wincc连接判断
private bool ConnectionWinCC()
{
try
{
//WinccOleDb数据对象创建
DBConnection = new System.Data.OleDb.OleDbConnection(wincccon);
//打开数据库连接
DBConnection.Open();
if (DBConnection.State == ConnectionState.Open)
{
return true;
}
return false;
}
catch (Exception exp)
{
lb_Info.Items.Add("--->WinCC服务器连接失败，错误信息:" + exp.Message);
return false;
}
}

//归档数据库连接判断
private bool ConnectionArchive()
{
try
{
//归档数据对象创建
sqlcon = new SqlConnection(strSqlcon);
//打开数据库连接
sqlcon.Open();
if (sqlcon.State == ConnectionState.Open)
{
return true;
}
return false;
}
catch (Exception exp)
{
lb_Info.Items.Add("--->归档数据库连接失败,错误信息:" + exp.Message);
return false;
}
}

//窗体关闭时执行内容
private void DataStatics_FormClosing(object sender, FormClosingEventArgs e)
{
if (e.CloseReason == CloseReason.UserClosing)
{
e.Cancel = true;
this.WindowState = FormWindowState.Minimized;
this.notify_Main.Visible = true;
this.Hide();
return;
}
}

//托盘图标双击
private void notify_Main_MouseDoubleClick(object sender, MouseEventArgs e)
{
if (this.Visible)
{
this.WindowState = FormWindowState.Minimized;
this.notify_Main.Visible = true;
this.Hide();
}
else
{
this.StartPosition = FormStartPosition.CenterScreen;
this.Visible = true;
this.WindowState = FormWindowState.Normal;
this.Activate();
}
}

//托盘菜单打开
private void tsmi_Open_Click(object sender, EventArgs e)
{
this.StartPosition = FormStartPosition.CenterScreen;
this.WindowState = FormWindowState.Maximized;
this.notify_Main.Visible = true;
this.Show();
}

//托盘菜单退出
private void tsmi_Exit_Click(object sender, EventArgs e)
{
if (MessageBox.Show("退出系统，数据归档会停止!", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
DBConnection.Close();
sqlcon.Close();
this.notify_Main.Visible = false;
this.Close();
this.Dispose();
System.Environment.Exit(System.Environment.ExitCode);
}
}

//数据采集归档
private void timer_Collect_Tick(object sender, EventArgs e)
{
tsl_AllTime.Image = image_State.Images[3];
tsl_AllTime.Text = "累计:" + (DateTime.Now - starttime).TotalHours.ToString("0.00") + "小时";
timer_Collect.Enabled = false;
backgroundWorker.DoWork += new DoWorkEventHandler(bgWork_DoWork);
backgroundWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgWork_RunWorkerCompleted);
if (!backgroundWorker.IsBusy)
{
backgroundWorker.RunWorkerAsync();
timer_Collect.Enabled = true;
}
}

//后台任务调度
private void bgWork_DoWork(object sender, DoWorkEventArgs e)
{
CollectionWincc();
}

//每次循环完成后
private void bgWork_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
lb_Info.Items.Add("--->" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "归档完成");
timer_Collect.Enabled = true;
}

//Wincc服务器状态与归档数据库服务器连接状态检测函数
private void CheckConState()
{
bool winccflag = false, archiveflag = false, envirflag = false;
lb_Info.Items.Add("--->WinCC服务器连接字符串" + wincccon);
lb_Info.Items.Add("--->归档服务器连接字符串" + strSqlcon);
if (ConnectionWinCC())
{
lb_Info.Items.Add("--->WinCC服务器连接成功");
tsl_Wincc.Image = image_State.Images[0];
tsl_Wincc.Text = "WinCC服务器连接成功";
winccflag = true;
}
else
{
lb_Info.Items.Add("--->WinCC服务器连接失败");
tsl_Wincc.Image = image_State.Images[1];
tsl_Wincc.Text = "WinCC服务器连接失败";

}
if (ConnectionArchive())
{
lb_Info.Items.Add("--->归档服务器连接成功");
tsl_Archive.Image = image_State.Images[0];
tsl_Archive.Text = "归档服务器连接成功";
archiveflag = true;
}
else
{
lb_Info.Items.Add("--->归档服务器连接失败");
tsl_Archive.Image = image_State.Images[1];
tsl_Archive.Text = "归档服务器连接失败";
}
if (SetCollectPara())
{
lb_Info.Items.Add("--->环境变量设置成功");
envirflag = true;
}
else
{
lb_Info.Items.Add("--->环境变量设置失败");
}
if (winccflag && archiveflag && envirflag)
{
if (!backgroundWorker.IsBusy)
{
timer_Collect.Enabled = true;
}
timer_State.Enabled = true;
}
else
{
timer_Collect.Enabled = false;
}

}

//Wincc服务器状态与归档数据库服务器连接状态检测，每1小时执行一次
private void timer_State_Tick(object sender, EventArgs e)
{
CheckConState();
}

//wincc服务器与归档数据库状态检测
private void tsmi_Check_Click(object sender, EventArgs e)
{
bool winccflag = false, archiveflag = false;
string msgtext = "";
if (ConnectionWinCC())
{
winccflag = true;
}
if (ConnectionArchive())
{
archiveflag = true;
}
if(winccflag&& archiveflag)
{
msgtext = "状态正常";
}
else
{
if(!winccflag)
{
msgtext += "WinCC服务器连接失败\n";
}
if (!archiveflag)
{
msgtext += "归档服务器连接失败\n";
}
}
MessageBox.Show(msgtext);