/// <summary>
/// Method to gets lsc project
/// </summary>
/// <param name="projectId">projectId</param>
/// <param name="connectionString">connectionString</param>
/// <returns>project object</returns>
public ProjectInfo GetProject(int lscId, string lscName, string projectId, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@ProjectId", SqlDbType.VarChar, 50) };
parms[0].Value = projectId;
ProjectInfo project = null;
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Appointment_Get_Project, parms)) {
if (rdr.Read())
{
project = new ProjectInfo();
project.LscID = lscId;
project.LscName = lscName;
project.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
project.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
project.BeginTime = ComUtility.DBNullDateTimeHandler(rdr["BeginTime"]);
project.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
project.Responsible = ComUtility.DBNullStringHandler(rdr["Responsible"]);
project.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
project.Company = ComUtility.DBNullStringHandler(rdr["Company"]);
project.Comment = ComUtility.DBNullStringHandler(rdr["Comment"]);
project.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
}
}
return(project);
}
/// <summary>
/// Method to get calendar events
/// </summary>
/// <param name="uId">uId</param>
public List <CalendarEventInfo> GetCalendarEvents(string uId)
{
try {
SqlParameter[] parms = { new SqlParameter("@UID", SqlDbType.VarChar, 50) };
parms[0].Value = uId;
var calEvents = new List <CalendarEventInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_CALENDAR_GETCALENDAREVENTS, parms)) {
while (rdr.Read())
{
var calEvent = new CalendarEventInfo();
calEvent.EventID = ComUtility.DBNullInt32Handler(rdr["EventID"]);
calEvent.CalendarID = ComUtility.DBNullInt32Handler(rdr["CalendarID"]);
calEvent.Title = ComUtility.DBNullStringHandler(rdr["Title"]);
calEvent.StartDate = ComUtility.DBNullDateTimeHandler(rdr["StartDate"]);
calEvent.EndDate = ComUtility.DBNullDateTimeHandler(rdr["EndDate"]);
calEvent.IsAllDay = ComUtility.DBNullNullableBooleanHandler(rdr["IsAllDay"]);
calEvent.Notes = ComUtility.DBNullStringHandler(rdr["Notes"]);
calEvent.Location = ComUtility.DBNullStringHandler(rdr["Location"]);
calEvent.Reminder = ComUtility.DBNullStringHandler(rdr["Reminder"]);
calEvent.Url = ComUtility.DBNullStringHandler(rdr["Url"]);
calEvent.IsNew = ComUtility.DBNullNullableBooleanHandler(rdr["IsNew"]);
calEvent.UID = ComUtility.DBNullStringHandler(rdr["UID"]);
calEvent.UpdateTime = ComUtility.DBNullDateTimeHandler(rdr["UpdateTime"]);
calEvents.Add(calEvent);
}
}
return(calEvents);
} catch { throw; }
}
/// <summary>
/// Gets an appointment
/// </summary>
/// <param name="lscId"></param>
/// <param name="lscName"></param>
/// <param name="id"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public AppointmentInfo GetAppointment(int lscId, string lscName, int id, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@Id", id) };
AppointmentInfo appointment = null;
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Appointment_Get, parms)) {
if (rdr.Read())
{
appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
}
}
return(appointment);
}
/// <summary>
/// Gets users from the lsc database
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="lscName">lscName</param>
/// <param name="userId">userId</param>
/// <param name="connectionString">connectionString</param>
/// <returns></returns>
public List <LscUserInfo> GetUsers(int lscId, string lscName, int?userId, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@UserId", SqlDbType.Int) };
parms[0].Value = DBNull.Value;
if (userId.HasValue)
{
parms[0].Value = userId.Value;
}
var lscUsers = new List <LscUserInfo>();
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Lsc_Get_Users, parms)) {
while (rdr.Read())
{
var lscUser = new LscUserInfo();
lscUser.LscID = lscId;
lscUser.LscName = lscName;
lscUser.UserID = ComUtility.DBNullInt32Handler(rdr["UserID"]);
lscUser.UserName = ComUtility.DBNullStringHandler(rdr["UserName"]);
lscUser.UID = ComUtility.DBNullStringHandler(rdr["UID"]);
lscUser.OpLevel = ComUtility.DBNullUserLevelHandler(rdr["OpLevel"]);
lscUser.LimitTime = ComUtility.DBNullDateTimeHandler(rdr["LimitTime"]);
lscUser.TelePhone = ComUtility.DBNullStringHandler(rdr["TelePhone"]);
lscUser.MobilePhone = ComUtility.DBNullStringHandler(rdr["MobilePhone"]);
lscUser.Email = ComUtility.DBNullStringHandler(rdr["Email"]);
lscUser.Address = ComUtility.DBNullStringHandler(rdr["Address"]);
lscUser.PostalCode = ComUtility.DBNullStringHandler(rdr["PostalCode"]);
lscUser.Remark = ComUtility.DBNullStringHandler(rdr["Remark"]);
lscUser.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
lscUsers.Add(lscUser);
}
}
return(lscUsers);
}
/// <summary>
/// Method to gets lsc project
/// </summary>
/// <param name="projectId">projectId</param>
/// <param name="connectionString">connectionString</param>
/// <returns>project object</returns>
public List <ProjectInfo> GetProjectItem(int lscId, string lscName, string connectionString)
{
var projects = new List <ProjectInfo>();
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Appointment_Get_ProjectItem, null)) {
while (rdr.Read())
{
var project = new ProjectInfo();
project.LscID = lscId;
project.LscName = lscName;
project.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
project.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
project.BeginTime = ComUtility.DBNullDateTimeHandler(rdr["BeginTime"]);
project.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
project.Responsible = ComUtility.DBNullStringHandler(rdr["Responsible"]);
project.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
project.Company = ComUtility.DBNullStringHandler(rdr["Company"]);
project.Comment = ComUtility.DBNullStringHandler(rdr["Comment"]);
project.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
projects.Add(project);
}
}
return(projects);
}
/// <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>
/// Method to get trend alarms
/// </summary>
public List <TrendAlarmInfo> GetTrendAlarms()
{
var alarms = new List <TrendAlarmInfo>();
var sqlText = @"
;WITH Alarms AS
(
SELECT TT.*,TA.[DevID],L.[LscName],DT.[TypeID] AS DevTypeID,DT.[TypeName] AS DevTypeName
FROM [dbo].[TA_TrendAlarm] TT
INNER JOIN [dbo].[TM_AIC] TA ON TT.[LscID] = TA.[LscID] AND TT.[NodeID] = TA.[AicID]
INNER JOIN [dbo].[TM_DEV] TD ON TA.[LscID] = TD.[LscID] AND TA.[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],
[NodeName],[DevTypeID],[DevTypeName],[AlarmType],[AlarmLevel],[StartValue],[AlarmValue],[DiffValue],
[StartTime],[AlarmTime],[ConfirmName],[ConfirmTime],[EndName],[EndTime] FROM Alarms;";
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText, null)) {
while (rdr.Read())
{
var alarm = new TrendAlarmInfo();
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.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.DevTypeID = ComUtility.DBNullInt32Handler(rdr["DevTypeID"]);
alarm.DevTypeName = ComUtility.DBNullStringHandler(rdr["DevTypeName"]);
alarm.AlarmType = ComUtility.DBNullStringHandler(rdr["AlarmType"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.StartValue = ComUtility.DBNullFloatHandler(rdr["StartValue"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.DiffValue = ComUtility.DBNullFloatHandler(rdr["DiffValue"]);
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>
/// Method to gets lsc project
/// </summary>
/// <param name="projectId">projectId</param>
/// <param name="connectionString">connectionString</param>
/// <returns>project object</returns>
public List <ProjectInfo> GetProjects(int lscId, string lscName, string projectId, string projectName, DateTime fromTime, DateTime endTime, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@ProjectId", SqlDbType.VarChar, 50),
new SqlParameter("@ProjectName", SqlDbType.VarChar, 100),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime) };
parms[0].Value = DBNull.Value;
if (projectId != null && projectId != String.Empty)
{
parms[0].Value = projectId;
}
parms[1].Value = DBNull.Value;
if (projectName != null && projectName != String.Empty)
{
parms[1].Value = projectName;
}
parms[2].Value = fromTime;
parms[3].Value = endTime;
var projects = new List <ProjectInfo>();
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Appointment_Get_Projects, parms)) {
while (rdr.Read())
{
var project = new ProjectInfo();
project.LscID = lscId;
project.LscName = lscName;
project.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
project.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
project.BeginTime = ComUtility.DBNullDateTimeHandler(rdr["BeginTime"]);
project.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
project.Responsible = ComUtility.DBNullStringHandler(rdr["Responsible"]);
project.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
project.Company = ComUtility.DBNullStringHandler(rdr["Company"]);
project.Comment = ComUtility.DBNullStringHandler(rdr["Comment"]);
project.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
projects.Add(project);
}
}
return(projects);
}
/// <summary>
/// Gets history appointment records.
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="lscName">lscName</param>
/// <param name="beginTime">beginTime</param>
/// <param name="endTime">endTime</param>
/// <param name="queryType">queryType</param>
/// <param name="queryText">queryText</param>
/// <returns></returns>
public List <AppointmentInfo> GetHisAppointments(int lscId, string lscName, DateTime beginTime, DateTime endTime, int queryType, string queryText)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@QueryType", SqlDbType.Int),
new SqlParameter("@QueryText", SqlDbType.NVarChar, 100) };
parms[0].Value = lscId;
parms[1].Value = beginTime;
parms[2].Value = endTime;
parms[3].Value = queryType;
parms[4].Value = DBNull.Value;
if (queryText != null && queryText.Trim() != String.Empty)
{
parms[4].Value = queryText.Trim();
}
var appointments = new List <AppointmentInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.Sql_Appointment_Get_His, parms)) {
while (rdr.Read())
{
var appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
appointment.RecordTime = ComUtility.DBNullDateTimeHandler(rdr["RecordTime"]);
appointments.Add(appointment);
}
}
return(appointments);
}
/// <summary>
/// Method to get load alarms
/// </summary>
public List <LoadAlarmInfo> GetLoadAlarms()
{
var alarms = new List <LoadAlarmInfo>();
var sqlText = @"
;WITH Alarms AS
(
SELECT LA.*,L.[LscName],DT.[TypeName] AS [DevTypeName]
FROM [dbo].[TA_LoadAlarm] LA
INNER JOIN [dbo].[TM_DEV] TD ON LA.[LscID] = TD.[LscID] AND LA.[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],
[DevTypeID],[DevTypeName],[AlarmLevel],[RateValue],[LoadValue],[LoadPercent],[RightPercent],
[StartTime],[ConfirmName],[ConfirmTime],[EndName],[EndTime] FROM Alarms;";
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText, null)) {
while (rdr.Read())
{
var alarm = new LoadAlarmInfo();
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.DevTypeID = ComUtility.DBNullInt32Handler(rdr["DevTypeID"]);
alarm.DevTypeName = ComUtility.DBNullStringHandler(rdr["DevTypeName"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.RateValue = ComUtility.DBNullFloatHandler(rdr["RateValue"]);
alarm.LoadValue = ComUtility.DBNullFloatHandler(rdr["LoadValue"]);
alarm.LoadPercent = ComUtility.DBNullFloatHandler(rdr["LoadPercent"]);
alarm.RightPercent = ComUtility.DBNullFloatHandler(rdr["RightPercent"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
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>
/// Method to get LSC user
/// </summary>
/// <param name="uId">uId</param>
/// <param name="pwd">pwd</param>
public List <LscUserInfo> GetUser(string uId, string pwd)
{
SqlParameter[] parms = { new SqlParameter("@UID", SqlDbType.VarChar, 20),
new SqlParameter("@PWD", SqlDbType.VarChar, 20) };
parms[0].Value = uId;
if (pwd == null)
{
parms[1].Value = DBNull.Value;
}
else
{
parms[1].Value = pwd;
}
var lscUsers = new List <LscUserInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_USER_GETUSER, parms)) {
while (rdr.Read())
{
var lscUser = new LscUserInfo();
lscUser.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
lscUser.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
lscUser.UserID = ComUtility.DBNullInt32Handler(rdr["UserID"]);
lscUser.UserName = ComUtility.DBNullStringHandler(rdr["UserName"]);
lscUser.UID = ComUtility.DBNullStringHandler(rdr["UID"]);
lscUser.OpLevel = ComUtility.DBNullUserLevelHandler(rdr["OpLevel"]);
lscUser.LimitTime = ComUtility.DBNullDateTimeHandler(rdr["LimitTime"]);
lscUser.TelePhone = ComUtility.DBNullStringHandler(rdr["TelePhone"]);
lscUser.MobilePhone = ComUtility.DBNullStringHandler(rdr["MobilePhone"]);
lscUser.Email = ComUtility.DBNullStringHandler(rdr["Email"]);
lscUser.Address = ComUtility.DBNullStringHandler(rdr["Address"]);
lscUser.PostalCode = ComUtility.DBNullStringHandler(rdr["PostalCode"]);
lscUser.Remark = ComUtility.DBNullStringHandler(rdr["Remark"]);
lscUser.AlarmSoundFiterItem = ComUtility.DBNullAlarmSoundFiterItemHandler(rdr["AlarmSoundFiterItem"]);
lscUser.AlarmStaticFiterItem = ComUtility.DBNullAlarmStaticFiterItemHandler(rdr["AlarmStaticFiterItem"]);
lscUser.ActiveValuesFiterItem = ComUtility.DBNullActiveValuesFiterItemHandler(rdr["ActiveValuesFiterItem"]);
lscUser.Group = null;
lscUser.UDGroups = null;
lscUser.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
lscUsers.Add(lscUser);
}
}
return(lscUsers);
}
/// <summary>
/// Method to get history pictures information
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="fromTime">fromTime</param>
/// <param name="toTime">toTime</param>
/// <param name="picModels">picModels</param>
public List <PicInfo> GetHisPictures(int lscId, DateTime fromTime, DateTime toTime, string[] picModels)
{
try {
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@FromTime", SqlDbType.DateTime),
new SqlParameter("@ToTime", SqlDbType.DateTime),
new SqlParameter("@PicModels", SqlDbType.NVarChar, 1024) };
parms[0].Value = lscId;
parms[1].Value = fromTime;
parms[2].Value = toTime;
if (picModels != null && picModels.Length > 0)
{
for (int i = 0; i < picModels.Length; i++)
{
picModels[i] = String.Format("\"{0}\"", picModels[i]);
}
parms[3].Value = String.Join(",", picModels);
}
else
{
parms[3].Value = DBNull.Value;
}
var pics = new List <PicInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_REPORT_GETHISPICTURES, parms)) {
var id = 0;
while (rdr.Read())
{
var pic = new PicInfo();
pic.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
pic.LscName = ComUtility.DefaultString;
pic.RtuID = ComUtility.DBNullInt32Handler(rdr["RtuID"]);
pic.PicID = ++id;
pic.PicName = ComUtility.DefaultString;
pic.PicModel = ComUtility.DBNullStringHandler(rdr["PicModel"]);
pic.PicBytes = ComUtility.DBNullBytesHandler(rdr["PicBytes"]);
pic.PicTime = ComUtility.DBNullDateTimeHandler(rdr["PicTime"]);
pics.Add(pic);
}
}
return(pics);
} catch { throw; }
}
/// <summary>
/// Method to get trend count alarms
/// </summary>
public List <TrendAlarmInfo> GetTrendCountAlarms(int lscId, int nodeId, DateTime beginTime, DateTime endTime, float aiValue, int timeType, int valueType)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@AicID", SqlDbType.Int),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@AIValue", SqlDbType.Real),
new SqlParameter("@TimeType", SqlDbType.Int),
new SqlParameter("@ValueType", SqlDbType.Int) };
parms[0].Value = lscId;
if (nodeId == ComUtility.DefaultInt32)
{
parms[1].Value = DBNull.Value;
}
else
{
parms[1].Value = nodeId;
}
parms[2].Value = beginTime;
parms[3].Value = endTime;
parms[4].Value = aiValue;
parms[5].Value = timeType;
parms[6].Value = valueType;
var alarms = new List <TrendAlarmInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_PREALARM_GETTRENDCOUNTALARMS, parms)) {
while (rdr.Read())
{
var alarm = new TrendAlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["AicID"]);
alarm.StartValue = ComUtility.DBNullFloatHandler(rdr["StartValue"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.DiffValue = ComUtility.DBNullFloatHandler(rdr["DiffValue"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarms.Add(alarm);
}
}
return(alarms);
}
/// <summary>
/// Method to get lscs information
/// </summary>
/// <param name="connectionString">connectionString</param>
public List <LscInfo> GetLscs(string connectionString)
{
try {
if (String.IsNullOrEmpty(connectionString))
{
connectionString = SqlHelper.ConnectionStringLocalTransaction;
}
var lscs = new List <LscInfo>();
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.SQL_SELECT_LSC_GETLSCS, null)) {
while (rdr.Read())
{
var lsc = new LscInfo();
lsc.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
lsc.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
lsc.LscIP = ComUtility.DBNullStringHandler(rdr["LscIP"]);
lsc.LscPort = ComUtility.DBNullInt32Handler(rdr["LscPort"]);
lsc.LscUID = ComUtility.DBNullStringHandler(rdr["LscUID"]);
lsc.LscPwd = ComUtility.DBNullStringHandler(rdr["LscPwd"]);
lsc.BeatInterval = ComUtility.DBNullInt32Handler(rdr["BeatInterval"]);
lsc.BeatDelay = ComUtility.DBNullInt32Handler(rdr["BeatDelay"]);
lsc.DBServer = ComUtility.DBNullStringHandler(rdr["DBServer"]);
lsc.DBPort = ComUtility.DBNullInt32Handler(rdr["DBPort"]);
lsc.DBName = ComUtility.DBNullStringHandler(rdr["DBName"]);
lsc.DBUID = ComUtility.DBNullStringHandler(rdr["DBUID"]);
lsc.DBPwd = ComUtility.DBNullStringHandler(rdr["DBPwd"]);
lsc.HisDBServer = ComUtility.DBNullStringHandler(rdr["HisDBServer"]);
lsc.HisDBPort = ComUtility.DBNullInt32Handler(rdr["HisDBPort"]);
lsc.HisDBName = ComUtility.DBNullStringHandler(rdr["HisDBName"]);
lsc.HisDBUID = ComUtility.DBNullStringHandler(rdr["HisDBUID"]);
lsc.HisDBPwd = ComUtility.DBNullStringHandler(rdr["HisDBPwd"]);
lsc.Connected = ComUtility.DBNullBooleanHandler(rdr["Connected"]);
lsc.ChangedTime = ComUtility.DBNullDateTimeHandler(rdr["ChangedTime"]);
lsc.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
lscs.Add(lsc);
}
}
return(lscs);
} catch { throw; }
}
/// <summary>
/// Method to get lsc information
/// </summary>
/// <param name="lscId">lscId</param>
public LscInfo GetLsc(int lscId)
{
try {
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int) };
parms[0].Value = lscId;
LscInfo lsc = null;
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_LSC_GETLSC, parms)) {
if (rdr.Read())
{
lsc = new LscInfo();
lsc.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
lsc.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
lsc.LscIP = ComUtility.DBNullStringHandler(rdr["LscIP"]);
lsc.LscPort = ComUtility.DBNullInt32Handler(rdr["LscPort"]);
lsc.LscUID = ComUtility.DBNullStringHandler(rdr["LscUID"]);
lsc.LscPwd = ComUtility.DBNullStringHandler(rdr["LscPwd"]);
lsc.BeatInterval = ComUtility.DBNullInt32Handler(rdr["BeatInterval"]);
lsc.BeatDelay = ComUtility.DBNullInt32Handler(rdr["BeatDelay"]);
lsc.DBServer = ComUtility.DBNullStringHandler(rdr["DBServer"]);
lsc.DBPort = ComUtility.DBNullInt32Handler(rdr["DBPort"]);
lsc.DBName = ComUtility.DBNullStringHandler(rdr["DBName"]);
lsc.DBUID = ComUtility.DBNullStringHandler(rdr["DBUID"]);
lsc.DBPwd = ComUtility.DBNullStringHandler(rdr["DBPwd"]);
lsc.HisDBServer = ComUtility.DBNullStringHandler(rdr["HisDBServer"]);
lsc.HisDBPort = ComUtility.DBNullInt32Handler(rdr["HisDBPort"]);
lsc.HisDBName = ComUtility.DBNullStringHandler(rdr["HisDBName"]);
lsc.HisDBUID = ComUtility.DBNullStringHandler(rdr["HisDBUID"]);
lsc.HisDBPwd = ComUtility.DBNullStringHandler(rdr["HisDBPwd"]);
lsc.Connected = ComUtility.DBNullBooleanHandler(rdr["Connected"]);
lsc.ChangedTime = ComUtility.DBNullDateTimeHandler(rdr["ChangedTime"]);
lsc.Enabled = ComUtility.DBNullBooleanHandler(rdr["Enabled"]);
}
}
return(lsc);
} catch { throw; }
}
public List <AlarmInfo> GetHisAlarms(Int32 lscId, String lscName, List <StandardProtocolInfo> protocols, List <GroupTreeInfo> groupNodes, DateTime startTime, DateTime endTime, String[] nodeNames, Int32[] devTypes, Double?minInterval, Double?maxInterval)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@BeginFromTime", SqlDbType.DateTime),
new SqlParameter("@BeginToTime", SqlDbType.DateTime),
new SqlParameter("@MinInterval", SqlDbType.Float),
new SqlParameter("@MaxInterval", SqlDbType.Float),
new SqlParameter("@NodeNames", SqlDbType.NVarChar, 4000) };
parms[0].Value = lscId;
parms[1].Value = startTime;
parms[2].Value = endTime;
parms[3].Value = DBNull.Value;
parms[4].Value = DBNull.Value;
parms[5].Value = DBNull.Value;
if (minInterval != null)
{
parms[3].Value = minInterval;
}
if (maxInterval != null)
{
parms[4].Value = maxInterval;
}
if (nodeNames != null && nodeNames.Length > 0)
{
for (var i = 0; i < nodeNames.Length; i++)
{
nodeNames[i] = String.Format(@"SELECT '%{0}%' AS [NodeName]", nodeNames[i]);
}
parms[5].Value = String.Join(" UNION ", nodeNames);
}
var alarms = new List <AlarmInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_ALARM_GETHISALARMS2, parms)) {
while (rdr.Read())
{
var alarm = new AlarmInfo();
alarm.LscID = lscId;
alarm.LscName = lscName;
alarm.SerialNO = ComUtility.DBNullInt32Handler(rdr["SerialNO"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.Area4Name = ComUtility.DBNullStringHandler(rdr["Area4Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.DevDesc = ComUtility.DefaultString;
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.AlarmID = ComUtility.DBNullInt32Handler(rdr["AlarmID"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.AlarmStatus = EnmAlarmStatus.Ended;
alarm.AlarmDesc = ComUtility.DBNullStringHandler(rdr["AlarmDesc"]);
alarm.AuxAlarmDesc = ComUtility.DBNullStringHandler(rdr["AuxAlarmDesc"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmMarking = ComUtility.DBNullConfirmMarkingHandler(rdr["ConfirmMarking"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.AuxSet = ComUtility.DefaultString;
alarm.ProjName = ComUtility.DBNullStringHandler(rdr["ProjName"]);
alarm.TurnCount = 0;
alarm.UpdateTime = ComUtility.DBNullDateTimeHandler(rdr["UpdateTime"]);
alarms.Add(alarm);
}
}
if (alarms.Count > 0)
{
alarms = (from alarm in alarms
join node in groupNodes on ComUtility.GetDevID(alarm.NodeID) equals node.NodeID
join sp in protocols on alarm.AlarmID equals sp.AlarmID into tp
from dl in tp.DefaultIfEmpty()
select new AlarmInfo {
LscID = alarm.LscID,
LscName = alarm.LscName,
SerialNO = alarm.SerialNO,
Area1Name = alarm.Area1Name,
Area2Name = alarm.Area2Name,
Area3Name = alarm.Area3Name,
Area4Name = alarm.Area4Name,
StaName = alarm.StaName,
DevName = alarm.DevName,
DevDesc = alarm.DevDesc,
NodeID = alarm.NodeID,
NodeType = alarm.NodeType,
NodeName = alarm.NodeName,
AlarmID = alarm.AlarmID,
AlarmValue = alarm.AlarmValue,
AlarmLevel = alarm.AlarmLevel,
AlarmStatus = alarm.AlarmStatus,
AlarmDesc = alarm.AlarmDesc,
AuxAlarmDesc = alarm.AuxAlarmDesc,
StartTime = alarm.StartTime,
EndTime = alarm.EndTime,
ConfirmName = alarm.ConfirmName,
ConfirmMarking = alarm.ConfirmMarking,
ConfirmTime = alarm.ConfirmTime,
AuxSet = alarm.AuxSet,
ProjName = alarm.ProjName,
TurnCount = alarm.TurnCount,
UpdateTime = alarm.UpdateTime,
NMAlarmID = (dl == null ? ComUtility.DefaultString : dl.NMAlarmID),
AlarmName = (dl == null ? ComUtility.DefaultString : dl.AlarmName),
AlarmClass = (dl == null ? ComUtility.DefaultString : dl.AlarmClass),
AlarmDeviceTypeID = (dl == null ? ComUtility.DefaultInt32 : dl.AlarmDeviceTypeID),
AlarmDeviceType = (dl == null ? ComUtility.DefaultString : dl.AlarmDeviceType),
AlarmLogTypeID = (dl == null ? ComUtility.DefaultInt32 : dl.AlarmLogTypeID),
AlarmLogType = (dl == null ? ComUtility.DefaultString : dl.AlarmLogType)
}).ToList();
if (devTypes != null)
{
alarms = alarms.FindAll(alarm => devTypes.Contains(alarm.AlarmDeviceTypeID));
}
}
return(alarms);
}
/// <summary>
/// Gets all appointments
/// </summary>
/// <param name="lscId"></param>
/// <param name="lscName"></param>
/// <param name="connectionString"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="queryType"></param>
/// <param name="queryText"></param>
/// <returns></returns>
public List <AppointmentInfo> GetAppointments(int lscId, string lscName, string connectionString, DateTime beginTime, DateTime endTime, int queryType, string queryText)
{
SqlParameter[] parms = { new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime) };
parms[0].Value = beginTime;
parms[1].Value = endTime;
var query = @"
SELECT PB.[BookingID] AS [Id],PB.[StartTime],PB.[EndTime],PB.[LscIncluded],PB.[StaIncluded],PB.[DevIncluded]
,PB.[ProjID] AS [ProjectId],PB.[ProjName] AS [ProjectName],PB.[ProjStatus] AS [Status],PB.[BookingUserID] AS [CreaterId],U.[UserName] AS [Creater]
,U.[MobilePhone] AS [ContactPhone],PB.[BookingTime] AS [CreatedTime] FROM [dbo].[TM_ProjBooking] PB
LEFT OUTER JOIN [dbo].[TU_User] U ON PB.[BookingUserID]=U.[UserID] WHERE PB.[StartTime] BETWEEN @StartTime AND @EndTime";
if (queryText != null && queryText.Trim() != String.Empty)
{
if (queryType == 0)
{
query += @" AND PB.[ProjID] LIKE '%" + queryText + @"%'";
}
else if (queryType == 1)
{
query += @" AND PB.[ProjName] LIKE '%" + queryText + @"%'";
}
else if (queryType == 2)
{
query += @" AND PB.[LscIncluded] LIKE '" + queryText + @"'";
}
else if (queryType == 3)
{
query += @" AND PB.[StaIncluded] LIKE '%" + queryText + @"%'";
}
else if (queryType == 4)
{
query += @" AND PB.[DevIncluded] LIKE '%" + queryText + @"%'";
}
}
query += @" ORDER BY PB.[BookingID]";
var appointments = new List <AppointmentInfo>();
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query, parms)) {
while (rdr.Read())
{
var appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
appointments.Add(appointment);
}
}
return(appointments);
}
/// <summary>
/// Method to get system logs information
/// </summary>
/// <param name="fromTime">fromTime</param>
/// <param name="toTime">toTime</param>
/// <param name="eventLevel">eventLevel</param>
/// <param name="eventType">eventType</param>
/// <param name="_operator">_operator</param>
/// <param name="startIndex">startIndex</param>
/// <param name="endIndex">endIndex</param>
/// <param name="rowsCnt">rowsCnt</param>
public List <SysLogInfo> GetSysLogs(DateTime fromTime, DateTime toTime, string[] eventLevel, string[] eventType, string _operator, int startIndex, int endIndex, ref int rowsCnt)
{
try {
var sqlText = new StringBuilder();
sqlText.AppendFormat(@"
DECLARE @LevelTable TABLE([ID] [INT] NOT NULL);
DECLARE @TypeTable TABLE([ID] [INT] NOT NULL);
DECLARE @Pos INT;
SET @Pos = CHARINDEX(',', @EventLevels);
WHILE(@Pos > 0)
BEGIN
INSERT @LevelTable([ID]) VALUES(CAST(LEFT(@EventLevels, @Pos - 1) AS INT));
SELECT @EventLevels = STUFF(@EventLevels, 1, @Pos, ''), @Pos = CHARINDEX(',', @EventLevels);
END
IF(LEN(LTRIM(RTRIM(@EventLevels))) > 0)
BEGIN
INSERT @LevelTable([ID]) VALUES(CAST(@EventLevels AS INT));
END
SET @Pos = CHARINDEX(',', @EventTypes);
WHILE(@Pos > 0)
BEGIN
INSERT @TypeTable([ID]) VALUES(CAST(LEFT(@EventTypes, @Pos - 1) AS INT));
SELECT @EventTypes = STUFF(@EventTypes, 1, @Pos, ''), @Pos = CHARINDEX(',', @EventTypes);
END
IF(LEN(LTRIM(RTRIM(@EventTypes))) > 0)
BEGIN
INSERT @TypeTable([ID]) VALUES(CAST(@EventTypes AS INT));
END
;WITH tpData AS
(
SELECT ROW_NUMBER() OVER (ORDER BY TS.[EventID] DESC) AS RowNo,TS.[EventID],TS.[EventTime],TS.[EventLevel],
TS.[EventType],TS.[Message],TS.[Url],TS.[ClientIP],TS.[Operator] FROM [dbo].[TH_SysLog] TS
INNER JOIN @LevelTable LT ON TS.[EventLevel] = LT.[ID]
INNER JOIN @TypeTable TT ON TS.[EventType] = TT.[ID]
WHERE TS.[EventTime] BETWEEN @FromTime AND @ToTime {0}
),
RowsCnt AS
(
SELECT COUNT(1) AS [TotalCnt] FROM tpData
)
SELECT RC.[TotalCnt],D.[EventID],D.[EventTime],D.[EventLevel],D.[EventType],D.[Message],D.[Url],D.[ClientIP],D.[Operator]
FROM RowsCnt RC CROSS JOIN tpData D WHERE D.RowNo BETWEEN @StartIndex AND @EndIndex;", !String.IsNullOrEmpty(_operator) ? String.Format("AND [Operator] = '{0}'", _operator) : String.Empty);
SqlParameter[] parms = { new SqlParameter("@FromTime", SqlDbType.DateTime),
new SqlParameter("@ToTime", SqlDbType.DateTime),
new SqlParameter("@StartIndex", SqlDbType.Int),
new SqlParameter("@EndIndex", SqlDbType.Int),
new SqlParameter("@EventLevels", SqlDbType.NVarChar, 1024),
new SqlParameter("@EventTypes", SqlDbType.NVarChar, 1024) };
parms[0].Value = fromTime;
parms[1].Value = toTime;
parms[2].Value = startIndex;
parms[3].Value = endIndex;
parms[4].Value = eventLevel != null?String.Join(",", eventLevel) : String.Empty;
parms[5].Value = eventType != null?String.Join(",", eventType) : String.Empty;
var logs = new List <SysLogInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText.ToString(), parms)) {
while (rdr.Read())
{
var log = new SysLogInfo();
rowsCnt = ComUtility.DBNullInt32Handler(rdr["TotalCnt"]);
log.EventID = ComUtility.DBNullInt32Handler(rdr["EventID"]);
log.EventTime = ComUtility.DBNullDateTimeHandler(rdr["EventTime"]);
log.EventLevel = ComUtility.DBNullSysLogLevelHandler(rdr["EventLevel"]);
log.EventType = ComUtility.DBNullSysLogTypeHandler(rdr["EventType"]);
log.Message = ComUtility.DBNullStringHandler(rdr["Message"]);
log.Url = ComUtility.DBNullStringHandler(rdr["Url"]);
log.ClientIP = ComUtility.DBNullStringHandler(rdr["ClientIP"]);
log.Operator = ComUtility.DBNullStringHandler(rdr["Operator"]);
logs.Add(log);
}
}
return(logs);
} catch { throw; }
}
/// <summary>
/// Method to get operating events information
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="lscName">lscName</param>
/// <param name="fromTime">fromTime</param>
/// <param name="toTime">toTime</param>
/// <param name="userType">userType</param>
/// <param name="userId">userId</param>
/// <param name="eventTypes">eventTypes</param>
/// <param name="eventDescs">eventDescs</param>
public List <OpEventInfo> GetOpEvents(int lscId, string lscName, DateTime fromTime, DateTime toTime, int userType, int userId, string[] eventTypes, string[] eventDescs)
{
try {
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@FromTime", SqlDbType.DateTime),
new SqlParameter("@ToTime", SqlDbType.DateTime),
new SqlParameter("@UserType", SqlDbType.Int),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@EventTypes", SqlDbType.NVarChar, 1024) };
parms[0].Value = lscId;
parms[1].Value = fromTime;
parms[2].Value = toTime;
if (userType == ComUtility.DefaultInt32)
{
parms[3].Value = DBNull.Value;
}
else
{
parms[3].Value = userType;
}
if (userId == ComUtility.DefaultInt32)
{
parms[4].Value = DBNull.Value;
}
else
{
parms[4].Value = userId;
}
if (eventTypes != null && eventTypes.Length > 0)
{
for (int i = 0; i < eventTypes.Length; i++)
{
eventTypes[i] = String.Format("\"{0}\"", eventTypes[i]);
}
parms[5].Value = String.Join(",", eventTypes);
}
else
{
parms[5].Value = DBNull.Value;
}
var events = new List <OpEventInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_REPORT_GETOPEVENTS, parms)) {
while (rdr.Read())
{
if (eventDescs != null && eventDescs.Length > 0)
{
string opDesc = ComUtility.DBNullStringHandler(rdr["OpDesc"]);
if (!eventDescs.Any(desc => opDesc.Contains(desc)))
{
continue;
}
}
var perEvent = new OpEventInfo();
perEvent.LscID = lscId;
perEvent.LscName = lscName;
perEvent.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
perEvent.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
perEvent.EventName = ComUtility.DBNullStringHandler(rdr["EventName"]);
perEvent.UserType = ComUtility.DBNullUserTypeHandler(rdr["UserType"]);
perEvent.UserID = ComUtility.DBNullInt32Handler(rdr["UserID"]);
perEvent.UserName = ComUtility.DBNullStringHandler(rdr["UserName"]);
perEvent.EventTime = ComUtility.DBNullDateTimeHandler(rdr["EventTime"]);
perEvent.OpDesc = ComUtility.DBNullStringHandler(rdr["OpDesc"]);
events.Add(perEvent);
}
}
return(events);
} catch { throw; }
}
/// <summary>
/// Method to get access records information
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="lscName">lscName</param>
/// <param name="fromTime">fromTime</param>
/// <param name="toTime">toTime</param>
/// <param name="Area2Name">Area2Name</param>
/// <param name="Area3Name">Area3Name</param>
/// <param name="StaName">StaName</param>
/// <param name="DevName">DevName</param>
/// <param name="empNames">empNames</param>
/// <param name="punchs">punchs</param>
public List <AccessRecordInfo> GetAccessRecords(int lscId, string lscName, DateTime fromTime, DateTime toTime, string Area2Name, string Area3Name, string StaName, string DevName, string[] empNames, string[] punchs)
{
try {
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@FromTime", SqlDbType.DateTime),
new SqlParameter("@ToTime", SqlDbType.DateTime),
new SqlParameter("@Area2Name", SqlDbType.NVarChar, 50),
new SqlParameter("@Area3Name", SqlDbType.NVarChar, 50),
new SqlParameter("@StaName", SqlDbType.NVarChar, 50),
new SqlParameter("@DevName", SqlDbType.NVarChar, 50) };
parms[0].Value = lscId;
parms[1].Value = fromTime;
parms[2].Value = toTime;
if (Area2Name == ComUtility.DefaultString)
{
parms[3].Value = DBNull.Value;
}
else
{
parms[3].Value = Area2Name;
}
if (Area3Name == ComUtility.DefaultString)
{
parms[4].Value = DBNull.Value;
}
else
{
parms[4].Value = Area3Name;
}
if (StaName == ComUtility.DefaultString)
{
parms[5].Value = DBNull.Value;
}
else
{
parms[5].Value = StaName;
}
if (DevName == ComUtility.DefaultString)
{
parms[6].Value = DBNull.Value;
}
else
{
parms[6].Value = DevName;
}
var records = new List <AccessRecordInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_REPORT_GETACCESSRECORDS, parms)) {
while (rdr.Read())
{
if (empNames != null && empNames.Length > 0)
{
string empName = ComUtility.DBNullStringHandler(rdr["EmpName"]);
if (!empNames.Any(name => empName.Contains(name)))
{
continue;
}
}
if (punchs != null && punchs.Length > 0)
{
string punch = ComUtility.DBNullStringHandler(rdr["PunchNO"]);
if (!punchs.Any(pch => punch.Contains(pch)))
{
continue;
}
}
var rec = new AccessRecordInfo();
rec.LscID = lscId;
rec.LscName = lscName;
rec.DevID = ComUtility.DBNullInt32Handler(rdr["DevID"]);
rec.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
rec.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
rec.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
rec.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
rec.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
rec.EmpName = ComUtility.DBNullStringHandler(rdr["EmpName"]);
rec.EmpNO = ComUtility.DBNullStringHandler(rdr["EmpNO"]);
rec.PunchTime = ComUtility.DBNullDateTimeHandler(rdr["PunchTime"]);
rec.PunchNO = ComUtility.DBNullStringHandler(rdr["PunchNO"]);
rec.Status = ComUtility.DBNullStringHandler(rdr["Status"]);
rec.Remark = ComUtility.DBNullStringHandler(rdr["Remark"]);
rec.Direction = ComUtility.DBNullAccessDirectionHandler(rdr["Direction"]);
records.Add(rec);
}
}
return(records);
} catch { throw; }
}
public List <AlarmInfo> GetHisAlarms(Int32 lscId, String lscName, List <StandardProtocolInfo> protocols, List <GroupTreeInfo> groupNodes, HisAlarmConditionInfo condition)
{
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("@Area4Name", SqlDbType.NVarChar, 40),
new SqlParameter("@StaName", SqlDbType.NVarChar, 40),
new SqlParameter("@DevName", SqlDbType.NVarChar, 40),
new SqlParameter("@NodeName", SqlDbType.NVarChar, 40),
new SqlParameter("@AlarmDevName", SqlDbType.NVarChar, 40),
new SqlParameter("@AlarmLogicName", SqlDbType.NVarChar, 40),
new SqlParameter("@AlarmName", SqlDbType.NVarChar, 40),
new SqlParameter("@AlarmLevels", SqlDbType.NVarChar, 50),
new SqlParameter("@BeginFromTime", SqlDbType.DateTime),
new SqlParameter("@BeginToTime", SqlDbType.DateTime),
new SqlParameter("@EndFromTime", SqlDbType.DateTime),
new SqlParameter("@EndToTime", SqlDbType.DateTime),
new SqlParameter("@ConfirmName", SqlDbType.NVarChar, 20),
new SqlParameter("@ConfirmFromTime", SqlDbType.DateTime),
new SqlParameter("@ConfirmToTime", SqlDbType.DateTime),
new SqlParameter("@MinDelay", SqlDbType.Real),
new SqlParameter("@MaxDelay", SqlDbType.Real),
new SqlParameter("@MHArea1", SqlDbType.Bit),
new SqlParameter("@MHArea2", SqlDbType.Bit),
new SqlParameter("@MHArea3", SqlDbType.Bit),
new SqlParameter("@MHArea4", SqlDbType.Bit),
new SqlParameter("@MHSta", SqlDbType.Bit),
new SqlParameter("@MHDev", SqlDbType.Bit),
new SqlParameter("@MHNode", SqlDbType.Bit) };
parms[0].Value = lscId;
if (condition.Area1Item.Value == null || condition.Area1Item.Value.Equals(ComUtility.DefaultItemID))
{
parms[1].Value = DBNull.Value;
}
else
{
parms[1].Value = condition.Area1Item.Text;
}
if (condition.Area2Item.Value == null || condition.Area2Item.Value.Equals(ComUtility.DefaultItemID))
{
parms[2].Value = DBNull.Value;
}
else
{
parms[2].Value = condition.Area2Item.Text;
}
if (condition.Area3Item.Value == null || condition.Area3Item.Value.Equals(ComUtility.DefaultItemID))
{
parms[3].Value = DBNull.Value;
}
else
{
parms[3].Value = condition.Area3Item.Text;
}
if (condition.Area4Item.Value == null || condition.Area4Item.Value.Equals(ComUtility.DefaultItemID))
{
parms[4].Value = DBNull.Value;
}
else
{
parms[4].Value = condition.Area4Item.Text;
}
if (condition.StaItem.Value == null || condition.StaItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[5].Value = DBNull.Value;
}
else
{
parms[5].Value = condition.StaItem.Text;
}
if (condition.DevItem.Value == null || condition.DevItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[6].Value = DBNull.Value;
}
else
{
parms[6].Value = condition.DevItem.Text;
}
if (condition.NodeItem.Value == null || condition.NodeItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[7].Value = DBNull.Value;
}
else
{
parms[7].Value = condition.NodeItem.Text;
}
if (condition.AlarmDevItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[8].Value = DBNull.Value;
}
else
{
parms[8].Value = condition.AlarmDevItem.Text;
}
if (condition.AlarmLogicItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[9].Value = DBNull.Value;
}
else
{
parms[9].Value = condition.AlarmLogicItem.Text;
}
if (condition.AlarmNameItem.Value.Equals(ComUtility.DefaultItemID))
{
parms[10].Value = DBNull.Value;
}
else
{
parms[10].Value = condition.AlarmNameItem.Text;
}
if (condition.AlarmLevelItems.Length <= 0)
{
parms[11].Value = DBNull.Value;
}
else
{
parms[11].Value = String.Join(",", condition.AlarmLevelItems);
}
if (String.IsNullOrEmpty(condition.BeginFromTimeText))
{
parms[12].Value = DBNull.Value;
}
else
{
parms[12].Value = condition.BeginFromTimeText;
}
if (String.IsNullOrEmpty(condition.BeginToTimeText))
{
parms[13].Value = DBNull.Value;
}
else
{
parms[13].Value = condition.BeginToTimeText;
}
if (String.IsNullOrEmpty(condition.EndFromTimeText))
{
parms[14].Value = DBNull.Value;
}
else
{
parms[14].Value = condition.EndFromTimeText;
}
if (String.IsNullOrEmpty(condition.EndToTimeText))
{
parms[15].Value = DBNull.Value;
}
else
{
parms[15].Value = condition.EndToTimeText;
}
if (String.IsNullOrEmpty(condition.ConfirmNameText))
{
parms[16].Value = DBNull.Value;
}
else
{
parms[16].Value = condition.ConfirmNameText;
}
if (String.IsNullOrEmpty(condition.ConfirmFromTimeText))
{
parms[17].Value = DBNull.Value;
}
else
{
parms[17].Value = condition.ConfirmFromTimeText;
}
if (String.IsNullOrEmpty(condition.ConfirmToTimeText))
{
parms[18].Value = DBNull.Value;
}
else
{
parms[18].Value = condition.ConfirmToTimeText;
}
if (condition.MinDelay == Double.MinValue)
{
parms[19].Value = DBNull.Value;
}
else
{
parms[19].Value = condition.MinDelay;
}
if (condition.MaxDelay == Double.MaxValue)
{
parms[20].Value = DBNull.Value;
}
else
{
parms[20].Value = condition.MaxDelay;
}
parms[21].Value = condition.Area1Item.SelectedIndex == -1;
parms[22].Value = condition.Area2Item.SelectedIndex == -1;
parms[23].Value = condition.Area3Item.SelectedIndex == -1;
parms[24].Value = condition.Area4Item.SelectedIndex == -1;
parms[25].Value = condition.StaItem.SelectedIndex == -1;
parms[26].Value = condition.DevItem.SelectedIndex == -1;
parms[27].Value = condition.NodeItem.SelectedIndex == -1;
var alarms = new List <AlarmInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_ALARM_GETHISALARMS, parms)) {
while (rdr.Read())
{
var alarm = new AlarmInfo();
alarm.LscID = lscId;
alarm.LscName = lscName;
alarm.SerialNO = ComUtility.DBNullInt32Handler(rdr["SerialNO"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.Area4Name = ComUtility.DBNullStringHandler(rdr["Area4Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.DevDesc = ComUtility.DefaultString;
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.AlarmID = ComUtility.DBNullInt32Handler(rdr["AlarmID"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.AlarmStatus = EnmAlarmStatus.Ended;
alarm.AlarmDesc = ComUtility.DBNullStringHandler(rdr["AlarmDesc"]);
alarm.AuxAlarmDesc = ComUtility.DBNullStringHandler(rdr["AuxAlarmDesc"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmMarking = ComUtility.DBNullConfirmMarkingHandler(rdr["ConfirmMarking"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.AuxSet = ComUtility.DefaultString;
alarm.ProjName = ComUtility.DBNullStringHandler(rdr["ProjName"]);
alarm.TurnCount = 0;
alarm.UpdateTime = ComUtility.DBNullDateTimeHandler(rdr["UpdateTime"]);
alarms.Add(alarm);
}
}
if (alarms.Count > 0)
{
alarms = (from alarm in alarms
join node in groupNodes on ComUtility.GetDevID(alarm.NodeID) equals node.NodeID
join sp in protocols on alarm.AlarmID equals sp.AlarmID into tp
from dl in tp.DefaultIfEmpty()
select new AlarmInfo {
LscID = alarm.LscID,
LscName = alarm.LscName,
SerialNO = alarm.SerialNO,
Area1Name = alarm.Area1Name,
Area2Name = alarm.Area2Name,
Area3Name = alarm.Area3Name,
Area4Name = alarm.Area4Name,
StaName = alarm.StaName,
DevName = alarm.DevName,
DevDesc = alarm.DevDesc,
NodeID = alarm.NodeID,
NodeType = alarm.NodeType,
NodeName = alarm.NodeName,
AlarmID = alarm.AlarmID,
AlarmValue = alarm.AlarmValue,
AlarmLevel = alarm.AlarmLevel,
AlarmStatus = alarm.AlarmStatus,
AlarmDesc = alarm.AlarmDesc,
AuxAlarmDesc = alarm.AuxAlarmDesc,
StartTime = alarm.StartTime,
EndTime = alarm.EndTime,
ConfirmName = alarm.ConfirmName,
ConfirmMarking = alarm.ConfirmMarking,
ConfirmTime = alarm.ConfirmTime,
AuxSet = alarm.AuxSet,
ProjName = alarm.ProjName,
TurnCount = alarm.TurnCount,
UpdateTime = alarm.UpdateTime,
NMAlarmID = (dl == null ? ComUtility.DefaultString : dl.NMAlarmID),
AlarmName = (dl == null ? ComUtility.DefaultString : dl.AlarmName),
AlarmClass = (dl == null ? ComUtility.DefaultString : dl.AlarmClass),
AlarmDeviceTypeID = (dl == null ? ComUtility.DefaultInt32 : dl.AlarmDeviceTypeID),
AlarmDeviceType = (dl == null ? ComUtility.DefaultString : dl.AlarmDeviceType),
AlarmLogTypeID = (dl == null ? ComUtility.DefaultInt32 : dl.AlarmLogTypeID),
AlarmLogType = (dl == null ? ComUtility.DefaultString : dl.AlarmLogType)
}).ToList();
}
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);
}
public List <AlarmInfo> GetAlarms()
{
var alarms = new List <AlarmInfo>();
var sqlText = @"
;WITH Alarms AS
(
SELECT L.[LscID],L.[LscName],TA.[SerialNO],TA.[Area1Name],TA.[Area2Name],TA.[Area3Name],TA.[Area4Name],
TA.[StaName],TA.[DevName],TA.[DevDesc],TA.[NodeID],TA.[NodeType],TA.[NodeName],TA.[AlarmID],TA.[AlarmValue],
TA.[AlarmLevel],TA.[AlarmStatus],TA.[AlarmDesc],TA.[AuxAlarmDesc],TA.[StartTime],TA.[EndTime],TA.[ConfirmName],
TA.[ConfirmMarking],TA.[ConfirmTime],TA.[AuxSet],TA.[ProjName],TA.[TurnCount],TA.[UpdateTime]
FROM [dbo].[TA_Alarm] TA INNER JOIN [dbo].[TM_LSC] L ON TA.[LscID] = L.[LscID]
),
AlarmDetails AS
(
SELECT AN.[ID] AS AlarmID,AN.[AlarmName],AN.[AlarmInterpret],AN.[AlarmVer],AN.[AlarmLevel],
AN.[DevEffect],AN.[OperEffect],AN.[SubAlarmLogTypeID],SALT.[TypeDesc] AS SubAlarmLogType,
SALT.[AlarmLogTypeID],ALT.[TypeDesc] AS AlarmLogType,ALT.[AlarmDeviceTypeID],
ADT.[TypeDesc] AS AlarmDeviceType,AN.[NMAlarmID], AN.[AlarmClass] FROM [dbo].[TC_AlarmName] AN
LEFT OUTER JOIN [dbo].[TC_SubAlarmLogType] SALT ON AN.[SubAlarmLogTypeID] = SALT.[TypeID]
LEFT OUTER JOIN [dbo].[TC_AlarmLogType] ALT ON SALT.[AlarmLogTypeID] = ALT.[TypeID]
LEFT OUTER JOIN [dbo].[TC_AlarmDeviceType] ADT ON ALT.[AlarmDeviceTypeID] = ADT.[TypeID]
)
SELECT A.[LscID],A.[LscName],A.[SerialNO],A.[Area1Name],A.[Area2Name],A.[Area3Name],A.[Area4Name],
A.[StaName],A.[DevName],A.[DevDesc],A.[NodeID],A.[NodeType],A.[NodeName],A.[AlarmID],A.[AlarmValue],
A.[AlarmLevel],A.[AlarmStatus],A.[AlarmDesc],A.[AuxAlarmDesc],A.[StartTime],A.[EndTime],A.[ConfirmName],
A.[ConfirmMarking],A.[ConfirmTime],A.[AuxSet],A.[ProjName],A.[TurnCount],A.[UpdateTime],AD.[NMAlarmID],
AD.[AlarmName],AD.[AlarmClass],AD.[AlarmDeviceTypeID],AD.[AlarmDeviceType],AD.[AlarmLogTypeID],AD.[AlarmLogType]
FROM Alarms A LEFT OUTER JOIN AlarmDetails AD ON A.AlarmID = AD.AlarmID;";
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText, null)) {
while (rdr.Read())
{
var alarm = new AlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
alarm.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
alarm.SerialNO = ComUtility.DBNullInt32Handler(rdr["SerialNO"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.Area4Name = ComUtility.DBNullStringHandler(rdr["Area4Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.DevDesc = ComUtility.DBNullStringHandler(rdr["DevDesc"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.AlarmID = ComUtility.DBNullInt32Handler(rdr["AlarmID"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.AlarmStatus = ComUtility.DBNullAlarmStatusHandler(rdr["AlarmStatus"]);
alarm.AlarmDesc = ComUtility.DBNullStringHandler(rdr["AlarmDesc"]);
alarm.AuxAlarmDesc = ComUtility.DBNullStringHandler(rdr["AuxAlarmDesc"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmMarking = ComUtility.DBNullConfirmMarkingHandler(rdr["ConfirmMarking"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.AuxSet = ComUtility.DBNullStringHandler(rdr["AuxSet"]);
alarm.ProjName = ComUtility.DBNullStringHandler(rdr["ProjName"]);
alarm.TurnCount = ComUtility.DBNullInt32Handler(rdr["TurnCount"]);
alarm.UpdateTime = ComUtility.DBNullDateTimeHandler(rdr["UpdateTime"]);
alarm.NMAlarmID = ComUtility.DBNullStringHandler(rdr["NMAlarmID"]);
alarm.AlarmName = ComUtility.DBNullStringHandler(rdr["AlarmName"]);
alarm.AlarmClass = ComUtility.DBNullStringHandler(rdr["AlarmClass"]);
alarm.AlarmDeviceTypeID = ComUtility.DBNullInt32Handler(rdr["AlarmDeviceTypeID"]);
alarm.AlarmDeviceType = ComUtility.DBNullStringHandler(rdr["AlarmDeviceType"]);
alarm.AlarmLogTypeID = ComUtility.DBNullInt32Handler(rdr["AlarmLogTypeID"]);
alarm.AlarmLogType = ComUtility.DBNullStringHandler(rdr["AlarmLogType"]);
alarms.Add(alarm);
}
}
return(alarms);
}
/// <summary>
/// Method to get virtual trend alarms
/// </summary>
public List <TrendAlarmInfo> GetVirtualTrendAlarms(int lscId, int area1Id, int area2Id, int area3Id, int staId, int devId, int nodeId)
{
var sqlText = new StringBuilder();
var alarms = new List <TrendAlarmInfo>();
sqlText.AppendFormat(@"
SELECT TL.[LscID],TL.[LscName],TAAA.[AreaName] AS [Area1Name],TAA.[AreaName] AS [Area2Name],TA.[AreaName] AS [Area3Name],TS.[StaName],
TD.[DevName],AI.[AicID] AS [NodeID],AI.[AicName] AS [NodeName],DT.[TypeID] AS [DevTypeID],DT.[TypeName] AS [DevTypeName],
N'统计对比' AS [AlarmType],AI.[AlarmLevel],0 AS [StartValue],0 AS [AlarmValue],0 AS [DiffValue],NULL AS [StartTime],NULL AS [AlarmTime],
NULL AS [ConfirmName],NULL AS [ConfirmTime],NULL AS [EndName],NULL AS [EndTime] FROM [dbo].[TM_AIC] AI
INNER JOIN [dbo].[TM_DEV] TD ON AI.[LscID] = TD.[LscID] AND AI.[DevID] = TD.[DevID]
INNER JOIN [dbo].[TM_STA] TS ON TD.[LscID] = TS.[LscID] AND TD.[StaID] = TS.[StaID]
INNER JOIN [dbo].[TM_AREA] TA ON TS.[LscID] = TA.[LscID] AND TS.[AreaID] = TA.[AreaID]
INNER JOIN [dbo].[TM_LSC] TL ON TA.[LscID] = TL.[LscID]
LEFT OUTER JOIN [dbo].[TC_DeviceType] DT ON TD.[DevTypeID] = DT.[TypeID]
LEFT OUTER JOIN [dbo].[TM_AREA] TAA ON TA.[LscID] = TAA.[LscID] AND TA.[LastAreaID] = TAA.[AreaID] AND TAA.[NodeLevel] = 2
LEFT OUTER JOIN [dbo].[TM_AREA] TAAA ON TAA.[LscID] = TAAA.[LscID] AND TAA.[LastAreaID] = TAAA.[AreaID] AND TAAA.[NodeLevel] = 1
WHERE TL.[LscID] = @LscID");
if (area1Id != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND TAAA.[AreaID] = {0}", area1Id);
}
if (area2Id != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND TAA.[AreaID] = {0}", area2Id);
}
if (area3Id != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND TA.[AreaID] = {0}", area3Id);
}
if (staId != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND TS.[StaID] = {0}", staId);
}
if (devId != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND TD.[DevID] = {0}", devId);
}
if (nodeId != ComUtility.DefaultInt32)
{
sqlText.AppendFormat(@" AND AI.[AicID] = {0}", nodeId);
}
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int) };
parms[0].Value = lscId;
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText.ToString(), parms)) {
while (rdr.Read())
{
var alarm = new TrendAlarmInfo();
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.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.DevTypeID = ComUtility.DBNullInt32Handler(rdr["DevTypeID"]);
alarm.DevTypeName = ComUtility.DBNullStringHandler(rdr["DevTypeName"]);
alarm.AlarmType = ComUtility.DBNullStringHandler(rdr["AlarmType"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.StartValue = ComUtility.DBNullFloatHandler(rdr["StartValue"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.DiffValue = ComUtility.DBNullFloatHandler(rdr["DiffValue"]);
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>
/// Method to get history load alarms
/// </summary>
public List <LoadAlarmInfo> GetHisLoadAlarms(int lscId, string lscName, string area1Name, string area2Name, string area3Name, string staName, int devId, 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("@DevID", 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 (devId == ComUtility.DefaultInt32)
{
parms[5].Value = DBNull.Value;
}
else
{
parms[5].Value = devId;
}
if (beginTime == ComUtility.DefaultDateTime)
{
parms[6].Value = DBNull.Value;
}
else
{
parms[6].Value = beginTime;
}
if (endTime == ComUtility.DefaultDateTime)
{
parms[7].Value = DBNull.Value;
}
else
{
parms[7].Value = endTime;
}
if (confirmBeginTime == ComUtility.DefaultDateTime)
{
parms[8].Value = DBNull.Value;
}
else
{
parms[8].Value = confirmBeginTime;
}
if (confirmEndTime == ComUtility.DefaultDateTime)
{
parms[9].Value = DBNull.Value;
}
else
{
parms[9].Value = confirmEndTime;
}
if (confirmName == ComUtility.DefaultString)
{
parms[10].Value = DBNull.Value;
}
else
{
parms[10].Value = confirmName;
}
if (endBeginTime == ComUtility.DefaultDateTime)
{
parms[11].Value = DBNull.Value;
}
else
{
parms[11].Value = endBeginTime;
}
if (endEndTime == ComUtility.DefaultDateTime)
{
parms[12].Value = DBNull.Value;
}
else
{
parms[12].Value = endEndTime;
}
if (endName == ComUtility.DefaultString)
{
parms[13].Value = DBNull.Value;
}
else
{
parms[13].Value = endName;
}
var alarms = new List <LoadAlarmInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_PREALARM_GETHISLOADALARMS, parms)) {
while (rdr.Read())
{
var alarm = new LoadAlarmInfo();
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.DevID = ComUtility.DBNullInt32Handler(rdr["DevID"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.DevTypeID = ComUtility.DBNullInt32Handler(rdr["DevTypeID"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.RateValue = ComUtility.DBNullFloatHandler(rdr["RateValue"]);
alarm.LoadValue = ComUtility.DBNullFloatHandler(rdr["LoadValue"]);
alarm.LoadPercent = ComUtility.DBNullFloatHandler(rdr["LoadPercent"]);
alarm.RightPercent = ComUtility.DBNullFloatHandler(rdr["RightPercent"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
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);
}