1 BEGIN
2 #Routine body goes here...
3
4 -- 现可以限制 卡口及号牌
5
6 -- //定义变量
7
8
9 declare insertedDatetime TIMESTAMP ;
10 declare licenseNumber varchar(50) default '' ;
11 declare pointNumber varchar(50) default '' ;
12
13 declare cursorSurveil CURSOR FOR select t.insertedDatetime, t.licenseNumber, t.pointNumber from t_surveil as t WHERE t.matchRuleIds is null ;
14 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET insertedDatetime = null;
15
16
17 DROP TEMPORARY TABLE IF EXISTS tmpLog ;
18
19 CREATE temporary table `tmpLog`( `insertedDatetime` timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
20 `log` VARCHAR(2000) not null ) ENGINE=MEMORY ;
21
22
23 INSERT tmpLog(log) VALUES ('以下是日志内容');
24
25 -- SELECT * from tmpLog ;
26
27 INSERT tmpLog(log) VALUES ( concat( '监控数据开始') );
28
29
30
31 -- 监控数据
32 OPEN cursorSurveil ;
33 FETCH cursorSurveil INTO insertedDatetime, licenseNumber , pointNumber ;
34 WHILE( insertedDatetime is not null) DO
35
36 -- SELECT 't_surveil' title, `insertedDatetime` ;
37
38 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, insertedDatetime = ' , insertedDatetime ) );
39 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, licenseNumber = ' , licenseNumber ) );
40 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, pointNumber = ' , pointNumber ) );
41
42 begin
43
44 -- 匹配的规则 ID
45 DECLARE matchRuleIds varchar(2000) DEFAULT '0' ;
46
47 declare ruleId INT DEFAULT 0 ;
48 declare cursorRule CURSOR FOR select id from t_rule as t WHERE t.activeStatus = 1 ;
49 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleId = null ;
50
51
52 INSERT tmpLog(log) VALUES ( concat( '规则开始') );
53
54 -- //打开游标
55 OPEN cursorRule;
56 -- //遍历游标
57 FETCH cursorRule INTO ruleId ;
58 -- //把游标查询出的 name 都加起并用 ; 号隔开
59 WHILE( ruleId is not null) DO
60 -- SELECT ruleId ;
61 -- SELECT 't_rule' title, ruleId ;
62
63 INSERT tmpLog(log) VALUES ( concat( 't_rule, ruleId = ' , ruleId ) );
64
65 -- 规则项
66 -- 规则项之间是 AND 的关系。即要全部成立才认为匹配成功
67
68 BEGIN
69
70 declare isThisItemMatch int default 0 ; -- 本项是否匹配项
71 DECLARE isAllItemMatch int default 1 ; -- 是否所有的项目都匹配？
72 DECLARE itemCount INT DEFAULT 0 ; -- 项数量。是否存在至少一个项？
73
74 declare ruleItemId INT DEFAULT 0 ;
75 DECLARE fieldName VARCHAR(50) ;
76
77 declare cursorRuleItem CURSOR FOR select id, t.fieldName from t_rule_item as t WHERE t.ruleId = ruleId ;
78 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleItemId = null;
79
80 INSERT tmpLog(log) VALUES ( concat( '规则项开始') );
81
82 OPEN cursorRuleItem ;
83 FETCH cursorRuleItem INTO ruleItemId, fieldName ;
84 WHILE( ruleItemId is not null) DO
85
86 -- SELECT 't_rule_item' title, ruleItemId ;
87
88
89 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, ruleItemId = ' , ruleItemId ) );
90
91 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, fieldName = ' , fieldName ) );
92
93 SET itemCount = itemCount + 1 ; -- 项目数
94
95 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, itemCount = ' , itemCount ) );
96
97 SET fieldName = CASE
98 WHEN fieldName = 'licenseNumber' THEN fieldName
99 WHEN fieldName = 'pointNumber' THEN fieldName
100 END ;
101
102
103 BEGIN
104
105 -- 规则项值
106 -- 规则项值之间是 OR 的关系，即有一条成立，即认为匹配成功。
107 -- 一但找到匹配，就退出本层游标
108
109 declare hasMatchedValue int default 0 ; -- 是否匹配值
110
111 declare ruleItemValueId INT DEFAULT 0 ;
112 declare comparisonType varchar(50) DEFAULT '' ;
113 declare itemValue varchar(200) DEFAULT '' ;
114 declare valueMinmum varchar(200) DEFAULT '' ;
115 declare valueMaximun varchar(200) DEFAULT '' ;
116
117 declare cursorRuleItemValue CURSOR FOR
118 select
119 t.id, t.comparisonType, t.itemValue, t.valueMinmum, t.valueMaximun
120 from
121 t_rule_item_value as t
122 WHERE
123 t.ruleItemId = ruleItemId ;
124
125 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleItemValueId = null;
126
127 -- SET hasMatchedValue = 0 ;
128
129 INSERT tmpLog(log) VALUES ( concat( '项值开始') );
130
131 OPEN cursorRuleItemValue ;
132 FETCH cursorRuleItemValue INTO ruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ;
133 WHILE( ruleItemValueId is not null ) DO
134
135 -- SELECT 't_rule_item_value' title, ruleItemValueId ;
136
137 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, ruleItemValueId = ' , ruleItemValueId ) );
138 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, comparisonType = ' , comparisonType ) );
139 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, itemValue = ' , itemValue ) );
140
141 -- 比较 - 等于
142 IF comparisonType = '等于' OR comparisonType = 'equal' then
143
144 INSERT tmpLog(log) VALUES ( concat( '等于' ) );
145
146
147 if fieldName = 'licenseNumber' then
148 IF licenseNumber = itemValue then
149 SET hasMatchedValue = 1 ;
150 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, 找到匹配值！！！ licenseNumber' ) );
151 END IF ;
152 end if ;
153
154 if fieldName = 'pointNumber' then
155 IF pointNumber = itemValue then
156 SET hasMatchedValue = 1 ;
157 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, 找到匹配值！！！ pointNumber' ) );
158 END IF ;
159 end if ;
160
161 END IF ;
162
163 -- 比较 - 区间
164 IF comparisonType = '区间' or comparisonType = 'between' then
165
166 if fieldName = 'licenseNumber' then
167 IF licenseNumber >= valueMinmum AND licenseNumber <= valueMaximun then
168 SET hasMatchedValue = 1 ;
169 END IF ;
170 end if ;
171
172 if fieldName = 'pointNumber' then
173 IF pointNumber >= valueMinmum AND pointNumber <= valueMaximun then
174 SET hasMatchedValue = 1 ;
175 END IF ;
176 end if ;
177
178 END IF ;
179
180 -- select hasMatchedValue ;
181
182 IF hasMatchedValue = 1 THEN
183 SET isThisItemMatch = 1 ; -- 本项匹配 ！！
184 SET ruleItemValueId = null ; -- 结束本层循环
185 INSERT tmpLog(log) VALUES ('存在匹配的项值，本项匹配，退出项值循环。');
186 ELSE
187 FETCH cursorRuleItemValue INTO ruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ;
188 end if ;
189
190 END WHILE ; -- 规则项值
191
192 CLOSE cursorRuleItemValue ;
193
194 -- SELECT 'rule item value over ' msg ;
195
196
197 INSERT tmpLog(log) VALUES ('项值结束');
198
199
200 END ;
201
202
203 IF isThisItemMatch = 1 THEN
204 FETCH cursorRuleItem INTO ruleItemId, fieldName ;
205 else
206 set isAllItemMatch = 0 ;
207 SET ruleItemId = null ;
208 end if ;
209
210 END WHILE ;
211
212 CLOSE cursorRuleItem ;
213
214 INSERT tmpLog(log) VALUES ('项结束');
215
216 -- ！！！！ 如果全部匹配 ！！！！
217 IF isAllItemMatch = 1 AND itemCount > 0 THEN
218 -- 这个规则符合 ！！！
219 set matchRuleIds = concat( matchRuleIds , ',' , ruleId ) ;
220 end if ;
221
222 END ;
223
224 -- SHOW VARIABLES ruleId ;
225 FETCH cursorRule INTO ruleId ;
226
227 END WHILE;
228 CLOSE cursorRule;
229 -- select mycursor;
230 INSERT tmpLog(log) VALUES ('规则结束');
231
232 -- 写入匹配结果
233 -- UPDATE t_surveil as t SET t.matchRuleIds = matchRuleIds WHERE t.insertedDatetime = insertedDatetime ;
234 -- SELECT matchRuleIds, insertedDatetime ;
235 INSERT tmpLog(log) VALUES ( concat( 'matchRuleIds = ', matchRuleIds ,', insertedDatetime = ' , insertedDatetime ) );
236
237 end ;
238
239 FETCH cursorSurveil INTO insertedDatetime, licenseNumber , pointNumber ;
240
241 END WHILE ;
242
243 CLOSE cursorSurveil ;
244
245 INSERT tmpLog(log) VALUES ('监控数据循环结束');
246
247
248 SELECT * from tmpLog ;
249
250 DROP TEMPORARY TABLE IF EXISTS tmpLog ;
251
252 END
浙公网安备 33010602011771号