drop view if exists POINT_FES_FAT_CHECK_EXPORT;
CREATE VIEW POINT_FES_FAT_CHECK_EXPORT
(站址,PCS系统记录号,标签名称,描述,输入输出,数据类型,SCADA地址,RAWDATA,工程值,级别I,级别II,级别III,归档信息事件记录,调试情况,备注)
AS
SELECT PSDB_FES.ST_ADDR AS 站址, POI_UPD.PSKEY::varchar(255) AS PCS系统记录号, POI_UPD.PSID AS 标签名称, POI_UPD.PSDESCR AS 描述, POI_UPD.DATA_STREAM AS 输入输出, POI_UPD.DATA_TYPE AS 数据类型,
CASE PSDB_FES.PROTOCOL_NAME
WHEN 'cip' THEN POI_UPD.CIP_NAME
WHEN 'modbus_tcp' THEN POI_UPD.EXTENDID::lvarchar(8000)
WHEN 'modbus_rtu' THEN POI_UPD.EXTENDID::lvarchar(8000)
ELSE POI_UPD.INDEX_NO::lvarchar(8000)
END AS SCADA地址, POI_UPD.BEGIN_VALUE AS RAWDATA, POI_UPD.RESULT_VALUE AS 工程值, POI_UPD.ALARMI AS 级别I, POI_UPD.ALARMII AS 级别II, POI_UPD.ALARMIII AS 级别III, POI_UPD.HISARCHIVE AS 归档信息事件记录, NULL AS 调试情况, NULL AS 备注
FROM ( SELECT 1 AS POS, POI.KEY AS PSKEY, POI.ID AS PSID, POI.DESCR AS PSDESCR, UPD.INDEX_NO, UPD.EXTENDID, UPD.CIP_NAME, 'IN' AS DATA_STREAM, 'bool' AS DATA_TYPE, NULL AS BEGIN_VALUE, NULL AS RESULT_VALUE,
CASE POI.ALARMID
WHEN 'POINT_A' THEN '√'
ELSE NULL
END AS ALARMI,
CASE POI.ALARMID
WHEN 'POINT_B' THEN '√'
ELSE NULL
END AS ALARMII,
CASE POI.ALARMID
WHEN 'POINT_C' THEN '√'
ELSE NULL
END AS ALARMIII,
CASE
WHEN BITAND(POI.QUALITY, 262144::decimal) = 262144::decimal THEN 'Y'
WHEN POI.ALARMID = 'POINT_D' THEN 'Y'
ELSE NULL
END AS HISARCHIVE, UPD.LINKNO, UPD.STNO
FROM SCM_PSDB_ONLINE.POINT POI
JOIN SCM_FES_ONLINE.UPDI UPD ON POI.KEY = UPD.PSID
UNION ALL
SELECT 2 AS POS, POI.KEY AS PSKEY, POI.ID AS PSID, POI.DESCR AS PSDESCR, DC.INDEX_NO, DC.EXTENDID, DC.CIP_NAME, 'OUT' AS DATA_STREAM, 'bool' AS DATA_TYPE, NULL AS BEGIN_VALUE, NULL AS RESULT_VALUE,
CASE POI.ALARMID
WHEN 'POINT_A' THEN '√'
ELSE NULL
END AS ALARMI,
CASE POI.ALARMID
WHEN 'POINT_B' THEN '√'
ELSE NULL
END AS ALARMII,
CASE POI.ALARMID
WHEN 'POINT_C' THEN '√'
ELSE NULL
END AS ALARMIII,
CASE
WHEN BITAND(POI.QUALITY, 262144::decimal) = 262144::decimal THEN 'Y'
WHEN POI.ALARMID = 'POINT_D' THEN 'Y'
ELSE NULL
END AS HISARCHIVE, DC.LINKNO, DC.STNO
FROM SCM_PSDB_ONLINE.POINT POI
JOIN SCM_FES_ONLINE.DC DC ON POI.KEY = DC.PSID) POI_UPD
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 POI_UPD.STNO = PSDB_FES.STNO
ORDER BY PSDB_FES.LINEID, PSDB_FES.STID, PSDB_FES.ST_ADDR, POI_UPD.PSID, POI_UPD.POS;