drop view if exists ANALOG_FES_FAT_CHECK_EXPORT1;
CREATE
VIEW ANALOG_FES_FAT_CHECK_EXPORT1(
站址,
PCS系统记录号,
标签名称,
描述,
输入输出,
数据类型,
SCADA地址,
RAWDATA,
工程值,
单位,
级别I,
级别II,
级别III,
归档信息事件记录,
调试情况,
备注
) AS
SELECT
PSDB_FES.ST_ADDR AS 站址,
ANA_UPA.PSKEY::varchar(255) AS PCS系统记录号,
ANA_UPA.PSID AS 标签名称,
ANA_UPA.PSDESCR AS 描述,
ANA_UPA.DATA_STREAM AS 输入输出,
ANA_UPA.DATA_TYPE AS 数据类型,
CASE
PSDB_FES.PROTOCOL_NAME
WHEN 'cip' THEN ANA_UPA.CIP_NAME
WHEN 'modbus_tcp' THEN(
ANA_UPA.EXTENDID::varchar(255)
)
WHEN '104' THEN(
ANA_UPA.EXTENDID::varchar(255)
)
ELSE(
ANA_UPA.INDEX_NO::varchar(255)
)
END AS SCADA地址,
ANA_UPA.BEGIN_VALUE AS RAWDATA,
ANA_UPA.RESULT_VALUE AS 工程值,
ANA_UPA.UNITNAME AS 单位,
ANA_UPA.ALARMI AS 级别I,
ANA_UPA.ALARMII AS 级别II,
ANA_UPA.ALARMIII AS 级别III,
ANA_UPA.HISARCHIVE AS 归档信息事件记录,
NULL AS 调试情况,
NULL AS 备注
FROM
(
(
SELECT
1 AS POS,
ANA.KEY AS PSKEY,
ANA.ID AS PSID,
ANA.DESCR AS PSDESCR,
UPA.INDEX_NO,
UPA.EXTENDID,
UPA.CIP_NAME,
'IN' AS DATA_STREAM,
CASE
UPA.POINT_NO
WHEN 1 THEN 'bool'
WHEN 2 THEN 'int16'
WHEN 3 THEN 'usint16'
WHEN 4 THEN 'int32'
WHEN 5 THEN 'swap_int32'
WHEN 6 THEN 'float32'
WHEN 7 THEN 'swap_float32'
ELSE 'float'
END AS DATA_TYPE,
ANA.UNITNAME,
NULL AS BEGIN_VALUE,
NULL AS RESULT_VALUE,
CASE
ANA.ALARMID
WHEN 'ANALOG_A' THEN '√'
ELSE NULL
END AS ALARMI,
CASE
ANA.ALARMID
WHEN 'ANALOG_B' THEN '√'
ELSE NULL
END AS ALARMII,
CASE
ANA.ALARMID
WHEN 'ANALOG_C' THEN '√'
ELSE NULL
END AS ALARMIII,
CASE
BITAND(
ANA.QUALITY,
33554432
)
WHEN 33554432 THEN 'Y'
ELSE NULL
END AS HISARCHIVE,
UPA.LINKNO,
UPA.STNO
FROM
(
SCM_PSDB_ONLINE.ANALOG ANA
JOIN SCM_FES_ONLINE.UPAI UPA ON
(
(
ANA.KEY = UPA.PSID
)
)
)
UNION ALL SELECT
2 AS POS,
ANA.KEY AS PSKEY,
ANA.ID AS PSID,
ANA.DESCR AS PSDESCR,
SP.INDEX_NO,
SP.EXTENDID,
SP.CIP_NAME,
'OUT' AS DATA_STREAM,
CASE
SP.SP_TYPE
WHEN 0 THEN 'dnp_int16'
WHEN 1 THEN 'dnp_int32'
WHEN 2 THEN 'mod_int16'
WHEN 3 THEN 'mod_usint16'
WHEN 4 THEN 'mod_int32'
WHEN 5 THEN 'mod_swapint32'
WHEN 6 THEN 'mod_float'
WHEN 7 THEN 'mod_swapfloat'
WHEN 16 THEN 'cip_int16'
WHEN 32 THEN 'cip_短浮点'
WHEN 64 THEN 'cip_int32'
WHEN 20 THEN '104_归一化'
WHEN 21 THEN '104_标度化'
WHEN 22 THEN '104_短浮点'
ELSE 'ERROR'
END AS DATA_TYPE,
ANA.UNITNAME,
NULL AS BEGIN_VALUE,
NULL AS RESULT_VALUE,
CASE
ANA.ALARMID
WHEN 'ANALOG_A' THEN '√'
ELSE NULL
END AS ALARMI,
CASE
ANA.ALARMID
WHEN 'ANALOG_B' THEN '√'
ELSE NULL
END AS ALARMII,
CASE
ANA.ALARMID
WHEN 'ANALOG_C' THEN '√'
ELSE NULL
END AS ALARMIII,
CASE
BITAND(
ANA.QUALITY,
33554432
)
WHEN 33554432 THEN 'Y'
ELSE NULL
END AS HISARCHIVE,
SP.LINKNO,
SP.STNO
FROM
(
SCM_PSDB_ONLINE.ANALOG ANA
JOIN SCM_FES_ONLINE.SP SP ON
(
(
ANA.KEY = SP.PSID
)
)
)
) ANA_UPA
JOIN(
SELECT
PSDB.LINEID,
PSDB.LINEDESCR,
PSDB.STID,
PSDB.STDESCR,
FES.PROTOCOL_NAME,
FES.ST_ADDR,
FES.LINKNO,
FES.STNO
FROM
(
(
SELECT
ST.KEY AS STKEY,
ST.ID AS STID,
ST.DESCR AS STDESCR,
LINE.ID AS LINEID,
LINE.DESCR AS LINEDESCR
FROM
(
SCM_PSDB_ONLINE.ST ST
JOIN SCM_PSDB_ONLINE.LINE LINE ON
(
(
ST.FATHER_KEY = LINE.KEY
)
)
)
) PSDB
JOIN(
SELECT
LINK.KEY,
LINK.LINKNO,
LINK.PROTOCOL_NAME,
LINK.STATIONID,
STATION.ST_ADDR,
STATION.CIP_PATH,
LINK.IP_ADDR,
LINK.PORT,
STATION.STNO
FROM
(
(
SELECT
LINK.KEY,
LINK.LINKNO,
LINK.PROTOCOL_NAME,
ST_SRC.STATIONID,
LINK.IP_ADDR,
LINK.PORT
FROM
(
SCM_FES_ONLINE.STATION_SRC ST_SRC
JOIN(
SELECT
LINK.KEY,
LINK.LINKNO,
PROTO.PROTOCOL_NAME,
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
'1:' ||(
LINK.IP_ADDR
)
)
)
) || '|2:'
)
)
) ||(
LINK.IP_ADDR_2
)
)
)
) || '|3:'
)
)
) ||(
LINK.IP_ADDR_3
)
)
)
) || '|4:'
)
)
) ||(
LINK.IP_ADDR_4
)
)
) AS IP_ADDR,
(
(
(
(
(
(
(
'1:' ||(
LINK.PORTID
)
)|| '|2:'
)||(
LINK.PORTID_2
)
)|| '|3:'
)||(
LINK.PORTID_3
)
)|| '|4:'
)||(
LINK.PORTID_4
)
) AS PORT
FROM
(
SCM_FES_ONLINE.LINK LINK
JOIN SCM_FES_ONLINE.PROTO_TYPE PROTO ON
(
(
LINK.PROTOCOL = PROTO.KEY
)
)
)
) LINK ON
(
(
LINK.KEY = ST_SRC.LINKID
)
)
)
) LINK
JOIN SCM_FES_ONLINE.STATION STATION ON
(
(
LINK.KEY = STATION.FATHER_KEY
)
)
)
) FES ON
(
(
PSDB.STKEY = FES.STATIONID
)
)
)
) PSDB_FES ON
(
(
ANA_UPA.STNO = PSDB_FES.STNO
)
)
)
ORDER BY
PSDB_FES.LINEID,
PSDB_FES.STID,
PSDB_FES.ST_ADDR,
ANA_UPA.PSID,
ANA_UPA.POS;