mysql想说爱你不容易啊，从mssql迁移到mysql时，几乎所有的存储过程都得改，语法相差很大，累人！！
Posted on 2009-09-24 19:30 落木 阅读(971) 评论(2) 编辑 收藏 举报 1
DROP PROCEDURE IF EXISTS Asap_SP_BulletinManager_GetBulletinInfo;
2CREATE DEFINER = 'root'@'192.168.100.114' PROCEDURE `Asap_SP_BulletinManager_GetBulletinInfo`(
3 $BulletinID varchar(100),
4 $BulletinTypeID varchar(100),
5 $BulletinTitle varchar(100),
6 $BulletinContent varchar(5000),
7 $BulletinImportantDegree smallint,
8 $BulletinDisplayDays smallint,
9 $BulletinPostByUserID varchar(100),
10 $BulletinPostTime varchar(100),
11 $KeyWord varchar(100),
12 $Receiver varchar(100),
13 $Flag varchar(100)
14 )
15 NOT DETERMINISTIC
16 CONTAINS SQL
17 SQL SECURITY DEFINER
18 COMMENT ''
19BEGIN
20
21#DECLARE $Sqlstring varchar(8000);
22DECLARE $TMP varchar(8000);
23DECLARE $C2_Subject varchar(8000);
24DECLARE $C2_KeyWord varchar(8000);
25DECLARE $C2_ID varchar(100);
26DECLARE $C2_BulletinTypeID varchar(8000);
27DECLARE $BulletinManageType varchar(20);
28declare stop int default 0;
29
30
31SET $C2_BulletinTypeID='''''';
32SET $C2_ID='';
33SET $TMP='';
34SET @Sqlstring='';
35
36DROP TABLE IF EXISTS TMP_TreeNodes;
37CREATE TEMPORARY TABLE TMP_TreeNodes
38(
39 ID VARCHAR(100) NULL,
40 TreeName VARCHAR(100) NULL,
41 ParentID VARCHAR(100) NULL,
42 IsHasPower VARCHAR(1) Default '1'
43
44);
45
46
47IF($Flag='AllBulletinReader') THEN
48BEGIN
49 set @Sqlstring=CONCAT('
50 SELECT * FROM(SELECT UnitName,System_UserLogon.UserId,System_UserLogon.UserName,CAST(null AS Datetime) AS ReaderTime,''UnReader'' AS stutas
51 FROM System_Msg_BulletinUserViewPower
52 INNER JOIN System_UserLogon ON System_Msg_BulletinUserViewPower.UserID=System_UserLogon.UserID
53 INNER JOIN System_FrameWork_Members ON System_FrameWork_Members.UserID=System_UserLogon.UserID
54 INNER JOIN System_FrameWork ON System_FrameWork.UnitCode=System_FrameWork_Members.UnitCode
55 WHERE System_Msg_BulletinUserViewPower.BulletinID=',$BulletinID,'
56 AND System_Msg_BulletinUserViewPower.UserID NOT IN(SELECT UserID FROM System_Msg_BulletinReader WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,')
57 UNION ALL
58 SELECT UnitName,System_UserLogon.UserId,System_UserLogon.UserName,System_Msg_BulletinReader.ReaderTime AS ReaderTime,''UnReader'' AS stutas
59 FROM System_UserLogon,System_FrameWork,System_FrameWork_Members,System_Msg_BulletinReader
60 WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,'
61 AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode
62 AND System_UserLogon.UserID=System_Msg_BulletinReader.UserID
63 AND System_UserLogon.UserID = System_FrameWork_Members.UserID
64 ) AS a WHERE IFNULL(a.UnitName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.UserID,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.UserName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.stutas,'''') LIKE ''%''',$KeyWord,'''%'' ORDER BY a.stutas DESC');
65END;
66
67ELSEIF($Flag='BulletinReader') THEN
68BEGIN
69 set @Sqlstring=CONCAT('
70 SELECT UnitName,System_UserLogon.*,System_Msg_BulletinReader.ReaderTime AS ReaderTime
71 FROM System_UserLogon,System_FrameWork,System_FrameWork_Members,System_Msg_BulletinReader
72 WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,'
73 AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode
74 AND System_UserLogon.UserID=System_Msg_BulletinReader.UserID
75 AND System_UserLogon.UserID = System_FrameWork_Members.UserID
76 AND ((IFNULL(UnitName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(System_UserLogon.UserID,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(System_UserLogon.UserName,'''') LIKE ''%''',$KeyWord,'''%''))
77 ORDER BY UnitName');
78END;
79
80ELSEIF($Flag='BulletinID') THEN
81BEGIN
82 set @Sqlstring=CONCAT('
83 SELECT BulletinID,BulletinTitle,BulletinContent,BulletinViewPowerXML,BulletinPostTime,BulletinTypeName
84 FROM System_Msg_Bulletin,System_Msg_BulletinType WHERE System_Msg_Bulletin.BulletinTypeID=System_Msg_BulletinType.BulletinTypeID
85 AND System_Msg_Bulletin.BulletinID=',$BulletinID,' And System_Msg_Bulletin.isDel = 0');
86END;
87
88ELSEIF($Flag='BulletinTypeID') THEN
89BEGIN
90 set @Sqlstring=CONCAT('
91 SELECT BulletinID,BulletinTitle,BulletinContent,BulletinViewPowerXML,BulletinPostTime,BulletinTypeName
92 FROM System_Msg_Bulletin,System_Msg_BulletinType WHERE System_Msg_Bulletin.BulletinTypeID=System_Msg_BulletinType.BulletinTypeID
93 AND IFNULL(IsTemporary,''0'')=''0''
94 AND System_Msg_Bulletin.BulletinTypeID=',$BulletinTypeID,'
95 AND GETDATE()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)
96 AND System_Msg_Bulletin.isDel = 0');
97END;
98
99ELSEIF($Flag='Dashboard') THEN
100BEGIN
101 #step1:delete temp table
102 DROP TABLE IF EXISTS TMPBulletinDashboard;
103 #step2:create temp table
104 set @innerSql=CONCAT('create TEMPORARY table TMPBulletinDashboard
105 SELECT UserName as UnitName,BulletinTypeName,BulletinID,BulletinTitle,BulletinContent,BulletinPostTime,System_Msg_Bulletin.BulletinTypeID AS BulletinTypeID
106 FROM System_Msg_Bulletin,System_UserLogon,System_Msg_BulletinType
107 WHERE System_Msg_Bulletin.isDel = 0 and System_Msg_Bulletin.BulletinPostByUserID=System_UserLogon.UserID AND System_Msg_BulletinType.BulletinTypeID=System_Msg_Bulletin.BulletinTypeID AND
108 #CONVERT(NVARCHAR(10), BulletinPostTime, 112) BETWEEN CONVERT(NVARCHAR(10), DATEADD(DAY,-7,GETDATE()), 112) AND CONVERT(NVARCHAR(10), GETDATE(), 112) AND
109 CAST(BulletinPostTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR) AND
110 System_Msg_Bulletin.BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower WHERE UserID=''',$Receiver,''')
111 AND `GetDate`()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)
112 AND IFNULL(IsTemporary,''0'')=''0''
113 ORDER BY BulletinPostTime DESC;');
114 PREPARE stmt from @innerSql;
115 EXECUTE stmt;
116 DEALLOCATE PREPARE stmt;
117 #step3:delete temp table rows if it is not required
118 set @innerSql=CONCAT('delete FROM TMPBulletinDashboard where BulletinID in (
119 select BulletinID from System_Msg_BulletinReader where UserID=''',$Receiver,''' and
120 CAST(ReaderTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR)
121 );');
122 PREPARE stmt from @innerSql;
123 EXECUTE stmt;
124 DEALLOCATE PREPARE stmt;
125 #step4:select temp table
126 select * from TMPBulletinDashboard order by BulletinPostTime desc;
127
128END;
129
130ELSEIF($Flag='DashboardOffice') THEN
131BEGIN
132 set @Sqlstring=CONCAT('
133 SELECT UserName as OrganizationUnitName,BulletinTypeName,BulletinID,BulletinTitle,BulletinContent,BulletinPostTime,System_Msg_Bulletin.BulletinTypeID AS BulletinTypeID
134 FROM System_Msg_Bulletin,System_UserLogon,System_Msg_BulletinType,System_FrameWork,System_FrameWork_Members
135 WHERE System_Msg_Bulletin.BulletinPostByUserID=System_UserLogon.UserID AND System_Msg_BulletinType.BulletinTypeID=System_Msg_Bulletin.BulletinTypeID
136 #AND CONVERT(NVARCHAR(10), BulletinPostTime, 112) BETWEEN CONVERT(NVARCHAR(10), DATEADD(DAY,-7,GETDATE()), 112) AND CONVERT(NVARCHAR(10), GETDATE(), 112)
137 AND CAST(BulletinPostTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR)
138 AND System_Msg_Bulletin.BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower WHERE UserID=''',$Receiver,''')
139 AND System_Msg_Bulletin.BulletinTypeID=',$BulletinTypeID,' and System_Msg_Bulletin.isDel = 0
140 AND GETDATE()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)#DATEADD(Day,BulletinDisplayDays,BulletinPostTime)#?????
141 AND IFNULL(IsTemporary,''0'')=''0''
142 AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode
143 AND System_UserLogon.UserID = System_FrameWork_Members.UserID
144 ORDER BY BulletinPostTime DESC;');
145END;
146
147ELSE
148 BEGIN
149
/*=====================创建临时表========================================*/
150 DROP TABLE IF EXISTS Temp_Bulletin;
151 CREATE TEMPORARY TABLE Temp_Bulletin
152 (
153 BulletinID varchar(100),
154 BulletinTypeID varchar(100),
155 BulletinTypeName varchar(100),
156 BulletinPostTime datetime ,
157 BulletinTitle varchar(100),
158 BulletinContent text,
159 BulletinImportantDegree smallint,
160 BulletinDisplayDays smallint,
161 BulletinPostByUserID varchar(100),
162 UserName varchar(100),
163
164 OrganizationUnitID varchar(100),
165 OrganizationUnitName varchar(100),
166
167 IsAutomaticRemind bit,
168 IsAutomaticNotify bit,
169 IsNotifyImmediately bit,
170 IsNotifyForRead bit,
171 NotifyFrequency int,
172 NotifyMode smallint,
173 BulletinFrequencyUnit varchar(100),
174 IsTemporary bit,
175 IsEnd smallint,
176 BulletinViewPowerXML varchar(8000)
177 );
178 /*=================向临时表中插入数据==========================================*/
179 INSERT INTO Temp_Bulletin( BulletinID ,
180 BulletinTypeID ,
181 BulletinPostTime ,
182 BulletinTitle ,
183 BulletinContent ,
184 BulletinImportantDegree ,
185 BulletinDisplayDays ,
186 BulletinPostByUserID ,
187
188 IsAutomaticRemind ,
189 IsAutomaticNotify ,
190 IsNotifyImmediately ,
191 IsNotifyForRead ,
192 NotifyFrequency ,
193 NotifyMode ,
194 BulletinFrequencyUnit,
195 IsTemporary,
196 IsEnd,
197 BulletinViewPowerXML
198 )
199 SELECT BulletinID ,
200 BulletinTypeID ,
201 BulletinPostTime ,
202 BulletinTitle ,
203 BulletinContent ,
204 BulletinImportantDegree ,
205 BulletinDisplayDays ,
206 BulletinPostByUserID ,
207
208 IsAutomaticRemind ,
209 IsAutomaticNotify ,
210 IsNotifyImmediately ,
211 IsNotifyForRead ,
212 NotifyFrequency ,
213 NotifyMode ,
214 BulletinFrequencyUnit,
215 IsTemporary,
216 IsEnd,
217 BulletinViewPowerXML
218 FROM System_Msg_Bulletin where isDel=0;#插入未放入垃圾箱的所有公告
219
220 /*==============删除临时表中数据==================================================*/
221 /*<分支1>==根据传入的公告ID，删除除传入的公告以外的所有公告（意思根据传入ID来查询公告）*/
222 IF(IFNULL($BulletinID,'')<>'') THEN
223 DELETE from Temp_Bulletin WHERE BulletinID<>$BulletinID;
224 END IF;
225 /*<分支2>==根据传入的公告类型ID，删除除传入的公告类型以外的所有公告（意思根据传入类型ID来查询公告）*/
226 IF(IFNULL($BulletinTypeID,'')<>'') THEN
227 DELETE FROM Temp_Bulletin WHERE BulletinTypeID NOT IN($BulletinTypeID);
228 ELSE #是否使用类别管理员审批
229 BEGIN
230 IF($Flag<>'TempOwner') THEN #不是临时公告时
231 BEGIN
232 IF($Flag='Owner') THEN #查看自己发送的公告
233 begin
234 CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('$Receiver','5');
235 INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;
236 DROP TABLE IF EXISTS TMPTreeNodes;
237 end;
238 ELSEIF ($Flag='admin') THEN #系统管理员
239 BEGIN
240 CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('admin','5');
241 INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;
242 DROP TABLE IF EXISTS TMPTreeNodes;
243 END;
244 ELSE #用于对公告类别信息查看的数据加载。
245 BEGIN
246 CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('$BulletinPostByUserID','6');
247 INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;
248 #SELECT * FROM TMP_TreeNodes
249 DROP TABLE IF EXISTS TMPTreeNodes;
250 END;
251 END IF;
252
253
254 #declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
255 BEGIN
256 declare cur cursor FOR SELECT DISTINCT ID FROM TMP_TreeNodes WHERE IsHasPower='1';
257 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
258 OPEN cur;
259 FETCH cur INTO $C2_ID;
260
261 while(stop <> 1)do
262 set $C2_BulletinTypeID=CONCAT($C2_BulletinTypeID,',''',$C2_ID,'''');
263 FETCH cur INTO $C2_ID;
264 end while;
265 CLOSE cur;
266
267 END;
268 #DEALLOCATE cur;
269 #SELECT $C2_BulletinTypeID;
270 DELETE FROM Temp_Bulletin WHERE BulletinTypeID NOT IN($C2_BulletinTypeID);
271 #SELECT 'FDSFDS';
272 END;
273 END IF;
274 END;
275 END IF;
276 /*<分支3>==根据发件人过滤查询*/
277 IF(IFNULL($BulletinPostByUserID,'')<>'') THEN
278 DELETE FROM Temp_Bulletin WHERE BulletinPostByUserID<>$BulletinPostByUserID;
279 END IF;
280 /*<分支4>==根据收件人过滤查询*/
281 IF(IFNULL($Receiver,'')<>'') THEN
282 DELETE FROM Temp_Bulletin WHERE NOT(BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower
283 WHERE UserID=$Receiver));
284 END IF;
285 /*<分支5>==根据重要性过滤查询*/
286 IF($BulletinImportantDegree=0 OR $BulletinImportantDegree=1 OR $BulletinImportantDegree=2) THEN
287 DELETE FROM Temp_Bulletin WHERE BulletinImportantDegree<>$BulletinImportantDegree;
288 END IF;
289 /*<分支6>==根据公告标题过滤查询*/
290 IF(IFNULL($BulletinTitle,'')<>'') THEN
291 SET @Sqlstring=CONCAT('DELETE FROM Temp_Bulletin WHERE BulletinTitle NOT LIKE ''%''',$BulletinTitle,'''%''');
292 #PREPARE strm from @thisSql;
293 #EXECUTE strm;
294 #DEALLOCATE PREPARE strm;
295 END IF;
296 /*<分支7>==根据公告内容过滤查询*/
297 IF(IFNULL($BulletinContent,'')<>'') THEN
298 SET @Sqlstring=CONCAT('DELETE FROM Temp_Bulletin WHERE BulletinContent NOT LIKE ''%''',$BulletinContent,'''%''');
299 #PREPARE strm1 from @thisSql1;
300 #EXECUTE strm1;
301 #DEALLOCATE PREPARE strm1;
302 END IF;
303 /***************update by Lilf 2009-6-18**************************/
304 /*<分支8>==根据是否过期过滤查询*/
305 IF(IFNULL($BulletinDisplayDays,0)=1) THEN #--表示查看自己未过期的公告
306 BEGIN
307 DELETE FROM Temp_Bulletin WHERE `GetDate`()>DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY);
308 #DELETE Bulletin WHERE GETDATE()>DATEADD(Day,BulletinDisplayDays,BulletinPostTime);--删除已经过期并且是接收的的公告
309 #AND BulletinPostByUserID<>@Receiver --自己发出去的公告不存在过期，永远都可以查看到。ISNULL(IsTemporary,'0')='1' And
310 #DELETE #Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE ISNULL(IsTemporary,'0')='1' or (IsEnd=0 or IsEnd=3))
311 END;
312 ELSEIF(IFNULL($BulletinDisplayDays,0)=2) THEN #--表示查看自己已过期的公告
313 BEGIN
314 #DELETE Bulletin WHERE GETDATE()<=DATEADD(Day,BulletinDisplayDays,BulletinPostTime);
315 DELETE FROM Temp_Bulletin WHERE `GetDate`()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY);
316 #AND BulletinPostByUserID<>@Receiver
317 END;
318 END IF;
319 /***************update End****************************/
320 /*<分支9>==*/
321 IF($Flag='Owner') THEN #--只能查看自己发送的公告（包括未过期，已过期，全部）
322 BEGIN
323 if($BulletinImportantDegree=9) THEN
324 begin
325 DELETE FROM Temp_Bulletin WHERE NOT(BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower
326 WHERE UserID=$Receiver) OR BulletinPostByUserID=$Receiver);
327 end;
328 END IF;
329
330 END;
331
332 ELSEIF($Flag='Other') THEN #--只能查看自己接收的公告
333 BEGIN
334 DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1');
335 END;
336 ELSEIF($Flag='TempOwner') THEN #--只能查看存入草稿中的公告
337 BEGIN
338 DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='0');
339 END;
340 ELSE #只能查看未存入草稿中的公告
341 BEGIN
342 DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1' );
343 END;
344 END IF;
345
346 /*<分支10>==根据时间段过滤查询*/
347 IF($BulletinPostTime='今日通告') THEN
348 DELETE FROM Temp_Bulletin WHERE CAST(BulletinPostTime AS CHAR) <> CAST(`GetDate`() AS CHAR);
349 ELSEIF($BulletinPostTime='本周通告') THEN
350 DELETE FROM Temp_Bulletin WHERE WEEKOFYEAR(`GetDate`())<>WEEKOFYEAR(BulletinPostTime);
351 ELSEIF($BulletinPostTime='本月通告') THEN
352 DELETE FROM Temp_Bulletin WHERE MONTH(`GetDate`())<>MONTH(BulletinPostTime);
353 ELSEIF($BulletinPostTime='前七天通告') THEN
354 BEGIN
355 DELETE FROM Temp_Bulletin WHERE CAST(BulletinPostTime AS CHAR) NOT BETWEEN
356 CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(GETDATE() AS CHAR);
357
358 /*UPDATE Temp_Bulletin SET Temp_Bulletin.BulletinTypeName=System_Msg_BulletinType.BulletinTypeName
359
FROM System_Msg_BulletinType WHERE System_Msg_BulletinType.BulletinTypeID=Temp_Bulletin.BulletinTypeID;*/
360
361 UPDATE Temp_Bulletin SET BulletinTypeName=(select BulletinTypeName
362 from System_Msg_BulletinType where BulletinTypeID=Temp_Bulletin.BulletinTypeID);
363
364 /*UPDATE Temp_Bulletin SET Temp_Bulletin.UserName=System_UserLogon.UserName
365 FROM System_UserLogon WHERE System_UserLogon.UserID=Temp_Bulletin.BulletinPostByUserID;*/
366
367 UPDATE Temp_Bulletin SET UserName=(select UserName
368 from System_UserLogon where UserID=Temp_Bulletin.BulletinPostByUserID);
369
370 /*
371
UPDATE Temp_Bulletin SET Temp_Bulletin.OrganizationUnitID=System_FrameWork_Members.UnitCode
372 FROM System_FrameWork_Members WHERE System_FrameWork_Members.UserID=Temp_Bulletin.BulletinPostByUserID;*/
373
374 UPDATE Temp_Bulletin SET OrganizationUnitID=(select UnitCode
375 from System_FrameWork_Members where UserID=Temp_Bulletin.BulletinPostByUserID);
376
377 /*
378 UPDATE Temp_Bulletin SET Temp_Bulletin.OrganizationUnitName=System_FrameWork.UnitName
379 FROM System_FrameWork WHERE System_FrameWork.UnitCode=Temp_Bulletin.OrganizationUnitID;
380 */
381 UPDATE Temp_Bulletin SET OrganizationUnitName=(select UnitName
382 from System_FrameWork where UnitCode=Temp_Bulletin.OrganizationUnitID);
383 END;
384 END IF;
385 /*====================获得最终结果=============================================================*/
386 IF($Flag='TempOwner') THEN #--只能查看自己存入草稿箱中的公告
387 begin
388 select * from Temp_Bulletin where BulletinPostByUserID = $BulletinPostByUserID;
389 end ;
390 elseif($Flag ='Owner') THEN
391 begin
392 /***************update by Lilf 2009-6-18**************************/
393 #DELETE Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1' and BulletinPostByUserID = @Receiver)
394 #DELETE Temp_Bulletin WHERE BulletinPostByUserID <> @Receiver and IsEnd = 0
395 SELECT * FROM Temp_Bulletin WHERE (IFNULL(IsTemporary,'0')='0' and IsEnd = 1 ) and (IFNULL(BulletinTitle,'') LIKE CONCAT('%',$KeyWord,'%') OR IFNULL(BulletinContent,'') LIKE CONCAT('%',$KeyWord,'%'))
396 ORDER BY BulletinPostTime DESC;
397 #IFNULL(IsTemporary,'0')='1' and IsEnd = 0 and BulletinPostByUserID = @Receiver
398 #return
399 /***************update End****************************/
400 end;
401 else
402 /***************update by Lilf 2009-6-18**************************/
403 SELECT * FROM Temp_Bulletin WHERE IFNULL(BulletinTitle,'') LIKE CONCAT('%',$KeyWord,'%') OR IFNULL(BulletinContent,'') LIKE CONCAT('%',$KeyWord,'%')
404 ORDER BY BulletinPostTime DESC;
405 /***************update End****************************/
406 end if;
407 END;
408END IF;
409
410
411#SELECT @Sqlstring;
412
413PREPARE strm from @Sqlstring;
414EXECUTE strm;
415DEALLOCATE PREPARE strm;
416END;
417
418CALL Asap_SP_BulletinManager_GetBulletinInfo('','','','',NULL,0,'','ALL','','admin','Owner')
419
420#select * from `system_msg_bulletin`
421#SELECT * FROM TMPTreeNodes
422#SELECT * FROM TMP_TreeNodes
DROP PROCEDURE IF EXISTS Asap_SP_BulletinManager_GetBulletinInfo;2
CREATE DEFINER = 'root'@'192.168.100.114' PROCEDURE `Asap_SP_BulletinManager_GetBulletinInfo`(3
$BulletinID varchar(100),4
$BulletinTypeID varchar(100),5
$BulletinTitle varchar(100),6
$BulletinContent varchar(5000),7
$BulletinImportantDegree smallint,8
$BulletinDisplayDays smallint,9
$BulletinPostByUserID varchar(100),10
$BulletinPostTime varchar(100),11
$KeyWord varchar(100),12
$Receiver varchar(100),13
$Flag varchar(100)14
)15
NOT DETERMINISTIC16
CONTAINS SQL17
SQL SECURITY DEFINER18
COMMENT ''19
BEGIN20
21
#DECLARE $Sqlstring varchar(8000);22
DECLARE $TMP varchar(8000);23
DECLARE $C2_Subject varchar(8000);24
DECLARE $C2_KeyWord varchar(8000);25
DECLARE $C2_ID varchar(100);26
DECLARE $C2_BulletinTypeID varchar(8000);27
DECLARE $BulletinManageType varchar(20);28
declare stop int default 0;29
30
31
SET $C2_BulletinTypeID='''''';32
SET $C2_ID='';33
SET $TMP='';34
SET @Sqlstring='';35
36
DROP TABLE IF EXISTS TMP_TreeNodes;37
CREATE TEMPORARY TABLE TMP_TreeNodes38
( 39
ID VARCHAR(100) NULL,40
TreeName VARCHAR(100) NULL,41
ParentID VARCHAR(100) NULL,42
IsHasPower VARCHAR(1) Default '1' 43
44
);45
46
47
IF($Flag='AllBulletinReader') THEN48
BEGIN49
set @Sqlstring=CONCAT('50
SELECT * FROM(SELECT UnitName,System_UserLogon.UserId,System_UserLogon.UserName,CAST(null AS Datetime) AS ReaderTime,''UnReader'' AS stutas51
FROM System_Msg_BulletinUserViewPower 52
INNER JOIN System_UserLogon ON System_Msg_BulletinUserViewPower.UserID=System_UserLogon.UserID 53
INNER JOIN System_FrameWork_Members ON System_FrameWork_Members.UserID=System_UserLogon.UserID 54
INNER JOIN System_FrameWork ON System_FrameWork.UnitCode=System_FrameWork_Members.UnitCode55
WHERE System_Msg_BulletinUserViewPower.BulletinID=',$BulletinID,'56
AND System_Msg_BulletinUserViewPower.UserID NOT IN(SELECT UserID FROM System_Msg_BulletinReader WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,')57
UNION ALL58
SELECT UnitName,System_UserLogon.UserId,System_UserLogon.UserName,System_Msg_BulletinReader.ReaderTime AS ReaderTime,''UnReader'' AS stutas59
FROM System_UserLogon,System_FrameWork,System_FrameWork_Members,System_Msg_BulletinReader60
WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,'61
AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode62
AND System_UserLogon.UserID=System_Msg_BulletinReader.UserID 63
AND System_UserLogon.UserID = System_FrameWork_Members.UserID64
) AS a WHERE IFNULL(a.UnitName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.UserID,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.UserName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(a.stutas,'''') LIKE ''%''',$KeyWord,'''%'' ORDER BY a.stutas DESC'); 65
END;66
67
ELSEIF($Flag='BulletinReader') THEN68
BEGIN69
set @Sqlstring=CONCAT('70
SELECT UnitName,System_UserLogon.*,System_Msg_BulletinReader.ReaderTime AS ReaderTime71
FROM System_UserLogon,System_FrameWork,System_FrameWork_Members,System_Msg_BulletinReader72
WHERE System_Msg_BulletinReader.BulletinID=',$BulletinID,'73
AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode74
AND System_UserLogon.UserID=System_Msg_BulletinReader.UserID 75
AND System_UserLogon.UserID = System_FrameWork_Members.UserID76
AND ((IFNULL(UnitName,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(System_UserLogon.UserID,'''') LIKE ''%''',$KeyWord,'''%'' OR IFNULL(System_UserLogon.UserName,'''') LIKE ''%''',$KeyWord,'''%''))77
ORDER BY UnitName'); 78
END;79
80
ELSEIF($Flag='BulletinID') THEN81
BEGIN 82
set @Sqlstring=CONCAT('83
SELECT BulletinID,BulletinTitle,BulletinContent,BulletinViewPowerXML,BulletinPostTime,BulletinTypeName 84
FROM System_Msg_Bulletin,System_Msg_BulletinType WHERE System_Msg_Bulletin.BulletinTypeID=System_Msg_BulletinType.BulletinTypeID 85
AND System_Msg_Bulletin.BulletinID=',$BulletinID,' And System_Msg_Bulletin.isDel = 0');86
END;87
88
ELSEIF($Flag='BulletinTypeID') THEN89
BEGIN 90
set @Sqlstring=CONCAT('91
SELECT BulletinID,BulletinTitle,BulletinContent,BulletinViewPowerXML,BulletinPostTime,BulletinTypeName 92
FROM System_Msg_Bulletin,System_Msg_BulletinType WHERE System_Msg_Bulletin.BulletinTypeID=System_Msg_BulletinType.BulletinTypeID 93
AND IFNULL(IsTemporary,''0'')=''0''94
AND System_Msg_Bulletin.BulletinTypeID=',$BulletinTypeID,' 95
AND GETDATE()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)96
AND System_Msg_Bulletin.isDel = 0');97
END;98
99
ELSEIF($Flag='Dashboard') THEN100
BEGIN101
#step1:delete temp table102
DROP TABLE IF EXISTS TMPBulletinDashboard;103
#step2:create temp table104
set @innerSql=CONCAT('create TEMPORARY table TMPBulletinDashboard105
SELECT UserName as UnitName,BulletinTypeName,BulletinID,BulletinTitle,BulletinContent,BulletinPostTime,System_Msg_Bulletin.BulletinTypeID AS BulletinTypeID106
FROM System_Msg_Bulletin,System_UserLogon,System_Msg_BulletinType107
WHERE System_Msg_Bulletin.isDel = 0 and System_Msg_Bulletin.BulletinPostByUserID=System_UserLogon.UserID AND System_Msg_BulletinType.BulletinTypeID=System_Msg_Bulletin.BulletinTypeID AND108
#CONVERT(NVARCHAR(10), BulletinPostTime, 112) BETWEEN CONVERT(NVARCHAR(10), DATEADD(DAY,-7,GETDATE()), 112) AND CONVERT(NVARCHAR(10), GETDATE(), 112) AND109
CAST(BulletinPostTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR) AND110
System_Msg_Bulletin.BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower WHERE UserID=''',$Receiver,''')111
AND `GetDate`()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)112
AND IFNULL(IsTemporary,''0'')=''0''113
ORDER BY BulletinPostTime DESC;');114
PREPARE stmt from @innerSql;115
EXECUTE stmt;116
DEALLOCATE PREPARE stmt;117
#step3:delete temp table rows if it is not required118
set @innerSql=CONCAT('delete FROM TMPBulletinDashboard where BulletinID in (119
select BulletinID from System_Msg_BulletinReader where UserID=''',$Receiver,''' and 120
CAST(ReaderTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR)121
);');122
PREPARE stmt from @innerSql;123
EXECUTE stmt;124
DEALLOCATE PREPARE stmt; 125
#step4:select temp table 126
select * from TMPBulletinDashboard order by BulletinPostTime desc;127
128
END;129
130
ELSEIF($Flag='DashboardOffice') THEN131
BEGIN132
set @Sqlstring=CONCAT('133
SELECT UserName as OrganizationUnitName,BulletinTypeName,BulletinID,BulletinTitle,BulletinContent,BulletinPostTime,System_Msg_Bulletin.BulletinTypeID AS BulletinTypeID134
FROM System_Msg_Bulletin,System_UserLogon,System_Msg_BulletinType,System_FrameWork,System_FrameWork_Members135
WHERE System_Msg_Bulletin.BulletinPostByUserID=System_UserLogon.UserID AND System_Msg_BulletinType.BulletinTypeID=System_Msg_Bulletin.BulletinTypeID 136
#AND CONVERT(NVARCHAR(10), BulletinPostTime, 112) BETWEEN CONVERT(NVARCHAR(10), DATEADD(DAY,-7,GETDATE()), 112) AND CONVERT(NVARCHAR(10), GETDATE(), 112) 137
AND CAST(BulletinPostTime AS CHAR) BETWEEN CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(`GetDate`() AS CHAR)138
AND System_Msg_Bulletin.BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower WHERE UserID=''',$Receiver,''') 139
AND System_Msg_Bulletin.BulletinTypeID=',$BulletinTypeID,' and System_Msg_Bulletin.isDel = 0 140
AND GETDATE()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY)#DATEADD(Day,BulletinDisplayDays,BulletinPostTime)#?????141
AND IFNULL(IsTemporary,''0'')=''0''142
AND System_FrameWork_Members.UnitCode=System_FrameWork.UnitCode143
AND System_UserLogon.UserID = System_FrameWork_Members.UserID144
ORDER BY BulletinPostTime DESC;');145
END;146
147
ELSE148
BEGIN149
/*=====================创建临时表========================================*/
150
DROP TABLE IF EXISTS Temp_Bulletin;151
CREATE TEMPORARY TABLE Temp_Bulletin152
( 153
BulletinID varchar(100),154
BulletinTypeID varchar(100),155
BulletinTypeName varchar(100),156
BulletinPostTime datetime ,157
BulletinTitle varchar(100),158
BulletinContent text,159
BulletinImportantDegree smallint,160
BulletinDisplayDays smallint,161
BulletinPostByUserID varchar(100),162
UserName varchar(100),163
164
OrganizationUnitID varchar(100),165
OrganizationUnitName varchar(100),166
167
IsAutomaticRemind bit,168
IsAutomaticNotify bit,169
IsNotifyImmediately bit,170
IsNotifyForRead bit,171
NotifyFrequency int,172
NotifyMode smallint,173
BulletinFrequencyUnit varchar(100),174
IsTemporary bit,175
IsEnd smallint,176
BulletinViewPowerXML varchar(8000)177
);178
/*=================向临时表中插入数据==========================================*/179
INSERT INTO Temp_Bulletin( BulletinID ,180
BulletinTypeID ,181
BulletinPostTime ,182
BulletinTitle ,183
BulletinContent ,184
BulletinImportantDegree ,185
BulletinDisplayDays ,186
BulletinPostByUserID ,187
188
IsAutomaticRemind ,189
IsAutomaticNotify ,190
IsNotifyImmediately ,191
IsNotifyForRead ,192
NotifyFrequency ,193
NotifyMode ,194
BulletinFrequencyUnit,195
IsTemporary, 196
IsEnd,197
BulletinViewPowerXML198
)199
SELECT BulletinID ,200
BulletinTypeID ,201
BulletinPostTime ,202
BulletinTitle ,203
BulletinContent ,204
BulletinImportantDegree ,205
BulletinDisplayDays ,206
BulletinPostByUserID ,207
208
IsAutomaticRemind , 209
IsAutomaticNotify ,210
IsNotifyImmediately ,211
IsNotifyForRead ,212
NotifyFrequency ,213
NotifyMode ,214
BulletinFrequencyUnit,215
IsTemporary,216
IsEnd,217
BulletinViewPowerXML218
FROM System_Msg_Bulletin where isDel=0;#插入未放入垃圾箱的所有公告219
220
/*==============删除临时表中数据==================================================*/221
/*<分支1>==根据传入的公告ID，删除除传入的公告以外的所有公告（意思根据传入ID来查询公告）*/222
IF(IFNULL($BulletinID,'')<>'') THEN223
DELETE from Temp_Bulletin WHERE BulletinID<>$BulletinID;224
END IF; 225
/*<分支2>==根据传入的公告类型ID，删除除传入的公告类型以外的所有公告（意思根据传入类型ID来查询公告）*/226
IF(IFNULL($BulletinTypeID,'')<>'') THEN227
DELETE FROM Temp_Bulletin WHERE BulletinTypeID NOT IN($BulletinTypeID);228
ELSE #是否使用类别管理员审批229
BEGIN230
IF($Flag<>'TempOwner') THEN #不是临时公告时231
BEGIN232
IF($Flag='Owner') THEN #查看自己发送的公告233
begin 234
CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('$Receiver','5');235
INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;236
DROP TABLE IF EXISTS TMPTreeNodes;237
end;238
ELSEIF ($Flag='admin') THEN #系统管理员 239
BEGIN240
CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('admin','5');241
INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;242
DROP TABLE IF EXISTS TMPTreeNodes;243
END;244
ELSE #用于对公告类别信息查看的数据加载。245
BEGIN 246
CALL Asap_SP_BulletinManager_GetBulletinTypeInfo('$BulletinPostByUserID','6');247
INSERT INTO TMP_TreeNodes select * from TMPTreeNodes;248
#SELECT * FROM TMP_TreeNodes249
DROP TABLE IF EXISTS TMPTreeNodes;250
END; 251
END IF; 252
253
254
#declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id; 255
BEGIN 256
declare cur cursor FOR SELECT DISTINCT ID FROM TMP_TreeNodes WHERE IsHasPower='1';257
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1; 258
OPEN cur;259
FETCH cur INTO $C2_ID;260
261
while(stop <> 1)do 262
set $C2_BulletinTypeID=CONCAT($C2_BulletinTypeID,',''',$C2_ID,''''); 263
FETCH cur INTO $C2_ID;264
end while;265
CLOSE cur;266
267
END;268
#DEALLOCATE cur;269
#SELECT $C2_BulletinTypeID;270
DELETE FROM Temp_Bulletin WHERE BulletinTypeID NOT IN($C2_BulletinTypeID);271
#SELECT 'FDSFDS';272
END;273
END IF;274
END;275
END IF;276
/*<分支3>==根据发件人过滤查询*/277
IF(IFNULL($BulletinPostByUserID,'')<>'') THEN278
DELETE FROM Temp_Bulletin WHERE BulletinPostByUserID<>$BulletinPostByUserID;279
END IF;280
/*<分支4>==根据收件人过滤查询*/281
IF(IFNULL($Receiver,'')<>'') THEN 282
DELETE FROM Temp_Bulletin WHERE NOT(BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower283
WHERE UserID=$Receiver));284
END IF;285
/*<分支5>==根据重要性过滤查询*/286
IF($BulletinImportantDegree=0 OR $BulletinImportantDegree=1 OR $BulletinImportantDegree=2) THEN 287
DELETE FROM Temp_Bulletin WHERE BulletinImportantDegree<>$BulletinImportantDegree;288
END IF;289
/*<分支6>==根据公告标题过滤查询*/290
IF(IFNULL($BulletinTitle,'')<>'') THEN291
SET @Sqlstring=CONCAT('DELETE FROM Temp_Bulletin WHERE BulletinTitle NOT LIKE ''%''',$BulletinTitle,'''%''');292
#PREPARE strm from @thisSql;293
#EXECUTE strm;294
#DEALLOCATE PREPARE strm;295
END IF;296
/*<分支7>==根据公告内容过滤查询*/297
IF(IFNULL($BulletinContent,'')<>'') THEN 298
SET @Sqlstring=CONCAT('DELETE FROM Temp_Bulletin WHERE BulletinContent NOT LIKE ''%''',$BulletinContent,'''%''');299
#PREPARE strm1 from @thisSql1;300
#EXECUTE strm1;301
#DEALLOCATE PREPARE strm1;302
END IF;303
/***************update by Lilf 2009-6-18**************************/304
/*<分支8>==根据是否过期过滤查询*/305
IF(IFNULL($BulletinDisplayDays,0)=1) THEN #--表示查看自己未过期的公告306
BEGIN307
DELETE FROM Temp_Bulletin WHERE `GetDate`()>DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY);308
#DELETE Bulletin WHERE GETDATE()>DATEADD(Day,BulletinDisplayDays,BulletinPostTime);--删除已经过期并且是接收的的公告309
#AND BulletinPostByUserID<>@Receiver --自己发出去的公告不存在过期，永远都可以查看到。ISNULL(IsTemporary,'0')='1' And 310
#DELETE #Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE ISNULL(IsTemporary,'0')='1' or (IsEnd=0 or IsEnd=3)) 311
END;312
ELSEIF(IFNULL($BulletinDisplayDays,0)=2) THEN #--表示查看自己已过期的公告313
BEGIN314
#DELETE Bulletin WHERE GETDATE()<=DATEADD(Day,BulletinDisplayDays,BulletinPostTime);315
DELETE FROM Temp_Bulletin WHERE `GetDate`()<=DATE_ADD(BulletinPostTime,INTERVAL BulletinDisplayDays DAY);316
#AND BulletinPostByUserID<>@Receiver317
END;318
END IF;319
/***************update End****************************/320
/*<分支9>==*/321
IF($Flag='Owner') THEN #--只能查看自己发送的公告（包括未过期，已过期，全部）322
BEGIN323
if($BulletinImportantDegree=9) THEN 324
begin325
DELETE FROM Temp_Bulletin WHERE NOT(BulletinID IN (SELECT BulletinID FROM System_Msg_BulletinUserViewPower326
WHERE UserID=$Receiver) OR BulletinPostByUserID=$Receiver);327
end;328
END IF;329
330
END;331
332
ELSEIF($Flag='Other') THEN #--只能查看自己接收的公告333
BEGIN334
DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1');335
END;336
ELSEIF($Flag='TempOwner') THEN #--只能查看存入草稿中的公告337
BEGIN338
DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='0');339
END;340
ELSE #只能查看未存入草稿中的公告341
BEGIN342
DELETE FROM Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1' );343
END;344
END IF; 345
346
/*<分支10>==根据时间段过滤查询*/347
IF($BulletinPostTime='今日通告') THEN348
DELETE FROM Temp_Bulletin WHERE CAST(BulletinPostTime AS CHAR) <> CAST(`GetDate`() AS CHAR);349
ELSEIF($BulletinPostTime='本周通告') THEN350
DELETE FROM Temp_Bulletin WHERE WEEKOFYEAR(`GetDate`())<>WEEKOFYEAR(BulletinPostTime);351
ELSEIF($BulletinPostTime='本月通告') THEN352
DELETE FROM Temp_Bulletin WHERE MONTH(`GetDate`())<>MONTH(BulletinPostTime);353
ELSEIF($BulletinPostTime='前七天通告') THEN354
BEGIN355
DELETE FROM Temp_Bulletin WHERE CAST(BulletinPostTime AS CHAR) NOT BETWEEN 356
CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAY) AS CHAR) AND CAST(GETDATE() AS CHAR);357
358
/*UPDATE Temp_Bulletin SET Temp_Bulletin.BulletinTypeName=System_Msg_BulletinType.BulletinTypeName359
FROM System_Msg_BulletinType WHERE System_Msg_BulletinType.BulletinTypeID=Temp_Bulletin.BulletinTypeID;*/360
361
UPDATE Temp_Bulletin SET BulletinTypeName=(select BulletinTypeName 362
from System_Msg_BulletinType where BulletinTypeID=Temp_Bulletin.BulletinTypeID); 363
364
/*UPDATE Temp_Bulletin SET Temp_Bulletin.UserName=System_UserLogon.UserName365
FROM System_UserLogon WHERE System_UserLogon.UserID=Temp_Bulletin.BulletinPostByUserID;*/366
367
UPDATE Temp_Bulletin SET UserName=(select UserName 368
from System_UserLogon where UserID=Temp_Bulletin.BulletinPostByUserID);369
370
/*371
UPDATE Temp_Bulletin SET Temp_Bulletin.OrganizationUnitID=System_FrameWork_Members.UnitCode372
FROM System_FrameWork_Members WHERE System_FrameWork_Members.UserID=Temp_Bulletin.BulletinPostByUserID;*/373
374
UPDATE Temp_Bulletin SET OrganizationUnitID=(select UnitCode 375
from System_FrameWork_Members where UserID=Temp_Bulletin.BulletinPostByUserID); 376
377
/*378
UPDATE Temp_Bulletin SET Temp_Bulletin.OrganizationUnitName=System_FrameWork.UnitName379
FROM System_FrameWork WHERE System_FrameWork.UnitCode=Temp_Bulletin.OrganizationUnitID;380
*/381
UPDATE Temp_Bulletin SET OrganizationUnitName=(select UnitName 382
from System_FrameWork where UnitCode=Temp_Bulletin.OrganizationUnitID); 383
END;384
END IF;385
/*====================获得最终结果=============================================================*/ 386
IF($Flag='TempOwner') THEN #--只能查看自己存入草稿箱中的公告387
begin388
select * from Temp_Bulletin where BulletinPostByUserID = $BulletinPostByUserID;389
end ; 390
elseif($Flag ='Owner') THEN391
begin392
/***************update by Lilf 2009-6-18**************************/393
#DELETE Temp_Bulletin WHERE BulletinID IN(SELECT BulletinID FROM System_Msg_Bulletin WHERE IFNULL(IsTemporary,'0')='1' and BulletinPostByUserID = @Receiver)394
#DELETE Temp_Bulletin WHERE BulletinPostByUserID <> @Receiver and IsEnd = 0395
SELECT * FROM Temp_Bulletin WHERE (IFNULL(IsTemporary,'0')='0' and IsEnd = 1 ) and (IFNULL(BulletinTitle,'') LIKE CONCAT('%',$KeyWord,'%') OR IFNULL(BulletinContent,'') LIKE CONCAT('%',$KeyWord,'%'))396
ORDER BY BulletinPostTime DESC;397
#IFNULL(IsTemporary,'0')='1' and IsEnd = 0 and BulletinPostByUserID = @Receiver398
#return399
/***************update End****************************/400
end;401
else402
/***************update by Lilf 2009-6-18**************************/403
SELECT * FROM Temp_Bulletin WHERE IFNULL(BulletinTitle,'') LIKE CONCAT('%',$KeyWord,'%') OR IFNULL(BulletinContent,'') LIKE CONCAT('%',$KeyWord,'%')404
ORDER BY BulletinPostTime DESC;405
/***************update End****************************/406
end if;407
END;408
END IF;409
410
411
#SELECT @Sqlstring;412
413
PREPARE strm from @Sqlstring;414
EXECUTE strm;415
DEALLOCATE PREPARE strm;416
END;417
418
CALL Asap_SP_BulletinManager_GetBulletinInfo('','','','',NULL,0,'','ALL','','admin','Owner')419
420
#select * from `system_msg_bulletin`421
#SELECT * FROM TMPTreeNodes422
#SELECT * FROM TMP_TreeNodes
