概述

## 概述
   组织架构设计用户表、用户详情表、岗位(编制)表、组织机构(表)、代理分配表等各表的关系，实现用户信息管理、组织架构调整、系统功能权限、操作权限、数据权限分配等功能。完善组织架构设计，实现系统各身份间**信息交互**与**流程运转**，通用软件定义完成**有效监管企业所有管理思想**。
　　设计用户模型通用redis缓存用户信息，用户session中只存储用户缓存id，通知id获取用户模型信息，每次登录根据约定id重写缓存。

![](/media/202405/2024-05-09_134739_213996.png)

1. 用户登录验证,获取用户密码进行验证
```sql
SELECT fuserid,fusernameid,fmbphone,femail,fpassword FROM sys_user WHERE fuserid='当前登录fuserid'
```
> 引入元素概念设计，用户可配置允许多种登录方式，如：用户id、用户姓名id,手机号,邮箱 等方式。
配置登录元素 $element.set.login.username=[fuserid,fusernameid,fmbphone,femail]
> ```sql
SELECT $sqlfunc.join($element.set.login.username,","）,fpassword FROM sys_user_info WHERE $sqlfunc.or($element.set.login.username,'当前登录用户名值')
```

2. 获取用户信息
```sql
SELECT fuserid,fusername,...必要用户信息字段 FROM sys_user_info WHERE fuserid='当前登录fuserid'
```

3. 获取用户编制号
```sql
SELECT fbzid,fbzname FROM sys_bz WHERE fuserid = '当前登录fuserid' AND fstate = 0
```

4. 获取用户所在组织机构sys_org所有forgguid(垂直向上)集合
```
配置sys_org数据集dsOrg，调用 dsOrg.queryTree方法，传入条件 fuserid = '当前登录fuserid'
```

5. 获取用户所在岗位(角色)表sys_role所有froleguid(垂直向上)集合
```
配置sys_role数据集dsRole，调用 dsRole.queryTree方法，根据第3条计算出用户所有岗位(编制)号集合作为传入条件 froleguid = '当前登录用户岗位(编制)号集合'
```

6. 获取用户功能权限列表
> 基于第4、5步计算结果查询。
需要返回哪些菜单表字段，根据实际情况修改
> ```sql
SELECT fresguid, fresname, frespguid, frestype, fresurl, forderindex, fisshow, fisactive, ficonclass, ficoncolor, fnote ,factionoperates FROM ( 
select D.*,C.factionoperates from(select * sys_resource_org where forgguid in [第3步计算forgguid集合])C
INNER JOIN sys_resource D ON C.fresguid = D.fresguid 
UNION 
select R.*,SR.factionoperates from(select * sys_resource_role where froleguid in [第4步计算froleguid集合])SR
LEFT JOIN sys_resource R ON SR.fresguid = R.fresguid
) sys_resources;
> ```
旧语句已过期，仅供参考
> ```sql
SELECT fresguid, fresname, frespguid, frestype, fresurl, forderindex, fisshow, fisactive, ficonclass, ficoncolor, fnote ,factionoperates 
FROM ( SELECT D.*,C.factionoperates 
FROM ( SELECT DISTINCT SUBSTRING_INDEX( SUBSTRING_INDEX(a.mSize,'/',b.help_topic_id+1),',',-1) AS FFF 
FROM (SELECT forgidpath AS mSize FROM sys_org WHERE fuserid="当前登录fuserid" ) a 
JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(a.mSize) - LENGTH(REPLACE(a.mSize,'/',''))+1) AND b.help_topic_id>0) A 
LEFT JOIN sys_org B ON A.FFF=B.forgidpath 
LEFT JOIN sys_resource_org C ON B.forgguid = C.forgguid 
INNER JOIN sys_resource D ON C.fresguid = D.fresguid 
UNION SELECT R.*,SR.factionoperates FROM sys_org SO 
LEFT JOIN sys_resource_role SR ON SO.froleguid = SR.froleguid 
LEFT JOIN sys_resource R ON SR.fresguid = R.fresguid WHERE SO.fuserid="当前登录fuserid" AND R.fresguid IS NOT NULL ) sys_resources;
```

