【BOS基础系统_权限】记录一个用户权限报表sql脚本

需求：按照以下报表截图案例来编写sql

--------------准备工作,在查询分析器依次执行-----------------------

--1、执行sql

SELECT  fid,'dsadsadsqadsadsadsadsadsadsa' as FLONGNUMBERSORT,FNUMBER,FLONGNUMBER INTO T_PM_PERMITEM_BK02 FROM T_pm_permitem

--2、执行sql

DELETE FROM T_PM_PERMITEM_BK02

--3、执行方言

INSERT INTO T_PM_PERMITEM_BK02(fid,FLONGNUMBERSORT,FNUMBER,FLONGNUMBER) SELECT  fid,substr(FLONGNUMBER,1,instr(FLONGNUMBER,'!',1,2)-1) as FLONGNUMBERSORT,FNUMBER,FLONGNUMBER FROM T_pm_permitem

---------------报表1：给用户直接分配权限数据整理，依次执行-----------------------------

--1、给用户直接分配权限数据整理且备份到表t_pm_userorgperm_deal_bk

select 

uop.FOWNER as fuserid ,uop.FORGID ,uop.FPERMTYPE,

 p.FOPERATIONTYPE ,

pbk.flongnumbersort,

count(p.FOPERATIONTYPE) as FOPERATIONTYPENUM 

INTO t_pm_userorgperm_deal_bk 

from t_pm_userorgperm uop

INNER JOIN T_pm_permitem p on uop.FPERMITEMID = p.FID 

INNER JOIN T_pm_user u on uop.FOWNER = u.FID 

INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID 

INNER JOIN T_PM_PERMITEM_BK02 pbk on p.FID = pbk.fid

where p.FISLEAF =1 group by p.FOPERATIONTYPE,uop.FOWNER,uop.FORGID,uop.FPERMTYPE,pbk.flongnumbersort;

--查询是否备份成功

SELECT * FROM t_pm_userorgperm_deal_bk;

--2、给用户直接分配权限数据整理后最终展示(授权类型：10代表有权限，20代表被禁用)

SELECT u.FNUMBER as 用户名,u.FNAME_L2 as 用户实名,u.FISDELETE as 用户是否删除, u.FFORBIDDEN as 用户是否被禁用 , bu.FNUMBER as 组织编码,bu.FNAME_l2 as 组织名称,uop.foperationtype as 操作类型,uop.flongnumbersort as 权限分类,

uop.FPERMTYPE as 授权类型, uop.foperationtypenum as 权限数量

  FROM t_pm_userorgperm_deal_bk uop

INNER JOIN T_pm_user u on uop.Fuserid = u.FID 

INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID

----------------------报表2：以下是通过角色分配权限数据整理，依次执行---------------------------

--1、给用户通过角色分配的权限数据整理且备份到表T_pm_userroleorg_deal_bk

SELECT 

uro.FUSERID as fuserid ,uro.FORGID ,rp.FPERMTYPE,

p.FOPERATIONTYPE,

pbk.flongnumbersort,

count(p.FOPERATIONTYPE) as FOPERATIONTYPENUM

INTO T_pm_userroleorg_deal_bk

 FROM T_pm_userroleorg uro 

INNER JOIN T_pm_role r on uro.FROLEID   = r.FID 

INNER JOIN T_pm_roleperm rp on uro.FROLEID  = rp.FROLEID 

INNER JOIN T_pm_user u on uro.FUSERID  = u.FID 

INNER JOIN T_org_baseunit bu on uro.FORGID  = bu.FID 

INNER JOIN T_pm_permitem p on rp.FPERMITEMID  = p.FID 

INNER JOIN T_PM_PERMITEM_BK02 pbk on p.FID = pbk.fid

where p.FISLEAF =1 group by p.FOPERATIONTYPE,uro.FUSERID,uro.FORGID,rp.FPERMTYPE,pbk.flongnumbersort;

--查询是否备份成功

SELECT * FROM T_pm_userroleorg_deal_bk;

--2、给用户通过角色分配的权限数据整理后最终展示(授权类型：10代表有权限，20代表被禁用)

SELECT u.FNUMBER as 用户名,u.FNAME_L2 as 用户实名,u.FISDELETE as 用户是否删除, u.FFORBIDDEN as 用户是否被禁用 ,  bu.FNUMBER as 组织编码,bu.FNAME_l2 as 组织名称,uop.foperationtype as 操作类型,uop.flongnumbersort as 权限分类,

uop.FPERMTYPE as 授权类型, uop.foperationtypenum as 权限数量

  FROM T_pm_userroleorg_deal_bk uop

INNER JOIN T_pm_user u on uop.Fuserid = u.FID 

INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID