create or replace procedure getSystemNotice(pHumanID in INTEGER)
is
iCount INTEGER ;
fromTable sys_refcursor;
msgcur sys_refcursor;
var_MAXID INTEGER ;
var_EVENTSQL CLOB ;
var_EVENTID INTEGER ;
var_HASGREENCHANNEL INTEGER;
var_EXPOFLAG INTEGER;
var_CIVIFLAG INTEGER;
var_READFLAG INTEGER;
var_SQL varchar2(10000);
var_CONTENT VARCHAR2(500) ;
var_DISPLAYTYPE INTEGER ;
var_ISREAD INTEGER ;
Begin
/*待办案卷*/
Select count(*) into iCount from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;
If iCount>0 then
/*获取开启待办案卷箱子的查询语句*/
Select HUMANLISTSQL into var_EVENTSQL from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;
/*获取案卷eventID*/
var_SQL := 'Select eventID from ('||var_EVENTSQL||')';
open fromTable for var_SQL;
loop
fetch fromTable into var_EVENTID;
exit when fromTable%notfound;
if var_EVENTID is not null then
/*获取案卷相关信息*/
Select eventDESC,HASGREENCHANNEL,EXPOFLAG,CIVIFLAG,READFLAG into
var_CONTENT ,
var_HASGREENCHANNEL,
var_EXPOFLAG,
var_CIVIFLAG,
var_READFLAG
from umdata.toEvent where eventID=var_EVENTID;
/*根据案卷信息设定网站数据*/
var_DISPLAYTYPE:=0;
If var_HASGREENCHANNEL=1 then
var_DISPLAYTYPE:=3;
end if;
if var_EXPOFLAG=1 then
var_DISPLAYTYPE:=2;
end if;
if var_CIVIFLAG=1 then
var_DISPLAYTYPE:=1;
End if;
If var_READFLAG=1 then
var_ISREAD:=1;
else
var_ISREAD:=0;
End if;
/*判定是新增还是变更*/
Select nvl(count(*),0) into iCount from umdata.tcinstantmsg where BIZID=var_EVENTID;
If iCount=0 and var_READFLAG =0 then
Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;
Insert into umdata.tcinstantmsg (ID,BIZID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID,DISPLAYTYPE,ISREAD)
values (var_MAXID+1,var_EVENTID,10000,1101,'待办案卷','待办案卷 ',var_CONTENT,pHumanID,var_DISPLAYTYPE,var_ISREAD);
else
if iCount<>0 then
Update umdata.tcinstantmsg set ISREAD=var_ISREAD where ISREAD<>var_ISREAD
and HumanID=pHumanID
and BIZID=var_eventID;
end if;
End if;
commit;
end if;
End loop;
End if;
close fromTable;
/*我的消息,每次刷新前先删除网站中我的消息信息*/
Delete from umdata.tcinstantmsg where MSGTYPEID=1105 and HumanID=pHumanID;
commit;
/*获取我的消息相关信息*/
Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;
open msgcur for
Select MESSAGETEXT,receiveflag from dlmis.trlogmessage
where receiveHumanID=pHumanID
and (receiveflag<>1 or receiveflag is null )
and (deleteflag<>1 or deleteflag is null) and sysdate-senddate<1 and sysdate-senddate>=0;
loop
fetch msgcur into var_CONTENT ,var_READFLAG;
exit when msgcur%notfound;
/*插入我的消息*/
Insert into umdata.tcinstantmsg (ID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID)
values (var_MAXID+1,10000,1105,'我的消息','我的消息',var_CONTENT,pHumanID);
commit;
end loop;
close msgcur;
Exception
When others then
Rollback;
End getSystemNotice;
/
is
iCount INTEGER ;
fromTable sys_refcursor;
msgcur sys_refcursor;
var_MAXID INTEGER ;
var_EVENTSQL CLOB ;
var_EVENTID INTEGER ;
var_HASGREENCHANNEL INTEGER;
var_EXPOFLAG INTEGER;
var_CIVIFLAG INTEGER;
var_READFLAG INTEGER;
var_SQL varchar2(10000);
var_CONTENT VARCHAR2(500) ;
var_DISPLAYTYPE INTEGER ;
var_ISREAD INTEGER ;
Begin
/*待办案卷*/
Select count(*) into iCount from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;
If iCount>0 then
/*获取开启待办案卷箱子的查询语句*/
Select HUMANLISTSQL into var_EVENTSQL from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;
/*获取案卷eventID*/
var_SQL := 'Select eventID from ('||var_EVENTSQL||')';
open fromTable for var_SQL;
loop
fetch fromTable into var_EVENTID;
exit when fromTable%notfound;
if var_EVENTID is not null then
/*获取案卷相关信息*/
Select eventDESC,HASGREENCHANNEL,EXPOFLAG,CIVIFLAG,READFLAG into
var_CONTENT ,
var_HASGREENCHANNEL,
var_EXPOFLAG,
var_CIVIFLAG,
var_READFLAG
from umdata.toEvent where eventID=var_EVENTID;
/*根据案卷信息设定网站数据*/
var_DISPLAYTYPE:=0;
If var_HASGREENCHANNEL=1 then
var_DISPLAYTYPE:=3;
end if;
if var_EXPOFLAG=1 then
var_DISPLAYTYPE:=2;
end if;
if var_CIVIFLAG=1 then
var_DISPLAYTYPE:=1;
End if;
If var_READFLAG=1 then
var_ISREAD:=1;
else
var_ISREAD:=0;
End if;
/*判定是新增还是变更*/
Select nvl(count(*),0) into iCount from umdata.tcinstantmsg where BIZID=var_EVENTID;
If iCount=0 and var_READFLAG =0 then
Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;
Insert into umdata.tcinstantmsg (ID,BIZID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID,DISPLAYTYPE,ISREAD)
values (var_MAXID+1,var_EVENTID,10000,1101,'待办案卷','待办案卷 ',var_CONTENT,pHumanID,var_DISPLAYTYPE,var_ISREAD);
else
if iCount<>0 then
Update umdata.tcinstantmsg set ISREAD=var_ISREAD where ISREAD<>var_ISREAD
and HumanID=pHumanID
and BIZID=var_eventID;
end if;
End if;
commit;
end if;
End loop;
End if;
close fromTable;
/*我的消息,每次刷新前先删除网站中我的消息信息*/
Delete from umdata.tcinstantmsg where MSGTYPEID=1105 and HumanID=pHumanID;
commit;
/*获取我的消息相关信息*/
Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;
open msgcur for
Select MESSAGETEXT,receiveflag from dlmis.trlogmessage
where receiveHumanID=pHumanID
and (receiveflag<>1 or receiveflag is null )
and (deleteflag<>1 or deleteflag is null) and sysdate-senddate<1 and sysdate-senddate>=0;
loop
fetch msgcur into var_CONTENT ,var_READFLAG;
exit when msgcur%notfound;
/*插入我的消息*/
Insert into umdata.tcinstantmsg (ID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID)
values (var_MAXID+1,10000,1105,'我的消息','我的消息',var_CONTENT,pHumanID);
commit;
end loop;
close msgcur;
Exception
When others then
Rollback;
End getSystemNotice;
/