...
select T.name as name, T.alias as alias from (select distinct 0 as name, "省联社" as alias from perm_inst union select distinct 1 as name, "市联社" as alias from perm_instunion select distinct 2 as name,"区支行" as alias from perm_inst)T where T.name >=用户机构层级
(此SQL仅供示例参考,实际sql需要根据自己的机构逻辑进行设置)
该sql语句通过select distinct 0 as name, "省联社" as alias from perm_inst union select distinct 1 as name, "市联社" as alias from perm_instunion select distinct 2 as name,"区支行" as alias from perm_inst,将用户的三个层级查询出来
...
(1)创建一个下拉框参数,名称为“选择机构_级联”,控件类型为下拉框
(2)勾选‘允许多选’
(3)备选值设置为:
select INST_NAME from perm_inst where INST_level= 选择层级(参数) and (INST_NAME= CurrentUserDefaultDepartmentName() or INST_NAME1= CurrentUserDefaultDepartmentName() or INST_NAME2= CurrentUserDefaultDepartmentName() or INST_NAME3= CurrentUserDefaultDepartmentName() )
(此SQL仅供示例参考,实际sql需要根据自己的机构逻辑进行设置)
通过上述的SQL,结合实际业务的机构表,先将“选择层级”中对应的INST_LEVEL的INST_NAME查询出来,并且根据后面的INST_NAME= CurrentUserDefaultDepartmentName() or INST_NAME1= CurrentUserDefaultDepartmentName() or INST_NAME2= CurrentUserDefaultDepartmentName() or INST_NAME3= CurrentUserDefaultDepartmentName()条件,将用户所属机构所选层级的机构或下级机构查询出来
(4)默认值设置为:
select INST_NAME from perm_inst where INST_level= 选择层级(参数) and (INST_NAME= CurrentUserDefaultDepartmentName() or INST_NAME1= CurrentUserDefaultDepartmentName() or INST_NAME2= CurrentUserDefaultDepartmentName() or INST_NAME3= CurrentUserDefaultDepartmentName() ) limit 1
此SQL与备选值的SQL是一致的,只是增加一个limit 1,限制默认值只查询1个机构。
几种情况:
1.参数中直接包含所有的机构
2.先选择层级,再下拉选择对应层级的机构
...