CREATE OR REPLACE PACKAGE BODY owb_workspace_manager AS

--global variables

g_isSeeding number :=0;
procedure  checkUserAccessibility(theWorkspaceID in number)  as

canAccess number:=0;
hasSeeded number;
begin

	--workaround: when do seeding, we need to set workspace context even before creation on anything since workpaace object's creation need this info for namespace table... 
        
	select count(*) into hasSeeded from cmpworkspace_v;
	--select count(*) into hasSeeded from owbsys.workspace_assignment;

	if(hasSeeded > 0) then 
        	g_isSeeding :=0;
	else
                g_isSeeding :=1;
	end if;

	select count(*) into canAccess from cmpallwbuser_v where workspaceid = theWorkspaceID and upper(name)=upper(user);
	if(canAccess <1 and g_isSeeding=0) then 
        	raise_application_error(-20004,'You do not have right to access the workspace with workspaceID:'||theWorkspaceID);
       end if;	
	--also prevent end user to set wksp to global workspace if not in seeding..
        --aug 1,06: we allow to set_workspace to global from OWB itself e.g. register language... we will regulate this from workspace_management package which is open to other user... 
  --     if(theWorkspaceID = 1 and g_isSeeding=0) then 
    --    raise_application_error(-20007,'You are trying to set workspace to GLOBAL_WORKSPACE which is just for internal usage.');

	--end if;
	
end checkUserAccessibility;

function isWorkspaceAdmin(theWorkspaceID in number ) return number as 

 isAdmin number :=0; 
 m_userId number;
begin 
	
	if(g_isSeeding=1) then
           return 1; 
	end if;

	select elementid into m_userId from cmpwbuser_v where upper(name) = upper(user);
	
	--now do the is admin info
	select count(*) into isAdmin 
	from cmpwbrole_v r, CMPRoleAssignment_v a	
	where a.wbuser = m_userId and 
	      r.elementid = a.wbrole and 
              r.isAdmin ='1';

	return isAdmin;	
exception 
 WHEN OTHERS THEN
	raise;
	--raise_application_error(-20006,'When set workspace context, error happened:'||SUBSTR(SQLERRM,1,400));

end isWorkspaceAdmin;

function getWorkspaceName(theWorkspaceID number) return varchar2 

as 
theWorkspaceName varchar2(200);

begin 
	if(g_isSeeding=1 and theWorkspaceID = 1) then
           return 'GLOBAL_WORKSPACE';
	end if;


	select name into theWorkspaceName from cmpworkspace_v where elementid = theWorkspaceID;
	return theWorkspaceName;

exception 
    WHEN OTHERS THEN
	raise;
	--raise_application_error(-20005,'When set workspace context, error happened:'||SUBSTR(SQLERRM,1,400));

end getWorkspaceName;


 
function getWorkspaceOwner(theWorkspaceID number) return varchar2 as

theOwner varchar2(200);
begin 


  if(g_isSeeding=1 and theWorkspaceID = 1) then
           return 'OWBSYS';
  end if;


select user_name into theOwner from workspace_assignment where workspace_id = theWorkspaceID and isWorkspaceOwner = '1';
	
return theOwner;


exception 
    WHEN OTHERS THEN
	raise;
	--raise_application_error(-20006,'When set workspace context, error happened:'||SUBSTR(SQLERRM,1,400));

end getWorkspaceOwner;


--added this function since we want to set context on some sys privilegs...
 function has_system_privilege(
    p_privilege_name varchar2
  ) return boolean
  as 

  pos number;
  hasSysFromAnyRole number;
  userHasSysPriv number;
  result boolean:=FALSE;
  theSysId number;
  userId number;
 
 begin 

	-- check the workspace has been set or not
	--checkWorkspaceBeSetted();
	
	--check whether the sys priv name is valid
	--validateSysPrivName(p_privilege_name );
	--first check if the user is workspace admin,he should have
	if(upper( sys_context('owb_workspace','workspace_owner')) = upper(user)) then 
	  return TRUE;
	end if;
	
       --then check if user himself has the priv
	select sysPrivId into theSysId from 	SYS_PRIV_ID_TO_NAME_TAB where upper(sysPrivName) = upper(p_privilege_name);
	pos := theSysId+1;

	select count(*) into userHasSysPriv from cmpwbuser_v where upper(name) = upper(user) and  SUBSTR(SYSPRIVSACCESSCHARMAP, pos, 1) = '1';



	if(userHasSysPriv >0) then 
	  return TRUE;
	end if;

	--finally check whether role has the priv
        select elementid into userId from cmpwbuser_v where upper(name) = upper(user);  
		
	select count(*) into hasSysFromAnyRole from cmpwbrole_v where elementid in (select  wbrole from  cmproleassignment_V where wbuser= userId ) and SUBSTR(SYSPRIVSACCESSCHARMAP, pos, 1) = '1' ;
	if(hasSysFromAnyRole >0) then 
	 return TRUE;
	end if;
	
	return FALSE;
  	

 exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20008,'When query system privilege:'||p_privilege_name ||' ,error happened:'||SUBSTR(SQLERRM,1,400));


end has_system_privilege;

procedure  checkWorkspaceExists(theWorkspaceID number)

as 
hasSeeded number:=0;
existing number :=0;
begin 


     
	select count(*) into hasSeeded from cmpworkspace_v;
	--select count(*) into hasSeeded from owbsys.workspace_assignment;

	if(hasSeeded > 0) then 
        	g_isSeeding :=0;
	else
                g_isSeeding :=1;
	end if;


	select count(*) into existing from workspace_assignment where workspace_id =theWorkspaceID;
	if(existing <1 AND g_isSeeding =0) then 
  		raise_application_error(-20011,'The workspace with id:'||theWorkspaceID || ' does not exist.' );
	end if;

exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20010,'procedure checkWorkspaceExists encountered error:'||SUBSTR(SQLERRM,1,400));


end checkWorkspaceExists;

procedure unset_workspace
as
owb_ctx varchar2(255) default 'owb_workspace';
begin
  dbms_session.set_context(owb_ctx, 'workspace_owner', null);
  dbms_session.set_context(owb_ctx, 'workspaceName', null);
  dbms_session.set_context(owb_ctx, 'workspaceID', null);
  dbms_session.set_context(owb_ctx, 'usernotnull', null);
  dbms_session.set_context(owb_ctx, 'isWorkspaceAdmin', null);
  dbms_session.set_context(owb_ctx, 'hasPublicViewPriv', null);
  dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_VIEW',null);
  dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_EXECUTION',null);
  dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_DEPLOYMENT',null);
end unset_workspace;

procedure set_workspace(theWorkspaceID in number)

as 

owb_ctx varchar2(255) default 'owb_workspace';
isAdmin number;
theWorkspaceName varchar2(200);
theWorkspaceOwner varchar2(200);
begin 
  if (theWorkspaceID is null) then
    unset_workspace;
    return;
  end if;
	
  --need to check whether the workspace exists or not...
  checkWorkspaceExists(theWorkspaceID);
  --first make sure the user can access the workspaceid
   if( (user is null) OR  (trim(user) is null ))  then  -- fix bug 6735804 
         null; 
  elsif(upper(user) != 'OWBSYS' AND upper(user) != 'SYS') then 
    checkUserAccessibility(theWorkspaceID);
  end if;
  
  dbms_session.set_context(owb_ctx, 'workspaceID', theWorkspaceID);
  
  theWorkspaceName :=getWorkspaceName(theWorkspaceID);
  dbms_session.set_context(owb_ctx, 'workspaceName', theWorkspaceName);
  
  theWorkspaceOwner :=getWorkspaceOwner(theWorkspaceID);
  dbms_session.set_context(owb_ctx, 'workspace_owner',theWorkspaceOwner);

   --since the following is related cmpwbuser, but OWBSYS is not cmpwbuser, so not to set those attributes from the context, but if needed, can do it in the following if section... 
  if(upper(user) = 'OWBSYS' OR upper(user) = 'SYS'  OR  user is null OR trim(user) is null) then  -- fix bug 6735804
        dbms_session.set_context(owb_ctx, 'usernotnull', 'SYS');
        dbms_session.set_context(owb_ctx, 'isWorkspaceAdmin', 1);
        dbms_session.set_context(owb_ctx, 'hasPublicViewPriv', 1);
        dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_VIEW',1);
        dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_EXECUTION',1);
        dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_DEPLOYMENT',1);
	return;
  else
        dbms_session.set_context(owb_ctx, 'usernotnull', user);
  end if;
	
  isAdmin:=isWorkspaceAdmin(theWorkspaceID);

  dbms_session.set_context(owb_ctx, 'isWorkspaceAdmin', isAdmin);

  if(g_isSeeding =1) then 
	return; --do not need the following info
   end if;

  if(has_system_privilege('ACCESS_PUBLICVIEW_BROWSER')) then
    dbms_session.set_context(owb_ctx, 'hasPublicViewPriv', 1);
  else
    dbms_session.set_context(owb_ctx, 'hasPublicViewPriv', 0);
  end if;


  --aug 3, 06: add control center privileges into the context
  if(has_system_privilege('CONTROL_CENTER_VIEW')) then 
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_VIEW',1);
 else
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_VIEW',0);
  end if;

 if(has_system_privilege('CONTROL_CENTER_EXECUTION')) then 
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_EXECUTION',1);
 else
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_EXECUTION',0);
  end if;

 if(has_system_privilege('CONTROL_CENTER_DEPLOYMENT')) then 
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_DEPLOYMENT',1);
 else
	dbms_session.set_context(owb_ctx, 'CONTROL_CENTER_DEPLOYMENT',0);
  end if;


EXCEPTION 
       WHEN OTHERS THEN
	raise;
	--raise_application_error(-20001,'When set workspace context, error happened:'||SUBSTR(SQLERRM,1,400));
end set_workspace;



function get_workspace return number
as
 wkspid number;
 
begin
	
	select sys_context('owb_workspace','workspaceID') into wkspid from dual; 

	if(wkspid ='') then

	 wkspid:= -1;

	end if;

	return wkspid;

EXCEPTION 
       WHEN OTHERS THEN
	raise;
	--raise_application_error(-20002,'When set workspace context, error happened:'||SUBSTR(SQLERRM,1,400));

end get_workspace;



--drop a contxt
--drop context owb_workspace;
--view a contxt
-- select * from session_context;

--use following step to set up workspace context
--SQL> call owb_workspace_manager.set_workspace(1);
 
--Call completed.
 
--SQL>  select sys_context('owb_workspace','workspaceID') from dual;
 
--SYS_CONTEXT('OWB_WORKSPACE','WORKSPACEID')
--------------------------------------------------------------------------------
--1
--SQL> select sys_context('owb_workspace','isWorkspaceAdmin') from dual;
 
--SYS_CONTEXT('OWB_WORKSPACE','ISWORKSPACEADMIN')
--------------------------------------------------------------------------------
--1
 -- select sys_context('owb_workspace','workspaceName') from dual;
 


END owb_workspace_manager;