7. 获取用户菜单
> 基于第6步计算结果中间层过滤。
根据客户端参数返回PC菜单、手机菜单
旧语句已过期，仅供参考
> ```sql
SELECT * FROM(
SELECT DISTINCT C.* FROM
(SELECT C.fresguid,C.factionoperates FROM
(SELECT forgguid FROM sys_org A WHERE EXISTS 
 (SELECT forgidpath FROM sys_org WHERE fuserid = '当前登录fuserid' AND LEFT(forgidpath, LENGTH(A.forgidpath)) = A.forgidpath)) A 
LEFT JOIN sys_resource_org C ON A.forgguid = C.forgguid 
UNION
SELECT C.fresguid,C.factionoperates FROM
(SELECT froleguid FROM sys_org WHERE fuserid = '当前登录fuserid') A 
LEFT JOIN sys_resource_role C ON A.froleguid = C.froleguid) A 
LEFT JOIN sys_resource B ON A.fresguid = B.fresguid 
INNER JOIN sys_resource C ON LEFT(B.frestreeno, LENGTH(C.frestreeno)) = C.frestreeno 
WHERE 
CASE
WHEN 'PC' = 'PC' 
THEN B.fisshow IN (1, 3, 5) 
AND C.fisshow IN (1, 3, 5) 
WHEN (('PC' = 'mobile') OR ('RnProject.'='RnProject.')) 
THEN B.fisshow IN (0,2, 3) 
AND C.fisshow IN (0,2, 3) 
AND B.frestype != 'dir' 
AND C.frestype != 'dir' 
END 
UNION ALL
SELECT DISTINCT B.* FROM
(SELECT C.fresguid,C.factionoperates FROM
(SELECT forgguid FROM sys_org A WHERE EXISTS 
 (SELECT forgidpath FROM sys_org WHERE fuserid = '当前登录fuserid' AND LEFT(forgidpath, LENGTH(A.forgidpath)) = A.forgidpath)) A 
LEFT JOIN sys_resource_org C ON A.forgguid = C.forgguid 
UNION
SELECT C.fresguid,C.factionoperates FROM
(SELECT froleguid FROM sys_org WHERE fuserid = '当前登录fuserid') A 
LEFT JOIN sys_resource_role C ON A.froleguid = C.froleguid) A 
LEFT JOIN sys_resource B ON A.fresguid = B.fresguid 
WHERE 
CASE 
WHEN 'PC' = 'PC'
THEN B.fisshow IN (0, 4, 6)
WHEN 'PC' = 'mobile'
THEN 1=0
END
)T
ORDER BY forderindex
```

8. 获取用户数据权限列表
> 基于第4、5步计算结果查询。
需要返回哪些策略字段，根据实际情况修改
> ```SQL
SELECT D.* FROM ( 
select D.* from(select * sys_resource_org where forgguid in [第3步计算forgguid集合])C
INNER JOIN sys_dataaccess D ON C.fdaschemeid = D.fdaschemeid 
UNION 
select R.* from(select * sys_resource_role where froleguid in [第4步计算froleguid集合])SR
LEFT JOIN sys_dataaccess R ON SR.fdaschemeid = R.fdaschemeid
) sys_resources;
> ```
> 旧语句已过期，仅供参考
> ```sql
SELECT * FROM(
SELECT D.* FROM (SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(a.mSize,'/',b.help_topic_id+1),',',-1)  AS FFF  FROM  
(SELECT forgidpath AS mSize FROM sys_org WHERE fuserid='当前登录fuserid' ) a  JOIN  mysql.help_topic b  ON b.help_topic_id < (LENGTH(a.mSize) - LENGTH(REPLACE(a.mSize,'/',''))+1) AND b.help_topic_id >0) A 
INNER JOIN sys_org B  ON A.FFF=B.forgidpath 
INNER JOIN sys_resource_org C ON B.forgguid = C.forgguid
INNER JOIN sys_dataaccess D ON C.fdaschemeid = D.fdaschemeid
UNION
SELECT R.* FROM sys_org SO
INNER JOIN sys_resource_role SR ON SO.froleguid = SR.froleguid
INNER JOIN sys_dataaccess R ON SR.fdaschemeid = R.fdaschemeid
WHERE SO.fuserid='当前登录fuserid'
)DA
```

9. 获取用户代理功能列表
> 根据第6步获取的用户功能权限列表
以下语句查出授权指定代理人代理记录，逐条解析代理记录，通过踢出或抽取方法计算拥有的代理功能
> ```sql
SELECT fagentguid,fagentnumber,fauthuserid,fauthuserbz,fresurl,factionoperates,fproxytype 
FROM sys_agent WHERE fagentuserid = '代理工号fuserid' AND fstarttime < NOW() AND ffinishtime > NOW() AND fstate = 0 AND fauditpoint = 1
```

10. 获取用户当前编制直属上级
> 根据第4步获取结果，以当前编制号为条件找出当前编制父节点机构负责人
判断当前用户id与负责人用户id是否相同，是继续向上找，不同即为改编制直属上级记录，可获取直属上级用户id(fuserid),编制号(fbzid)
当前用户机构记录取消存储直属上级，数据变更不好同步更新。

11. 获取用户所有编制直属上级
> 根据第5步获取结果，获取该用户所有编制号
循环第10步骤，获取直属上级用户id集合，用户编制集合