【指标统计】根据遥控补全遥信

一时失言乱红尘 2023-06-17 10:52 9阅读 0赞

设计思路：每一个成功的遥控（返信成功）应当对应一个COS和SOE。如果返信成功时间前后5秒内，未找到同一测点相同状态的变位信息，则认为对应遥信已被误删。随即进行恢复（插入到scada\_event和scada\_event\_yx表中）。

金仓

CREATE or REPLACE PROCEDURE MAKEUP()   
    AS DECLARE
    	trigger_cur refcursor;
    	occur_time_var date;
    	milli_second_var int;
    	
    	time_0_var date;
    	time_1_var date;
    	cnt_var integer;
    	
    	soe_time_var date;
    	soe_msec_var int;
    	obj_id_var bigint;
    	dev_id_var bigint;
    	rtu_id_var bigint;
    	rtu_type_var integer;
    	comm_type_var integer;
    	manu_fac_var integer;
    	feeder_id_var bigint;
    	station_id_var bigint;
    	status_var bigint;
    	content_var varchar(1024);
    	pos_var  varchar(1024);
    	sql_var varchar(1024);
    BEGIN
    	delete from scada_event_yx where yk_of_yx = -123;
    	open trigger_cur for select occur_time,milli_second,obj_id,dev_id,rtu_id,rtu_type,comm_type,manu_fac,feeder_id,station_id,status,content from scada_event_yk where occur_time > '2019-08-31' and status = 17 order by occur_time;
    		LOOP
    			fetch trigger_cur into occur_time_var,milli_second_var,obj_id_var,dev_id_var,rtu_id_var,rtu_type_var,comm_type_var,manu_fac_var,feeder_id_var,station_id_var,status_var,content_var;
    			EXIT WHEN trigger_cur%NOTFOUND;
    			time_0_var = occur_time_var - 5 second;
    			time_1_var = occur_time_var + 5 second;
    			pos_var = substring(content_var, length(content_var)-3, 4);
    			content_var = substring(content_var, position(']' in content_var)+2, position(' 遥控' in content_var) - 1 - position(']' in content_var));
    			if TRIM(pos_var) = '分位]' then
    				content_var = concat(content_var,' 分闸');
    				status_var = 0;
    			else
    				content_var = concat(content_var,' 合闸');
    				status_var = 1;
    			end if;
    			
    			sql_var = 'select count(*) from scada_event_yx where occur_time > '''||time_0_var||''' and occur_time < '''||time_1_var||''' and obj_id= '||obj_id_var||' and status = '||status_var||' and alarm_type = 1';
    			execute immediate sql_var into cnt_var;
    			soe_time_var = occur_time_var - 2 second;
    			soe_msec_var = milli_second_var + 3;
    			if cnt_var = 0 then
    				sql_var = 'insert into scada_event_yx_1(occur_time,milli_second,obj_id,dev_id,rtu_id,rtu_type,comm_type,manu_fac,feeder_id,station_id,status,content,sysid,alarm_type,yk_of_yx,result) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,1,1,-123,0)';
    				execute immediate sql_var using occur_time_var,milli_second_var,obj_id_var,dev_id_var,rtu_id_var,rtu_type_var,comm_type_var,manu_fac_var,feeder_id_var,station_id_var,status_var,content_var;
    				sql_var = 'insert into scada_event_1(occur_time,milli_second,obj_id,dev_id,station_id,status,content,sysid,alarm_type,key_id_tag,tag_id) values($1,$2,$3,$4,$5,$6,$7,1,1,''-123'',-123)';
    				execute immediate sql_var using occur_time_var,milli_second_var,obj_id_var,dev_id_var,station_id_var,status_var,content_var;
    				content_var = concat(' ',content_var);
    				content_var = concat(soe_msec_var,content_var);
    				content_var = concat('.',content_var);
    				content_var = concat(soe_time_var,content_var);
    				content_var = concat('SOE时标:',content_var);
    				sql_var = 'insert into scada_event_yx_1(occur_time,milli_second,soe_time,soe_msecond,obj_id,dev_id,rtu_id,rtu_type,comm_type,manu_fac,feeder_id,station_id,status,content,sysid,alarm_type,yk_of_yx,result) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,1,3,-123,0)';
    				execute immediate sql_var using occur_time_var,milli_second_var,soe_time_var,soe_msec_var,obj_id_var,dev_id_var,rtu_id_var,rtu_type_var,comm_type_var,manu_fac_var,feeder_id_var,station_id_var,status_var,content_var;
    				sql_var = 'insert into scada_event_1(occur_time,milli_second,soe_time,soe_msecond,obj_id,dev_id,station_id,status,content,sysid,alarm_type,key_id_tag,tag_id) values($1,$2,$3,$4,$5,$6,$7,$8,$9,1,3,''-123'',-123)';
    				execute immediate sql_var using occur_time_var,milli_second_var,soe_time_var,soe_msec_var,obj_id_var,dev_id_var,station_id_var,status_var,content_var;
    			end if;
    		end LOOP;
    	close trigger_cur;
    END;