/// <summary>
/// Method to get frequency alarms
/// </summary>
public List <FrequencyAlarmInfo> GetFrequencyAlarms()
{
var alarms = new List <FrequencyAlarmInfo>();
var sqlText = String.Format(@"
;WITH Nodes AS
(
SELECT TA.[LscID],TA.[AicID] AS [NodeID],{0} AS [NodeType],TA.[DevID] FROM [dbo].[TM_AIC] TA
UNION ALL
SELECT TD.[LscID],TD.[DicID] AS [NodeID],{1} AS [NodeType],TD.[DevID] FROM [dbo].[TM_DIC] TD
),
Alarms AS
(
SELECT TF.*,N.[DevID],L.[LscName],DT.[TypeID] AS [DevTypeID],DT.[TypeName] AS [DevTypeName]
FROM [dbo].[TA_FrequencyAlarm] TF
INNER JOIN Nodes N ON TF.[LscID] = N.[LscID] AND TF.[NodeType] = N.[NodeType] AND TF.[NodeID] = N.[NodeID]
INNER JOIN [dbo].[TM_DEV] TD ON N.[LscID] = TD.[LscID] AND N.[DevID] = TD.[DevID]
INNER JOIN [dbo].[TM_LSC] L ON TD.[LscID] = L.[LscID]
LEFT OUTER JOIN [dbo].[TC_DeviceType] DT ON TD.[DevTypeID] = DT.[TypeID]
)
SELECT [LscID],[LscName],[Area1Name],[Area2Name],[Area3Name],[StaName],[DevID],[DevName],[NodeID],
[NodeType],[NodeName],[DevTypeID],[DevTypeName],[AlarmLevel],[FreAlarmValue],[FreRightValue],
[StartTime],[AlarmTime],[ConfirmName],[ConfirmTime],[EndName],[EndTime] FROM Alarms;", (Int32)EnmNodeType.Aic, (Int32)EnmNodeType.Dic);
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText, null)) {
while (rdr.Read())
{
var alarm = new FrequencyAlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
alarm.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevID = ComUtility.DBNullInt32Handler(rdr["DevID"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.DevTypeID = ComUtility.DBNullInt32Handler(rdr["DevTypeID"]);
alarm.DevTypeName = ComUtility.DBNullStringHandler(rdr["DevTypeName"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.FreAlarmValue = ComUtility.DBNullInt32Handler(rdr["FreAlarmValue"]);
alarm.FreRightValue = ComUtility.DBNullInt32Handler(rdr["FreRightValue"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.AlarmTime = ComUtility.DBNullDateTimeHandler(rdr["AlarmTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.EndName = ComUtility.DBNullStringHandler(rdr["EndName"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarms.Add(alarm);
}
}
return(alarms);
}
/// <summary>
/// Syn Frequency Alarms
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="connectionString">connectionString</param>
/// <returns>Frequency Alarms</returns>
public List <FrequencyAlarmInfo> SynFrequencyAlarms(int lscId, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int) };
parms[0].Value = lscId;
var alarms = new List <FrequencyAlarmInfo>();
SqlHelper.TestConnection(connectionString);
using (var dt = SqlHelper.ExecuteTable(connectionString, CommandType.Text, SqlText.SQL_SELECT_ALARM_SYNFREQUENCYALARMS, parms)) {
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var alarm = new FrequencyAlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(dr["LscID"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(dr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(dr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(dr["Area3Name"]);
alarm.Area4Name = String.Empty;
alarm.StaName = ComUtility.DBNullStringHandler(dr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(dr["DevName"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(dr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(dr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(dr["NodeName"]);
alarm.AlarmStatus = ComUtility.DBNullInt32Handler(dr["AlarmStatus"]);
alarm.AlarmLevel = ComUtility.DBNullInt32Handler(dr["AlarmLevel"]);
alarm.FreAlarmValue = ComUtility.DBNullInt32Handler(dr["FreAlarmValue"]);
alarm.FreRightValue = ComUtility.DBNullInt32Handler(dr["FreRightValue"]);
alarm.FreCompareValue = ComUtility.DBNullInt32Handler(dr["FreCompareValue"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(dr["StartTime"]);
alarm.AlarmTime = ComUtility.DBNullDateTimeHandler(dr["AlarmTime"]);
alarm.EventTime = ComUtility.DBNullDateTimeHandler(dr["EventTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(dr["ConfirmName"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(dr["ConfirmTime"]);
alarm.EndName = ComUtility.DBNullStringHandler(dr["EndName"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(dr["EndTime"]);
alarm.StartIsAddAlarmList = ComUtility.DBNullBooleanHandler(dr["StartIsAddAlarmList"]);
alarm.EndIsAddAlarmList = ComUtility.DBNullBooleanHandler(dr["EndIsAddAlarmList"]);
alarm.ConfirmIsAddAlarmList = ComUtility.DBNullBooleanHandler(dr["ConfirmIsAddAlarmList"]);
alarms.Add(alarm);
}
SqlHelper.ExecuteBulkCopy(SqlHelper.ConnectionStringLocalTransaction, SqlText.TN_FrequencyAlarm, dt);
}
}
return(alarms);
}
/// <summary>
/// Method to get history frequency alarms
/// </summary>
public List <FrequencyAlarmInfo> GetHisFrequencyAlarms(int lscId, string lscName, string area1Name, string area2Name, string area3Name, string staName, string devName, int nodeId, DateTime beginTime, DateTime endTime, DateTime confirmBeginTime, DateTime confirmEndTime, string confirmName, DateTime endBeginTime, DateTime endEndTime, string endName)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@Area1Name", SqlDbType.NVarChar, 40),
new SqlParameter("@Area2Name", SqlDbType.NVarChar, 40),
new SqlParameter("@Area3Name", SqlDbType.NVarChar, 40),
new SqlParameter("@StaName", SqlDbType.NVarChar, 40),
new SqlParameter("@DevName", SqlDbType.NVarChar, 40),
new SqlParameter("@NodeID", SqlDbType.Int),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@ConfirmBeginTime", SqlDbType.DateTime),
new SqlParameter("@ConfirmEndTime", SqlDbType.DateTime),
new SqlParameter("@ConfirmName", SqlDbType.NVarChar, 20),
new SqlParameter("@EndBeginTime", SqlDbType.DateTime),
new SqlParameter("@EndEndTime", SqlDbType.DateTime),
new SqlParameter("@EndName", SqlDbType.NVarChar, 20) };
parms[0].Value = lscId;
if (area1Name == ComUtility.DefaultString)
{
parms[1].Value = DBNull.Value;
}
else
{
parms[1].Value = area1Name;
}
if (area2Name == ComUtility.DefaultString)
{
parms[2].Value = DBNull.Value;
}
else
{
parms[2].Value = area2Name;
}
if (area3Name == ComUtility.DefaultString)
{
parms[3].Value = DBNull.Value;
}
else
{
parms[3].Value = area3Name;
}
if (staName == ComUtility.DefaultString)
{
parms[4].Value = DBNull.Value;
}
else
{
parms[4].Value = staName;
}
if (devName == ComUtility.DefaultString)
{
parms[5].Value = DBNull.Value;
}
else
{
parms[5].Value = devName;
}
if (nodeId == ComUtility.DefaultInt32)
{
parms[6].Value = DBNull.Value;
}
else
{
parms[6].Value = nodeId;
}
if (beginTime == ComUtility.DefaultDateTime)
{
parms[7].Value = DBNull.Value;
}
else
{
parms[7].Value = beginTime;
}
if (endTime == ComUtility.DefaultDateTime)
{
parms[8].Value = DBNull.Value;
}
else
{
parms[8].Value = endTime;
}
if (confirmBeginTime == ComUtility.DefaultDateTime)
{
parms[9].Value = DBNull.Value;
}
else
{
parms[9].Value = confirmBeginTime;
}
if (confirmEndTime == ComUtility.DefaultDateTime)
{
parms[10].Value = DBNull.Value;
}
else
{
parms[10].Value = confirmEndTime;
}
if (confirmName == ComUtility.DefaultString)
{
parms[11].Value = DBNull.Value;
}
else
{
parms[11].Value = confirmName;
}
if (endBeginTime == ComUtility.DefaultDateTime)
{
parms[12].Value = DBNull.Value;
}
else
{
parms[12].Value = endBeginTime;
}
if (endEndTime == ComUtility.DefaultDateTime)
{
parms[13].Value = DBNull.Value;
}
else
{
parms[13].Value = endEndTime;
}
if (endName == ComUtility.DefaultString)
{
parms[14].Value = DBNull.Value;
}
else
{
parms[14].Value = endName;
}
var dict = new Dictionary <String, Int32>();
var alarms = new List <FrequencyAlarmInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_PREALARM_GETFREHISALARMS, parms)) {
if (rdr.HasRows)
{
dict = GetDevType(lscId, nodeId);
}
while (rdr.Read())
{
var alarm = new FrequencyAlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
alarm.LscName = lscName;
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.FreAlarmValue = ComUtility.DBNullInt32Handler(rdr["FreAlarmValue"]);
alarm.FreRightValue = ComUtility.DBNullInt32Handler(rdr["FreRightValue"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.AlarmTime = ComUtility.DBNullDateTimeHandler(rdr["AlarmTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.EndName = ComUtility.DBNullStringHandler(rdr["EndName"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
var key = String.Format("{0}-{1}", alarm.LscID, ComUtility.GetDevID(alarm.NodeID));
alarm.DevTypeID = dict.ContainsKey(key) ? dict[key] : ComUtility.DefaultInt32;
alarms.Add(alarm);
}
}
return(alarms);
}
