dbo.QBM_PMissingDisplayRightFill

1   create   procedure QBM_PMissingDisplayRightFill ( @UID_DialogTable varchar(38) ) as begin declare @TableName varchar(30) declare @hashes table
2 (HashWithoutJoin varbinary(20) index Hashes1 (HashWithoutJoin) ) declare @JoinConditionConcat nvarchar(max) declare @HashWithoutJoin varbinary(20) declare
3 @zwischen table ( UID_DialogGroup varchar(38) collate database_default , UID_DialogColumn varchar(38) collate database_default , ColumnName varchar(30
4) collate database_default   , IsTableRightExisting bit  , SelectWhereClause nvarchar(max) collate database_default , ChildTableName varchar(30) collate
5 database_default , JoinCondition nvarchar(max) collate database_default , HashWithoutJoin varbinary(20) , BaseClauseEmpty bit default 0 , index zwischen1
6 (HashWithoutJoin) ) declare @erg table ( UID_DialogGroup varchar(38) collate database_default , UID_DialogColumn varchar(38) collate database_default 
7, ColumnName varchar(30) collate database_default , IsTableRightExisting bit  , SelectWhereClause nvarchar(max) collate database_default , ChildTableName
8 varchar(30) collate database_default  , UID_QBMMissingDisplayRight varchar(38) collate database_default  , UID_DialogTableChild varchar(38) collate database_default
9  , XObjectKey varchar(138) collate database_default   ) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @DebugMessage nvarchar
10(max) declare @Anzahl int BEGIN TRY set nocount on select top 1 @TableName = t.TableName from DialogTable t with (readpast) where t.UID_DialogTable = @UID_DialogTable
11  insert into @zwischen(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName, JoinCondition, HashWithoutJoin
12 , BaseClauseEmpty ) select distinct soll.UID_DialogGroup , soll.UID_DialogColumn , soll.ParentDisplayColumn , sign(len(isnull(istT.UID_DialogTable, ''
13)))  , soll.SelectWhereClause , soll.ChildTableName , soll.JoinCondition , dbo.QBM_FCVStringToHash( concat( soll.UID_DialogGroup , soll.UID_DialogColumn
14 , soll.ParentDisplayColumn , str(sign(len(isnull(istT.UID_DialogTable, '')))) , soll.SelectWhereClause , soll.ChildTableName ) ) , soll.BaseClauseEmpty
15 from ( select  tr.UID_DialogTable , cr.ColumnName  , ref.TableNameFK as TablenameParent , @UID_DialogTable as UID_DialogTableDisplay , cgr.UID_DialogGroup
16 , cr.UID_DialogColumn as UID_DialogColumnChild , @UID_DialogTable as UID_DialogTableParent , ref.UID_DialogColumnDisplay as UID_DialogColumn , ref.ColumnNameDisplay
17 as ParentDisplayColumn , case when tgr.SelectWhereClause > ' ' then 0 else 1 end as BaseClauseEmpty ,  case  when tr.TableName <> @TableName then 'exists (select top 1 1  
18			from '
19 + tr.TableName + ' with (readpast)
20			where '  + case when tgr.SelectWhereClause is null then '' else '(
21' + dbo.QBM_FCVStringToIndent( isnull(tgr.SelectWhereClause
22, '1=1'), 5) + '
23					)
24			and ' end + '@@joinCondition@@' + '
25		) '  else  r.ParentColumn + ' in ( select ' + cr.ColumnName + ' from ' + tr.TableName
26 + ' with (readpast)  where ' + isnull(tgr.SelectWhereClause, '1=1') + ') /* old style */ '  end as SelectWhereClause , tr.TableName as ChildTableName 
27, isnull(tgr.SelectWhereClause, '1=1') as KernelClause  , case  when tr.TableName <> @TableName then tr.TableName + '.' + cr.ColumnName + ' = ' + @TableName
28 + '.' + r.ParentColumn else '' end as JoinCondition    from ( select tabref.TableName as TableNameFK, colref.ColumnName as ColumnNameDisplay, colref.UID_DialogColumn
29 as UID_DialogColumnDisplay    from DialogTable tabref with (readpast) join DialogColumn colref with (readpast) on tabref.UID_DialogTable = @UID_DialogTable
30 and colref.UID_DialogTable = @UID_DialogTable join DialogColumn colpk with (readpast) on colpk.UID_DialogTable = @UID_DialogTable and colpk.IsPKMember
31 = 1 where ( replace(tabref.DisplayPattern, '??', '%%') like '%[%]' + colref.columnname + N'[%]%' or replace(tabref.DisplayPatternLong, '??', '%%') like
32 '%[%]' + colref.columnname + N'[%]%' ) and tabref.IsDeactivatedByPreProcessor = 0 and colref.IsDeactivatedByPreProcessor = 0 group by tabref.TableName
33 , colref.ColumnName , colref.UID_DialogColumn, colpk.UID_DialogColumn, colpk.ColumnName ) as ref join QBM_VQBMRelationALL r on r.UID_DialogTableParent
34 = @UID_DialogTable join DialogTableGroupRight tgr with (readpast) on tgr.UID_DialogTable = r.UID_DialogTableChild and tgr.CanSee = 1 join DialogTable 
35tr with (readpast) on tgr.UID_DialogTable = tr.UID_DialogTable and tr.IsDeactivatedByPreProcessor = 0 join DialogColumnGroupRight cgr with (readpast) on
36 cgr.UID_DialogColumn = r.UID_ChildColumn and tgr.UID_DialogGroup = cgr.UID_DialogGroup and cgr.CanSee = 1 join DialogColumn cr with (readpast) on cr.UID_DialogColumn
37 = cgr.UID_DialogColumn and cr.IsDeactivatedByPreProcessor = 0 and (cr.IsForeignKey = 1 or cr.IsDynamicFK = 1 ) ) as soll left outer join  (  select co.UID_DialogGroup
38, tgr.UID_DialogTable, co.UID_DialogGroupParent  from DialogGroupCollection co with (readpast) join DialogTableGroupRight tgr with (readpast) on co.UID_DialogGroupParent
39 = tgr.UID_DialogGroup and tgr.UID_DialogTable = @UID_DialogTable and tgr.CanSee = 1     ) as istT on soll.UID_DialogTableParent = istT.UID_DialogTable
40 and soll.UID_DialogGroup = istT.UID_DialogGroup  left outer join  (  select co.UID_DialogGroup, tgr.UID_DialogTable, co.UID_DialogGroupParent , cgr.UID_DialogColumn
41 from DialogGroupCollection co with (readpast) join DialogTableGroupRight tgr with (readpast) on co.UID_DialogGroupParent = tgr.UID_DialogGroup and tgr.UID_DialogTable
42 = @UID_DialogTable and tgr.CanSee = 1 join DialogColumnGroupRight cgr with (readpast) on tgr.UID_DialogGroup = cgr.UID_DialogGroup and cgr.CanSee = 1 
43join DialogColumn c with (readpast) on cgr.UID_DialogColumn = c.UID_DialogColumn and c.UID_DialogTable = tgr.UID_DialogTable ) as istC on soll.UID_DialogTableParent
44 = istC.UID_DialogTable and soll.UID_DialogGroup = istC.UID_DialogGroup and soll.UID_DialogColumn = istC.UID_DialogColumn where istC.UID_DialogColumn is
45 null or istT.UID_DialogTable > ' '  insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName
46 ) select distinct  UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName from @zwischen where JoinCondition
47 = '' insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName ) select distinct  z.UID_DialogGroup
48, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting , replace(z.SelectWhereClause, '@@joincondition@@', z.JoinCondition) , z.ChildTableName from
49 @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in ( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count(*
50) = 1 )   insert into @hashes(HashWithoutJoin) select distinct z.HashWithoutJoin from @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in 
51( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count(*) > 1 )  and z.BaseClauseEmpty = 0 select @HashWithoutJoin = 0x01 
52while @HashWithoutJoin is not null begin select @HashWithoutJoin = null select top 1 @HashWithoutJoin = h.HashWithoutJoin from @hashes h order by h.HashWithoutJoin
53 if @HashWithoutJoin is null begin continue end select @JoinConditionConcat = concat('(' , string_agg ( convert(nvarchar(max),z.JoinCondition )  , N' or  '
54 ) within group (order by z.JoinCondition )  , ')' ) from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin insert into @erg(UID_DialogGroup, UID_DialogColumn
55, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName ) select top 1  z.UID_DialogGroup, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting
56 , replace(z.SelectWhereClause, '@@joincondition@@', @JoinConditionConcat) , z.ChildTableName from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin
57 delete  @hashes  where HashWithoutJoin = @HashWithoutJoin end   delete  @hashes  insert into @hashes(HashWithoutJoin) select distinct z.HashWithoutJoin
58 from @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in ( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count
59(*) > 1 )  and z.BaseClauseEmpty = 1 select @HashWithoutJoin = 0x01 while @HashWithoutJoin is not null begin select @HashWithoutJoin = null select top 
601 @HashWithoutJoin = h.HashWithoutJoin from @hashes h if @HashWithoutJoin is null begin continue end select @JoinConditionConcat = string_agg (convert(nvarchar
61(max), N'
62		select top 1 1  
63			from @Tablename@ with (readpast)
64			where ' + z.JoinCondition )  , N'
65		
66		union all 
67				' ) within group (order
68 by z.JoinCondition)  from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting
69, SelectWhereClause,ChildTableName ) select top 1  z.UID_DialogGroup, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting , 'exists (
70			' + replace
71(@JoinConditionConcat, '@Tablename@', z.ChildTableName) + '
72		)' , z.ChildTableName from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin delete
73  @hashes  where HashWithoutJoin = @HashWithoutJoin end  update @erg set UID_DialogTableChild = t.UID_DialogTable from @erg e join DialogTable t with (readpast
74) on e.ChildTableName = t.TableName update @erg set UID_QBMMissingDisplayRight = dbo.QBM_FCVStringToGUID('', CONCAT ( e.UID_DialogGroup, e.UID_DialogColumn
75, e.UID_DialogTableChild, e.SelectWhereClause ) ) , XObjectKey = dbo.QBM_FCVElementToObjectKey1('QBMMissingDisplayRight', '' ,dbo.QBM_FCVStringToGUID(''
76, CONCAT ( e.UID_DialogGroup, e.UID_DialogColumn, e.UID_DialogTableChild, e.SelectWhereClause ) ) ) from @erg e    begin transaction  insert into QBMMissingDisplayRight
77 with (tablockX) (UID_QBMMissingDisplayRight , UID_DialogGroup, UID_DialogColumn, UID_DialogTableChild , IsTableRightExisting, SelectWhereClause , XObjectKey
78 ) select UID_QBMMissingDisplayRight , UID_DialogGroup, UID_DialogColumn, UID_DialogTableChild , IsTableRightExisting, SelectWhereClause , XObjectKey from
79 @erg e where not exists (select top 1 1 from QBMMissingDisplayRight v with (readpast) where v.UID_QBMMissingDisplayRight = e.UID_QBMMissingDisplayRight
80 ) select @Anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'eingefügt ' + @TableName + str(@anzahl) end  update QBMMissingDisplayRight set IsTableRightExisting