private void DecorateTableAmmeterInfo(DataTable sourceTable, string organizationId) { DataColumn columnAmmeterCode = new DataColumn("AmmeterCode", typeof(string)); columnAmmeterCode.DefaultValue = "/"; DataColumn columnCT = new DataColumn("CT", typeof(string)); columnCT.DefaultValue = "/"; DataColumn columnPT = new DataColumn("PT", typeof(string)); columnPT.DefaultValue = "/"; DataColumn columnAmmeterValue = new DataColumn("AmmeterValue", typeof(string)); columnAmmeterValue.DefaultValue = "/"; //sourceTable.Columns.Add(columnAmmeterCode); sourceTable.Columns.Add(columnCT); sourceTable.Columns.Add(columnPT); sourceTable.Columns.Add(columnAmmeterValue); string ammeterDBName = ConnectionStringFactory.GetAmmeterDatabaseName(organizationId); string infoSql = @"select B.*,A.{0}Energy/(B.CT*B.PT) as AmmeterValue from [{1}].[dbo].[RealtimeAmmeter] A,(select C.AmmeterAddress,C.CT,C.PT,C.AmmeterCode,C.ElectricEnergyFieldNameSave from [{1}].[dbo].[AmmeterContrast] C where C.AmmeterNumber='{0}') B"; foreach (DataRow dr in sourceTable.Rows) { //string AmmeterNumber = dr["Name"].ToString().Substring(dr["Name"].ToString().IndexOf('A')).TrimEnd(')'); string AmmeterNumber = dr["AmmeterCode"].ToString().Trim(); if (AmmeterNumber.Contains('A')) { DataTable t_table = _nxjcFactory.Query(string.Format(infoSql, AmmeterNumber, ammeterDBName)); if (t_table.Rows.Count == 1) { dr["AmmeterCode"] = t_table.Rows[0]["AmmeterAddress"].ToString(); dr["CT"] = t_table.Rows[0]["CT"].ToString(); dr["PT"] = t_table.Rows[0]["PT"].ToString(); dr["AmmeterValue"] = decimal.Parse(t_table.Rows[0]["AmmeterValue"].ToString()).ToString("#.00"); } } } }
public static IList <TagInfo> GetTagInfos(string tagId) { IList <TagInfo> tagList = new List <TagInfo>(); /* * idArray[0]:组织机构 * idArray[1]:标签 * idArray[2]:后缀 */ string[] idArray = tagId.Split('>'); if (idArray.Length == 0) { return(tagList); } //数据库连接 ISqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); //根据组织结构取得分厂数据库名 string ammeterDBName = ConnectionStringFactory.GetAmmeterDatabaseName(idArray[0].Trim()); string mySql = @"select * from [{0}].[dbo].[View_DCSContrast] A where A.TagName=@tagName" ; SqlParameter parameter = new SqlParameter("tagName", idArray[1].Trim()); DataTable table = dataFactory.Query(string.Format(mySql, ammeterDBName), parameter); if (table.Rows.Count != 0) { TagInfo tagObj = new TagInfo(table.Rows[0]["Item"].ToString().Trim(), table.Rows[0]["VariableDescription"].ToString().Trim(), table.Rows[0]["DCSName"].ToString().Trim(), table.Rows[0]["IpAddress"].ToString().Trim(), table.Rows[0]["OPCName"].ToString().Trim()); tagList.Add(tagObj); } return(tagList); }
public static DataTable GetElectricityUsageGroupByHour(string organizationId, string levelcode, DateTime startTime, DateTime endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string queryString = @"SELECT YEAR([vDate]) AS [Year], MONTH([vDate]) AS [Month], DAY([vDate]) AS [Day], DATEPART(HOUR, [vDate]) AS [Hour], SUM([FormulaValue]) AS [Sum] FROM [{0}].[dbo].[HistoyFormulaValue] WHERE [OrganizationID] = @organizationId AND [LevelCode] = @levelcode AND [vDate] >= @startTime AND [vDate] <= @endTime GROUP BY YEAR([vDate]), MONTH([vDate]), DAY([vDate]), DATEPART(HOUR, [vDate]) "; SqlParameter[] parameters = new SqlParameter[]{ new SqlParameter("organizationId", organizationId), new SqlParameter("levelcode", levelcode), new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; return dataFactory.Query(string.Format(queryString, ConnectionStringFactory.GetAmmeterDatabaseName(organizationId)), parameters); }
/// <summary> /// /// </summary> /// <param name="organizationId">组织机构</param> /// <param name="tags">模拟量、汉子隐藏、柱状图标签</param> /// <param name="boolSignalTags">开关量相关标签</param> /// <returns></returns> public static IList <string> TagsCheck(string organizationId, string tags, string boolSignalTags) { ISqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); string ammeterDBName = ConnectionStringFactory.GetAmmeterDatabaseName(organizationId); //有问题的标签 IList <string> resultList = new List <string>(); //所有的标签集合 IList <string> tagsList = new List <string>(); #region //处理标签 string[] tempArray = tags.Trim(';').Split(',', ';'); foreach (string item in tempArray) { string[] t_array = item.Split('>'); if (t_array.Length == 3) { tagsList.Add(t_array[1].Trim()); } else { resultList.Add(item); } } string[] boolSignalArray = boolSignalTags.Split(',', ';'); foreach (string item in boolSignalArray) { if ("" != item.Trim()) { if (item.Split('>').Length != 3) { tagsList.Add(item.Trim()); } } } #endregion string mySql = @"select * from [{0}].[dbo].View_DCSContrast as A where " ; StringBuilder sqlBuilder = new StringBuilder(mySql); foreach (string tag in tagsList) { sqlBuilder.Append("TagName='" + tag + "' or "); } sqlBuilder.Remove(sqlBuilder.Length - 4, 4); DataTable tagsTable = dataFactory.Query(string.Format(sqlBuilder.ToString(), ammeterDBName)); IList <string> getTags = new List <string>(); foreach (DataRow dr in tagsTable.Rows) { getTags.Add(dr["TagName"].ToString()); } foreach (string tag in tagsList) { if (!getTags.Contains(tag)) { resultList.Add(tag); } } return(resultList); }
public static IList <TagInfo> GetTagInfos(string tagId, string relatedTags) { IList <TagInfo> tagList = new List <TagInfo>(); /* * idArray[0]:组织机构 * idArray[1]:标签 * idArray[2]:后缀 */ string[] idArray = tagId.Split('>'); if (idArray.Length == 0) { return(tagList); } //数据库连接 ISqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); //根据组织结构取得分厂数据库名 string ammeterDBName = ConnectionStringFactory.GetAmmeterDatabaseName(idArray[0].Trim()); string mySql = @"select * from [{0}].[dbo].[View_DCSContrast] A where {1}" ; StringBuilder parameterStringBuilder = new StringBuilder(); List <SqlParameter> parameterList = new List <SqlParameter>(); string[] tagArray = relatedTags.Split(','); List <string> flag_list = new List <string>(); foreach (string tag in tagArray) { //SqlParameter parameter=new SqlParameter(tag.Trim(),tag.Trim()); if (!flag_list.Contains(tag.Trim())) { flag_list.Add(tag.Trim()); parameterList.Add(new SqlParameter(tag.Trim(), tag.Trim())); parameterStringBuilder.Append("A.TagName=@" + tag.Trim()); parameterStringBuilder.Append(" or "); } } parameterStringBuilder = parameterStringBuilder.Remove(parameterStringBuilder.Length - 4, 4); DataTable table = dataFactory.Query(string.Format(mySql, ammeterDBName, parameterStringBuilder.ToString()), parameterList.ToArray()); //foreach (DataRow row in table.Rows) //{ // TagInfo tagObj = new TagInfo(row["Item"].ToString().Trim(), // row["VariableDescription"].ToString().Trim(), // row["DCSName"].ToString().Trim(), // row["IpAddress"].ToString().Trim(), // row["OPCName"].ToString().Trim()); // tagList.Add(tagObj); //} foreach (string item in tagArray) { DataRow[] rows = table.Select("TagName='" + item.Trim() + "'"); if (rows.Length != 0) { TagInfo tagObj = new TagInfo(rows[0]["Item"].ToString().Trim(), rows[0]["VariableDescription"].ToString().Trim(), rows[0]["DCSName"].ToString().Trim(), rows[0]["IpAddress"].ToString().Trim(), rows[0]["OPCName"].ToString().Trim()); tagList.Add(tagObj); } } return(tagList); }
/// <summary> /// 获取仪表信息 /// </summary> /// <param name="organizationId"></param> /// <param name="tagSet"></param> /// <returns></returns> public static List <GaugesInfo> GetGaugesInfos(string organizationId, string tagSet) { string[] organizationIdName = organizationId.Split(','); List <GaugesInfo> gaugesInfoList = new List <GaugesInfo>(); foreach (string organizationId_new in organizationIdName) { ISqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); string factoryDBName = ConnectionStringFactory.GetAmmeterDatabaseName(organizationId_new); if (tagSet != "") { string[] tagArray = tagSet.Trim(',').Split(',');//去除前导匹配项和尾部匹配项的“,”,再拆分标签 string[] tagEnviornment = tagArray[0].Split('_'); string tagname = tagEnviornment[tagEnviornment.Length - 1]; List <string> tagArrayNew = new List <string>(); if (tagEnviornment[tagEnviornment.Length - 1] == "TenMinutes" || tagEnviornment[tagEnviornment.Length - 1] == "TwentyMinutes" || tagEnviornment[tagEnviornment.Length - 1] == "OneHour") { for (int i = 0; i < tagArray.Length; i++) { string t_tag = tagArray[i]; tagEnviornment = t_tag.Split('_'); StringBuilder tagBulider = new StringBuilder(); for (int j = 0; j < tagEnviornment.Length - 1; j++) { tagBulider.Append(tagEnviornment[j] + "_"); } tagBulider.Remove(tagBulider.Length - 1, 1); string id = tagBulider.ToString(); tagArrayNew.Add(id); } } else { foreach (string t_tag_new in tagArray) { tagArrayNew.Add(t_tag_new); } } string mySql = @"select A.TagName,B.Max as MaxRange,B.Min as MinRange,B.AlarmH as Value_H,B.AlarmHH as Value_HH from {0}.[dbo].[View_DCSContrast] A JOIN [NXJC].[dbo].[realtime_KeyIndicatorsMonitorContrast] B on A.[VariableName]=B.[Tags] where "; StringBuilder sqlBuilder = new StringBuilder(mySql); //sqlBuilder.Append(" where "); List <SqlParameter> parameterList = new List <SqlParameter>(); List <string> tagList = new List <string>(); foreach (string t_tag in tagArrayNew) { if (!tagList.Contains(t_tag.Trim()))//将重复的标签去掉 { //sqlBuilder.Append("DCSName+'_'+TagName=@" + t_tag + " or "); sqlBuilder.Append("A.TagName=@" + t_tag + " or "); parameterList.Add(new SqlParameter(t_tag, t_tag)); tagList.Add(t_tag.Trim()); } } sqlBuilder.Remove(sqlBuilder.Length - 4, 4); //sqlBuilder.Append(")"); DataTable table = dataFactory.Query(string.Format(sqlBuilder.ToString(), factoryDBName), parameterList.ToArray()); if (tagname == "TenMinutes" || tagname == "TwentyMinutes" || tagname == "OneHour") { foreach (DataRow dr in table.Rows) { GaugesInfo gaugesObj = new GaugesInfo(); gaugesObj.TagName = dr["TagName"].ToString().Trim() + "_" + tagname; gaugesObj.OrganiazationName = organizationId_new; //gaugesObj.DCSName = dr["DCSName"].ToString().Trim(); //gaugesObj.TagDescription = dr["TagDescription"].ToString().Trim(); gaugesObj.MaxRange = dr["MaxRange"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["MaxRange"]); gaugesObj.MinRange = dr["MinRange"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["MinRange"]); gaugesObj.Value_HH = dr["Value_HH"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["Value_HH"]); gaugesObj.Value_H = dr["Value_H"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["Value_H"]); //gaugesObj.Value_L = dr["Value_L"] is DBNull ? decimal.MinValue : Convert.ToDecimal(dr["Value_L"]); //gaugesObj.Value_LL = dr["Value_LL"] is DBNull ? decimal.MinValue : Convert.ToDecimal(dr["Value_LL"]); gaugesInfoList.Add(gaugesObj); } } else { foreach (DataRow dr in table.Rows) { GaugesInfo gaugesObj = new GaugesInfo(); gaugesObj.TagName = dr["TagName"].ToString().Trim(); gaugesObj.OrganiazationName = organizationId_new; //gaugesObj.DCSName = dr["DCSName"].ToString().Trim(); //gaugesObj.TagDescription = dr["TagDescription"].ToString().Trim(); gaugesObj.MaxRange = dr["MaxRange"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["MaxRange"]); gaugesObj.MinRange = dr["MinRange"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["MinRange"]); gaugesObj.Value_HH = dr["Value_HH"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["Value_HH"]); gaugesObj.Value_H = dr["Value_H"] is DBNull ? decimal.MaxValue : Convert.ToDecimal(dr["Value_H"]); //gaugesObj.Value_L = dr["Value_L"] is DBNull ? decimal.MinValue : Convert.ToDecimal(dr["Value_L"]); //gaugesObj.Value_LL = dr["Value_LL"] is DBNull ? decimal.MinValue : Convert.ToDecimal(dr["Value_LL"]); gaugesInfoList.Add(gaugesObj); } } } } return(gaugesInfoList); }
public static DataTable GetElectricityUsageGroupByHour(DataTable tagTable, DateTime startTime, DateTime endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string queryString = @"SELECT [OrganizationID], [LevelCode], YEAR([vDate]) AS [Year], MONTH([vDate]) AS [Month], DAY([vDate]) AS [Day], DATEPART(HOUR, [vDate]) AS [Hour], SUM([FormulaValue]) AS [Sum] FROM [{2}].[dbo].[HistoryFormulaValue] WHERE [OrganizationID] = '{0}' AND [LevelCode] = '{1}' AND [vDate] >= @startTime AND [vDate] <= @endTime GROUP BY [OrganizationID], [LevelCode], YEAR([vDate]), MONTH([vDate]), DAY([vDate]), DATEPART(HOUR, [vDate]) "; StringBuilder queryBuilder = new StringBuilder(); foreach (DataRow dr in tagTable.Rows) { queryBuilder.Append(string.Format(queryString, dr["OrganizationID"], dr["LevelCode"], ConnectionStringFactory.GetAmmeterDatabaseName(dr["OrganizationID"].ToString()))); queryBuilder.Append(" UNION "); } queryBuilder.Remove(queryBuilder.Length - 8, 7); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; return(dataFactory.Query(queryBuilder.ToString(), parameters)); }