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