Alarms

The Alarm result set is used for notifying clients about alarms. The result set alone will not create the alarm so the alarm record has to be created manually in the table before the result set is issued.

Furthermore, the alarm has to be started and then ended separately for the alarm result set to work (for example, the alarm must be opened by issuing a result set with a Null End Time and then closed by issuing a result set with the End Time filled out).

Order	Field Name	Values/Table References
0	Result Set Type	6
1	Update Type	0 = Pre-Update 1 = Post-Update
2	Transaction Number
3	Alarm Id	Alarms.Alarm_Id
4	Alarm Template Data Id	Alarm_Template_Var_Data.ATD_Id
5	Start Time	Alarms.Start_Time
6	End Time	Alarms.End_Time
7	Duration	Alarms.Duration
8	Acknowledged	Alarms.Ack
9	Acknowledged Timestamp	Alarms.Ack_On
10	Acknowledged By	Alarms.Ack_By
11	Starting Value	Alarms.Start_Result
12	Ending Value	Alarms.End_Result
13	Minimum Value	Alarms.Min_Result
14	Maximum Value	Alarms.Max_Result
15	Cause 1	Alarms.Cause1 Event_Reasons.Reason_Id
16	Cause 2	Alarms.Cause2 Event_Reasons.Reason_Id
17	Cause 3	Alarms.Cause3 Event_Reasons.Reason_Id
18	Cause 4	Alarms.Cause4 Event_Reasons.Reason_Id
19	Cause Comment Id	Alarms.Cause_Comment_Id Comments.Comment_Id
20	Action 1	Alarms.Action1 Event_Reasons.Reason_Id
21	Action 2	Alarms.Action1 Event_Reasons.Reason_Id
22	Action 3	Alarms.Action1 Event_Reasons.Reason_Id
23	Action 4	Alarms.Action1 Event_Reasons.Reason_Id
24	Action Comment Id	Alarms.Action_Comment_Id Comments.Comment_Id
25	Research User Id	Alarms.Research_User_Id Users.User_Id
26	Research Status Id	Alarms.Research_Status_Id
27	Research Open Date	Alarms.Research_Open_Date
28	Research Close Date	Alarms.Research_Close_Date
29	Research Comment Id	Alarms.Research_Comment_Id Comments.Comment_Id
30	Source PU Id	Alarms.Source_PU_Id Prod_Units.PU_Id
31	Alarm Type Id	Alarms.Alarm_Type_Id Alarm_Types.Alarm_Type_Id
32	Key Id	Variables.Var_Id Alarm_Template_Var_Data.Var_Id
33	Alarm Description	Alarms.Alarm_Desc
34	Transaction Type	1 = Add 2 = Update 3 = Delete
35	Template Variable Comment Id	Alarm_Templates.Comment_Id Comments.Comment_Id
36	Alarm Priority Id	Alarm_Templates.AP_Id Alarm_Priorities.AP_Id
37	Alarm Template Id	Alarm_Templates.AT_Id
38	Variable Comment Id	Variables.Comment_Id Comments.Comment_Id
39	Cutoff	Alarms.Cutoff

Example


DECLARE	@TimeStamp	datetime, 
@AlarmId		         int,
@ATDId	                 int,
@AlarmTypeId	           int,
@ATId	                  int,
@ATDesc	                varchar(50),
@VarId	                 int,
@VarDesc	               varchar(50),
@AlarmCount	           int

DECLARE @Alarms TABLE (
ResultSetType	int DEFAULT 6,
PreUpdate	    int DEFAULT 0,
TransNum	     int DEFAULT 0,
AlarmId	      int NULL,
ATDId	        int NULL,
StartTime	    varchar(50) NULL,
EndTime	      varchar(50) NULL,
Duration	     float NULL,
Ack	          bit DEFAULT 0,
AckOn	        varchar(50) NULL,
AckBy	        int NULL,
StartResult	varchar(50) NULL, 
EndResult		varchar(50) NULL,
MinResult	varchar(50) NULL,
MaxResult	varchar(50) NULL,
Cause1	int NULL,
Cause2	int NULL,
Cause3	int NULL,
Cause4	int NULL,
CauseCommentId int NULL,
Action1	 int NULL,
Action2	 int NULL,
Action3	 int NULL,
Action4	 int NULL,
ActionCommentId      int NULL,
ResearchUserId	int NULL,
ResearchStatusId    int NULL,
ResearchOpenDate    varchar(50) NULL,
ResearchCloseDate   varchar(50) NULL,
ResearchCommentId   int NULL,
SourcePUId	   int NULL,
AlarmTypeId	  int NULL,
KeyId	        int NULL,
AlarmDesc	    char(50),
TransType	    int NULL,
TemplateVariableCommentId  int NULL,
APId	                 int NULL,
ATId	                 int NULL,
VarCommentId	         int NULL,
Cutoff	               tinyint NULL)

SELECT	@ATId	= AT_Id,
            @AlarmTypeId	= Alarm_Type_Id 
FROM Alarm_Templates
WHERE AT_Desc = @ATDesc

SELECT	@VarId	= Var_Id 
FROM Variables
WHERE	PU_Id = @PUId
           AND Var_Desc = @VarDesc

SELECT	@ATDId = ATD_Id
FROM Alarm_Template_Var_Data 
WHERE	Var_Id = @VarId
           AND AT_Id = @ATId

IF	@VarId IS NOT NULL 
         AND @ATId IS NOT NULL       
         AND @ATDId IS NOT NULL 
BEGIN
SELECT @AlarmCount = count(Alarm_Id) + 1 
FROM Alarms
WHERE	ATD_Id = @ATDId
           AND Key_Id = @VarId
           AND Start_Time = @TimeStamp

