In a recent project, in the case of automatic voice supervision of the fault ticket, it is necessary to not supervise the work orders under certain combination conditions at a certain time. such as in real-time supervision, the alarm ID for all areas of the "ff--1057361341" work order, the daytime does not supervise, but at night to supervise, in the acceptance of the supervision, a designated area of the alarm ID "207-121-00-800316", day supervision, night without supervision.
In order to ensure the timeliness of the supervision, we use a task-driven approach. That is, build the task, according to the task to determine the use of the mode of supervision. Such as:
So in order to achieve a certain period of time on a combination of work orders not to supervise the function, we only need to generate a supervisor task, one after another to determine whether to meet the conditions a,b,c, such as. But every time we add a condition, we have to modify and publish the system. At the same time, when judging many conditions, the efficiency is extremely low.
So, we think of the "blacklist", a certain combination of conditions, designed to "blacklist." We only need to determine whether this ticket is in the blacklist, if so, then do not supervise, such as. However, for different combination conditions, different blacklist tables need to be designed, and the system needs to be modified and published without adding a condition.
So, how to achieve a blacklist, can meet the different combinations of conditions of the change situation. We designed the following blacklist table blacklist.
Field |
Type |
Description |
f_id |
Int |
Auto numbering |
F_area |
Varchar (50) |
Zone Name |
F_classid |
Int |
Supervise category ID |
F_timeslot |
Int |
Supervising time period |
F_mark |
Varchar (100) |
Network Management Alarm ID |
F_grede |
Varchar (100) |
Processing level |
F_class1 |
Varchar (100) |
First Class classification |
F_class2 |
Varchar (100) |
Level Two classification |
F_class3 |
Varchar (100) |
Level Three classification |
We then provide a get_isdoblacklist function to determine if this worker's order exists in the blacklist table, and if 0 is present, return 1, the code is as follows:
--Create a function to determine the blacklistCreate function [MM].[dbo].[get_isdoblacklist](@Area varchar( -),--Area@ClassID int,--type number of the supervising@TimeSlot int,--execution time period, daytime (-23 points) is 1, night is 2;@Grade varchar( -),--Work Order level@Mark varchar( -),--Alarm ID@Class1 varchar( -),--First Class classification@Class2 varchar( -),--Level Two classification@Class3 varchar( -)--Level Three classification)/*function Description: Query the current need to supervise the task ticket is configured in the Blacklist table blacklist, if any, return, otherwise return creator: LDH creation time: 2016/6/30*/returns int asbegin Declare @rt int if not exists(SelectF_mark fromdbo. Blacklistwhere(F_area=@Area orF_area=' All') and(F_classid=@ClassID orF_classid=0) and(F_grade=@Grade orF_grade=' All') and(F_mark=@Mark orF_mark=' All') andF_timeslot=@TimeSlot and(F_class1=@Class1 orF_class1=' All') and(F_class2=@Class2 orF_class2=' All') and(F_CLASS3=@Class3 orF_class3=' All') ) begin Set @rt= 1 End Else begin Set @rt= 0 End return @rt End
View Code
In this way, we only need to set the records in the Blacklist, based on the values in the record to determine whether this field is enabled as a condition. For an int field, when set to 0 o'clock, all conditions are ignored, that is, the decision condition is omitted, otherwise, the value set to this field is used as a criterion. For a varchar-type field, all means that all rules are used, that is, the decision condition is ignored, otherwise, the value set to this field is used as a criterion. Of course, if we know that 0 and all values will not appear.
Let me verify our methods and ideas according to the following code:
1) Set blacklist record
--set up a blacklist recordINSERT into [IVR].[dbo].[t_r_eomsblacklist]([F_area],[F_classid],[F_timeslot], [F_grade],[F_mark],[F_class1],[F_class2],[F_CLASS3])Select ' All',2,2,' All','208-069-00-300008',' All',' All',' All'UnionSelect ' All',2,2,' All','208-121-00-800030',' All',' All',' All'UnionSelect ' All',2,2,' All','207-121-00-800316',' All',' All',' All'UnionSelect ' All',1,1,' All','ff--1057361341',' All',' All',' All'
View Code
2) Call method to determine whether the blacklist
if(IVR.dbo.Get_isDoBlackList (' One',2,2,'Two-level supervising','207-121-00-800316','a11a','a11a','123')=0)begin Print 'is the blacklist, need to filter, do not supervise'EndElsebegin Print 'not a blacklist, no filtering, no need to supervise'End*/
View Code
By setting a record, we can increase the criterion of a combination condition, so that the work orders under certain combination conditions are not supervise at a certain time.
If there is a better way, welcome to explore and guide. (Please do not reprint without permission.)
A method of implementing multi-conditional arbitrary combination blacklist in SQL