dbo.QBM_PDynamicUserForPerson

1   create   procedure QBM_PDynamicUserForPerson (@UID_Person varchar(38) , @UID_QBMProduct varchar(38) , @UID_DialogUser varchar(38) output ) as
2 begin declare @GenProcID varchar(38) declare @XUser nvarchar(64) = object_name(@@procid) declare @XDate datetime = getutcdate() declare @SQLCmd nvarchar
3(max) declare @DebugSwitch int = 0 declare @Startdate datetime = getutcdate() declare @UID_QBMProduct_Intern varchar(38) = isnull(@UID_QBMProduct, '##'
4)   drop table if exists #GroupsAvailable create table #GroupsAvailable (UID_DialogGroup varchar(38) collate database_default primary key (UID_DialogGroup
5) ) drop table if exists #GroupsForPerson create table #GroupsForPerson (UID_SingleGUID varchar(38) collate database_default primary key (UID_SingleGUID
6) ) declare @anzahlGruppen int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext
7('XUser') declare @QBM_BaseRight varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5' SET XACT_ABORT OFF BEGIN TRY set nocount on select @GenProcID = @GenProcID_R
8  select @SQLCmd = concat(  'select g.UID_DialogGroup-- , gl.*
9			from DialogGroup g left outer join DialogGroupInProductLimited gl on g.UID_DialogGroup = gl.UID_DialogGroup
10			where gl.UID_DialogGroup is null
11			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen'
12 , case when dbo.QBM_FGIModuleExists('QER') = 1 then '
13						and g.IsRoleBasedOnly = 1' else '' end, '
14		union
15
16		-- die Gruppen für dieses Produkt
17		select gl.UID_DialogGroup
18			from DialogGroupInProductLimited gl join DialogGroup g on gl.UID_DialogGroup = g.UID_DialogGroup
19			where gl.UID_DialogProduct = '''
20, @UID_QBMProduct_Intern , '''
21			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen' , case when dbo.QBM_FGIModuleExists('QER') = 1 
22then '
23						and g.IsRoleBasedOnly = 1' else '' end, '
24		union
25
26		select ''', @QBM_BaseRight, '''
27		' )  if @DebugSwitch > 0 begin print @sqlcmd end
28 insert into #GroupsAvailable(UID_DialogGroup) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select * from #GroupsAvailable end insert into #GroupsForPerson
29(UID_SingleGuid) select distinct UID_DialogGroup from ( select rp.UID_Org as UID_AERole, c.UID_Org, rp.UID_DialogGroup, rp.treelevel from AERole r join
30 BaseTreeCollectionF c on c.UID_Org = r.UID_AERole join BaseTree  rp on rp.UID_Org = c.UID_ParentOrg join #GroupsAvailable gv on rp.UID_DialogGroup = gv.UID_DialogGroup
31 join PersonInAERole p on p.UID_AERole = c.UID_Org and p.UID_Person = @UID_Person ) td join ( select UID_Org, max(x.treelevel) as maxLevel from ( select
32 rp.UID_Org as UID_AERole, c.UID_Org, rp.UID_DialogGroup, rp.treelevel from AERole r join BaseTreeCollectionF c on c.UID_Org = r.UID_AERole join BaseTree
33  rp on rp.UID_Org = c.UID_ParentOrg join #GroupsAvailable gv on rp.UID_DialogGroup = gv.UID_DialogGroup join PersonInBaseTree  p on p.UID_Org = c.UID_Org
34 and p.UID_Person = @UID_Person ) x group by UID_Org ) v on v.UID_Org = td.UID_Org and v.maxLevel = td.treelevel if @DebugSwitch > 0 begin select * from
35 #GroupsForPerson end  delete  #GroupsForPerson  from #GroupsForPerson g where exists (select top 1 1 from DialogGroupCollection co where g.UID_SingleGuid
36  = co.UID_DialogGroupParent and co.UID_DialogGroup <> co.UID_DialogGroupParent and co.UID_DialogGroup in (select e.UID_SingleGuid from #GroupsForPerson
37 e ) ) and g.UID_SingleGuid  <> @QBM_BaseRight delete  #GroupsForPerson  from #GroupsForPerson g where Not exists (select top 1 1 from DialogGroupCollection
38 co where g.UID_SingleGuid  = co.uid_dialogGroup ) and g.UID_SingleGuid  <> @QBM_BaseRight  if @DebugSwitch > 0 begin select * from #GroupsForPerson end
39 if not exists (select top 1 1 from #GroupsForPerson g where g.UID_SingleGUID = @QBM_BaseRight ) begin insert into #GroupsForPerson(UID_SingleGUID) select
40 @QBM_BaseRight end select @AnzahlGruppen = count(*) from #GroupsForPerson if @AnzahlGruppen = 0 begin  raiserror('#LDS#No valid group passed.|' , 18, 
412) with nowait return end if @DebugSwitch > 0 begin print 'Anzahl Gruppen ' + str(@AnzahlGruppen) end select @UID_DialogUser = null select top 1 @UID_DialogUser
42 = y.UID_DialogUser from ( select x.UID_DialogUser from (  select uig.UID_DialogUser from #GroupsForPerson g join DialogUserInGroup uig on uig.uid_DialogGroup
43 = g.UID_SingleGuid  group by uig.UID_DialogUser having count(*) = @AnzahlGruppen union all  select uig.UID_DialogUser from DialogUserInGroup uig group
44 by uig.UID_DialogUser having count(*) = @AnzahlGruppen ) as x group by x.UID_DialogUser having count(*) = 2 ) as y join DialogUser u on y.UID_DialogUser
45 = u.UID_DialogUser where u.isDynamicUser = 1  if @UID_DialogUser > ' ' begin if @DebugSwitch > 0 begin print 'fertigen User gefunden' end goto fertig 
46end if @DebugSwitch > 0 begin print '-- dann müssen wir einen anlegen' end select @UID_DialogUser = newid()   if exists (select top 1 1 from DialogTable
47 t where t.TableName = 'DialogUser' and t.IsModuleGUIDDefault = 1 ) begin select @UID_DialogUser = dbo.QBM_FCVGuidToTransfer(@UID_DialogUser, 'CCC') end
48  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser if @DebugSwitch > 0 begin print 'vor insert user ' + 
49str(DATEDIFF(ms, @Startdate, getutcdate())) end insert into DialogUser (UID_DialogUser, UserName, XObjectKey, Password, XDateInserted, XDateUpdated, XUserInserted
50, XUserUpdated, CustomRemarks , IsReadOnly, IsDynamicUser ) select @UID_DialogUser, @UID_DialogUser, dbo.QBM_FCVElementToObjectKey1('DialogUser', 'UID_DialogUser'
51, @UID_DialogUser), 'UnableForDialogLogin ', @XDate, @XDate, N'System', @XUser_R, N'Created by System' , 0, 1 exec QBM_PSessionContextSet 'GenProcID', 
52@GenProcID exec QBM_PSessionContextSet 'XUser', @XUser_R if @DebugSwitch > 0 begin print 'vor insert useringroup ' + str(DATEDIFF(ms, @Startdate, getutcdate
53())) end insert into DialogUserInGroup (UID_DialogUser , uid_DialogGroup , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated , XObjectKey) select
54 distinct @UID_DialogUser, g.UID_SingleGuid , @XDate, @XDate, N'System', @XUser, dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogUser', 
55@UID_DialogUser, 'uid_DialogGroup', g.UID_SingleGuid ) from #GroupsForPerson g  where not exists (select top 1 1 from DialogUserInGroup e where e.UID_DialogUser
56 = @UID_DialogUser and e.UID_DialogGroup = g.UID_SingleGuid  ) if @DebugSwitch > 0 begin print 'nach insert useringroup ' + str(DATEDIFF(ms, @Startdate
57, getutcdate())) end fertig: update DialogUser set LastLogin = @XDate  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_DialogUser = @UID_DialogUser
58 if @DebugSwitch > 0 begin print 'nach update user ' + str(DATEDIFF(ms, @Startdate, getutcdate())) end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default
59 declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  EndLabel: exec QBM_PSessionContextSet
60 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
61