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

设计思路：每一个成功的遥控（返信成功）应当对应一个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;