历史告警导出
Select
OBJID as FSUID,
OBJNAME as FSU名称,
SUBOBJID as 设备ID,
ALARMLEVEL as 告警等级,
ALARMNAME as 告警名称,
DETAILINFO as 告警信息,
INSERTTIME as 告警开始时间,
CLEARSYSTEMTIME as 告警清除时间,
FAULTID as 工单号,
CLEARCAUSE as 告警清除方式,
STATIONID as 站址系统编码,
STATIONSTATUS as 站址状态,
MID as 信号量
from
itower.V_IF_PROV_ALARM_JSFGS
WHERE
CLEARSYSTEMTIME >= TO_DATE('2022-03-01 00:00:00','yyyy-MM-dd HH24:MI:ss') AND CLEARSYSTEMTIME < TO_DATE('2022-04-01 00:00:00','yyyy-MM-dd HH24:MI:ss')
历史工单导出-1
Select
*
from
itower.V_IF_PROV_bill_hbase_jsfgs
WHERE (INSERTDATE >= to_date('2022-03-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) AND (INSERTDATE < to_date('2022-05-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS'))历史工单导出-2
Select
BILLSN AS 故障工单号,
CREATETIME AS 受理时间,
ACCEPT_TIME AS 接单时间,
REVERTTIME AS 回单时间,
FAULTSRC AS 故障来源,
SIGNALNAME AS 告警名称,
SITECODE AS 运维ID,
SITENAME AS 站址名称,
ALARMBEGINTIME AS 告警发生时间,
ALARMENDTIME AS 告警清除时间,
SIGNDATE AS 签到时间,
IS_POWERBILL AS 是否是发电工单,
ISUPSTATION AS 是否上站,
HASTEN AS 是否催办,
ACCEPT_TIMEOUT AS 接单是否超时,
ISREVERTTIMEOUT AS 回单是否超时,
INSERTDATE AS 数据插入时间
from
itower.V_IF_PROV_bill_hbase_jsfgs
WHERE (INSERTDATE >= to_date('2022-03-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) AND (INSERTDATE <= to_date('2022-05-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS'))历史工单导出-2-MYSQL
Select
BILLSN AS 故障工单号,
CREATETIME AS 受理时间,
ACCEPT_TIME AS 接单时间,
REVERTTIME AS 回单时间,
FAULTSRC AS 故障来源,
SIGNALNAME AS 告警名称,
SITECODE AS 运维ID,
SITENAME AS 站址名称,
ALARMBEGINTIME AS 告警发生时间,
ALARMENDTIME AS 告警清除时间,
SIGNDATE AS 签到时间,
IS_POWERBILL AS 是否是发电工单,
ISUPSTATION AS 是否上站,
HASTEN AS 是否催办,
ACCEPT_TIMEOUT AS 接单是否超时,
ISREVERTTIMEOUT AS 回单是否超时,
INSERTDATE AS 数据插入时间
from
itower.V_IF_PROV_bill_hbase_jsfgs
WHERE INSERTDATE >= '2022-03-01 00:00:00' AND INSERTDATE <= '2022-05-01 00:00:00'
最新进展方式1
SELECT * FROM
( SELECT
ROW_NUMBER () OVER(PARTITION BY BILL_ID ORDER BY OPERATE_END_TIME DESC ) rn,
BILL_ID AS 工单流水号,
OPERATE_END_TIME AS 操作时间,
DEAL_INFO AS 操作内容
FROM
ITOWER.v_if_pub_bill_action_jsfgs
WHERE
ACTION_TYPE IN ('FEEDBACK','ADDREMARK','HASTEN') )
WHERE rn = 1
查询当前停电站址经纬度
SELECT
DISTINCT b.DEVICEID,
NAME,
BAIDUX,
BAIDUY
FROM
itower.V_IF_PROV_ALARMAT_JSFGS a,
itower.V_IF_PROV_STATION_JSFGS b
WHERE
a.STATIONID = b.id AND
STATIONSTATUS IN (2,7,8) AND a.MID != '0418011001'
AND a.ALARMNAME LIKE '%停电%'
查询当前FSU清单
SELECT
fsu.FSUID,
fsu.AREAID AS 系统编码,
zhan.DEVICEID AS 站址编码,
zhan.CODE AS 运维ID,
zhan.NAME AS 站址名称,
zhan.STATUS AS 站址状态,
qu.PARENTNAME AS 地市,
fsu.INNERIP AS FSUIP,
fsu.REGISTTIME AS 注册时间,
fsu.OUTLINETIME AS 离线时间,
fsu.NETWORKTYPE AS 网络模式,
fsu.OPENTIME AS 最近开门时间,
fsu.OPENRESULT AS 最近开门结果,
(
CASE
fsu.FACTORYNAME
WHEN 'JSYAAO' THEN '亚奥'
WHEN 'JSYAAAO' THEN '亚奥'
WHEN 'YA' THEN '亚奥'
WHEN 'TDYY' THEN '义益钛迪'
WHEN 'YYTD' THEN '义益钛迪'
WHEN 'ZXLW' THEN '力维'
WHEN 'LW' THEN '力维'
WHEN 'DTYD' THEN '大唐移动'
WHEN 'AMS' THEN '维谛'
WHEN 'HW' THEN '华为'
WHEN '华为' THEN '华为'
WHEN 'ZGTT' THEN '中国铁塔'
WHEN 'DLY' THEN '动力源'
WHEN 'KDCT' THEN '康大诚泰'
WHEN 'SAIERCOM' THEN '赛尔'
ELSE '其他'
END
) AS FSU软件厂家,
(
CASE
fsu.REGISTSTATUS
WHEN '1' THEN '在线'
WHEN '0' THEN '离线'
ELSE '其他'
END
) AS 状态,
(
CASE
fsu.OPERATIONTYPE
WHEN 'LT' THEN '联通'
WHEN 'YD' THEN '移动'
WHEN 'DX' THEN '电信'
ELSE '其他'
END
) AS 运营商,
(
CASE
fsu.FSUCLASS
WHEN 'INTSTAN' THEN '标准一体'
WHEN 'DISSTAN' THEN '标准分体'
WHEN 'IHIEXTER' THEN 'I型高压室外'
WHEN 'IIHIEXTER' THEN 'II型高压室外'
WHEN 'ILOEXTER' THEN 'I型低压室外'
WHEN 'IILOEXTER' THEN 'II型低压室外'
WHEN 'MICSTA' THEN '微站'
ELSE '其他'
END
) AS FSU应用类型
FROM
ITOWER.V_IF_PROV_FSU_JSFGS fsu
LEFT JOIN itower.V_IF_PROV_STATION_JSFGS zhan ON fsu.AREAID = zhan.ID
LEFT JOIN itower.BAF_ORG_COUNTRY qu ON fsu.COUNTYID = qu.ORGCODE
WHERE
zhan.STATUS IN ('2','8')