INSERT Alarms (	ATD_Id, 
                     Start_Time, 
                     Start_Result, 
                     Alarm_Type_Id, 
                     Key_Id, 
                     Alarm_Desc, 
                     User_Id )
VALUES (	@ATDId,
              @TimeStamp, 
              @AlarmCount, 
              @AlarmTypeId, 
              @VarId, 
              @Message, 
              @UserId)
SELECT @AlarmId = @@Identity

INSERT @Alarms (	PreUpdate,
                      TransNum, 
                      AlarmId, 
                      ATDId,
                      StartTime, 
                      EndTime, 
                      Duration, 
                      Ack, 
                      AckOn, 
                      AckBy, 
                      StartResult, 
                      EndResult,

SELECT	0,
            0,
            a.Alarm_Id, 
            a.ATD_Id, 
            a.Start_Time, 
            a.End_Time, 
            a.Duration, 
            a.Ack, 
            a.Ack_On, 
            a.Ack_By, 
            a.Start_Result, 
            a.End_Result, 
            a.Min_Result, 
            a.Max_Result, 
            a.Cause1, 
            a.Cause2, 
            a.Cause3, 
            a.Cause4,
            a.Cause_Comment_Id, 
            a.Action1,
            a.Action2, 
            a.Action3, 
            a.Action4,
            a.Action_Comment_Id, 
            a.Research_User_Id, 
            a.Research_Status_Id, 
            a.Research_Open_Date, 
            a.Research_Close_Date, 
            a.Research_Comment_Id, 
            a.Source_PU_Id, 
            a.Alarm_Type_Id, 
            a.Key_Id,
            a.Alarm_Desc, 
            1,
            d.Comment_Id, 
            t.AP_Id, 
            d.AT_Id, 
           v.Comment_Id, 
           0
FROM Alarms a
INNER JOIN Variables v ON a.Key_Id = v.Var_Id
 
INNER JOIN Alarm_Template_Var_Data d ON a.ATD_Id = d.ATD_Id 
INNER JOIN Alarm_Templates t ON d.AT_Id = t.AT_Id
WHERE a.Alarm_Id = @AlarmId

UPDATE Alarms
SET End_Time = dateadd(minute, 1, @TimeStamp) 
WHERE Alarm_Id = @AlarmId
INSERT @Alarms (	PreUpdate,
                      TransNum, 
                      AlarmId, 
                      ATDId,
                      StartTime, 
                      EndTime, 
                      Duration, 
                      Ack, 
                      AckOn, 
                      AckBy, 
                      StartResult, 
                      EndResult, 
                      MinResult, 
                      MaxResult, 
                      Cause1, 
                      Cause2, 
                      Cause3, 
                      Cause4,
                      CauseCommentId, 
                      Action1,
                      Action2, 
                      Action3, 
                      Action4,
                      ActionCommentId, 
                      ResearchUserId, 
                      ResearchStatusId, 
                      ResearchOpenDate, 
                      ResearchCloseDate, 
                      ResearchCommentId, 
                      SourcePUId, 
                      AlarmTypeId,
                      KeyId, 
                      AlarmDesc, 
                      TransType,
                      TemplateVariableCommentId, 
                      APId,
                      ATId,
                      VarCommentId, 
                      Cutoff)
SELECT	0,
            0,
            a.Alarm_Id, 
            a.ATD_Id, 
            a.Start_Time, 
            a.End_Time, 
            a.Duration, 
            a.Ack, 
            a.Ack_On, 
            a.Ack_By, 
            a.Start_Result, 
            a.End_Result, 
            a.Min_Result, 
            a.Max_Result, 
            a.Cause1, 
            a.Cause2, 
            a.Cause3, 
            a.Cause4,
            a.Cause_Comment_Id, 
            a.Action1,
            a.Action2,
            a.Action3, 
            a.Action4,
            a.Action_Comment_Id, 
            a.Research_User_Id, 
            a.Research_Status_Id, 
            a.Research_Open_Date, 
            a.Research_Close_Date, 
            a.Research_Comment_Id, 
            a.Source_PU_Id, 
            a.Alarm_Type_Id, 
            a.Key_Id,
            a.Alarm_Desc, 2,
            d.Comment_Id, 
            t.AP_Id, 
            d.AT_Id, 
            v.Comment_Id, 
            0
FROM Alarms a
INNER JOIN Variables v ON a.Key_Id = v.Var_Id
INNER JOIN Alarm_Template_Var_Data d ON a.ATD_Id = d.ATD_Id 
INNER JOIN Alarm_Templates t ON d.AT_Id = t.AT_Id
WHERE a.Alarm_Id = @AlarmId

SELECT	ResultSetType, 
             PreUpdate, 
             TransNum, 
             AlarmId, 
             ATDId,
             StartTime, 
             EndTime, 
             Duration, 
             Ack, 
             AckOn, 
             AckBy, 
             StartResult, 
             EndResult, 
             MinResult, 
             MaxResult, 
             Cause1, 
             Cause2, 
             Cause3, 
             Cause4,
             CauseCommentId, 
             Action1,
             Action2, 
             Action3, 
             Action4,
             ActionCommentId, 
             ResearchUserId, 
             ResearchStatusId, 
             ResearchOpenDate, 
             ResearchCloseDate, 
             ResearchCommentId, 
             SourcePUId, 
             AlarmTypeId,
             KeyId, 
             AlarmDesc, 
             TransType,
             TemplateVariableCommentId, 
             APId,
             ATId,
             VarCommentId, 
             Cutoff
FROM @Alarms 
END