XinYu_RZMES/.svn/pristine/17/177ad1b45e5c9e40c280d5e5540dd51cc97648a5.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="角色使用权限设定">

	<query id="UIZ020030.Menu.Select" desc="获取菜单" fetchSize="10">
		<![CDATA[
			SELECT LEVEL, MNSCR_ID, MNSCR_NM FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH MNSCR_ID = '*'
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID
			ORDER SIBLINGS BY MNSCR_DSP_SEQ
		]]>
	</query> 
	<query id="UIZ020030.User.MnScr.Select" desc="获取用户菜单/界面" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			START WITH MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_USERPRIV
				WHERE USER_CD IN (?, ?))				-- 参数：用户、角色代码
			CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID		-- 查找父菜单
			UNION
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			START WITH P_MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_USERPRIV
				WHERE USER_CD IN (?, ?))				-- 参数：用户、角色代码
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID		-- 查找子菜单
		]]>
	</query> 
	<query id="UIZ020030.Dept.MnScr.Select" desc="获取部门菜单/界面" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			START WITH MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
				WHERE DT_CD IN (
					SELECT DEPT_CD FROM TBZ01_DEPT
					START WITH DEPT_CD = ?				-- 参数：部门代码
					CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
			CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID		-- 查找父菜单
			UNION
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			START WITH P_MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
				WHERE DT_CD IN (
					SELECT DEPT_CD FROM TBZ01_DEPT
					START WITH DEPT_CD = ?				-- 参数：部门代码
					CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID		-- 查找子菜单
		]]>
	</query> 
	<query id="UIZ020030.Role.Select" desc="角色查询" fetchSize="10">
		<![CDATA[
			SELECT
				A.USER_CD ROLE_CD,				-- 角色代码
				A.USER_NM ROLE_NM,				-- 角色名称
				A.DEPT_CD,						-- 部门代码
				A.LOCKED_YN,					-- 是否被锁定
				A.MOD_ID,
				A.MOD_DATE,
				B.DEPT_NM
			FROM TBZ01_USER A, TBZ01_DEPT B
			WHERE ROLEUSER_TP = 'R'
			  AND A.DEPT_CD IN (
			  	SELECT DEPT_CD FROM TBZ01_DEPT
			  	WHERE NVL(LOCKED_YN, 'N') = 'N'
				START WITH DEPT_CD = ?			-- 参数：部门代码
			  	CONNECT BY PRIOR DEPT_CD = P_DEPT_CD)
			  AND A.DEPT_CD = B.DEPT_CD
			  AND A.USER_NM||'&' LIKE ?||'%'	-- 参数：角色名
			ORDER BY MOD_DATE
		]]>
	</query> 
	<query id="UIZ020030.Menu.R.Select" desc="R 权限" fetchSize="10">
		<![CDATA[
			SELECT
				LEVEL,
				A.MNSCR_ID,			-- 菜单/画面ID
				A.MNSCR_NM,			-- 菜单/画面名称
				A.MNSCR_TP,			-- 菜单/菜单区分
				B.FCNRS_ID FC_ID,	-- 权限ID
				NVL2(B.FCNRS_ID, 'Y', 'N') CHK,
				B.MOD_ID,
				B.MOD_DATE,
				B.FCNRS_ID FCNRS_REF
			FROM TBZ01_MNSCR A, (
				SELECT * FROM TBZ01_USERPRIV
				WHERE FCNRS_ID LIKE 'R%'
				  AND USER_CD = ?) B					-- 参数：角色代码
			WHERE A.MNSCR_ID = B.MNSCR_ID(+)
			START WITH A.MNSCR_ID = ?					-- 参数：MNSCR_ID
			CONNECT BY PRIOR A.MNSCR_ID = A.P_MNSCR_ID
			ORDER SIBLINGS BY A.MNSCR_DSP_SEQ
		]]>
	</query> 
	<query id="UIZ020030.Menu.F.Select" desc="F 功能" fetchSize="10">
		<![CDATA[
			SELECT
				LEVEL,
				A.MNSCR_ID,			-- 菜单/画面ID
				A.MNSCR_NM,			-- 菜单/画面名称
				A.MNSCR_TP,			-- 菜单/菜单区分
				B.FC_ID,			-- 功能 ID
				NVL2(C.FC_ID, 'Y', 'N') CHK,
				C.MOD_ID,
				C.MOD_DATE,
				DECODE(A.MNSCR_TP, 'M', 'Y', NVL2(C.FC_ID, NVL2(C.MOD_ID, B.FC_ID, 'Y'), B.FC_ID)) FCNRS_REF
			FROM TBZ01_MNSCR A
				LEFT JOIN TBZ01_SCRFN B ON (A.MNSCR_ID = B.SCR_ID)
				LEFT JOIN (
					SELECT DISTINCT P.MNSCR_ID,
						NVL(R.FC_ID, P.FCNRS_ID) FC_ID,
						NVL2(R.FC_ID, '', P.MOD_ID) MOD_ID,
						NVL2(R.FC_ID, '', P.MOD_DATE) MOD_DATE
					FROM TBZ01_RSDTL R RIGHT JOIN (
						SELECT DISTINCT M.MNSCR_ID, U.FCNRS_ID, U.MOD_ID, U.MOD_DATE
						FROM TBZ01_MNSCR M, (
							SELECT X.MNSCR_ID, X.FCNRS_ID, X.MOD_ID, X.MOD_DATE
							FROM TBZ01_USERPRIV X
							INNER JOIN TBZ01_FCNRS Y ON (X.FCNRS_ID = Y.FCNRS_ID)
							WHERE USER_CD IN (?, ?)	-- 参数：角色代码，用户代码
							  AND NVL(Y.LOCKED_YN, 'N') = 'N') U
						WHERE U.MNSCR_ID IN (
							SELECT MNSCR_ID FROM TBZ01_MNSCR
							START WITH MNSCR_ID = M.MNSCR_ID
							CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID)
						START WITH M.MNSCR_ID = U.MNSCR_ID
						CONNECT BY PRIOR M.MNSCR_ID = M.P_MNSCR_ID) P
					ON (R.RS_ID = P.FCNRS_ID)) C
				ON (B.SCR_ID = C.MNSCR_ID AND B.FC_ID = C.FC_ID)
			WHERE (A.MNSCR_TP = 'M' OR B.FC_ID IS NOT NULL)
			START WITH A.P_MNSCR_ID = ?				-- 参数：MNSCR_ID
			CONNECT BY PRIOR A.MNSCR_ID = A.P_MNSCR_ID
			ORDER SIBLINGS BY A.MNSCR_DSP_SEQ
		]]>
	</query> 

	<query id="UIZ020030.Role.Update" desc="角色更新" fetchSize="10">
		<![CDATA[
			UPDATE TBZ01_USER SET
				USER_NM = ?,	-- 角色名称
				DEPT_CD = ?,
				LOCKED_YN = ?,	-- 是否被锁定
				MOD_ID = ?,		-- 修改者ID
				MOD_DATE = TO_CHAR(SYSTIMESTAMP, 'yyyymmdd')	-- 修改日期
			WHERE USER_CD = ?	-- 角色代码
		]]>
	</query> 
	<query id="UIZ020030.RolePriv.Update" desc="角色权限更新" fetchSize="10">
		<![CDATA[
			UPDATE TBZ01_USERPRIV SET
				FCNRS_ID = ?,	-- 权限ID
				MOD_ID = ?,		-- 修改者ID
				MOD_DATE = TO_CHAR(SYSTIMESTAMP, 'yyyymmdd')	-- 修改日期
			WHERE USER_CD = ?	-- 使用者ID
			  AND MNSCR_ID = ?	-- 菜单/画面ID
			  AND FCNRS_ID = ?	-- 权限ID
		]]>
	</query> 

	<query id="UIZ020030.Role.Insert" desc="角色插入" fetchSize="10">
		<![CDATA[
			INSERT INTO TBZ01_USER
			( USER_CD, USER_NM, DEPT_CD, LOCKED_YN, MOD_ID, MOD_DATE, ROLEUSER_TP )
			-- 角色代码, 角色名称, 是否被锁定
			VALUES( ?, ?, ?, ?, ?, TO_CHAR(SYSTIMESTAMP, 'yyyymmdd'), 'R' )
		]]>
	</query> 
	<query id="UIZ020030.RolePriv.Insert" desc="角色权限插入" fetchSize="10">
		<![CDATA[
			INSERT INTO TBZ01_USERPRIV
			( USER_CD, MNSCR_ID, FCNRS_ID, MOD_ID, MOD_DATE )
			-- 使用者ID,	 菜单/画面ID, 功能权限ID
			VALUES( ?, ?, ?, ?, TO_CHAR(SYSTIMESTAMP, 'yyyymmdd') )
		]]>
	</query> 

	<query id="UIZ020030.RolePriv.Delete" desc="角色权限删除" fetchSize="10">
		<![CDATA[
			DELETE FROM TBZ01_USERPRIV
			WHERE USER_CD = ?	-- 使用者ID
			  AND MNSCR_ID = ?	-- 菜单/画面ID
			  AND FCNRS_ID = ?	-- 权限ID
		]]>
	</query> 
    
</queryMap>