--- 刪除 trigger drop trigger tt_insert_record; drop trigger tt_update_record; # udpate record drop trigger tt_before_facetvalue; drop trigger tt_insert_faceValue; # facetvalue ---1 --- trigger 建立-------------------------- DELIMITER $$ create trigger tt_insert_record after insert on alarmorion_orionalarmrecord FOR EACH ROW begin INSERT INTO `err_main`(`id`, `timestamp`, `datestamp`, `uuidHash`, `uuid`, `isOpen`, `sourceState`, `ackState`, `ackRequired`, `alarmClass`, `priority`, `normalTime`, `ackTime`, `userAccount`, `alarmTransition`, `lastUpdate`) VALUES (new.id, new.`timestamp`, new.`datestamp`, new.`uuidHash`, new.`uuid`, new.`isOpen`, new.`sourceState`, new.`ackState`, new.`ackRequired`, new.`alarmClass`, new.`priority`, new.`normalTime`, new.`ackTime`, new.`userAccount`, new.`alarmTransition`, new.`lastUpdate`); end$$ DELIMITER ; ---2 -------------------------------------------- DELIMITER $$ create trigger tt_update_record after update on alarmorion_orionalarmrecord FOR EACH ROW begin DECLARE _siteID varchar(20); DECLARE _devID varchar(20); UPDATE`err_main` SET `timestamp` = new.`timestamp`, `datestamp` = new.`datestamp`, `uuidHash` = new.`uuidHash`, `uuid` = new.`uuid`, `isOpen` = new.`isOpen`, `sourceState` = new.`sourceState`, `ackState` = new.`ackState`, `ackRequired` = new.`ackRequired`, `alarmClass` = new.`alarmClass`, `priority` = new.`priority`, `normalTime` = new.`normalTime`, `ackTime` = new.`ackTime`, `userAccount` = new.`userAccount`, `alarmTransition` = new.`alarmTransition`, `lastUpdate` = new.`lastUpdate` WHERE `id` = new.`id`; # 2.卡片紅綠燈 -- 只做恢復，(正常-->異常)在 faceValue # v2 # 1 Inverter # 2 InverterkWh0 # 3 DUST # 4 Network # 5 FIC # SourceState = 1 異常狀態, 0 異常復歸 # 燈號 HealthStatus: 1:設備正常 Green, 2.設備異常 Yellow , 3:設備斷線 Red # update 主表燈號 --------------- 主要作為恢復使用 ---------------------------- select site_id into _siteID from err_main where id = new.`id`; IF(_siteID is not null) THEN update power_station a join ( select site_id, case when (sourceState = 1 and alarmClass = 4) then 3 # Network 控制盒斷線 - 紅燈 when (sourceState = 1 and alarmClass != 4) then 2 # inv, sensor 異常 黃燈 when sourceState = 0 then 1 end healthStatus from err_main where site_id = _siteID group by site_id, alarmClass, sourceState order by 2 desc # 以燈號嚴重性為主要顯示 limit 1 ) b on a.`code` = b.`site_id` set a.HealthStatus = b.healthStatus; end if; #end; # update 紅綠燈 for Inverter update inv_status set `status` = case new.sourceState when 1 then 2 # 異常黃燈 when 0 then 1 end # 異常復歸 where inverterid = _devID; end$$ DELIMITER ; ----------3 ------------------------------------------------------ DROP TRIGGER IF EXISTS tt_before_facetvalue; DELIMITER $$ create trigger tt_before_facetvalue before insert on alarmorion_orionalarmfacetvalue FOR EACH ROW begin set new.errmsg = unicode_decode(new.`value`); IF (NEW.facetName = 8) THEN # 8 異常設備 set new.device_id = SUBSTRING_INDEX(new.`value`,'S', -1); set new.site_id = substring(new.device_id , 1, 9); end if; end$$ DELIMITER ; ---4 ------------------------------------------------------------- DROP TRIGGER IF EXISTS tt_insert_faceValue; DELIMITER $$ create trigger tt_insert_faceValue after insert on alarmorion_orionalarmfacetvalue FOR EACH ROW begin # 022020001 s:S022020001010011 --> 022020001010011 DECLARE _siteID varchar(20); DECLARE _devID varchar(20); DECLARE _msg varchar(30); IF (NEW.facetName = 4 ) THEN # 4 異常原始值 #insert into ts_facevalue(alarm_no, facename, errvalue, created_time) value(NEW.alarm, NEW.facetname, NEW.`value`, now()); update err_main set err_valuekind = left(new.`value`, 1), errValue = SUBSTRING_INDEX( # e:7@{Off=1,... SUBSTRING_INDEX(new.`value`, '@', 1) # @前面所有string --> e:7 , ':', -1) # : 後面所有string --> 7 where id = new.`alarm`; ELSEIF (NEW.facetName = 8) THEN # 8 異常設備 select SUBSTRING_INDEX(SUBSTRING_INDEX(new.`value`, ':', -1) # : 後面所有string ,'S', -1) into _devID; # S 後面所有string set _siteID = substring(_devID , 1, 9); #1. 記錄 異常主表.site_id, 設備編號, 設備廠牌 update err_main set errDevice = _devID, site_id = _siteID, errDeviceBrand = ( select brand from v_company_inv where INVERTERID = _devID ), errDeviceModel = ( select Model from v_company_inv where INVERTERID = _devID ) where id = new.`alarm`; # 2.卡片紅綠燈 -- 新增異常只考慮最新的異常 不考慮已存在的其他異常 #if (_siteID is not null or len(_siteID) > 0) then # 燈號 HealthStatus: 1:設備正常 Green 2:設備異常 Yellow 3.設備斷線 Red update power_station a join err_main b on a.`code` = b.site_id set a.HealthStatus = case when b.alarmClass = 4 then 3 # Network 控制盒斷線 - 紅燈 when b.alarmClass != 4 then 2 end # inv, sensor 異常 黃燈 where b.id = new.`alarm`; #select * from power_station a join err_main b on a.`code` = b.site_id #end; # 3. sensor、inverter 設備狀態 # 3.1 inverter alarmorion_orionalarmrecord update inv_status a set `status` = 2, # 有 insert 一定是異常，復歸在主表Record alarmID = new.`alarm` where inverterid = _devID ; #log 8 -- INSERT INTO `log_trigger`(`site_id`, `device_id`, `HealthStatus`, alarm_id, `triggerFrom`) -- VALUES ( _siteID, _devID, null, new.`alarm`, 'facetvalue8'); end if; end$$ DELIMITER; ------------------------------------------------------------------------------ DROP FUNCTION IF EXISTS unicode_decode; DELIMITER $$ CREATE FUNCTION unicode_decode(content text) RETURNS text BEGIN DECLARE code1,code2 varchar(20); DECLARE n_index,s_index smallint unsigned default 0; DECLARE result,tmp_txt text; DECLARE temp varchar(1); SET s_index=LOCATE('$u', content,1); set result =''; while s_index>0 DO set code1 = conv(substring(content,s_index+2,2),16,10); set code2 = conv(substring(content,s_index+4,2),16,10); set temp = convert(char(code1,code2) USING 'ucs2'); set tmp_txt = substring(content,n_index+1,s_index - (n_index+1)); set result = concat(result,tmp_txt,temp); set n_index = s_index+5; set s_index = LOCATE("$u", content, s_index+1); END while ; set tmp_txt = substring(content,n_index+1); set result = concat(result,tmp_txt); RETURN result; END $$ DROP FUNCTION IF EXISTS unicodeNum_decode; DELIMITER $$ CREATE FUNCTION unicodeNum_decode(content text) RETURNS text begin -- DECLARE i INT DEFAULT 1; -- DECLARE v_char VARCHAR(1); DECLARE result VARCHAR(255) DEFAULT ''; -- -- WHILE (i <= LENGTH(content) ) DO -- -- SET v_char = SUBSTR(content,i,1); -- IF v_char REGEXP '^[A-Za-z0-9 ]+$' THEN #alphanumeric -- -- SET v_parseStr = CONCAT(v_parseStr,v_char); -- -- END IF; -- SET i = i + 1; -- END WHILE; select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( SUBSTRING_INDEX(content, ':', -1) ,'$20',' '),'$2d','-'),'$2f','/'),'$28','('),'$29',')'),'$7b','{'), '$7d','}'),'$25','%'),'$40','@'),'$2e','.'),'$3a',':') into result; RETURN trim(result); END