在实施MES项目中,比如产量统计、质量数据追溯、SPC统计等业务,不可避免需要使用OT层设备历史或者实时数据,如果恰好数据采集和存储使用了西门子WinCC,本文主要介绍如何使用WinCC数据采集功能和历史数据归档存储能力为MES系统提供数据支持。
1.前置条件:需要使用WinCC的连通性数据包(Connectivity Pack),需要安装服务端和客户端,Connectivity Pack提供实时和历史归档数据的查询SDK;
2.使用SDK包,需要自己写代码,支持VB、C#等语言;
3.以下一个实际项目中使用的部分代码,实现设备运行和故障历史数据统计;
项目代码如下:
(1)如何连接WinCC历史数据库
//需要查询变量数据字典业务数据库连接
string strSqlcon = "SERVER=【wincc数据库IP地址】;DATABASE=数据库;PWD=密码;UID=用户;";
//WinCC归档库连接
string conn = @"Provider=WinCCOLEDBProvider.1;Catalog=【归档数据库名称】; Data Source=主机名称\WINCC";
//WinccOleDb数据对象创建
using (OleDbConnection DBConnection = new System.Data.OleDb.OleDbConnection(conn))
{
//打开数据库连接DBConnection.Open();
if (DBConnection.State == ConnectionState.Open)}
(2)WinCC变量名称SQL查询
using (SqlConnection sqlcon = new SqlConnection(strSqlcon))
{
sqlcon.Open();
SqlCommand sqlcom = sqlcon.CreateCommand();
sqlcom.CommandText = "select * from MESDB.dbo.WINCC_TbFlag";
DataTable dtflagTemp = new DataTable();
dtflagTemp.Load(sqlcom.ExecuteReader());
string beingtime = string.Empty;
string frequency = string.Empty;
string filter = string.Empty;
for (int i = 0; i < dtflagTemp.Rows.Count; i++)
{
if (dtflagTemp.Rows[i]["Flag"].ToString() == "1")
{
beingtime = dtflagTemp.Rows[i]["FlagTime"].ToString();
}
else if (dtflagTemp.Rows[i]["Flag"].ToString() == "2")
{
frequency = dtflagTemp.Rows[i]["FlagTime"].ToString();
}
else if (dtflagTemp.Rows[i]["Flag"].ToString() == "3")
{
filter = dtflagTemp.Rows[i]["FlagTime"].ToString();
}
}}
(3)变量对应WinCC归档数据查询
//Wincc OLEDB 查询语法
string CommandStringTemp = @"TAG:R,";
string CommandString = string.Empty;
string begintimeTemp = DateTime.Parse(beingtime).AddSeconds(double.Parse(frequency)).ToString("yyyy-MM-dd HH:mm:ss");
sqlcom.CommandText = "select WinccAbbreviation,WinccName from MESDB.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
{CommandString = CommandStringTemp + "'" + dtflag.Rows[i]["WinccAbbreviation"] + "\\" + dtflag.Rows[i]["WinccName"] + "'" + ",'" + beingtime + "','" + begintimeTemp + "'";
OleDbCommand 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");
}
}
}
(4)依据WinCC变量返回的数据可用性,使用数据完成统计
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 MESDB.[dbo].[WINCC_FileHistory] (WinccName,FileTime,startTime,deptState) values ( '" + dtflag.Rows[i]["WinccName"] + "','" + tempstamp + "','" + timestamp + "','0')";
// tempTime = timestamp;
sqlcom.CommandText = sql;
sqlcom.ExecuteNonQuery();
}if (value == "1")//开机
{
int tempConut = 0;string ckupdateSql = "UPDATE [MESDB].[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 [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 MESDB.[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();
}
}}
}
通过上述代码即可实现与WinCC的实时和历史数据交互,无需针对MES系统实施单独数据采集和历史归档系统,沿用既有的SCADA系统实现数据使用。