XinYu_RZMES/.svn/pristine/a2/a2dd7131b61fd2c18a3802276646ce37b0e31538.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="用户录入及使用权限设定">

	<query id="UIZ020050.UserRole.Select" desc="用户角色" fetchSize="10">
		<![CDATA[
			SELECT
				USER_CD,		-- 角色代码
				USER_NM			-- 角色名称
			FROM TBZ01_USER
			WHERE NVL(ROLEUSER_TP, 'R') = 'R'
			  AND NVL(LOCKED_YN, 'N') = 'N'
			  AND 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)
			ORDER BY MOD_DATE
		]]>
	</query> 

	<query id="UIZ020050.User.Select" desc="按姓名及部门获取用户" fetchSize="10">
		<![CDATA[
			SELECT NVL2(B.USER_CD, A.LVL+1, A.LVL) "LEVEL",
				NVL(B.USER_CD, A.DEPT_NM) USER_CD,		-- 用户代码
				B.USER_NM,								-- 用户名称
				NVL2(B.USER_CD, '**********', '') M_USER_PW,
				UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(NVL(B.USER_PW, '1'))) USER_PW,	-- 用户密码
				B.DEPT_CD,								-- 部门代码
				B.DEPT_MNG_YN,							-- 部门管理
				B.ROOT_AMN_YN,							-- 最高管理
				B.ROLE_CD,
				B.LOCKED_YN,							-- 是否被锁定
				B.LOCKED_YN AS LOCKED,					-- 是否被锁定
				B.MOD_ID,								-- 修改人姓名
				B.MOD_DATE,								-- 修改日期
				NVL2(B.USER_CD, A.DEPT_NM, '') DEPT_NM	-- 部门名称
			FROM (
				SELECT LEVEL LVL, DEPT_CD, DEPT_NM
				FROM TBZ01_DEPT
				WHERE NVL(LOCKED_YN, 'N') = 'N'
				START WITH DEPT_CD = ?					-- 参数：部门代码
				CONNECT BY PRIOR DEPT_CD = P_DEPT_CD) A, (
				
				SELECT * FROM (
					SELECT 2 CD_TP, USER_CD, USER_NM, USER_PW, DEPT_CD,
						DEPT_MNG_YN, ROOT_AMN_YN, ROLE_CD, LOCKED_YN, MOD_ID, MOD_DATE
					FROM TBZ01_USER
					WHERE ROLEUSER_TP = 'U'
					  AND USER_NM||'&' LIKE ?||'%'		-- 参数：用户代码
					UNION ALL
					SELECT DISTINCT 1, '', '', '', X.DEPT_CD, '', '', '', '', '', ''
					FROM TBZ01_DEPT X, TBZ01_USER Y
					WHERE Y.ROLEUSER_TP = 'U'
					  AND X.DEPT_CD = Y.DEPT_CD(+)
					  AND Y.USER_NM||'&' LIKE ?||'%')) B	-- 参数：用户代码
			WHERE A.DEPT_CD = B.DEPT_CD(+)
			ORDER BY A.LVL, A.DEPT_CD, B.CD_TP
		]]>
	</query> 

	<query id="UIZ020050.User.Update" desc="用户更新" fetchSize="10">
		<![CDATA[
			UPDATE TBZ01_USER SET
				USER_NM = ?,		-- 用户名称
				USER_PW = UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(NVL(?, '111111'))),	-- 用户密码
				DEPT_CD = ?,		-- 部门代码
				DEPT_MNG_YN = ?,	-- 部门管理
				ROOT_AMN_YN = ?,	-- 最高管理
				ROLE_CD = ?,
				LOCKED_YN = ?,		-- 是否被锁定
				MOD_ID = ?,			-- 修改者ID
				MOD_DATE = TO_CHAR(SYSTIMESTAMP, 'yyyymmdd')	-- 修改日期
			WHERE USER_CD = ?		-- 用户代码
		]]>
	</query> 

	<query id="UIZ020050.User.Insert" desc="" fetchSize="10">
		<![CDATA[
			INSERT INTO TBZ01_USER
			( USER_CD, USER_NM, USER_PW, DEPT_CD, DEPT_MNG_YN, ROOT_AMN_YN, ROLE_CD, LOCKED_YN, MOD_ID, MOD_DATE, ROLEUSER_TP )
			-- 用户代码, 用户名称, 部门代码, 部门管理, 最高管理, 用户密码, 是否被锁定
			VALUES( ?, ?, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(NVL(?, '111111'))), ?, ?, ?, ?, ?, ?, TO_CHAR(SYSTIMESTAMP, 'yyyymmdd'), 'U' )
		]]>
	</query> 
	<query id="UIZ020050.UserPriv.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="UIZ020050.UserPriv.Delete" desc="" fetchSize="10">
		<![CDATA[
			DELETE FROM TBZ01_USERPRIV
			WHERE USER_CD = ?	-- 使用者ID
			  AND MNSCR_ID = ?	-- 菜单/画面ID
			  AND FCNRS_ID = ?	-- 权限ID
		]]>
	</query> 
    
</queryMap>