Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Autor         : J.Feuerstein'
Print ' Creation Date : 02.02.2015'
Print ' #LUP..........: 16.11.2023 / @ObjectFromSiteID - @ObjectFromSiteId'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


Print '-- [DS_IR_Relation_CHECK] --------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DS_IR_Relation_CHECK" and type = "P")
Drop Procedure DS_IR_Relation_CHECK
GO


Create Procedure DS_IR_Relation_CHECK
                                    @OwningSiteID                nvarchar(64) = null,
                                    @Import_Obj_ObjectName       nvarchar(128) = null,
                                    @Import_Obj_ObjectType       nvarchar(64) = null,
                                    @ObjectCLINameNew            nvarchar(128) = null,
                                    @Import_Obj_IR               nvarchar(128) = null,
                                    @IrCLICur                    nvarchar(128) = null,
                                    @IrCLINew                    nvarchar(128) = null,
                                    @IrId_r                      uniqueidentifier = null Output

/* 02.02.2015 21:11
Repairs for Named Base Object IR and DS Relations */
As
BEGIN

 DECLARE @DataSetId_r uniqueidentifier
 DECLARE @DataSet_Ir_RelId uniqueidentifier
 DECLARE @DataSet_ItemIr_RelId uniqueidentifier

 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='DS_IR_Relation_CHECK'

 if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '----------------------' , '----------------------'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '01-@@OwningSiteID' ,          @OwningSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '02-@Import_Obj_ObjectName' ,  @Import_Obj_ObjectName
  end


declare @DSId  uniqueidentifier,
        @IrCLICurId  uniqueidentifier,
        @IrCLINewId  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @ObjectFromSiteId nvarchar(32),
        @ProcessPacketName nvarchar(255)

 declare @IrCLICurTbl TABLE(
  IrCLICurId  uniqueidentifier not null,
  ObjectCliName nvarchar(255) not null
)
  DECLARE IrCLICurTbl_cursor CURSOR
  FOR SELECT IrCLICurId,ObjectCliName FROM @IrCLICurTbl
      ORDER BY ObjectCliName

  -- #Step: suche DS
   set @IrId_r = null
   select @DSId=Id from Objects_tbl WITH (nolock)
                 where ObjectName       = @Import_Obj_ObjectName
                   and ObjectFromSiteID = @OwningSiteID
                   and ObjectClass      = 'DS'
    if @DSId is NULL
    begin
       goto MyExit
    end

    -- #Step: suche IR aktuelle
    insert INTO @IrCLICurTbl
    select ID, ObjectCLIName
           from Objects_tbl WITH (nolock)
                where ObjectCLIName    = @IrCLICur
                  and ObjectFromSiteID = @OwningSiteID
                  and ObjectClass      = 'IR'

    OPEN IrCLICurTbl_cursor
    while 1=1
     begin
     FETCH NEXT FROM IrCLICurTbl_cursor into @IrCLICurId
     if (@@fetch_status <> 0) break
     -- Use Order by
     -- finde ersten
     --
     end
    CLOSE IrCLICurTbl_cursor
    DEALLOCATE IrCLICurTbl_cursor



    set @IrCLICurId=null
    select @IrCLICurId=Id from Objects_tbl WITH (nolock)
            where ObjectCLIName    = @IrCLICur
              and ObjectFromSiteID = @OwningSiteID
              and ObjectClass      = 'IR'

    if @IrCLICurId is NULL
    begin
       goto MyExit
    end

    -- #Step: suche IR aktuelle
    set @IrCLINewId=null
    select @IrCLINewId=Id from Objects_tbl WITH (nolock)
            where ObjectCLIName    = @IrCLINew
              and ObjectFromSiteID = @OwningSiteID
              and ObjectClass      = 'IR'

    if @IrCLICurId is NULL
    begin
       goto MyExit
    end

    if @IrCLINewId is NULL
    begin
       goto MyExit
    end

    -- #Step: Update DS
    UPDATE Objects_tbl SET [ObjectCLIName]=@ObjectCLINameNew where [Id]= @DSId


 MyExit:

END
GO

--- ####################
Print '-- [Objects_tbl_DS_AssignToIR #New: 05.07.2021 J.Fes] ---------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_DS_AssignToIR" and type = "P")
DROP PROCEDURE Objects_tbl_DS_AssignToIR
GO

-- #Doc: Returns for  @DSPuid and @ObjectFromSiteId all IRS
CREATE PROCEDURE Objects_tbl_DS_AssignToIR(@ObjectFromSiteId nvarchar(32),
                                           @DSPuid nvarchar(64),
                                           @IRPuid nvarchar(64),
                                           @RowsUpdatedN_r int = null Output,
                                           @ResultMsg_r nvarchar(max) = null Output)

As
BEGIN


 DECLARE @DebugPrintOn bit,
         @MyProcName nvarchar(32),
         @DSId uniqueidentifier,
         @IRId uniqueidentifier
 set @DebugPrintOn = 0
 set @MyProcName='Objects_tbl_DS_AssignToIR'

 if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '----------------------' , '----------------------'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '01-ObjectFromSiteId' ,        @ObjectFromSiteId
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '02-DSPuid' ,                  @DSPuid
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '03-IRPuid' ,                  @IRPuid
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '04-ResultMsg_r' ,             @ResultMsg_r
  end


 -- set @MyProcName='Objects_tbl_DS_AssignToIR'

 -- #Doc: Step: find IR ID ermittelt @ObjectId Id der IR aus Objects_tbl
 EXECUTE Objects_tbl_Object_GetId_via_ObjectPuidOnly
         @ObjectFromSiteId,
         @IRPuid,
         'IR',
         @IRId Output

 -- #Doc: Step: find DS ID
 EXECUTE Objects_tbl_Object_GetId_via_ObjectPuidOnly
         @ObjectFromSiteId,
         @DSPuid,
         'DS',
         @DSId Output

 -- ID IrId ObjectID
 -- ID Ir1  Obj1
 -- ID Ir2  Obj1  UPDATE Objects_Ir_Rel_tbl SET [IrId]='Ir2' where [ObjectId]='Obj1'

 if (NOT @IRId is NULL) AND (NOT @DSId is NULL)
 begin
  -- #Doc: Edit IR-DS Releation via change IRId @DSId
  UPDATE Objects_Ir_Rel_tbl SET [IrId]=@IRId where [ObjectId]=@DSId
  set @RowsUpdatedN_r = @@ROWCOUNT
  set @ResultMsg_r = 'OK: ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + '(n) rows affected'
 end
 else
 begin
   set @ResultMsg_r = 'ISSUE:Data missing SideId:' + @ObjectFromSiteId
   if @DSId is NULL set @ResultMsg_r = @ResultMsg_r + ' DS.Puid nf:' + @DSPuid
   if @IRId is NULL set @ResultMsg_r = @ResultMsg_r + ' IR.Puid nf:' + @IRPuid
   set @RowsUpdatedN_r = 0
 end

 if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '10-@ResultMsg_r', @ResultMsg_r
  end

end
GO
--- ####################

ende:

Print ''
Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'

GO

