v_usertype in int ,
v_gc_num in varchar2,
v_projectname in varchar2,
v_organid in varchar2,
v_userid in varchar2,
--visualize in varchar2,
v_user_organ varchar2,
V_Sys_User_ID varchar2,
v_pageidex in int,
v_pagesize in int,
v_pagecount out INTEGER)
AS
V_WHERESQL VARCHAR2(2000);
V_SQL VARCHAR2(30000);
BEGIN
V_WHERESQL:=
CASE
WHEN (v_usertype=1 or v_usertype=2) THEN
''
WHEN (v_usertype=3 or v_usertype=4 or v_usertype=6 or v_usertype=7) THEN
' AND a.GC_NUM IN(Select gc_num From operation_project Where sys_organ_id=v_user_organ And State=1)'
WHEN (v_usertype=5 or v_usertype=8) THEN
' AND a.GC_NUM IN (SELECT GC_NUM FROM OPERATION_PROJECT
WHERE ID IN (SELECT GC_NUM FROM OPERATION_PROJECT_USER WHERE SYS_USER_ID = V_Sys_User_ID) And State=1)'
WHEN v_usertype=16 THEN
' And a.gc_num in(Select gc_num From operation_project
Where Sys_Organ_ID IN(Select ID From sys_organ Where sys_organ_type_id=2))'
WHEN v_usertype=19 THEN
' AND a.ID IN (SELECT OPERATION_PROJECT_ID FROM PROJECT_USER
WHERE SYS_ORGAN_ID=v_user_organ and state=2) '
ELSE
' AND 1<>1'
END||
CASE WHEN V_GC_NUM IS NOT NULL THEN ' AND A.GC_NUM LIKE ''%''||UPPER(V_GC_NUM)||''%''' ELSE '' END||
CASE WHEN V_PROJECTNAME IS NOT NULL THEN ' AND A.PROJECTNAME LIKE ''%''||V_PROJECTNAME||''%''' ELSE '' END||
CASE WHEN V_ORGANID IS NOT NULL and V_ORGANID<>'1' THEN ' AND A.SYS_ORGAN_ID=v_organid'
WHEN V_ORGANID='1' THEN ' AND A.sys_user_id in (select id FROM SYS_USER WHERE SYS_USER_TYPE_ID = 3) ' ELSE '' END||
-- CASE WHEN v_visualize IS NOT NULL THEN ' AND A.SYS_ORGAN_ID=v_visualize' ELSE '' END||
CASE WHEN v_userid IS NOT NULL THEN ' AND EXISTS(SELECT 1 FROM OPERATION_PROJECT_USER X WHERE A.ID=X.GC_NUM AND X.TYPE=1 AND X.SYS_USER_ID=v_userid)' ELSE '' END;
DBMS_OUTPUT.PUT_LINE(V_WHERESQL);
V_SQL:='
DECLARE
v_usertype int;
v_gc_num varchar2(100);
V_PROJECTNAME varchar2(1000);
v_organid varchar2(100);
v_userid varchar2(100);
v_user_organ varchar2(100);
V_Sys_User_ID varchar2(100);
v_count int;
BEGIN
v_usertype :=:v_usertype;
v_gc_num :=:v_gc_num;
V_PROJECTNAME :=:V_PROJECTNAME;
v_organid :=:v_organid;
v_userid :=:v_userid;
v_user_organ :=:v_user_organ;
V_Sys_User_ID :=:V_Sys_User_ID;
select count(*) into v_count from operation_project a
inner join sys_organ b on a.sys_organ_id=b.id
inner join mv_emp_gc_num c on a.gc_num=c.gc_num
where a.state=1'||V_WHERESQL||';'||'
end;'
;
EXECUTE IMMEDIATE V_SQL INTO v_pagecount USING v_usertype,v_gc_num,v_projectname,v_organid,v_userid,v_user_organ,V_Sys_User_ID;
