dbo.QBM_PMultilanguageDoubleClear

1   create   procedure QBM_PMultilanguageDoubleClear as begin declare @ich varchar(3) declare @DebugSwitch int = 0 declare @CountRows int declare
2 @DieDoppelten1 QBM_YMultilanguageDouble   declare @DieDoppelten2 QBM_YMultilanguageDouble declare @DieDoppelten3 QBM_YMultilanguageDouble declare @MDKMLGuidReplace
3 table (UID_DialogMultiLanguageNew varchar(38) collate database_default  , UID_DialogMultiLanguageOld varchar(38) collate database_default  , XUserInserted
4 nvarchar(64) collate database_default , XUserUpdated nvarchar(64) collate database_default , XDateInserted datetime , XDateUpdated datetime ) SET XACT_ABORT
5 OFF BEGIN TRY set nocount on select @ich = dbo.QBM_FGIDBOwner()  insert into @DieDoppelten1 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn
6, m.UID_DialogCulture, m.EntryKey from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT
7 = 0 begin goto endlabel end insert into @MDKMLGuidReplace(UID_DialogMultiLanguageNew, UID_DialogMultiLanguageOld , XDateInserted, XDateUpdated, XUserInserted
8, XUserUpdated ) select ml.UID_DialogMultiLanguage, mh.UID_DialogMultiLanguage , mh.XDateInserted, mh.XDateUpdated, mh.XUserInserted, mh.XUserUpdated  
9from DialogMultiLanguage ml join DialogMultiLanguage mh on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and
10 ml.EntryKey = mh.EntryKey join DialogColumn c with (readpast) on ml.UID_DialogColumn = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable
11 = t.UID_DialogTable join QBMModuleDef mol with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef
12 moh with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage
13 and  mol.ModuleName <> moh.ModuleName and  exists (Select top 1 1 from QBMModuleDependCollection co where left(co.UID_ModuleFollower, 3) = moh.ModuleName
14 and left(co.UID_ModulePredecessor, 3) = mol.ModuleName )  and exists (Select top 1 1 from @DieDoppelten1 je where ml.UID_DialogColumn = je.UID_DialogColumn
15 and ml.UID_DialogCulture = je.UID_DialogCulture and ml.EntryKey = je.EntryKey ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'lineare gelöscht '
16 + str(@CountRows) end  if exists (select top 1 1 from sys.tables t where t.name = 'MDKMLGuidReplace' ) begin insert into MDKMLGuidReplace(UID_DialogMultiLanguageNew
17, UID_DialogMultiLanguageOld, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated) select UID_DialogMultiLanguageNew, UID_DialogMultiLanguageOld, 
18XDateInserted, XDateUpdated, XUserInserted, XUserUpdated from @MDKMLGuidReplace end delete DialogMultiLanguage from DialogMultiLanguage ml join @MDKMLGuidReplace
19 me on ml.UID_DialogMultiLanguage = me.UID_DialogMultiLanguageOld  insert into @DieDoppelten2 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn
20, m.UID_DialogCulture, m.EntryKey from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT
21 = 0 begin goto endlabel end if @ich = 'CCC' begin delete DialogMultiLanguage   where UID_DialogMultiLanguage in ( select mh.UID_DialogMultiLanguage  from
22 DialogMultiLanguage ml join DialogMultiLanguage mh on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and ml.EntryKey
23 = mh.EntryKey join DialogColumn c with (readpast) on ml.UID_DialogColumn = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable 
24= t.UID_DialogTable join QBMModuleDef mol with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef
25 moh with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage
26 and  mol.ModuleName <> moh.ModuleName and  not exists (Select top 1 1 from QBMModuleDependCollection co where left(co.UID_ModuleFollower, 3) = mol.ModuleName
27 and left(co.UID_ModulePredecessor, 3) = moh.ModuleName )    and dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString( case mol.ModuleName when @ich then 0 
28else mol.SortOrder end ), 16, '0') + case mol.ModuleName when @ich then '' else mol.ModuleName end < dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(moh.SortOrder
29), 16, '0') + moh.ModuleName  and exists (Select top 1 1 from @DieDoppelten2 je where ml.UID_DialogColumn = je.UID_DialogColumn and ml.UID_DialogCulture
30 = je.UID_DialogCulture and ml.EntryKey = je.EntryKey ) ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'nicht-Lineare gelöscht ' + str
31(@CountRows) end end insert into @DieDoppelten3 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey
32 from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT = 0 begin goto endlabel end 
33delete DialogMultiLanguage where UID_DialogMultiLanguage in ( select mh.UID_DialogMultiLanguage  from DialogMultiLanguage ml join DialogMultiLanguage mh
34 on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and ml.EntryKey = mh.EntryKey join DialogColumn c on ml.UID_DialogColumn
35 = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join QBMModuleDef mol on dbo.QBM_FCVGUIDToModuleOwner
36(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef moh on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where 
37ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage and  mol.ModuleName = @ich and moh.ModuleName = @ich   and dbo.QBM_FCVDatetimeToString(isnull(ml.XDateUpdated
38, '1899-12-30')) + ml.UID_DialogMultiLanguage < dbo.QBM_FCVDatetimeToString(isnull(mh.XDateUpdated, '1899-12-30')) + mh.UID_DialogMultiLanguage  and exists
39 (Select top 1 1 from @DieDoppelten3 je where ml.UID_DialogColumn = je.UID_DialogColumn and ml.UID_DialogCulture = je.UID_DialogCulture and ml.EntryKey
40 = je.EntryKey ) ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'echte Doubletten gelöscht ' + str(@CountRows) end END TRY BEGIN CATCH
41 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 
42endLabel:  return end 
43