市场服务二部”修改为“市场二部”、
“市场服务三部”修改为“市场三部”、
“县域服务一部”修改为“县域一部”、
“县域服务二部”修改为“县域二部”、“综合管理部”修改为“综合业务部”。
SELECT * FROM SysDepart WHERE BranchID='3030'
UPDATE S SET S.DepartName='综合业务部'
FROM SysDepart S WHERE S.BranchID='3030' AND S.DepartName='综合管理部'
------------------------------------------------------------
select S.ID,S.[Account],S.[RealName],B.[BranchName] "地市"
from SysUser S,Branch B
WHERE S.[BranchID]=B.[ID]
AND B.[BranchName]='苏州业务部'
AND S.[RealName] in ('缪艳','龚建国','王靖东')
ORDER BY S.[RealName] ASC
SELECT DISTINCT S.[RealName],CASE Enable WHEN '1' THEN '在用'
WHEN '0' THEN '注销'
ELSE '注销' END 状态 ,
B.[BranchName] "地市"
from SysUser S,UserRole U , SysRole R ,Branch B
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND S.[BranchID]=B.[ID]
ORDER BY B.[BranchName] ASC
--------------------------------------------------------------------------------
--查看权限关联的角色
select SysRole.RoleName,* from RoleAuthority
left join SysRole
on SysRole.ID=RoleAuthority.RoleID where AuthorityID='2231'
--查看权限关联的用户
SELECT SysUser.RealName 用户名
,Branch.BranchName 地市
FROM SysUser
LEFT JOIN Branch ON SysUser.BranchID = Branch.ID
WHERE SysUser.ID IN
( SELECT DISTINCT(UserID) FROM dbo.UserRole WHERE RoleID IN
( SELECT RoleID FROM dbo.RoleAuthority WHERE AuthorityID='2153'))
--查看角色关联用户
查询全省导出权限
select SysUser.RealName,SysRole.RoleName, SysDepart.DepartName,Branch.BranchName from UserRole
left join SysUser on sysuser.ID=UserRole.UserID
left join SysRole on SysRole.ID=UserRole.RoleID
left join SysDepart on SysDepart.ID=SysUser.DepartID
left join Branch on Branch.ID=sysuser.BranchCode
WHERE RoleID = '107'
order by Branch.BranchCode asc
-----角色 ID (导出 权限)
UserRole ID in (104,375)
select * from UserRole WHERE [UserID]='6229'
select * from UserRole WHERE [Creater] LIKE '%bai%'
select * from UserRole WHERE [Creater]='test'
select * from Branch
select * from UserRole where RoleID in (104,375)
select * from SysRole
WHERE [ID] IN
( select [RoleID] from UserRole WHERE [UserID]='6229' )
---查看用户拥有的角色权限：
select * from SysRole
WHERE [ID] IN
(select [RoleID] from UserRole WHERE [UserID]='6229')
----------------------------------------------------------------------
select * from SysDepart where BranchCode='3160' AND ID='3279'
delete from SysDepart where BranchCode='3160' AND ID='3279'
ORDER BY BranchCode ASC
select * from COM_CITY WHERE Name LIKE '%泰州%'
select * from SysUser WHERE [RealName] LIKE '%柏%'
select ID,[RealName],[DepartID] from SysUser WHERE [RealName] LIKE '%柏%'
select * from SysUser where ID in ( select UserID from UserRole where RoleID in (104,375) )
----查询人员 地市中所属 部门：
select u.id userid,u.RealName , d.DepartName from SysDepart d ,SysUser u
where d.ID=U.DepartID
--and d.BranchID='3080'
and u.RealName like '%左鹏鹏%'
------------------------------------------------
select S.[Account],S.[RealName],S.[Telephone],S.[Creater],S.[CreateDate],
R.[RoleName] "权限" ,
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S,UserRole U , SysRole R , SysDepart D,Branch B
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND D.ID=S.DepartID
AND S.[BranchID]=B.[ID]
AND s.RealName like '%姚丹丹%'
AND ( R.[RoleName] LIKE '%经理%' or R.[RoleName] LIKE '%主管%' )
--------------------------------------------------------------------------------
查询每个部门有哪些人：
SELECT DISTINCT S.[Account],S.[RealName],
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S,UserRole U , SysRole R , SysDepart D,Branch B
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND D.ID=S.DepartID
AND S.[BranchID]=B.[ID]
AND B.[BranchName]='苏州业务部'
AND S.[RealName] IN
(
'周丽芳',
'吴寅',
'齐丹丹',
'王寅',
'胡伟倩'
)
ORDER BY d.DepartName ASC
---------------------------------------------------------------
江苏银商：
select S.[Account],count(R.[RoleName]) "权限"
from SysUser S,UserRole U , SysRole R , SysDepart D
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND D.ID=S.DepartID
--AND s.[RealName] LIKE '5703%'
GROUP BY S.[Account]
ORDER BY S.[Account] ASC
select * from SysRole
WHERE [ID] IN
( select * from UserRole WHERE [UserID]='6229')
select * from SysUser WHERE [RealName] LIKE '%华慧%'
select * from dbo.Branch
select S.[Account],S.[RealName],R.[RoleName] "权限"
from SysUser S,UserRole U , SysRole R
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND s.[RealName] LIKE '%柏%'
select * from SysDepart where BranchCode='3160'
---业务部对应的代码表：
select * from Branch
----------OSS 角色对应的 菜单查询
select SysRole.RoleName,SysAuthority.DisplayName
from RoleAuthority
left join SysRole
on RoleAuthority.RoleID=SysRole.ID
left join SysAuthority on
RoleAuthority.AuthorityID=SysAuthority.ID
--where SysAuthority.DisplayName LIKE '%派工部门%'
where SysRole.RoleName like '%派工部门查询%'
------------- 查询全省 工单（非自己的）
派工部门查询 这个角色 打勾
-------------------------------------------------------------------------
员工 在部门 所属的角色:
select S.[Account],S.[RealName],S.[Telephone],S.[Creater],S.[CreateDate],
R.[RoleName] "权限" ,
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S,UserRole U , SysRole R , SysDepart D,Branch B
WHERE S.[ID]=U.[UserID]
AND U.[RoleID]=R.[ID]
AND D.ID=S.DepartID
AND S.[BranchID]=B.[ID]
AND d.DepartName <>'终端部员工'
AND S.[RealName]='余良'
ORDER BY S.[Account] ASC
------------------------------------------------------------
乡镇代理点用户:
select DISTINCT
S.[Account],S.[RealName],S.[Telephone],S.[Creater],S.[CreateDate],
--R.[RoleName] "权限" ,
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S LEFT JOIN UserRole U
ON S.[ID]=U.[UserID]
LEFT JOIN SysRole R
ON U.[RoleID]=R.[ID]
LEFT JOIN SysDepart D
ON D.ID=S.DepartID
LEFT JOIN Branch B
ON S.[BranchID]=B.[ID]
WHERE d.DepartName LIKE '%乡镇%'
ORDER BY S.[Account] ASC
-------------------------------------------------------------------------
select
S.[Account],S.RealName,S.ID,
d.DepartName "部门"
from SysUser S LEFT JOIN dbo.SysDepart d
ON D.ID=S.DepartID
WHERE d.DepartName LIKE '%乡镇%'
select * from UserRole WHERE [UserID]='6349'
select * from SysUser WHERE [RealName] LIKE '%柏%'
-----------------------------------------------------------------
select SysRole.RoleName,SysAuthority.DisplayName
from RoleAuthority
left join SysRole
on RoleAuthority.RoleID=SysRole.ID
left join SysAuthority on
RoleAuthority.AuthorityID=SysAuthority.ID
where SysRole.RoleName like '%测试角色%'
----如果是南京的客户经理 ：需要 “客户回访”
维护经理
UserRole ID in (104,375)
用户表
的用户权限有104和375的
都是维护经理
无法派工 需要在“分组管理” 里面，添加成员， 才能派工
-----------------------------------------------
工单分组的： 看不到其他部门的工单 权限
你记一下
这是工单那块的
只要涉及到工单那块
市场部内勤
市场部主管
超级管理员
派工部门查询
------------------------------------------------------
常州渠道商：
SELECT * FROM COM_BASECODE WHERE TYPE='Channelcompany'
AND BaseCode IN ('1','4','25','8')
select * from SysUser WHERE ChannelCompany IN ('1','4','25','8')
select DISTINCT
S.[Account],S.[RealName],S.Mobile,S.[Creater],S.[CreateDate],
--R.[RoleName] "权限" ,
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S LEFT JOIN UserRole U
ON S.[ID]=U.[UserID]
LEFT JOIN SysRole R
ON U.[RoleID]=R.[ID]
LEFT JOIN SysDepart D
ON D.ID=S.DepartID
LEFT JOIN Branch B
ON S.[BranchID]=B.[ID]
WHERE S.ChannelCompany IN ('1','4','25','8')
AND S.BranchCode='3040'
--ORDER BY S.[Account] ASC
UNION
select DISTINCT
S.[Account],S.[RealName],S.Mobile,S.[Creater],S.[CreateDate],
--R.[RoleName] "权限" ,
d.DepartName "部门",B.[BranchName] "地市"
from SysUser S LEFT JOIN UserRole U
ON S.[ID]=U.[UserID]
LEFT JOIN SysRole R
ON U.[RoleID]=R.[ID]
LEFT JOIN SysDepart D
ON D.ID=S.DepartID
LEFT JOIN Branch B
ON S.BranchCode=B.[ID]
WHERE (d.DepartName LIKE '%吉连%'
OR d.DepartName LIKE '%东大集成%'
OR d.DepartName LIKE '%美宽达%'
OR d.DepartName LIKE '%富锐%')
AND S.BranchCode='3040'
ORDER BY S.[Account] ASC