Lilf

落木
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
  1DROP 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=2THEN 
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)=1THEN #--表示查看自己未过期的公告
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)=2THEN #--表示查看自己已过期的公告
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=9THEN 
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 CHARNOT BETWEEN 
356            CAST(DATE_ADD(`GetDate`(),INTERVAL -7 DAYAS 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