fr_user_post

@title 帆软用户所在部门及其职位的视图 @author 2023072432 @date 20240829 @reference 帆软11表结构(https://help.fanruan.com/finereport/doc-view-3151.html)

SELECT M.userId,M.roleId
	,FU.userName,FU.realAlias,FU.realName
	,FR.department,FR.post,FR.departmentId,FR.fullPath
	,SUBSTRING(FR.fullPath,1,36) AS F1
	,(SELECT name FROM fine_department WHERE id = SUBSTRING(FR.fullPath,1,36)) AS F1T
	,SUBSTRING(FR.fullPath,39+1,36) AS F2
	,(SELECT name FROM fine_department WHERE id = SUBSTRING(FR.fullPath,39+1,36)) AS F2T
	,SUBSTRING(FR.fullPath,39*2+1,36) AS F3
	,(SELECT name FROM fine_department WHERE id = SUBSTRING(FR.fullPath,39*2+1,36)) AS F3T
	,SUBSTRING(FR.fullPath,39*3+1,36) AS F4
	,(SELECT name FROM fine_department WHERE id = SUBSTRING(FR.fullPath,39*3+1,36)) AS F4T
FROM (SELECT roleId,userId FROM fine_user_role_middle WHERE roleType = '1') M -- 用户角色中间表，1表示部门职位
LEFT JOIN (SELECT id,userAlias,userName,realAlias,realName FROM fine_user) FU ON M.userId = FU.id --用户表
LEFT JOIN (
	SELECT FDR.id,FDR.fullPath,FD.id AS departmentId,FD.name AS department,FP.name AS post 
	FROM (SELECT id,departmentId,postId,fullPath FROM fine_dep_role) FDR -- 部门职位的中间表
	LEFT JOIN (SELECT id,name,parentId FROM fine_department) FD ON FDR.departmentId = FD.id -- 部门表
	LEFT JOIN (SELECT id,name FROM fine_post) FP ON FDR.postId = FP.id -- 职位表
) FR ON M.roleId = FR.id

fr_report_department

@title 帆软报表部门视图 @author 2023072432 @date 20240918 @reference 帆软11表结构(https://help.fanruan.com/finereport/doc-view-3151.html)

SELECT * FROM (
	SELECT *
		,ISNULL((SELECT displayName FROM fine_authority_object WHERE id = SUBSTRING(Z.fullPath,27,36)),'/') AS F1T
		,ISNULL((SELECT displayName FROM fine_authority_object WHERE id = SUBSTRING(Z.fullPath,27 + 39*1,36)),'/') AS F2T
		,ISNULL((SELECT displayName FROM fine_authority_object WHERE id = SUBSTRING(Z.fullPath,27 + 39*2,36)),'/') AS F3T
		,ISNULL((SELECT displayName FROM fine_authority_object WHERE id = SUBSTRING(Z.fullPath,27 + 39*3,36)),'/') AS F4T
	FROM (
		SELECT ISNULL(FA.authorityEntityId,FO.id) AS authorityEntityId
			,FO.fullPath,FO.displayName,FO.path,FO.sortIndex
			-- ,FA.roleId
			-- ,FR.fullPath,FR.departmentId
			,FR.department + '-' + ISNULL(FR.post,'') AS DEPART
		FROM (
			SELECT authorityEntityId,roleType,roleId FROM fine_authority -- 权限表，只记录当前目录被授予的权限
			WHERE authority = 2 -- 允许访问
			AND authorityType = 1 -- 查看权限
			AND authorityEntityType = 0 -- 目录权限
			AND roleType = 1 -- 只查部门职位
		) FA 
		LEFT JOIN (
			SELECT FDR.id,FDR.fullPath,FD.id AS departmentId,FD.name AS department,FP.name AS post 
			FROM (SELECT id,departmentId,postId,fullPath FROM fine_dep_role) FDR -- 部门职位的中间表
			LEFT JOIN (SELECT id,name,parentId FROM fine_department) FD ON FDR.departmentId = FD.id -- 部门表
			LEFT JOIN (SELECT id,name FROM fine_post) FP ON FDR.postId = FP.id -- 职位表
		) FR ON FA.roleId = FR.id
		RIGHT JOIN ( -- 以已挂载报表为准
			SELECT id,expandType,displayName,fullPath,path,sortIndex FROM fine_authority_object -- 已挂载报表
			WHERE expandType = '102' -- 报表文件
			AND fullPath NOT LIKE 'decision-directory-root-_-c1107973-c11d-42ac-b192-a2d70c81b4b3%' -- 去除DEMO路径
		) FO ON FA.authorityEntityId = FO.id
	) Z
) Z
-- WHERE displayName LIKE '%桶收发%'
-- ORDER BY LEFT(path,3),F1T,F2T,F3T,F4T,sortIndex,DEPART

fr_customer_role

@title 帆软自定义角色视图 @author 2023072432 @date 20240830 @reference 帆软11表结构(https://help.fanruan.com/finereport/doc-view-3151.html)

SELECT M.userId,M.roleId
	,FU.userName,FU.realAlias,FU.realName
	,FCR.name
FROM (SELECT roleId,userId FROM fine_user_role_middle WHERE roleType = '2') M -- 用户角色中间表，2表示自定义角色
LEFT JOIN (SELECT id,userAlias,userName,realAlias,realName FROM fine_user) FU ON M.userId = FU.id -- 用户表
LEFT JOIN (SELECT * FROM FINE_CUSTOM_ROLE WHERE enable = 1) FCR ON M.roleId = FCR.id -- 角色表