A method of implementing multi-conditional arbitrary combination blacklist in SQL

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