-------------添加分销代表团队
--select SystemUserid from SystemUser a where a.FullName='白玉国'
--select * from SystemUserRoles where SystemUserId='A456A3CC-EC07-DE11-8365-00155D000309'
--select * from RoleBase where RoleId='62D9DCAC-61DB-4588-A3C0-76F46E3FE3D6'
--insert into TeamMembership(SystemUserId,teamid) values(@uid,@teamid)
-- insert into SystemUserPrincipals(SystemUserPrincipalId,PrincipalId,SystemUserId)
-- values(NEWID(),@teamid,@uid)
select teamid from TeamBase where [name]='分销代表'
292FBD6F-F532-DE11-98C7-000C29A68834
drop table #t1
drop table #t2
select newid() as nid,
'292FBD6F-F532-DE11-98C7-000C29A68834' as teamid,a.SystemUserId
into #t1
from SystemUser a
join SystemUserRoles b on a.SystemUserId=b.SystemUserId
join RoleBase c on c.RoleId=b.RoleId
where c.Name='销售代表-分销'
select *
into #t2
from #t1
where nid not in(
select a.nid from #t1 a
join TeamMembership b on a.SystemUserId=b.SystemUserId and a.teamid=b.TeamId)
insert into TeamMembership(SystemUserId,teamid)
select SystemUserid,teamid from #t2
insert into SystemUserPrincipals(SystemUserPrincipalId,PrincipalId,SystemUserId)
select nid,teamid,SystemUserid from #t2
select a.FullName from SystemUser a
where a.SystemUserId in (select SystemUserId from #t1)
-------------添加分销经理团队
select teamid from TeamBase where [name]='分销地区经理'
AEC35E95-F532-DE11-98C7-000C29A68834
drop table #t1
drop table #t2
select newid() as nid,
'AEC35E95-F532-DE11-98C7-000C29A68834' as teamid,a.SystemUserId
into #t1
from SystemUser a
join SystemUserRoles b on a.SystemUserId=b.SystemUserId
join RoleBase c on c.RoleId=b.RoleId
where c.Name='地区经理-分销'
select *
into #t2
from #t1
where nid not in(
select a.nid from #t1 a
join TeamMembership b on a.SystemUserId=b.SystemUserId and a.teamid=b.TeamId)
insert into TeamMembership(SystemUserId,teamid)
select SystemUserid,teamid from #t2
insert into SystemUserPrincipals(SystemUserPrincipalId,PrincipalId,SystemUserId)
select nid,teamid,SystemUserid from #t2
select a.FullName from SystemUser a
where a.SystemUserId in (select SystemUserId from #t1)
评论