public string GetFieldNameByVariableId(string variableId)
{
string commandText = @"
select b.Formula
from tz_Material as a,material_MaterialDetail as b
where a.KeyID=B.KeyID
and a.OrganizationID=@organizationId
and b.VariableId=@variableName";
VariableParams vp = new VariableParams(variableId);
string fieldName = "";
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = commandText;
command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId));
command.Parameters.Add(new SqlParameter("variableName", vp.VariableName));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read() == true)
{
fieldName = reader[0].ToString().Trim();
}
return(fieldName);
}
}
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="variableId"></param>
/// <param name="startTime"></param>
/// <param name="stopTime"></param>
/// <param name="timeSpanInMin"></param>
/// <returns></returns>
public IDictionary <string, decimal> GetData(string variableId, DateTime startTime, DateTime stopTime, int timeSpanInMin)
{
DataTable dt = new DataTable();
VariableParams vp = new VariableParams(variableId);
// 获取变量对应的列名
string fieldName = GetFieldNameByVariableId(variableId);
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format(COMMAND_FORMAT, fieldName, timeSpanInMin, FACTORY_DATABASE);
command.Parameters.Add(new SqlParameter("startTime", startTime));
command.Parameters.Add(new SqlParameter("stopTime", stopTime));
//command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId));
//command.Parameters.Add(new SqlParameter("variableName", vp.VariableName));
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dt);
}
}
return(Utility.ConvertData(dt));
}
/// <summary>
///
/// </summary>
/// <param name="variableId"></param>
/// <returns>[0]：数据库名 [1]：表格名 [2]：列名</returns>
private string[] GetTableNameAndFieldNameByVariableId(string variableId)
{
string commandFormat = @"
SELECT [TableName], [FieldName]
FROM [zc_nxjc_byc_byf_dcs01].[dbo].[EnergyConsumptionContrast]
WHERE [OrganizationID] = @organizationId AND
[VariableName] = @variableName";
VariableParams vp = new VariableParams(variableId);
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
foreach (var database in DCS_DATABASES)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format(commandFormat, database);
command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId));
command.Parameters.Add(new SqlParameter("variableName", vp.VariableName));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read() == false)
{
continue;
}
// [0]：数据库名 [1]：表格名 [2]：列名
return(new string[] { database, reader[0].ToString().Replace("Realtime", "History"), reader[1].ToString() });
}
}
}
throw new ArgumentException("所提供的VariableID：" + variableId + "在DCS数据库中无对应项，请检查对照表配置是否正确。");
}