public static DataTable GetAmeterDataTable(string organizationId, string meterNumber, string startTime, string endTime)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string meterDbName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string sql = @" USE [{0}]
select B.vDate,A.AmmeterName,A.AmmeterNumber,A.CT,A.PT, C.{1} as [Current],CA.{1} as [CurrentA],
CB.{1} as [CurrentB],CC.{1} as [CurrentC],
VA.{1} as [VoltageA], VB.{1} as [VoltageB], VC.{1} as [VoltageC],
PF.{1} as [PF],PFA.{1} as [PFA],PFB.{1} as [PFB],PFC.{1} as [PFC],
B.{1}Energy as Energy,B.{1}Power as Power
from [dbo].[AmmeterContrast] A,[dbo].[HistoryAmmeter] B,
[dbo].[History_Current] C,[dbo].[History_CurrentA] CA,[dbo].[History_CurrentB] CB,[dbo].[History_CurrentC] CC,
[dbo].[History_PF] PF,[dbo].[History_PF] PFA,[dbo].[History_PF] PFB,[dbo].[History_PF] PFC,
[dbo].[History_VoltageA] VA,[dbo].[History_VoltageB] VB,[dbo].[History_VoltageC] VC
where A.EnabledFlag=1 and A.AmmeterNumber='{1}'
and B.vDate>=@startTime and B.vDate<=@endTime
and B.vDate=C.vDate and B.vDate=CA.vDate and B.vDate=CB.vDate and B.vDate=CC.vDate
and B.vDate=PF.vDate and B.vDate=PFA.vDate and B.vDate=PFB.vDate and B.vDate=PFC.vDate
and B.vDate=VA.vDate and B.vDate=VB.vDate and B.vDate=VC.vDate
order by vDate ";
DataTable EmeterListTable = new DataTable();
string mySql = string.Format(sql, meterDbName, meterNumber);
SqlParameter[] parameter = { new SqlParameter("@startTime", startTime), new SqlParameter("@endTime", endTime) };
EmeterListTable = _dataFactory.Query(mySql, parameter);
return(EmeterListTable);
}
public static DataTable GetEmeterList(string organizationId, string eRoomName)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string meterDbName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string sql = @" select AmmeterName,AmmeterNumber from [{0}].[dbo].[AmmeterContrast] {1}
order by AmmeterNumber";
DataTable EmeterListTable = new DataTable();
EmeterListTable = _dataFactory.Query(string.Format(sql, meterDbName, "where EnabledFlag=1 and ElectricRoom='" + eRoomName + "'"));
return(EmeterListTable);
}
public static DataTable GetEDRoomList(string organizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string meterDbName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string sql = @"select A.ElectricRoom AS ElectricRoom, B.ElectricRoomName AS ElectricRoomName
from (SELECT DISTINCT ElectricRoom FROM [{0}].[dbo].[AmmeterContrast] {1}) A
left join [{0}].[dbo].[ElectricRoomContrast] B on A.ElectricRoom=B.ElectricRoom
order by B.DisplayIndex" ;
DataTable EDRoomListTable = new DataTable();
EDRoomListTable = _dataFactory.Query(string.Format(sql, meterDbName, "where EnabledFlag=1 "));
return(EDRoomListTable);
}
public static DataTable GetRealtimeData(string organizationId, string electricRoomName, string levelType)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string meterDbName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string sql = @"select A.AmmeterNumber, A.AmmeterName,A.ElectricRoom, A.CT, A.PT,A.Status,A.TimeStatusChange, A.AmmeterCode,B.ElectricRoomName,B.DisplayIndex
from [{0}].[dbo].AmmeterContrast A
left join [{0}].[dbo].ElectricRoomContrast B on A.ElectricRoom=B.ElectricRoom {1}
order by B.DisplayIndex,A.AmmeterNumber";
DataTable mainTable = new DataTable();
if ("ElectricRoom" == levelType)
{
mainTable = _dataFactory.Query(string.Format(sql, meterDbName, "where A.EnabledFlag=1 and A.ElectricRoom=(select C.ElectricRoom from [" + meterDbName + "].[dbo].ElectricRoomContrast C where C.ElectricRoomName= '" + electricRoomName + "')"));
}
else
{
mainTable = _dataFactory.Query(string.Format(sql, meterDbName, "where A.EnabledFlag=1"));
}
string[] array = new string[] { "Current", "CurrentA", "CurrentB", "CurrentC", "PF", "PFA", "PFB", "PFC",
"VoltageA", "VoltageB", "VoltageC" };
List <DataTable> list = new List <DataTable>();
Dictionary <string, DataTable> dictionary = new Dictionary <string, DataTable>();//存储电流电压实时值
//增加电流电压等信息
foreach (string item in array)
{
mainTable.Columns.Add(item, typeof(decimal));
try
{
dictionary.Add(item, _dataFactory.Query(string.Format("select * from [{0}].[dbo].Realtime_{1}", meterDbName, item)));
}
catch { };
}
//电能，功率特殊处理
mainTable.Columns.Add("Energy", typeof(decimal));
mainTable.Columns.Add("Power", typeof(decimal));
dictionary.Add("EnergyPower", _dataFactory.Query(string.Format("select * from [{0}].[dbo].RealtimeAmmeter", meterDbName)));
//将电流电压等信息添加到mainTable表中
foreach (DataRow dr in mainTable.Rows)
{
string ammeterNum = dr["AmmeterNumber"].ToString().Trim();
foreach (string key in dictionary.Keys.ToArray())
{
if (dictionary[key].Columns.Contains(ammeterNum))
{
dr[key] = dictionary[key].Rows[0][ammeterNum];
}
}
//电能功率特殊处理
if (dictionary["EnergyPower"].Columns.Contains(ammeterNum + "Energy"))//电能
{
dr["Energy"] = dictionary["EnergyPower"].Rows[0][ammeterNum + "Energy"];
}
if (dictionary["EnergyPower"].Columns.Contains(ammeterNum + "Power"))//电压
{
dr["Power"] = dictionary["EnergyPower"].Rows[0][ammeterNum + "Power"];
}
}
return(mainTable);
}
