Home

Script to enable default alarms for "Completeness" report:

INSERT INTO Alarm(
      NodeID,
      TagName,
      SignalID,
      AssociatedMeasurementID,
      Description,
      Severity,
      Operation,
      SetPoint,
      Tolerance,
      Delay,
      Hysteresis,
      LoadOrder,
      Enabled)
SELECT
    (SELECT ID FROM Node) AS NodeID,
    'AL-' + 
    (CASE WHEN Operation.Name = 'GreaterThan' THEN 'HIGH'
         WHEN Operation.Name = 'LessThan' THEN 'LOW'
         WHEN Operation.Name = 'Flatline' THEN 'FLATLINE'
         WHEN Operation.Name = 'Equal' THEN 'EXEMPT'
    END) + ':' + Measurement.PointTag AS TagName,
    Measurement.SignalID,
    NULL AS AssociatedMeasurementID,
    '' AS Description,
    CASE WHEN Operation.Name = 'Equal' THEN 0
         WHEN Operation.Name = 'Flatline' THEN 980
         ELSE 900
    END AS Severity,
    Operation.ID AS Operation,
    CASE WHEN Operation.Name = 'Equal' THEN 0
         WHEN Operation.Name = 'Flatline' THEN NULL
         WHEN Operation.Name = 'GreaterThan' THEN
            CASE WHEN SignalType.Acronym = 'IPHM' THEN 6000
                 WHEN SignalType.Acronym = 'IPHA' THEN 180
                 WHEN SignalType.Acronym = 'VPHM' THEN 750000
                 WHEN SignalType.Acronym = 'VPHA' THEN 180
                 WHEN SignalType.Acronym = 'FREQ' THEN 65
            END
         WHEN Operation.Name = 'LessThan' THEN
            CASE WHEN SignalType.Acronym = 'IPHM' THEN 0
                 WHEN SignalType.Acronym = 'IPHA' THEN -180
                 WHEN SignalType.Acronym = 'VPHM' THEN 0
                 WHEN SignalType.Acronym = 'VPHA' THEN -180
                 WHEN SignalType.Acronym = 'FREQ' THEN 55
            END
    END AS SetPoint,
    0 AS Tolerance,
    CASE WHEN Operation.Name = 'Flatline' THEN 10 ELSE 0 END AS Delay,
    0 AS Hysteresis,
    0 AS LoadOrder,
    1 AS Enabled
FROM
    Measurement JOIN
    SignalType ON Measurement.SignalTypeID = SignalType.ID CROSS JOIN
    (SELECT 12 AS ID, 'GreaterThan' AS Name UNION SELECT 22 AS ID, 'LessThan' AS Name UNION SELECT 3 AS ID, 'Flatline' AS Name UNION SELECT 1 AS ID, 'Equal' AS Name) AS Operation
WHERE
    SignalType.Acronym IN ('IPHM', 'VPHM', 'FREQ') OR
    (SignalType.Acronym IN ('IPHA', 'VPHA') AND Operation.Name <> 'Equal') OR
    (SignalType.Acronym = 'ALOG' AND Operation.Name = 'Flatline') OR
    (SignalType.Acronym = 'ALOG' AND Operation.Name = 'Equal')