dbo.QBM_PQBMRelationExpand_One

1   create   procedure QBM_PQBMRelationExpand_One ( @UID_QBMRelation varchar(38) ) as begin declare @ModuleOwner varchar(3) declare @UID_QBMRelationBase
2 varchar(38)  declare @UID_DialogTableParent varchar(38) declare @UID_ParentColumn varchar(38) declare @UID_DialogTableChild varchar(38) declare @UID_ChildColumn
3 varchar(38) declare @Xdate datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid)  declare @tabletypeParent varchar(1) declare @tabletypeChild
4 varchar(1) declare @parentcolumn varchar(30) declare @Childcolumn varchar(30) declare @DebugSwitch int = 0 declare @CountItems int declare @RelationIDab4
5 nvarchar(64) declare @UID_QBMRelationAb4 varchar(38) declare @RelationID nvarchar(64) declare @ParentRestriction nchar(2) , @ParentExecuteBy nchar(1) 
6, @ParentAllowUpdate bit , @ChildRestriction nchar(2) , @ChildExecuteBy nchar(1) , @ChildAllowUpdate bit declare @QBMRelation table ( RelationID nvarchar
7(64) collate database_default , UID_QBMRelation varchar(38) collate database_default , UID_ParentColumn varchar(38) collate database_default , ParentRestriction
8 varchar(2) collate database_default , ParentExecuteBy varchar(1) collate database_default , ParentAllowUpdate bit , UID_ChildColumn varchar(38) collate
9 database_default , ChildRestriction varchar(2) collate database_default , ChildExecuteBy varchar(1) collate database_default , ChildAllowUpdate bit , 
10UID_QBMRelationBase varchar(38) collate database_default ) SET XACT_ABORT OFF BEGIN TRY select @ModuleOwner = dbo.QBM_FGIDBOwner() if @UID_QBMRelation 
11like '%-Base' begin if @DebugSwitch > 0 begin print '@UID_QBMRelation ' + @UID_QBMRelation + ' einkürzen' end   select top 1 @UID_QBMRelation = substring
12(@UID_QBMRelation, 1, LEN(@UID_QBMRelation) - len('-Base')) , @UID_QBMRelationBase = v.UID_QBMRelationBase , @UID_DialogTableParent = v.UID_DialogTableParent
13 , @tabletypeParent = tp.TableType , @UID_ParentColumn = v.UID_ParentColumn , @parentcolumn = v.ParentColumn , @UID_DialogTableChild = vb.UID_DialogTableChild
14 , @tabletypeChild = tcb.TableType , @UID_ChildColumn = vb.UID_ChildColumn , @Childcolumn = vb.ChildColumn , @RelationID = v.RelationID  , @RelationIDab4
15 = substring(substring(vb.relationid, 4,60), 1, LEN(substring(vb.relationid, 4,60)) - len('-Base') ) , @UID_QBMRelationAb4 = substring(substring(vb.UID_QBMRelation
16, 4,34), 1, LEN(substring(vb.UID_QBMRelation, 4,34)) - len('-Base') ) , @ParentRestriction = v.ParentRestriction , @ParentExecuteBy = v.ParentExecuteBy
17 , @ParentAllowUpdate = v.ParentAllowUpdate , @ChildRestriction = vb.ChildRestriction , @ChildExecuteBy = vb.ChildExecuteBy , @ChildAllowUpdate = vb.ChildAllowUpdate
18 from QBM_VQBMRelation v join DialogTable tp on v.UID_DialogTableParent = tp.UID_DialogTable join DialogTable tc on v.UID_DialogTableChild = tc.UID_DialogTable
19 join QBM_VQBMRelation vb on vb.UID_QBMRelation = @UID_QBMRelation join DialogTable tpb on vb.UID_DialogTableParent = tpb.UID_DialogTable join DialogTable
20 tcb on vb.UID_DialogTableChild = tcb.UID_DialogTable where v.UID_QBMRelationBase = @UID_QBMRelation end else begin select top 1 @UID_QBMRelationBase =
21 v.UID_QBMRelationBase , @UID_DialogTableParent = v.UID_DialogTableParent , @tabletypeParent = tp.TableType , @UID_ParentColumn = v.UID_ParentColumn , 
22@parentcolumn = v.ParentColumn , @UID_DialogTableChild = v.UID_DialogTableChild , @tabletypeChild = tc.TableType , @UID_ChildColumn = v.UID_ChildColumn
23 , @Childcolumn = v.ChildColumn , @RelationID = v.RelationID  , @RelationIDab4 = substring(v.relationid, 4,60) , @UID_QBMRelationAb4 = substring(v.UID_QBMRelation
24, 4,34) , @ParentRestriction = v.ParentRestriction , @ParentExecuteBy = v.ParentExecuteBy , @ParentAllowUpdate = v.ParentAllowUpdate , @ChildRestriction
25 = v.ChildRestriction , @ChildExecuteBy = v.ChildExecuteBy , @ChildAllowUpdate = v.ChildAllowUpdate from QBM_VQBMRelation v join DialogTable tp on v.UID_DialogTableParent
26 = tp.UID_DialogTable join DialogTable tc on v.UID_DialogTableChild = tc.UID_DialogTable where v.UID_QBMRelation = @UID_QBMRelation if @UID_QBMRelationBase
27 > ' ' begin if @DebugSwitch > 0 begin print 'nicht leere Basis Relation' end goto endlabel end end if @DebugSwitch > 0 begin print '@RelationID ' + @RelationID
28 print '@RelationIDab4 ' + @RelationIDab4 print '@UID_QBMRelationAb4 ' + @UID_QBMRelationAb4 print '@UID_QBMRelationBase ' + isnull(@UID_QBMRelationBase
29, '<null>') print @UID_DialogTableParent + ' ' + @tabletypeParent + ' ' +@parentcolumn + ' ' + @uid_parentColumn print @UID_DialogTableChild + ' ' + @tabletypeChild
30 + ' ' +@Childcolumn + ' ' + @uid_ChildColumn end if @DebugSwitch > 0 begin print 'wir machen was' end if @UID_DialogTableChild in ('QER-T-BaseTreeCollection'
31 , 'QER-T-BaseTreeExcludesBaseTree' ) begin goto endLabel end  if @tabletypeParent in ('B', 'T') and @tabletypeChild = 'T' begin if @DebugSwitch > 0 begin
32 print 'Es bleibt wie es ist' end goto endlabel end  if @tabletypeParent = 'B' and @tabletypeChild = 'B' begin if @DebugSwitch > 0 begin print 'View auf beiden Seiten finden'
33 print ' gefundenes wird relation, angegebenes wird basis' end insert into @QBMRelation (RelationID , UID_QBMRelation , UID_ParentColumn , ParentRestriction
34, ParentExecuteBy, ParentAllowUpdate , UID_ChildColumn , ChildRestriction, ChildExecuteBy, ChildAllowUpdate , UID_QBMRelationBase ) select @ModuleOwner
35 + @RelationIDab4 + '-' + tvr.TableName , left(@ModuleOwner + @UID_QBMRelationab4 + '-' + substring(dbo.QBM_FCVBinaryToString( dbo.QBM_FCVAnyToHash(upper
36(tvl.TableName)), 0), 3,40), 36) , cvl.UID_DialogColumn , @ParentRestriction, @ParentExecuteBy, @ParentAllowUpdate , cvr.UID_DialogColumn , @ChildRestriction
37, @ChildExecuteBy, @ChildAllowUpdate , @UID_QBMRelation from DialogTable tbr join DialogTable tvr on tbr.UID_DialogTable = @UID_DialogTableChild and tvr.UID_DialogTableBase
38 = tbr.UID_DialogTable join DialogColumn cvr on cvr.UID_DialogTable = tvr.UID_DialogTable and cvr.UID_BaseColumn = @UID_ChildColumn  join DialogTable tbl
39 on tbl.UID_DialogTable = @UID_DialogTableParent join DialogTable tvl on tvl.UID_DialogTableBase = tbl.UID_DialogTable join DialogColumn cvl on cvl.UID_DialogTable
40 = tvl.UID_DialogTable and cvl.UID_BaseColumn = @UID_ParentColumn  and ( (cvl.ColumnName = cvr.ColumnName  and tvl.UID_DialogTable <> tvr.UID_DialogTable
41 )  or (tbr.UID_DialogTable = tbl.UID_DialogTable and tvr.UID_DialogTable = tvl.UID_DialogTable  and dbo.QBM_FCVGUIDToModuleOwner(@UID_ChildColumn) <> 
42'CCC' ) )     goto BasisDefiniert end   if @tabletypeParent = 'T' and @tabletypeChild = 'B' begin if @DebugSwitch > 0 begin print 'View auf Child-Seite finden'
43 print ' gefundenes wird relation, angegebenes wird basis' end  insert into @QBMRelation (RelationID , UID_QBMRelation , UID_ParentColumn , ParentRestriction
44, ParentExecuteBy, ParentAllowUpdate , UID_ChildColumn , ChildRestriction, ChildExecuteBy, ChildAllowUpdate , UID_QBMRelationBase ) select @ModuleOwner
45 + @RelationIDab4 + '-' + tvr.TableName , left(@ModuleOwner + @UID_QBMRelationab4 + '-' + substring(dbo.QBM_FCVBinaryToString( dbo.QBM_FCVAnyToHash(upper
46(tvr.TableName)), 0), 3,40) , 36) , cTl.uid_dialogcolumn , @ParentRestriction, @ParentExecuteBy, @ParentAllowUpdate , cvr.UID_DialogColumn , @ChildRestriction
47, @ChildExecuteBy, @ChildAllowUpdate , dbo.QBM_FGIModulehigher(dbo.QBM_FCVGUIDToModuleOwner(cbr.UID_DialogColumn)  , dbo.QBM_FCVGUIDToModuleOwner(ctl.UID_DialogColumn
48 ) ) + @UID_QBMRelationab4 from DialogTable tbr join DialogTable tvr on tbr.UID_DialogTable = @UID_DialogTableChild and tvr.UID_DialogTableBase = tbr.UID_DialogTable
49 join DialogColumn cvr on cvr.UID_DialogTable = tvr.UID_DialogTable join DialogColumn cbr on cbr.UID_DialogTable = tbr.UID_DialogTable and cvr.UID_BaseColumn
50 = cbr.UID_DialogColumn and cbr.UID_DialogColumn = @UID_ChildColumn join DialogTable tTl on tTl.UID_DialogTable = @UID_DialogTableParent join DialogColumn
51 cTl on cTl.UID_DialogColumn = @UID_ParentColumn goto BasisDefiniert end    if @tabletypeParent = 'V' and @tabletypeChild = 'B' begin if @DebugSwitch >
52 0 begin print 'Dann alle Parents beim Child finden und multiplizieren' end  insert into @QBMRelation (RelationID , UID_QBMRelation , UID_ParentColumn 
53, ParentRestriction, ParentExecuteBy, ParentAllowUpdate , UID_ChildColumn , ChildRestriction, ChildExecuteBy, ChildAllowUpdate , UID_QBMRelationBase ) 
54 select @ModuleOwner + @RelationIDab4 + '-' + tvr.TableName , left(@ModuleOwner + @UID_QBMRelationab4 + '-' + substring(dbo.QBM_FCVBinaryToString( dbo.QBM_FCVAnyToHash
55(upper(tvr.TableName)), 0), 3,40) , 36) , cvl.UID_DialogColumn , @ParentRestriction, @ParentExecuteBy, @ParentAllowUpdate , cvr.UID_DialogColumn , @ChildRestriction
56, @ChildExecuteBy, @ChildAllowUpdate , @UID_QBMRelation + '-Base' from DialogTable tvl join DialogColumn cvl on tvl.UID_DialogTable = @UID_DialogTableParent
57 and tvl.UID_DialogTable = cvl.UID_DialogTable and cvl.UID_DialogColumn = @UID_ParentColumn join DialogTable tbr on tbr.UID_DialogTable = @UID_DialogTableChild
58 join DialogTable tvr on tvr.UID_DialogTableBase = tbr.UID_DialogTable join DialogColumn cbr on cbr.UID_DialogColumn = @UID_ChildColumn and cbr.UID_DialogTable
59 = tbr.UID_DialogTable join DialogColumn cvr on cvr.UID_BaseColumn = cbr.UID_DialogColumn and cvr.UID_DialogTable = tvr.UID_DialogTable goto BasisDefiniert
60 end   if @tabletypeParent = 'V' and @tabletypeChild = 'T' begin if @DebugSwitch > 0 begin print 'nichts zu erweitern, kann nur passieren wenn auch die View neu definiert wurde'
61 end goto endlabel end if @tabletypeParent = 'T' and @tabletypeChild = 'V' begin if @DebugSwitch > 0 begin print 'kann beim erweitern nicht neu dazu kommen'
62 end goto endlabel end   if @tabletypeParent = 'V' and @tabletypeChild = 'V' begin if @DebugSwitch > 0 begin print 'kann beim erweitern nicht neu dazu kommen'
63 end goto endlabel end  BasisDefiniert: if @DebugSwitch > 0 begin print 'nach datenermittlung' select * from @QBMRelation end delete  @QBMRelation from
64 QBMRelation r join @QBMRelation rn on r.UID_ChildColumn = rn.UID_ChildColumn and r.UID_ParentColumn = rn.UID_ParentColumn where r.UID_QBMRelation <> rn.UID_QBMRelation
65 if @DebugSwitch > 0 begin print 'nach Ausputzen vorhandener Beziehungen' select * from @QBMRelation end  insert into QBMRelation ( RelationID , UID_QBMRelation
66 , UID_ParentColumn , ParentRestriction , ParentExecuteBy , ParentAllowUpdate , UID_ChildColumn , ChildRestriction , ChildExecuteBy , ChildAllowUpdate 
67, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey , UID_QBMRelationBase , XTouched ) select rn.RelationID , rn.UID_QBMRelation , rn.UID_ParentColumn
68 , rn.ParentRestriction , rn.ParentExecuteBy , rn.ParentAllowUpdate , rn.UID_ChildColumn , rn.ChildRestriction , rn.ChildExecuteBy , rn.ChildAllowUpdate
69 , @Xdate, @Xdate, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey1('QBMRelation', 'UID_QBMRelation', rn.UID_QBMRelation) , rn.UID_QBMRelationBase , 'I'
70 from @QBMRelation rn where Not exists (select top 1 1 from QBMRelation re where re.UID_QBMRelation = rn.UID_QBMRelation ) select @CountItems = @@ROWCOUNT
71 if @DebugSwitch > 0 begin print 'Zeilen eingefügt ' + str(@CountItems) end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar
72(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 
73